<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 "First Python Notebook" data preparation
By Ben Welsh
This notebook prepares a dataset to be used in the forthcoming class "First Python Notebook
Step2:  Download raw data
Pull down raw CAL-ACCESS files published by the [California Civic Data Coalition's API](http
Step3:  Itemized receipts reported by campaigns
Step5:  Hack down RCPT_CD table outside of Python
The raw RCPT_CD table is nearly 2 gigabytes in size and too large to load into this Python notebook. Outside of this script, I split it into slices of 1,000,000 rows using the Linux command line.
bash
$ split -l 1000000 rcpt_cd.csv rcpt_cd_part
I then cleaned up the file names a little and added the header row to all files using vim.
Import and trim the RCPT_CD table
The source file is believed to expand in chronological order as new filings are made with the state. Since we are only interested in studying contributions to a 2016 ballot, only the three latest slices are imported and trimmed.
Step6:  Concatenate them together into one DataFrame.
Step8:  Remove amended filings
Step9:  Filter the table down to monetary contributions reported via Schedule A.
Step10:  Reduce the number of the headers down to the ones we want to keep
Step11:  Rename the ugly ones
Step12:  Import and trim the FILER_FILINGS_CD table
Download and read in the table with links between the RCPT_CD table's FILING_ID field and the FILER_ID field that identifies which committee reported the receipt.
Step13:  Since this table does not indicate if the filing is an amendment, let's just reduce it to the distinct connections between filers and filings.
Step14:  Import the shortlist of Prop. 64 committees we want to study
California's Proposition 64 asks voters if the growth and sale marijuana should be legalized in the state. As of September 20, California's Secretary of State reports that 16 million has been raised to campaign in support the measure, and 2 million to oppose it. 
Here are the committees the state lists as supporting the measure.
Step15:  Here are the committees the state lists as opposing the measure.
Step16:  Join Prop. 64 committees to the contributions they've received
Step17:  Export the data to a CSV file
<ASSISTANT_TASK:>
Python Code:
import os
import requests
from datetime import datetime
from clint.textui import progress
import pandas
pandas.set_option('display.float_format', lambda x: '%.2f' % x)
pandas.set_option('display.max_columns', None)
Explanation: "First Python Notebook" data preparation
By Ben Welsh
This notebook prepares a dataset to be used in the forthcoming class "First Python Notebook: Scripting your way to the story."
The course is scheduled to be taught for the first time at the Oct. 2 2016 "watchdog workshop" organized by Investigative Reporters and Editors at San Diego State University's school of journalism.
It will focus on teaching the basic skills of Python by analyzing the contributors to Proposition 64, which asks California voters this November to decide if the growth and sale of marijuana should be legalized.
The preparation effort is necessary because the raw data published at californiacivicdata.org is still difficult for beginners to navigate.
The goal is to create something simple so the class can focus on using Python to interrogate the data.
Import Python tools
End of explanation
def download_csv(name):
    
    Accepts the name of a calaccess.download CSV and returns its path.
    
    path = os.path.join(os.getcwd(), '{}.csv'.format(name))
    if not os.path.exists(path):
        url = "http://calaccess.download/latest/{}.csv".format(name)
        r = requests.get(url, stream=True)
        with open(path, 'w') as f:
            total_length = int(r.headers.get('content-length'))
            for chunk in progress.bar(r.iter_content(chunk_size=1024), expected_size=(total_length/1024) + 1): 
                if chunk:
                    f.write(chunk)
                    f.flush()
    return path
Explanation: Download raw data
Pull down raw CAL-ACCESS files published by the [California Civic Data Coalition's API](http://www.californiacivicdata.org/.
End of explanation
rcpt_path = download_csv("rcpt_cd")
ff_path = download_csv("filer_filings_cd")
Explanation: Itemized receipts reported by campaigns
End of explanation
def rcpt_part_to_dataframe(part_name):
    
    Import a slide of the RCPT_CD table prepared for this notebook.
    
    file_name = "rcpt_cd_parta{}.csv".format(part_name)
    path = os.path.join(os.getcwd(), file_name)
    return pandas.read_csv(path, sep=',', dtype="unicode")
itemized_receipts_df_h = rcpt_part_to_dataframe("h")
itemized_receipts_df_i = rcpt_part_to_dataframe("i")
itemized_receipts_df_j = rcpt_part_to_dataframe("j")
Explanation: Hack down RCPT_CD table outside of Python
The raw RCPT_CD table is nearly 2 gigabytes in size and too large to load into this Python notebook. Outside of this script, I split it into slices of 1,000,000 rows using the Linux command line.
bash
$ split -l 1000000 rcpt_cd.csv rcpt_cd_part
I then cleaned up the file names a little and added the header row to all files using vim.
Import and trim the RCPT_CD table
The source file is believed to expand in chronological order as new filings are made with the state. Since we are only interested in studying contributions to a 2016 ballot, only the three latest slices are imported and trimmed.
End of explanation
recent_itemized_receipts = pandas.concat([
    itemized_receipts_df_h,
    itemized_receipts_df_i,
    itemized_receipts_df_j
])
Explanation: Concatenate them together into one DataFrame.
End of explanation
def remove_amended_filings(df):
    
    Accepts a dataframe with FILING_ID and AMEND_ID files.
    
    Returns only the highest amendment for each unique filing id.
    
    max_amendments = df.groupby('FILING_ID')['AMEND_ID'].agg("max").reset_index()
    merged_df = pandas.merge(df, max_amendments, how='inner', on=['FILING_ID', 'AMEND_ID'])
    print "Removed {} amendments".format(len(df)-len(merged_df))
    print "DataFrame now contains {} rows".format(len(merged_df))
    return merged_df
real_recent_itemized_receipts = remove_amended_filings(recent_itemized_receipts)
Explanation: Remove amended filings
End of explanation
real_sked_a = real_recent_itemized_receipts[
    real_recent_itemized_receipts['FORM_TYPE'] == 'A'
]
Explanation: Filter the table down to monetary contributions reported via Schedule A.
End of explanation
trimmed_itemized = real_sked_a[[
    'FILING_ID',
    'AMEND_ID',
    'CTRIB_NAMF',
    'CTRIB_NAML',
    'CTRIB_CITY',
    'CTRIB_ST',
    'CTRIB_ZIP4',
    'CTRIB_EMP',
    'CTRIB_OCC',
    'RCPT_DATE',
    'AMOUNT',
]]
Explanation: Reduce the number of the headers down to the ones we want to keep
End of explanation
clean_itemized = trimmed_itemized.rename(
    index=str,
    columns={
        "CTRIB_NAMF": "FIRST_NAME",
        "CTRIB_NAML": "LAST_NAME",
        "CTRIB_CITY": "CITY",
        "CTRIB_ST": "STATE",
        "CTRIB_ZIP4": "ZIPCODE",
        "CTRIB_EMP": "EMPLOYER",
        "CTRIB_OCC": "OCCUPATION",
        "RCPT_DATE": "DATE"
    }
)
Explanation: Rename the ugly ones
End of explanation
filer_filings_df = pandas.read_csv(ff_path, sep=',', index_col=False, dtype='unicode')
Explanation: Import and trim the FILER_FILINGS_CD table
Download and read in the table with links between the RCPT_CD table's FILING_ID field and the FILER_ID field that identifies which committee reported the receipt.
End of explanation
filer_to_filing = filer_filings_df[['FILER_ID', 'FILING_ID']].drop_duplicates()
Explanation: Since this table does not indicate if the filing is an amendment, let's just reduce it to the distinct connections between filers and filings.
End of explanation
supporting_committees = pandas.DataFrame([
    {"COMMITTEE_ID":"1343793","COMMITTEE_NAME":"Californians for Responsible Marijuana Reform, Sponsored by Drug Policy Action, Yes on Prop. 64"},
    {"COMMITTEE_ID":"1376077","COMMITTEE_NAME":"Californians for Sensible Reform, Sponsored by Ghost Management Group, LLC dba Weedmaps"},
    {"COMMITTEE_ID":"1385506","COMMITTEE_NAME":"Drug Policy Action - Non Profit 501c4, Yes on Prop. 64"},
    {"COMMITTEE_ID":"1385745","COMMITTEE_NAME":"Fund for Policy Reform (Nonprofit 501(C)(4))"},
    {"COMMITTEE_ID":"1371855","COMMITTEE_NAME":"Marijuana Policy Project of California"},
    {"COMMITTEE_ID":"1382525","COMMITTEE_NAME":"New Approach PAC (MPO)"},
    {"COMMITTEE_ID":"1386560","COMMITTEE_NAME":"The Adult Use Campaign for Proposition 64"},
    {"COMMITTEE_ID":"1381808","COMMITTEE_NAME":"Yes on 64, Californians to Control, Regulate and Tax Adult Use of Marijuana While Protecting Children, Sponsored by Business, Physicians, Environmental and Social-Justice Advocate Organizations"}
])
supporting_committees['COMMITTEE_POSITION'] = 'SUPPORT'
Explanation: Import the shortlist of Prop. 64 committees we want to study
California's Proposition 64 asks voters if the growth and sale marijuana should be legalized in the state. As of September 20, California's Secretary of State reports that 16 million has been raised to campaign in support the measure, and 2 million to oppose it. 
Here are the committees the state lists as supporting the measure.
End of explanation
opposing_committees = pandas.DataFrame([
    {"COMMITTEE_ID":"1382568","COMMITTEE_NAME":"No on Prop. 64, Sponsored by California Public Safety Institute"},
    {"COMMITTEE_ID":"1387789","COMMITTEE_NAME":"Sam Action, Inc., a Committee Against Proposition 64 with Help from Citizens (NonProfit 501(C)(4))"}
])
opposing_committees['COMMITTEE_POSITION'] = 'OPPOSE'
prop_64_committees = pandas.concat([supporting_committees, opposing_committees])
Explanation: Here are the committees the state lists as opposing the measure.
End of explanation
prop_64_filings = filer_to_filing.merge(
    prop_64_committees,
    how="inner",
    left_on='FILER_ID',
    right_on="COMMITTEE_ID"
)
prop_64_itemized = prop_64_filings.merge(
    clean_itemized,
    how="inner",
    left_on="FILING_ID",
    right_on="FILING_ID"
)
print len(prop_64_itemized)
Explanation: Join Prop. 64 committees to the contributions they've received
End of explanation
prop_64_itemized.drop('FILER_ID', axis=1, inplace=True)
prop_64_itemized.to_csv("./prop_64_contributions.csv", index=False)
Explanation: Export the data to a CSV file
End of explanation
<END_TASK>