Match 2016 NCSBE Senate Votes.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Match 2016 NCSBE Senate Votes\n",
    "\n",
    "Retrieve precinct-sorted 2016 general election results from [`precinct_sort_20161108.zip`](https://dl.ncsbe.gov/index.html?prefix=ENRS/2016_11_08/precinct%20sort/), match them to [parties from Ballotpedia 2016 races](https://docs.google.com/spreadsheets/d/1LmNoDfZH9lMtGh5kLwQG7Te7o3XugiarnK2zron72pE/edit#gid=1587907766), and output [new 2016 North Carolina model data](https://docs.google.com/spreadsheets/d/1aMPAXJN7Km3fxglhHXPqsCl4TKGCE_JXooufT6KVom0/edit#gid=684088115)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "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": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>county_id</th>\n",
       "      <th>county_desc</th>\n",
       "      <th>precinct_code</th>\n",
       "      <th>precinct_desc</th>\n",
       "      <th>contest_name</th>\n",
       "      <th>vote_for</th>\n",
       "      <th>candidate_name</th>\n",
       "      <th>votes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>92</td>\n",
       "      <td>1</td>\n",
       "      <td>ALAMANCE</td>\n",
       "      <td>01</td>\n",
       "      <td>PATTERSON</td>\n",
       "      <td>NC STATE SENATE DISTRICT 24</td>\n",
       "      <td>1</td>\n",
       "      <td>John Thorpe</td>\n",
       "      <td>474</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>93</td>\n",
       "      <td>1</td>\n",
       "      <td>ALAMANCE</td>\n",
       "      <td>01</td>\n",
       "      <td>PATTERSON</td>\n",
       "      <td>NC STATE SENATE DISTRICT 24</td>\n",
       "      <td>1</td>\n",
       "      <td>OVER VOTES</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>94</td>\n",
       "      <td>1</td>\n",
       "      <td>ALAMANCE</td>\n",
       "      <td>01</td>\n",
       "      <td>PATTERSON</td>\n",
       "      <td>NC STATE SENATE DISTRICT 24</td>\n",
       "      <td>1</td>\n",
       "      <td>Rick Gunn</td>\n",
       "      <td>1826</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>95</td>\n",
       "      <td>1</td>\n",
       "      <td>ALAMANCE</td>\n",
       "      <td>01</td>\n",
       "      <td>PATTERSON</td>\n",
       "      <td>NC STATE SENATE DISTRICT 24</td>\n",
       "      <td>1</td>\n",
       "      <td>UNDER VOTES</td>\n",
       "      <td>67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>222</td>\n",
       "      <td>1</td>\n",
       "      <td>ALAMANCE</td>\n",
       "      <td>02</td>\n",
       "      <td>COBLE</td>\n",
       "      <td>NC STATE SENATE DISTRICT 24</td>\n",
       "      <td>1</td>\n",
       "      <td>John Thorpe</td>\n",
       "      <td>436</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>371496</td>\n",
       "      <td>100</td>\n",
       "      <td>YANCEY</td>\n",
       "      <td>10 PEN</td>\n",
       "      <td>PENSACOLA</td>\n",
       "      <td>NC STATE SENATE DISTRICT 47</td>\n",
       "      <td>1</td>\n",
       "      <td>UNDER VOTES</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>371618</td>\n",
       "      <td>100</td>\n",
       "      <td>YANCEY</td>\n",
       "      <td>11 PRI</td>\n",
       "      <td>PRICES CREEK</td>\n",
       "      <td>NC STATE SENATE DISTRICT 47</td>\n",
       "      <td>1</td>\n",
       "      <td>Mary Jane Boyd</td>\n",
       "      <td>293</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>371619</td>\n",
       "      <td>100</td>\n",
       "      <td>YANCEY</td>\n",
       "      <td>11 PRI</td>\n",
       "      <td>PRICES CREEK</td>\n",
       "      <td>NC STATE SENATE DISTRICT 47</td>\n",
       "      <td>1</td>\n",
       "      <td>OVER VOTES</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>371620</td>\n",
       "      <td>100</td>\n",
       "      <td>YANCEY</td>\n",
       "      <td>11 PRI</td>\n",
       "      <td>PRICES CREEK</td>\n",
       "      <td>NC STATE SENATE DISTRICT 47</td>\n",
       "      <td>1</td>\n",
       "      <td>Ralph Hise</td>\n",
       "      <td>566</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>371621</td>\n",
       "      <td>100</td>\n",
       "      <td>YANCEY</td>\n",
       "      <td>11 PRI</td>\n",
       "      <td>PRICES CREEK</td>\n",
       "      <td>NC STATE SENATE DISTRICT 47</td>\n",
       "      <td>1</td>\n",
       "      <td>UNDER VOTES</td>\n",
       "      <td>38</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>11146 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       county_id county_desc precinct_code precinct_desc  \\\n",
       "92             1    ALAMANCE            01     PATTERSON   \n",
       "93             1    ALAMANCE            01     PATTERSON   \n",
       "94             1    ALAMANCE            01     PATTERSON   \n",
       "95             1    ALAMANCE            01     PATTERSON   \n",
       "222            1    ALAMANCE            02         COBLE   \n",
       "...          ...         ...           ...           ...   \n",
       "371496       100      YANCEY        10 PEN     PENSACOLA   \n",
       "371618       100      YANCEY        11 PRI  PRICES CREEK   \n",
       "371619       100      YANCEY        11 PRI  PRICES CREEK   \n",
       "371620       100      YANCEY        11 PRI  PRICES CREEK   \n",
       "371621       100      YANCEY        11 PRI  PRICES CREEK   \n",
       "\n",
       "                       contest_name vote_for  candidate_name votes  \n",
       "92      NC STATE SENATE DISTRICT 24        1     John Thorpe   474  \n",
       "93      NC STATE SENATE DISTRICT 24        1      OVER VOTES     0  \n",
       "94      NC STATE SENATE DISTRICT 24        1       Rick Gunn  1826  \n",
       "95      NC STATE SENATE DISTRICT 24        1     UNDER VOTES    67  \n",
       "222     NC STATE SENATE DISTRICT 24        1     John Thorpe   436  \n",
       "...                             ...      ...             ...   ...  \n",
       "371496  NC STATE SENATE DISTRICT 47        1     UNDER VOTES    22  \n",
       "371618  NC STATE SENATE DISTRICT 47        1  Mary Jane Boyd   293  \n",
       "371619  NC STATE SENATE DISTRICT 47        1      OVER VOTES     0  \n",
       "371620  NC STATE SENATE DISTRICT 47        1      Ralph Hise   566  \n",
       "371621  NC STATE SENATE DISTRICT 47        1     UNDER VOTES    38  \n",
       "\n",
       "[11146 rows x 8 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_2016 = pandas.read_csv('precinct_sort_20161108.txt.gz', sep='\\t', dtype=str)\n",
    "df_2016sldu = df_2016[df_2016.contest_name.str.startswith('NC STATE SENATE DISTRICT ')]\n",
    "df_2016uspres = df_2016[df_2016.contest_name == 'US PRESIDENT']\n",
    "\n",
    "df_2016sldu"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Import Ballotpedia Candidates\n",
    "\n",
    "Match candidate names between Ballotpedia and NCSBE in each house district to later determine party votes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R</td>\n",
       "      <td>Brownie Futrell</td>\n",
       "      <td>Bill Cook</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC STATE SENATE DISTRICT 01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R</td>\n",
       "      <td>Dorothea E. White</td>\n",
       "      <td>Norman Sanderson</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC STATE SENATE DISTRICT 02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>D</td>\n",
       "      <td>Erica Smith-Ingram</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC STATE SENATE DISTRICT 03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>D</td>\n",
       "      <td>Angela R. Bryant</td>\n",
       "      <td>Richard Scott</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC STATE SENATE DISTRICT 04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>D</td>\n",
       "      <td>Don Davis</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC STATE SENATE DISTRICT 05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R</td>\n",
       "      <td>None</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>7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R</td>\n",
       "      <td>None</td>\n",
       "      <td>Louis M. 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>8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R</td>\n",
       "      <td>None</td>\n",
       "      <td>Bill Rabon</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC STATE SENATE DISTRICT 08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R</td>\n",
       "      <td>Andrew Barnhill</td>\n",
       "      <td>Michael Lee</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC STATE SENATE DISTRICT 09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R</td>\n",
       "      <td>None</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>11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>O</td>\n",
       "      <td>Albert 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>12</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R</td>\n",
       "      <td>Susan Byerly</td>\n",
       "      <td>Ronald Rabin</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC STATE SENATE DISTRICT 12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>12</td>\n",
       "      <td>13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>D</td>\n",
       "      <td>Jane W. Smith</td>\n",
       "      <td>Danny Earl (J. R.) Britt</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC STATE SENATE DISTRICT 13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>13</td>\n",
       "      <td>14</td>\n",
       "      <td>NaN</td>\n",
       "      <td>D</td>\n",
       "      <td>Dan Blue</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC STATE SENATE DISTRICT 14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>14</td>\n",
       "      <td>15</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R</td>\n",
       "      <td>Laurel Deegan-Fricke</td>\n",
       "      <td>John Alexander</td>\n",
       "      <td>Brad Hessel: 5,081 (L)</td>\n",
       "      <td>NC STATE SENATE DISTRICT 15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>15</td>\n",
       "      <td>16</td>\n",
       "      <td>NaN</td>\n",
       "      <td>D</td>\n",
       "      <td>Jay Chaudhuri</td>\n",
       "      <td>Eric Weaver</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC STATE SENATE DISTRICT 16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>16</td>\n",
       "      <td>17</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R</td>\n",
       "      <td>Susan P. Evans</td>\n",
       "      <td>Tamara Barringer</td>\n",
       "      <td>Susan Hogarth: 5,191 (L)</td>\n",
       "      <td>NC STATE SENATE DISTRICT 17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>17</td>\n",
       "      <td>18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R</td>\n",
       "      <td>Gil Johnson</td>\n",
       "      <td>Chad Barefoot</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC STATE SENATE DISTRICT 18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>18</td>\n",
       "      <td>19</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R</td>\n",
       "      <td>Toni Morris</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>20</td>\n",
       "      <td>NaN</td>\n",
       "      <td>D</td>\n",
       "      <td>Floyd B. McKissick, Jr.</td>\n",
       "      <td>None</td>\n",
       "      <td>Barbara Howe: 16,202 (L)</td>\n",
       "      <td>NC STATE SENATE DISTRICT 20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>20</td>\n",
       "      <td>21</td>\n",
       "      <td>NaN</td>\n",
       "      <td>D</td>\n",
       "      <td>Ben Clark</td>\n",
       "      <td>Dan Travieso</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC STATE SENATE DISTRICT 21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>21</td>\n",
       "      <td>22</td>\n",
       "      <td>NaN</td>\n",
       "      <td>D</td>\n",
       "      <td>Mike Woodard</td>\n",
       "      <td>T. Greg Doucette</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC STATE SENATE DISTRICT 22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>22</td>\n",
       "      <td>23</td>\n",
       "      <td>NaN</td>\n",
       "      <td>D</td>\n",
       "      <td>Valerie P. Foushee</td>\n",
       "      <td>Mary Lopez Carter</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC STATE SENATE DISTRICT 23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>23</td>\n",
       "      <td>24</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R</td>\n",
       "      <td>John Thorpe</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>25</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R</td>\n",
       "      <td>Dannie M. Montgomery</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>26</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R</td>\n",
       "      <td>None</td>\n",
       "      <td>OVER VOTES</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC STATE SENATE DISTRICT 26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>26</td>\n",
       "      <td>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>28</td>\n",
       "      <td>NaN</td>\n",
       "      <td>D</td>\n",
       "      <td>Gladys A. Robinson</td>\n",
       "      <td>Devin R. King</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC STATE SENATE DISTRICT 28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>28</td>\n",
       "      <td>29</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R</td>\n",
       "      <td>None</td>\n",
       "      <td>Jerry W. Tillman</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC STATE SENATE DISTRICT 29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>29</td>\n",
       "      <td>30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R</td>\n",
       "      <td>Michael W. Holleman</td>\n",
       "      <td>Shirley Blackburn Randleman</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC STATE SENATE DISTRICT 30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>30</td>\n",
       "      <td>31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R</td>\n",
       "      <td>None</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>32</td>\n",
       "      <td>NaN</td>\n",
       "      <td>D</td>\n",
       "      <td>Paul Lowe, Jr.</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC STATE SENATE DISTRICT 32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>32</td>\n",
       "      <td>33</td>\n",
       "      <td>NaN</td>\n",
       "      <td>O</td>\n",
       "      <td>Jim Beall Graham</td>\n",
       "      <td>Cathy Dunn</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC STATE SENATE DISTRICT 33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>33</td>\n",
       "      <td>34</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R</td>\n",
       "      <td>None</td>\n",
       "      <td>Andrew C. Brock</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC STATE SENATE DISTRICT 34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>34</td>\n",
       "      <td>35</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R</td>\n",
       "      <td>None</td>\n",
       "      <td>Tommy Tucker</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC STATE SENATE DISTRICT 35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>35</td>\n",
       "      <td>36</td>\n",
       "      <td>NaN</td>\n",
       "      <td>O</td>\n",
       "      <td>Robert Brown</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>37</td>\n",
       "      <td>NaN</td>\n",
       "      <td>D</td>\n",
       "      <td>Jeff Jackson</td>\n",
       "      <td>Bob Diamond</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC STATE SENATE DISTRICT 37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>37</td>\n",
       "      <td>38</td>\n",
       "      <td>NaN</td>\n",
       "      <td>D</td>\n",
       "      <td>Joel Ford</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>39</td>\n",
       "      <td>NaN</td>\n",
       "      <td>O</td>\n",
       "      <td>Lloyd Scher</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>40</td>\n",
       "      <td>NaN</td>\n",
       "      <td>D</td>\n",
       "      <td>Joyce Waddell</td>\n",
       "      <td>Marguerite Cooke</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC STATE SENATE DISTRICT 40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>40</td>\n",
       "      <td>41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R</td>\n",
       "      <td>Jonathan Hudson</td>\n",
       "      <td>Jeff Tarte</td>\n",
       "      <td>Chris Cole: 4,938 (L)</td>\n",
       "      <td>NC STATE SENATE DISTRICT 41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>41</td>\n",
       "      <td>42</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R</td>\n",
       "      <td>None</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>43</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R</td>\n",
       "      <td>None</td>\n",
       "      <td>Kathy Harrington</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC STATE SENATE DISTRICT 43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>43</td>\n",
       "      <td>44</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R</td>\n",
       "      <td>None</td>\n",
       "      <td>David L. Curtis</td>\n",
       "      <td>Nic Haag: 20,881 (L)</td>\n",
       "      <td>NC STATE SENATE DISTRICT 44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>44</td>\n",
       "      <td>45</td>\n",
       "      <td>NaN</td>\n",
       "      <td>O</td>\n",
       "      <td>Art Sherwood</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>46</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R</td>\n",
       "      <td>Anne Fischer</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>47</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R</td>\n",
       "      <td>Mary Jane Boyd</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>48</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R</td>\n",
       "      <td>Norman 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>49</td>\n",
       "      <td>NaN</td>\n",
       "      <td>D</td>\n",
       "      <td>Terry Van Duyn</td>\n",
       "      <td>None</td>\n",
       "      <td>William Meredith: 24,672 (L)</td>\n",
       "      <td>NC STATE SENATE DISTRICT 49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>49</td>\n",
       "      <td>50</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R</td>\n",
       "      <td>Jane Hipps</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                       1            NaN               R   \n",
       "1                       2            NaN               R   \n",
       "2                       3            NaN               D   \n",
       "3                       4            NaN               D   \n",
       "4                       5            NaN               D   \n",
       "5                       6            NaN               R   \n",
       "6                       7            NaN               R   \n",
       "7                       8            NaN               R   \n",
       "8                       9            NaN               R   \n",
       "9                      10            NaN               R   \n",
       "10                     11            NaN               O   \n",
       "11                     12            NaN               R   \n",
       "12                     13            NaN               D   \n",
       "13                     14            NaN               D   \n",
       "14                     15            NaN               R   \n",
       "15                     16            NaN               D   \n",
       "16                     17            NaN               R   \n",
       "17                     18            NaN               R   \n",
       "18                     19            NaN               R   \n",
       "19                     20            NaN               D   \n",
       "20                     21            NaN               D   \n",
       "21                     22            NaN               D   \n",
       "22                     23            NaN               D   \n",
       "23                     24            NaN               R   \n",
       "24                     25            NaN               R   \n",
       "25                     26            NaN               R   \n",
       "26                     27            NaN               R   \n",
       "27                     28            NaN               D   \n",
       "28                     29            NaN               R   \n",
       "29                     30            NaN               R   \n",
       "30                     31            NaN               R   \n",
       "31                     32            NaN               D   \n",
       "32                     33            NaN               O   \n",
       "33                     34            NaN               R   \n",
       "34                     35            NaN               R   \n",
       "35                     36            NaN               O   \n",
       "36                     37            NaN               D   \n",
       "37                     38            NaN               D   \n",
       "38                     39            NaN               O   \n",
       "39                     40            NaN               D   \n",
       "40                     41            NaN               R   \n",
       "41                     42            NaN               R   \n",
       "42                     43            NaN               R   \n",
       "43                     44            NaN               R   \n",
       "44                     45            NaN               O   \n",
       "45                     46            NaN               R   \n",
       "46                     47            NaN               R   \n",
       "47                     48            NaN               R   \n",
       "48                     49            NaN               D   \n",
       "49                     50            NaN               R   \n",
       "\n",
       "                   Democrat                   Republican  \\\n",
       "0           Brownie Futrell                    Bill Cook   \n",
       "1         Dorothea E. White             Norman Sanderson   \n",
       "2        Erica Smith-Ingram                         None   \n",
       "3          Angela R. Bryant                Richard Scott   \n",
       "4                 Don Davis                         None   \n",
       "5                      None                  Harry Brown   \n",
       "6                      None           Louis M. Pate, Jr.   \n",
       "7                      None                   Bill Rabon   \n",
       "8           Andrew Barnhill                  Michael Lee   \n",
       "9                      None                Brent Jackson   \n",
       "10             Albert Pacer                  Rick Horner   \n",
       "11             Susan Byerly                 Ronald Rabin   \n",
       "12            Jane W. Smith     Danny Earl (J. R.) Britt   \n",
       "13                 Dan Blue                         None   \n",
       "14     Laurel Deegan-Fricke               John Alexander   \n",
       "15            Jay Chaudhuri                  Eric Weaver   \n",
       "16           Susan P. Evans             Tamara Barringer   \n",
       "17              Gil Johnson                Chad Barefoot   \n",
       "18              Toni Morris              Wesley Meredith   \n",
       "19  Floyd B. McKissick, Jr.                         None   \n",
       "20                Ben Clark                 Dan Travieso   \n",
       "21             Mike Woodard             T. Greg Doucette   \n",
       "22       Valerie P. Foushee            Mary Lopez Carter   \n",
       "23              John Thorpe                    Rick Gunn   \n",
       "24     Dannie M. Montgomery                  Tom McInnis   \n",
       "25                     None                   OVER VOTES   \n",
       "26          Michael Garrett                   Trudy Wade   \n",
       "27       Gladys A. Robinson                Devin R. King   \n",
       "28                     None             Jerry W. Tillman   \n",
       "29      Michael W. Holleman  Shirley Blackburn Randleman   \n",
       "30                     None                Joyce Krawiec   \n",
       "31           Paul Lowe, Jr.                         None   \n",
       "32         Jim Beall Graham                   Cathy Dunn   \n",
       "33                     None              Andrew C. Brock   \n",
       "34                     None                 Tommy Tucker   \n",
       "35             Robert Brown               Paul R. Newton   \n",
       "36             Jeff Jackson                  Bob Diamond   \n",
       "37                Joel Ford              Richard Rivette   \n",
       "38              Lloyd Scher                   Dan Bishop   \n",
       "39            Joyce Waddell             Marguerite Cooke   \n",
       "40          Jonathan Hudson                   Jeff Tarte   \n",
       "41                     None                   Andy Wells   \n",
       "42                     None             Kathy Harrington   \n",
       "43                     None              David L. Curtis   \n",
       "44             Art Sherwood               Deanna Ballard   \n",
       "45             Anne Fischer                Warren Daniel   \n",
       "46           Mary Jane Boyd                   Ralph Hise   \n",
       "47           Norman Bossert                Chuck Edwards   \n",
       "48           Terry Van Duyn                         None   \n",
       "49               Jane Hipps                    Jim Davis   \n",
       "\n",
       "                           Other                      Contest  \n",
       "0                            NaN  NC STATE SENATE DISTRICT 01  \n",
       "1                            NaN  NC STATE SENATE DISTRICT 02  \n",
       "2                            NaN  NC STATE SENATE DISTRICT 03  \n",
       "3                            NaN  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                            NaN  NC STATE SENATE DISTRICT 08  \n",
       "8                            NaN  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                           NaN  NC STATE SENATE DISTRICT 14  \n",
       "14        Brad Hessel: 5,081 (L)  NC STATE SENATE DISTRICT 15  \n",
       "15                           NaN  NC STATE SENATE DISTRICT 16  \n",
       "16      Susan Hogarth: 5,191 (L)  NC STATE SENATE DISTRICT 17  \n",
       "17                           NaN  NC STATE SENATE DISTRICT 18  \n",
       "18                           NaN  NC STATE SENATE DISTRICT 19  \n",
       "19      Barbara Howe: 16,202 (L)  NC STATE SENATE DISTRICT 20  \n",
       "20                           NaN  NC STATE SENATE DISTRICT 21  \n",
       "21                           NaN  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                           NaN  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                           NaN  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         Chris Cole: 4,938 (L)  NC STATE SENATE DISTRICT 41  \n",
       "41                           NaN  NC STATE SENATE DISTRICT 42  \n",
       "42                           NaN  NC STATE SENATE DISTRICT 43  \n",
       "43          Nic Haag: 20,881 (L)  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  William Meredith: 24,672 (L)  NC STATE SENATE DISTRICT 49  \n",
       "49                           NaN  NC STATE SENATE DISTRICT 50  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_candidates = pandas.read_csv('North Carolina State Lege Candidates - 2016 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(row['State Senate District'])\n",
    "    names = set(df_2016sldu[df_2016sldu.contest_name == contest_name].candidate_name)\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_2016new` DataFrame with vote counts for State representatives and U.S. President by party."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(5679, 9)\n",
      "SLDU Votes: 1823676 2242349\n",
      "US Pres Votes: 2187676 2362130\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>474</td>\n",
       "      <td>1826</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>436</td>\n",
       "      <td>1964</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>950</td>\n",
       "      <td>1530</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>641</td>\n",
       "      <td>1172</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>960</td>\n",
       "      <td>1243</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>5674</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>5675</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>5676</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>5677</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>5678</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>5679 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           474   \n",
       "1       ALAMANCE            02       24         R     NaN           436   \n",
       "2       ALAMANCE           035       24         R     NaN           950   \n",
       "3       ALAMANCE           03C       24         R     NaN           641   \n",
       "4       ALAMANCE           03N       24         R     NaN           960   \n",
       "...          ...           ...      ...       ...     ...           ...   \n",
       "5674      YANCEY        07 BRU     None      None     NaN             0   \n",
       "5675      YANCEY        08 CRA     None      None     NaN             0   \n",
       "5676      YANCEY        09 SOU     None      None     NaN             0   \n",
       "5677      YANCEY        10 PEN     None      None     NaN             0   \n",
       "5678      YANCEY        11 PRI     None      None     NaN             0   \n",
       "\n",
       "      sldu_votes_R  uspres_votes_D  uspres_votes_R  \n",
       "0             1826               0               0  \n",
       "1             1964               0               0  \n",
       "2             1530               0               0  \n",
       "3             1172               0               0  \n",
       "4             1243               0               0  \n",
       "...            ...             ...             ...  \n",
       "5674             0              91             163  \n",
       "5675             0             522            1260  \n",
       "5676             0             596             766  \n",
       "5677             0             106             270  \n",
       "5678             0             198             639  \n",
       "\n",
       "[5679 rows x 9 columns]"
      ]
     },
     "execution_count": 5,
     "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_2016sldu.groupby(['county_desc', 'precinct_code', 'contest_name']).groups\n",
    "\n",
    "for (county_desc, precinct_code, contest_name) in groups1.keys():\n",
    "    _df1 = df_2016sldu[df_2016sldu.county_desc == county_desc]\n",
    "    _df2 = _df1[_df1.precinct_code == precinct_code]\n",
    "    subdf_2016sldu = _df2[_df2.contest_name == contest_name]\n",
    "    \n",
    "    arrays['county_desc'].append(county_desc)\n",
    "    arrays['precinct_code'].append(precinct_code)\n",
    "    \n",
    "    row_candidates = df_candidates[df_candidates.Contest == contest_name].iloc[0]\n",
    "    arrays['district'].append(row_candidates['State Senate District'].astype(str))\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_2016sldu[subdf_2016sldu.candidate_name == 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.votes))\n",
    "            \n",
    "    try:\n",
    "        REP_name = row_candidates.Republican\n",
    "        REP_row = subdf_2016sldu[subdf_2016sldu.candidate_name == 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.votes))\n",
    "    \n",
    "    arrays['uspres_votes_D'].append(0)\n",
    "    arrays['uspres_votes_R'].append(0)\n",
    "    \n",
    "\n",
    "groups2 = df_2016sldu.groupby(['county_desc', 'precinct_code']).groups\n",
    "\n",
    "for (county_desc, precinct_code) in groups2.keys():\n",
    "    _df1 = df_2016uspres[df_2016uspres.county_desc == county_desc]\n",
    "    subdf_2016uspres = _df1[_df1.precinct_code == precinct_code]\n",
    "    \n",
    "    arrays['county_desc'].append(county_desc)\n",
    "    arrays['precinct_code'].append(precinct_code)\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_2016new = pandas.DataFrame(arrays)\n",
    "print(df_2016new.shape)\n",
    "print('SLDU Votes:', df_2016new.sldu_votes_D.sum(), df_2016new.sldu_votes_R.sum())\n",
    "print('US Pres Votes:', df_2016new.uspres_votes_D.sum(), df_2016new.uspres_votes_R.sum())\n",
    "df_2016new"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Assign Precinct Geography PSIDs\n",
    "\n",
    "Read precinct PSID values from `NC-Geographies.gpkg`, merge by county name and precinct ID."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>psid</th>\n",
       "      <th>gid</th>\n",
       "      <th>year</th>\n",
       "      <th>county_fips</th>\n",
       "      <th>county_name</th>\n",
       "      <th>precinct_id</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>474</td>\n",
       "      <td>1826</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>PSID:1158854937</td>\n",
       "      <td>2344.0</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>ALAMANCE</td>\n",
       "      <td>01</td>\n",
       "      <td>(POLYGON ((-79.43197308657012 35.8892919150817...</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>436</td>\n",
       "      <td>1964</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>PSID:1158848961</td>\n",
       "      <td>2342.0</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>ALAMANCE</td>\n",
       "      <td>02</td>\n",
       "      <td>(POLYGON ((-79.53483899133798 36.0438210001847...</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>950</td>\n",
       "      <td>1530</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>PSID:1158850573</td>\n",
       "      <td>2337.0</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>ALAMANCE</td>\n",
       "      <td>035</td>\n",
       "      <td>(POLYGON ((-79.50051299120325 36.1542650001393...</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>641</td>\n",
       "      <td>1172</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>PSID:1158854377</td>\n",
       "      <td>2350.0</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>ALAMANCE</td>\n",
       "      <td>03C</td>\n",
       "      <td>(POLYGON ((-79.5203239911727 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>960</td>\n",
       "      <td>1243</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>PSID:1158852503</td>\n",
       "      <td>2351.0</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>ALAMANCE</td>\n",
       "      <td>03N</td>\n",
       "      <td>(POLYGON ((-79.49082799096274 36.1056899996169...</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5674</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>PSID:1158854169</td>\n",
       "      <td>1089.0</td>\n",
       "      <td>2016</td>\n",
       "      <td>100</td>\n",
       "      <td>YANCEY</td>\n",
       "      <td>07 BRU</td>\n",
       "      <td>(POLYGON ((-82.19135299166359 35.9750180004796...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5675</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>PSID:1158852683</td>\n",
       "      <td>1092.0</td>\n",
       "      <td>2016</td>\n",
       "      <td>100</td>\n",
       "      <td>YANCEY</td>\n",
       "      <td>08 CRA</td>\n",
       "      <td>(POLYGON ((-82.18617999165113 35.9435280000667...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5676</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>PSID:1158854577</td>\n",
       "      <td>1096.0</td>\n",
       "      <td>2016</td>\n",
       "      <td>100</td>\n",
       "      <td>YANCEY</td>\n",
       "      <td>09 SOU</td>\n",
       "      <td>(POLYGON ((-82.22187599103363 35.8651619998303...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5677</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>PSID:1158853255</td>\n",
       "      <td>1095.0</td>\n",
       "      <td>2016</td>\n",
       "      <td>100</td>\n",
       "      <td>YANCEY</td>\n",
       "      <td>10 PEN</td>\n",
       "      <td>(POLYGON ((-82.26803599172992 35.7637380002675...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5678</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>PSID:1158852881</td>\n",
       "      <td>1094.0</td>\n",
       "      <td>2016</td>\n",
       "      <td>100</td>\n",
       "      <td>YANCEY</td>\n",
       "      <td>11 PRI</td>\n",
       "      <td>(POLYGON ((-82.35773699085409 35.9102740004773...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5679 rows × 16 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     county_desc precinct_code district incumbent  winner  sldu_votes_D  \\\n",
       "0       ALAMANCE            01       24         R     NaN           474   \n",
       "1       ALAMANCE            02       24         R     NaN           436   \n",
       "2       ALAMANCE           035       24         R     NaN           950   \n",
       "3       ALAMANCE           03C       24         R     NaN           641   \n",
       "4       ALAMANCE           03N       24         R     NaN           960   \n",
       "...          ...           ...      ...       ...     ...           ...   \n",
       "5674      YANCEY        07 BRU     None      None     NaN             0   \n",
       "5675      YANCEY        08 CRA     None      None     NaN             0   \n",
       "5676      YANCEY        09 SOU     None      None     NaN             0   \n",
       "5677      YANCEY        10 PEN     None      None     NaN             0   \n",
       "5678      YANCEY        11 PRI     None      None     NaN             0   \n",
       "\n",
       "      sldu_votes_R  uspres_votes_D  uspres_votes_R             psid     gid  \\\n",
       "0             1826               0               0  PSID:1158854937  2344.0   \n",
       "1             1964               0               0  PSID:1158848961  2342.0   \n",
       "2             1530               0               0  PSID:1158850573  2337.0   \n",
       "3             1172               0               0  PSID:1158854377  2350.0   \n",
       "4             1243               0               0  PSID:1158852503  2351.0   \n",
       "...            ...             ...             ...              ...     ...   \n",
       "5674             0              91             163  PSID:1158854169  1089.0   \n",
       "5675             0             522            1260  PSID:1158852683  1092.0   \n",
       "5676             0             596             766  PSID:1158854577  1096.0   \n",
       "5677             0             106             270  PSID:1158853255  1095.0   \n",
       "5678             0             198             639  PSID:1158852881  1094.0   \n",
       "\n",
       "      year county_fips county_name precinct_id  \\\n",
       "0     2016           1    ALAMANCE          01   \n",
       "1     2016           1    ALAMANCE          02   \n",
       "2     2016           1    ALAMANCE         035   \n",
       "3     2016           1    ALAMANCE         03C   \n",
       "4     2016           1    ALAMANCE         03N   \n",
       "...    ...         ...         ...         ...   \n",
       "5674  2016         100      YANCEY      07 BRU   \n",
       "5675  2016         100      YANCEY      08 CRA   \n",
       "5676  2016         100      YANCEY      09 SOU   \n",
       "5677  2016         100      YANCEY      10 PEN   \n",
       "5678  2016         100      YANCEY      11 PRI   \n",
       "\n",
       "                                               geometry  \n",
       "0     (POLYGON ((-79.43197308657012 35.8892919150817...  \n",
       "1     (POLYGON ((-79.53483899133798 36.0438210001847...  \n",
       "2     (POLYGON ((-79.50051299120325 36.1542650001393...  \n",
       "3     (POLYGON ((-79.5203239911727 36.08219700003401...  \n",
       "4     (POLYGON ((-79.49082799096274 36.1056899996169...  \n",
       "...                                                 ...  \n",
       "5674  (POLYGON ((-82.19135299166359 35.9750180004796...  \n",
       "5675  (POLYGON ((-82.18617999165113 35.9435280000667...  \n",
       "5676  (POLYGON ((-82.22187599103363 35.8651619998303...  \n",
       "5677  (POLYGON ((-82.26803599172992 35.7637380002675...  \n",
       "5678  (POLYGON ((-82.35773699085409 35.9102740004773...  \n",
       "\n",
       "[5679 rows x 16 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "geogs_2016 = geopandas.read_file('NC-Geographies.gpkg', layer='precincts')\n",
    "\n",
    "geogs_2016.psid = geogs_2016.psid.astype(str).str.replace(re.compile(r'^'), 'PSID:')\n",
    "\n",
    "df_2016out = df_2016new.merge(geogs_2016, how='left',\n",
    "                              left_on=('county_desc', 'precinct_code'),\n",
    "                              right_on=('county_name', 'precinct_id'))\n",
    "\n",
    "df_2016out"
   ]
  },
  {
   "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": 7,
   "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>474</td>\n",
       "      <td>1826</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>436</td>\n",
       "      <td>1964</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>950</td>\n",
       "      <td>1530</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>641</td>\n",
       "      <td>1172</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>960</td>\n",
       "      <td>1243</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>117</td>\n",
       "      <td>144</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>717</td>\n",
       "      <td>1098</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>669</td>\n",
       "      <td>694</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>154</td>\n",
       "      <td>216</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>293</td>\n",
       "      <td>566</td>\n",
       "      <td>198</td>\n",
       "      <td>639</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2695 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           474          1826             411   \n",
       "            02            0           436          1964             403   \n",
       "            035           0           950          1530             995   \n",
       "            03C           0           641          1172             679   \n",
       "            03N           0           960          1243            1036   \n",
       "...                                   ...           ...             ...   \n",
       "YANCEY      07 BRU        0           117           144              91   \n",
       "            08 CRA        0           717          1098             522   \n",
       "            09 SOU        0           669           694             596   \n",
       "            10 PEN        0           154           216             106   \n",
       "            11 PRI        0           293           566             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",
       "[2695 rows x 8 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped = df_2016out.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_2016final = grouped.apply(doit)\n",
    "\n",
    "df_2016final"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_2016final.to_csv('df_2016-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
}