pivot_results.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Pivot and Validate Precinct Level Election Results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import geopandas as gpd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Import the precinct level election results\n",
    "\n",
    "\n",
    "#### [Open Elections - 2018 NY General Election - Precinct Level Results](https://github.com/openelections/openelections-data-al/blob/master/2018/20181106__al__general__precinct.csv)\n",
    "\n",
    "* Relative Path: `data/precinct_election_results/20181106__al__general__precinct.csv`\n",
    "* Link: <https://github.com/openelections/openelections-data-al/blob/master/2018/20181106__al__general__precinct.csv>\n",
    "* Date Accessed: January 31, 2021\n",
    "* This data appears to be sourced from the official results ([link](https://www.sos.alabama.gov/alabama-votes/voter/election-data), but all the counties have been aggregated into one file and pivoted.\n",
    "* Of course I will use official county results to ensure that Open Elections didn't make any mistakes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>county</th>\n",
       "      <th>precinct</th>\n",
       "      <th>office</th>\n",
       "      <th>district</th>\n",
       "      <th>party</th>\n",
       "      <th>candidate</th>\n",
       "      <th>votes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Autauga</td>\n",
       "      <td>10 JONES COMMUNITY CTR</td>\n",
       "      <td>Attorney General</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DEM</td>\n",
       "      <td>Joseph Siegelman</td>\n",
       "      <td>115</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Autauga</td>\n",
       "      <td>10 JONES COMMUNITY CTR</td>\n",
       "      <td>Attorney General</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NON</td>\n",
       "      <td>Write-ins</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    county                precinct            office  district party  \\\n",
       "0  Autauga  10 JONES COMMUNITY CTR  Attorney General       NaN   DEM   \n",
       "1  Autauga  10 JONES COMMUNITY CTR  Attorney General       NaN   NON   \n",
       "\n",
       "          candidate  votes  \n",
       "0  Joseph Siegelman    115  \n",
       "1         Write-ins      0  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "statewide_election_results_file_path = 'data/precinct_election_results/20181106__al__general__precinct.csv'\n",
    "statewide_results_df = pd.read_csv(statewide_election_results_file_path,low_memory=False)\n",
    "statewide_results_df.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The next cell aliases `statewide_results_df` as `df` because typing fewer characters allows for faster data exploration.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = statewide_results_df.copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Clean `votes`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "def remove_commas(df, col_lst):\n",
    "    \"\"\"\n",
    "    remove commas from the string representation of numbers in the cols in col_lst\n",
    "    :df: DataFrame object\n",
    "    :col_lst: list of strings that are each the name of a column :df:\n",
    "    \"\"\"\n",
    "    for col in col_lst:\n",
    "        if df.dtypes[col] == \"object\":\n",
    "            df[col] = df[col].map(lambda s: str(s).replace(\",\", \"\"))\n",
    "        df = df.astype({col: \"float\"})\n",
    "        df[col] = df[col].fillna(0)\n",
    "        df = df.astype({col: \"int64\"})\n",
    "    return df\n",
    "\n",
    "df = remove_commas(df[df.votes.notna()], ['votes'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Clean `county`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.county = df.county.str.strip() + ' County'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "67"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.county.nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Autauga County', 'Baldwin County', 'Barbour County',\n",
       "       'Bibb County', 'Blount County', 'Bullock County', 'Butler County',\n",
       "       'Calhoun County', 'Chambers County', 'Cherokee County',\n",
       "       'Chilton County', 'Choctaw County', 'Clarke County', 'Clay County',\n",
       "       'Cleburne County', 'Coffee County', 'Colbert County',\n",
       "       'Conecuh County', 'Coosa County', 'Covington County',\n",
       "       'Crenshaw County', 'Cullman County', 'Dale County',\n",
       "       'Dallas County', 'DeKalb County', 'Elmore County',\n",
       "       'Escambia County', 'Etowah County', 'Fayette County',\n",
       "       'Franklin County', 'Geneva County', 'Greene County', 'Hale County',\n",
       "       'Henry County', 'Houston County', 'Jackson County',\n",
       "       'Jefferson County', 'Lamar County', 'Lauderdale County',\n",
       "       'Lawrence County', 'Lee County', 'Limestone County',\n",
       "       'Lowndes County', 'Macon County', 'Madison County',\n",
       "       'Marengo County', 'Marion County', 'Marshall County',\n",
       "       'Mobile County', 'Monroe County', 'Montgomery County',\n",
       "       'Morgan County', 'Perry County', 'Pickens County', 'Pike County',\n",
       "       'Randolph County', 'Russell County', 'Shelby County',\n",
       "       'StClair County', 'Sumter County', 'Talladega County',\n",
       "       'Tallapoosa County', 'Tuscaloosa County', 'Walker County',\n",
       "       'Washington County', 'Wilcox County', 'Winston County'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.county.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.county = df.county.map(lambda county: 'St. Clair County' if county == 'StClair County' else county) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Autauga County', 'Baldwin County', 'Barbour County',\n",
       "       'Bibb County', 'Blount County', 'Bullock County', 'Butler County',\n",
       "       'Calhoun County', 'Chambers County', 'Cherokee County',\n",
       "       'Chilton County', 'Choctaw County', 'Clarke County', 'Clay County',\n",
       "       'Cleburne County', 'Coffee County', 'Colbert County',\n",
       "       'Conecuh County', 'Coosa County', 'Covington County',\n",
       "       'Crenshaw County', 'Cullman County', 'Dale County',\n",
       "       'Dallas County', 'DeKalb County', 'Elmore County',\n",
       "       'Escambia County', 'Etowah County', 'Fayette County',\n",
       "       'Franklin County', 'Geneva County', 'Greene County', 'Hale County',\n",
       "       'Henry County', 'Houston County', 'Jackson County',\n",
       "       'Jefferson County', 'Lamar County', 'Lauderdale County',\n",
       "       'Lawrence County', 'Lee County', 'Limestone County',\n",
       "       'Lowndes County', 'Macon County', 'Madison County',\n",
       "       'Marengo County', 'Marion County', 'Marshall County',\n",
       "       'Mobile County', 'Monroe County', 'Montgomery County',\n",
       "       'Morgan County', 'Perry County', 'Pickens County', 'Pike County',\n",
       "       'Randolph County', 'Russell County', 'Shelby County',\n",
       "       'St. Clair County', 'Sumter County', 'Talladega County',\n",
       "       'Tallapoosa County', 'Tuscaloosa County', 'Walker County',\n",
       "       'Washington County', 'Wilcox County', 'Winston County'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.county.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "67"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.county.nunique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Clean `office`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "12\n",
      "['Attorney General', 'Ballots Cast', 'Commissioner of Agriculture and Industries', 'Governor', 'Lieutenant Governor', 'Secretary of State', 'State Auditor', 'State House', 'State Senate', 'State Treasurer', 'Straight Party', 'U.S. House']\n"
     ]
    }
   ],
   "source": [
    "offices = list(df.office.unique())\n",
    "print(len(offices))\n",
    "print(offices)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ensure that all counties data for each office"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Series([], Name: office, dtype: object)"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "county_to_offices = df.groupby('county').office.unique()\n",
    "county_to_missing_offices = county_to_offices.apply(lambda x: set(offices).difference(x))\n",
    "county_to_missing_offices[county_to_missing_offices != set()]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ensure that all counties data for each office"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "offices_to_keep = ['Governor', 'Lieutenant Governor', 'State House', 'State Senate', 'U.S. House', 'Straight Party']\n",
    "df = df[df.office.isin(offices_to_keep)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Clean `candidate`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Over Votes          14368\n",
       "Under Votes         14368\n",
       "Write-ins           11761\n",
       "Kay Ivey             2240\n",
       "Walt Maddox          2240\n",
       "                    ...  \n",
       "Linda Meigs            13\n",
       "Thad McClammy          12\n",
       "Anthony Daniels        12\n",
       "Mary Wynne Kling       11\n",
       "Joe Lovvorn            11\n",
       "Name: candidate, Length: 241, dtype: int64"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.candidate.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "candidates_to_exclude = ['Under Votes', 'Over Votes', 'Write-ins']\n",
    "df = df[~df.candidate.isin(candidates_to_exclude)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Clean `party`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['DEM', 'REP', 'LIB', 'IND'], dtype=object)"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.party.unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Validate\n",
    "\n",
    "\n",
    "#### [Alabama Secretary of State - 2018 AL General Election - County Level Results](https://github.com/openelections/openelections-data-al/blob/master/2018/20181106__al__general__precinct.csv)\n",
    "\n",
    "* Relative Path: `data/official_county_level_results/2018GeneralVoterTurnoutStats.csv`\n",
    "* Website Link: <https://www.sos.alabama.gov/alabama-votes/voter/election-data>\n",
    "* Link: <https://www.sos.alabama.gov/sites/default/files/election-data/2019-01/2018GeneralVoterTurnoutStats.xlsx>\n",
    "* Date Accessed: January 31, 2021"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>Total Registered Voters</th>\n",
       "      <th>Total Ballots Cast</th>\n",
       "      <th>Straight Party        Democrat Ballots Cast</th>\n",
       "      <th>Straight Party        Republican Ballots Cast</th>\n",
       "      <th>Total Absentee Ballots Cast</th>\n",
       "      <th>Turnout as a Percentage of Registered Voters</th>\n",
       "      <th>Absentee Ballots Cast as Percentage of Total Ballots Cast</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Autauga</td>\n",
       "      <td>\\t39,666</td>\n",
       "      <td>19,814</td>\n",
       "      <td>3,666</td>\n",
       "      <td>9,118</td>\n",
       "      <td>725</td>\n",
       "      <td>49.95%</td>\n",
       "      <td>3.66%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Baldwin</td>\n",
       "      <td>\\t155,006</td>\n",
       "      <td>78,270</td>\n",
       "      <td>10,465</td>\n",
       "      <td>40,459</td>\n",
       "      <td>2,652</td>\n",
       "      <td>50.49%</td>\n",
       "      <td>3.39%</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    County Total Registered Voters Total Ballots Cast  \\\n",
       "0  Autauga               \\t39,666              19,814   \n",
       "1  Baldwin              \\t155,006              78,270   \n",
       "\n",
       "  Straight Party        Democrat Ballots Cast  \\\n",
       "0                                       3,666   \n",
       "1                                      10,465   \n",
       "\n",
       "  Straight Party        Republican Ballots Cast Total Absentee Ballots Cast  \\\n",
       "0                                         9,118                         725   \n",
       "1                                        40,459                       2,652   \n",
       "\n",
       "  Turnout as a Percentage of Registered Voters  \\\n",
       "0                                       49.95%   \n",
       "1                                       50.49%   \n",
       "\n",
       "  Absentee Ballots Cast as Percentage of Total Ballots Cast  \n",
       "0                                              3.66%         \n",
       "1                                              3.39%         "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "county_level_election_results_file_path = 'data/official_county_level_results/2018GeneralVoterTurnoutStats.csv'\n",
    "county_level_election_results_df = pd.read_csv(county_level_election_results_file_path,low_memory=False)\n",
    "county_level_election_results_df.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "cdf = county_level_election_results_df.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "cdf.County = cdf.County + ' County'\n",
    "cdf = cdf[cdf.County!='Total County']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "cdf = cdf.set_index('County')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(67, 7)"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cdf.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "cdf = remove_commas(cdf, ['Straight Party        Democrat Ballots Cast', 'Straight Party        Republican Ballots Cast'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    67\n",
       "dtype: int64"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(df[(df.office == 'Straight Party') & (df.party == 'DEM')].groupby('county').votes.sum() - cdf['Straight Party        Democrat Ballots Cast']).value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       " 0    66\n",
       "-5     1\n",
       "dtype: int64"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(df[(df.office == 'Straight Party') & (df.party == 'REP')].groupby('county').votes.sum() - cdf['Straight Party        Republican Ballots Cast']).value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Looks like it matches very well. Only off by 5 in one county. It seems like Open Elections did a great job aggregating the files provided by the state."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pivot the data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"16\" halign=\"left\">votes</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>office</th>\n",
       "      <th colspan=\"2\" halign=\"left\">Governor</th>\n",
       "      <th colspan=\"2\" halign=\"left\">Lieutenant Governor</th>\n",
       "      <th colspan=\"4\" halign=\"left\">State House</th>\n",
       "      <th colspan=\"3\" halign=\"left\">State Senate</th>\n",
       "      <th colspan=\"3\" halign=\"left\">Straight Party</th>\n",
       "      <th colspan=\"2\" halign=\"left\">U.S. House</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>party</th>\n",
       "      <th>DEM</th>\n",
       "      <th>REP</th>\n",
       "      <th>DEM</th>\n",
       "      <th>REP</th>\n",
       "      <th>DEM</th>\n",
       "      <th>IND</th>\n",
       "      <th>LIB</th>\n",
       "      <th>REP</th>\n",
       "      <th>DEM</th>\n",
       "      <th>IND</th>\n",
       "      <th>REP</th>\n",
       "      <th>DEM</th>\n",
       "      <th>LIB</th>\n",
       "      <th>REP</th>\n",
       "      <th>DEM</th>\n",
       "      <th>REP</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>county</th>\n",
       "      <th>precinct</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Autauga County</th>\n",
       "      <th>10 JONES COMMUNITY CTR</th>\n",
       "      <td>105.0</td>\n",
       "      <td>168.0</td>\n",
       "      <td>109.0</td>\n",
       "      <td>162.0</td>\n",
       "      <td>127.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>170.0</td>\n",
       "      <td>98.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>110.0</td>\n",
       "      <td>118.0</td>\n",
       "      <td>153.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100 TRINITY METHODIST</th>\n",
       "      <td>333.0</td>\n",
       "      <td>1436.0</td>\n",
       "      <td>288.0</td>\n",
       "      <td>1470.0</td>\n",
       "      <td>252.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1499.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1494.0</td>\n",
       "      <td>150.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>916.0</td>\n",
       "      <td>391.0</td>\n",
       "      <td>1375.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                         votes                              \\\n",
       "office                                Governor         Lieutenant Governor   \n",
       "party                                      DEM     REP                 DEM   \n",
       "county         precinct                                                      \n",
       "Autauga County 10 JONES COMMUNITY CTR    105.0   168.0               109.0   \n",
       "               100 TRINITY METHODIST     333.0  1436.0               288.0   \n",
       "\n",
       "                                                                           \\\n",
       "office                                        State House                   \n",
       "party                                     REP         DEM IND LIB     REP   \n",
       "county         precinct                                                     \n",
       "Autauga County 10 JONES COMMUNITY CTR   162.0       127.0 NaN NaN     NaN   \n",
       "               100 TRINITY METHODIST   1470.0       252.0 NaN NaN  1499.0   \n",
       "\n",
       "                                                                               \\\n",
       "office                                State Senate             Straight Party   \n",
       "party                                          DEM IND     REP            DEM   \n",
       "county         precinct                                                         \n",
       "Autauga County 10 JONES COMMUNITY CTR          NaN NaN   170.0           98.0   \n",
       "               100 TRINITY METHODIST           NaN NaN  1494.0          150.0   \n",
       "\n",
       "                                                                     \n",
       "office                                           U.S. House          \n",
       "party                                 LIB    REP        DEM     REP  \n",
       "county         precinct                                              \n",
       "Autauga County 10 JONES COMMUNITY CTR NaN  110.0      118.0   153.0  \n",
       "               100 TRINITY METHODIST  NaN  916.0      391.0  1375.0  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "piv = pd.pivot_table(df, index = ['county', 'precinct'], columns = ['office','party'], values = ['votes'], aggfunc = np.sum)\n",
    "piv.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "MultiIndex([('votes',            'Governor', 'DEM'),\n",
       "            ('votes',            'Governor', 'REP'),\n",
       "            ('votes', 'Lieutenant Governor', 'DEM'),\n",
       "            ('votes', 'Lieutenant Governor', 'REP'),\n",
       "            ('votes',         'State House', 'DEM'),\n",
       "            ('votes',         'State House', 'IND'),\n",
       "            ('votes',         'State House', 'LIB'),\n",
       "            ('votes',         'State House', 'REP'),\n",
       "            ('votes',        'State Senate', 'DEM'),\n",
       "            ('votes',        'State Senate', 'IND'),\n",
       "            ('votes',        'State Senate', 'REP'),\n",
       "            ('votes',      'Straight Party', 'DEM'),\n",
       "            ('votes',      'Straight Party', 'LIB'),\n",
       "            ('votes',      'Straight Party', 'REP'),\n",
       "            ('votes',          'U.S. House', 'DEM'),\n",
       "            ('votes',          'U.S. House', 'REP')],\n",
       "           names=[None, 'office', 'party'])"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "piv.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "office_to_abbreviation = {\n",
    "    'Governor':'Gov', \n",
    "    'Lieutenant Governor':'LtGov',\n",
    "    'U.S. House':'USHOR',\n",
    "    'State Senate':'StSen', \n",
    "    'State House':'StHOR',\n",
    "    'Straight Party':'SP',\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "9"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "max([len(x) for x in [office_to_abbreviation[office] + '_' + party for _,office,party in piv.columns]])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "All column names will be under the 10 character limit for shapefiles. Now rename and flatten the columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "piv.columns = [office_to_abbreviation[office] + '_' + party for _,office,party in piv.columns]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Save the file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>county</th>\n",
       "      <th>precinct</th>\n",
       "      <th>Gov_DEM</th>\n",
       "      <th>Gov_REP</th>\n",
       "      <th>LtGov_DEM</th>\n",
       "      <th>LtGov_REP</th>\n",
       "      <th>StHOR_DEM</th>\n",
       "      <th>StHOR_IND</th>\n",
       "      <th>StHOR_LIB</th>\n",
       "      <th>StHOR_REP</th>\n",
       "      <th>StSen_DEM</th>\n",
       "      <th>StSen_IND</th>\n",
       "      <th>StSen_REP</th>\n",
       "      <th>SP_DEM</th>\n",
       "      <th>SP_LIB</th>\n",
       "      <th>SP_REP</th>\n",
       "      <th>USHOR_DEM</th>\n",
       "      <th>USHOR_REP</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Autauga County</td>\n",
       "      <td>10 JONES COMMUNITY CTR</td>\n",
       "      <td>105.0</td>\n",
       "      <td>168.0</td>\n",
       "      <td>109.0</td>\n",
       "      <td>162.0</td>\n",
       "      <td>127.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>170.0</td>\n",
       "      <td>98.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>110.0</td>\n",
       "      <td>118.0</td>\n",
       "      <td>153.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Autauga County</td>\n",
       "      <td>100 TRINITY METHODIST</td>\n",
       "      <td>333.0</td>\n",
       "      <td>1436.0</td>\n",
       "      <td>288.0</td>\n",
       "      <td>1470.0</td>\n",
       "      <td>252.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1499.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1494.0</td>\n",
       "      <td>150.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>916.0</td>\n",
       "      <td>391.0</td>\n",
       "      <td>1375.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Autauga County</td>\n",
       "      <td>110 CENTRAL AL ELECTRIC</td>\n",
       "      <td>149.0</td>\n",
       "      <td>106.0</td>\n",
       "      <td>157.0</td>\n",
       "      <td>97.0</td>\n",
       "      <td>166.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>107.0</td>\n",
       "      <td>138.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>59.0</td>\n",
       "      <td>159.0</td>\n",
       "      <td>94.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Autauga County</td>\n",
       "      <td>140 AUTAUGAVILLE VFD</td>\n",
       "      <td>308.0</td>\n",
       "      <td>280.0</td>\n",
       "      <td>314.0</td>\n",
       "      <td>271.0</td>\n",
       "      <td>351.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>296.0</td>\n",
       "      <td>267.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>186.0</td>\n",
       "      <td>326.0</td>\n",
       "      <td>254.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Autauga County</td>\n",
       "      <td>150 PRATTMONT BAPTIST CH</td>\n",
       "      <td>175.0</td>\n",
       "      <td>472.0</td>\n",
       "      <td>163.0</td>\n",
       "      <td>478.0</td>\n",
       "      <td>158.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>482.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>507.0</td>\n",
       "      <td>94.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>300.0</td>\n",
       "      <td>196.0</td>\n",
       "      <td>448.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           county                  precinct  Gov_DEM  Gov_REP  LtGov_DEM  \\\n",
       "0  Autauga County    10 JONES COMMUNITY CTR    105.0    168.0      109.0   \n",
       "1  Autauga County     100 TRINITY METHODIST    333.0   1436.0      288.0   \n",
       "2  Autauga County   110 CENTRAL AL ELECTRIC    149.0    106.0      157.0   \n",
       "3  Autauga County      140 AUTAUGAVILLE VFD    308.0    280.0      314.0   \n",
       "4  Autauga County  150 PRATTMONT BAPTIST CH    175.0    472.0      163.0   \n",
       "\n",
       "   LtGov_REP  StHOR_DEM  StHOR_IND  StHOR_LIB  StHOR_REP  StSen_DEM  \\\n",
       "0      162.0      127.0        NaN        NaN        NaN        NaN   \n",
       "1     1470.0      252.0        NaN        NaN     1499.0        NaN   \n",
       "2       97.0      166.0        NaN        NaN        NaN        NaN   \n",
       "3      271.0      351.0        NaN        NaN        NaN        NaN   \n",
       "4      478.0      158.0        NaN        NaN      482.0        NaN   \n",
       "\n",
       "   StSen_IND  StSen_REP  SP_DEM  SP_LIB  SP_REP  USHOR_DEM  USHOR_REP  \n",
       "0        NaN      170.0    98.0     NaN   110.0      118.0      153.0  \n",
       "1        NaN     1494.0   150.0     NaN   916.0      391.0     1375.0  \n",
       "2        NaN      107.0   138.0     NaN    59.0      159.0       94.0  \n",
       "3        NaN      296.0   267.0     NaN   186.0      326.0      254.0  \n",
       "4        NaN      507.0    94.0     NaN   300.0      196.0      448.0  "
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "export_df = piv.reset_index()\n",
    "export_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "export_df.to_csv('data/precinct_election_results/AL_G18_pivoted.csv', index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "venv-al",
   "language": "python",
   "name": "venv-al"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}