Match 2018 NCSBE Senate Votes.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Match 2018 NCSBE Senate Votes\n",
    "\n",
    "Retrieve precinct-sorted 2018 general election results from [`2019-04-04 precinct_con_can_stats.zip`](https://dl.ncsbe.gov/index.html?prefix=ENRS/2018_11_06/precinct_sort/), match them to [parties from Ballotpedia 2018 races](https://docs.google.com/spreadsheets/d/1LmNoDfZH9lMtGh5kLwQG7Te7o3XugiarnK2zron72pE/edit#gid=0), and output [new 2018 North Carolina model data](https://docs.google.com/spreadsheets/d/1aMPAXJN7Km3fxglhHXPqsCl4TKGCE_JXooufT6KVom0/edit#gid=1429158271)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os; os.environ['DYLD_LIBRARY_PATH'] = './.venv-NC/lib'\n",
    "import pandas, editdistance, numpy, geopandas, re"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Import NCSBE Votes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>County</th>\n",
       "      <th>Election Date</th>\n",
       "      <th>Precinct</th>\n",
       "      <th>Contest Group ID</th>\n",
       "      <th>Contest Type</th>\n",
       "      <th>Contest Name</th>\n",
       "      <th>Choice</th>\n",
       "      <th>Choice Party</th>\n",
       "      <th>Vote For</th>\n",
       "      <th>Election Day</th>\n",
       "      <th>One Stop</th>\n",
       "      <th>Absentee by Mail</th>\n",
       "      <th>Provisional</th>\n",
       "      <th>Total Votes</th>\n",
       "      <th>Real Precinct</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>1462</td>\n",
       "      <td>BUNCOMBE</td>\n",
       "      <td>11/06/2018</td>\n",
       "      <td>07.1</td>\n",
       "      <td>1172</td>\n",
       "      <td>S</td>\n",
       "      <td>NC STATE SENATE DISTRICT 49</td>\n",
       "      <td>Terry Van Duyn</td>\n",
       "      <td>DEM</td>\n",
       "      <td>1</td>\n",
       "      <td>373</td>\n",
       "      <td>0</td>\n",
       "      <td>29</td>\n",
       "      <td>3</td>\n",
       "      <td>405</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1463</td>\n",
       "      <td>BUNCOMBE</td>\n",
       "      <td>11/06/2018</td>\n",
       "      <td>25.1</td>\n",
       "      <td>1172</td>\n",
       "      <td>S</td>\n",
       "      <td>NC STATE SENATE DISTRICT 49</td>\n",
       "      <td>Terry Van Duyn</td>\n",
       "      <td>DEM</td>\n",
       "      <td>1</td>\n",
       "      <td>299</td>\n",
       "      <td>0</td>\n",
       "      <td>23</td>\n",
       "      <td>1</td>\n",
       "      <td>323</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1464</td>\n",
       "      <td>BUNCOMBE</td>\n",
       "      <td>11/06/2018</td>\n",
       "      <td>64.1</td>\n",
       "      <td>1172</td>\n",
       "      <td>S</td>\n",
       "      <td>NC STATE SENATE DISTRICT 49</td>\n",
       "      <td>Terry Van Duyn</td>\n",
       "      <td>DEM</td>\n",
       "      <td>1</td>\n",
       "      <td>203</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>211</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1465</td>\n",
       "      <td>BUNCOMBE</td>\n",
       "      <td>11/06/2018</td>\n",
       "      <td>OS ECBL</td>\n",
       "      <td>1172</td>\n",
       "      <td>S</td>\n",
       "      <td>NC STATE SENATE DISTRICT 49</td>\n",
       "      <td>Terry Van Duyn</td>\n",
       "      <td>DEM</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3186</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3186</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1466</td>\n",
       "      <td>BUNCOMBE</td>\n",
       "      <td>11/06/2018</td>\n",
       "      <td>CURBSIDE</td>\n",
       "      <td>1172</td>\n",
       "      <td>S</td>\n",
       "      <td>NC STATE SENATE DISTRICT 49</td>\n",
       "      <td>Terry Van Duyn</td>\n",
       "      <td>DEM</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>182554</td>\n",
       "      <td>WASHINGTON</td>\n",
       "      <td>11/06/2018</td>\n",
       "      <td>P2</td>\n",
       "      <td>1124</td>\n",
       "      <td>S</td>\n",
       "      <td>NC STATE SENATE DISTRICT 01</td>\n",
       "      <td>D. Cole Phelps</td>\n",
       "      <td>DEM</td>\n",
       "      <td>1</td>\n",
       "      <td>174</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>174</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>182555</td>\n",
       "      <td>WASHINGTON</td>\n",
       "      <td>11/06/2018</td>\n",
       "      <td>SC</td>\n",
       "      <td>1124</td>\n",
       "      <td>S</td>\n",
       "      <td>NC STATE SENATE DISTRICT 01</td>\n",
       "      <td>D. Cole Phelps</td>\n",
       "      <td>DEM</td>\n",
       "      <td>1</td>\n",
       "      <td>206</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>206</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>182556</td>\n",
       "      <td>WASHINGTON</td>\n",
       "      <td>11/06/2018</td>\n",
       "      <td>LM</td>\n",
       "      <td>1124</td>\n",
       "      <td>S</td>\n",
       "      <td>NC STATE SENATE DISTRICT 01</td>\n",
       "      <td>D. Cole Phelps</td>\n",
       "      <td>DEM</td>\n",
       "      <td>1</td>\n",
       "      <td>342</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>342</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>182557</td>\n",
       "      <td>WASHINGTON</td>\n",
       "      <td>11/06/2018</td>\n",
       "      <td>SK</td>\n",
       "      <td>1124</td>\n",
       "      <td>S</td>\n",
       "      <td>NC STATE SENATE DISTRICT 01</td>\n",
       "      <td>D. Cole Phelps</td>\n",
       "      <td>DEM</td>\n",
       "      <td>1</td>\n",
       "      <td>126</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>126</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>182558</td>\n",
       "      <td>WASHINGTON</td>\n",
       "      <td>11/06/2018</td>\n",
       "      <td>P3</td>\n",
       "      <td>1124</td>\n",
       "      <td>S</td>\n",
       "      <td>NC STATE SENATE DISTRICT 01</td>\n",
       "      <td>D. Cole Phelps</td>\n",
       "      <td>DEM</td>\n",
       "      <td>1</td>\n",
       "      <td>249</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>249</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>7667 rows × 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            County Election Date  Precinct Contest Group ID Contest Type  \\\n",
       "1462      BUNCOMBE    11/06/2018      07.1             1172            S   \n",
       "1463      BUNCOMBE    11/06/2018      25.1             1172            S   \n",
       "1464      BUNCOMBE    11/06/2018      64.1             1172            S   \n",
       "1465      BUNCOMBE    11/06/2018   OS ECBL             1172            S   \n",
       "1466      BUNCOMBE    11/06/2018  CURBSIDE             1172            S   \n",
       "...            ...           ...       ...              ...          ...   \n",
       "182554  WASHINGTON    11/06/2018        P2             1124            S   \n",
       "182555  WASHINGTON    11/06/2018        SC             1124            S   \n",
       "182556  WASHINGTON    11/06/2018        LM             1124            S   \n",
       "182557  WASHINGTON    11/06/2018        SK             1124            S   \n",
       "182558  WASHINGTON    11/06/2018        P3             1124            S   \n",
       "\n",
       "                       Contest Name          Choice Choice Party Vote For  \\\n",
       "1462    NC STATE SENATE DISTRICT 49  Terry Van Duyn          DEM        1   \n",
       "1463    NC STATE SENATE DISTRICT 49  Terry Van Duyn          DEM        1   \n",
       "1464    NC STATE SENATE DISTRICT 49  Terry Van Duyn          DEM        1   \n",
       "1465    NC STATE SENATE DISTRICT 49  Terry Van Duyn          DEM        1   \n",
       "1466    NC STATE SENATE DISTRICT 49  Terry Van Duyn          DEM        1   \n",
       "...                             ...             ...          ...      ...   \n",
       "182554  NC STATE SENATE DISTRICT 01  D. Cole Phelps          DEM        1   \n",
       "182555  NC STATE SENATE DISTRICT 01  D. Cole Phelps          DEM        1   \n",
       "182556  NC STATE SENATE DISTRICT 01  D. Cole Phelps          DEM        1   \n",
       "182557  NC STATE SENATE DISTRICT 01  D. Cole Phelps          DEM        1   \n",
       "182558  NC STATE SENATE DISTRICT 01  D. Cole Phelps          DEM        1   \n",
       "\n",
       "       Election Day One Stop Absentee by Mail Provisional Total Votes  \\\n",
       "1462            373        0               29           3         405   \n",
       "1463            299        0               23           1         323   \n",
       "1464            203        0                7           1         211   \n",
       "1465              0     3186                0           0        3186   \n",
       "1466              0        0                0           0           0   \n",
       "...             ...      ...              ...         ...         ...   \n",
       "182554          174        0                0           0         174   \n",
       "182555          206        0                0           0         206   \n",
       "182556          342        0                0           0         342   \n",
       "182557          126        0                0           0         126   \n",
       "182558          249        0                0           0         249   \n",
       "\n",
       "       Real Precinct  \n",
       "1462               Y  \n",
       "1463               Y  \n",
       "1464               Y  \n",
       "1465               N  \n",
       "1466               N  \n",
       "...              ...  \n",
       "182554             Y  \n",
       "182555             Y  \n",
       "182556             Y  \n",
       "182557             Y  \n",
       "182558             Y  \n",
       "\n",
       "[7667 rows x 15 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_2018 = pandas.read_csv('results_pct_20181106.txt.gz', sep='\\t', dtype=str)\n",
    "df_2018sldu = df_2018[df_2018['Contest Name'].str.startswith('NC STATE SENATE DISTRICT ')]\n",
    "\n",
    "df_2016 = pandas.read_csv('precinct_sort_20161108.txt.gz', sep='\\t', dtype=str)\n",
    "df_2016uspres = df_2016[df_2016.contest_name == 'US PRESIDENT']\n",
    "\n",
    "df_2018sldu"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Import Ballotpedia Candidates\n",
    "\n",
    "Match candidate names between Ballotpedia and NCSBE in each senate district to later determine party votes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>State Senate District</th>\n",
       "      <th>Winning Party</th>\n",
       "      <th>Incumbent Party</th>\n",
       "      <th>Democrat</th>\n",
       "      <th>Republican</th>\n",
       "      <th>Other</th>\n",
       "      <th>Contest</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>District 1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>O</td>\n",
       "      <td>D. Cole Phelps</td>\n",
       "      <td>Bob Steinburg</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC STATE SENATE DISTRICT 01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>District 2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R</td>\n",
       "      <td>Ginger Garner</td>\n",
       "      <td>Norman Sanderson</td>\n",
       "      <td>Tim Harris (Libertarian Party)</td>\n",
       "      <td>NC STATE SENATE DISTRICT 02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>District 3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>D</td>\n",
       "      <td>Erica D. Smith</td>\n",
       "      <td>C. (Chuck) Earley, Jr.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC STATE SENATE DISTRICT 03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>District 4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>D</td>\n",
       "      <td>Milton F. (Toby) Fitch, Jr.</td>\n",
       "      <td>Richard Scott</td>\n",
       "      <td>Jesse Shearin (Libertarian Party)</td>\n",
       "      <td>NC STATE SENATE DISTRICT 04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>District 5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>D</td>\n",
       "      <td>Don Davis</td>\n",
       "      <td>Kimberly Robb</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC STATE SENATE DISTRICT 05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>District 6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R</td>\n",
       "      <td>Joseph (Joe) Webb</td>\n",
       "      <td>Harry Brown</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC STATE SENATE DISTRICT 06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>District 7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R</td>\n",
       "      <td>David B. Brantley</td>\n",
       "      <td>Louis Milford Pate, Jr.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC STATE SENATE DISTRICT 07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>District 8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R</td>\n",
       "      <td>David W. Sink, Jr.</td>\n",
       "      <td>Bill Rabon</td>\n",
       "      <td>Anthony Mascolo (Libertarian Party)</td>\n",
       "      <td>NC STATE SENATE DISTRICT 08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>District 9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R</td>\n",
       "      <td>Harper Peterson</td>\n",
       "      <td>Michael Lee</td>\n",
       "      <td>Ethan Bickley (Libertarian Party)</td>\n",
       "      <td>NC STATE SENATE DISTRICT 09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>District 10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R</td>\n",
       "      <td>Vernon R. Moore</td>\n",
       "      <td>Brent Jackson</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC STATE SENATE DISTRICT 10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>10</td>\n",
       "      <td>District 11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R</td>\n",
       "      <td>Albert R. Pacer</td>\n",
       "      <td>Rick Horner</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC STATE SENATE DISTRICT 11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>11</td>\n",
       "      <td>District 12</td>\n",
       "      <td>NaN</td>\n",
       "      <td>O</td>\n",
       "      <td>Jean Sivoli</td>\n",
       "      <td>Jim Burgin</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC STATE SENATE DISTRICT 12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>12</td>\n",
       "      <td>District 13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R</td>\n",
       "      <td>John Campbell</td>\n",
       "      <td>Danny Earl Britt, Jr.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC STATE SENATE DISTRICT 13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>13</td>\n",
       "      <td>District 14</td>\n",
       "      <td>NaN</td>\n",
       "      <td>D</td>\n",
       "      <td>Dan Blue</td>\n",
       "      <td>Sandy Andrews</td>\n",
       "      <td>Richard Haygood (Libertarian Party)</td>\n",
       "      <td>NC STATE SENATE DISTRICT 14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>14</td>\n",
       "      <td>District 15</td>\n",
       "      <td>NaN</td>\n",
       "      <td>D</td>\n",
       "      <td>Jay J. Chaudhuri</td>\n",
       "      <td>Alan David Michael</td>\n",
       "      <td>Brian Lewis (Libertarian Party)</td>\n",
       "      <td>NC STATE SENATE DISTRICT 15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>15</td>\n",
       "      <td>District 16</td>\n",
       "      <td>NaN</td>\n",
       "      <td>O</td>\n",
       "      <td>Wiley Nickel</td>\n",
       "      <td>Paul Smith</td>\n",
       "      <td>Brian Irving (Libertarian Party)</td>\n",
       "      <td>NC STATE SENATE DISTRICT 16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>16</td>\n",
       "      <td>District 17</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R</td>\n",
       "      <td>Sam Searcy</td>\n",
       "      <td>Tamara Barringer</td>\n",
       "      <td>Bruce Basson (Libertarian Party)</td>\n",
       "      <td>NC STATE SENATE DISTRICT 17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>17</td>\n",
       "      <td>District 18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R</td>\n",
       "      <td>Mack Paul</td>\n",
       "      <td>John Alexander</td>\n",
       "      <td>Brad Hessel (Libertarian Party)</td>\n",
       "      <td>NC STATE SENATE DISTRICT 18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>18</td>\n",
       "      <td>District 19</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R</td>\n",
       "      <td>Kirk DeViere</td>\n",
       "      <td>Wesley Meredith</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC STATE SENATE DISTRICT 19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>19</td>\n",
       "      <td>District 20</td>\n",
       "      <td>NaN</td>\n",
       "      <td>D</td>\n",
       "      <td>Floyd B. McKissick, Jr.</td>\n",
       "      <td>Tom Stark</td>\n",
       "      <td>Jared Erickson (Libertarian Party)</td>\n",
       "      <td>NC STATE SENATE DISTRICT 20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>20</td>\n",
       "      <td>District 21</td>\n",
       "      <td>NaN</td>\n",
       "      <td>D</td>\n",
       "      <td>Ben Clark</td>\n",
       "      <td>Timothy Leever</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC STATE SENATE DISTRICT 21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>21</td>\n",
       "      <td>District 22</td>\n",
       "      <td>NaN</td>\n",
       "      <td>D</td>\n",
       "      <td>Mike Woodard</td>\n",
       "      <td>Rickey (Rick) Padgett</td>\n",
       "      <td>Ray Ubinger (Libertarian Party)</td>\n",
       "      <td>NC STATE SENATE DISTRICT 22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>22</td>\n",
       "      <td>District 23</td>\n",
       "      <td>NaN</td>\n",
       "      <td>D</td>\n",
       "      <td>Valerie P. Foushee</td>\n",
       "      <td>Tom Glendinning</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC STATE SENATE DISTRICT 23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>23</td>\n",
       "      <td>District 24</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R</td>\n",
       "      <td>J. D. Wooten</td>\n",
       "      <td>Rick Gunn</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC STATE SENATE DISTRICT 24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>24</td>\n",
       "      <td>District 25</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R</td>\n",
       "      <td>Helen Probst Mills</td>\n",
       "      <td>Tom McInnis</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC STATE SENATE DISTRICT 25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>25</td>\n",
       "      <td>District 26</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R</td>\n",
       "      <td>William (Bill) McCaskill</td>\n",
       "      <td>Jerry W. Tillman</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC STATE SENATE DISTRICT 26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>26</td>\n",
       "      <td>District 27</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R</td>\n",
       "      <td>Michael Garrett</td>\n",
       "      <td>Trudy Wade</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC STATE SENATE DISTRICT 27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>27</td>\n",
       "      <td>District 28</td>\n",
       "      <td>NaN</td>\n",
       "      <td>D</td>\n",
       "      <td>Gladys Robinson</td>\n",
       "      <td>Clark Porter</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC STATE SENATE DISTRICT 28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>28</td>\n",
       "      <td>District 29</td>\n",
       "      <td>NaN</td>\n",
       "      <td>O</td>\n",
       "      <td>Cheraton Love</td>\n",
       "      <td>Eddie Gallimore</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC STATE SENATE DISTRICT 29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>29</td>\n",
       "      <td>District 30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R</td>\n",
       "      <td>Jen Mangrum</td>\n",
       "      <td>Jen Mangrum</td>\n",
       "      <td>R. Michael Jordan (Libertarian Party)</td>\n",
       "      <td>NC STATE SENATE DISTRICT 30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>30</td>\n",
       "      <td>District 31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R</td>\n",
       "      <td>John Motsinger, Jr.</td>\n",
       "      <td>Joyce Krawiec</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC STATE SENATE DISTRICT 31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>31</td>\n",
       "      <td>District 32</td>\n",
       "      <td>NaN</td>\n",
       "      <td>D</td>\n",
       "      <td>Paul Lowe, Jr.</td>\n",
       "      <td>Eric Henderson</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC STATE SENATE DISTRICT 32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>32</td>\n",
       "      <td>District 33</td>\n",
       "      <td>NaN</td>\n",
       "      <td>O</td>\n",
       "      <td>Arin Wilhelm</td>\n",
       "      <td>Carl Ford</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC STATE SENATE DISTRICT 33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>33</td>\n",
       "      <td>District 34</td>\n",
       "      <td>NaN</td>\n",
       "      <td>O</td>\n",
       "      <td>Beniah McMiller</td>\n",
       "      <td>Vickie Sawyer</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC STATE SENATE DISTRICT 34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>34</td>\n",
       "      <td>District 35</td>\n",
       "      <td>NaN</td>\n",
       "      <td>O</td>\n",
       "      <td>Caroline L. Walker</td>\n",
       "      <td>Todd Johnson</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC STATE SENATE DISTRICT 35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>35</td>\n",
       "      <td>District 36</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R</td>\n",
       "      <td>Mark E. Shelley</td>\n",
       "      <td>Paul R. Newton</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC STATE SENATE DISTRICT 36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>36</td>\n",
       "      <td>District 37</td>\n",
       "      <td>NaN</td>\n",
       "      <td>D</td>\n",
       "      <td>Jeff Jackson</td>\n",
       "      <td>Nora Trotman</td>\n",
       "      <td>Stuart Andrew Collins (Constitution Party)</td>\n",
       "      <td>NC STATE SENATE DISTRICT 37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>37</td>\n",
       "      <td>District 38</td>\n",
       "      <td>NaN</td>\n",
       "      <td>O</td>\n",
       "      <td>Mujtaba A. Mohammed</td>\n",
       "      <td>Richard Rivette</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC STATE SENATE DISTRICT 38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>38</td>\n",
       "      <td>District 39</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R</td>\n",
       "      <td>Chad Stachowicz</td>\n",
       "      <td>Dan Bishop</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC STATE SENATE DISTRICT 39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>39</td>\n",
       "      <td>District 40</td>\n",
       "      <td>NaN</td>\n",
       "      <td>D</td>\n",
       "      <td>Joyce Waddell</td>\n",
       "      <td>Mr. Bobbie Shields</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC STATE SENATE DISTRICT 40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>40</td>\n",
       "      <td>District 41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R</td>\n",
       "      <td>Natasha Marcus</td>\n",
       "      <td>Jeff Tarte</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC STATE SENATE DISTRICT 41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>41</td>\n",
       "      <td>District 42</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R</td>\n",
       "      <td>Ric Vandett</td>\n",
       "      <td>Andy Wells</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC STATE SENATE DISTRICT 42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>42</td>\n",
       "      <td>District 43</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R</td>\n",
       "      <td>Altriese Price</td>\n",
       "      <td>Kathy Harrington</td>\n",
       "      <td>Mitchell Bridges (Libertarian Party)</td>\n",
       "      <td>NC STATE SENATE DISTRICT 43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>43</td>\n",
       "      <td>District 44</td>\n",
       "      <td>NaN</td>\n",
       "      <td>O</td>\n",
       "      <td>David Lee Lattimore</td>\n",
       "      <td>Ted Alexander</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC STATE SENATE DISTRICT 44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>44</td>\n",
       "      <td>District 45</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R</td>\n",
       "      <td>Wes Luther</td>\n",
       "      <td>Deanna Ballard</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC STATE SENATE DISTRICT 45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>45</td>\n",
       "      <td>District 46</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R</td>\n",
       "      <td>Art Sherwood</td>\n",
       "      <td>Warren Daniel</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC STATE SENATE DISTRICT 46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>46</td>\n",
       "      <td>District 47</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R</td>\n",
       "      <td>David Wheeler</td>\n",
       "      <td>Ralph Hise</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC STATE SENATE DISTRICT 47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>47</td>\n",
       "      <td>District 48</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R</td>\n",
       "      <td>Norm Bossert</td>\n",
       "      <td>Chuck Edwards</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC STATE SENATE DISTRICT 48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>48</td>\n",
       "      <td>District 49</td>\n",
       "      <td>NaN</td>\n",
       "      <td>D</td>\n",
       "      <td>Terry Van Duyn</td>\n",
       "      <td>Mark Crawford</td>\n",
       "      <td>Lyndon John Smith (Libertarian Party)</td>\n",
       "      <td>NC STATE SENATE DISTRICT 49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>49</td>\n",
       "      <td>District 50</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R</td>\n",
       "      <td>Bobby Kuppers</td>\n",
       "      <td>Jim Davis</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC STATE SENATE DISTRICT 50</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   State Senate District  Winning Party Incumbent Party  \\\n",
       "0             District 1            NaN               O   \n",
       "1             District 2            NaN               R   \n",
       "2             District 3            NaN               D   \n",
       "3             District 4            NaN               D   \n",
       "4             District 5            NaN               D   \n",
       "5             District 6            NaN               R   \n",
       "6             District 7            NaN               R   \n",
       "7             District 8            NaN               R   \n",
       "8             District 9            NaN               R   \n",
       "9            District 10            NaN               R   \n",
       "10           District 11            NaN               R   \n",
       "11           District 12            NaN               O   \n",
       "12           District 13            NaN               R   \n",
       "13           District 14            NaN               D   \n",
       "14           District 15            NaN               D   \n",
       "15           District 16            NaN               O   \n",
       "16           District 17            NaN               R   \n",
       "17           District 18            NaN               R   \n",
       "18           District 19            NaN               R   \n",
       "19           District 20            NaN               D   \n",
       "20           District 21            NaN               D   \n",
       "21           District 22            NaN               D   \n",
       "22           District 23            NaN               D   \n",
       "23           District 24            NaN               R   \n",
       "24           District 25            NaN               R   \n",
       "25           District 26            NaN               R   \n",
       "26           District 27            NaN               R   \n",
       "27           District 28            NaN               D   \n",
       "28           District 29            NaN               O   \n",
       "29           District 30            NaN               R   \n",
       "30           District 31            NaN               R   \n",
       "31           District 32            NaN               D   \n",
       "32           District 33            NaN               O   \n",
       "33           District 34            NaN               O   \n",
       "34           District 35            NaN               O   \n",
       "35           District 36            NaN               R   \n",
       "36           District 37            NaN               D   \n",
       "37           District 38            NaN               O   \n",
       "38           District 39            NaN               R   \n",
       "39           District 40            NaN               D   \n",
       "40           District 41            NaN               R   \n",
       "41           District 42            NaN               R   \n",
       "42           District 43            NaN               R   \n",
       "43           District 44            NaN               O   \n",
       "44           District 45            NaN               R   \n",
       "45           District 46            NaN               R   \n",
       "46           District 47            NaN               R   \n",
       "47           District 48            NaN               R   \n",
       "48           District 49            NaN               D   \n",
       "49           District 50            NaN               R   \n",
       "\n",
       "                       Democrat               Republican  \\\n",
       "0                D. Cole Phelps            Bob Steinburg   \n",
       "1                 Ginger Garner         Norman Sanderson   \n",
       "2                Erica D. Smith   C. (Chuck) Earley, Jr.   \n",
       "3   Milton F. (Toby) Fitch, Jr.            Richard Scott   \n",
       "4                     Don Davis            Kimberly Robb   \n",
       "5             Joseph (Joe) Webb              Harry Brown   \n",
       "6             David B. Brantley  Louis Milford Pate, Jr.   \n",
       "7            David W. Sink, Jr.               Bill Rabon   \n",
       "8               Harper Peterson              Michael Lee   \n",
       "9               Vernon R. Moore            Brent Jackson   \n",
       "10              Albert R. Pacer              Rick Horner   \n",
       "11                  Jean Sivoli               Jim Burgin   \n",
       "12                John Campbell    Danny Earl Britt, Jr.   \n",
       "13                     Dan Blue            Sandy Andrews   \n",
       "14             Jay J. Chaudhuri       Alan David Michael   \n",
       "15                 Wiley Nickel               Paul Smith   \n",
       "16                   Sam Searcy         Tamara Barringer   \n",
       "17                    Mack Paul           John Alexander   \n",
       "18                 Kirk DeViere          Wesley Meredith   \n",
       "19      Floyd B. McKissick, Jr.                Tom Stark   \n",
       "20                    Ben Clark           Timothy Leever   \n",
       "21                 Mike Woodard    Rickey (Rick) Padgett   \n",
       "22           Valerie P. Foushee          Tom Glendinning   \n",
       "23                 J. D. Wooten                Rick Gunn   \n",
       "24           Helen Probst Mills              Tom McInnis   \n",
       "25     William (Bill) McCaskill         Jerry W. Tillman   \n",
       "26              Michael Garrett               Trudy Wade   \n",
       "27              Gladys Robinson             Clark Porter   \n",
       "28                Cheraton Love          Eddie Gallimore   \n",
       "29                  Jen Mangrum              Jen Mangrum   \n",
       "30          John Motsinger, Jr.            Joyce Krawiec   \n",
       "31               Paul Lowe, Jr.           Eric Henderson   \n",
       "32                 Arin Wilhelm                Carl Ford   \n",
       "33              Beniah McMiller            Vickie Sawyer   \n",
       "34           Caroline L. Walker             Todd Johnson   \n",
       "35              Mark E. Shelley           Paul R. Newton   \n",
       "36                 Jeff Jackson             Nora Trotman   \n",
       "37          Mujtaba A. Mohammed          Richard Rivette   \n",
       "38              Chad Stachowicz               Dan Bishop   \n",
       "39                Joyce Waddell       Mr. Bobbie Shields   \n",
       "40               Natasha Marcus               Jeff Tarte   \n",
       "41                  Ric Vandett               Andy Wells   \n",
       "42               Altriese Price         Kathy Harrington   \n",
       "43          David Lee Lattimore            Ted Alexander   \n",
       "44                   Wes Luther           Deanna Ballard   \n",
       "45                 Art Sherwood            Warren Daniel   \n",
       "46                David Wheeler               Ralph Hise   \n",
       "47                 Norm Bossert            Chuck Edwards   \n",
       "48               Terry Van Duyn            Mark Crawford   \n",
       "49                Bobby Kuppers                Jim Davis   \n",
       "\n",
       "                                         Other                      Contest  \n",
       "0                                          NaN  NC STATE SENATE DISTRICT 01  \n",
       "1               Tim Harris (Libertarian Party)  NC STATE SENATE DISTRICT 02  \n",
       "2                                          NaN  NC STATE SENATE DISTRICT 03  \n",
       "3            Jesse Shearin (Libertarian Party)  NC STATE SENATE DISTRICT 04  \n",
       "4                                          NaN  NC STATE SENATE DISTRICT 05  \n",
       "5                                          NaN  NC STATE SENATE DISTRICT 06  \n",
       "6                                          NaN  NC STATE SENATE DISTRICT 07  \n",
       "7          Anthony Mascolo (Libertarian Party)  NC STATE SENATE DISTRICT 08  \n",
       "8            Ethan Bickley (Libertarian Party)  NC STATE SENATE DISTRICT 09  \n",
       "9                                          NaN  NC STATE SENATE DISTRICT 10  \n",
       "10                                         NaN  NC STATE SENATE DISTRICT 11  \n",
       "11                                         NaN  NC STATE SENATE DISTRICT 12  \n",
       "12                                         NaN  NC STATE SENATE DISTRICT 13  \n",
       "13         Richard Haygood (Libertarian Party)  NC STATE SENATE DISTRICT 14  \n",
       "14             Brian Lewis (Libertarian Party)  NC STATE SENATE DISTRICT 15  \n",
       "15            Brian Irving (Libertarian Party)  NC STATE SENATE DISTRICT 16  \n",
       "16            Bruce Basson (Libertarian Party)  NC STATE SENATE DISTRICT 17  \n",
       "17             Brad Hessel (Libertarian Party)  NC STATE SENATE DISTRICT 18  \n",
       "18                                         NaN  NC STATE SENATE DISTRICT 19  \n",
       "19          Jared Erickson (Libertarian Party)  NC STATE SENATE DISTRICT 20  \n",
       "20                                         NaN  NC STATE SENATE DISTRICT 21  \n",
       "21             Ray Ubinger (Libertarian Party)  NC STATE SENATE DISTRICT 22  \n",
       "22                                         NaN  NC STATE SENATE DISTRICT 23  \n",
       "23                                         NaN  NC STATE SENATE DISTRICT 24  \n",
       "24                                         NaN  NC STATE SENATE DISTRICT 25  \n",
       "25                                         NaN  NC STATE SENATE DISTRICT 26  \n",
       "26                                         NaN  NC STATE SENATE DISTRICT 27  \n",
       "27                                         NaN  NC STATE SENATE DISTRICT 28  \n",
       "28                                         NaN  NC STATE SENATE DISTRICT 29  \n",
       "29       R. Michael Jordan (Libertarian Party)  NC STATE SENATE DISTRICT 30  \n",
       "30                                         NaN  NC STATE SENATE DISTRICT 31  \n",
       "31                                         NaN  NC STATE SENATE DISTRICT 32  \n",
       "32                                         NaN  NC STATE SENATE DISTRICT 33  \n",
       "33                                         NaN  NC STATE SENATE DISTRICT 34  \n",
       "34                                         NaN  NC STATE SENATE DISTRICT 35  \n",
       "35                                         NaN  NC STATE SENATE DISTRICT 36  \n",
       "36  Stuart Andrew Collins (Constitution Party)  NC STATE SENATE DISTRICT 37  \n",
       "37                                         NaN  NC STATE SENATE DISTRICT 38  \n",
       "38                                         NaN  NC STATE SENATE DISTRICT 39  \n",
       "39                                         NaN  NC STATE SENATE DISTRICT 40  \n",
       "40                                         NaN  NC STATE SENATE DISTRICT 41  \n",
       "41                                         NaN  NC STATE SENATE DISTRICT 42  \n",
       "42        Mitchell Bridges (Libertarian Party)  NC STATE SENATE DISTRICT 43  \n",
       "43                                         NaN  NC STATE SENATE DISTRICT 44  \n",
       "44                                         NaN  NC STATE SENATE DISTRICT 45  \n",
       "45                                         NaN  NC STATE SENATE DISTRICT 46  \n",
       "46                                         NaN  NC STATE SENATE DISTRICT 47  \n",
       "47                                         NaN  NC STATE SENATE DISTRICT 48  \n",
       "48       Lyndon John Smith (Libertarian Party)  NC STATE SENATE DISTRICT 49  \n",
       "49                                         NaN  NC STATE SENATE DISTRICT 50  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_candidates = pandas.read_csv('North Carolina State Lege Candidates - 2018 Senate.csv')\n",
    "\n",
    "def closest_name(name, names):\n",
    "    if name in (numpy.nan, 'No candidate'):\n",
    "        return None\n",
    "    distances = sorted([(editdistance.distance(n, str(name)), n) for n in names])\n",
    "    return distances[0][1]\n",
    "\n",
    "DEMs, REPs, contests = list(), list(), list()\n",
    "\n",
    "for (index, row) in df_candidates.iterrows():\n",
    "    contest_name = 'NC STATE SENATE DISTRICT {:02d}'.format(int(row['State Senate District'][9:]))\n",
    "    names = set(df_2018sldu[df_2018sldu['Contest Name'] == contest_name].Choice)\n",
    "    DEMs.append(closest_name(row.Democrat, names))\n",
    "    REPs.append(closest_name(row.Republican, names))\n",
    "    contests.append(contest_name)\n",
    "\n",
    "df_candidates['Democrat'], df_candidates['Republican'], df_candidates['Contest'] = DEMs, REPs, contests\n",
    "\n",
    "df_candidates"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Count Precinct Party Votes\n",
    "\n",
    "Create a new `df_2018new` DataFrame with vote counts for State representatives and U.S. President by party."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(6369, 9)\n",
      "SLDU Votes: 1856678 1797125\n",
      "US Pres Votes: 2126510 2321481\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>county_desc</th>\n",
       "      <th>precinct_code</th>\n",
       "      <th>district</th>\n",
       "      <th>incumbent</th>\n",
       "      <th>winner</th>\n",
       "      <th>sldu_votes_D</th>\n",
       "      <th>sldu_votes_R</th>\n",
       "      <th>uspres_votes_D</th>\n",
       "      <th>uspres_votes_R</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>ALAMANCE</td>\n",
       "      <td>01</td>\n",
       "      <td>24</td>\n",
       "      <td>R</td>\n",
       "      <td>NaN</td>\n",
       "      <td>358</td>\n",
       "      <td>1462</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>ALAMANCE</td>\n",
       "      <td>02</td>\n",
       "      <td>24</td>\n",
       "      <td>R</td>\n",
       "      <td>NaN</td>\n",
       "      <td>409</td>\n",
       "      <td>1572</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>ALAMANCE</td>\n",
       "      <td>035</td>\n",
       "      <td>24</td>\n",
       "      <td>R</td>\n",
       "      <td>NaN</td>\n",
       "      <td>835</td>\n",
       "      <td>1167</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>ALAMANCE</td>\n",
       "      <td>03C</td>\n",
       "      <td>24</td>\n",
       "      <td>R</td>\n",
       "      <td>NaN</td>\n",
       "      <td>631</td>\n",
       "      <td>1012</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>ALAMANCE</td>\n",
       "      <td>03N</td>\n",
       "      <td>24</td>\n",
       "      <td>R</td>\n",
       "      <td>NaN</td>\n",
       "      <td>763</td>\n",
       "      <td>963</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6364</td>\n",
       "      <td>YANCEY</td>\n",
       "      <td>07 BRU</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>91</td>\n",
       "      <td>163</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6365</td>\n",
       "      <td>YANCEY</td>\n",
       "      <td>08 CRA</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>522</td>\n",
       "      <td>1260</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6366</td>\n",
       "      <td>YANCEY</td>\n",
       "      <td>09 SOU</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>596</td>\n",
       "      <td>766</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6367</td>\n",
       "      <td>YANCEY</td>\n",
       "      <td>10 PEN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>106</td>\n",
       "      <td>270</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6368</td>\n",
       "      <td>YANCEY</td>\n",
       "      <td>11 PRI</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>198</td>\n",
       "      <td>639</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6369 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     county_desc precinct_code district incumbent  winner  sldu_votes_D  \\\n",
       "0       ALAMANCE            01       24         R     NaN           358   \n",
       "1       ALAMANCE            02       24         R     NaN           409   \n",
       "2       ALAMANCE           035       24         R     NaN           835   \n",
       "3       ALAMANCE           03C       24         R     NaN           631   \n",
       "4       ALAMANCE           03N       24         R     NaN           763   \n",
       "...          ...           ...      ...       ...     ...           ...   \n",
       "6364      YANCEY        07 BRU     None      None     NaN             0   \n",
       "6365      YANCEY        08 CRA     None      None     NaN             0   \n",
       "6366      YANCEY        09 SOU     None      None     NaN             0   \n",
       "6367      YANCEY        10 PEN     None      None     NaN             0   \n",
       "6368      YANCEY        11 PRI     None      None     NaN             0   \n",
       "\n",
       "      sldu_votes_R  uspres_votes_D  uspres_votes_R  \n",
       "0             1462               0               0  \n",
       "1             1572               0               0  \n",
       "2             1167               0               0  \n",
       "3             1012               0               0  \n",
       "4              963               0               0  \n",
       "...            ...             ...             ...  \n",
       "6364             0              91             163  \n",
       "6365             0             522            1260  \n",
       "6366             0             596             766  \n",
       "6367             0             106             270  \n",
       "6368             0             198             639  \n",
       "\n",
       "[6369 rows x 9 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "arrays = dict(county_desc=list(), precinct_code=list(), district=list(),\n",
    "              incumbent=list(), winner=list(), sldu_votes_D=list(), sldu_votes_R=list(),\n",
    "              uspres_votes_D=list(), uspres_votes_R=list())\n",
    "\n",
    "groups1 = df_2018sldu.groupby(['County', 'Precinct', 'Contest Name']).groups\n",
    "\n",
    "for (County, Precinct, Contest_Name) in groups1.keys():\n",
    "    _df1 = df_2018sldu[df_2018sldu.County == County]\n",
    "    _df2 = _df1[_df1.Precinct == Precinct]\n",
    "    subdf_2018sldu = _df2[_df2['Contest Name'] == Contest_Name]\n",
    "    \n",
    "    arrays['county_desc'].append(County)\n",
    "    arrays['precinct_code'].append(Precinct)\n",
    "    \n",
    "    row_candidates = df_candidates[df_candidates.Contest == Contest_Name].iloc[0]\n",
    "    arrays['district'].append(row_candidates['State Senate District'][9:])\n",
    "    arrays['incumbent'].append(row_candidates['Incumbent Party'])\n",
    "    arrays['winner'].append(row_candidates['Winning Party'])\n",
    "    \n",
    "    try:\n",
    "        DEM_name = row_candidates.Democrat\n",
    "        DEM_row = subdf_2018sldu[subdf_2018sldu.Choice == DEM_name].iloc[0]\n",
    "    except IndexError:\n",
    "        arrays['sldu_votes_D'].append(0)\n",
    "    else:\n",
    "        arrays['sldu_votes_D'].append(int(DEM_row['Total Votes']))\n",
    "            \n",
    "    try:\n",
    "        REP_name = row_candidates.Republican\n",
    "        REP_row = subdf_2018sldu[subdf_2018sldu.Choice == REP_name].iloc[0]\n",
    "    except IndexError:\n",
    "        arrays['sldu_votes_R'].append(0)\n",
    "    else:\n",
    "        arrays['sldu_votes_R'].append(int(REP_row['Total Votes']))\n",
    "    \n",
    "    arrays['uspres_votes_D'].append(0)\n",
    "    arrays['uspres_votes_R'].append(0)\n",
    "    \n",
    "\n",
    "groups2 = df_2018sldu.groupby(['County', 'Precinct']).groups\n",
    "\n",
    "for (County, Precinct) in groups2.keys():\n",
    "    _df1 = df_2016uspres[df_2016uspres.county_desc == County]\n",
    "    subdf_2016uspres = _df1[_df1.precinct_code == Precinct]\n",
    "    \n",
    "    arrays['county_desc'].append(County)\n",
    "    arrays['precinct_code'].append(Precinct)\n",
    "    \n",
    "    arrays['district'].append(None)\n",
    "    arrays['incumbent'].append(None)\n",
    "    arrays['winner'].append(None)\n",
    "    \n",
    "    arrays['sldu_votes_D'].append(0)\n",
    "    arrays['sldu_votes_R'].append(0)\n",
    "    \n",
    "    try:\n",
    "        DEM_row = subdf_2016uspres[subdf_2016uspres.candidate_name == 'Hillary Clinton'].iloc[0]\n",
    "    except IndexError:\n",
    "        arrays['uspres_votes_D'].append(0)\n",
    "    else:\n",
    "        arrays['uspres_votes_D'].append(int(DEM_row.votes))\n",
    "            \n",
    "    try:\n",
    "        REP_row = subdf_2016uspres[subdf_2016uspres.candidate_name == 'Donald J. Trump'].iloc[0]\n",
    "    except IndexError:\n",
    "        arrays['uspres_votes_R'].append(0)\n",
    "    else:\n",
    "        arrays['uspres_votes_R'].append(int(REP_row.votes))\n",
    "    \n",
    "\n",
    "df_2018new = pandas.DataFrame(arrays)\n",
    "print(df_2018new.shape)\n",
    "print('SLDU Votes:', df_2018new.sldu_votes_D.sum(), df_2018new.sldu_votes_R.sum())\n",
    "print('US Pres Votes:', df_2018new.uspres_votes_D.sum(), df_2018new.uspres_votes_R.sum())\n",
    "df_2018new"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Assign Precinct Geography PSIDs\n",
    "\n",
    "Read precinct PSID values from `geogs_2018.shp`, merge by county name and precinct ID."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>county_desc</th>\n",
       "      <th>precinct_code</th>\n",
       "      <th>district</th>\n",
       "      <th>incumbent</th>\n",
       "      <th>winner</th>\n",
       "      <th>sldu_votes_D</th>\n",
       "      <th>sldu_votes_R</th>\n",
       "      <th>uspres_votes_D</th>\n",
       "      <th>uspres_votes_R</th>\n",
       "      <th>PREC_ID</th>\n",
       "      <th>...</th>\n",
       "      <th>G18GHOR</th>\n",
       "      <th>G18LStHOR</th>\n",
       "      <th>G18LStSEN</th>\n",
       "      <th>G18LHOR</th>\n",
       "      <th>G18RStHOR</th>\n",
       "      <th>G18RStSEN</th>\n",
       "      <th>G18RHOR</th>\n",
       "      <th>G18UnaHOR</th>\n",
       "      <th>PSID</th>\n",
       "      <th>geometry</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>ALAMANCE</td>\n",
       "      <td>01</td>\n",
       "      <td>24</td>\n",
       "      <td>R</td>\n",
       "      <td>NaN</td>\n",
       "      <td>358</td>\n",
       "      <td>1462</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>01</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1474.0</td>\n",
       "      <td>1462.0</td>\n",
       "      <td>1475.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>PSID:1158854937</td>\n",
       "      <td>POLYGON ((-79.43197308657012 35.88929191508174...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>ALAMANCE</td>\n",
       "      <td>02</td>\n",
       "      <td>24</td>\n",
       "      <td>R</td>\n",
       "      <td>NaN</td>\n",
       "      <td>409</td>\n",
       "      <td>1572</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>02</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1589.0</td>\n",
       "      <td>1572.0</td>\n",
       "      <td>1619.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>PSID:1158848961</td>\n",
       "      <td>POLYGON ((-79.53483899133799 36.04382100018476...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>ALAMANCE</td>\n",
       "      <td>035</td>\n",
       "      <td>24</td>\n",
       "      <td>R</td>\n",
       "      <td>NaN</td>\n",
       "      <td>835</td>\n",
       "      <td>1167</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>035</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1159.0</td>\n",
       "      <td>1167.0</td>\n",
       "      <td>1185.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>PSID:1158850573</td>\n",
       "      <td>POLYGON ((-79.50051299120325 36.15426500013934...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>ALAMANCE</td>\n",
       "      <td>03C</td>\n",
       "      <td>24</td>\n",
       "      <td>R</td>\n",
       "      <td>NaN</td>\n",
       "      <td>631</td>\n",
       "      <td>1012</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>03C</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1002.0</td>\n",
       "      <td>1012.0</td>\n",
       "      <td>1034.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>PSID:1158854377</td>\n",
       "      <td>POLYGON ((-79.52032399117273 36.08219700003401...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>ALAMANCE</td>\n",
       "      <td>03N</td>\n",
       "      <td>24</td>\n",
       "      <td>R</td>\n",
       "      <td>NaN</td>\n",
       "      <td>763</td>\n",
       "      <td>963</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>03N</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>964.0</td>\n",
       "      <td>963.0</td>\n",
       "      <td>979.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>PSID:1158852503</td>\n",
       "      <td>POLYGON ((-79.49082799096276 36.10568999961696...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6364</td>\n",
       "      <td>YANCEY</td>\n",
       "      <td>07 BRU</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>91</td>\n",
       "      <td>163</td>\n",
       "      <td>07 BRU</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>143.0</td>\n",
       "      <td>135.0</td>\n",
       "      <td>145.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>PSID:1158854169</td>\n",
       "      <td>POLYGON ((-82.19135299166359 35.97501800047963...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6365</td>\n",
       "      <td>YANCEY</td>\n",
       "      <td>08 CRA</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>522</td>\n",
       "      <td>1260</td>\n",
       "      <td>08 CRA</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>1096.0</td>\n",
       "      <td>997.0</td>\n",
       "      <td>1131.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>PSID:1158852683</td>\n",
       "      <td>POLYGON ((-82.18617999165116 35.94352800006673...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6366</td>\n",
       "      <td>YANCEY</td>\n",
       "      <td>09 SOU</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>596</td>\n",
       "      <td>766</td>\n",
       "      <td>09 SOU</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>626.0</td>\n",
       "      <td>604.0</td>\n",
       "      <td>633.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>PSID:1158854577</td>\n",
       "      <td>POLYGON ((-82.22187599103364 35.86516199983036...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6367</td>\n",
       "      <td>YANCEY</td>\n",
       "      <td>10 PEN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>106</td>\n",
       "      <td>270</td>\n",
       "      <td>10 PEN</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>225.0</td>\n",
       "      <td>221.0</td>\n",
       "      <td>232.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>PSID:1158853255</td>\n",
       "      <td>POLYGON ((-82.26803599172993 35.76373800026749...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6368</td>\n",
       "      <td>YANCEY</td>\n",
       "      <td>11 PRI</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>198</td>\n",
       "      <td>639</td>\n",
       "      <td>11 PRI</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>557.0</td>\n",
       "      <td>516.0</td>\n",
       "      <td>566.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>PSID:1158852881</td>\n",
       "      <td>POLYGON ((-82.35773699085409 35.91027400047732...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6369 rows × 33 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     county_desc precinct_code district incumbent  winner  sldu_votes_D  \\\n",
       "0       ALAMANCE            01       24         R     NaN           358   \n",
       "1       ALAMANCE            02       24         R     NaN           409   \n",
       "2       ALAMANCE           035       24         R     NaN           835   \n",
       "3       ALAMANCE           03C       24         R     NaN           631   \n",
       "4       ALAMANCE           03N       24         R     NaN           763   \n",
       "...          ...           ...      ...       ...     ...           ...   \n",
       "6364      YANCEY        07 BRU     None      None     NaN             0   \n",
       "6365      YANCEY        08 CRA     None      None     NaN             0   \n",
       "6366      YANCEY        09 SOU     None      None     NaN             0   \n",
       "6367      YANCEY        10 PEN     None      None     NaN             0   \n",
       "6368      YANCEY        11 PRI     None      None     NaN             0   \n",
       "\n",
       "      sldu_votes_R  uspres_votes_D  uspres_votes_R PREC_ID  ... G18GHOR  \\\n",
       "0             1462               0               0      01  ...     0.0   \n",
       "1             1572               0               0      02  ...     0.0   \n",
       "2             1167               0               0     035  ...     0.0   \n",
       "3             1012               0               0     03C  ...     0.0   \n",
       "4              963               0               0     03N  ...     0.0   \n",
       "...            ...             ...             ...     ...  ...     ...   \n",
       "6364             0              91             163  07 BRU  ...     0.0   \n",
       "6365             0             522            1260  08 CRA  ...     0.0   \n",
       "6366             0             596             766  09 SOU  ...     0.0   \n",
       "6367             0             106             270  10 PEN  ...     0.0   \n",
       "6368             0             198             639  11 PRI  ...     0.0   \n",
       "\n",
       "     G18LStHOR G18LStSEN  G18LHOR G18RStHOR G18RStSEN  G18RHOR  G18UnaHOR  \\\n",
       "0          0.0       0.0      0.0    1474.0    1462.0   1475.0        0.0   \n",
       "1          0.0       0.0      0.0    1589.0    1572.0   1619.0        0.0   \n",
       "2          0.0       0.0      0.0    1159.0    1167.0   1185.0        0.0   \n",
       "3          0.0       0.0      0.0    1002.0    1012.0   1034.0        0.0   \n",
       "4          0.0       0.0      0.0     964.0     963.0    979.0        0.0   \n",
       "...        ...       ...      ...       ...       ...      ...        ...   \n",
       "6364       0.0       0.0      4.0     143.0     135.0    145.0        0.0   \n",
       "6365       0.0       0.0     26.0    1096.0     997.0   1131.0        0.0   \n",
       "6366       0.0       0.0     21.0     626.0     604.0    633.0        0.0   \n",
       "6367       0.0       0.0      7.0     225.0     221.0    232.0        0.0   \n",
       "6368       0.0       0.0     16.0     557.0     516.0    566.0        0.0   \n",
       "\n",
       "                 PSID                                           geometry  \n",
       "0     PSID:1158854937  POLYGON ((-79.43197308657012 35.88929191508174...  \n",
       "1     PSID:1158848961  POLYGON ((-79.53483899133799 36.04382100018476...  \n",
       "2     PSID:1158850573  POLYGON ((-79.50051299120325 36.15426500013934...  \n",
       "3     PSID:1158854377  POLYGON ((-79.52032399117273 36.08219700003401...  \n",
       "4     PSID:1158852503  POLYGON ((-79.49082799096276 36.10568999961696...  \n",
       "...               ...                                                ...  \n",
       "6364  PSID:1158854169  POLYGON ((-82.19135299166359 35.97501800047963...  \n",
       "6365  PSID:1158852683  POLYGON ((-82.18617999165116 35.94352800006673...  \n",
       "6366  PSID:1158854577  POLYGON ((-82.22187599103364 35.86516199983036...  \n",
       "6367  PSID:1158853255  POLYGON ((-82.26803599172993 35.76373800026749...  \n",
       "6368  PSID:1158852881  POLYGON ((-82.35773699085409 35.91027400047732...  \n",
       "\n",
       "[6369 rows x 33 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "geogs_2018 = geopandas.read_file('geogs_2018.shp', layer='geogs_2018')\n",
    "\n",
    "geogs_2018.PSID = geogs_2018.PSID.str.replace(re.compile(r'^'), 'PSID:')\n",
    "\n",
    "df_2018out = df_2018new.merge(geogs_2018, how='left',\n",
    "                              left_on=('county_desc', 'precinct_code'),\n",
    "                              right_on=('COUNTY_NAM', 'PREC_ID'))\n",
    "\n",
    "df_2018out"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Group Votes by Precinct\n",
    "\n",
    "Add up all votes, grouping by precinct and concatenating multiple districts and incumbents."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>psid</th>\n",
       "      <th>district</th>\n",
       "      <th>winner</th>\n",
       "      <th>incumbent</th>\n",
       "      <th>sldu_votes_D</th>\n",
       "      <th>sldu_votes_R</th>\n",
       "      <th>uspres_votes_D</th>\n",
       "      <th>uspres_votes_R</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>county_desc</th>\n",
       "      <th>precinct_code</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td rowspan=\"5\" valign=\"top\">ALAMANCE</td>\n",
       "      <td>01</td>\n",
       "      <td>0</td>\n",
       "      <td>PSID:1158854937</td>\n",
       "      <td>24</td>\n",
       "      <td>nan</td>\n",
       "      <td>R</td>\n",
       "      <td>358</td>\n",
       "      <td>1462</td>\n",
       "      <td>411</td>\n",
       "      <td>1865</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>02</td>\n",
       "      <td>0</td>\n",
       "      <td>PSID:1158848961</td>\n",
       "      <td>24</td>\n",
       "      <td>nan</td>\n",
       "      <td>R</td>\n",
       "      <td>409</td>\n",
       "      <td>1572</td>\n",
       "      <td>403</td>\n",
       "      <td>2004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>035</td>\n",
       "      <td>0</td>\n",
       "      <td>PSID:1158850573</td>\n",
       "      <td>24</td>\n",
       "      <td>nan</td>\n",
       "      <td>R</td>\n",
       "      <td>835</td>\n",
       "      <td>1167</td>\n",
       "      <td>995</td>\n",
       "      <td>1485</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>03C</td>\n",
       "      <td>0</td>\n",
       "      <td>PSID:1158854377</td>\n",
       "      <td>24</td>\n",
       "      <td>nan</td>\n",
       "      <td>R</td>\n",
       "      <td>631</td>\n",
       "      <td>1012</td>\n",
       "      <td>679</td>\n",
       "      <td>1059</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>03N</td>\n",
       "      <td>0</td>\n",
       "      <td>PSID:1158852503</td>\n",
       "      <td>24</td>\n",
       "      <td>nan</td>\n",
       "      <td>R</td>\n",
       "      <td>763</td>\n",
       "      <td>963</td>\n",
       "      <td>1036</td>\n",
       "      <td>1160</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td rowspan=\"5\" valign=\"top\">YANCEY</td>\n",
       "      <td>07 BRU</td>\n",
       "      <td>0</td>\n",
       "      <td>PSID:1158854169</td>\n",
       "      <td>47</td>\n",
       "      <td>nan</td>\n",
       "      <td>R</td>\n",
       "      <td>112</td>\n",
       "      <td>135</td>\n",
       "      <td>91</td>\n",
       "      <td>163</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>08 CRA</td>\n",
       "      <td>0</td>\n",
       "      <td>PSID:1158852683</td>\n",
       "      <td>47</td>\n",
       "      <td>nan</td>\n",
       "      <td>R</td>\n",
       "      <td>667</td>\n",
       "      <td>997</td>\n",
       "      <td>522</td>\n",
       "      <td>1260</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>09 SOU</td>\n",
       "      <td>0</td>\n",
       "      <td>PSID:1158854577</td>\n",
       "      <td>47</td>\n",
       "      <td>nan</td>\n",
       "      <td>R</td>\n",
       "      <td>649</td>\n",
       "      <td>604</td>\n",
       "      <td>596</td>\n",
       "      <td>766</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>10 PEN</td>\n",
       "      <td>0</td>\n",
       "      <td>PSID:1158853255</td>\n",
       "      <td>47</td>\n",
       "      <td>nan</td>\n",
       "      <td>R</td>\n",
       "      <td>130</td>\n",
       "      <td>221</td>\n",
       "      <td>106</td>\n",
       "      <td>270</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>11 PRI</td>\n",
       "      <td>0</td>\n",
       "      <td>PSID:1158852881</td>\n",
       "      <td>47</td>\n",
       "      <td>nan</td>\n",
       "      <td>R</td>\n",
       "      <td>283</td>\n",
       "      <td>516</td>\n",
       "      <td>198</td>\n",
       "      <td>639</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3052 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        psid district winner incumbent  \\\n",
       "county_desc precinct_code                                                \n",
       "ALAMANCE    01            0  PSID:1158854937       24    nan         R   \n",
       "            02            0  PSID:1158848961       24    nan         R   \n",
       "            035           0  PSID:1158850573       24    nan         R   \n",
       "            03C           0  PSID:1158854377       24    nan         R   \n",
       "            03N           0  PSID:1158852503       24    nan         R   \n",
       "...                                      ...      ...    ...       ...   \n",
       "YANCEY      07 BRU        0  PSID:1158854169       47    nan         R   \n",
       "            08 CRA        0  PSID:1158852683       47    nan         R   \n",
       "            09 SOU        0  PSID:1158854577       47    nan         R   \n",
       "            10 PEN        0  PSID:1158853255       47    nan         R   \n",
       "            11 PRI        0  PSID:1158852881       47    nan         R   \n",
       "\n",
       "                             sldu_votes_D  sldu_votes_R  uspres_votes_D  \\\n",
       "county_desc precinct_code                                                 \n",
       "ALAMANCE    01            0           358          1462             411   \n",
       "            02            0           409          1572             403   \n",
       "            035           0           835          1167             995   \n",
       "            03C           0           631          1012             679   \n",
       "            03N           0           763           963            1036   \n",
       "...                                   ...           ...             ...   \n",
       "YANCEY      07 BRU        0           112           135              91   \n",
       "            08 CRA        0           667           997             522   \n",
       "            09 SOU        0           649           604             596   \n",
       "            10 PEN        0           130           221             106   \n",
       "            11 PRI        0           283           516             198   \n",
       "\n",
       "                             uspres_votes_R  \n",
       "county_desc precinct_code                    \n",
       "ALAMANCE    01            0            1865  \n",
       "            02            0            2004  \n",
       "            035           0            1485  \n",
       "            03C           0            1059  \n",
       "            03N           0            1160  \n",
       "...                                     ...  \n",
       "YANCEY      07 BRU        0             163  \n",
       "            08 CRA        0            1260  \n",
       "            09 SOU        0             766  \n",
       "            10 PEN        0             270  \n",
       "            11 PRI        0             639  \n",
       "\n",
       "[3052 rows x 8 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped = df_2018out.groupby(['county_desc', 'precinct_code'])\n",
    "\n",
    "def semicolon(series):\n",
    "    return ';'.join({str(v) for v in series.values if v and v is not numpy.nan})\n",
    "\n",
    "def doit(df):\n",
    "    #print(df)\n",
    "    return pandas.DataFrame({\n",
    "        'psid': [semicolon(df.PSID)],\n",
    "        'district': [semicolon(df.district)],\n",
    "        'winner': [semicolon(df.winner)],\n",
    "        'incumbent': [semicolon(df.incumbent)],\n",
    "        'sldu_votes_D': [df.sldu_votes_D.sum()],\n",
    "        'sldu_votes_R': [df.sldu_votes_R.sum()],\n",
    "        'uspres_votes_D': [df.uspres_votes_D.sum()],\n",
    "        'uspres_votes_R': [df.uspres_votes_R.sum()],\n",
    "    })\n",
    "\n",
    "df_2018final = grouped.apply(doit)\n",
    "\n",
    "df_2018final"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_2018final.to_csv('df_2018-senate.csv')"
   ]
  }
 ],
 "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.6.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}