Open Elections + VTDs precinct matching.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Pennsylvania 2018 General Election Shapefile Assembly Script\n",
    "Joins precinct geometries with election results for the 2018 Pennsylvania General Election.\n",
    "\n",
    "Author: [Baxter Demers](https://github.com/baxterdemers)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from reference_data import (\n",
    "    geoid_to_county_name,\n",
    "    state_to_fips,\n",
    "    state_abbreviation_to_state_name,\n",
    "    state_fip_to_county_to_geoid,\n",
    ")\n",
    "\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import geopandas as gpd\n",
    "import re"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Import the datasets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "  STATEFP COUNTYFP   VTDST                    NAMELSAD VTDI LSAD CHNG_TYPE  \\\n",
       "0      42      099  000010       BLAIN Voting District    A   V2      None   \n",
       "1      42      099  000020  BLOOMFIELD Voting District    A   V2      None   \n",
       "\n",
       "  ORIG_NAME ORIG_CODE RELATE        NAME VINTAGE FUNCSTAT JUSTIFY  MTFCC  \\\n",
       "0      None      None   None       BLAIN      90        N    None  G5240   \n",
       "1      None      None   None  BLOOMFIELD      90        N    None  G5240   \n",
       "\n",
       "                                            geometry  \n",
       "0  POLYGON Z ((-77.51856 40.33821 0.00000, -77.51...  \n",
       "1  POLYGON Z ((-77.20030 40.41591 0.00000, -77.20...  "
      ],
      "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>STATEFP</th>\n      <th>COUNTYFP</th>\n      <th>VTDST</th>\n      <th>NAMELSAD</th>\n      <th>VTDI</th>\n      <th>LSAD</th>\n      <th>CHNG_TYPE</th>\n      <th>ORIG_NAME</th>\n      <th>ORIG_CODE</th>\n      <th>RELATE</th>\n      <th>NAME</th>\n      <th>VINTAGE</th>\n      <th>FUNCSTAT</th>\n      <th>JUSTIFY</th>\n      <th>MTFCC</th>\n      <th>geometry</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>42</td>\n      <td>099</td>\n      <td>000010</td>\n      <td>BLAIN Voting District</td>\n      <td>A</td>\n      <td>V2</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n      <td>BLAIN</td>\n      <td>90</td>\n      <td>N</td>\n      <td>None</td>\n      <td>G5240</td>\n      <td>POLYGON Z ((-77.51856 40.33821 0.00000, -77.51...</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>42</td>\n      <td>099</td>\n      <td>000020</td>\n      <td>BLOOMFIELD Voting District</td>\n      <td>A</td>\n      <td>V2</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n      <td>BLOOMFIELD</td>\n      <td>90</td>\n      <td>N</td>\n      <td>None</td>\n      <td>G5240</td>\n      <td>POLYGON Z ((-77.20030 40.41591 0.00000, -77.20...</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 2
    }
   ],
   "source": [
    "gdf_name = 'Census Partnership VTDs (Shapefile)'\n",
    "gdf = gpd.read_file('data/shapefiles/pa-precincts/census-partnership/compiled')\n",
    "gdf.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "  county          precinct       office  district       candidate party votes  \\\n",
       "0   York  Carroll Township  U.S. Senate       NaN  Bob CASEY, JR.   Dem   958   \n",
       "1   York  Carroll Township  U.S. Senate       NaN    Lou BARLETTA   Rep  1858   \n",
       "\n",
       "   absentee  election_day  \n",
       "0       NaN           NaN  \n",
       "1       NaN           NaN  "
      ],
      "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>absentee</th>\n      <th>election_day</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>York</td>\n      <td>Carroll Township</td>\n      <td>U.S. Senate</td>\n      <td>NaN</td>\n      <td>Bob CASEY, JR.</td>\n      <td>Dem</td>\n      <td>958</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>York</td>\n      <td>Carroll Township</td>\n      <td>U.S. Senate</td>\n      <td>NaN</td>\n      <td>Lou BARLETTA</td>\n      <td>Rep</td>\n      <td>1858</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 3
    }
   ],
   "source": [
    "df_name = 'Open Elections (Results)'\n",
    "df = pd.read_csv('data/election-results/open-elections/20181106__pa__general__precinct.csv', low_memory=False)\n",
    "df.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Precondition: County ID\n",
    "Make a column in gdf and df called `county_id` with a common naming scheme. County name or County fips code will both work."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Census Partnership VTDs (Shapefile) unmatched counties:  set()\nOpen Elections (Results) unmatched counties:  {'Westmoreland County', 'Delaware County', 'Armstrong County'}\n"
     ]
    }
   ],
   "source": [
    "pa_fips_code = state_to_fips['Pennsylvania']\n",
    "def get_county_name_pa(county_fips):\n",
    "    geoid = str(pa_fips_code).zfill(2) + str(county_fips).zfill(3)\n",
    "    assert(len(geoid) == 5)\n",
    "    return geoid_to_county_name[geoid]\n",
    "\n",
    "county_to_geoid_pa = state_fip_to_county_to_geoid[pa_fips_code]\n",
    "def get_geoid_pa(county_name):\n",
    "    return county_to_geoid_pa[county_name]\n",
    "\n",
    "gdf['county_id'] = gdf['COUNTYFP'].map(get_county_name_pa)\n",
    "df['county_id'] = df['county'].apply(lambda x: x.strip() + ' County') \n",
    "assert 'county_id' in gdf.columns and 'county_id' in df.columns\n",
    "county_set_x = set(gdf['county_id'].unique())\n",
    "county_set_y = set(df['county_id'].unique())\n",
    "print(gdf_name, \"unmatched counties: \", county_set_x.difference(county_set_y))\n",
    "print(df_name, \"unmatched counties: \", county_set_y.difference(county_set_x))\n",
    "assert county_set_x.intersection(county_set_y) != set()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "office\n",
       "Governor       [Butler County, Westmoreland County]\n",
       "U.S. House                      [Schuylkill County]\n",
       "U.S. Senate                     [Clearfield County]\n",
       "Name: county_id, dtype: object"
      ]
     },
     "metadata": {},
     "execution_count": 5
    }
   ],
   "source": [
    "df = df.loc[df.office.isin(['U.S. Senate', 'Governor', 'U.S. House'])]\n",
    "df.groupby('office').county_id.unique().map(lambda x: [county_id for county_id in df.county_id.unique() if county_id not in x])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Fill in missing election results\n",
    "\n",
    "As you can see in the cell above, Open Elections is missing results for the following offices in the specified counties.\n",
    "\n",
    "* Governor: [Butler, Westmoreland] \n",
    "* U.S. House: [Schuylkill] \n",
    "* U.S. Senate: [Clearfield]\n",
    "\n",
    "Accordingly, I parsed some or all election results from each county's website. For more details, check out `county-files/{county_id}/parsing-election-results/`. The following cell adds these parsed resutls into the results dataframe to be used later for matching."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Census Partnership VTDs (Shapefile) unmatched counties:  set()\nOpen Elections (Results) unmatched counties:  {'Westmoreland County', 'Delaware County', 'Armstrong County'}\n"
     ]
    }
   ],
   "source": [
    "butler_results_df = pd.read_csv('county-files/Butler County/parsing-election-results/butler_county_parsed.csv')\n",
    "westmoreland_results_df = pd.read_csv('county-files/Westmoreland County/parsing-election-results/westmoreland_county_parsed.csv')\n",
    "clearfield_results_df = pd.read_csv('county-files/Clearfield County/parsing_election_results/clearfield_county_parsed.csv')\n",
    "\n",
    "df = df[~df.county.isin({\"Butler\",\"Westmoreland\",\"Clearfield\"})]\n",
    "\n",
    "df.loc[df.county_id == 'Schuylkill County', 'precinct'] = df.loc[df.county_id == 'Schuylkill County', 'precinct'].map(lambda x: x[x.index(' ')+1:] if len(x.split())> 1 else x)\n",
    "schuylkill_us_house_results_df = pd.read_csv('county-files/Schuylkill County/parsing-election-results/schuylkill_county_parsed.csv')\n",
    "\n",
    "df = df.append([butler_results_df,westmoreland_results_df,clearfield_results_df,schuylkill_us_house_results_df])\n",
    "df = df.loc[df.office.isin(['U.S. Senate', 'Governor', 'U.S. House'])]\n",
    "df['county_id'] = df['county'].apply(lambda x: x.strip() + ' County') \n",
    "county_set_x = set(gdf['county_id'].unique())\n",
    "county_set_y = set(df['county_id'].unique())\n",
    "print(gdf_name, \"unmatched counties: \", county_set_x.difference(county_set_y))\n",
    "print(df_name, \"unmatched counties: \", county_set_y.difference(county_set_x))\n",
    "assert county_set_x.intersection(county_set_y) != set()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Precondition: Precinct Name\n",
    "Identify the column that should contains the precinct names to be matched by naming it `original_precinct_name`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# TODO: Pass the precondition described above which takes the form of an assert statement in this cell.\n",
    "gdf['original_precinct_name'] = gdf['NAME'].str.strip()\n",
    "df['original_precinct_name'] = df['precinct'].str.strip()\n",
    "assert 'original_precinct_name' in gdf.columns and 'original_precinct_name' in df.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Shapefile modifications\n",
    "\n",
    "The counties listed below in comments (e.g. `# Elk County`) were either missing from the original census VTD shapefile `('Westmoreland County', 'Armstrong County', 'Delaware County')` or needed to be replaced for accuracy reasons `('Elk County', 'Philadelphia County','Cumberland County','Dauphin County')`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "to_append = []\n",
    "\n",
    "# Elk County\n",
    "elk_gdf = gpd.read_file('county-files/Elk County/ElkCoPAdistricts')\n",
    "elk_gdf['county_id'] = 'Elk County'\n",
    "elk_gdf['original_precinct_name'] = elk_gdf['NAME'].str.strip()\n",
    "to_append.append(elk_gdf)\n",
    "\n",
    "# Philadelphia County\n",
    "philly_gdf = gpd.read_file('county-files/Philadelphia County/Divisions_2018General')\n",
    "philly_gdf['county_id'] = 'Philadelphia County'\n",
    "philly_gdf['original_precinct_name'] = philly_gdf['DIVISION_N'].str.strip()\n",
    "to_append.append(philly_gdf)\n",
    "\n",
    "\n",
    "# Cumberland County\n",
    "cumberland_gdf = gpd.read_file('county-files/Cumberland County/Voting_Precincts-shp')\n",
    "cumberland_gdf['county_id'] = 'Cumberland County'\n",
    "cumberland_gdf['original_precinct_name'] = cumberland_gdf['Precinct'].str.strip()\n",
    "to_append.append(cumberland_gdf)\n",
    "\n",
    "# Dauphin County\n",
    "def dauphin_precs(row):\n",
    "    nm = row['MCD_NAME']\n",
    "    if row['WARD']:\n",
    "        nm = nm + ' ' + row['WARD']\n",
    "    if row['PRECINCT']:\n",
    "        nm = nm + ' ' + row['PRECINCT']\n",
    "    return nm\n",
    "dauphin_gdf = gpd.read_file('county-files/Dauphin County/Voting_Districts-shp')\n",
    "dauphin_gdf['county_id'] = 'Dauphin County'\n",
    "dauphin_gdf['original_precinct_name'] = dauphin_gdf.apply(lambda row: dauphin_precs(row), axis=1)\n",
    "to_append.append(dauphin_gdf)\n",
    "\n",
    "migurski_pa_16 = gpd.read_file('data/shapefiles/pa-precincts/VTDs_Oct17')\n",
    "migurski_pa_16['county_id'] = migurski_pa_16['COUNTYNAME'].str.strip() + ' County'\n",
    "migurski_pa_16['original_precinct_name'] = migurski_pa_16['NAMELSAD']\n",
    "\n",
    "# Westmoreland County\n",
    "westmoreland_gdf = migurski_pa_16[migurski_pa_16.COUNTYNAME.isin(['Westmoreland'])].copy()\n",
    "to_append.append(westmoreland_gdf)\n",
    "\n",
    "# Armstrong County\n",
    "armstrong_gdf = migurski_pa_16[migurski_pa_16.COUNTYNAME.isin(['Armstrong'])].copy()\n",
    "to_append.append(armstrong_gdf)\n",
    "\n",
    "# Delaware County\n",
    "delaware_gdf = migurski_pa_16[migurski_pa_16.COUNTYNAME.isin(['Delaware'])].copy()\n",
    "to_append.append(delaware_gdf)\n",
    "\n",
    "county_id_to_be_replaced_lst = ['Elk County', 'Philadelphia County','Cumberland County','Dauphin County']\n",
    "gdf = gdf[~(gdf.county_id.isin(county_id_to_be_replaced_lst))]\n",
    "\n",
    "if not gdf.crs:\n",
    "    gdf.crs = \"epsg:4326\"\n",
    "gdf = gdf.append([cnty_gdf.to_crs(gdf.crs) for cnty_gdf in to_append], ignore_index=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Data Cleaning"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "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 = df.astype({col: \"str\"})\n",
    "            df[col] = df[col].map(lambda s: s.replace(\",\", \"\"))\n",
    "        df = df.astype({col: \"float\"})\n",
    "        df = df.astype({col: \"int\"})\n",
    "    return df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Remove duplicate rows and cast votes to int"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "gdf = gdf[~gdf.original_precinct_name.isin({'Voting Districts not defined'})]\n",
    "df = df[~df.votes.isnull()]\n",
    "df = df[~(df.original_precinct_name.isin(['Total', 'total', 'GOVERNMENT STUDY COMMISSIONER', 'SPRINGFIELD REFERENDUM']))] # Armstrong, Schuylkill, Carbon and Cumberland counties all have a 'total' precinct which has the countywide votes, but we don't want to include it\n",
    "df = df.drop_duplicates() # Delaware had a ton of duplicates for some reason\n",
    "df = remove_commas(df, ['votes'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Standardize Party Names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "candidate\n",
       "Bob Casey Jr.                           [Adams County]\n",
       "Brent Ottaway                           [Adams County]\n",
       "Dale R. Kerns Jr.                       [Adams County]\n",
       "John Joyce                              [Adams County]\n",
       "Ken V. Krawchuk / K. S. Smith           [Adams County]\n",
       "Lou Barletta                            [Adams County]\n",
       "Neale Gale                              [Adams County]\n",
       "Paul Glover / J. Bowser Bostick         [Adams County]\n",
       "Scott R. Wagner / Jeff Bartos           [Adams County]\n",
       "TOM WOLF                           [Cumberland County]\n",
       "Tom Wolf / John Fetterman               [Adams County]\n",
       "Name: county_id, dtype: object"
      ]
     },
     "metadata": {},
     "execution_count": 11
    }
   ],
   "source": [
    "skip = {'Blank Votes', 'No Vote', 'DEMOCRATIC', 'REPUBLICAN', 'GREEN', 'LIBERTARIAN','YES', 'NO', 'Cast Votes', 'Over Votes', 'Under Votes', \"Write-In\", \"Write In\", \"Write - In\", 'Write In ', 'Write In', 'Write-ins', 'Write-in Votes','WRITE-IN', 'Write In', 'Write In Votes', 'Write-In Votes', 'Write-Ins', 'Write-in'}\n",
    "df[df.party.isna() & (~df.candidate.str.contains('(W)', na=True, regex=False)) & (~df.candidate.isin(skip)) & (df.votes > 1)].groupby('candidate').county_id.unique()\n",
    "# candidates missing party affiliation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[(df.county_id == 'Cumberland County') & (df.candidate == 'TOM WOLF'), 'party'] = 'dem'\n",
    "candidate_to_party_adams_county = {\n",
    "    'Bob Casey Jr.':'dem', \n",
    "    'Lou Barletta':'rep', \n",
    "    'Neale Gale':'green', \n",
    "    'Dale R. Kerns Jr.':'lib',\n",
    "    'Tom Wolf / John Fetterman':'dem', \n",
    "    'Scott R. Wagner / Jeff Bartos':'rep',\n",
    "    'Paul Glover / J. Bowser Bostick':'green',\n",
    "    'Ken V. Krawchuk / K. S. Smith':'lib',\n",
    "    'Brent Ottaway':'dem', \n",
    "    'John Joyce':'rep',\n",
    "}\n",
    "df.loc[(df.county_id == 'Adams County'),'party'] = df.loc[(df.county_id == 'Adams County'),'candidate'].map(candidate_to_party_adams_county)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "array(['Democrat', 'Republican', 'Green', 'Libertarian', 'Independent'],\n",
       "      dtype=object)"
      ]
     },
     "metadata": {},
     "execution_count": 13
    }
   ],
   "source": [
    "def clean_party_name(raw_party_name):\n",
    "    if type(raw_party_name) != str:\n",
    "        return 'Independent'\n",
    "    elif raw_party_name.lower().startswith('dem/'):\n",
    "        return 'Independent'\n",
    "    elif raw_party_name.lower().startswith('dem'):\n",
    "        return 'Democrat'\n",
    "    elif raw_party_name.lower().startswith('rep'):\n",
    "        return 'Republican'\n",
    "    elif raw_party_name.lower().startswith('gr'):\n",
    "        return 'Green'\n",
    "    elif raw_party_name.lower().startswith('lib') or raw_party_name.lower().startswith('ln'):\n",
    "        return 'Libertarian'\n",
    "    else:\n",
    "        return 'Independent'\n",
    "df['party'] = df['party'].map(clean_party_name)\n",
    "df['party'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Fix ambiguous precinct names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "addison_borough_dict = {\n",
    "    'U.S. Senate':{\n",
    "        'Democrat':26,\n",
    "        'Republican':49,\n",
    "        'Green':1,\n",
    "        'Libertarian':1,\n",
    "    },\n",
    "    'Governor':{\n",
    "        'Democrat':22,\n",
    "        'Republican':52,\n",
    "        'Green':0,\n",
    "        'Libertarian':3,\n",
    "    },\n",
    "    'U.S. House':{\n",
    "        'Democrat':18,\n",
    "        'Republican':57,\n",
    "        'Green':0,\n",
    "        'Libertarian':0,\n",
    "    },\n",
    "}\n",
    "\n",
    "for office in ['U.S. Senate', 'Governor', 'U.S. House']:\n",
    "    party_to_votes_dict = addison_borough_dict[office]\n",
    "    for party,votes in party_to_votes_dict.items():\n",
    "        mask = ((df['party']==party)\n",
    "                    & (df['office']==office)\n",
    "                    & (df['votes']==votes)\n",
    "                    & (df['county_id']=='Somerset County')\n",
    "                    & (df['original_precinct_name']=='ADDISON'))\n",
    "        df.loc[mask, 'original_precinct_name'] = 'Addison Borough'\n",
    "        \n",
    "addison_township_dict = {\n",
    "    'U.S. Senate':{\n",
    "        'Democrat':110,\n",
    "        'Republican':233,\n",
    "        'Green':2,\n",
    "        'Libertarian':5,\n",
    "    },\n",
    "    'Governor':{\n",
    "        'Democrat':117,\n",
    "        'Republican':230,\n",
    "        'Green':2,\n",
    "        'Libertarian':4,\n",
    "    },\n",
    "    'U.S. House':{\n",
    "        'Democrat':87,\n",
    "        'Republican':265,\n",
    "        'Green':0,\n",
    "        'Libertarian':0,\n",
    "    },\n",
    "}\n",
    "\n",
    "for office in ['U.S. Senate', 'Governor', 'U.S. House']:\n",
    "    party_to_votes_dict = addison_township_dict[office]\n",
    "    for party,votes in party_to_votes_dict.items():\n",
    "        mask = ((df['party']==party)\n",
    "                    & (df['office']==office)\n",
    "                    & (df['votes']==votes)\n",
    "                    & (df['county_id']=='Somerset County')\n",
    "                    & (df['original_precinct_name']=='ADDISON'))\n",
    "        df.loc[mask, 'original_precinct_name'] = 'Addison Township'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "array(['Addison Township', 'Addison Borough'], dtype=object)"
      ]
     },
     "metadata": {},
     "execution_count": 15
    }
   ],
   "source": [
    "df[df.precinct == 'ADDISON'].original_precinct_name.unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Pivoting the election results\n",
    "\n",
    "Now that the data has been cleaned and standardized I will pivot the election results so they take their final format. Specifically, each precinct will get exactly one row. Columns constitute votes for a `(party, race)` tuple e.g. votes for the Democrat running for Congress. Then rename each column to conform to the 10 character maximum imposed by the shapefile format."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "['votes Democrat Governor', 'votes Democrat U.S. House', 'votes Democrat U.S. Senate', 'votes Green Governor', 'votes Green U.S. House', 'votes Green U.S. Senate', 'votes Independent Governor', 'votes Independent U.S. House', 'votes Independent U.S. Senate', 'votes Libertarian Governor', 'votes Libertarian U.S. House', 'votes Libertarian U.S. Senate', 'votes Republican Governor', 'votes Republican U.S. House', 'votes Republican U.S. Senate']\n"
     ]
    }
   ],
   "source": [
    "prec_row_df = pd.pivot_table(df, index = ['county_id', 'original_precinct_name'], columns = ['party','office'], values = ['votes'], aggfunc = np.sum)\n",
    "prec_row_df.columns = prec_row_df.columns.to_series().str.join(' ')\n",
    "print(list(prec_row_df.columns))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "{'votes Democrat Governor': 'G18DemGov',\n",
       " 'votes Democrat U.S. House': 'G18DemHOR',\n",
       " 'votes Democrat U.S. Senate': 'G18DemSen',\n",
       " 'votes Green Governor': 'G18GreGov',\n",
       " 'votes Green U.S. House': 'G18GreHOR',\n",
       " 'votes Green U.S. Senate': 'G18GreSen',\n",
       " 'votes Independent Governor': 'G18IndGov',\n",
       " 'votes Independent U.S. House': 'G18IndHOR',\n",
       " 'votes Independent U.S. Senate': 'G18IndSen',\n",
       " 'votes Libertarian Governor': 'G18LibGov',\n",
       " 'votes Libertarian U.S. House': 'G18LibHOR',\n",
       " 'votes Libertarian U.S. Senate': 'G18LibSen',\n",
       " 'votes Republican Governor': 'G18RepGov',\n",
       " 'votes Republican U.S. House': 'G18RepHOR',\n",
       " 'votes Republican U.S. Senate': 'G18RepSen'}"
      ]
     },
     "metadata": {},
     "execution_count": 17
    }
   ],
   "source": [
    "def rename(nm):\n",
    "    office_to_office_short = {\n",
    "        'Governor':'Gov',\n",
    "        'U.S. House':'HOR',\n",
    "        'U.S. Senate':'Sen'\n",
    "    }\n",
    "    votes,party,office = nm.split(maxsplit=2)\n",
    "    party_short = party[:3]\n",
    "    office_short = office_to_office_short[office]\n",
    "    ret = 'G18'+party_short+office_short\n",
    "    assert len(ret) <= 10\n",
    "    return ret\n",
    "col_to_short_col = {nm:rename(nm) for nm in list(prec_row_df.columns)}\n",
    "col_to_short_col"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "      county_id original_precinct_name  G18DemGov  G18DemHOR  G18DemSen  \\\n",
       "0  Adams County            Abbottstown      120.0      108.0      120.0   \n",
       "1  Adams County           Arendtsville      160.0      132.0      151.0   \n",
       "2  Adams County           Bendersville       76.0       66.0       74.0   \n",
       "3  Adams County                Berwick      318.0      252.0      289.0   \n",
       "4  Adams County            Biglerville      168.0      133.0      152.0   \n",
       "\n",
       "   G18GreGov  G18GreHOR  G18GreSen  G18IndGov  G18IndHOR  G18IndSen  \\\n",
       "0        2.0        0.0        2.0        0.0        0.0        0.0   \n",
       "1        2.0        0.0        3.0        0.0        1.0        0.0   \n",
       "2        2.0        0.0        2.0        0.0        0.0        0.0   \n",
       "3        5.0        0.0        5.0        0.0        0.0        0.0   \n",
       "4        2.0        0.0        7.0        1.0        0.0        1.0   \n",
       "\n",
       "   G18LibGov  G18LibHOR  G18LibSen  G18RepGov  G18RepHOR  G18RepSen  \n",
       "0        2.0        0.0        5.0      185.0      201.0      183.0  \n",
       "1        4.0        0.0        6.0      172.0      204.0      178.0  \n",
       "2        3.0        0.0        1.0       98.0      111.0      103.0  \n",
       "3        9.0        0.0       14.0      554.0      631.0      575.0  \n",
       "4        5.0        0.0        3.0      215.0      261.0      231.0  "
      ],
      "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_id</th>\n      <th>original_precinct_name</th>\n      <th>G18DemGov</th>\n      <th>G18DemHOR</th>\n      <th>G18DemSen</th>\n      <th>G18GreGov</th>\n      <th>G18GreHOR</th>\n      <th>G18GreSen</th>\n      <th>G18IndGov</th>\n      <th>G18IndHOR</th>\n      <th>G18IndSen</th>\n      <th>G18LibGov</th>\n      <th>G18LibHOR</th>\n      <th>G18LibSen</th>\n      <th>G18RepGov</th>\n      <th>G18RepHOR</th>\n      <th>G18RepSen</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>Adams County</td>\n      <td>Abbottstown</td>\n      <td>120.0</td>\n      <td>108.0</td>\n      <td>120.0</td>\n      <td>2.0</td>\n      <td>0.0</td>\n      <td>2.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>2.0</td>\n      <td>0.0</td>\n      <td>5.0</td>\n      <td>185.0</td>\n      <td>201.0</td>\n      <td>183.0</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>Adams County</td>\n      <td>Arendtsville</td>\n      <td>160.0</td>\n      <td>132.0</td>\n      <td>151.0</td>\n      <td>2.0</td>\n      <td>0.0</td>\n      <td>3.0</td>\n      <td>0.0</td>\n      <td>1.0</td>\n      <td>0.0</td>\n      <td>4.0</td>\n      <td>0.0</td>\n      <td>6.0</td>\n      <td>172.0</td>\n      <td>204.0</td>\n      <td>178.0</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>Adams County</td>\n      <td>Bendersville</td>\n      <td>76.0</td>\n      <td>66.0</td>\n      <td>74.0</td>\n      <td>2.0</td>\n      <td>0.0</td>\n      <td>2.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>3.0</td>\n      <td>0.0</td>\n      <td>1.0</td>\n      <td>98.0</td>\n      <td>111.0</td>\n      <td>103.0</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>Adams County</td>\n      <td>Berwick</td>\n      <td>318.0</td>\n      <td>252.0</td>\n      <td>289.0</td>\n      <td>5.0</td>\n      <td>0.0</td>\n      <td>5.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>9.0</td>\n      <td>0.0</td>\n      <td>14.0</td>\n      <td>554.0</td>\n      <td>631.0</td>\n      <td>575.0</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>Adams County</td>\n      <td>Biglerville</td>\n      <td>168.0</td>\n      <td>133.0</td>\n      <td>152.0</td>\n      <td>2.0</td>\n      <td>0.0</td>\n      <td>7.0</td>\n      <td>1.0</td>\n      <td>0.0</td>\n      <td>1.0</td>\n      <td>5.0</td>\n      <td>0.0</td>\n      <td>3.0</td>\n      <td>215.0</td>\n      <td>261.0</td>\n      <td>231.0</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 18
    }
   ],
   "source": [
    "#rename columns\n",
    "prec_row_df = prec_row_df.rename(columns=col_to_short_col)\n",
    "prec_row_df = prec_row_df.fillna(0)\n",
    "prec_row_df.reset_index(inplace=True)  \n",
    "prec_row_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = prec_row_df.reset_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### General Modifications\n",
    "Its benificial to apply some modifications uniformly to all precincts. For example, its good practice to make everything lower case. This modification and other are made in `edit_precinct_name` - read its specification to learn more about how to use it to make modifications."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "county_id_to_precinct_modification_dictionary_y = {\n",
    "    'Adams County':\n",
    "        {'gettysburg 1': 'gettysburg 1 1',\n",
    "         'gettysburg 3': 'gettysburg 3 1',\n",
    "         'huntington': 'huntington 1'},\n",
    "    'Allegheny County':\n",
    "        {'blawnox': 'blawnox 1',\n",
    "         'chalfant 1': 'chalfant',\n",
    "         'frazer': 'frazer 1',\n",
    "         'heidelberg': 'heidelberg 1',\n",
    "         'leetsdale': 'leetsdale 1',\n",
    "         'mcdonald': 'mcdonald 5',\n",
    "         'pennsbury vill': 'pennsbury village',\n",
    "         'rosslyn farm': 'rosslyn farms',\n",
    "         'thornburg': 'thornburg 1',\n",
    "         'trafford': 'trafford 1'},\n",
    "    'Berks County':\n",
    "        {'bally': 'bally 1',\n",
    "         '(4 cong)':'1 (4 cong)', \n",
    "         '(9 cong)':'1 (9 cong)',\n",
    "         'exeter 5 1 (6 cong)':'exeter 5 (6 cong)',\n",
    "         'exeter 5 2 (9 cong)':'exeter 5 (9 cong)',\n",
    "         'bechtelsville': 'bechtelsville 1',\n",
    "         'bernville': 'bernville 1',\n",
    "         'centerport': 'centerport 1',\n",
    "         'heidelberg': 'heidelberg 1',\n",
    "         'jefferson': 'jefferson 1',\n",
    "         'leesport': 'leesport 1',\n",
    "         'lenhartsville': 'lenhartsville 1',\n",
    "         'lyons': 'lyons 1',\n",
    "         'marion': 'marion 1',\n",
    "         'mohnton': 'mohnton 1',\n",
    "         'mount penn': 'mount penn 1',\n",
    "         'north heidelberg': 'north heidelberg 1',\n",
    "         'ontelaunee': 'ontelaunee 1',\n",
    "         'pike': 'pike 1',\n",
    "         'reading 5 1': 'reading 5',\n",
    "         'reading 7 1': 'reading 7',\n",
    "         'reading 8 1': 'reading 8',\n",
    "         'richmond': 'richmond 1',\n",
    "         'robesonia': 'robesonia 1',\n",
    "         'shoemakersville': 'shoemakersville 1',\n",
    "         'upper bern': 'upper bern 1',\n",
    "         'upper tulpehocken': 'upper tulpehocken 1',\n",
    "         'wernersville': 'wernersville 1',\n",
    "         'windsor': 'windsor 1',\n",
    "         'womelsdorf': 'womelsdorf 1'},\n",
    "    'Butler County':\n",
    "        {'butler 6n': 'butler 6 north',\n",
    "         'butler 6s': 'butler 6 south',\n",
    "         'fields':'seven fields',\n",
    "         'cherry vallery': 'cherry valley',\n",
    "         'jefferson 1': 'jefferson i',\n",
    "         'jefferson 2': 'jefferson ii'},\n",
    "    'Cameron County':\n",
    "        {'east emporium': 'emporium east',\n",
    "         'east shippen': 'shippen east',\n",
    "         'middle emporium': 'emporium middle',\n",
    "         'west emporium': 'emporium west',\n",
    "         'west shippen': 'shippen west'},\n",
    "    'Carbon County':\n",
    "        {'banks': 'banks 4',\n",
    "         'ii franklin independent': 'franklin franklin ind',\n",
    "         'lansford east': 'lansford 1',\n",
    "         'mahoning packerton jamestown': 'mahoning packer jamestown',\n",
    "         'palmerton east': 'palmerton eastern',\n",
    "         'palmerton west': 'palmerton western',\n",
    "         'penn forest northeast': 'penn forest north east',\n",
    "         'penn forest northwest': 'penn forest north west',\n",
    "         'penn forest southwest': 'penn forest south west',\n",
    "         'fast penn south' : 'east penn south',\n",
    "         'penn forest 2 northeast' : 'penn forest north east',\n",
    "         'franklin twp�harrity' : 'franklin harrity',\n",
    "         'i towamensing south 1' : 'towamensing south 1',},\n",
    "    'Chester County':\n",
    "        {'east downingtown east': 'downingtown east east',\n",
    "         'east downingtown west':'downingtown east west',\n",
    "         'north coventry 1': 'north coventry west 1',\n",
    "         'north coventry 2': 'north coventry west 2',\n",
    "         'london grove ch': 'london grove chatham',\n",
    "         'west downingtown north': 'downingtown west north',\n",
    "         'west downingtown south': 'downingtown west south',\n",
    "         'west goshen m 1': 'west goshen middle 1',\n",
    "         'west goshen m 2': 'west goshen middle 2',\n",
    "         'east fallowfield': 'east fallowfield west',\n",
    "         'east marlborough': 'east marlborough west',\n",
    "         'west brandywine': 'west brandywine west'},\n",
    "    'Clarion County': {'toby': 'toby east'},\n",
    "    'Clearfield County':\n",
    "        {'dubois city 1': 'dubois 1',\n",
    "         'dubois city 2': 'dubois 2',\n",
    "         'dubois city 3': 'dubois 3',\n",
    "         'dubois city 4': 'dubois 4',\n",
    "         'dubois city 5': 'dubois 5',\n",
    "         'lawrence golden': 'lawrence golden rod hillsdale',\n",
    "         'lawrence hyde': 'lawrence hyde city',\n",
    "         'sandy falls creek 2': 'falls creek',\n",
    "         'sandy sabula pct': 'sandy sabula'},\n",
    "    'Clinton County':{'pine creek i': 'pine creek 1', 'pine creek ii': 'pine creek 2'},\n",
    "    'Columbia County':\n",
    "        {'briarcreek': 'briar creek',\n",
    "         'briarcreek west': 'briar creek west',\n",
    "         'briarcreek ne': 'briar creek northeast',\n",
    "         'fishingcreek': 'fishing creek',\n",
    "         'roaringcreek': 'roaring creek'},\n",
    "    'Crawford County':\n",
    "        {'vernon i': 'vernon 1',\n",
    "         'vernon ii': 'vernon 2',\n",
    "         'vernon iii': 'vernon 3',\n",
    "         'west mead i': 'west mead 1',\n",
    "         'west mead ii': 'west mead 2'},\n",
    "    'Dauphin County':\n",
    "        {'city 1 1': 'harrisburg 1 1',\n",
    "         'city 1 2': 'harrisburg 1 2',\n",
    "         'city 1 3': 'harrisburg 1 3',\n",
    "         'city 10 1': 'harrisburg 10 1',\n",
    "         'city 10 2': 'harrisburg 10 2',\n",
    "         'city 10 3': 'harrisburg 10 3',\n",
    "         'city 10 4': 'harrisburg 10 4',\n",
    "         'city 11': 'harrisburg 11',\n",
    "         'city 12': 'harrisburg 12',\n",
    "         'city 13 1': 'harrisburg 13 1',\n",
    "         'city 13 2': 'harrisburg 13 2',\n",
    "         'city 13 3': 'harrisburg 13 3',\n",
    "         'city 14': 'harrisburg 14',\n",
    "         'city 15': 'harrisburg 15',\n",
    "         'city 2 1': 'harrisburg 2 1',\n",
    "         'city 2 2': 'harrisburg 2 2',\n",
    "         'city 3': 'harrisburg 3',\n",
    "         'city 4': 'harrisburg 4',\n",
    "         'city 5': 'harrisburg 5',\n",
    "         'city 6': 'harrisburg 6',\n",
    "         'city 7 1': 'harrisburg 7 1',\n",
    "         'city 7 2': 'harrisburg 7 2',\n",
    "         'city 8': 'harrisburg 8',\n",
    "         'city 9 1': 'harrisburg 9 1',\n",
    "         'city 9 2': 'harrisburg 9 2',\n",
    "         'city 9 3': 'harrisburg 9 3',\n",
    "         'city 9 4': 'harrisburg 9 4',\n",
    "         'city 9 5': 'harrisburg 9 5'},\n",
    "    'Elk County':{\n",
    "        'benezette':'benezett',\n",
    "        'highland': 'highland highland',\n",
    "        'north horton':'horton n',\n",
    "        'south horton':'horton s',\n",
    "         'james city': 'highland james city',\n",
    "         'lamont': 'jones lamont',\n",
    "         'north ridgway': 'ridgway north',\n",
    "         'south ridgway': 'ridgway south',\n",
    "         'wilcox': 'jones wilcox'},\n",
    "    'Erie County':\n",
    "        {'erie 2 1': 'erie 2 1 3',\n",
    "         'erie 2 4': 'erie 2 4 9',\n",
    "         'fairview 1 d': 'fairview 1',\n",
    "         'fairview 2 d': 'fairview 2',\n",
    "         'fairview 3 d': 'fairview 3',\n",
    "         'fairview 4 d': 'fairview 4',\n",
    "         'fairview 5 d': 'fairview 5',\n",
    "         'harborcreek 1s': 'harborcreek 1',\n",
    "         'harborcreek 2n': 'harborcreek 2',\n",
    "         'harborcreek 3r': 'harborcreek 3',\n",
    "         'harborcreek 4t': 'harborcreek 4',\n",
    "         'harborcreek 5t': 'harborcreek 5',\n",
    "         'harborcreek 6t': 'harborcreek 6',\n",
    "         'harborcreek 7t': 'harborcreek 7',\n",
    "         'union city 1 war': 'union city 1',\n",
    "         'union city 2 war': 'union city 2',\n",
    "         'union townsip': 'union',\n",
    "         'wesleyville 1':'wesleyville east',\n",
    "         'wesleyville 2':'wesleyville west',\n",
    "         'waterford 1':'waterford east',\n",
    "         'waterford 2':'waterford west',},\n",
    "    'Forest County': {'kinglsey': 'kingsley'},\n",
    "    'Franklin County':{'fannett sulphur spr': 'fannett sulphur springs', 'west end shippensburg': 'shippensburg'},\n",
    "    'Greene County':\n",
    "        {'cumberlandx1': 'cumberland 1',\n",
    "         'cumberlandx2': 'cumberland 2',\n",
    "         'cumberlandx4': 'cumberland 4',\n",
    "         'cumberlandxnemacolin': 'cumberland nemacolin',\n",
    "         'dunkardxbobtown': 'dunkard bobtown',\n",
    "         'dunkardxdilliner': 'dunkard dilliner',\n",
    "         'dunkardxlower': 'dunkard lower',\n",
    "         'dunkardxupper': 'dunkard upper',\n",
    "         'franklinxeast': 'franklin east',\n",
    "         'franklinxnorth': 'franklin north',\n",
    "         'franklinxsouth': 'franklin south',\n",
    "         'franklinxwest': 'franklin west',\n",
    "         'jeffersonx1': 'jefferson 1',\n",
    "         'jeffersonx2': 'jefferson 2',\n",
    "         'jeffersonx3': 'jefferson 3',\n",
    "         'jeffersonx4': 'jefferson 4',\n",
    "         'monongahelax1': 'monongahela 1',\n",
    "         'monongahelax2': 'monongahela 2',\n",
    "         'morganxchart tgrdn': 'morgan chart tgrdn',\n",
    "         'morganxlippencott': 'morgan lippencott',\n",
    "         'morganxmather': 'morgan mather',\n",
    "         'waynesburgw1': 'waynesburg 1',\n",
    "         'waynesburgw2': 'waynesburg 2',\n",
    "         'waynesburgw3': 'waynesburg 3',\n",
    "         'waynexeast': 'wayne east',\n",
    "         'waynexwest': 'wayne west'},\n",
    "    'Jefferson County':\n",
    "        {'brockway': 'brockway 1',\n",
    "         'oliver': 'oliver 1',\n",
    "         'perry': 'perry 1',\n",
    "         'reynoldsville': 'reynoldsville 1',\n",
    "         'washington': 'washington east',\n",
    "         'winslow': 'winslow 1',\n",
    "         'young': 'young north 1'},\n",
    "    'Juniata County':\n",
    "        {'tuscarora tusc': 'tuscarora'},\n",
    "    'Lackawanna County':\n",
    "        {'carbondale ne': 'carbondale northeast',\n",
    "         'carbondale nw': 'carbondale northwest',\n",
    "         'carbondale so': 'carbondale south',\n",
    "         'scranton 7 1': 'scranton 7',\n",
    "         'springbrook': 'spring brook'},\n",
    "    'Lawrence County':\n",
    "        {'new castle 6 1': 'new castle 6',\n",
    "         'new castle 7 1': 'new castle 7',\n",
    "         'new castle 8 1': 'new castle 8'},\n",
    "    'Lebanon County':\n",
    "        {'bethel fredericksburg': 'bethel fred',\n",
    "         'heidelberg kleinfeltersvl': 'heidelberg klein',\n",
    "         'heidelberg schaefferstown': 'heidelberg sch',\n",
    "         'south londonderry campbelltwn': 'south londonderry campbelltown',\n",
    "         'union green point': 'union green pt'},\n",
    "    'Lehigh County':\n",
    "        {'allentown 1 1': 'allentown 1',\n",
    "         'allentown 2 1': 'allentown 2',\n",
    "         'allentown 3 1': 'allentown 3',\n",
    "         'allentown 4 1': 'allentown 4',\n",
    "         'allentown 5 1': 'allentown 5',\n",
    "         'allentown 9 1': 'allentown 9',\n",
    "         'bethlehem 10 1': 'bethlehem 10',\n",
    "         'bethlehem 11 1': 'bethlehem 11'},\n",
    "    'McKean County':\n",
    "        {'bradford city 1': 'bradford 1',\n",
    "         'bradford city 2': 'bradford 2',\n",
    "         'bradford city 3 1': 'bradford 3 1',\n",
    "         'bradford city 3 2': 'bradford 3 2',\n",
    "         'bradford city 4': 'bradford 4',\n",
    "         'bradford city 5': 'bradford 5',\n",
    "         'bradford city 6 1': 'bradford 6 1',\n",
    "         'bradford city 6 2': 'bradford 6 2',\n",
    "         'bradford twnship 3': 'bradford 3',\n",
    "         'foster 4 distr': 'foster 4'},\n",
    "    'Mercer County':\n",
    "        {'hermitage n1': 'hermitage nw 1',\n",
    "         'hermitage n2': 'hermitage nw 2',\n",
    "         'hermitage n3': 'hermitage nw 3',\n",
    "         'hermitage n4': 'hermitage nw 4',\n",
    "         'hermitage s1': 'hermitage sw 1',\n",
    "         'hermitage s2': 'hermitage sw 2',\n",
    "         'hermitage s3': 'hermitage sw 3'},\n",
    "    'Monroe County':\n",
    "        {'hamilton northern': 'hamilton north',\n",
    "         'hamilton southern': 'hamilton south',\n",
    "         'smithfield 4':'smithfield 1',\n",
    "         'middle smithfield eastern': 'middle smithfield east',\n",
    "         'middle smithfield western': 'middle smithfield west',\n",
    "         'tobyhanna eastern': 'tobyhanna east',\n",
    "         'tobyhanna western': 'tobyhanna west'},\n",
    "    'Montgomery County':\n",
    "        {'northwales 1': 'north wales 1',\n",
    "         'northwales 2': 'north wales 2',\n",
    "         'northwales 3': 'north wales 3',\n",
    "         'plymouth 2 3': 'plymouth 2 3a',\n",
    "         'plymouth 2 3 7': 'plymouth 2 3b',\n",
    "         'towamencin 1 4': 'towamencin 2 1',\n",
    "         'upper dublin 3 1': 'upper dublin 3 1a',\n",
    "         'upper merion bel 1': 'upper merion belmont 1',\n",
    "         'upper merion bel 2': 'upper merion belmont 2',\n",
    "         'upper merion bel 3': 'upper merion belmont 3',\n",
    "         'upper merion bel 4': 'upper merion belmont 4',\n",
    "         'upper merion bel 5': 'upper merion belmont 5',\n",
    "         'upper merion cand 1': 'upper merion candlebrook 1',\n",
    "         'upper merion cand 2': 'upper merion candlebrook 2',\n",
    "         'upper providence m c': 'upper providence mont clare',\n",
    "         'west conshocken': 'west conshohocken'},\n",
    "    'Montour County':\n",
    "    {'mahoning i': 'mahoning 1',\n",
    "     'mahoning ii': 'mahoning 2',\n",
    "     'washingtonville bor': 'washingtonville'},\n",
    "    'Northumberland County':\n",
    "        {'chillisquaque': 'east chillisquaque',\n",
    "         'shamokin city 1': 'shamokin 1',\n",
    "         'shamokin city 2': 'shamokin 2 1',\n",
    "         'shamokin city 3': 'shamokin 3',\n",
    "         'shamokin city 4': 'shamokin 4 1',\n",
    "         'shamokin city 5': 'shamokin 5 1',\n",
    "         'shamokin city 6': 'shamokin 6',\n",
    "         'shamokin city 7': 'shamokin 7',\n",
    "         'shamokin city 8': 'shamokin 8',\n",
    "         'sunbury city 1': 'sunbury 1',\n",
    "         'sunbury city 2': 'sunbury 2',\n",
    "         'sunbury city 3': 'sunbury 3',\n",
    "         'sunbury city 4': 'sunbury 4',\n",
    "         'sunbury city 5': 'sunbury 5',\n",
    "         'sunbury city 6': 'sunbury 6',\n",
    "         'upper mahanoy (12 district)': 'upper mahanoy'},\n",
    "    'Potter County':\n",
    "        {'eulalia': 'eulalia eulalia',\n",
    "         'north sharon': 'sharon north',\n",
    "         'south sharon': 'sharon south'},\n",
    "    'Schuylkill County': {'norwegian marlin': 'norwegian mar lin', 'rush elixir': 'rush elixer'},\n",
    "    'Snyder County':\n",
    "        {'monroe no1': 'monroe 1',\n",
    "         'monroe no2': 'monroe 2',\n",
    "         'penn no 1': 'penn 1',\n",
    "         'penn no 2': 'penn 2',\n",
    "         'selinsgrove no1': 'selinsgrove 1',\n",
    "         'selinsgrove no2': 'selinsgrove 2',\n",
    "         'selinsgrove no3': 'selinsgrove 3'},\n",
    "    'Somerset County':\n",
    "        {'brothers valley': 'brothersvalley',\n",
    "         'conemaughx1': 'conemaugh 1',\n",
    "         'conemaughx2': 'conemaugh 2',\n",
    "         'conemaughx3': 'conemaugh 3',\n",
    "         'conemaughx4': 'conemaugh 4',\n",
    "         'conemaughx5': 'conemaugh 5',\n",
    "         'jennerx1': 'jenner 1',\n",
    "         'jennerx2': 'jenner 2',\n",
    "         'jennerx3': 'jenner 3',\n",
    "         'paintx1': 'paint 1',\n",
    "         'paintx2': 'paint 2',\n",
    "         'paintx3': 'paint 3',\n",
    "         'shadex1': 'shade 1',\n",
    "         'shadex2': 'shade 2',\n",
    "         'shadex3': 'shade 3',\n",
    "         'somersetx1': 'somerset 1',\n",
    "         'somersetx2': 'somerset 2',\n",
    "         'somersetx3': 'somerset 3',\n",
    "         'somersetx4': 'somerset 4',\n",
    "         'somersetxeast': 'somerset east',\n",
    "         'somersetxnorth west': 'somerset north west',\n",
    "         'somersetxsouth west': 'somerset south west',\n",
    "         'windberx1': 'windber 1',\n",
    "         'windberx2': 'windber 2',\n",
    "         'windberx3': 'windber 3',\n",
    "         'windberx4': 'windber 4'},\n",
    "    'Sullivan County':{'bernice': 'cherry bernice', 'lopez': 'colley lopez'},\n",
    "    'Susquehanna County':\n",
    "        {'forest city': 'forest city 1',\n",
    "         'montrose 1w': 'montrose 1',\n",
    "         'montrose 2w': 'montrose 2',\n",
    "         'susquehanna': 'susquehanna depot',\n",
    "         'union dale': 'uniondale'},\n",
    "    'Venango County':\n",
    "        {'oilcreek': 'oil creek', 'sugarcreek 5': 'sugarcreek 4'},\n",
    "    'Warren County':\n",
    "        {'warren city central': 'warren central',\n",
    "         'warren city east': 'warren east',\n",
    "         'warren city north': 'warren north',\n",
    "         'warren city south': 'warren south',\n",
    "         'warren city south east': 'warren south east',\n",
    "         'warren city west': 'warren west'},\n",
    "    'Washington County':\n",
    "        {'bentleyville': 'bentleyville 1',\n",
    "         'east bethlehem 1w': 'east bethlehem 1',\n",
    "         'east bethlehem 2w': 'east bethlehem 2',\n",
    "         'east bethlehem 3w': 'east bethlehem 3',\n",
    "         'east bethlehem 4w': 'east bethlehem 4',\n",
    "         'east finley': 'east finley 1',\n",
    "         'east washington': 'east washington 1',\n",
    "         'mc donald': 'mcdonald',\n",
    "         'monongahela 1w': 'monongahela 1',\n",
    "         'north bethlehem': 'north bethlehem 1',\n",
    "         'north charleroi': 'north charleroi 1',\n",
    "         'washington 4 west': 'washington 4',\n",
    "         'petersxd 3':'peters 3'},\n",
    "    'Wayne County':{'so canaan': 'south canaan'},\n",
    "    'York County':\n",
    "        {'york 1': 'york city 1',\n",
    "         'york 11': 'york city 11',\n",
    "         'york 12 1': 'york city 12 1',\n",
    "         'york 12 2': 'york city 12 2',\n",
    "         'york 12 3': 'york city 12 3',\n",
    "         'york 12 4': 'york city 12 4',\n",
    "         'york 13': 'york city 13',\n",
    "         'york 14 1': 'york city 14 1',\n",
    "         'york 14 2': 'york city 14 2',\n",
    "         'york 14 3': 'york city 14 3',\n",
    "         'york 15': 'york city 15',\n",
    "         'york 5': 'york city 5',\n",
    "         'york 6': 'york city 6',\n",
    "         'york 7': 'york city 7',\n",
    "         'york 8': 'york city 8',\n",
    "         'york 9 1': 'york city 9 1',\n",
    "         'york 9 2': 'york city 9 2'},\n",
    "    'York County':\n",
    "        {'york city 1': 'york 1',\n",
    "         'york city 11': 'york 11',\n",
    "         'york city 12 1': 'york 12 1',\n",
    "         'york city 12 2': 'york 12 2',\n",
    "         'york city 12 3': 'york 12 3',\n",
    "         'york city 12 4': 'york 12 4',\n",
    "         'york city 13': 'york 13',\n",
    "         'york city 14 1': 'york 14 1',\n",
    "         'york city 14 2': 'york 14 2',\n",
    "         'york city 14 3': 'york 14 3',\n",
    "         'york city 15': 'york 15',\n",
    "         'york city 5': 'york 5',\n",
    "         'york city 6': 'york 6',\n",
    "         'york city 7': 'york 7',\n",
    "         'york city 8': 'york 8',\n",
    "         'york city 9 1': 'york 9 1',\n",
    "         'york city 9 2': 'york 9 2'},\n",
    "    'Lancaster County':\n",
    "        {'cocalico east reamstown': 'east cocalico reamstown',\n",
    "         'cocalico east smokestown': 'east cocalico smokestown',\n",
    "         'cocalico east stevens': 'east cocalico stevens',\n",
    "         'cocalico east swartzville': 'east cocalico swartzville',\n",
    "         'cocalico west reinholds': 'west cocalico reinholds',\n",
    "         'cocalico west schoeneck': 'west cocalico schoeneck',\n",
    "         'donegal east maytown': 'east donegal maytown',\n",
    "         'donegal east maytown west': 'east donegal maytown west',\n",
    "         'donegal east springville': 'east donegal springville',\n",
    "         'donegal west 1': 'west donegal 1',\n",
    "         'donegal west 2': 'west donegal 2',\n",
    "         'donegal west 3': 'west donegal 3',\n",
    "         'donegal west 4': 'west donegal 4',\n",
    "         'drumore east': 'east drumore',\n",
    "         'earl east blue ball': 'east earl blue ball',\n",
    "         'earl east terre hill': 'east earl terre hill',\n",
    "         'east hempfield landisville': 'east hempfield landisville west',\n",
    "         'lancaster 7 8 (cv)':'lancaster 7 8',\n",
    "         'lancaster 7 8 (ls)':'lancaster 7 8',\n",
    "         'manheim 7 41': 'manheim 7 a (hd 41)',\n",
    "         'manheim 7 96': 'manheim 7 b (hd 96)',\n",
    "         'west willow street': 'west willow st'},\n",
    "    'Luzerne County':\n",
    "        {'bear creek vlg': 'bear creek village', 'butler 2': 'butler 2 (upper lehigh)'},\n",
    "    'Northampton County':\n",
    "        {'bethlehem 1 2 (138)': 'bethlehem 1 2',\n",
    "         'bethlehem 2 1 (135)': 'bethlehem 2 1',\n",
    "         'bushkill bushkill center': 'bushkill centre',\n",
    "         'bushkill cherryhill': 'bushkill cherry hill',\n",
    "         'forks east 1': 'forks eastern 1',\n",
    "         'forks east 2': 'forks eastern 2',\n",
    "         'forks west 1': 'forks western 1',\n",
    "         'forks west 2': 'forks western 2',\n",
    "         'lehigh northwestern': 'lehigh northwest',\n",
    "         'lower mount bethel lower ind': 'lower mount bethel independent',\n",
    "         'lower saucon hellertown 1': 'lower saucon 1',\n",
    "         'lower saucon leithsville 2': 'lower saucon 2',\n",
    "         'lower saucon lower saucon 3': 'lower saucon 3',\n",
    "         'lower saucon seidersville 4': 'lower saucon 4',\n",
    "         'lower saucon shimersville 5': 'lower saucon 5',\n",
    "         'lower saucon wassergass 6': 'lower saucon 6',\n",
    "         'moore east': 'moore eastern',\n",
    "         'palmer east': 'palmer eastern',\n",
    "         'palmer upper east': 'palmer upper eastern',\n",
    "         'palmer upper west': 'palmer upper western',\n",
    "         'palmer upper west naz ind':'palmer upper western',\n",
    "         'palmer west 1': 'palmer western 1',\n",
    "         'palmer west 2': 'palmer western 2',\n",
    "         'plainfield plf church': 'plainfield plainfield church',\n",
    "         'williams east': 'williams eastern',\n",
    "         'williams west': 'williams western'},\n",
    "    'Fayette County':\n",
    "        {'bellevernon': 'belle vernon',\n",
    "         'bullskind1': 'bullskin 3',\n",
    "         'bullskind2': 'bullskin 1',\n",
    "         'bullskind3': 'bullskin 2',\n",
    "         'cvilletwp': 'connellsville',\n",
    "         'cvillew1': 'connellsville 1',\n",
    "         'cvillew2': 'connellsville 2',\n",
    "         'cvillew3': 'connellsville 3',\n",
    "         'cvillew4': 'connellsville 4',\n",
    "         'dawsonboro': 'dawson',\n",
    "         'dunbarboro': 'dunbar',\n",
    "         'dunbartwp1': 'dunbar 1',\n",
    "         'dunbartwp2': 'dunbar 2',\n",
    "         'fayctyboro': 'fayette city',\n",
    "         'fchance': 'fairchance',\n",
    "         'georgesd1': 'georges 1',\n",
    "         'georgesd2': 'georges 2',\n",
    "         'georgesd3': 'georges 3',\n",
    "         'germand1': 'german 1',\n",
    "         'germand2': 'german 2',\n",
    "         'germand3': 'german 3',\n",
    "         'germand4': 'german 4',\n",
    "         'henryclay': 'henry clay',\n",
    "         'jefftwp': 'jefferson',\n",
    "         'ltyronetwp': 'lower tyrone',\n",
    "         'luzerned1': 'luzerne 1',\n",
    "         'luzerned2': 'luzerne 2',\n",
    "         'luzerned3': 'luzerne 3',\n",
    "         'luzerned4': 'luzerne 4',\n",
    "         'markleysbrg': 'markleysburg',\n",
    "         'menallen1': 'menallen 1',\n",
    "         'menallen2': 'menallen 2',\n",
    "         'menallen3': 'menallen 3',\n",
    "         'newellboro': 'newell',\n",
    "         'nunion1': 'north union 1',\n",
    "         'nunion2': 'north union 2',\n",
    "         'nunion3': 'north union 3',\n",
    "         'nunion4': 'north union 4',\n",
    "         'nunion5': 'north union 5',\n",
    "         'perrytwp': 'perry 1',\n",
    "         'ptmarion': 'point marion',\n",
    "         'redstone1': 'redstone 1',\n",
    "         'redstone2': 'redstone 2',\n",
    "         'redstone3': 'redstone 3',\n",
    "         'redstone4': 'redstone 4',\n",
    "         'scvilleboro': 'south connellsville',\n",
    "         'spfield1': 'springfield 1',\n",
    "         'spfield2': 'springfield 2',\n",
    "         'sphill1': 'springhill 1',\n",
    "         'sphill2': 'springhill 2',\n",
    "         'sunion1': 'south union 1',\n",
    "         'sunion2': 'south union 2',\n",
    "         'sunion3': 'south union 3',\n",
    "         'utownw1': 'uniontown 1',\n",
    "         'utownw2': 'uniontown 2',\n",
    "         'utownw3': 'uniontown 3',\n",
    "         'utownw4': 'uniontown 4',\n",
    "         'utownw5': 'uniontown 5',\n",
    "         'utownw6': 'uniontown 6',\n",
    "         'utownw7': 'uniontown 7',\n",
    "         'utyronetwp': 'upper tyrone',\n",
    "         'vbiltboro': 'vanderbilt',\n",
    "         'bvillew1':'brownsville',\n",
    "         'bvillew2':'brownsville',\n",
    "         'bvillew3':'brownsville',\n",
    "         'franklind1':'franklin',\n",
    "         'franklind2':'franklin',\n",
    "         },\n",
    "    'Westmoreland County':\n",
    "        {'donegal four mile r': 'donegal four mile run',\n",
    "         'donegal indian cree': 'donegal indian creek',\n",
    "         'east huntingdon 1': 'east huntingdon bessemer ed 1',\n",
    "         'east huntingdon 2': 'east huntingdon bessemer ed 2',\n",
    "         'east huntingdon ruffs': 'east huntingdon ruffsdale',\n",
    "         'east huntingdon stoner': 'east huntingdon stoners',\n",
    "         'east huntingdon strohm': 'east huntingdon strohms',\n",
    "         'fairfield north fairfie': 'fairfield north fairfield',\n",
    "         'fairfield south fairfie': 'fairfield south fairfield',\n",
    "         'hempfield east adamsbu': 'hempfield east adamsburg',\n",
    "         'hempfield 1': 'hempfield foxhill',\n",
    "         'hempfield 2': 'hempfield alwine',\n",
    "         'hempfield 3': 'hempfield maplewood',\n",
    "         'hempfield 4': 'hempfield carbon',\n",
    "         'hempfield 5': 'hempfield wendel herm',\n",
    "         'hempfield 6': 'hempfield luxor',\n",
    "         'hempfield 7': 'hempfield hannastown',\n",
    "         'hempfield 8': 'hempfield bovard',\n",
    "         'hempfield fort alle': 'hempfield fort allen',\n",
    "         'hempfield grapevill': 'hempfield grapeville',\n",
    "         'hempfield lincoln east': 'hempfield lincoln heights',\n",
    "         'hempfield lincoln west': 'hempfield lincoln heights west',\n",
    "         'hempfield middletow': 'hempfield middletown',\n",
    "         'hempfield universit': 'hempfield university',\n",
    "         'hempfield weavers o': 'hempfield weavers old stand',\n",
    "         'hempfield haydenvil':'hempfield haydenville', \n",
    "         'hempfield new stant':'hempfield new stanton', \n",
    "         'hempfield west hempfie':'west hempfield hempfield',\n",
    "         'ligonier laughlinst': 'ligonier laughlinstown',\n",
    "         'mt pleasant bridgep': 'mt pleasant bridgeport',\n",
    "         'mt pleasant hecla': 'mt pleasant heccla',\n",
    "         'mt pleasant laurel': 'mt pleasant laurel run',\n",
    "         'mt pleasant pleasan': 'mt pleasant pleasant valley',\n",
    "         'mt pleasant ridgevi': 'mt pleasant ridgeview',\n",
    "         'mt pleasant spring': 'mt pleasant spring garden',\n",
    "         'mt pleasant westmor': 'mt pleasant westmoreland',\n",
    "         'murrysville east murrysvil': 'murrysville east murrysville',\n",
    "         'murrysville south murrysvil': 'murrysville south murrysville',\n",
    "         'murrysville west murrysvil': 'murrysville west murrysville',\n",
    "         'rostraver collinsbu': 'rostraver collinsburg',\n",
    "         'rostraver cross roa': 'rostraver cross roads',\n",
    "         'sewickley east hermini': 'sewickley east herminie',\n",
    "         'sewickley west hermini': 'sewickley west herminie',\n",
    "         'south huntingdon jacobs': 'south huntingdon jacobs creek',\n",
    "         'south huntingdon minera': 'south huntingdon mineral',\n",
    "         'south huntingdon port r': 'south huntingdon port royal',\n",
    "         'south huntingdon south hunt': 'south huntingdon south huntingdon',\n",
    "         'sw greensburg 1': 'southwest greensburg 1',\n",
    "         'sw greensburg 2': 'southwest greensburg 2',\n",
    "         'unity pleasant unit': 'unity pleasant unity',\n",
    "         'washington north washin': 'washington north washington',\n",
    "         'washington oakland': 'washington oakland x roads',},\n",
    "    'Armstrong County':\n",
    "        {'cowanshannock east': 'cowanshannock eastern',\n",
    "         'cowanshannock sag': 'cowanshannock sagamore',\n",
    "         'cowanshannock west': 'cowanshannock western',\n",
    "         'east franklin east': 'east franklin eastern',\n",
    "         'east franklin north': 'east franklin northern',\n",
    "         'east franklin west': 'east franklin western',\n",
    "         'ford city 1 north': 'ford city northern 1',\n",
    "         'ford city 1 south': 'ford city southern 1',\n",
    "         'ford city 2 north': 'ford city northern 2',\n",
    "         'ford city 2 south': 'ford city southern 2',\n",
    "         'gilpin 2': 'gilpin 2 ed schenley',\n",
    "         'kiskiminetas ohn': 'kiskiminetas orchhills north',\n",
    "         'kiskiminetas ohs': 'kiskiminetas orchhills south',\n",
    "         'manor north': 'manor northern',\n",
    "         'manor south 1': 'manor southern 1',\n",
    "         'manor south 2': 'manor southern 2',\n",
    "         'north buffalo east': 'north buffalo eastern',\n",
    "         'north buffalo west': 'north buffalo western',\n",
    "         'parks n vandergrift': 'parks north vandergrift',\n",
    "         'parks west': 'parks western',\n",
    "         'south buffalo east': 'south buffalo eastern',\n",
    "         'south buffalo west': 'south buffalo western'},\n",
    "    'Delaware County':\n",
    "        {'chadds ford ne': 'chadds ford northeast',\n",
    "         'chadds ford sw': 'chadds ford southeast',\n",
    "         'east lansdowne e': 'east lansdowne eastern',\n",
    "         'east lansdowne w': 'east lansdowne western',\n",
    "         'lwr chichester 1p': 'lower chichester 1',\n",
    "         'lwr chichester 2p': 'lower chichester 2',\n",
    "         'radnor 6w 1p': 'radnor 6 1 hd 165',\n",
    "         'springfield 2w 3p emerg': 'springfield 2 3',\n",
    "         'upper darby 6d 11p emerg': 'upper darby 6 11',\n",
    "         'upper darby 6d 8p emerg': 'upper darby 6 8',\n",
    "         'CHESTER TWP PCT 03': 'CHESTER CITY 3W',\n",
    "         'CHESTER TWP PCT 04': 'CHESTER CITY 4W',\n",
    "         'CHESTER WD 03': 'CHESTER TWP 3P',\n",
    "         'CHESTER WD 04': 'CHESTER TWP 4P',\n",
    "         'DARBY TWP WD 03 PCT 01': 'DARBY BORO 3W 1P',\n",
    "         'DARBY TWP WD 03 PCT 02': 'DARBY BORO 3W 2P',\n",
    "         'DARBY WD 03 PCT 01': 'DARBY TWP 3W 1P',\n",
    "         'DARBY WD 03 PCT 02': 'DARBY TWP 3W 2P',\n",
    "        },\n",
    "    'Mifflin County':\n",
    "        {'brown church hill': 'brown', 'brown reedsville big va': 'brown'},\n",
    "}\n",
    "\n",
    "county_id_to_vtd_to_oe_prec_name = {\n",
    "    'Allegheny County': {\n",
    "        '000130' : 'BALDWIN BR DIST 1',\n",
    "        '000140' : 'BALDWIN BR DIST 2',\n",
    "        '000151' :'BALDWIN TP DIST 1',\n",
    "        '000161' : 'BALDWIN TP DIST 2',},\n",
    "    'Beaver County' : {\n",
    "        '000630': '1401 DARLINGTON TWP', \n",
    "        '000640': '1301 DARLINGTON BORO',\n",
    "        '001430': '4801 ROCHESTER TWP 1', \n",
    "        '001445': '4802 ROCHESTER TWP 2', \n",
    "        '001460': '4701 ROCHESTER BORO 1', \n",
    "        '001470': '4702 ROCHESTER BORO 2',\n",
    "        # Unmatched precincts\n",
    "        '001320': '4004 NORTH SEWICKLEY TWP 4',\n",
    "        '001280': '3903 NEW SEWICKLEY TWP 3',\n",
    "        '001260' : '3901 NEW SEWICKLEY TWP 1',\n",
    "        '001270' : '3902 NEW SEWICKLEY TWP 2',\n",
    "    },\n",
    "    'Berks County' : {\n",
    "        '000239' : 'Cumru Twp. 01', \n",
    "        '000241' : 'Cumru Twp. 01',\n",
    "        '000529': 'Laureldale Boro 01',\n",
    "        '000531' : 'Laureldale Boro 01',\n",
    "    },\n",
    "    'Blair County' : {\n",
    "        '000870': 'TYRONE TWP 1', \n",
    "        '000880': 'TYRONE TWP 2',\n",
    "        '000890': 'TYRONE BORO 1',\n",
    "        '000900': 'TYRONE BORO 2'},\n",
    "    'Bradford County' : {\n",
    "        '000120': 'BURLINGTON BOROUGH',\n",
    "        '000310': 'ROME BOROUGH',\n",
    "        '000230': 'MONROE BOROUGH',\n",
    "        '000080': 'ATHENS BOROUGH 2ND',\n",
    "        '000110': 'BURLINGTON TOWNSHIP', \n",
    "        '000070': 'ATHENS BOROUGH 1ST',\n",
    "        '000600': 'WYALUSING BOROUGH',\n",
    "        '000220': 'MONROE TOWNSHIP',\n",
    "        '000300': 'ROME TOWNSHIP',\n",
    "        '000590': 'WYALUSING TOWNSHIP',\n",
    "        '000130': 'CANTON TOWNSHIP',\n",
    "        '000510': 'TROY BOROUGH',\n",
    "        '000140': 'CANTON BOROUGH',\n",
    "        '000500': 'TROY TOWNSHIP',\n",
    "        '000050': 'ATHENS TOWNSHIP 1ST',\n",
    "        '000060': 'ATHENS TOWNSHIP 2ND'},\n",
    "    'Bucks County' : {\n",
    "        '001920': 'Newtown Boro 2nd',\n",
    "        '001850': 'Newtown Twp 1',\n",
    "        '001860': 'Newtown Twp 2',\n",
    "        '001910': 'Newtown Boro 1st'},\n",
    "    'Butler County' : {\n",
    "        '000670':'0052 SLIPPERY ROCK TOWNSHIP',\n",
    "        '000680':'0077 SLIPPERY ROCK BOROUGH',\n",
    "        '000420':'0036 FAIRVIEW TOWNSHIP',\n",
    "        '000430':'0067 FAIRVIEW BOROUGH', \n",
    "        '000330':'0025 CONNOQUENESSING TOWNSHIP',\n",
    "        '000340':'0063 CONNOQUENESSING BOROUGH',},\n",
    "    'Cambria County' : {\n",
    "        '000636': 'Gallitzin Boro',\n",
    "        '000615': 'Gallitzin Twp',},\n",
    "    'Clarion County' : {\n",
    "        '000070': '5.1 Clarion Borough First',\n",
    "        '000080': '5.2 Clarion Borough Second',\n",
    "        '000050': '6.1 Clarion Township First',\n",
    "        '000060': '6.2 Clarion Township Second',\n",
    "        '000210': '13.1 Knox Borough',\n",
    "        '000200': '14.1 Knox Township'},\n",
    "    'Clearfield County' : {\n",
    "        '000120': '0002 Burnside Borough', \n",
    "        '000110': '0036 Burnside Township'},\n",
    "    'Clinton County' : {\n",
    "        '000050': '0004 BEECH CREEK BOROUGH',\n",
    "        '000040': '0005 BEECH CREEK TOWNSHIP'},\n",
    "    'Columbia County' : {\n",
    "        '000020': 'Benton Township',\n",
    "        '000030': 'Benton Borough',\n",
    "        '000260': 'Catawissa Township',\n",
    "        '000275': 'Catawissa Borough'},\n",
    "    'Crawford County' : {\n",
    "        '000670': 'Woodcock Boro',\n",
    "        '000580': 'Venango Boro',\n",
    "        '000570': 'Venango Twp',\n",
    "        '000660': 'Woodcock Twp'},\n",
    "    'Erie County' : {\n",
    "        '001320': 'NORTH EAST TOWNSHIP 1ST',\n",
    "        '001330': 'NORTH EAST TOWNSHIP 2ND',\n",
    "        '001350': 'NORTH EAST BORO 2ND WAR',\n",
    "        '001340': 'NORTH EAST BORO 1ST WAR'},\n",
    "    'Forest County' : {\n",
    "        '000100': '0008 TIONESTA TWP',\n",
    "        '000110': '0009 TIONESTA BORO'},\n",
    "    'Lancaster County' : {\n",
    "        '000580': 'Lancaster City - 1st Ward',\n",
    "        '000980': 'Lancaster Twp - 1st Dist',\n",
    "        '001140': 'Manheim Twp - 1st Dist',\n",
    "        '001155': 'Manheim Twp - 2nd Dist',\n",
    "        '001340': 'Manheim Boro - 1st Ward',\n",
    "        '001350': 'Manheim Boro - 2nd Ward',\n",
    "        '000610': 'Lancaster City - 3rd Ward',\n",
    "    },\n",
    "    'Luzerne County' : {\n",
    "        '001285': 'KINGSTON TWP D 01',\n",
    "        '001295': 'KINGSTON TWP D 02',\n",
    "        '001305': 'KINGSTON TWP D 03',\n",
    "        '001310': 'KINGSTON BORO W 01',\n",
    "        '001320': 'KINGSTON BORO W 02',\n",
    "        '001330': 'KINGSTON BORO W 03',\n",
    "        '001740': 'NESCOPECK TWP',\n",
    "        '001750': 'NESCOPECK BORO',\n",
    "        '001852': 'PITTSTON CITY W 01',\n",
    "        '001862': 'PITTSTON CITY W 02',\n",
    "        '001995': 'PITTSTON TWP D 01',\n",
    "        '002005': 'PITTSTON TWP D 02',\n",
    "        '002122': 'PLYMOUTH BORO W 01',\n",
    "        '002142': 'PLYMOUTH TWP',\n",
    "        '000230':'DALLAS BORO W 01',\n",
    "        '000240':'DALLAS BORO W 02',\n",
    "        '000165':'CONYNGHAM TWP',\n",
    "        '002635': 'WILKES BARRE CITY W 01',\n",
    "        '002655': 'WILKES BARRE CITY W 02',\n",
    "        '003075': 'WILKES BARRE TWP W 01',\n",
    "        '003095': 'WILKES BARRE TWP W 02'},\n",
    "    'McKean County' : {\n",
    "        '000020': '0002 BRADFORD CITY-1ST WARD',\n",
    "        '000030': '0003 BRADFORD CITY-2ND WARD',\n",
    "        '000110': '0010 BRADFORD TWNSHIP-1ST DIS',\n",
    "        '000120': '0011 BRADFORD TWNSHIP-2ND DIST',\n",
    "        '000160': '0015 ELDRED BOROUGH',\n",
    "        '000170': '0016 ELDRED TOWNSHIP'},\n",
    "    'Mercer County' : {'000710': '3301 SANDY LAKE BORO', '000700': '3401 SANDY LAKE TWP'},\n",
    "    'Mifflin County' : {\n",
    "        '000010':'0001 ARMAGH - EAST',\n",
    "        '000020':'0002 ARMAGH - WEST',\n",
    "        '000160':'0016 LEWISTOWN - WEST',\n",
    "        '000180':'0017 LEWISTOWN - CENTRAL',\n",
    "        '000210':'0018 LEWISTOWN - NORTH',\n",
    "        '000230':'0019 LEWISTOWN - SOUTH',},\n",
    "    'Perry County' : {'000140': '0012 LIVERPOOL BOROUGH', '000130': '0013 LIVERPOOL TOWNSHIP'},\n",
    "    'Pike County' : {'000090': 'Milford Boro', '000100': 'Milford Twp'},\n",
    "    'Potter County' : {'000190': 'Oswayo Boro', '000180': 'Oswayo Twp', '000310': 'Ulysses Twp', '000320': 'Ulysses Boro'},\n",
    "    'Schuylkill County' : {'001580': 'Tremont Twp.', '001595': 'Tremont', '000900': 'Pine Grove Twp. Pct. 2', '000890': 'Pine Grove Twp. Pct. 1'},\n",
    "    'Sullivan County' : {'000140': 'Laporte Borough', '000130': 'Laporte Township'},\n",
    "    'Susquehanna County' : {'000420': 'Thompson Boro', '000410': 'Thompson Twp', '000340': 'Oakland Twp', '000350': 'Oakland Boro', '000160': 'Great Bend Boro', '000330': 'New Milford Boro', '000320': 'New Milford Twp', '000150': 'Great Bend Twp'},\n",
    "    'Tioga County' : {\n",
    "        '000240': 'Liberty Boro',\n",
    "        '000230': 'Liberty Twp',\n",
    "        '000460': 'Westfield Boro',\n",
    "        '000445': 'Westfield Twp',\n",
    "        '000370': 'Tioga Twp',\n",
    "        '000380': 'Tioga Boro',\n",
    "        '000410': 'Ward'},\n",
    "    'Warren County' : {\n",
    "        '000255': 'SUGAR GROVE Twp',\n",
    "        '000290': 'SUGAR GROVE Boro'},\n",
    "    'Wyoming County' : {\n",
    "        '000230': 'Tunkhannock Township #1',\n",
    "        '000240': 'Tunkhannock Township #2',\n",
    "        '000130': 'Meshoppen Township',\n",
    "        '000140': 'Meshoppen Borough',\n",
    "        '000170': 'Nicholson Borough at Large',\n",
    "        '000160': 'Nicholson Township',\n",
    "        '000250': 'Tunkhannock Borough Ward 1',\n",
    "        '000260': 'Tunkhannock Borough Ward 2'},\n",
    "    'Bedford County' : {\n",
    "        '000150': 'Hopewell Township',\n",
    "        '000160': 'Hopewell Borough',\n",
    "        '000410': 'Woodbury Township',\n",
    "        '000420': 'Woodbury Borough',\n",
    "    },\n",
    "    'Centre County' : {\n",
    "        '000290' : 'Howard Township',\n",
    "        '000300' : 'Howard Borough'\n",
    "    },\n",
    "    'Somerset County' : {\n",
    "        '000010':'Addison Township',\n",
    "        '000020':'Addison Borough',\n",
    "    }\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "default_remove_lst = ['#', '.',\"'\", '`', ',', 'twp-', 'w-']\n",
    "default_target_to_replacement_substring = {'~':' ', '-':' ', '/':' ', 'upper leacock':'leacock upper', 'hempfield east':'east hempfield','hempfield west':'west hempfield', 'west earl':'earl west'}\n",
    "default_stopping_words = ['pct', 'di', 'dis', 'division', 'borough', 'township', 'district', 'dist', 'br', 'boro', 'ward', 'wrd', 'park', 'prk', 'tp', 'twp', 'wd', 'pk', 'precinct', 'p', 'd', 'dst']\n",
    "default_target_to_replacement_word = {'one':'1','two':'2', 'three':'3','four':'4', 'five':'5', 'six':'6', 'seven':'7','sq':'square', 'mid':'middle', 'johnstown':'johns', 'first':'1', 'second':'2', 'third':'3', 'fourth':'4', 'fifth':'5', 'sixth':'6','seventh':'7','eighth':'8','seventeenth':'17', 'eighteenth':'18', 'nineteenth':'19', 'twenteith': '20', 'spg':'spring','l':'lower', 'mt':'mount', 'casl':'castle', 'n':'north', 's':'south', 'e':'east', 'w':'west', 'hts':'heights', 'ht':'heights', 'hl':'hills', 'springdal':'springdale','up':'upper'}\n",
    "\n",
    "# Helper Functions\n",
    "def remove_pre_num_char(prec_name, target):\n",
    "    window_size = len(target)\n",
    "    for idx in range(window_size, len(prec_name)):\n",
    "        window = prec_name[idx-window_size:idx]\n",
    "        if window == target and prec_name[idx].isnumeric():\n",
    "            prec_name = prec_name[:idx-(window_size-1)] + prec_name[idx:]\n",
    "    return prec_name\n",
    "\n",
    "def edit_precinct_name(prec_name, \n",
    "    remove_lst=default_remove_lst, \n",
    "    target_to_replacement_substring = default_target_to_replacement_substring,\n",
    "    stopping_words=default_stopping_words,\n",
    "    target_to_replacement_word = default_target_to_replacement_word,\n",
    "    prec_dict={}):\n",
    "    '''\n",
    "    Returns the a lower case precinct string with certian modifications depending other arguments. \n",
    "    \n",
    "    Modifications are performed in order of the parameters they depend on. By convention, case is \n",
    "    ignored by making prec_name lower case. Accordingly, one should pass arguements with lower case\n",
    "    elements. That is, keys of the dictionaries and elements of lists should be lower case strings.\n",
    "\n",
    "\tParameters:\n",
    "\t\tprec_name (str): precinct name\n",
    "\t\tremove_lst ((str) list): if a string in this list is a substring in prec_name it will be removed. \n",
    "            All elements should be lower case.\n",
    "        target_to_replacement_substring ({str:str} dictionary): keys (targets) will be replaced with their \n",
    "            corresponding value (replacements) in prec_name if the key is a substring. All keys should be lower case.\n",
    "        stopping_words ({str} list): If any substring of prec_name contains a element of stopping_words\n",
    "             that is adjacent to a space character it will be removed. All elements should be lower case.\n",
    "        target_to_replacement_words ({str:str} dictionary): keys (targets) will be replaced with their \n",
    "            corresponding value (replacements) in prec_name if the key is a word. All keys should be lower case.\n",
    "        prec_dict ({str:str} dictionary): After all the modifications above, if the edited prec_name\n",
    "            string is in the set of keys for prec_dict, then it will be replaced with that key's value.  \n",
    "            All keys should be lower case.\n",
    "\n",
    "\tReturns:\n",
    "\t\tprec_name (str): prec_name arguement returned with the \n",
    "    '''\n",
    "    prec_name = str(prec_name).lower()\n",
    "    prec_name = re.sub(r\"(?<=\\d)(st|nd|rd|th)\\b\", '', prec_name) # removes ordinals (e.g. 3rd -> 3)\n",
    "    for word in remove_lst:\n",
    "        prec_name = prec_name.replace(word, '')\n",
    "    for target, replacement in target_to_replacement_substring.items():\n",
    "        prec_name = prec_name.replace(target, replacement)\n",
    "    for target in [' w', ' d', ' p', ' twp']:\n",
    "        prec_name = remove_pre_num_char(prec_name, target) # e.g. remove d and w in front of number (e.g. d1 -> 1)\n",
    "    words = prec_name.split()\n",
    "    words = [target_to_replacement_word.get(word, word) for word in words]\n",
    "    words = [word.lstrip('0') for word in words if word not in stopping_words]\n",
    "    has_words = any(not word.isnumeric() for word in words)\n",
    "    while has_words and words[0].isnumeric():\n",
    "        words = words[1:]\n",
    "    prec_name = \" \".join([word.lstrip('0') for word in words if len(word) > 0])\n",
    "    return prec_dict[prec_name] if prec_name in prec_dict.keys() else prec_name\n",
    "\n",
    "\n",
    "def row_to_edited_precinct_name(row, county_id_to_precinct_modifications_dictionary, county_id_to_target_to_replacement_word):\n",
    "    county_id = row['county_id']\n",
    "    original_prec_name = row['original_precinct_name']\n",
    "    county_target_to_replacement_word = county_id_to_target_to_replacement_word.get(county_id, default_target_to_replacement_word)\n",
    "    county_precinct_modification_dictionary = county_id_to_precinct_modifications_dictionary.get(county_id, {})\n",
    "    edited_prec_name = edit_precinct_name(original_prec_name, prec_dict=county_precinct_modification_dictionary, target_to_replacement_word=county_target_to_replacement_word)\n",
    "    return edited_prec_name.strip()\n",
    "\n",
    "def enumerate_unmatched(precinct_list, unmatched_precincts, dataset_name):\n",
    "    for original_precinct_name, edited_precinct_name in precinct_list:\n",
    "        if edited_precinct_name in unmatched_precincts:\n",
    "            print(\"{} <-- {} ({})\".format(edited_precinct_name, original_precinct_name, dataset_name))  \n",
    "\n",
    "county_id_to_target_to_replacement_word_y = {\n",
    "    'Lancaster County':{'city':'', 'lampeter':''},\n",
    "    'Luzerne County':{'w':'', 'city': ''},\n",
    "    'Northampton County':dict(default_target_to_replacement_word, **{'city':'', 'eastern':'east','western':'west'}),\n",
    "    'Westmoreland County':{'no':'','n':'north', 's':'south', 'e':'east','w':'west'},\n",
    "    'Armstrong County':{'nort':'north', 'sout':'south'},\n",
    "    'Delaware County':{'city':'', 'prov':'providence','upp':'upper'},\n",
    "}\n",
    "\n",
    "county_id_to_replacement_words_x = {\n",
    "    'Butler County':{'ward':'city'},\n",
    "    'Lancaster County':{'city':'', 'lampeter':''},\n",
    "    'Philadelphia County':{'~':' ','philadelphia':''},\n",
    "    'Elk County':{'vtd':'',},\n",
    "    'Westmoreland County':{'vtd':'', 'voting':''},\n",
    "    'Armstrong County':{'vtd':'', 'voting':''},\n",
    "    'Delaware County':{'vtd':'', 'voting':''},\n",
    "}\n",
    "\n",
    "county_id_to_precinct_modification_dictionary_x = {\n",
    "    'Allegheny County': {'whitehall 1 a (cong 14)':'whitehall 1', 'whitehall 1 b (cong 18)':'whitehall 1'},\n",
    "    'Cambria County':  {\n",
    "        'johns 21' : 'johns twenty 1',\n",
    "        'johns 8 2' : 'johns 8 3',\n",
    "        'carrolltown' : 'carroltown', \n",
    "        'clearfield' : 'clearfied', \n",
    "        'johns old conemaugh' : 'johns old conemaugh woodv',\n",
    "        'johns 11' : 'johns old conemaugh woodv',\n",
    "        'reade north' : 'reade',\n",
    "        'reade south' : 'reade', \n",
    "        'northern cambria 3 a (cong 9)' : 'northern cambria 3',\n",
    "        'northern cambria 3 b (cong 12)' : 'northern cambria 3',\n",
    "        'barr south a (cong 9)' : 'barr',\n",
    "        'barr south b (cong 12)' : 'barr',\n",
    "        'johns center town 1' : 'johns center town',\n",
    "        'johns center town 2' : 'johns center town',\n",
    "    },\n",
    "    'Chester County': {\n",
    "        'phoenixville middle 1 (hd 157)' : 'phoenixville middle 1',\n",
    "        'phoenixville middle 1 (hd 155)' : 'phoenixville middle 1',\n",
    "        'kennett 2 a (cong 7)' : 'kennett 2',\n",
    "        'kennett 2 b (cong 16)' : 'kennett 2',\n",
    "    },\n",
    "    'Clarion County': {\n",
    "        'farmington west' : 'farmington', \n",
    "        'farmington north' : 'farmington',\n",
    "        'farmington south' : 'farmington',\n",
    "        'piney a (cong 3)' : 'piney',\n",
    "        'piney b (cong 5)' : 'piney',\n",
    "    },\n",
    "    'Cumberland County': {\n",
    "        'lower allen annex':'lower allen 1 annex',\n",
    "        'north middleton 1 congressional 10':'north middleton 1',\n",
    "        'north middleton 1 congressional 13':'north middleton 1',\n",
    "        'north middleton 3 congressional 10':'north middleton 3',\n",
    "        'north middleton 3 congressional 13':'north middleton 3',\n",
    "    },\n",
    "    'Erie County': {'corry 4':'corry 3'},\n",
    "    'Fayette County': {'masontown 1':'masontwn', 'masontown 2':'masontwn',},\n",
    "    'Huntingdon County':{'penn b (9 cong)':'penn', 'penn a (5 cong)':'penn'},\n",
    "    'Lebanon County':{'north lebanon east b (cong 15)':'north lebanon east', 'north lebanon east a (cong 6)':'north lebanon east'},\n",
    "    'Montgomery County':{\n",
    "        'hatfield 5 2 a (cong 13)':'hatfield 5 2',\n",
    "        'hatfield 5 2 b (cong 8)':'hatfield 5 2',\n",
    "        'horsham 2 2 b (cong 13)':'horsham 2 2',\n",
    "        'horsham 2 2 a (cong 7)':'horsham 2 2 7',\n",
    "        'lower merion 2 2 a (cong 2)':'lower merion 2 2',\n",
    "        'lower merion 2 2 b (cong 13)':'lower merion 2 2',\n",
    "        'perkiomen 1 a (cong 6)':'perkiomen 1',\n",
    "        'perkiomen 1 b (cong 7)':'perkiomen 1 7',},\n",
    "    'Northampton County':{'bethlehem 17 b (cong 17)':'bethlehem 17', 'bethlehem 17 a (cong 15)':'bethlehem 17'},\n",
    "    'Northumberland County':{'riverside b (cong 11)':'riverside', 'riverside a (cong 10)':'riverside',},\n",
    "    'Perry County':{'south west madison':'madison', 'north east madison':'sandy hill',},\n",
    "    'Schuylkill County': {\n",
    "        'cass 1':'cass north',\n",
    "        'cass 2':'cass south',\n",
    "        'pine grove 1':'pine grove north',\n",
    "        'pine grove 2':'pine grove south',},\n",
    "    'Tioga County': {'shippen a (cong 5)':'shippen', 'shippen b (cong 10)':'shippen'},\n",
    "    'Washington County': {'canton 3':'canton 1', 'fallowfield 2 a (cong 9)':'fallowfield 2', 'fallowfield 2 b (cong 18)':'fallowfield 2'},\n",
    "    'Delaware County':{'radnor 6 1 hd 165': 'radnor 6 1 hd 16', 'springfield 3 2': 'springfield 3 2 16'},\n",
    "    'Bedford County':\n",
    "        {'bedford 1': 'Bedford Township No 1',\n",
    "         'bedford 2': 'Bedford Township No 2',\n",
    "         'bedford east': 'Bedford Borough East Ward',\n",
    "         'bedford west': 'Bedford Borough West Ward',\n",
    "         'bloomfield': 'Bloomfield Township',\n",
    "         'broad top': 'Broad Top Township',\n",
    "         'coaldale': 'Coaldale Borough',\n",
    "         'colerain': 'Colerain Township',\n",
    "         'cumberland valley': 'Cumberland Valley',\n",
    "         'east providence': 'East Providence Township',\n",
    "         'east st clair': 'East St. Clair Township',\n",
    "         'everett': 'Everett Borough',\n",
    "         'harrison': 'Harrison Township',\n",
    "         'hyndman': 'Hyndman Borough',\n",
    "         'juniata': 'Juniata Township',\n",
    "         'kimmel': 'Kimmel Township',\n",
    "         'king': 'King Township',\n",
    "         'liberty': 'Liberty Township',\n",
    "         'lincoln': 'Lincoln Township',\n",
    "         'londonderry': 'Londonderry Township',\n",
    "         'mann': 'Mann Township',\n",
    "         'manns choice': 'Manns Choice Borough',\n",
    "         'monroe': 'Monroe Township Township',\n",
    "         'napier': 'Napier Township',\n",
    "         'new paris': 'New Paris Borough',\n",
    "         'pavia': 'Pavia Township',\n",
    "         'pleasantville': 'Pleasantville Borough',\n",
    "         'rainsburg': 'Rainsburg Borough',\n",
    "         'saxton': 'Saxton Borough',\n",
    "         'schellsburg': 'Schellsburg Borough',\n",
    "         'snake spring': 'Snake Spring',\n",
    "         'south woodbury': 'South Woodbury Township',\n",
    "         'southampton': 'Southampton Township',\n",
    "         'st clairsville': 'St. Clairsville Borough',\n",
    "         'west providence': 'West Providence Township',\n",
    "         'west st clair': 'West St. Clair Township',\n",
    "    },\n",
    "    'Centre County':\n",
    "        {'ferguson north central 2': 'ferguson north central',\n",
    "         'ferguson northeast 1 a': 'ferguson northeast 1',\n",
    "         'ferguson northeast 1 b': 'ferguson northeast 1',\n",
    "         'ferguson west central 1': 'ferguson west central',\n",
    "         'halfmoon proper': 'halfmoon',\n",
    "         'state college east 1': 'sc east 1',\n",
    "         'state college east 2': 'sc east 2',\n",
    "         'state college east 3': 'sc east 3',\n",
    "         'state college east 4': 'sc east 4',\n",
    "         'state college east central 1': 'sc east central 1',\n",
    "         'state college west central 1': 'sc west central 1'}\n",
    "}\n",
    "\n",
    "county_lst = sorted(list(gdf['county_id'].unique()))\n",
    "n_counties = len(county_lst)\n",
    "\n",
    "# Centre County\n",
    "centre = pd.read_csv('county-files/Centre County/num_to_name.csv')\n",
    "number_to_name_centre = pd.Series(centre.Name.values,index=centre.Precinct).to_dict()\n",
    "number_to_name_centre['24'] = '0024 24 SC EAST 1'\n",
    "number_to_name_centre['25'] = '0025 25 SC EAST 2'\n",
    "number_to_name_centre['26'] = '0026 26 SC EAST 3'\n",
    "number_to_name_centre['27'] = '0027 27 SC EAST 4'\n",
    "number_to_name_centre['28'] = '0028 28 SC EAST CENTRAL 1'\n",
    "number_to_name_centre['33'] = '0033 33 SC WEST CENTRAL 1'\n",
    "number_to_name_centre['44'] = 'College West'\n",
    "df.loc[df.county_id.isin(['Centre County']), 'original_precinct_name'] = df[df.county_id.isin(['Centre County'])].original_precinct_name.map(lambda x: x.zfill(2)).map(number_to_name_centre)\n",
    "\n",
    "gdf['edited_precinct_name'] = gdf.apply(lambda row: row_to_edited_precinct_name(row, county_id_to_precinct_modification_dictionary_x, county_id_to_replacement_words_x), axis=1)\n",
    "df['edited_precinct_name'] = df.apply(lambda row: row_to_edited_precinct_name(row, county_id_to_precinct_modification_dictionary_y, county_id_to_target_to_replacement_word_y), axis=1)\n",
    "\n",
    "# VTDST modifications\n",
    "def get_name_x(row):\n",
    "    vtd_to_oe_prec_name = county_id_to_vtd_to_oe_prec_name.get(row['county_id'], {})\n",
    "    return vtd_to_oe_prec_name[row['VTDST']] if row['VTDST'] in vtd_to_oe_prec_name.keys() else row['edited_precinct_name']\n",
    "\n",
    "def get_name_y(row):\n",
    "    vtd_to_oe_prec_name = county_id_to_vtd_to_oe_prec_name.get(row['county_id'], {})\n",
    "    return row['original_precinct_name'] if row['original_precinct_name'] in vtd_to_oe_prec_name.values() else row['edited_precinct_name']\n",
    "\n",
    "gdf['edited_precinct_name'] = gdf.apply(get_name_x, axis=1)\n",
    "df['edited_precinct_name'] = df.apply(get_name_y, axis=1)\n",
    "\n",
    "######## Manual Corrections ###########\n",
    "\n",
    "# cross county precincts\n",
    "gdf.loc[(gdf.edited_precinct_name == 'springs') & (gdf.county_id == 'Fayette County'), 'county_id'] = 'Somerset County'\n",
    "gdf.loc[(gdf.edited_precinct_name == 'adamstown') & (gdf.county_id == 'Berks County'), 'county_id'] = 'Lancaster County'\n",
    "gdf.loc[(gdf.edited_precinct_name == 'tunnelhill') & (gdf.county_id == 'Blair County'), 'county_id'] = 'Cambria County'\n",
    "gdf.loc[(gdf.edited_precinct_name == 'emlenton') & (gdf.county_id == 'Clarion County'), 'county_id'] = 'Venango County'\n",
    "gdf.loc[(gdf.edited_precinct_name == 'ashland') & (gdf.county_id == 'Columbia County'), 'county_id'] = 'Schuylkill County'\n",
    "gdf.loc[(gdf.edited_precinct_name == 'ashland') & (gdf.county_id == 'Schuylkill County'), 'edited_precinct_name'] = 'ashland 2'\n",
    "\n",
    "# Fulton County \n",
    "valley_hi = gdf[gdf.edited_precinct_name == 'brush creek and valley hi']\n",
    "valley_hi = valley_hi.reset_index()\n",
    "valley_hi.loc[0,'edited_precinct_name'] = 'valley hi'\n",
    "MGGG_gdf = gpd.read_file('data/shapefiles/pa-precincts/PA_VTDs')\n",
    "fulton_gdf = MGGG_gdf[(MGGG_gdf.COUNTYFP10 == '057')]\n",
    "valley_hi.loc[0:,'geometry'] = fulton_gdf[fulton_gdf.NAME10 == 'VALLEY-HI'].reset_index().at[0,'geometry']\n",
    "gdf = gdf.append(valley_hi)\n",
    "gdf.loc[(gdf.edited_precinct_name == 'wells') & (gdf.county_id == 'Fulton County'),'geometry'] = fulton_gdf[fulton_gdf.NAME10 == 'WELLS TWP'].reset_index().at[0,'geometry']\n",
    "gdf.loc[(gdf.edited_precinct_name == 'brush creek and valley hi') & (gdf.county_id == 'Fulton County'),'edited_precinct_name'] = 'brush creek'\n",
    "\n",
    "# Precinct Splitting\n",
    "cds = gpd.read_file(\"data/shapefiles/tl_2018_us_cd116\")\n",
    "cds = cds[cds.STATEFP == '42']\n",
    "\n",
    "# Berks County\n",
    "cds_4_6_9 = cds[cds.CD116FP.isin({'04','06','09'})][['CD116FP','geometry']]\n",
    "precs_to_split = ['1', 'exeter 5']\n",
    "berks_county_gdf = gdf[(gdf.county_id == 'Berks County') & gdf.edited_precinct_name.isin(precs_to_split)]\n",
    "overlayed = gpd.overlay(cds_4_6_9, berks_county_gdf, how='intersection')\n",
    "overlayed['edited_precinct_name'] = overlayed.apply(lambda row: row['edited_precinct_name'] + ' ({} cong)'.format(str(int(row['CD116FP']))), axis=1)\n",
    "gdf = gdf[~gdf.edited_precinct_name.isin(precs_to_split)]\n",
    "gdf = gdf.append(overlayed[gdf.columns])\n",
    "\n",
    "# Cambria County\n",
    "cds_13_15 = cds[cds.CD116FP.isin({'13','15'})][['CD116FP','geometry']]\n",
    "precs_to_split = ['east taylor 1', 'east taylor 2', 'east taylor 3', 'east taylor 4']\n",
    "cambria_county_gdf = gdf[(gdf.county_id == 'Cambria County') & gdf.edited_precinct_name.isin(precs_to_split)]\n",
    "overlayed = gpd.overlay(cds_13_15, cambria_county_gdf, how='intersection')\n",
    "overlayed['edited_precinct_name'] = overlayed.apply(lambda row: 'east taylor 1' if row['CD116FP'] == '15' else 'east taylor 2', axis=1)\n",
    "gdf = gdf[~gdf.edited_precinct_name.isin(precs_to_split)]\n",
    "gdf = gdf.append(overlayed[gdf.columns])\n",
    "\n",
    "# Montgomery County precinct splitting\n",
    "cds_1_4_5 = cds[cds.CD116FP.isin({'01','04','05'})][['CD116FP','geometry']]\n",
    "precs_to_split = ['franconia 2', 'horsham 2 1', 'horsham 4 2', 'lower merion 12 2', 'lower merion 12 3']\n",
    "montgomery_county_gdf = gdf[(gdf.county_id == 'Montgomery County') & gdf.edited_precinct_name.isin(precs_to_split)]\n",
    "overlayed = gpd.overlay(cds_1_4_5, montgomery_county_gdf, how='intersection')\n",
    "overlayed['edited_precinct_name'] = overlayed.apply(lambda row: row['edited_precinct_name'] + ' ' + str(int(row['CD116FP'])), axis=1)\n",
    "gdf = gdf[~gdf.edited_precinct_name.isin(precs_to_split)]\n",
    "gdf = gdf.append(overlayed[gdf.columns])\n",
    "gdf = gdf.reset_index()\n",
    "\n",
    "# delaware\n",
    "def del_precs(nm):\n",
    "    lst = []\n",
    "    for elt in nm.split():\n",
    "        if elt[0].isdigit() and len(elt) > 1:\n",
    "            lst.append(elt[:-1])\n",
    "        else:\n",
    "            lst.append(elt)\n",
    "    return ' '.join(lst)\n",
    "df.loc[df.county_id == 'Delaware County', 'edited_precinct_name'] = df[df.county_id == 'Delaware County']['edited_precinct_name'].map(del_precs)\n",
    "df.loc[((df.county_id == 'Delaware County') & df.edited_precinct_name.isin(['chester 3', 'chester 4', 'darby 3 1', 'darby 3 2'])), 'edited_precinct_name'] = df.original_precinct_name\n",
    "delaware_corrections = {\n",
    "    'CHESTER TWP PCT 03': 'CHESTER CITY 3W',\n",
    "    'CHESTER TWP PCT 04': 'CHESTER CITY 4W',\n",
    "    'CHESTER WD 03': 'CHESTER TWP 3P',\n",
    "    'CHESTER WD 04': 'CHESTER TWP 4P',\n",
    "    'DARBY TWP WD 03 PCT 01': 'DARBY BORO 3W 1P',\n",
    "    'DARBY TWP WD 03 PCT 02': 'DARBY BORO 3W 2P',\n",
    "    'DARBY WD 03 PCT 01': 'DARBY TWP 3W 1P',\n",
    "    'DARBY WD 03 PCT 02': 'DARBY TWP 3W 2P'}\n",
    "gdf.loc[((gdf.county_id == 'Delaware County') & gdf.edited_precinct_name.isin(['chester 3', 'chester 4', 'darby 3 1', 'darby 3 2'])), 'edited_precinct_name'] = gdf.original_precinct_name.map(delaware_corrections)\n",
    "\n",
    "\n",
    "# Philly\n",
    "df.loc[df.county_id == 'Philadelphia County', 'edited_precinct_name'] = df['original_precinct_name']\n",
    "def philly_precs(nm):\n",
    "    nm = nm.zfill(4)\n",
    "    return nm[0:2] + '~' + nm[2:]\n",
    "gdf.loc[gdf.county_id == 'Philadelphia County', 'edited_precinct_name'] = gdf[gdf.county_id == 'Philadelphia County']['edited_precinct_name'].map(philly_precs)\n",
    "\n",
    "# Fayette County\n",
    "gdf.loc[(gdf.VTDST == '000020') & (gdf.county_id == 'Fayette County'), 'edited_precinct_name'] = 'bvilletwp'\n",
    "\n",
    "# Tioga County\n",
    "df.loc[(df.county_id =='Tioga County') & (df.original_precinct_name =='Ward Twp'),'edited_precinct_name'] = 'Ward'\n",
    "\n",
    "# Bedford County\n",
    "bedford = pd.read_csv('county-files/Bedford County/split_polling_locations.csv')\n",
    "bedford['num'] = bedford.num.apply(lambda x: 'Precinct ' + str(x).zfill(4))\n",
    "number_to_name_bedford = pd.Series(bedford.name.values,index=bedford.num).to_dict()\n",
    "df.loc[df.county_id.isin(['Bedford County']), 'edited_precinct_name']= df[df.county_id.isin(['Bedford County'])].original_precinct_name.map(number_to_name_bedford)\n",
    "df.loc[df.county_id.isin(['Bedford County']), 'original_precinct_name']= df[df.county_id.isin(['Bedford County'])].original_precinct_name.map(number_to_name_bedford)\n",
    "\n",
    "gdf['original_precinct_name, edited_precinct_name'] = gdf[['original_precinct_name','edited_precinct_name']].apply(tuple, axis=1)\n",
    "df['original_precinct_name, edited_precinct_name'] = df[['original_precinct_name','edited_precinct_name']].apply(tuple, axis=1)"
   ]
  },
  {
   "source": [
    "#### Disolve Shapefile on `loc, prec`"
   ],
   "cell_type": "code",
   "metadata": {},
   "execution_count": 22,
   "outputs": []
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "gdf['loc, prec'] = gdf.apply(lambda row: row['county_id'] + ', ' + row['edited_precinct_name'], axis=1)\n",
    "gdf = gdf.dissolve(by='loc, prec', as_index=False)\n",
    "county_to_geoid = state_fip_to_county_to_geoid[42]\n",
    "gdf['COUNTYFP'] = gdf['county_id'].map(county_to_geoid)\n",
    "gdf = gdf.set_index('loc, prec')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Final validation for the election results before joining with the shapefile"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create 'loc, prec' to be used for the join \n",
    "df['loc, prec'] = df.apply(lambda row: row['county_id'] + ', ' + row['edited_precinct_name'], axis=1)\n",
    "\n",
    "# screen for duplicate names\n",
    "acceptable_duplicates_to_reason = {\n",
    "    'Clarion County, farmington':\"Multiple polling locations - same precinct\",\n",
    "    'Delaware County, springfield 2 3':\"combine 'emerg' results with normal results\",\n",
    "    'Delaware County, springfield 3 2 16':\"originally seperate because state leg distirct splits the precinct and thus they get different ballots. merging since we aren't including state leg resutls.\",\n",
    "    'Delaware County, upper darby 6 1':\"combine 'emerg' results with normal results\",\n",
    "    'Delaware County, upper darby 6 8':\"combine 'emerg' results with normal results\",\n",
    "    'Fayette County, brownsville':\"Fayette Board of elections said they don't have a shapefile with brownsville seperated into 'bvillew1', 'bvillew2', and 'bvillew3'.\",\n",
    "    'Fayette County, franklin':\"Fayette Board of elections said they don't have a shapefile with franklin seperated into 'franklind1' and 'franklind2'.\",\n",
    "    'Lancaster County, lancaster 7 8':\"Spoke with Diane who told me that 'lancaster 7 8' has two different school districts, but their ballots are the same other than school district related items. Therefore I combined their results under the single shapefiles.\",\n",
    "    'Mifflin County, brown':\"'brown reedsville big va', 'brown church hill' are contigous and County is unwilling to share shapefile so I am aggregating the results for 'brown reedsville big va' and 'brown church hill' under the 'brown' geometry.\",\n",
    "    'Monroe County, smithfield 1':\"Monroe County Shapefile from the county doesn't have a 'smithfield 4' precinct. The polling location for smithfield 1 and smithfield 4 is the same address, so I decided to just add the smithfield 4 results to smithfield 1.\",\n",
    "    'Northampton County, palmer upper western':\"Called 610-829-6260 and learned that 'NAZ IND' is only seperate for school district related elections. For Federal Elections, it is the same precinct as PALMER TOWNSHIP UPPER WEST DISTRICT EASTON. \",\n",
    "    'Washington County, peters 3':\"'petersxd 3' looks like a mis-scan of 'peter d-3' renamed it accordingly. \",\n",
    "}\n",
    "counts = df['loc, prec'].value_counts()\n",
    "duplicates = counts[(counts>1) & (~counts.index.isin(acceptable_duplicates_to_reason.keys()))]\n",
    "assert len(duplicates) == 0\n",
    "\n",
    "# remove aggregate 'loc, prec' into one row (summing the result)\n",
    "df = df.groupby(by='loc, prec').sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "assert set(gdf.index.unique()) == set(df.index.unique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Join the election results and shapefile on `loc, prec`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "(9150, 123)\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "                    loc, prec  index_left  G18DemGov  G18DemHOR  G18DemSen  \\\n",
       "0   Adams County, abbottstown           0      120.0      108.0      120.0   \n",
       "1  Adams County, arendtsville           1      160.0      132.0      151.0   \n",
       "\n",
       "   G18GreGov  G18GreHOR  G18GreSen  G18IndGov  G18IndHOR  ...  T16TREASR  \\\n",
       "0        2.0        0.0        2.0        0.0        0.0  ...        NaN   \n",
       "1        2.0        0.0        3.0        0.0        1.0  ...        NaN   \n",
       "\n",
       "   T16CONGD  T16CONGR  T16STSD  T16STSR  T16STHD  T16STHR  \\\n",
       "0       NaN       NaN      NaN      NaN      NaN      NaN   \n",
       "1       NaN       NaN      NaN      NaN      NaN      NaN   \n",
       "\n",
       "  edited_precinct_name  index_right  \\\n",
       "0          abbottstown          NaN   \n",
       "1         arendtsville          NaN   \n",
       "\n",
       "  original_precinct_name, edited_precinct_name  \n",
       "0                   (ABBOTTSTOWN, abbottstown)  \n",
       "1                 (ARENDTSVILLE, arendtsville)  \n",
       "\n",
       "[2 rows x 123 columns]"
      ],
      "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>loc, prec</th>\n      <th>index_left</th>\n      <th>G18DemGov</th>\n      <th>G18DemHOR</th>\n      <th>G18DemSen</th>\n      <th>G18GreGov</th>\n      <th>G18GreHOR</th>\n      <th>G18GreSen</th>\n      <th>G18IndGov</th>\n      <th>G18IndHOR</th>\n      <th>...</th>\n      <th>T16TREASR</th>\n      <th>T16CONGD</th>\n      <th>T16CONGR</th>\n      <th>T16STSD</th>\n      <th>T16STSR</th>\n      <th>T16STHD</th>\n      <th>T16STHR</th>\n      <th>edited_precinct_name</th>\n      <th>index_right</th>\n      <th>original_precinct_name, edited_precinct_name</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>Adams County, abbottstown</td>\n      <td>0</td>\n      <td>120.0</td>\n      <td>108.0</td>\n      <td>120.0</td>\n      <td>2.0</td>\n      <td>0.0</td>\n      <td>2.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>...</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      <td>abbottstown</td>\n      <td>NaN</td>\n      <td>(ABBOTTSTOWN, abbottstown)</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>Adams County, arendtsville</td>\n      <td>1</td>\n      <td>160.0</td>\n      <td>132.0</td>\n      <td>151.0</td>\n      <td>2.0</td>\n      <td>0.0</td>\n      <td>3.0</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>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>arendtsville</td>\n      <td>NaN</td>\n      <td>(ARENDTSVILLE, arendtsville)</td>\n    </tr>\n  </tbody>\n</table>\n<p>2 rows × 123 columns</p>\n</div>"
     },
     "metadata": {},
     "execution_count": 26
    }
   ],
   "source": [
    "joined_df = df.join(gdf, lsuffix='_left', rsuffix='_right').reset_index()\n",
    "print(joined_df.shape)\n",
    "joined_df.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Choose columns, check that there are no empty values, and save the file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "                    loc, prec  GEOID     county_id      precinct  G18DemSen  \\\n",
       "0   Adams County, abbottstown  42001  Adams County   abbottstown      120.0   \n",
       "1  Adams County, arendtsville  42001  Adams County  arendtsville      151.0   \n",
       "2  Adams County, bendersville  42001  Adams County  bendersville       74.0   \n",
       "3       Adams County, berwick  42001  Adams County       berwick      289.0   \n",
       "4   Adams County, biglerville  42001  Adams County   biglerville      152.0   \n",
       "\n",
       "   G18RepSen  G18LibSen  G18GreSen  G18IndSen  G18DemGov  G18RepGov  \\\n",
       "0      183.0        5.0        2.0        0.0      120.0      185.0   \n",
       "1      178.0        6.0        3.0        0.0      160.0      172.0   \n",
       "2      103.0        1.0        2.0        0.0       76.0       98.0   \n",
       "3      575.0       14.0        5.0        0.0      318.0      554.0   \n",
       "4      231.0        3.0        7.0        1.0      168.0      215.0   \n",
       "\n",
       "   G18LibGov  G18GreGov  G18IndGov  G18DemHOR  G18RepHOR  G18LibHOR  \\\n",
       "0        2.0        2.0        0.0      108.0      201.0        0.0   \n",
       "1        4.0        2.0        0.0      132.0      204.0        0.0   \n",
       "2        3.0        2.0        0.0       66.0      111.0        0.0   \n",
       "3        9.0        5.0        0.0      252.0      631.0        0.0   \n",
       "4        5.0        2.0        1.0      133.0      261.0        0.0   \n",
       "\n",
       "   G18GreHOR  G18IndHOR                                           geometry  \n",
       "0        0.0        0.0  POLYGON Z ((-76.99801 39.88359 0.00000, -76.99...  \n",
       "1        0.0        1.0  POLYGON Z ((-77.31141 39.92625 0.00000, -77.30...  \n",
       "2        0.0        0.0  POLYGON Z ((-77.25596 39.98075 0.00000, -77.25...  \n",
       "3        0.0        0.0  MULTIPOLYGON Z (((-77.02734 39.87105 0.00000, ...  \n",
       "4        0.0        0.0  POLYGON Z ((-77.25594 39.93043 0.00000, -77.25...  "
      ],
      "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>loc, prec</th>\n      <th>GEOID</th>\n      <th>county_id</th>\n      <th>precinct</th>\n      <th>G18DemSen</th>\n      <th>G18RepSen</th>\n      <th>G18LibSen</th>\n      <th>G18GreSen</th>\n      <th>G18IndSen</th>\n      <th>G18DemGov</th>\n      <th>G18RepGov</th>\n      <th>G18LibGov</th>\n      <th>G18GreGov</th>\n      <th>G18IndGov</th>\n      <th>G18DemHOR</th>\n      <th>G18RepHOR</th>\n      <th>G18LibHOR</th>\n      <th>G18GreHOR</th>\n      <th>G18IndHOR</th>\n      <th>geometry</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>Adams County, abbottstown</td>\n      <td>42001</td>\n      <td>Adams County</td>\n      <td>abbottstown</td>\n      <td>120.0</td>\n      <td>183.0</td>\n      <td>5.0</td>\n      <td>2.0</td>\n      <td>0.0</td>\n      <td>120.0</td>\n      <td>185.0</td>\n      <td>2.0</td>\n      <td>2.0</td>\n      <td>0.0</td>\n      <td>108.0</td>\n      <td>201.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>POLYGON Z ((-76.99801 39.88359 0.00000, -76.99...</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>Adams County, arendtsville</td>\n      <td>42001</td>\n      <td>Adams County</td>\n      <td>arendtsville</td>\n      <td>151.0</td>\n      <td>178.0</td>\n      <td>6.0</td>\n      <td>3.0</td>\n      <td>0.0</td>\n      <td>160.0</td>\n      <td>172.0</td>\n      <td>4.0</td>\n      <td>2.0</td>\n      <td>0.0</td>\n      <td>132.0</td>\n      <td>204.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>1.0</td>\n      <td>POLYGON Z ((-77.31141 39.92625 0.00000, -77.30...</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>Adams County, bendersville</td>\n      <td>42001</td>\n      <td>Adams County</td>\n      <td>bendersville</td>\n      <td>74.0</td>\n      <td>103.0</td>\n      <td>1.0</td>\n      <td>2.0</td>\n      <td>0.0</td>\n      <td>76.0</td>\n      <td>98.0</td>\n      <td>3.0</td>\n      <td>2.0</td>\n      <td>0.0</td>\n      <td>66.0</td>\n      <td>111.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>POLYGON Z ((-77.25596 39.98075 0.00000, -77.25...</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>Adams County, berwick</td>\n      <td>42001</td>\n      <td>Adams County</td>\n      <td>berwick</td>\n      <td>289.0</td>\n      <td>575.0</td>\n      <td>14.0</td>\n      <td>5.0</td>\n      <td>0.0</td>\n      <td>318.0</td>\n      <td>554.0</td>\n      <td>9.0</td>\n      <td>5.0</td>\n      <td>0.0</td>\n      <td>252.0</td>\n      <td>631.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>MULTIPOLYGON Z (((-77.02734 39.87105 0.00000, ...</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>Adams County, biglerville</td>\n      <td>42001</td>\n      <td>Adams County</td>\n      <td>biglerville</td>\n      <td>152.0</td>\n      <td>231.0</td>\n      <td>3.0</td>\n      <td>7.0</td>\n      <td>1.0</td>\n      <td>168.0</td>\n      <td>215.0</td>\n      <td>5.0</td>\n      <td>2.0</td>\n      <td>1.0</td>\n      <td>133.0</td>\n      <td>261.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>POLYGON Z ((-77.25594 39.93043 0.00000, -77.25...</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 27
    }
   ],
   "source": [
    "cols = ['loc, prec', 'COUNTYFP', 'county_id', 'edited_precinct_name',\n",
    " 'G18DemSen', 'G18RepSen', 'G18LibSen', 'G18GreSen', 'G18IndSen',\n",
    " 'G18DemGov', 'G18RepGov', 'G18LibGov', 'G18GreGov', 'G18IndGov', \n",
    " 'G18DemHOR', 'G18RepHOR', 'G18LibHOR','G18GreHOR', 'G18IndHOR', \n",
    " 'geometry']\n",
    "output = gpd.GeoDataFrame(joined_df[cols].rename(columns={'COUNTYFP':'GEOID', 'edited_precinct_name':'precinct'}))\n",
    "output.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "output.to_file('Pennsylvania 2018 General Election Shapefile')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.7.6 64-bit",
   "language": "python",
   "name": "python37664bit88461838a1dc4854b1993feddc66b2f9"
  },
  "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.6-final"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}