<jupyter_start><jupyter_text>4.03: Data Preparation, EDA and Cleaning---<jupyter_code>import pandas as pd
from pandas_profiling import ProfileReport
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
import scipy.stats as stats
import string
import re
import feather
import csv
import os
import collections
from datetime import datetime
from pickletools import pickle
from bs4 import BeautifulSoup
from easymoney.money import EasyPeasy
% matplotlib inline
% config InlineBackend.figure_format = 'retina'
feather_path = '../../../Large Datasets/capstone/feather/'
rep_path = '../../../Large Datasets/capstone/QJPS_6026_supp/'
cap_path = '../../../Large Datasets/capstone/'
feather_files = os.listdir(feather_path)
rep_files = os.listdir(rep_path)
cap_files = os.listdir(cap_path)<jupyter_output><empty_output><jupyter_text>---From Gelman, et al. (2005):> Key methods used in this research are:* (1) plots of repeated cross-sectional analyses,* (2) varying-intercept, varying-slope multilevel models, and* (3) a graph that simultaneously shows within-group and between-group patterns in a multilevel model.> > These statistical tools help us understand patterns of variation within and between states in a way that would not be possible from classical regressions or by looking at tables of coefficient estimates.> Comparing to previous studies of income and voting, our key contribution is to study patterns both within and between states, with both individual income and state-level income as predictors, using survey data on individuals and election and Census data for states and counties. The pattern that richer states support the Democrats is not a simple aggregation of rich voters supporting the Democrats. This can be viewed either as a debunking of the journalistic image of rich “latte” Democrats and poor “Nascar” Republicans—or as support for the journalistic images of political and cultural differences between red and blue states—differences which are not explained by differences in individuals’ incomes. We find that income matters more in “red America” than in “blue America.” In poor states, rich people are much more likely than poor people to vote for the Republican presidential candidate, but in rich states (such as Connecticut), income has almost no correlation with vote preference. The United States has red and blue voters, and red and blue states, but income cuts across them in different ways (a point noted by Alford (1963) in his study of social class and voting by region of the U.S.). As we shall see in Sections 2–4, the statistical technique of multilevel modeling allows us to understand the relation between income and vote among individuals, counties, and states.>  2.  Studying the relation between income and vote preferences>> Survey data show a small but persistent correlation between income and support for the Republican party, but at the aggregate level, it is the Democrats who do better in the richer states. Our strategy to understand these patterns is to study the relation between income and voting in four ways:>> * Aggregate, by state: to what extent do richer states favor the Democrats?> * Nationally, at the level of the individual voter: to what extent do richer voters support the Republicans?> * Individual voters within states: to what extent do richer voters support the Republicans, within any given state? In other words, how much does context matter?> * Counties within states: to what extent do richer counties favor the Democrats, within any given state?>> Patterns at these four levels have much different political interpretations from those supposed by confused political commentators. Most notably, the support for the Democrats in the richer states had led observers to view the typical Democrat as an upper-middle-class resident of a coastal metropolitan area, and the typical Republican as lower-middle-class and rural (see Brooks, 2001). That these claims have been overstated (see Frank, 2004, and Issenberg, 2004) does not seem to lessen their appeal. A multilevel strategy in understanding voting behavior is useful because we are interested in election outcomes as well as individual decisions. Elections are not simple cumulations of voter decisions (because of institutional features such as electoral rules and geographic boundaries, and the political decisions of parties and candidates), and so aggregate analysis should not be dis-carded simply by citing the ecological fallacy problem (Wright, 1989). Trends of economic voting at multiple levels of analysis may or may not be similar, and their causes may or may not besimilar.>>As in Wright (1989, p. 390), we consider the variation at eachlevel of analysis. Income varies far more within states than average income does between states. Consequently, it is the within-state rather than the between-state effect of income that dominates the national patterns. In particular, a positive correlation of income and Republican voting within states, plus a negative correlation between states, combine to form a positive correlation among all voters. We have both individual and aggregate data on income and votes. Thus, the statistical analysis is relatively straightforward, without the well-known problems that can arise when only aggregate data are available (Robinson, 1950, Kramer, 1983).>> * For individual vote preferences, we use the National Election Study (NES) polls taken during the month before each election.> * For aggregate patterns, we use presidential election returns and Bureau of Economic Affairs data on average income by state and county.> * We estimate the relations between income and vote preferences for each presidential election from 1968 to 2000, with a particular goal of studying trends including any changes over time in the support for particular political parties.This background information gives us a nice bird's-eye view of which data we'll need to use when. Luckily, we have pretty much all the data we need (2004 through 2016) on hand. The next step, then, is to go ahead and start importing the data we've collected for comparison with Gelman, et al.'s data, which is freely available as [supplementary information](http://dx.doi.org/10.1561/100.00006026_supp) along with the [original journal article](http://www.stat.columbia.edu/~gelman/research/published/rb_qjps.pdf).---  1. Estimating Income-Voting Relationship at the State Level Importing and viewing the dataGelman, et al.'s supplementary data collection includes a dataset with state-aggregated income and presidential-election data through the year 2000. We will import that first and take a look at it.<jupyter_code>gelman_income_data = feather.read_dataframe(feather_path+'avgincome_orig.feather')
ProfileReport(gelman_income_data)<jupyter_output><empty_output><jupyter_text>The above data provide us with the following information: * `st_fips`: State identifier (`float64`)* `st_year`: Year (`float64`)* `st_state`: State name (`object`).* `st_stateabb`: Postal abbreviation (`object`).* `st_total`: Total number of votes in that year's presidential election, in thousands (`float64`).* `st_dem`, `st_rep`: Number of votes for the Democratic and Republican candidates, respectively, in thousands (`float64`).* `st_repshare`, `st_demshare`: Two-party vote shares as decimals (`float64`).* `st_income`: Average state-level income in 1996 dollars. Source of that data not immediately clear.* `st_inc10k`: Average state-level income in tens of thousands of 1996 dollars. Likely a calculated column.* `test`: Unclear what this variable represents. Will look it up. It may well have been a product of whatever statistical inference the scholars did. Next, let's bring in the `st.dat` file:<jupyter_code>st = pd.read_table(rep_path+'st.dat', sep='"', delimiter=' ')
st.head()
st_1952 = st[st['year'] == 1952]
st_1956 = st[st['year'] == 1956]
st_1960 = st[st['year'] == 1960]
st_1964 = st[st['year'] == 1964]
st_1968 = st[st['year'] == 1968]
st_1972 = st[st['year'] == 1972]
st_1976 = st[st['year'] == 1976]
st_1980 = st[st['year'] == 1980]
st_1984 = st[st['year'] == 1984]
st_1988 = st[st['year'] == 1988]
st_1992 = st[st['year'] == 1992]
st_1996 = st[st['year'] == 1996]
st_2000 = st[st['year'] == 2000]
st_2004 = st[st['year'] == 2004]
import statsmodels.api as sm
X_2000 = st_2000['z.st.inc.pop']
y_2000 = st_2000['st.repshare']
lm_2000 = sm.OLS(y_2000, X_2000)
X_2000 = sm.add_constant(X_2000)
lm_2000 = sm.OLS(y_2000, X_2000)
model_2000 = lm_2000.fit()
model_2000.tvalues
years = [year for year in st['year'].unique()]
model_results = [{'Year':year, 'Coef':None, 'Intercept':None, 'R2':None,
                  'Fitted Values':None, 'Coef t':None, 'Intercept t':None}
                 for year in years]
for i in range(len(years)):
    df = st[st['year'] == years[i]]
    X = sm.add_constant(df['z.st.inc.pop'])
    y = df['st.repshare']
    model = sm.OLS(y, X).fit()
    coef = model.params[1]
    intercept = model.params[0]
    r2 = model.rsquared
    fitted = model.fittedvalues
    coef_t = model.tvalues[1]
    intercept_t = model.tvalues[0]
    model_results[i].update({'Year':years[i], 'Coef':coef, 'Intercept':intercept, 'R2':r2,
                             'Fitted Values':fitted, 'Coef t':coef_t, 'Intercept t':intercept_t})
model_results = [{'Year':year, 'Coef':None, 'Intercept':None, 'R2':None,
                  'Fitted Values':None, 'Coef t':None, 'Intercept t':None}
                 for year in years]
for i in range(len(years)):
    df = st[st['year'] == years[i]]
    X = sm.add_constant(df['z.st.inc.pop'])
    y = df['st.repshare']
    model = sm.OLS(y, X).fit()
    coef = model.params[1]
    intercept = model.params[0]
    r2 = model.rsquared
    fitted = model.fittedvalues
    coef_t = model.tvalues[1]
    intercept_t = model.tvalues[0]
    model_results[i].update({'Year':years[i], 'Coef':coef, 'Intercept':intercept, 'R2':r2,
                             'Coef t':coef_t, 'Intercept t':intercept_t})
lr_df = pd.DataFrame(model_results)
lr_df.head()
lr_df.to_csv('./figure-1-lr_df.csv', index=False)
sns.set_style("white")
# cmap = sns.diverging_palette(250, 15, sep=1, s=90, l=60, n=20, center="dark", as_cmap=True)
fig, ax = plt.subplots(figsize=(20,16))
ax.set_ylim([-0.25,0.25])
points = plt.scatter(lr_df["Year"], lr_df["Coef"],
                     c=lr_df['Coef'], s=800, cmap="coolwarm")
sns.regplot('Year','Coef',data=lr_df, ax=ax, scatter=False, color='gray', line_kws={'lw':8})
ax.set_title('Starting in 1980, Rich States Began\nShifting From GOP to Democrats',
             fontdict={'fontsize':48,'fontname':'Franklin Gothic Medium'})
ax.set_xlabel('Year', fontdict={'fontsize':36, 'fontname':'Franklin Gothic Medium'})
ax.set_ylabel('Slope of GOP Vote Share vs. Income Regression Line',
              fontdict={'fontsize':36, 'fontname':'Franklin Gothic Medium'})
ax.tick_params(axis='both', which='major', labelsize=24)
ax.grid()<jupyter_output><empty_output><jupyter_text>Useful resources:* [Color palettes](https://seaborn.pydata.org/tutorial/color_palettes.html)* [Diverging palettes](https://seaborn.pydata.org/generated/seaborn.diverging_palette.html)* [Regplots with color bars](https://stackoverflow.com/questions/30353363/seaborn-regplot-with-colorbar)<jupyter_code>fig, ax = plt.subplots(figsize=(10,8))
ax.scatter(lr_df['Year'],lr_df['Coef'])
ax.set_ylim([-0.25,0.25])
ax.set_xlabel('Year', fontdict={'fontsize':'18', 'fontname':'Franklin Gothic Medium'})
ax.set_ylabel('Slope of GOP Vote Share - Income Regression Line',
              fontdict={'fontsize':'18', 'fontname':'Franklin Gothic Medium'})
plt.show()<jupyter_output><empty_output><jupyter_text>Now, let's import the election results data for 2008 through 2016, along with income data, and we can replicate the above graph in a more current way:  Importing 2008-2016 election data<jupyter_code>with open('./2016-state.html','r') as f:
    pres_st_2016 = f.read()
f.close()
with open('./2012-state.html','r') as f:
    pres_st_2012 = f.read()
f.close()
with open('./2008-state.html','r') as f:
    pres_st_2008 = f.read()
f.close()
soup = BeautifulSoup(pres_st_2016, 'lxml')
header2016_html = soup.select_one('thead').select_one('tr').select('td')
header = [column.text for column in header2016_html]
# for i,j in enumerate(header):
#     print (i,j)
results2016_html = soup.select('tr')[3:54]
results = []
for row in results2016_html:
    results.append({header[i]:row.select('td')[i].text for i in [2,6,16,17]})
for row in results:
    row['Clinton'] = int(row['Clinton'].replace(',',''))
    row['Trump'] = int(row['Trump'].replace(',',''))
    row['Total\xa0Vote'] = int(row['Total\xa0Vote'].replace(',',''))
for row in results:
    row.update({'DemShare':(row['Clinton']/(row['Trump']+row['Clinton']))})
    row.update({'RepShare':(row['Trump']/(row['Trump']+row['Clinton']))})
    row.update({'Total':row['Total\xa0Vote']})
results_2016 = pd.DataFrame(results)
results_2016 = results_2016[['State','Total\xa0Vote','Clinton','Trump','DemShare','RepShare']]
results_2016.head()
results_2016.rename({'Total\xa0Vote':'Total'},inplace=True)
soup = BeautifulSoup(pres_st_2012, 'lxml')
header2012_html = soup.select_one('thead').select_one('tr').select('td')
header = [column.text for column in header2012_html]
for i,j in enumerate(header):
    print (i,j)
results2012_html = soup.select('tr')[3:54]
results_2012 = []
for row in results2012_html:
    results_2012.append({header[i]:row.select('td')[i].text for i in [2,5,13,14]})
for row in results_2012:
    row['Obama'] = int(row['Obama'].replace(',',''))
    row['Romney'] = int(row['Romney'].replace(',',''))
    row['Total\xa0Vote'] = int(row['Total\xa0Vote'].replace(',',''))
for row in results_2012:
    row.update({'DemShare':(row['Obama']/(row['Romney']+row['Obama']))})
    row.update({'RepShare':(row['Romney']/(row['Romney']+row['Obama']))})
    row.update({'Total':row['Total\xa0Vote']})
results_2012 = pd.DataFrame(results_2012)
results_2012 = results_2012[['State','Total','Obama','Romney','DemShare','RepShare']]
results_2012.head()
soup = BeautifulSoup(pres_st_2008, 'lxml')
header2008_html = soup.select_one('thead').select_one('tr').select('td')
header = [column.text for column in header2008_html]
for i,j in enumerate(header):
    print (i,j)
results2008_html = soup.select('tr')[3:54]
results_2008 = []
for row in results2008_html:
    results_2008.append({header[i]:row.select('td')[i].text for i in [2,5,13,14]})
for row in results_2008:
    row['Obama'] = int(row['Obama'].replace(',',''))
    row['McCain'] = int(row['McCain'].replace(',',''))
    row['Total\xa0Vote'] = int(row['Total\xa0Vote'].replace(',',''))
for row in results_2008:
    row.update({'DemShare':(row['Obama']/(row['McCain']+row['Obama']))})
    row.update({'RepShare':(row['McCain']/(row['McCain']+row['Obama']))})
    row.update({'Total':row['Total\xa0Vote']})
results_2008 = pd.DataFrame(results_2008)
results_2008 = results_2008[['State','Total','Obama','McCain','DemShare','RepShare']]
results_2008['Year'] = 2008
results_2012['Year'] = 2012
results_2016['Year'] = 2016

cols_0816 = ['Year','State','Total','DemTotal','RepTotal','DemShare','RepShare']

results_2008.columns = cols_0816
results_2012.columns = cols_0816
results_2016.columns = cols_0816
results_0816 = results_2008.append([results_2012, results_2016], ignore_index=True)<jupyter_output><empty_output><jupyter_text>Importing income data One of the issues that is becoming increasingly clear to me is that it's unclear how to replicate the Gelman, et al.'s adjustments to their income data to account for a state's share of black residents. There was little, if any, explanation offered in the original research. It's very much possible, as a result, that we will have to default to using a non-adjusted income dataset for this study.Another factor that has become increasingly important since Gelman, et al. published their work is a state's share of Hispanic/Latino voters. This voting block has become increasingly influential since the 2004 presidential election, especially in the wake of the failure of a comprehensive immigration-reform bill that Congress was considering during President <NAME>'s second term. As a result, it would be great to adjust the income data for states' Hispanic/Latino population shares.However, for the time being we'll keep it simple. We can always perform these adjustments at a later time.Let's go ahead and import some per-capita income data that was obtained from the Bureau of Economic Analysis, the same agency that provided the raw income data that Gelman, et al. used for their work.<jupyter_code>percapinc = pd.read_csv(cap_path+'SA1-pres-years-percapinc.csv', skiprows=4, nrows=50)
percapinc['GeoFips'] = percapinc['GeoFips'].apply(lambda x: int(x/1000))
percapinc.drop('1948', axis=1, inplace=True)
percapinc.rename({'GeoFips':'statefips','GeoName':'state'}, axis=1, inplace=True)
percapinc['state'] = percapinc['state'].apply(lambda x: x.strip('*'))
percapinc_columns = list(percapinc.columns)
years = [int(column) for column in percapinc_columns[2:]]
percapinc_columns = percapinc_columns[0:2]
percapinc_columns.extend(years)
percapinc.columns = percapinc_columns
ep = EasyPeasy()
percapinc_infadj = percapinc.copy()
for year in years:
    if year < 1960:
        pass
    else:
        percapinc[str(year)+'_infadj'] = percapinc[year].apply(lambda x: ep.inflation_calculator(x, 'US', year, 1996))
        percapinc_infadj[year] = percapinc_infadj[year].apply(lambda x: ep.inflation_calculator(x, 'US', year, 1996))
percapinc_infadj.drop(labels=[1952,1956],axis=1,inplace=True)
percapinc_infadj_s
for i in percapinc_infadj.columns[2:]:
    

# st_nonadjAA = st
# st1960_nonadjAA = st[st['year'] == 1960]
# st1964_nonadjAA = st[st['year'] == 1964]
# st1968_nonadjAA = st[st['year'] == 1968]
# st1972_nonadjAA = st[st['year'] == 1972]
# st1976_nonadjAA = st[st['year'] == 1976]
# st1980_nonadjAA = st[st['year'] == 1980]
# st1984_nonadjAA = st[st['year'] == 1984]
# st1988_nonadjAA = st[st['year'] == 1988]
# st1992_nonadjAA = st[st['year'] == 1992]
# st1996_nonadjAA = st[st['year'] == 1996]
# st2000_nonadjAA = st[st['year'] == 2000]
# st2004_nonadjAA = st[st['year'] == 2004]
# st1960_nonadjAA.head()
# pd.DataFrame(np.array([st1960_nonadjAA['state'].values, stats.zscore(st1960_nonadjAA['st.income'].values)/st1960_nonadjAA['z.st.inc'].values])).transpose()
percapinc_USA = pd.read_csv(cap_path+'SA1-pres-years-percapinc-natl.csv', skiprows=4, nrows=1)
percapinc_USA = percapinc_USA.transpose()
percapinc_USA = percapinc_USA.drop(labels=['GeoName','GeoFips'], axis=0)
percapinc_USA.columns = ['percapinc']
percapinc_USA.index = percapinc_USA.index.astype('int64')
# percapinc_USA.loc[1948]['percapinc_infadj1996']
percapinc_USA_infadj = []
for i in range(len(percapinc_USA.index)):
    percapinc_USA_infadj.append(ep.inflation_calculator(percapinc_USA['percapinc'].values[i],'US',percapinc_USA.index[i],1996))
percapinc_USA['percapinc_infadj1996'] = percapinc_USA_infadj
percapinc_USA
percapinc_USA.loc[1960]['percapinc_infadj1996']
# stats.zscore(income[income['LineCode'] == 3]['1960'][1:52])
income = pd.read_csv(cap_path+'SA1-pres-years-all-natl.csv', skiprows=4, nrows=180)
print(income.columns)
print(income.dtypes)
income.head()
income.replace('Alaska*','Alaska', axis=0, inplace=True)
income['GeoFips'] = [str(x).rstrip('000').zfill(2) for x in income['GeoFips'].values]
income_1 = pd.DataFrame(data=income[income['LineCode'] == 1]).reset_index()
income_2 = pd.DataFrame(data=income[income['LineCode'] == 2]).reset_index()
income_3 = pd.DataFrame(data=income[income['LineCode'] == 3]).reset_index()
income_1.drop(labels=['1948','1952','1956'], axis=1, inplace=True)
income_2.drop(labels=['1948','1952','1956'], axis=1, inplace=True)
income_3.drop(labels=['1948','1952','1956'], axis=1, inplace=True)
income_1.head()
income_1_melt = pd.melt(income_1,id_vars=['GeoFips','GeoName'],value_vars=income_1.columns[5:], var_name='Year', value_name='st.income')
income_2_melt = pd.melt(income_2,id_vars=['GeoFips','GeoName'],value_vars=income_2.columns[5:], var_name='Year', value_name='st.pop')
income_3_melt = pd.melt(income_3,id_vars=['GeoFips','GeoName'],value_vars=income_3.columns[5:], var_name='Year', value_name='incpop')
income_melt = income_1_melt.copy()
income_melt['st.pop'] = income_2_melt['st.pop'].values
income_melt['incpop'] = income_3_melt['incpop'].values
income_melt['GeoName'].replace('Hawaii*','Hawaii',inplace=True)
# sns.set_style("white")
# # cmap = sns.diverging_palette(250, 15, sep=1, s=90, l=60, n=20, center="dark", as_cmap=True)
# fig, ax = plt.subplots(figsize=(20,16))
# ax.set_ylim([-0.25,0.25])
# points = plt.scatter(lr_df["Year"], lr_df["Coef"],
#                      c=lr_df['Coef'], s=800, cmap="coolwarm")
# sns.regplot('Year','Coef',data=lr_df, ax=ax, scatter=False, color='gray', line_kws={'lw':8})
# ax.set_title('Figure 1 from Gelman, et al.',
#              fontdict={'fontsize':48,'fontname':'Arial', 'fontweight':'bold'})
# ax.set_xlabel('Year', fontdict={'fontsize':36, 'fontname':'Franklin Gothic Medium'})
# ax.set_ylabel('Slope of GOP-Share-vs.-Income Regression Line',
#               fontdict={'fontsize':36, 'fontname':'Franklin Gothic Medium'})
# ax.tick_params(axis='both', which='major', labelsize=24)
# ax.grid()
# lr_df.to_csv('./figure-1-lr_df.csv', index=False)
# results_0816.to_csv('./results-2008-2016.csv', index=False) 
# percapinc_infadj.to_csv('./SA1-percapinc_infadj-1960-2016.csv', index=False)
# income_melt.to_csv('./SA1-income-melt-1960-2016.csv', index=False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1401518659135105, 'fraction_numerical': 0.04565237521992777, 'mean_word_length': 5.764484810523019, 'pattern_counts': {'":': 0, '<': 27, '<?xml version=': 0, '>': 46, 'https://': 3, 'lorem ipsum': 0, 'www.': 1, 'xml': 3}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '553976', 'license': 'MIT', 'n_tokens_mistral': 7757, 'n_tokens_neox': 6839, 'n_words': 1993, 'path': '03-data-cleaning-eda.ipynb', 'repo_name': 'kolliparap/DSI-capstone-project', 'size': '21606'}