2016_US_County_Level_Presidential_Results.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from bs4 import BeautifulSoup\n",
    "import urllib\n",
    "from IPython.display import display, display_pretty, Javascript, HTML"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Flow\n",
    "\n",
    "- [Download and preprocess county-level results](#Townhall-data)\n",
    "- [Downlaod and preprocess county-level metadata](#Census-data)\n",
    "- [Combine datasets](#Combine-data)\n",
    "- [Export county-level results](#Export-data)\n",
    "- [Visualize](#Visualize)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Townhall data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# each page has a summary table that rolls up results at the state level\n",
    "# get rid of it\n",
    "def cond(x):\n",
    "    if x:\n",
    "        return x.startswith(\"table ec-table\") and not \"table ec-table ec-table-summary\" in x\n",
    "    else:\n",
    "        return False"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# list of state abbreviations\n",
    "states = ['AL','AK','AZ','AR','CA','CO','CT','DC','DE','FL','GA','HI','ID','IL','IN','IA','KS','KY','LA','ME','MD','MA','MI','MN','MS','MO','MT','NE','NV','NH','NJ','NM','NY','NC','ND','OH','OK','OR','PA','RI','SC','SD','TN','TX','UT','VT','VA','WA','WV','WI','WY']\n",
    "\n",
    "# headers for csv export\n",
    "data = [['state_abbr', 'county_name', 'party', 'votes_total']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# loop through each state's web page http://townhall.com/election/2016/president/%s/county, where %s is the state abbr\n",
    "for state in states:\n",
    "    r = urllib.urlopen('http://townhall.com/election/2016/president/' + state + '/county').read()\n",
    "    soup = BeautifulSoup(r, \"html.parser\")\n",
    "\n",
    "    # loop through each <table> tag with .ec-table class\n",
    "    tables = soup.findAll('table', attrs={'class':cond})\n",
    "\n",
    "    for table in tables:\n",
    "        if table.findParent(\"table\") is None:\n",
    "            table_body = table.find('tbody')\n",
    "\n",
    "            rows = table_body.find_all('tr')\n",
    "            for row in rows:\n",
    "                cols = row.find_all('td')\n",
    "                # first tbody tr has four td\n",
    "                if len(cols) == 4:\n",
    "                    # strip text from each td\n",
    "                    divs = cols[0].find_all('div')\n",
    "                    county = divs[0].text.strip()\n",
    "                    party = cols[1]['class'][0]\n",
    "                    total_votes = int(cols[2].text.strip().replace(',','').replace('-','0'))\n",
    "                # all other tbody tr have three td\n",
    "                else:\n",
    "                    party = cols[1]['class'][0]\n",
    "                    total_votes = int(cols[1].text.strip().replace(',','').replace('-','0'))\n",
    "                    \n",
    "                #combine each row's results\n",
    "                rowData = [state,county,party,total_votes]\n",
    "                data.append(rowData)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "14188\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>state_abbr</th>\n",
       "      <th>county_name</th>\n",
       "      <th>party</th>\n",
       "      <th>votes_total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AL</td>\n",
       "      <td>Autauga</td>\n",
       "      <td>GOP</td>\n",
       "      <td>18110.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AL</td>\n",
       "      <td>Autauga</td>\n",
       "      <td>DEM</td>\n",
       "      <td>5908.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AL</td>\n",
       "      <td>Autauga</td>\n",
       "      <td>IND</td>\n",
       "      <td>538.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AL</td>\n",
       "      <td>Autauga</td>\n",
       "      <td>IND</td>\n",
       "      <td>105.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>AL</td>\n",
       "      <td>Baldwin</td>\n",
       "      <td>GOP</td>\n",
       "      <td>72780.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "0 state_abbr county_name party  votes_total\n",
       "1         AL     Autauga   GOP      18110.0\n",
       "2         AL     Autauga   DEM       5908.0\n",
       "3         AL     Autauga   IND        538.0\n",
       "4         AL     Autauga   IND        105.0\n",
       "5         AL     Baldwin   GOP      72780.0"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "townhall = pd.DataFrame(data) # throw results in dataframe\n",
    "new_header = townhall.iloc[0] #grab the first row for the header\n",
    "townhall = townhall[1:] #take the data less the header row\n",
    "townhall.columns = new_header #set the header row as the df header\n",
    "townhall['votes_total'] = townhall['votes_total'].astype('float64')\n",
    "print(townhall.shape[0])\n",
    "townhall.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>state_abbr</th>\n",
       "      <th>county_name</th>\n",
       "      <th>party</th>\n",
       "      <th>votes_total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>269</th>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>GOP</td>\n",
       "      <td>130415.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>270</th>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>DEM</td>\n",
       "      <td>93007.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>271</th>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>LIB</td>\n",
       "      <td>14593.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>272</th>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>GRN</td>\n",
       "      <td>4445.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>273</th>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>OTH</td>\n",
       "      <td>4128.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "0   state_abbr county_name party  votes_total\n",
       "269         AK      Alaska   GOP     130415.0\n",
       "270         AK      Alaska   DEM      93007.0\n",
       "271         AK      Alaska   LIB      14593.0\n",
       "272         AK      Alaska   GRN       4445.0\n",
       "273         AK      Alaska   OTH       4128.0"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# view by state\n",
    "townhall[(townhall['state_abbr'] == 'AK')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0    state_abbr  county_name party  votes_total\n",
      "7943         NV  Carson City   GOP      13125.0\n",
      "7944         NV  Carson City   DEM       9610.0\n",
      "7945         NV  Carson City   LIB       1159.0\n",
      "7946         NV  Carson City   OTH       1122.0\n"
     ]
    }
   ],
   "source": [
    "# view special cases\n",
    "print(townhall[(townhall['state_abbr'] == 'NV') & (townhall['county_name'] == 'Carson City')])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0     state_abbr            county_name party  votes_total\n",
      "1415          DC   District of Columbia   DEM     260223.0\n",
      "1416          DC   District of Columbia   GOP      11553.0\n",
      "1417          DC   District of Columbia   LIB       4501.0\n",
      "1418          DC   District of Columbia   DCG       3995.0\n",
      "7227          MO  Ste. Genevieve County   GOP       5495.0\n",
      "7228          MO  Ste. Genevieve County   DEM       2540.0\n",
      "7229          MO  Ste. Genevieve County   LIB        323.0\n",
      "7230          MO  Ste. Genevieve County   GRN         55.0\n",
      "7231          MO  Ste. Genevieve County   OTH         46.0\n",
      "10780         SD                 Oglala   DEM       2504.0\n",
      "10781         SD                 Oglala   GOP        241.0\n",
      "10782         SD                 Oglala   LIB        104.0\n",
      "10783         SD                 Oglala   OTH         47.0\n"
     ]
    }
   ],
   "source": [
    "# fix townhall county name for Washington DC, Sainte Genevieve, MO, Oglala, SD\n",
    "townhall.loc[townhall['state_abbr'] =='DC', 'county_name'] = 'District of Columbia'\n",
    "townhall.loc[townhall['county_name'] == 'Sainte Genevieve', 'county_name'] = 'Ste. Genevieve County'\n",
    "townhall.loc[townhall['county_name'] == 'Oglala Lakota', 'county_name'] = 'Oglala'\n",
    "print(townhall[(townhall['county_name'] == 'District of Columbia') | (townhall['county_name'] == 'Ste. Genevieve County') | (townhall['county_name'] == 'Oglala')])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0    state_abbr  county_name party  votes_total\n",
      "7943         NV  Carson City   GOP      13125.0\n",
      "7944         NV  Carson City   DEM       9610.0\n",
      "7945         NV  Carson City   LIB       1159.0\n",
      "7946         NV  Carson City   OTH       1122.0\n"
     ]
    }
   ],
   "source": [
    "# change 'Co.' to 'County' in county_name to match census county name\n",
    "townhall['county_name'] = townhall['county_name'].apply(lambda x: x.replace('Co.','County').strip())\n",
    "print(townhall[(townhall['state_abbr'] == 'NV') & (townhall['county_name'] == 'Carson City')])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0    state_abbr  county_name party  votes_total      combined\n",
      "7943         NV  Carson City   GOP      13125.0  NVcarsoncity\n",
      "7944         NV  Carson City   DEM       9610.0  NVcarsoncity\n",
      "7945         NV  Carson City   LIB       1159.0  NVcarsoncity\n",
      "7946         NV  Carson City   OTH       1122.0  NVcarsoncity\n"
     ]
    }
   ],
   "source": [
    "# combine state and county names\n",
    "townhall['combined'] = townhall['state_abbr'] + townhall['county_name'].apply(lambda x: x.replace(' ','').lower())\n",
    "print(townhall[(townhall['state_abbr'] == 'NV') & (townhall['county_name'] == 'Carson City')])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Census data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(3235, 5)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>state_abbr</th>\n",
       "      <th>state_fips</th>\n",
       "      <th>county_fips</th>\n",
       "      <th>county_name</th>\n",
       "      <th>fips_class_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AL</td>\n",
       "      <td>01</td>\n",
       "      <td>001</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AL</td>\n",
       "      <td>01</td>\n",
       "      <td>003</td>\n",
       "      <td>Baldwin County</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AL</td>\n",
       "      <td>01</td>\n",
       "      <td>005</td>\n",
       "      <td>Barbour County</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AL</td>\n",
       "      <td>01</td>\n",
       "      <td>007</td>\n",
       "      <td>Bibb County</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AL</td>\n",
       "      <td>01</td>\n",
       "      <td>009</td>\n",
       "      <td>Blount County</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  state_abbr state_fips county_fips     county_name fips_class_code\n",
       "0         AL         01         001  Autauga County              H1\n",
       "1         AL         01         003  Baldwin County              H1\n",
       "2         AL         01         005  Barbour County              H1\n",
       "3         AL         01         007     Bibb County              H1\n",
       "4         AL         01         009   Blount County              H1"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# county_fips data from https://www.census.gov/geo/reference/codes/cou.html\n",
    "census = pd.read_csv('http://www2.census.gov/geo/docs/reference/codes/files/national_county.txt',sep=',',header=None, dtype=str)\n",
    "census.columns = ['state_abbr', 'state_fips', 'county_fips', 'county_name', 'fips_class_code']\n",
    "print(census.shape)\n",
    "census.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "29\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>state_abbr</th>\n",
       "      <th>state_fips</th>\n",
       "      <th>county_fips</th>\n",
       "      <th>county_name</th>\n",
       "      <th>fips_class_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>013</td>\n",
       "      <td>Aleutians East Borough</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68</th>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>016</td>\n",
       "      <td>Aleutians West Census Area</td>\n",
       "      <td>H5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69</th>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>020</td>\n",
       "      <td>Anchorage Municipality</td>\n",
       "      <td>H6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>050</td>\n",
       "      <td>Bethel Census Area</td>\n",
       "      <td>H5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71</th>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>060</td>\n",
       "      <td>Bristol Bay Borough</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72</th>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>068</td>\n",
       "      <td>Denali Borough</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73</th>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>070</td>\n",
       "      <td>Dillingham Census Area</td>\n",
       "      <td>H5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>090</td>\n",
       "      <td>Fairbanks North Star Borough</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75</th>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>100</td>\n",
       "      <td>Haines Borough</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76</th>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>105</td>\n",
       "      <td>Hoonah-Angoon Census Area</td>\n",
       "      <td>H5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77</th>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>110</td>\n",
       "      <td>Juneau City and Borough</td>\n",
       "      <td>H6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78</th>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>122</td>\n",
       "      <td>Kenai Peninsula Borough</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>130</td>\n",
       "      <td>Ketchikan Gateway Borough</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>150</td>\n",
       "      <td>Kodiak Island Borough</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81</th>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>164</td>\n",
       "      <td>Lake and Peninsula Borough</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82</th>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>170</td>\n",
       "      <td>Matanuska-Susitna Borough</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>180</td>\n",
       "      <td>Nome Census Area</td>\n",
       "      <td>H5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84</th>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>185</td>\n",
       "      <td>North Slope Borough</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85</th>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>188</td>\n",
       "      <td>Northwest Arctic Borough</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86</th>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>195</td>\n",
       "      <td>Petersburg Census Area</td>\n",
       "      <td>H5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87</th>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>198</td>\n",
       "      <td>Prince of Wales-Hyder Census Area</td>\n",
       "      <td>H5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>220</td>\n",
       "      <td>Sitka City and Borough</td>\n",
       "      <td>H6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>230</td>\n",
       "      <td>Skagway Municipality</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90</th>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>240</td>\n",
       "      <td>Southeast Fairbanks Census Area</td>\n",
       "      <td>H5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91</th>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>261</td>\n",
       "      <td>Valdez-Cordova Census Area</td>\n",
       "      <td>H5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92</th>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>270</td>\n",
       "      <td>Wade Hampton Census Area</td>\n",
       "      <td>H5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93</th>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>275</td>\n",
       "      <td>Wrangell City and Borough</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>282</td>\n",
       "      <td>Yakutat City and Borough</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>290</td>\n",
       "      <td>Yukon-Koyukuk Census Area</td>\n",
       "      <td>H5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   state_abbr state_fips county_fips                        county_name  \\\n",
       "67         AK         02         013             Aleutians East Borough   \n",
       "68         AK         02         016         Aleutians West Census Area   \n",
       "69         AK         02         020             Anchorage Municipality   \n",
       "70         AK         02         050                 Bethel Census Area   \n",
       "71         AK         02         060                Bristol Bay Borough   \n",
       "72         AK         02         068                     Denali Borough   \n",
       "73         AK         02         070             Dillingham Census Area   \n",
       "74         AK         02         090       Fairbanks North Star Borough   \n",
       "75         AK         02         100                     Haines Borough   \n",
       "76         AK         02         105          Hoonah-Angoon Census Area   \n",
       "77         AK         02         110            Juneau City and Borough   \n",
       "78         AK         02         122            Kenai Peninsula Borough   \n",
       "79         AK         02         130          Ketchikan Gateway Borough   \n",
       "80         AK         02         150              Kodiak Island Borough   \n",
       "81         AK         02         164         Lake and Peninsula Borough   \n",
       "82         AK         02         170          Matanuska-Susitna Borough   \n",
       "83         AK         02         180                   Nome Census Area   \n",
       "84         AK         02         185                North Slope Borough   \n",
       "85         AK         02         188           Northwest Arctic Borough   \n",
       "86         AK         02         195             Petersburg Census Area   \n",
       "87         AK         02         198  Prince of Wales-Hyder Census Area   \n",
       "88         AK         02         220             Sitka City and Borough   \n",
       "89         AK         02         230               Skagway Municipality   \n",
       "90         AK         02         240    Southeast Fairbanks Census Area   \n",
       "91         AK         02         261         Valdez-Cordova Census Area   \n",
       "92         AK         02         270           Wade Hampton Census Area   \n",
       "93         AK         02         275          Wrangell City and Borough   \n",
       "94         AK         02         282           Yakutat City and Borough   \n",
       "95         AK         02         290          Yukon-Koyukuk Census Area   \n",
       "\n",
       "   fips_class_code  \n",
       "67              H1  \n",
       "68              H5  \n",
       "69              H6  \n",
       "70              H5  \n",
       "71              H1  \n",
       "72              H1  \n",
       "73              H5  \n",
       "74              H1  \n",
       "75              H1  \n",
       "76              H5  \n",
       "77              H6  \n",
       "78              H1  \n",
       "79              H1  \n",
       "80              H1  \n",
       "81              H1  \n",
       "82              H1  \n",
       "83              H5  \n",
       "84              H1  \n",
       "85              H1  \n",
       "86              H5  \n",
       "87              H5  \n",
       "88              H6  \n",
       "89              H1  \n",
       "90              H5  \n",
       "91              H5  \n",
       "92              H5  \n",
       "93              H1  \n",
       "94              H1  \n",
       "95              H5  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# view by state\n",
    "ak_counties = census[(census['state_abbr'] == 'AK')].shape[0]\n",
    "print(ak_counties)\n",
    "census[(census['state_abbr'] == 'AK')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     state_abbr state_fips county_fips  county_name fips_class_code\n",
      "1763         NV         32         510  Carson City              C7\n"
     ]
    }
   ],
   "source": [
    "# veiw special cases\n",
    "print(census[(census['state_abbr'] == 'NV') & (census['county_name'] == 'Carson City')])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>state_abbr</th>\n",
       "      <th>state_fips</th>\n",
       "      <th>county_fips</th>\n",
       "      <th>county_name</th>\n",
       "      <th>fips_class_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2362</th>\n",
       "      <td>SD</td>\n",
       "      <td>46</td>\n",
       "      <td>003</td>\n",
       "      <td>Aurora County</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2363</th>\n",
       "      <td>SD</td>\n",
       "      <td>46</td>\n",
       "      <td>005</td>\n",
       "      <td>Beadle County</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2364</th>\n",
       "      <td>SD</td>\n",
       "      <td>46</td>\n",
       "      <td>007</td>\n",
       "      <td>Bennett County</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2365</th>\n",
       "      <td>SD</td>\n",
       "      <td>46</td>\n",
       "      <td>009</td>\n",
       "      <td>Bon Homme County</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2366</th>\n",
       "      <td>SD</td>\n",
       "      <td>46</td>\n",
       "      <td>011</td>\n",
       "      <td>Brookings County</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2367</th>\n",
       "      <td>SD</td>\n",
       "      <td>46</td>\n",
       "      <td>013</td>\n",
       "      <td>Brown County</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2368</th>\n",
       "      <td>SD</td>\n",
       "      <td>46</td>\n",
       "      <td>015</td>\n",
       "      <td>Brule County</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2369</th>\n",
       "      <td>SD</td>\n",
       "      <td>46</td>\n",
       "      <td>017</td>\n",
       "      <td>Buffalo County</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2370</th>\n",
       "      <td>SD</td>\n",
       "      <td>46</td>\n",
       "      <td>019</td>\n",
       "      <td>Butte County</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2371</th>\n",
       "      <td>SD</td>\n",
       "      <td>46</td>\n",
       "      <td>021</td>\n",
       "      <td>Campbell County</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2372</th>\n",
       "      <td>SD</td>\n",
       "      <td>46</td>\n",
       "      <td>023</td>\n",
       "      <td>Charles Mix County</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2373</th>\n",
       "      <td>SD</td>\n",
       "      <td>46</td>\n",
       "      <td>025</td>\n",
       "      <td>Clark County</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2374</th>\n",
       "      <td>SD</td>\n",
       "      <td>46</td>\n",
       "      <td>027</td>\n",
       "      <td>Clay County</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2375</th>\n",
       "      <td>SD</td>\n",
       "      <td>46</td>\n",
       "      <td>029</td>\n",
       "      <td>Codington County</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2376</th>\n",
       "      <td>SD</td>\n",
       "      <td>46</td>\n",
       "      <td>031</td>\n",
       "      <td>Corson County</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2377</th>\n",
       "      <td>SD</td>\n",
       "      <td>46</td>\n",
       "      <td>033</td>\n",
       "      <td>Custer County</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2378</th>\n",
       "      <td>SD</td>\n",
       "      <td>46</td>\n",
       "      <td>035</td>\n",
       "      <td>Davison County</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2379</th>\n",
       "      <td>SD</td>\n",
       "      <td>46</td>\n",
       "      <td>037</td>\n",
       "      <td>Day County</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2380</th>\n",
       "      <td>SD</td>\n",
       "      <td>46</td>\n",
       "      <td>039</td>\n",
       "      <td>Deuel County</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2381</th>\n",
       "      <td>SD</td>\n",
       "      <td>46</td>\n",
       "      <td>041</td>\n",
       "      <td>Dewey County</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2382</th>\n",
       "      <td>SD</td>\n",
       "      <td>46</td>\n",
       "      <td>043</td>\n",
       "      <td>Douglas County</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2383</th>\n",
       "      <td>SD</td>\n",
       "      <td>46</td>\n",
       "      <td>045</td>\n",
       "      <td>Edmunds County</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2384</th>\n",
       "      <td>SD</td>\n",
       "      <td>46</td>\n",
       "      <td>047</td>\n",
       "      <td>Fall River County</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2385</th>\n",
       "      <td>SD</td>\n",
       "      <td>46</td>\n",
       "      <td>049</td>\n",
       "      <td>Faulk County</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2386</th>\n",
       "      <td>SD</td>\n",
       "      <td>46</td>\n",
       "      <td>051</td>\n",
       "      <td>Grant County</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2387</th>\n",
       "      <td>SD</td>\n",
       "      <td>46</td>\n",
       "      <td>053</td>\n",
       "      <td>Gregory County</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2388</th>\n",
       "      <td>SD</td>\n",
       "      <td>46</td>\n",
       "      <td>055</td>\n",
       "      <td>Haakon County</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2389</th>\n",
       "      <td>SD</td>\n",
       "      <td>46</td>\n",
       "      <td>057</td>\n",
       "      <td>Hamlin County</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2390</th>\n",
       "      <td>SD</td>\n",
       "      <td>46</td>\n",
       "      <td>059</td>\n",
       "      <td>Hand County</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2391</th>\n",
       "      <td>SD</td>\n",
       "      <td>46</td>\n",
       "      <td>061</td>\n",
       "      <td>Hanson County</td>\n",
       "      <td>H1</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>2398</th>\n",
       "      <td>SD</td>\n",
       "      <td>46</td>\n",
       "      <td>075</td>\n",
       "      <td>Jones County</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2399</th>\n",
       "      <td>SD</td>\n",
       "      <td>46</td>\n",
       "      <td>077</td>\n",
       "      <td>Kingsbury County</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2400</th>\n",
       "      <td>SD</td>\n",
       "      <td>46</td>\n",
       "      <td>079</td>\n",
       "      <td>Lake County</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2401</th>\n",
       "      <td>SD</td>\n",
       "      <td>46</td>\n",
       "      <td>081</td>\n",
       "      <td>Lawrence County</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2402</th>\n",
       "      <td>SD</td>\n",
       "      <td>46</td>\n",
       "      <td>083</td>\n",
       "      <td>Lincoln County</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2403</th>\n",
       "      <td>SD</td>\n",
       "      <td>46</td>\n",
       "      <td>085</td>\n",
       "      <td>Lyman County</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2404</th>\n",
       "      <td>SD</td>\n",
       "      <td>46</td>\n",
       "      <td>087</td>\n",
       "      <td>McCook County</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2405</th>\n",
       "      <td>SD</td>\n",
       "      <td>46</td>\n",
       "      <td>089</td>\n",
       "      <td>McPherson County</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2406</th>\n",
       "      <td>SD</td>\n",
       "      <td>46</td>\n",
       "      <td>091</td>\n",
       "      <td>Marshall County</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2407</th>\n",
       "      <td>SD</td>\n",
       "      <td>46</td>\n",
       "      <td>093</td>\n",
       "      <td>Meade County</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2408</th>\n",
       "      <td>SD</td>\n",
       "      <td>46</td>\n",
       "      <td>095</td>\n",
       "      <td>Mellette County</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2409</th>\n",
       "      <td>SD</td>\n",
       "      <td>46</td>\n",
       "      <td>097</td>\n",
       "      <td>Miner County</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2410</th>\n",
       "      <td>SD</td>\n",
       "      <td>46</td>\n",
       "      <td>099</td>\n",
       "      <td>Minnehaha County</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2411</th>\n",
       "      <td>SD</td>\n",
       "      <td>46</td>\n",
       "      <td>101</td>\n",
       "      <td>Moody County</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2412</th>\n",
       "      <td>SD</td>\n",
       "      <td>46</td>\n",
       "      <td>103</td>\n",
       "      <td>Pennington County</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2413</th>\n",
       "      <td>SD</td>\n",
       "      <td>46</td>\n",
       "      <td>105</td>\n",
       "      <td>Perkins County</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2414</th>\n",
       "      <td>SD</td>\n",
       "      <td>46</td>\n",
       "      <td>107</td>\n",
       "      <td>Potter County</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2415</th>\n",
       "      <td>SD</td>\n",
       "      <td>46</td>\n",
       "      <td>109</td>\n",
       "      <td>Roberts County</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2416</th>\n",
       "      <td>SD</td>\n",
       "      <td>46</td>\n",
       "      <td>111</td>\n",
       "      <td>Sanborn County</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2417</th>\n",
       "      <td>SD</td>\n",
       "      <td>46</td>\n",
       "      <td>113</td>\n",
       "      <td>Oglala County</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2418</th>\n",
       "      <td>SD</td>\n",
       "      <td>46</td>\n",
       "      <td>115</td>\n",
       "      <td>Spink County</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2419</th>\n",
       "      <td>SD</td>\n",
       "      <td>46</td>\n",
       "      <td>117</td>\n",
       "      <td>Stanley County</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2420</th>\n",
       "      <td>SD</td>\n",
       "      <td>46</td>\n",
       "      <td>119</td>\n",
       "      <td>Sully County</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2421</th>\n",
       "      <td>SD</td>\n",
       "      <td>46</td>\n",
       "      <td>121</td>\n",
       "      <td>Todd County</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2422</th>\n",
       "      <td>SD</td>\n",
       "      <td>46</td>\n",
       "      <td>123</td>\n",
       "      <td>Tripp County</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2423</th>\n",
       "      <td>SD</td>\n",
       "      <td>46</td>\n",
       "      <td>125</td>\n",
       "      <td>Turner County</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2424</th>\n",
       "      <td>SD</td>\n",
       "      <td>46</td>\n",
       "      <td>127</td>\n",
       "      <td>Union County</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2425</th>\n",
       "      <td>SD</td>\n",
       "      <td>46</td>\n",
       "      <td>129</td>\n",
       "      <td>Walworth County</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2426</th>\n",
       "      <td>SD</td>\n",
       "      <td>46</td>\n",
       "      <td>135</td>\n",
       "      <td>Yankton County</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2427</th>\n",
       "      <td>SD</td>\n",
       "      <td>46</td>\n",
       "      <td>137</td>\n",
       "      <td>Ziebach County</td>\n",
       "      <td>H1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>66 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     state_abbr state_fips county_fips         county_name fips_class_code\n",
       "2362         SD         46         003       Aurora County              H1\n",
       "2363         SD         46         005       Beadle County              H1\n",
       "2364         SD         46         007      Bennett County              H1\n",
       "2365         SD         46         009    Bon Homme County              H1\n",
       "2366         SD         46         011    Brookings County              H1\n",
       "2367         SD         46         013        Brown County              H1\n",
       "2368         SD         46         015        Brule County              H1\n",
       "2369         SD         46         017      Buffalo County              H1\n",
       "2370         SD         46         019        Butte County              H1\n",
       "2371         SD         46         021     Campbell County              H1\n",
       "2372         SD         46         023  Charles Mix County              H1\n",
       "2373         SD         46         025        Clark County              H1\n",
       "2374         SD         46         027         Clay County              H1\n",
       "2375         SD         46         029    Codington County              H1\n",
       "2376         SD         46         031       Corson County              H1\n",
       "2377         SD         46         033       Custer County              H1\n",
       "2378         SD         46         035      Davison County              H1\n",
       "2379         SD         46         037          Day County              H1\n",
       "2380         SD         46         039        Deuel County              H1\n",
       "2381         SD         46         041        Dewey County              H1\n",
       "2382         SD         46         043      Douglas County              H1\n",
       "2383         SD         46         045      Edmunds County              H1\n",
       "2384         SD         46         047   Fall River County              H1\n",
       "2385         SD         46         049        Faulk County              H1\n",
       "2386         SD         46         051        Grant County              H1\n",
       "2387         SD         46         053      Gregory County              H1\n",
       "2388         SD         46         055       Haakon County              H1\n",
       "2389         SD         46         057       Hamlin County              H1\n",
       "2390         SD         46         059         Hand County              H1\n",
       "2391         SD         46         061       Hanson County              H1\n",
       "...         ...        ...         ...                 ...             ...\n",
       "2398         SD         46         075        Jones County              H1\n",
       "2399         SD         46         077    Kingsbury County              H1\n",
       "2400         SD         46         079         Lake County              H1\n",
       "2401         SD         46         081     Lawrence County              H1\n",
       "2402         SD         46         083      Lincoln County              H1\n",
       "2403         SD         46         085        Lyman County              H1\n",
       "2404         SD         46         087       McCook County              H1\n",
       "2405         SD         46         089    McPherson County              H1\n",
       "2406         SD         46         091     Marshall County              H1\n",
       "2407         SD         46         093        Meade County              H1\n",
       "2408         SD         46         095     Mellette County              H1\n",
       "2409         SD         46         097        Miner County              H1\n",
       "2410         SD         46         099    Minnehaha County              H1\n",
       "2411         SD         46         101        Moody County              H1\n",
       "2412         SD         46         103   Pennington County              H1\n",
       "2413         SD         46         105      Perkins County              H1\n",
       "2414         SD         46         107       Potter County              H1\n",
       "2415         SD         46         109      Roberts County              H1\n",
       "2416         SD         46         111      Sanborn County              H1\n",
       "2417         SD         46         113       Oglala County              H1\n",
       "2418         SD         46         115        Spink County              H1\n",
       "2419         SD         46         117      Stanley County              H1\n",
       "2420         SD         46         119        Sully County              H1\n",
       "2421         SD         46         121         Todd County              H1\n",
       "2422         SD         46         123        Tripp County              H1\n",
       "2423         SD         46         125       Turner County              H1\n",
       "2424         SD         46         127        Union County              H1\n",
       "2425         SD         46         129     Walworth County              H1\n",
       "2426         SD         46         135      Yankton County              H1\n",
       "2427         SD         46         137      Ziebach County              H1\n",
       "\n",
       "[66 rows x 5 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# change Shannon County, SD to Oglala County, SD\n",
    "# http://rapidcityjournal.com/news/local/it-s-official-oglala-lakota-county-replaces-shannon-county-name/article_ac5c2369-3fea-5f94-9898-b007b7ddf22c.html\n",
    "# townhall.loc[townhall['county_name'] == 'Sainte Genevieve', 'county_name'] = 'Ste. Genevieve County'\n",
    "census.loc[(census['county_name'] == 'Shannon County') & (census['state_abbr'] == 'SD'), 'county_name'] = 'Oglala County'\n",
    "census[(census['state_abbr'] == 'SD')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# state of Alaska reports results at the precinct and state level; no county level data available\n",
    "# report results as the states level; \n",
    "# ugly fix to get townhall results and census counties to work together\n",
    "# future plan: roll up precinct-level results to the county level\n",
    "census.loc[(census['state_abbr'] == 'AK'),'county_name'] = 'Alaska'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1216          MDbaltimore\n",
       "1597           MOst.louis\n",
       "1763         NVcarsoncity\n",
       "2915         VAalexandria\n",
       "2916            VAbedford\n",
       "2917            VAbristol\n",
       "2918         VAbuenavista\n",
       "2919    VAcharlottesville\n",
       "2920         VAchesapeake\n",
       "2921    VAcolonialheights\n",
       "2922          VAcovington\n",
       "2923           VAdanville\n",
       "2924            VAemporia\n",
       "2925            VAfairfax\n",
       "2926        VAfallschurch\n",
       "2927           VAfranklin\n",
       "2928     VAfredericksburg\n",
       "2929              VAgalax\n",
       "2930            VAhampton\n",
       "2931       VAharrisonburg\n",
       "2932           VAhopewell\n",
       "2933          VAlexington\n",
       "2934          VAlynchburg\n",
       "2935           VAmanassas\n",
       "2936       VAmanassaspark\n",
       "2937       VAmartinsville\n",
       "2938        VAnewportnews\n",
       "2939            VAnorfolk\n",
       "2940             VAnorton\n",
       "2941         VApetersburg\n",
       "2942           VApoquoson\n",
       "2943         VAportsmouth\n",
       "2944            VAradford\n",
       "2945           VArichmond\n",
       "2946            VAroanoke\n",
       "2947              VAsalem\n",
       "2948           VAstaunton\n",
       "2949            VAsuffolk\n",
       "2950      VAvirginiabeach\n",
       "2951         VAwaynesboro\n",
       "2952       VAwilliamsburg\n",
       "2953         VAwinchester\n",
       "dtype: object"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# change county_name values in townhall data to match 'county_name' values for C7 fips class code cities\n",
    "# get and transform C7 city names\n",
    "cities = (census['state_abbr'][(census['fips_class_code'] == 'C7')] + census['county_name'][(census['fips_class_code'] == 'C7')].apply(lambda x: x.replace('city','').replace(' ','').lower()))\n",
    "cities"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0    state_abbr  county_name party  votes_total      combined\n",
      "7943         NV  Carson City   GOP      13125.0  NVcarsoncity\n",
      "7944         NV  Carson City   DEM       9610.0  NVcarsoncity\n",
      "7945         NV  Carson City   LIB       1159.0  NVcarsoncity\n",
      "7946         NV  Carson City   OTH       1122.0  NVcarsoncity\n"
     ]
    }
   ],
   "source": [
    "# loop through 'combined' column and compare to cities series to add 'city' to H1 fips class code to townhall data\n",
    "for i, row in cities.iteritems():\n",
    "    if row <> 'NVcarsoncity':\n",
    "        townhall.loc[townhall['combined'] == row, 'combined'] = row + 'city'\n",
    "    \n",
    "print(townhall[(townhall['combined'] == 'NVcarsoncity')])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0     state_abbr county_name party  votes_total  combined\n",
      "10780         SD      Oglala   DEM       2504.0  SDoglala\n",
      "10781         SD      Oglala   GOP        241.0  SDoglala\n",
      "10782         SD      Oglala   LIB        104.0  SDoglala\n",
      "10783         SD      Oglala   OTH         47.0  SDoglala\n"
     ]
    }
   ],
   "source": [
    "# remove 'county' from 'combined' column of C7 fips class code counties in townhall\n",
    "townhall['combined'] = townhall['combined'].str.replace('county','')\n",
    "print(townhall[(townhall['county_name'] == 'Oglala')])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     state_abbr state_fips county_fips  county_name fips_class_code  \\\n",
      "1763         NV         32         510  Carson City              C7   \n",
      "\n",
      "          combined  \n",
      "1763  NVcarsoncity  \n"
     ]
    }
   ],
   "source": [
    "census['combined'] = census['state_abbr'] + census['county_name'].apply(lambda x: x.replace('County','').replace('Parish','').replace(' ','').lower())\n",
    "print(census[(census['state_abbr'] == 'NV') & (census['county_name'] == 'Carson City')])\n",
    "# print(census[(census['state_abbr'] == 'VA') & (census['county_name'] == 'Bedford County')])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0    state_abbr  county_name party      combined  total_votes\n",
      "7943         NV  Carson City   GOP  NVcarsoncity      25016.0\n",
      "7944         NV  Carson City   DEM  NVcarsoncity      25016.0\n",
      "7945         NV  Carson City   LIB  NVcarsoncity      25016.0\n",
      "7946         NV  Carson City   OTH  NVcarsoncity      25016.0\n"
     ]
    }
   ],
   "source": [
    "# return sum of votes by state and county\n",
    "townhall['total_votes'] = townhall['votes_total'].groupby(townhall['combined']).transform('sum')\n",
    "townhall_counties = townhall.drop('votes_total',axis=1)\n",
    "\n",
    "# view dataset by selected state\n",
    "print(townhall_counties[(townhall_counties['state_abbr'] == 'NV') & (townhall_counties['county_name'] == 'Carson City')])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Combine data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Joined dataset has 14422 items\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>combined</th>\n",
       "      <th>state_abbr</th>\n",
       "      <th>state_fips</th>\n",
       "      <th>county_fips</th>\n",
       "      <th>county_name</th>\n",
       "      <th>fips_class_code</th>\n",
       "      <th>state_abbr_r</th>\n",
       "      <th>county_name_r</th>\n",
       "      <th>party</th>\n",
       "      <th>votes_total</th>\n",
       "      <th>total_votes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>8968</th>\n",
       "      <td>NVcarsoncity</td>\n",
       "      <td>NV</td>\n",
       "      <td>32</td>\n",
       "      <td>510</td>\n",
       "      <td>Carson City</td>\n",
       "      <td>C7</td>\n",
       "      <td>NV</td>\n",
       "      <td>Carson City</td>\n",
       "      <td>GOP</td>\n",
       "      <td>13125.0</td>\n",
       "      <td>25016.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8969</th>\n",
       "      <td>NVcarsoncity</td>\n",
       "      <td>NV</td>\n",
       "      <td>32</td>\n",
       "      <td>510</td>\n",
       "      <td>Carson City</td>\n",
       "      <td>C7</td>\n",
       "      <td>NV</td>\n",
       "      <td>Carson City</td>\n",
       "      <td>DEM</td>\n",
       "      <td>9610.0</td>\n",
       "      <td>25016.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8970</th>\n",
       "      <td>NVcarsoncity</td>\n",
       "      <td>NV</td>\n",
       "      <td>32</td>\n",
       "      <td>510</td>\n",
       "      <td>Carson City</td>\n",
       "      <td>C7</td>\n",
       "      <td>NV</td>\n",
       "      <td>Carson City</td>\n",
       "      <td>LIB</td>\n",
       "      <td>1159.0</td>\n",
       "      <td>25016.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8971</th>\n",
       "      <td>NVcarsoncity</td>\n",
       "      <td>NV</td>\n",
       "      <td>32</td>\n",
       "      <td>510</td>\n",
       "      <td>Carson City</td>\n",
       "      <td>C7</td>\n",
       "      <td>NV</td>\n",
       "      <td>Carson City</td>\n",
       "      <td>OTH</td>\n",
       "      <td>1122.0</td>\n",
       "      <td>25016.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          combined state_abbr state_fips county_fips  county_name  \\\n",
       "8968  NVcarsoncity         NV         32         510  Carson City   \n",
       "8969  NVcarsoncity         NV         32         510  Carson City   \n",
       "8970  NVcarsoncity         NV         32         510  Carson City   \n",
       "8971  NVcarsoncity         NV         32         510  Carson City   \n",
       "\n",
       "     fips_class_code state_abbr_r county_name_r party  votes_total  \\\n",
       "8968              C7           NV   Carson City   GOP      13125.0   \n",
       "8969              C7           NV   Carson City   DEM       9610.0   \n",
       "8970              C7           NV   Carson City   LIB       1159.0   \n",
       "8971              C7           NV   Carson City   OTH       1122.0   \n",
       "\n",
       "      total_votes  \n",
       "8968      25016.0  \n",
       "8969      25016.0  \n",
       "8970      25016.0  \n",
       "8971      25016.0  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# join census and townhall data on the 'combined' column\n",
    "right = townhall.set_index('combined')\n",
    "left = census.set_index('combined')\n",
    "\n",
    "combined = left.join(right, lsuffix='', rsuffix='_r')\n",
    "combined = combined.reset_index()\n",
    "print('Joined dataset has ' + str(combined.shape[0]) + ' items')\n",
    "\n",
    "# view data by selected state\n",
    "combined[(combined['state_abbr'] == 'NV') & (combined['fips_class_code'] == 'C7')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>combined</th>\n",
       "      <th>state_abbr</th>\n",
       "      <th>state_fips</th>\n",
       "      <th>county_fips</th>\n",
       "      <th>county_name</th>\n",
       "      <th>fips_class_code</th>\n",
       "      <th>state_abbr_r</th>\n",
       "      <th>county_name_r</th>\n",
       "      <th>party</th>\n",
       "      <th>votes_total</th>\n",
       "      <th>total_votes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AKalaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>013</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>H1</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>GOP</td>\n",
       "      <td>4497.0</td>\n",
       "      <td>246588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AKalaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>013</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>H1</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>DEM</td>\n",
       "      <td>3207.0</td>\n",
       "      <td>246588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AKalaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>013</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>H1</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>LIB</td>\n",
       "      <td>503.0</td>\n",
       "      <td>246588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AKalaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>013</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>H1</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>GRN</td>\n",
       "      <td>153.0</td>\n",
       "      <td>246588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AKalaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>013</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>H1</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>OTH</td>\n",
       "      <td>142.0</td>\n",
       "      <td>246588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>AKalaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>016</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>H5</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>GOP</td>\n",
       "      <td>4497.0</td>\n",
       "      <td>246588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>AKalaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>016</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>H5</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>DEM</td>\n",
       "      <td>3207.0</td>\n",
       "      <td>246588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>AKalaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>016</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>H5</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>LIB</td>\n",
       "      <td>503.0</td>\n",
       "      <td>246588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>AKalaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>016</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>H5</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>GRN</td>\n",
       "      <td>153.0</td>\n",
       "      <td>246588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>AKalaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>016</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>H5</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>OTH</td>\n",
       "      <td>142.0</td>\n",
       "      <td>246588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>AKalaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>020</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>H6</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>GOP</td>\n",
       "      <td>4497.0</td>\n",
       "      <td>246588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>AKalaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>020</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>H6</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>DEM</td>\n",
       "      <td>3207.0</td>\n",
       "      <td>246588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>AKalaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>020</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>H6</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>LIB</td>\n",
       "      <td>503.0</td>\n",
       "      <td>246588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>AKalaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>020</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>H6</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>GRN</td>\n",
       "      <td>153.0</td>\n",
       "      <td>246588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>AKalaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>020</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>H6</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>OTH</td>\n",
       "      <td>142.0</td>\n",
       "      <td>246588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>AKalaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>050</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>H5</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>GOP</td>\n",
       "      <td>4497.0</td>\n",
       "      <td>246588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>AKalaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>050</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>H5</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>DEM</td>\n",
       "      <td>3207.0</td>\n",
       "      <td>246588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>AKalaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>050</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>H5</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>LIB</td>\n",
       "      <td>503.0</td>\n",
       "      <td>246588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>AKalaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>050</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>H5</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>GRN</td>\n",
       "      <td>153.0</td>\n",
       "      <td>246588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>AKalaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>050</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>H5</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>OTH</td>\n",
       "      <td>142.0</td>\n",
       "      <td>246588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>AKalaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>060</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>H1</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>GOP</td>\n",
       "      <td>4497.0</td>\n",
       "      <td>246588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>AKalaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>060</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>H1</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>DEM</td>\n",
       "      <td>3207.0</td>\n",
       "      <td>246588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>AKalaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>060</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>H1</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>LIB</td>\n",
       "      <td>503.0</td>\n",
       "      <td>246588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>AKalaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>060</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>H1</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>GRN</td>\n",
       "      <td>153.0</td>\n",
       "      <td>246588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>AKalaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>060</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>H1</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>OTH</td>\n",
       "      <td>142.0</td>\n",
       "      <td>246588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>AKalaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>068</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>H1</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>GOP</td>\n",
       "      <td>4497.0</td>\n",
       "      <td>246588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>AKalaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>068</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>H1</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>DEM</td>\n",
       "      <td>3207.0</td>\n",
       "      <td>246588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>AKalaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>068</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>H1</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>LIB</td>\n",
       "      <td>503.0</td>\n",
       "      <td>246588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>AKalaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>068</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>H1</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>GRN</td>\n",
       "      <td>153.0</td>\n",
       "      <td>246588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>AKalaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>068</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>H1</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>OTH</td>\n",
       "      <td>142.0</td>\n",
       "      <td>246588.0</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>115</th>\n",
       "      <td>AKalaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>240</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>H5</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>GOP</td>\n",
       "      <td>4497.0</td>\n",
       "      <td>246588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>116</th>\n",
       "      <td>AKalaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>240</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>H5</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>DEM</td>\n",
       "      <td>3207.0</td>\n",
       "      <td>246588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>117</th>\n",
       "      <td>AKalaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>240</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>H5</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>LIB</td>\n",
       "      <td>503.0</td>\n",
       "      <td>246588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>118</th>\n",
       "      <td>AKalaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>240</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>H5</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>GRN</td>\n",
       "      <td>153.0</td>\n",
       "      <td>246588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>119</th>\n",
       "      <td>AKalaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>240</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>H5</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>OTH</td>\n",
       "      <td>142.0</td>\n",
       "      <td>246588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120</th>\n",
       "      <td>AKalaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>261</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>H5</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>GOP</td>\n",
       "      <td>4497.0</td>\n",
       "      <td>246588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121</th>\n",
       "      <td>AKalaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>261</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>H5</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>DEM</td>\n",
       "      <td>3207.0</td>\n",
       "      <td>246588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122</th>\n",
       "      <td>AKalaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>261</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>H5</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>LIB</td>\n",
       "      <td>503.0</td>\n",
       "      <td>246588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>AKalaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>261</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>H5</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>GRN</td>\n",
       "      <td>153.0</td>\n",
       "      <td>246588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124</th>\n",
       "      <td>AKalaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>261</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>H5</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>OTH</td>\n",
       "      <td>142.0</td>\n",
       "      <td>246588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>125</th>\n",
       "      <td>AKalaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>270</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>H5</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>GOP</td>\n",
       "      <td>4497.0</td>\n",
       "      <td>246588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126</th>\n",
       "      <td>AKalaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>270</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>H5</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>DEM</td>\n",
       "      <td>3207.0</td>\n",
       "      <td>246588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>127</th>\n",
       "      <td>AKalaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>270</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>H5</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>LIB</td>\n",
       "      <td>503.0</td>\n",
       "      <td>246588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>128</th>\n",
       "      <td>AKalaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>270</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>H5</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>GRN</td>\n",
       "      <td>153.0</td>\n",
       "      <td>246588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>129</th>\n",
       "      <td>AKalaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>270</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>H5</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>OTH</td>\n",
       "      <td>142.0</td>\n",
       "      <td>246588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>130</th>\n",
       "      <td>AKalaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>275</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>H1</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>GOP</td>\n",
       "      <td>4497.0</td>\n",
       "      <td>246588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>131</th>\n",
       "      <td>AKalaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>275</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>H1</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>DEM</td>\n",
       "      <td>3207.0</td>\n",
       "      <td>246588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>132</th>\n",
       "      <td>AKalaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>275</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>H1</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>LIB</td>\n",
       "      <td>503.0</td>\n",
       "      <td>246588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>133</th>\n",
       "      <td>AKalaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>275</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>H1</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>GRN</td>\n",
       "      <td>153.0</td>\n",
       "      <td>246588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>134</th>\n",
       "      <td>AKalaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>275</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>H1</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>OTH</td>\n",
       "      <td>142.0</td>\n",
       "      <td>246588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>135</th>\n",
       "      <td>AKalaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>282</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>H1</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>GOP</td>\n",
       "      <td>4497.0</td>\n",
       "      <td>246588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>136</th>\n",
       "      <td>AKalaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>282</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>H1</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>DEM</td>\n",
       "      <td>3207.0</td>\n",
       "      <td>246588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>137</th>\n",
       "      <td>AKalaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>282</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>H1</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>LIB</td>\n",
       "      <td>503.0</td>\n",
       "      <td>246588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>138</th>\n",
       "      <td>AKalaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>282</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>H1</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>GRN</td>\n",
       "      <td>153.0</td>\n",
       "      <td>246588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>139</th>\n",
       "      <td>AKalaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>282</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>H1</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>OTH</td>\n",
       "      <td>142.0</td>\n",
       "      <td>246588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>140</th>\n",
       "      <td>AKalaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>290</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>H5</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>GOP</td>\n",
       "      <td>4497.0</td>\n",
       "      <td>246588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141</th>\n",
       "      <td>AKalaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>290</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>H5</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>DEM</td>\n",
       "      <td>3207.0</td>\n",
       "      <td>246588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>142</th>\n",
       "      <td>AKalaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>290</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>H5</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>LIB</td>\n",
       "      <td>503.0</td>\n",
       "      <td>246588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143</th>\n",
       "      <td>AKalaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>290</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>H5</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>GRN</td>\n",
       "      <td>153.0</td>\n",
       "      <td>246588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>144</th>\n",
       "      <td>AKalaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>290</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>H5</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>OTH</td>\n",
       "      <td>142.0</td>\n",
       "      <td>246588.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>145 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     combined state_abbr state_fips county_fips county_name fips_class_code  \\\n",
       "0    AKalaska         AK         02         013      Alaska              H1   \n",
       "1    AKalaska         AK         02         013      Alaska              H1   \n",
       "2    AKalaska         AK         02         013      Alaska              H1   \n",
       "3    AKalaska         AK         02         013      Alaska              H1   \n",
       "4    AKalaska         AK         02         013      Alaska              H1   \n",
       "5    AKalaska         AK         02         016      Alaska              H5   \n",
       "6    AKalaska         AK         02         016      Alaska              H5   \n",
       "7    AKalaska         AK         02         016      Alaska              H5   \n",
       "8    AKalaska         AK         02         016      Alaska              H5   \n",
       "9    AKalaska         AK         02         016      Alaska              H5   \n",
       "10   AKalaska         AK         02         020      Alaska              H6   \n",
       "11   AKalaska         AK         02         020      Alaska              H6   \n",
       "12   AKalaska         AK         02         020      Alaska              H6   \n",
       "13   AKalaska         AK         02         020      Alaska              H6   \n",
       "14   AKalaska         AK         02         020      Alaska              H6   \n",
       "15   AKalaska         AK         02         050      Alaska              H5   \n",
       "16   AKalaska         AK         02         050      Alaska              H5   \n",
       "17   AKalaska         AK         02         050      Alaska              H5   \n",
       "18   AKalaska         AK         02         050      Alaska              H5   \n",
       "19   AKalaska         AK         02         050      Alaska              H5   \n",
       "20   AKalaska         AK         02         060      Alaska              H1   \n",
       "21   AKalaska         AK         02         060      Alaska              H1   \n",
       "22   AKalaska         AK         02         060      Alaska              H1   \n",
       "23   AKalaska         AK         02         060      Alaska              H1   \n",
       "24   AKalaska         AK         02         060      Alaska              H1   \n",
       "25   AKalaska         AK         02         068      Alaska              H1   \n",
       "26   AKalaska         AK         02         068      Alaska              H1   \n",
       "27   AKalaska         AK         02         068      Alaska              H1   \n",
       "28   AKalaska         AK         02         068      Alaska              H1   \n",
       "29   AKalaska         AK         02         068      Alaska              H1   \n",
       "..        ...        ...        ...         ...         ...             ...   \n",
       "115  AKalaska         AK         02         240      Alaska              H5   \n",
       "116  AKalaska         AK         02         240      Alaska              H5   \n",
       "117  AKalaska         AK         02         240      Alaska              H5   \n",
       "118  AKalaska         AK         02         240      Alaska              H5   \n",
       "119  AKalaska         AK         02         240      Alaska              H5   \n",
       "120  AKalaska         AK         02         261      Alaska              H5   \n",
       "121  AKalaska         AK         02         261      Alaska              H5   \n",
       "122  AKalaska         AK         02         261      Alaska              H5   \n",
       "123  AKalaska         AK         02         261      Alaska              H5   \n",
       "124  AKalaska         AK         02         261      Alaska              H5   \n",
       "125  AKalaska         AK         02         270      Alaska              H5   \n",
       "126  AKalaska         AK         02         270      Alaska              H5   \n",
       "127  AKalaska         AK         02         270      Alaska              H5   \n",
       "128  AKalaska         AK         02         270      Alaska              H5   \n",
       "129  AKalaska         AK         02         270      Alaska              H5   \n",
       "130  AKalaska         AK         02         275      Alaska              H1   \n",
       "131  AKalaska         AK         02         275      Alaska              H1   \n",
       "132  AKalaska         AK         02         275      Alaska              H1   \n",
       "133  AKalaska         AK         02         275      Alaska              H1   \n",
       "134  AKalaska         AK         02         275      Alaska              H1   \n",
       "135  AKalaska         AK         02         282      Alaska              H1   \n",
       "136  AKalaska         AK         02         282      Alaska              H1   \n",
       "137  AKalaska         AK         02         282      Alaska              H1   \n",
       "138  AKalaska         AK         02         282      Alaska              H1   \n",
       "139  AKalaska         AK         02         282      Alaska              H1   \n",
       "140  AKalaska         AK         02         290      Alaska              H5   \n",
       "141  AKalaska         AK         02         290      Alaska              H5   \n",
       "142  AKalaska         AK         02         290      Alaska              H5   \n",
       "143  AKalaska         AK         02         290      Alaska              H5   \n",
       "144  AKalaska         AK         02         290      Alaska              H5   \n",
       "\n",
       "    state_abbr_r county_name_r party  votes_total  total_votes  \n",
       "0             AK        Alaska   GOP       4497.0     246588.0  \n",
       "1             AK        Alaska   DEM       3207.0     246588.0  \n",
       "2             AK        Alaska   LIB        503.0     246588.0  \n",
       "3             AK        Alaska   GRN        153.0     246588.0  \n",
       "4             AK        Alaska   OTH        142.0     246588.0  \n",
       "5             AK        Alaska   GOP       4497.0     246588.0  \n",
       "6             AK        Alaska   DEM       3207.0     246588.0  \n",
       "7             AK        Alaska   LIB        503.0     246588.0  \n",
       "8             AK        Alaska   GRN        153.0     246588.0  \n",
       "9             AK        Alaska   OTH        142.0     246588.0  \n",
       "10            AK        Alaska   GOP       4497.0     246588.0  \n",
       "11            AK        Alaska   DEM       3207.0     246588.0  \n",
       "12            AK        Alaska   LIB        503.0     246588.0  \n",
       "13            AK        Alaska   GRN        153.0     246588.0  \n",
       "14            AK        Alaska   OTH        142.0     246588.0  \n",
       "15            AK        Alaska   GOP       4497.0     246588.0  \n",
       "16            AK        Alaska   DEM       3207.0     246588.0  \n",
       "17            AK        Alaska   LIB        503.0     246588.0  \n",
       "18            AK        Alaska   GRN        153.0     246588.0  \n",
       "19            AK        Alaska   OTH        142.0     246588.0  \n",
       "20            AK        Alaska   GOP       4497.0     246588.0  \n",
       "21            AK        Alaska   DEM       3207.0     246588.0  \n",
       "22            AK        Alaska   LIB        503.0     246588.0  \n",
       "23            AK        Alaska   GRN        153.0     246588.0  \n",
       "24            AK        Alaska   OTH        142.0     246588.0  \n",
       "25            AK        Alaska   GOP       4497.0     246588.0  \n",
       "26            AK        Alaska   DEM       3207.0     246588.0  \n",
       "27            AK        Alaska   LIB        503.0     246588.0  \n",
       "28            AK        Alaska   GRN        153.0     246588.0  \n",
       "29            AK        Alaska   OTH        142.0     246588.0  \n",
       "..           ...           ...   ...          ...          ...  \n",
       "115           AK        Alaska   GOP       4497.0     246588.0  \n",
       "116           AK        Alaska   DEM       3207.0     246588.0  \n",
       "117           AK        Alaska   LIB        503.0     246588.0  \n",
       "118           AK        Alaska   GRN        153.0     246588.0  \n",
       "119           AK        Alaska   OTH        142.0     246588.0  \n",
       "120           AK        Alaska   GOP       4497.0     246588.0  \n",
       "121           AK        Alaska   DEM       3207.0     246588.0  \n",
       "122           AK        Alaska   LIB        503.0     246588.0  \n",
       "123           AK        Alaska   GRN        153.0     246588.0  \n",
       "124           AK        Alaska   OTH        142.0     246588.0  \n",
       "125           AK        Alaska   GOP       4497.0     246588.0  \n",
       "126           AK        Alaska   DEM       3207.0     246588.0  \n",
       "127           AK        Alaska   LIB        503.0     246588.0  \n",
       "128           AK        Alaska   GRN        153.0     246588.0  \n",
       "129           AK        Alaska   OTH        142.0     246588.0  \n",
       "130           AK        Alaska   GOP       4497.0     246588.0  \n",
       "131           AK        Alaska   DEM       3207.0     246588.0  \n",
       "132           AK        Alaska   LIB        503.0     246588.0  \n",
       "133           AK        Alaska   GRN        153.0     246588.0  \n",
       "134           AK        Alaska   OTH        142.0     246588.0  \n",
       "135           AK        Alaska   GOP       4497.0     246588.0  \n",
       "136           AK        Alaska   DEM       3207.0     246588.0  \n",
       "137           AK        Alaska   LIB        503.0     246588.0  \n",
       "138           AK        Alaska   GRN        153.0     246588.0  \n",
       "139           AK        Alaska   OTH        142.0     246588.0  \n",
       "140           AK        Alaska   GOP       4497.0     246588.0  \n",
       "141           AK        Alaska   DEM       3207.0     246588.0  \n",
       "142           AK        Alaska   LIB        503.0     246588.0  \n",
       "143           AK        Alaska   GRN        153.0     246588.0  \n",
       "144           AK        Alaska   OTH        142.0     246588.0  \n",
       "\n",
       "[145 rows x 11 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# scale Alaska by number of counties\n",
    "combined.loc[(combined['state_abbr'] == 'AK'),'votes_total'] = (combined['votes_total'][combined['state_abbr'] == 'AK']/ak_counties).astype(int)\n",
    "combined[combined['state_abbr'] == 'AK']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Combined dataset has 14421 total items\n",
      "Filtered dataset has 6282 D and R items\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>combined</th>\n",
       "      <th>state_abbr</th>\n",
       "      <th>state_fips</th>\n",
       "      <th>county_fips</th>\n",
       "      <th>county_name</th>\n",
       "      <th>fips_class_code</th>\n",
       "      <th>total_votes</th>\n",
       "      <th>votes_dem</th>\n",
       "      <th>votes_gop</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>8968</th>\n",
       "      <td>NVcarsoncity</td>\n",
       "      <td>NV</td>\n",
       "      <td>32</td>\n",
       "      <td>510</td>\n",
       "      <td>Carson City</td>\n",
       "      <td>C7</td>\n",
       "      <td>25016.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>13125.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8969</th>\n",
       "      <td>NVcarsoncity</td>\n",
       "      <td>NV</td>\n",
       "      <td>32</td>\n",
       "      <td>510</td>\n",
       "      <td>Carson City</td>\n",
       "      <td>C7</td>\n",
       "      <td>25016.0</td>\n",
       "      <td>9610.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          combined state_abbr state_fips county_fips  county_name  \\\n",
       "8968  NVcarsoncity         NV         32         510  Carson City   \n",
       "8969  NVcarsoncity         NV         32         510  Carson City   \n",
       "\n",
       "     fips_class_code  total_votes  votes_dem  votes_gop  \n",
       "8968              C7      25016.0        0.0    13125.0  \n",
       "8969              C7      25016.0     9610.0        0.0  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# return unique dataset\n",
    "county_level_combined = combined.drop_duplicates()\n",
    "print('Combined dataset has ' + str(county_level_combined.shape[0]) + ' total items')\n",
    "                                                   \n",
    "# return only D and R results\n",
    "county_level_combined = county_level_combined[(county_level_combined['party'] == 'GOP') | (county_level_combined['party'] == 'DEM')]\n",
    "print('Filtered dataset has ' + str(county_level_combined.shape[0]) + ' D and R items')\n",
    "\n",
    "# flatten dataset by adding votes by R and D columns\n",
    "county_level_combined['votes_dem'] = county_level_combined['votes_total'].where(county_level_combined['party'] == 'DEM',0).astype('float32')\n",
    "county_level_combined['votes_gop'] = county_level_combined['votes_total'].where(county_level_combined['party'] == 'GOP',0).astype('float32')\n",
    "\n",
    "# drop party and party-level totals and other columns\n",
    "county_level_combined.drop(['party','votes_total','state_abbr_r','county_name_r'], axis=1, inplace=True)\n",
    "# total_results = county_level_combined.drop(['party','votes_total','fips_class_code','state_abbr_r','county_name_r', 'votes_dem', 'votes_gop'], axis=1, inplace=True)\n",
    "county_level_combined[(county_level_combined['state_abbr'] == 'NV') & (county_level_combined['fips_class_code'] == 'C7')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Joined dataset has 3113 items\n",
      "Joined dataset has 3141 items\n",
      "Final dataset has 3141 items\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>votes_dem</th>\n",
       "      <th>votes_gop</th>\n",
       "      <th>total_votes</th>\n",
       "      <th>per_dem</th>\n",
       "      <th>per_gop</th>\n",
       "      <th>diff</th>\n",
       "      <th>per_point_diff</th>\n",
       "      <th>state_abbr</th>\n",
       "      <th>county_name</th>\n",
       "      <th>combined_fips</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>93003.0</td>\n",
       "      <td>130413.0</td>\n",
       "      <td>246588.0</td>\n",
       "      <td>0.377159</td>\n",
       "      <td>0.52887</td>\n",
       "      <td>37,410</td>\n",
       "      <td>15.17%</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>2013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>93003.0</td>\n",
       "      <td>130413.0</td>\n",
       "      <td>246588.0</td>\n",
       "      <td>0.377159</td>\n",
       "      <td>0.52887</td>\n",
       "      <td>37,410</td>\n",
       "      <td>15.17%</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>93003.0</td>\n",
       "      <td>130413.0</td>\n",
       "      <td>246588.0</td>\n",
       "      <td>0.377159</td>\n",
       "      <td>0.52887</td>\n",
       "      <td>37,410</td>\n",
       "      <td>15.17%</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>93003.0</td>\n",
       "      <td>130413.0</td>\n",
       "      <td>246588.0</td>\n",
       "      <td>0.377159</td>\n",
       "      <td>0.52887</td>\n",
       "      <td>37,410</td>\n",
       "      <td>15.17%</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>2050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>93003.0</td>\n",
       "      <td>130413.0</td>\n",
       "      <td>246588.0</td>\n",
       "      <td>0.377159</td>\n",
       "      <td>0.52887</td>\n",
       "      <td>37,410</td>\n",
       "      <td>15.17%</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>2060</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>93003.0</td>\n",
       "      <td>130413.0</td>\n",
       "      <td>246588.0</td>\n",
       "      <td>0.377159</td>\n",
       "      <td>0.52887</td>\n",
       "      <td>37,410</td>\n",
       "      <td>15.17%</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>2068</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>93003.0</td>\n",
       "      <td>130413.0</td>\n",
       "      <td>246588.0</td>\n",
       "      <td>0.377159</td>\n",
       "      <td>0.52887</td>\n",
       "      <td>37,410</td>\n",
       "      <td>15.17%</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>2070</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>93003.0</td>\n",
       "      <td>130413.0</td>\n",
       "      <td>246588.0</td>\n",
       "      <td>0.377159</td>\n",
       "      <td>0.52887</td>\n",
       "      <td>37,410</td>\n",
       "      <td>15.17%</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>2090</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>93003.0</td>\n",
       "      <td>130413.0</td>\n",
       "      <td>246588.0</td>\n",
       "      <td>0.377159</td>\n",
       "      <td>0.52887</td>\n",
       "      <td>37,410</td>\n",
       "      <td>15.17%</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>2100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>93003.0</td>\n",
       "      <td>130413.0</td>\n",
       "      <td>246588.0</td>\n",
       "      <td>0.377159</td>\n",
       "      <td>0.52887</td>\n",
       "      <td>37,410</td>\n",
       "      <td>15.17%</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>2105</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>93003.0</td>\n",
       "      <td>130413.0</td>\n",
       "      <td>246588.0</td>\n",
       "      <td>0.377159</td>\n",
       "      <td>0.52887</td>\n",
       "      <td>37,410</td>\n",
       "      <td>15.17%</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>2110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>93003.0</td>\n",
       "      <td>130413.0</td>\n",
       "      <td>246588.0</td>\n",
       "      <td>0.377159</td>\n",
       "      <td>0.52887</td>\n",
       "      <td>37,410</td>\n",
       "      <td>15.17%</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>2122</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>93003.0</td>\n",
       "      <td>130413.0</td>\n",
       "      <td>246588.0</td>\n",
       "      <td>0.377159</td>\n",
       "      <td>0.52887</td>\n",
       "      <td>37,410</td>\n",
       "      <td>15.17%</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>2130</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>93003.0</td>\n",
       "      <td>130413.0</td>\n",
       "      <td>246588.0</td>\n",
       "      <td>0.377159</td>\n",
       "      <td>0.52887</td>\n",
       "      <td>37,410</td>\n",
       "      <td>15.17%</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>2150</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>93003.0</td>\n",
       "      <td>130413.0</td>\n",
       "      <td>246588.0</td>\n",
       "      <td>0.377159</td>\n",
       "      <td>0.52887</td>\n",
       "      <td>37,410</td>\n",
       "      <td>15.17%</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>2164</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>93003.0</td>\n",
       "      <td>130413.0</td>\n",
       "      <td>246588.0</td>\n",
       "      <td>0.377159</td>\n",
       "      <td>0.52887</td>\n",
       "      <td>37,410</td>\n",
       "      <td>15.17%</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>2170</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>93003.0</td>\n",
       "      <td>130413.0</td>\n",
       "      <td>246588.0</td>\n",
       "      <td>0.377159</td>\n",
       "      <td>0.52887</td>\n",
       "      <td>37,410</td>\n",
       "      <td>15.17%</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>2180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>93003.0</td>\n",
       "      <td>130413.0</td>\n",
       "      <td>246588.0</td>\n",
       "      <td>0.377159</td>\n",
       "      <td>0.52887</td>\n",
       "      <td>37,410</td>\n",
       "      <td>15.17%</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>2185</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>93003.0</td>\n",
       "      <td>130413.0</td>\n",
       "      <td>246588.0</td>\n",
       "      <td>0.377159</td>\n",
       "      <td>0.52887</td>\n",
       "      <td>37,410</td>\n",
       "      <td>15.17%</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>2188</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>93003.0</td>\n",
       "      <td>130413.0</td>\n",
       "      <td>246588.0</td>\n",
       "      <td>0.377159</td>\n",
       "      <td>0.52887</td>\n",
       "      <td>37,410</td>\n",
       "      <td>15.17%</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>2195</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>93003.0</td>\n",
       "      <td>130413.0</td>\n",
       "      <td>246588.0</td>\n",
       "      <td>0.377159</td>\n",
       "      <td>0.52887</td>\n",
       "      <td>37,410</td>\n",
       "      <td>15.17%</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>2198</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>93003.0</td>\n",
       "      <td>130413.0</td>\n",
       "      <td>246588.0</td>\n",
       "      <td>0.377159</td>\n",
       "      <td>0.52887</td>\n",
       "      <td>37,410</td>\n",
       "      <td>15.17%</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>2220</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>93003.0</td>\n",
       "      <td>130413.0</td>\n",
       "      <td>246588.0</td>\n",
       "      <td>0.377159</td>\n",
       "      <td>0.52887</td>\n",
       "      <td>37,410</td>\n",
       "      <td>15.17%</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>2230</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>93003.0</td>\n",
       "      <td>130413.0</td>\n",
       "      <td>246588.0</td>\n",
       "      <td>0.377159</td>\n",
       "      <td>0.52887</td>\n",
       "      <td>37,410</td>\n",
       "      <td>15.17%</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>2240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>93003.0</td>\n",
       "      <td>130413.0</td>\n",
       "      <td>246588.0</td>\n",
       "      <td>0.377159</td>\n",
       "      <td>0.52887</td>\n",
       "      <td>37,410</td>\n",
       "      <td>15.17%</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>2261</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>93003.0</td>\n",
       "      <td>130413.0</td>\n",
       "      <td>246588.0</td>\n",
       "      <td>0.377159</td>\n",
       "      <td>0.52887</td>\n",
       "      <td>37,410</td>\n",
       "      <td>15.17%</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>2270</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>93003.0</td>\n",
       "      <td>130413.0</td>\n",
       "      <td>246588.0</td>\n",
       "      <td>0.377159</td>\n",
       "      <td>0.52887</td>\n",
       "      <td>37,410</td>\n",
       "      <td>15.17%</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>2275</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>93003.0</td>\n",
       "      <td>130413.0</td>\n",
       "      <td>246588.0</td>\n",
       "      <td>0.377159</td>\n",
       "      <td>0.52887</td>\n",
       "      <td>37,410</td>\n",
       "      <td>15.17%</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>2282</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>93003.0</td>\n",
       "      <td>130413.0</td>\n",
       "      <td>246588.0</td>\n",
       "      <td>0.377159</td>\n",
       "      <td>0.52887</td>\n",
       "      <td>37,410</td>\n",
       "      <td>15.17%</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>2290</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    votes_dem  votes_gop  total_votes   per_dem  per_gop    diff  \\\n",
       "0     93003.0   130413.0     246588.0  0.377159  0.52887  37,410   \n",
       "1     93003.0   130413.0     246588.0  0.377159  0.52887  37,410   \n",
       "2     93003.0   130413.0     246588.0  0.377159  0.52887  37,410   \n",
       "3     93003.0   130413.0     246588.0  0.377159  0.52887  37,410   \n",
       "4     93003.0   130413.0     246588.0  0.377159  0.52887  37,410   \n",
       "5     93003.0   130413.0     246588.0  0.377159  0.52887  37,410   \n",
       "6     93003.0   130413.0     246588.0  0.377159  0.52887  37,410   \n",
       "7     93003.0   130413.0     246588.0  0.377159  0.52887  37,410   \n",
       "8     93003.0   130413.0     246588.0  0.377159  0.52887  37,410   \n",
       "9     93003.0   130413.0     246588.0  0.377159  0.52887  37,410   \n",
       "10    93003.0   130413.0     246588.0  0.377159  0.52887  37,410   \n",
       "11    93003.0   130413.0     246588.0  0.377159  0.52887  37,410   \n",
       "12    93003.0   130413.0     246588.0  0.377159  0.52887  37,410   \n",
       "13    93003.0   130413.0     246588.0  0.377159  0.52887  37,410   \n",
       "14    93003.0   130413.0     246588.0  0.377159  0.52887  37,410   \n",
       "15    93003.0   130413.0     246588.0  0.377159  0.52887  37,410   \n",
       "16    93003.0   130413.0     246588.0  0.377159  0.52887  37,410   \n",
       "17    93003.0   130413.0     246588.0  0.377159  0.52887  37,410   \n",
       "18    93003.0   130413.0     246588.0  0.377159  0.52887  37,410   \n",
       "19    93003.0   130413.0     246588.0  0.377159  0.52887  37,410   \n",
       "20    93003.0   130413.0     246588.0  0.377159  0.52887  37,410   \n",
       "21    93003.0   130413.0     246588.0  0.377159  0.52887  37,410   \n",
       "22    93003.0   130413.0     246588.0  0.377159  0.52887  37,410   \n",
       "23    93003.0   130413.0     246588.0  0.377159  0.52887  37,410   \n",
       "24    93003.0   130413.0     246588.0  0.377159  0.52887  37,410   \n",
       "25    93003.0   130413.0     246588.0  0.377159  0.52887  37,410   \n",
       "26    93003.0   130413.0     246588.0  0.377159  0.52887  37,410   \n",
       "27    93003.0   130413.0     246588.0  0.377159  0.52887  37,410   \n",
       "28    93003.0   130413.0     246588.0  0.377159  0.52887  37,410   \n",
       "\n",
       "   per_point_diff state_abbr county_name combined_fips  \n",
       "0          15.17%         AK      Alaska          2013  \n",
       "1          15.17%         AK      Alaska          2016  \n",
       "2          15.17%         AK      Alaska          2020  \n",
       "3          15.17%         AK      Alaska          2050  \n",
       "4          15.17%         AK      Alaska          2060  \n",
       "5          15.17%         AK      Alaska          2068  \n",
       "6          15.17%         AK      Alaska          2070  \n",
       "7          15.17%         AK      Alaska          2090  \n",
       "8          15.17%         AK      Alaska          2100  \n",
       "9          15.17%         AK      Alaska          2105  \n",
       "10         15.17%         AK      Alaska          2110  \n",
       "11         15.17%         AK      Alaska          2122  \n",
       "12         15.17%         AK      Alaska          2130  \n",
       "13         15.17%         AK      Alaska          2150  \n",
       "14         15.17%         AK      Alaska          2164  \n",
       "15         15.17%         AK      Alaska          2170  \n",
       "16         15.17%         AK      Alaska          2180  \n",
       "17         15.17%         AK      Alaska          2185  \n",
       "18         15.17%         AK      Alaska          2188  \n",
       "19         15.17%         AK      Alaska          2195  \n",
       "20         15.17%         AK      Alaska          2198  \n",
       "21         15.17%         AK      Alaska          2220  \n",
       "22         15.17%         AK      Alaska          2230  \n",
       "23         15.17%         AK      Alaska          2240  \n",
       "24         15.17%         AK      Alaska          2261  \n",
       "25         15.17%         AK      Alaska          2270  \n",
       "26         15.17%         AK      Alaska          2275  \n",
       "27         15.17%         AK      Alaska          2282  \n",
       "28         15.17%         AK      Alaska          2290  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# pivot data to consolidate\n",
    "party_pivot = pd.pivot_table(county_level_combined,index=[\"combined\"],values=[\"votes_dem\",\"votes_gop\"],aggfunc=np.sum)\n",
    "total_pivot = pd.pivot_table(county_level_combined,index=[\"combined\"],values=[\"total_votes\"],aggfunc=np.mean)\n",
    "\n",
    "# join party and total pivots\n",
    "combined_pivot = party_pivot.join(total_pivot, lsuffix='', rsuffix='_r')\n",
    "print('Joined dataset has ' + str(combined_pivot.shape[0]) + ' items')\n",
    "combined_pivot\n",
    "\n",
    "# add percentages for each R and D of total votes\n",
    "# calculate percentage of total vote per major candidates\n",
    "combined_pivot['per_dem'] = combined_pivot['votes_dem'] / combined_pivot['total_votes']\n",
    "combined_pivot['per_gop'] = combined_pivot['votes_gop'] / combined_pivot['total_votes']\n",
    "combined_pivot['diff'] = abs(combined_pivot['votes_gop'] - combined_pivot['votes_dem']).map('{:,.0f}'.format)\n",
    "combined_pivot['per_point_diff'] = abs(combined_pivot['per_dem'] - combined_pivot['per_gop']).map('{:,.2%}'.format)\n",
    "\n",
    "combined_pivot\n",
    "\n",
    "# join pivotted and unpivotted data\n",
    "right = census.set_index('combined')\n",
    "# left = combined_pivot.set_index('combined')\n",
    "\n",
    "county_level_final = combined_pivot.join(right, lsuffix='', rsuffix='_r')\n",
    "county_level_final = county_level_final.reset_index()\n",
    "print('Joined dataset has ' + str(county_level_final.shape[0]) + ' items')\n",
    "county_level_final\n",
    "\n",
    "# create FIPS columns for visualizations\n",
    "county_level_final['combined_fips'] = county_level_final['state_fips'].apply(lambda x: x.lstrip('0')) + county_level_final['county_fips']\n",
    "county_level_final\n",
    "\n",
    "# drop irrelevant columns\n",
    "county_level_final = county_level_final.drop(['combined','county_fips','state_fips','fips_class_code'], axis=1)\n",
    "print('Final dataset has ' + str(county_level_final.shape[0]) + ' items')\n",
    "county_level_final\n",
    "\n",
    "# view data by selected state\n",
    "county_level_final[(county_level_final['state_abbr'] == 'AK')]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Export data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "county_level_final.to_csv('2016_US_County_Level_Presidential_Results.csv',sep=',')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Visualize"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "// https://github.com/d3/d3/issues/1693#issuecomment-35556356\n",
       "require.config({\n",
       "    paths: {\n",
       "        \"d3\": \"http://d3js.org/d3.v4\",\n",
       "        \"d3scalechoromatic\": \"http://d3js.org/d3-scale-chromatic.v1.min\",\n",
       "        \"topojson\": \"http://d3js.org/topojson.v1.min\",\n",
       "        \"d3legend\":\"https://cdnjs.cloudflare.com/ajax/libs/d3-legend/2.11.0/d3-legend.min\"\n",
       "  },\n",
       "    shim: {\n",
       "        \"d3scalechoromatic\": [\"d3.global\"],\n",
       "        \"d3-interpolate\":[\"d3.global\"]\n",
       "  }\n",
       "});\n",
       "\n",
       "define(\"d3.global\", [\"d3\"], function(_) {\n",
       "  this.d3 = _;\n",
       "});"
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%javascript\n",
    "// https://github.com/d3/d3/issues/1693#issuecomment-35556356\n",
    "require.config({\n",
    "    paths: {\n",
    "        \"d3\": \"http://d3js.org/d3.v4\",\n",
    "        \"d3scalechoromatic\": \"http://d3js.org/d3-scale-chromatic.v1.min\",\n",
    "        \"topojson\": \"http://d3js.org/topojson.v1.min\",\n",
    "        \"d3legend\":\"https://cdnjs.cloudflare.com/ajax/libs/d3-legend/2.11.0/d3-legend.min\"\n",
    "  },\n",
    "    shim: {\n",
    "        \"d3scalechoromatic\": [\"d3.global\"],\n",
    "        \"d3-interpolate\":[\"d3.global\"]\n",
    "  }\n",
    "});\n",
    "\n",
    "define(\"d3.global\", [\"d3\"], function(_) {\n",
    "  this.d3 = _;\n",
    "});"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "<style>\n",
       "  .tract {\n",
       "    stroke: #777;\n",
       "    stroke-width: 0.05px;\n",
       "    pointer-events: all;\n",
       "  }\n",
       "  .tract:hover {\n",
       "    stroke: orange;\n",
       "    pointer-events: all;\n",
       "\n",
       "  }\n",
       "  .tract-border {\n",
       "    fill: none;\n",
       "    /*stroke: #777;*/\n",
       "    stroke-width: 0.05px;\n",
       "    pointer-events: none;\n",
       "  }\n",
       "  @media (max-width: 767px) {\n",
       "    .label {\n",
       "      transform: rotate(-45deg) translate(-10px, 25px);\n",
       "    }\n",
       "  }\n",
       "  .tract-border-state {\n",
       "    fill: none;\n",
       "    stroke: #333;\n",
       "    stroke-width: 0.5px;\n",
       "    pointer-events: none;\n",
       "  }\n",
       "  .legend {\n",
       "    font-family: sans-serif;\n",
       "    font-size: 10pt;\n",
       "\n",
       "  }\n",
       "  .legendTitle {\n",
       "    font-weight: bold;\n",
       "    font-size:11pt;\n",
       "  }\n",
       "  .background {\n",
       "    fill:#f5f5f5;\n",
       "  }\n",
       "  body {\n",
       "    background: #f5f5f5;\n",
       "    /*font-family: sans-serif;*/\n",
       "  }\n",
       "  .bar {\n",
       "    font-family: sans-serif;\n",
       "    font-size: 8pt;\n",
       "  }\n",
       "  #cityLegend {\n",
       "    font-family: sans-serif;\n",
       "    text-anchor: middle;\n",
       "  }\n",
       "  #details .background {\n",
       "    fill: #f5f5f5;\n",
       "    fill-opacity: 0.7;\n",
       "  }\n",
       "  svg {\n",
       "    width:100%;\n",
       "  }\n",
       "</style>\n",
       "<div class=\"row\">\n",
       "  <div class=\"col-md-1 col-xs-1\"></div>\n",
       "  <div class=\"col-md-10 col-xs-10\">\n",
       "    <h2>2016 Presidential General Election Results by County</h2>\n",
       "    <h4>Click on the map to explore</h4>\n",
       "  </div>\n",
       "</div>\n",
       "<div class=\"row\">\n",
       "  <div class=\"col-md-12 col-xs-12\">\n",
       "    <svg ></svg>\n",
       "  </div>\n",
       "</div>\n",
       "<div class=\"row\">\n",
       "  <div class=\"col-md-1 col-xs-1\"></div>\n",
       "  <div class=\"col-md-10 col-xs-10\">\n",
       "    <p>\n",
       "      By <a href=\"http://johnguerra.co\">John Alexis Guerra Gómez</a>\n",
       "    </p>\n",
       "\n",
       "  </div> <!-- col-md-10 -->\n",
       "  <div class=\"col-md-1\">\n",
       "  </div>\n",
       "\n",
       "</div> <!-- .row -->\n"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(HTML(\"\"\"\n",
    "<style>\n",
    "  .tract {\n",
    "    stroke: #777;\n",
    "    stroke-width: 0.05px;\n",
    "    pointer-events: all;\n",
    "  }\n",
    "  .tract:hover {\n",
    "    stroke: orange;\n",
    "    pointer-events: all;\n",
    "\n",
    "  }\n",
    "  .tract-border {\n",
    "    fill: none;\n",
    "    /*stroke: #777;*/\n",
    "    stroke-width: 0.05px;\n",
    "    pointer-events: none;\n",
    "  }\n",
    "  @media (max-width: 767px) {\n",
    "    .label {\n",
    "      transform: rotate(-45deg) translate(-10px, 25px);\n",
    "    }\n",
    "  }\n",
    "  .tract-border-state {\n",
    "    fill: none;\n",
    "    stroke: #333;\n",
    "    stroke-width: 0.5px;\n",
    "    pointer-events: none;\n",
    "  }\n",
    "  .legend {\n",
    "    font-family: sans-serif;\n",
    "    font-size: 10pt;\n",
    "\n",
    "  }\n",
    "  .legendTitle {\n",
    "    font-weight: bold;\n",
    "    font-size:11pt;\n",
    "  }\n",
    "  .background {\n",
    "    fill:#f5f5f5;\n",
    "  }\n",
    "  body {\n",
    "    background: #f5f5f5;\n",
    "    /*font-family: sans-serif;*/\n",
    "  }\n",
    "  .bar {\n",
    "    font-family: sans-serif;\n",
    "    font-size: 8pt;\n",
    "  }\n",
    "  #cityLegend {\n",
    "    font-family: sans-serif;\n",
    "    text-anchor: middle;\n",
    "  }\n",
    "  #details .background {\n",
    "    fill: #f5f5f5;\n",
    "    fill-opacity: 0.7;\n",
    "  }\n",
    "  svg {\n",
    "    width:100%;\n",
    "  }\n",
    "</style>\n",
    "<div class=\"row\">\n",
    "  <div class=\"col-md-1 col-xs-1\"></div>\n",
    "  <div class=\"col-md-10 col-xs-10\">\n",
    "    <h2>2016 Presidential General Election Results by County</h2>\n",
    "    <h4>Click on the map to explore</h4>\n",
    "  </div>\n",
    "</div>\n",
    "<div class=\"row\">\n",
    "  <div class=\"col-md-12 col-xs-12\">\n",
    "    <svg ></svg>\n",
    "  </div>\n",
    "</div>\n",
    "<div class=\"row\">\n",
    "  <div class=\"col-md-1 col-xs-1\"></div>\n",
    "  <div class=\"col-md-10 col-xs-10\">\n",
    "    <p>\n",
    "      By <a href=\"http://johnguerra.co\">John Alexis Guerra Gómez</a>\n",
    "    </p>\n",
    "\n",
    "  </div> <!-- col-md-10 -->\n",
    "  <div class=\"col-md-1\">\n",
    "  </div>\n",
    "\n",
    "</div> <!-- .row -->\n",
    "\"\"\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "\n",
       "require([\"d3\", \"topojson\"], function(d3, topojson) {\n",
       "    var svg = d3.select(\"svg\"),\n",
       "    width = $(document).width() * 10 / 12,\n",
       "    height = $(document).height() - 200,\n",
       "    margin = {\n",
       "        top: 20,\n",
       "        bottom: width > 767 ? 20 : 100,\n",
       "        right: 20,\n",
       "        left: 0\n",
       "    },\n",
       "    centered,\n",
       "    fmt = d3.format(\" >5.2%\"),\n",
       "    errorCount = 0;\n",
       "\n",
       "svg.attr(\"width\", width)\n",
       "    .attr(\"height\", height);\n",
       "\n",
       "function ready(error, us, data) {\n",
       "    if (error) throw error;\n",
       "\n",
       "\n",
       "    var dictCities = {};\n",
       "    data.forEach(function(d) {\n",
       "        //Parse the percentages\n",
       "        d[\"per_gop\"] = +(d[\"per_gop\"].slice(0, -1).replace(\",\", \".\"));\n",
       "        d[\"per_dem\"] = +(d[\"per_dem\"].slice(0, -1).replace(\",\", \".\"));\n",
       "        d.result = d[\"per_dem\"] - d[\"per_gop\"];\n",
       "        d.combined_fips = +d.combined_fips;\n",
       "        dictCities[d.combined_fips] = d;\n",
       "    });\n",
       "\n",
       "    var color = d3.scaleSequential(d3.interpolateRdBu)\n",
       "        .domain([-1, 1]);\n",
       "\n",
       "    // Add background\n",
       "    svg.append(\"rect\")\n",
       "        .attr(\"class\", \"background\")\n",
       "        .attr(\"width\", width)\n",
       "        .attr(\"height\", height)\n",
       "        // .on(\"click\", clicked);\n",
       "        // To allow the zoom back\n",
       "        // svg.on(\"click\", clicked);\n",
       "    var zoom = d3.zoom()\n",
       "        .scaleExtent([1, 15])\n",
       "        .on(\"zoom\", zoomed);\n",
       "\n",
       "    svg.style(\"pointer-events\", \"all\")\n",
       "        .call(zoom);\n",
       "    var g = svg.append(\"g\");\n",
       "\n",
       "    function zoomed() {\n",
       "        console.log(d3.event.transform);\n",
       "        g.attr(\"transform\", d3.event.transform);\n",
       "    };\n",
       "\n",
       "    var projection = d3.geoAlbersUsa()\n",
       "        .scale(1280)\n",
       "        .translate([width / 2, height / 2]);\n",
       "\n",
       "    var path = d3.geoPath()\n",
       "        .projection(projection);\n",
       "\n",
       "    g.selectAll(\"path\")\n",
       "        .data(topojson.feature(us, us.objects.counties).features)\n",
       "        .enter().append(\"path\")\n",
       "        .attr(\"class\", \"tract\")\n",
       "        .on(\"click\", clicked)\n",
       "        .on(\"mouseover\", updateDetails)\n",
       "        .style(\"fill\", function(d) {\n",
       "            var city = dictCities[d.id];\n",
       "            if (city)\n",
       "                return color(city.result);\n",
       "            else {\n",
       "                errorCount++;\n",
       "                console.log(d.id + \" Not found\" + \" errors = \" + errorCount);\n",
       "                return color(0);\n",
       "            }\n",
       "        })\n",
       "        .attr(\"d\", path)\n",
       "        .append(\"title\")\n",
       "        .text(function(d) {\n",
       "            var city = dictCities[d.id],\n",
       "                county,\n",
       "                state;\n",
       "                \n",
       "            // var msg = d.id;\n",
       "            if (city) {\n",
       "                county = city.county_name;\n",
       "                state = city.state_abbr;\n",
       "                var msg = county + ', ' + state + \" Difference: \" + fmt(city.result);\n",
       "            }\n",
       "            return msg;\n",
       "        });\n",
       "\n",
       "\n",
       "    // g.append(\"g\")\n",
       "    //     .attr(\"class\", \"counties\")\n",
       "    //   .selectAll(\"path\")\n",
       "    //     .data(topojson.feature(us, us.objects.counties).features)\n",
       "    //   .enter().append(\"path\")\n",
       "    //     .attr(\"class\", \"tract-border\")\n",
       "    //     .attr(\"d\", path);\n",
       "\n",
       "    g.append(\"path\")\n",
       "        .datum(topojson.mesh(us, us.objects.states, function(a, b) {\n",
       "            return a !== b;\n",
       "        }))\n",
       "        .attr(\"class\", \"tract-border-state\")\n",
       "        .attr(\"d\", path);\n",
       "\n",
       "\n",
       "    // g.append(\"path\")\n",
       "    //     .datum(topojson.mesh(mapData, mapData.objects.depts, function(a, b) { return a !== b; }))\n",
       "    //     .attr(\"class\", \"tract-border-state\")\n",
       "    //     .attr(\"d\", pathState);\n",
       "\n",
       "    // The details\n",
       "    var wScale = d3.scaleLinear()\n",
       "        .domain([-1, 1])\n",
       "        .range([-width / 3, width / 3]);\n",
       "    var details_layer = svg.append(\"g\")\n",
       "        .attr(\"id\", \"details\")\n",
       "        .attr(\"transform\", \"translate(\" + (width / 2 - 100) + \", 30)\");\n",
       "    details_layer.append(\"rect\")\n",
       "        .attr(\"class\", \"background\")\n",
       "        .attr(\"transform\", \"translate(\" + (-wScale.range()[1] + 100) + \", -20)\")\n",
       "        .attr(\"width\", wScale.range()[1] * 2 + 70)\n",
       "        .attr(\"rx\", 5)\n",
       "        .attr(\"ry\", 5)\n",
       "        .attr(\"height\", 60);\n",
       "    details_layer.append(\"text\")\n",
       "        .attr(\"id\", \"cityLegend\")\n",
       "        .text(\"Difference\")\n",
       "        .attr(\"transform\", \"translate(100, 0)\");\n",
       "\n",
       "    var detailsBars = details_layer.selectAll(\"bar\")\n",
       "        .data([0.4978, -0.5021])\n",
       "        .enter()\n",
       "        .append(\"g\")\n",
       "        .attr(\"class\", \"bar\");\n",
       "    detailsBars\n",
       "        .append(\"rect\")\n",
       "        .attr(\"width\", 0)\n",
       "        .attr(\"height\", width > 767 ? 20 : 10)\n",
       "        .attr(\"x\", 100)\n",
       "        .attr(\"y\", 10)\n",
       "        .style(\"fill\", color)\n",
       "        .transition()\n",
       "        .duration(500)\n",
       "        .attr(\"x\", function(d) {\n",
       "            return d > 0 ? 100 : 100 - wScale(-d);\n",
       "        })\n",
       "        .attr(\"width\", function(d) {\n",
       "            return d > 0 ? wScale(d) : wScale(-d);\n",
       "        });\n",
       "    detailsBars.append(\"text\")\n",
       "        .text(function(d) {\n",
       "            return (d > 0 ? \"\" : \"Rep \") +\n",
       "                fmt(d > 0 ? d : -d) +\n",
       "                (d > 0 ? \" Dem\" : \"\");\n",
       "        })\n",
       "        .attr(\"dx\", function(d) {\n",
       "            return d > 0 ? 5 : -5;\n",
       "        })\n",
       "        .attr(\"dy\", 24)\n",
       "        .attr(\"x\", 100)\n",
       "        .style(\"text-anchor\", function(d) {\n",
       "            return d > 0 ? \"start\" : \"end\";\n",
       "        })\n",
       "        .transition()\n",
       "        .duration(500)\n",
       "        .attr(\"x\", function(d) {\n",
       "            return d > 0 ? 100 + wScale(d) : 100 - wScale(-d);\n",
       "        });\n",
       "\n",
       "\n",
       "\n",
       "    // The legend\n",
       "    svg.append(\"g\")\n",
       "        .attr(\"class\", \"legend\")\n",
       "        .attr(\"transform\",\n",
       "            width > 767 ?\n",
       "            \"translate(\" + (width - margin.right - 150) + \",100)\" :\n",
       "            \"translate(\" + (width / 2 - 100) + \",\" + (height - 120) + \")\"\n",
       "        );\n",
       "\n",
       "    var legendLinear = d3.legendColor()\n",
       "        // .shapeWidth(30)\n",
       "        .cells(7)\n",
       "        .orient(width > 767 ? \"vertical\" : \"horizontal\")\n",
       "        .title(\"Diferencia\")\n",
       "        .labels([\n",
       "            \" 100.00% Dem\",\n",
       "            \"  66.67%\",\n",
       "            \"  33.33%\",\n",
       "            \"   0.00%\",\n",
       "            \"  33.33%\",\n",
       "            \"  66.67%\",\n",
       "            \" 100.00% Rep\",\n",
       "        ].reverse())\n",
       "        .labelFormat(fmt)\n",
       "        .ascending(true)\n",
       "        .labelAlign(\"end\")\n",
       "        .scale(color);\n",
       "\n",
       "    svg.select(\".legend\")\n",
       "        .call(legendLinear);\n",
       "\n",
       "    // When clicked, zoom in\n",
       "    function clicked(d) {\n",
       "        updateDetails(d);\n",
       "        var x, y, k;\n",
       "\n",
       "        // Compute centroid of the selected path\n",
       "        if (d && centered !== d) {\n",
       "            // if (d) {\n",
       "            var centroid = path.centroid(d);\n",
       "            x = centroid[0];\n",
       "            y = centroid[1];\n",
       "            // k = zoom.scaleExtent()[1];\n",
       "            k = 10;\n",
       "            centered = d;\n",
       "        }\n",
       "        else {\n",
       "            x = width / 2;\n",
       "            y = height / 2;\n",
       "            k = 1;\n",
       "            centered = null;\n",
       "        }\n",
       "\n",
       "\n",
       "\n",
       "        // Manually Zoom\n",
       "        svg.transition()\n",
       "            .duration(750)\n",
       "            .call(zoom.transform, d3.zoomIdentity\n",
       "                .translate(width / 2, height / 2)\n",
       "                .scale(k)\n",
       "                .translate(-x, -y));\n",
       "    }\n",
       "\n",
       "    function updateDetails(d) {\n",
       "\n",
       "        var data = [0.4978, -0.5021],\n",
       "            name = \"Difference \" + fmt(data[0] + data[1]),\n",
       "            state,\n",
       "            county,\n",
       "            city;\n",
       "            \n",
       "        if (d) {\n",
       "            city = dictCities[d.id];\n",
       "            if (city) {\n",
       "                county = city['county_name'];\n",
       "                state = city['state_abbr'];\n",
       "                data = [city[\"per_dem\"], -city[\"per_gop\"]];\n",
       "                name = county + ', ' + state + \" Difference: \" + fmt(data[0] + data[1]);\n",
       "            }\n",
       "        }\n",
       "        // console.log(data);\n",
       "        // console.log(name);\n",
       "        var detailsBars = details_layer\n",
       "            .selectAll(\".bar\")\n",
       "            .data(data);\n",
       "\n",
       "        detailsBars.select(\"rect\")\n",
       "            .transition()\n",
       "            .duration(500)\n",
       "            .attr(\"x\", function(d) {\n",
       "                return d > 0 ? 100 : 100 - wScale(-d);\n",
       "            })\n",
       "            .attr(\"width\", function(d) {\n",
       "                return d > 0 ? wScale(d) : wScale(-d);\n",
       "            })\n",
       "            .style(\"fill\", color);\n",
       "\n",
       "        detailsBars.select(\"text\")\n",
       "            .text(function(d) {\n",
       "                return (d > 0 ? \"\" : \"Rep \") +\n",
       "                    fmt(d > 0 ? d : -d) +\n",
       "                    (d > 0 ? \" Dem\" : \"\")\n",
       "            })\n",
       "            .transition()\n",
       "            .duration(500)\n",
       "            .attr(\"x\", function(d) {\n",
       "                return d > 0 ? 100 + wScale(d) : 100 - wScale(-d);\n",
       "            })\n",
       "\n",
       "\n",
       "        details_layer.select(\"#cityLegend\").text(name);\n",
       "\n",
       "    }\n",
       "}\n",
       "\n",
       "d3.queue()\n",
       "    .defer(d3.json, \"https://raw.githubusercontent.com/john-guerra/US_Elections_Results/master/us.json\")\n",
       "    .defer(d3.csv, \"https://raw.githubusercontent.com/tonmcg/US_Elections_Results/master/2016_US_County_Level_Presidential_Results.csv\")\n",
       "    .await(ready);\n",
       "    \n",
       "});"
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%javascript\n",
    "\n",
    "require([\"d3\", \"topojson\"], function(d3, topojson) {\n",
    "    var svg = d3.select(\"svg\"),\n",
    "    width = $(document).width() * 10 / 12,\n",
    "    height = $(document).height() - 200,\n",
    "    margin = {\n",
    "        top: 20,\n",
    "        bottom: width > 767 ? 20 : 100,\n",
    "        right: 20,\n",
    "        left: 0\n",
    "    },\n",
    "    centered,\n",
    "    fmt = d3.format(\" >5.2%\"),\n",
    "    errorCount = 0;\n",
    "\n",
    "svg.attr(\"width\", width)\n",
    "    .attr(\"height\", height);\n",
    "\n",
    "function ready(error, us, data) {\n",
    "    if (error) throw error;\n",
    "\n",
    "\n",
    "    var dictCities = {};\n",
    "    data.forEach(function(d) {\n",
    "        //Parse the percentages\n",
    "        d[\"per_gop\"] = +(d[\"per_gop\"].slice(0, -1).replace(\",\", \".\"));\n",
    "        d[\"per_dem\"] = +(d[\"per_dem\"].slice(0, -1).replace(\",\", \".\"));\n",
    "        d.result = d[\"per_dem\"] - d[\"per_gop\"];\n",
    "        d.combined_fips = +d.combined_fips;\n",
    "        dictCities[d.combined_fips] = d;\n",
    "    });\n",
    "\n",
    "    var color = d3.scaleSequential(d3.interpolateRdBu)\n",
    "        .domain([-1, 1]);\n",
    "\n",
    "    // Add background\n",
    "    svg.append(\"rect\")\n",
    "        .attr(\"class\", \"background\")\n",
    "        .attr(\"width\", width)\n",
    "        .attr(\"height\", height)\n",
    "        // .on(\"click\", clicked);\n",
    "        // To allow the zoom back\n",
    "        // svg.on(\"click\", clicked);\n",
    "    var zoom = d3.zoom()\n",
    "        .scaleExtent([1, 15])\n",
    "        .on(\"zoom\", zoomed);\n",
    "\n",
    "    svg.style(\"pointer-events\", \"all\")\n",
    "        .call(zoom);\n",
    "    var g = svg.append(\"g\");\n",
    "\n",
    "    function zoomed() {\n",
    "        console.log(d3.event.transform);\n",
    "        g.attr(\"transform\", d3.event.transform);\n",
    "    };\n",
    "\n",
    "    var projection = d3.geoAlbersUsa()\n",
    "        .scale(1280)\n",
    "        .translate([width / 2, height / 2]);\n",
    "\n",
    "    var path = d3.geoPath()\n",
    "        .projection(projection);\n",
    "\n",
    "    g.selectAll(\"path\")\n",
    "        .data(topojson.feature(us, us.objects.counties).features)\n",
    "        .enter().append(\"path\")\n",
    "        .attr(\"class\", \"tract\")\n",
    "        .on(\"click\", clicked)\n",
    "        .on(\"mouseover\", updateDetails)\n",
    "        .style(\"fill\", function(d) {\n",
    "            var city = dictCities[d.id];\n",
    "            if (city)\n",
    "                return color(city.result);\n",
    "            else {\n",
    "                errorCount++;\n",
    "                console.log(d.id + \" Not found\" + \" errors = \" + errorCount);\n",
    "                return color(0);\n",
    "            }\n",
    "        })\n",
    "        .attr(\"d\", path)\n",
    "        .append(\"title\")\n",
    "        .text(function(d) {\n",
    "            var city = dictCities[d.id],\n",
    "                county,\n",
    "                state;\n",
    "                \n",
    "            // var msg = d.id;\n",
    "            if (city) {\n",
    "                county = city.county_name;\n",
    "                state = city.state_abbr;\n",
    "                var msg = county + ', ' + state + \" Difference: \" + fmt(city.result);\n",
    "            }\n",
    "            return msg;\n",
    "        });\n",
    "\n",
    "\n",
    "    // g.append(\"g\")\n",
    "    //     .attr(\"class\", \"counties\")\n",
    "    //   .selectAll(\"path\")\n",
    "    //     .data(topojson.feature(us, us.objects.counties).features)\n",
    "    //   .enter().append(\"path\")\n",
    "    //     .attr(\"class\", \"tract-border\")\n",
    "    //     .attr(\"d\", path);\n",
    "\n",
    "    g.append(\"path\")\n",
    "        .datum(topojson.mesh(us, us.objects.states, function(a, b) {\n",
    "            return a !== b;\n",
    "        }))\n",
    "        .attr(\"class\", \"tract-border-state\")\n",
    "        .attr(\"d\", path);\n",
    "\n",
    "\n",
    "    // g.append(\"path\")\n",
    "    //     .datum(topojson.mesh(mapData, mapData.objects.depts, function(a, b) { return a !== b; }))\n",
    "    //     .attr(\"class\", \"tract-border-state\")\n",
    "    //     .attr(\"d\", pathState);\n",
    "\n",
    "    // The details\n",
    "    var wScale = d3.scaleLinear()\n",
    "        .domain([-1, 1])\n",
    "        .range([-width / 3, width / 3]);\n",
    "    var details_layer = svg.append(\"g\")\n",
    "        .attr(\"id\", \"details\")\n",
    "        .attr(\"transform\", \"translate(\" + (width / 2 - 100) + \", 30)\");\n",
    "    details_layer.append(\"rect\")\n",
    "        .attr(\"class\", \"background\")\n",
    "        .attr(\"transform\", \"translate(\" + (-wScale.range()[1] + 100) + \", -20)\")\n",
    "        .attr(\"width\", wScale.range()[1] * 2 + 70)\n",
    "        .attr(\"rx\", 5)\n",
    "        .attr(\"ry\", 5)\n",
    "        .attr(\"height\", 60);\n",
    "    details_layer.append(\"text\")\n",
    "        .attr(\"id\", \"cityLegend\")\n",
    "        .text(\"Difference\")\n",
    "        .attr(\"transform\", \"translate(100, 0)\");\n",
    "\n",
    "    var detailsBars = details_layer.selectAll(\"bar\")\n",
    "        .data([0.4978, -0.5021])\n",
    "        .enter()\n",
    "        .append(\"g\")\n",
    "        .attr(\"class\", \"bar\");\n",
    "    detailsBars\n",
    "        .append(\"rect\")\n",
    "        .attr(\"width\", 0)\n",
    "        .attr(\"height\", width > 767 ? 20 : 10)\n",
    "        .attr(\"x\", 100)\n",
    "        .attr(\"y\", 10)\n",
    "        .style(\"fill\", color)\n",
    "        .transition()\n",
    "        .duration(500)\n",
    "        .attr(\"x\", function(d) {\n",
    "            return d > 0 ? 100 : 100 - wScale(-d);\n",
    "        })\n",
    "        .attr(\"width\", function(d) {\n",
    "            return d > 0 ? wScale(d) : wScale(-d);\n",
    "        });\n",
    "    detailsBars.append(\"text\")\n",
    "        .text(function(d) {\n",
    "            return (d > 0 ? \"\" : \"Rep \") +\n",
    "                fmt(d > 0 ? d : -d) +\n",
    "                (d > 0 ? \" Dem\" : \"\");\n",
    "        })\n",
    "        .attr(\"dx\", function(d) {\n",
    "            return d > 0 ? 5 : -5;\n",
    "        })\n",
    "        .attr(\"dy\", 24)\n",
    "        .attr(\"x\", 100)\n",
    "        .style(\"text-anchor\", function(d) {\n",
    "            return d > 0 ? \"start\" : \"end\";\n",
    "        })\n",
    "        .transition()\n",
    "        .duration(500)\n",
    "        .attr(\"x\", function(d) {\n",
    "            return d > 0 ? 100 + wScale(d) : 100 - wScale(-d);\n",
    "        });\n",
    "\n",
    "\n",
    "\n",
    "    // The legend\n",
    "    svg.append(\"g\")\n",
    "        .attr(\"class\", \"legend\")\n",
    "        .attr(\"transform\",\n",
    "            width > 767 ?\n",
    "            \"translate(\" + (width - margin.right - 150) + \",100)\" :\n",
    "            \"translate(\" + (width / 2 - 100) + \",\" + (height - 120) + \")\"\n",
    "        );\n",
    "\n",
    "    var legendLinear = d3.legendColor()\n",
    "        // .shapeWidth(30)\n",
    "        .cells(7)\n",
    "        .orient(width > 767 ? \"vertical\" : \"horizontal\")\n",
    "        .title(\"Diferencia\")\n",
    "        .labels([\n",
    "            \" 100.00% Dem\",\n",
    "            \"  66.67%\",\n",
    "            \"  33.33%\",\n",
    "            \"   0.00%\",\n",
    "            \"  33.33%\",\n",
    "            \"  66.67%\",\n",
    "            \" 100.00% Rep\",\n",
    "        ].reverse())\n",
    "        .labelFormat(fmt)\n",
    "        .ascending(true)\n",
    "        .labelAlign(\"end\")\n",
    "        .scale(color);\n",
    "\n",
    "    svg.select(\".legend\")\n",
    "        .call(legendLinear);\n",
    "\n",
    "    // When clicked, zoom in\n",
    "    function clicked(d) {\n",
    "        updateDetails(d);\n",
    "        var x, y, k;\n",
    "\n",
    "        // Compute centroid of the selected path\n",
    "        if (d && centered !== d) {\n",
    "            // if (d) {\n",
    "            var centroid = path.centroid(d);\n",
    "            x = centroid[0];\n",
    "            y = centroid[1];\n",
    "            // k = zoom.scaleExtent()[1];\n",
    "            k = 10;\n",
    "            centered = d;\n",
    "        }\n",
    "        else {\n",
    "            x = width / 2;\n",
    "            y = height / 2;\n",
    "            k = 1;\n",
    "            centered = null;\n",
    "        }\n",
    "\n",
    "\n",
    "\n",
    "        // Manually Zoom\n",
    "        svg.transition()\n",
    "            .duration(750)\n",
    "            .call(zoom.transform, d3.zoomIdentity\n",
    "                .translate(width / 2, height / 2)\n",
    "                .scale(k)\n",
    "                .translate(-x, -y));\n",
    "    }\n",
    "\n",
    "    function updateDetails(d) {\n",
    "\n",
    "        var data = [0.4978, -0.5021],\n",
    "            name = \"Difference \" + fmt(data[0] + data[1]),\n",
    "            state,\n",
    "            county,\n",
    "            city;\n",
    "            \n",
    "        if (d) {\n",
    "            city = dictCities[d.id];\n",
    "            if (city) {\n",
    "                county = city['county_name'];\n",
    "                state = city['state_abbr'];\n",
    "                data = [city[\"per_dem\"], -city[\"per_gop\"]];\n",
    "                name = county + ', ' + state + \" Difference: \" + fmt(data[0] + data[1]);\n",
    "            }\n",
    "        }\n",
    "        // console.log(data);\n",
    "        // console.log(name);\n",
    "        var detailsBars = details_layer\n",
    "            .selectAll(\".bar\")\n",
    "            .data(data);\n",
    "\n",
    "        detailsBars.select(\"rect\")\n",
    "            .transition()\n",
    "            .duration(500)\n",
    "            .attr(\"x\", function(d) {\n",
    "                return d > 0 ? 100 : 100 - wScale(-d);\n",
    "            })\n",
    "            .attr(\"width\", function(d) {\n",
    "                return d > 0 ? wScale(d) : wScale(-d);\n",
    "            })\n",
    "            .style(\"fill\", color);\n",
    "\n",
    "        detailsBars.select(\"text\")\n",
    "            .text(function(d) {\n",
    "                return (d > 0 ? \"\" : \"Rep \") +\n",
    "                    fmt(d > 0 ? d : -d) +\n",
    "                    (d > 0 ? \" Dem\" : \"\")\n",
    "            })\n",
    "            .transition()\n",
    "            .duration(500)\n",
    "            .attr(\"x\", function(d) {\n",
    "                return d > 0 ? 100 + wScale(d) : 100 - wScale(-d);\n",
    "            })\n",
    "\n",
    "\n",
    "        details_layer.select(\"#cityLegend\").text(name);\n",
    "\n",
    "    }\n",
    "}\n",
    "\n",
    "d3.queue()\n",
    "    .defer(d3.json, \"https://raw.githubusercontent.com/john-guerra/US_Elections_Results/master/us.json\")\n",
    "    .defer(d3.csv, \"https://raw.githubusercontent.com/tonmcg/US_Elections_Results/master/2016_US_County_Level_Presidential_Results.csv\")\n",
    "    .await(ready);\n",
    "    \n",
    "});"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python [default]",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}