pivot_results.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Pivot and Validate Precinct Level Election Results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import geopandas as gpd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Import the precinct level election results\n",
    "\n",
    "\n",
    "#### [Open Elections - 2018 NY General Election - Precinct Level Results](data/precinct_election_results/20181106__ny__general__precinct.csv)\n",
    "\n",
    "* Relative Path: `data/precinct_election_results/20181106__ny__general__precinct.csv`\n",
    "* Link: <https://github.com/openelections/openelections-data-ny/blob/master/2018/20181106__ny__general__precinct.csv>\n",
    "* Date Accessed: January 21, 2021"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>county</th>\n",
       "      <th>precinct</th>\n",
       "      <th>office</th>\n",
       "      <th>district</th>\n",
       "      <th>candidate</th>\n",
       "      <th>party</th>\n",
       "      <th>votes</th>\n",
       "      <th>election_day</th>\n",
       "      <th>absentee</th>\n",
       "      <th>machine_votes</th>\n",
       "      <th>absentee_hc</th>\n",
       "      <th>affidavit</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Chemung</td>\n",
       "      <td>Elmira 0901</td>\n",
       "      <td>Governor</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Andrew Cuomo</td>\n",
       "      <td>Dem</td>\n",
       "      <td>139</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Chemung</td>\n",
       "      <td>Elmira 0902</td>\n",
       "      <td>Governor</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Andrew Cuomo</td>\n",
       "      <td>Dem</td>\n",
       "      <td>104</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    county     precinct    office district     candidate party votes  \\\n",
       "0  Chemung  Elmira 0901  Governor      NaN  Andrew Cuomo   Dem   139   \n",
       "1  Chemung  Elmira 0902  Governor      NaN  Andrew Cuomo   Dem   104   \n",
       "\n",
       "   election_day  absentee  machine_votes  absentee_hc  affidavit  \n",
       "0           NaN       NaN            NaN          NaN        NaN  \n",
       "1           NaN       NaN            NaN          NaN        NaN  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "statewide_election_results_file_path = '20181106__ny__general__precinct.csv'\n",
    "statewide_results_df = pd.read_csv(statewide_election_results_file_path,low_memory=False)\n",
    "statewide_results_df.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The next cell aliases `statewide_results_df` as `df` because typing fewer characters allows for faster data exploration.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = statewide_results_df.copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Clean `votes`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/baxterdemers/ny2018/venv/lib/python3.7/site-packages/ipykernel_launcher.py:9: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  if __name__ == '__main__':\n"
     ]
    }
   ],
   "source": [
    "def remove_commas(df, col_lst):\n",
    "    \"\"\"\n",
    "    remove commas from the string representation of numbers in the cols in col_lst\n",
    "    :df: DataFrame object\n",
    "    :col_lst: list of strings that are each the name of a column :df:\n",
    "    \"\"\"\n",
    "    for col in col_lst:\n",
    "        if df.dtypes[col] == \"object\":\n",
    "            df[col] = df[col].map(lambda s: str(s).replace(\",\", \"\"))\n",
    "        df = df.astype({col: \"float\"})\n",
    "        df[col] = df[col].fillna(0)\n",
    "        df = df.astype({col: \"int64\"})\n",
    "    return df\n",
    "\n",
    "df = remove_commas(df[df.votes.notna()], ['votes'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Clean `county`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.county = df.county.str.strip() + ' County'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "62"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.county.nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(922315, 12)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.drop_duplicates().shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Clean `office`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Governor', 'Comptroller', 'Attorney General', 'U.S. Senate',\n",
       "       'U.S. House', 'State Senate', 'State Assembly'], dtype=object)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def clean_office(office):\n",
    "    if 'gov' in office.lower():\n",
    "        return 'Governor'\n",
    "    if office == 'U.S.Senate':\n",
    "        return 'U.S. Senate'\n",
    "    if 'U.S.House' in office:\n",
    "        return 'U.S. House'\n",
    "    return office.title().strip()\n",
    "\n",
    "df.office.map(clean_office).unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.office = df.office.map(clean_office)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "7\n",
      "['Governor', 'Comptroller', 'Attorney General', 'U.S. Senate', 'U.S. House', 'State Senate', 'State Assembly']\n"
     ]
    }
   ],
   "source": [
    "offices = list(df.office.unique())\n",
    "print(len(offices))\n",
    "print(offices)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ensure that all counties data for each office"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "county\n",
       "Albany County                                {State Assembly}\n",
       "Allegany County                                    {Governor}\n",
       "Broome County                                {State Assembly}\n",
       "Chautauqua County                            {State Assembly}\n",
       "Chemung County                               {State Assembly}\n",
       "Clinton County                               {State Assembly}\n",
       "Columbia County               {Attorney General, Comptroller}\n",
       "Cortland County                              {State Assembly}\n",
       "Erie County                                  {State Assembly}\n",
       "Genesee County                                     {Governor}\n",
       "Kings County                                 {State Assembly}\n",
       "Madison County                               {State Assembly}\n",
       "Montgomery County                            {State Assembly}\n",
       "New York County                              {State Assembly}\n",
       "Niagara County                                  {U.S. Senate}\n",
       "Oneida County                                {State Assembly}\n",
       "Orange County          {State Assembly, Governor, U.S. House}\n",
       "Queens County                                   {Comptroller}\n",
       "Richmond County                              {State Assembly}\n",
       "Saratoga County                                  {U.S. House}\n",
       "Schuyler County                                    {Governor}\n",
       "St. Lawrence County                          {State Assembly}\n",
       "Suffolk County                                     {Governor}\n",
       "Tompkins County                              {State Assembly}\n",
       "Warren County                                   {Comptroller}\n",
       "Wayne County                                 {State Assembly}\n",
       "Westchester County                           {State Assembly}\n",
       "Name: office, dtype: object"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "county_to_offices = df.groupby('county').office.unique()\n",
    "county_to_missing_offices = county_to_offices.apply(lambda x: set(offices).difference(x))\n",
    "county_to_missing_offices[county_to_missing_offices != set()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "7\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "county\n",
       "Allegany County                {Governor}\n",
       "Genesee County                 {Governor}\n",
       "Niagara County              {U.S. Senate}\n",
       "Orange County      {Governor, U.S. House}\n",
       "Saratoga County              {U.S. House}\n",
       "Schuyler County                {Governor}\n",
       "Suffolk County                 {Governor}\n",
       "Name: office, dtype: object"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "federal_offices = ['Governor', 'U.S. Senate', 'U.S. House']\n",
    "county_to_missing_federal_offices = county_to_offices.apply(lambda x: set(federal_offices).difference(x))\n",
    "counties_missing_federal_offices = county_to_missing_federal_offices[county_to_missing_federal_offices != set()]\n",
    "print(len(counties_missing_federal_offices))\n",
    "counties_missing_federal_offices"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Seems like Open Elections has more up to date county level files here: https://github.com/openelections/openelections-data-ny/tree/master/2018/counties\n",
    "\n",
    "* Allegany County                {X} https://github.com/openelections/openelections-data-ny/blob/master/2018/counties/20181106__ny__general__allegany__precinct.csv\n",
    "* Genesee County                 {X} https://github.com/openelections/openelections-data-ny/blob/master/2018/counties/20181106__ny__general__genesee__precinct.csv\n",
    "* Niagara County              {X} https://github.com/openelections/openelections-data-ny/blob/master/2018/counties/20181106__ny__general__niagara__precinct.csv\n",
    "* Orange County      {U.S. House, X} https://github.com/openelections/openelections-data-ny/blob/master/2018/counties/20181106__ny__general__orange__precinct.csv\n",
    "* Saratoga County              {X} https://github.com/openelections/openelections-data-ny/blob/master/2018/counties/20181106__ny__general__saratoga__precinct.csv\n",
    "* Schuyler County                {X} https://github.com/openelections/openelections-data-ny/blob/master/2018/counties/20181106__ny__general__schuyler__precinct.csv\n",
    "* Suffolk County                 {X} https://github.com/openelections/openelections-data-ny/blob/master/2018/counties/20181106__ny__general__suffolk__precinct.csv \n",
    "\n",
    "All set with the expection of missing results for the U.S. House race in Orange County."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Getting results for Governor in Orange County\n",
    "\n",
    "1. Downloaded `data/parse_results/orange-county/GE18 - Gov Lt. Gov.pdf` from https://www.orangecountygov.com/DocumentCenter/View/11654/GE18---NYS-Governor--Lt-Governor\n",
    "2. Used https://pdftables.com/ to convert the pdf to a csv (`data/parse_results/orange-county/GE18 - Gov Lt. Gov.csv`)\n",
    "3. Made some manual tweaks to clean up the handwork of the pdf to csv converter in Numbers (like Excel for Mac OS). Saved it locally at `data/parse_results/orange-county/GE18 - Gov Lt. Gov-cleaned.csv`\n",
    "4. Imported below\n",
    "\n",
    "Since this data is already pivoted, I will incoperate it after I pivot the rest of the data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>precinct</th>\n",
       "      <th>DEM</th>\n",
       "      <th>REP</th>\n",
       "      <th>CON</th>\n",
       "      <th>GRE</th>\n",
       "      <th>WOR</th>\n",
       "      <th>IND</th>\n",
       "      <th>WEP</th>\n",
       "      <th>REF</th>\n",
       "      <th>SAM</th>\n",
       "      <th>SAM.1</th>\n",
       "      <th>SAM.2</th>\n",
       "      <th>LBT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Middletown - Ward 1-1</td>\n",
       "      <td>151</td>\n",
       "      <td>72</td>\n",
       "      <td>9</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Middletown - Ward 1-2</td>\n",
       "      <td>76</td>\n",
       "      <td>32</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Middletown - Ward 1-3</td>\n",
       "      <td>147</td>\n",
       "      <td>64</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Middletown - Ward 1-4</td>\n",
       "      <td>35</td>\n",
       "      <td>20</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Middletown - Ward 1-5</td>\n",
       "      <td>134</td>\n",
       "      <td>59</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>329</th>\n",
       "      <td>Woodbury 6</td>\n",
       "      <td>168</td>\n",
       "      <td>209</td>\n",
       "      <td>32</td>\n",
       "      <td>7</td>\n",
       "      <td>10</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>9</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>330</th>\n",
       "      <td>Woodbury 7</td>\n",
       "      <td>160</td>\n",
       "      <td>149</td>\n",
       "      <td>18</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>331</th>\n",
       "      <td>Woodbury 8</td>\n",
       "      <td>130</td>\n",
       "      <td>251</td>\n",
       "      <td>23</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>332</th>\n",
       "      <td>Woodbury 9</td>\n",
       "      <td>117</td>\n",
       "      <td>42</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>333</th>\n",
       "      <td>Woodbury 10</td>\n",
       "      <td>263</td>\n",
       "      <td>257</td>\n",
       "      <td>36</td>\n",
       "      <td>6</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>7</td>\n",
       "      <td>17</td>\n",
       "      <td>0</td>\n",
       "      <td>17</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>334 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                  precinct  DEM  REP  CON  GRE  WOR  IND  WEP  REF  SAM  \\\n",
       "0    Middletown - Ward 1-1  151   72    9    5    3    5    0    1    1   \n",
       "1    Middletown - Ward 1-2   76   32    4    1    1    0    1    0    0   \n",
       "2    Middletown - Ward 1-3  147   64    3    1    3    1    0    2    2   \n",
       "3    Middletown - Ward 1-4   35   20    1    1    0    2    0    0    0   \n",
       "4    Middletown - Ward 1-5  134   59    7    1    0    0    0    2    1   \n",
       "..                     ...  ...  ...  ...  ...  ...  ...  ...  ...  ...   \n",
       "329             Woodbury 6  168  209   32    7   10    4    2    9    5   \n",
       "330             Woodbury 7  160  149   18    4    0    2    2    6    6   \n",
       "331             Woodbury 8  130  251   23    5    1    0    1    5    3   \n",
       "332             Woodbury 9  117   42    5    5    1    1    0    0    1   \n",
       "333            Woodbury 10  263  257   36    6    5    2    2    7   17   \n",
       "\n",
       "     SAM.1  SAM.2  LBT  \n",
       "0        0      1    2  \n",
       "1        0      0    3  \n",
       "2        0      2    5  \n",
       "3        0      0    1  \n",
       "4        0      1    2  \n",
       "..     ...    ...  ...  \n",
       "329      0      5   13  \n",
       "330      0      6    4  \n",
       "331      0      3    1  \n",
       "332      0      1    2  \n",
       "333      0     17   12  \n",
       "\n",
       "[334 rows x 13 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_Orange_Gov = pd.read_csv('data/parse_results/orange-county/GE18 - Gov Lt. Gov-cleaned.csv')\n",
    "df_Orange_Gov"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "123446"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_Orange_Gov.sum(axis=1).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "334"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_Orange_Gov.precinct.nunique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Getting results for U.S. House in Orange County\n",
    "\n",
    "1. Downloaded `data/parse_results/orange-county/GE18 -CD 18th.pdf` from https://www.orangecountygov.com/DocumentCenter/View/11663/GE18--CD-18th\n",
    "2. Used https://pdftables.com/ to convert the pdf to a csv (`data/parse_results/orange-county/GE18 -CD 18th.csv`)\n",
    "3. Made some manual tweaks to clean up the handwork of the pdf to csv converter in Numbers (like Excel for Mac OS). Saved it locally at `data/parse_results/orange-county/GE18-CD-18th-cleaned.csv`\n",
    "4. Imported below\n",
    "\n",
    "Since this data is already pivoted, I will incoperate it after I pivot the rest of the data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>precinct</th>\n",
       "      <th>DEM</th>\n",
       "      <th>REP</th>\n",
       "      <th>CON</th>\n",
       "      <th>WOR</th>\n",
       "      <th>IND</th>\n",
       "      <th>WEP</th>\n",
       "      <th>REF</th>\n",
       "      <th>WRITE IN</th>\n",
       "      <th>BLANK</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Middletown - Ward 1-1</td>\n",
       "      <td>159</td>\n",
       "      <td>67</td>\n",
       "      <td>7</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Middletown - Ward 1-2</td>\n",
       "      <td>79</td>\n",
       "      <td>34</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Middletown - Ward 1-3</td>\n",
       "      <td>158</td>\n",
       "      <td>47</td>\n",
       "      <td>7</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Middletown - Ward 1-4</td>\n",
       "      <td>36</td>\n",
       "      <td>19</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Middletown - Ward 1-5</td>\n",
       "      <td>139</td>\n",
       "      <td>53</td>\n",
       "      <td>7</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>329</th>\n",
       "      <td>Woodbury 6</td>\n",
       "      <td>202</td>\n",
       "      <td>191</td>\n",
       "      <td>27</td>\n",
       "      <td>7</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>330</th>\n",
       "      <td>Woodbury 7</td>\n",
       "      <td>192</td>\n",
       "      <td>117</td>\n",
       "      <td>13</td>\n",
       "      <td>3</td>\n",
       "      <td>9</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>331</th>\n",
       "      <td>Woodbury 8</td>\n",
       "      <td>175</td>\n",
       "      <td>211</td>\n",
       "      <td>27</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>332</th>\n",
       "      <td>Woodbury 9</td>\n",
       "      <td>129</td>\n",
       "      <td>36</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>333</th>\n",
       "      <td>Woodbury 10</td>\n",
       "      <td>321</td>\n",
       "      <td>216</td>\n",
       "      <td>36</td>\n",
       "      <td>14</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>334 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                  precinct  DEM  REP  CON  WOR  IND  WEP  REF  WRITE IN  BLANK\n",
       "0    Middletown - Ward 1-1  159   67    7    6    4    2    1         0     11\n",
       "1    Middletown - Ward 1-2   79   34    3    1    0    0    0         0      3\n",
       "2    Middletown - Ward 1-3  158   47    7    3    2    1    2         0     15\n",
       "3    Middletown - Ward 1-4   36   19    0    2    1    0    0         0      3\n",
       "4    Middletown - Ward 1-5  139   53    7    6    1    0    1         0      9\n",
       "..                     ...  ...  ...  ...  ...  ...  ...  ...       ...    ...\n",
       "329             Woodbury 6  202  191   27    7    6    3    4         0     34\n",
       "330             Woodbury 7  192  117   13    3    9    2    8         0     15\n",
       "331             Woodbury 8  175  211   27    3    5    1    2         0     13\n",
       "332             Woodbury 9  129   36    7    0    1    1    1         0     10\n",
       "333            Woodbury 10  321  216   36   14    5    3    9         0     23\n",
       "\n",
       "[334 rows x 10 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_Orange_HOR = pd.read_csv('data/parse_results/orange-county/GE18-CD-18th-cleaned.csv')\n",
    "df_Orange_HOR"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "125924"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_Orange_HOR.sum(axis=1).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "334"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_Orange_HOR.precinct.nunique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Patch in Counties that are missing election results\n",
    "\n",
    "For whatever reason, Open Elections has more complete results at the county level. Counties still missing election results for federal offices: \n",
    "* Allegany County                {Governor}\n",
    "* Genesee County                 {Governor}\n",
    "* Niagara County              {U.S. Senate}\n",
    "* Saratoga County              {U.S. House}\n",
    "* Schuyler County                {Governor}\n",
    "* Suffolk County                 {Governor}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_Allegany = pd.read_csv('data/county_precinct_election_results_oe/20181106__ny__general__allegany__precinct.csv')\n",
    "df_Genesee = pd.read_csv('data/county_precinct_election_results_oe/20181106__ny__general__genesee__precinct.csv')\n",
    "df_Niagara = pd.read_csv('data/county_precinct_election_results_oe/20181106__ny__general__niagara__precinct.csv')\n",
    "df_Saratoga = pd.read_csv('data/county_precinct_election_results_oe/20181106__ny__general__saratoga__precinct.csv')\n",
    "df_Schuyler = pd.read_csv('data/county_precinct_election_results_oe/20181106__ny__general__schuyler__precinct.csv')\n",
    "df_Suffolk = pd.read_csv('data/county_precinct_election_results_oe/20181106__ny__general__suffolk__precinct.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "county_lst = ['Allegany', 'Genesee', 'Niagara', 'Saratoga', 'Schuyler', 'Suffolk']\n",
    "county_df_lst = [df_Allegany, df_Genesee, df_Niagara, df_Saratoga, df_Schuyler,df_Suffolk]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "for idx, county in enumerate(county_lst):\n",
    "    county_df = county_df_lst[idx]\n",
    "    assert  {county} == set(county_df.county)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df[~df.county.isin(county_lst)]\n",
    "df = df.append(county_df_lst)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Repeat cleaning"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Governor', 'Comptroller', 'Attorney General', 'U.S. Senate',\n",
       "       'U.S. House', 'State Senate', 'State Assembly'], dtype=object)"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def clean_office(office):\n",
    "    if 'gov' in office.lower():\n",
    "        return 'Governor'\n",
    "    if office == 'U.S.Senate':\n",
    "        return 'U.S. Senate'\n",
    "    if 'U.S.House' in office:\n",
    "        return 'U.S. House'\n",
    "    return office.title().strip()\n",
    "\n",
    "df.office = df.office.map(clean_office)\n",
    "df = df[df.office.isin(offices)]\n",
    "df.office.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[df.county.isin(county_lst), 'county'] = df.loc[df.county.isin(county_lst),'county'].str.strip() + ' County'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ensure that all counties data for each office"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "21\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "county\n",
       "Albany County                                {State Assembly}\n",
       "Broome County                                {State Assembly}\n",
       "Chautauqua County                            {State Assembly}\n",
       "Chemung County                               {State Assembly}\n",
       "Clinton County                               {State Assembly}\n",
       "Columbia County               {Attorney General, Comptroller}\n",
       "Cortland County                              {State Assembly}\n",
       "Erie County                                  {State Assembly}\n",
       "Kings County                                 {State Assembly}\n",
       "Madison County                               {State Assembly}\n",
       "Montgomery County                            {State Assembly}\n",
       "New York County                              {State Assembly}\n",
       "Oneida County                                {State Assembly}\n",
       "Orange County          {State Assembly, Governor, U.S. House}\n",
       "Queens County                                   {Comptroller}\n",
       "Richmond County                              {State Assembly}\n",
       "St. Lawrence County                          {State Assembly}\n",
       "Tompkins County                              {State Assembly}\n",
       "Warren County                                   {Comptroller}\n",
       "Wayne County                                 {State Assembly}\n",
       "Westchester County                           {State Assembly}\n",
       "Name: office, dtype: object"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "county_to_offices = df.groupby('county').office.unique()\n",
    "county_to_missing_offices = county_to_offices.apply(lambda x: set(offices).difference(x))\n",
    "county_to_missing_offices = county_to_missing_offices[county_to_missing_offices != set()]\n",
    "print(len(county_to_missing_offices))\n",
    "county_to_missing_offices"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "county\n",
       "Orange County    {Governor, U.S. House}\n",
       "Name: office, dtype: object"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "federal_offices = ['Governor', 'U.S. Senate', 'U.S. House']\n",
    "county_to_missing_federal_offices = county_to_offices.apply(lambda x: set(federal_offices).difference(x))\n",
    "counties_missing_federal_offices = county_to_missing_federal_offices[county_to_missing_federal_offices != set()]\n",
    "print(len(counties_missing_federal_offices))\n",
    "counties_missing_federal_offices"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Exclude non-federal offices because so much source data is missing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df[df.office.isin(federal_offices)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_cleaned_offices = df.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_cleaned_offices[(df_cleaned_offices.county=='Genesee County') & df_cleaned_offices.party.isna()].candidate.unique()\n",
    "df.loc[(df.county=='Genesee County') & (df.candidate=='Gillibrand')] = ''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>county</th>\n",
       "      <th>precinct</th>\n",
       "      <th>office</th>\n",
       "      <th>district</th>\n",
       "      <th>candidate</th>\n",
       "      <th>party</th>\n",
       "      <th>votes</th>\n",
       "      <th>election_day</th>\n",
       "      <th>absentee</th>\n",
       "      <th>machine_votes</th>\n",
       "      <th>absentee_hc</th>\n",
       "      <th>affidavit</th>\n",
       "      <th>Unnamed: 7</th>\n",
       "      <th>Unnamed: 8</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [county, precinct, office, district, candidate, party, votes, election_day, absentee, machine_votes, absentee_hc, affidavit, Unnamed: 7, Unnamed: 8]\n",
       "Index: []"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[(df.county=='Genesee County') & (df.candidate=='Gillibrand')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>county</th>\n",
       "      <th>precinct</th>\n",
       "      <th>office</th>\n",
       "      <th>district</th>\n",
       "      <th>candidate</th>\n",
       "      <th>party</th>\n",
       "      <th>votes</th>\n",
       "      <th>election_day</th>\n",
       "      <th>absentee</th>\n",
       "      <th>machine_votes</th>\n",
       "      <th>absentee_hc</th>\n",
       "      <th>affidavit</th>\n",
       "      <th>Unnamed: 7</th>\n",
       "      <th>Unnamed: 8</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>497784</th>\n",
       "      <td>Genesee County</td>\n",
       "      <td>Alabama 1</td>\n",
       "      <td>U.S. Senate</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Gillibrand</td>\n",
       "      <td>NaN</td>\n",
       "      <td>105</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>497785</th>\n",
       "      <td>Genesee County</td>\n",
       "      <td>Alabama 1</td>\n",
       "      <td>U.S. Senate</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CC Farley</td>\n",
       "      <td>NaN</td>\n",
       "      <td>185</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>497786</th>\n",
       "      <td>Genesee County</td>\n",
       "      <td>Alabama 1</td>\n",
       "      <td>U.S. Senate</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CC Farley</td>\n",
       "      <td>NaN</td>\n",
       "      <td>21</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>497787</th>\n",
       "      <td>Genesee County</td>\n",
       "      <td>Alabama 1</td>\n",
       "      <td>U.S. Senate</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Gillibrand</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>497788</th>\n",
       "      <td>Genesee County</td>\n",
       "      <td>Alabama 1</td>\n",
       "      <td>U.S. Senate</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Gillibrand</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2792</th>\n",
       "      <td>Genesee County</td>\n",
       "      <td>Ward 6-2</td>\n",
       "      <td>U.S. House</td>\n",
       "      <td>27</td>\n",
       "      <td>Collins</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2793</th>\n",
       "      <td>Genesee County</td>\n",
       "      <td>Ward 6-2</td>\n",
       "      <td>U.S. House</td>\n",
       "      <td>27</td>\n",
       "      <td>McMurray</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2794</th>\n",
       "      <td>Genesee County</td>\n",
       "      <td>Ward 6-2</td>\n",
       "      <td>U.S. House</td>\n",
       "      <td>27</td>\n",
       "      <td>Collins</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2795</th>\n",
       "      <td>Genesee County</td>\n",
       "      <td>Ward 6-2</td>\n",
       "      <td>U.S. House</td>\n",
       "      <td>27</td>\n",
       "      <td>McMurray</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2796</th>\n",
       "      <td>Genesee County</td>\n",
       "      <td>Ward 6-2</td>\n",
       "      <td>U.S. House</td>\n",
       "      <td>27</td>\n",
       "      <td>Piegza</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2014 rows × 14 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                county   precinct       office district   candidate party  \\\n",
       "497784  Genesee County  Alabama 1  U.S. Senate      NaN  Gillibrand   NaN   \n",
       "497785  Genesee County  Alabama 1  U.S. Senate      NaN   CC Farley   NaN   \n",
       "497786  Genesee County  Alabama 1  U.S. Senate      NaN   CC Farley   NaN   \n",
       "497787  Genesee County  Alabama 1  U.S. Senate      NaN  Gillibrand   NaN   \n",
       "497788  Genesee County  Alabama 1  U.S. Senate      NaN  Gillibrand   NaN   \n",
       "...                ...        ...          ...      ...         ...   ...   \n",
       "2792    Genesee County   Ward 6-2   U.S. House       27     Collins   NaN   \n",
       "2793    Genesee County   Ward 6-2   U.S. House       27    McMurray   NaN   \n",
       "2794    Genesee County   Ward 6-2   U.S. House       27     Collins   NaN   \n",
       "2795    Genesee County   Ward 6-2   U.S. House       27    McMurray   NaN   \n",
       "2796    Genesee County   Ward 6-2   U.S. House       27      Piegza   NaN   \n",
       "\n",
       "        votes  election_day  absentee  machine_votes  absentee_hc  affidavit  \\\n",
       "497784    105           NaN       NaN            NaN          NaN        NaN   \n",
       "497785    185           NaN       NaN            NaN          NaN        NaN   \n",
       "497786     21           NaN       NaN            NaN          NaN        NaN   \n",
       "497787      4           NaN       NaN            NaN          NaN        NaN   \n",
       "497788      3           NaN       NaN            NaN          NaN        NaN   \n",
       "...       ...           ...       ...            ...          ...        ...   \n",
       "2792       14           NaN       NaN            NaN          NaN        NaN   \n",
       "2793        9           NaN       NaN            NaN          NaN        NaN   \n",
       "2794        7           NaN       NaN            NaN          NaN        NaN   \n",
       "2795        2           NaN       NaN            NaN          NaN        NaN   \n",
       "2796        6           NaN       NaN            NaN          NaN        NaN   \n",
       "\n",
       "        Unnamed: 7  Unnamed: 8  \n",
       "497784         NaN         NaN  \n",
       "497785         NaN         NaN  \n",
       "497786         NaN         NaN  \n",
       "497787         NaN         NaN  \n",
       "497788         NaN         NaN  \n",
       "...            ...         ...  \n",
       "2792           NaN         NaN  \n",
       "2793           NaN         NaN  \n",
       "2794           NaN         NaN  \n",
       "2795           NaN         NaN  \n",
       "2796           NaN         NaN  \n",
       "\n",
       "[2014 rows x 14 columns]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_cleaned_offices[(df_cleaned_offices.county=='Genesee County') & df_cleaned_offices.party.isna()]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Clean `party`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The oficial parties in New York are [defined as](https://www.elections.ny.gov/CFCommittees.html)\n",
    "\n",
    "- Democratic\n",
    "- Republican\n",
    "- Conservative\n",
    "- Working Families\n",
    "- Green\n",
    "- Libertarian\n",
    "- Independence\n",
    "- SAM\n",
    "\n",
    "Since the official results also included `WEP (Women's Equality Party)` and `REF (Reform Party of New York State)` I will include those as well."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "74"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.party.nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "county\n",
       "Genesee County        0\n",
       "                      1\n",
       "Orange County         9\n",
       "Monroe County        10\n",
       "Nassau County        10\n",
       "                     ..\n",
       "Clinton County       17\n",
       "Kings County         17\n",
       "Wayne County         17\n",
       "Rensselaer County    20\n",
       "Rockland County      23\n",
       "Name: party, Length: 63, dtype: int64"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby('county').party.nunique().sort_values()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Genesee is missing party labels for candidates. Need to add them in before pivoting."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "official_party_to_abbreviation = {\n",
    "    \"Democratic\":\"DEM\",\n",
    "    \"Republican\":\"REP\",\n",
    "    \"Conservative\":\"CON\",\n",
    "    \"Working Families\":\"WOR\",\n",
    "    \"Green\":\"GRE\",\n",
    "    \"Libertarian\":\"LBT\",\n",
    "    \"Independence\":\"IND\",\n",
    "    \"SAM\": \"SAM\",\n",
    "    \"Women's Equality Party\":\"WEP\",\n",
    "    \"Reform Party\":\"REF\",\n",
    "}\n",
    "\n",
    "parties = set(official_party_to_abbreviation.values())\n",
    "misc = {'BLANK, VOID OR SCATTERED', 'WRITEIN', 'OVERVOTE', 'UNDERVOTE', 'UNQUALIFIED WRITE-INS'}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "party_lookup_table = {\n",
    "    'DEMOCRATIC':'DEM',\n",
    "    '0':'BLANK, VOID OR SCATTERED',\n",
    "    '1635':'BLANK, VOID OR SCATTERED',\n",
    "    '4328':'BLANK, VOID OR SCATTERED',\n",
    "    'BLANK & VOID':'BLANK, VOID OR SCATTERED',\n",
    "    'BLANKS':'BLANK, VOID OR SCATTERED',\n",
    "    'BLANKS & VOID':'BLANK, VOID OR SCATTERED',\n",
    "    'IND': 'IND',\n",
    "    'REP': 'REP',\n",
    " 'Write': 'Write',\n",
    " 'DEM': 'DEM',\n",
    " 'CON': 'CON',\n",
    " 'SAM': 'SAM',\n",
    "     'GRE': 'GRE',\n",
    " 'WOR': 'WOR',\n",
    " 'REF': 'REF',\n",
    " 'WEP': 'WEP',\n",
    " 'LBT': 'LBT',\n",
    " 'WRI':'WRITEIN',\n",
    " 'WRITE IN':'WRITEIN',\n",
    " 'WRITE INS':'WRITEIN',\n",
    " 'WRITE-IN':'WRITEIN',\n",
    " 'WRITE-INS':'WRITEIN',\n",
    " 'WRITEIN':'WRITEIN',\n",
    " 'WRITEINS':'WRITEIN',\n",
    "     'VOID':'BLANK, VOID OR SCATTERED',\n",
    " 'VOIDS':'BLANK, VOID OR SCATTERED',\n",
    " 'VOIDS/BLANKS':'BLANK, VOID OR SCATTERED',\n",
    "    'GREEN':'GRE',\n",
    " 'GRN':'GRE',\n",
    "     'BLANK':'BLANK, VOID OR SCATTERED',\n",
    " 'BLANK VOTES':'BLANK, VOID OR SCATTERED',\n",
    " 'BLANK/VOID':'BLANK, VOID OR SCATTERED',\n",
    "     'LIB':'LBT',\n",
    " 'LIBERTARIAN':'LBT',\n",
    "    'SCATTER':'BLANK, VOID OR SCATTERED',\n",
    " 'SCATTERED':'BLANK, VOID OR SCATTERED',\n",
    " 'SCATTERING':'BLANK, VOID OR SCATTERED',\n",
    " 'SCATTERINGS':'BLANK, VOID OR SCATTERED',\n",
    "'CONSERVATIVE':'CON',\n",
    "         'OVER':'OVERVOTE',\n",
    " 'OVER VOTES':'OVERVOTE',\n",
    " 'OVERVOTE':'OVERVOTE',\n",
    " 'OVERVOTES':'OVERVOTE',\n",
    " 'UNDER':'UNDERVOTE',\n",
    " 'UNDER VOTES':'UNDERVOTE',\n",
    " 'UNDER/OVER':'UNDERVOTE',\n",
    " 'UNDER/OVER VOTES':'UNDERVOTE',\n",
    " 'UNDERVOTE':'UNDERVOTE',\n",
    " 'UNDERVOTES':'UNDERVOTE',\n",
    "    'REPUBLICAN':'REP',\n",
    "    'REFORM':'REF',\n",
    "   'REFORM PARTY':'REF',\n",
    "    'INDEPENDENCE':'IND',\n",
    "    'WFP':'WOR',\n",
    "    'WF':'WOR',\n",
    "    \"WOMEN'S EQUALITY PARTY\":'WEP',\n",
    "    \"WOMEN'S EQUALITY\":\"WEP\",\n",
    "    # ballotpedia lookups\n",
    "    'GLOGOWSKI':'LBT',\n",
    "    \"CHRISTOPHER B. GARVEY\":'LBT',\n",
    "    'SEAMANS':'DEM',\n",
    "    'DINAPOLI':'DEM',\n",
    "    'GILLIBRAND':'DEM',\n",
    "    'JAMES': 'DEM',\n",
    "    'COLLINS':'REP',\n",
    "    'TRICHTER':'REP',\n",
    "    'CC FARLEY':'REP',\n",
    "    'WOFFORD':'REP',\n",
    "    'RANZENHOFER':'REP',\n",
    "    'HAWLEY':'REP',\n",
    "    'MCMURRAY':'DEM',\n",
    "}\n",
    "\n",
    "def clean_party(party):\n",
    "    if (type(party) != str):\n",
    "        return 'nS'\n",
    "    cleaned_party = party.strip().upper()\n",
    "    looked_up_party = party_lookup_table.get(cleaned_party, cleaned_party)\n",
    "    return official_party_to_abbreviation.get(looked_up_party.title(), looked_up_party)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.loc[~(df.party.str.upper().isin([\n",
    "        'BALLOTS CAST',\n",
    "        'TOTAL',\n",
    "        'TOTAL BALLOTS',\n",
    "        'TOTAL VOTES',\n",
    "        'REGISTERED VOTERS',\n",
    "        'FEDERAL',\n",
    "        'FEDERAL BALLOTS',\n",
    "        'FEDERAL VOTES',\n",
    "        'STATE BALLOTS',\n",
    "        'STATE VOTES',\n",
    "        'REGISTERED',\n",
    "        'PUBLIC COUNTER',]))\n",
    "        ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['party'] = df.party.map(clean_party)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['U.S. Senate', 'U.S. House', 'Governor'], dtype=object)"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df.county =='Genesee County'].office.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(10,)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "party\n",
       "SAM       62454\n",
       "LBT      103878\n",
       "REF      108673\n",
       "WEP      131414\n",
       "GRE      139193\n",
       "IND      234210\n",
       "WOR      563466\n",
       "CON      836638\n",
       "REP     6173122\n",
       "DEM    13077951\n",
       "Name: votes, dtype: int64"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['party'] = df.party.map(clean_party)\n",
    "df = df[df.party.isin(parties)]\n",
    "value_counts = df.groupby('party').votes.sum()\n",
    "print(value_counts[value_counts > 1000].shape)\n",
    "value_counts[value_counts > 1000].sort_values().tail(60)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(10,)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "party\n",
       "SAM       62454\n",
       "LBT      103878\n",
       "REF      108673\n",
       "WEP      131414\n",
       "GRE      139193\n",
       "IND      234210\n",
       "WOR      563466\n",
       "CON      836638\n",
       "REP     6173122\n",
       "DEM    13077951\n",
       "Name: votes, dtype: int64"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df[df.party.isin(parties)]\n",
    "value_counts = df.groupby('party').votes.sum()\n",
    "print(value_counts[value_counts > 1000].shape)\n",
    "value_counts[value_counts > 1000].sort_values()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'CON', 'DEM', 'GRE', 'IND', 'LBT', 'REF', 'REP', 'SAM', 'WEP', 'WOR'}"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "parties"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([], dtype=object)"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df.county =='Genesee County'].office.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Governor', 'U.S. Senate', 'U.S. House'], dtype=object)"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.office.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "office_to_abbreviation = {\n",
    "    'Governor':'Gov', \n",
    "    'Comptroller':'Compt', \n",
    "    'Attorney General':'AG',\n",
    "    'U.S. Senate':'Sen',\n",
    "    'U.S. House':'HOR',\n",
    "    'State Senate':'StSen', \n",
    "    'State Assembly':'StAssy'\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.office = df.office.map(office_to_abbreviation)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>county</th>\n",
       "      <th>precinct</th>\n",
       "      <th>office</th>\n",
       "      <th>district</th>\n",
       "      <th>candidate</th>\n",
       "      <th>party</th>\n",
       "      <th>votes</th>\n",
       "      <th>election_day</th>\n",
       "      <th>absentee</th>\n",
       "      <th>machine_votes</th>\n",
       "      <th>absentee_hc</th>\n",
       "      <th>affidavit</th>\n",
       "      <th>Unnamed: 7</th>\n",
       "      <th>Unnamed: 8</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Chemung County</td>\n",
       "      <td>Elmira 0901</td>\n",
       "      <td>Gov</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Andrew Cuomo</td>\n",
       "      <td>DEM</td>\n",
       "      <td>139</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Chemung County</td>\n",
       "      <td>Elmira 0902</td>\n",
       "      <td>Gov</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Andrew Cuomo</td>\n",
       "      <td>DEM</td>\n",
       "      <td>104</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Chemung County</td>\n",
       "      <td>Elmira 0903</td>\n",
       "      <td>Gov</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Andrew Cuomo</td>\n",
       "      <td>DEM</td>\n",
       "      <td>84</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Chemung County</td>\n",
       "      <td>Elmira 0904</td>\n",
       "      <td>Gov</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Andrew Cuomo</td>\n",
       "      <td>DEM</td>\n",
       "      <td>64</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Chemung County</td>\n",
       "      <td>Elmira 0905</td>\n",
       "      <td>Gov</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Andrew Cuomo</td>\n",
       "      <td>DEM</td>\n",
       "      <td>15</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           county     precinct office district     candidate party votes  \\\n",
       "0  Chemung County  Elmira 0901    Gov      NaN  Andrew Cuomo   DEM   139   \n",
       "1  Chemung County  Elmira 0902    Gov      NaN  Andrew Cuomo   DEM   104   \n",
       "2  Chemung County  Elmira 0903    Gov      NaN  Andrew Cuomo   DEM    84   \n",
       "3  Chemung County  Elmira 0904    Gov      NaN  Andrew Cuomo   DEM    64   \n",
       "4  Chemung County  Elmira 0905    Gov      NaN  Andrew Cuomo   DEM    15   \n",
       "\n",
       "  election_day absentee machine_votes absentee_hc affidavit Unnamed: 7  \\\n",
       "0          NaN      NaN           NaN         NaN       NaN        NaN   \n",
       "1          NaN      NaN           NaN         NaN       NaN        NaN   \n",
       "2          NaN      NaN           NaN         NaN       NaN        NaN   \n",
       "3          NaN      NaN           NaN         NaN       NaN        NaN   \n",
       "4          NaN      NaN           NaN         NaN       NaN        NaN   \n",
       "\n",
       "  Unnamed: 8  \n",
       "0        NaN  \n",
       "1        NaN  \n",
       "2        NaN  \n",
       "3        NaN  \n",
       "4        NaN  "
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "# remove empty columns and drop dups + `total` precs\n",
    "df = df[['county', 'precinct', 'office', 'district', 'candidate', 'party', 'votes']]\n",
    "df = df.drop_duplicates()\n",
    "precincts_to_remove_lower = ['total', 'county totals', 'tompkins ny', 'cortland ny', 'city of buffalo total']\n",
    "df = df[~df.precinct.str.lower().isin(precincts_to_remove_lower)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "piv = pd.pivot_table(df, index = ['county', 'precinct'], columns = ['office','party'], values = ['votes'], aggfunc = np.sum)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>Gov_CON</th>\n",
       "      <th>Gov_DEM</th>\n",
       "      <th>Gov_GRE</th>\n",
       "      <th>Gov_IND</th>\n",
       "      <th>Gov_LBT</th>\n",
       "      <th>Gov_REF</th>\n",
       "      <th>Gov_REP</th>\n",
       "      <th>Gov_SAM</th>\n",
       "      <th>Gov_WEP</th>\n",
       "      <th>Gov_WOR</th>\n",
       "      <th>...</th>\n",
       "      <th>HOR_REP</th>\n",
       "      <th>HOR_WEP</th>\n",
       "      <th>HOR_WOR</th>\n",
       "      <th>Sen_CON</th>\n",
       "      <th>Sen_DEM</th>\n",
       "      <th>Sen_IND</th>\n",
       "      <th>Sen_REF</th>\n",
       "      <th>Sen_REP</th>\n",
       "      <th>Sen_WEP</th>\n",
       "      <th>Sen_WOR</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>county</th>\n",
       "      <th>precinct</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">Albany County</th>\n",
       "      <th>0001 ALBANY W1 ED1</th>\n",
       "      <td>1.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>...</td>\n",
       "      <td>7.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0002 ALBANY W1 ED2</th>\n",
       "      <td>11.0</td>\n",
       "      <td>148.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>32.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>...</td>\n",
       "      <td>20.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>176.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>22.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0003 ALBANY W1 ED3</th>\n",
       "      <td>11.0</td>\n",
       "      <td>212.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>55.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>...</td>\n",
       "      <td>41.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>228.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0004 ALBANY W1 ED4</th>\n",
       "      <td>5.0</td>\n",
       "      <td>220.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>...</td>\n",
       "      <td>18.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>241.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0005 ALBANY W1 ED5</th>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                  Gov_CON  Gov_DEM  Gov_GRE  Gov_IND  Gov_LBT  \\\n",
       "county        precinct                                                          \n",
       "Albany County 0001 ALBANY W1 ED1      1.0     35.0      3.0      0.0      1.0   \n",
       "              0002 ALBANY W1 ED2     11.0    148.0     15.0      1.0      3.0   \n",
       "              0003 ALBANY W1 ED3     11.0    212.0      4.0      1.0      5.0   \n",
       "              0004 ALBANY W1 ED4      5.0    220.0      4.0      4.0      4.0   \n",
       "              0005 ALBANY W1 ED5      0.0      2.0      1.0      0.0      0.0   \n",
       "\n",
       "                                  Gov_REF  Gov_REP  Gov_SAM  Gov_WEP  Gov_WOR  \\\n",
       "county        precinct                                                          \n",
       "Albany County 0001 ALBANY W1 ED1      0.0      9.0      2.0      0.0      3.0   \n",
       "              0002 ALBANY W1 ED2      2.0     32.0      9.0      1.0     11.0   \n",
       "              0003 ALBANY W1 ED3      0.0     55.0      5.0      3.0     10.0   \n",
       "              0004 ALBANY W1 ED4      3.0     30.0      7.0      1.0     12.0   \n",
       "              0005 ALBANY W1 ED5      0.0      0.0      0.0      0.0      0.0   \n",
       "\n",
       "                                  ...  HOR_REP  HOR_WEP  HOR_WOR  Sen_CON  \\\n",
       "county        precinct            ...                                       \n",
       "Albany County 0001 ALBANY W1 ED1  ...      7.0      1.0      5.0      1.0   \n",
       "              0002 ALBANY W1 ED2  ...     20.0      6.0     21.0      5.0   \n",
       "              0003 ALBANY W1 ED3  ...     41.0      1.0     11.0     11.0   \n",
       "              0004 ALBANY W1 ED4  ...     18.0      5.0     18.0      6.0   \n",
       "              0005 ALBANY W1 ED5  ...      0.0      0.0      0.0      0.0   \n",
       "\n",
       "                                  Sen_DEM  Sen_IND  Sen_REF  Sen_REP  Sen_WEP  \\\n",
       "county        precinct                                                          \n",
       "Albany County 0001 ALBANY W1 ED1     40.0      0.0      0.0      8.0      0.0   \n",
       "              0002 ALBANY W1 ED2    176.0      4.0      1.0     23.0      4.0   \n",
       "              0003 ALBANY W1 ED3    228.0      5.0      0.0     40.0      5.0   \n",
       "              0004 ALBANY W1 ED4    241.0      5.0      3.0     15.0      4.0   \n",
       "              0005 ALBANY W1 ED5      4.0      0.0      0.0      0.0      0.0   \n",
       "\n",
       "                                  Sen_WOR  \n",
       "county        precinct                     \n",
       "Albany County 0001 ALBANY W1 ED1      6.0  \n",
       "              0002 ALBANY W1 ED2     22.0  \n",
       "              0003 ALBANY W1 ED3     10.0  \n",
       "              0004 ALBANY W1 ED4     15.0  \n",
       "              0005 ALBANY W1 ED5      0.0  \n",
       "\n",
       "[5 rows x 25 columns]"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "piv.columns = [office + '_' + party for _,office,party in piv.columns]\n",
    "piv.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Gov_CON</th>\n",
       "      <th>Gov_DEM</th>\n",
       "      <th>Gov_GRE</th>\n",
       "      <th>Gov_IND</th>\n",
       "      <th>Gov_LBT</th>\n",
       "      <th>Gov_REF</th>\n",
       "      <th>Gov_REP</th>\n",
       "      <th>Gov_SAM</th>\n",
       "      <th>Gov_WEP</th>\n",
       "      <th>Gov_WOR</th>\n",
       "      <th>...</th>\n",
       "      <th>HOR_REP</th>\n",
       "      <th>HOR_WEP</th>\n",
       "      <th>HOR_WOR</th>\n",
       "      <th>Sen_CON</th>\n",
       "      <th>Sen_DEM</th>\n",
       "      <th>Sen_IND</th>\n",
       "      <th>Sen_REF</th>\n",
       "      <th>Sen_REP</th>\n",
       "      <th>Sen_WEP</th>\n",
       "      <th>Sen_WOR</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>county</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Albany County</th>\n",
       "      <td>6725.0</td>\n",
       "      <td>54909.0</td>\n",
       "      <td>3543.0</td>\n",
       "      <td>1408.0</td>\n",
       "      <td>1867.0</td>\n",
       "      <td>835.0</td>\n",
       "      <td>38357.0</td>\n",
       "      <td>3007.0</td>\n",
       "      <td>886.0</td>\n",
       "      <td>2489.0</td>\n",
       "      <td>...</td>\n",
       "      <td>32326.0</td>\n",
       "      <td>1640.0</td>\n",
       "      <td>4781.0</td>\n",
       "      <td>6034.0</td>\n",
       "      <td>70182.0</td>\n",
       "      <td>2655.0</td>\n",
       "      <td>597.0</td>\n",
       "      <td>29825.0</td>\n",
       "      <td>1395.0</td>\n",
       "      <td>3883.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Allegany County</th>\n",
       "      <td>833.0</td>\n",
       "      <td>12488.0</td>\n",
       "      <td>183.0</td>\n",
       "      <td>106.0</td>\n",
       "      <td>963.0</td>\n",
       "      <td>65.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>117.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2252.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>892.0</td>\n",
       "      <td>4844.0</td>\n",
       "      <td>222.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>8305.0</td>\n",
       "      <td>99.0</td>\n",
       "      <td>295.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bronx County</th>\n",
       "      <td>2407.0</td>\n",
       "      <td>253712.0</td>\n",
       "      <td>2828.0</td>\n",
       "      <td>2143.0</td>\n",
       "      <td>1074.0</td>\n",
       "      <td>271.0</td>\n",
       "      <td>20401.0</td>\n",
       "      <td>593.0</td>\n",
       "      <td>503.0</td>\n",
       "      <td>4198.0</td>\n",
       "      <td>...</td>\n",
       "      <td>14712.0</td>\n",
       "      <td>1204.0</td>\n",
       "      <td>8339.0</td>\n",
       "      <td>2590.0</td>\n",
       "      <td>249485.0</td>\n",
       "      <td>2369.0</td>\n",
       "      <td>331.0</td>\n",
       "      <td>20112.0</td>\n",
       "      <td>698.0</td>\n",
       "      <td>5171.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Broome County</th>\n",
       "      <td>3244.0</td>\n",
       "      <td>29065.0</td>\n",
       "      <td>1320.0</td>\n",
       "      <td>931.0</td>\n",
       "      <td>2000.0</td>\n",
       "      <td>474.0</td>\n",
       "      <td>32865.0</td>\n",
       "      <td>802.0</td>\n",
       "      <td>379.0</td>\n",
       "      <td>1144.0</td>\n",
       "      <td>...</td>\n",
       "      <td>29452.0</td>\n",
       "      <td>469.0</td>\n",
       "      <td>1774.0</td>\n",
       "      <td>2910.0</td>\n",
       "      <td>37053.0</td>\n",
       "      <td>1588.0</td>\n",
       "      <td>331.0</td>\n",
       "      <td>27656.0</td>\n",
       "      <td>597.0</td>\n",
       "      <td>1907.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Cattaraugus County</th>\n",
       "      <td>1629.0</td>\n",
       "      <td>6545.0</td>\n",
       "      <td>307.0</td>\n",
       "      <td>265.0</td>\n",
       "      <td>1060.0</td>\n",
       "      <td>159.0</td>\n",
       "      <td>13240.0</td>\n",
       "      <td>192.0</td>\n",
       "      <td>78.0</td>\n",
       "      <td>146.0</td>\n",
       "      <td>...</td>\n",
       "      <td>13173.0</td>\n",
       "      <td>239.0</td>\n",
       "      <td>444.0</td>\n",
       "      <td>1556.0</td>\n",
       "      <td>9199.0</td>\n",
       "      <td>556.0</td>\n",
       "      <td>114.0</td>\n",
       "      <td>11586.0</td>\n",
       "      <td>179.0</td>\n",
       "      <td>497.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",
       "      <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>Washington County</th>\n",
       "      <td>1395.0</td>\n",
       "      <td>5964.0</td>\n",
       "      <td>453.0</td>\n",
       "      <td>198.0</td>\n",
       "      <td>571.0</td>\n",
       "      <td>154.0</td>\n",
       "      <td>11330.0</td>\n",
       "      <td>199.0</td>\n",
       "      <td>82.0</td>\n",
       "      <td>167.0</td>\n",
       "      <td>...</td>\n",
       "      <td>10263.0</td>\n",
       "      <td>182.0</td>\n",
       "      <td>437.0</td>\n",
       "      <td>1255.0</td>\n",
       "      <td>9248.0</td>\n",
       "      <td>499.0</td>\n",
       "      <td>89.0</td>\n",
       "      <td>8596.0</td>\n",
       "      <td>168.0</td>\n",
       "      <td>550.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wayne County</th>\n",
       "      <td>2765.0</td>\n",
       "      <td>8655.0</td>\n",
       "      <td>435.0</td>\n",
       "      <td>255.0</td>\n",
       "      <td>1648.0</td>\n",
       "      <td>262.0</td>\n",
       "      <td>16547.0</td>\n",
       "      <td>461.0</td>\n",
       "      <td>114.0</td>\n",
       "      <td>217.0</td>\n",
       "      <td>...</td>\n",
       "      <td>15918.0</td>\n",
       "      <td>338.0</td>\n",
       "      <td>625.0</td>\n",
       "      <td>2712.0</td>\n",
       "      <td>12374.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>218.0</td>\n",
       "      <td>14592.0</td>\n",
       "      <td>267.0</td>\n",
       "      <td>660.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Westchester County</th>\n",
       "      <td>10295.0</td>\n",
       "      <td>212833.0</td>\n",
       "      <td>3738.0</td>\n",
       "      <td>4692.0</td>\n",
       "      <td>1892.0</td>\n",
       "      <td>1100.0</td>\n",
       "      <td>90785.0</td>\n",
       "      <td>2576.0</td>\n",
       "      <td>1865.0</td>\n",
       "      <td>3295.0</td>\n",
       "      <td>...</td>\n",
       "      <td>10079.0</td>\n",
       "      <td>3753.0</td>\n",
       "      <td>9666.0</td>\n",
       "      <td>10789.0</td>\n",
       "      <td>221750.0</td>\n",
       "      <td>4190.0</td>\n",
       "      <td>967.0</td>\n",
       "      <td>85222.0</td>\n",
       "      <td>2277.0</td>\n",
       "      <td>5956.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wyoming County</th>\n",
       "      <td>1258.0</td>\n",
       "      <td>2644.0</td>\n",
       "      <td>138.0</td>\n",
       "      <td>79.0</td>\n",
       "      <td>536.0</td>\n",
       "      <td>83.0</td>\n",
       "      <td>9024.0</td>\n",
       "      <td>103.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>82.0</td>\n",
       "      <td>...</td>\n",
       "      <td>7400.0</td>\n",
       "      <td>93.0</td>\n",
       "      <td>424.0</td>\n",
       "      <td>1252.0</td>\n",
       "      <td>4043.0</td>\n",
       "      <td>224.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>7852.0</td>\n",
       "      <td>81.0</td>\n",
       "      <td>289.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Yates County</th>\n",
       "      <td>560.0</td>\n",
       "      <td>2399.0</td>\n",
       "      <td>110.0</td>\n",
       "      <td>55.0</td>\n",
       "      <td>401.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>4386.0</td>\n",
       "      <td>104.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>63.0</td>\n",
       "      <td>...</td>\n",
       "      <td>4101.0</td>\n",
       "      <td>82.0</td>\n",
       "      <td>163.0</td>\n",
       "      <td>509.0</td>\n",
       "      <td>3393.0</td>\n",
       "      <td>130.0</td>\n",
       "      <td>39.0</td>\n",
       "      <td>3796.0</td>\n",
       "      <td>65.0</td>\n",
       "      <td>155.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>61 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                    Gov_CON   Gov_DEM  Gov_GRE  Gov_IND  Gov_LBT  Gov_REF  \\\n",
       "county                                                                      \n",
       "Albany County        6725.0   54909.0   3543.0   1408.0   1867.0    835.0   \n",
       "Allegany County       833.0   12488.0    183.0    106.0    963.0     65.0   \n",
       "Bronx County         2407.0  253712.0   2828.0   2143.0   1074.0    271.0   \n",
       "Broome County        3244.0   29065.0   1320.0    931.0   2000.0    474.0   \n",
       "Cattaraugus County   1629.0    6545.0    307.0    265.0   1060.0    159.0   \n",
       "...                     ...       ...      ...      ...      ...      ...   \n",
       "Washington County    1395.0    5964.0    453.0    198.0    571.0    154.0   \n",
       "Wayne County         2765.0    8655.0    435.0    255.0   1648.0    262.0   \n",
       "Westchester County  10295.0  212833.0   3738.0   4692.0   1892.0   1100.0   \n",
       "Wyoming County       1258.0    2644.0    138.0     79.0    536.0     83.0   \n",
       "Yates County          560.0    2399.0    110.0     55.0    401.0     45.0   \n",
       "\n",
       "                    Gov_REP  Gov_SAM  Gov_WEP  Gov_WOR  ...  HOR_REP  HOR_WEP  \\\n",
       "county                                                  ...                     \n",
       "Albany County       38357.0   3007.0    886.0   2489.0  ...  32326.0   1640.0   \n",
       "Allegany County         0.0    117.0     46.0     80.0  ...   2252.0      0.0   \n",
       "Bronx County        20401.0    593.0    503.0   4198.0  ...  14712.0   1204.0   \n",
       "Broome County       32865.0    802.0    379.0   1144.0  ...  29452.0    469.0   \n",
       "Cattaraugus County  13240.0    192.0     78.0    146.0  ...  13173.0    239.0   \n",
       "...                     ...      ...      ...      ...  ...      ...      ...   \n",
       "Washington County   11330.0    199.0     82.0    167.0  ...  10263.0    182.0   \n",
       "Wayne County        16547.0    461.0    114.0    217.0  ...  15918.0    338.0   \n",
       "Westchester County  90785.0   2576.0   1865.0   3295.0  ...  10079.0   3753.0   \n",
       "Wyoming County       9024.0    103.0     31.0     82.0  ...   7400.0     93.0   \n",
       "Yates County         4386.0    104.0     41.0     63.0  ...   4101.0     82.0   \n",
       "\n",
       "                    HOR_WOR  Sen_CON   Sen_DEM  Sen_IND  Sen_REF  Sen_REP  \\\n",
       "county                                                                      \n",
       "Albany County        4781.0   6034.0   70182.0   2655.0    597.0  29825.0   \n",
       "Allegany County         0.0    892.0    4844.0    222.0     64.0   8305.0   \n",
       "Bronx County         8339.0   2590.0  249485.0   2369.0    331.0  20112.0   \n",
       "Broome County        1774.0   2910.0   37053.0   1588.0    331.0  27656.0   \n",
       "Cattaraugus County    444.0   1556.0    9199.0    556.0    114.0  11586.0   \n",
       "...                     ...      ...       ...      ...      ...      ...   \n",
       "Washington County     437.0   1255.0    9248.0    499.0     89.0   8596.0   \n",
       "Wayne County          625.0   2712.0   12374.0      0.0    218.0  14592.0   \n",
       "Westchester County   9666.0  10789.0  221750.0   4190.0    967.0  85222.0   \n",
       "Wyoming County        424.0   1252.0    4043.0    224.0     74.0   7852.0   \n",
       "Yates County          163.0    509.0    3393.0    130.0     39.0   3796.0   \n",
       "\n",
       "                    Sen_WEP  Sen_WOR  \n",
       "county                                \n",
       "Albany County        1395.0   3883.0  \n",
       "Allegany County        99.0    295.0  \n",
       "Bronx County          698.0   5171.0  \n",
       "Broome County         597.0   1907.0  \n",
       "Cattaraugus County    179.0    497.0  \n",
       "...                     ...      ...  \n",
       "Washington County     168.0    550.0  \n",
       "Wayne County          267.0    660.0  \n",
       "Westchester County   2277.0   5956.0  \n",
       "Wyoming County         81.0    289.0  \n",
       "Yates County           65.0    155.0  \n",
       "\n",
       "[61 rows x 25 columns]"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "piv.groupby('county').sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Add in Gov results for Orange County"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Need to add a multi index to `df_Orange_Gov` to allow for indexed assignment with `piv` (which has a multi index) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>DEM</th>\n",
       "      <th>REP</th>\n",
       "      <th>CON</th>\n",
       "      <th>GRE</th>\n",
       "      <th>WOR</th>\n",
       "      <th>IND</th>\n",
       "      <th>WEP</th>\n",
       "      <th>REF</th>\n",
       "      <th>SAM</th>\n",
       "      <th>SAM.1</th>\n",
       "      <th>SAM.2</th>\n",
       "      <th>LBT</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>county</th>\n",
       "      <th>precinct</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">Orange County</th>\n",
       "      <th>Middletown - Ward 1-1</th>\n",
       "      <td>151</td>\n",
       "      <td>72</td>\n",
       "      <td>9</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Middletown - Ward 1-2</th>\n",
       "      <td>76</td>\n",
       "      <td>32</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Middletown - Ward 1-3</th>\n",
       "      <td>147</td>\n",
       "      <td>64</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Middletown - Ward 1-4</th>\n",
       "      <td>35</td>\n",
       "      <td>20</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Middletown - Ward 1-5</th>\n",
       "      <td>134</td>\n",
       "      <td>59</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     DEM  REP  CON  GRE  WOR  IND  WEP  REF  \\\n",
       "county        precinct                                                        \n",
       "Orange County Middletown - Ward 1-1  151   72    9    5    3    5    0    1   \n",
       "              Middletown - Ward 1-2   76   32    4    1    1    0    1    0   \n",
       "              Middletown - Ward 1-3  147   64    3    1    3    1    0    2   \n",
       "              Middletown - Ward 1-4   35   20    1    1    0    2    0    0   \n",
       "              Middletown - Ward 1-5  134   59    7    1    0    0    0    2   \n",
       "\n",
       "                                     SAM  SAM.1  SAM.2  LBT  \n",
       "county        precinct                                       \n",
       "Orange County Middletown - Ward 1-1    1      0      1    2  \n",
       "              Middletown - Ward 1-2    0      0      0    3  \n",
       "              Middletown - Ward 1-3    2      0      2    5  \n",
       "              Middletown - Ward 1-4    0      0      0    1  \n",
       "              Middletown - Ward 1-5    1      0      1    2  "
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_Orange_Gov = df_Orange_Gov.set_index('precinct')\n",
    "df_Orange_Gov['county'] = 'Orange County'\n",
    "df_Orange_Gov.set_index('county', append=True, inplace=True)\n",
    "df_Orange_Gov = df_Orange_Gov.reorder_levels(['county', 'precinct'])\n",
    "df_Orange_Gov.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Along the same lines, sorting the indices is needed so they line up."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "piv = piv.sort_index()\n",
    "df_Orange_Gov = df_Orange_Gov.sort_index()\n",
    "list(piv.loc['Orange County'].index) == list(df_Orange_Gov.loc['Orange County','DEM'].index)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now I can make the substitutions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Gov_CON',\n",
       " 'Gov_DEM',\n",
       " 'Gov_GRE',\n",
       " 'Gov_IND',\n",
       " 'Gov_LBT',\n",
       " 'Gov_REF',\n",
       " 'Gov_REP',\n",
       " 'Gov_SAM',\n",
       " 'Gov_WEP',\n",
       " 'Gov_WOR']"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "[x for x in piv.columns if 'Gov' in x]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "piv.loc['Orange County', 'Gov_CON'] = df_Orange_Gov['CON']\n",
    "piv.loc['Orange County', 'Gov_DEM'] = df_Orange_Gov['DEM']\n",
    "piv.loc['Orange County', 'Gov_GRE'] = 0 # not present in the results for orange county\n",
    "piv.loc['Orange County', 'Gov_IND'] = df_Orange_Gov['IND']\n",
    "piv.loc['Orange County', 'Gov_REF'] = df_Orange_Gov['REF']\n",
    "piv.loc['Orange County', 'Gov_REP'] = df_Orange_Gov['REP']\n",
    "piv.loc['Orange County', 'Gov_WEP'] = df_Orange_Gov['WEP']\n",
    "piv.loc['Orange County', 'Gov_WOR'] = df_Orange_Gov['WOR']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Gov_CON</th>\n",
       "      <th>Gov_DEM</th>\n",
       "      <th>Gov_GRE</th>\n",
       "      <th>Gov_IND</th>\n",
       "      <th>Gov_LBT</th>\n",
       "      <th>Gov_REF</th>\n",
       "      <th>Gov_REP</th>\n",
       "      <th>Gov_SAM</th>\n",
       "      <th>Gov_WEP</th>\n",
       "      <th>Gov_WOR</th>\n",
       "      <th>...</th>\n",
       "      <th>HOR_REP</th>\n",
       "      <th>HOR_WEP</th>\n",
       "      <th>HOR_WOR</th>\n",
       "      <th>Sen_CON</th>\n",
       "      <th>Sen_DEM</th>\n",
       "      <th>Sen_IND</th>\n",
       "      <th>Sen_REF</th>\n",
       "      <th>Sen_REP</th>\n",
       "      <th>Sen_WEP</th>\n",
       "      <th>Sen_WOR</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>precinct</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Blooming Grove 1-1</th>\n",
       "      <td>9.0</td>\n",
       "      <td>99.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>82.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11.0</td>\n",
       "      <td>116.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Blooming Grove 1-2</th>\n",
       "      <td>25.0</td>\n",
       "      <td>225.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.0</td>\n",
       "      <td>200.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20.0</td>\n",
       "      <td>260.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>175.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                    Gov_CON  Gov_DEM  Gov_GRE  Gov_IND  Gov_LBT  Gov_REF  \\\n",
       "precinct                                                                   \n",
       "Blooming Grove 1-1      9.0     99.0      0.0      0.0      NaN      5.0   \n",
       "Blooming Grove 1-2     25.0    225.0      0.0     12.0      NaN      8.0   \n",
       "\n",
       "                    Gov_REP  Gov_SAM  Gov_WEP  Gov_WOR  ...  HOR_REP  HOR_WEP  \\\n",
       "precinct                                                ...                     \n",
       "Blooming Grove 1-1     82.0      NaN      0.0      1.0  ...      NaN      NaN   \n",
       "Blooming Grove 1-2    200.0      NaN      2.0      3.0  ...      NaN      NaN   \n",
       "\n",
       "                    HOR_WOR  Sen_CON  Sen_DEM  Sen_IND  Sen_REF  Sen_REP  \\\n",
       "precinct                                                                   \n",
       "Blooming Grove 1-1      NaN     11.0    116.0      1.0      3.0     68.0   \n",
       "Blooming Grove 1-2      NaN     20.0    260.0     10.0      6.0    175.0   \n",
       "\n",
       "                    Sen_WEP  Sen_WOR  \n",
       "precinct                              \n",
       "Blooming Grove 1-1      0.0      2.0  \n",
       "Blooming Grove 1-2      5.0     10.0  \n",
       "\n",
       "[2 rows x 25 columns]"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "piv.loc['Orange County'].head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Add in HOR results for Orange County"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Need to add a multi index to `df_Orange_HOR` to allow for indexed assignment with `piv` (which has a multi index) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>DEM</th>\n",
       "      <th>REP</th>\n",
       "      <th>CON</th>\n",
       "      <th>WOR</th>\n",
       "      <th>IND</th>\n",
       "      <th>WEP</th>\n",
       "      <th>REF</th>\n",
       "      <th>WRITE IN</th>\n",
       "      <th>BLANK</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>county</th>\n",
       "      <th>precinct</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">Orange County</th>\n",
       "      <th>Middletown - Ward 1-1</th>\n",
       "      <td>159</td>\n",
       "      <td>67</td>\n",
       "      <td>7</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Middletown - Ward 1-2</th>\n",
       "      <td>79</td>\n",
       "      <td>34</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Middletown - Ward 1-3</th>\n",
       "      <td>158</td>\n",
       "      <td>47</td>\n",
       "      <td>7</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Middletown - Ward 1-4</th>\n",
       "      <td>36</td>\n",
       "      <td>19</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Middletown - Ward 1-5</th>\n",
       "      <td>139</td>\n",
       "      <td>53</td>\n",
       "      <td>7</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     DEM  REP  CON  WOR  IND  WEP  REF  \\\n",
       "county        precinct                                                   \n",
       "Orange County Middletown - Ward 1-1  159   67    7    6    4    2    1   \n",
       "              Middletown - Ward 1-2   79   34    3    1    0    0    0   \n",
       "              Middletown - Ward 1-3  158   47    7    3    2    1    2   \n",
       "              Middletown - Ward 1-4   36   19    0    2    1    0    0   \n",
       "              Middletown - Ward 1-5  139   53    7    6    1    0    1   \n",
       "\n",
       "                                     WRITE IN  BLANK  \n",
       "county        precinct                                \n",
       "Orange County Middletown - Ward 1-1         0     11  \n",
       "              Middletown - Ward 1-2         0      3  \n",
       "              Middletown - Ward 1-3         0     15  \n",
       "              Middletown - Ward 1-4         0      3  \n",
       "              Middletown - Ward 1-5         0      9  "
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_Orange_HOR = df_Orange_HOR.set_index('precinct')\n",
    "df_Orange_HOR['county'] = 'Orange County'\n",
    "df_Orange_HOR.set_index('county', append=True, inplace=True)\n",
    "df_Orange_HOR = df_Orange_HOR.reorder_levels(['county', 'precinct'])\n",
    "df_Orange_HOR.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Along the same lines, sorting the indices is needed so they line up."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "piv = piv.sort_index()\n",
    "df_Orange_HOR = df_Orange_HOR.sort_index()\n",
    "list(piv.loc['Orange County'].index) == list(df_Orange_HOR.loc['Orange County','DEM'].index)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now I can make the substitutions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['HOR_CON',\n",
       " 'HOR_DEM',\n",
       " 'HOR_GRE',\n",
       " 'HOR_IND',\n",
       " 'HOR_REF',\n",
       " 'HOR_REP',\n",
       " 'HOR_WEP',\n",
       " 'HOR_WOR']"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "[x for x in piv.columns if 'HOR' in x]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "piv.loc['Orange County', 'HOR_CON'] = df_Orange_HOR['CON']\n",
    "piv.loc['Orange County', 'HOR_DEM'] = df_Orange_HOR['DEM']\n",
    "piv.loc['Orange County', 'HOR_GRE'] = 0 # not present in the results for orange county\n",
    "piv.loc['Orange County', 'HOR_IND'] = df_Orange_HOR['IND']\n",
    "piv.loc['Orange County', 'HOR_REF'] = df_Orange_HOR['REF']\n",
    "piv.loc['Orange County', 'HOR_REP'] = df_Orange_HOR['REP']\n",
    "piv.loc['Orange County', 'HOR_WEP'] = df_Orange_HOR['WEP']\n",
    "piv.loc['Orange County', 'HOR_WOR'] = df_Orange_HOR['WOR']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Gov_CON</th>\n",
       "      <th>Gov_DEM</th>\n",
       "      <th>Gov_GRE</th>\n",
       "      <th>Gov_IND</th>\n",
       "      <th>Gov_LBT</th>\n",
       "      <th>Gov_REF</th>\n",
       "      <th>Gov_REP</th>\n",
       "      <th>Gov_SAM</th>\n",
       "      <th>Gov_WEP</th>\n",
       "      <th>Gov_WOR</th>\n",
       "      <th>...</th>\n",
       "      <th>HOR_REP</th>\n",
       "      <th>HOR_WEP</th>\n",
       "      <th>HOR_WOR</th>\n",
       "      <th>Sen_CON</th>\n",
       "      <th>Sen_DEM</th>\n",
       "      <th>Sen_IND</th>\n",
       "      <th>Sen_REF</th>\n",
       "      <th>Sen_REP</th>\n",
       "      <th>Sen_WEP</th>\n",
       "      <th>Sen_WOR</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>precinct</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Blooming Grove 1-1</th>\n",
       "      <td>9.0</td>\n",
       "      <td>99.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>82.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>64.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>116.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Blooming Grove 1-2</th>\n",
       "      <td>25.0</td>\n",
       "      <td>225.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.0</td>\n",
       "      <td>200.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>...</td>\n",
       "      <td>183.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>260.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>175.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                    Gov_CON  Gov_DEM  Gov_GRE  Gov_IND  Gov_LBT  Gov_REF  \\\n",
       "precinct                                                                   \n",
       "Blooming Grove 1-1      9.0     99.0      0.0      0.0      NaN      5.0   \n",
       "Blooming Grove 1-2     25.0    225.0      0.0     12.0      NaN      8.0   \n",
       "\n",
       "                    Gov_REP  Gov_SAM  Gov_WEP  Gov_WOR  ...  HOR_REP  HOR_WEP  \\\n",
       "precinct                                                ...                     \n",
       "Blooming Grove 1-1     82.0      NaN      0.0      1.0  ...     64.0      0.0   \n",
       "Blooming Grove 1-2    200.0      NaN      2.0      3.0  ...    183.0      0.0   \n",
       "\n",
       "                    HOR_WOR  Sen_CON  Sen_DEM  Sen_IND  Sen_REF  Sen_REP  \\\n",
       "precinct                                                                   \n",
       "Blooming Grove 1-1      1.0     11.0    116.0      1.0      3.0     68.0   \n",
       "Blooming Grove 1-2      8.0     20.0    260.0     10.0      6.0    175.0   \n",
       "\n",
       "                    Sen_WEP  Sen_WOR  \n",
       "precinct                              \n",
       "Blooming Grove 1-1      0.0      2.0  \n",
       "Blooming Grove 1-2      5.0     10.0  \n",
       "\n",
       "[2 rows x 25 columns]"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "piv.loc['Orange County'].head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Patch in Counties that didn't match the official results\n",
    "\n",
    "Open Elections source data didn't match the official results for the following so I went to county websites to get their data directly:\n",
    "\n",
    "#### Jefferson County\n",
    "1. Download precinct level results from the Jefferson County [website](https://co.jefferson.ny.us/departments/Elections/election-results)\n",
    "2. Made some manual tweaks to clean up the handwork of the pdf to csv converter in Numbers (like Excel for Mac OS). Saved it locally at `data/parse_results/jefferson-county`\n",
    "3. Imported below"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Gov_DEM</th>\n",
       "      <th>Gov_REP</th>\n",
       "      <th>Gov_CON</th>\n",
       "      <th>Gov_GRE</th>\n",
       "      <th>Gov_WOR</th>\n",
       "      <th>Gov_IND</th>\n",
       "      <th>Gov_WEP</th>\n",
       "      <th>Gov_REF</th>\n",
       "      <th>Gov_SAM</th>\n",
       "      <th>Gov_LBT</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>precinct</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>12/1</th>\n",
       "      <td>327</td>\n",
       "      <td>353</td>\n",
       "      <td>52</td>\n",
       "      <td>9</td>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>27</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12/2</th>\n",
       "      <td>256</td>\n",
       "      <td>283</td>\n",
       "      <td>31</td>\n",
       "      <td>9</td>\n",
       "      <td>5</td>\n",
       "      <td>9</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>19</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Gov_DEM  Gov_REP  Gov_CON  Gov_GRE  Gov_WOR  Gov_IND  Gov_WEP  \\\n",
       "precinct                                                                  \n",
       "12/1          327      353       52        9        2       10        1   \n",
       "12/2          256      283       31        9        5        9        2   \n",
       "\n",
       "          Gov_REF  Gov_SAM  Gov_LBT  \n",
       "precinct                             \n",
       "12/1            4       27       11  \n",
       "12/2            1       19       21  "
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_Jefferson_Gov = pd.read_csv('data/parse_results/jefferson-county/Gov.csv')\n",
    "df_Jefferson_Gov = df_Jefferson_Gov.set_index('precinct')\n",
    "df_Jefferson_Gov.columns = ['Gov_' + col for col in df_Jefferson_Gov.columns]\n",
    "df_Jefferson_Gov.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>HOR_DEM</th>\n",
       "      <th>HOR_REP</th>\n",
       "      <th>HOR_CON</th>\n",
       "      <th>HOR_GRE</th>\n",
       "      <th>HOR_WOR</th>\n",
       "      <th>HOR_IND</th>\n",
       "      <th>HOR_WEP</th>\n",
       "      <th>HOR_REF</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>precinct</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>12/1</th>\n",
       "      <td>372</td>\n",
       "      <td>361</td>\n",
       "      <td>51</td>\n",
       "      <td>13</td>\n",
       "      <td>6</td>\n",
       "      <td>10</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12/2</th>\n",
       "      <td>306</td>\n",
       "      <td>283</td>\n",
       "      <td>29</td>\n",
       "      <td>17</td>\n",
       "      <td>9</td>\n",
       "      <td>12</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          HOR_DEM  HOR_REP  HOR_CON  HOR_GRE  HOR_WOR  HOR_IND  HOR_WEP  \\\n",
       "precinct                                                                  \n",
       "12/1          372      361       51       13        6       10        7   \n",
       "12/2          306      283       29       17        9       12        3   \n",
       "\n",
       "          HOR_REF  \n",
       "precinct           \n",
       "12/1            1  \n",
       "12/2            2  "
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_Jefferson_HOR = pd.read_csv('data/parse_results/jefferson-county/HOR.csv')\n",
    "df_Jefferson_HOR = df_Jefferson_HOR.set_index('precinct')\n",
    "df_Jefferson_HOR.columns = ['HOR_' + col for col in df_Jefferson_HOR.columns]\n",
    "df_Jefferson_HOR.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Sen_DEM</th>\n",
       "      <th>Sen_REP</th>\n",
       "      <th>Sen_CON</th>\n",
       "      <th>Sen_WOR</th>\n",
       "      <th>Sen_IND</th>\n",
       "      <th>Sen_WEP</th>\n",
       "      <th>Sen_REF</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>precinct</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>12/1</th>\n",
       "      <td>454</td>\n",
       "      <td>274</td>\n",
       "      <td>35</td>\n",
       "      <td>14</td>\n",
       "      <td>21</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12/2</th>\n",
       "      <td>353</td>\n",
       "      <td>230</td>\n",
       "      <td>24</td>\n",
       "      <td>13</td>\n",
       "      <td>20</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Sen_DEM  Sen_REP  Sen_CON  Sen_WOR  Sen_IND  Sen_WEP  Sen_REF\n",
       "precinct                                                               \n",
       "12/1          454      274       35       14       21        6        1\n",
       "12/2          353      230       24       13       20        3        5"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_Jefferson_Sen = pd.read_csv('data/parse_results/jefferson-county/Sen.csv')\n",
    "df_Jefferson_Sen = df_Jefferson_Sen.set_index('precinct')\n",
    "df_Jefferson_Sen.columns = ['Sen_' + col for col in df_Jefferson_Sen.columns]\n",
    "df_Jefferson_Sen.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>HOR_DEM</th>\n",
       "      <th>HOR_REP</th>\n",
       "      <th>HOR_CON</th>\n",
       "      <th>HOR_GRE</th>\n",
       "      <th>HOR_WOR</th>\n",
       "      <th>HOR_IND</th>\n",
       "      <th>HOR_WEP</th>\n",
       "      <th>HOR_REF</th>\n",
       "      <th>Sen_DEM</th>\n",
       "      <th>Sen_REP</th>\n",
       "      <th>...</th>\n",
       "      <th>Gov_DEM</th>\n",
       "      <th>Gov_REP</th>\n",
       "      <th>Gov_CON</th>\n",
       "      <th>Gov_GRE</th>\n",
       "      <th>Gov_WOR</th>\n",
       "      <th>Gov_IND</th>\n",
       "      <th>Gov_WEP</th>\n",
       "      <th>Gov_REF</th>\n",
       "      <th>Gov_SAM</th>\n",
       "      <th>Gov_LBT</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>county</th>\n",
       "      <th>precinct</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Jefferson County</th>\n",
       "      <th>12/1</th>\n",
       "      <td>372</td>\n",
       "      <td>361</td>\n",
       "      <td>51</td>\n",
       "      <td>13</td>\n",
       "      <td>6</td>\n",
       "      <td>10</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>454</td>\n",
       "      <td>274</td>\n",
       "      <td>...</td>\n",
       "      <td>327</td>\n",
       "      <td>353</td>\n",
       "      <td>52</td>\n",
       "      <td>9</td>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>27</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12/2</th>\n",
       "      <td>306</td>\n",
       "      <td>283</td>\n",
       "      <td>29</td>\n",
       "      <td>17</td>\n",
       "      <td>9</td>\n",
       "      <td>12</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>353</td>\n",
       "      <td>230</td>\n",
       "      <td>...</td>\n",
       "      <td>256</td>\n",
       "      <td>283</td>\n",
       "      <td>31</td>\n",
       "      <td>9</td>\n",
       "      <td>5</td>\n",
       "      <td>9</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>19</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                           HOR_DEM  HOR_REP  HOR_CON  HOR_GRE  HOR_WOR  \\\n",
       "county           precinct                                                \n",
       "Jefferson County 12/1          372      361       51       13        6   \n",
       "                 12/2          306      283       29       17        9   \n",
       "\n",
       "                           HOR_IND  HOR_WEP  HOR_REF  Sen_DEM  Sen_REP  ...  \\\n",
       "county           precinct                                               ...   \n",
       "Jefferson County 12/1           10        7        1      454      274  ...   \n",
       "                 12/2           12        3        2      353      230  ...   \n",
       "\n",
       "                           Gov_DEM  Gov_REP  Gov_CON  Gov_GRE  Gov_WOR  \\\n",
       "county           precinct                                                \n",
       "Jefferson County 12/1          327      353       52        9        2   \n",
       "                 12/2          256      283       31        9        5   \n",
       "\n",
       "                           Gov_IND  Gov_WEP  Gov_REF  Gov_SAM  Gov_LBT  \n",
       "county           precinct                                               \n",
       "Jefferson County 12/1           10        1        4       27       11  \n",
       "                 12/2            9        2        1       19       21  \n",
       "\n",
       "[2 rows x 25 columns]"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_Jefferson = df_Jefferson_HOR.join([df_Jefferson_Sen, df_Jefferson_Gov])\n",
    "df_Jefferson['county'] = 'Jefferson County'\n",
    "df_Jefferson.set_index('county', append=True, inplace=True)\n",
    "df_Jefferson = df_Jefferson.reorder_levels(['county', 'precinct'])\n",
    "df_Jefferson.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Add it into the rest of the results for the state\n",
    "piv.drop([('Jefferson County', precinct) for precinct in piv.loc['Jefferson County'].index], inplace=True)\n",
    "piv = pd.concat([df_Jefferson, piv])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Madison County\n",
    "1. Downloaded `data/parse_results/madison-county/United States Senator.pdf` from https://www.madisoncounty.ny.gov/DocumentCenter/View/7554/United-States-Senator\n",
    "2. Used https://pdftables.com/ to convert the pdf to a csv (`data/parse_results/madison-county/United States Senator.csv`)\n",
    "3. Made some manual tweaks to clean up the handwork of the pdf to csv converter in Numbers (like Excel for Mac OS). Saved it locally at `data/parse_results/madison-county/United States Senator-cleaned.csv`\n",
    "4. Imported below"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>precinct</th>\n",
       "      <th>DEM</th>\n",
       "      <th>REP</th>\n",
       "      <th>CON</th>\n",
       "      <th>WOR</th>\n",
       "      <th>IND</th>\n",
       "      <th>WEP</th>\n",
       "      <th>REF</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>City of Oneida 1</td>\n",
       "      <td>373</td>\n",
       "      <td>346</td>\n",
       "      <td>47</td>\n",
       "      <td>17</td>\n",
       "      <td>14</td>\n",
       "      <td>9</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>City of Oneida 2</td>\n",
       "      <td>322</td>\n",
       "      <td>295</td>\n",
       "      <td>36</td>\n",
       "      <td>9</td>\n",
       "      <td>22</td>\n",
       "      <td>8</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>City of Oneida 3</td>\n",
       "      <td>389</td>\n",
       "      <td>283</td>\n",
       "      <td>44</td>\n",
       "      <td>17</td>\n",
       "      <td>20</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>City of Oneida 4</td>\n",
       "      <td>127</td>\n",
       "      <td>116</td>\n",
       "      <td>20</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>City of Oneida 5</td>\n",
       "      <td>178</td>\n",
       "      <td>173</td>\n",
       "      <td>20</td>\n",
       "      <td>7</td>\n",
       "      <td>9</td>\n",
       "      <td>6</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           precinct  DEM  REP  CON  WOR  IND  WEP  REF\n",
       "0  City of Oneida 1  373  346   47   17   14    9    5\n",
       "1  City of Oneida 2  322  295   36    9   22    8    7\n",
       "2  City of Oneida 3  389  283   44   17   20    5    6\n",
       "3  City of Oneida 4  127  116   20    4    7    3    1\n",
       "4  City of Oneida 5  178  173   20    7    9    6   12"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_Madison_Sen = pd.read_csv('data/parse_results/madison-county/United States Senator-cleaned.csv')\n",
    "df_Madison_Sen.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>DEM</th>\n",
       "      <th>REP</th>\n",
       "      <th>CON</th>\n",
       "      <th>WOR</th>\n",
       "      <th>IND</th>\n",
       "      <th>WEP</th>\n",
       "      <th>REF</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>county</th>\n",
       "      <th>precinct</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">Madison County</th>\n",
       "      <th>City of Oneida 1</th>\n",
       "      <td>373</td>\n",
       "      <td>346</td>\n",
       "      <td>47</td>\n",
       "      <td>17</td>\n",
       "      <td>14</td>\n",
       "      <td>9</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>City of Oneida 2</th>\n",
       "      <td>322</td>\n",
       "      <td>295</td>\n",
       "      <td>36</td>\n",
       "      <td>9</td>\n",
       "      <td>22</td>\n",
       "      <td>8</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>City of Oneida 3</th>\n",
       "      <td>389</td>\n",
       "      <td>283</td>\n",
       "      <td>44</td>\n",
       "      <td>17</td>\n",
       "      <td>20</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>City of Oneida 4</th>\n",
       "      <td>127</td>\n",
       "      <td>116</td>\n",
       "      <td>20</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>City of Oneida 5</th>\n",
       "      <td>178</td>\n",
       "      <td>173</td>\n",
       "      <td>20</td>\n",
       "      <td>7</td>\n",
       "      <td>9</td>\n",
       "      <td>6</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 DEM  REP  CON  WOR  IND  WEP  REF\n",
       "county         precinct                                           \n",
       "Madison County City of Oneida 1  373  346   47   17   14    9    5\n",
       "               City of Oneida 2  322  295   36    9   22    8    7\n",
       "               City of Oneida 3  389  283   44   17   20    5    6\n",
       "               City of Oneida 4  127  116   20    4    7    3    1\n",
       "               City of Oneida 5  178  173   20    7    9    6   12"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_Madison_Sen = df_Madison_Sen.set_index('precinct')\n",
    "df_Madison_Sen['county'] = 'Madison County'\n",
    "df_Madison_Sen.set_index('county', append=True, inplace=True)\n",
    "df_Madison_Sen = df_Madison_Sen.reorder_levels(['county', 'precinct'])\n",
    "df_Madison_Sen.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "piv = piv.sort_index()\n",
    "df_Madison_Sen = df_Madison_Sen.sort_index()\n",
    "list(piv.loc['Madison County'].index) == list(df_Madison_Sen.loc['Madison County','DEM'].index)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now I can make the substitutions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Sen_DEM', 'Sen_REP', 'Sen_CON', 'Sen_WOR', 'Sen_IND', 'Sen_WEP', 'Sen_REF']"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "[x for x in piv.columns if 'Sen' in x]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [],
   "source": [
    "piv.loc['Madison County', 'Sen_CON'] = df_Madison_Sen['CON']\n",
    "piv.loc['Madison County', 'Sen_DEM'] = df_Madison_Sen['DEM']\n",
    "piv.loc['Madison County', 'Sen_IND'] = df_Madison_Sen['IND']\n",
    "piv.loc['Madison County', 'Sen_REF'] = df_Madison_Sen['REF']\n",
    "piv.loc['Madison County', 'Sen_REP'] = df_Madison_Sen['REP']\n",
    "piv.loc['Madison County', 'Sen_WEP'] = df_Madison_Sen['WEP']\n",
    "piv.loc['Madison County', 'Sen_WOR'] = df_Madison_Sen['WOR']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>HOR_DEM</th>\n",
       "      <th>HOR_REP</th>\n",
       "      <th>HOR_CON</th>\n",
       "      <th>HOR_GRE</th>\n",
       "      <th>HOR_WOR</th>\n",
       "      <th>HOR_IND</th>\n",
       "      <th>HOR_WEP</th>\n",
       "      <th>HOR_REF</th>\n",
       "      <th>Sen_DEM</th>\n",
       "      <th>Sen_REP</th>\n",
       "      <th>...</th>\n",
       "      <th>Gov_DEM</th>\n",
       "      <th>Gov_REP</th>\n",
       "      <th>Gov_CON</th>\n",
       "      <th>Gov_GRE</th>\n",
       "      <th>Gov_WOR</th>\n",
       "      <th>Gov_IND</th>\n",
       "      <th>Gov_WEP</th>\n",
       "      <th>Gov_REF</th>\n",
       "      <th>Gov_SAM</th>\n",
       "      <th>Gov_LBT</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>precinct</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>City of Oneida 1</th>\n",
       "      <td>370.0</td>\n",
       "      <td>381.0</td>\n",
       "      <td>42.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>373.0</td>\n",
       "      <td>346.0</td>\n",
       "      <td>...</td>\n",
       "      <td>262.0</td>\n",
       "      <td>420.0</td>\n",
       "      <td>47.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>30.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>City of Oneida 2</th>\n",
       "      <td>334.0</td>\n",
       "      <td>295.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>322.0</td>\n",
       "      <td>295.0</td>\n",
       "      <td>...</td>\n",
       "      <td>233.0</td>\n",
       "      <td>347.0</td>\n",
       "      <td>43.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>38.0</td>\n",
       "      <td>22.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                  HOR_DEM  HOR_REP  HOR_CON  HOR_GRE  HOR_WOR  HOR_IND  \\\n",
       "precinct                                                                 \n",
       "City of Oneida 1    370.0    381.0     42.0      NaN     11.0     17.0   \n",
       "City of Oneida 2    334.0    295.0     33.0      NaN     12.0     28.0   \n",
       "\n",
       "                  HOR_WEP  HOR_REF  Sen_DEM  Sen_REP  ...  Gov_DEM  Gov_REP  \\\n",
       "precinct                                              ...                     \n",
       "City of Oneida 1      7.0      2.0    373.0    346.0  ...    262.0    420.0   \n",
       "City of Oneida 2      2.0      4.0    322.0    295.0  ...    233.0    347.0   \n",
       "\n",
       "                  Gov_CON  Gov_GRE  Gov_WOR  Gov_IND  Gov_WEP  Gov_REF  \\\n",
       "precinct                                                                 \n",
       "City of Oneida 1     47.0     18.0      8.0      6.0      6.0      6.0   \n",
       "City of Oneida 2     43.0      6.0      4.0     13.0      2.0      4.0   \n",
       "\n",
       "                  Gov_SAM  Gov_LBT  \n",
       "precinct                            \n",
       "City of Oneida 1     16.0     30.0  \n",
       "City of Oneida 2     38.0     22.0  \n",
       "\n",
       "[2 rows x 25 columns]"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "piv.loc['Madison County'].head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Lewis County\n",
    "\n",
    "https://www.lewiscounty.org/media/Board%20of%20Elections/Election%20Results/2018/GE18%20Election%20Results\n",
    "\n",
    "Not sure how Lewis County counts results... \n",
    "\n",
    "Does Abs/Aff need to get added to the data?\n",
    "\n",
    "If yes: then need to parse the pdf and manually add in the abs/aff column.\n",
    "If no: official county results are wrong and need to be corrected, but can use the OE data as is"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## TODO\n",
    "* [_] Validate Results for Gov\n",
    "* [_] Pull more county level official results and do sen + HOR?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Validation\n",
    "\n",
    "Compare the precinct level election results to official county level election results released by New York's Secretary of State.\n",
    "\n",
    "Official County Level Election Results Source: https://www.elections.ny.gov/2018ElectionResults.html"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Clean up the pivoted data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [],
   "source": [
    "piv = remove_commas(piv.fillna(0), piv.columns)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Compare Senate Results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [],
   "source": [
    "office_prefix = 'Sen'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Kirsten E. Gillibrand (DEM)</th>\n",
       "      <th>Chele Chiavacci Farley (REP)</th>\n",
       "      <th>Chele Chiavacci Farley (CON)</th>\n",
       "      <th>Kirsten E. Gillibrand (WOR)</th>\n",
       "      <th>Kirsten E. Gillibrand (IND)</th>\n",
       "      <th>Kirsten E. Gillibrand (WEP)</th>\n",
       "      <th>Chele Chiavacci Farley (REF)</th>\n",
       "      <th>Blank</th>\n",
       "      <th>Void</th>\n",
       "      <th>Scattering</th>\n",
       "      <th>Total Votes by County</th>\n",
       "      <th>Unnamed: 12</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>County</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Albany County</th>\n",
       "      <td>70182</td>\n",
       "      <td>29825</td>\n",
       "      <td>6034</td>\n",
       "      <td>3883</td>\n",
       "      <td>2655</td>\n",
       "      <td>1395</td>\n",
       "      <td>597</td>\n",
       "      <td>2545</td>\n",
       "      <td>56</td>\n",
       "      <td>124</td>\n",
       "      <td>117296</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Allegany County</th>\n",
       "      <td>4844</td>\n",
       "      <td>8305</td>\n",
       "      <td>892</td>\n",
       "      <td>295</td>\n",
       "      <td>222</td>\n",
       "      <td>99</td>\n",
       "      <td>64</td>\n",
       "      <td>626</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>15353</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 Kirsten E. Gillibrand (DEM)  Chele Chiavacci Farley (REP)  \\\n",
       "County                                                                       \n",
       "Albany County                          70182                         29825   \n",
       "Allegany County                         4844                          8305   \n",
       "\n",
       "                 Chele Chiavacci Farley (CON)  Kirsten E. Gillibrand (WOR)  \\\n",
       "County                                                                       \n",
       "Albany County                            6034                         3883   \n",
       "Allegany County                           892                          295   \n",
       "\n",
       "                 Kirsten E. Gillibrand (IND)  Kirsten E. Gillibrand (WEP)  \\\n",
       "County                                                                      \n",
       "Albany County                           2655                         1395   \n",
       "Allegany County                          222                           99   \n",
       "\n",
       "                 Chele Chiavacci Farley (REF)  Blank  Void  Scattering  \\\n",
       "County                                                                   \n",
       "Albany County                             597   2545    56         124   \n",
       "Allegany County                            64    626     3           3   \n",
       "\n",
       "                 Total Votes by County  Unnamed: 12  \n",
       "County                                               \n",
       "Albany County                   117296            0  \n",
       "Allegany County                  15353            0  "
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#import official county level results for the gubernatorial race\n",
    "official_county_level_election_results_file_path = 'data/official_county_level_election_results/2018USSenate.csv'\n",
    "official_county_level_election_results_df_sen = pd.read_csv(official_county_level_election_results_file_path,low_memory=False)\n",
    "official_county_level_election_results_df_sen = official_county_level_election_results_df_sen.set_index('County')\n",
    "official_county_level_election_results_df_sen.index = official_county_level_election_results_df_sen.index.str.strip()\n",
    "official_county_level_election_results_df_sen = remove_commas(official_county_level_election_results_df_sen, official_county_level_election_results_df_sen.columns)\n",
    "official_county_level_election_results_df_sen.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>DEM</th>\n",
       "      <th>REP</th>\n",
       "      <th>CON</th>\n",
       "      <th>WOR</th>\n",
       "      <th>IND</th>\n",
       "      <th>WEP</th>\n",
       "      <th>REF</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>County</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Albany County</th>\n",
       "      <td>70182</td>\n",
       "      <td>29825</td>\n",
       "      <td>6034</td>\n",
       "      <td>3883</td>\n",
       "      <td>2655</td>\n",
       "      <td>1395</td>\n",
       "      <td>597</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Allegany County</th>\n",
       "      <td>4844</td>\n",
       "      <td>8305</td>\n",
       "      <td>892</td>\n",
       "      <td>295</td>\n",
       "      <td>222</td>\n",
       "      <td>99</td>\n",
       "      <td>64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Broome County</th>\n",
       "      <td>37053</td>\n",
       "      <td>27656</td>\n",
       "      <td>2910</td>\n",
       "      <td>1907</td>\n",
       "      <td>1588</td>\n",
       "      <td>597</td>\n",
       "      <td>331</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Cattaraugus County</th>\n",
       "      <td>9199</td>\n",
       "      <td>11586</td>\n",
       "      <td>1556</td>\n",
       "      <td>497</td>\n",
       "      <td>556</td>\n",
       "      <td>179</td>\n",
       "      <td>114</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Cayuga County</th>\n",
       "      <td>12339</td>\n",
       "      <td>11318</td>\n",
       "      <td>1772</td>\n",
       "      <td>591</td>\n",
       "      <td>666</td>\n",
       "      <td>227</td>\n",
       "      <td>142</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      DEM    REP   CON   WOR   IND   WEP  REF\n",
       "County                                                       \n",
       "Albany County       70182  29825  6034  3883  2655  1395  597\n",
       "Allegany County      4844   8305   892   295   222    99   64\n",
       "Broome County       37053  27656  2910  1907  1588   597  331\n",
       "Cattaraugus County   9199  11586  1556   497   556   179  114\n",
       "Cayuga County       12339  11318  1772   591   666   227  142"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "o_sen = official_county_level_election_results_df_sen[[x for x in official_county_level_election_results_df_sen.columns if '(' in x]]\n",
    "o_sen.columns = [x.split('(')[1][:3] for x in o_sen.columns]\n",
    "o_sen.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>DEM</th>\n",
       "      <th>REP</th>\n",
       "      <th>CON</th>\n",
       "      <th>WOR</th>\n",
       "      <th>IND</th>\n",
       "      <th>WEP</th>\n",
       "      <th>REF</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>county</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Albany County</th>\n",
       "      <td>70182</td>\n",
       "      <td>29825</td>\n",
       "      <td>6034</td>\n",
       "      <td>3883</td>\n",
       "      <td>2655</td>\n",
       "      <td>1395</td>\n",
       "      <td>597</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Allegany County</th>\n",
       "      <td>4844</td>\n",
       "      <td>8305</td>\n",
       "      <td>892</td>\n",
       "      <td>295</td>\n",
       "      <td>222</td>\n",
       "      <td>99</td>\n",
       "      <td>64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bronx County</th>\n",
       "      <td>249485</td>\n",
       "      <td>20112</td>\n",
       "      <td>2590</td>\n",
       "      <td>5171</td>\n",
       "      <td>2369</td>\n",
       "      <td>698</td>\n",
       "      <td>331</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Broome County</th>\n",
       "      <td>37053</td>\n",
       "      <td>27656</td>\n",
       "      <td>2910</td>\n",
       "      <td>1907</td>\n",
       "      <td>1588</td>\n",
       "      <td>597</td>\n",
       "      <td>331</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Cattaraugus County</th>\n",
       "      <td>9199</td>\n",
       "      <td>11586</td>\n",
       "      <td>1556</td>\n",
       "      <td>497</td>\n",
       "      <td>556</td>\n",
       "      <td>179</td>\n",
       "      <td>114</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       DEM    REP   CON   WOR   IND   WEP  REF\n",
       "county                                                        \n",
       "Albany County        70182  29825  6034  3883  2655  1395  597\n",
       "Allegany County       4844   8305   892   295   222    99   64\n",
       "Bronx County        249485  20112  2590  5171  2369   698  331\n",
       "Broome County        37053  27656  2910  1907  1588   597  331\n",
       "Cattaraugus County    9199  11586  1556   497   556   179  114"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "res_sen = piv.groupby('county').sum()[[x for x in piv.columns if x.startswith(office_prefix)]]\n",
    "res_sen.columns = [x.split('_')[1] for x in res_sen.columns]\n",
    "res_sen.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Total votes cast for the race"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Accepting ratios between 0.98 and 1.02\n",
      "The outliers are: \n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "Lewis County       0.926589\n",
       "Onondaga County    0.967235\n",
       "Wayne County       0.979970\n",
       "Wyoming County     1.049214\n",
       "dtype: float64"
      ]
     },
     "execution_count": 96,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "percent_diff = res_sen.sum(axis=1)/o_sen.sum(axis=1)\n",
    "acceptable_percent_diff = .02\n",
    "lower_bound = 1 - acceptable_percent_diff\n",
    "upper_bound = 1 + acceptable_percent_diff\n",
    "print(\"Accepting ratios between {} and {}\".format(lower_bound, upper_bound))\n",
    "print(\"The outliers are: \")\n",
    "outliers = percent_diff[(percent_diff < lower_bound) | (percent_diff > upper_bound)]\n",
    "outliers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Lewis County       -648.0\n",
       "Onondaga County   -5760.0\n",
       "Wayne County       -630.0\n",
       "Wyoming County      648.0\n",
       "dtype: float64"
      ]
     },
     "execution_count": 97,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_votes_diff = res_sen.sum(axis=1) - o_sen.sum(axis=1)\n",
    "non_zero_votes_diff = total_votes_diff[total_votes_diff.index.isin(outliers.index)]\n",
    "non_zero_votes_diff"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>DEM</th>\n",
       "      <th>REP</th>\n",
       "      <th>CON</th>\n",
       "      <th>WOR</th>\n",
       "      <th>IND</th>\n",
       "      <th>WEP</th>\n",
       "      <th>REF</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>County</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Lewis County</th>\n",
       "      <td>3260</td>\n",
       "      <td>4593</td>\n",
       "      <td>490</td>\n",
       "      <td>197</td>\n",
       "      <td>193</td>\n",
       "      <td>57</td>\n",
       "      <td>37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Onondaga County</th>\n",
       "      <td>98804</td>\n",
       "      <td>55240</td>\n",
       "      <td>10455</td>\n",
       "      <td>3833</td>\n",
       "      <td>4855</td>\n",
       "      <td>1709</td>\n",
       "      <td>904</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wayne County</th>\n",
       "      <td>12374</td>\n",
       "      <td>14592</td>\n",
       "      <td>2712</td>\n",
       "      <td>660</td>\n",
       "      <td>630</td>\n",
       "      <td>267</td>\n",
       "      <td>218</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wyoming County</th>\n",
       "      <td>3766</td>\n",
       "      <td>7553</td>\n",
       "      <td>1215</td>\n",
       "      <td>280</td>\n",
       "      <td>214</td>\n",
       "      <td>70</td>\n",
       "      <td>69</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   DEM    REP    CON   WOR   IND   WEP  REF\n",
       "County                                                     \n",
       "Lewis County      3260   4593    490   197   193    57   37\n",
       "Onondaga County  98804  55240  10455  3833  4855  1709  904\n",
       "Wayne County     12374  14592   2712   660   630   267  218\n",
       "Wyoming County    3766   7553   1215   280   214    70   69"
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "o_sen[o_sen.index.isin(outliers.index)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>DEM</th>\n",
       "      <th>REP</th>\n",
       "      <th>CON</th>\n",
       "      <th>WOR</th>\n",
       "      <th>IND</th>\n",
       "      <th>WEP</th>\n",
       "      <th>REF</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>county</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Lewis County</th>\n",
       "      <td>2981</td>\n",
       "      <td>4298</td>\n",
       "      <td>449</td>\n",
       "      <td>187</td>\n",
       "      <td>181</td>\n",
       "      <td>49</td>\n",
       "      <td>34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Onondaga County</th>\n",
       "      <td>98804</td>\n",
       "      <td>51013</td>\n",
       "      <td>9695</td>\n",
       "      <td>3600</td>\n",
       "      <td>4486</td>\n",
       "      <td>1601</td>\n",
       "      <td>841</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wayne County</th>\n",
       "      <td>12374</td>\n",
       "      <td>14592</td>\n",
       "      <td>2712</td>\n",
       "      <td>660</td>\n",
       "      <td>0</td>\n",
       "      <td>267</td>\n",
       "      <td>218</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wyoming County</th>\n",
       "      <td>4043</td>\n",
       "      <td>7852</td>\n",
       "      <td>1252</td>\n",
       "      <td>289</td>\n",
       "      <td>224</td>\n",
       "      <td>81</td>\n",
       "      <td>74</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   DEM    REP   CON   WOR   IND   WEP  REF\n",
       "county                                                    \n",
       "Lewis County      2981   4298   449   187   181    49   34\n",
       "Onondaga County  98804  51013  9695  3600  4486  1601  841\n",
       "Wayne County     12374  14592  2712   660     0   267  218\n",
       "Wyoming County    4043   7852  1252   289   224    81   74"
      ]
     },
     "execution_count": 99,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "res_sen[res_sen.index.isin(outliers.index)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Compare Gubernatorial results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Andrew M. Cuomo and Kathy C. Hochul (DEM)</th>\n",
       "      <th>Marc Molinaro and Julie Killian (REP)</th>\n",
       "      <th>Marc Molinaro and Julie Killian (CON)</th>\n",
       "      <th>Howie Hawkins and Jia Lee (GRE)</th>\n",
       "      <th>Andrew M. Cuomo and Kathy C. Hochul (WOR)</th>\n",
       "      <th>Andrew M. Cuomo and Kathy C. Hochul (IND)</th>\n",
       "      <th>Andrew M. Cuomo and Kathy C. Hochul (WEP)</th>\n",
       "      <th>Marc Molinaro and Julie Killian (REF)</th>\n",
       "      <th>Larry Sharpe and Andrew C. Hollister (LBT)</th>\n",
       "      <th>Stephanie A. Miner and Michael J. Volpe (SAM)</th>\n",
       "      <th>Blank</th>\n",
       "      <th>Void</th>\n",
       "      <th>Scattering</th>\n",
       "      <th>Total Votes by County</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>County</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Albany County</th>\n",
       "      <td>54909</td>\n",
       "      <td>38357</td>\n",
       "      <td>6725</td>\n",
       "      <td>3543</td>\n",
       "      <td>2489</td>\n",
       "      <td>1408</td>\n",
       "      <td>886</td>\n",
       "      <td>835</td>\n",
       "      <td>1867</td>\n",
       "      <td>3007</td>\n",
       "      <td>2807</td>\n",
       "      <td>268</td>\n",
       "      <td>195</td>\n",
       "      <td>117,296</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Allegany County</th>\n",
       "      <td>3254</td>\n",
       "      <td>9234</td>\n",
       "      <td>833</td>\n",
       "      <td>183</td>\n",
       "      <td>80</td>\n",
       "      <td>106</td>\n",
       "      <td>46</td>\n",
       "      <td>65</td>\n",
       "      <td>963</td>\n",
       "      <td>117</td>\n",
       "      <td>462</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>15,353</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 Andrew M. Cuomo and Kathy C. Hochul (DEM)  \\\n",
       "County                                                       \n",
       "Albany County                                        54909   \n",
       "Allegany County                                       3254   \n",
       "\n",
       "                 Marc Molinaro and Julie Killian (REP)  \\\n",
       "County                                                   \n",
       "Albany County                                    38357   \n",
       "Allegany County                                   9234   \n",
       "\n",
       "                 Marc Molinaro and Julie Killian (CON)  \\\n",
       "County                                                   \n",
       "Albany County                                     6725   \n",
       "Allegany County                                    833   \n",
       "\n",
       "                 Howie Hawkins and Jia Lee (GRE)  \\\n",
       "County                                             \n",
       "Albany County                               3543   \n",
       "Allegany County                              183   \n",
       "\n",
       "                 Andrew M. Cuomo and Kathy C. Hochul (WOR)  \\\n",
       "County                                                       \n",
       "Albany County                                         2489   \n",
       "Allegany County                                         80   \n",
       "\n",
       "                 Andrew M. Cuomo and Kathy C. Hochul (IND)  \\\n",
       "County                                                       \n",
       "Albany County                                         1408   \n",
       "Allegany County                                        106   \n",
       "\n",
       "                 Andrew M. Cuomo and Kathy C. Hochul (WEP)  \\\n",
       "County                                                       \n",
       "Albany County                                          886   \n",
       "Allegany County                                         46   \n",
       "\n",
       "                 Marc Molinaro and Julie Killian (REF)  \\\n",
       "County                                                   \n",
       "Albany County                                      835   \n",
       "Allegany County                                     65   \n",
       "\n",
       "                 Larry Sharpe and Andrew C. Hollister (LBT)  \\\n",
       "County                                                        \n",
       "Albany County                                          1867   \n",
       "Allegany County                                         963   \n",
       "\n",
       "                 Stephanie A. Miner and Michael J. Volpe (SAM)  Blank  Void  \\\n",
       "County                                                                        \n",
       "Albany County                                             3007   2807   268   \n",
       "Allegany County                                            117    462     3   \n",
       "\n",
       "                 Scattering Total Votes by County  \n",
       "County                                             \n",
       "Albany County           195               117,296  \n",
       "Allegany County           7                15,353  "
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#import official county level results for the gubernatorial race\n",
    "official_county_level_election_results_file_path_gov = 'data/official_county_level_election_results/2018Governor.csv'\n",
    "official_county_level_election_results_df_gov = pd.read_csv(official_county_level_election_results_file_path_gov,low_memory=False)\n",
    "official_county_level_election_results_df_gov = official_county_level_election_results_df_gov.set_index('County')\n",
    "official_county_level_election_results_df_gov.index = official_county_level_election_results_df_gov.index.str.strip()\n",
    "official_county_level_election_results_df_gov = remove_commas(official_county_level_election_results_df_gov, official_county_level_election_results_df_gov.columns[:-4])\n",
    "official_county_level_election_results_df_gov.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>DEM</th>\n",
       "      <th>REP</th>\n",
       "      <th>CON</th>\n",
       "      <th>GRE</th>\n",
       "      <th>WOR</th>\n",
       "      <th>IND</th>\n",
       "      <th>WEP</th>\n",
       "      <th>REF</th>\n",
       "      <th>LBT</th>\n",
       "      <th>SAM</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>County</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Albany County</th>\n",
       "      <td>54909</td>\n",
       "      <td>38357</td>\n",
       "      <td>6725</td>\n",
       "      <td>3543</td>\n",
       "      <td>2489</td>\n",
       "      <td>1408</td>\n",
       "      <td>886</td>\n",
       "      <td>835</td>\n",
       "      <td>1867</td>\n",
       "      <td>3007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Allegany County</th>\n",
       "      <td>3254</td>\n",
       "      <td>9234</td>\n",
       "      <td>833</td>\n",
       "      <td>183</td>\n",
       "      <td>80</td>\n",
       "      <td>106</td>\n",
       "      <td>46</td>\n",
       "      <td>65</td>\n",
       "      <td>963</td>\n",
       "      <td>117</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Broome County</th>\n",
       "      <td>29065</td>\n",
       "      <td>32865</td>\n",
       "      <td>3244</td>\n",
       "      <td>1320</td>\n",
       "      <td>1144</td>\n",
       "      <td>931</td>\n",
       "      <td>379</td>\n",
       "      <td>474</td>\n",
       "      <td>2000</td>\n",
       "      <td>802</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Cattaraugus County</th>\n",
       "      <td>6545</td>\n",
       "      <td>13240</td>\n",
       "      <td>1629</td>\n",
       "      <td>307</td>\n",
       "      <td>146</td>\n",
       "      <td>265</td>\n",
       "      <td>78</td>\n",
       "      <td>159</td>\n",
       "      <td>1060</td>\n",
       "      <td>192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Cayuga County</th>\n",
       "      <td>9112</td>\n",
       "      <td>12728</td>\n",
       "      <td>1799</td>\n",
       "      <td>520</td>\n",
       "      <td>232</td>\n",
       "      <td>333</td>\n",
       "      <td>106</td>\n",
       "      <td>179</td>\n",
       "      <td>1023</td>\n",
       "      <td>787</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      DEM    REP   CON   GRE   WOR   IND  WEP  REF   LBT   SAM\n",
       "County                                                                        \n",
       "Albany County       54909  38357  6725  3543  2489  1408  886  835  1867  3007\n",
       "Allegany County      3254   9234   833   183    80   106   46   65   963   117\n",
       "Broome County       29065  32865  3244  1320  1144   931  379  474  2000   802\n",
       "Cattaraugus County   6545  13240  1629   307   146   265   78  159  1060   192\n",
       "Cayuga County        9112  12728  1799   520   232   333  106  179  1023   787"
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "o_gov = official_county_level_election_results_df_gov[[x for x in official_county_level_election_results_df_gov.columns if '(' in x]]\n",
    "o_gov.columns = [x.split('(')[1][:3] for x in o_gov.columns]\n",
    "o_gov.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Gov_DEM</th>\n",
       "      <th>Gov_REP</th>\n",
       "      <th>Gov_CON</th>\n",
       "      <th>Gov_GRE</th>\n",
       "      <th>Gov_WOR</th>\n",
       "      <th>Gov_IND</th>\n",
       "      <th>Gov_WEP</th>\n",
       "      <th>Gov_REF</th>\n",
       "      <th>Gov_SAM</th>\n",
       "      <th>Gov_LBT</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>county</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Albany County</th>\n",
       "      <td>54909</td>\n",
       "      <td>38357</td>\n",
       "      <td>6725</td>\n",
       "      <td>3543</td>\n",
       "      <td>2489</td>\n",
       "      <td>1408</td>\n",
       "      <td>886</td>\n",
       "      <td>835</td>\n",
       "      <td>3007</td>\n",
       "      <td>1867</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Allegany County</th>\n",
       "      <td>12488</td>\n",
       "      <td>0</td>\n",
       "      <td>833</td>\n",
       "      <td>183</td>\n",
       "      <td>80</td>\n",
       "      <td>106</td>\n",
       "      <td>46</td>\n",
       "      <td>65</td>\n",
       "      <td>117</td>\n",
       "      <td>963</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bronx County</th>\n",
       "      <td>253712</td>\n",
       "      <td>20401</td>\n",
       "      <td>2407</td>\n",
       "      <td>2828</td>\n",
       "      <td>4198</td>\n",
       "      <td>2143</td>\n",
       "      <td>503</td>\n",
       "      <td>271</td>\n",
       "      <td>593</td>\n",
       "      <td>1074</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Broome County</th>\n",
       "      <td>29065</td>\n",
       "      <td>32865</td>\n",
       "      <td>3244</td>\n",
       "      <td>1320</td>\n",
       "      <td>1144</td>\n",
       "      <td>931</td>\n",
       "      <td>379</td>\n",
       "      <td>474</td>\n",
       "      <td>802</td>\n",
       "      <td>2000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Cattaraugus County</th>\n",
       "      <td>6545</td>\n",
       "      <td>13240</td>\n",
       "      <td>1629</td>\n",
       "      <td>307</td>\n",
       "      <td>146</td>\n",
       "      <td>265</td>\n",
       "      <td>78</td>\n",
       "      <td>159</td>\n",
       "      <td>192</td>\n",
       "      <td>1060</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    Gov_DEM  Gov_REP  Gov_CON  Gov_GRE  Gov_WOR  Gov_IND  \\\n",
       "county                                                                     \n",
       "Albany County         54909    38357     6725     3543     2489     1408   \n",
       "Allegany County       12488        0      833      183       80      106   \n",
       "Bronx County         253712    20401     2407     2828     4198     2143   \n",
       "Broome County         29065    32865     3244     1320     1144      931   \n",
       "Cattaraugus County     6545    13240     1629      307      146      265   \n",
       "\n",
       "                    Gov_WEP  Gov_REF  Gov_SAM  Gov_LBT  \n",
       "county                                                  \n",
       "Albany County           886      835     3007     1867  \n",
       "Allegany County          46       65      117      963  \n",
       "Bronx County            503      271      593     1074  \n",
       "Broome County           379      474      802     2000  \n",
       "Cattaraugus County       78      159      192     1060  "
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "res_gov = piv.groupby('county').sum()[[x for x in piv.columns if x.startswith('Gov')]]\n",
    "res_gov.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>DEM</th>\n",
       "      <th>REP</th>\n",
       "      <th>CON</th>\n",
       "      <th>GRE</th>\n",
       "      <th>WOR</th>\n",
       "      <th>IND</th>\n",
       "      <th>WEP</th>\n",
       "      <th>REF</th>\n",
       "      <th>SAM</th>\n",
       "      <th>LBT</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>county</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Albany County</th>\n",
       "      <td>54909</td>\n",
       "      <td>38357</td>\n",
       "      <td>6725</td>\n",
       "      <td>3543</td>\n",
       "      <td>2489</td>\n",
       "      <td>1408</td>\n",
       "      <td>886</td>\n",
       "      <td>835</td>\n",
       "      <td>3007</td>\n",
       "      <td>1867</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Allegany County</th>\n",
       "      <td>12488</td>\n",
       "      <td>0</td>\n",
       "      <td>833</td>\n",
       "      <td>183</td>\n",
       "      <td>80</td>\n",
       "      <td>106</td>\n",
       "      <td>46</td>\n",
       "      <td>65</td>\n",
       "      <td>117</td>\n",
       "      <td>963</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bronx County</th>\n",
       "      <td>253712</td>\n",
       "      <td>20401</td>\n",
       "      <td>2407</td>\n",
       "      <td>2828</td>\n",
       "      <td>4198</td>\n",
       "      <td>2143</td>\n",
       "      <td>503</td>\n",
       "      <td>271</td>\n",
       "      <td>593</td>\n",
       "      <td>1074</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Broome County</th>\n",
       "      <td>29065</td>\n",
       "      <td>32865</td>\n",
       "      <td>3244</td>\n",
       "      <td>1320</td>\n",
       "      <td>1144</td>\n",
       "      <td>931</td>\n",
       "      <td>379</td>\n",
       "      <td>474</td>\n",
       "      <td>802</td>\n",
       "      <td>2000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Cattaraugus County</th>\n",
       "      <td>6545</td>\n",
       "      <td>13240</td>\n",
       "      <td>1629</td>\n",
       "      <td>307</td>\n",
       "      <td>146</td>\n",
       "      <td>265</td>\n",
       "      <td>78</td>\n",
       "      <td>159</td>\n",
       "      <td>192</td>\n",
       "      <td>1060</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       DEM    REP   CON   GRE   WOR   IND  WEP  REF   SAM  \\\n",
       "county                                                                      \n",
       "Albany County        54909  38357  6725  3543  2489  1408  886  835  3007   \n",
       "Allegany County      12488      0   833   183    80   106   46   65   117   \n",
       "Bronx County        253712  20401  2407  2828  4198  2143  503  271   593   \n",
       "Broome County        29065  32865  3244  1320  1144   931  379  474   802   \n",
       "Cattaraugus County    6545  13240  1629   307   146   265   78  159   192   \n",
       "\n",
       "                     LBT  \n",
       "county                    \n",
       "Albany County       1867  \n",
       "Allegany County      963  \n",
       "Bronx County        1074  \n",
       "Broome County       2000  \n",
       "Cattaraugus County  1060  "
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "res_gov.columns = [x.split('_')[1] for x in res_gov.columns]\n",
    "res_gov.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['CON', 'DEM', 'GRE', 'IND', 'LBT', 'REF', 'REP', 'SAM', 'WEP', 'WOR']"
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "party_lst = list(parties)\n",
    "party_lst.sort()\n",
    "party_lst"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Order columns the same way in both data sets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CON</th>\n",
       "      <th>DEM</th>\n",
       "      <th>GRE</th>\n",
       "      <th>IND</th>\n",
       "      <th>LBT</th>\n",
       "      <th>REF</th>\n",
       "      <th>REP</th>\n",
       "      <th>SAM</th>\n",
       "      <th>WEP</th>\n",
       "      <th>WOR</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>County</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Albany County</th>\n",
       "      <td>6725</td>\n",
       "      <td>54909</td>\n",
       "      <td>3543</td>\n",
       "      <td>1408</td>\n",
       "      <td>1867</td>\n",
       "      <td>835</td>\n",
       "      <td>38357</td>\n",
       "      <td>3007</td>\n",
       "      <td>886</td>\n",
       "      <td>2489</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Allegany County</th>\n",
       "      <td>833</td>\n",
       "      <td>3254</td>\n",
       "      <td>183</td>\n",
       "      <td>106</td>\n",
       "      <td>963</td>\n",
       "      <td>65</td>\n",
       "      <td>9234</td>\n",
       "      <td>117</td>\n",
       "      <td>46</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  CON    DEM   GRE   IND   LBT  REF    REP   SAM  WEP   WOR\n",
       "County                                                                     \n",
       "Albany County    6725  54909  3543  1408  1867  835  38357  3007  886  2489\n",
       "Allegany County   833   3254   183   106   963   65   9234   117   46    80"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "o_gov = o_gov[party_lst]\n",
    "o_gov.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CON</th>\n",
       "      <th>DEM</th>\n",
       "      <th>GRE</th>\n",
       "      <th>IND</th>\n",
       "      <th>LBT</th>\n",
       "      <th>REF</th>\n",
       "      <th>REP</th>\n",
       "      <th>SAM</th>\n",
       "      <th>WEP</th>\n",
       "      <th>WOR</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>county</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Albany County</th>\n",
       "      <td>6725</td>\n",
       "      <td>54909</td>\n",
       "      <td>3543</td>\n",
       "      <td>1408</td>\n",
       "      <td>1867</td>\n",
       "      <td>835</td>\n",
       "      <td>38357</td>\n",
       "      <td>3007</td>\n",
       "      <td>886</td>\n",
       "      <td>2489</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Allegany County</th>\n",
       "      <td>833</td>\n",
       "      <td>12488</td>\n",
       "      <td>183</td>\n",
       "      <td>106</td>\n",
       "      <td>963</td>\n",
       "      <td>65</td>\n",
       "      <td>0</td>\n",
       "      <td>117</td>\n",
       "      <td>46</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  CON    DEM   GRE   IND   LBT  REF    REP   SAM  WEP   WOR\n",
       "county                                                                     \n",
       "Albany County    6725  54909  3543  1408  1867  835  38357  3007  886  2489\n",
       "Allegany County   833  12488   183   106   963   65      0   117   46    80"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "res_gov = res_gov[party_lst]\n",
    "res_gov.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Compare party by party and also total votes cast in each county between Open Elections and official data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Total votes cast for the race"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Accepting ratios between 0.95 and 1.05\n",
      "The outliers are: \n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "Lewis County    0.928737\n",
       "dtype: float64"
      ]
     },
     "execution_count": 100,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "percent_diff = res_gov.sum(axis=1)/o_gov.sum(axis=1)\n",
    "acceptable_percent_diff = .05\n",
    "lower_bound = 1 - acceptable_percent_diff\n",
    "upper_bound = 1 + acceptable_percent_diff\n",
    "print(\"Accepting ratios between {} and {}\".format(lower_bound, upper_bound))\n",
    "print(\"The outliers are: \")\n",
    "outliers = percent_diff[(percent_diff < lower_bound) | (percent_diff > upper_bound)]\n",
    "outliers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Lewis County   -645.0\n",
       "dtype: float64"
      ]
     },
     "execution_count": 101,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_votes_diff = res_gov.sum(axis=1) - o_gov.sum(axis=1)\n",
    "non_zero_votes_diff = total_votes_diff[total_votes_diff.index.isin(outliers.index)]\n",
    "non_zero_votes_diff"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CON</th>\n",
       "      <th>DEM</th>\n",
       "      <th>GRE</th>\n",
       "      <th>IND</th>\n",
       "      <th>LBT</th>\n",
       "      <th>REF</th>\n",
       "      <th>REP</th>\n",
       "      <th>SAM</th>\n",
       "      <th>WEP</th>\n",
       "      <th>WOR</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>County</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Lewis County</th>\n",
       "      <td>534</td>\n",
       "      <td>1718</td>\n",
       "      <td>102</td>\n",
       "      <td>71</td>\n",
       "      <td>412</td>\n",
       "      <td>74</td>\n",
       "      <td>5945</td>\n",
       "      <td>130</td>\n",
       "      <td>31</td>\n",
       "      <td>34</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              CON   DEM  GRE  IND  LBT  REF   REP  SAM  WEP  WOR\n",
       "County                                                          \n",
       "Lewis County  534  1718  102   71  412   74  5945  130   31   34"
      ]
     },
     "execution_count": 102,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "o_gov[o_gov.index.isin(outliers.index)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "county\n",
       "Lewis County    8406\n",
       "dtype: int64"
      ]
     },
     "execution_count": 103,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "res_gov[res_gov.index.isin(outliers.index)].sum(axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Save the file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>county</th>\n",
       "      <th>precinct</th>\n",
       "      <th>HOR_DEM</th>\n",
       "      <th>HOR_REP</th>\n",
       "      <th>HOR_CON</th>\n",
       "      <th>HOR_GRE</th>\n",
       "      <th>HOR_WOR</th>\n",
       "      <th>HOR_IND</th>\n",
       "      <th>HOR_WEP</th>\n",
       "      <th>HOR_REF</th>\n",
       "      <th>...</th>\n",
       "      <th>Gov_DEM</th>\n",
       "      <th>Gov_REP</th>\n",
       "      <th>Gov_CON</th>\n",
       "      <th>Gov_GRE</th>\n",
       "      <th>Gov_WOR</th>\n",
       "      <th>Gov_IND</th>\n",
       "      <th>Gov_WEP</th>\n",
       "      <th>Gov_REF</th>\n",
       "      <th>Gov_SAM</th>\n",
       "      <th>Gov_LBT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Albany County</td>\n",
       "      <td>0001 ALBANY W1 ED1</td>\n",
       "      <td>40</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>35</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Albany County</td>\n",
       "      <td>0002 ALBANY W1 ED2</td>\n",
       "      <td>182</td>\n",
       "      <td>20</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>21</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>148</td>\n",
       "      <td>32</td>\n",
       "      <td>11</td>\n",
       "      <td>15</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>9</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Albany County</td>\n",
       "      <td>0003 ALBANY W1 ED3</td>\n",
       "      <td>241</td>\n",
       "      <td>41</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>...</td>\n",
       "      <td>212</td>\n",
       "      <td>55</td>\n",
       "      <td>11</td>\n",
       "      <td>4</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Albany County</td>\n",
       "      <td>0004 ALBANY W1 ED4</td>\n",
       "      <td>245</td>\n",
       "      <td>18</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>18</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>220</td>\n",
       "      <td>30</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>12</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Albany County</td>\n",
       "      <td>0005 ALBANY W1 ED5</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 27 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          county            precinct  HOR_DEM  HOR_REP  HOR_CON  HOR_GRE  \\\n",
       "0  Albany County  0001 ALBANY W1 ED1       40        7        0        0   \n",
       "1  Albany County  0002 ALBANY W1 ED2      182       20        0        0   \n",
       "2  Albany County  0003 ALBANY W1 ED3      241       41        0        0   \n",
       "3  Albany County  0004 ALBANY W1 ED4      245       18        0        0   \n",
       "4  Albany County  0005 ALBANY W1 ED5        4        0        0        0   \n",
       "\n",
       "   HOR_WOR  HOR_IND  HOR_WEP  HOR_REF  ...  Gov_DEM  Gov_REP  Gov_CON  \\\n",
       "0        5        0        1        0  ...       35        9        1   \n",
       "1       21        0        6        3  ...      148       32       11   \n",
       "2       11        0        1        4  ...      212       55       11   \n",
       "3       18        0        5        3  ...      220       30        5   \n",
       "4        0        0        0        0  ...        2        0        0   \n",
       "\n",
       "   Gov_GRE  Gov_WOR  Gov_IND  Gov_WEP  Gov_REF  Gov_SAM  Gov_LBT  \n",
       "0        3        3        0        0        0        2        1  \n",
       "1       15       11        1        1        2        9        3  \n",
       "2        4       10        1        3        0        5        5  \n",
       "3        4       12        4        1        3        7        4  \n",
       "4        1        0        0        0        0        0        0  \n",
       "\n",
       "[5 rows x 27 columns]"
      ]
     },
     "execution_count": 105,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "export_df = piv.reset_index()\n",
    "export_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {},
   "outputs": [],
   "source": [
    "export_df.to_csv('data/precinct_election_results/NY_G18_pivoted.csv', index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python (ny-venv)",
   "language": "python",
   "name": "venv"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}