<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
Step2:  Independent expenditures experiments
By Ben Welsh
Import tools
Step3:  Download quarterly filings
Independent expenditure committees are required to file Form 461. The summary totals from those filings are found in the SMRY_CD file.
Step4:  Basic information about the file
Step5:  Frequency counts on the fields
Step6:  Filter down to only Form 461 filings
Step7:  Check what line items are recorded for this form
Step8:  Here's the paper form where those five line-items can be seen in the right-hand summary section. It looks to be that line three is the total made during the current reporting period.
Filter down to those total line items
Step9:  Prepare the table for analysis
Step10:  Download itemized independent expenditures
Download EXPN_CD table
Filter it down to independent expenditures
Remove all amended filings
Check if they all come from Form 461 filings, or if other forms can disclosure IEs
If they all come from 461s, try to walk them back to their filers via the cover sheet or filer_filings or something
If you can do that, reconcile them with the late filings with some kind of date trick
Step11:  Download late independent expenditure filings
They are filed via Form 496 for all independent expenditures over $1,000 in the 90 days prior to an election. The data are recorded in the S496 file.
Step12:  Convert the date field to a datetime object
Step13:  Basic information about the file
Step14:  Frequency counts on the fields
Step15:  Preparing the file for analysis
Step16:  Figure out what to do with the different line numbers
Let's start by pulling the filing with the most line items.
Step17:  That filing can be reviewed here. The expenditure count is 104, which matches this data. There are three late contributions at the bottom that do not appear in this table and must be recorded elsewhere.
Summing up the money
Step18:  Spending by year
Step19:  Spending by month
Step20:  Summing up the expenditure types
<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)
import matplotlib.pyplot as plt
import matplotlib.dates as dates
%matplotlib inline
def download_csv_to_dataframe(name):
    
    Accepts the name of a calaccess.download CSV and returns it as a pandas dataframe.
    
    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 pandas.read_csv(path)
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
Explanation: Independent expenditures experiments
By Ben Welsh
Import tools
End of explanation
quarterly_df = download_csv_to_dataframe("smry_cd")
Explanation: Download quarterly filings
Independent expenditure committees are required to file Form 461. The summary totals from those filings are found in the SMRY_CD file.
End of explanation
quarterly_df.info()
quarterly_df.head()
Explanation: Basic information about the file
End of explanation
quarterly_df.groupby(['FORM_TYPE'])['FILING_ID'].agg(['count'])
Explanation: Frequency counts on the fields
End of explanation
quarterly_ie_filings = quarterly_df[quarterly_df['FORM_TYPE'] == 'F461']
print len(quarterly_ie_filings)
Explanation: Filter down to only Form 461 filings
End of explanation
quarterly_ie_filings.groupby(['LINE_ITEM'])['FILING_ID'].agg(['count'])
Explanation: Check what line items are recorded for this form
End of explanation
quarterly_ie_totals = quarterly_ie_filings[quarterly_ie_filings['LINE_ITEM'] == '3']
print len(quarterly_ie_totals)
Explanation: Here's the paper form where those five line-items can be seen in the right-hand summary section. It looks to be that line three is the total made during the current reporting period.
Filter down to those total line items
End of explanation
real_ie_totals = remove_amended_filings(quarterly_ie_totals)
Explanation: Prepare the table for analysis
End of explanation
itemized_df = download_csv_to_dataframe("expn_cd")
itemized_df.info()
itemized_ies = itemized_df[itemized_df['EXPN_CODE'] == 'IND']
print len(itemized_ies)
real_itemized_ies = remove_amended_filings(itemized_ies)
real_itemized_ies.sort_values('EXPN_DATE', ascending=False).head()
Explanation: Download itemized independent expenditures
Download EXPN_CD table
Filter it down to independent expenditures
Remove all amended filings
Check if they all come from Form 461 filings, or if other forms can disclosure IEs
If they all come from 461s, try to walk them back to their filers via the cover sheet or filer_filings or something
If you can do that, reconcile them with the late filings with some kind of date trick
End of explanation
late_df = download_csv_to_dataframe("s496_cd")
Explanation: Download late independent expenditure filings
They are filed via Form 496 for all independent expenditures over $1,000 in the 90 days prior to an election. The data are recorded in the S496 file.
End of explanation
late_df['EXP_DATE'] = pandas.to_datetime(
    late_df['EXP_DATE'],
    errors="coerce"
)
Explanation: Convert the date field to a datetime object
End of explanation
late_df.info()
late_df.head()
Explanation: Basic information about the file
End of explanation
late_df.groupby(['REC_TYPE'])['FILING_ID'].agg(['count'])
late_df.groupby(['FORM_TYPE'])['FILING_ID'].agg(['count'])
late_df.groupby(['LINE_ITEM'])['FILING_ID'].agg(['count'])
Explanation: Frequency counts on the fields
End of explanation
real_late_filings = remove_amended_filings(late_df)
Explanation: Preparing the file for analysis
End of explanation
real_late_filings[real_late_filings['LINE_ITEM'] == 104]
longest_late_filing = real_late_filings[real_late_filings['FILING_ID'] == 1717649]
longest_late_filing
Explanation: Figure out what to do with the different line numbers
Let's start by pulling the filing with the most line items.
End of explanation
real_late_filings['AMOUNT'].sum()
Explanation: That filing can be reviewed here. The expenditure count is 104, which matches this data. There are three late contributions at the bottom that do not appear in this table and must be recorded elsewhere.
Summing up the money
End of explanation
def trim_to_year(row):
    try:
        return row['EXP_DATE'].year
    except TypeError:
        return pandas.NaN
real_late_filings["year"] = real_late_filings.apply(trim_to_year, axis=1) 
late_by_year = real_late_filings.groupby('year')['AMOUNT'].agg('sum')
late_by_year = late_by_year.to_frame('sum').reset_index()
fig = plt.figure(1, figsize=(16,8))
ax1 = fig.add_subplot(211)
ax1.plot(late_by_year['year'], late_by_year['sum'])
ax1.set_title('Independent expenditure spending')
ax1.set_xlabel('Year')
ax1.set_ylabel('Sum')
Explanation: Spending by year
End of explanation
def trim_to_month(row):
    try:
        return datetime(year=row['EXP_DATE'].year, month=row['EXP_DATE'].month, day=1)
    except TypeError:
        return pandas.NaT
real_late_filings["month"] = real_late_filings.apply(trim_to_month, axis=1) 
late_by_month = real_late_filings.groupby('month')['AMOUNT'].agg('sum')
late_by_month = late_by_month.to_frame('sum').reset_index()
fig = plt.figure(1, figsize=(16,8))
ax1 = fig.add_subplot(211)
ax1.plot(late_by_month['month'], late_by_month['sum'])
ax1.set_title('Independent expenditure spending')
ax1.set_xlabel('Month')
ax1.set_ylabel('Sum')
Explanation: Spending by month
End of explanation
by_description = real_late_filings.groupby('EXPN_DSCR')['AMOUNT'].agg('sum')
by_description = by_description.to_frame('sum').reset_index()
by_description.sort_values("sum", ascending=False)
Explanation: Summing up the expenditure types
End of explanation
<END_TASK>