voting_national.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "from io import StringIO\n",
    "\n",
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# County Data (Note: started using MIT data below instead)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# state_abbrev = {\n",
    "#     'Alabama': 'AL','Alaska': 'AK','Arizona': 'AZ','Arkansas': 'AR','California': 'CA',\n",
    "#     'Colorado': 'CO','Connecticut': 'CT','Delaware': 'DE','Florida': 'FL','Georgia': 'GA',\n",
    "#     'Hawaii': 'HI','Idaho': 'ID','Illinois': 'IL','Indiana': 'IN','Iowa': 'IA','Kansas': 'KS',\n",
    "#     'Kentucky': 'KY','Louisiana': 'LA','Maine': 'ME','Maryland': 'MD','Massachusetts': 'MA',\n",
    "#     'Michigan': 'MI','Minnesota': 'MN','Mississippi': 'MS','Missouri': 'MO','Montana': 'MT',\n",
    "#     'Nebraska': 'NE','Nevada': 'NV','New Hampshire': 'NH','New Jersey': 'NJ','New Mexico': 'NM',\n",
    "#     'New York': 'NY','North Carolina': 'NC','North Dakota': 'ND','Ohio': 'OH','Oklahoma': 'OK',\n",
    "#     'Oregon': 'OR','Pennsylvania': 'PA','Rhode Island': 'RI','South Carolina': 'SC',\n",
    "#     'South Dakota': 'SD','Tennessee': 'TN','Texas': 'TX','Utah': 'UT','Vermont': 'VT',\n",
    "#     'Virginia': 'VA','Washington': 'WA','West Virginia': 'WV','Wisconsin': 'WI','Wyoming': 'WY',\n",
    "#     'District of Columbia': 'DC'\n",
    "# }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # Load the 2012, 2016 Voting and Turnout data\n",
    "# # Source: https://github.com/kyaroch/2012_and_2016_presidential_election_results_by_county\n",
    "\n",
    "# voting1_df = pd.read_csv('./data/2012_and_2016_presidential_election_results_by_county.csv',\n",
    "#                          converters={'fips_code': lambda x: str(x)})\n",
    "\n",
    "# columns = ['state_postal_abbrev', 'fips_code', 'county_name', '2012_total_votes', \n",
    "#            'obama_votes', 'romney_votes', '2016_total_votes', 'clinton_votes', \n",
    "#            'trump_votes', '2012_adult_pop', '2016_extrapolated_adult_pop', \n",
    "#            '2012_turnout', '2016_turnout']\n",
    "\n",
    "# renames = ['state',  'fips_code', 'county_name',  'total_2012', 'dem_2012', \n",
    "#            'rep_2012','total_2016', 'dem_2016', 'rep_2016', 'vap_2012','vap_2016',\n",
    "#            'turnout_2012','turnout_2016']\n",
    "\n",
    "# voting1_df = voting1_df[columns]\n",
    "# voting1_df.rename(columns = dict(zip(columns, renames)), inplace=True)\n",
    "\n",
    "# #Fix fips_code, add leading zero\n",
    "# voting1_df['fips_code'] = voting1_df['fips_code'].apply(lambda x: '0' + x if len(x) < 5 else x)\n",
    "\n",
    "# voting1_df['county_name'] = voting1_df['county_name'] + \" County\"\n",
    "\n",
    "# # voting1_df[voting1_df['2016_results_official'] == 0].count()\n",
    "# # 385 unofficial results\n",
    "\n",
    "# #Close enough to above numbers from Leip's election atlas. \n",
    "# print(voting1_df.sum(numeric_only=True).apply(lambda x: '%.2f' % x))\n",
    "\n",
    "# #Turn year into column\n",
    "# year_dfs = []\n",
    "\n",
    "# for year in [2012, 2016]:\n",
    "#     cols = 'state fips_code county_name total_{0} dem_{0} rep_{0} vap_{0} turnout_{0}'.format(year)\n",
    "#     cols = cols.split(' ')\n",
    "#     temp_df = voting1_df[cols].copy()\n",
    "#     temp_df['year'] = year\n",
    "#     renames = dict(zip(cols, ['state', 'fips_code', 'county_name',\n",
    "#                               'county_num', 'dem_num', 'rep_num', 'vap', 'turnout']))\n",
    "#     temp_df.rename(columns=renames, inplace=True)\n",
    "#     year_dfs.append(temp_df)\n",
    "\n",
    "# voting1_df = pd.concat(year_dfs)\n",
    "\n",
    "# voting1_df['dem_margin'] = (voting1_df['dem_num'] - voting1_df['rep_num'])/voting1_df['county_num']\n",
    "\n",
    "# #voting1_df.sort_values(by='dem_margin')\n",
    "# voting1_df.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # Load Voting Age Population 2005 to 2009 average, can't find yearly VAP data\n",
    "# # Source: https://www.census.gov/rdo/data/voting_age_population_by_citizenship_and_race_cvap.html\n",
    "\n",
    "# # 'latin-1' deals with e-acute: https://stackoverflow.com/questions/5552555\n",
    "# population2009_df = pd.read_csv('./data/CVAP_CSV_Format_2005-2009/County.csv', encoding='latin-1')\n",
    "\n",
    "# population2009_df['fips_code'] = population2009_df['GEOID'].apply(lambda x: x[7:])\n",
    "# population2009_df = population2009_df[(population2009_df['LNTITLE'] == 'Total')]\n",
    "# population2009_df.rename(columns={'CVAP_EST':'vap'}, inplace=True)\n",
    "# population2009_df = population2009_df[['fips_code', 'vap']]  \n",
    "\n",
    "# population2009_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # Load 2004-2012 Voting data \n",
    "# # Source: https://github.com/helloworlddata/us-presidential-election-county-results\n",
    "# columns = ['year', 'state', 'county', 'fips', 'vote_rep', 'vote_dem', 'vote_total']\n",
    "# renames = ['year', 'state', 'county_name', 'fips_code', 'rep_num', 'dem_num', 'county_num']\n",
    "\n",
    "\n",
    "# voting2_df = pd.read_csv('./data/us-presidential-election-county-results-2004-through-2012.csv')\n",
    "\n",
    "# voting2_df = voting2_df[columns]\n",
    "# voting2_df.rename(columns=dict(zip(columns, renames)), inplace=True)\n",
    "\n",
    "# # Fix Laclede County Results, otherwise state switches to D\n",
    "# # https://en.wikipedia.org/wiki/United_States_presidential_election_in_Missouri,_2008\n",
    "# # Laclede\t32.4%\t5,218\t67.6%\t10,875\t16,093\n",
    "# voting2_df.loc[(voting2_df['state'] == 'MO') & \n",
    "#            (voting2_df['year'] == 2008) & \n",
    "#            (voting2_df['county_name'] == 'Laclede County'), 'county_num'] = 16093\n",
    "\n",
    "# voting2_df['dem_margin'] = (voting2_df['dem_num'] - voting2_df['rep_num'])/voting2_df['county_num'] \n",
    "\n",
    "# voting2_df = voting2_df[(voting2_df['year'].isin([2004, 2008])) &\n",
    "#                         (voting2_df['state'] != 'AK')]\n",
    "\n",
    "# voting2_df = pd.merge(voting2_df, population2009_df, on='fips_code')\n",
    "\n",
    "# voting2_df['turnout'] = voting2_df['county_num'] / voting2_df['vap']\n",
    "\n",
    "# #Close enough to above numbers from Leip's election atlas. \n",
    "# #print(voting2_df[.sum(numeric_only=True).apply(lambda x: '%.2f' % x))\n",
    "\n",
    "\n",
    "# voting2_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # Source: https://en.wikipedia.org/wiki/Electoral_College_(United_States)#Chronological_table\n",
    "# electoral_df = pd.read_csv('./data/state_electoral_votes.csv') #sep=\"\\t\"\n",
    "\n",
    "# # Electoral points in 2004 = 2008, 2012=2016=2020\n",
    "# electoral_df['votes_2016'] = electoral_df['votes_2012']\n",
    "# electoral_df['votes_2004'] = electoral_df['votes_2008']\n",
    "# electoral_df['state'] = electoral_df['state'].apply(lambda x: state_abbrev[x])\n",
    "\n",
    "# years = [2004, 2008, 2012, 2016]\n",
    "# electoral_dfs = []\n",
    "\n",
    "# #Stack by year\n",
    "# for year in years:\n",
    "#     cols = 'state votes_{0}'.format(year)\n",
    "#     cols = cols.split(' ')\n",
    "#     temp_df = electoral_df[cols].copy()\n",
    "#     temp_df['year'] = year\n",
    "#     temp_df.rename(columns={'votes_{}'.format(year): 'state_electoral_votes'}, inplace=True)\n",
    "#     electoral_dfs.append(temp_df)\n",
    "\n",
    "# electoral_df = pd.concat(electoral_dfs)\n",
    "# electoral_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # Combine all dataframes together\n",
    "# turnout_df = pd.concat([voting1_df, voting2_df, alaska_df])\n",
    "\n",
    "# #Add in electoral college points for each year\n",
    "# turnout_df = pd.merge(turnout_df, electoral_df, on=('state', 'year'))\n",
    "\n",
    "# turnout_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# #Data output script for interactive visualization, filters out abnormal values  \n",
    "# out_df = turnout_df[(turnout_df['dem_margin'] <= 1.0) &\n",
    "#                     (turnout_df['dem_margin'] >= -1.0) &\n",
    "#                     (turnout_df['turnout'] >= 0.0) &\n",
    "#                     (turnout_df['turnout'] <= 1.0)]\n",
    "\n",
    "# # out_df = out_df[['county', 'state', 'county_num', 'turnout', 'num_rep', 'num_dem', 'num_state_dem',\n",
    "# #                  'num_state_rep','num_state', 'year', 'vap', 'fips_code', 'state_electoral_votes']]\n",
    "\n",
    "# out_df = out_df.round(decimals=4)\n",
    "\n",
    "# out_df.to_csv('./US_County_Level_Presidential_Results_04-16.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# State Electoral Votes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "state_abbrev = {\n",
    "    'Alabama': 'AL','Alaska': 'AK','Arizona': 'AZ','Arkansas': 'AR','California': 'CA',\n",
    "    'Colorado': 'CO','Connecticut': 'CT','Delaware': 'DE','Florida': 'FL','Georgia': 'GA',\n",
    "    'Hawaii': 'HI','Idaho': 'ID','Illinois': 'IL','Indiana': 'IN','Iowa': 'IA','Kansas': 'KS',\n",
    "    'Kentucky': 'KY','Louisiana': 'LA','Maine': 'ME','Maryland': 'MD','Massachusetts': 'MA',\n",
    "    'Michigan': 'MI','Minnesota': 'MN','Mississippi': 'MS','Missouri': 'MO','Montana': 'MT',\n",
    "    'Nebraska': 'NE','Nevada': 'NV','New Hampshire': 'NH','New Jersey': 'NJ','New Mexico': 'NM',\n",
    "    'New York': 'NY','North Carolina': 'NC','North Dakota': 'ND','Ohio': 'OH','Oklahoma': 'OK',\n",
    "    'Oregon': 'OR','Pennsylvania': 'PA','Rhode Island': 'RI','South Carolina': 'SC',\n",
    "    'South Dakota': 'SD','Tennessee': 'TN','Texas': 'TX','Utah': 'UT','Vermont': 'VT',\n",
    "    'Virginia': 'VA','Washington': 'WA','West Virginia': 'WV','Wisconsin': 'WI','Wyoming': 'WY',\n",
    "    'District of Columbia': 'DC'\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>state</th>\n",
       "      <th>state_abbr</th>\n",
       "      <th>fips</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Washington</td>\n",
       "      <td>WA</td>\n",
       "      <td>53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Delaware</td>\n",
       "      <td>DE</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>District of Columbia</td>\n",
       "      <td>DC</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Wisconsin</td>\n",
       "      <td>WI</td>\n",
       "      <td>55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>West Virginia</td>\n",
       "      <td>WV</td>\n",
       "      <td>54</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  state state_abbr fips\n",
       "0            Washington         WA   53\n",
       "1              Delaware         DE   10\n",
       "2  District of Columbia         DC   11\n",
       "3             Wisconsin         WI   55\n",
       "4         West Virginia         WV   54"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "state_converter = pd.read_csv('./data/state_converter.csv', dtype='object')\n",
    "state_converter.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>state</th>\n",
       "      <th>state_electoral_votes</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AL</td>\n",
       "      <td>9</td>\n",
       "      <td>2004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AK</td>\n",
       "      <td>3</td>\n",
       "      <td>2004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AZ</td>\n",
       "      <td>10</td>\n",
       "      <td>2004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AR</td>\n",
       "      <td>6</td>\n",
       "      <td>2004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>CA</td>\n",
       "      <td>55</td>\n",
       "      <td>2004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>VA</td>\n",
       "      <td>13</td>\n",
       "      <td>2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>WA</td>\n",
       "      <td>12</td>\n",
       "      <td>2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>WV</td>\n",
       "      <td>5</td>\n",
       "      <td>2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>WI</td>\n",
       "      <td>10</td>\n",
       "      <td>2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>WY</td>\n",
       "      <td>3</td>\n",
       "      <td>2020</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>255 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   state  state_electoral_votes  year\n",
       "0     AL                      9  2004\n",
       "1     AK                      3  2004\n",
       "2     AZ                     10  2004\n",
       "3     AR                      6  2004\n",
       "4     CA                     55  2004\n",
       "..   ...                    ...   ...\n",
       "46    VA                     13  2020\n",
       "47    WA                     12  2020\n",
       "48    WV                      5  2020\n",
       "49    WI                     10  2020\n",
       "50    WY                      3  2020\n",
       "\n",
       "[255 rows x 3 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Source: https://en.wikipedia.org/wiki/Electoral_College_(United_States)#Chronological_table\n",
    "electoral_df = pd.read_csv('./data/state_electoral_votes.csv') #sep=\"\\t\"\n",
    "\n",
    "# Electoral points in 2004 = 2008, 2012=2016=2020\n",
    "electoral_df['votes_2020'] = electoral_df['votes_2012']\n",
    "electoral_df['votes_2016'] = electoral_df['votes_2012']\n",
    "electoral_df['votes_2004'] = electoral_df['votes_2008']\n",
    "electoral_df['state'] = electoral_df['state'].apply(lambda x: state_abbrev[x])\n",
    "\n",
    "years = [2004, 2008, 2012, 2016, 2020]\n",
    "electoral_dfs = []\n",
    "\n",
    "#Stack by year\n",
    "for year in years:\n",
    "    cols = 'state votes_{0}'.format(year)\n",
    "    cols = cols.split(' ')\n",
    "    temp_df = electoral_df[cols].copy()\n",
    "    temp_df['year'] = year\n",
    "    temp_df.rename(columns={'votes_{}'.format(year): 'state_electoral_votes'}, inplace=True)\n",
    "    electoral_dfs.append(temp_df)\n",
    "\n",
    "electoral_df = pd.concat(electoral_dfs)\n",
    "electoral_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# County Population Data\n",
    "\n",
    "Citizen voting age population [data](https://www.census.gov/programs-surveys/decennial-census/about/voting-rights/cvap.2020.html) by county, for 2005-2009, 2010-2014, 2014-2018"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>geoid</th>\n",
       "      <th>geoname</th>\n",
       "      <th>year</th>\n",
       "      <th>vap</th>\n",
       "      <th>fips_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>05000US45001</td>\n",
       "      <td>Abbeville County, South Carolina</td>\n",
       "      <td>2004</td>\n",
       "      <td>19270.0</td>\n",
       "      <td>45001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>05000US22001</td>\n",
       "      <td>Acadia Parish, Louisiana</td>\n",
       "      <td>2004</td>\n",
       "      <td>42939.0</td>\n",
       "      <td>22001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>05000US51001</td>\n",
       "      <td>Accomack County, Virginia</td>\n",
       "      <td>2004</td>\n",
       "      <td>26918.0</td>\n",
       "      <td>51001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>05000US16001</td>\n",
       "      <td>Ada County, Idaho</td>\n",
       "      <td>2004</td>\n",
       "      <td>237421.0</td>\n",
       "      <td>16001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>05000US19001</td>\n",
       "      <td>Adair County, Iowa</td>\n",
       "      <td>2004</td>\n",
       "      <td>6145.0</td>\n",
       "      <td>19001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58009</th>\n",
       "      <td>05000US04027</td>\n",
       "      <td>Yuma County, Arizona</td>\n",
       "      <td>2021</td>\n",
       "      <td>136756.0</td>\n",
       "      <td>04027</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58010</th>\n",
       "      <td>05000US08125</td>\n",
       "      <td>Yuma County, Colorado</td>\n",
       "      <td>2021</td>\n",
       "      <td>6884.0</td>\n",
       "      <td>08125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58011</th>\n",
       "      <td>05000US48505</td>\n",
       "      <td>Zapata County, Texas</td>\n",
       "      <td>2021</td>\n",
       "      <td>6612.0</td>\n",
       "      <td>48505</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58012</th>\n",
       "      <td>05000US48507</td>\n",
       "      <td>Zavala County, Texas</td>\n",
       "      <td>2021</td>\n",
       "      <td>8773.0</td>\n",
       "      <td>48507</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58013</th>\n",
       "      <td>05000US46137</td>\n",
       "      <td>Ziebach County, South Dakota</td>\n",
       "      <td>2021</td>\n",
       "      <td>1997.0</td>\n",
       "      <td>46137</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>58014 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              geoid                           geoname  year       vap  \\\n",
       "0      05000US45001  Abbeville County, South Carolina  2004   19270.0   \n",
       "1      05000US22001          Acadia Parish, Louisiana  2004   42939.0   \n",
       "2      05000US51001         Accomack County, Virginia  2004   26918.0   \n",
       "3      05000US16001                 Ada County, Idaho  2004  237421.0   \n",
       "4      05000US19001                Adair County, Iowa  2004    6145.0   \n",
       "...             ...                               ...   ...       ...   \n",
       "58009  05000US04027              Yuma County, Arizona  2021  136756.0   \n",
       "58010  05000US08125             Yuma County, Colorado  2021    6884.0   \n",
       "58011  05000US48505              Zapata County, Texas  2021    6612.0   \n",
       "58012  05000US48507              Zavala County, Texas  2021    8773.0   \n",
       "58013  05000US46137      Ziebach County, South Dakota  2021    1997.0   \n",
       "\n",
       "      fips_code  \n",
       "0         45001  \n",
       "1         22001  \n",
       "2         51001  \n",
       "3         16001  \n",
       "4         19001  \n",
       "...         ...  \n",
       "58009     04027  \n",
       "58010     08125  \n",
       "58011     48505  \n",
       "58012     48507  \n",
       "58013     46137  \n",
       "\n",
       "[58014 rows x 5 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# # Load Voting Age Population 2005 to 2009 average, can't find yearly VAP data\n",
    "# # Source: https://www.census.gov/rdo/data/voting_age_population_by_citizenship_and_race_cvap.html\n",
    "# # 'latin-1' deals with e-acute: https://stackoverflow.com/questions/5552555\n",
    "# population2009_df = pd.read_csv('./data/counties/CVAP_CSV_Format_2005-2009/County.csv', encoding='latin-1')\n",
    "# population2009_df['fips_code'] = population2009_df['GEOID'].apply(lambda x: x[7:])\n",
    "# population2009_df = population2009_df[(population2009_df['LNTITLE'] == 'Total')]\n",
    "# population2009_df.rename(columns={'CVAP_EST':'vap'}, inplace=True)\n",
    "# population2009_df = population2009_df[['fips_code', 'vap']]\n",
    "# population2009_df['year_category'] = '2004-2008'\n",
    "\n",
    "# population2014_df = pd.read_csv('./data/counties/CVAP_2010-2014_ACS_csv_files/County.csv', encoding='latin-1')\n",
    "# population2014_df['fips_code'] = population2014_df['GEOID'].apply(lambda x: x[7:])\n",
    "# population2014_df = population2014_df[(population2014_df['LNTITLE'] == 'Total')]\n",
    "# population2014_df.rename(columns={'CVAP_EST':'vap'}, inplace=True)\n",
    "# population2014_df = population2014_df[['fips_code', 'vap']]\n",
    "# population2014_df['year_category'] = '2012'\n",
    "\n",
    "# population2018_df = pd.read_csv('./data/counties/CVAP_2014-2018_ACS_csv_files/County.csv', encoding='latin-1')\n",
    "# population2018_df['fips_code'] = population2018_df['geoid'].apply(lambda x: x[7:])\n",
    "# population2018_df = population2018_df[(population2018_df['lntitle'] == 'Total')]\n",
    "# population2018_df.rename(columns={'cvap_est':'vap'}, inplace=True)\n",
    "# population2018_df = population2018_df[['fips_code', 'vap']]\n",
    "# population2018_df['year_category'] = '2016-2020'\n",
    "\n",
    "# countypop_df = pd.concat([population2009_df, population2014_df, population2018_df])\n",
    "# countypop_df\n",
    "\n",
    "# Updated this code in a separate project, I now use a regression to estimate annual\n",
    "# CVAP data by county\n",
    "countypop_df = pd.read_csv('./data/counties/county-cvap-2004-2021.csv')\n",
    "countypop_df['fips_code'] = countypop_df['geoid'].str[-5:]\n",
    "countypop_df.rename(columns={'cvap_est':'vap'}, inplace=True)\n",
    "countypop_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>geoid</th>\n",
       "      <th>geoname</th>\n",
       "      <th>year</th>\n",
       "      <th>cvap_est</th>\n",
       "      <th>fips_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>814</th>\n",
       "      <td>05000US11001</td>\n",
       "      <td>District of Columbia, District of Columbia</td>\n",
       "      <td>2004</td>\n",
       "      <td>400115.0</td>\n",
       "      <td>11001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4037</th>\n",
       "      <td>05000US11001</td>\n",
       "      <td>District of Columbia, District of Columbia</td>\n",
       "      <td>2005</td>\n",
       "      <td>409407.0</td>\n",
       "      <td>11001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7260</th>\n",
       "      <td>05000US11001</td>\n",
       "      <td>District of Columbia, District of Columbia</td>\n",
       "      <td>2006</td>\n",
       "      <td>418699.0</td>\n",
       "      <td>11001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10483</th>\n",
       "      <td>05000US11001</td>\n",
       "      <td>District of Columbia, District of Columbia</td>\n",
       "      <td>2007</td>\n",
       "      <td>430110.0</td>\n",
       "      <td>11001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13706</th>\n",
       "      <td>05000US11001</td>\n",
       "      <td>District of Columbia, District of Columbia</td>\n",
       "      <td>2008</td>\n",
       "      <td>435875.0</td>\n",
       "      <td>11001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16929</th>\n",
       "      <td>05000US11001</td>\n",
       "      <td>District of Columbia, District of Columbia</td>\n",
       "      <td>2009</td>\n",
       "      <td>444270.0</td>\n",
       "      <td>11001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20152</th>\n",
       "      <td>05000US11001</td>\n",
       "      <td>District of Columbia, District of Columbia</td>\n",
       "      <td>2010</td>\n",
       "      <td>454205.0</td>\n",
       "      <td>11001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23375</th>\n",
       "      <td>05000US11001</td>\n",
       "      <td>District of Columbia, District of Columbia</td>\n",
       "      <td>2011</td>\n",
       "      <td>466325.0</td>\n",
       "      <td>11001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26598</th>\n",
       "      <td>05000US11001</td>\n",
       "      <td>District of Columbia, District of Columbia</td>\n",
       "      <td>2012</td>\n",
       "      <td>475400.0</td>\n",
       "      <td>11001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29821</th>\n",
       "      <td>05000US11001</td>\n",
       "      <td>District of Columbia, District of Columbia</td>\n",
       "      <td>2013</td>\n",
       "      <td>485115.0</td>\n",
       "      <td>11001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33044</th>\n",
       "      <td>05000US11001</td>\n",
       "      <td>District of Columbia, District of Columbia</td>\n",
       "      <td>2014</td>\n",
       "      <td>494090.0</td>\n",
       "      <td>11001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36267</th>\n",
       "      <td>05000US11001</td>\n",
       "      <td>District of Columbia, District of Columbia</td>\n",
       "      <td>2015</td>\n",
       "      <td>503110.0</td>\n",
       "      <td>11001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39490</th>\n",
       "      <td>05000US11001</td>\n",
       "      <td>District of Columbia, District of Columbia</td>\n",
       "      <td>2016</td>\n",
       "      <td>511190.0</td>\n",
       "      <td>11001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42713</th>\n",
       "      <td>05000US11001</td>\n",
       "      <td>District of Columbia, District of Columbia</td>\n",
       "      <td>2017</td>\n",
       "      <td>519265.0</td>\n",
       "      <td>11001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45936</th>\n",
       "      <td>05000US11001</td>\n",
       "      <td>District of Columbia, District of Columbia</td>\n",
       "      <td>2018</td>\n",
       "      <td>530202.0</td>\n",
       "      <td>11001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49159</th>\n",
       "      <td>05000US11001</td>\n",
       "      <td>District of Columbia, District of Columbia</td>\n",
       "      <td>2019</td>\n",
       "      <td>539494.0</td>\n",
       "      <td>11001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52382</th>\n",
       "      <td>05000US11001</td>\n",
       "      <td>District of Columbia, District of Columbia</td>\n",
       "      <td>2020</td>\n",
       "      <td>548786.0</td>\n",
       "      <td>11001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55605</th>\n",
       "      <td>05000US11001</td>\n",
       "      <td>District of Columbia, District of Columbia</td>\n",
       "      <td>2021</td>\n",
       "      <td>558078.0</td>\n",
       "      <td>11001</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              geoid                                     geoname  year  \\\n",
       "814    05000US11001  District of Columbia, District of Columbia  2004   \n",
       "4037   05000US11001  District of Columbia, District of Columbia  2005   \n",
       "7260   05000US11001  District of Columbia, District of Columbia  2006   \n",
       "10483  05000US11001  District of Columbia, District of Columbia  2007   \n",
       "13706  05000US11001  District of Columbia, District of Columbia  2008   \n",
       "16929  05000US11001  District of Columbia, District of Columbia  2009   \n",
       "20152  05000US11001  District of Columbia, District of Columbia  2010   \n",
       "23375  05000US11001  District of Columbia, District of Columbia  2011   \n",
       "26598  05000US11001  District of Columbia, District of Columbia  2012   \n",
       "29821  05000US11001  District of Columbia, District of Columbia  2013   \n",
       "33044  05000US11001  District of Columbia, District of Columbia  2014   \n",
       "36267  05000US11001  District of Columbia, District of Columbia  2015   \n",
       "39490  05000US11001  District of Columbia, District of Columbia  2016   \n",
       "42713  05000US11001  District of Columbia, District of Columbia  2017   \n",
       "45936  05000US11001  District of Columbia, District of Columbia  2018   \n",
       "49159  05000US11001  District of Columbia, District of Columbia  2019   \n",
       "52382  05000US11001  District of Columbia, District of Columbia  2020   \n",
       "55605  05000US11001  District of Columbia, District of Columbia  2021   \n",
       "\n",
       "       cvap_est fips_code  \n",
       "814    400115.0     11001  \n",
       "4037   409407.0     11001  \n",
       "7260   418699.0     11001  \n",
       "10483  430110.0     11001  \n",
       "13706  435875.0     11001  \n",
       "16929  444270.0     11001  \n",
       "20152  454205.0     11001  \n",
       "23375  466325.0     11001  \n",
       "26598  475400.0     11001  \n",
       "29821  485115.0     11001  \n",
       "33044  494090.0     11001  \n",
       "36267  503110.0     11001  \n",
       "39490  511190.0     11001  \n",
       "42713  519265.0     11001  \n",
       "45936  530202.0     11001  \n",
       "49159  539494.0     11001  \n",
       "52382  548786.0     11001  \n",
       "55605  558078.0     11001  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Only 1 county for DC?\n",
    "countypop_df[countypop_df['fips_code'].str.contains('^11')]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# MIT County Margin Data\n",
    "\n",
    "MIT Election labs have since [started](https://dataverse.harvard.edu/dataset.xhtml?persistentId=doi:10.7910/DVN/VOQCHQ) compiling this data, so use that instead. \n",
    "\n",
    "\n",
    "* TODO: Wait for more official county data? Because this looks weird, very scattered for turnout\n",
    "* TODO: Interpolate/project CVAP, so turnout is more accurate?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>party</th>\n",
       "      <th>year</th>\n",
       "      <th>state</th>\n",
       "      <th>county_name</th>\n",
       "      <th>fips_code</th>\n",
       "      <th>rep_num</th>\n",
       "      <th>dem_num</th>\n",
       "      <th>county_num</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2004</td>\n",
       "      <td>AL</td>\n",
       "      <td>Autauga</td>\n",
       "      <td>01001</td>\n",
       "      <td>15196.0</td>\n",
       "      <td>4758.0</td>\n",
       "      <td>20081</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2004</td>\n",
       "      <td>AL</td>\n",
       "      <td>Baldwin</td>\n",
       "      <td>01003</td>\n",
       "      <td>52971.0</td>\n",
       "      <td>15599.0</td>\n",
       "      <td>69320</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2004</td>\n",
       "      <td>AL</td>\n",
       "      <td>Barbour</td>\n",
       "      <td>01005</td>\n",
       "      <td>5899.0</td>\n",
       "      <td>4832.0</td>\n",
       "      <td>10777</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2004</td>\n",
       "      <td>AL</td>\n",
       "      <td>Bibb</td>\n",
       "      <td>01007</td>\n",
       "      <td>5472.0</td>\n",
       "      <td>2089.0</td>\n",
       "      <td>7600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2004</td>\n",
       "      <td>AL</td>\n",
       "      <td>Blount</td>\n",
       "      <td>01009</td>\n",
       "      <td>17386.0</td>\n",
       "      <td>3938.0</td>\n",
       "      <td>21504</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12610</th>\n",
       "      <td>2016</td>\n",
       "      <td>WY</td>\n",
       "      <td>Sweetwater</td>\n",
       "      <td>56037</td>\n",
       "      <td>12154.0</td>\n",
       "      <td>3231.0</td>\n",
       "      <td>17130</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12611</th>\n",
       "      <td>2016</td>\n",
       "      <td>WY</td>\n",
       "      <td>Teton</td>\n",
       "      <td>56039</td>\n",
       "      <td>3921.0</td>\n",
       "      <td>7314.0</td>\n",
       "      <td>12627</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12612</th>\n",
       "      <td>2016</td>\n",
       "      <td>WY</td>\n",
       "      <td>Uinta</td>\n",
       "      <td>56041</td>\n",
       "      <td>6154.0</td>\n",
       "      <td>1202.0</td>\n",
       "      <td>8470</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12613</th>\n",
       "      <td>2016</td>\n",
       "      <td>WY</td>\n",
       "      <td>Washakie</td>\n",
       "      <td>56043</td>\n",
       "      <td>2911.0</td>\n",
       "      <td>532.0</td>\n",
       "      <td>3814</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12614</th>\n",
       "      <td>2016</td>\n",
       "      <td>WY</td>\n",
       "      <td>Weston</td>\n",
       "      <td>56045</td>\n",
       "      <td>3033.0</td>\n",
       "      <td>299.0</td>\n",
       "      <td>3526</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>12615 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "party  year state county_name fips_code  rep_num  dem_num  county_num\n",
       "0      2004    AL     Autauga     01001  15196.0   4758.0       20081\n",
       "1      2004    AL     Baldwin     01003  52971.0  15599.0       69320\n",
       "2      2004    AL     Barbour     01005   5899.0   4832.0       10777\n",
       "3      2004    AL        Bibb     01007   5472.0   2089.0        7600\n",
       "4      2004    AL      Blount     01009  17386.0   3938.0       21504\n",
       "...     ...   ...         ...       ...      ...      ...         ...\n",
       "12610  2016    WY  Sweetwater     56037  12154.0   3231.0       17130\n",
       "12611  2016    WY       Teton     56039   3921.0   7314.0       12627\n",
       "12612  2016    WY       Uinta     56041   6154.0   1202.0        8470\n",
       "12613  2016    WY    Washakie     56043   2911.0    532.0        3814\n",
       "12614  2016    WY      Weston     56045   3033.0    299.0        3526\n",
       "\n",
       "[12615 rows x 7 columns]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "county = pd.read_csv(\n",
    "    './data/counties/mit_countydata/countypres_2000-2016.csv',\n",
    "    dtype={'year':'int', 'FIPS':'object'}\n",
    ")\n",
    "county['fips_code'] = county['FIPS'].str.zfill(5)\n",
    "\n",
    "county = county[\n",
    "    (county['year'] > 2000) &\n",
    "    (county['party'].isin(['democrat', 'republican']))\n",
    "]\n",
    "\n",
    "county = county.pivot_table(\n",
    "    index=['year', 'state', 'state_po', 'fips_code', 'county', 'totalvotes'],\n",
    "    columns='party',\n",
    "    values='candidatevotes'\n",
    ")\n",
    "\n",
    "county.reset_index(drop=False, inplace=True)\n",
    "del county['state']\n",
    "county.rename(columns={'state_po':'state',\n",
    "    'county':'county_name', 'democrat':'dem_num', \n",
    "    'republican':'rep_num', 'totalvotes':'county_num'},\n",
    "    inplace=True)\n",
    "cols = ['year', 'state', 'county_name', 'fips_code', 'rep_num', 'dem_num', 'county_num']\n",
    "county = county[cols]\n",
    "county"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>year</th>\n",
       "      <th>state</th>\n",
       "      <th>county_name</th>\n",
       "      <th>fips_code</th>\n",
       "      <th>rep_num</th>\n",
       "      <th>dem_num</th>\n",
       "      <th>county_num</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020</td>\n",
       "      <td>AL</td>\n",
       "      <td>Autauga</td>\n",
       "      <td>01001</td>\n",
       "      <td>19764</td>\n",
       "      <td>7450</td>\n",
       "      <td>27639</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020</td>\n",
       "      <td>AL</td>\n",
       "      <td>Baldwin</td>\n",
       "      <td>01003</td>\n",
       "      <td>83055</td>\n",
       "      <td>24344</td>\n",
       "      <td>108945</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020</td>\n",
       "      <td>AL</td>\n",
       "      <td>Barbour</td>\n",
       "      <td>01005</td>\n",
       "      <td>5605</td>\n",
       "      <td>4772</td>\n",
       "      <td>10457</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020</td>\n",
       "      <td>AL</td>\n",
       "      <td>Bibb</td>\n",
       "      <td>01007</td>\n",
       "      <td>7508</td>\n",
       "      <td>1982</td>\n",
       "      <td>9573</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020</td>\n",
       "      <td>AL</td>\n",
       "      <td>Blount</td>\n",
       "      <td>01009</td>\n",
       "      <td>24595</td>\n",
       "      <td>2627</td>\n",
       "      <td>27459</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2020</td>\n",
       "      <td>WY</td>\n",
       "      <td>Teton</td>\n",
       "      <td>56039</td>\n",
       "      <td>4341</td>\n",
       "      <td>9848</td>\n",
       "      <td>14677</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>2020</td>\n",
       "      <td>WY</td>\n",
       "      <td>Uinta</td>\n",
       "      <td>56041</td>\n",
       "      <td>7496</td>\n",
       "      <td>1591</td>\n",
       "      <td>9402</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>2020</td>\n",
       "      <td>WY</td>\n",
       "      <td>Washakie</td>\n",
       "      <td>56043</td>\n",
       "      <td>3245</td>\n",
       "      <td>651</td>\n",
       "      <td>4012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>2020</td>\n",
       "      <td>WY</td>\n",
       "      <td>Weston</td>\n",
       "      <td>56045</td>\n",
       "      <td>3107</td>\n",
       "      <td>360</td>\n",
       "      <td>3542</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020</td>\n",
       "      <td>DC</td>\n",
       "      <td>District of Columbia</td>\n",
       "      <td>11001</td>\n",
       "      <td>18172</td>\n",
       "      <td>307265</td>\n",
       "      <td>333682</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3112 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    year state           county_name fips_code  rep_num  dem_num  county_num\n",
       "0   2020    AL               Autauga     01001    19764     7450       27639\n",
       "1   2020    AL               Baldwin     01003    83055    24344      108945\n",
       "2   2020    AL               Barbour     01005     5605     4772       10457\n",
       "3   2020    AL                  Bibb     01007     7508     1982        9573\n",
       "4   2020    AL                Blount     01009    24595     2627       27459\n",
       "..   ...   ...                   ...       ...      ...      ...         ...\n",
       "19  2020    WY                 Teton     56039     4341     9848       14677\n",
       "20  2020    WY                 Uinta     56041     7496     1591        9402\n",
       "21  2020    WY              Washakie     56043     3245      651        4012\n",
       "22  2020    WY                Weston     56045     3107      360        3542\n",
       "0   2020    DC  District of Columbia     11001    18172   307265      333682\n",
       "\n",
       "[3112 rows x 7 columns]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get 2020 data by county, also courtesy of MIT:\n",
    "# Doesn't seem like this source is accurate\n",
    "# https://github.com/MEDSL/election_night2020/blob/main/results/leip_natl_data.csv\n",
    "# county_2020 = pd.read_csv(\n",
    "#     './data/counties/mit_countydata/leip_natl_data_2020.csv',\n",
    "#     dtype={'FIPS':'object'}\n",
    "# )\n",
    "# county_2020['fips_code'] = county_2020['FIPS'].str.zfill(5)\n",
    "# county_2020['state_fips'] = county_2020['fips_code'].apply(lambda x: x[0:2])\n",
    "# county_2020 = county_2020.merge(\n",
    "#     state_converter[['fips', 'state_abbr']],\n",
    "#     left_on='state_fips', right_on='fips'\n",
    "# )\n",
    "# county_2020.rename(columns={'state_abbr':'state',\n",
    "#     'Geographic Name':'county_name', 'Donald J. Trump':'rep_num', \n",
    "#     'Joseph R. Biden Jr.':'dem_num', 'Total Vote':'county_num'},\n",
    "#     inplace=True)\n",
    "# county_2020['year'] = 2020\n",
    "# cols = ['year', 'state', 'county_name', 'fips_code', 'rep_num', 'dem_num', 'county_num']\n",
    "# county_2020 = county_2020[cols]\n",
    "# county_2020\n",
    "\n",
    "# Either there was a huge turnout, VAP data is too old from 2018, \n",
    "# or MIT data is really off. Trying this other source:\n",
    "# https://github.com/tonmcg/US_County_Level_Election_Results_08-20/blob/master/2020_US_County_Level_Presidential_Results.csv\n",
    "# county_2020 = pd.read_csv(\n",
    "#     './data/counties/2020_US_County_Level_Presidential_Results.csv',\n",
    "#     dtype={'county_fips':'object'}\n",
    "# )\n",
    "# county_2020['state_fips'] = county_2020['county_fips'].apply(lambda x: x[0:2])\n",
    "# county_2020 = county_2020.merge(\n",
    "#     state_converter[['fips', 'state_abbr']],\n",
    "#     left_on='state_fips', right_on='fips'\n",
    "# )\n",
    "# county_2020.rename(columns={'state_abbr':'state',\n",
    "#     'votes_dem':'dem_num', 'votes_gop':'rep_num',\n",
    "#     'total_votes':'county_num', 'county_fips':'fips_code'},\n",
    "#     inplace=True)\n",
    "# county_2020['year'] = 2020\n",
    "# cols = ['year', 'state', 'county_name', 'fips_code', 'rep_num', 'dem_num', 'county_num']\n",
    "# county_2020 = county_2020[cols]\n",
    "# county_2020\n",
    "\n",
    "\n",
    "# Trying NYTimes data, to allow projecting forward to 100% reporting.\n",
    "# This does change things, but the end result is that the High Density areas become\n",
    "# substantially more conservative, which makes sense if the outstanding vote is still\n",
    "# mostly Democratic. So don't do this for now, just go with existing county data.\n",
    "#https://static01.nyt.com/elections-assets/2020/data/api/2020-11-03/national-map-page/national/president.json\n",
    "with open('./data/counties/20201016-nytimes-president.json', 'r') as f:\n",
    "    county_obj = json.loads(f.read())\n",
    "\n",
    "county_dfs = []\n",
    "for race in county_obj['data']['races']:\n",
    "    temp = pd.DataFrame(race['counties'])\n",
    "    temp['state'] = race['state_id']\n",
    "    county_dfs.append(temp)\n",
    "\n",
    "# Use reported numbers\n",
    "county_2020 = pd.concat(county_dfs)\n",
    "county_2020['dem_reported'] = county_2020['results'].apply(\n",
    "    lambda x: x.get('bidenj')\n",
    ")\n",
    "county_2020['rep_reported'] = county_2020['results'].apply(\n",
    "    lambda x: x.get('trumpd')\n",
    ")\n",
    "\n",
    "# Create simple projections, assuming the same fraction as\n",
    "# reported to each candidate.\n",
    "# county_2020['dem_num'] = (county_2020['dem_reported']/county_2020['votes'])*county_2020['tot_exp_vote']\n",
    "# county_2020['rep_num'] = (county_2020['rep_reported']/county_2020['votes'])*county_2020['tot_exp_vote']\n",
    "# Instead of projecting, just use current values\n",
    "county_2020['dem_num'] = county_2020['dem_reported']\n",
    "county_2020['rep_num'] = county_2020['rep_reported']\n",
    "county_2020['year'] = 2020\n",
    "cols = ['year', 'state', 'name', 'fips', 'rep_reported', 'dem_reported', 'votes'] #Fixed bug, votes, not tot_exp_votes \n",
    "renames = ['year', 'state', 'county_name', 'fips_code', 'rep_num', 'dem_num', 'county_num']\n",
    "county_2020 = county_2020[cols]\n",
    "county_2020.rename(columns=dict(zip(cols, renames)), inplace=True)\n",
    "# Remove AK\n",
    "county_2020 = county_2020[county_2020['state'] != 'AK']\n",
    "\n",
    "# Replace DC with single entity, to match census data\n",
    "dc_2020 = county_2020[county_2020['state'] == 'DC']\n",
    "dc_2020 = dc_2020.groupby(by=['year', 'state'])[['rep_num', 'dem_num', 'county_num']].sum()\n",
    "dc_2020.reset_index(drop=False, inplace=True)\n",
    "dc_2020[['county_name', 'fips_code']] = ['District of Columbia', '11001']\n",
    "\n",
    "county_2020 = county_2020[county_2020['state'] != 'DC']\n",
    "county_2020 = pd.concat([county_2020, dc_2020])\n",
    "county_2020"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "county_num    154395222\n",
       "dem_num        78639569\n",
       "rep_num        72982751\n",
       "dtype: int64"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "county_2020[['county_num', 'dem_num', 'rep_num']].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'AK'}"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Only AK removed\n",
    "set(electoral_df['state']) - set(county_2020['state'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>fips_code</th>\n",
       "      <th>county_num</th>\n",
       "      <th>dem_num</th>\n",
       "      <th>rep_num</th>\n",
       "      <th>year</th>\n",
       "      <th>state</th>\n",
       "      <th>county_name</th>\n",
       "      <th>vap</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>02</td>\n",
       "      <td>312598</td>\n",
       "      <td>111025</td>\n",
       "      <td>190889</td>\n",
       "      <td>2004</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>467404</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>02</td>\n",
       "      <td>326197</td>\n",
       "      <td>123594</td>\n",
       "      <td>193841</td>\n",
       "      <td>2008</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>483060</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>02</td>\n",
       "      <td>300495</td>\n",
       "      <td>122640</td>\n",
       "      <td>164676</td>\n",
       "      <td>2012</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>519015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>02</td>\n",
       "      <td>318608</td>\n",
       "      <td>116454</td>\n",
       "      <td>163387</td>\n",
       "      <td>2016</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>530385</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>02</td>\n",
       "      <td>349411</td>\n",
       "      <td>150155</td>\n",
       "      <td>185807</td>\n",
       "      <td>2020</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>557082</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  fips_code  county_num  dem_num  rep_num  year state county_name     vap\n",
       "0        02      312598   111025   190889  2004    AK      Alaska  467404\n",
       "1        02      326197   123594   193841  2008    AK      Alaska  483060\n",
       "2        02      300495   122640   164676  2012    AK      Alaska  519015\n",
       "3        02      318608   116454   163387  2016    AK      Alaska  530385\n",
       "4        02      349411   150155   185807  2020    AK      Alaska  557082"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Manually input Alaska statewide data because they don't use counties\n",
    "# http://uselectionatlas.org/RESULTS/compare.php?year=2016&fips=2&f=0&off=0&elect=0&type=state\n",
    "# https://www.census.gov/library/visualizations/2016/comm/citizen_voting_age_population/cb16-tps18_alaska.html\n",
    "columns = ['fips_code', 'county_num', 'dem_num', 'rep_num', \n",
    "    'year', 'state', 'county_name', 'vap']\n",
    "ak = [\n",
    "    ['02', '02', '02', '02', '02'],\n",
    "    [312598, 326197, 300495, 318608, 349411],\n",
    "    [111025, 123594, 122640, 116454, 150155], \n",
    "    [190889, 193841, 164676, 163387, 185807],\n",
    "    [2004, 2008, 2012, 2016, 2020],\n",
    "    ['AK', 'AK', 'AK', 'AK', 'AK'],\n",
    "    ['Alaska', 'Alaska', 'Alaska', 'Alaska', 'Alaska'],\n",
    "    [467404, 483060, 519015, 530385, 557082]\n",
    "]\n",
    "alaska_df = pd.DataFrame(dict(zip(columns, ak)))\n",
    "alaska_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>year</th>\n",
       "      <th>state</th>\n",
       "      <th>county_name</th>\n",
       "      <th>fips_code</th>\n",
       "      <th>rep_num</th>\n",
       "      <th>dem_num</th>\n",
       "      <th>county_num</th>\n",
       "      <th>vap</th>\n",
       "      <th>dem_margin</th>\n",
       "      <th>turnout</th>\n",
       "      <th>state_electoral_votes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2004</td>\n",
       "      <td>AL</td>\n",
       "      <td>Autauga</td>\n",
       "      <td>01001</td>\n",
       "      <td>15196.0</td>\n",
       "      <td>4758.0</td>\n",
       "      <td>20081</td>\n",
       "      <td>35818.0</td>\n",
       "      <td>-0.519795</td>\n",
       "      <td>0.560640</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2004</td>\n",
       "      <td>AL</td>\n",
       "      <td>Baldwin</td>\n",
       "      <td>01003</td>\n",
       "      <td>52971.0</td>\n",
       "      <td>15599.0</td>\n",
       "      <td>69320</td>\n",
       "      <td>116753.0</td>\n",
       "      <td>-0.539123</td>\n",
       "      <td>0.593732</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2004</td>\n",
       "      <td>AL</td>\n",
       "      <td>Barbour</td>\n",
       "      <td>01005</td>\n",
       "      <td>5899.0</td>\n",
       "      <td>4832.0</td>\n",
       "      <td>10777</td>\n",
       "      <td>22069.0</td>\n",
       "      <td>-0.099007</td>\n",
       "      <td>0.488332</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2004</td>\n",
       "      <td>AL</td>\n",
       "      <td>Bibb</td>\n",
       "      <td>01007</td>\n",
       "      <td>5472.0</td>\n",
       "      <td>2089.0</td>\n",
       "      <td>7600</td>\n",
       "      <td>16628.0</td>\n",
       "      <td>-0.445132</td>\n",
       "      <td>0.457060</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2004</td>\n",
       "      <td>AL</td>\n",
       "      <td>Blount</td>\n",
       "      <td>01009</td>\n",
       "      <td>17386.0</td>\n",
       "      <td>3938.0</td>\n",
       "      <td>21504</td>\n",
       "      <td>40194.0</td>\n",
       "      <td>-0.625372</td>\n",
       "      <td>0.535005</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15575</th>\n",
       "      <td>2020</td>\n",
       "      <td>WY</td>\n",
       "      <td>Uinta</td>\n",
       "      <td>56041</td>\n",
       "      <td>7496.0</td>\n",
       "      <td>1591.0</td>\n",
       "      <td>9402</td>\n",
       "      <td>14322.0</td>\n",
       "      <td>-0.628058</td>\n",
       "      <td>0.656473</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15576</th>\n",
       "      <td>2020</td>\n",
       "      <td>WY</td>\n",
       "      <td>Washakie</td>\n",
       "      <td>56043</td>\n",
       "      <td>3245.0</td>\n",
       "      <td>651.0</td>\n",
       "      <td>4012</td>\n",
       "      <td>6192.0</td>\n",
       "      <td>-0.646560</td>\n",
       "      <td>0.647933</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15577</th>\n",
       "      <td>2020</td>\n",
       "      <td>WY</td>\n",
       "      <td>Weston</td>\n",
       "      <td>56045</td>\n",
       "      <td>3107.0</td>\n",
       "      <td>360.0</td>\n",
       "      <td>3542</td>\n",
       "      <td>5530.0</td>\n",
       "      <td>-0.775551</td>\n",
       "      <td>0.640506</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15578</th>\n",
       "      <td>2020</td>\n",
       "      <td>DC</td>\n",
       "      <td>District of Columbia</td>\n",
       "      <td>11001</td>\n",
       "      <td>18172.0</td>\n",
       "      <td>307265.0</td>\n",
       "      <td>333682</td>\n",
       "      <td>548786.0</td>\n",
       "      <td>0.866373</td>\n",
       "      <td>0.608037</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15579</th>\n",
       "      <td>2020</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>02</td>\n",
       "      <td>185807.0</td>\n",
       "      <td>150155.0</td>\n",
       "      <td>349411</td>\n",
       "      <td>557082.0</td>\n",
       "      <td>-0.102035</td>\n",
       "      <td>0.627216</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>15580 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       year state           county_name fips_code   rep_num   dem_num  \\\n",
       "0      2004    AL               Autauga     01001   15196.0    4758.0   \n",
       "1      2004    AL               Baldwin     01003   52971.0   15599.0   \n",
       "2      2004    AL               Barbour     01005    5899.0    4832.0   \n",
       "3      2004    AL                  Bibb     01007    5472.0    2089.0   \n",
       "4      2004    AL                Blount     01009   17386.0    3938.0   \n",
       "...     ...   ...                   ...       ...       ...       ...   \n",
       "15575  2020    WY                 Uinta     56041    7496.0    1591.0   \n",
       "15576  2020    WY              Washakie     56043    3245.0     651.0   \n",
       "15577  2020    WY                Weston     56045    3107.0     360.0   \n",
       "15578  2020    DC  District of Columbia     11001   18172.0  307265.0   \n",
       "15579  2020    AK                Alaska        02  185807.0  150155.0   \n",
       "\n",
       "       county_num       vap  dem_margin   turnout  state_electoral_votes  \n",
       "0           20081   35818.0   -0.519795  0.560640                      9  \n",
       "1           69320  116753.0   -0.539123  0.593732                      9  \n",
       "2           10777   22069.0   -0.099007  0.488332                      9  \n",
       "3            7600   16628.0   -0.445132  0.457060                      9  \n",
       "4           21504   40194.0   -0.625372  0.535005                      9  \n",
       "...           ...       ...         ...       ...                    ...  \n",
       "15575        9402   14322.0   -0.628058  0.656473                      3  \n",
       "15576        4012    6192.0   -0.646560  0.647933                      3  \n",
       "15577        3542    5530.0   -0.775551  0.640506                      3  \n",
       "15578      333682  548786.0    0.866373  0.608037                      3  \n",
       "15579      349411  557082.0   -0.102035  0.627216                      3  \n",
       "\n",
       "[15580 rows x 11 columns]"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Combine 2020 data with 2004-2016 data, get VAP data\n",
    "\n",
    "# No longer need year category, as I have annual CVAP estimates by county now.\n",
    "# def get_yearcategory(el):\n",
    "#     if el in [2004, 2008]:\n",
    "#         return '2004-2008'\n",
    "#     elif el in [2012]:\n",
    "#         return '2012'\n",
    "#     elif el in [2016, 2020]:\n",
    "#         return '2016-2020'\n",
    "#     else:\n",
    "#         return None\n",
    "    \n",
    "county = pd.concat([county, county_2020])\n",
    "# county['year_category'] = county['year'].apply(get_yearcategory)\n",
    "# # premerge = county.copy()\n",
    "# # https://stackoverflow.com/questions/36891977/diff-of-two-dataframes\n",
    "# # missing = premerge.merge(county, on=['year', 'fips_code'], how='outer', indicator=True)\n",
    "# # missing[missing['_merge'] == 'left_only']['state_x'].value_counts()\n",
    "# # Ok, it's mostly Alaska missing. Just append it separately.\n",
    "# # AK    188\n",
    "# # MO      4\n",
    "# # SD      1\n",
    "# # VA      1\n",
    "# Merge on county VAP data\n",
    "county = county.merge(countypop_df, on=['fips_code', 'year'])\n",
    "county = pd.concat([county, alaska_df])\n",
    "county['dem_margin'] =  (county['dem_num'] - county['rep_num'])/county['county_num']\n",
    "county['turnout'] = county['county_num']/ county['vap']\n",
    "county = county[['year', 'state', 'county_name', \n",
    "    'fips_code', 'rep_num', 'dem_num', 'county_num', 'vap',\n",
    "    'dem_margin', 'turnout']]\n",
    "county = county.merge(electoral_df, on=['state', 'year'])\n",
    "county.reset_index(drop=True, inplace=True)\n",
    "county"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>year</th>\n",
       "      <th>state</th>\n",
       "      <th>county_name</th>\n",
       "      <th>fips_code</th>\n",
       "      <th>rep_num</th>\n",
       "      <th>dem_num</th>\n",
       "      <th>county_num</th>\n",
       "      <th>vap</th>\n",
       "      <th>dem_margin</th>\n",
       "      <th>turnout</th>\n",
       "      <th>state_electoral_votes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3034</th>\n",
       "      <td>2004</td>\n",
       "      <td>WI</td>\n",
       "      <td>Dane</td>\n",
       "      <td>55025</td>\n",
       "      <td>90369.0</td>\n",
       "      <td>181052.0</td>\n",
       "      <td>274249</td>\n",
       "      <td>336138.0</td>\n",
       "      <td>0.330659</td>\n",
       "      <td>0.815882</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6151</th>\n",
       "      <td>2008</td>\n",
       "      <td>WI</td>\n",
       "      <td>Dane</td>\n",
       "      <td>55025</td>\n",
       "      <td>73065.0</td>\n",
       "      <td>205984.0</td>\n",
       "      <td>282939</td>\n",
       "      <td>353385.0</td>\n",
       "      <td>0.469780</td>\n",
       "      <td>0.800654</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9268</th>\n",
       "      <td>2012</td>\n",
       "      <td>WI</td>\n",
       "      <td>Dane</td>\n",
       "      <td>55025</td>\n",
       "      <td>83644.0</td>\n",
       "      <td>216071.0</td>\n",
       "      <td>304181</td>\n",
       "      <td>372315.0</td>\n",
       "      <td>0.435356</td>\n",
       "      <td>0.816999</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12384</th>\n",
       "      <td>2016</td>\n",
       "      <td>WI</td>\n",
       "      <td>Dane</td>\n",
       "      <td>55025</td>\n",
       "      <td>71279.0</td>\n",
       "      <td>217526.0</td>\n",
       "      <td>310017</td>\n",
       "      <td>394780.0</td>\n",
       "      <td>0.471739</td>\n",
       "      <td>0.785291</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15440</th>\n",
       "      <td>2020</td>\n",
       "      <td>WI</td>\n",
       "      <td>Dane</td>\n",
       "      <td>55025</td>\n",
       "      <td>78800.0</td>\n",
       "      <td>260185.0</td>\n",
       "      <td>344791</td>\n",
       "      <td>412206.0</td>\n",
       "      <td>0.526072</td>\n",
       "      <td>0.836453</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       year state county_name fips_code  rep_num   dem_num  county_num  \\\n",
       "3034   2004    WI        Dane     55025  90369.0  181052.0      274249   \n",
       "6151   2008    WI        Dane     55025  73065.0  205984.0      282939   \n",
       "9268   2012    WI        Dane     55025  83644.0  216071.0      304181   \n",
       "12384  2016    WI        Dane     55025  71279.0  217526.0      310017   \n",
       "15440  2020    WI        Dane     55025  78800.0  260185.0      344791   \n",
       "\n",
       "            vap  dem_margin   turnout  state_electoral_votes  \n",
       "3034   336138.0    0.330659  0.815882                     10  \n",
       "6151   353385.0    0.469780  0.800654                     10  \n",
       "9268   372315.0    0.435356  0.816999                     10  \n",
       "12384  394780.0    0.471739  0.785291                     10  \n",
       "15440  412206.0    0.526072  0.836453                     10  "
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "county[county['county_name'] == 'Dane']\n",
    "#So this is 394780, vs estimated VAP of 345,604/0.8 = 432005 based on this article:, 87 vs 80pct turnout diff.\n",
    "#https://madison.com/wsj/news/local/govt-and-politics/turnout-sets-records-with-dane-county-at-80-madison-at-75/article_e7d5acba-d6e6-5188-9cc0-a540e8afa64f.html\n",
    "#Ok, after updating to new CVAP data, based on a regression, this is now much closer to the 80% turnout mentioned\n",
    "#In the article. Cool."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Checking the county count to find missing 2020 counties\n",
    "# res1 = county_2020.groupby(by='state')['fips_code'].count().to_frame()\n",
    "# res2 = county[county['year'] == 2020].groupby('state')['fips_code'].count().to_frame()\n",
    "# res = res1.merge(res2, on='state')\n",
    "# res['diff'] = res['fips_code_x'] - res['fips_code_y']\n",
    "# res"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>year</th>\n",
       "      <th>rep_num</th>\n",
       "      <th>dem_num</th>\n",
       "      <th>county_num</th>\n",
       "      <th>vap</th>\n",
       "      <th>dem_margin</th>\n",
       "      <th>turnout</th>\n",
       "      <th>state_electoral_votes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>15541.000000</td>\n",
       "      <td>1.554100e+04</td>\n",
       "      <td>1.554100e+04</td>\n",
       "      <td>1.554100e+04</td>\n",
       "      <td>1.554100e+04</td>\n",
       "      <td>15541.000000</td>\n",
       "      <td>15541.000000</td>\n",
       "      <td>15541.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>2011.995367</td>\n",
       "      <td>2.048893e+04</td>\n",
       "      <td>2.176251e+04</td>\n",
       "      <td>4.330646e+04</td>\n",
       "      <td>7.075243e+04</td>\n",
       "      <td>-0.242506</td>\n",
       "      <td>0.603642</td>\n",
       "      <td>13.732836</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>5.655214</td>\n",
       "      <td>4.696340e+04</td>\n",
       "      <td>7.900638e+04</td>\n",
       "      <td>1.247803e+05</td>\n",
       "      <td>2.059195e+05</td>\n",
       "      <td>0.298274</td>\n",
       "      <td>0.097658</td>\n",
       "      <td>10.795838</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>2004.000000</td>\n",
       "      <td>5.400000e+01</td>\n",
       "      <td>4.000000e+00</td>\n",
       "      <td>6.400000e+01</td>\n",
       "      <td>6.600000e+01</td>\n",
       "      <td>-0.930900</td>\n",
       "      <td>0.025800</td>\n",
       "      <td>3.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>2008.000000</td>\n",
       "      <td>3.100000e+03</td>\n",
       "      <td>1.501000e+03</td>\n",
       "      <td>5.018000e+03</td>\n",
       "      <td>8.515000e+03</td>\n",
       "      <td>-0.467400</td>\n",
       "      <td>0.536500</td>\n",
       "      <td>6.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>2012.000000</td>\n",
       "      <td>6.914000e+03</td>\n",
       "      <td>3.904000e+03</td>\n",
       "      <td>1.116500e+04</td>\n",
       "      <td>1.960000e+04</td>\n",
       "      <td>-0.267800</td>\n",
       "      <td>0.603000</td>\n",
       "      <td>10.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>2016.000000</td>\n",
       "      <td>1.750600e+04</td>\n",
       "      <td>1.127100e+04</td>\n",
       "      <td>2.961600e+04</td>\n",
       "      <td>5.090500e+04</td>\n",
       "      <td>-0.054100</td>\n",
       "      <td>0.669700</td>\n",
       "      <td>16.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>2020.000000</td>\n",
       "      <td>1.121538e+06</td>\n",
       "      <td>2.980478e+06</td>\n",
       "      <td>4.188764e+06</td>\n",
       "      <td>6.655374e+06</td>\n",
       "      <td>0.874100</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>55.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               year       rep_num       dem_num    county_num           vap  \\\n",
       "count  15541.000000  1.554100e+04  1.554100e+04  1.554100e+04  1.554100e+04   \n",
       "mean    2011.995367  2.048893e+04  2.176251e+04  4.330646e+04  7.075243e+04   \n",
       "std        5.655214  4.696340e+04  7.900638e+04  1.247803e+05  2.059195e+05   \n",
       "min     2004.000000  5.400000e+01  4.000000e+00  6.400000e+01  6.600000e+01   \n",
       "25%     2008.000000  3.100000e+03  1.501000e+03  5.018000e+03  8.515000e+03   \n",
       "50%     2012.000000  6.914000e+03  3.904000e+03  1.116500e+04  1.960000e+04   \n",
       "75%     2016.000000  1.750600e+04  1.127100e+04  2.961600e+04  5.090500e+04   \n",
       "max     2020.000000  1.121538e+06  2.980478e+06  4.188764e+06  6.655374e+06   \n",
       "\n",
       "         dem_margin       turnout  state_electoral_votes  \n",
       "count  15541.000000  15541.000000           15541.000000  \n",
       "mean      -0.242506      0.603642              13.732836  \n",
       "std        0.298274      0.097658              10.795838  \n",
       "min       -0.930900      0.025800               3.000000  \n",
       "25%       -0.467400      0.536500               6.000000  \n",
       "50%       -0.267800      0.603000              10.000000  \n",
       "75%       -0.054100      0.669700              16.000000  \n",
       "max        0.874100      1.000000              55.000000  "
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Data output script for interactive visualization, filters out abnormal values  \n",
    "out_df = county[\n",
    "    (county['dem_margin'] <= 1.0) &\n",
    "    (county['dem_margin'] >= -1.0) &\n",
    "    (county['turnout'] >= 0.0) &\n",
    "    (county['turnout'] <= 1.0)\n",
    "]\n",
    "# # Clip instead?\n",
    "# out_df = county.copy()\n",
    "# out_df['dem_margin'] = out_df['dem_margin'].clip(lower=-1.0, upper=1.0)\n",
    "# out_df['turnout'] = out_df['turnout'].clip(lower=0.0, upper=1.0)\n",
    "out_df = out_df.round(decimals=4)\n",
    "out_df.to_csv('./US_County_Level_Presidential_Results_04-20.csv', index=False)\n",
    "out_df.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# State and National"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>state</th>\n",
       "      <th>year</th>\n",
       "      <th>state_num</th>\n",
       "      <th>dem_num</th>\n",
       "      <th>rep_num</th>\n",
       "      <th>vap</th>\n",
       "      <th>state_electoral_votes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AK</td>\n",
       "      <td>2004</td>\n",
       "      <td>324078</td>\n",
       "      <td>115490.0</td>\n",
       "      <td>197521.0</td>\n",
       "      <td>662387.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AK</td>\n",
       "      <td>2008</td>\n",
       "      <td>331335</td>\n",
       "      <td>126068.0</td>\n",
       "      <td>196377.0</td>\n",
       "      <td>682015.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AK</td>\n",
       "      <td>2012</td>\n",
       "      <td>308459</td>\n",
       "      <td>125721.0</td>\n",
       "      <td>169220.0</td>\n",
       "      <td>730505.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AK</td>\n",
       "      <td>2016</td>\n",
       "      <td>325748</td>\n",
       "      <td>120605.0</td>\n",
       "      <td>165574.0</td>\n",
       "      <td>743130.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AK</td>\n",
       "      <td>2020</td>\n",
       "      <td>349411</td>\n",
       "      <td>150155.0</td>\n",
       "      <td>185807.0</td>\n",
       "      <td>557082.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>250</th>\n",
       "      <td>WY</td>\n",
       "      <td>2004</td>\n",
       "      <td>243428</td>\n",
       "      <td>70776.0</td>\n",
       "      <td>167629.0</td>\n",
       "      <td>390463.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>251</th>\n",
       "      <td>WY</td>\n",
       "      <td>2008</td>\n",
       "      <td>256035</td>\n",
       "      <td>82868.0</td>\n",
       "      <td>164958.0</td>\n",
       "      <td>405095.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>252</th>\n",
       "      <td>WY</td>\n",
       "      <td>2012</td>\n",
       "      <td>249061</td>\n",
       "      <td>69286.0</td>\n",
       "      <td>170962.0</td>\n",
       "      <td>427305.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>253</th>\n",
       "      <td>WY</td>\n",
       "      <td>2016</td>\n",
       "      <td>255849</td>\n",
       "      <td>55973.0</td>\n",
       "      <td>174419.0</td>\n",
       "      <td>432285.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>254</th>\n",
       "      <td>WY</td>\n",
       "      <td>2020</td>\n",
       "      <td>276765</td>\n",
       "      <td>73491.0</td>\n",
       "      <td>193559.0</td>\n",
       "      <td>452384.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>255 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    state  year  state_num   dem_num   rep_num       vap  \\\n",
       "0      AK  2004     324078  115490.0  197521.0  662387.0   \n",
       "1      AK  2008     331335  126068.0  196377.0  682015.0   \n",
       "2      AK  2012     308459  125721.0  169220.0  730505.0   \n",
       "3      AK  2016     325748  120605.0  165574.0  743130.0   \n",
       "4      AK  2020     349411  150155.0  185807.0  557082.0   \n",
       "..    ...   ...        ...       ...       ...       ...   \n",
       "250    WY  2004     243428   70776.0  167629.0  390463.0   \n",
       "251    WY  2008     256035   82868.0  164958.0  405095.0   \n",
       "252    WY  2012     249061   69286.0  170962.0  427305.0   \n",
       "253    WY  2016     255849   55973.0  174419.0  432285.0   \n",
       "254    WY  2020     276765   73491.0  193559.0  452384.0   \n",
       "\n",
       "     state_electoral_votes  \n",
       "0                        3  \n",
       "1                        3  \n",
       "2                        3  \n",
       "3                        3  \n",
       "4                        3  \n",
       "..                     ...  \n",
       "250                      3  \n",
       "251                      3  \n",
       "252                      3  \n",
       "253                      3  \n",
       "254                      3  \n",
       "\n",
       "[255 rows x 7 columns]"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "state_df = county[\n",
    "    (county['dem_margin'] <= 1.0) &\n",
    "    (county['dem_margin'] >= -1.0) &\n",
    "    (county['turnout'] >= 0.0) &\n",
    "    (county['turnout'] <= 1.0)\n",
    "]\n",
    "# # Clip instead?\n",
    "# state_df = county.copy()\n",
    "# state_df['dem_margin'] = state_df['dem_margin'].clip(lower=-1.0, upper=1.0)\n",
    "# state_df['turnout'] = state_df['turnout'].clip(lower=0.0, upper=1.0)\n",
    "state_df = state_df.round(decimals=4)\n",
    "state_df = state_df.groupby(['state','year']).agg(\n",
    "    {'county_num':'sum', 'dem_num':'sum','rep_num':'sum',\n",
    "     'vap':'sum', 'state_electoral_votes':'max'})\n",
    "\n",
    "state_df = state_df.round(decimals=4)\n",
    "state_df = state_df.reset_index()\n",
    "state_df.rename(columns={'county_num':'state_num'}, inplace=True)\n",
    "state_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "state_df.to_csv('./US_State_Level_Presidential_Results_04-20.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>year</th>\n",
       "      <th>num_nation</th>\n",
       "      <th>dem_num</th>\n",
       "      <th>rep_num</th>\n",
       "      <th>vap</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2004</td>\n",
       "      <td>122046363</td>\n",
       "      <td>58739173.0</td>\n",
       "      <td>61822463.0</td>\n",
       "      <td>201435196.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2008</td>\n",
       "      <td>131061479</td>\n",
       "      <td>69336086.0</td>\n",
       "      <td>59915583.0</td>\n",
       "      <td>210438550.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2012</td>\n",
       "      <td>128904321</td>\n",
       "      <td>65641120.0</td>\n",
       "      <td>60635783.0</td>\n",
       "      <td>220272165.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2016</td>\n",
       "      <td>136407990</td>\n",
       "      <td>65751981.0</td>\n",
       "      <td>62963094.0</td>\n",
       "      <td>229127395.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020</td>\n",
       "      <td>154744633</td>\n",
       "      <td>78789724.0</td>\n",
       "      <td>73168558.0</td>\n",
       "      <td>238352979.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   year  num_nation     dem_num     rep_num          vap\n",
       "0  2004   122046363  58739173.0  61822463.0  201435196.0\n",
       "1  2008   131061479  69336086.0  59915583.0  210438550.0\n",
       "2  2012   128904321  65641120.0  60635783.0  220272165.0\n",
       "3  2016   136407990  65751981.0  62963094.0  229127395.0\n",
       "4  2020   154744633  78789724.0  73168558.0  238352979.0"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# This filter has weird effects, not sure why\n",
    "# national_df = county[\n",
    "#     (county['dem_margin'] <= 1.0) &\n",
    "#     (county['dem_margin'] >= -1.0) &\n",
    "#     (county['turnout'] >= 0.0) &\n",
    "#     (county['turnout'] <= 1.0)\n",
    "# ].copy()\n",
    "# OK, so the problem is that some places have turnout that is too high\n",
    "national_df = county.copy()\n",
    "national_df = national_df.groupby(['year']).agg(\n",
    "    {'county_num':'sum', 'dem_num':'sum','rep_num':'sum',\n",
    "     'vap':'sum'})\n",
    "national_df.rename(columns={'county_num':'num_nation'}, inplace=True)\n",
    "national_df = national_df.reset_index()\n",
    "national_df\n",
    "# 2020, 2.5 million are missing. Alaska? Other yet uncalled states?\n",
    "# So with new data, things aren't missing anymore."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Demographic Data\n",
    "\n",
    "# ANES 2016 Race Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/psthomas/miniconda2/envs/datascience3/lib/python3.5/site-packages/IPython/core/interactiveshell.py:2717: DtypeWarning: Columns (790,1129,1131) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>group</th>\n",
       "      <th>year</th>\n",
       "      <th>dem_frac</th>\n",
       "      <th>rep_frac</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Non-Hispanic White</td>\n",
       "      <td>2016</td>\n",
       "      <td>0.393836</td>\n",
       "      <td>0.538035</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Non-Hispanic Black</td>\n",
       "      <td>2016</td>\n",
       "      <td>0.899861</td>\n",
       "      <td>0.063772</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Hispanic</td>\n",
       "      <td>2016</td>\n",
       "      <td>0.686294</td>\n",
       "      <td>0.238819</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Other</td>\n",
       "      <td>2016</td>\n",
       "      <td>0.573222</td>\n",
       "      <td>0.314705</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                group  year  dem_frac  rep_frac\n",
       "0  Non-Hispanic White  2016  0.393836  0.538035\n",
       "1  Non-Hispanic Black  2016  0.899861  0.063772\n",
       "2            Hispanic  2016  0.686294  0.238819\n",
       "3               Other  2016  0.573222  0.314705"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Calculate 2016 Democratic Margin By Race\n",
    "## source: ANES 2016 time series study:\n",
    "## http://www.electionstudies.org/studypages/anes_timeseries_2016/anes_timeseries_2016.htm\n",
    "\n",
    "anes_df = pd.read_csv('./data/anes/anes_timeseries_2016/anes_timeseries_2016_rawdata.txt',\n",
    "                      sep='|')\n",
    "\n",
    "columns = ['V160102', 'V161310x', 'V162058x']\n",
    "labels = ['weight_postfull', 'group', 'party']\n",
    "\n",
    "anes_df = anes_df[columns]\n",
    "anes_df.rename(columns=dict(zip(columns, labels)), inplace=True)\n",
    "\n",
    "anes_df = anes_df[anes_df['party'].isin([10,11,12])]  #Filter out nonvoters\n",
    "\n",
    "group_df = anes_df.groupby(['group', 'party']).agg({'weight_postfull':'sum'})\n",
    "group_df = group_df.unstack(level=-1)\n",
    "group_df.columns = group_df.columns.droplevel(0)\n",
    "group_df = group_df.rename_axis(None, axis=1)\n",
    "\n",
    "group_df.loc[6] = group_df.loc[[3,4,6,-9]].sum()  #Include nonresponders in other, -9\n",
    "rows = [1,2,5,6]\n",
    "races = ['Non-Hispanic White', 'Non-Hispanic Black', 'Hispanic', 'Other']\n",
    "group_df.rename(index=dict(zip(rows,races)), inplace=True)\n",
    "group_df = group_df.loc[races] \n",
    "\n",
    "group_df.reset_index(inplace=True)\n",
    "group_df['total'] = group_df[[10,11,12]].sum(axis=1)\n",
    "group_df.rename(columns={10:'d',11:'r',12:'o'}, inplace=True)\n",
    "\n",
    "group_df['dem_frac'] = group_df['d']/group_df['total']\n",
    "group_df['rep_frac'] = group_df['r']/group_df['total']\n",
    "group_df['other_frac'] = group_df['o']/group_df['total']\n",
    "#group_df['dem_margin'] = group_df['dem_frac'] - group_df['rep_frac']\n",
    "\n",
    "group_df['year'] = 2016\n",
    "\n",
    "exp_df = group_df.copy()\n",
    "group_df = group_df[['group','year','dem_frac', 'rep_frac']]\n",
    "group_df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 169,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0.49001285898\n",
      "0.44153235198\n",
      "0.481491655712\n",
      "0.460532572669\n"
     ]
    }
   ],
   "source": [
    "exp_df = exp_df.merge(national_df, on='year')\n",
    "exp_df['electorate_frac'] = exp_df['total'] / exp_df['total'].sum()\n",
    "\n",
    "exp_df['altdemnum'] = exp_df['electorate_frac']*exp_df['dem_frac']*exp_df['num_nation']\n",
    "exp_df['altrepnum'] = exp_df['electorate_frac']*exp_df['rep_frac']*exp_df['num_nation']\n",
    "\n",
    "print(exp_df['altdemnum'].sum()/exp_df['num_nation'].mean())\n",
    "print(exp_df['altrepnum'].sum()/exp_df['num_nation'].mean())\n",
    "\n",
    "print(exp_df['dem_num'].mean()/exp_df['num_nation'].mean())\n",
    "print(exp_df['rep_num'].mean()/exp_df['num_nation'].mean())\n",
    "# exp_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The results of the demographics extrapolated don't agree with the \n",
    "actual cumulative results:  \n",
    "Extrapolated:  \n",
    "D: 0.49001285898  \n",
    "R: 0.44153235198  \n",
    "Actual:  \n",
    "D: 0.481491655712  \n",
    "R: 0.460532572669  \n",
    "\n",
    "This is a commonly known problem: \n",
    "\n",
    "https://www.americanprogress.org/issues/democracy/reports/2017/11/01/441926/voter-trends-in-2016/\n",
    "\n",
    "> The most vexing issue for electoral analysts exploring these voting trends is determining which, if any, of the existing data sources provide the best and most reliable information on vote composition, turnout, and support rates. For this project, we developed original turnout and support estimates by combining a multitude of publicly available data sources including the American Communities Survey (ACS), the November supplement of the Current Population Survey (CPS), the American National Election Study (ANES), the Cooperative Congressional Election Survey (CCES), our own post-election polling, and voter files from several states.\n",
    "We used this approach to help address what we believe are systematic problems with some of the most widely available and most frequently cited pieces of data about elections—mainly, that some of the most reliable sources of data we have on demographics do not fit well together with the best data we have on turnout rates, leading to results that vary from the actual levels of turnout seen on Election Day. Furthermore, if we combine those data with the best data we have on vote choice, we get election results that do not line up with reality. This is not due to any one source of information being particularly biased; rather, each particular source has points of weakness. To overcome this, we created a new method for combining these data in ways that fit with known outcomes."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## ANES and CPS 2004-2012 Race Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>demographic</th>\n",
       "      <th>group</th>\n",
       "      <th>year</th>\n",
       "      <th>dem_frac</th>\n",
       "      <th>rep_frac</th>\n",
       "      <th>electorate_frac</th>\n",
       "      <th>num_nation</th>\n",
       "      <th>dem_margin</th>\n",
       "      <th>turnout</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>race</td>\n",
       "      <td>Non-Hispanic White</td>\n",
       "      <td>2012</td>\n",
       "      <td>0.430000</td>\n",
       "      <td>0.570000</td>\n",
       "      <td>0.737</td>\n",
       "      <td>129254383</td>\n",
       "      <td>-0.140000</td>\n",
       "      <td>0.641</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>race</td>\n",
       "      <td>Non-Hispanic Black</td>\n",
       "      <td>2012</td>\n",
       "      <td>0.970000</td>\n",
       "      <td>0.030000</td>\n",
       "      <td>0.134</td>\n",
       "      <td>129254383</td>\n",
       "      <td>0.940000</td>\n",
       "      <td>0.664</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>race</td>\n",
       "      <td>Hispanic</td>\n",
       "      <td>2012</td>\n",
       "      <td>0.730000</td>\n",
       "      <td>0.270000</td>\n",
       "      <td>0.084</td>\n",
       "      <td>129254383</td>\n",
       "      <td>0.460000</td>\n",
       "      <td>0.480</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>race</td>\n",
       "      <td>Other</td>\n",
       "      <td>2012</td>\n",
       "      <td>0.610000</td>\n",
       "      <td>0.390000</td>\n",
       "      <td>0.045</td>\n",
       "      <td>129254383</td>\n",
       "      <td>0.220000</td>\n",
       "      <td>0.490</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>race</td>\n",
       "      <td>Non-Hispanic White</td>\n",
       "      <td>2008</td>\n",
       "      <td>0.440000</td>\n",
       "      <td>0.560000</td>\n",
       "      <td>0.763</td>\n",
       "      <td>131049863</td>\n",
       "      <td>-0.120000</td>\n",
       "      <td>0.661</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>race</td>\n",
       "      <td>Non-Hispanic Black</td>\n",
       "      <td>2008</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.124</td>\n",
       "      <td>131049863</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.652</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>race</td>\n",
       "      <td>Hispanic</td>\n",
       "      <td>2008</td>\n",
       "      <td>0.730000</td>\n",
       "      <td>0.270000</td>\n",
       "      <td>0.074</td>\n",
       "      <td>131049863</td>\n",
       "      <td>0.460000</td>\n",
       "      <td>0.499</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>race</td>\n",
       "      <td>Other</td>\n",
       "      <td>2008</td>\n",
       "      <td>0.820000</td>\n",
       "      <td>0.180000</td>\n",
       "      <td>0.039</td>\n",
       "      <td>131049863</td>\n",
       "      <td>0.640000</td>\n",
       "      <td>0.495</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>race</td>\n",
       "      <td>Non-Hispanic White</td>\n",
       "      <td>2004</td>\n",
       "      <td>0.420000</td>\n",
       "      <td>0.580000</td>\n",
       "      <td>0.792</td>\n",
       "      <td>122217180</td>\n",
       "      <td>-0.160000</td>\n",
       "      <td>0.672</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>race</td>\n",
       "      <td>Non-Hispanic Black</td>\n",
       "      <td>2004</td>\n",
       "      <td>0.890000</td>\n",
       "      <td>0.110000</td>\n",
       "      <td>0.112</td>\n",
       "      <td>122217180</td>\n",
       "      <td>0.780000</td>\n",
       "      <td>0.603</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>race</td>\n",
       "      <td>Hispanic</td>\n",
       "      <td>2004</td>\n",
       "      <td>0.610000</td>\n",
       "      <td>0.390000</td>\n",
       "      <td>0.060</td>\n",
       "      <td>122217180</td>\n",
       "      <td>0.220000</td>\n",
       "      <td>0.472</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>race</td>\n",
       "      <td>Other</td>\n",
       "      <td>2004</td>\n",
       "      <td>0.360000</td>\n",
       "      <td>0.640000</td>\n",
       "      <td>0.036</td>\n",
       "      <td>122217180</td>\n",
       "      <td>-0.280000</td>\n",
       "      <td>0.480</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>race</td>\n",
       "      <td>Non-Hispanic White</td>\n",
       "      <td>2016</td>\n",
       "      <td>0.393836</td>\n",
       "      <td>0.538035</td>\n",
       "      <td>0.733</td>\n",
       "      <td>136670626</td>\n",
       "      <td>-0.144198</td>\n",
       "      <td>0.653</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>race</td>\n",
       "      <td>Non-Hispanic Black</td>\n",
       "      <td>2016</td>\n",
       "      <td>0.899861</td>\n",
       "      <td>0.063772</td>\n",
       "      <td>0.124</td>\n",
       "      <td>136670626</td>\n",
       "      <td>0.836089</td>\n",
       "      <td>0.593</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>race</td>\n",
       "      <td>Hispanic</td>\n",
       "      <td>2016</td>\n",
       "      <td>0.686294</td>\n",
       "      <td>0.238819</td>\n",
       "      <td>0.092</td>\n",
       "      <td>136670626</td>\n",
       "      <td>0.447475</td>\n",
       "      <td>0.476</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>race</td>\n",
       "      <td>Other</td>\n",
       "      <td>2016</td>\n",
       "      <td>0.573222</td>\n",
       "      <td>0.314705</td>\n",
       "      <td>0.050</td>\n",
       "      <td>136670626</td>\n",
       "      <td>0.258517</td>\n",
       "      <td>0.490</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   demographic               group  year  dem_frac  rep_frac  electorate_frac  \\\n",
       "0         race  Non-Hispanic White  2012  0.430000  0.570000            0.737   \n",
       "1         race  Non-Hispanic Black  2012  0.970000  0.030000            0.134   \n",
       "2         race            Hispanic  2012  0.730000  0.270000            0.084   \n",
       "3         race               Other  2012  0.610000  0.390000            0.045   \n",
       "4         race  Non-Hispanic White  2008  0.440000  0.560000            0.763   \n",
       "5         race  Non-Hispanic Black  2008  1.000000  0.000000            0.124   \n",
       "6         race            Hispanic  2008  0.730000  0.270000            0.074   \n",
       "7         race               Other  2008  0.820000  0.180000            0.039   \n",
       "8         race  Non-Hispanic White  2004  0.420000  0.580000            0.792   \n",
       "9         race  Non-Hispanic Black  2004  0.890000  0.110000            0.112   \n",
       "10        race            Hispanic  2004  0.610000  0.390000            0.060   \n",
       "11        race               Other  2004  0.360000  0.640000            0.036   \n",
       "12        race  Non-Hispanic White  2016  0.393836  0.538035            0.733   \n",
       "13        race  Non-Hispanic Black  2016  0.899861  0.063772            0.124   \n",
       "14        race            Hispanic  2016  0.686294  0.238819            0.092   \n",
       "15        race               Other  2016  0.573222  0.314705            0.050   \n",
       "\n",
       "    num_nation  dem_margin  turnout  \n",
       "0    129254383   -0.140000    0.641  \n",
       "1    129254383    0.940000    0.664  \n",
       "2    129254383    0.460000    0.480  \n",
       "3    129254383    0.220000    0.490  \n",
       "4    131049863   -0.120000    0.661  \n",
       "5    131049863    1.000000    0.652  \n",
       "6    131049863    0.460000    0.499  \n",
       "7    131049863    0.640000    0.495  \n",
       "8    122217180   -0.160000    0.672  \n",
       "9    122217180    0.780000    0.603  \n",
       "10   122217180    0.220000    0.472  \n",
       "11   122217180   -0.280000    0.480  \n",
       "12   136670626   -0.144198    0.653  \n",
       "13   136670626    0.836089    0.593  \n",
       "14   136670626    0.447475    0.476  \n",
       "15   136670626    0.258517    0.490  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Sources:\n",
    "#http://www.electproject.org/home/voter-turnout/demographics\n",
    "#https://docs.google.com/spreadsheets/d/1l5fpK7ysQhQbZPv9hnZ_-PO1J1zBVPXSSQjNejTXecY/edit#gid=0\n",
    "#Below includes both corrected and uncorrected data, as data corrected for turnout response\n",
    "#bias doesn't result in correct overall D/R voter percentages.\n",
    "#Neither corrected or uncorrected result in right count.  \n",
    "#These turnouts are for citizen voting age population:\n",
    "\n",
    "# Corrected USEP turnout data:\n",
    "# race_turnout = StringIO(\"\"\"\n",
    "# Turnout Rate\t2016\t2014\t2012\t2010\t2008\t2006\t2004\n",
    "# Non-Hispanic White\t64.7%\t40.8%\t61.8%\t45.0%\t65.2%\t44.7%\t64.3%\n",
    "# Non-Hispanic Black\t59.9%\t36.4%\t67.4%\t41.6%\t69.1%\t36.6%\t61.4%\n",
    "# Hispanic\t44.9%\t21.1%\t43.1%\t26.6%\t46.5%\t25.5%\t42.9%\n",
    "# Other\t46.3%\t24.5%\t45.4%\t30.7%\t48.0%\t28.5%\t44.9%\n",
    "# \"\"\")\n",
    "\n",
    "#Uncorrected USEP turnout data:\n",
    "race_turnout = StringIO(\"\"\"\n",
    "Turnout Rate\t2016\t2014\t2012\t2010\t2008\t2006\t2004\n",
    "Non-Hispanic White\t65.3%\t45.8%\t64.1%\t48.6%\t66.1%\t51.6%\t67.2%\n",
    "Non-Hispanic Black\t59.3%\t40.4%\t66.4%\t43.8%\t65.2%\t41.2%\t60.3%\n",
    "Hispanic\t47.6%\t27.0%\t48.0%\t31.2%\t49.9%\t32.3%\t47.2%\n",
    "Other\t49.0%\t29.5%\t49.0%\t34.1%\t49.5%\t33.4%\t48.0%\n",
    "\"\"\")\n",
    "\n",
    "# Corrected USEP share of electorate data:\n",
    "# race_share = StringIO(\"\"\"\n",
    "# Share of Electorate\t2016\t2014\t2012\t2010\t2008\t2006\t2004\n",
    "# Non-Hispanic White\t73.6%\t76.9%\t74.1%\t77.9%\t76.6%\t81.0%\t79.5%\n",
    "# Non-Hispanic Black\t12.3%\t11.9%\t13.2%\t11.4%\t12.3%\t10.0%\t11.1%\n",
    "# Hispanic\t9.1%\t7.0%\t8.3%\t6.8%\t7.3%\t5.6%\t5.9%\n",
    "# Other\t5.0%\t4.2%\t4.4%\t3.9%\t3.8%\t3.4%\t3.6%\n",
    "# \"\"\")\n",
    "\n",
    "#Try uncorrected USEP data:\n",
    "race_share = StringIO(\"\"\"\n",
    "Share of Electorate\t2016\t2014\t2012\t2010\t2008\t2006\t2004\n",
    "Non-Hispanic White\t73.3%\t76.3%\t73.7%\t77.5%\t76.3%\t80.4%\t79.2%\n",
    "Non-Hispanic Black\t12.4%\t12.1%\t13.4%\t11.7%\t12.4%\t10.3%\t11.2%\n",
    "Hispanic\t9.2%\t7.3%\t8.4%\t6.9%\t7.4%\t5.8%\t6.0%\n",
    "Other\t5.0%\t4.3%\t4.5%\t3.9%\t3.9%\t3.4%\t3.6%\n",
    "\"\"\")\n",
    "\n",
    "#Source:\n",
    "#http://www.electionstudies.org/nesguide/text/t9a_1_1.txt\n",
    "race_demmargin = StringIO(\"\"\"\n",
    "percent_of_group,1948,1952,1954,1956,1958,1960,1962,1964,1966,1968,1970,1972,1974,1976,1978,1980,1982,1984,1986,1988,1990,1992,1994,1996,1998,2000,2002,2004,2008,2012\n",
    "Non-Hispanic White,53,40,,39,,48,,65,,41,,30,,46,,36,,36,,39,,52,,51,,46,,42,44,43,\n",
    "Non-Hispanic Black,65*,80,,64*,,71,,100,,97,,87,,95,,93,,91,,92,,95,,99,,92,,89,100,97,\n",
    "Hispanic,,,,,,,,,,40*,,50*,,86*,,58*,,52,,65,,65,,83,,59,,61,73,73,\n",
    "Other,,,,,,,,,,33*,,70*,,43*,,25*,,50*,,83*,,44*,,54*,,57,,36*,82,61\n",
    "\"\"\")\n",
    "\n",
    "race_repmargin = StringIO(\"\"\"\n",
    "percent_of_group,2004,2008,2012\n",
    "Non-Hispanic White,58,56,57 \n",
    "Non-Hispanic Black,11,0,3 \n",
    "Hispanic,39,27,27 \n",
    "Other,64,18,39 \n",
    "\"\"\")\n",
    "\n",
    "raceturnout_df = pd.read_csv(race_turnout, sep='\\t') #.T\n",
    "raceturnout_df = pd.melt(raceturnout_df, id_vars=[\"Turnout Rate\"], \n",
    "                  var_name=\"year\", value_name=\"turnout\")\n",
    "raceturnout_df.rename(columns={'Turnout Rate':'group'}, inplace=True)\n",
    "\n",
    "raceshare_df = pd.read_csv(race_share, sep='\\t')\n",
    "raceshare_df = pd.melt(raceshare_df, id_vars=[\"Share of Electorate\"], \n",
    "                  var_name=\"year\", value_name=\"electorate_frac\")\n",
    "raceshare_df.rename(columns={'Share of Electorate':'group'}, inplace=True)\n",
    "\n",
    "racedemmargin_df = pd.read_csv(race_demmargin, sep=',',index_col=False)\n",
    "racedemmargin_df = racedemmargin_df[['percent_of_group', '2004', '2008', '2012']]\n",
    "racedemmargin_df = pd.melt(racedemmargin_df, id_vars=[\"percent_of_group\"], \n",
    "                  var_name=\"year\", value_name=\"dem_frac\")\n",
    "racedemmargin_df.rename(columns={'percent_of_group':'group'}, inplace=True)\n",
    "\n",
    "racerepmargin_df = pd.read_csv(race_repmargin, sep=',',index_col=False)\n",
    "racerepmargin_df = racerepmargin_df[['percent_of_group', '2004', '2008', '2012']]\n",
    "racerepmargin_df = pd.melt(racerepmargin_df, id_vars=[\"percent_of_group\"], \n",
    "                  var_name=\"year\", value_name=\"rep_frac\")\n",
    "racerepmargin_df.rename(columns={'percent_of_group':'group'}, inplace=True)\n",
    "\n",
    "race_df = raceturnout_df.merge(raceshare_df, on=['group','year'], how='inner')\n",
    "race_df = race_df.merge(racedemmargin_df, on=['group','year'], how='inner')\n",
    "race_df = race_df.merge(racerepmargin_df, on=['group','year'], how='inner')\n",
    "race_df = race_df.replace({'\\%': '', '\\*':''}, regex=True)\n",
    "race_df = race_df.apply(pd.to_numeric, errors='ignore', axis=0)\n",
    "race_df[['turnout', 'electorate_frac',\n",
    "         'dem_frac', 'rep_frac']] = race_df[['turnout', 'electorate_frac', \n",
    "                                             'dem_frac','rep_frac']] / 100\n",
    "\n",
    "#Add in 2016 data from ANES\n",
    "int_df = raceturnout_df.merge(raceshare_df, on=['group','year'], how='inner')\n",
    "int_df = int_df.replace({'\\%': '', '\\*':''}, regex=True)\n",
    "int_df = int_df.apply(pd.to_numeric, errors='ignore', axis=0)\n",
    "int_df = int_df.merge(group_df, on=['group','year'], how='inner')\n",
    "int_df['electorate_frac'] = int_df['electorate_frac']/100\n",
    "int_df['turnout'] = int_df['turnout']/100\n",
    "race_df = pd.concat([race_df, int_df])\n",
    "\n",
    "electorate_df = national_df[['year','num_nation']]\n",
    "race_df = race_df.merge(electorate_df, on='year', how='left')\n",
    "\n",
    "race_df['dem_margin'] = race_df['dem_frac'] - race_df['rep_frac']\n",
    "#race_df['num_group'] = race_df['electorate_frac']*race_df['num_nation']\n",
    "#race_df['num_dem'] = race_df['dem_frac']*race_df['num_group']  #Calculate when loaded into JS\n",
    "#race_df['num_rep'] = race_df['rep_frac']*race_df['num_group']  #Calculate when loaded into JS\n",
    "#e.g. race_df['num_dem'] = race_df['dem_frac']*race_df['num_nation']*race_df['electorate_frac']\n",
    "\n",
    "race_df['demographic'] = 'race'\n",
    "order = ['demographic','group', 'year','dem_frac','rep_frac','electorate_frac', 'num_nation', 'dem_margin', 'turnout']\n",
    "race_df = race_df[order]\n",
    "\n",
    "race_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "#out_df = out_df.round(decimals=4), don't round, numbers are huge\n",
    "#race_df.to_csv('./US_Race_Presidential_Results_04-16.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# ANES and CPS 2004-2012, 2016 Gender Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/psthomas/miniconda2/envs/datascience3/lib/python3.5/site-packages/IPython/core/interactiveshell.py:2717: DtypeWarning: Columns (790,1129,1131) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>demographic</th>\n",
       "      <th>group</th>\n",
       "      <th>year</th>\n",
       "      <th>dem_frac</th>\n",
       "      <th>rep_frac</th>\n",
       "      <th>other_frac</th>\n",
       "      <th>electorate_frac</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>gender</td>\n",
       "      <td>male</td>\n",
       "      <td>2016</td>\n",
       "      <td>0.454789</td>\n",
       "      <td>0.469016</td>\n",
       "      <td>0.076195</td>\n",
       "      <td>0.462845</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>gender</td>\n",
       "      <td>female</td>\n",
       "      <td>2016</td>\n",
       "      <td>0.520975</td>\n",
       "      <td>0.419133</td>\n",
       "      <td>0.059892</td>\n",
       "      <td>0.524759</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  demographic   group  year  dem_frac  rep_frac  other_frac  electorate_frac\n",
       "0      gender    male  2016  0.454789  0.469016    0.076195         0.462845\n",
       "1      gender  female  2016  0.520975  0.419133    0.059892         0.524759"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Calculate 2016 Democratic Margin By Gender\n",
    "## source: ANES time series cumulative:\n",
    "## http://www.electionstudies.org/studypages/anes_timeseries_cdf/anes_timeseries_cdf.htm\n",
    "anes2016_df = pd.read_csv('./data/anes/anes_timeseries_2016/anes_timeseries_2016_rawdata.txt',\n",
    "                      sep='|')\n",
    "\n",
    "columns = ['V160102', 'V161342', 'V162058x']\n",
    "labels = ['weight_postfull', 'group', 'party']\n",
    "\n",
    "anes2016_df = anes2016_df[columns]\n",
    "anes2016_df.rename(columns=dict(zip(columns, labels)), inplace=True)\n",
    "\n",
    "anes2016_df = anes2016_df[anes2016_df['party'].isin([10,11,12])]  #Filter out nonvoters\n",
    "\n",
    "gender2016_df = anes2016_df.groupby(['group', 'party']).agg({'weight_postfull':'sum'})\n",
    "gender2016_df = gender2016_df.unstack(level=-1)\n",
    "gender2016_df.columns = gender2016_df.columns.droplevel(0)\n",
    "gender2016_df = gender2016_df.rename_axis(None, axis=1)\n",
    "\n",
    "rows = [1,2,3,-9]\n",
    "races = ['Male', 'Female', 'Other', 'Nonresponse']\n",
    "gender2016_df.rename(index=dict(zip(rows,races)), inplace=True)\n",
    "gender2016_df = gender2016_df.loc[races] \n",
    "\n",
    "gender2016_df.reset_index(inplace=True)\n",
    "gender2016_df['total'] = gender2016_df[[10,11,12]].sum(axis=1)\n",
    "gender2016_df.rename(columns={10:'d',11:'r',12:'o'}, inplace=True)\n",
    "\n",
    "gender2016_df['dem_frac'] = gender2016_df['d']/gender2016_df['total']\n",
    "gender2016_df['rep_frac'] = gender2016_df['r']/gender2016_df['total']\n",
    "gender2016_df['other_frac'] = gender2016_df['o']/gender2016_df['total']\n",
    "\n",
    "#0 R votes among 'other'\n",
    "gender2016_df.fillna(0, inplace=True)\n",
    "\n",
    "#Create electorate fraction\n",
    "gender2016_df['electorate_frac'] = gender2016_df['total'] / gender2016_df['total'].sum()\n",
    "\n",
    "gender2016_df['year'] = 2016\n",
    "gender2016_df['demographic'] = 'gender'\n",
    "gender2016_df = gender2016_df[['demographic', 'group','year','dem_frac', 'rep_frac',\n",
    "                               'other_frac','electorate_frac']]\n",
    "\n",
    "gender2016_df = gender2016_df[gender2016_df['group'].isin(['Male','Female'])]\n",
    "gender2016_df['group'].replace({'Male':'male','Female':'female'}, inplace=True)\n",
    "\n",
    "gender2016_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/psthomas/miniconda2/envs/datascience3/lib/python3.5/site-packages/IPython/core/interactiveshell.py:2717: DtypeWarning: Columns (13,14,15,18,19,22,23,24,25,26,27,28,29,30,31,32,33,34,35,39,40,41,42,43,44,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,234,255,276,297,298,299,300,301,302,303,304,305,306,307,308,309,310,311,312,313,314,315,316,317,318,319,320,321,322,323,324,325,326,327,328,329,330,331,332,333,334,335,336,337,338,339,340,341,362,383,404,425,426,427,428,429,430,431,432,433,434,435,436,437,438,439,440,441,442,443,444,445,446,447,448,449,450,451,452,453,454,455,456,457,458,459,460,461,462,463,464,465,466,467,468,469,470,471,472,473,474,475,476,477,478,479,480,481,482,483,484,485,486,487,488,489,490,491,492,493,494,495,496,497,498,499,500,501,502,503,504,505,506,507,508,509,510,511,512,513,514,515,516,517,518,519,520,521,522,523,524,525,526,527,528,529,530,531,532,533,534,535,536,537,538,539,540,541,542,543,544,545,546,547,548,549,550,551,552,553,554,555,556,557,558,559,560,561,562,563,564,565,566,567,568,569,570,571,572,573,574,575,576,577,578,579,580,581,582,583,584,585,586,587,588,589,590,591,592,593,594,595,596,597,598,599,600,601,602,603,604,605,606,607,608,609,610,611,612,613,614,615,616,617,618,619,620,621,622,623,624,625,626,627,628,629,630,631,632,633,634,635,636,637,638,639,640,641,642,643,644,645,646,648,649,650,651,652,653,654,655,656,657,658,659,660,661,662,663,664,665,666,667,668,669,670,671,672,673,674,675,676,677,678,679,680,681,682,683,684,685,686,687,688,689,690,691,692,693,694,695,696,697,698,699,700,701,702,703,704,705,706,707,708,709,710,711,712,713,714,715,716,717,718,719,720,721,722,723,724,725,726,727,728,729,730,731,732,733,734,735,736,737,738,739,740,741,742,743,744,745,746,747,748,749,750,751,752,753,754,755,756,757,758,759,760,761,762,763,764,765,766,767,812,813,814,815,816,817,818,819,820,821,822,823,824,825,826,827,828,829,830,831,832,833,834,835,836,837,838,839,840,841,842,843,844,845,846,847,848,849,850,851,852,853,854,855,856,857,858,859,860,861,862,863,864,865,866,867,868,869,870,871,872,873,874,875,876,877,878,879,880,881,882,883,884,885,886,887,888,889,890,891,892,893,894,895,896,897,898,899,900,901,902,903,904,905,906,907,908,909,910,911,912,913,914,915,916,917,918,919,920,921,922,923,924,925,926,927,928,929,930,931,932,933,934,935,936,937,938,939,940,941,942,943,944,945,946,947,948,949,950,951) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    }
   ],
   "source": [
    "baseanes2004_df = pd.read_csv('./data/anes/anes_timeseries_cdf/anes_timeseries_cdf_rawdata.txt',\n",
    "                      sep='|' )  \n",
    "#, low_memory=False\n",
    "#error_bad_lines=False, warn_bad_lines=False\n",
    "\n",
    "columns = ['VCF0004', 'VCF0009z', 'VCF0104', 'VCF0102', 'VCF0110', 'VCF0705', 'VCF0702']\n",
    "labels = ['year', 'weight_postfull', 'gender', 'age','education', 'party', 'voted']\n",
    "\n",
    "baseanes2004_df = baseanes2004_df[columns]\n",
    "baseanes2004_df.rename(columns=dict(zip(columns, labels)), inplace=True)\n",
    "baseanes2004_df = baseanes2004_df[baseanes2004_df['year'].isin([2004,2008,2012])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>group</th>\n",
       "      <th>year</th>\n",
       "      <th>dem_frac</th>\n",
       "      <th>rep_frac</th>\n",
       "      <th>other_frac</th>\n",
       "      <th>electorate_frac</th>\n",
       "      <th>demographic</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>male</td>\n",
       "      <td>2004</td>\n",
       "      <td>0.478701</td>\n",
       "      <td>0.491888</td>\n",
       "      <td>0.029411</td>\n",
       "      <td>0.519864</td>\n",
       "      <td>gender</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>female</td>\n",
       "      <td>2004</td>\n",
       "      <td>0.444623</td>\n",
       "      <td>0.544912</td>\n",
       "      <td>0.010465</td>\n",
       "      <td>0.480136</td>\n",
       "      <td>gender</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>male</td>\n",
       "      <td>2008</td>\n",
       "      <td>0.505027</td>\n",
       "      <td>0.467885</td>\n",
       "      <td>0.027088</td>\n",
       "      <td>0.429771</td>\n",
       "      <td>gender</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>female</td>\n",
       "      <td>2008</td>\n",
       "      <td>0.561853</td>\n",
       "      <td>0.423652</td>\n",
       "      <td>0.014495</td>\n",
       "      <td>0.570229</td>\n",
       "      <td>gender</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>male</td>\n",
       "      <td>2012</td>\n",
       "      <td>0.486533</td>\n",
       "      <td>0.469894</td>\n",
       "      <td>0.043573</td>\n",
       "      <td>0.478998</td>\n",
       "      <td>gender</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>female</td>\n",
       "      <td>2012</td>\n",
       "      <td>0.549398</td>\n",
       "      <td>0.430607</td>\n",
       "      <td>0.019995</td>\n",
       "      <td>0.521002</td>\n",
       "      <td>gender</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    group  year  dem_frac  rep_frac  other_frac  electorate_frac demographic\n",
       "0    male  2004  0.478701  0.491888    0.029411         0.519864      gender\n",
       "1  female  2004  0.444623  0.544912    0.010465         0.480136      gender\n",
       "2    male  2008  0.505027  0.467885    0.027088         0.429771      gender\n",
       "3  female  2008  0.561853  0.423652    0.014495         0.570229      gender\n",
       "4    male  2012  0.486533  0.469894    0.043573         0.478998      gender\n",
       "5  female  2012  0.549398  0.430607    0.019995         0.521002      gender"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Calculate 2004-2012 Democratic Margin By Gender\n",
    "## source: ANES time series cumulative:\n",
    "## http://www.electionstudies.org/studypages/anes_timeseries_cdf/anes_timeseries_cdf.htm\n",
    "gender2004_df = baseanes2004_df[baseanes2004_df['party'].isin([1,2,3])].copy()  #Filter out nonvoters\n",
    "gender2004_df.rename(columns={'gender':'group'},inplace=True)\n",
    "gender2004_df = gender2004_df.groupby(['year', 'group', 'party']).agg({'weight_postfull':'sum'})\n",
    "gender2004_df = gender2004_df.unstack(level=-1)\n",
    "gender2004_df.columns = gender2004_df.columns.droplevel(0)\n",
    "gender2004_df = gender2004_df.rename_axis(None, axis=1)\n",
    "\n",
    "#Reset index, then just relabel by applying to column, no need to select out\n",
    "gender2004_df.reset_index(inplace=True)\n",
    "gender2004_df['group'] = gender2004_df['group'].replace({1:'male',2:'female'})\n",
    "gender2004_df['total'] = gender2004_df[[1,2,3]].sum(axis=1)\n",
    "gender2004_df.rename(columns={1:'d',2:'r',3:'o'}, inplace=True)\n",
    "\n",
    "#Divide the number of respondents for each gender by total gender respondents\n",
    "#to get fraction of electorate\n",
    "sum_df = gender2004_df.groupby('year')['total'].sum()\n",
    "def electorate_frac(row):\n",
    "    row['electorate_frac'] = row['total']/sum_df[row['year']]\n",
    "    return row \n",
    "gender2004_df = gender2004_df.apply(electorate_frac, axis=1)\n",
    "\n",
    "gender2004_df['dem_frac'] = gender2004_df['d']/gender2004_df['total']\n",
    "gender2004_df['rep_frac'] = gender2004_df['r']/gender2004_df['total']\n",
    "gender2004_df['other_frac'] = gender2004_df['o']/gender2004_df['total']\n",
    "\n",
    "gender2004_df['demographic'] = 'gender'\n",
    "gender2004_df = gender2004_df[['group','year','dem_frac', 'rep_frac', \n",
    "                               'other_frac', 'electorate_frac', 'demographic']]\n",
    "\n",
    "gender2004_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>demographic</th>\n",
       "      <th>group</th>\n",
       "      <th>year</th>\n",
       "      <th>dem_frac</th>\n",
       "      <th>rep_frac</th>\n",
       "      <th>electorate_frac</th>\n",
       "      <th>num_nation</th>\n",
       "      <th>dem_margin</th>\n",
       "      <th>turnout</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>gender</td>\n",
       "      <td>male</td>\n",
       "      <td>2004</td>\n",
       "      <td>0.478701</td>\n",
       "      <td>0.491888</td>\n",
       "      <td>0.519864</td>\n",
       "      <td>122217180</td>\n",
       "      <td>-0.013188</td>\n",
       "      <td>0.621</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>gender</td>\n",
       "      <td>female</td>\n",
       "      <td>2004</td>\n",
       "      <td>0.444623</td>\n",
       "      <td>0.544912</td>\n",
       "      <td>0.480136</td>\n",
       "      <td>122217180</td>\n",
       "      <td>-0.100289</td>\n",
       "      <td>0.654</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>gender</td>\n",
       "      <td>male</td>\n",
       "      <td>2008</td>\n",
       "      <td>0.505027</td>\n",
       "      <td>0.467885</td>\n",
       "      <td>0.429771</td>\n",
       "      <td>131049863</td>\n",
       "      <td>0.037142</td>\n",
       "      <td>0.615</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>gender</td>\n",
       "      <td>female</td>\n",
       "      <td>2008</td>\n",
       "      <td>0.561853</td>\n",
       "      <td>0.423652</td>\n",
       "      <td>0.570229</td>\n",
       "      <td>131049863</td>\n",
       "      <td>0.138201</td>\n",
       "      <td>0.657</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>gender</td>\n",
       "      <td>male</td>\n",
       "      <td>2012</td>\n",
       "      <td>0.486533</td>\n",
       "      <td>0.469894</td>\n",
       "      <td>0.478998</td>\n",
       "      <td>129254383</td>\n",
       "      <td>0.016639</td>\n",
       "      <td>0.597</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>gender</td>\n",
       "      <td>female</td>\n",
       "      <td>2012</td>\n",
       "      <td>0.549398</td>\n",
       "      <td>0.430607</td>\n",
       "      <td>0.521002</td>\n",
       "      <td>129254383</td>\n",
       "      <td>0.118791</td>\n",
       "      <td>0.637</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>gender</td>\n",
       "      <td>male</td>\n",
       "      <td>2016</td>\n",
       "      <td>0.454789</td>\n",
       "      <td>0.469016</td>\n",
       "      <td>0.462845</td>\n",
       "      <td>136670626</td>\n",
       "      <td>-0.014226</td>\n",
       "      <td>0.593</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>gender</td>\n",
       "      <td>female</td>\n",
       "      <td>2016</td>\n",
       "      <td>0.520975</td>\n",
       "      <td>0.419133</td>\n",
       "      <td>0.524759</td>\n",
       "      <td>136670626</td>\n",
       "      <td>0.101842</td>\n",
       "      <td>0.633</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  demographic   group  year  dem_frac  rep_frac  electorate_frac  num_nation  \\\n",
       "0      gender    male  2004  0.478701  0.491888         0.519864   122217180   \n",
       "1      gender  female  2004  0.444623  0.544912         0.480136   122217180   \n",
       "2      gender    male  2008  0.505027  0.467885         0.429771   131049863   \n",
       "3      gender  female  2008  0.561853  0.423652         0.570229   131049863   \n",
       "4      gender    male  2012  0.486533  0.469894         0.478998   129254383   \n",
       "5      gender  female  2012  0.549398  0.430607         0.521002   129254383   \n",
       "6      gender    male  2016  0.454789  0.469016         0.462845   136670626   \n",
       "7      gender  female  2016  0.520975  0.419133         0.524759   136670626   \n",
       "\n",
       "   dem_margin  turnout  \n",
       "0   -0.013188    0.621  \n",
       "1   -0.100289    0.654  \n",
       "2    0.037142    0.615  \n",
       "3    0.138201    0.657  \n",
       "4    0.016639    0.597  \n",
       "5    0.118791    0.637  \n",
       "6   -0.014226    0.593  \n",
       "7    0.101842    0.633  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Concat 2016 data on, and then merge with num_nation and turnout data:\n",
    "gender_df = pd.concat([gender2004_df, gender2016_df])\n",
    "\n",
    "#Add in number in electorate\n",
    "electorate_df = national_df[['year','num_nation']]\n",
    "gender_df = gender_df.merge(electorate_df, on='year', how='left')\n",
    "\n",
    "#2004-2016 Turnout by Gender\n",
    "#It's possible to calculate this from ANES data, but it is really off.  See cell below\n",
    "#Source Current Population Survey data:\n",
    "#https://www.census.gov/data/tables/2004/demo/voting-and-registration/p20-562-rv.html\n",
    "#https://www.census.gov/data/tables/2008/demo/voting-and-registration/p20-562-rv.html\n",
    "#https://www.census.gov/data/tables/2012/demo/voting-and-registration/p20-568.html\n",
    "#https://www.census.gov/data/tables/2016/demo/voting-and-registration/p20-562-rv.html\n",
    "#All are percent of citizens voting\n",
    "gender_turnout = StringIO(\"\"\"\n",
    "year,group,turnout\n",
    "2004,male,62.1\n",
    "2004,female,65.4\n",
    "2008,male,61.5\n",
    "2008,female,65.7\n",
    "2012,male,59.7\n",
    "2012,female,63.7\n",
    "2016,male,59.3\n",
    "2016,female,63.3\n",
    "\"\"\")\n",
    "gender_turnout_df = pd.read_csv(gender_turnout, sep=',')\n",
    "gender_turnout_df = gender_turnout_df.apply(pd.to_numeric, errors='ignore', axis=0)\n",
    "gender_turnout_df['turnout'] = gender_turnout_df['turnout']/100\n",
    "gender_df = gender_df.merge(gender_turnout_df, on=['year','group'])\n",
    "\n",
    "gender_df['dem_margin'] = gender_df['dem_frac'] - gender_df['rep_frac']\n",
    "\n",
    "order = ['demographic','group', 'year','dem_frac','rep_frac','electorate_frac', 'num_nation', 'dem_margin', 'turnout']\n",
    "\n",
    "gender_df = gender_df[order]\n",
    "\n",
    "gender_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>weight_postfull</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>year</th>\n",
       "      <th>voted</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">2004</th>\n",
       "      <th>0</th>\n",
       "      <td>150.9553</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>248.7554</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>812.2912</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">2008</th>\n",
       "      <th>0</th>\n",
       "      <td>208.9927</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>473.0947</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1639.9759</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">2012</th>\n",
       "      <th>0</th>\n",
       "      <td>413.1968</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1218.2133</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4282.5888</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            weight_postfull\n",
       "year voted                 \n",
       "2004 0             150.9553\n",
       "     1             248.7554\n",
       "     2             812.2912\n",
       "2008 0             208.9927\n",
       "     1             473.0947\n",
       "     2            1639.9759\n",
       "2012 0             413.1968\n",
       "     1            1218.2133\n",
       "     2            4282.5888"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "turnout_df = baseanes2004_df.copy()\n",
    "turnout_df = turnout_df.groupby(['year', 'voted']).agg({'weight_postfull': 'sum'})\n",
    "turnout_df\n",
    "#This would put 2008 turnout at 70%\n",
    "#While this only has it at 62%\n",
    "#http://www.electproject.org/2008g\n",
    "#            VEP (all ballots) VEP   VAP\n",
    "#2004 United States\t60.7%\t60.1%\t55.4%\n",
    "#2008 United States\t62.2%\t61.6%\t56.9%\n",
    "#2012 United States\t58.6%\t58.0%\t53.6%\n",
    "#2016 United States\t60.2%\t59.3%\t54.7%\n",
    "\n",
    "# 2004 812/(812+248+150) = 67.17%\n",
    "# 2008 1639/(473+208+1639) = 70.6%\n",
    "# 2012 4282/(4282+1218+413) = 72.4%\n",
    "# So it does seem that raw surveys overestimate voter turnout substantially.\n",
    "# So stick with the adjusted CPS Turnout rates from the elections project,\n",
    "# and the demographic data from them as well when possible. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Age data from CPS, ANES, 2004-2016\n",
    "\n",
    "I obtain turnout and share of the electorate data frome CPS, the democratic margin by age from the ANES data,\n",
    "and the number of national voters from the cumulative county data.  \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/psthomas/miniconda2/envs/datascience3/lib/python3.5/site-packages/IPython/core/interactiveshell.py:2717: DtypeWarning: Columns (790,1129,1131) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>demographic</th>\n",
       "      <th>group</th>\n",
       "      <th>year</th>\n",
       "      <th>dem_frac</th>\n",
       "      <th>rep_frac</th>\n",
       "      <th>other_frac</th>\n",
       "      <th>electorate_frac</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>age</td>\n",
       "      <td>17 - 29</td>\n",
       "      <td>2016</td>\n",
       "      <td>0.548326</td>\n",
       "      <td>0.351484</td>\n",
       "      <td>0.100191</td>\n",
       "      <td>0.173320</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>age</td>\n",
       "      <td>30 - 44</td>\n",
       "      <td>2016</td>\n",
       "      <td>0.555207</td>\n",
       "      <td>0.345984</td>\n",
       "      <td>0.098809</td>\n",
       "      <td>0.245604</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>age</td>\n",
       "      <td>45 - 59</td>\n",
       "      <td>2016</td>\n",
       "      <td>0.446847</td>\n",
       "      <td>0.479965</td>\n",
       "      <td>0.073187</td>\n",
       "      <td>0.338233</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>age</td>\n",
       "      <td>60 +</td>\n",
       "      <td>2016</td>\n",
       "      <td>0.475273</td>\n",
       "      <td>0.494703</td>\n",
       "      <td>0.030024</td>\n",
       "      <td>0.242843</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  demographic    group  year  dem_frac  rep_frac  other_frac  electorate_frac\n",
       "0         age  17 - 29  2016  0.548326  0.351484    0.100191         0.173320\n",
       "1         age  30 - 44  2016  0.555207  0.345984    0.098809         0.245604\n",
       "2         age  45 - 59  2016  0.446847  0.479965    0.073187         0.338233\n",
       "3         age     60 +  2016  0.475273  0.494703    0.030024         0.242843"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Calculate 2016 Democratic Margin By Age Group\n",
    "## source: ANES time series cumulative:\n",
    "## http://www.electionstudies.org/studypages/anes_timeseries_cdf/anes_timeseries_cdf.htm\n",
    "\n",
    "# V161267x\n",
    "# Label: Item name:\n",
    "# Question:\n",
    "# PRE: SUMMARY - Respondent age group\n",
    "# Not applicable; administrative or derived variable\n",
    "# Not applicable\n",
    "# Unweighted Frequencies\n",
    "# ￼01. Age group 18-20 \n",
    "# 02. Age group 21-24 \n",
    "# 03. Age group 25-29 \n",
    "# 04. Age group 30-34 \n",
    "# 05. Age group 35-39 \n",
    "# 06. Age group 40-44 \n",
    "# 07. Age group 45-49 \n",
    "# 08. Age group 50-54 \n",
    "# 09. Age group 55-59 \n",
    "# 10. Age group 60-64 \n",
    "# 11. Age group 65-69 \n",
    "# 12. Age group 70-74\n",
    "# 13. Age group 75 or older\n",
    "# -1. Inapplicable\n",
    "\n",
    "anes2016_df = pd.read_csv('./data/anes/anes_timeseries_2016/anes_timeseries_2016_rawdata.txt',\n",
    "                      sep='|')\n",
    "\n",
    "columns = ['V160102', 'V161267x', 'V162058x']\n",
    "labels = ['weight_postfull', 'group', 'party']\n",
    "\n",
    "anes2016_df = anes2016_df[columns]\n",
    "anes2016_df.rename(columns=dict(zip(columns, labels)), inplace=True)\n",
    "\n",
    "anes2016_df = anes2016_df[anes2016_df['party'].isin([10,11,12])]  #Filter out nonvoters\n",
    "\n",
    "age2016_df = anes2016_df.groupby(['group', 'party']).agg({'weight_postfull':'sum'})\n",
    "age2016_df = age2016_df.unstack(level=-1)\n",
    "age2016_df.columns = age2016_df.columns.droplevel(0)\n",
    "age2016_df = age2016_df.rename_axis(None, axis=1)\n",
    "\n",
    "# pairs = [[1,2],[3,4],[5,6],[7,8],[9,10],[11,12]]\n",
    "# labels = {1: \"17 - 24\", 2: \"25 - 34\", 3: \"35 - 44\", 4: \"45 - 54\", 5: \"55 - 64\", 6: \"65 - 74\", 7: \"75 +\"}\n",
    "\n",
    "pairs = [[1,2,3],[4,5,6],[7,8,9],[10,11,12]]\n",
    "labels = {1: \"17 - 29\", 2: \"30 - 44\", 3: \"45 - 59\", 4: \"60 +\"}\n",
    "\n",
    "for i, pair in enumerate(pairs):\n",
    "    age2016_df.loc[labels[i+1]] = age2016_df.loc[pair].sum()\n",
    "    \n",
    "age2016_df = age2016_df.loc[labels.values()]\n",
    "\n",
    "\n",
    "age2016_df.reset_index(inplace=True)\n",
    "age2016_df['total'] = age2016_df[[10,11,12]].sum(axis=1)\n",
    "age2016_df.rename(columns={10:'d',11:'r',12:'o'}, inplace=True)\n",
    "\n",
    "age2016_df['dem_frac'] = age2016_df['d']/age2016_df['total']\n",
    "age2016_df['rep_frac'] = age2016_df['r']/age2016_df['total']\n",
    "age2016_df['other_frac'] = age2016_df['o']/age2016_df['total']\n",
    "\n",
    "#0 R votes among 'other'\n",
    "age2016_df.fillna(0, inplace=True)\n",
    "\n",
    "#Create electorate fraction\n",
    "age2016_df['electorate_frac'] = age2016_df['total'] / age2016_df['total'].sum()\n",
    "\n",
    "\n",
    "age2016_df['year'] = 2016\n",
    "age2016_df['demographic'] = 'age'\n",
    "\n",
    "\n",
    "age2016_df = age2016_df[['demographic', 'group','year','dem_frac', 'rep_frac',\n",
    "                               'other_frac','electorate_frac']]\n",
    "\n",
    "\n",
    "age2016_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>demographic</th>\n",
       "      <th>group</th>\n",
       "      <th>year</th>\n",
       "      <th>dem_frac</th>\n",
       "      <th>rep_frac</th>\n",
       "      <th>other_frac</th>\n",
       "      <th>electorate_frac</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>age</td>\n",
       "      <td>17 - 29</td>\n",
       "      <td>2004</td>\n",
       "      <td>0.429017</td>\n",
       "      <td>0.412320</td>\n",
       "      <td>0.158662</td>\n",
       "      <td>0.123634</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>age</td>\n",
       "      <td>30 - 44</td>\n",
       "      <td>2004</td>\n",
       "      <td>0.581622</td>\n",
       "      <td>0.373218</td>\n",
       "      <td>0.045159</td>\n",
       "      <td>0.255757</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>age</td>\n",
       "      <td>45 - 59</td>\n",
       "      <td>2004</td>\n",
       "      <td>0.420608</td>\n",
       "      <td>0.579392</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.322568</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>age</td>\n",
       "      <td>60 +</td>\n",
       "      <td>2004</td>\n",
       "      <td>0.445143</td>\n",
       "      <td>0.554857</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.298042</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>age</td>\n",
       "      <td>17 - 29</td>\n",
       "      <td>2008</td>\n",
       "      <td>0.671394</td>\n",
       "      <td>0.325719</td>\n",
       "      <td>0.002887</td>\n",
       "      <td>0.141188</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>age</td>\n",
       "      <td>30 - 44</td>\n",
       "      <td>2008</td>\n",
       "      <td>0.629155</td>\n",
       "      <td>0.350683</td>\n",
       "      <td>0.020162</td>\n",
       "      <td>0.268001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>age</td>\n",
       "      <td>45 - 59</td>\n",
       "      <td>2008</td>\n",
       "      <td>0.562251</td>\n",
       "      <td>0.423448</td>\n",
       "      <td>0.014301</td>\n",
       "      <td>0.250993</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>age</td>\n",
       "      <td>60 +</td>\n",
       "      <td>2008</td>\n",
       "      <td>0.504923</td>\n",
       "      <td>0.465264</td>\n",
       "      <td>0.029813</td>\n",
       "      <td>0.339818</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>age</td>\n",
       "      <td>17 - 29</td>\n",
       "      <td>2012</td>\n",
       "      <td>0.625693</td>\n",
       "      <td>0.338356</td>\n",
       "      <td>0.035951</td>\n",
       "      <td>0.139413</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>age</td>\n",
       "      <td>30 - 44</td>\n",
       "      <td>2012</td>\n",
       "      <td>0.571377</td>\n",
       "      <td>0.368016</td>\n",
       "      <td>0.060607</td>\n",
       "      <td>0.264247</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>age</td>\n",
       "      <td>45 - 59</td>\n",
       "      <td>2012</td>\n",
       "      <td>0.512499</td>\n",
       "      <td>0.447605</td>\n",
       "      <td>0.039897</td>\n",
       "      <td>0.295920</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>age</td>\n",
       "      <td>60 +</td>\n",
       "      <td>2012</td>\n",
       "      <td>0.526140</td>\n",
       "      <td>0.443622</td>\n",
       "      <td>0.030238</td>\n",
       "      <td>0.300420</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   demographic    group  year  dem_frac  rep_frac  other_frac  electorate_frac\n",
       "0          age  17 - 29  2004  0.429017  0.412320    0.158662         0.123634\n",
       "1          age  30 - 44  2004  0.581622  0.373218    0.045159         0.255757\n",
       "2          age  45 - 59  2004  0.420608  0.579392    0.000000         0.322568\n",
       "3          age     60 +  2004  0.445143  0.554857    0.000000         0.298042\n",
       "8          age  17 - 29  2008  0.671394  0.325719    0.002887         0.141188\n",
       "9          age  30 - 44  2008  0.629155  0.350683    0.020162         0.268001\n",
       "10         age  45 - 59  2008  0.562251  0.423448    0.014301         0.250993\n",
       "11         age     60 +  2008  0.504923  0.465264    0.029813         0.339818\n",
       "16         age  17 - 29  2012  0.625693  0.338356    0.035951         0.139413\n",
       "17         age  30 - 44  2012  0.571377  0.368016    0.060607         0.264247\n",
       "18         age  45 - 59  2012  0.512499  0.447605    0.039897         0.295920\n",
       "19         age     60 +  2012  0.526140  0.443622    0.030238         0.300420"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Calculate 2004-2012 Democratic Margin By Gender\n",
    "## source: ANES time series cumulative:\n",
    "## http://www.electionstudies.org/studypages/anes_timeseries_cdf/anes_timeseries_cdf.htm\n",
    "# VCF0102\n",
    "\n",
    "# DEMOGRAPHICS: Respondent - Age Group\n",
    "\n",
    "# QUESTION:\n",
    "# ---------\n",
    "# 1964-1976:  What is your date of birth?\n",
    "# 1978-1982:  What is the month and year of your birth?\n",
    "# 1984-LATER: What is the month, day and year of your birth?\n",
    "\n",
    "# VALID_CODES:\n",
    "# ------------\n",
    "# 1.  17 - 24\n",
    "# 2.  25 - 34\n",
    "# 3.  35 - 44\n",
    "# 4.  45 - 54\n",
    "# 5.  55 - 64\n",
    "# 6.  65 - 74\n",
    "# 7.  75 - 99 and over (except 1954)\n",
    "# MISSING_CODES:\n",
    "# --------------\n",
    "# 0.  NA; DK; RF; no Pre IW\n",
    "# INAP. Inap. question not used\n",
    "\n",
    "age2004_df = baseanes2004_df[baseanes2004_df['party'].isin([1,2,3])].copy()  #Filter out nonvoters\n",
    "age2004_df.rename(columns={'age':'group'},inplace=True)\n",
    "age2004_df = age2004_df.groupby(['year', 'group', 'party']).agg({'weight_postfull':'sum'})\n",
    "age2004_df = age2004_df.unstack(level=-1)\n",
    "age2004_df.columns = age2004_df.columns.droplevel(0)\n",
    "age2004_df = age2004_df.rename_axis(None, axis=1)\n",
    "\n",
    "age2004_df.fillna(0, inplace=True)\n",
    "age2004_df.reset_index(inplace=True)\n",
    "\n",
    "#Interpolate to get correct age ranges\n",
    "labels = {1: \"17 - 29\", 2: \"30 - 44\", 3: \"45 - 59\", 4: \"60 +\"}\n",
    "age2004_df.loc[\"17 - 29\"] = age2004_df.loc[1] + 0.5*age2004_df.loc[2]\n",
    "age2004_df.loc[\"30 - 44\"] = 0.5*age2004_df.loc[2] + age2004_df.loc[3]\n",
    "age2004_df.loc[\"45 - 59\"] = age2004_df.loc[4] + 0.5*age2004_df.loc[5]\n",
    "age2004_df.loc[\"60 +\"] = 0.5*age2004_df.loc[5] + age2004_df.loc[6] + age2004_df.loc[7]\n",
    "\n",
    "age2004_df['group'] = age2004_df['group'].replace(labels)\n",
    "age2004_df = age2004_df[age2004_df['group'].isin(labels.values())]\n",
    "age2004_df['total'] = age2004_df[[1,2,3]].sum(axis=1)\n",
    "age2004_df.rename(columns={1:'d',2:'r',3:'o'}, inplace=True)\n",
    "\n",
    "#Divide the number of respondents for each gender by total gender respondents\n",
    "#to get fraction of electorate\n",
    "sum_df = age2004_df.groupby('year')['total'].sum()\n",
    "def electorate_frac(row):\n",
    "    row['electorate_frac'] = row['total']/sum_df[row['year']]\n",
    "    return row \n",
    "age2004_df = age2004_df.apply(electorate_frac, axis=1)\n",
    "\n",
    "age2004_df['dem_frac'] = age2004_df['d']/age2004_df['total']\n",
    "age2004_df['rep_frac'] = age2004_df['r']/age2004_df['total']\n",
    "age2004_df['other_frac'] = age2004_df['o']/age2004_df['total']\n",
    "\n",
    "age2004_df['demographic'] = 'age'\n",
    "age2004_df = age2004_df[['demographic','group','year','dem_frac', 'rep_frac', \n",
    "                               'other_frac', 'electorate_frac']]\n",
    "\n",
    "age2004_df['year'] = age2004_df['year'].astype(int)\n",
    "\n",
    "age2004_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>demographic</th>\n",
       "      <th>group</th>\n",
       "      <th>year</th>\n",
       "      <th>dem_frac</th>\n",
       "      <th>rep_frac</th>\n",
       "      <th>electorate_frac</th>\n",
       "      <th>num_nation</th>\n",
       "      <th>dem_margin</th>\n",
       "      <th>turnout</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>age</td>\n",
       "      <td>17 - 29</td>\n",
       "      <td>2004</td>\n",
       "      <td>0.429017</td>\n",
       "      <td>0.412320</td>\n",
       "      <td>0.123634</td>\n",
       "      <td>122217180</td>\n",
       "      <td>0.016697</td>\n",
       "      <td>0.490</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>age</td>\n",
       "      <td>30 - 44</td>\n",
       "      <td>2004</td>\n",
       "      <td>0.581622</td>\n",
       "      <td>0.373218</td>\n",
       "      <td>0.255757</td>\n",
       "      <td>122217180</td>\n",
       "      <td>0.208404</td>\n",
       "      <td>0.624</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>age</td>\n",
       "      <td>45 - 59</td>\n",
       "      <td>2004</td>\n",
       "      <td>0.420608</td>\n",
       "      <td>0.579392</td>\n",
       "      <td>0.322568</td>\n",
       "      <td>122217180</td>\n",
       "      <td>-0.158784</td>\n",
       "      <td>0.700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>age</td>\n",
       "      <td>60 +</td>\n",
       "      <td>2004</td>\n",
       "      <td>0.445143</td>\n",
       "      <td>0.554857</td>\n",
       "      <td>0.298042</td>\n",
       "      <td>122217180</td>\n",
       "      <td>-0.109714</td>\n",
       "      <td>0.714</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>age</td>\n",
       "      <td>17 - 29</td>\n",
       "      <td>2008</td>\n",
       "      <td>0.671394</td>\n",
       "      <td>0.325719</td>\n",
       "      <td>0.141188</td>\n",
       "      <td>131049863</td>\n",
       "      <td>0.345674</td>\n",
       "      <td>0.511</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>age</td>\n",
       "      <td>30 - 44</td>\n",
       "      <td>2008</td>\n",
       "      <td>0.629155</td>\n",
       "      <td>0.350683</td>\n",
       "      <td>0.268001</td>\n",
       "      <td>131049863</td>\n",
       "      <td>0.278472</td>\n",
       "      <td>0.618</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>age</td>\n",
       "      <td>45 - 59</td>\n",
       "      <td>2008</td>\n",
       "      <td>0.562251</td>\n",
       "      <td>0.423448</td>\n",
       "      <td>0.250993</td>\n",
       "      <td>131049863</td>\n",
       "      <td>0.138803</td>\n",
       "      <td>0.685</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>age</td>\n",
       "      <td>60 +</td>\n",
       "      <td>2008</td>\n",
       "      <td>0.504923</td>\n",
       "      <td>0.465264</td>\n",
       "      <td>0.339818</td>\n",
       "      <td>131049863</td>\n",
       "      <td>0.039659</td>\n",
       "      <td>0.708</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>age</td>\n",
       "      <td>17 - 29</td>\n",
       "      <td>2012</td>\n",
       "      <td>0.625693</td>\n",
       "      <td>0.338356</td>\n",
       "      <td>0.139413</td>\n",
       "      <td>129254383</td>\n",
       "      <td>0.287337</td>\n",
       "      <td>0.450</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>age</td>\n",
       "      <td>30 - 44</td>\n",
       "      <td>2012</td>\n",
       "      <td>0.571377</td>\n",
       "      <td>0.368016</td>\n",
       "      <td>0.264247</td>\n",
       "      <td>129254383</td>\n",
       "      <td>0.203361</td>\n",
       "      <td>0.595</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>age</td>\n",
       "      <td>45 - 59</td>\n",
       "      <td>2012</td>\n",
       "      <td>0.512499</td>\n",
       "      <td>0.447605</td>\n",
       "      <td>0.295920</td>\n",
       "      <td>129254383</td>\n",
       "      <td>0.064894</td>\n",
       "      <td>0.669</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>age</td>\n",
       "      <td>60 +</td>\n",
       "      <td>2012</td>\n",
       "      <td>0.526140</td>\n",
       "      <td>0.443622</td>\n",
       "      <td>0.300420</td>\n",
       "      <td>129254383</td>\n",
       "      <td>0.082518</td>\n",
       "      <td>0.718</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>age</td>\n",
       "      <td>17 - 29</td>\n",
       "      <td>2016</td>\n",
       "      <td>0.548326</td>\n",
       "      <td>0.351484</td>\n",
       "      <td>0.173320</td>\n",
       "      <td>136670626</td>\n",
       "      <td>0.196842</td>\n",
       "      <td>0.461</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>age</td>\n",
       "      <td>30 - 44</td>\n",
       "      <td>2016</td>\n",
       "      <td>0.555207</td>\n",
       "      <td>0.345984</td>\n",
       "      <td>0.245604</td>\n",
       "      <td>136670626</td>\n",
       "      <td>0.209222</td>\n",
       "      <td>0.588</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>age</td>\n",
       "      <td>45 - 59</td>\n",
       "      <td>2016</td>\n",
       "      <td>0.446847</td>\n",
       "      <td>0.479965</td>\n",
       "      <td>0.338233</td>\n",
       "      <td>136670626</td>\n",
       "      <td>-0.033118</td>\n",
       "      <td>0.659</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>age</td>\n",
       "      <td>60 +</td>\n",
       "      <td>2016</td>\n",
       "      <td>0.475273</td>\n",
       "      <td>0.494703</td>\n",
       "      <td>0.242843</td>\n",
       "      <td>136670626</td>\n",
       "      <td>-0.019430</td>\n",
       "      <td>0.703</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   demographic    group  year  dem_frac  rep_frac  electorate_frac  \\\n",
       "0          age  17 - 29  2004  0.429017  0.412320         0.123634   \n",
       "1          age  30 - 44  2004  0.581622  0.373218         0.255757   \n",
       "2          age  45 - 59  2004  0.420608  0.579392         0.322568   \n",
       "3          age     60 +  2004  0.445143  0.554857         0.298042   \n",
       "4          age  17 - 29  2008  0.671394  0.325719         0.141188   \n",
       "5          age  30 - 44  2008  0.629155  0.350683         0.268001   \n",
       "6          age  45 - 59  2008  0.562251  0.423448         0.250993   \n",
       "7          age     60 +  2008  0.504923  0.465264         0.339818   \n",
       "8          age  17 - 29  2012  0.625693  0.338356         0.139413   \n",
       "9          age  30 - 44  2012  0.571377  0.368016         0.264247   \n",
       "10         age  45 - 59  2012  0.512499  0.447605         0.295920   \n",
       "11         age     60 +  2012  0.526140  0.443622         0.300420   \n",
       "12         age  17 - 29  2016  0.548326  0.351484         0.173320   \n",
       "13         age  30 - 44  2016  0.555207  0.345984         0.245604   \n",
       "14         age  45 - 59  2016  0.446847  0.479965         0.338233   \n",
       "15         age     60 +  2016  0.475273  0.494703         0.242843   \n",
       "\n",
       "    num_nation  dem_margin  turnout  \n",
       "0    122217180    0.016697    0.490  \n",
       "1    122217180    0.208404    0.624  \n",
       "2    122217180   -0.158784    0.700  \n",
       "3    122217180   -0.109714    0.714  \n",
       "4    131049863    0.345674    0.511  \n",
       "5    131049863    0.278472    0.618  \n",
       "6    131049863    0.138803    0.685  \n",
       "7    131049863    0.039659    0.708  \n",
       "8    129254383    0.287337    0.450  \n",
       "9    129254383    0.203361    0.595  \n",
       "10   129254383    0.064894    0.669  \n",
       "11   129254383    0.082518    0.718  \n",
       "12   136670626    0.196842    0.461  \n",
       "13   136670626    0.209222    0.588  \n",
       "14   136670626   -0.033118    0.659  \n",
       "15   136670626   -0.019430    0.703  "
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Turnout by age, CPS\n",
    "#Source: http://www.electproject.org/home/voter-turnout/demographics\n",
    "# Corrected for response bias\n",
    "# age_turnout = StringIO(\"\"\"\n",
    "#     2016\t2012\t2008\t2004\n",
    "# 18-29\t43.4\t40.9\t48.4\t45.0\n",
    "# 30-44\t56.9\t56.2\t60.7\t59.5\n",
    "# 45-59\t66.2\t66.0\t69.5\t69.0\n",
    "# 60+\t71.4\t71.2\t71.0\t69.7\n",
    "# \"\"\")\n",
    "# Uncorrected for response bias\n",
    "age_turnout = StringIO(\"\"\"\n",
    "group\t2016\t2012\t2008\t2004\n",
    "17 - 29\t46.1\t45.0\t51.1\t49.0\n",
    "30 - 44\t58.8\t59.5\t61.8\t62.4\n",
    "45 - 59\t65.9\t66.9\t68.5\t70.0\n",
    "60 +\t70.3\t71.8\t70.8\t71.4\n",
    "\"\"\")\n",
    "\n",
    "ageturnout_df = pd.read_csv(age_turnout, sep='\\t')\n",
    "ageturnout_df = pd.melt(ageturnout_df, id_vars=[\"group\"], \n",
    "                  var_name=\"year\", value_name=\"turnout\")\n",
    "ageturnout_df = ageturnout_df.apply(pd.to_numeric, errors='ignore', axis=0)\n",
    "ageturnout_df['turnout'] = ageturnout_df['turnout']/100\n",
    "\n",
    "age_df = pd.concat([age2004_df, age2016_df])\n",
    "age_df = age_df.merge(ageturnout_df, on=['group','year'])\n",
    "\n",
    "#Add in number in electorate\n",
    "electorate_df = national_df[['year','num_nation']]\n",
    "age_df = age_df.merge(electorate_df, on='year', how='left')\n",
    "\n",
    "age_df['dem_margin'] = age_df['dem_frac'] - age_df['rep_frac']\n",
    "\n",
    "order = ['demographic','group', 'year','dem_frac','rep_frac','electorate_frac', 'num_nation', 'dem_margin', 'turnout']\n",
    "age_df = age_df[order]\n",
    "\n",
    "age_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Education Data from CPS, ANES 2004-2016"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>group</th>\n",
       "      <th>year</th>\n",
       "      <th>turnout</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Less Than High School</td>\n",
       "      <td>2016</td>\n",
       "      <td>0.343000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>High School Grad</td>\n",
       "      <td>2016</td>\n",
       "      <td>0.516000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Some College to Post-Graduate</td>\n",
       "      <td>2016</td>\n",
       "      <td>0.705875</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Less Than High School</td>\n",
       "      <td>2012</td>\n",
       "      <td>0.380000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>High School Grad</td>\n",
       "      <td>2012</td>\n",
       "      <td>0.526000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Some College to Post-Graduate</td>\n",
       "      <td>2012</td>\n",
       "      <td>0.712990</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Less Than High School</td>\n",
       "      <td>2008</td>\n",
       "      <td>0.394000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>High School Grad</td>\n",
       "      <td>2008</td>\n",
       "      <td>0.549000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Some College to Post-Graduate</td>\n",
       "      <td>2008</td>\n",
       "      <td>0.739976</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Less Than High School</td>\n",
       "      <td>2004</td>\n",
       "      <td>0.395000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>High School Grad</td>\n",
       "      <td>2004</td>\n",
       "      <td>0.564000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Some College to Post-Graduate</td>\n",
       "      <td>2004</td>\n",
       "      <td>0.748760</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            group  year   turnout\n",
       "0           Less Than High School  2016  0.343000\n",
       "1                High School Grad  2016  0.516000\n",
       "2   Some College to Post-Graduate  2016  0.705875\n",
       "3           Less Than High School  2012  0.380000\n",
       "4                High School Grad  2012  0.526000\n",
       "5   Some College to Post-Graduate  2012  0.712990\n",
       "6           Less Than High School  2008  0.394000\n",
       "7                High School Grad  2008  0.549000\n",
       "8   Some College to Post-Graduate  2008  0.739976\n",
       "9           Less Than High School  2004  0.395000\n",
       "10               High School Grad  2004  0.564000\n",
       "11  Some College to Post-Graduate  2004  0.748760"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Turnout by education, CPS\n",
    "#Source: http://www.electproject.org/home/voter-turnout/demographics\n",
    "# Corrected for response bias\n",
    "# Uncorrected for response bias\n",
    "age_turnout = StringIO(\"\"\"\n",
    "group\t2016\t2012\t2008\t2004\n",
    "Less Than High School\t34.3\t38.0\t39.4\t39.5\n",
    "High School Grad\t51.6\t52.6\t54.9\t56.4\n",
    "Some College to College Grad\t67.8\t68.4\t71.5\t72.2\n",
    "Post-Graduate\t80.3\t81.4\t82.7\t84.2\n",
    "\"\"\")\n",
    "\n",
    "edturnout_df = pd.read_csv(age_turnout, sep='\\t')\n",
    "#Just interpolate Some College to Post-Graduate to deal with lack of data\n",
    "#in ANES survey:\n",
    "#Either this, or do weighted average later, but it's impossible to do weighted\n",
    "#average for 2004 to 2012 because you don't know the correct subpopulation.\n",
    "#Also note that \"Less Than High School\" and \"High School Grad\" leaves out those\n",
    "#that started high school and didn't finish. . . \n",
    "#Weight instead based on US overall values:\n",
    "#https://en.wikipedia.org/wiki/Educational_attainment_in_the_United_States\n",
    "#Postgrad: 11.77+3.27+1.77 = 16.81%\n",
    "#Some college: 58.57%\n",
    "edturnout_df.set_index('group', inplace=True)\n",
    "#Simple average overestimates:\n",
    "# edturnout_df.loc['Some College to Post-Graduate'] = (edturnout_df.loc['Some College to College Grad'] + \n",
    "#                                                      edturnout_df.loc['Post-Graduate'])/2\n",
    "#Weighted average:\n",
    "edturnout_df.loc['Some College to Post-Graduate'] = (edturnout_df.loc['Some College to College Grad']*0.5857 + \n",
    "                                                     edturnout_df.loc['Post-Graduate']*0.1681)/(0.5857+0.1681)\n",
    "edturnout_df.reset_index(inplace=True)\n",
    "edturnout_df = edturnout_df.loc[[0,1,4]]\n",
    "edturnout_df = pd.melt(edturnout_df, id_vars=[\"group\"], \n",
    "                  var_name=\"year\", value_name=\"turnout\")\n",
    "edturnout_df = edturnout_df.apply(pd.to_numeric, errors='ignore', axis=0)\n",
    "edturnout_df['turnout'] = edturnout_df['turnout']/100\n",
    "\n",
    "\n",
    "\n",
    "edturnout_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>demographic</th>\n",
       "      <th>group</th>\n",
       "      <th>year</th>\n",
       "      <th>dem_frac</th>\n",
       "      <th>rep_frac</th>\n",
       "      <th>other_frac</th>\n",
       "      <th>electorate_frac</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>education</td>\n",
       "      <td>High School Grad</td>\n",
       "      <td>2004</td>\n",
       "      <td>0.393690</td>\n",
       "      <td>0.580143</td>\n",
       "      <td>0.026167</td>\n",
       "      <td>0.296605</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>education</td>\n",
       "      <td>Less Than High School</td>\n",
       "      <td>2004</td>\n",
       "      <td>0.652207</td>\n",
       "      <td>0.347793</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.047057</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>education</td>\n",
       "      <td>Some College to Post-Graduate</td>\n",
       "      <td>2004</td>\n",
       "      <td>0.479749</td>\n",
       "      <td>0.501125</td>\n",
       "      <td>0.019126</td>\n",
       "      <td>0.656338</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>education</td>\n",
       "      <td>High School Grad</td>\n",
       "      <td>2008</td>\n",
       "      <td>0.582714</td>\n",
       "      <td>0.407605</td>\n",
       "      <td>0.009682</td>\n",
       "      <td>0.340335</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>education</td>\n",
       "      <td>Less Than High School</td>\n",
       "      <td>2008</td>\n",
       "      <td>0.758134</td>\n",
       "      <td>0.241866</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.014612</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>education</td>\n",
       "      <td>Some College to Post-Graduate</td>\n",
       "      <td>2008</td>\n",
       "      <td>0.508247</td>\n",
       "      <td>0.466495</td>\n",
       "      <td>0.025258</td>\n",
       "      <td>0.641328</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>education</td>\n",
       "      <td>High School Grad</td>\n",
       "      <td>2012</td>\n",
       "      <td>0.540501</td>\n",
       "      <td>0.439836</td>\n",
       "      <td>0.019663</td>\n",
       "      <td>0.331596</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>education</td>\n",
       "      <td>Less Than High School</td>\n",
       "      <td>2012</td>\n",
       "      <td>0.690355</td>\n",
       "      <td>0.281942</td>\n",
       "      <td>0.027703</td>\n",
       "      <td>0.013457</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>education</td>\n",
       "      <td>Some College to Post-Graduate</td>\n",
       "      <td>2012</td>\n",
       "      <td>0.503933</td>\n",
       "      <td>0.458739</td>\n",
       "      <td>0.037328</td>\n",
       "      <td>0.644741</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   demographic                          group  year  dem_frac  rep_frac  \\\n",
       "0    education               High School Grad  2004  0.393690  0.580143   \n",
       "1    education          Less Than High School  2004  0.652207  0.347793   \n",
       "2    education  Some College to Post-Graduate  2004  0.479749  0.501125   \n",
       "4    education               High School Grad  2008  0.582714  0.407605   \n",
       "5    education          Less Than High School  2008  0.758134  0.241866   \n",
       "6    education  Some College to Post-Graduate  2008  0.508247  0.466495   \n",
       "8    education               High School Grad  2012  0.540501  0.439836   \n",
       "9    education          Less Than High School  2012  0.690355  0.281942   \n",
       "10   education  Some College to Post-Graduate  2012  0.503933  0.458739   \n",
       "\n",
       "    other_frac  electorate_frac  \n",
       "0     0.026167         0.296605  \n",
       "1     0.000000         0.047057  \n",
       "2     0.019126         0.656338  \n",
       "4     0.009682         0.340335  \n",
       "5     0.000000         0.014612  \n",
       "6     0.025258         0.641328  \n",
       "8     0.019663         0.331596  \n",
       "9     0.027703         0.013457  \n",
       "10    0.037328         0.644741  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Calculate 2004-2012 Democratic Margin By Education\n",
    "## source: ANES time series cumulative:\n",
    "## http://www.electionstudies.org/studypages/anes_timeseries_cdf/anes_timeseries_cdf.htm\n",
    "\n",
    "# VCF0110\n",
    "# DEMOGRAPHICS: Respondent - Education 4-category\n",
    "\n",
    "# VALID_CODES:\n",
    "# ------------\n",
    "# 1.  Grade school or less (0-8 grades)\n",
    "# 2.  High school (12 grades or fewer, incl. non-college\n",
    "#       training if applicable)\n",
    "# 3.  Some college (13 grades or more but no degree; \n",
    "#       1948 ONLY: college, no identification of degree\n",
    "#       status)\n",
    "# 4.  College or advanced degree (no cases 1948)\n",
    "\n",
    "#https://nces.ed.gov/fastfacts/display.asp?id=40\n",
    "#The 6-year graduation rate for first-time, full-time undergraduate students \n",
    "#who began seeking a bachelor's degree at a 4-year degree-granting institution \n",
    "#in fall 2009 was 59 percent.\n",
    "# so (finish_college + advanced) = starters*0.59\n",
    "# some_college = 0.41*starters\n",
    "\n",
    "ed2004_df = baseanes2004_df[baseanes2004_df['party'].isin([1,2,3])].copy()  #Filter out nonvoters\n",
    "ed2004_df.rename(columns={'education':'group'},inplace=True)\n",
    "ed2004_df = ed2004_df.groupby(['year', 'group', 'party']).agg({'weight_postfull':'sum'})\n",
    "ed2004_df = ed2004_df.unstack(level=-1)\n",
    "ed2004_df.columns = ed2004_df.columns.droplevel(0)\n",
    "ed2004_df = ed2004_df.rename_axis(None, axis=1)\n",
    "ed2004_df.fillna(0, inplace=True)\n",
    "ed2004_df.reset_index(inplace=True)\n",
    "\n",
    "#Interpolate to get correct age ranges\n",
    "labels = {1: \"Less Than High School\", 2: \"High School Grad\",\n",
    "          3: \"Some College to Post-Graduate\",\n",
    "          4: \"Some College to Post-Graduate\"}\n",
    "ed2004_df['group'].replace(labels, inplace=True)\n",
    "ed2004_df = ed2004_df.groupby(['year','group']).agg('sum')\n",
    "ed2004_df.reset_index(inplace=True)\n",
    "\n",
    "ed2004_df['total'] = ed2004_df[[1,2,3]].sum(axis=1)\n",
    "ed2004_df.rename(columns={1:'d',2:'r',3:'o'}, inplace=True)\n",
    "\n",
    "#Divide the number of respondents for each group by total gender respondents\n",
    "#to get fraction of electorate\n",
    "sum_df = ed2004_df.groupby('year')['total'].sum()\n",
    "def electorate_frac(row):\n",
    "    row['electorate_frac'] = row['total']/sum_df[row['year']]\n",
    "    return row \n",
    "ed2004_df = ed2004_df.apply(electorate_frac, axis=1)\n",
    "ed2004_df = ed2004_df[ed2004_df['group'].isin(labels.values())]\n",
    "\n",
    "\n",
    "ed2004_df['dem_frac'] = ed2004_df['d']/ed2004_df['total']\n",
    "ed2004_df['rep_frac'] = ed2004_df['r']/ed2004_df['total']\n",
    "ed2004_df['other_frac'] = ed2004_df['o']/ed2004_df['total']\n",
    "\n",
    "ed2004_df['demographic'] = 'education'\n",
    "ed2004_df = ed2004_df[['demographic','group','year','dem_frac', 'rep_frac', \n",
    "                               'other_frac', 'electorate_frac']]\n",
    "\n",
    "ed2004_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/psthomas/miniconda2/envs/datascience3/lib/python3.5/site-packages/IPython/core/interactiveshell.py:2717: DtypeWarning: Columns (790,1129,1131) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>demographic</th>\n",
       "      <th>group</th>\n",
       "      <th>year</th>\n",
       "      <th>dem_frac</th>\n",
       "      <th>rep_frac</th>\n",
       "      <th>other_frac</th>\n",
       "      <th>electorate_frac</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>education</td>\n",
       "      <td>Less Than High School</td>\n",
       "      <td>2016</td>\n",
       "      <td>0.722333</td>\n",
       "      <td>0.277667</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.008721</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>education</td>\n",
       "      <td>High School Grad</td>\n",
       "      <td>2016</td>\n",
       "      <td>0.450025</td>\n",
       "      <td>0.486963</td>\n",
       "      <td>0.063012</td>\n",
       "      <td>0.305485</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>education</td>\n",
       "      <td>Some College to Post-Graduate</td>\n",
       "      <td>2016</td>\n",
       "      <td>0.505691</td>\n",
       "      <td>0.422492</td>\n",
       "      <td>0.071817</td>\n",
       "      <td>0.685794</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   demographic                          group  year  dem_frac  rep_frac  \\\n",
       "19   education          Less Than High School  2016  0.722333  0.277667   \n",
       "20   education               High School Grad  2016  0.450025  0.486963   \n",
       "21   education  Some College to Post-Graduate  2016  0.505691  0.422492   \n",
       "\n",
       "    other_frac  electorate_frac  \n",
       "19    0.000000         0.008721  \n",
       "20    0.063012         0.305485  \n",
       "21    0.071817         0.685794  "
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Calculate 2016 Democratic Margin By Education\n",
    "## source: ANES time series cumulative:\n",
    "## http://www.electionstudies.org/studypages/anes_timeseries_cdf/anes_timeseries_cdf.htm\n",
    "# V161270\n",
    "# What is the highest level of school you have completed or the highest degree you have received?\n",
    "\n",
    "# FTF Web Total\n",
    "# 13. Bachelor’s degree (for example: BA, AB, BS)\n",
    "# 10. Some college but no degree \n",
    "# 9. High school graduate- high school diploma or equivalent (for example: GED) \n",
    "# 14. Master’s degree (for example: MA, MS, MENG, MED, MSW, MBA) \n",
    "# 11. Associate degree in college - occupational/vocational\n",
    "# program \n",
    "# 12. Associate degree in college – academic program\n",
    "# 8. 12th grade no diploma \n",
    "# 16. Doctorate degree (for example: PHD, EDD) \n",
    "# 15. Professional school degree (for example: MD, DDS, DVM, LLB, JD)\n",
    "# 7. 11th grade \n",
    "# 6. 10th grade \n",
    "# 5. 9th grade \n",
    "# 95. Other SPECIFY \n",
    "# 4. 7th or 8th grade \n",
    "# -9. Refused\n",
    "anes2016_df = pd.read_csv('./data/anes/anes_timeseries_2016/anes_timeseries_2016_rawdata.txt',\n",
    "                      sep='|')\n",
    "\n",
    "columns = ['V160102', 'V161270', 'V162058x']\n",
    "labels = ['weight_postfull', 'group', 'party']\n",
    "anes2016_df = anes2016_df[columns]\n",
    "anes2016_df.rename(columns=dict(zip(columns, labels)), inplace=True)\n",
    "anes2016_df = anes2016_df[anes2016_df['party'].isin([10,11,12])]  #Filter out nonvoters\n",
    "\n",
    "ed2016_df = anes2016_df.groupby(['group', 'party']).agg({'weight_postfull':'sum'})\n",
    "ed2016_df = ed2016_df.unstack(level=-1)\n",
    "ed2016_df.columns = ed2016_df.columns.droplevel(0)\n",
    "ed2016_df = ed2016_df.rename_axis(None, axis=1)\n",
    "ed2016_df.fillna(0, inplace=True)\n",
    "\n",
    "\n",
    "pairs = [[1,2,3,4],[5,6,7,8,9],[10,11,12,13,14,15,16]]\n",
    "#Note, I'm including any with some high school in the \"High School Grad\" category\n",
    "#because they don't fit anywhere else. . . \n",
    "labels = {1: \"Less Than High School\", 2: \"High School Grad\",\n",
    "          3: \"Some College to Post-Graduate\"}\n",
    "for i, pair in enumerate(pairs):\n",
    "    ed2016_df.loc[labels[i+1]] = ed2016_df.loc[pair].sum()\n",
    "\n",
    "ed2016_df.reset_index(inplace=True)\n",
    "#Rest are included in sums, so you can remove these:\n",
    "ed2016_df =  ed2016_df[ed2016_df['group'].isin(labels.values())]\n",
    "ed2016_df['total'] = ed2016_df[[10,11,12]].sum(axis=1)\n",
    "ed2016_df.rename(columns={10:'d',11:'r',12:'o'}, inplace=True)\n",
    "ed2016_df['dem_frac'] = ed2016_df['d']/ed2016_df['total']\n",
    "ed2016_df['rep_frac'] = ed2016_df['r']/ed2016_df['total']\n",
    "ed2016_df['other_frac'] = ed2016_df['o']/ed2016_df['total']\n",
    "ed2016_df.fillna(0, inplace=True)\n",
    "\n",
    "#Create electorate fraction\n",
    "ed2016_df['electorate_frac'] = ed2016_df['total'] / ed2016_df['total'].sum()\n",
    "ed2016_df['year'] = 2016\n",
    "ed2016_df['demographic'] = 'education'\n",
    "\n",
    "\n",
    "ed2016_df = ed2016_df[['demographic', 'group','year','dem_frac', 'rep_frac',\n",
    "                               'other_frac','electorate_frac']]\n",
    "\n",
    "\n",
    "ed2016_df\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>demographic</th>\n",
       "      <th>group</th>\n",
       "      <th>year</th>\n",
       "      <th>dem_frac</th>\n",
       "      <th>rep_frac</th>\n",
       "      <th>electorate_frac</th>\n",
       "      <th>num_nation</th>\n",
       "      <th>dem_margin</th>\n",
       "      <th>turnout</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>education</td>\n",
       "      <td>High School Grad</td>\n",
       "      <td>2004</td>\n",
       "      <td>0.393690</td>\n",
       "      <td>0.580143</td>\n",
       "      <td>0.296605</td>\n",
       "      <td>122217180</td>\n",
       "      <td>-0.186452</td>\n",
       "      <td>0.564000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>education</td>\n",
       "      <td>Less Than High School</td>\n",
       "      <td>2004</td>\n",
       "      <td>0.652207</td>\n",
       "      <td>0.347793</td>\n",
       "      <td>0.047057</td>\n",
       "      <td>122217180</td>\n",
       "      <td>0.304414</td>\n",
       "      <td>0.395000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>education</td>\n",
       "      <td>Some College to Post-Graduate</td>\n",
       "      <td>2004</td>\n",
       "      <td>0.479749</td>\n",
       "      <td>0.501125</td>\n",
       "      <td>0.656338</td>\n",
       "      <td>122217180</td>\n",
       "      <td>-0.021377</td>\n",
       "      <td>0.748760</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>education</td>\n",
       "      <td>High School Grad</td>\n",
       "      <td>2008</td>\n",
       "      <td>0.582714</td>\n",
       "      <td>0.407605</td>\n",
       "      <td>0.340335</td>\n",
       "      <td>131049863</td>\n",
       "      <td>0.175109</td>\n",
       "      <td>0.549000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>education</td>\n",
       "      <td>Less Than High School</td>\n",
       "      <td>2008</td>\n",
       "      <td>0.758134</td>\n",
       "      <td>0.241866</td>\n",
       "      <td>0.014612</td>\n",
       "      <td>131049863</td>\n",
       "      <td>0.516269</td>\n",
       "      <td>0.394000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>education</td>\n",
       "      <td>Some College to Post-Graduate</td>\n",
       "      <td>2008</td>\n",
       "      <td>0.508247</td>\n",
       "      <td>0.466495</td>\n",
       "      <td>0.641328</td>\n",
       "      <td>131049863</td>\n",
       "      <td>0.041751</td>\n",
       "      <td>0.739976</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>education</td>\n",
       "      <td>High School Grad</td>\n",
       "      <td>2012</td>\n",
       "      <td>0.540501</td>\n",
       "      <td>0.439836</td>\n",
       "      <td>0.331596</td>\n",
       "      <td>129254383</td>\n",
       "      <td>0.100665</td>\n",
       "      <td>0.526000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>education</td>\n",
       "      <td>Less Than High School</td>\n",
       "      <td>2012</td>\n",
       "      <td>0.690355</td>\n",
       "      <td>0.281942</td>\n",
       "      <td>0.013457</td>\n",
       "      <td>129254383</td>\n",
       "      <td>0.408414</td>\n",
       "      <td>0.380000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>education</td>\n",
       "      <td>Some College to Post-Graduate</td>\n",
       "      <td>2012</td>\n",
       "      <td>0.503933</td>\n",
       "      <td>0.458739</td>\n",
       "      <td>0.644741</td>\n",
       "      <td>129254383</td>\n",
       "      <td>0.045194</td>\n",
       "      <td>0.712990</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>education</td>\n",
       "      <td>Less Than High School</td>\n",
       "      <td>2016</td>\n",
       "      <td>0.722333</td>\n",
       "      <td>0.277667</td>\n",
       "      <td>0.008721</td>\n",
       "      <td>136670626</td>\n",
       "      <td>0.444665</td>\n",
       "      <td>0.343000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>education</td>\n",
       "      <td>High School Grad</td>\n",
       "      <td>2016</td>\n",
       "      <td>0.450025</td>\n",
       "      <td>0.486963</td>\n",
       "      <td>0.305485</td>\n",
       "      <td>136670626</td>\n",
       "      <td>-0.036938</td>\n",
       "      <td>0.516000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>education</td>\n",
       "      <td>Some College to Post-Graduate</td>\n",
       "      <td>2016</td>\n",
       "      <td>0.505691</td>\n",
       "      <td>0.422492</td>\n",
       "      <td>0.685794</td>\n",
       "      <td>136670626</td>\n",
       "      <td>0.083199</td>\n",
       "      <td>0.705875</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   demographic                          group  year  dem_frac  rep_frac  \\\n",
       "0    education               High School Grad  2004  0.393690  0.580143   \n",
       "1    education          Less Than High School  2004  0.652207  0.347793   \n",
       "2    education  Some College to Post-Graduate  2004  0.479749  0.501125   \n",
       "3    education               High School Grad  2008  0.582714  0.407605   \n",
       "4    education          Less Than High School  2008  0.758134  0.241866   \n",
       "5    education  Some College to Post-Graduate  2008  0.508247  0.466495   \n",
       "6    education               High School Grad  2012  0.540501  0.439836   \n",
       "7    education          Less Than High School  2012  0.690355  0.281942   \n",
       "8    education  Some College to Post-Graduate  2012  0.503933  0.458739   \n",
       "9    education          Less Than High School  2016  0.722333  0.277667   \n",
       "10   education               High School Grad  2016  0.450025  0.486963   \n",
       "11   education  Some College to Post-Graduate  2016  0.505691  0.422492   \n",
       "\n",
       "    electorate_frac  num_nation  dem_margin   turnout  \n",
       "0          0.296605   122217180   -0.186452  0.564000  \n",
       "1          0.047057   122217180    0.304414  0.395000  \n",
       "2          0.656338   122217180   -0.021377  0.748760  \n",
       "3          0.340335   131049863    0.175109  0.549000  \n",
       "4          0.014612   131049863    0.516269  0.394000  \n",
       "5          0.641328   131049863    0.041751  0.739976  \n",
       "6          0.331596   129254383    0.100665  0.526000  \n",
       "7          0.013457   129254383    0.408414  0.380000  \n",
       "8          0.644741   129254383    0.045194  0.712990  \n",
       "9          0.008721   136670626    0.444665  0.343000  \n",
       "10         0.305485   136670626   -0.036938  0.516000  \n",
       "11         0.685794   136670626    0.083199  0.705875  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ed_df = pd.concat([ed2004_df, ed2016_df])\n",
    "\n",
    "ed_df = ed_df.merge(edturnout_df, on=['year', 'group'])\n",
    "\n",
    "#Add in number in electorate\n",
    "electorate_df = national_df[['year','num_nation']]\n",
    "ed_df = ed_df.merge(electorate_df, on='year', how='left')\n",
    "\n",
    "ed_df['dem_margin'] = ed_df['dem_frac'] - ed_df['rep_frac']\n",
    "order = ['demographic','group', 'year','dem_frac','rep_frac','electorate_frac', 'num_nation', 'dem_margin', 'turnout']\n",
    "ed_df = ed_df[order]\n",
    "\n",
    "ed_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Combined Demographic Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>demographic</th>\n",
       "      <th>group</th>\n",
       "      <th>year</th>\n",
       "      <th>dem_frac</th>\n",
       "      <th>rep_frac</th>\n",
       "      <th>electorate_frac</th>\n",
       "      <th>num_nation</th>\n",
       "      <th>dem_margin</th>\n",
       "      <th>turnout</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>race</td>\n",
       "      <td>Non-Hispanic White</td>\n",
       "      <td>2012</td>\n",
       "      <td>0.4300</td>\n",
       "      <td>0.5700</td>\n",
       "      <td>0.7370</td>\n",
       "      <td>129254383</td>\n",
       "      <td>-0.1400</td>\n",
       "      <td>0.6410</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>race</td>\n",
       "      <td>Non-Hispanic Black</td>\n",
       "      <td>2012</td>\n",
       "      <td>0.9700</td>\n",
       "      <td>0.0300</td>\n",
       "      <td>0.1340</td>\n",
       "      <td>129254383</td>\n",
       "      <td>0.9400</td>\n",
       "      <td>0.6640</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>race</td>\n",
       "      <td>Hispanic</td>\n",
       "      <td>2012</td>\n",
       "      <td>0.7300</td>\n",
       "      <td>0.2700</td>\n",
       "      <td>0.0840</td>\n",
       "      <td>129254383</td>\n",
       "      <td>0.4600</td>\n",
       "      <td>0.4800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>race</td>\n",
       "      <td>Other</td>\n",
       "      <td>2012</td>\n",
       "      <td>0.6100</td>\n",
       "      <td>0.3900</td>\n",
       "      <td>0.0450</td>\n",
       "      <td>129254383</td>\n",
       "      <td>0.2200</td>\n",
       "      <td>0.4900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>race</td>\n",
       "      <td>Non-Hispanic White</td>\n",
       "      <td>2008</td>\n",
       "      <td>0.4400</td>\n",
       "      <td>0.5600</td>\n",
       "      <td>0.7630</td>\n",
       "      <td>131049863</td>\n",
       "      <td>-0.1200</td>\n",
       "      <td>0.6610</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>race</td>\n",
       "      <td>Non-Hispanic Black</td>\n",
       "      <td>2008</td>\n",
       "      <td>1.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.1240</td>\n",
       "      <td>131049863</td>\n",
       "      <td>1.0000</td>\n",
       "      <td>0.6520</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>race</td>\n",
       "      <td>Hispanic</td>\n",
       "      <td>2008</td>\n",
       "      <td>0.7300</td>\n",
       "      <td>0.2700</td>\n",
       "      <td>0.0740</td>\n",
       "      <td>131049863</td>\n",
       "      <td>0.4600</td>\n",
       "      <td>0.4990</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>race</td>\n",
       "      <td>Other</td>\n",
       "      <td>2008</td>\n",
       "      <td>0.8200</td>\n",
       "      <td>0.1800</td>\n",
       "      <td>0.0390</td>\n",
       "      <td>131049863</td>\n",
       "      <td>0.6400</td>\n",
       "      <td>0.4950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>race</td>\n",
       "      <td>Non-Hispanic White</td>\n",
       "      <td>2004</td>\n",
       "      <td>0.4200</td>\n",
       "      <td>0.5800</td>\n",
       "      <td>0.7920</td>\n",
       "      <td>122217180</td>\n",
       "      <td>-0.1600</td>\n",
       "      <td>0.6720</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>race</td>\n",
       "      <td>Non-Hispanic Black</td>\n",
       "      <td>2004</td>\n",
       "      <td>0.8900</td>\n",
       "      <td>0.1100</td>\n",
       "      <td>0.1120</td>\n",
       "      <td>122217180</td>\n",
       "      <td>0.7800</td>\n",
       "      <td>0.6030</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>race</td>\n",
       "      <td>Hispanic</td>\n",
       "      <td>2004</td>\n",
       "      <td>0.6100</td>\n",
       "      <td>0.3900</td>\n",
       "      <td>0.0600</td>\n",
       "      <td>122217180</td>\n",
       "      <td>0.2200</td>\n",
       "      <td>0.4720</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>race</td>\n",
       "      <td>Other</td>\n",
       "      <td>2004</td>\n",
       "      <td>0.3600</td>\n",
       "      <td>0.6400</td>\n",
       "      <td>0.0360</td>\n",
       "      <td>122217180</td>\n",
       "      <td>-0.2800</td>\n",
       "      <td>0.4800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>race</td>\n",
       "      <td>Non-Hispanic White</td>\n",
       "      <td>2016</td>\n",
       "      <td>0.3938</td>\n",
       "      <td>0.5380</td>\n",
       "      <td>0.7330</td>\n",
       "      <td>136670626</td>\n",
       "      <td>-0.1442</td>\n",
       "      <td>0.6530</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>race</td>\n",
       "      <td>Non-Hispanic Black</td>\n",
       "      <td>2016</td>\n",
       "      <td>0.8999</td>\n",
       "      <td>0.0638</td>\n",
       "      <td>0.1240</td>\n",
       "      <td>136670626</td>\n",
       "      <td>0.8361</td>\n",
       "      <td>0.5930</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>race</td>\n",
       "      <td>Hispanic</td>\n",
       "      <td>2016</td>\n",
       "      <td>0.6863</td>\n",
       "      <td>0.2388</td>\n",
       "      <td>0.0920</td>\n",
       "      <td>136670626</td>\n",
       "      <td>0.4475</td>\n",
       "      <td>0.4760</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>race</td>\n",
       "      <td>Other</td>\n",
       "      <td>2016</td>\n",
       "      <td>0.5732</td>\n",
       "      <td>0.3147</td>\n",
       "      <td>0.0500</td>\n",
       "      <td>136670626</td>\n",
       "      <td>0.2585</td>\n",
       "      <td>0.4900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>gender</td>\n",
       "      <td>male</td>\n",
       "      <td>2004</td>\n",
       "      <td>0.4787</td>\n",
       "      <td>0.4919</td>\n",
       "      <td>0.5199</td>\n",
       "      <td>122217180</td>\n",
       "      <td>-0.0132</td>\n",
       "      <td>0.6210</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>gender</td>\n",
       "      <td>female</td>\n",
       "      <td>2004</td>\n",
       "      <td>0.4446</td>\n",
       "      <td>0.5449</td>\n",
       "      <td>0.4801</td>\n",
       "      <td>122217180</td>\n",
       "      <td>-0.1003</td>\n",
       "      <td>0.6540</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>gender</td>\n",
       "      <td>male</td>\n",
       "      <td>2008</td>\n",
       "      <td>0.5050</td>\n",
       "      <td>0.4679</td>\n",
       "      <td>0.4298</td>\n",
       "      <td>131049863</td>\n",
       "      <td>0.0371</td>\n",
       "      <td>0.6150</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>gender</td>\n",
       "      <td>female</td>\n",
       "      <td>2008</td>\n",
       "      <td>0.5619</td>\n",
       "      <td>0.4237</td>\n",
       "      <td>0.5702</td>\n",
       "      <td>131049863</td>\n",
       "      <td>0.1382</td>\n",
       "      <td>0.6570</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>gender</td>\n",
       "      <td>male</td>\n",
       "      <td>2012</td>\n",
       "      <td>0.4865</td>\n",
       "      <td>0.4699</td>\n",
       "      <td>0.4790</td>\n",
       "      <td>129254383</td>\n",
       "      <td>0.0166</td>\n",
       "      <td>0.5970</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>gender</td>\n",
       "      <td>female</td>\n",
       "      <td>2012</td>\n",
       "      <td>0.5494</td>\n",
       "      <td>0.4306</td>\n",
       "      <td>0.5210</td>\n",
       "      <td>129254383</td>\n",
       "      <td>0.1188</td>\n",
       "      <td>0.6370</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>gender</td>\n",
       "      <td>male</td>\n",
       "      <td>2016</td>\n",
       "      <td>0.4548</td>\n",
       "      <td>0.4690</td>\n",
       "      <td>0.4628</td>\n",
       "      <td>136670626</td>\n",
       "      <td>-0.0142</td>\n",
       "      <td>0.5930</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>gender</td>\n",
       "      <td>female</td>\n",
       "      <td>2016</td>\n",
       "      <td>0.5210</td>\n",
       "      <td>0.4191</td>\n",
       "      <td>0.5248</td>\n",
       "      <td>136670626</td>\n",
       "      <td>0.1018</td>\n",
       "      <td>0.6330</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>age</td>\n",
       "      <td>17 - 29</td>\n",
       "      <td>2004</td>\n",
       "      <td>0.4290</td>\n",
       "      <td>0.4123</td>\n",
       "      <td>0.1236</td>\n",
       "      <td>122217180</td>\n",
       "      <td>0.0167</td>\n",
       "      <td>0.4900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>age</td>\n",
       "      <td>30 - 44</td>\n",
       "      <td>2004</td>\n",
       "      <td>0.5816</td>\n",
       "      <td>0.3732</td>\n",
       "      <td>0.2558</td>\n",
       "      <td>122217180</td>\n",
       "      <td>0.2084</td>\n",
       "      <td>0.6240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>age</td>\n",
       "      <td>45 - 59</td>\n",
       "      <td>2004</td>\n",
       "      <td>0.4206</td>\n",
       "      <td>0.5794</td>\n",
       "      <td>0.3226</td>\n",
       "      <td>122217180</td>\n",
       "      <td>-0.1588</td>\n",
       "      <td>0.7000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>age</td>\n",
       "      <td>60 +</td>\n",
       "      <td>2004</td>\n",
       "      <td>0.4451</td>\n",
       "      <td>0.5549</td>\n",
       "      <td>0.2980</td>\n",
       "      <td>122217180</td>\n",
       "      <td>-0.1097</td>\n",
       "      <td>0.7140</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>age</td>\n",
       "      <td>17 - 29</td>\n",
       "      <td>2008</td>\n",
       "      <td>0.6714</td>\n",
       "      <td>0.3257</td>\n",
       "      <td>0.1412</td>\n",
       "      <td>131049863</td>\n",
       "      <td>0.3457</td>\n",
       "      <td>0.5110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>age</td>\n",
       "      <td>30 - 44</td>\n",
       "      <td>2008</td>\n",
       "      <td>0.6292</td>\n",
       "      <td>0.3507</td>\n",
       "      <td>0.2680</td>\n",
       "      <td>131049863</td>\n",
       "      <td>0.2785</td>\n",
       "      <td>0.6180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>age</td>\n",
       "      <td>45 - 59</td>\n",
       "      <td>2008</td>\n",
       "      <td>0.5623</td>\n",
       "      <td>0.4234</td>\n",
       "      <td>0.2510</td>\n",
       "      <td>131049863</td>\n",
       "      <td>0.1388</td>\n",
       "      <td>0.6850</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>age</td>\n",
       "      <td>60 +</td>\n",
       "      <td>2008</td>\n",
       "      <td>0.5049</td>\n",
       "      <td>0.4653</td>\n",
       "      <td>0.3398</td>\n",
       "      <td>131049863</td>\n",
       "      <td>0.0397</td>\n",
       "      <td>0.7080</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>age</td>\n",
       "      <td>17 - 29</td>\n",
       "      <td>2012</td>\n",
       "      <td>0.6257</td>\n",
       "      <td>0.3384</td>\n",
       "      <td>0.1394</td>\n",
       "      <td>129254383</td>\n",
       "      <td>0.2873</td>\n",
       "      <td>0.4500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>age</td>\n",
       "      <td>30 - 44</td>\n",
       "      <td>2012</td>\n",
       "      <td>0.5714</td>\n",
       "      <td>0.3680</td>\n",
       "      <td>0.2642</td>\n",
       "      <td>129254383</td>\n",
       "      <td>0.2034</td>\n",
       "      <td>0.5950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>age</td>\n",
       "      <td>45 - 59</td>\n",
       "      <td>2012</td>\n",
       "      <td>0.5125</td>\n",
       "      <td>0.4476</td>\n",
       "      <td>0.2959</td>\n",
       "      <td>129254383</td>\n",
       "      <td>0.0649</td>\n",
       "      <td>0.6690</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>age</td>\n",
       "      <td>60 +</td>\n",
       "      <td>2012</td>\n",
       "      <td>0.5261</td>\n",
       "      <td>0.4436</td>\n",
       "      <td>0.3004</td>\n",
       "      <td>129254383</td>\n",
       "      <td>0.0825</td>\n",
       "      <td>0.7180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>age</td>\n",
       "      <td>17 - 29</td>\n",
       "      <td>2016</td>\n",
       "      <td>0.5483</td>\n",
       "      <td>0.3515</td>\n",
       "      <td>0.1733</td>\n",
       "      <td>136670626</td>\n",
       "      <td>0.1968</td>\n",
       "      <td>0.4610</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>age</td>\n",
       "      <td>30 - 44</td>\n",
       "      <td>2016</td>\n",
       "      <td>0.5552</td>\n",
       "      <td>0.3460</td>\n",
       "      <td>0.2456</td>\n",
       "      <td>136670626</td>\n",
       "      <td>0.2092</td>\n",
       "      <td>0.5880</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>age</td>\n",
       "      <td>45 - 59</td>\n",
       "      <td>2016</td>\n",
       "      <td>0.4468</td>\n",
       "      <td>0.4800</td>\n",
       "      <td>0.3382</td>\n",
       "      <td>136670626</td>\n",
       "      <td>-0.0331</td>\n",
       "      <td>0.6590</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>age</td>\n",
       "      <td>60 +</td>\n",
       "      <td>2016</td>\n",
       "      <td>0.4753</td>\n",
       "      <td>0.4947</td>\n",
       "      <td>0.2428</td>\n",
       "      <td>136670626</td>\n",
       "      <td>-0.0194</td>\n",
       "      <td>0.7030</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>education</td>\n",
       "      <td>High School Grad</td>\n",
       "      <td>2004</td>\n",
       "      <td>0.3937</td>\n",
       "      <td>0.5801</td>\n",
       "      <td>0.2966</td>\n",
       "      <td>122217180</td>\n",
       "      <td>-0.1865</td>\n",
       "      <td>0.5640</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>education</td>\n",
       "      <td>Less Than High School</td>\n",
       "      <td>2004</td>\n",
       "      <td>0.6522</td>\n",
       "      <td>0.3478</td>\n",
       "      <td>0.0471</td>\n",
       "      <td>122217180</td>\n",
       "      <td>0.3044</td>\n",
       "      <td>0.3950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>education</td>\n",
       "      <td>Some College to Post-Graduate</td>\n",
       "      <td>2004</td>\n",
       "      <td>0.4797</td>\n",
       "      <td>0.5011</td>\n",
       "      <td>0.6563</td>\n",
       "      <td>122217180</td>\n",
       "      <td>-0.0214</td>\n",
       "      <td>0.7488</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>education</td>\n",
       "      <td>High School Grad</td>\n",
       "      <td>2008</td>\n",
       "      <td>0.5827</td>\n",
       "      <td>0.4076</td>\n",
       "      <td>0.3403</td>\n",
       "      <td>131049863</td>\n",
       "      <td>0.1751</td>\n",
       "      <td>0.5490</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>education</td>\n",
       "      <td>Less Than High School</td>\n",
       "      <td>2008</td>\n",
       "      <td>0.7581</td>\n",
       "      <td>0.2419</td>\n",
       "      <td>0.0146</td>\n",
       "      <td>131049863</td>\n",
       "      <td>0.5163</td>\n",
       "      <td>0.3940</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>education</td>\n",
       "      <td>Some College to Post-Graduate</td>\n",
       "      <td>2008</td>\n",
       "      <td>0.5082</td>\n",
       "      <td>0.4665</td>\n",
       "      <td>0.6413</td>\n",
       "      <td>131049863</td>\n",
       "      <td>0.0418</td>\n",
       "      <td>0.7400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>education</td>\n",
       "      <td>High School Grad</td>\n",
       "      <td>2012</td>\n",
       "      <td>0.5405</td>\n",
       "      <td>0.4398</td>\n",
       "      <td>0.3316</td>\n",
       "      <td>129254383</td>\n",
       "      <td>0.1007</td>\n",
       "      <td>0.5260</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>education</td>\n",
       "      <td>Less Than High School</td>\n",
       "      <td>2012</td>\n",
       "      <td>0.6904</td>\n",
       "      <td>0.2819</td>\n",
       "      <td>0.0135</td>\n",
       "      <td>129254383</td>\n",
       "      <td>0.4084</td>\n",
       "      <td>0.3800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>education</td>\n",
       "      <td>Some College to Post-Graduate</td>\n",
       "      <td>2012</td>\n",
       "      <td>0.5039</td>\n",
       "      <td>0.4587</td>\n",
       "      <td>0.6447</td>\n",
       "      <td>129254383</td>\n",
       "      <td>0.0452</td>\n",
       "      <td>0.7130</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>education</td>\n",
       "      <td>Less Than High School</td>\n",
       "      <td>2016</td>\n",
       "      <td>0.7223</td>\n",
       "      <td>0.2777</td>\n",
       "      <td>0.0087</td>\n",
       "      <td>136670626</td>\n",
       "      <td>0.4447</td>\n",
       "      <td>0.3430</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>education</td>\n",
       "      <td>High School Grad</td>\n",
       "      <td>2016</td>\n",
       "      <td>0.4500</td>\n",
       "      <td>0.4870</td>\n",
       "      <td>0.3055</td>\n",
       "      <td>136670626</td>\n",
       "      <td>-0.0369</td>\n",
       "      <td>0.5160</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>education</td>\n",
       "      <td>Some College to Post-Graduate</td>\n",
       "      <td>2016</td>\n",
       "      <td>0.5057</td>\n",
       "      <td>0.4225</td>\n",
       "      <td>0.6858</td>\n",
       "      <td>136670626</td>\n",
       "      <td>0.0832</td>\n",
       "      <td>0.7059</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   demographic                          group  year  dem_frac  rep_frac  \\\n",
       "0         race             Non-Hispanic White  2012    0.4300    0.5700   \n",
       "1         race             Non-Hispanic Black  2012    0.9700    0.0300   \n",
       "2         race                       Hispanic  2012    0.7300    0.2700   \n",
       "3         race                          Other  2012    0.6100    0.3900   \n",
       "4         race             Non-Hispanic White  2008    0.4400    0.5600   \n",
       "5         race             Non-Hispanic Black  2008    1.0000    0.0000   \n",
       "6         race                       Hispanic  2008    0.7300    0.2700   \n",
       "7         race                          Other  2008    0.8200    0.1800   \n",
       "8         race             Non-Hispanic White  2004    0.4200    0.5800   \n",
       "9         race             Non-Hispanic Black  2004    0.8900    0.1100   \n",
       "10        race                       Hispanic  2004    0.6100    0.3900   \n",
       "11        race                          Other  2004    0.3600    0.6400   \n",
       "12        race             Non-Hispanic White  2016    0.3938    0.5380   \n",
       "13        race             Non-Hispanic Black  2016    0.8999    0.0638   \n",
       "14        race                       Hispanic  2016    0.6863    0.2388   \n",
       "15        race                          Other  2016    0.5732    0.3147   \n",
       "0       gender                           male  2004    0.4787    0.4919   \n",
       "1       gender                         female  2004    0.4446    0.5449   \n",
       "2       gender                           male  2008    0.5050    0.4679   \n",
       "3       gender                         female  2008    0.5619    0.4237   \n",
       "4       gender                           male  2012    0.4865    0.4699   \n",
       "5       gender                         female  2012    0.5494    0.4306   \n",
       "6       gender                           male  2016    0.4548    0.4690   \n",
       "7       gender                         female  2016    0.5210    0.4191   \n",
       "0          age                        17 - 29  2004    0.4290    0.4123   \n",
       "1          age                        30 - 44  2004    0.5816    0.3732   \n",
       "2          age                        45 - 59  2004    0.4206    0.5794   \n",
       "3          age                           60 +  2004    0.4451    0.5549   \n",
       "4          age                        17 - 29  2008    0.6714    0.3257   \n",
       "5          age                        30 - 44  2008    0.6292    0.3507   \n",
       "6          age                        45 - 59  2008    0.5623    0.4234   \n",
       "7          age                           60 +  2008    0.5049    0.4653   \n",
       "8          age                        17 - 29  2012    0.6257    0.3384   \n",
       "9          age                        30 - 44  2012    0.5714    0.3680   \n",
       "10         age                        45 - 59  2012    0.5125    0.4476   \n",
       "11         age                           60 +  2012    0.5261    0.4436   \n",
       "12         age                        17 - 29  2016    0.5483    0.3515   \n",
       "13         age                        30 - 44  2016    0.5552    0.3460   \n",
       "14         age                        45 - 59  2016    0.4468    0.4800   \n",
       "15         age                           60 +  2016    0.4753    0.4947   \n",
       "0    education               High School Grad  2004    0.3937    0.5801   \n",
       "1    education          Less Than High School  2004    0.6522    0.3478   \n",
       "2    education  Some College to Post-Graduate  2004    0.4797    0.5011   \n",
       "3    education               High School Grad  2008    0.5827    0.4076   \n",
       "4    education          Less Than High School  2008    0.7581    0.2419   \n",
       "5    education  Some College to Post-Graduate  2008    0.5082    0.4665   \n",
       "6    education               High School Grad  2012    0.5405    0.4398   \n",
       "7    education          Less Than High School  2012    0.6904    0.2819   \n",
       "8    education  Some College to Post-Graduate  2012    0.5039    0.4587   \n",
       "9    education          Less Than High School  2016    0.7223    0.2777   \n",
       "10   education               High School Grad  2016    0.4500    0.4870   \n",
       "11   education  Some College to Post-Graduate  2016    0.5057    0.4225   \n",
       "\n",
       "    electorate_frac  num_nation  dem_margin  turnout  \n",
       "0            0.7370   129254383     -0.1400   0.6410  \n",
       "1            0.1340   129254383      0.9400   0.6640  \n",
       "2            0.0840   129254383      0.4600   0.4800  \n",
       "3            0.0450   129254383      0.2200   0.4900  \n",
       "4            0.7630   131049863     -0.1200   0.6610  \n",
       "5            0.1240   131049863      1.0000   0.6520  \n",
       "6            0.0740   131049863      0.4600   0.4990  \n",
       "7            0.0390   131049863      0.6400   0.4950  \n",
       "8            0.7920   122217180     -0.1600   0.6720  \n",
       "9            0.1120   122217180      0.7800   0.6030  \n",
       "10           0.0600   122217180      0.2200   0.4720  \n",
       "11           0.0360   122217180     -0.2800   0.4800  \n",
       "12           0.7330   136670626     -0.1442   0.6530  \n",
       "13           0.1240   136670626      0.8361   0.5930  \n",
       "14           0.0920   136670626      0.4475   0.4760  \n",
       "15           0.0500   136670626      0.2585   0.4900  \n",
       "0            0.5199   122217180     -0.0132   0.6210  \n",
       "1            0.4801   122217180     -0.1003   0.6540  \n",
       "2            0.4298   131049863      0.0371   0.6150  \n",
       "3            0.5702   131049863      0.1382   0.6570  \n",
       "4            0.4790   129254383      0.0166   0.5970  \n",
       "5            0.5210   129254383      0.1188   0.6370  \n",
       "6            0.4628   136670626     -0.0142   0.5930  \n",
       "7            0.5248   136670626      0.1018   0.6330  \n",
       "0            0.1236   122217180      0.0167   0.4900  \n",
       "1            0.2558   122217180      0.2084   0.6240  \n",
       "2            0.3226   122217180     -0.1588   0.7000  \n",
       "3            0.2980   122217180     -0.1097   0.7140  \n",
       "4            0.1412   131049863      0.3457   0.5110  \n",
       "5            0.2680   131049863      0.2785   0.6180  \n",
       "6            0.2510   131049863      0.1388   0.6850  \n",
       "7            0.3398   131049863      0.0397   0.7080  \n",
       "8            0.1394   129254383      0.2873   0.4500  \n",
       "9            0.2642   129254383      0.2034   0.5950  \n",
       "10           0.2959   129254383      0.0649   0.6690  \n",
       "11           0.3004   129254383      0.0825   0.7180  \n",
       "12           0.1733   136670626      0.1968   0.4610  \n",
       "13           0.2456   136670626      0.2092   0.5880  \n",
       "14           0.3382   136670626     -0.0331   0.6590  \n",
       "15           0.2428   136670626     -0.0194   0.7030  \n",
       "0            0.2966   122217180     -0.1865   0.5640  \n",
       "1            0.0471   122217180      0.3044   0.3950  \n",
       "2            0.6563   122217180     -0.0214   0.7488  \n",
       "3            0.3403   131049863      0.1751   0.5490  \n",
       "4            0.0146   131049863      0.5163   0.3940  \n",
       "5            0.6413   131049863      0.0418   0.7400  \n",
       "6            0.3316   129254383      0.1007   0.5260  \n",
       "7            0.0135   129254383      0.4084   0.3800  \n",
       "8            0.6447   129254383      0.0452   0.7130  \n",
       "9            0.0087   136670626      0.4447   0.3430  \n",
       "10           0.3055   136670626     -0.0369   0.5160  \n",
       "11           0.6858   136670626      0.0832   0.7059  "
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "demographic_df = pd.concat([race_df, gender_df, age_df, ed_df])\n",
    "#demographic_df['year'] = demographic_df['year'].round()\n",
    "demographic_df = demographic_df.round(decimals=4)\n",
    "\n",
    "demographic_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "demographic_df.to_csv('./US_Demographic_Presidential_Results_04-16.csv', index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}