precinct_matching_framework.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Precinct Matching Framework"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import geopandas as gpd\n",
    "from pprint import pprint\n",
    "\n",
    "from op_verification.reference_data import (\n",
    "    geoid_to_county_name,\n",
    "    state_to_fips,\n",
    "    state_to_state_po,\n",
    "    state_abbreviation_to_state_name,\n",
    "    state_fip_to_county_to_geoid,\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Import the datasets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "county_id = '<$COUNTY_ID$>'\n",
    "\n",
    "county_results_filename = 'election_results_county_id={}.csv'.format(county_id)\n",
    "county_results_df = pd.read_csv(county_results_filename)\n",
    "county_shapefile_filename = 'shapefile_county_id={}'.format(county_id)\n",
    "county_shapefile_gdf = gpd.read_file(county_shapefile_filename)\n",
    "\n",
    "# correct for the truncation caused by 10 character column name limit in shapefiles\n",
    "county_shapefile_gdf.rename(columns={'original_p':'original_precinct_name'}, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The next cell aliases `county_results_df` as `df` and `county_shapefile_gdf` as `gdf` here because typing fewer characters allows for faster data exploration. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = county_results_df.copy()\n",
    "gdf = county_shapefile_gdf.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "gdf.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Check Preconditions\n",
    "These should all pass - they're here to ensure that everything in `setup.ipynb` worked correctly"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# TODO: Pass the precondition described above which takes the form of an assert statement in this cell.\n",
    "assert 'county_id' in df.columns and 'county_id' in gdf.columns\n",
    "assert 'original_precinct_name' in df.columns and 'original_precinct_name' in gdf.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### General Modifications\n",
    "Its normally benificial to apply some modifications uniformly to all precincts. For example, its good practice to make everything lower case. This modification is made in `edit_precinct_name` - read its specification to learn more about how to use it to make more modifications. Consider removing substrings that appear in every precinct name like `voting district`. Be careful of removing words that will result in duplicate precinct names. For example, if there are two `Newtown Voting District` precincts in the shapefile, and the election results have `Newtown Boro` and `Newtown Township`, its okay to remove `Voting District`, but you probably don't want to remove `Boro` or `Township`. Of course, this will differ from County to County - so be vigilant!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "dataset_name_df = 'df'\n",
    "dataset_name_gdf = 'gdf'\n",
    "\n",
    "def edit_precinct_name(prec_name, \n",
    "    remove_lst=[], \n",
    "    target_to_replacement={},\n",
    "    stopping_words=[],\n",
    "    prec_dict={}):\n",
    "    '''\n",
    "    Returns a lower case precinct name (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 ({str:str} dictionary): keys (targets) will be replaced with their \n",
    "            corresponding value (replacements) in prec_name. 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",
    "        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",
    "    for word in remove_lst:\n",
    "        prec_name = prec_name.replace(word, '')\n",
    "    for target, replacement in target_to_replacement.items():\n",
    "        prec_name = prec_name.replace(target, replacement)\n",
    "    words = prec_name.split()\n",
    "    words = [word.lstrip('0') for word in words if word not in stopping_words]\n",
    "    prec_name = \" \".join(words)\n",
    "    return prec_dict[prec_name] if prec_name in prec_dict.keys() else prec_name\n",
    "\n",
    "df_to_gdf = {\n",
    "}\n",
    "\n",
    "gdf_to_df = {\n",
    "}\n",
    "\n",
    "# Tune the matching by adding optional arguements to edit\n",
    "df['edited_precinct_name'] = df['original_precinct_name'].apply(lambda name: edit_precinct_name(name,prec_dict=df_to_gdf))\n",
    "gdf['edited_precinct_name'] = gdf['original_precinct_name'].apply(lambda name: edit_precinct_name(name,prec_dict=gdf_to_df))\n",
    "\n",
    "######## Manual Corrections ###########\n",
    "# Make precinct specific corrections here like splitting one precinct into two because of new congressional districts\n",
    "# e.g gdf.loc[gdf['VTDST']=='000290','edited_precinct_name'] = 'howard township'\n",
    "\n",
    "# make the 'original_precinct_name, edited_precinct_name' for use in the loop below\n",
    "df['original_precinct_name, edited_precinct_name'] = df[['original_precinct_name','edited_precinct_name']].apply(tuple, axis=1)\n",
    "gdf['original_precinct_name, edited_precinct_name'] = gdf[['original_precinct_name','edited_precinct_name']].apply(tuple, axis=1)\n",
    "\n",
    "######## Matching Framework ###########\n",
    "unmatched_precinct_lst_gdf = []\n",
    "unmatched_precinct_lst_df = []\n",
    "\n",
    "precinct_list_df = sorted(list(df[df['county_id'] == county_id]['original_precinct_name, edited_precinct_name'].unique()), key=lambda x: x[1])\n",
    "precinct_list_gdf = sorted(list(gdf[gdf['county_id'] == county_id]['original_precinct_name, edited_precinct_name'].unique()), key=lambda x: x[1])\n",
    "\n",
    "precinct_set_df = {x[1] for x in precinct_list_df if x[1] not in unmatched_precinct_lst_df}\n",
    "precinct_set_gdf = {x[1] for x in precinct_list_gdf if x[1] not in unmatched_precinct_lst_gdf}\n",
    "\n",
    "unmatched_precincts_df = sorted(list(precinct_set_df - precinct_set_gdf))\n",
    "unmatched_precincts_gdf = sorted(list(precinct_set_gdf - precinct_set_df))\n",
    "n_unmatched = len(unmatched_precincts_df) + len(unmatched_precincts_gdf)\n",
    "matching_is_done = False\n",
    "if n_unmatched > 0:\n",
    "        print(\"county_id: '{}' | {} precincts in {} | {} precincts in {}:\\n\".format(county_id, len(precinct_list_df), dataset_name_df, len(precinct_list_gdf), dataset_name_gdf))\n",
    "        n_precincts_total = len(precinct_list_df) + len(precinct_list_gdf)\n",
    "        print(n_unmatched, \" precincts are unmatched out of \", n_precincts_total)\n",
    "        df_unmatched = df[(df['edited_precinct_name'].isin(unmatched_precincts_df)) & (df.county_id == county_id)]\n",
    "        gdf_unmatched = gdf[(gdf['edited_precinct_name'].isin(unmatched_precincts_gdf)) & (gdf.county_id == county_id)]\n",
    "        if n_unmatched > 100:\n",
    "            print(\"\\nLook for parterns and use change the parameters to edit_precinct_name accordingly.\\n\")\n",
    "            for index, (original_precinct_name_df, edited_precinct_name_df) in enumerate(precinct_list_df):\n",
    "                original_precinct_name_gdf, edited_precinct_name_gdf = precinct_list_gdf[index]\n",
    "                if edited_precinct_name_df in unmatched_precincts_df and edited_precinct_name_gdf in unmatched_precincts_gdf:\n",
    "                    print(\"{} <-- {} ({})\".format(edited_precinct_name_df, original_precinct_name_df, dataset_name_df))\n",
    "                    print(\"{} <-- {} ({})\\n\".format(edited_precinct_name_gdf, original_precinct_name_gdf, dataset_name_gdf))\n",
    "        else:\n",
    "            print(\"unmatched_precincts_df ({}) - len = {}| '{}':\".format(dataset_name_df, len(unmatched_precincts_df), county_id), unmatched_precincts_df)\n",
    "            print(\"\\nunmatched_precincts_gdf ({}) - len = {}| '{}':\".format(dataset_name_gdf, len(unmatched_precincts_gdf), county_id), unmatched_precincts_gdf)\n",
    "            precinct_modification_dictionary_df_to_gdf = {unmatched_precincts_df[i]: unmatched_precincts_gdf[i] if i < len(unmatched_precincts_gdf) else '' for i in range(len(unmatched_precincts_df))}\n",
    "            precinct_modification_dicitonary_gdf_to_df = {unmatched_precincts_gdf[i]: unmatched_precincts_df[i] if i < len(unmatched_precincts_df) else '' for i in range(len(unmatched_precincts_gdf))}\n",
    "            print(\"{}  to {} precinct modification dictionary: \".format(dataset_name_df, dataset_name_gdf))\n",
    "            print(\"'{}':\".format(county_id))\n",
    "            pprint(precinct_modification_dictionary_df_to_gdf)\n",
    "            print(\"{}  to {} precinct modification dictionary: \".format(dataset_name_gdf, dataset_name_df))\n",
    "            print(\"'{}':\".format(county_id))\n",
    "            pprint(precinct_modification_dicitonary_gdf_to_df)\n",
    "            for index, (original_precinct_name_df, edited_precinct_name_df) in enumerate(precinct_list_df):\n",
    "                original_precinct_name_gdf, edited_precinct_name_gdf = precinct_list_gdf[index]\n",
    "                if edited_precinct_name_df in unmatched_precincts_df or edited_precinct_name_gdf in unmatched_precincts_gdf:\n",
    "                    print(\"{} <-- {} ({})\".format(edited_precinct_name_df, original_precinct_name_df, dataset_name_df))\n",
    "                    print(\"{} <-- {} ({})\\n\".format(edited_precinct_name_gdf, original_precinct_name_gdf, dataset_name_gdf))\n",
    "            else:\n",
    "                print(\"Add unmatched precincts to the unmatched precinct.\")\n",
    "else:\n",
    "    print(\"All Done! (make sure you have one to one matches)\")\n",
    "    matching_is_done = True"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Validate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "assert matching_is_done"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "acceptable_duplicates_to_reason_df = {\n",
    "}\n",
    "counts = df['edited_precinct_name'].value_counts()\n",
    "duplicates = counts[(counts>1) & (~counts.index.isin(acceptable_duplicates_to_reason_df.keys()))]\n",
    "assert len(duplicates) == 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "acceptable_duplicates_to_reason_gdf = {\n",
    "}\n",
    "counts = gdf['edited_precinct_name'].value_counts()\n",
    "duplicates = counts[(counts>1) & (~counts.index.isin(acceptable_duplicates_to_reason_gdf.keys()))]\n",
    "assert len(duplicates) == 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "49"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gdf.edited_precinct_name.nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "49"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gdf.original_precinct_name.nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "51"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.edited_precinct_name.nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "51"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.edited_precinct_name.nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df[~df['edited_precinct_name'].isin(unmatched_precinct_lst_df)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Export"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['edited_precinct_name'] = df['edited_precinct_name'].str.title()\n",
    "df['loc, prec'] = df.apply(lambda row: county_id + ', ' + row['edited_precinct_name'], axis=1)\n",
    "df = df.groupby(by='loc, prec').sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "gdf['edited_precinct_name'] = gdf['edited_precinct_name'].str.title()\n",
    "gdf['loc, prec'] = gdf.apply(lambda 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[1]\n",
    "gdf['GEOID'] = gdf['county_id'].map(county_to_geoid)\n",
    "gdf = gdf.set_index('loc, prec')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['loc, prec',\n",
       " 'GEOID',\n",
       " 'county_id',\n",
       " 'edited_precinct_name',\n",
       " 'Gov_DEM',\n",
       " 'Gov_REP',\n",
       " 'LtGov_DEM',\n",
       " 'LtGov_REP',\n",
       " 'StHOR_DEM',\n",
       " 'StHOR_IND',\n",
       " 'StHOR_LIB',\n",
       " 'StHOR_REP',\n",
       " 'StSen_DEM',\n",
       " 'StSen_IND',\n",
       " 'StSen_REP',\n",
       " 'SP_DEM',\n",
       " 'SP_LIB',\n",
       " 'SP_REP',\n",
       " 'USHOR_DEM',\n",
       " 'USHOR_REP',\n",
       " 'geometry']"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cols = [ 'loc, prec', 'GEOID', 'county_id', 'edited_precinct_name'] + list(df.columns) + ['geometry']\n",
    "cols"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(49, 38)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>loc, prec</th>\n",
       "      <th>Gov_DEM</th>\n",
       "      <th>Gov_REP</th>\n",
       "      <th>LtGov_DEM</th>\n",
       "      <th>LtGov_REP</th>\n",
       "      <th>StHOR_DEM</th>\n",
       "      <th>StHOR_IND</th>\n",
       "      <th>StHOR_LIB</th>\n",
       "      <th>StHOR_REP</th>\n",
       "      <th>StSen_DEM</th>\n",
       "      <th>...</th>\n",
       "      <th>NAME</th>\n",
       "      <th>VINTAGE</th>\n",
       "      <th>FUNCSTAT</th>\n",
       "      <th>JUSTIFY</th>\n",
       "      <th>MTFCC</th>\n",
       "      <th>county_id</th>\n",
       "      <th>original_precinct_name</th>\n",
       "      <th>edited_precinct_name</th>\n",
       "      <th>original_precinct_name, edited_precinct_name</th>\n",
       "      <th>GEOID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Baldwin County, Barnwell Vfd</td>\n",
       "      <td>418.0</td>\n",
       "      <td>1621.0</td>\n",
       "      <td>350.0</td>\n",
       "      <td>1670.0</td>\n",
       "      <td>421.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1605.0</td>\n",
       "      <td>365.0</td>\n",
       "      <td>...</td>\n",
       "      <td>Barnwell VFD</td>\n",
       "      <td>90</td>\n",
       "      <td>N</td>\n",
       "      <td>None</td>\n",
       "      <td>G5240</td>\n",
       "      <td>Baldwin County</td>\n",
       "      <td>Barnwell VFD</td>\n",
       "      <td>Barnwell Vfd</td>\n",
       "      <td>(Barnwell VFD, barnwell vfd)</td>\n",
       "      <td>01003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Baldwin County, Bay Minette City Hall / Civic Ctr</td>\n",
       "      <td>641.0</td>\n",
       "      <td>2046.0</td>\n",
       "      <td>622.0</td>\n",
       "      <td>2044.0</td>\n",
       "      <td>645.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1998.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>Bay Minette City Hall / Civic Ctr</td>\n",
       "      <td>90</td>\n",
       "      <td>N</td>\n",
       "      <td>None</td>\n",
       "      <td>G5240</td>\n",
       "      <td>Baldwin County</td>\n",
       "      <td>Bay Minette City Hall / Civic Ctr</td>\n",
       "      <td>Bay Minette City Hall / Civic Ctr</td>\n",
       "      <td>(Bay Minette City Hall / Civic Ctr, bay minett...</td>\n",
       "      <td>01003</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 38 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                           loc, prec  Gov_DEM  Gov_REP  \\\n",
       "0                       Baldwin County, Barnwell Vfd    418.0   1621.0   \n",
       "1  Baldwin County, Bay Minette City Hall / Civic Ctr    641.0   2046.0   \n",
       "\n",
       "   LtGov_DEM  LtGov_REP  StHOR_DEM  StHOR_IND  StHOR_LIB  StHOR_REP  \\\n",
       "0      350.0     1670.0      421.0        0.0        0.0     1605.0   \n",
       "1      622.0     2044.0      645.0        0.0        0.0     1998.0   \n",
       "\n",
       "   StSen_DEM  ...                               NAME  VINTAGE  FUNCSTAT  \\\n",
       "0      365.0  ...                       Barnwell VFD       90         N   \n",
       "1        0.0  ...  Bay Minette City Hall / Civic Ctr       90         N   \n",
       "\n",
       "   JUSTIFY  MTFCC       county_id             original_precinct_name  \\\n",
       "0     None  G5240  Baldwin County                       Barnwell VFD   \n",
       "1     None  G5240  Baldwin County  Bay Minette City Hall / Civic Ctr   \n",
       "\n",
       "                edited_precinct_name  \\\n",
       "0                       Barnwell Vfd   \n",
       "1  Bay Minette City Hall / Civic Ctr   \n",
       "\n",
       "        original_precinct_name, edited_precinct_name  GEOID  \n",
       "0                       (Barnwell VFD, barnwell vfd)  01003  \n",
       "1  (Bay Minette City Hall / Civic Ctr, bay minett...  01003  \n",
       "\n",
       "[2 rows x 38 columns]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "joined_df = df.join(gdf, lsuffix='_left', rsuffix='_right').reset_index()\n",
    "print(joined_df.shape)\n",
    "joined_df.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>loc, prec</th>\n",
       "      <th>GEOID</th>\n",
       "      <th>county_id</th>\n",
       "      <th>precinct</th>\n",
       "      <th>Gov_DEM</th>\n",
       "      <th>Gov_REP</th>\n",
       "      <th>LtGov_DEM</th>\n",
       "      <th>LtGov_REP</th>\n",
       "      <th>StHOR_DEM</th>\n",
       "      <th>StHOR_IND</th>\n",
       "      <th>...</th>\n",
       "      <th>StHOR_REP</th>\n",
       "      <th>StSen_DEM</th>\n",
       "      <th>StSen_IND</th>\n",
       "      <th>StSen_REP</th>\n",
       "      <th>SP_DEM</th>\n",
       "      <th>SP_LIB</th>\n",
       "      <th>SP_REP</th>\n",
       "      <th>USHOR_DEM</th>\n",
       "      <th>USHOR_REP</th>\n",
       "      <th>geometry</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Baldwin County, Barnwell Vfd</td>\n",
       "      <td>01003</td>\n",
       "      <td>Baldwin County</td>\n",
       "      <td>Barnwell Vfd</td>\n",
       "      <td>418.0</td>\n",
       "      <td>1621.0</td>\n",
       "      <td>350.0</td>\n",
       "      <td>1670.0</td>\n",
       "      <td>421.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1605.0</td>\n",
       "      <td>365.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1638.0</td>\n",
       "      <td>191.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1104.0</td>\n",
       "      <td>344.0</td>\n",
       "      <td>1690.0</td>\n",
       "      <td>POLYGON Z ((-88.01621 30.40448 0.00000, -88.01...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Baldwin County, Bay Minette City Hall / Civic Ctr</td>\n",
       "      <td>01003</td>\n",
       "      <td>Baldwin County</td>\n",
       "      <td>Bay Minette City Hall / Civic Ctr</td>\n",
       "      <td>641.0</td>\n",
       "      <td>2046.0</td>\n",
       "      <td>622.0</td>\n",
       "      <td>2044.0</td>\n",
       "      <td>645.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1998.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2116.0</td>\n",
       "      <td>396.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1335.0</td>\n",
       "      <td>597.0</td>\n",
       "      <td>2085.0</td>\n",
       "      <td>POLYGON Z ((-87.92361 30.93146 0.00000, -87.92...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Baldwin County, Belforest Community Ctr</td>\n",
       "      <td>01003</td>\n",
       "      <td>Baldwin County</td>\n",
       "      <td>Belforest Community Ctr</td>\n",
       "      <td>399.0</td>\n",
       "      <td>1453.0</td>\n",
       "      <td>346.0</td>\n",
       "      <td>1497.0</td>\n",
       "      <td>345.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1433.0</td>\n",
       "      <td>346.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1481.0</td>\n",
       "      <td>158.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>964.0</td>\n",
       "      <td>327.0</td>\n",
       "      <td>1521.0</td>\n",
       "      <td>POLYGON Z ((-87.88146 30.55826 0.00000, -87.88...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Baldwin County, Bethel Bapt Church</td>\n",
       "      <td>01003</td>\n",
       "      <td>Baldwin County</td>\n",
       "      <td>Bethel Bapt Church</td>\n",
       "      <td>78.0</td>\n",
       "      <td>588.0</td>\n",
       "      <td>63.0</td>\n",
       "      <td>599.0</td>\n",
       "      <td>69.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>590.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>605.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>414.0</td>\n",
       "      <td>63.0</td>\n",
       "      <td>602.0</td>\n",
       "      <td>POLYGON Z ((-87.67327 30.56642 0.00000, -87.67...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Baldwin County, Bon Secour Morgan'S Chapel Um ...</td>\n",
       "      <td>01003</td>\n",
       "      <td>Baldwin County</td>\n",
       "      <td>Bon Secour Morgan'S Chapel Um Church</td>\n",
       "      <td>233.0</td>\n",
       "      <td>898.0</td>\n",
       "      <td>221.0</td>\n",
       "      <td>910.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>936.0</td>\n",
       "      <td>211.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>912.0</td>\n",
       "      <td>126.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>670.0</td>\n",
       "      <td>214.0</td>\n",
       "      <td>915.0</td>\n",
       "      <td>POLYGON Z ((-88.02759 30.30582 0.00000, -88.02...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                           loc, prec  GEOID       county_id  \\\n",
       "0                       Baldwin County, Barnwell Vfd  01003  Baldwin County   \n",
       "1  Baldwin County, Bay Minette City Hall / Civic Ctr  01003  Baldwin County   \n",
       "2            Baldwin County, Belforest Community Ctr  01003  Baldwin County   \n",
       "3                 Baldwin County, Bethel Bapt Church  01003  Baldwin County   \n",
       "4  Baldwin County, Bon Secour Morgan'S Chapel Um ...  01003  Baldwin County   \n",
       "\n",
       "                               precinct  Gov_DEM  Gov_REP  LtGov_DEM  \\\n",
       "0                          Barnwell Vfd    418.0   1621.0      350.0   \n",
       "1     Bay Minette City Hall / Civic Ctr    641.0   2046.0      622.0   \n",
       "2               Belforest Community Ctr    399.0   1453.0      346.0   \n",
       "3                    Bethel Bapt Church     78.0    588.0       63.0   \n",
       "4  Bon Secour Morgan'S Chapel Um Church    233.0    898.0      221.0   \n",
       "\n",
       "   LtGov_REP  StHOR_DEM  StHOR_IND  ...  StHOR_REP  StSen_DEM  StSen_IND  \\\n",
       "0     1670.0      421.0        0.0  ...     1605.0      365.0        0.0   \n",
       "1     2044.0      645.0        0.0  ...     1998.0        0.0        0.0   \n",
       "2     1497.0      345.0        0.0  ...     1433.0      346.0        0.0   \n",
       "3      599.0       69.0        0.0  ...      590.0        0.0        0.0   \n",
       "4      910.0        0.0        0.0  ...      936.0      211.0        0.0   \n",
       "\n",
       "   StSen_REP  SP_DEM  SP_LIB  SP_REP  USHOR_DEM  USHOR_REP  \\\n",
       "0     1638.0   191.0     0.0  1104.0      344.0     1690.0   \n",
       "1     2116.0   396.0     0.0  1335.0      597.0     2085.0   \n",
       "2     1481.0   158.0     4.0   964.0      327.0     1521.0   \n",
       "3      605.0    33.0     0.0   414.0       63.0      602.0   \n",
       "4      912.0   126.0     0.0   670.0      214.0      915.0   \n",
       "\n",
       "                                            geometry  \n",
       "0  POLYGON Z ((-88.01621 30.40448 0.00000, -88.01...  \n",
       "1  POLYGON Z ((-87.92361 30.93146 0.00000, -87.92...  \n",
       "2  POLYGON Z ((-87.88146 30.55826 0.00000, -87.88...  \n",
       "3  POLYGON Z ((-87.67327 30.56642 0.00000, -87.67...  \n",
       "4  POLYGON Z ((-88.02759 30.30582 0.00000, -88.02...  \n",
       "\n",
       "[5 rows x 21 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "output = gpd.GeoDataFrame(joined_df[cols].rename(columns={'edited_precinct_name':'precinct'}))\n",
    "output.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "parent_dir = '../../data/shapefiles/county_level_precinct_election_results'\n",
    "output.to_file('/'.join([parent_dir, \"_\".join(county_id.split())]))"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}