notebooks/Data Engineering.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Engineering \n",
    "\n",
    "### Source: [Alberto Nieto](https://github.com/Qberto/Voter-Participation-Data-Wrangling)\n",
    "\n",
    "### The goal is to explore, visualize, and analyze U.S. presidential election participation data \n",
    "\n",
    "### Data available [here](https://dataverse.harvard.edu/dataset.xhtml?persistentId=doi:10.7910/DVN/VOQCHQ)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true,
    "jp-MarkdownHeadingCollapsed": true,
    "tags": []
   },
   "source": [
    "## Step 1: Load and Clean Election Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "hidden": true
   },
   "outputs": [],
   "source": [
    "# The import statements load each module\n",
    "import arcgis\n",
    "import pandas as pd\n",
    "import os\n",
    "import arcpy  # Best practice: Load arcpy last to maintain priority for namespace"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "hidden": true
   },
   "outputs": [],
   "source": [
    "# Make reference to the file path for the csv, which should be in the same directory as the notebook\n",
    "table_csv_path = \"countypres_2000-2016.csv\"\n",
    "\n",
    "# Use Pandas to read the csv into a dataframe\n",
    "data_df = pd.read_csv(table_csv_path, dtype={'year': str, 'FIPS': str})  # dtype parameter specifies that year and FIPS fields are string"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "hidden": true
   },
   "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>year</th>\n",
       "      <th>state</th>\n",
       "      <th>state_po</th>\n",
       "      <th>county</th>\n",
       "      <th>FIPS</th>\n",
       "      <th>office</th>\n",
       "      <th>candidate</th>\n",
       "      <th>party</th>\n",
       "      <th>candidatevotes</th>\n",
       "      <th>totalvotes</th>\n",
       "      <th>version</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2000</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>Autauga</td>\n",
       "      <td>1001</td>\n",
       "      <td>President</td>\n",
       "      <td>Al Gore</td>\n",
       "      <td>democrat</td>\n",
       "      <td>4942.0</td>\n",
       "      <td>17208</td>\n",
       "      <td>20190722</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2000</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>Autauga</td>\n",
       "      <td>1001</td>\n",
       "      <td>President</td>\n",
       "      <td>George W. Bush</td>\n",
       "      <td>republican</td>\n",
       "      <td>11993.0</td>\n",
       "      <td>17208</td>\n",
       "      <td>20190722</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2000</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>Autauga</td>\n",
       "      <td>1001</td>\n",
       "      <td>President</td>\n",
       "      <td>Ralph Nader</td>\n",
       "      <td>green</td>\n",
       "      <td>160.0</td>\n",
       "      <td>17208</td>\n",
       "      <td>20190722</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2000</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>Autauga</td>\n",
       "      <td>1001</td>\n",
       "      <td>President</td>\n",
       "      <td>Other</td>\n",
       "      <td>NaN</td>\n",
       "      <td>113.0</td>\n",
       "      <td>17208</td>\n",
       "      <td>20190722</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2000</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>Baldwin</td>\n",
       "      <td>1003</td>\n",
       "      <td>President</td>\n",
       "      <td>Al Gore</td>\n",
       "      <td>democrat</td>\n",
       "      <td>13997.0</td>\n",
       "      <td>56480</td>\n",
       "      <td>20190722</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   year    state state_po  ... candidatevotes totalvotes   version\n",
       "0  2000  Alabama       AL  ...         4942.0      17208  20190722\n",
       "1  2000  Alabama       AL  ...        11993.0      17208  20190722\n",
       "2  2000  Alabama       AL  ...          160.0      17208  20190722\n",
       "3  2000  Alabama       AL  ...          113.0      17208  20190722\n",
       "4  2000  Alabama       AL  ...        13997.0      56480  20190722\n",
       "\n",
       "[5 rows x 11 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(50524, 11)"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "### Handle missing data "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There were 64 records with null FIPS values in the data.\n",
      "This amounts to 0.13% of the available data.\n"
     ]
    }
   ],
   "source": [
    "# Set the field to check nulls for\n",
    "field_to_check = \"FIPS\"\n",
    "\n",
    "# Determine how many rows are in the table\n",
    "rowcount = data_df.shape[0]\n",
    "\n",
    "# Determine how many rows have null FIPS \n",
    "null_fips_rowcount = data_df.loc[data_df[field_to_check].isnull()].shape[0]\n",
    "\n",
    "# Calculate how much of the data this represents as a percentage\n",
    "percentage_null_fips = round((null_fips_rowcount / rowcount) * 100, 2)\n",
    "\n",
    "# Use a print statement to report this information\n",
    "print(\"There were \"+str(null_fips_rowcount)+\" records with null \"+str(field_to_check)+\" values in the data.\\nThis amounts to \" +str(percentage_null_fips)+\"% of the available data.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "hidden": true
   },
   "outputs": [],
   "source": [
    "# Use the notnull function and the loc function to create a new dataframe without null FIPS records\n",
    "data_df = data_df.loc[data_df['FIPS'].notnull()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(50460, 11)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "### Explore and handle data types"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "10.36% of data (5228 rows) has truncated FIPS values.\n"
     ]
    }
   ],
   "source": [
    "# FIPS (Federal Information Processing Standards) is a five-digit code\n",
    "\n",
    "# Check how many records have a FIPS value with four characters\n",
    "trunc_df = data_df.loc[data_df['FIPS'].str.len() == 4]\n",
    "trunc_data_per = (trunc_df.shape[0] / data_df.shape[0])*100\n",
    "\n",
    "# Use another print statement (using the f format key) to report this information\n",
    "print(f\"{round(trunc_data_per, 2)}% of data ({trunc_df.shape[0]} rows) has truncated FIPS values.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Next, we need to fix the FIPS field since the data has leading zeroes truncated by its interpretation as a numeric field. \n",
    "\n",
    "We can create a simple function in python to determine if the value is four characters, and append a leading zero if that's the case. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "hidden": true
   },
   "outputs": [],
   "source": [
    "# Define a helper function to fix truncated zeros, with one parameter: the value to be processed\n",
    "def fix_trunc_zeros(val):\n",
    "    # Use an if statement to check if there are four characters in the string representation of the value\n",
    "    if len(str(val)) == 4:\n",
    "        # If this is the case, return the value with an appended \"0\" in the front\n",
    "        return \"0\"+str(val)\n",
    "    # Otherwise...\n",
    "    else:\n",
    "        # Return the value itself\n",
    "        return str(val)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'07042'"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Test helper function with truncated value\n",
    "fix_trunc_zeros(7042)  # You should see an appended zero: \"07042\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "10.36% of data (5228 rows) had truncated FIPS IDs corrected.\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>year</th>\n",
       "      <th>state</th>\n",
       "      <th>state_po</th>\n",
       "      <th>county</th>\n",
       "      <th>FIPS</th>\n",
       "      <th>office</th>\n",
       "      <th>candidate</th>\n",
       "      <th>party</th>\n",
       "      <th>candidatevotes</th>\n",
       "      <th>totalvotes</th>\n",
       "      <th>version</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2000</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>Autauga</td>\n",
       "      <td>01001</td>\n",
       "      <td>President</td>\n",
       "      <td>Al Gore</td>\n",
       "      <td>democrat</td>\n",
       "      <td>4942.0</td>\n",
       "      <td>17208</td>\n",
       "      <td>20190722</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2000</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>Autauga</td>\n",
       "      <td>01001</td>\n",
       "      <td>President</td>\n",
       "      <td>George W. Bush</td>\n",
       "      <td>republican</td>\n",
       "      <td>11993.0</td>\n",
       "      <td>17208</td>\n",
       "      <td>20190722</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2000</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>Autauga</td>\n",
       "      <td>01001</td>\n",
       "      <td>President</td>\n",
       "      <td>Ralph Nader</td>\n",
       "      <td>green</td>\n",
       "      <td>160.0</td>\n",
       "      <td>17208</td>\n",
       "      <td>20190722</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2000</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>Autauga</td>\n",
       "      <td>01001</td>\n",
       "      <td>President</td>\n",
       "      <td>Other</td>\n",
       "      <td>NaN</td>\n",
       "      <td>113.0</td>\n",
       "      <td>17208</td>\n",
       "      <td>20190722</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2000</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>Baldwin</td>\n",
       "      <td>01003</td>\n",
       "      <td>President</td>\n",
       "      <td>Al Gore</td>\n",
       "      <td>democrat</td>\n",
       "      <td>13997.0</td>\n",
       "      <td>56480</td>\n",
       "      <td>20190722</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   year    state state_po  ... candidatevotes totalvotes   version\n",
       "0  2000  Alabama       AL  ...         4942.0      17208  20190722\n",
       "1  2000  Alabama       AL  ...        11993.0      17208  20190722\n",
       "2  2000  Alabama       AL  ...          160.0      17208  20190722\n",
       "3  2000  Alabama       AL  ...          113.0      17208  20190722\n",
       "4  2000  Alabama       AL  ...        13997.0      56480  20190722\n",
       "\n",
       "[5 rows x 11 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Run helper function on the FIPS field using the apply and lambda method \n",
    "data_df['FIPS'] = data_df['FIPS'].apply(lambda x: fix_trunc_zeros(x)) \n",
    "\n",
    "# Print information on the operation performed, and show the first few records to confirm it worked\n",
    "print(f\"{round(trunc_data_per, 2)}% of data ({trunc_df.shape[0]} rows) had truncated FIPS IDs corrected.\")\n",
    "data_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "### Reformat the table structure"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "We now need to reformat the structure of the table. Currently, each record corresponds to a candidate and their votes in a particular county. We need each record to correspond to each county, with fields showing the votes for different candidates for that election year. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "**Let's break this down step by step and fully understand it.**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "\n",
    "In essence, we need to:\n",
    "\n",
    "1. Set a few fields aside, \"locking\" them from the table pivot. (set_index)\n",
    "2. Pivot the table using the remaining fields. (groupby, cumcount, unstack)\n",
    "3. Give the pivoted fields designations for each party. \n",
    "4. Bring the locked fields back to our table. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "**1. Setting an index using multiple fields** "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "hidden": true
   },
   "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></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>candidate</th>\n",
       "      <th>party</th>\n",
       "      <th>candidatevotes</th>\n",
       "      <th>totalvotes</th>\n",
       "      <th>version</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FIPS</th>\n",
       "      <th>year</th>\n",
       "      <th>county</th>\n",
       "      <th>state</th>\n",
       "      <th>state_po</th>\n",
       "      <th>office</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=\"4\" valign=\"top\">01001</th>\n",
       "      <th rowspan=\"4\" valign=\"top\">2000</th>\n",
       "      <th rowspan=\"4\" valign=\"top\">Autauga</th>\n",
       "      <th rowspan=\"4\" valign=\"top\">Alabama</th>\n",
       "      <th rowspan=\"4\" valign=\"top\">AL</th>\n",
       "      <th>President</th>\n",
       "      <td>Al Gore</td>\n",
       "      <td>democrat</td>\n",
       "      <td>4942.0</td>\n",
       "      <td>17208</td>\n",
       "      <td>20190722</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>President</th>\n",
       "      <td>George W. Bush</td>\n",
       "      <td>republican</td>\n",
       "      <td>11993.0</td>\n",
       "      <td>17208</td>\n",
       "      <td>20190722</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>President</th>\n",
       "      <td>Ralph Nader</td>\n",
       "      <td>green</td>\n",
       "      <td>160.0</td>\n",
       "      <td>17208</td>\n",
       "      <td>20190722</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>President</th>\n",
       "      <td>Other</td>\n",
       "      <td>NaN</td>\n",
       "      <td>113.0</td>\n",
       "      <td>17208</td>\n",
       "      <td>20190722</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>01003</th>\n",
       "      <th>2000</th>\n",
       "      <th>Baldwin</th>\n",
       "      <th>Alabama</th>\n",
       "      <th>AL</th>\n",
       "      <th>President</th>\n",
       "      <td>Al Gore</td>\n",
       "      <td>democrat</td>\n",
       "      <td>13997.0</td>\n",
       "      <td>56480</td>\n",
       "      <td>20190722</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">56043</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">2016</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">Washakie</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">Wyoming</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">WY</th>\n",
       "      <th>President</th>\n",
       "      <td>Donald Trump</td>\n",
       "      <td>republican</td>\n",
       "      <td>2911.0</td>\n",
       "      <td>3814</td>\n",
       "      <td>20190722</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>President</th>\n",
       "      <td>Other</td>\n",
       "      <td>NaN</td>\n",
       "      <td>371.0</td>\n",
       "      <td>3814</td>\n",
       "      <td>20190722</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">56045</th>\n",
       "      <th rowspan=\"3\" valign=\"top\">2016</th>\n",
       "      <th rowspan=\"3\" valign=\"top\">Weston</th>\n",
       "      <th rowspan=\"3\" valign=\"top\">Wyoming</th>\n",
       "      <th rowspan=\"3\" valign=\"top\">WY</th>\n",
       "      <th>President</th>\n",
       "      <td>Hillary Clinton</td>\n",
       "      <td>democrat</td>\n",
       "      <td>299.0</td>\n",
       "      <td>3526</td>\n",
       "      <td>20190722</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>President</th>\n",
       "      <td>Donald Trump</td>\n",
       "      <td>republican</td>\n",
       "      <td>3033.0</td>\n",
       "      <td>3526</td>\n",
       "      <td>20190722</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>President</th>\n",
       "      <td>Other</td>\n",
       "      <td>NaN</td>\n",
       "      <td>194.0</td>\n",
       "      <td>3526</td>\n",
       "      <td>20190722</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>50460 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                      candidate  ...   version\n",
       "FIPS  year county   state   state_po office                      ...          \n",
       "01001 2000 Autauga  Alabama AL       President          Al Gore  ...  20190722\n",
       "                                     President   George W. Bush  ...  20190722\n",
       "                                     President      Ralph Nader  ...  20190722\n",
       "                                     President            Other  ...  20190722\n",
       "01003 2000 Baldwin  Alabama AL       President          Al Gore  ...  20190722\n",
       "...                                                         ...  ...       ...\n",
       "56043 2016 Washakie Wyoming WY       President     Donald Trump  ...  20190722\n",
       "                                     President            Other  ...  20190722\n",
       "56045 2016 Weston   Wyoming WY       President  Hillary Clinton  ...  20190722\n",
       "                                     President     Donald Trump  ...  20190722\n",
       "                                     President            Other  ...  20190722\n",
       "\n",
       "[50460 rows x 5 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Notice how running this cell uses the specified fields as row indices, \n",
    "# which prevents them from being \"rotated\" in the table pivot\n",
    "data_df.set_index(['FIPS', \n",
    "                   'year', \n",
    "                   'county', \n",
    "                   'state', \n",
    "                   'state_po', \n",
    "                   'office'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "**2. The built-in groupby function** allows us to perform an operation using the unique values from a specified set of fields. This is useful because we can then count how many rows exist for a given FIPS and Year combination, which essentially lets us group data by the candidate that it pertains to. See the following example: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "hidden": true
   },
   "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></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>candidate</th>\n",
       "      <th>party</th>\n",
       "      <th>candidatevotes</th>\n",
       "      <th>totalvotes</th>\n",
       "      <th>version</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FIPS</th>\n",
       "      <th>year</th>\n",
       "      <th>county</th>\n",
       "      <th>state</th>\n",
       "      <th>state_po</th>\n",
       "      <th>office</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=\"4\" valign=\"top\">01001</th>\n",
       "      <th rowspan=\"4\" valign=\"top\">2000</th>\n",
       "      <th rowspan=\"4\" valign=\"top\">Autauga</th>\n",
       "      <th rowspan=\"4\" valign=\"top\">Alabama</th>\n",
       "      <th rowspan=\"4\" valign=\"top\">AL</th>\n",
       "      <th rowspan=\"4\" valign=\"top\">President</th>\n",
       "      <th>1</th>\n",
       "      <td>Al Gore</td>\n",
       "      <td>democrat</td>\n",
       "      <td>4942.0</td>\n",
       "      <td>17208</td>\n",
       "      <td>20190722</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>George W. Bush</td>\n",
       "      <td>republican</td>\n",
       "      <td>11993.0</td>\n",
       "      <td>17208</td>\n",
       "      <td>20190722</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Ralph Nader</td>\n",
       "      <td>green</td>\n",
       "      <td>160.0</td>\n",
       "      <td>17208</td>\n",
       "      <td>20190722</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Other</td>\n",
       "      <td>NaN</td>\n",
       "      <td>113.0</td>\n",
       "      <td>17208</td>\n",
       "      <td>20190722</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>01003</th>\n",
       "      <th>2000</th>\n",
       "      <th>Baldwin</th>\n",
       "      <th>Alabama</th>\n",
       "      <th>AL</th>\n",
       "      <th>President</th>\n",
       "      <th>1</th>\n",
       "      <td>Al Gore</td>\n",
       "      <td>democrat</td>\n",
       "      <td>13997.0</td>\n",
       "      <td>56480</td>\n",
       "      <td>20190722</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">56043</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">2016</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">Washakie</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">Wyoming</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">WY</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">President</th>\n",
       "      <th>2</th>\n",
       "      <td>Donald Trump</td>\n",
       "      <td>republican</td>\n",
       "      <td>2911.0</td>\n",
       "      <td>3814</td>\n",
       "      <td>20190722</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Other</td>\n",
       "      <td>NaN</td>\n",
       "      <td>371.0</td>\n",
       "      <td>3814</td>\n",
       "      <td>20190722</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">56045</th>\n",
       "      <th rowspan=\"3\" valign=\"top\">2016</th>\n",
       "      <th rowspan=\"3\" valign=\"top\">Weston</th>\n",
       "      <th rowspan=\"3\" valign=\"top\">Wyoming</th>\n",
       "      <th rowspan=\"3\" valign=\"top\">WY</th>\n",
       "      <th rowspan=\"3\" valign=\"top\">President</th>\n",
       "      <th>1</th>\n",
       "      <td>Hillary Clinton</td>\n",
       "      <td>democrat</td>\n",
       "      <td>299.0</td>\n",
       "      <td>3526</td>\n",
       "      <td>20190722</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Donald Trump</td>\n",
       "      <td>republican</td>\n",
       "      <td>3033.0</td>\n",
       "      <td>3526</td>\n",
       "      <td>20190722</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Other</td>\n",
       "      <td>NaN</td>\n",
       "      <td>194.0</td>\n",
       "      <td>3526</td>\n",
       "      <td>20190722</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>50460 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                        candidate  ...   version\n",
       "FIPS  year county   state   state_po office                        ...          \n",
       "01001 2000 Autauga  Alabama AL       President 1          Al Gore  ...  20190722\n",
       "                                               2   George W. Bush  ...  20190722\n",
       "                                               3      Ralph Nader  ...  20190722\n",
       "                                               4            Other  ...  20190722\n",
       "01003 2000 Baldwin  Alabama AL       President 1          Al Gore  ...  20190722\n",
       "...                                                           ...  ...       ...\n",
       "56043 2016 Washakie Wyoming WY       President 2     Donald Trump  ...  20190722\n",
       "                                               3            Other  ...  20190722\n",
       "56045 2016 Weston   Wyoming WY       President 1  Hillary Clinton  ...  20190722\n",
       "                                               2     Donald Trump  ...  20190722\n",
       "                                               3            Other  ...  20190722\n",
       "\n",
       "[50460 rows x 5 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_df.set_index(['FIPS', \n",
    "                   'year', \n",
    "                   'county', \n",
    "                   'state', \n",
    "                   'state_po', \n",
    "                   'office', \n",
    "                   data_df.groupby(['FIPS', 'year']).cumcount()+1])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "**3. The ability to unstack a table** allows us to perform the table pivot, which essentially \"rotates\" the table and makes rows into columns (or columns into rows)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "hidden": true
   },
   "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></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"4\" halign=\"left\">candidate</th>\n",
       "      <th colspan=\"4\" halign=\"left\">party</th>\n",
       "      <th colspan=\"4\" halign=\"left\">candidatevotes</th>\n",
       "      <th colspan=\"4\" halign=\"left\">totalvotes</th>\n",
       "      <th colspan=\"4\" halign=\"left\">version</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FIPS</th>\n",
       "      <th>year</th>\n",
       "      <th>county</th>\n",
       "      <th>state</th>\n",
       "      <th>state_po</th>\n",
       "      <th>office</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",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">01001</th>\n",
       "      <th>2000</th>\n",
       "      <th>Autauga</th>\n",
       "      <th>Alabama</th>\n",
       "      <th>AL</th>\n",
       "      <th>President</th>\n",
       "      <td>Al Gore</td>\n",
       "      <td>George W. Bush</td>\n",
       "      <td>Ralph Nader</td>\n",
       "      <td>Other</td>\n",
       "      <td>democrat</td>\n",
       "      <td>republican</td>\n",
       "      <td>green</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4942.0</td>\n",
       "      <td>11993.0</td>\n",
       "      <td>160.0</td>\n",
       "      <td>113.0</td>\n",
       "      <td>17208.0</td>\n",
       "      <td>17208.0</td>\n",
       "      <td>17208.0</td>\n",
       "      <td>17208.0</td>\n",
       "      <td>20190722.0</td>\n",
       "      <td>20190722.0</td>\n",
       "      <td>20190722.0</td>\n",
       "      <td>20190722.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2004</th>\n",
       "      <th>Autauga</th>\n",
       "      <th>Alabama</th>\n",
       "      <th>AL</th>\n",
       "      <th>President</th>\n",
       "      <td>John Kerry</td>\n",
       "      <td>George W. Bush</td>\n",
       "      <td>Other</td>\n",
       "      <td>NaN</td>\n",
       "      <td>democrat</td>\n",
       "      <td>republican</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4758.0</td>\n",
       "      <td>15196.0</td>\n",
       "      <td>127.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20081.0</td>\n",
       "      <td>20081.0</td>\n",
       "      <td>20081.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20190722.0</td>\n",
       "      <td>20190722.0</td>\n",
       "      <td>20190722.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2008</th>\n",
       "      <th>Autauga</th>\n",
       "      <th>Alabama</th>\n",
       "      <th>AL</th>\n",
       "      <th>President</th>\n",
       "      <td>Barack Obama</td>\n",
       "      <td>John McCain</td>\n",
       "      <td>Other</td>\n",
       "      <td>NaN</td>\n",
       "      <td>democrat</td>\n",
       "      <td>republican</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6093.0</td>\n",
       "      <td>17403.0</td>\n",
       "      <td>145.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>23641.0</td>\n",
       "      <td>23641.0</td>\n",
       "      <td>23641.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20190722.0</td>\n",
       "      <td>20190722.0</td>\n",
       "      <td>20190722.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012</th>\n",
       "      <th>Autauga</th>\n",
       "      <th>Alabama</th>\n",
       "      <th>AL</th>\n",
       "      <th>President</th>\n",
       "      <td>Barack Obama</td>\n",
       "      <td>Mitt Romney</td>\n",
       "      <td>Other</td>\n",
       "      <td>NaN</td>\n",
       "      <td>democrat</td>\n",
       "      <td>republican</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6363.0</td>\n",
       "      <td>17379.0</td>\n",
       "      <td>190.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>23932.0</td>\n",
       "      <td>23932.0</td>\n",
       "      <td>23932.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20190722.0</td>\n",
       "      <td>20190722.0</td>\n",
       "      <td>20190722.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016</th>\n",
       "      <th>Autauga</th>\n",
       "      <th>Alabama</th>\n",
       "      <th>AL</th>\n",
       "      <th>President</th>\n",
       "      <td>Hillary Clinton</td>\n",
       "      <td>Donald Trump</td>\n",
       "      <td>Other</td>\n",
       "      <td>NaN</td>\n",
       "      <td>democrat</td>\n",
       "      <td>republican</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5936.0</td>\n",
       "      <td>18172.0</td>\n",
       "      <td>865.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>24973.0</td>\n",
       "      <td>24973.0</td>\n",
       "      <td>24973.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20190722.0</td>\n",
       "      <td>20190722.0</td>\n",
       "      <td>20190722.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">56045</th>\n",
       "      <th>2000</th>\n",
       "      <th>Weston</th>\n",
       "      <th>Wyoming</th>\n",
       "      <th>WY</th>\n",
       "      <th>President</th>\n",
       "      <td>Al Gore</td>\n",
       "      <td>George W. Bush</td>\n",
       "      <td>Ralph Nader</td>\n",
       "      <td>Other</td>\n",
       "      <td>democrat</td>\n",
       "      <td>republican</td>\n",
       "      <td>green</td>\n",
       "      <td>NaN</td>\n",
       "      <td>449.0</td>\n",
       "      <td>2521.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>3060.0</td>\n",
       "      <td>3060.0</td>\n",
       "      <td>3060.0</td>\n",
       "      <td>3060.0</td>\n",
       "      <td>20190722.0</td>\n",
       "      <td>20190722.0</td>\n",
       "      <td>20190722.0</td>\n",
       "      <td>20190722.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2004</th>\n",
       "      <th>Weston</th>\n",
       "      <th>Wyoming</th>\n",
       "      <th>WY</th>\n",
       "      <th>President</th>\n",
       "      <td>John Kerry</td>\n",
       "      <td>George W. Bush</td>\n",
       "      <td>Other</td>\n",
       "      <td>NaN</td>\n",
       "      <td>democrat</td>\n",
       "      <td>republican</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>580.0</td>\n",
       "      <td>2739.0</td>\n",
       "      <td>73.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3392.0</td>\n",
       "      <td>3392.0</td>\n",
       "      <td>3392.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20190722.0</td>\n",
       "      <td>20190722.0</td>\n",
       "      <td>20190722.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2008</th>\n",
       "      <th>Weston</th>\n",
       "      <th>Wyoming</th>\n",
       "      <th>WY</th>\n",
       "      <th>President</th>\n",
       "      <td>Barack Obama</td>\n",
       "      <td>John McCain</td>\n",
       "      <td>Other</td>\n",
       "      <td>NaN</td>\n",
       "      <td>democrat</td>\n",
       "      <td>republican</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>658.0</td>\n",
       "      <td>2618.0</td>\n",
       "      <td>138.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3414.0</td>\n",
       "      <td>3414.0</td>\n",
       "      <td>3414.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20190722.0</td>\n",
       "      <td>20190722.0</td>\n",
       "      <td>20190722.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012</th>\n",
       "      <th>Weston</th>\n",
       "      <th>Wyoming</th>\n",
       "      <th>WY</th>\n",
       "      <th>President</th>\n",
       "      <td>Barack Obama</td>\n",
       "      <td>Mitt Romney</td>\n",
       "      <td>Other</td>\n",
       "      <td>NaN</td>\n",
       "      <td>democrat</td>\n",
       "      <td>republican</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>422.0</td>\n",
       "      <td>2821.0</td>\n",
       "      <td>116.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3359.0</td>\n",
       "      <td>3359.0</td>\n",
       "      <td>3359.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20190722.0</td>\n",
       "      <td>20190722.0</td>\n",
       "      <td>20190722.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016</th>\n",
       "      <th>Weston</th>\n",
       "      <th>Wyoming</th>\n",
       "      <th>WY</th>\n",
       "      <th>President</th>\n",
       "      <td>Hillary Clinton</td>\n",
       "      <td>Donald Trump</td>\n",
       "      <td>Other</td>\n",
       "      <td>NaN</td>\n",
       "      <td>democrat</td>\n",
       "      <td>republican</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>299.0</td>\n",
       "      <td>3033.0</td>\n",
       "      <td>194.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3526.0</td>\n",
       "      <td>3526.0</td>\n",
       "      <td>3526.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20190722.0</td>\n",
       "      <td>20190722.0</td>\n",
       "      <td>20190722.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>15769 rows × 20 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                     candidate  ...     version\n",
       "                                                             1  ...           4\n",
       "FIPS  year county  state   state_po office                      ...            \n",
       "01001 2000 Autauga Alabama AL       President          Al Gore  ...  20190722.0\n",
       "      2004 Autauga Alabama AL       President       John Kerry  ...         NaN\n",
       "      2008 Autauga Alabama AL       President     Barack Obama  ...         NaN\n",
       "      2012 Autauga Alabama AL       President     Barack Obama  ...         NaN\n",
       "      2016 Autauga Alabama AL       President  Hillary Clinton  ...         NaN\n",
       "...                                                        ...  ...         ...\n",
       "56045 2000 Weston  Wyoming WY       President          Al Gore  ...  20190722.0\n",
       "      2004 Weston  Wyoming WY       President       John Kerry  ...         NaN\n",
       "      2008 Weston  Wyoming WY       President     Barack Obama  ...         NaN\n",
       "      2012 Weston  Wyoming WY       President     Barack Obama  ...         NaN\n",
       "      2016 Weston  Wyoming WY       President  Hillary Clinton  ...         NaN\n",
       "\n",
       "[15769 rows x 20 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_df.set_index(['FIPS', \n",
    "                   'year', \n",
    "                   'county', \n",
    "                   'state', \n",
    "                   'state_po', \n",
    "                   'office', \n",
    "                   data_df.groupby(['FIPS', 'year']).cumcount()+1]).unstack()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "**4. Finally, we put it all together and rename the output columns**, using the pandas ability to rename fields and removing extraneous fields we no longer need. This produces the table format we need."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "hidden": true
   },
   "outputs": [],
   "source": [
    "# Reformat the dataframe by setting a multiindex (set_index with multiple fields) and pivoting the table (unstack)\n",
    "df_out = data_df.set_index(['FIPS', \n",
    "                            'year', \n",
    "                            'county', \n",
    "                            'state', \n",
    "                            'state_po', \n",
    "                            'office', \n",
    "                            data_df.groupby(['FIPS', 'year']).cumcount()+1]).unstack()\n",
    "\n",
    "\n",
    "# Use the indexes for the columns to set column names (Ex: candidate_1, candidate_2, votes_1, votes_2, etc.)\n",
    "df_out.columns = df_out.columns.map('{0[0]}_{0[1]}'.format)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "hidden": true
   },
   "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></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>candidate_1</th>\n",
       "      <th>candidate_2</th>\n",
       "      <th>candidate_3</th>\n",
       "      <th>candidate_4</th>\n",
       "      <th>party_1</th>\n",
       "      <th>party_2</th>\n",
       "      <th>party_3</th>\n",
       "      <th>party_4</th>\n",
       "      <th>candidatevotes_1</th>\n",
       "      <th>candidatevotes_2</th>\n",
       "      <th>candidatevotes_3</th>\n",
       "      <th>candidatevotes_4</th>\n",
       "      <th>totalvotes_1</th>\n",
       "      <th>totalvotes_2</th>\n",
       "      <th>totalvotes_3</th>\n",
       "      <th>totalvotes_4</th>\n",
       "      <th>version_1</th>\n",
       "      <th>version_2</th>\n",
       "      <th>version_3</th>\n",
       "      <th>version_4</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FIPS</th>\n",
       "      <th>year</th>\n",
       "      <th>county</th>\n",
       "      <th>state</th>\n",
       "      <th>state_po</th>\n",
       "      <th>office</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",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">01001</th>\n",
       "      <th>2000</th>\n",
       "      <th>Autauga</th>\n",
       "      <th>Alabama</th>\n",
       "      <th>AL</th>\n",
       "      <th>President</th>\n",
       "      <td>Al Gore</td>\n",
       "      <td>George W. Bush</td>\n",
       "      <td>Ralph Nader</td>\n",
       "      <td>Other</td>\n",
       "      <td>democrat</td>\n",
       "      <td>republican</td>\n",
       "      <td>green</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4942.0</td>\n",
       "      <td>11993.0</td>\n",
       "      <td>160.0</td>\n",
       "      <td>113.0</td>\n",
       "      <td>17208.0</td>\n",
       "      <td>17208.0</td>\n",
       "      <td>17208.0</td>\n",
       "      <td>17208.0</td>\n",
       "      <td>20190722.0</td>\n",
       "      <td>20190722.0</td>\n",
       "      <td>20190722.0</td>\n",
       "      <td>20190722.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2004</th>\n",
       "      <th>Autauga</th>\n",
       "      <th>Alabama</th>\n",
       "      <th>AL</th>\n",
       "      <th>President</th>\n",
       "      <td>John Kerry</td>\n",
       "      <td>George W. Bush</td>\n",
       "      <td>Other</td>\n",
       "      <td>NaN</td>\n",
       "      <td>democrat</td>\n",
       "      <td>republican</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4758.0</td>\n",
       "      <td>15196.0</td>\n",
       "      <td>127.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20081.0</td>\n",
       "      <td>20081.0</td>\n",
       "      <td>20081.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20190722.0</td>\n",
       "      <td>20190722.0</td>\n",
       "      <td>20190722.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2008</th>\n",
       "      <th>Autauga</th>\n",
       "      <th>Alabama</th>\n",
       "      <th>AL</th>\n",
       "      <th>President</th>\n",
       "      <td>Barack Obama</td>\n",
       "      <td>John McCain</td>\n",
       "      <td>Other</td>\n",
       "      <td>NaN</td>\n",
       "      <td>democrat</td>\n",
       "      <td>republican</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6093.0</td>\n",
       "      <td>17403.0</td>\n",
       "      <td>145.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>23641.0</td>\n",
       "      <td>23641.0</td>\n",
       "      <td>23641.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20190722.0</td>\n",
       "      <td>20190722.0</td>\n",
       "      <td>20190722.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012</th>\n",
       "      <th>Autauga</th>\n",
       "      <th>Alabama</th>\n",
       "      <th>AL</th>\n",
       "      <th>President</th>\n",
       "      <td>Barack Obama</td>\n",
       "      <td>Mitt Romney</td>\n",
       "      <td>Other</td>\n",
       "      <td>NaN</td>\n",
       "      <td>democrat</td>\n",
       "      <td>republican</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6363.0</td>\n",
       "      <td>17379.0</td>\n",
       "      <td>190.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>23932.0</td>\n",
       "      <td>23932.0</td>\n",
       "      <td>23932.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20190722.0</td>\n",
       "      <td>20190722.0</td>\n",
       "      <td>20190722.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016</th>\n",
       "      <th>Autauga</th>\n",
       "      <th>Alabama</th>\n",
       "      <th>AL</th>\n",
       "      <th>President</th>\n",
       "      <td>Hillary Clinton</td>\n",
       "      <td>Donald Trump</td>\n",
       "      <td>Other</td>\n",
       "      <td>NaN</td>\n",
       "      <td>democrat</td>\n",
       "      <td>republican</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5936.0</td>\n",
       "      <td>18172.0</td>\n",
       "      <td>865.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>24973.0</td>\n",
       "      <td>24973.0</td>\n",
       "      <td>24973.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20190722.0</td>\n",
       "      <td>20190722.0</td>\n",
       "      <td>20190722.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">56045</th>\n",
       "      <th>2000</th>\n",
       "      <th>Weston</th>\n",
       "      <th>Wyoming</th>\n",
       "      <th>WY</th>\n",
       "      <th>President</th>\n",
       "      <td>Al Gore</td>\n",
       "      <td>George W. Bush</td>\n",
       "      <td>Ralph Nader</td>\n",
       "      <td>Other</td>\n",
       "      <td>democrat</td>\n",
       "      <td>republican</td>\n",
       "      <td>green</td>\n",
       "      <td>NaN</td>\n",
       "      <td>449.0</td>\n",
       "      <td>2521.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>3060.0</td>\n",
       "      <td>3060.0</td>\n",
       "      <td>3060.0</td>\n",
       "      <td>3060.0</td>\n",
       "      <td>20190722.0</td>\n",
       "      <td>20190722.0</td>\n",
       "      <td>20190722.0</td>\n",
       "      <td>20190722.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2004</th>\n",
       "      <th>Weston</th>\n",
       "      <th>Wyoming</th>\n",
       "      <th>WY</th>\n",
       "      <th>President</th>\n",
       "      <td>John Kerry</td>\n",
       "      <td>George W. Bush</td>\n",
       "      <td>Other</td>\n",
       "      <td>NaN</td>\n",
       "      <td>democrat</td>\n",
       "      <td>republican</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>580.0</td>\n",
       "      <td>2739.0</td>\n",
       "      <td>73.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3392.0</td>\n",
       "      <td>3392.0</td>\n",
       "      <td>3392.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20190722.0</td>\n",
       "      <td>20190722.0</td>\n",
       "      <td>20190722.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2008</th>\n",
       "      <th>Weston</th>\n",
       "      <th>Wyoming</th>\n",
       "      <th>WY</th>\n",
       "      <th>President</th>\n",
       "      <td>Barack Obama</td>\n",
       "      <td>John McCain</td>\n",
       "      <td>Other</td>\n",
       "      <td>NaN</td>\n",
       "      <td>democrat</td>\n",
       "      <td>republican</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>658.0</td>\n",
       "      <td>2618.0</td>\n",
       "      <td>138.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3414.0</td>\n",
       "      <td>3414.0</td>\n",
       "      <td>3414.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20190722.0</td>\n",
       "      <td>20190722.0</td>\n",
       "      <td>20190722.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012</th>\n",
       "      <th>Weston</th>\n",
       "      <th>Wyoming</th>\n",
       "      <th>WY</th>\n",
       "      <th>President</th>\n",
       "      <td>Barack Obama</td>\n",
       "      <td>Mitt Romney</td>\n",
       "      <td>Other</td>\n",
       "      <td>NaN</td>\n",
       "      <td>democrat</td>\n",
       "      <td>republican</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>422.0</td>\n",
       "      <td>2821.0</td>\n",
       "      <td>116.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3359.0</td>\n",
       "      <td>3359.0</td>\n",
       "      <td>3359.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20190722.0</td>\n",
       "      <td>20190722.0</td>\n",
       "      <td>20190722.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016</th>\n",
       "      <th>Weston</th>\n",
       "      <th>Wyoming</th>\n",
       "      <th>WY</th>\n",
       "      <th>President</th>\n",
       "      <td>Hillary Clinton</td>\n",
       "      <td>Donald Trump</td>\n",
       "      <td>Other</td>\n",
       "      <td>NaN</td>\n",
       "      <td>democrat</td>\n",
       "      <td>republican</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>299.0</td>\n",
       "      <td>3033.0</td>\n",
       "      <td>194.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3526.0</td>\n",
       "      <td>3526.0</td>\n",
       "      <td>3526.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20190722.0</td>\n",
       "      <td>20190722.0</td>\n",
       "      <td>20190722.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>15769 rows × 20 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                   candidate_1  ...   version_4\n",
       "FIPS  year county  state   state_po office                      ...            \n",
       "01001 2000 Autauga Alabama AL       President          Al Gore  ...  20190722.0\n",
       "      2004 Autauga Alabama AL       President       John Kerry  ...         NaN\n",
       "      2008 Autauga Alabama AL       President     Barack Obama  ...         NaN\n",
       "      2012 Autauga Alabama AL       President     Barack Obama  ...         NaN\n",
       "      2016 Autauga Alabama AL       President  Hillary Clinton  ...         NaN\n",
       "...                                                        ...  ...         ...\n",
       "56045 2000 Weston  Wyoming WY       President          Al Gore  ...  20190722.0\n",
       "      2004 Weston  Wyoming WY       President       John Kerry  ...         NaN\n",
       "      2008 Weston  Wyoming WY       President     Barack Obama  ...         NaN\n",
       "      2012 Weston  Wyoming WY       President     Barack Obama  ...         NaN\n",
       "      2016 Weston  Wyoming WY       President  Hillary Clinton  ...         NaN\n",
       "\n",
       "[15769 rows x 20 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_out"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "hidden": true
   },
   "outputs": [],
   "source": [
    "# Rename columns \n",
    "df_out = df_out.rename(columns={\"candidate_1\": \"candidate_dem\",\n",
    "                                \"candidatevotes_1\": \"votes_dem\",\n",
    "                                \"candidate_2\": \"candidate_gop\",\n",
    "                                \"candidatevotes_2\": \"votes_gop\",\n",
    "                                \"totalvotes_1\": \"votes_total\"\n",
    "                                \n",
    "                                })\n",
    "\n",
    "# Keep only the necessary columns\n",
    "df_out = df_out[[\"candidate_dem\", \"votes_dem\",\n",
    "                 \"candidate_gop\", \"votes_gop\",\n",
    "                 \"votes_total\"]]\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "hidden": true
   },
   "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></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>candidate_dem</th>\n",
       "      <th>votes_dem</th>\n",
       "      <th>candidate_gop</th>\n",
       "      <th>votes_gop</th>\n",
       "      <th>votes_total</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FIPS</th>\n",
       "      <th>year</th>\n",
       "      <th>county</th>\n",
       "      <th>state</th>\n",
       "      <th>state_po</th>\n",
       "      <th>office</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=\"5\" valign=\"top\">01001</th>\n",
       "      <th>2000</th>\n",
       "      <th>Autauga</th>\n",
       "      <th>Alabama</th>\n",
       "      <th>AL</th>\n",
       "      <th>President</th>\n",
       "      <td>Al Gore</td>\n",
       "      <td>4942.0</td>\n",
       "      <td>George W. Bush</td>\n",
       "      <td>11993.0</td>\n",
       "      <td>17208.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2004</th>\n",
       "      <th>Autauga</th>\n",
       "      <th>Alabama</th>\n",
       "      <th>AL</th>\n",
       "      <th>President</th>\n",
       "      <td>John Kerry</td>\n",
       "      <td>4758.0</td>\n",
       "      <td>George W. Bush</td>\n",
       "      <td>15196.0</td>\n",
       "      <td>20081.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2008</th>\n",
       "      <th>Autauga</th>\n",
       "      <th>Alabama</th>\n",
       "      <th>AL</th>\n",
       "      <th>President</th>\n",
       "      <td>Barack Obama</td>\n",
       "      <td>6093.0</td>\n",
       "      <td>John McCain</td>\n",
       "      <td>17403.0</td>\n",
       "      <td>23641.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012</th>\n",
       "      <th>Autauga</th>\n",
       "      <th>Alabama</th>\n",
       "      <th>AL</th>\n",
       "      <th>President</th>\n",
       "      <td>Barack Obama</td>\n",
       "      <td>6363.0</td>\n",
       "      <td>Mitt Romney</td>\n",
       "      <td>17379.0</td>\n",
       "      <td>23932.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016</th>\n",
       "      <th>Autauga</th>\n",
       "      <th>Alabama</th>\n",
       "      <th>AL</th>\n",
       "      <th>President</th>\n",
       "      <td>Hillary Clinton</td>\n",
       "      <td>5936.0</td>\n",
       "      <td>Donald Trump</td>\n",
       "      <td>18172.0</td>\n",
       "      <td>24973.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">56045</th>\n",
       "      <th>2000</th>\n",
       "      <th>Weston</th>\n",
       "      <th>Wyoming</th>\n",
       "      <th>WY</th>\n",
       "      <th>President</th>\n",
       "      <td>Al Gore</td>\n",
       "      <td>449.0</td>\n",
       "      <td>George W. Bush</td>\n",
       "      <td>2521.0</td>\n",
       "      <td>3060.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2004</th>\n",
       "      <th>Weston</th>\n",
       "      <th>Wyoming</th>\n",
       "      <th>WY</th>\n",
       "      <th>President</th>\n",
       "      <td>John Kerry</td>\n",
       "      <td>580.0</td>\n",
       "      <td>George W. Bush</td>\n",
       "      <td>2739.0</td>\n",
       "      <td>3392.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2008</th>\n",
       "      <th>Weston</th>\n",
       "      <th>Wyoming</th>\n",
       "      <th>WY</th>\n",
       "      <th>President</th>\n",
       "      <td>Barack Obama</td>\n",
       "      <td>658.0</td>\n",
       "      <td>John McCain</td>\n",
       "      <td>2618.0</td>\n",
       "      <td>3414.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012</th>\n",
       "      <th>Weston</th>\n",
       "      <th>Wyoming</th>\n",
       "      <th>WY</th>\n",
       "      <th>President</th>\n",
       "      <td>Barack Obama</td>\n",
       "      <td>422.0</td>\n",
       "      <td>Mitt Romney</td>\n",
       "      <td>2821.0</td>\n",
       "      <td>3359.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016</th>\n",
       "      <th>Weston</th>\n",
       "      <th>Wyoming</th>\n",
       "      <th>WY</th>\n",
       "      <th>President</th>\n",
       "      <td>Hillary Clinton</td>\n",
       "      <td>299.0</td>\n",
       "      <td>Donald Trump</td>\n",
       "      <td>3033.0</td>\n",
       "      <td>3526.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>15769 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 candidate_dem  ...  votes_total\n",
       "FIPS  year county  state   state_po office                      ...             \n",
       "01001 2000 Autauga Alabama AL       President          Al Gore  ...      17208.0\n",
       "      2004 Autauga Alabama AL       President       John Kerry  ...      20081.0\n",
       "      2008 Autauga Alabama AL       President     Barack Obama  ...      23641.0\n",
       "      2012 Autauga Alabama AL       President     Barack Obama  ...      23932.0\n",
       "      2016 Autauga Alabama AL       President  Hillary Clinton  ...      24973.0\n",
       "...                                                        ...  ...          ...\n",
       "56045 2000 Weston  Wyoming WY       President          Al Gore  ...       3060.0\n",
       "      2004 Weston  Wyoming WY       President       John Kerry  ...       3392.0\n",
       "      2008 Weston  Wyoming WY       President     Barack Obama  ...       3414.0\n",
       "      2012 Weston  Wyoming WY       President     Barack Obama  ...       3359.0\n",
       "      2016 Weston  Wyoming WY       President  Hillary Clinton  ...       3526.0\n",
       "\n",
       "[15769 rows x 5 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_out"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "hidden": true
   },
   "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>FIPS</th>\n",
       "      <th>year</th>\n",
       "      <th>county</th>\n",
       "      <th>state</th>\n",
       "      <th>state_abbrev</th>\n",
       "      <th>office</th>\n",
       "      <th>candidate_dem</th>\n",
       "      <th>votes_dem</th>\n",
       "      <th>candidate_gop</th>\n",
       "      <th>votes_gop</th>\n",
       "      <th>votes_total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>01001</td>\n",
       "      <td>2000</td>\n",
       "      <td>Autauga</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>President</td>\n",
       "      <td>Al Gore</td>\n",
       "      <td>4942.0</td>\n",
       "      <td>George W. Bush</td>\n",
       "      <td>11993.0</td>\n",
       "      <td>17208.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>01001</td>\n",
       "      <td>2004</td>\n",
       "      <td>Autauga</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>President</td>\n",
       "      <td>John Kerry</td>\n",
       "      <td>4758.0</td>\n",
       "      <td>George W. Bush</td>\n",
       "      <td>15196.0</td>\n",
       "      <td>20081.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>01001</td>\n",
       "      <td>2008</td>\n",
       "      <td>Autauga</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>President</td>\n",
       "      <td>Barack Obama</td>\n",
       "      <td>6093.0</td>\n",
       "      <td>John McCain</td>\n",
       "      <td>17403.0</td>\n",
       "      <td>23641.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>01001</td>\n",
       "      <td>2012</td>\n",
       "      <td>Autauga</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>President</td>\n",
       "      <td>Barack Obama</td>\n",
       "      <td>6363.0</td>\n",
       "      <td>Mitt Romney</td>\n",
       "      <td>17379.0</td>\n",
       "      <td>23932.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>01001</td>\n",
       "      <td>2016</td>\n",
       "      <td>Autauga</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>President</td>\n",
       "      <td>Hillary Clinton</td>\n",
       "      <td>5936.0</td>\n",
       "      <td>Donald Trump</td>\n",
       "      <td>18172.0</td>\n",
       "      <td>24973.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    FIPS  year   county  ...   candidate_gop votes_gop votes_total\n",
       "0  01001  2000  Autauga  ...  George W. Bush   11993.0     17208.0\n",
       "1  01001  2004  Autauga  ...  George W. Bush   15196.0     20081.0\n",
       "2  01001  2008  Autauga  ...     John McCain   17403.0     23641.0\n",
       "3  01001  2012  Autauga  ...     Mitt Romney   17379.0     23932.0\n",
       "4  01001  2016  Autauga  ...    Donald Trump   18172.0     24973.0\n",
       "\n",
       "[5 rows x 11 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Remove the multiindex since we no longer need these fields to be \"locked\" for the pivot\n",
    "df_out.reset_index(inplace=True)\n",
    "\n",
    "# Rename \"state_po\": \"state_abbrev\"\n",
    "\n",
    "df_out = df_out.rename(columns={\"state_po\": \"state_abbrev\"})\n",
    "\n",
    "# Print out the first few records to confirm everything worked\n",
    "df_out.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "### Calculate Additional Columns/Features"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "##### Calculate total votes for non major party"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "hidden": true
   },
   "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>FIPS</th>\n",
       "      <th>year</th>\n",
       "      <th>county</th>\n",
       "      <th>state</th>\n",
       "      <th>state_abbrev</th>\n",
       "      <th>office</th>\n",
       "      <th>candidate_dem</th>\n",
       "      <th>votes_dem</th>\n",
       "      <th>candidate_gop</th>\n",
       "      <th>votes_gop</th>\n",
       "      <th>votes_total</th>\n",
       "      <th>votes_other</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>01001</td>\n",
       "      <td>2000</td>\n",
       "      <td>Autauga</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>President</td>\n",
       "      <td>Al Gore</td>\n",
       "      <td>4942.0</td>\n",
       "      <td>George W. Bush</td>\n",
       "      <td>11993.0</td>\n",
       "      <td>17208.0</td>\n",
       "      <td>273.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>01001</td>\n",
       "      <td>2004</td>\n",
       "      <td>Autauga</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>President</td>\n",
       "      <td>John Kerry</td>\n",
       "      <td>4758.0</td>\n",
       "      <td>George W. Bush</td>\n",
       "      <td>15196.0</td>\n",
       "      <td>20081.0</td>\n",
       "      <td>127.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>01001</td>\n",
       "      <td>2008</td>\n",
       "      <td>Autauga</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>President</td>\n",
       "      <td>Barack Obama</td>\n",
       "      <td>6093.0</td>\n",
       "      <td>John McCain</td>\n",
       "      <td>17403.0</td>\n",
       "      <td>23641.0</td>\n",
       "      <td>145.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>01001</td>\n",
       "      <td>2012</td>\n",
       "      <td>Autauga</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>President</td>\n",
       "      <td>Barack Obama</td>\n",
       "      <td>6363.0</td>\n",
       "      <td>Mitt Romney</td>\n",
       "      <td>17379.0</td>\n",
       "      <td>23932.0</td>\n",
       "      <td>190.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>01001</td>\n",
       "      <td>2016</td>\n",
       "      <td>Autauga</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>President</td>\n",
       "      <td>Hillary Clinton</td>\n",
       "      <td>5936.0</td>\n",
       "      <td>Donald Trump</td>\n",
       "      <td>18172.0</td>\n",
       "      <td>24973.0</td>\n",
       "      <td>865.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    FIPS  year   county  ... votes_gop votes_total votes_other\n",
       "0  01001  2000  Autauga  ...   11993.0     17208.0       273.0\n",
       "1  01001  2004  Autauga  ...   15196.0     20081.0       127.0\n",
       "2  01001  2008  Autauga  ...   17403.0     23641.0       145.0\n",
       "3  01001  2012  Autauga  ...   17379.0     23932.0       190.0\n",
       "4  01001  2016  Autauga  ...   18172.0     24973.0       865.0\n",
       "\n",
       "[5 rows x 12 columns]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Calculate votes that did not go for the Democrat or Republican party\n",
    "df_out['votes_other'] = df_out['votes_total'] - (df_out['votes_dem'] + df_out['votes_gop'])\n",
    "df_out.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "##### Create additional attributes (voter percentages and raw differences)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "hidden": true
   },
   "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>FIPS</th>\n",
       "      <th>year</th>\n",
       "      <th>county</th>\n",
       "      <th>state</th>\n",
       "      <th>state_abbrev</th>\n",
       "      <th>office</th>\n",
       "      <th>candidate_dem</th>\n",
       "      <th>votes_dem</th>\n",
       "      <th>candidate_gop</th>\n",
       "      <th>votes_gop</th>\n",
       "      <th>votes_total</th>\n",
       "      <th>votes_other</th>\n",
       "      <th>voter_share_major_party</th>\n",
       "      <th>voter_share_dem</th>\n",
       "      <th>voter_share_gop</th>\n",
       "      <th>voter_share_other</th>\n",
       "      <th>rawdiff_dem_vs_gop</th>\n",
       "      <th>rawdiff_gop_vs_dem</th>\n",
       "      <th>rawdiff_dem_vs_other</th>\n",
       "      <th>rawdiff_gop_vs_other</th>\n",
       "      <th>rawdiff_other_vs_dem</th>\n",
       "      <th>rawdiff_other_vs_gop</th>\n",
       "      <th>pctdiff_dem_vs_gop</th>\n",
       "      <th>pctdiff_gop_vs_dem</th>\n",
       "      <th>pctdiff_dem_vs_other</th>\n",
       "      <th>pctdiff_gop_vs_other</th>\n",
       "      <th>pctdiff_other_vs_dem</th>\n",
       "      <th>pctdiff_other_vs_gop</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>01001</td>\n",
       "      <td>2000</td>\n",
       "      <td>Autauga</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>President</td>\n",
       "      <td>Al Gore</td>\n",
       "      <td>4942.0</td>\n",
       "      <td>George W. Bush</td>\n",
       "      <td>11993.0</td>\n",
       "      <td>17208.0</td>\n",
       "      <td>273.0</td>\n",
       "      <td>0.984135</td>\n",
       "      <td>0.287192</td>\n",
       "      <td>0.696943</td>\n",
       "      <td>0.015865</td>\n",
       "      <td>-7051.0</td>\n",
       "      <td>7051.0</td>\n",
       "      <td>4669.0</td>\n",
       "      <td>11720.0</td>\n",
       "      <td>-4669.0</td>\n",
       "      <td>-11720.0</td>\n",
       "      <td>-0.409751</td>\n",
       "      <td>0.409751</td>\n",
       "      <td>0.271327</td>\n",
       "      <td>0.681079</td>\n",
       "      <td>-0.271327</td>\n",
       "      <td>-0.681079</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>01001</td>\n",
       "      <td>2004</td>\n",
       "      <td>Autauga</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>President</td>\n",
       "      <td>John Kerry</td>\n",
       "      <td>4758.0</td>\n",
       "      <td>George W. Bush</td>\n",
       "      <td>15196.0</td>\n",
       "      <td>20081.0</td>\n",
       "      <td>127.0</td>\n",
       "      <td>0.993676</td>\n",
       "      <td>0.236940</td>\n",
       "      <td>0.756735</td>\n",
       "      <td>0.006324</td>\n",
       "      <td>-10438.0</td>\n",
       "      <td>10438.0</td>\n",
       "      <td>4631.0</td>\n",
       "      <td>15069.0</td>\n",
       "      <td>-4631.0</td>\n",
       "      <td>-15069.0</td>\n",
       "      <td>-0.519795</td>\n",
       "      <td>0.519795</td>\n",
       "      <td>0.230616</td>\n",
       "      <td>0.750411</td>\n",
       "      <td>-0.230616</td>\n",
       "      <td>-0.750411</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>01001</td>\n",
       "      <td>2008</td>\n",
       "      <td>Autauga</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>President</td>\n",
       "      <td>Barack Obama</td>\n",
       "      <td>6093.0</td>\n",
       "      <td>John McCain</td>\n",
       "      <td>17403.0</td>\n",
       "      <td>23641.0</td>\n",
       "      <td>145.0</td>\n",
       "      <td>0.993867</td>\n",
       "      <td>0.257730</td>\n",
       "      <td>0.736136</td>\n",
       "      <td>0.006133</td>\n",
       "      <td>-11310.0</td>\n",
       "      <td>11310.0</td>\n",
       "      <td>5948.0</td>\n",
       "      <td>17258.0</td>\n",
       "      <td>-5948.0</td>\n",
       "      <td>-17258.0</td>\n",
       "      <td>-0.478406</td>\n",
       "      <td>0.478406</td>\n",
       "      <td>0.251597</td>\n",
       "      <td>0.730003</td>\n",
       "      <td>-0.251597</td>\n",
       "      <td>-0.730003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>01001</td>\n",
       "      <td>2012</td>\n",
       "      <td>Autauga</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>President</td>\n",
       "      <td>Barack Obama</td>\n",
       "      <td>6363.0</td>\n",
       "      <td>Mitt Romney</td>\n",
       "      <td>17379.0</td>\n",
       "      <td>23932.0</td>\n",
       "      <td>190.0</td>\n",
       "      <td>0.992061</td>\n",
       "      <td>0.265878</td>\n",
       "      <td>0.726183</td>\n",
       "      <td>0.007939</td>\n",
       "      <td>-11016.0</td>\n",
       "      <td>11016.0</td>\n",
       "      <td>6173.0</td>\n",
       "      <td>17189.0</td>\n",
       "      <td>-6173.0</td>\n",
       "      <td>-17189.0</td>\n",
       "      <td>-0.460304</td>\n",
       "      <td>0.460304</td>\n",
       "      <td>0.257939</td>\n",
       "      <td>0.718243</td>\n",
       "      <td>-0.257939</td>\n",
       "      <td>-0.718243</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>01001</td>\n",
       "      <td>2016</td>\n",
       "      <td>Autauga</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>President</td>\n",
       "      <td>Hillary Clinton</td>\n",
       "      <td>5936.0</td>\n",
       "      <td>Donald Trump</td>\n",
       "      <td>18172.0</td>\n",
       "      <td>24973.0</td>\n",
       "      <td>865.0</td>\n",
       "      <td>0.965363</td>\n",
       "      <td>0.237697</td>\n",
       "      <td>0.727666</td>\n",
       "      <td>0.034637</td>\n",
       "      <td>-12236.0</td>\n",
       "      <td>12236.0</td>\n",
       "      <td>5071.0</td>\n",
       "      <td>17307.0</td>\n",
       "      <td>-5071.0</td>\n",
       "      <td>-17307.0</td>\n",
       "      <td>-0.489969</td>\n",
       "      <td>0.489969</td>\n",
       "      <td>0.203059</td>\n",
       "      <td>0.693028</td>\n",
       "      <td>-0.203059</td>\n",
       "      <td>-0.693028</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    FIPS  year  ... pctdiff_other_vs_dem pctdiff_other_vs_gop\n",
       "0  01001  2000  ...            -0.271327            -0.681079\n",
       "1  01001  2004  ...            -0.230616            -0.750411\n",
       "2  01001  2008  ...            -0.251597            -0.730003\n",
       "3  01001  2012  ...            -0.257939            -0.718243\n",
       "4  01001  2016  ...            -0.203059            -0.693028\n",
       "\n",
       "[5 rows x 28 columns]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Calculate voter share attributes\n",
    "df_out['voter_share_major_party'] = (df_out['votes_dem'] + df_out['votes_gop']) / df_out['votes_total']\n",
    "df_out['voter_share_dem'] = df_out['votes_dem'] / df_out['votes_total']\n",
    "df_out['voter_share_gop'] = df_out['votes_gop'] / df_out['votes_total']\n",
    "df_out['voter_share_other'] = df_out['votes_other'] / df_out['votes_total']\n",
    "\n",
    "# Calculate raw difference attributes\n",
    "df_out['rawdiff_dem_vs_gop'] = df_out['votes_dem'] - df_out['votes_gop']\n",
    "df_out['rawdiff_gop_vs_dem'] = df_out['votes_gop'] - df_out['votes_dem']\n",
    "df_out['rawdiff_dem_vs_other'] = df_out['votes_dem'] - df_out['votes_other']\n",
    "df_out['rawdiff_gop_vs_other'] = df_out['votes_gop'] - df_out['votes_other']\n",
    "df_out['rawdiff_other_vs_dem'] = df_out['votes_other'] - df_out['votes_dem']\n",
    "df_out['rawdiff_other_vs_gop'] = df_out['votes_other'] - df_out['votes_gop']\n",
    "\n",
    "# Calculate percent difference attributes\n",
    "df_out['pctdiff_dem_vs_gop'] = (df_out['votes_dem'] - df_out['votes_gop']) / df_out['votes_total']\n",
    "df_out['pctdiff_gop_vs_dem'] = (df_out['votes_gop'] - df_out['votes_dem']) / df_out['votes_total']\n",
    "df_out['pctdiff_dem_vs_other'] = (df_out['votes_dem'] - df_out['votes_other']) / df_out['votes_total']\n",
    "df_out['pctdiff_gop_vs_other'] = (df_out['votes_gop'] - df_out['votes_other']) / df_out['votes_total']\n",
    "df_out['pctdiff_other_vs_dem'] = (df_out['votes_other'] - df_out['votes_dem']) / df_out['votes_total']\n",
    "df_out['pctdiff_other_vs_gop'] = (df_out['votes_other'] - df_out['votes_gop']) / df_out['votes_total']\n",
    "\n",
    "df_out.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(15769, 28)"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_out.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "**Fix Shannon County Mess** for more info [here](https://www.census.gov/programs-surveys/geography/technical-documentation/county-changes.html)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Changes to Counties or County Equivalent Entities: 2010s\n",
    "\n",
    "- Shannon County, South Dakota, was renamed as Oglala Lakota County and the county code changed from 113 to 102. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "hidden": true
   },
   "outputs": [],
   "source": [
    "df_out.loc[df_out['FIPS'] == '46113', 'FIPS'] = '46102'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true,
    "jp-MarkdownHeadingCollapsed": true,
    "tags": []
   },
   "source": [
    "## Step 2: Geoenable election data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Goals:\n",
    "   - Retrieve 2016 USA county population GIS data\n",
    "   - Perform join, bringing geometry to election data\n",
    "   - Calculate Voter Turnout per county\n",
    "   - Convert dataframe to feature class"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "\n",
    "We will now bring geometry data for each county into the table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "### Retrieve 2016 USA county population GIS data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "A useful source of data is the [ArcGIS Living Atlas of the World](https://livingatlas.arcgis.com), where we can find a service containing [Voting Age Population totals (citizens aged 18+) for each county](https://www.arcgis.com/home/item.html?id=2e8aaf91178c4c91b974d0bc4234dbfa). "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "hidden": true
   },
   "outputs": [],
   "source": [
    "# Authenticate with a GIS using the ArcGIS API for Python\n",
    "gis = arcgis.gis.GIS()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class=\"item_container\" style=\"height: auto; overflow: hidden; border: 1px solid #cfcfcf; border-radius: 2px; background: #f6fafa; line-height: 1.21429em; padding: 10px;\">\n",
       "                    <div class=\"item_left\" style=\"width: 210px; float: left;\">\n",
       "                       <a href='https://www.arcgis.com/home/item.html?id=4cb598ae041348fb92270f102a6783cb' target='_blank'>\n",
       "                        <img src='https://www.arcgis.com/sharing/rest//content/items/4cb598ae041348fb92270f102a6783cb/info/thumbnail/ago_downloaded.png' class=\"itemThumbnail\">\n",
       "                       </a>\n",
       "                    </div>\n",
       "\n",
       "                    <div class=\"item_right\"     style=\"float: none; width: auto; overflow: hidden;\">\n",
       "                        <a href='https://www.arcgis.com/home/item.html?id=4cb598ae041348fb92270f102a6783cb' target='_blank'><b>JHU Centers for Civic Impact Covid-19 County Cases (Daily Update)</b>\n",
       "                        </a>\n",
       "                        <br/>The current situation for the coronavirus COVID-19 in the US per county. <img src='https://www.arcgis.com/home/js/jsapi/esri/css/images/item_type_icons/featureshosted16.png' style=\"vertical-align:middle;\">Feature Layer Collection by CivicImpactJHU\n",
       "                        <br/>Last Modified: March 25, 2022\n",
       "                        <br/>10 comments, 786,810,732 views\n",
       "                    </div>\n",
       "                </div>\n",
       "                "
      ],
      "text/plain": [
       "<Item title:\"JHU Centers for Civic Impact Covid-19 County Cases (Daily Update)\" type:Feature Layer Collection owner:CivicImpactJHU>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div class=\"item_container\" style=\"height: auto; overflow: hidden; border: 1px solid #cfcfcf; border-radius: 2px; background: #f6fafa; line-height: 1.21429em; padding: 10px;\">\n",
       "                    <div class=\"item_left\" style=\"width: 210px; float: left;\">\n",
       "                       <a href='https://www.arcgis.com/home/item.html?id=7566e0221e5646f99ea249a197116605' target='_blank'>\n",
       "                        <img src='https://www.arcgis.com/sharing/rest//content/items/7566e0221e5646f99ea249a197116605/info/thumbnail/thumbnail1563404748186.jpeg' class=\"itemThumbnail\">\n",
       "                       </a>\n",
       "                    </div>\n",
       "\n",
       "                    <div class=\"item_right\"     style=\"float: none; width: auto; overflow: hidden;\">\n",
       "                        <a href='https://www.arcgis.com/home/item.html?id=7566e0221e5646f99ea249a197116605' target='_blank'><b>USA Counties (Generalized)</b>\n",
       "                        </a>\n",
       "                        <br/>USA Counties (Generalized) provides the county boundaries of the United States in the 50 states and the District of Columbia.<img src='https://www.arcgis.com/home/js/jsapi/esri/css/images/item_type_icons/featureshosted16.png' style=\"vertical-align:middle;\">Feature Layer Collection by esri_dm\n",
       "                        <br/>Last Modified: July 01, 2021\n",
       "                        <br/>1 comments, 232,462,334 views\n",
       "                    </div>\n",
       "                </div>\n",
       "                "
      ],
      "text/plain": [
       "<Item title:\"USA Counties (Generalized)\" type:Feature Layer Collection owner:esri_dm>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div class=\"item_container\" style=\"height: auto; overflow: hidden; border: 1px solid #cfcfcf; border-radius: 2px; background: #f6fafa; line-height: 1.21429em; padding: 10px;\">\n",
       "                    <div class=\"item_left\" style=\"width: 210px; float: left;\">\n",
       "                       <a href='https://www.arcgis.com/home/item.html?id=48f9af87daa241c4b267c5931ad3b226' target='_blank'>\n",
       "                        <img src='https://www.arcgis.com/sharing/rest//content/items/48f9af87daa241c4b267c5931ad3b226/info/thumbnail/thumbnail1641322414733.png' class=\"itemThumbnail\">\n",
       "                       </a>\n",
       "                    </div>\n",
       "\n",
       "                    <div class=\"item_right\"     style=\"float: none; width: auto; overflow: hidden;\">\n",
       "                        <a href='https://www.arcgis.com/home/item.html?id=48f9af87daa241c4b267c5931ad3b226' target='_blank'><b>USA Counties</b>\n",
       "                        </a>\n",
       "                        <br/>USA Counties represents the counties of the United States in the 50 states and the District of Columbia.<img src='https://www.arcgis.com/home/js/jsapi/esri/css/images/item_type_icons/featureshosted16.png' style=\"vertical-align:middle;\">Feature Layer Collection by esri_dm\n",
       "                        <br/>Last Modified: January 04, 2022\n",
       "                        <br/>0 comments, 230,446,124 views\n",
       "                    </div>\n",
       "                </div>\n",
       "                "
      ],
      "text/plain": [
       "<Item title:\"USA Counties\" type:Feature Layer Collection owner:esri_dm>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div class=\"item_container\" style=\"height: auto; overflow: hidden; border: 1px solid #cfcfcf; border-radius: 2px; background: #f6fafa; line-height: 1.21429em; padding: 10px;\">\n",
       "                    <div class=\"item_left\" style=\"width: 210px; float: left;\">\n",
       "                       <a href='https://www.arcgis.com/home/item.html?id=628578697fb24d8ea4c32fa0c5ae1843' target='_blank'>\n",
       "                        <img src='https://www.arcgis.com/sharing/rest//content/items/628578697fb24d8ea4c32fa0c5ae1843/info/thumbnail/thumbnail1584926359474.png' class=\"itemThumbnail\">\n",
       "                       </a>\n",
       "                    </div>\n",
       "\n",
       "                    <div class=\"item_right\"     style=\"float: none; width: auto; overflow: hidden;\">\n",
       "                        <a href='https://www.arcgis.com/home/item.html?id=628578697fb24d8ea4c32fa0c5ae1843' target='_blank'><b>COVID-19 Cases US</b>\n",
       "                        </a>\n",
       "                        <br/>The current situation for the COVID-19 virus in the US updated hourly.<img src='https://www.arcgis.com/home/js/jsapi/esri/css/images/item_type_icons/featureshosted16.png' style=\"vertical-align:middle;\">Feature Layer Collection by CSSE_covid19\n",
       "                        <br/>Last Modified: March 26, 2022\n",
       "                        <br/>0 comments, 210,889,452 views\n",
       "                    </div>\n",
       "                </div>\n",
       "                "
      ],
      "text/plain": [
       "<Item title:\"COVID-19 Cases US\" type:Feature Layer Collection owner:CSSE_covid19>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div class=\"item_container\" style=\"height: auto; overflow: hidden; border: 1px solid #cfcfcf; border-radius: 2px; background: #f6fafa; line-height: 1.21429em; padding: 10px;\">\n",
       "                    <div class=\"item_left\" style=\"width: 210px; float: left;\">\n",
       "                       <a href='https://www.arcgis.com/home/item.html?id=77202b11309c45f1a2bdd0bb9f78e7b8' target='_blank'>\n",
       "                        <img src='https://www.arcgis.com/sharing/rest//content/items/77202b11309c45f1a2bdd0bb9f78e7b8/info/thumbnail/ago_downloaded.png' class=\"itemThumbnail\">\n",
       "                       </a>\n",
       "                    </div>\n",
       "\n",
       "                    <div class=\"item_right\"     style=\"float: none; width: auto; overflow: hidden;\">\n",
       "                        <a href='https://www.arcgis.com/home/item.html?id=77202b11309c45f1a2bdd0bb9f78e7b8' target='_blank'><b>JHU Centers for Civic Impact Covid-19 (test) - Deprecated</b>\n",
       "                        </a>\n",
       "                        <br/>The current situation for the COVID-19 virus in the US per county.<img src='https://www.arcgis.com/home/js/jsapi/esri/css/images/item_type_icons/featureshosted16.png' style=\"vertical-align:middle;\">Feature Layer Collection by CivicImpactJHU_Test\n",
       "                        <br/>Last Modified: March 25, 2022\n",
       "                        <br/>0 comments, 187,513,075 views\n",
       "                    </div>\n",
       "                </div>\n",
       "                "
      ],
      "text/plain": [
       "<Item title:\"JHU Centers for Civic Impact Covid-19 (test) - Deprecated\" type:Feature Layer Collection owner:CivicImpactJHU_Test>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div class=\"item_container\" style=\"height: auto; overflow: hidden; border: 1px solid #cfcfcf; border-radius: 2px; background: #f6fafa; line-height: 1.21429em; padding: 10px;\">\n",
       "                    <div class=\"item_left\" style=\"width: 210px; float: left;\">\n",
       "                       <a href='https://www.arcgis.com/home/item.html?id=d957997ccee7408287a963600a77f61f' target='_blank'>\n",
       "                        <img src='https://www.arcgis.com/sharing/rest//content/items/d957997ccee7408287a963600a77f61f/info/thumbnail/thumbnail1589394128160.jpeg' class=\"itemThumbnail\">\n",
       "                       </a>\n",
       "                    </div>\n",
       "\n",
       "                    <div class=\"item_right\"     style=\"float: none; width: auto; overflow: hidden;\">\n",
       "                        <a href='https://www.arcgis.com/home/item.html?id=d957997ccee7408287a963600a77f61f' target='_blank'><b>USA Current Wildfires</b>\n",
       "                        </a>\n",
       "                        <br/>This layer shows wildfires that have been updated within the past 7 days in the United States from IRWIN and NIFC information.<img src='https://www.arcgis.com/home/js/jsapi/esri/css/images/item_type_icons/featureshosted16.png' style=\"vertical-align:middle;\">Feature Layer Collection by esri_livefeeds2\n",
       "                        <br/>Last Modified: March 26, 2022\n",
       "                        <br/>171 comments, 112,796,335 views\n",
       "                    </div>\n",
       "                </div>\n",
       "                "
      ],
      "text/plain": [
       "<Item title:\"USA Current Wildfires\" type:Feature Layer Collection owner:esri_livefeeds2>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div class=\"item_container\" style=\"height: auto; overflow: hidden; border: 1px solid #cfcfcf; border-radius: 2px; background: #f6fafa; line-height: 1.21429em; padding: 10px;\">\n",
       "                    <div class=\"item_left\" style=\"width: 210px; float: left;\">\n",
       "                       <a href='https://www.arcgis.com/home/item.html?id=a6134ae01aad44c499d12feec782b386' target='_blank'>\n",
       "                        <img src='https://www.arcgis.com/sharing/rest//content/items/a6134ae01aad44c499d12feec782b386/info/thumbnail/thumbnail1560897974595.jpeg' class=\"itemThumbnail\">\n",
       "                       </a>\n",
       "                    </div>\n",
       "\n",
       "                    <div class=\"item_right\"     style=\"float: none; width: auto; overflow: hidden;\">\n",
       "                        <a href='https://www.arcgis.com/home/item.html?id=a6134ae01aad44c499d12feec782b386' target='_blank'><b>USA Weather Watches and Warnings</b>\n",
       "                        </a>\n",
       "                        <br/>A live data feed from the National Weather Service containing official weather warnings, watches, and advisory statements for the United States.<img src='https://www.arcgis.com/home/js/jsapi/esri/css/images/item_type_icons/featureshosted16.png' style=\"vertical-align:middle;\">Feature Layer Collection by esri_livefeeds2\n",
       "                        <br/>Last Modified: August 17, 2021\n",
       "                        <br/>51 comments, 68,811,945 views\n",
       "                    </div>\n",
       "                </div>\n",
       "                "
      ],
      "text/plain": [
       "<Item title:\"USA Weather Watches and Warnings\" type:Feature Layer Collection owner:esri_livefeeds2>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div class=\"item_container\" style=\"height: auto; overflow: hidden; border: 1px solid #cfcfcf; border-radius: 2px; background: #f6fafa; line-height: 1.21429em; padding: 10px;\">\n",
       "                    <div class=\"item_left\" style=\"width: 210px; float: left;\">\n",
       "                       <a href='https://www.arcgis.com/home/item.html?id=378554c51c8b44a7aeae5a2c8362be61' target='_blank'>\n",
       "                        <img src='https://www.arcgis.com/sharing/rest//content/items/378554c51c8b44a7aeae5a2c8362be61/info/thumbnail/thumbnail.png' class=\"itemThumbnail\">\n",
       "                       </a>\n",
       "                    </div>\n",
       "\n",
       "                    <div class=\"item_right\"     style=\"float: none; width: auto; overflow: hidden;\">\n",
       "                        <a href='https://www.arcgis.com/home/item.html?id=378554c51c8b44a7aeae5a2c8362be61' target='_blank'><b>US Native American Lands COVID</b>\n",
       "                        </a>\n",
       "                        <br/>This map identifies counties in the U.S. that intersect with federally recognized Native land reservations and displays the most up-to-date COVID data that are available for those counties<img src='https://www.arcgis.com/home/js/jsapi/esri/css/images/item_type_icons/featureshosted16.png' style=\"vertical-align:middle;\">Feature Layer Collection by CivicImpactJHU_Test\n",
       "                        <br/>Last Modified: September 10, 2020\n",
       "                        <br/>0 comments, 31,393,391 views\n",
       "                    </div>\n",
       "                </div>\n",
       "                "
      ],
      "text/plain": [
       "<Item title:\"US Native American Lands COVID\" type:Feature Layer Collection owner:CivicImpactJHU_Test>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div class=\"item_container\" style=\"height: auto; overflow: hidden; border: 1px solid #cfcfcf; border-radius: 2px; background: #f6fafa; line-height: 1.21429em; padding: 10px;\">\n",
       "                    <div class=\"item_left\" style=\"width: 210px; float: left;\">\n",
       "                       <a href='https://www.arcgis.com/home/item.html?id=492f02b473df4c8f8aa68fedf420c933' target='_blank'>\n",
       "                        <img src='https://www.arcgis.com/sharing/rest//content/items/492f02b473df4c8f8aa68fedf420c933/info/thumbnail/thumbnail1588712694467.png' class=\"itemThumbnail\">\n",
       "                       </a>\n",
       "                    </div>\n",
       "\n",
       "                    <div class=\"item_right\"     style=\"float: none; width: auto; overflow: hidden;\">\n",
       "                        <a href='https://www.arcgis.com/home/item.html?id=492f02b473df4c8f8aa68fedf420c933' target='_blank'><b>Florida Health Metrics</b>\n",
       "                        </a>\n",
       "                        <br/>Monitoring data, summarized by week, based on syndromic data from hospitals, new case reports, deaths, and testing by county. <img src='https://www.arcgis.com/home/js/jsapi/esri/css/images/item_type_icons/table16.png' style=\"vertical-align:middle;\">Table Layer by Epidemiology.GIS\n",
       "                        <br/>Last Modified: June 09, 2020\n",
       "                        <br/>0 comments, 30,539,979 views\n",
       "                    </div>\n",
       "                </div>\n",
       "                "
      ],
      "text/plain": [
       "<Item title:\"Florida Health Metrics\" type:Table Layer owner:Epidemiology.GIS>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div class=\"item_container\" style=\"height: auto; overflow: hidden; border: 1px solid #cfcfcf; border-radius: 2px; background: #f6fafa; line-height: 1.21429em; padding: 10px;\">\n",
       "                    <div class=\"item_left\" style=\"width: 210px; float: left;\">\n",
       "                       <a href='https://www.arcgis.com/home/item.html?id=080e963cb5c148d488ff527f3f98b3a7' target='_blank'>\n",
       "                        <img src='https://www.arcgis.com/sharing/rest//content/items/080e963cb5c148d488ff527f3f98b3a7/info/thumbnail/thumbnail.png' class=\"itemThumbnail\">\n",
       "                       </a>\n",
       "                    </div>\n",
       "\n",
       "                    <div class=\"item_right\"     style=\"float: none; width: auto; overflow: hidden;\">\n",
       "                        <a href='https://www.arcgis.com/home/item.html?id=080e963cb5c148d488ff527f3f98b3a7' target='_blank'><b>Sonoma_County</b>\n",
       "                        </a>\n",
       "                        <br/>The jurisdictional boundary of Sonoma County, California.<img src='https://www.arcgis.com/home/js/jsapi/esri/css/images/item_type_icons/featureshosted16.png' style=\"vertical-align:middle;\">Feature Layer Collection by sc_prmdgis\n",
       "                        <br/>Last Modified: May 10, 2021\n",
       "                        <br/>0 comments, 21,966,122 views\n",
       "                    </div>\n",
       "                </div>\n",
       "                "
      ],
      "text/plain": [
       "<Item title:\"Sonoma_County\" type:Feature Layer Collection owner:sc_prmdgis>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "from IPython.display import display\n",
    "\n",
    "# Search for USA_Counties\n",
    "items = gis.content.search(\"USA Counties\", item_type=\"feature_service\", outside_org=True, sort_field=\"numViews\")\n",
    "for item in items:\n",
    "    display(item)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class=\"item_container\" style=\"height: auto; overflow: hidden; border: 1px solid #cfcfcf; border-radius: 2px; background: #f6fafa; line-height: 1.21429em; padding: 10px;\">\n",
       "                    <div class=\"item_left\" style=\"width: 210px; float: left;\">\n",
       "                       <a href='https://www.arcgis.com/home/item.html?id=7566e0221e5646f99ea249a197116605' target='_blank'>\n",
       "                        <img src='https://www.arcgis.com/sharing/rest//content/items/7566e0221e5646f99ea249a197116605/info/thumbnail/thumbnail1563404748186.jpeg' class=\"itemThumbnail\">\n",
       "                       </a>\n",
       "                    </div>\n",
       "\n",
       "                    <div class=\"item_right\"     style=\"float: none; width: auto; overflow: hidden;\">\n",
       "                        <a href='https://www.arcgis.com/home/item.html?id=7566e0221e5646f99ea249a197116605' target='_blank'><b>USA Counties (Generalized)</b>\n",
       "                        </a>\n",
       "                        <br/>USA Counties (Generalized) provides the county boundaries of the United States in the 50 states and the District of Columbia.<img src='https://www.arcgis.com/home/js/jsapi/esri/css/images/item_type_icons/featureshosted16.png' style=\"vertical-align:middle;\">Feature Layer Collection by esri_dm\n",
       "                        <br/>Last Modified: July 01, 2021\n",
       "                        <br/>1 comments, 232,462,334 views\n",
       "                    </div>\n",
       "                </div>\n",
       "                "
      ],
      "text/plain": [
       "<Item title:\"USA Counties (Generalized)\" type:Feature Layer Collection owner:esri_dm>"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Choose the item\n",
    "\n",
    "counties_item = items[1]\n",
    "counties_item"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "hidden": true
   },
   "outputs": [],
   "source": [
    "# Read the layer into a dataframe\n",
    "counties_df = pd.DataFrame.spatial.from_layer(counties_item.layers[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(3142, 61)"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "counties_df.shape\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['AGE_10_14', 'AGE_15_19', 'AGE_20_24', 'AGE_25_34', 'AGE_35_44',\n",
       "       'AGE_45_54', 'AGE_55_64', 'AGE_5_9', 'AGE_65_74', 'AGE_75_84',\n",
       "       'AGE_85_UP', 'AGE_UNDER5', 'AMERI_ES', 'ASIAN', 'AVE_FAM_SZ',\n",
       "       'AVE_HH_SZ', 'AVE_SALE12', 'AVE_SIZE12', 'BLACK', 'CNTY_FIPS',\n",
       "       'CROP_ACR12', 'FAMILIES', 'FEMALES', 'FHH_CHILD', 'FID', 'FIPS',\n",
       "       'GlobalID', 'HAWN_PI', 'HISPANIC', 'HOUSEHOLDS', 'HSEHLD_1_F',\n",
       "       'HSEHLD_1_M', 'HSE_UNITS', 'MALES', 'MARHH_CHD', 'MARHH_NO_C',\n",
       "       'MED_AGE', 'MED_AGE_F', 'MED_AGE_M', 'MHH_CHILD', 'MULT_RACE', 'NAME',\n",
       "       'NO_FARMS12', 'OBJECTID', 'OTHER', 'OWNER_OCC', 'POP10_SQMI', 'POP2010',\n",
       "       'POPULATION', 'POP_SQMI', 'RENTER_OCC', 'SHAPE', 'SQMI', 'STATE_FIPS',\n",
       "       'STATE_NAME', 'Shape_Area', 'Shape_Leng', 'Shape__Area',\n",
       "       'Shape__Length', 'VACANT', 'WHITE'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "counties_df.columns\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(3142, 12)"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "counties_df = counties_df[['FIPS', 'NAME', 'STATE_FIPS', 'STATE_NAME', 'OBJECTID', 'POPULATION', 'POP_SQMI', 'SHAPE', 'Shape_Area', 'Shape_Leng', 'Shape__Area', 'Shape__Length']]\n",
    "counties_df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "### Perform join, bringing geometry and population column to election data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "We now have a dataframe with election data ('df_out') and a spatially-enabled dataframe of county voting-age population data ('counties_df'). Let's merge the datasets."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(15559, 39)"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Join the data to our election data table\n",
    "geo_df = pd.merge(df_out, counties_df, left_on='FIPS', right_on=\"FIPS\", how= 'inner')\n",
    "# Visualize the merged data, notice the SHAPE column at the end\n",
    "geo_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "hidden": true,
    "scrolled": true
   },
   "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>FIPS</th>\n",
       "      <th>year</th>\n",
       "      <th>county</th>\n",
       "      <th>state</th>\n",
       "      <th>state_abbrev</th>\n",
       "      <th>office</th>\n",
       "      <th>candidate_dem</th>\n",
       "      <th>votes_dem</th>\n",
       "      <th>candidate_gop</th>\n",
       "      <th>votes_gop</th>\n",
       "      <th>votes_total</th>\n",
       "      <th>votes_other</th>\n",
       "      <th>voter_share_major_party</th>\n",
       "      <th>voter_share_dem</th>\n",
       "      <th>voter_share_gop</th>\n",
       "      <th>voter_share_other</th>\n",
       "      <th>rawdiff_dem_vs_gop</th>\n",
       "      <th>rawdiff_gop_vs_dem</th>\n",
       "      <th>rawdiff_dem_vs_other</th>\n",
       "      <th>rawdiff_gop_vs_other</th>\n",
       "      <th>rawdiff_other_vs_dem</th>\n",
       "      <th>rawdiff_other_vs_gop</th>\n",
       "      <th>pctdiff_dem_vs_gop</th>\n",
       "      <th>pctdiff_gop_vs_dem</th>\n",
       "      <th>pctdiff_dem_vs_other</th>\n",
       "      <th>pctdiff_gop_vs_other</th>\n",
       "      <th>pctdiff_other_vs_dem</th>\n",
       "      <th>pctdiff_other_vs_gop</th>\n",
       "      <th>NAME</th>\n",
       "      <th>STATE_FIPS</th>\n",
       "      <th>STATE_NAME</th>\n",
       "      <th>OBJECTID</th>\n",
       "      <th>POPULATION</th>\n",
       "      <th>POP_SQMI</th>\n",
       "      <th>SHAPE</th>\n",
       "      <th>Shape_Area</th>\n",
       "      <th>Shape_Leng</th>\n",
       "      <th>Shape__Area</th>\n",
       "      <th>Shape__Length</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>01001</td>\n",
       "      <td>2016</td>\n",
       "      <td>Autauga</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>President</td>\n",
       "      <td>Hillary Clinton</td>\n",
       "      <td>5936.0</td>\n",
       "      <td>Donald Trump</td>\n",
       "      <td>18172.0</td>\n",
       "      <td>24973.0</td>\n",
       "      <td>865.0</td>\n",
       "      <td>0.965363</td>\n",
       "      <td>0.237697</td>\n",
       "      <td>0.727666</td>\n",
       "      <td>0.034637</td>\n",
       "      <td>-12236.0</td>\n",
       "      <td>12236.0</td>\n",
       "      <td>5071.0</td>\n",
       "      <td>17307.0</td>\n",
       "      <td>-5071.0</td>\n",
       "      <td>-17307.0</td>\n",
       "      <td>-0.489969</td>\n",
       "      <td>0.489969</td>\n",
       "      <td>0.203059</td>\n",
       "      <td>0.693028</td>\n",
       "      <td>-0.203059</td>\n",
       "      <td>-0.693028</td>\n",
       "      <td>Autauga</td>\n",
       "      <td>01</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>1965</td>\n",
       "      <td>56319</td>\n",
       "      <td>93.2</td>\n",
       "      <td>{'rings': [[[-9664832.76273449, 3808987.252222...</td>\n",
       "      <td>0.148903</td>\n",
       "      <td>1.884137</td>\n",
       "      <td>2.188823e+09</td>\n",
       "      <td>229109.164169</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>01003</td>\n",
       "      <td>2016</td>\n",
       "      <td>Baldwin</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>President</td>\n",
       "      <td>Hillary Clinton</td>\n",
       "      <td>18458.0</td>\n",
       "      <td>Donald Trump</td>\n",
       "      <td>72883.0</td>\n",
       "      <td>95215.0</td>\n",
       "      <td>3874.0</td>\n",
       "      <td>0.959313</td>\n",
       "      <td>0.193856</td>\n",
       "      <td>0.765457</td>\n",
       "      <td>0.040687</td>\n",
       "      <td>-54425.0</td>\n",
       "      <td>54425.0</td>\n",
       "      <td>14584.0</td>\n",
       "      <td>69009.0</td>\n",
       "      <td>-14584.0</td>\n",
       "      <td>-69009.0</td>\n",
       "      <td>-0.571601</td>\n",
       "      <td>0.571601</td>\n",
       "      <td>0.153169</td>\n",
       "      <td>0.724770</td>\n",
       "      <td>-0.153169</td>\n",
       "      <td>-0.724770</td>\n",
       "      <td>Baldwin</td>\n",
       "      <td>01</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>1250</td>\n",
       "      <td>209227</td>\n",
       "      <td>127.6</td>\n",
       "      <td>{'rings': [[[-9793119.56121114, 3654173.030111...</td>\n",
       "      <td>0.404489</td>\n",
       "      <td>3.678276</td>\n",
       "      <td>5.832085e+09</td>\n",
       "      <td>442546.872592</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>01005</td>\n",
       "      <td>2016</td>\n",
       "      <td>Barbour</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>President</td>\n",
       "      <td>Hillary Clinton</td>\n",
       "      <td>4871.0</td>\n",
       "      <td>Donald Trump</td>\n",
       "      <td>5454.0</td>\n",
       "      <td>10469.0</td>\n",
       "      <td>144.0</td>\n",
       "      <td>0.986245</td>\n",
       "      <td>0.465278</td>\n",
       "      <td>0.520967</td>\n",
       "      <td>0.013755</td>\n",
       "      <td>-583.0</td>\n",
       "      <td>583.0</td>\n",
       "      <td>4727.0</td>\n",
       "      <td>5310.0</td>\n",
       "      <td>-4727.0</td>\n",
       "      <td>-5310.0</td>\n",
       "      <td>-0.055688</td>\n",
       "      <td>0.055688</td>\n",
       "      <td>0.451524</td>\n",
       "      <td>0.507212</td>\n",
       "      <td>-0.451524</td>\n",
       "      <td>-0.507212</td>\n",
       "      <td>Barbour</td>\n",
       "      <td>01</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>1251</td>\n",
       "      <td>27072</td>\n",
       "      <td>29.9</td>\n",
       "      <td>{'rings': [[[-9544908.60175304, 3714348.259590...</td>\n",
       "      <td>0.222431</td>\n",
       "      <td>2.218514</td>\n",
       "      <td>3.245946e+09</td>\n",
       "      <td>266874.305406</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>01007</td>\n",
       "      <td>2016</td>\n",
       "      <td>Bibb</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>President</td>\n",
       "      <td>Hillary Clinton</td>\n",
       "      <td>1874.0</td>\n",
       "      <td>Donald Trump</td>\n",
       "      <td>6738.0</td>\n",
       "      <td>8819.0</td>\n",
       "      <td>207.0</td>\n",
       "      <td>0.976528</td>\n",
       "      <td>0.212496</td>\n",
       "      <td>0.764032</td>\n",
       "      <td>0.023472</td>\n",
       "      <td>-4864.0</td>\n",
       "      <td>4864.0</td>\n",
       "      <td>1667.0</td>\n",
       "      <td>6531.0</td>\n",
       "      <td>-1667.0</td>\n",
       "      <td>-6531.0</td>\n",
       "      <td>-0.551536</td>\n",
       "      <td>0.551536</td>\n",
       "      <td>0.189024</td>\n",
       "      <td>0.740560</td>\n",
       "      <td>-0.189024</td>\n",
       "      <td>-0.740560</td>\n",
       "      <td>Bibb</td>\n",
       "      <td>01</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>1463</td>\n",
       "      <td>22932</td>\n",
       "      <td>36.6</td>\n",
       "      <td>{'rings': [[[-9731533.81242838, 3896866.748799...</td>\n",
       "      <td>0.157736</td>\n",
       "      <td>1.852453</td>\n",
       "      <td>2.330690e+09</td>\n",
       "      <td>223825.974676</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>01009</td>\n",
       "      <td>2016</td>\n",
       "      <td>Blount</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>President</td>\n",
       "      <td>Hillary Clinton</td>\n",
       "      <td>2156.0</td>\n",
       "      <td>Donald Trump</td>\n",
       "      <td>22859.0</td>\n",
       "      <td>25588.0</td>\n",
       "      <td>573.0</td>\n",
       "      <td>0.977607</td>\n",
       "      <td>0.084258</td>\n",
       "      <td>0.893348</td>\n",
       "      <td>0.022393</td>\n",
       "      <td>-20703.0</td>\n",
       "      <td>20703.0</td>\n",
       "      <td>1583.0</td>\n",
       "      <td>22286.0</td>\n",
       "      <td>-1583.0</td>\n",
       "      <td>-22286.0</td>\n",
       "      <td>-0.809090</td>\n",
       "      <td>0.809090</td>\n",
       "      <td>0.061865</td>\n",
       "      <td>0.870955</td>\n",
       "      <td>-0.061865</td>\n",
       "      <td>-0.870955</td>\n",
       "      <td>Blount</td>\n",
       "      <td>01</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>1966</td>\n",
       "      <td>57596</td>\n",
       "      <td>88.5</td>\n",
       "      <td>{'rings': [[[-9681232.86521302, 4010079.855362...</td>\n",
       "      <td>0.167530</td>\n",
       "      <td>2.067456</td>\n",
       "      <td>2.503708e+09</td>\n",
       "      <td>249967.911420</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     FIPS  year   county  ... Shape_Leng   Shape__Area  Shape__Length\n",
       "4   01001  2016  Autauga  ...   1.884137  2.188823e+09  229109.164169\n",
       "9   01003  2016  Baldwin  ...   3.678276  5.832085e+09  442546.872592\n",
       "14  01005  2016  Barbour  ...   2.218514  3.245946e+09  266874.305406\n",
       "19  01007  2016     Bibb  ...   1.852453  2.330690e+09  223825.974676\n",
       "24  01009  2016   Blount  ...   2.067456  2.503708e+09  249967.911420\n",
       "\n",
       "[5 rows x 39 columns]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create a copy of the data, and perform a query\n",
    "# Create a subset of the data just for the 2016 presidential election\n",
    "data_2016_df = geo_df.copy()\n",
    "data_2016_df.query(\"year == '2016'\", inplace=True)\n",
    "data_2016_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "hidden": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(3112, 39)"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_2016_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "hidden": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "### Convert dataframe to feature class"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "We can now finally convert our data to feature classes! The ArcGIS API for Python (which was invoked by using \"import arcgis\") lets us export the spatially-enabled dataframe to a feature class so we can do further analysis.\n",
    "\n",
    "**Note: Executing the following cell may take a few minutes**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'C:\\\\Users\\\\dahla\\\\Desktop\\\\GIS utbildning\\\\Spatial Data Science\\\\Voter-Participation-Data-Wrangling-master\\\\Data Engineering\\\\Data Engineering.gdb\\\\county_elections_pres_2016'"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fgdb = r\"C:\\Users\\dahla\\Desktop\\GIS utbildning\\Spatial Data Science\\Voter-Participation-Data-Wrangling-master\\Data Engineering\\Data Engineering.gdb\"\n",
    "out_2016_fc_name = \"county_elections_pres_2016\"\n",
    "out_2016_fc = data_2016_df.spatial.to_featureclass(os.path.join(fgdb, out_2016_fc_name))\n",
    "out_2016_fc"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true,
    "jp-MarkdownHeadingCollapsed": true,
    "tags": []
   },
   "source": [
    "## Step 3: Geoenrich election data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "\n",
    "Goals:\n",
    "   - Use Geoenrichment to bring demographic and socioeconomic variables to use in analysis"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Geoenrichment in ArcGIS Pro allows us to add columns of data for each county that can help us analyze relationships and potentially model voter turnout. Geoenrichment can be performed using ArcPy, but we recommend that you use the GeoProcessing Enrich tool to explore potential variables that may help explain voter turnout. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Another option is the one that we have just done. In other words, load, clean and add new data. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "**load data of The Citizen Voting Age Population (CVAP)** [here](https://www.census.gov/data/datasets/2016/dec/rdo/2012-2016-CVAP.html)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {
    "hidden": true
   },
   "outputs": [],
   "source": [
    "# CVAP_EST: The rounded estimate of the total number of United States citizens 18 years of age or older\n",
    "# CVAP_MOE: The margin of error for the total number of United States citizens 18 years of age or older\n",
    "\n",
    "# Read the data \n",
    "\n",
    "fp = \"CVAP.csv\"\n",
    "\n",
    "data = pd.read_csv(fp, dtype={'GEOID': str, 'CVAP_EST': str, 'CVAP_MOE': str})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {
    "hidden": true
   },
   "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>GEONAME</th>\n",
       "      <th>LNTITLE</th>\n",
       "      <th>GEOID</th>\n",
       "      <th>LNNUMBER</th>\n",
       "      <th>TOT_EST</th>\n",
       "      <th>TOT_MOE</th>\n",
       "      <th>ADU_EST</th>\n",
       "      <th>ADU_MOE</th>\n",
       "      <th>CIT_EST</th>\n",
       "      <th>CIT_MOE</th>\n",
       "      <th>CVAP_EST</th>\n",
       "      <th>CVAP_MOE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Autauga County, Alabama</td>\n",
       "      <td>Total</td>\n",
       "      <td>05000US01001</td>\n",
       "      <td>1</td>\n",
       "      <td>55050</td>\n",
       "      <td>NaN</td>\n",
       "      <td>41195</td>\n",
       "      <td>34.0</td>\n",
       "      <td>54510</td>\n",
       "      <td>263</td>\n",
       "      <td>40690</td>\n",
       "      <td>236</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Autauga County, Alabama</td>\n",
       "      <td>Not Hispanic or Latino</td>\n",
       "      <td>05000US01001</td>\n",
       "      <td>2</td>\n",
       "      <td>53635</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40290</td>\n",
       "      <td>35.0</td>\n",
       "      <td>53325</td>\n",
       "      <td>220</td>\n",
       "      <td>40015</td>\n",
       "      <td>184</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Autauga County, Alabama</td>\n",
       "      <td>American Indian or Alaska Native Alone</td>\n",
       "      <td>05000US01001</td>\n",
       "      <td>3</td>\n",
       "      <td>225</td>\n",
       "      <td>74.0</td>\n",
       "      <td>125</td>\n",
       "      <td>55.0</td>\n",
       "      <td>225</td>\n",
       "      <td>74</td>\n",
       "      <td>125</td>\n",
       "      <td>55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Autauga County, Alabama</td>\n",
       "      <td>Asian Alone</td>\n",
       "      <td>05000US01001</td>\n",
       "      <td>4</td>\n",
       "      <td>485</td>\n",
       "      <td>165.0</td>\n",
       "      <td>390</td>\n",
       "      <td>106.0</td>\n",
       "      <td>340</td>\n",
       "      <td>200</td>\n",
       "      <td>245</td>\n",
       "      <td>161</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Autauga County, Alabama</td>\n",
       "      <td>Black or African American Alone</td>\n",
       "      <td>05000US01001</td>\n",
       "      <td>5</td>\n",
       "      <td>10115</td>\n",
       "      <td>242.0</td>\n",
       "      <td>7470</td>\n",
       "      <td>75.0</td>\n",
       "      <td>10115</td>\n",
       "      <td>242</td>\n",
       "      <td>7470</td>\n",
       "      <td>75</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   GEONAME  ... CVAP_MOE\n",
       "0  Autauga County, Alabama  ...      236\n",
       "1  Autauga County, Alabama  ...      184\n",
       "2  Autauga County, Alabama  ...       55\n",
       "3  Autauga County, Alabama  ...      161\n",
       "4  Autauga County, Alabama  ...       75\n",
       "\n",
       "[5 rows x 12 columns]"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {
    "hidden": true
   },
   "outputs": [],
   "source": [
    "# Subset the data\n",
    "\n",
    "data = data.loc[data['LNTITLE'] == 'Total']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {
    "hidden": true
   },
   "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>GEONAME</th>\n",
       "      <th>LNTITLE</th>\n",
       "      <th>GEOID</th>\n",
       "      <th>LNNUMBER</th>\n",
       "      <th>TOT_EST</th>\n",
       "      <th>TOT_MOE</th>\n",
       "      <th>ADU_EST</th>\n",
       "      <th>ADU_MOE</th>\n",
       "      <th>CIT_EST</th>\n",
       "      <th>CIT_MOE</th>\n",
       "      <th>CVAP_EST</th>\n",
       "      <th>CVAP_MOE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Autauga County, Alabama</td>\n",
       "      <td>Total</td>\n",
       "      <td>05000US01001</td>\n",
       "      <td>1</td>\n",
       "      <td>55050</td>\n",
       "      <td>NaN</td>\n",
       "      <td>41195</td>\n",
       "      <td>34.0</td>\n",
       "      <td>54510</td>\n",
       "      <td>263</td>\n",
       "      <td>40690</td>\n",
       "      <td>236</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Baldwin County, Alabama</td>\n",
       "      <td>Total</td>\n",
       "      <td>05000US01003</td>\n",
       "      <td>1</td>\n",
       "      <td>199510</td>\n",
       "      <td>NaN</td>\n",
       "      <td>155240</td>\n",
       "      <td>NaN</td>\n",
       "      <td>195655</td>\n",
       "      <td>761</td>\n",
       "      <td>151770</td>\n",
       "      <td>680</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Barbour County, Alabama</td>\n",
       "      <td>Total</td>\n",
       "      <td>05000US01005</td>\n",
       "      <td>1</td>\n",
       "      <td>26615</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20880</td>\n",
       "      <td>19.0</td>\n",
       "      <td>26085</td>\n",
       "      <td>118</td>\n",
       "      <td>20375</td>\n",
       "      <td>113</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>Bibb County, Alabama</td>\n",
       "      <td>Total</td>\n",
       "      <td>05000US01007</td>\n",
       "      <td>1</td>\n",
       "      <td>22570</td>\n",
       "      <td>NaN</td>\n",
       "      <td>17815</td>\n",
       "      <td>46.0</td>\n",
       "      <td>22345</td>\n",
       "      <td>63</td>\n",
       "      <td>17590</td>\n",
       "      <td>81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>Blount County, Alabama</td>\n",
       "      <td>Total</td>\n",
       "      <td>05000US01009</td>\n",
       "      <td>1</td>\n",
       "      <td>57705</td>\n",
       "      <td>NaN</td>\n",
       "      <td>44105</td>\n",
       "      <td>29.0</td>\n",
       "      <td>55925</td>\n",
       "      <td>339</td>\n",
       "      <td>42430</td>\n",
       "      <td>314</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    GEONAME LNTITLE         GEOID  ...  CIT_MOE  CVAP_EST  CVAP_MOE\n",
       "0   Autauga County, Alabama   Total  05000US01001  ...      263     40690       236\n",
       "13  Baldwin County, Alabama   Total  05000US01003  ...      761    151770       680\n",
       "26  Barbour County, Alabama   Total  05000US01005  ...      118     20375       113\n",
       "39     Bibb County, Alabama   Total  05000US01007  ...       63     17590        81\n",
       "52   Blount County, Alabama   Total  05000US01009  ...      339     42430       314\n",
       "\n",
       "[5 rows x 12 columns]"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {
    "hidden": true
   },
   "outputs": [],
   "source": [
    "# Clean the data\n",
    "# Drop the columns that are not useful\n",
    "\n",
    "data = data[['GEOID', 'CVAP_EST', 'CVAP_MOE']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {
    "hidden": true
   },
   "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>GEOID</th>\n",
       "      <th>CVAP_EST</th>\n",
       "      <th>CVAP_MOE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>05000US01001</td>\n",
       "      <td>40690</td>\n",
       "      <td>236</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>05000US01003</td>\n",
       "      <td>151770</td>\n",
       "      <td>680</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>05000US01005</td>\n",
       "      <td>20375</td>\n",
       "      <td>113</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>05000US01007</td>\n",
       "      <td>17590</td>\n",
       "      <td>81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>05000US01009</td>\n",
       "      <td>42430</td>\n",
       "      <td>314</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           GEOID CVAP_EST CVAP_MOE\n",
       "0   05000US01001    40690      236\n",
       "13  05000US01003   151770      680\n",
       "26  05000US01005    20375      113\n",
       "39  05000US01007    17590       81\n",
       "52  05000US01009    42430      314"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {
    "hidden": true
   },
   "outputs": [],
   "source": [
    "# fix the GEOID\n",
    "\n",
    "data['GEOID'] = data['GEOID'].apply(lambda x: x[7:])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {
    "hidden": true
   },
   "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>GEOID</th>\n",
       "      <th>CVAP_EST</th>\n",
       "      <th>CVAP_MOE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>01001</td>\n",
       "      <td>40690</td>\n",
       "      <td>236</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>01003</td>\n",
       "      <td>151770</td>\n",
       "      <td>680</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>01005</td>\n",
       "      <td>20375</td>\n",
       "      <td>113</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>01007</td>\n",
       "      <td>17590</td>\n",
       "      <td>81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>01009</td>\n",
       "      <td>42430</td>\n",
       "      <td>314</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    GEOID CVAP_EST CVAP_MOE\n",
       "0   01001    40690      236\n",
       "13  01003   151770      680\n",
       "26  01005    20375      113\n",
       "39  01007    17590       81\n",
       "52  01009    42430      314"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {
    "hidden": true
   },
   "outputs": [],
   "source": [
    "# change the column name\n",
    "\n",
    "data = data.rename(columns={'GEOID':'FIPS'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {
    "hidden": true
   },
   "outputs": [],
   "source": [
    "# merge the data\n",
    "\n",
    "df_CVAP = pd.merge(data_2016_df, data, on= 'FIPS', how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(3112, 41)"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_CVAP.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {
    "hidden": true
   },
   "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>FIPS</th>\n",
       "      <th>year</th>\n",
       "      <th>county</th>\n",
       "      <th>state</th>\n",
       "      <th>state_abbrev</th>\n",
       "      <th>office</th>\n",
       "      <th>candidate_dem</th>\n",
       "      <th>votes_dem</th>\n",
       "      <th>candidate_gop</th>\n",
       "      <th>votes_gop</th>\n",
       "      <th>votes_total</th>\n",
       "      <th>votes_other</th>\n",
       "      <th>voter_share_major_party</th>\n",
       "      <th>voter_share_dem</th>\n",
       "      <th>voter_share_gop</th>\n",
       "      <th>voter_share_other</th>\n",
       "      <th>rawdiff_dem_vs_gop</th>\n",
       "      <th>rawdiff_gop_vs_dem</th>\n",
       "      <th>rawdiff_dem_vs_other</th>\n",
       "      <th>rawdiff_gop_vs_other</th>\n",
       "      <th>rawdiff_other_vs_dem</th>\n",
       "      <th>rawdiff_other_vs_gop</th>\n",
       "      <th>pctdiff_dem_vs_gop</th>\n",
       "      <th>pctdiff_gop_vs_dem</th>\n",
       "      <th>pctdiff_dem_vs_other</th>\n",
       "      <th>pctdiff_gop_vs_other</th>\n",
       "      <th>pctdiff_other_vs_dem</th>\n",
       "      <th>pctdiff_other_vs_gop</th>\n",
       "      <th>NAME</th>\n",
       "      <th>STATE_FIPS</th>\n",
       "      <th>STATE_NAME</th>\n",
       "      <th>OBJECTID</th>\n",
       "      <th>POPULATION</th>\n",
       "      <th>POP_SQMI</th>\n",
       "      <th>SHAPE</th>\n",
       "      <th>Shape_Area</th>\n",
       "      <th>Shape_Leng</th>\n",
       "      <th>Shape__Area</th>\n",
       "      <th>Shape__Length</th>\n",
       "      <th>CVAP_EST</th>\n",
       "      <th>CVAP_MOE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>01001</td>\n",
       "      <td>2016</td>\n",
       "      <td>Autauga</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>President</td>\n",
       "      <td>Hillary Clinton</td>\n",
       "      <td>5936.0</td>\n",
       "      <td>Donald Trump</td>\n",
       "      <td>18172.0</td>\n",
       "      <td>24973.0</td>\n",
       "      <td>865.0</td>\n",
       "      <td>0.965363</td>\n",
       "      <td>0.237697</td>\n",
       "      <td>0.727666</td>\n",
       "      <td>0.034637</td>\n",
       "      <td>-12236.0</td>\n",
       "      <td>12236.0</td>\n",
       "      <td>5071.0</td>\n",
       "      <td>17307.0</td>\n",
       "      <td>-5071.0</td>\n",
       "      <td>-17307.0</td>\n",
       "      <td>-0.489969</td>\n",
       "      <td>0.489969</td>\n",
       "      <td>0.203059</td>\n",
       "      <td>0.693028</td>\n",
       "      <td>-0.203059</td>\n",
       "      <td>-0.693028</td>\n",
       "      <td>Autauga</td>\n",
       "      <td>01</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>1965</td>\n",
       "      <td>56319</td>\n",
       "      <td>93.2</td>\n",
       "      <td>{'rings': [[[-9664832.76273449, 3808987.252222...</td>\n",
       "      <td>0.148903</td>\n",
       "      <td>1.884137</td>\n",
       "      <td>2.188823e+09</td>\n",
       "      <td>229109.164169</td>\n",
       "      <td>40690</td>\n",
       "      <td>236</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>01003</td>\n",
       "      <td>2016</td>\n",
       "      <td>Baldwin</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>President</td>\n",
       "      <td>Hillary Clinton</td>\n",
       "      <td>18458.0</td>\n",
       "      <td>Donald Trump</td>\n",
       "      <td>72883.0</td>\n",
       "      <td>95215.0</td>\n",
       "      <td>3874.0</td>\n",
       "      <td>0.959313</td>\n",
       "      <td>0.193856</td>\n",
       "      <td>0.765457</td>\n",
       "      <td>0.040687</td>\n",
       "      <td>-54425.0</td>\n",
       "      <td>54425.0</td>\n",
       "      <td>14584.0</td>\n",
       "      <td>69009.0</td>\n",
       "      <td>-14584.0</td>\n",
       "      <td>-69009.0</td>\n",
       "      <td>-0.571601</td>\n",
       "      <td>0.571601</td>\n",
       "      <td>0.153169</td>\n",
       "      <td>0.724770</td>\n",
       "      <td>-0.153169</td>\n",
       "      <td>-0.724770</td>\n",
       "      <td>Baldwin</td>\n",
       "      <td>01</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>1250</td>\n",
       "      <td>209227</td>\n",
       "      <td>127.6</td>\n",
       "      <td>{'rings': [[[-9793119.56121114, 3654173.030111...</td>\n",
       "      <td>0.404489</td>\n",
       "      <td>3.678276</td>\n",
       "      <td>5.832085e+09</td>\n",
       "      <td>442546.872592</td>\n",
       "      <td>151770</td>\n",
       "      <td>680</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>01005</td>\n",
       "      <td>2016</td>\n",
       "      <td>Barbour</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>President</td>\n",
       "      <td>Hillary Clinton</td>\n",
       "      <td>4871.0</td>\n",
       "      <td>Donald Trump</td>\n",
       "      <td>5454.0</td>\n",
       "      <td>10469.0</td>\n",
       "      <td>144.0</td>\n",
       "      <td>0.986245</td>\n",
       "      <td>0.465278</td>\n",
       "      <td>0.520967</td>\n",
       "      <td>0.013755</td>\n",
       "      <td>-583.0</td>\n",
       "      <td>583.0</td>\n",
       "      <td>4727.0</td>\n",
       "      <td>5310.0</td>\n",
       "      <td>-4727.0</td>\n",
       "      <td>-5310.0</td>\n",
       "      <td>-0.055688</td>\n",
       "      <td>0.055688</td>\n",
       "      <td>0.451524</td>\n",
       "      <td>0.507212</td>\n",
       "      <td>-0.451524</td>\n",
       "      <td>-0.507212</td>\n",
       "      <td>Barbour</td>\n",
       "      <td>01</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>1251</td>\n",
       "      <td>27072</td>\n",
       "      <td>29.9</td>\n",
       "      <td>{'rings': [[[-9544908.60175304, 3714348.259590...</td>\n",
       "      <td>0.222431</td>\n",
       "      <td>2.218514</td>\n",
       "      <td>3.245946e+09</td>\n",
       "      <td>266874.305406</td>\n",
       "      <td>20375</td>\n",
       "      <td>113</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>01007</td>\n",
       "      <td>2016</td>\n",
       "      <td>Bibb</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>President</td>\n",
       "      <td>Hillary Clinton</td>\n",
       "      <td>1874.0</td>\n",
       "      <td>Donald Trump</td>\n",
       "      <td>6738.0</td>\n",
       "      <td>8819.0</td>\n",
       "      <td>207.0</td>\n",
       "      <td>0.976528</td>\n",
       "      <td>0.212496</td>\n",
       "      <td>0.764032</td>\n",
       "      <td>0.023472</td>\n",
       "      <td>-4864.0</td>\n",
       "      <td>4864.0</td>\n",
       "      <td>1667.0</td>\n",
       "      <td>6531.0</td>\n",
       "      <td>-1667.0</td>\n",
       "      <td>-6531.0</td>\n",
       "      <td>-0.551536</td>\n",
       "      <td>0.551536</td>\n",
       "      <td>0.189024</td>\n",
       "      <td>0.740560</td>\n",
       "      <td>-0.189024</td>\n",
       "      <td>-0.740560</td>\n",
       "      <td>Bibb</td>\n",
       "      <td>01</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>1463</td>\n",
       "      <td>22932</td>\n",
       "      <td>36.6</td>\n",
       "      <td>{'rings': [[[-9731533.81242838, 3896866.748799...</td>\n",
       "      <td>0.157736</td>\n",
       "      <td>1.852453</td>\n",
       "      <td>2.330690e+09</td>\n",
       "      <td>223825.974676</td>\n",
       "      <td>17590</td>\n",
       "      <td>81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>01009</td>\n",
       "      <td>2016</td>\n",
       "      <td>Blount</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>President</td>\n",
       "      <td>Hillary Clinton</td>\n",
       "      <td>2156.0</td>\n",
       "      <td>Donald Trump</td>\n",
       "      <td>22859.0</td>\n",
       "      <td>25588.0</td>\n",
       "      <td>573.0</td>\n",
       "      <td>0.977607</td>\n",
       "      <td>0.084258</td>\n",
       "      <td>0.893348</td>\n",
       "      <td>0.022393</td>\n",
       "      <td>-20703.0</td>\n",
       "      <td>20703.0</td>\n",
       "      <td>1583.0</td>\n",
       "      <td>22286.0</td>\n",
       "      <td>-1583.0</td>\n",
       "      <td>-22286.0</td>\n",
       "      <td>-0.809090</td>\n",
       "      <td>0.809090</td>\n",
       "      <td>0.061865</td>\n",
       "      <td>0.870955</td>\n",
       "      <td>-0.061865</td>\n",
       "      <td>-0.870955</td>\n",
       "      <td>Blount</td>\n",
       "      <td>01</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>1966</td>\n",
       "      <td>57596</td>\n",
       "      <td>88.5</td>\n",
       "      <td>{'rings': [[[-9681232.86521302, 4010079.855362...</td>\n",
       "      <td>0.167530</td>\n",
       "      <td>2.067456</td>\n",
       "      <td>2.503708e+09</td>\n",
       "      <td>249967.911420</td>\n",
       "      <td>42430</td>\n",
       "      <td>314</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    FIPS  year   county    state  ...   Shape__Area  Shape__Length CVAP_EST  CVAP_MOE\n",
       "0  01001  2016  Autauga  Alabama  ...  2.188823e+09  229109.164169    40690       236\n",
       "1  01003  2016  Baldwin  Alabama  ...  5.832085e+09  442546.872592   151770       680\n",
       "2  01005  2016  Barbour  Alabama  ...  3.245946e+09  266874.305406    20375       113\n",
       "3  01007  2016     Bibb  Alabama  ...  2.330690e+09  223825.974676    17590        81\n",
       "4  01009  2016   Blount  Alabama  ...  2.503708e+09  249967.911420    42430       314\n",
       "\n",
       "[5 rows x 41 columns]"
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_CVAP.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {
    "hidden": true
   },
   "outputs": [],
   "source": [
    "# Create a new column for 'voter_turnout'\n",
    "\n",
    "df_CVAP['voter_turnout'] = df_CVAP['votes_total'].astype(int) / df_CVAP['CVAP_EST'].astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {
    "hidden": true
   },
   "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>FIPS</th>\n",
       "      <th>year</th>\n",
       "      <th>county</th>\n",
       "      <th>state</th>\n",
       "      <th>state_abbrev</th>\n",
       "      <th>office</th>\n",
       "      <th>candidate_dem</th>\n",
       "      <th>votes_dem</th>\n",
       "      <th>candidate_gop</th>\n",
       "      <th>votes_gop</th>\n",
       "      <th>votes_total</th>\n",
       "      <th>votes_other</th>\n",
       "      <th>voter_share_major_party</th>\n",
       "      <th>voter_share_dem</th>\n",
       "      <th>voter_share_gop</th>\n",
       "      <th>voter_share_other</th>\n",
       "      <th>rawdiff_dem_vs_gop</th>\n",
       "      <th>rawdiff_gop_vs_dem</th>\n",
       "      <th>rawdiff_dem_vs_other</th>\n",
       "      <th>rawdiff_gop_vs_other</th>\n",
       "      <th>rawdiff_other_vs_dem</th>\n",
       "      <th>rawdiff_other_vs_gop</th>\n",
       "      <th>pctdiff_dem_vs_gop</th>\n",
       "      <th>pctdiff_gop_vs_dem</th>\n",
       "      <th>pctdiff_dem_vs_other</th>\n",
       "      <th>pctdiff_gop_vs_other</th>\n",
       "      <th>pctdiff_other_vs_dem</th>\n",
       "      <th>pctdiff_other_vs_gop</th>\n",
       "      <th>NAME</th>\n",
       "      <th>STATE_FIPS</th>\n",
       "      <th>STATE_NAME</th>\n",
       "      <th>OBJECTID</th>\n",
       "      <th>POPULATION</th>\n",
       "      <th>POP_SQMI</th>\n",
       "      <th>SHAPE</th>\n",
       "      <th>Shape_Area</th>\n",
       "      <th>Shape_Leng</th>\n",
       "      <th>Shape__Area</th>\n",
       "      <th>Shape__Length</th>\n",
       "      <th>CVAP_EST</th>\n",
       "      <th>CVAP_MOE</th>\n",
       "      <th>voter_turnout</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>01001</td>\n",
       "      <td>2016</td>\n",
       "      <td>Autauga</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>President</td>\n",
       "      <td>Hillary Clinton</td>\n",
       "      <td>5936.0</td>\n",
       "      <td>Donald Trump</td>\n",
       "      <td>18172.0</td>\n",
       "      <td>24973.0</td>\n",
       "      <td>865.0</td>\n",
       "      <td>0.965363</td>\n",
       "      <td>0.237697</td>\n",
       "      <td>0.727666</td>\n",
       "      <td>0.034637</td>\n",
       "      <td>-12236.0</td>\n",
       "      <td>12236.0</td>\n",
       "      <td>5071.0</td>\n",
       "      <td>17307.0</td>\n",
       "      <td>-5071.0</td>\n",
       "      <td>-17307.0</td>\n",
       "      <td>-0.489969</td>\n",
       "      <td>0.489969</td>\n",
       "      <td>0.203059</td>\n",
       "      <td>0.693028</td>\n",
       "      <td>-0.203059</td>\n",
       "      <td>-0.693028</td>\n",
       "      <td>Autauga</td>\n",
       "      <td>01</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>1965</td>\n",
       "      <td>56319</td>\n",
       "      <td>93.2</td>\n",
       "      <td>{'rings': [[[-9664832.76273449, 3808987.252222...</td>\n",
       "      <td>0.148903</td>\n",
       "      <td>1.884137</td>\n",
       "      <td>2.188823e+09</td>\n",
       "      <td>229109.164169</td>\n",
       "      <td>40690</td>\n",
       "      <td>236</td>\n",
       "      <td>0.613738</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>01003</td>\n",
       "      <td>2016</td>\n",
       "      <td>Baldwin</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>President</td>\n",
       "      <td>Hillary Clinton</td>\n",
       "      <td>18458.0</td>\n",
       "      <td>Donald Trump</td>\n",
       "      <td>72883.0</td>\n",
       "      <td>95215.0</td>\n",
       "      <td>3874.0</td>\n",
       "      <td>0.959313</td>\n",
       "      <td>0.193856</td>\n",
       "      <td>0.765457</td>\n",
       "      <td>0.040687</td>\n",
       "      <td>-54425.0</td>\n",
       "      <td>54425.0</td>\n",
       "      <td>14584.0</td>\n",
       "      <td>69009.0</td>\n",
       "      <td>-14584.0</td>\n",
       "      <td>-69009.0</td>\n",
       "      <td>-0.571601</td>\n",
       "      <td>0.571601</td>\n",
       "      <td>0.153169</td>\n",
       "      <td>0.724770</td>\n",
       "      <td>-0.153169</td>\n",
       "      <td>-0.724770</td>\n",
       "      <td>Baldwin</td>\n",
       "      <td>01</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>1250</td>\n",
       "      <td>209227</td>\n",
       "      <td>127.6</td>\n",
       "      <td>{'rings': [[[-9793119.56121114, 3654173.030111...</td>\n",
       "      <td>0.404489</td>\n",
       "      <td>3.678276</td>\n",
       "      <td>5.832085e+09</td>\n",
       "      <td>442546.872592</td>\n",
       "      <td>151770</td>\n",
       "      <td>680</td>\n",
       "      <td>0.627364</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>01005</td>\n",
       "      <td>2016</td>\n",
       "      <td>Barbour</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>President</td>\n",
       "      <td>Hillary Clinton</td>\n",
       "      <td>4871.0</td>\n",
       "      <td>Donald Trump</td>\n",
       "      <td>5454.0</td>\n",
       "      <td>10469.0</td>\n",
       "      <td>144.0</td>\n",
       "      <td>0.986245</td>\n",
       "      <td>0.465278</td>\n",
       "      <td>0.520967</td>\n",
       "      <td>0.013755</td>\n",
       "      <td>-583.0</td>\n",
       "      <td>583.0</td>\n",
       "      <td>4727.0</td>\n",
       "      <td>5310.0</td>\n",
       "      <td>-4727.0</td>\n",
       "      <td>-5310.0</td>\n",
       "      <td>-0.055688</td>\n",
       "      <td>0.055688</td>\n",
       "      <td>0.451524</td>\n",
       "      <td>0.507212</td>\n",
       "      <td>-0.451524</td>\n",
       "      <td>-0.507212</td>\n",
       "      <td>Barbour</td>\n",
       "      <td>01</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>1251</td>\n",
       "      <td>27072</td>\n",
       "      <td>29.9</td>\n",
       "      <td>{'rings': [[[-9544908.60175304, 3714348.259590...</td>\n",
       "      <td>0.222431</td>\n",
       "      <td>2.218514</td>\n",
       "      <td>3.245946e+09</td>\n",
       "      <td>266874.305406</td>\n",
       "      <td>20375</td>\n",
       "      <td>113</td>\n",
       "      <td>0.513816</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>01007</td>\n",
       "      <td>2016</td>\n",
       "      <td>Bibb</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>President</td>\n",
       "      <td>Hillary Clinton</td>\n",
       "      <td>1874.0</td>\n",
       "      <td>Donald Trump</td>\n",
       "      <td>6738.0</td>\n",
       "      <td>8819.0</td>\n",
       "      <td>207.0</td>\n",
       "      <td>0.976528</td>\n",
       "      <td>0.212496</td>\n",
       "      <td>0.764032</td>\n",
       "      <td>0.023472</td>\n",
       "      <td>-4864.0</td>\n",
       "      <td>4864.0</td>\n",
       "      <td>1667.0</td>\n",
       "      <td>6531.0</td>\n",
       "      <td>-1667.0</td>\n",
       "      <td>-6531.0</td>\n",
       "      <td>-0.551536</td>\n",
       "      <td>0.551536</td>\n",
       "      <td>0.189024</td>\n",
       "      <td>0.740560</td>\n",
       "      <td>-0.189024</td>\n",
       "      <td>-0.740560</td>\n",
       "      <td>Bibb</td>\n",
       "      <td>01</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>1463</td>\n",
       "      <td>22932</td>\n",
       "      <td>36.6</td>\n",
       "      <td>{'rings': [[[-9731533.81242838, 3896866.748799...</td>\n",
       "      <td>0.157736</td>\n",
       "      <td>1.852453</td>\n",
       "      <td>2.330690e+09</td>\n",
       "      <td>223825.974676</td>\n",
       "      <td>17590</td>\n",
       "      <td>81</td>\n",
       "      <td>0.501364</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>01009</td>\n",
       "      <td>2016</td>\n",
       "      <td>Blount</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>President</td>\n",
       "      <td>Hillary Clinton</td>\n",
       "      <td>2156.0</td>\n",
       "      <td>Donald Trump</td>\n",
       "      <td>22859.0</td>\n",
       "      <td>25588.0</td>\n",
       "      <td>573.0</td>\n",
       "      <td>0.977607</td>\n",
       "      <td>0.084258</td>\n",
       "      <td>0.893348</td>\n",
       "      <td>0.022393</td>\n",
       "      <td>-20703.0</td>\n",
       "      <td>20703.0</td>\n",
       "      <td>1583.0</td>\n",
       "      <td>22286.0</td>\n",
       "      <td>-1583.0</td>\n",
       "      <td>-22286.0</td>\n",
       "      <td>-0.809090</td>\n",
       "      <td>0.809090</td>\n",
       "      <td>0.061865</td>\n",
       "      <td>0.870955</td>\n",
       "      <td>-0.061865</td>\n",
       "      <td>-0.870955</td>\n",
       "      <td>Blount</td>\n",
       "      <td>01</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>1966</td>\n",
       "      <td>57596</td>\n",
       "      <td>88.5</td>\n",
       "      <td>{'rings': [[[-9681232.86521302, 4010079.855362...</td>\n",
       "      <td>0.167530</td>\n",
       "      <td>2.067456</td>\n",
       "      <td>2.503708e+09</td>\n",
       "      <td>249967.911420</td>\n",
       "      <td>42430</td>\n",
       "      <td>314</td>\n",
       "      <td>0.603064</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3107</th>\n",
       "      <td>56037</td>\n",
       "      <td>2016</td>\n",
       "      <td>Sweetwater</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>WY</td>\n",
       "      <td>President</td>\n",
       "      <td>Hillary Clinton</td>\n",
       "      <td>3231.0</td>\n",
       "      <td>Donald Trump</td>\n",
       "      <td>12154.0</td>\n",
       "      <td>17130.0</td>\n",
       "      <td>1745.0</td>\n",
       "      <td>0.898132</td>\n",
       "      <td>0.188616</td>\n",
       "      <td>0.709515</td>\n",
       "      <td>0.101868</td>\n",
       "      <td>-8923.0</td>\n",
       "      <td>8923.0</td>\n",
       "      <td>1486.0</td>\n",
       "      <td>10409.0</td>\n",
       "      <td>-1486.0</td>\n",
       "      <td>-10409.0</td>\n",
       "      <td>-0.520899</td>\n",
       "      <td>0.520899</td>\n",
       "      <td>0.086748</td>\n",
       "      <td>0.607647</td>\n",
       "      <td>-0.086748</td>\n",
       "      <td>-0.607647</td>\n",
       "      <td>Sweetwater</td>\n",
       "      <td>56</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>278</td>\n",
       "      <td>44755</td>\n",
       "      <td>4.3</td>\n",
       "      <td>{'rings': [[[-12251087.7250076, 5098602.359683...</td>\n",
       "      <td>2.939413</td>\n",
       "      <td>7.676330</td>\n",
       "      <td>4.875622e+10</td>\n",
       "      <td>949368.698433</td>\n",
       "      <td>30565</td>\n",
       "      <td>396</td>\n",
       "      <td>0.560445</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3108</th>\n",
       "      <td>56039</td>\n",
       "      <td>2016</td>\n",
       "      <td>Teton</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>WY</td>\n",
       "      <td>President</td>\n",
       "      <td>Hillary Clinton</td>\n",
       "      <td>7314.0</td>\n",
       "      <td>Donald Trump</td>\n",
       "      <td>3921.0</td>\n",
       "      <td>12627.0</td>\n",
       "      <td>1392.0</td>\n",
       "      <td>0.889760</td>\n",
       "      <td>0.579235</td>\n",
       "      <td>0.310525</td>\n",
       "      <td>0.110240</td>\n",
       "      <td>3393.0</td>\n",
       "      <td>-3393.0</td>\n",
       "      <td>5922.0</td>\n",
       "      <td>2529.0</td>\n",
       "      <td>-5922.0</td>\n",
       "      <td>-2529.0</td>\n",
       "      <td>0.268710</td>\n",
       "      <td>-0.268710</td>\n",
       "      <td>0.468995</td>\n",
       "      <td>0.200285</td>\n",
       "      <td>-0.468995</td>\n",
       "      <td>-0.200285</td>\n",
       "      <td>Teton</td>\n",
       "      <td>56</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>279</td>\n",
       "      <td>23219</td>\n",
       "      <td>5.5</td>\n",
       "      <td>{'rings': [[[-12361750.9541678, 5355412.268422...</td>\n",
       "      <td>1.234222</td>\n",
       "      <td>4.743149</td>\n",
       "      <td>2.123677e+10</td>\n",
       "      <td>649933.862516</td>\n",
       "      <td>16335</td>\n",
       "      <td>452</td>\n",
       "      <td>0.773003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3109</th>\n",
       "      <td>56041</td>\n",
       "      <td>2016</td>\n",
       "      <td>Uinta</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>WY</td>\n",
       "      <td>President</td>\n",
       "      <td>Hillary Clinton</td>\n",
       "      <td>1202.0</td>\n",
       "      <td>Donald Trump</td>\n",
       "      <td>6154.0</td>\n",
       "      <td>8470.0</td>\n",
       "      <td>1114.0</td>\n",
       "      <td>0.868477</td>\n",
       "      <td>0.141913</td>\n",
       "      <td>0.726564</td>\n",
       "      <td>0.131523</td>\n",
       "      <td>-4952.0</td>\n",
       "      <td>4952.0</td>\n",
       "      <td>88.0</td>\n",
       "      <td>5040.0</td>\n",
       "      <td>-88.0</td>\n",
       "      <td>-5040.0</td>\n",
       "      <td>-0.584652</td>\n",
       "      <td>0.584652</td>\n",
       "      <td>0.010390</td>\n",
       "      <td>0.595041</td>\n",
       "      <td>-0.010390</td>\n",
       "      <td>-0.595041</td>\n",
       "      <td>Uinta</td>\n",
       "      <td>56</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>280</td>\n",
       "      <td>21790</td>\n",
       "      <td>10.4</td>\n",
       "      <td>{'rings': [[[-12251087.7250076, 5098602.359683...</td>\n",
       "      <td>0.577557</td>\n",
       "      <td>3.164961</td>\n",
       "      <td>9.525333e+09</td>\n",
       "      <td>395229.959199</td>\n",
       "      <td>14355</td>\n",
       "      <td>217</td>\n",
       "      <td>0.590038</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3110</th>\n",
       "      <td>56043</td>\n",
       "      <td>2016</td>\n",
       "      <td>Washakie</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>WY</td>\n",
       "      <td>President</td>\n",
       "      <td>Hillary Clinton</td>\n",
       "      <td>532.0</td>\n",
       "      <td>Donald Trump</td>\n",
       "      <td>2911.0</td>\n",
       "      <td>3814.0</td>\n",
       "      <td>371.0</td>\n",
       "      <td>0.902727</td>\n",
       "      <td>0.139486</td>\n",
       "      <td>0.763241</td>\n",
       "      <td>0.097273</td>\n",
       "      <td>-2379.0</td>\n",
       "      <td>2379.0</td>\n",
       "      <td>161.0</td>\n",
       "      <td>2540.0</td>\n",
       "      <td>-161.0</td>\n",
       "      <td>-2540.0</td>\n",
       "      <td>-0.623755</td>\n",
       "      <td>0.623755</td>\n",
       "      <td>0.042213</td>\n",
       "      <td>0.665967</td>\n",
       "      <td>-0.042213</td>\n",
       "      <td>-0.665967</td>\n",
       "      <td>Washakie</td>\n",
       "      <td>56</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>281</td>\n",
       "      <td>8510</td>\n",
       "      <td>3.8</td>\n",
       "      <td>{'rings': [[[-11970715.446986, 5389090.2619934...</td>\n",
       "      <td>0.646324</td>\n",
       "      <td>4.191258</td>\n",
       "      <td>1.111627e+10</td>\n",
       "      <td>522723.139768</td>\n",
       "      <td>6135</td>\n",
       "      <td>116</td>\n",
       "      <td>0.621679</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3111</th>\n",
       "      <td>56045</td>\n",
       "      <td>2016</td>\n",
       "      <td>Weston</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>WY</td>\n",
       "      <td>President</td>\n",
       "      <td>Hillary Clinton</td>\n",
       "      <td>299.0</td>\n",
       "      <td>Donald Trump</td>\n",
       "      <td>3033.0</td>\n",
       "      <td>3526.0</td>\n",
       "      <td>194.0</td>\n",
       "      <td>0.944980</td>\n",
       "      <td>0.084799</td>\n",
       "      <td>0.860182</td>\n",
       "      <td>0.055020</td>\n",
       "      <td>-2734.0</td>\n",
       "      <td>2734.0</td>\n",
       "      <td>105.0</td>\n",
       "      <td>2839.0</td>\n",
       "      <td>-105.0</td>\n",
       "      <td>-2839.0</td>\n",
       "      <td>-0.775383</td>\n",
       "      <td>0.775383</td>\n",
       "      <td>0.029779</td>\n",
       "      <td>0.805162</td>\n",
       "      <td>-0.029779</td>\n",
       "      <td>-0.805162</td>\n",
       "      <td>Weston</td>\n",
       "      <td>56</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>1041</td>\n",
       "      <td>7480</td>\n",
       "      <td>3.1</td>\n",
       "      <td>{'rings': [[[-11583673.9903948, 5388958.996829...</td>\n",
       "      <td>0.696373</td>\n",
       "      <td>3.406857</td>\n",
       "      <td>1.196476e+10</td>\n",
       "      <td>437800.222806</td>\n",
       "      <td>5565</td>\n",
       "      <td>145</td>\n",
       "      <td>0.633603</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3112 rows × 42 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       FIPS  year      county  ... CVAP_EST CVAP_MOE voter_turnout\n",
       "0     01001  2016     Autauga  ...    40690      236      0.613738\n",
       "1     01003  2016     Baldwin  ...   151770      680      0.627364\n",
       "2     01005  2016     Barbour  ...    20375      113      0.513816\n",
       "3     01007  2016        Bibb  ...    17590       81      0.501364\n",
       "4     01009  2016      Blount  ...    42430      314      0.603064\n",
       "...     ...   ...         ...  ...      ...      ...           ...\n",
       "3107  56037  2016  Sweetwater  ...    30565      396      0.560445\n",
       "3108  56039  2016       Teton  ...    16335      452      0.773003\n",
       "3109  56041  2016       Uinta  ...    14355      217      0.590038\n",
       "3110  56043  2016    Washakie  ...     6135      116      0.621679\n",
       "3111  56045  2016      Weston  ...     5565      145      0.633603\n",
       "\n",
       "[3112 rows x 42 columns]"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_CVAP"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {
    "hidden": true
   },
   "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>FIPS</th>\n",
       "      <th>year</th>\n",
       "      <th>county</th>\n",
       "      <th>state</th>\n",
       "      <th>state_abbrev</th>\n",
       "      <th>office</th>\n",
       "      <th>candidate_dem</th>\n",
       "      <th>votes_dem</th>\n",
       "      <th>candidate_gop</th>\n",
       "      <th>votes_gop</th>\n",
       "      <th>votes_total</th>\n",
       "      <th>votes_other</th>\n",
       "      <th>voter_share_major_party</th>\n",
       "      <th>voter_share_dem</th>\n",
       "      <th>voter_share_gop</th>\n",
       "      <th>voter_share_other</th>\n",
       "      <th>rawdiff_dem_vs_gop</th>\n",
       "      <th>rawdiff_gop_vs_dem</th>\n",
       "      <th>rawdiff_dem_vs_other</th>\n",
       "      <th>rawdiff_gop_vs_other</th>\n",
       "      <th>rawdiff_other_vs_dem</th>\n",
       "      <th>rawdiff_other_vs_gop</th>\n",
       "      <th>pctdiff_dem_vs_gop</th>\n",
       "      <th>pctdiff_gop_vs_dem</th>\n",
       "      <th>pctdiff_dem_vs_other</th>\n",
       "      <th>pctdiff_gop_vs_other</th>\n",
       "      <th>pctdiff_other_vs_dem</th>\n",
       "      <th>pctdiff_other_vs_gop</th>\n",
       "      <th>NAME</th>\n",
       "      <th>STATE_FIPS</th>\n",
       "      <th>STATE_NAME</th>\n",
       "      <th>OBJECTID</th>\n",
       "      <th>POPULATION</th>\n",
       "      <th>POP_SQMI</th>\n",
       "      <th>SHAPE</th>\n",
       "      <th>Shape_Area</th>\n",
       "      <th>Shape_Leng</th>\n",
       "      <th>Shape__Area</th>\n",
       "      <th>Shape__Length</th>\n",
       "      <th>CVAP_EST</th>\n",
       "      <th>CVAP_MOE</th>\n",
       "      <th>voter_turnout</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>271</th>\n",
       "      <td>08111</td>\n",
       "      <td>2016</td>\n",
       "      <td>San Juan</td>\n",
       "      <td>Colorado</td>\n",
       "      <td>CO</td>\n",
       "      <td>President</td>\n",
       "      <td>Hillary Clinton</td>\n",
       "      <td>265.0</td>\n",
       "      <td>Donald Trump</td>\n",
       "      <td>215.0</td>\n",
       "      <td>506.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>0.948617</td>\n",
       "      <td>0.523715</td>\n",
       "      <td>0.424901</td>\n",
       "      <td>0.051383</td>\n",
       "      <td>50.0</td>\n",
       "      <td>-50.0</td>\n",
       "      <td>239.0</td>\n",
       "      <td>189.0</td>\n",
       "      <td>-239.0</td>\n",
       "      <td>-189.0</td>\n",
       "      <td>0.098814</td>\n",
       "      <td>-0.098814</td>\n",
       "      <td>0.472332</td>\n",
       "      <td>0.373518</td>\n",
       "      <td>-0.472332</td>\n",
       "      <td>-0.373518</td>\n",
       "      <td>San Juan</td>\n",
       "      <td>08</td>\n",
       "      <td>Colorado</td>\n",
       "      <td>176</td>\n",
       "      <td>699</td>\n",
       "      <td>1.8</td>\n",
       "      <td>{'rings': [[[-11964231.3789618, 4527207.437803...</td>\n",
       "      <td>0.106206</td>\n",
       "      <td>1.510836</td>\n",
       "      <td>1.664680e+09</td>\n",
       "      <td>185670.123521</td>\n",
       "      <td>495</td>\n",
       "      <td>78</td>\n",
       "      <td>1.022222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1776</th>\n",
       "      <td>35021</td>\n",
       "      <td>2016</td>\n",
       "      <td>Harding</td>\n",
       "      <td>New Mexico</td>\n",
       "      <td>NM</td>\n",
       "      <td>President</td>\n",
       "      <td>Hillary Clinton</td>\n",
       "      <td>156.0</td>\n",
       "      <td>Donald Trump</td>\n",
       "      <td>311.0</td>\n",
       "      <td>527.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>0.886148</td>\n",
       "      <td>0.296015</td>\n",
       "      <td>0.590133</td>\n",
       "      <td>0.113852</td>\n",
       "      <td>-155.0</td>\n",
       "      <td>155.0</td>\n",
       "      <td>96.0</td>\n",
       "      <td>251.0</td>\n",
       "      <td>-96.0</td>\n",
       "      <td>-251.0</td>\n",
       "      <td>-0.294118</td>\n",
       "      <td>0.294118</td>\n",
       "      <td>0.182163</td>\n",
       "      <td>0.476281</td>\n",
       "      <td>-0.182163</td>\n",
       "      <td>-0.476281</td>\n",
       "      <td>Harding</td>\n",
       "      <td>35</td>\n",
       "      <td>New Mexico</td>\n",
       "      <td>786</td>\n",
       "      <td>707</td>\n",
       "      <td>0.3</td>\n",
       "      <td>{'rings': [[[-11617439.1827118, 4272321.261841...</td>\n",
       "      <td>0.546497</td>\n",
       "      <td>3.723858</td>\n",
       "      <td>8.356103e+09</td>\n",
       "      <td>454360.823288</td>\n",
       "      <td>470</td>\n",
       "      <td>91</td>\n",
       "      <td>1.121277</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2643</th>\n",
       "      <td>48301</td>\n",
       "      <td>2016</td>\n",
       "      <td>Loving</td>\n",
       "      <td>Texas</td>\n",
       "      <td>TX</td>\n",
       "      <td>President</td>\n",
       "      <td>Hillary Clinton</td>\n",
       "      <td>4.0</td>\n",
       "      <td>Donald Trump</td>\n",
       "      <td>58.0</td>\n",
       "      <td>65.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.953846</td>\n",
       "      <td>0.061538</td>\n",
       "      <td>0.892308</td>\n",
       "      <td>0.046154</td>\n",
       "      <td>-54.0</td>\n",
       "      <td>54.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>55.0</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>-55.0</td>\n",
       "      <td>-0.830769</td>\n",
       "      <td>0.830769</td>\n",
       "      <td>0.015385</td>\n",
       "      <td>0.846154</td>\n",
       "      <td>-0.015385</td>\n",
       "      <td>-0.846154</td>\n",
       "      <td>Loving</td>\n",
       "      <td>48</td>\n",
       "      <td>Texas</td>\n",
       "      <td>366</td>\n",
       "      <td>82</td>\n",
       "      <td>0.1</td>\n",
       "      <td>{'rings': [[[-11504264.3855893, 3718440.641716...</td>\n",
       "      <td>0.162189</td>\n",
       "      <td>1.930544</td>\n",
       "      <td>2.366258e+09</td>\n",
       "      <td>228647.477628</td>\n",
       "      <td>60</td>\n",
       "      <td>26</td>\n",
       "      <td>1.083333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2648</th>\n",
       "      <td>48311</td>\n",
       "      <td>2016</td>\n",
       "      <td>McMullen</td>\n",
       "      <td>Texas</td>\n",
       "      <td>TX</td>\n",
       "      <td>President</td>\n",
       "      <td>Hillary Clinton</td>\n",
       "      <td>40.0</td>\n",
       "      <td>Donald Trump</td>\n",
       "      <td>454.0</td>\n",
       "      <td>499.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.989980</td>\n",
       "      <td>0.080160</td>\n",
       "      <td>0.909820</td>\n",
       "      <td>0.010020</td>\n",
       "      <td>-414.0</td>\n",
       "      <td>414.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>449.0</td>\n",
       "      <td>-35.0</td>\n",
       "      <td>-449.0</td>\n",
       "      <td>-0.829659</td>\n",
       "      <td>0.829659</td>\n",
       "      <td>0.070140</td>\n",
       "      <td>0.899800</td>\n",
       "      <td>-0.070140</td>\n",
       "      <td>-0.899800</td>\n",
       "      <td>McMullen</td>\n",
       "      <td>48</td>\n",
       "      <td>Texas</td>\n",
       "      <td>369</td>\n",
       "      <td>809</td>\n",
       "      <td>0.7</td>\n",
       "      <td>{'rings': [[[-10947088.3480166, 3256745.973013...</td>\n",
       "      <td>0.269492</td>\n",
       "      <td>2.086627</td>\n",
       "      <td>3.794698e+09</td>\n",
       "      <td>249801.028037</td>\n",
       "      <td>460</td>\n",
       "      <td>105</td>\n",
       "      <td>1.084783</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       FIPS  year    county  ... CVAP_EST CVAP_MOE voter_turnout\n",
       "271   08111  2016  San Juan  ...      495       78      1.022222\n",
       "1776  35021  2016   Harding  ...      470       91      1.121277\n",
       "2643  48301  2016    Loving  ...       60       26      1.083333\n",
       "2648  48311  2016  McMullen  ...      460      105      1.084783\n",
       "\n",
       "[4 rows x 42 columns]"
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check the voter turnout values\n",
    "\n",
    "df_CVAP.loc[df_CVAP['voter_turnout'] > 1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {
    "hidden": true
   },
   "outputs": [],
   "source": [
    "df_CVAP.loc[df_CVAP['voter_turnout'] > 1 , 'voter_turnout'] = 0.99"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "**convert our data to a feature class**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'C:\\\\Users\\\\dahla\\\\Desktop\\\\GIS utbildning\\\\Spatial Data Science\\\\Voter-Participation-Data-Wrangling-master\\\\Data Engineering\\\\Data Engineering.gdb\\\\county_elections_pres_2016'"
      ]
     },
     "execution_count": 94,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fgdb = r\"C:\\Users\\dahla\\Desktop\\GIS utbildning\\Spatial Data Science\\Voter-Participation-Data-Wrangling-master\\Data Engineering\\Data Engineering.gdb\"\n",
    "out_2016_fc_name = \"county_elections_pres_2016\"\n",
    "out_2016_fc = df_CVAP.spatial.to_featureclass(os.path.join(fgdb, out_2016_fc_name))\n",
    "out_2016_fc"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Step 4: Visualize and analyze"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![](img/e1.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![](img/e2.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![](img/e3.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![](img/e4.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![](img/e5.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![](img/e6.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![](img/e7.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![](img/e8.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![](img/e9.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.8.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}