<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Data cleanup
This notebook is meant for cleaning up the donation data.
The following is a summary of some of the cleanup tasks from this notebook
Step1:  Load csv
Step2:  Address nan column values
Step3:  Change column types and drop unused columns
Step4:  Cleanup amounts
Step5:  Outlier data
Step6:  Exchanged emails with Anil and confirmed the decision to drop the outlier for the anonymous donor with the 9.5 million dollars.
Step7:  Amounts with zero values
Step8:  Dropping rows with zero amounts (after confirmation with SEF office)
Step9:  Negative amounts
Step10:  Dropping rows with negative amounts (after confirmation with SEF office)
Step11:  Investigate invalid state codes
Step12:  Explanation for invalid state codes
Step13:  Dropping data for non-US locations
Step14:  Investigate donations with state of YY
Step15:  We will add these donations to the noloc_df below (which is the donations that have empty strings for the city/state/zipcode.
Investigate empty city, state and zip code
Pecentage of total amount from donations with no location
Step16:  Investigate City in ('YYY','yyy')
These entries have invalid location information and will be added to the noloc_df dataframe.
Step17:  Investigate empty state but non-empty city
Percentage of total amount for data with City but no state
Step18:  By visually examining the cities for rows that don't have a state, we can see that all the cities are coming from Canada and India and some from other countries (except two entries). So we will correct these two entries and drop all the other rows as they are not relevant to the USA.
Step19:  Investigate empty city and zipcode but valid US state
Percentage of total amount for data with valid US state, but no city, zipcode
Step20:  Investigating empty city and empty state with non-empty zip code
Since we have the zip code data from the US census data, we can use that to fill in the city and state
Step21:  Investigate invalid zip codes
Step22:  Final check on all location data to confirm that we have no rows with empty state, city or location
Step23:  All done! Let's save our dataframes for the next stage of processing
<ASSISTANT_TASK:>
Python Code:
import pandas as pd
import numpy as np
import locale
import matplotlib.pyplot as plt
from bokeh.plotting import figure, show
from bokeh.models import ColumnDataSource, HoverTool
%matplotlib inline
from bokeh.plotting import output_notebook
output_notebook()
_ = locale.setlocale(locale.LC_ALL, '')
thousands_sep = lambda x: locale.format("%.2f", x, grouping=True)
#example:
print thousands_sep(1234567890.76543)
getdate_ym = lambda x: str(x.year) + "_" + str(x.month)
getdate_ymd = lambda x: str(x.month) + "/" + str(x.day) + "/" + str(x.year)
dates = pd.DatetimeIndex(['2010-10-17', '2011-05-13', "2012-01-15"])
map(getdate_ym, dates)
map(getdate_ymd, dates)
Explanation: Data cleanup
This notebook is meant for cleaning up the donation data.
The following is a summary of some of the cleanup tasks from this notebook:
Load the csv file that has the donors
Strip out whitespace from all the columns
Fill na with empty strings
Change column data types (after examining for correctness)
Cleanup amounts column - removed negative (totals to -641910.46 dollars) and zero values
Cleanup state codes.
Removed donations that are outside of US - about \$30,000 USD
Removed donations totaling to 9.5 million dollars that came from anonymous donors (as outliers)
If there is no location information or it is inaccurate, move it to a different, move it to a different dataframe
Update the city and state names when not present based on the zipcodes dataset.
End of explanation
df = pd.read_csv('in/gifts_Feb2016_2.csv')
source_columns = ['donor_id', 'amount_initial', 'donation_date', 'appeal', 'fund', 'city', 'state', 'zipcode_initial', 'charitable', 'sales']
df.columns = source_columns
df.info()
strip_func = lambda x: x.strip() if isinstance(x, str) else x
df = df.applymap(strip_func)
Explanation: Load csv
End of explanation
df.replace({'appeal': {'0': ''}}, inplace=True)
df.appeal.fillna('', inplace=True)
df.fund.fillna('', inplace=True)
Explanation: Address nan column values
End of explanation
df.donation_date = pd.to_datetime(df.donation_date)
df.charitable = df.charitable.astype('bool')
df['zipcode'] = df.zipcode_initial.str[0:5]
fill_zipcode = lambda x: '0'*(5-len(str(x))) + str(x)
x1 = pd.DataFrame([[1, '8820'], [2, 8820]], columns=['a','b'])
x1.b = x1.b.apply(fill_zipcode)
x1
df.zipcode = df.zipcode.apply(fill_zipcode)
Explanation: Change column types and drop unused columns
End of explanation
## Ensure that all amounts are dollar figures
df[~df.amount_initial.str.startswith('-$') & ~df.amount_initial.str.startswith('$')]
## drop row with invalid data
df.drop(df[df.donation_date == '1899-12-31'].index, axis=0, inplace=True)
df['amount_cleanup'] = df.amount_initial.str.replace(',', '')
df['amount_cleanup'] = df.amount_cleanup.str.replace('$', '')
df['amount'] = df.amount_cleanup.astype(float)
## Make sure we did not throw away valid numbers by checking with the original value
df[(df.amount == 0)].amount_initial.unique()
Explanation: Cleanup amounts
End of explanation
# There are some outliers in the data, quite a few of them are recent.
_ = plt.scatter(df[df.amount > 5000].amount.values, df[df.amount > 5000].donation_date.values)
plt.show()
# Fun little thing to try out bokeh (we can hover and detect the culprits)
def plot_data(df):
    dates = map(getdate_ym, pd.DatetimeIndex(df[df.amount > 5000].donation_date))
    amounts = map(thousands_sep, df[df.amount > 5000].amount)
    x = df[df.amount > 5000].donation_date.values
    y = df[df.amount > 5000].amount.values
    donor_ids = df[df.amount > 5000].donor_id.values
    states = df[df.amount > 5000].state.values
    source = ColumnDataSource(
            data=dict(
                x=x,
                y=y,
                dates=dates,
                amounts=amounts,
                donor_ids=donor_ids,
                states=states,
            )
        )
    hover = HoverTool(
            tooltips=[
                ("date", "@dates"),
                ("amount", "@amounts"),
                ("donor", "@donor_ids"),
                ("states", "@states"),
            ]
        )
    p = figure(plot_width=400, plot_height=400, title=None, tools=[hover])
    p.circle('x', 'y', size=5, source=source)
    show(p)
plot_data(df.query('amount > 5000'))
# All the Outliers seem to have the following properties: state == YY and specific donorid.
# Plot the remaining data outside of these to check that we caught all the outliers.
plot_data(df[~df.index.isin(df.query('state == "YY" and amount > 5000').index)])
# Outlier data
df[(df.state == 'YY') & (df.amount >= 45000)]
df[(df.state == 'YY') & (df.amount >= 45000)]\
    .sort_values(by='amount', ascending=False)\
    .head(6)[source_columns]\
    .to_csv('out/0/outlier_data.csv')
Explanation: Outlier data
End of explanation
df.drop(df[(df.state == 'YY') & (df.amount >= 45000)].index, inplace=True)
print 'After dropping the anonymous donor, total amounts from the unknown state as a percentage of all amounts is: '\
    , thousands_sep(100*df[(df.state == 'YY')].amount.sum()/df.amount.sum()), '%'
Explanation: Exchanged emails with Anil and confirmed the decision to drop the outlier for the anonymous donor with the 9.5 million dollars.
End of explanation
## Some funds have zero amounts associated with them.
## They mostly look like costs - expense fees, transaction fees, administrative fees
## Let us examine if we can safely drop them from our analysis
df[df.amount_initial == '$0.00'].groupby(['fund', 'appeal'])['donor_id'].count()
Explanation: Amounts with zero values
End of explanation
df.drop(df[df.amount == 0].index, axis=0, inplace=True)
Explanation: Dropping rows with zero amounts (after confirmation with SEF office)
End of explanation
## What is the total amount of the negative?
print 'Total negative amount is: ', df[df.amount < 0].amount.sum()
# Add if condition to make this re-runnable
if df[df.amount < 0].amount.sum() > 0:
    print 'Amounts grouped by fund and appeal, sorted by most negative amounts'
    df[df.amount < 0]\
        .groupby(['fund', 'appeal'])['amount',]\
        .sum()\
        .sort_values(by='amount')\
        .to_csv('out/0/negative_amounts_sorted.csv')
    df[df.amount < 0]\
        .groupby(['fund', 'appeal'])['amount',]\
        .sum()\
        .to_csv('out/0/negative_amounts_grouped_by_fund.csv')
Explanation: Negative amounts
End of explanation
df.drop(df[df.amount < 0].index, axis=0, inplace=True)
Explanation: Dropping rows with negative amounts (after confirmation with SEF office)
End of explanation
df.info()
df.state.unique()
## States imported from http://statetable.com/
states = pd.read_csv('in/state_table.csv')
states.rename(columns={'abbreviation': 'state'}, inplace=True)
all_states = pd.merge(states, pd.DataFrame(df.state.unique(), columns=['state']), on='state', how='right')
invalid_states = all_states[pd.isnull(all_states.id)].state
df[df.state.isin(invalid_states)].state.value_counts().sort_index()
df[df.state.isin(['56', 'AB', 'BC', 'CF', 'Ca', 'Co', 'HY', 'IO', 'Ny', 'PR', 'UK', 'VI', 'ja'])]
%%html
<style>table {float:left}</style>
Explanation: Investigate invalid state codes
End of explanation
state_renames = {'Ny': 'NY', 'IO': 'IA', 'Ca' : 'CA', 'Co' : 'CO', 'CF' : 'FL', 'ja' : 'FL'}
df.replace({'state': state_renames}, inplace=True)
Explanation: Explanation for invalid state codes:
State|Count|Action|Explanation|
-----|-----|------|-----------|
YY|268|None|All these rows are bogus entries (City and Zip are also YYYYs) - about 20% of the donation amount has this
ON|62|Remove|This is the state of Ontario, Canada
AP|18|Remove|This is data for Hyderabad
VI|6|Remove|Virgin Islands
PR|5|Remove|Peurto Rico
Ny|5|NY|Same as NY - rename Ny as NY
56|1|Remove|This is one donation from Bangalore, Karnataka
HY|1|Remove|Hyderabad
BC|1|Remove|British Columbia, Canada
IO|1|IA|Changed to Iowa - based on city and zip code
AB|1|Remove|AB stands for Alberta, Canada
Ca|1|CA|Same as California - rename Ca to CA
Co|1|CO|Same as Colarado - rename Co to CO
CF|1|FL|Changed to Florida based on zip code and city
ja|1|FL|Change to FL based on zip code and city
UK|1|Remove|London, UK
KA|1|Remove|Bangalore, Karnataka
End of explanation
non_usa_states = ['ON', 'AP', 'VI', 'PR', '56', 'HY', 'BC', 'AB', 'UK', 'KA']
print 'Total amount for locations outside USA: ', sum(df[df.state.isin(non_usa_states)].amount)
#### Total amount for locations outside USA:  30710.63
df.drop(df[df.state.isin(non_usa_states)].index, axis=0, inplace=True)
Explanation: Dropping data for non-US locations
End of explanation
print 'Percentage of amount for unknown (YY) state : {:.2f}'.format(100*df[df.state == 'YY'].amount.sum()/df.amount.sum())
print 'Total amount for the unknown state excluding outliers: ', df[(df.state == 'YY') & (df.amount < 45000)].amount.sum()
print 'Total amount for the unknown state: ', df[(df.state == 'YY')].amount.sum()
print 'Total amount: ', df.amount.sum()
Explanation: Investigate donations with state of YY
End of explanation
print 'Pecentage of total amount from donations with no location: ', 100*sum(df[(df.city == '') & (df.state == '') & (df.zipcode_initial == '')].amount)/sum(df.amount)
noloc_df = df[(df.city == '') & (df.state == '') & (df.zipcode_initial == '')].copy()
df = df[~((df.city == '') & (df.state == '') & (df.zipcode_initial == ''))].copy()
print df.shape[0] + noloc_df.shape[0]
noloc_df = noloc_df.append(df[(df.state == 'YY')])
df = df[~(df.state == 'YY')]
# Verify that we transferred all the rows over correctly. This total must match the total from above.
print df.shape[0] + noloc_df.shape[0]
Explanation: We will add these donations to the noloc_df below (which is the donations that have empty strings for the city/state/zipcode.
Investigate empty city, state and zip code
Pecentage of total amount from donations with no location:  3.087
Moving all the data with no location to a different dataframe.
We will investigate the data that does have location information for correctness of location and then merge the no location data back at the end.
End of explanation
noloc_df = noloc_df.append(df[(df.city.str.lower() == 'yyy') | (df.city.str.lower() == 'yyyy')])
df = df[~((df.city.str.lower() == 'yyy') | (df.city.str.lower() == 'yyyy'))]
# Verify that we transferred all the rows over correctly. This total must match the total from above.
print df.shape[0] + noloc_df.shape[0]
Explanation: Investigate City in ('YYY','yyy')
These entries have invalid location information and will be added to the noloc_df dataframe.
End of explanation
print 'Percentage of total amount for data with City but no state: {:.3f}'.format(100*sum(df[df.state == ''].amount)/sum(df.amount))
df[((df.state == '') & (df.city != ''))][['city','zipcode','amount']].sort_values('city', ascending=True).to_csv('out/0/City_No_State.csv')
Explanation: Investigate empty state but non-empty city
Percentage of total amount for data with City but no state:  0.566
End of explanation
index = df[(df.donor_id == '-28K0T47RF') & (df.donation_date == '2007-11-30') & (df.city == 'Cupertino')].index
df.ix[index,'state'] = 'CA'
index = df[(df.donor_id == '9F4812A118') & (df.donation_date == '2012-06-30') & (df.city == 'San Juan')].index
df.ix[index,'state'] = 'WA'
df.ix[index,'zipcode'] = 98250
# Verified that these remaining entries are for non-US location
print 'Total amount for non-USA location: ', df[((df.state == '') & (df.city != ''))].amount.sum()
df.drop(df[((df.state == '') & (df.city != ''))].index, inplace=True)
Explanation: By visually examining the cities for rows that don't have a state, we can see that all the cities are coming from Canada and India and some from other countries (except two entries). So we will correct these two entries and drop all the other rows as they are not relevant to the USA.
End of explanation
print 'Percentage of total amount for data with valid US state, but no city, zipcode: {:.3f}'.format(100*sum(df[(df.city == '') & (df.zipcode_initial == '')].amount)/sum(df.amount))
# Verify that we transferred all the rows over correctly. This total must match the total from above.
print df.shape[0] + noloc_df.shape[0]
stateonly_df = df[(df.city == '') & (df.zipcode_initial == '')].copy()
stateonly_df.state = ''
## Move the rows with just the state over to the noloc_df dataset
noloc_df = pd.concat([noloc_df, stateonly_df])
df = df[~((df.city == '') & (df.zipcode_initial == ''))].copy()
# Verify that we transferred all the rows over correctly. This total must match the total from above.
print df.shape[0] + noloc_df.shape[0]
print 100*sum(df[df.city == ''].amount)/sum(df.amount)
print len(df[df.city == '']), len(df[df.zipcode_initial == ''])
print sum(df[df.city == ''].amount), sum(df[df.zipcode_initial == ''].amount)
print sum(df[(df.city == '') & (df.zipcode_initial != '')].amount),\
    sum(df[(df.city != '') & (df.zipcode_initial == '')].amount)
print sum(df.amount)
Explanation: Investigate empty city and zipcode but valid US state
Percentage of total amount for data with valid US state, but no city, zipcode: 4.509
Most of this amount (1.7 of 1.8 million) is coming from about 600 donors in California. We already know that about California is a major contributor to donations.
Although, we can do some analytics based on just the US state using this data, it complicates the analysis that does not substantiate the knowledge gain.
Therefore, we are dropping the state column from these rows and moving over this data to the dataset that has no location (the one that we created earlier) to simplify our analysis.
End of explanation
## Zip codes from ftp://ftp.census.gov/econ2013/CBP_CSV/zbp13totals.zip
zipcodes = pd.read_csv('in/zbp13totals.txt', dtype={'zip': object})
zipcodes = zipcodes[['zip', 'city', 'stabbr']]
zipcodes = zipcodes.rename(columns = {'zip':'zipcode', 'stabbr': 'state', 'city': 'city'})
zipcodes.city = zipcodes.city.str.title()
zipcodes.zipcode = zipcodes.zipcode.astype('str')
## If we know the zip code, we can populate the city by using the zipcodes data
df.replace({'city': {'': np.nan}, 'state': {'': np.nan}}, inplace=True)
## Set the index correctly for update to work. Then reset it back.
df.set_index(['zipcode'], inplace=True)
zipcodes.set_index(['zipcode'], inplace=True)
df.update(zipcodes, join='left', overwrite=False, raise_conflict=False)
df.reset_index(drop=False, inplace=True)
zipcodes.reset_index(drop=False, inplace=True)
zipcodesdetail = pd.read_csv('in/zip_code_database.csv')
zipcodesdetail = zipcodesdetail[zipcodesdetail.country == 'US'][['zip', 'primary_city', 'county', 'state', 'timezone', 'latitude', 'longitude']]
zipcodesdetail = zipcodesdetail.rename(columns = {'zip':'zipcode', 'primary_city': 'city'})
# The zip codes dataset has quite a few missing values. Filling in what we need for now.
# If this happens again, search for a different data source!!
zipcodesdetail.loc[(zipcodesdetail.city == 'Frisco') & (zipcodesdetail.state == 'TX') & (pd.isnull(zipcodesdetail.county)), 'county'] = 'Denton'
# Strip the ' County' portion from the county names
def getcounty(county):
    if pd.isnull(county):
        return county
    elif county.endswith(' County'):
        return county[:-7]
    else:
        return county
zipcodesdetail.county = zipcodesdetail['county'].apply(getcounty)
zipcodesdetail.zipcode = zipcodesdetail.zipcode.apply(fill_zipcode)
newcols = np.array(list(set(df.columns).union(zipcodesdetail.columns)))
df = pd.merge(df, zipcodesdetail, on=['state', 'city', 'zipcode'], how='inner', suffixes=('_x', ''))[newcols]
# For some reason, the data types are being reset. So setting them back to their expected data types.
df.donation_date = df.donation_date.apply(pd.to_datetime)
df.charitable = df.charitable.apply(bool)
df.amount = df.amount.apply(int)
Explanation: Investigating empty city and empty state with non-empty zip code
Since we have the zip code data from the US census data, we can use that to fill in the city and state
End of explanation
all_zipcodes = pd.merge(df, zipcodes, on='zipcode', how='left')
all_zipcodes[pd.isnull(all_zipcodes.city_x)].head()
## There seems to be only one row with an invalid zip code. Let's drop it.
df.drop(df[df.zipcode_initial.isin(['GU214ND','94000'])].index, axis=0, inplace=True)
Explanation: Investigate invalid zip codes
End of explanation
print 'No state: count of rows: ', len(df[df.state == ''].amount),\
      'Total amount: ', sum(df[df.state == ''].amount)
print 'No zipcode: count of rows: ', len(df[df.zipcode == ''].amount),\
      'Total amount: ', sum(df[df.zipcode == ''].amount)
print 'No city: count of rows: ', len(df[df.city == ''].amount),\
      'Total amount: ', sum(df[df.city == ''].amount)
# Examining data - top 10 states by amount and number of donors
print df.groupby('state')['amount',].sum().sort_values(by='amount', ascending=False)[0:10]
print df.groupby('state')['donor_id',].count().sort_values(by='donor_id', ascending=False)[0:10]
print noloc_df.state.unique()
print noloc_df.city.unique()
print noloc_df.zipcode.unique()
noloc_df['city'] = ''
noloc_df['state'] = ''
noloc_df['zipcode'] = ''
print df.shape[0] + noloc_df.shape[0]
df.shape, noloc_df.shape
# The input data has the latest zip code for each donor. So we cannot observe any movement even if there was any since
# all donations by a given donor will only have the same exact zipcode.
x1 = pd.DataFrame(df.groupby(['donor_id','zipcode']).zipcode.nunique())
x1[x1.zipcode != 1]
# The noloc_df and the df with location values have no donors in common - so we cannot use the donor
# location information from df to detect the location in noloc_df.
set(df.donor_id.values).intersection(noloc_df.donor_id.values)
df.rename(columns={'donation_date': 'activity_date'}, inplace=True)
df['activity_year'] = df.activity_date.apply(lambda x: x.year)
df['activity_month'] = df.activity_date.apply(lambda x: x.month)
df['activity_dow'] = df.activity_date.apply(lambda x: x.dayofweek)
df['activity_ym'] = df['activity_date'].map(lambda x: 100*x.year + x.month)
df['activity_yq'] = df['activity_date'].map(lambda x: 10*x.year + (x.month-1)//3)
df['activity_ymd'] = df['activity_date'].map(lambda x: 10000*x.year + 100*x.month + x.day)
# Drop the zipcode_initial (for privacy reasons)
df.drop('zipcode_initial', axis=1, inplace=True)
Explanation: Final check on all location data to confirm that we have no rows with empty state, city or location
End of explanation
!mkdir -p out/0
df.to_pickle('out/0/donations.pkl')
noloc_df.to_pickle('out/0/donations_noloc.pkl')
df[df.donor_id == '_1D50SWTKX'].sort_values(by='activity_date').tail()
df.columns
df.shape
Explanation: All done! Let's save our dataframes for the next stage of processing
End of explanation
<END_TASK>