notebook.community

Edit and run

Propositions

Extract ballot measures from the database and their one-to-many relationship with elections





In [1]:



    


import os
import settings
import pandas as pd

Extract all elections





In [2]:



    


elections_sql = """
SELECT *
FROM "calaccess_processed_election"
INNER JOIN "calaccess_processed_event"
ON ("calaccess_processed_election"."event_ptr_id" = "calaccess_processed_event"."id")
ORDER BY "calaccess_processed_event"."start_time";
"""



    











In [3]:



    


elections = pd.read_sql_query(elections_sql, settings.db_connection, parse_dates=["start_time",])



    











In [4]:



    


elections.head()



    


















    
Out[4]:










  
    

      
      event_ptr_id
      state
      is_statewide
      administrative_org_id
      created_at
      updated_at
      extras
      id
      name
      description
      classification
      start_time
      timezone
      end_time
      all_day
    

  
  
    

      0
      ocd-event/e9cb6a1f-203e-4305-883b-17de53940dfd
      st06
      True
      ocd-organization/1831cced-b469-4b47-838e-abd9b...
      2017-03-14 13:01:24.122613+00:00
      2017-03-14 13:01:24.122635+00:00
      {}
      ocd-event/e9cb6a1f-203e-4305-883b-17de53940dfd
      2000 PRIMARY
      
      election
      2000-03-07 00:00:00+00:00
      US/Pacific
      None
      True
    

    

      1
      ocd-event/733b062d-75d2-4e23-8e17-838f808780be
      st06
      True
      ocd-organization/1831cced-b469-4b47-838e-abd9b...
      2017-03-14 13:01:24.161423+00:00
      2017-03-14 13:01:24.161444+00:00
      {}
      ocd-event/733b062d-75d2-4e23-8e17-838f808780be
      2000 GENERAL
      
      election
      2000-11-07 00:00:00+00:00
      US/Pacific
      None
      True
    

    

      2
      ocd-event/710d2a25-b8ea-4617-a370-f21373c78a59
      st06
      False
      ocd-organization/1831cced-b469-4b47-838e-abd9b...
      2017-03-14 13:01:25.163340+00:00
      2017-03-14 13:01:25.163361+00:00
      {}
      ocd-event/710d2a25-b8ea-4617-a370-f21373c78a59
      2001 SPECIAL RUNOFF (ASSEMBLY 65)
      
      election
      2001-02-06 00:00:00+00:00
      US/Pacific
      None
      True
    

    

      3
      ocd-event/583923d1-2ffb-4f2f-9e63-ac80542c5eff
      st06
      False
      ocd-organization/1831cced-b469-4b47-838e-abd9b...
      2017-03-14 13:01:25.581526+00:00
      2017-03-14 13:01:25.581547+00:00
      {}
      ocd-event/583923d1-2ffb-4f2f-9e63-ac80542c5eff
      2001 SPECIAL ELECTION (STATE SENATE 24)
      
      election
      2001-03-26 00:00:00+00:00
      US/Pacific
      None
      True
    

    

      4
      ocd-event/11c07a43-9b8b-47c0-a9ba-599de0fc6f3a
      st06
      False
      ocd-organization/1831cced-b469-4b47-838e-abd9b...
      2017-03-14 13:01:25.136680+00:00
      2017-03-14 13:01:25.136704+00:00
      {}
      ocd-event/11c07a43-9b8b-47c0-a9ba-599de0fc6f3a
      2001 SPECIAL ELECTION (ASSEMBLY 65)
      
      election
      2001-04-03 00:00:00+00:00
      US/Pacific
      None
      True
    

  




















In [5]:



    


trimmed_elections = elections[['id', 'name', 'start_time']]



    











In [6]:



    


trimmed_elections.to_csv(os.path.join(settings.data_dir, "elections.csv"), index=False)

Extract all propositions





In [7]:



    


prop_sql = """
SELECT *
FROM calaccess_processed_ballotmeasurecontest as b
INNER JOIN calaccess_processed_contestbase as c
ON b.contestbase_ptr_id = c.id
"""



    











In [8]:



    


props = pd.read_sql_query(prop_sql, settings.db_connection)

Join in the other IDs from the database





In [9]:



    


prop_calaccess_id = pd.read_sql_table("calaccess_processed_contestidentifier", settings.db_connection)



    











In [10]:



    


prop_local_ids = pd.read_sql_table("calaccess_processed_scrapedproposition", settings.db_connection)



    











In [11]:



    


prop_ids = prop_calaccess_id.merge(prop_local_ids, left_on="identifier", right_on="scraped_id")[['id_y', 'scraped_id', 'contest_id']].rename(
    columns={
        "id_y": "ccdc_prop_id",
        "scraped_id": "calaccess_prop_id",
        "contest_id": "ocd_prop_id"
    },
)



    











In [12]:



    


props = props.merge(prop_ids, left_on="contestbase_ptr_id", right_on="ocd_prop_id")

Clean up fields and join to elections





In [13]:



    


trimmed_props = props[[
    'election_id',
    'ocd_prop_id',
    'calaccess_prop_id',
    'ccdc_prop_id',
    'name',
]]



    











In [14]:



    


merged_props = pd.merge(
    trimmed_elections,
    trimmed_props,
    how="inner",
    left_on="id",
    right_on="election_id",
    suffixes=["_elections", "_props"]
)



    











In [15]:



    


merged_props.rename(columns={
    "name_elections": "election_name",
    "start_time": "election_date",
    "name_props": "prop_name"
}, inplace=True)



    











In [16]:



    


merged_props.drop("id", axis=1, inplace=True)



    











In [17]:



    


merged_props.to_csv(os.path.join(settings.data_dir, "props.csv"), index=False)

Filter down to props from the most recent election





In [18]:



    


recent_props = merged_props[merged_props.election_name == "2016 GENERAL"]



    











In [19]:



    


recent_props



    


















    
Out[19]:










  
    

      
      election_name
      election_date
      election_id
      ocd_prop_id
      calaccess_prop_id
      ccdc_prop_id
      prop_name
    

  
  
    

      151
      2016 GENERAL
      2016-11-08 00:00:00+00:00
      ocd-event/9c8198bc-a05f-44b9-9352-ea04800c55aa
      ocd-contest/08324a50-6e16-4a9a-8f66-ea1fa4dea70d
      1376258
      70
      PROPOSITION 051 - SCHOOL BONDS. FUNDING FOR K-...
    

    

      152
      2016 GENERAL
      2016-11-08 00:00:00+00:00
      ocd-event/9c8198bc-a05f-44b9-9352-ea04800c55aa
      ocd-contest/2d4de540-adcb-4c2e-a58b-13f43f60fff1
      1362198
      71
      PROPOSITION 052 - STATE FEES ON HOSPITALS. FED...
    

    

      153
      2016 GENERAL
      2016-11-08 00:00:00+00:00
      ocd-event/9c8198bc-a05f-44b9-9352-ea04800c55aa
      ocd-contest/687d67df-238e-4424-89d5-7445dcd43aee
      1376142
      72
      PROPOSITION 053 - REVENUE BONDS. STATEWIDE VOT...
    

    

      154
      2016 GENERAL
      2016-11-08 00:00:00+00:00
      ocd-event/9c8198bc-a05f-44b9-9352-ea04800c55aa
      ocd-contest/ea81f6ce-4b1c-42b9-9455-9edceafe9561
      1381642
      73
      PROPOSITION 054 - LEGISLATURE. LEGISLATION AND...
    

    

      155
      2016 GENERAL
      2016-11-08 00:00:00+00:00
      ocd-event/9c8198bc-a05f-44b9-9352-ea04800c55aa
      ocd-contest/5961b189-8414-45ba-9253-eac65069e5d7
      1382647
      74
      PROPOSITION 055 - TAX EXTENSION TO FUND EDUCAT...
    

    

      156
      2016 GENERAL
      2016-11-08 00:00:00+00:00
      ocd-event/9c8198bc-a05f-44b9-9352-ea04800c55aa
      ocd-contest/7ae8b4a5-2c36-413d-9ca5-72ce3f1e289d
      1381640
      75
      PROPOSITION 056 - CIGARETTE TAX TO FUND HEALTH...
    

    

      157
      2016 GENERAL
      2016-11-08 00:00:00+00:00
      ocd-event/9c8198bc-a05f-44b9-9352-ea04800c55aa
      ocd-contest/8c8a3fef-6fea-4031-910f-9fbd01ba1f4d
      1383319
      76
      PROPOSITION 057 - CRIMINAL SENTENCES. JUVENILE...
    

    

      158
      2016 GENERAL
      2016-11-08 00:00:00+00:00
      ocd-event/9c8198bc-a05f-44b9-9352-ea04800c55aa
      ocd-contest/3db22e03-360e-45b0-bc57-d8d798dfb510
      1382395
      77
      PROPOSITION 058 - SB 1174 (CHAPTER 753, STATUT...
    

    

      159
      2016 GENERAL
      2016-11-08 00:00:00+00:00
      ocd-event/9c8198bc-a05f-44b9-9352-ea04800c55aa
      ocd-contest/8a690633-cba0-4838-beae-8a27e058cc15
      1386783
      78
      PROPOSITION 059- SB 254 (CHAPTER 20, STATUTES ...
    

    

      160
      2016 GENERAL
      2016-11-08 00:00:00+00:00
      ocd-event/9c8198bc-a05f-44b9-9352-ea04800c55aa
      ocd-contest/1df18bd0-02a0-4d5e-96bd-6c80ff64d6f9
      1376195
      79
      PROPOSITION 060- ADULT FILMS. CONDOMS. HEALTH ...
    

    

      161
      2016 GENERAL
      2016-11-08 00:00:00+00:00
      ocd-event/9c8198bc-a05f-44b9-9352-ea04800c55aa
      ocd-contest/5e7acbdb-1965-414e-b18a-7f348635344e
      1377343
      80
      PROPOSITION 061- STATE PRESCRIPTION DRUG PURCH...
    

    

      162
      2016 GENERAL
      2016-11-08 00:00:00+00:00
      ocd-event/9c8198bc-a05f-44b9-9352-ea04800c55aa
      ocd-contest/c79212ca-2a51-459c-8ef1-2bc29c46bf39
      1381268
      81
      PROPOSITION 062- DEATH PENALTY. INITIATIVE STA...
    

    

      163
      2016 GENERAL
      2016-11-08 00:00:00+00:00
      ocd-event/9c8198bc-a05f-44b9-9352-ea04800c55aa
      ocd-contest/6516fe7d-c7fb-4315-8f1d-31027eff464f
      1381803
      82
      PROPOSITION 063- FIREARMS. AMMUNITION SALES. I...
    

    

      164
      2016 GENERAL
      2016-11-08 00:00:00+00:00
      ocd-event/9c8198bc-a05f-44b9-9352-ea04800c55aa
      ocd-contest/2a015401-92bc-47f6-bcf9-100ef333b05e
      1381868
      83
      PROPOSITION 064- MARIJUANA LEGALIZATION. INITI...
    

    

      165
      2016 GENERAL
      2016-11-08 00:00:00+00:00
      ocd-event/9c8198bc-a05f-44b9-9352-ea04800c55aa
      ocd-contest/9da3389c-cb25-4a94-a659-f1a09f3a9de6
      1381520
      84
      PROPOSITION 065- CARRY-OUT BAGS. CHARGES. INIT...
    

    

      166
      2016 GENERAL
      2016-11-08 00:00:00+00:00
      ocd-event/9c8198bc-a05f-44b9-9352-ea04800c55aa
      ocd-contest/60ef9a20-9963-45f1-a170-e9e5b35a3295
      1381724
      85
      PROPOSITION 066- DEATH PENALTY. PROCEDURES. IN...
    

    

      167
      2016 GENERAL
      2016-11-08 00:00:00+00:00
      ocd-event/9c8198bc-a05f-44b9-9352-ea04800c55aa
      ocd-contest/50193a28-c6b0-4105-b1b6-a82ee0805402
      1372638
      86
      PROPOSITION 067- REFERENDUM TO OVERTURN BAN ON...
    

  




















In [20]:



    


recent_props.drop([
    'election_name',
    'election_date',
    'election_id'
], axis=1, inplace=True)



    


















    






/home/ben/.virtualenvs/california-2018-fundraising-analysis/lib/python2.7/site-packages/ipykernel/__main__.py:5: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy

















In [21]:



    


recent_props.to_csv(os.path.join(settings.data_dir, "props-2016.csv"), index=False)

Content source: california-civic-data-coalition/python-calaccess-notebooks

Similar notebooks:

notebook.community | gallery | about