election_data_extract.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "86609a27-40b3-4ef1-b851-0bc7fc2c22a2",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Overview\n",
    "The goal of this notebook is to take state election data as reported by the Associated Press (via api) and analyze this data for consistency.  The data for the 2020 Presidential election has already been pulled and can be found on Github (as of 07/26/2022).  There are two reasons that I am looking at this data:  \n",
    "1.  Polling data reported by the AP often does not make sense initially since the data is coming from every polling station and states are trying to disperse the results of each data batch as the information arrives.  It is not uncommon for states to include adjustments to correct data that was mis-reported or was re-counted.  Also, each state has their own way of reporting the information.  \n",
    "2.  In time I would like to take this data and make a visualization that better illustrates how vote tallies are changing over time."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "competent-cooler",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import Packages\n",
    "from os.path import exists\n",
    "import pickle\n",
    "import re\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "# start timer\n",
    "time_start = pd.Timestamp.now()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "naval-killer",
   "metadata": {},
   "source": [
    "## Obtain Election Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "removed-proposal",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Data exists in the dataPickle file\n"
     ]
    }
   ],
   "source": [
    "# check to see if the data has been captured in pickle file\n",
    "# if True then move on, if False then grab data and capture information\n",
    "\n",
    "if exists('./data/dataPickle'):\n",
    "    print('Data exists in the dataPickle file')\n",
    "else:\n",
    "    # Setup pickle file to store scraped tables\n",
    "\n",
    "    # create and open pickle file \n",
    "    election_scraped_file = open('./data/dataPickle', 'ab')\n",
    "\n",
    "    # extract datat from source\n",
    "    election_scrape = pd.read_html('https://alex.github.io/nyt-2020-election-scraper/all-state-changes.html')\n",
    "\n",
    "    # add content to pickle file\n",
    "    pickle.dump(election_scrape, election_scraped_file)\n",
    "\n",
    "    # closse pickle file\n",
    "    election_scraped_file.close()\n",
    "\n",
    "    print('Data file not found.  Data is now stored in the dataPickle file')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "later-state",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# grab data from pickle file\n",
    "stored_pickle_file = open('./data/dataPickle', 'rb')\n",
    "election_results = pickle.load(stored_pickle_file)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "c79c1ac5-d1cc-4a6b-9a7a-c2bb371b4f05",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"9\" halign=\"left\">Alabama (EV: 9)  Trump leads with 1,441,170 votes (62.0%), Biden trails with 849,624 votes (36.6%).</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>Timestamp</th>\n",
       "      <th>In The Lead</th>\n",
       "      <th>Vote Margin</th>\n",
       "      <th>Votes Remaining (est.)</th>\n",
       "      <th>Change</th>\n",
       "      <th>Batch Breakdown</th>\n",
       "      <th>Batch Trend</th>\n",
       "      <th>Hurdle</th>\n",
       "      <th>Unnamed: 8_level_1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-01-05 15:21:52 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>591546</td>\n",
       "      <td>4</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Trump 9.1% /  109.1% Biden</td>\n",
       "      <td>Biden is averaging 58.5%</td>\n",
       "      <td>Biden needs 7499241.2%</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-12-04 21:12:00 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>591520</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>22</td>\n",
       "      <td>Trump 9.1% /  109.1% Biden</td>\n",
       "      <td>Biden is averaging 58.5%</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-11-23 20:33:25 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>591546</td>\n",
       "      <td>4</td>\n",
       "      <td>2476</td>\n",
       "      <td>Trump 44.2% /  55.8% Biden</td>\n",
       "      <td>Biden is averaging 58.5%</td>\n",
       "      <td>Biden needs 7499241.2%</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-11-23 20:11:23 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>591832</td>\n",
       "      <td>2521</td>\n",
       "      <td>5894</td>\n",
       "      <td>Trump 50.6% /  49.4% Biden</td>\n",
       "      <td>Biden is averaging 55.0%</td>\n",
       "      <td>Biden needs 11954.5%</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-11-23 19:48:16 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>591760</td>\n",
       "      <td>8534</td>\n",
       "      <td>4792</td>\n",
       "      <td>Trump 61.2% /  38.8% Biden</td>\n",
       "      <td>Biden is averaging 47.8%</td>\n",
       "      <td>Biden needs 3566.2%</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2020-11-11 03:23:42 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>590686</td>\n",
       "      <td>13386</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Biden is averaging 44.9%</td>\n",
       "      <td>Biden needs 2287.6%</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2020-11-06 22:26:37 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>590686</td>\n",
       "      <td>13386</td>\n",
       "      <td>12548</td>\n",
       "      <td>Trump 28.6% /  71.4% Biden</td>\n",
       "      <td>Biden is averaging 44.9%</td>\n",
       "      <td>Biden needs 2287.6%</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2020-11-06 18:40:11 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>596046</td>\n",
       "      <td>26070</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Trump 5.9% /  105.9% Biden</td>\n",
       "      <td>Biden is averaging 49.1%</td>\n",
       "      <td>Biden needs 1209.4%</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2020-11-04 21:12:19 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>595572</td>\n",
       "      <td>25662</td>\n",
       "      <td>10</td>\n",
       "      <td>Trump 30.0% /  70.0% Biden</td>\n",
       "      <td>Biden is averaging 49.4%</td>\n",
       "      <td>Biden needs 1226.9%</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2020-11-04 21:00:38 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>595576</td>\n",
       "      <td>25815</td>\n",
       "      <td>2938</td>\n",
       "      <td>Trump 59.2% /  40.8% Biden</td>\n",
       "      <td>Biden is averaging 49.5%</td>\n",
       "      <td>Biden needs 1219.9%</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2020-11-04 20:28:27 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>595034</td>\n",
       "      <td>28763</td>\n",
       "      <td>54</td>\n",
       "      <td>Trump 1870.4% /  1970.4% Biden</td>\n",
       "      <td>Biden is averaging 53.5%</td>\n",
       "      <td>Biden needs 1099.0%</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2020-11-04 20:23:38 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>597108</td>\n",
       "      <td>28830</td>\n",
       "      <td>13958</td>\n",
       "      <td>Trump 86.9% /  13.1% Biden</td>\n",
       "      <td>Biden is averaging 50.1%</td>\n",
       "      <td>Biden needs 1100.3%</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2020-11-04 17:16:47 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>586810</td>\n",
       "      <td>43805</td>\n",
       "      <td>48782</td>\n",
       "      <td>Trump 17.7% /  82.3% Biden</td>\n",
       "      <td>Biden is averaging 82.3%</td>\n",
       "      <td>Biden needs 729.3%</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2020-11-04 13:28:22 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>618344</td>\n",
       "      <td>93031</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Biden needs 387.0%</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Alabama (EV: 9)  Trump leads with 1,441,170 votes (62.0%), Biden trails with 849,624 votes (36.6%).  \\\n",
       "                                                                                             Timestamp   \n",
       "0                             2021-01-05 15:21:52 UTC                                                    \n",
       "1                             2020-12-04 21:12:00 UTC                                                    \n",
       "2                             2020-11-23 20:33:25 UTC                                                    \n",
       "3                             2020-11-23 20:11:23 UTC                                                    \n",
       "4                             2020-11-23 19:48:16 UTC                                                    \n",
       "5                             2020-11-11 03:23:42 UTC                                                    \n",
       "6                             2020-11-06 22:26:37 UTC                                                    \n",
       "7                             2020-11-06 18:40:11 UTC                                                    \n",
       "8                             2020-11-04 21:12:19 UTC                                                    \n",
       "9                             2020-11-04 21:00:38 UTC                                                    \n",
       "10                            2020-11-04 20:28:27 UTC                                                    \n",
       "11                            2020-11-04 20:23:38 UTC                                                    \n",
       "12                            2020-11-04 17:16:47 UTC                                                    \n",
       "13                            2020-11-04 13:28:22 UTC                                                    \n",
       "\n",
       "                                                            \\\n",
       "   In The Lead Vote Margin Votes Remaining (est.)   Change   \n",
       "0        Trump      591546                      4  Unknown   \n",
       "1        Trump      591520                Unknown       22   \n",
       "2        Trump      591546                      4     2476   \n",
       "3        Trump      591832                   2521     5894   \n",
       "4        Trump      591760                   8534     4792   \n",
       "5        Trump      590686                  13386        0   \n",
       "6        Trump      590686                  13386    12548   \n",
       "7        Trump      596046                  26070  Unknown   \n",
       "8        Trump      595572                  25662       10   \n",
       "9        Trump      595576                  25815     2938   \n",
       "10       Trump      595034                  28763       54   \n",
       "11       Trump      597108                  28830    13958   \n",
       "12       Trump      586810                  43805    48782   \n",
       "13       Trump      618344                  93031        0   \n",
       "\n",
       "                                                              \\\n",
       "                   Batch Breakdown               Batch Trend   \n",
       "0       Trump 9.1% /  109.1% Biden  Biden is averaging 58.5%   \n",
       "1       Trump 9.1% /  109.1% Biden  Biden is averaging 58.5%   \n",
       "2       Trump 44.2% /  55.8% Biden  Biden is averaging 58.5%   \n",
       "3       Trump 50.6% /  49.4% Biden  Biden is averaging 55.0%   \n",
       "4       Trump 61.2% /  38.8% Biden  Biden is averaging 47.8%   \n",
       "5                              NaN  Biden is averaging 44.9%   \n",
       "6       Trump 28.6% /  71.4% Biden  Biden is averaging 44.9%   \n",
       "7       Trump 5.9% /  105.9% Biden  Biden is averaging 49.1%   \n",
       "8       Trump 30.0% /  70.0% Biden  Biden is averaging 49.4%   \n",
       "9       Trump 59.2% /  40.8% Biden  Biden is averaging 49.5%   \n",
       "10  Trump 1870.4% /  1970.4% Biden  Biden is averaging 53.5%   \n",
       "11      Trump 86.9% /  13.1% Biden  Biden is averaging 50.1%   \n",
       "12      Trump 17.7% /  82.3% Biden  Biden is averaging 82.3%   \n",
       "13                             NaN                       NaN   \n",
       "\n",
       "                                               \n",
       "                    Hurdle Unnamed: 8_level_1  \n",
       "0   Biden needs 7499241.2%                NaN  \n",
       "1                  Unknown                NaN  \n",
       "2   Biden needs 7499241.2%                NaN  \n",
       "3     Biden needs 11954.5%                NaN  \n",
       "4      Biden needs 3566.2%                NaN  \n",
       "5      Biden needs 2287.6%                NaN  \n",
       "6      Biden needs 2287.6%                NaN  \n",
       "7      Biden needs 1209.4%                NaN  \n",
       "8      Biden needs 1226.9%                NaN  \n",
       "9      Biden needs 1219.9%                NaN  \n",
       "10     Biden needs 1099.0%                NaN  \n",
       "11     Biden needs 1100.3%                NaN  \n",
       "12      Biden needs 729.3%                NaN  \n",
       "13      Biden needs 387.0%                NaN  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# the result is a list of dataframes\n",
    "# here is an example, note:  the extra row at the top right of the table and the use of text within columns\n",
    "election_results[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "personalized-organic",
   "metadata": {},
   "source": [
    "## Create summary table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "painted-hunger",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>State</th>\n",
       "      <th>Electoral Votes</th>\n",
       "      <th>Leader</th>\n",
       "      <th>Leader Total Votes</th>\n",
       "      <th>Leader Vote Percent</th>\n",
       "      <th>Trailer</th>\n",
       "      <th>Trailer Total Votes</th>\n",
       "      <th>Trailer Vote Percent</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>9</td>\n",
       "      <td>Trump</td>\n",
       "      <td>1441170.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>Biden</td>\n",
       "      <td>849624.0</td>\n",
       "      <td>36.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alaska</td>\n",
       "      <td>3</td>\n",
       "      <td>Trump</td>\n",
       "      <td>189951.0</td>\n",
       "      <td>52.8</td>\n",
       "      <td>Biden</td>\n",
       "      <td>153778.0</td>\n",
       "      <td>42.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Arizona</td>\n",
       "      <td>11</td>\n",
       "      <td>Biden</td>\n",
       "      <td>1672143.0</td>\n",
       "      <td>49.4</td>\n",
       "      <td>Trump</td>\n",
       "      <td>1661686.0</td>\n",
       "      <td>49.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Arkansas</td>\n",
       "      <td>6</td>\n",
       "      <td>Trump</td>\n",
       "      <td>760647.0</td>\n",
       "      <td>62.4</td>\n",
       "      <td>Biden</td>\n",
       "      <td>423932.0</td>\n",
       "      <td>34.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>California</td>\n",
       "      <td>55</td>\n",
       "      <td>Biden</td>\n",
       "      <td>11109764.0</td>\n",
       "      <td>63.5</td>\n",
       "      <td>Trump</td>\n",
       "      <td>6005961.0</td>\n",
       "      <td>34.3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        State  Electoral Votes Leader  Leader Total Votes  \\\n",
       "0     Alabama                9  Trump           1441170.0   \n",
       "1      Alaska                3  Trump            189951.0   \n",
       "2     Arizona               11  Biden           1672143.0   \n",
       "3    Arkansas                6  Trump            760647.0   \n",
       "4  California               55  Biden          11109764.0   \n",
       "\n",
       "   Leader Vote Percent Trailer  Trailer Total Votes  Trailer Vote Percent  \n",
       "0                 62.0   Biden             849624.0                  36.6  \n",
       "1                 52.8   Biden             153778.0                  42.8  \n",
       "2                 49.4   Trump            1661686.0                  49.1  \n",
       "3                 62.4   Biden             423932.0                  34.8  \n",
       "4                 63.5   Trump            6005961.0                  34.3  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# extract out header information that contains total votes, electoral votes, percentages, and winner\n",
    "summary_table = []\n",
    "\n",
    "for state in election_results:\n",
    "    stringState = state.columns.get_level_values(0)[0]\n",
    "    d = stringState.split(\"  \",2)\n",
    "    state_name = d[0].split(\"(\")[0].strip()\n",
    "    electoral_votes = d[0].split(\"(EV: \")[1].replace(\")\",\"\").strip()\n",
    "    s = d[1].replace(\",\",\"\")\n",
    "    lead_votes, lead_percent, trail_votes, trail_percent = [float(i) for i in re.findall(r'([0-9]+\\.?\\d)', s)]\n",
    "    leader, trailer = re.findall(r'([A-Z][a-z]+)',s)\n",
    "    dict = {\n",
    "        'State': state_name, \n",
    "        'Electoral Votes': int(electoral_votes), \n",
    "        'Leader': leader, \n",
    "        'Leader Total Votes': lead_votes, \n",
    "        'Leader Vote Percent': lead_percent, \n",
    "        'Trailer': trailer, \n",
    "        'Trailer Total Votes': trail_votes, \n",
    "        'Trailer Vote Percent': trail_percent\n",
    "    }\n",
    "    summary_table.append(dict)\n",
    "summary_df = pd.DataFrame(summary_table)\n",
    "summary_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "irish-reference",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "State                    object\n",
       "Electoral Votes           int64\n",
       "Leader                   object\n",
       "Leader Total Votes      float64\n",
       "Leader Vote Percent     float64\n",
       "Trailer                  object\n",
       "Trailer Total Votes     float64\n",
       "Trailer Vote Percent    float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check data types for accuracy\n",
    "summary_df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "square-rider",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Store summary table as csv\n",
    "summary_df.to_csv('./data/summary.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "proprietary-nancy",
   "metadata": {},
   "source": [
    "## Make election results table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "saving-income",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Make a list of only numerical values from text string\n",
    "def extract_batch(s, index):\n",
    "    string = re.findall(r'([0-9]+\\.?\\d)', s)\n",
    "    try:\n",
    "        strval = string[index]\n",
    "    except:\n",
    "        strval = None\n",
    "        \n",
    "    return strval"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "ethical-brooks",
   "metadata": {},
   "outputs": [],
   "source": [
    "# election_records = pd.read_html('https://alex.github.io/nyt-2020-election-scraper/all-state-changes.html')\n",
    "frames = []\n",
    "\n",
    "for state in election_results:\n",
    "    stringState = state.columns.get_level_values(0)[0]\n",
    "    d = stringState.split(\"  \",2)\n",
    "    state_name = d[0].split(\"(\")[0].strip()\n",
    "    df = state\n",
    "    df.columns = df.columns.droplevel(0)\n",
    "    df['State'] = state_name\n",
    "    \n",
    "    # capture the first value in list \n",
    "    df['Leader Batch Breakdown'] = df['Batch Breakdown'].apply(lambda x: extract_batch(str(x), 0) )\n",
    "    # capture the second value in list\n",
    "    df['Trailer Batch Breakdown'] = df['Batch Breakdown'].apply(lambda x: extract_batch(str(x), 1) )\n",
    "    # capture the first value in list\n",
    "    df['Trailer Batch Trend'] = df['Batch Trend'].apply(lambda x: extract_batch(str(x), 0) )\n",
    "    # capture the first value in list\n",
    "    df['Trailer Hurdle'] = df['Hurdle'].apply(lambda x: extract_batch(str(x), 0) )\n",
    "    # remove columns not needed\n",
    "    df.drop('Unnamed: 8_level_1', axis='columns', inplace=True)\n",
    "    df.drop('Batch Breakdown', axis='columns', inplace=True)\n",
    "    df.drop(['Batch Trend', 'Hurdle'], axis='columns', inplace=True)\n",
    "    frames.append(df)\n",
    "\n",
    "result_df = pd.concat(frames)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "civilian-indiana",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 3406 entries, 0 to 4\n",
      "Data columns (total 10 columns):\n",
      " #   Column                   Non-Null Count  Dtype \n",
      "---  ------                   --------------  ----- \n",
      " 0   Timestamp                3406 non-null   object\n",
      " 1   In The Lead              3406 non-null   object\n",
      " 2   Vote Margin              3406 non-null   int64 \n",
      " 3   Votes Remaining (est.)   3406 non-null   object\n",
      " 4   Change                   3406 non-null   object\n",
      " 5   State                    3406 non-null   object\n",
      " 6   Leader Batch Breakdown   2886 non-null   object\n",
      " 7   Trailer Batch Breakdown  2886 non-null   object\n",
      " 8   Trailer Batch Trend      3298 non-null   object\n",
      " 9   Trailer Hurdle           3166 non-null   object\n",
      "dtypes: int64(1), object(9)\n",
      "memory usage: 292.7+ KB\n"
     ]
    }
   ],
   "source": [
    "# instead of using .dtypes, use .info() to see aditional information\n",
    "result_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "bored-promotion",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Store batch vote records for each state\n",
    "# don't include the index column\n",
    "result_df.to_csv('./data/raw_records.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "accompanied-removal",
   "metadata": {},
   "source": [
    "## Dataset Review"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "finished-jungle",
   "metadata": {},
   "source": [
    "The data in the columns is not very helpful in its current form.  I need the geospacial-temporal data to have a highly versatile dataset.\n",
    "Since this data is mostly about batches of votes but has some faulty information, I need to calculate the vote counting trends based on the final vote tally and this batch data.  Verifying the validity of the data is necessary.\n",
    "\n",
    "The columns that I need are time, candidate, batch votes, state, district, maybe a note if any of the records were estimated or not.\n",
    "\n",
    "Here are somet things I think that should be checked:\n",
    "* Do the zero values in the 'Change' column need to be removed?  Is there anything else wrong with this data?\n",
    "* Do the 'Unknown' values in the 'Change' column need to be removed?  \n",
    "* Can I estimate the 'Change' column zero and 'Unknown' values from the change in the margin votes and the percent of the leader batch votes?\n",
    "* Is the change in margin between batches smaller than the batch size?  This must be true and values that violate this are likely faulty.\n",
    "\n",
    "In the end, the batch votes need to be filtered for faulty data and the individual batch candidate votes should be calculated from the existing data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "recent-sound",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 3406 entries, 0 to 3405\n",
      "Data columns (total 12 columns):\n",
      " #   Column                   Non-Null Count  Dtype  \n",
      "---  ------                   --------------  -----  \n",
      " 0   Timestamp                3406 non-null   object \n",
      " 1   In The Lead              3406 non-null   object \n",
      " 2   Vote Margin              3406 non-null   int64  \n",
      " 3   Votes Remaining (est.)   3406 non-null   object \n",
      " 4   Change                   3406 non-null   object \n",
      " 5   State                    3406 non-null   object \n",
      " 6   Leader Batch Breakdown   2886 non-null   float64\n",
      " 7   Trailer Batch Breakdown  2886 non-null   float64\n",
      " 8   Trailer Batch Trend      3298 non-null   object \n",
      " 9   Trailer Hurdle           3166 non-null   object \n",
      " 10  Margin_change_estimate   0 non-null      float64\n",
      " 11  Type                     3406 non-null   object \n",
      "dtypes: float64(3), int64(1), object(8)\n",
      "memory usage: 319.4+ KB\n"
     ]
    }
   ],
   "source": [
    "# Create two new columns, convert 3 columns to numeric type\n",
    "result_df['Margin_change_estimate']=\"\"\n",
    "result_df['Type']=\"\"\n",
    "result_df[['Margin_change_estimate','Leader Batch Breakdown', 'Trailer Batch Breakdown']]=result_df[['Margin_change_estimate', \n",
    "                                                                                                     'Leader Batch Breakdown', 'Trailer Batch Breakdown']].apply(pd.to_numeric)\n",
    "\n",
    "# renumber index and discard current index\n",
    "result_df.reset_index(drop=True, inplace=True)\n",
    "\n",
    "# view updated column info\n",
    "result_df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "unsigned-blank",
   "metadata": {},
   "source": [
    "## Zero Data Review"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "southwest-bangladesh",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Timestamp</th>\n",
       "      <th>In The Lead</th>\n",
       "      <th>Vote Margin</th>\n",
       "      <th>Votes Remaining (est.)</th>\n",
       "      <th>Change</th>\n",
       "      <th>State</th>\n",
       "      <th>Leader Batch Breakdown</th>\n",
       "      <th>Trailer Batch Breakdown</th>\n",
       "      <th>Trailer Batch Trend</th>\n",
       "      <th>Trailer Hurdle</th>\n",
       "      <th>Margin_change_estimate</th>\n",
       "      <th>Type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2020-11-11 03:23:42 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>590686</td>\n",
       "      <td>13386</td>\n",
       "      <td>0</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>44.9</td>\n",
       "      <td>2287.6</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2020-11-04 13:28:22 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>618344</td>\n",
       "      <td>93031</td>\n",
       "      <td>0</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>387.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2020-12-02 19:04:28 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>36173</td>\n",
       "      <td>31816</td>\n",
       "      <td>0</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>49.4</td>\n",
       "      <td>109.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2020-12-02 17:18:21 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>36173</td>\n",
       "      <td>31816</td>\n",
       "      <td>0</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>49.4</td>\n",
       "      <td>109.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2020-12-02 16:33:22 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>36173</td>\n",
       "      <td>31816</td>\n",
       "      <td>0</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>49.4</td>\n",
       "      <td>109.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td></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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3358</th>\n",
       "      <td>2020-11-10 13:17:25 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>307807</td>\n",
       "      <td>6992</td>\n",
       "      <td>0</td>\n",
       "      <td>West Virginia</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>34.6</td>\n",
       "      <td>2288.6</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3359</th>\n",
       "      <td>2020-11-09 23:16:41 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>307807</td>\n",
       "      <td>6992</td>\n",
       "      <td>0</td>\n",
       "      <td>West Virginia</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>34.6</td>\n",
       "      <td>2288.6</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3372</th>\n",
       "      <td>2020-11-04 13:28:22 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>307126</td>\n",
       "      <td>8526</td>\n",
       "      <td>0</td>\n",
       "      <td>West Virginia</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>1881.7</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3399</th>\n",
       "      <td>2020-11-04 17:33:32 UTC</td>\n",
       "      <td>Biden</td>\n",
       "      <td>20697</td>\n",
       "      <td>592</td>\n",
       "      <td>0</td>\n",
       "      <td>Wisconsin</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>1828.6</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3400</th>\n",
       "      <td>2020-11-04 13:28:22 UTC</td>\n",
       "      <td>Biden</td>\n",
       "      <td>20697</td>\n",
       "      <td>592</td>\n",
       "      <td>0</td>\n",
       "      <td>Wisconsin</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>1828.6</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>364 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                    Timestamp In The Lead  Vote Margin Votes Remaining (est.)  \\\n",
       "5     2020-11-11 03:23:42 UTC       Trump       590686                  13386   \n",
       "13    2020-11-04 13:28:22 UTC       Trump       618344                  93031   \n",
       "14    2020-12-02 19:04:28 UTC       Trump        36173                  31816   \n",
       "15    2020-12-02 17:18:21 UTC       Trump        36173                  31816   \n",
       "16    2020-12-02 16:33:22 UTC       Trump        36173                  31816   \n",
       "...                       ...         ...          ...                    ...   \n",
       "3358  2020-11-10 13:17:25 UTC       Trump       307807                   6992   \n",
       "3359  2020-11-09 23:16:41 UTC       Trump       307807                   6992   \n",
       "3372  2020-11-04 13:28:22 UTC       Trump       307126                   8526   \n",
       "3399  2020-11-04 17:33:32 UTC       Biden        20697                    592   \n",
       "3400  2020-11-04 13:28:22 UTC       Biden        20697                    592   \n",
       "\n",
       "     Change          State  Leader Batch Breakdown  Trailer Batch Breakdown  \\\n",
       "5         0        Alabama                     NaN                      NaN   \n",
       "13        0        Alabama                     NaN                      NaN   \n",
       "14        0         Alaska                     NaN                      NaN   \n",
       "15        0         Alaska                     NaN                      NaN   \n",
       "16        0         Alaska                     NaN                      NaN   \n",
       "...     ...            ...                     ...                      ...   \n",
       "3358      0  West Virginia                     NaN                      NaN   \n",
       "3359      0  West Virginia                     NaN                      NaN   \n",
       "3372      0  West Virginia                     NaN                      NaN   \n",
       "3399      0      Wisconsin                     NaN                      NaN   \n",
       "3400      0      Wisconsin                     NaN                      NaN   \n",
       "\n",
       "     Trailer Batch Trend Trailer Hurdle  Margin_change_estimate Type  \n",
       "5                   44.9         2287.6                     NaN       \n",
       "13                  None          387.0                     NaN       \n",
       "14                  49.4          109.5                     NaN       \n",
       "15                  49.4          109.5                     NaN       \n",
       "16                  49.4          109.5                     NaN       \n",
       "...                  ...            ...                     ...  ...  \n",
       "3358                34.6         2288.6                     NaN       \n",
       "3359                34.6         2288.6                     NaN       \n",
       "3372                None         1881.7                     NaN       \n",
       "3399                None         1828.6                     NaN       \n",
       "3400                None         1828.6                     NaN       \n",
       "\n",
       "[364 rows x 12 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check Zero Data - is it real or is it flawed\n",
    "# Note - searching for a string bc I can not convert to numeric directly until \"Unknowns\" are removed\n",
    "result_df[result_df[\"Change\"] == \"0\"]\n",
    "# shows what looks like data without any percent breakdowns of the batch"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "foreign-corner",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Timestamp</th>\n",
       "      <th>In The Lead</th>\n",
       "      <th>Vote Margin</th>\n",
       "      <th>Votes Remaining (est.)</th>\n",
       "      <th>Change</th>\n",
       "      <th>State</th>\n",
       "      <th>Leader Batch Breakdown</th>\n",
       "      <th>Trailer Batch Breakdown</th>\n",
       "      <th>Trailer Batch Trend</th>\n",
       "      <th>Trailer Hurdle</th>\n",
       "      <th>Margin_change_estimate</th>\n",
       "      <th>Type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [Timestamp, In The Lead, Vote Margin, Votes Remaining (est.), Change, State, Leader Batch Breakdown, Trailer Batch Breakdown, Trailer Batch Trend, Trailer Hurdle, Margin_change_estimate, Type]\n",
       "Index: []"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check to see if any of the percent columns have bad data (values greater than 100%)\n",
    "result_df[(result_df[\"Change\"] == \"0\") & ( (result_df[\"Leader Batch Breakdown\"] >100) | (result_df[\"Trailer Batch Breakdown\"] >100) )]\n",
    "# Note: there are no records of Change data outside noral percentages"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "spanish-trauma",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Timestamp</th>\n",
       "      <th>In The Lead</th>\n",
       "      <th>Vote Margin</th>\n",
       "      <th>Votes Remaining (est.)</th>\n",
       "      <th>Change</th>\n",
       "      <th>State</th>\n",
       "      <th>Leader Batch Breakdown</th>\n",
       "      <th>Trailer Batch Breakdown</th>\n",
       "      <th>Trailer Batch Trend</th>\n",
       "      <th>Trailer Hurdle</th>\n",
       "      <th>Margin_change_estimate</th>\n",
       "      <th>Type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [Timestamp, In The Lead, Vote Margin, Votes Remaining (est.), Change, State, Leader Batch Breakdown, Trailer Batch Breakdown, Trailer Batch Trend, Trailer Hurdle, Margin_change_estimate, Type]\n",
       "Index: []"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check to see if the vote percents add up to 100%\n",
    "result_df[(result_df[\"Change\"] == \"0\") & (result_df[\"Leader Batch Breakdown\"] + result_df[\"Trailer Batch Breakdown\"] == 100) ]\n",
    "\n",
    "# Note:  none of the values add up to 100 so all values can be thrown out as bad data since individual percents are not greater than 100 and they don't add up to 100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "beginning-lottery",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[13, 39, 125, 211, 355, 428, 559, 565, 575, 624, 850, 854, 863, 933, 984, 1060, 1153, 1210, 1225, 1321, 1419, 1545, 1622, 1810, 1873, 1890, 1910, 1935, 1981, 2046, 2191, 2216, 2298, 2364, 2401, 2443, 2448, 2484, 2820, 2832, 2896, 2938, 2945, 3001, 3055, 3085, 3191, 3341, 3372, 3400, 3405]\n"
     ]
    }
   ],
   "source": [
    "df = result_df.copy()\n",
    "\n",
    "# preserve the original index in the groupby (next step)\n",
    "df.reset_index(inplace=True)\n",
    "\n",
    "# first find the last rows of each state\n",
    "last_row_list = list(df.groupby('State').last()['index'])\n",
    "print(last_row_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "sudden-celebration",
   "metadata": {},
   "outputs": [],
   "source": [
    "# remove zero values but modify below so only non-last values are removed.\n",
    "results_excludes_last_row = result_df[~result_df.index.isin(last_row_list)]\n",
    "zero_check = results_excludes_last_row[(results_excludes_last_row['Change'] == \"0\")]\n",
    "zero_values = list(zero_check.index)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ef716ab1-0463-47cb-9dac-8e78c15e317d",
   "metadata": {},
   "source": [
    "## Collect drop values into csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "7ccaef2b-73dc-4a0b-8947-e37cc21847d0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Store dropped rows\n",
    "dropped_rows_df = result_df[result_df.index.isin(zero_values)].copy()\n",
    "# add category for Type column\n",
    "dropped_rows_df.loc[zero_values,\"Type\"] = \"removed-zero-batch\"\n",
    "# drop values from dataframe\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "1d52c8fa-2030-4e4f-bcc4-2322f58c852e",
   "metadata": {},
   "outputs": [],
   "source": [
    "bad_records = []\n",
    "dict = {}\n",
    "for i in dropped_rows_df.index.to_list():\n",
    "    datetime = dropped_rows_df.loc[i,\"Timestamp\"]\n",
    "    leader = dropped_rows_df.loc[i,\"In The Lead\"]\n",
    "    vote_margin = dropped_rows_df.loc[i,\"Vote Margin\"]\n",
    "    batch_votes = dropped_rows_df.loc[i,\"Change\"]\n",
    "    state = dropped_rows_df.loc[i,\"State\"]\n",
    "    district = \"\"\n",
    "    type_desc = dropped_rows_df.loc[i,\"Type\"]\n",
    "\n",
    "    \n",
    "    # runner_up_votes = dropped_rows_df.loc[i,\"Batch Votes Loser\"]\n",
    "    \n",
    "    dict = {\n",
    "        \"datetime\": datetime,\n",
    "        \"leader\": leader,\n",
    "        \"votes_margin\": vote_margin,\n",
    "        \"batch_votes\": batch_votes,\n",
    "        \"state\": state,\n",
    "        \"district\": district,\n",
    "        \"type\": type_desc\n",
    "    }\n",
    "    \n",
    "    bad_records.append(dict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "572d8dfd-141a-402b-867a-5dfe943b3b46",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>datetime</th>\n",
       "      <th>leader</th>\n",
       "      <th>votes_margin</th>\n",
       "      <th>batch_votes</th>\n",
       "      <th>state</th>\n",
       "      <th>district</th>\n",
       "      <th>type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-11-11 03:23:42 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>590686</td>\n",
       "      <td>0</td>\n",
       "      <td>Alabama</td>\n",
       "      <td></td>\n",
       "      <td>removed-zero-batch</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-12-02 19:04:28 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>36173</td>\n",
       "      <td>0</td>\n",
       "      <td>Alaska</td>\n",
       "      <td></td>\n",
       "      <td>removed-zero-batch</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-12-02 17:18:21 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>36173</td>\n",
       "      <td>0</td>\n",
       "      <td>Alaska</td>\n",
       "      <td></td>\n",
       "      <td>removed-zero-batch</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-12-02 16:33:22 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>36173</td>\n",
       "      <td>0</td>\n",
       "      <td>Alaska</td>\n",
       "      <td></td>\n",
       "      <td>removed-zero-batch</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-12-01 18:45:19 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>36494</td>\n",
       "      <td>0</td>\n",
       "      <td>Alaska</td>\n",
       "      <td></td>\n",
       "      <td>removed-zero-batch</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  datetime leader  votes_margin batch_votes    state district  \\\n",
       "0  2020-11-11 03:23:42 UTC  Trump        590686           0  Alabama            \n",
       "1  2020-12-02 19:04:28 UTC  Trump         36173           0   Alaska            \n",
       "2  2020-12-02 17:18:21 UTC  Trump         36173           0   Alaska            \n",
       "3  2020-12-02 16:33:22 UTC  Trump         36173           0   Alaska            \n",
       "4  2020-12-01 18:45:19 UTC  Trump         36494           0   Alaska            \n",
       "\n",
       "                 type  \n",
       "0  removed-zero-batch  \n",
       "1  removed-zero-batch  \n",
       "2  removed-zero-batch  \n",
       "3  removed-zero-batch  \n",
       "4  removed-zero-batch  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2 = pd.DataFrame(bad_records)\n",
    "df2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "83072c87-bec7-4ad0-b5d3-23d09b5166b4",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv('./data/db_bad_records_file.csv', index_label=\"record_id\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "3ab7b846-9b09-4187-a180-1a4e755dbf9e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop the values\n",
    "result_df.drop(zero_values, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fancy-nickname",
   "metadata": {},
   "source": [
    "## Unknown Data Review"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "demonstrated-poultry",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of Unknowns: 113\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>Timestamp</th>\n",
       "      <th>In The Lead</th>\n",
       "      <th>Vote Margin</th>\n",
       "      <th>Votes Remaining (est.)</th>\n",
       "      <th>Change</th>\n",
       "      <th>State</th>\n",
       "      <th>Leader Batch Breakdown</th>\n",
       "      <th>Trailer Batch Breakdown</th>\n",
       "      <th>Trailer Batch Trend</th>\n",
       "      <th>Trailer Hurdle</th>\n",
       "      <th>Margin_change_estimate</th>\n",
       "      <th>Type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-01-05 15:21:52 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>591546</td>\n",
       "      <td>4</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>9.1</td>\n",
       "      <td>109.1</td>\n",
       "      <td>58.5</td>\n",
       "      <td>7499241.2</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2020-11-06 18:40:11 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>596046</td>\n",
       "      <td>26070</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>5.9</td>\n",
       "      <td>105.9</td>\n",
       "      <td>49.1</td>\n",
       "      <td>1209.4</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>2020-11-25 01:59:12 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>36355</td>\n",
       "      <td>34858</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>6.8</td>\n",
       "      <td>106.8</td>\n",
       "      <td>48.2</td>\n",
       "      <td>104.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157</th>\n",
       "      <td>2020-11-16 18:16:52 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>338005</td>\n",
       "      <td>7175</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>74.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>36.0</td>\n",
       "      <td>2474.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>192</th>\n",
       "      <td>2020-11-09 17:17:21 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>338978</td>\n",
       "      <td>9790</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>48.6</td>\n",
       "      <td>51.4</td>\n",
       "      <td>29.2</td>\n",
       "      <td>1831.7</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   Timestamp In The Lead  Vote Margin Votes Remaining (est.)  \\\n",
       "0    2021-01-05 15:21:52 UTC       Trump       591546                      4   \n",
       "7    2020-11-06 18:40:11 UTC       Trump       596046                  26070   \n",
       "22   2020-11-25 01:59:12 UTC       Trump        36355                  34858   \n",
       "157  2020-11-16 18:16:52 UTC       Trump       338005                   7175   \n",
       "192  2020-11-09 17:17:21 UTC       Trump       338978                   9790   \n",
       "\n",
       "      Change     State  Leader Batch Breakdown  Trailer Batch Breakdown  \\\n",
       "0    Unknown   Alabama                     9.1                    109.1   \n",
       "7    Unknown   Alabama                     5.9                    105.9   \n",
       "22   Unknown    Alaska                     6.8                    106.8   \n",
       "157  Unknown  Arkansas                    74.0                     26.0   \n",
       "192  Unknown  Arkansas                    48.6                     51.4   \n",
       "\n",
       "    Trailer Batch Trend Trailer Hurdle  Margin_change_estimate Type  \n",
       "0                  58.5      7499241.2                     NaN       \n",
       "7                  49.1         1209.4                     NaN       \n",
       "22                 48.2          104.5                     NaN       \n",
       "157                36.0         2474.0                     NaN       \n",
       "192                29.2         1831.7                     NaN       "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check if Unknowns should be thrown out\n",
    "unknown_df = result_df[result_df['Change']==\"Unknown\"]\n",
    "print(f\"Number of Unknowns: {len(unknown_df)}\")\n",
    "unknown_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "historic-pound",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Timestamp</th>\n",
       "      <th>In The Lead</th>\n",
       "      <th>Vote Margin</th>\n",
       "      <th>Votes Remaining (est.)</th>\n",
       "      <th>Change</th>\n",
       "      <th>State</th>\n",
       "      <th>Leader Batch Breakdown</th>\n",
       "      <th>Trailer Batch Breakdown</th>\n",
       "      <th>Trailer Batch Trend</th>\n",
       "      <th>Trailer Hurdle</th>\n",
       "      <th>Margin_change_estimate</th>\n",
       "      <th>Type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>157</th>\n",
       "      <td>2020-11-16 18:16:52 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>338005</td>\n",
       "      <td>7175</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>74.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>36.0</td>\n",
       "      <td>2474.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>192</th>\n",
       "      <td>2020-11-09 17:17:21 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>338978</td>\n",
       "      <td>9790</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>48.6</td>\n",
       "      <td>51.4</td>\n",
       "      <td>29.2</td>\n",
       "      <td>1831.7</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>193</th>\n",
       "      <td>2020-11-09 16:41:11 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>338874</td>\n",
       "      <td>5916</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>75.7</td>\n",
       "      <td>24.3</td>\n",
       "      <td>33.3</td>\n",
       "      <td>2997.4</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>197</th>\n",
       "      <td>2020-11-06 19:30:23 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>337918</td>\n",
       "      <td>11131</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>75.7</td>\n",
       "      <td>24.3</td>\n",
       "      <td>33.7</td>\n",
       "      <td>1612.1</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>198</th>\n",
       "      <td>2020-11-06 19:13:48 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>340363</td>\n",
       "      <td>6134</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>48.7</td>\n",
       "      <td>51.3</td>\n",
       "      <td>27.3</td>\n",
       "      <td>2905.2</td>\n",
       "      <td>NaN</td>\n",
       "      <td></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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3369</th>\n",
       "      <td>2020-11-08 16:45:34 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>307108</td>\n",
       "      <td>8464</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>West Virginia</td>\n",
       "      <td>65.3</td>\n",
       "      <td>34.7</td>\n",
       "      <td>56.5</td>\n",
       "      <td>1895.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3373</th>\n",
       "      <td>2020-12-08 01:37:07 UTC</td>\n",
       "      <td>Biden</td>\n",
       "      <td>20608</td>\n",
       "      <td>76</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Wisconsin</td>\n",
       "      <td>61.9</td>\n",
       "      <td>38.1</td>\n",
       "      <td>64.4</td>\n",
       "      <td>13844.7</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3381</th>\n",
       "      <td>2020-11-16 19:31:33 UTC</td>\n",
       "      <td>Biden</td>\n",
       "      <td>20546</td>\n",
       "      <td>202</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Wisconsin</td>\n",
       "      <td>0.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>67.6</td>\n",
       "      <td>5224.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3389</th>\n",
       "      <td>2020-11-12 01:26:41 UTC</td>\n",
       "      <td>Biden</td>\n",
       "      <td>20798</td>\n",
       "      <td>321</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Wisconsin</td>\n",
       "      <td>0.7</td>\n",
       "      <td>99.3</td>\n",
       "      <td>35.8</td>\n",
       "      <td>3346.2</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3397</th>\n",
       "      <td>2020-11-04 18:30:33 UTC</td>\n",
       "      <td>Biden</td>\n",
       "      <td>20509</td>\n",
       "      <td>294</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Wisconsin</td>\n",
       "      <td>68.8</td>\n",
       "      <td>31.2</td>\n",
       "      <td>82.6</td>\n",
       "      <td>3598.9</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>97 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                    Timestamp In The Lead  Vote Margin Votes Remaining (est.)  \\\n",
       "157   2020-11-16 18:16:52 UTC       Trump       338005                   7175   \n",
       "192   2020-11-09 17:17:21 UTC       Trump       338978                   9790   \n",
       "193   2020-11-09 16:41:11 UTC       Trump       338874                   5916   \n",
       "197   2020-11-06 19:30:23 UTC       Trump       337918                  11131   \n",
       "198   2020-11-06 19:13:48 UTC       Trump       340363                   6134   \n",
       "...                       ...         ...          ...                    ...   \n",
       "3369  2020-11-08 16:45:34 UTC       Trump       307108                   8464   \n",
       "3373  2020-12-08 01:37:07 UTC       Biden        20608                     76   \n",
       "3381  2020-11-16 19:31:33 UTC       Biden        20546                    202   \n",
       "3389  2020-11-12 01:26:41 UTC       Biden        20798                    321   \n",
       "3397  2020-11-04 18:30:33 UTC       Biden        20509                    294   \n",
       "\n",
       "       Change          State  Leader Batch Breakdown  Trailer Batch Breakdown  \\\n",
       "157   Unknown       Arkansas                    74.0                     26.0   \n",
       "192   Unknown       Arkansas                    48.6                     51.4   \n",
       "193   Unknown       Arkansas                    75.7                     24.3   \n",
       "197   Unknown       Arkansas                    75.7                     24.3   \n",
       "198   Unknown       Arkansas                    48.7                     51.3   \n",
       "...       ...            ...                     ...                      ...   \n",
       "3369  Unknown  West Virginia                    65.3                     34.7   \n",
       "3373  Unknown      Wisconsin                    61.9                     38.1   \n",
       "3381  Unknown      Wisconsin                     0.0                    100.0   \n",
       "3389  Unknown      Wisconsin                     0.7                     99.3   \n",
       "3397  Unknown      Wisconsin                    68.8                     31.2   \n",
       "\n",
       "     Trailer Batch Trend Trailer Hurdle  Margin_change_estimate Type  \n",
       "157                 36.0         2474.0                     NaN       \n",
       "192                 29.2         1831.7                     NaN       \n",
       "193                 33.3         2997.4                     NaN       \n",
       "197                 33.7         1612.1                     NaN       \n",
       "198                 27.3         2905.2                     NaN       \n",
       "...                  ...            ...                     ...  ...  \n",
       "3369                56.5         1895.0                     NaN       \n",
       "3373                64.4        13844.7                     NaN       \n",
       "3381                67.6         5224.5                     NaN       \n",
       "3389                35.8         3346.2                     NaN       \n",
       "3397                82.6         3598.9                     NaN       \n",
       "\n",
       "[97 rows x 12 columns]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# I can use the percents and change in vote margin to calculate the votes for each candidate.  This is not an ideal way since \n",
    "# percents are rounded and induce error.\n",
    "\n",
    "# check if percents add to one hundred percent\n",
    "result_df[ (result_df['Change']==\"Unknown\") & (result_df['Leader Batch Breakdown'] + result_df['Trailer Batch Breakdown'] == 100) ]\n",
    "# These 97 rows can be used to calculate the Unknown value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "increased-boards",
   "metadata": {
    "tags": []
   },
   "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>Timestamp</th>\n",
       "      <th>In The Lead</th>\n",
       "      <th>Vote Margin</th>\n",
       "      <th>Votes Remaining (est.)</th>\n",
       "      <th>Change</th>\n",
       "      <th>State</th>\n",
       "      <th>Leader Batch Breakdown</th>\n",
       "      <th>Trailer Batch Breakdown</th>\n",
       "      <th>Trailer Batch Trend</th>\n",
       "      <th>Trailer Hurdle</th>\n",
       "      <th>Margin_change_estimate</th>\n",
       "      <th>Type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-01-05 15:21:52 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>591546</td>\n",
       "      <td>4</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>9.1</td>\n",
       "      <td>109.1</td>\n",
       "      <td>58.5</td>\n",
       "      <td>7499241.2</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2020-11-06 18:40:11 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>596046</td>\n",
       "      <td>26070</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>5.9</td>\n",
       "      <td>105.9</td>\n",
       "      <td>49.1</td>\n",
       "      <td>1209.4</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>2020-11-25 01:59:12 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>36355</td>\n",
       "      <td>34858</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>6.8</td>\n",
       "      <td>106.8</td>\n",
       "      <td>48.2</td>\n",
       "      <td>104.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>899</th>\n",
       "      <td>2020-11-09 21:52:34 UTC</td>\n",
       "      <td>Biden</td>\n",
       "      <td>711793</td>\n",
       "      <td>535553</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Illinois</td>\n",
       "      <td>38.3</td>\n",
       "      <td>138.3</td>\n",
       "      <td>21.6</td>\n",
       "      <td>117.7</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>993</th>\n",
       "      <td>2020-11-11 14:59:03 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>138793</td>\n",
       "      <td>3227</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Iowa</td>\n",
       "      <td>114.3</td>\n",
       "      <td>14.3</td>\n",
       "      <td>39.1</td>\n",
       "      <td>2244.8</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1629</th>\n",
       "      <td>2020-11-25 14:47:14 UTC</td>\n",
       "      <td>Biden</td>\n",
       "      <td>233039</td>\n",
       "      <td>90020</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Minnesota</td>\n",
       "      <td>37.0</td>\n",
       "      <td>137.0</td>\n",
       "      <td>52.7</td>\n",
       "      <td>182.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1632</th>\n",
       "      <td>2020-11-25 14:10:14 UTC</td>\n",
       "      <td>Biden</td>\n",
       "      <td>232802</td>\n",
       "      <td>94785</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Minnesota</td>\n",
       "      <td>300.0</td>\n",
       "      <td>400.0</td>\n",
       "      <td>52.7</td>\n",
       "      <td>175.7</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1813</th>\n",
       "      <td>2020-12-06 14:16:59 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>217366</td>\n",
       "      <td>12735</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Mississippi</td>\n",
       "      <td>52.4</td>\n",
       "      <td>152.4</td>\n",
       "      <td>69.3</td>\n",
       "      <td>915.9</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1823</th>\n",
       "      <td>2020-11-17 02:21:52 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>236423</td>\n",
       "      <td>67085</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Mississippi</td>\n",
       "      <td>736.4</td>\n",
       "      <td>836.4</td>\n",
       "      <td>54.3</td>\n",
       "      <td>228.8</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1878</th>\n",
       "      <td>2020-12-09 14:21:38 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>465722</td>\n",
       "      <td>1422</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Missouri</td>\n",
       "      <td>13.3</td>\n",
       "      <td>113.3</td>\n",
       "      <td>59.6</td>\n",
       "      <td>16724.3</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2441</th>\n",
       "      <td>2020-11-09 20:27:00 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>470723</td>\n",
       "      <td>157615</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Ohio</td>\n",
       "      <td>46.4</td>\n",
       "      <td>146.4</td>\n",
       "      <td>34.0</td>\n",
       "      <td>201.6</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2974</th>\n",
       "      <td>2020-11-09 21:44:56 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>647601</td>\n",
       "      <td>68196</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Texas</td>\n",
       "      <td>123.2</td>\n",
       "      <td>23.2</td>\n",
       "      <td>66.1</td>\n",
       "      <td>532.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3096</th>\n",
       "      <td>2020-11-17 20:55:57 UTC</td>\n",
       "      <td>Biden</td>\n",
       "      <td>450833</td>\n",
       "      <td>18042</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Virginia</td>\n",
       "      <td>14.3</td>\n",
       "      <td>114.3</td>\n",
       "      <td>25.3</td>\n",
       "      <td>1323.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3097</th>\n",
       "      <td>2020-11-17 20:48:37 UTC</td>\n",
       "      <td>Biden</td>\n",
       "      <td>450788</td>\n",
       "      <td>19687</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Virginia</td>\n",
       "      <td>3.7</td>\n",
       "      <td>103.7</td>\n",
       "      <td>24.9</td>\n",
       "      <td>1217.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3098</th>\n",
       "      <td>2020-11-17 20:40:39 UTC</td>\n",
       "      <td>Biden</td>\n",
       "      <td>450584</td>\n",
       "      <td>22881</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Virginia</td>\n",
       "      <td>105.4</td>\n",
       "      <td>5.4</td>\n",
       "      <td>25.4</td>\n",
       "      <td>1053.6</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3383</th>\n",
       "      <td>2020-11-16 19:15:12 UTC</td>\n",
       "      <td>Biden</td>\n",
       "      <td>20527</td>\n",
       "      <td>261</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Wisconsin</td>\n",
       "      <td>105.9</td>\n",
       "      <td>5.9</td>\n",
       "      <td>70.2</td>\n",
       "      <td>4051.1</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    Timestamp In The Lead  Vote Margin Votes Remaining (est.)  \\\n",
       "0     2021-01-05 15:21:52 UTC       Trump       591546                      4   \n",
       "7     2020-11-06 18:40:11 UTC       Trump       596046                  26070   \n",
       "22    2020-11-25 01:59:12 UTC       Trump        36355                  34858   \n",
       "899   2020-11-09 21:52:34 UTC       Biden       711793                 535553   \n",
       "993   2020-11-11 14:59:03 UTC       Trump       138793                   3227   \n",
       "1629  2020-11-25 14:47:14 UTC       Biden       233039                  90020   \n",
       "1632  2020-11-25 14:10:14 UTC       Biden       232802                  94785   \n",
       "1813  2020-12-06 14:16:59 UTC       Trump       217366                  12735   \n",
       "1823  2020-11-17 02:21:52 UTC       Trump       236423                  67085   \n",
       "1878  2020-12-09 14:21:38 UTC       Trump       465722                   1422   \n",
       "2441  2020-11-09 20:27:00 UTC       Trump       470723                 157615   \n",
       "2974  2020-11-09 21:44:56 UTC       Trump       647601                  68196   \n",
       "3096  2020-11-17 20:55:57 UTC       Biden       450833                  18042   \n",
       "3097  2020-11-17 20:48:37 UTC       Biden       450788                  19687   \n",
       "3098  2020-11-17 20:40:39 UTC       Biden       450584                  22881   \n",
       "3383  2020-11-16 19:15:12 UTC       Biden        20527                    261   \n",
       "\n",
       "       Change        State  Leader Batch Breakdown  Trailer Batch Breakdown  \\\n",
       "0     Unknown      Alabama                     9.1                    109.1   \n",
       "7     Unknown      Alabama                     5.9                    105.9   \n",
       "22    Unknown       Alaska                     6.8                    106.8   \n",
       "899   Unknown     Illinois                    38.3                    138.3   \n",
       "993   Unknown         Iowa                   114.3                     14.3   \n",
       "1629  Unknown    Minnesota                    37.0                    137.0   \n",
       "1632  Unknown    Minnesota                   300.0                    400.0   \n",
       "1813  Unknown  Mississippi                    52.4                    152.4   \n",
       "1823  Unknown  Mississippi                   736.4                    836.4   \n",
       "1878  Unknown     Missouri                    13.3                    113.3   \n",
       "2441  Unknown         Ohio                    46.4                    146.4   \n",
       "2974  Unknown        Texas                   123.2                     23.2   \n",
       "3096  Unknown     Virginia                    14.3                    114.3   \n",
       "3097  Unknown     Virginia                     3.7                    103.7   \n",
       "3098  Unknown     Virginia                   105.4                      5.4   \n",
       "3383  Unknown    Wisconsin                   105.9                      5.9   \n",
       "\n",
       "     Trailer Batch Trend Trailer Hurdle  Margin_change_estimate Type  \n",
       "0                   58.5      7499241.2                     NaN       \n",
       "7                   49.1         1209.4                     NaN       \n",
       "22                  48.2          104.5                     NaN       \n",
       "899                 21.6          117.7                     NaN       \n",
       "993                 39.1         2244.8                     NaN       \n",
       "1629                52.7          182.5                     NaN       \n",
       "1632                52.7          175.7                     NaN       \n",
       "1813                69.3          915.9                     NaN       \n",
       "1823                54.3          228.8                     NaN       \n",
       "1878                59.6        16724.3                     NaN       \n",
       "2441                34.0          201.6                     NaN       \n",
       "2974                66.1          532.0                     NaN       \n",
       "3096                25.3         1323.5                     NaN       \n",
       "3097                24.9         1217.0                     NaN       \n",
       "3098                25.4         1053.6                     NaN       \n",
       "3383                70.2         4051.1                     NaN       "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result_df[ (result_df['Change']==\"Unknown\") & (result_df['Leader Batch Breakdown'] + result_df['Trailer Batch Breakdown'] != 100) ]\n",
    "# These 16 rows have faulty change and percent breakdowns so these batch records should be thrown out."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "overall-complex",
   "metadata": {},
   "source": [
    "## Add change in margin to DF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "exterior-sustainability",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[13, 39, 125, 211, 355, 428, 559, 565, 575, 624, 850, 854, 863, 933, 984, 1060, 1153, 1210, 1225, 1321, 1419, 1545, 1622, 1810, 1873, 1890, 1910, 1935, 1981, 2046, 2191, 2216, 2298, 2364, 2401, 2443, 2448, 2484, 2820, 2832, 2896, 2938, 2945, 3001, 3055, 3085, 3191, 3341, 3372, 3400, 3405]\n"
     ]
    }
   ],
   "source": [
    "# For each batch added the margin of the votes can not increase/decrease more than the Change columns\n",
    "# key for this is to enure we are only looking at data for each state because if the last row has an unknown then this calculation will not work\n",
    "\n",
    "# create a copy of the data so I can change the index a bit\n",
    "# I added the 'Margin_change_estimate' column in the original dataframe so the df are identical\n",
    "df = result_df.copy()\n",
    "\n",
    "# preserve the original index in the groupby (next step)\n",
    "df.reset_index(inplace=True)\n",
    "\n",
    "# first find the last rows of each state\n",
    "last_row_list = list(df.groupby('State').last()['index'])\n",
    "print(last_row_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "controlled-nightlife",
   "metadata": {},
   "outputs": [],
   "source": [
    "# go through original df and create the change in margin calculation except on last rows of each state\n",
    "for i in range(len(result_df)-1):\n",
    "    if i not in last_row_list:\n",
    "        vote_margin_now = result_df.iloc[i,2]\n",
    "        vote_margin_prev = result_df.iloc[i+1, 2]\n",
    "        result_df.iloc[i, 10] = abs(vote_margin_now - vote_margin_prev)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "secret-wiring",
   "metadata": {
    "tags": []
   },
   "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>Timestamp</th>\n",
       "      <th>In The Lead</th>\n",
       "      <th>Vote Margin</th>\n",
       "      <th>Votes Remaining (est.)</th>\n",
       "      <th>Change</th>\n",
       "      <th>State</th>\n",
       "      <th>Leader Batch Breakdown</th>\n",
       "      <th>Trailer Batch Breakdown</th>\n",
       "      <th>Trailer Batch Trend</th>\n",
       "      <th>Trailer Hurdle</th>\n",
       "      <th>Margin_change_estimate</th>\n",
       "      <th>Type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2020-12-02 15:22:19 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>36173</td>\n",
       "      <td>31816</td>\n",
       "      <td>587</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>22.7</td>\n",
       "      <td>77.3</td>\n",
       "      <td>49.4</td>\n",
       "      <td>109.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>2020-11-13 21:47:22 UTC</td>\n",
       "      <td>Biden</td>\n",
       "      <td>10986</td>\n",
       "      <td>4417</td>\n",
       "      <td>2</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>0.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>53.1</td>\n",
       "      <td>176.4</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>137</th>\n",
       "      <td>2020-11-18 16:53:14 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>336300</td>\n",
       "      <td>1769</td>\n",
       "      <td>36</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>30.6</td>\n",
       "      <td>69.4</td>\n",
       "      <td>43.7</td>\n",
       "      <td>9832.1</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>230</th>\n",
       "      <td>2020-11-27 23:11:15 UTC</td>\n",
       "      <td>Biden</td>\n",
       "      <td>5100667</td>\n",
       "      <td>59916</td>\n",
       "      <td>2176</td>\n",
       "      <td>California</td>\n",
       "      <td>71.5</td>\n",
       "      <td>28.5</td>\n",
       "      <td>39.3</td>\n",
       "      <td>4401.1</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>427</th>\n",
       "      <td>2020-11-04 18:56:14 UTC</td>\n",
       "      <td>Biden</td>\n",
       "      <td>410041</td>\n",
       "      <td>329999</td>\n",
       "      <td>3436</td>\n",
       "      <td>Colorado</td>\n",
       "      <td>45.4</td>\n",
       "      <td>54.6</td>\n",
       "      <td>54.6</td>\n",
       "      <td>113.9</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>522</th>\n",
       "      <td>2020-11-04 23:49:45 UTC</td>\n",
       "      <td>Biden</td>\n",
       "      <td>305877</td>\n",
       "      <td>146135</td>\n",
       "      <td>8</td>\n",
       "      <td>Connecticut</td>\n",
       "      <td>100.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>42.4</td>\n",
       "      <td>156.3</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>690</th>\n",
       "      <td>2020-11-09 17:17:21 UTC</td>\n",
       "      <td>Biden</td>\n",
       "      <td>10625</td>\n",
       "      <td>10657</td>\n",
       "      <td>81</td>\n",
       "      <td>Georgia</td>\n",
       "      <td>51.9</td>\n",
       "      <td>48.1</td>\n",
       "      <td>29.0</td>\n",
       "      <td>100.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>696</th>\n",
       "      <td>2020-11-09 13:43:13 UTC</td>\n",
       "      <td>Biden</td>\n",
       "      <td>10352</td>\n",
       "      <td>11757</td>\n",
       "      <td>5</td>\n",
       "      <td>Georgia</td>\n",
       "      <td>40.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>28.2</td>\n",
       "      <td>94.6</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>706</th>\n",
       "      <td>2020-11-07 02:58:27 UTC</td>\n",
       "      <td>Biden</td>\n",
       "      <td>4395</td>\n",
       "      <td>22595</td>\n",
       "      <td>160</td>\n",
       "      <td>Georgia</td>\n",
       "      <td>83.1</td>\n",
       "      <td>16.9</td>\n",
       "      <td>32.7</td>\n",
       "      <td>59.8</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>755</th>\n",
       "      <td>2020-11-06 16:32:19 UTC</td>\n",
       "      <td>Biden</td>\n",
       "      <td>1587</td>\n",
       "      <td>34558</td>\n",
       "      <td>15</td>\n",
       "      <td>Georgia</td>\n",
       "      <td>53.3</td>\n",
       "      <td>46.7</td>\n",
       "      <td>30.5</td>\n",
       "      <td>52.3</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1000</th>\n",
       "      <td>2020-11-10 21:29:41 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>138802</td>\n",
       "      <td>3353</td>\n",
       "      <td>19</td>\n",
       "      <td>Iowa</td>\n",
       "      <td>73.7</td>\n",
       "      <td>26.3</td>\n",
       "      <td>37.9</td>\n",
       "      <td>2162.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1004</th>\n",
       "      <td>2020-11-10 19:37:47 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>138790</td>\n",
       "      <td>3390</td>\n",
       "      <td>3</td>\n",
       "      <td>Iowa</td>\n",
       "      <td>66.7</td>\n",
       "      <td>33.3</td>\n",
       "      <td>37.9</td>\n",
       "      <td>2139.2</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1013</th>\n",
       "      <td>2020-11-10 16:33:27 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>138527</td>\n",
       "      <td>3970</td>\n",
       "      <td>17</td>\n",
       "      <td>Iowa</td>\n",
       "      <td>70.6</td>\n",
       "      <td>29.4</td>\n",
       "      <td>41.9</td>\n",
       "      <td>1830.6</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1116</th>\n",
       "      <td>2020-11-05 19:45:41 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>206118</td>\n",
       "      <td>25423</td>\n",
       "      <td>4</td>\n",
       "      <td>Kansas</td>\n",
       "      <td>75.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>32.7</td>\n",
       "      <td>465.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1174</th>\n",
       "      <td>2020-11-07 00:06:00 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>563155</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>795</td>\n",
       "      <td>Kentucky</td>\n",
       "      <td>46.5</td>\n",
       "      <td>53.5</td>\n",
       "      <td>67.5</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1254</th>\n",
       "      <td>2020-11-07 01:05:24 UTC</td>\n",
       "      <td>Biden</td>\n",
       "      <td>78900</td>\n",
       "      <td>36562</td>\n",
       "      <td>748</td>\n",
       "      <td>Maine</td>\n",
       "      <td>43.4</td>\n",
       "      <td>56.6</td>\n",
       "      <td>50.0</td>\n",
       "      <td>161.1</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1348</th>\n",
       "      <td>2020-11-13 22:32:04 UTC</td>\n",
       "      <td>Biden</td>\n",
       "      <td>893424</td>\n",
       "      <td>210871</td>\n",
       "      <td>5804</td>\n",
       "      <td>Maryland</td>\n",
       "      <td>61.3</td>\n",
       "      <td>38.7</td>\n",
       "      <td>26.8</td>\n",
       "      <td>267.2</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1405</th>\n",
       "      <td>2020-11-05 21:37:23 UTC</td>\n",
       "      <td>Biden</td>\n",
       "      <td>607167</td>\n",
       "      <td>871324</td>\n",
       "      <td>0</td>\n",
       "      <td>Maryland</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>51.2</td>\n",
       "      <td>85.7</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1420</th>\n",
       "      <td>2020-12-11 22:11:35 UTC</td>\n",
       "      <td>Biden</td>\n",
       "      <td>1215000</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>0</td>\n",
       "      <td>Massachusetts</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>23.9</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1516</th>\n",
       "      <td>2020-11-05 16:56:46 UTC</td>\n",
       "      <td>Biden</td>\n",
       "      <td>1032368</td>\n",
       "      <td>647628</td>\n",
       "      <td>6451</td>\n",
       "      <td>Massachusetts</td>\n",
       "      <td>69.4</td>\n",
       "      <td>30.6</td>\n",
       "      <td>42.0</td>\n",
       "      <td>131.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1632</th>\n",
       "      <td>2020-11-25 14:10:14 UTC</td>\n",
       "      <td>Biden</td>\n",
       "      <td>232802</td>\n",
       "      <td>94785</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Minnesota</td>\n",
       "      <td>300.0</td>\n",
       "      <td>400.0</td>\n",
       "      <td>52.7</td>\n",
       "      <td>175.7</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1766</th>\n",
       "      <td>2020-11-05 03:49:40 UTC</td>\n",
       "      <td>Biden</td>\n",
       "      <td>231988</td>\n",
       "      <td>107412</td>\n",
       "      <td>31</td>\n",
       "      <td>Minnesota</td>\n",
       "      <td>35.5</td>\n",
       "      <td>64.5</td>\n",
       "      <td>47.3</td>\n",
       "      <td>160.6</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1845</th>\n",
       "      <td>2020-11-14 17:02:46 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>233141</td>\n",
       "      <td>129355</td>\n",
       "      <td>1228</td>\n",
       "      <td>Mississippi</td>\n",
       "      <td>58.8</td>\n",
       "      <td>41.2</td>\n",
       "      <td>53.5</td>\n",
       "      <td>141.4</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2057</th>\n",
       "      <td>2020-11-20 15:00:19 UTC</td>\n",
       "      <td>Biden</td>\n",
       "      <td>725294</td>\n",
       "      <td>53395</td>\n",
       "      <td>13360</td>\n",
       "      <td>New Jersey</td>\n",
       "      <td>72.5</td>\n",
       "      <td>27.5</td>\n",
       "      <td>29.2</td>\n",
       "      <td>740.2</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2120</th>\n",
       "      <td>2020-11-10 21:44:40 UTC</td>\n",
       "      <td>Biden</td>\n",
       "      <td>648786</td>\n",
       "      <td>638197</td>\n",
       "      <td>17731</td>\n",
       "      <td>New Jersey</td>\n",
       "      <td>57.7</td>\n",
       "      <td>42.3</td>\n",
       "      <td>47.6</td>\n",
       "      <td>101.7</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2137</th>\n",
       "      <td>2020-11-09 16:41:11 UTC</td>\n",
       "      <td>Biden</td>\n",
       "      <td>653079</td>\n",
       "      <td>838423</td>\n",
       "      <td>93922</td>\n",
       "      <td>New Jersey</td>\n",
       "      <td>46.6</td>\n",
       "      <td>53.4</td>\n",
       "      <td>53.4</td>\n",
       "      <td>89.6</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2157</th>\n",
       "      <td>2020-11-06 20:08:18 UTC</td>\n",
       "      <td>Biden</td>\n",
       "      <td>649553</td>\n",
       "      <td>1223513</td>\n",
       "      <td>2113</td>\n",
       "      <td>New Jersey</td>\n",
       "      <td>57.3</td>\n",
       "      <td>42.7</td>\n",
       "      <td>57.6</td>\n",
       "      <td>77.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2182</th>\n",
       "      <td>2020-11-05 19:45:41 UTC</td>\n",
       "      <td>Biden</td>\n",
       "      <td>658238</td>\n",
       "      <td>1536278</td>\n",
       "      <td>31294</td>\n",
       "      <td>New Jersey</td>\n",
       "      <td>48.1</td>\n",
       "      <td>51.9</td>\n",
       "      <td>51.9</td>\n",
       "      <td>71.8</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2235</th>\n",
       "      <td>2020-11-24 19:34:17 UTC</td>\n",
       "      <td>Biden</td>\n",
       "      <td>1085655</td>\n",
       "      <td>1458186</td>\n",
       "      <td>24831</td>\n",
       "      <td>New York</td>\n",
       "      <td>75.6</td>\n",
       "      <td>24.4</td>\n",
       "      <td>25.1</td>\n",
       "      <td>87.7</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2309</th>\n",
       "      <td>2020-11-15 15:33:28 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>73302</td>\n",
       "      <td>4616</td>\n",
       "      <td>634</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>52.1</td>\n",
       "      <td>47.9</td>\n",
       "      <td>47.2</td>\n",
       "      <td>855.9</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2466</th>\n",
       "      <td>2020-11-19 02:15:50 UTC</td>\n",
       "      <td>Biden</td>\n",
       "      <td>381292</td>\n",
       "      <td>13412</td>\n",
       "      <td>2270</td>\n",
       "      <td>Oregon</td>\n",
       "      <td>55.6</td>\n",
       "      <td>44.4</td>\n",
       "      <td>40.8</td>\n",
       "      <td>1518.1</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2491</th>\n",
       "      <td>2020-12-02 00:43:30 UTC</td>\n",
       "      <td>Biden</td>\n",
       "      <td>81638</td>\n",
       "      <td>1074</td>\n",
       "      <td>2</td>\n",
       "      <td>Pennsylvania</td>\n",
       "      <td>100.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>50.1</td>\n",
       "      <td>3899.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2578</th>\n",
       "      <td>2020-11-13 19:13:58 UTC</td>\n",
       "      <td>Biden</td>\n",
       "      <td>58988</td>\n",
       "      <td>95071</td>\n",
       "      <td>1</td>\n",
       "      <td>Pennsylvania</td>\n",
       "      <td>0.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>39.4</td>\n",
       "      <td>81.4</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2645</th>\n",
       "      <td>2020-11-09 16:41:11 UTC</td>\n",
       "      <td>Biden</td>\n",
       "      <td>45657</td>\n",
       "      <td>160034</td>\n",
       "      <td>25</td>\n",
       "      <td>Pennsylvania</td>\n",
       "      <td>24.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>25.4</td>\n",
       "      <td>64.4</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2682</th>\n",
       "      <td>2020-11-06 19:11:02 UTC</td>\n",
       "      <td>Biden</td>\n",
       "      <td>13558</td>\n",
       "      <td>230341</td>\n",
       "      <td>1840</td>\n",
       "      <td>Pennsylvania</td>\n",
       "      <td>54.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>29.3</td>\n",
       "      <td>53.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2726</th>\n",
       "      <td>2020-11-05 21:21:19 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>97908</td>\n",
       "      <td>459803</td>\n",
       "      <td>80</td>\n",
       "      <td>Pennsylvania</td>\n",
       "      <td>55.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>72.4</td>\n",
       "      <td>60.8</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2731</th>\n",
       "      <td>2020-11-05 20:01:34 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>108592</td>\n",
       "      <td>479097</td>\n",
       "      <td>3382</td>\n",
       "      <td>Pennsylvania</td>\n",
       "      <td>47.3</td>\n",
       "      <td>52.7</td>\n",
       "      <td>69.8</td>\n",
       "      <td>61.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2768</th>\n",
       "      <td>2020-11-04 22:54:27 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>292148</td>\n",
       "      <td>866111</td>\n",
       "      <td>1848</td>\n",
       "      <td>Pennsylvania</td>\n",
       "      <td>42.9</td>\n",
       "      <td>57.1</td>\n",
       "      <td>70.0</td>\n",
       "      <td>67.1</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3132</th>\n",
       "      <td>2020-11-09 21:35:44 UTC</td>\n",
       "      <td>Biden</td>\n",
       "      <td>434328</td>\n",
       "      <td>38529</td>\n",
       "      <td>618</td>\n",
       "      <td>Virginia</td>\n",
       "      <td>61.7</td>\n",
       "      <td>38.3</td>\n",
       "      <td>37.5</td>\n",
       "      <td>624.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3145</th>\n",
       "      <td>2020-11-09 00:38:26 UTC</td>\n",
       "      <td>Biden</td>\n",
       "      <td>430952</td>\n",
       "      <td>49372</td>\n",
       "      <td>1608</td>\n",
       "      <td>Virginia</td>\n",
       "      <td>73.3</td>\n",
       "      <td>26.7</td>\n",
       "      <td>36.8</td>\n",
       "      <td>494.9</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3211</th>\n",
       "      <td>2020-11-24 18:39:17 UTC</td>\n",
       "      <td>Biden</td>\n",
       "      <td>784672</td>\n",
       "      <td>31478</td>\n",
       "      <td>11</td>\n",
       "      <td>Washington</td>\n",
       "      <td>36.4</td>\n",
       "      <td>63.6</td>\n",
       "      <td>48.9</td>\n",
       "      <td>1338.4</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3253</th>\n",
       "      <td>2020-11-14 00:30:24 UTC</td>\n",
       "      <td>Biden</td>\n",
       "      <td>784439</td>\n",
       "      <td>59125</td>\n",
       "      <td>1768</td>\n",
       "      <td>Washington</td>\n",
       "      <td>54.1</td>\n",
       "      <td>45.9</td>\n",
       "      <td>52.7</td>\n",
       "      <td>735.7</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3260</th>\n",
       "      <td>2020-11-13 01:00:45 UTC</td>\n",
       "      <td>Biden</td>\n",
       "      <td>784747</td>\n",
       "      <td>68554</td>\n",
       "      <td>2674</td>\n",
       "      <td>Washington</td>\n",
       "      <td>53.3</td>\n",
       "      <td>46.7</td>\n",
       "      <td>52.3</td>\n",
       "      <td>641.7</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3316</th>\n",
       "      <td>2020-11-06 00:01:17 UTC</td>\n",
       "      <td>Biden</td>\n",
       "      <td>820354</td>\n",
       "      <td>461090</td>\n",
       "      <td>66679</td>\n",
       "      <td>Washington</td>\n",
       "      <td>66.1</td>\n",
       "      <td>33.9</td>\n",
       "      <td>33.9</td>\n",
       "      <td>142.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3384</th>\n",
       "      <td>2020-11-12 02:35:43 UTC</td>\n",
       "      <td>Biden</td>\n",
       "      <td>20546</td>\n",
       "      <td>254</td>\n",
       "      <td>16</td>\n",
       "      <td>Wisconsin</td>\n",
       "      <td>62.5</td>\n",
       "      <td>37.5</td>\n",
       "      <td>69.0</td>\n",
       "      <td>4165.1</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3405</th>\n",
       "      <td>2020-11-04 13:28:22 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>119839</td>\n",
       "      <td>422</td>\n",
       "      <td>0</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>14765.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    Timestamp In The Lead  Vote Margin Votes Remaining (est.)  \\\n",
       "17    2020-12-02 15:22:19 UTC       Trump        36173                  31816   \n",
       "49    2020-11-13 21:47:22 UTC       Biden        10986                   4417   \n",
       "137   2020-11-18 16:53:14 UTC       Trump       336300                   1769   \n",
       "230   2020-11-27 23:11:15 UTC       Biden      5100667                  59916   \n",
       "427   2020-11-04 18:56:14 UTC       Biden       410041                 329999   \n",
       "522   2020-11-04 23:49:45 UTC       Biden       305877                 146135   \n",
       "690   2020-11-09 17:17:21 UTC       Biden        10625                  10657   \n",
       "696   2020-11-09 13:43:13 UTC       Biden        10352                  11757   \n",
       "706   2020-11-07 02:58:27 UTC       Biden         4395                  22595   \n",
       "755   2020-11-06 16:32:19 UTC       Biden         1587                  34558   \n",
       "1000  2020-11-10 21:29:41 UTC       Trump       138802                   3353   \n",
       "1004  2020-11-10 19:37:47 UTC       Trump       138790                   3390   \n",
       "1013  2020-11-10 16:33:27 UTC       Trump       138527                   3970   \n",
       "1116  2020-11-05 19:45:41 UTC       Trump       206118                  25423   \n",
       "1174  2020-11-07 00:06:00 UTC       Trump       563155                Unknown   \n",
       "1254  2020-11-07 01:05:24 UTC       Biden        78900                  36562   \n",
       "1348  2020-11-13 22:32:04 UTC       Biden       893424                 210871   \n",
       "1405  2020-11-05 21:37:23 UTC       Biden       607167                 871324   \n",
       "1420  2020-12-11 22:11:35 UTC       Biden      1215000                Unknown   \n",
       "1516  2020-11-05 16:56:46 UTC       Biden      1032368                 647628   \n",
       "1632  2020-11-25 14:10:14 UTC       Biden       232802                  94785   \n",
       "1766  2020-11-05 03:49:40 UTC       Biden       231988                 107412   \n",
       "1845  2020-11-14 17:02:46 UTC       Trump       233141                 129355   \n",
       "2057  2020-11-20 15:00:19 UTC       Biden       725294                  53395   \n",
       "2120  2020-11-10 21:44:40 UTC       Biden       648786                 638197   \n",
       "2137  2020-11-09 16:41:11 UTC       Biden       653079                 838423   \n",
       "2157  2020-11-06 20:08:18 UTC       Biden       649553                1223513   \n",
       "2182  2020-11-05 19:45:41 UTC       Biden       658238                1536278   \n",
       "2235  2020-11-24 19:34:17 UTC       Biden      1085655                1458186   \n",
       "2309  2020-11-15 15:33:28 UTC       Trump        73302                   4616   \n",
       "2466  2020-11-19 02:15:50 UTC       Biden       381292                  13412   \n",
       "2491  2020-12-02 00:43:30 UTC       Biden        81638                   1074   \n",
       "2578  2020-11-13 19:13:58 UTC       Biden        58988                  95071   \n",
       "2645  2020-11-09 16:41:11 UTC       Biden        45657                 160034   \n",
       "2682  2020-11-06 19:11:02 UTC       Biden        13558                 230341   \n",
       "2726  2020-11-05 21:21:19 UTC       Trump        97908                 459803   \n",
       "2731  2020-11-05 20:01:34 UTC       Trump       108592                 479097   \n",
       "2768  2020-11-04 22:54:27 UTC       Trump       292148                 866111   \n",
       "3132  2020-11-09 21:35:44 UTC       Biden       434328                  38529   \n",
       "3145  2020-11-09 00:38:26 UTC       Biden       430952                  49372   \n",
       "3211  2020-11-24 18:39:17 UTC       Biden       784672                  31478   \n",
       "3253  2020-11-14 00:30:24 UTC       Biden       784439                  59125   \n",
       "3260  2020-11-13 01:00:45 UTC       Biden       784747                  68554   \n",
       "3316  2020-11-06 00:01:17 UTC       Biden       820354                 461090   \n",
       "3384  2020-11-12 02:35:43 UTC       Biden        20546                    254   \n",
       "3405  2020-11-04 13:28:22 UTC       Trump       119839                    422   \n",
       "\n",
       "       Change           State  Leader Batch Breakdown  \\\n",
       "17        587          Alaska                    22.7   \n",
       "49          2         Arizona                     0.0   \n",
       "137        36        Arkansas                    30.6   \n",
       "230      2176      California                    71.5   \n",
       "427      3436        Colorado                    45.4   \n",
       "522         8     Connecticut                   100.0   \n",
       "690        81         Georgia                    51.9   \n",
       "696         5         Georgia                    40.0   \n",
       "706       160         Georgia                    83.1   \n",
       "755        15         Georgia                    53.3   \n",
       "1000       19            Iowa                    73.7   \n",
       "1004        3            Iowa                    66.7   \n",
       "1013       17            Iowa                    70.6   \n",
       "1116        4          Kansas                    75.0   \n",
       "1174      795        Kentucky                    46.5   \n",
       "1254      748           Maine                    43.4   \n",
       "1348     5804        Maryland                    61.3   \n",
       "1405        0        Maryland                     NaN   \n",
       "1420        0   Massachusetts                     NaN   \n",
       "1516     6451   Massachusetts                    69.4   \n",
       "1632  Unknown       Minnesota                   300.0   \n",
       "1766       31       Minnesota                    35.5   \n",
       "1845     1228     Mississippi                    58.8   \n",
       "2057    13360      New Jersey                    72.5   \n",
       "2120    17731      New Jersey                    57.7   \n",
       "2137    93922      New Jersey                    46.6   \n",
       "2157     2113      New Jersey                    57.3   \n",
       "2182    31294      New Jersey                    48.1   \n",
       "2235    24831        New York                    75.6   \n",
       "2309      634  North Carolina                    52.1   \n",
       "2466     2270          Oregon                    55.6   \n",
       "2491        2    Pennsylvania                   100.0   \n",
       "2578        1    Pennsylvania                     0.0   \n",
       "2645       25    Pennsylvania                    24.0   \n",
       "2682     1840    Pennsylvania                    54.0   \n",
       "2726       80    Pennsylvania                    55.0   \n",
       "2731     3382    Pennsylvania                    47.3   \n",
       "2768     1848    Pennsylvania                    42.9   \n",
       "3132      618        Virginia                    61.7   \n",
       "3145     1608        Virginia                    73.3   \n",
       "3211       11      Washington                    36.4   \n",
       "3253     1768      Washington                    54.1   \n",
       "3260     2674      Washington                    53.3   \n",
       "3316    66679      Washington                    66.1   \n",
       "3384       16       Wisconsin                    62.5   \n",
       "3405        0         Wyoming                     NaN   \n",
       "\n",
       "      Trailer Batch Breakdown Trailer Batch Trend Trailer Hurdle  \\\n",
       "17                       77.3                49.4          109.5   \n",
       "49                      100.0                53.1          176.4   \n",
       "137                      69.4                43.7         9832.1   \n",
       "230                      28.5                39.3         4401.1   \n",
       "427                      54.6                54.6          113.9   \n",
       "522                       0.0                42.4          156.3   \n",
       "690                      48.1                29.0          100.5   \n",
       "696                      60.0                28.2           94.6   \n",
       "706                      16.9                32.7           59.8   \n",
       "755                      46.7                30.5           52.3   \n",
       "1000                     26.3                37.9         2162.5   \n",
       "1004                     33.3                37.9         2139.2   \n",
       "1013                     29.4                41.9         1830.6   \n",
       "1116                     25.0                32.7          465.5   \n",
       "1174                     53.5                67.5           None   \n",
       "1254                     56.6                50.0          161.1   \n",
       "1348                     38.7                26.8          267.2   \n",
       "1405                      NaN                51.2           85.7   \n",
       "1420                      NaN                23.9           None   \n",
       "1516                     30.6                42.0          131.5   \n",
       "1632                    400.0                52.7          175.7   \n",
       "1766                     64.5                47.3          160.6   \n",
       "1845                     41.2                53.5          141.4   \n",
       "2057                     27.5                29.2          740.2   \n",
       "2120                     42.3                47.6          101.7   \n",
       "2137                     53.4                53.4           89.6   \n",
       "2157                     42.7                57.6           77.0   \n",
       "2182                     51.9                51.9           71.8   \n",
       "2235                     24.4                25.1           87.7   \n",
       "2309                     47.9                47.2          855.9   \n",
       "2466                     44.4                40.8         1518.1   \n",
       "2491                      0.0                50.1         3899.0   \n",
       "2578                    100.0                39.4           81.4   \n",
       "2645                     76.0                25.4           64.4   \n",
       "2682                     46.0                29.3           53.0   \n",
       "2726                     45.0                72.4           60.8   \n",
       "2731                     52.7                69.8           61.5   \n",
       "2768                     57.1                70.0           67.1   \n",
       "3132                     38.3                37.5          624.5   \n",
       "3145                     26.7                36.8          494.9   \n",
       "3211                     63.6                48.9         1338.4   \n",
       "3253                     45.9                52.7          735.7   \n",
       "3260                     46.7                52.3          641.7   \n",
       "3316                     33.9                33.9          142.0   \n",
       "3384                     37.5                69.0         4165.1   \n",
       "3405                      NaN                None        14765.5   \n",
       "\n",
       "      Margin_change_estimate Type  \n",
       "17                       NaN       \n",
       "49                       NaN       \n",
       "137                      NaN       \n",
       "230                      NaN       \n",
       "427                      NaN       \n",
       "522                      NaN       \n",
       "690                      NaN       \n",
       "696                      NaN       \n",
       "706                      NaN       \n",
       "755                      NaN       \n",
       "1000                     NaN       \n",
       "1004                     NaN       \n",
       "1013                     NaN       \n",
       "1116                     NaN       \n",
       "1174                     NaN       \n",
       "1254                     NaN       \n",
       "1348                     NaN       \n",
       "1405                     NaN       \n",
       "1420                     NaN       \n",
       "1516                     NaN       \n",
       "1632                     NaN       \n",
       "1766                     NaN       \n",
       "1845                     NaN       \n",
       "2057                     NaN       \n",
       "2120                     NaN       \n",
       "2137                     NaN       \n",
       "2157                     NaN       \n",
       "2182                     NaN       \n",
       "2235                     NaN       \n",
       "2309                     NaN       \n",
       "2466                     NaN       \n",
       "2491                     NaN       \n",
       "2578                     NaN       \n",
       "2645                     NaN       \n",
       "2682                     NaN       \n",
       "2726                     NaN       \n",
       "2731                     NaN       \n",
       "2768                     NaN       \n",
       "3132                     NaN       \n",
       "3145                     NaN       \n",
       "3211                     NaN       \n",
       "3253                     NaN       \n",
       "3260                     NaN       \n",
       "3316                     NaN       \n",
       "3384                     NaN       \n",
       "3405                     NaN       "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# how many NaN are in the Margin_change_estimate column - these are last row values that lack data to calculate a change value\n",
    "# remove all of these values from the df\n",
    "result_df[ result_df['Margin_change_estimate'].isnull() ]\n",
    "# Actually, lets leave them in the df since these are the last row values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "separated-waters",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Timestamp                   object\n",
       "In The Lead                 object\n",
       "Vote Margin                  int64\n",
       "Votes Remaining (est.)      object\n",
       "Change                      object\n",
       "State                       object\n",
       "Leader Batch Breakdown     float64\n",
       "Trailer Batch Breakdown    float64\n",
       "Trailer Batch Trend         object\n",
       "Trailer Hurdle              object\n",
       "Margin_change_estimate     float64\n",
       "Type                        object\n",
       "dtype: object"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check data types before continuing\n",
    "result_df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "acting-sheriff",
   "metadata": {},
   "outputs": [],
   "source": [
    "## Need to convert the timestamp to a date and convert the Change column to integer - but I think this needs done after the unknowns are removed.\n",
    "result_df[['Timestamp']]=result_df[['Timestamp']].apply(pd.to_datetime)  ## could pass in arguments to apply like:  .apply(pd.to_datetime, args=(format = ) )\n",
    "# get existing data in the right type\n",
    "# this will be used for calculating the individal candidate vote\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "statutory-tunnel",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[0, 7, 22, 899, 993, 1629, 1632, 1813, 1823, 1878, 2441, 2974, 3096, 3097, 3098, 3383]\n"
     ]
    }
   ],
   "source": [
    "# find Unknowns where the percents do not add to 100%\n",
    "bad_unknowns = list(result_df[ (result_df['Change']==\"Unknown\") & (result_df['Leader Batch Breakdown'] + result_df['Trailer Batch Breakdown'] != 100) ].index)\n",
    "print(bad_unknowns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "periodic-success",
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop bad Unknown values that can not be replaced with the help of percent values\n",
    "result_df.drop(bad_unknowns, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "verified-submission",
   "metadata": {},
   "source": [
    "## Calculate and replace Unknowns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "agricultural-arabic",
   "metadata": {},
   "source": [
    "I think the margin estimate is wrong below because some values were dropped above.\n",
    "-- recheck this at the end of the process  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "suitable-persian",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Can not calculate vote margin for row 19\n",
      "Can not calculate vote margin for row 26\n",
      "Can not calculate vote margin for row 50\n",
      "Can not calculate vote margin for row 51\n",
      "Can not calculate vote margin for row 88\n",
      "Can not calculate vote margin for row 89\n",
      "Can not calculate vote margin for row 94\n"
     ]
    }
   ],
   "source": [
    "# replace some of the unknown values\n",
    "batch_leader_votes = result_df['Leader Batch Breakdown']\n",
    "unknown_check = result_df[(result_df['Change'] == 'Unknown') & ( (batch_leader_votes >= 0) | (batch_leader_votes <= 100) ) ].copy()\n",
    "for i in range(len(unknown_check)):\n",
    "    \n",
    "    raw_value_percent = unknown_check.iloc[i,6]\n",
    "    winner_batch_percent = float(raw_value_percent)/100\n",
    "    margin_change_estimate = unknown_check.iloc[i, 10]\n",
    "\n",
    "#     new_change = round((vote_margin_now - vote_margin_prev)/(winner_batch_percent),0)\n",
    "    if (raw_value_percent != 0.0):\n",
    "        new_change = round(margin_change_estimate/winner_batch_percent,0)\n",
    "    else:\n",
    "        new_change = round(margin_change_estimate,0)\n",
    "        print(f\"Can not calculate vote margin for row {i}\")\n",
    "        \n",
    "    unknown_check.iloc[i, 4] = new_change\n",
    "    unknown_check.iloc[i, 11] = \"updated\"\n",
    "\n",
    "# I should store these in a dataframe for future analysis\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "seasonal-nevada",
   "metadata": {},
   "outputs": [],
   "source": [
    "restored = pd.concat([result_df, unknown_check])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "serial-dollar",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Timestamp</th>\n",
       "      <th>In The Lead</th>\n",
       "      <th>Vote Margin</th>\n",
       "      <th>Votes Remaining (est.)</th>\n",
       "      <th>Change</th>\n",
       "      <th>State</th>\n",
       "      <th>Leader Batch Breakdown</th>\n",
       "      <th>Trailer Batch Breakdown</th>\n",
       "      <th>Trailer Batch Trend</th>\n",
       "      <th>Trailer Hurdle</th>\n",
       "      <th>Margin_change_estimate</th>\n",
       "      <th>Type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-12-04 21:12:00+00:00</td>\n",
       "      <td>Trump</td>\n",
       "      <td>591520</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>22</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>9.1</td>\n",
       "      <td>109.1</td>\n",
       "      <td>58.5</td>\n",
       "      <td>None</td>\n",
       "      <td>26.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-11-23 20:33:25+00:00</td>\n",
       "      <td>Trump</td>\n",
       "      <td>591546</td>\n",
       "      <td>4</td>\n",
       "      <td>2476</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>44.2</td>\n",
       "      <td>55.8</td>\n",
       "      <td>58.5</td>\n",
       "      <td>7499241.2</td>\n",
       "      <td>286.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-11-23 20:11:23+00:00</td>\n",
       "      <td>Trump</td>\n",
       "      <td>591832</td>\n",
       "      <td>2521</td>\n",
       "      <td>5894</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>50.6</td>\n",
       "      <td>49.4</td>\n",
       "      <td>55.0</td>\n",
       "      <td>11954.5</td>\n",
       "      <td>72.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-11-23 19:48:16+00:00</td>\n",
       "      <td>Trump</td>\n",
       "      <td>591760</td>\n",
       "      <td>8534</td>\n",
       "      <td>4792</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>61.2</td>\n",
       "      <td>38.8</td>\n",
       "      <td>47.8</td>\n",
       "      <td>3566.2</td>\n",
       "      <td>1074.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2020-11-06 22:26:37+00:00</td>\n",
       "      <td>Trump</td>\n",
       "      <td>590686</td>\n",
       "      <td>13386</td>\n",
       "      <td>12548</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>28.6</td>\n",
       "      <td>71.4</td>\n",
       "      <td>44.9</td>\n",
       "      <td>2287.6</td>\n",
       "      <td>5360.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  Timestamp In The Lead  Vote Margin Votes Remaining (est.)  \\\n",
       "1 2020-12-04 21:12:00+00:00       Trump       591520                Unknown   \n",
       "2 2020-11-23 20:33:25+00:00       Trump       591546                      4   \n",
       "3 2020-11-23 20:11:23+00:00       Trump       591832                   2521   \n",
       "4 2020-11-23 19:48:16+00:00       Trump       591760                   8534   \n",
       "6 2020-11-06 22:26:37+00:00       Trump       590686                  13386   \n",
       "\n",
       "  Change    State  Leader Batch Breakdown  Trailer Batch Breakdown  \\\n",
       "1     22  Alabama                     9.1                    109.1   \n",
       "2   2476  Alabama                    44.2                     55.8   \n",
       "3   5894  Alabama                    50.6                     49.4   \n",
       "4   4792  Alabama                    61.2                     38.8   \n",
       "6  12548  Alabama                    28.6                     71.4   \n",
       "\n",
       "  Trailer Batch Trend Trailer Hurdle  Margin_change_estimate Type  \n",
       "1                58.5           None                    26.0       \n",
       "2                58.5      7499241.2                   286.0       \n",
       "3                55.0        11954.5                    72.0       \n",
       "4                47.8         3566.2                  1074.0       \n",
       "6                44.9         2287.6                  5360.0       "
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filtered_df = restored[~restored.index.duplicated(keep='last')]\n",
    "filtered_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "moved-taiwan",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Timestamp</th>\n",
       "      <th>In The Lead</th>\n",
       "      <th>Vote Margin</th>\n",
       "      <th>Votes Remaining (est.)</th>\n",
       "      <th>Change</th>\n",
       "      <th>State</th>\n",
       "      <th>Leader Batch Breakdown</th>\n",
       "      <th>Trailer Batch Breakdown</th>\n",
       "      <th>Trailer Batch Trend</th>\n",
       "      <th>Trailer Hurdle</th>\n",
       "      <th>Margin_change_estimate</th>\n",
       "      <th>Type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-12-04 21:12:00+00:00</td>\n",
       "      <td>Trump</td>\n",
       "      <td>591520</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>22</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>9.1</td>\n",
       "      <td>109.1</td>\n",
       "      <td>58.5</td>\n",
       "      <td>None</td>\n",
       "      <td>26.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-11-23 20:33:25+00:00</td>\n",
       "      <td>Trump</td>\n",
       "      <td>591546</td>\n",
       "      <td>4</td>\n",
       "      <td>2476</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>44.2</td>\n",
       "      <td>55.8</td>\n",
       "      <td>58.5</td>\n",
       "      <td>7499241.2</td>\n",
       "      <td>286.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-11-23 20:11:23+00:00</td>\n",
       "      <td>Trump</td>\n",
       "      <td>591832</td>\n",
       "      <td>2521</td>\n",
       "      <td>5894</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>50.6</td>\n",
       "      <td>49.4</td>\n",
       "      <td>55.0</td>\n",
       "      <td>11954.5</td>\n",
       "      <td>72.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-11-23 19:48:16+00:00</td>\n",
       "      <td>Trump</td>\n",
       "      <td>591760</td>\n",
       "      <td>8534</td>\n",
       "      <td>4792</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>61.2</td>\n",
       "      <td>38.8</td>\n",
       "      <td>47.8</td>\n",
       "      <td>3566.2</td>\n",
       "      <td>1074.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2020-11-06 22:26:37+00:00</td>\n",
       "      <td>Trump</td>\n",
       "      <td>590686</td>\n",
       "      <td>13386</td>\n",
       "      <td>12548</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>28.6</td>\n",
       "      <td>71.4</td>\n",
       "      <td>44.9</td>\n",
       "      <td>2287.6</td>\n",
       "      <td>5360.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2020-11-04 21:12:19+00:00</td>\n",
       "      <td>Trump</td>\n",
       "      <td>595572</td>\n",
       "      <td>25662</td>\n",
       "      <td>10</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>30.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>49.4</td>\n",
       "      <td>1226.9</td>\n",
       "      <td>4.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2020-11-04 21:00:38+00:00</td>\n",
       "      <td>Trump</td>\n",
       "      <td>595576</td>\n",
       "      <td>25815</td>\n",
       "      <td>2938</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>59.2</td>\n",
       "      <td>40.8</td>\n",
       "      <td>49.5</td>\n",
       "      <td>1219.9</td>\n",
       "      <td>542.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2020-11-04 20:28:27+00:00</td>\n",
       "      <td>Trump</td>\n",
       "      <td>595034</td>\n",
       "      <td>28763</td>\n",
       "      <td>54</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>1870.4</td>\n",
       "      <td>1970.4</td>\n",
       "      <td>53.5</td>\n",
       "      <td>1099.0</td>\n",
       "      <td>2074.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2020-11-04 20:23:38+00:00</td>\n",
       "      <td>Trump</td>\n",
       "      <td>597108</td>\n",
       "      <td>28830</td>\n",
       "      <td>13958</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>86.9</td>\n",
       "      <td>13.1</td>\n",
       "      <td>50.1</td>\n",
       "      <td>1100.3</td>\n",
       "      <td>10298.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2020-11-04 17:16:47+00:00</td>\n",
       "      <td>Trump</td>\n",
       "      <td>586810</td>\n",
       "      <td>43805</td>\n",
       "      <td>48782</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>17.7</td>\n",
       "      <td>82.3</td>\n",
       "      <td>82.3</td>\n",
       "      <td>729.3</td>\n",
       "      <td>31534.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2020-11-04 13:28:22+00:00</td>\n",
       "      <td>Trump</td>\n",
       "      <td>618344</td>\n",
       "      <td>93031</td>\n",
       "      <td>0</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>387.0</td>\n",
       "      <td>582171.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   Timestamp In The Lead  Vote Margin Votes Remaining (est.)  \\\n",
       "1  2020-12-04 21:12:00+00:00       Trump       591520                Unknown   \n",
       "2  2020-11-23 20:33:25+00:00       Trump       591546                      4   \n",
       "3  2020-11-23 20:11:23+00:00       Trump       591832                   2521   \n",
       "4  2020-11-23 19:48:16+00:00       Trump       591760                   8534   \n",
       "6  2020-11-06 22:26:37+00:00       Trump       590686                  13386   \n",
       "8  2020-11-04 21:12:19+00:00       Trump       595572                  25662   \n",
       "9  2020-11-04 21:00:38+00:00       Trump       595576                  25815   \n",
       "10 2020-11-04 20:28:27+00:00       Trump       595034                  28763   \n",
       "11 2020-11-04 20:23:38+00:00       Trump       597108                  28830   \n",
       "12 2020-11-04 17:16:47+00:00       Trump       586810                  43805   \n",
       "13 2020-11-04 13:28:22+00:00       Trump       618344                  93031   \n",
       "\n",
       "   Change    State  Leader Batch Breakdown  Trailer Batch Breakdown  \\\n",
       "1      22  Alabama                     9.1                    109.1   \n",
       "2    2476  Alabama                    44.2                     55.8   \n",
       "3    5894  Alabama                    50.6                     49.4   \n",
       "4    4792  Alabama                    61.2                     38.8   \n",
       "6   12548  Alabama                    28.6                     71.4   \n",
       "8      10  Alabama                    30.0                     70.0   \n",
       "9    2938  Alabama                    59.2                     40.8   \n",
       "10     54  Alabama                  1870.4                   1970.4   \n",
       "11  13958  Alabama                    86.9                     13.1   \n",
       "12  48782  Alabama                    17.7                     82.3   \n",
       "13      0  Alabama                     NaN                      NaN   \n",
       "\n",
       "   Trailer Batch Trend Trailer Hurdle  Margin_change_estimate Type  \n",
       "1                 58.5           None                    26.0       \n",
       "2                 58.5      7499241.2                   286.0       \n",
       "3                 55.0        11954.5                    72.0       \n",
       "4                 47.8         3566.2                  1074.0       \n",
       "6                 44.9         2287.6                  5360.0       \n",
       "8                 49.4         1226.9                     4.0       \n",
       "9                 49.5         1219.9                   542.0       \n",
       "10                53.5         1099.0                  2074.0       \n",
       "11                50.1         1100.3                 10298.0       \n",
       "12                82.3          729.3                 31534.0       \n",
       "13                None          387.0                582171.0       "
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filtered_df[ filtered_df['State'] == 'Alabama']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "better-market",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Timestamp                  datetime64[ns, UTC]\n",
       "In The Lead                             object\n",
       "Vote Margin                              int64\n",
       "Votes Remaining (est.)                  object\n",
       "Change                                  object\n",
       "State                                   object\n",
       "Leader Batch Breakdown                 float64\n",
       "Trailer Batch Breakdown                float64\n",
       "Trailer Batch Trend                     object\n",
       "Trailer Hurdle                          object\n",
       "Margin_change_estimate                 float64\n",
       "Type                                    object\n",
       "dtype: object"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filtered_df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "middle-wholesale",
   "metadata": {},
   "outputs": [],
   "source": [
    "result_df = filtered_df.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "outdoor-supplement",
   "metadata": {},
   "outputs": [],
   "source": [
    "result_df.sort_values(by=['State', 'Timestamp'], ascending=[True, False], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "fifth-superintendent",
   "metadata": {},
   "outputs": [],
   "source": [
    "# needed for groupby setup so we have access to index number within the df\n",
    "result_df.reset_index(inplace=True, drop=True)\n",
    "result_df.reset_index(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "graduate-seattle",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[10, 26, 112, 194, 285, 353, 461, 467, 477, 494, 711, 715, 722, 785, 833, 903, 963, 1014, 1029, 1121, 1219, 1345, 1405, 1582, 1640, 1650, 1670, 1681, 1727, 1789, 1934, 1955, 2027, 2086, 2120, 2160, 2165, 2198, 2522, 2534, 2592, 2633, 2640, 2688, 2742, 2772, 2861, 3011, 3028, 3054, 3059]\n",
      "[0, 11, 27, 113, 195, 286, 354, 462, 468, 478, 495, 712, 716, 723, 786, 834, 904, 964, 1015, 1030, 1122, 1220, 1346, 1406, 1583, 1641, 1651, 1671, 1682, 1728, 1790, 1935, 1956, 2028, 2087, 2121, 2161, 2166, 2199, 2523, 2535, 2593, 2634, 2641, 2689, 2743, 2773, 2862, 3012, 3029, 3055]\n"
     ]
    }
   ],
   "source": [
    "last_rows = list(result_df.groupby('State').last()['index'])\n",
    "print(last_rows)\n",
    "first_rows = list(result_df.groupby('State').first()['index'])\n",
    "print(first_rows)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "sufficient-conversation",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "index                                    int64\n",
       "Timestamp                  datetime64[ns, UTC]\n",
       "In The Lead                             object\n",
       "Vote Margin                              int64\n",
       "Votes Remaining (est.)                  object\n",
       "Change                                 float64\n",
       "State                                   object\n",
       "Leader Batch Breakdown                 float64\n",
       "Trailer Batch Breakdown                float64\n",
       "Trailer Batch Trend                    float64\n",
       "Trailer Hurdle                         float64\n",
       "Margin_change_estimate                 float64\n",
       "Type                                    object\n",
       "Batch Votes Winner                     float64\n",
       "Batch Votes Loser                      float64\n",
       "Accum. Votes Winner                    float64\n",
       "Accum. Votes Loser                     float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result_df[['Change','Trailer Batch Trend', 'Trailer Hurdle']] = result_df[['Change','Trailer Batch Trend', 'Trailer Hurdle']].apply(pd.to_numeric)\n",
    "result_df[['Batch Votes Winner', 'Batch Votes Loser', 'Accum. Votes Winner', 'Accum. Votes Loser']] = \"\"\n",
    "result_df[['Batch Votes Winner', 'Batch Votes Loser', 'Accum. Votes Winner', 'Accum. Votes Loser']] = result_df[['Batch Votes Winner', 'Batch Votes Loser', 'Accum. Votes Winner', 'Accum. Votes Loser']].apply(pd.to_numeric)\n",
    "result_df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "thick-finger",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Need to remove the rows of change data that is smaller than the vote margin change - that data doesn't make sense\n",
    "bad_data_rows = []\n",
    "for i in range(len(result_df)-1):\n",
    "    if i in last_rows:\n",
    "        ## Skip = Not valuable data\n",
    "        continue\n",
    "    else:\n",
    "        change = result_df.iloc[i,5]\n",
    "        vote_margin_now = int(result_df.iloc[i,3])\n",
    "        vote_margin_prev = int(result_df.iloc[i+1,3])\n",
    "        \n",
    "        if change > abs(vote_margin_now - vote_margin_prev):\n",
    "            ## Skip = Good Data\n",
    "            continue\n",
    "        else: \n",
    "            bad_data_rows.append(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "sharp-riverside",
   "metadata": {},
   "outputs": [],
   "source": [
    "# remove change in margin values that are \n",
    "result_df.drop(bad_data_rows, inplace=True, )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "higher-spring",
   "metadata": {},
   "outputs": [],
   "source": [
    "# check if any percentages exist over 100%"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "perfect-stocks",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>Timestamp</th>\n",
       "      <th>In The Lead</th>\n",
       "      <th>Vote Margin</th>\n",
       "      <th>Votes Remaining (est.)</th>\n",
       "      <th>Change</th>\n",
       "      <th>State</th>\n",
       "      <th>Leader Batch Breakdown</th>\n",
       "      <th>Trailer Batch Breakdown</th>\n",
       "      <th>Trailer Batch Trend</th>\n",
       "      <th>Trailer Hurdle</th>\n",
       "      <th>Margin_change_estimate</th>\n",
       "      <th>Type</th>\n",
       "      <th>Batch Votes Winner</th>\n",
       "      <th>Batch Votes Loser</th>\n",
       "      <th>Accum. Votes Winner</th>\n",
       "      <th>Accum. Votes Loser</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [index, Timestamp, In The Lead, Vote Margin, Votes Remaining (est.), Change, State, Leader Batch Breakdown, Trailer Batch Breakdown, Trailer Batch Trend, Trailer Hurdle, Margin_change_estimate, Type, Batch Votes Winner, Batch Votes Loser, Accum. Votes Winner, Accum. Votes Loser]\n",
       "Index: []"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# find if any percentages are greater than 100% and remove\n",
    "df33 = result_df[ (result_df['Trailer Batch Breakdown'] > 100) | (result_df['Trailer Batch Breakdown'] < 0)]\n",
    "df33.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "silver-majority",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "No Values greater than 100\n"
     ]
    }
   ],
   "source": [
    "if len(df33)>0:\n",
    "    result_df.drop(list(df33.index), inplace=True)\n",
    "else:\n",
    "    print(\"No Values greater than 100\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "unnecessary-fundamentals",
   "metadata": {},
   "source": [
    "## Calculate Significant Columns\n",
    "* Time Stamp\n",
    "* Leader\n",
    "* Vote Margin\n",
    "* Votes Remaining\n",
    "* Batch Total\n",
    "* Batch Votes Winner\n",
    "* Batch Votes Loser\n",
    "* Accum. Votes Winner\n",
    "* Accum. Votes Loser\n",
    "* Trailer Batch Trend\n",
    "* Hurdle Percent Needed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "biological-ethernet",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>Timestamp</th>\n",
       "      <th>In The Lead</th>\n",
       "      <th>Vote Margin</th>\n",
       "      <th>Votes Remaining (est.)</th>\n",
       "      <th>Change</th>\n",
       "      <th>State</th>\n",
       "      <th>Leader Batch Breakdown</th>\n",
       "      <th>Trailer Batch Breakdown</th>\n",
       "      <th>Trailer Batch Trend</th>\n",
       "      <th>Trailer Hurdle</th>\n",
       "      <th>Margin_change_estimate</th>\n",
       "      <th>Type</th>\n",
       "      <th>Batch Votes Winner</th>\n",
       "      <th>Batch Votes Loser</th>\n",
       "      <th>Accum. Votes Winner</th>\n",
       "      <th>Accum. Votes Loser</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2020-11-23 20:33:25+00:00</td>\n",
       "      <td>Trump</td>\n",
       "      <td>591546</td>\n",
       "      <td>4</td>\n",
       "      <td>2476.0</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>44.2</td>\n",
       "      <td>55.8</td>\n",
       "      <td>58.5</td>\n",
       "      <td>7499241.2</td>\n",
       "      <td>286.0</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>2020-11-23 20:11:23+00:00</td>\n",
       "      <td>Trump</td>\n",
       "      <td>591832</td>\n",
       "      <td>2521</td>\n",
       "      <td>5894.0</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>50.6</td>\n",
       "      <td>49.4</td>\n",
       "      <td>55.0</td>\n",
       "      <td>11954.5</td>\n",
       "      <td>72.0</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>2020-11-23 19:48:16+00:00</td>\n",
       "      <td>Trump</td>\n",
       "      <td>591760</td>\n",
       "      <td>8534</td>\n",
       "      <td>4792.0</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>61.2</td>\n",
       "      <td>38.8</td>\n",
       "      <td>47.8</td>\n",
       "      <td>3566.2</td>\n",
       "      <td>1074.0</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>2020-11-06 22:26:37+00:00</td>\n",
       "      <td>Trump</td>\n",
       "      <td>590686</td>\n",
       "      <td>13386</td>\n",
       "      <td>12548.0</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>28.6</td>\n",
       "      <td>71.4</td>\n",
       "      <td>44.9</td>\n",
       "      <td>2287.6</td>\n",
       "      <td>5360.0</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>2020-11-04 21:12:19+00:00</td>\n",
       "      <td>Trump</td>\n",
       "      <td>595572</td>\n",
       "      <td>25662</td>\n",
       "      <td>10.0</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>30.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>49.4</td>\n",
       "      <td>1226.9</td>\n",
       "      <td>4.0</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   index                 Timestamp In The Lead  Vote Margin  \\\n",
       "1      1 2020-11-23 20:33:25+00:00       Trump       591546   \n",
       "2      2 2020-11-23 20:11:23+00:00       Trump       591832   \n",
       "3      3 2020-11-23 19:48:16+00:00       Trump       591760   \n",
       "4      4 2020-11-06 22:26:37+00:00       Trump       590686   \n",
       "5      5 2020-11-04 21:12:19+00:00       Trump       595572   \n",
       "\n",
       "  Votes Remaining (est.)   Change    State  Leader Batch Breakdown  \\\n",
       "1                      4   2476.0  Alabama                    44.2   \n",
       "2                   2521   5894.0  Alabama                    50.6   \n",
       "3                   8534   4792.0  Alabama                    61.2   \n",
       "4                  13386  12548.0  Alabama                    28.6   \n",
       "5                  25662     10.0  Alabama                    30.0   \n",
       "\n",
       "   Trailer Batch Breakdown  Trailer Batch Trend  Trailer Hurdle  \\\n",
       "1                     55.8                 58.5       7499241.2   \n",
       "2                     49.4                 55.0         11954.5   \n",
       "3                     38.8                 47.8          3566.2   \n",
       "4                     71.4                 44.9          2287.6   \n",
       "5                     70.0                 49.4          1226.9   \n",
       "\n",
       "   Margin_change_estimate Type  Batch Votes Winner  Batch Votes Loser  \\\n",
       "1                   286.0                      NaN                NaN   \n",
       "2                    72.0                      NaN                NaN   \n",
       "3                  1074.0                      NaN                NaN   \n",
       "4                  5360.0                      NaN                NaN   \n",
       "5                     4.0                      NaN                NaN   \n",
       "\n",
       "   Accum. Votes Winner  Accum. Votes Loser  \n",
       "1                  NaN                 NaN  \n",
       "2                  NaN                 NaN  \n",
       "3                  NaN                 NaN  \n",
       "4                  NaN                 NaN  \n",
       "5                  NaN                 NaN  "
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# checked multiple rows to see if 'Change' column  is useable\n",
    "result_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "referenced-conjunction",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "index                                    int64\n",
       "Timestamp                  datetime64[ns, UTC]\n",
       "In The Lead                             object\n",
       "Vote Margin                              int64\n",
       "Votes Remaining (est.)                  object\n",
       "Change                                 float64\n",
       "State                                   object\n",
       "Leader Batch Breakdown                 float64\n",
       "Trailer Batch Breakdown                float64\n",
       "Trailer Batch Trend                    float64\n",
       "Trailer Hurdle                         float64\n",
       "Margin_change_estimate                 float64\n",
       "Type                                    object\n",
       "Batch Votes Winner                     float64\n",
       "Batch Votes Loser                      float64\n",
       "Accum. Votes Winner                    float64\n",
       "Accum. Votes Loser                     float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result_df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bearing-sponsorship",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "hindu-skill",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Reset index so index goes from 1,2,...,N\n",
    "result_df.drop('index', axis='columns', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "independent-format",
   "metadata": {},
   "outputs": [],
   "source": [
    "result_df.reset_index(drop=True, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "northern-groove",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>Timestamp</th>\n",
       "      <th>In The Lead</th>\n",
       "      <th>Vote Margin</th>\n",
       "      <th>Votes Remaining (est.)</th>\n",
       "      <th>Change</th>\n",
       "      <th>State</th>\n",
       "      <th>Leader Batch Breakdown</th>\n",
       "      <th>Trailer Batch Breakdown</th>\n",
       "      <th>Trailer Batch Trend</th>\n",
       "      <th>Trailer Hurdle</th>\n",
       "      <th>Margin_change_estimate</th>\n",
       "      <th>Type</th>\n",
       "      <th>Batch Votes Winner</th>\n",
       "      <th>Batch Votes Loser</th>\n",
       "      <th>Accum. Votes Winner</th>\n",
       "      <th>Accum. Votes Loser</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>2020-11-23 20:33:25+00:00</td>\n",
       "      <td>Trump</td>\n",
       "      <td>591546</td>\n",
       "      <td>4</td>\n",
       "      <td>2476.0</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>44.2</td>\n",
       "      <td>55.8</td>\n",
       "      <td>58.5</td>\n",
       "      <td>7499241.2</td>\n",
       "      <td>286.0</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2020-11-23 20:11:23+00:00</td>\n",
       "      <td>Trump</td>\n",
       "      <td>591832</td>\n",
       "      <td>2521</td>\n",
       "      <td>5894.0</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>50.6</td>\n",
       "      <td>49.4</td>\n",
       "      <td>55.0</td>\n",
       "      <td>11954.5</td>\n",
       "      <td>72.0</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   index                 Timestamp In The Lead  Vote Margin  \\\n",
       "0      0 2020-11-23 20:33:25+00:00       Trump       591546   \n",
       "1      1 2020-11-23 20:11:23+00:00       Trump       591832   \n",
       "\n",
       "  Votes Remaining (est.)  Change    State  Leader Batch Breakdown  \\\n",
       "0                      4  2476.0  Alabama                    44.2   \n",
       "1                   2521  5894.0  Alabama                    50.6   \n",
       "\n",
       "   Trailer Batch Breakdown  Trailer Batch Trend  Trailer Hurdle  \\\n",
       "0                     55.8                 58.5       7499241.2   \n",
       "1                     49.4                 55.0         11954.5   \n",
       "\n",
       "   Margin_change_estimate Type  Batch Votes Winner  Batch Votes Loser  \\\n",
       "0                   286.0                      NaN                NaN   \n",
       "1                    72.0                      NaN                NaN   \n",
       "\n",
       "   Accum. Votes Winner  Accum. Votes Loser  \n",
       "0                  NaN                 NaN  \n",
       "1                  NaN                 NaN  "
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result_df.reset_index(inplace=True)\n",
    "result_df.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "dedicated-virginia",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "index                                    int64\n",
       "Timestamp                  datetime64[ns, UTC]\n",
       "In The Lead                             object\n",
       "Vote Margin                              int64\n",
       "Votes Remaining (est.)                  object\n",
       "Change                                 float64\n",
       "State                                   object\n",
       "Leader Batch Breakdown                 float64\n",
       "Trailer Batch Breakdown                float64\n",
       "Trailer Batch Trend                    float64\n",
       "Trailer Hurdle                         float64\n",
       "Margin_change_estimate                 float64\n",
       "Type                                    object\n",
       "Batch Votes Winner                     float64\n",
       "Batch Votes Loser                      float64\n",
       "Accum. Votes Winner                    float64\n",
       "Accum. Votes Loser                     float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result_df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "secure-updating",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[0, 9, 23, 98, 171, 262, 326, 417, 421, 431, 445, 636, 640, 644, 701, 743, 803, 860, 906, 910, 1001, 1058, 1173, 1226, 1368, 1424, 1431, 1449, 1458, 1492, 1549, 1685, 1705, 1776, 1834, 1861, 1900, 1902, 1934, 2242, 2250, 2304, 2321, 2327, 2370, 2401, 2410, 2490, 2635, 2652, 2673]\n"
     ]
    }
   ],
   "source": [
    "first_rows = list(result_df.groupby('State').first()['index'])\n",
    "print(first_rows)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "approximate-calvin",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[8, 22, 97, 170, 261, 325, 416, 420, 430, 444, 635, 639, 643, 700, 742, 802, 859, 905, 909, 1000, 1057, 1172, 1225, 1367, 1423, 1430, 1448, 1457, 1491, 1548, 1684, 1704, 1775, 1833, 1860, 1899, 1901, 1933, 2241, 2249, 2303, 2320, 2326, 2369, 2400, 2409, 2489, 2634, 2651, 2672, 2675]\n"
     ]
    }
   ],
   "source": [
    "last_rows = list(result_df.groupby('State').last()['index'])\n",
    "print(last_rows)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "painted-moses",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "state_index = 0\n",
    "for i in range(len(result_df)-1):\n",
    "    if i in first_rows:\n",
    "        winner_final_votes = summary_df.iloc[state_index,3].astype(int)\n",
    "        loser_final_votes = summary_df.iloc[state_index, 6].astype(int)\n",
    "        \n",
    "        #Accum. Votes Winner\n",
    "        result_df.iloc[i,15] = winner_final_votes.astype(int)\n",
    "                \n",
    "        #Accum. Votes Loser\n",
    "        result_df.iloc[i,16] = loser_final_votes.astype(int)\n",
    "        \n",
    "        # Next state\n",
    "        state_index += 1\n",
    "        \n",
    "        # Calculate Batch Votes\n",
    "        change = int(result_df.iloc[i,5])\n",
    "        vote_margin_now = int(result_df.iloc[i,3])\n",
    "        vote_margin_prev = int(result_df.iloc[i+1,3])\n",
    "        \n",
    "        # Batch Votes Loser\n",
    "        bvl = ( change - vote_margin_now + vote_margin_prev )/2\n",
    "        result_df.iloc[i,14] = bvl\n",
    "        \n",
    "        # Batch Votes Winner\n",
    "        bvw = bvl + vote_margin_now - vote_margin_prev\n",
    "        result_df.iloc[i,13] = bvw\n",
    "        \n",
    "    elif i in last_rows:\n",
    "        change = int(result_df.iloc[i,5])\n",
    "        loser_batch_percent = result_df.iloc[i,8]/100\n",
    "        winner_batch_percent = result_df.iloc[i,7]/100\n",
    "        \n",
    "        # Batch Votes Loser\n",
    "        result_df.iloc[i,14] = round(result_df.iloc[i-1,16] - result_df.iloc[i-1,14],0)\n",
    "        \n",
    "        # Batch Votes Winner\n",
    "        result_df.iloc[i,13] = round(result_df.iloc[i-1,15] - result_df.iloc[i-1,13],0)\n",
    "\n",
    "        # Accum Winner\n",
    "        result_df.iloc[i,15] = round(result_df.iloc[i-1,15] - result_df.iloc[i-1,13],0)\n",
    "        \n",
    "        # Accum Loser\n",
    "        result_df.iloc[i,16] = round(result_df.iloc[i-1,16] - result_df.iloc[i-1,14],0)\n",
    "        \n",
    "    else:\n",
    "        change = int(result_df.iloc[i,5])\n",
    "        vote_margin_now = int(result_df.iloc[i,3])\n",
    "        vote_margin_prev = int(result_df.iloc[i+1,3])\n",
    "        \n",
    "        # Batch Votes Loser\n",
    "        bvl = ( change - vote_margin_now + vote_margin_prev )/2\n",
    "        result_df.iloc[i,14] = bvl\n",
    "        \n",
    "        # Batch Votes Winner\n",
    "        bvw = bvl + vote_margin_now - vote_margin_prev\n",
    "        result_df.iloc[i,13] = bvw\n",
    "\n",
    "        # Accum Winner\n",
    "        result_df.iloc[i,15] = result_df.iloc[i-1,15] - bvw\n",
    "        \n",
    "        # Accum Loser\n",
    "        result_df.iloc[i,16] = result_df.iloc[i-1,16] - bvl"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "computational-shareware",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>Timestamp</th>\n",
       "      <th>In The Lead</th>\n",
       "      <th>Vote Margin</th>\n",
       "      <th>Votes Remaining (est.)</th>\n",
       "      <th>Change</th>\n",
       "      <th>State</th>\n",
       "      <th>Leader Batch Breakdown</th>\n",
       "      <th>Trailer Batch Breakdown</th>\n",
       "      <th>Trailer Batch Trend</th>\n",
       "      <th>Trailer Hurdle</th>\n",
       "      <th>Margin_change_estimate</th>\n",
       "      <th>Type</th>\n",
       "      <th>Batch Votes Winner</th>\n",
       "      <th>Batch Votes Loser</th>\n",
       "      <th>Accum. Votes Winner</th>\n",
       "      <th>Accum. Votes Loser</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>2020-11-23 20:33:25+00:00</td>\n",
       "      <td>Trump</td>\n",
       "      <td>591546</td>\n",
       "      <td>4</td>\n",
       "      <td>2476.0</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>44.2</td>\n",
       "      <td>55.8</td>\n",
       "      <td>58.5</td>\n",
       "      <td>7499241.2</td>\n",
       "      <td>286.0</td>\n",
       "      <td></td>\n",
       "      <td>1095.0</td>\n",
       "      <td>1381.0</td>\n",
       "      <td>1441170.0</td>\n",
       "      <td>849624.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2020-11-23 20:11:23+00:00</td>\n",
       "      <td>Trump</td>\n",
       "      <td>591832</td>\n",
       "      <td>2521</td>\n",
       "      <td>5894.0</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>50.6</td>\n",
       "      <td>49.4</td>\n",
       "      <td>55.0</td>\n",
       "      <td>11954.5</td>\n",
       "      <td>72.0</td>\n",
       "      <td></td>\n",
       "      <td>2983.0</td>\n",
       "      <td>2911.0</td>\n",
       "      <td>1438187.0</td>\n",
       "      <td>846713.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>2020-11-23 19:48:16+00:00</td>\n",
       "      <td>Trump</td>\n",
       "      <td>591760</td>\n",
       "      <td>8534</td>\n",
       "      <td>4792.0</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>61.2</td>\n",
       "      <td>38.8</td>\n",
       "      <td>47.8</td>\n",
       "      <td>3566.2</td>\n",
       "      <td>1074.0</td>\n",
       "      <td></td>\n",
       "      <td>2933.0</td>\n",
       "      <td>1859.0</td>\n",
       "      <td>1435254.0</td>\n",
       "      <td>844854.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>2020-11-06 22:26:37+00:00</td>\n",
       "      <td>Trump</td>\n",
       "      <td>590686</td>\n",
       "      <td>13386</td>\n",
       "      <td>12548.0</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>28.6</td>\n",
       "      <td>71.4</td>\n",
       "      <td>44.9</td>\n",
       "      <td>2287.6</td>\n",
       "      <td>5360.0</td>\n",
       "      <td></td>\n",
       "      <td>3831.0</td>\n",
       "      <td>8717.0</td>\n",
       "      <td>1431423.0</td>\n",
       "      <td>836137.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>2020-11-04 21:12:19+00:00</td>\n",
       "      <td>Trump</td>\n",
       "      <td>595572</td>\n",
       "      <td>25662</td>\n",
       "      <td>10.0</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>30.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>49.4</td>\n",
       "      <td>1226.9</td>\n",
       "      <td>4.0</td>\n",
       "      <td></td>\n",
       "      <td>3.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>1431420.0</td>\n",
       "      <td>836130.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>2020-11-04 21:00:38+00:00</td>\n",
       "      <td>Trump</td>\n",
       "      <td>595576</td>\n",
       "      <td>25815</td>\n",
       "      <td>2938.0</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>59.2</td>\n",
       "      <td>40.8</td>\n",
       "      <td>49.5</td>\n",
       "      <td>1219.9</td>\n",
       "      <td>542.0</td>\n",
       "      <td></td>\n",
       "      <td>703.0</td>\n",
       "      <td>2235.0</td>\n",
       "      <td>1430717.0</td>\n",
       "      <td>833895.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "      <td>2020-11-04 20:23:38+00:00</td>\n",
       "      <td>Trump</td>\n",
       "      <td>597108</td>\n",
       "      <td>28830</td>\n",
       "      <td>13958.0</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>86.9</td>\n",
       "      <td>13.1</td>\n",
       "      <td>50.1</td>\n",
       "      <td>1100.3</td>\n",
       "      <td>10298.0</td>\n",
       "      <td></td>\n",
       "      <td>12128.0</td>\n",
       "      <td>1830.0</td>\n",
       "      <td>1418589.0</td>\n",
       "      <td>832065.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7</td>\n",
       "      <td>2020-11-04 17:16:47+00:00</td>\n",
       "      <td>Trump</td>\n",
       "      <td>586810</td>\n",
       "      <td>43805</td>\n",
       "      <td>48782.0</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>17.7</td>\n",
       "      <td>82.3</td>\n",
       "      <td>82.3</td>\n",
       "      <td>729.3</td>\n",
       "      <td>31534.0</td>\n",
       "      <td></td>\n",
       "      <td>8624.0</td>\n",
       "      <td>40158.0</td>\n",
       "      <td>1409965.0</td>\n",
       "      <td>791907.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>8</td>\n",
       "      <td>2020-11-04 13:28:22+00:00</td>\n",
       "      <td>Trump</td>\n",
       "      <td>618344</td>\n",
       "      <td>93031</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>387.0</td>\n",
       "      <td>582171.0</td>\n",
       "      <td></td>\n",
       "      <td>1401341.0</td>\n",
       "      <td>751749.0</td>\n",
       "      <td>1401341.0</td>\n",
       "      <td>751749.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   index                 Timestamp In The Lead  Vote Margin  \\\n",
       "0      0 2020-11-23 20:33:25+00:00       Trump       591546   \n",
       "1      1 2020-11-23 20:11:23+00:00       Trump       591832   \n",
       "2      2 2020-11-23 19:48:16+00:00       Trump       591760   \n",
       "3      3 2020-11-06 22:26:37+00:00       Trump       590686   \n",
       "4      4 2020-11-04 21:12:19+00:00       Trump       595572   \n",
       "5      5 2020-11-04 21:00:38+00:00       Trump       595576   \n",
       "6      6 2020-11-04 20:23:38+00:00       Trump       597108   \n",
       "7      7 2020-11-04 17:16:47+00:00       Trump       586810   \n",
       "8      8 2020-11-04 13:28:22+00:00       Trump       618344   \n",
       "\n",
       "  Votes Remaining (est.)   Change    State  Leader Batch Breakdown  \\\n",
       "0                      4   2476.0  Alabama                    44.2   \n",
       "1                   2521   5894.0  Alabama                    50.6   \n",
       "2                   8534   4792.0  Alabama                    61.2   \n",
       "3                  13386  12548.0  Alabama                    28.6   \n",
       "4                  25662     10.0  Alabama                    30.0   \n",
       "5                  25815   2938.0  Alabama                    59.2   \n",
       "6                  28830  13958.0  Alabama                    86.9   \n",
       "7                  43805  48782.0  Alabama                    17.7   \n",
       "8                  93031      0.0  Alabama                     NaN   \n",
       "\n",
       "   Trailer Batch Breakdown  Trailer Batch Trend  Trailer Hurdle  \\\n",
       "0                     55.8                 58.5       7499241.2   \n",
       "1                     49.4                 55.0         11954.5   \n",
       "2                     38.8                 47.8          3566.2   \n",
       "3                     71.4                 44.9          2287.6   \n",
       "4                     70.0                 49.4          1226.9   \n",
       "5                     40.8                 49.5          1219.9   \n",
       "6                     13.1                 50.1          1100.3   \n",
       "7                     82.3                 82.3           729.3   \n",
       "8                      NaN                  NaN           387.0   \n",
       "\n",
       "   Margin_change_estimate Type  Batch Votes Winner  Batch Votes Loser  \\\n",
       "0                   286.0                   1095.0             1381.0   \n",
       "1                    72.0                   2983.0             2911.0   \n",
       "2                  1074.0                   2933.0             1859.0   \n",
       "3                  5360.0                   3831.0             8717.0   \n",
       "4                     4.0                      3.0                7.0   \n",
       "5                   542.0                    703.0             2235.0   \n",
       "6                 10298.0                  12128.0             1830.0   \n",
       "7                 31534.0                   8624.0            40158.0   \n",
       "8                582171.0                1401341.0           751749.0   \n",
       "\n",
       "   Accum. Votes Winner  Accum. Votes Loser  \n",
       "0            1441170.0            849624.0  \n",
       "1            1438187.0            846713.0  \n",
       "2            1435254.0            844854.0  \n",
       "3            1431423.0            836137.0  \n",
       "4            1431420.0            836130.0  \n",
       "5            1430717.0            833895.0  \n",
       "6            1418589.0            832065.0  \n",
       "7            1409965.0            791907.0  \n",
       "8            1401341.0            751749.0  "
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result_df[result_df['State'] ==  'Alabama']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "excess-fifteen",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>Timestamp</th>\n",
       "      <th>In The Lead</th>\n",
       "      <th>Vote Margin</th>\n",
       "      <th>Votes Remaining (est.)</th>\n",
       "      <th>Change</th>\n",
       "      <th>State</th>\n",
       "      <th>Leader Batch Breakdown</th>\n",
       "      <th>Trailer Batch Breakdown</th>\n",
       "      <th>Trailer Batch Trend</th>\n",
       "      <th>Trailer Hurdle</th>\n",
       "      <th>Margin_change_estimate</th>\n",
       "      <th>Type</th>\n",
       "      <th>Batch Votes Winner</th>\n",
       "      <th>Batch Votes Loser</th>\n",
       "      <th>Accum. Votes Winner</th>\n",
       "      <th>Accum. Votes Loser</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2675</th>\n",
       "      <td>2675</td>\n",
       "      <td>2020-11-04 13:28:22+00:00</td>\n",
       "      <td>Trump</td>\n",
       "      <td>119839</td>\n",
       "      <td>422</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14765.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      index                 Timestamp In The Lead  Vote Margin  \\\n",
       "2675   2675 2020-11-04 13:28:22+00:00       Trump       119839   \n",
       "\n",
       "     Votes Remaining (est.)  Change    State  Leader Batch Breakdown  \\\n",
       "2675                    422     0.0  Wyoming                     NaN   \n",
       "\n",
       "      Trailer Batch Breakdown  Trailer Batch Trend  Trailer Hurdle  \\\n",
       "2675                      NaN                  NaN         14765.5   \n",
       "\n",
       "      Margin_change_estimate Type  Batch Votes Winner  Batch Votes Loser  \\\n",
       "2675                     NaN                      NaN                NaN   \n",
       "\n",
       "      Accum. Votes Winner  Accum. Votes Loser  \n",
       "2675                  NaN                 NaN  "
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result_df[result_df['Batch Votes Winner'].isna()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "attempted-payment",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Wyoming last row calculations\n",
    "result_df.loc[2675, 'Accum. Votes Winner'] = (193384 - 175)\n",
    "result_df.loc[2675, 'Accum. Votes Loser'] = (73486 - 5)\n",
    "result_df.loc[2675, 'Batch Votes Winner'] = (193384 - 175)\n",
    "result_df.loc[2675, 'Batch Votes Loser'] = (73486 - 5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "complete-psychiatry",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>Timestamp</th>\n",
       "      <th>In The Lead</th>\n",
       "      <th>Vote Margin</th>\n",
       "      <th>Votes Remaining (est.)</th>\n",
       "      <th>Change</th>\n",
       "      <th>State</th>\n",
       "      <th>Leader Batch Breakdown</th>\n",
       "      <th>Trailer Batch Breakdown</th>\n",
       "      <th>Trailer Batch Trend</th>\n",
       "      <th>Trailer Hurdle</th>\n",
       "      <th>Margin_change_estimate</th>\n",
       "      <th>Type</th>\n",
       "      <th>Batch Votes Winner</th>\n",
       "      <th>Batch Votes Loser</th>\n",
       "      <th>Accum. Votes Winner</th>\n",
       "      <th>Accum. Votes Loser</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2670</th>\n",
       "      <td>2670</td>\n",
       "      <td>2020-11-04 18:30:33+00:00</td>\n",
       "      <td>Biden</td>\n",
       "      <td>20509</td>\n",
       "      <td>294</td>\n",
       "      <td>35.0</td>\n",
       "      <td>Wisconsin</td>\n",
       "      <td>68.8</td>\n",
       "      <td>31.2</td>\n",
       "      <td>82.6</td>\n",
       "      <td>3598.9</td>\n",
       "      <td>24.0</td>\n",
       "      <td>updated</td>\n",
       "      <td>5.5</td>\n",
       "      <td>29.5</td>\n",
       "      <td>1610328.5</td>\n",
       "      <td>1589869.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2671</th>\n",
       "      <td>2671</td>\n",
       "      <td>2020-11-04 17:57:21+00:00</td>\n",
       "      <td>Biden</td>\n",
       "      <td>20533</td>\n",
       "      <td>229</td>\n",
       "      <td>352.0</td>\n",
       "      <td>Wisconsin</td>\n",
       "      <td>26.7</td>\n",
       "      <td>73.3</td>\n",
       "      <td>73.3</td>\n",
       "      <td>4611.5</td>\n",
       "      <td>164.0</td>\n",
       "      <td></td>\n",
       "      <td>94.0</td>\n",
       "      <td>258.0</td>\n",
       "      <td>1610234.5</td>\n",
       "      <td>1589611.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2672</th>\n",
       "      <td>2672</td>\n",
       "      <td>2020-11-04 13:28:22+00:00</td>\n",
       "      <td>Biden</td>\n",
       "      <td>20697</td>\n",
       "      <td>592</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Wisconsin</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1828.6</td>\n",
       "      <td>99371.0</td>\n",
       "      <td></td>\n",
       "      <td>1610140.0</td>\n",
       "      <td>1589354.0</td>\n",
       "      <td>1610140.0</td>\n",
       "      <td>1589354.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2673</th>\n",
       "      <td>2673</td>\n",
       "      <td>2020-11-11 20:39:54+00:00</td>\n",
       "      <td>Trump</td>\n",
       "      <td>120068</td>\n",
       "      <td>84</td>\n",
       "      <td>151.0</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>69.5</td>\n",
       "      <td>30.5</td>\n",
       "      <td>15.4</td>\n",
       "      <td>74119.0</td>\n",
       "      <td>59.0</td>\n",
       "      <td></td>\n",
       "      <td>105.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>193559.0</td>\n",
       "      <td>73491.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2674</th>\n",
       "      <td>2674</td>\n",
       "      <td>2020-11-06 02:50:08+00:00</td>\n",
       "      <td>Trump</td>\n",
       "      <td>120009</td>\n",
       "      <td>237</td>\n",
       "      <td>180.0</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>97.2</td>\n",
       "      <td>2.8</td>\n",
       "      <td>2.8</td>\n",
       "      <td>26289.4</td>\n",
       "      <td>170.0</td>\n",
       "      <td></td>\n",
       "      <td>175.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>193384.0</td>\n",
       "      <td>73486.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2675</th>\n",
       "      <td>2675</td>\n",
       "      <td>2020-11-04 13:28:22+00:00</td>\n",
       "      <td>Trump</td>\n",
       "      <td>119839</td>\n",
       "      <td>422</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14765.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>193209.0</td>\n",
       "      <td>73481.0</td>\n",
       "      <td>193209.0</td>\n",
       "      <td>73481.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      index                 Timestamp In The Lead  Vote Margin  \\\n",
       "2670   2670 2020-11-04 18:30:33+00:00       Biden        20509   \n",
       "2671   2671 2020-11-04 17:57:21+00:00       Biden        20533   \n",
       "2672   2672 2020-11-04 13:28:22+00:00       Biden        20697   \n",
       "2673   2673 2020-11-11 20:39:54+00:00       Trump       120068   \n",
       "2674   2674 2020-11-06 02:50:08+00:00       Trump       120009   \n",
       "2675   2675 2020-11-04 13:28:22+00:00       Trump       119839   \n",
       "\n",
       "     Votes Remaining (est.)  Change      State  Leader Batch Breakdown  \\\n",
       "2670                    294    35.0  Wisconsin                    68.8   \n",
       "2671                    229   352.0  Wisconsin                    26.7   \n",
       "2672                    592     0.0  Wisconsin                     NaN   \n",
       "2673                     84   151.0    Wyoming                    69.5   \n",
       "2674                    237   180.0    Wyoming                    97.2   \n",
       "2675                    422     0.0    Wyoming                     NaN   \n",
       "\n",
       "      Trailer Batch Breakdown  Trailer Batch Trend  Trailer Hurdle  \\\n",
       "2670                     31.2                 82.6          3598.9   \n",
       "2671                     73.3                 73.3          4611.5   \n",
       "2672                      NaN                  NaN          1828.6   \n",
       "2673                     30.5                 15.4         74119.0   \n",
       "2674                      2.8                  2.8         26289.4   \n",
       "2675                      NaN                  NaN         14765.5   \n",
       "\n",
       "      Margin_change_estimate     Type  Batch Votes Winner  Batch Votes Loser  \\\n",
       "2670                    24.0  updated                 5.5               29.5   \n",
       "2671                   164.0                         94.0              258.0   \n",
       "2672                 99371.0                    1610140.0          1589354.0   \n",
       "2673                    59.0                        105.0               46.0   \n",
       "2674                   170.0                        175.0                5.0   \n",
       "2675                     NaN                     193209.0            73481.0   \n",
       "\n",
       "      Accum. Votes Winner  Accum. Votes Loser  \n",
       "2670            1610328.5           1589869.5  \n",
       "2671            1610234.5           1589611.5  \n",
       "2672            1610140.0           1589354.0  \n",
       "2673             193559.0             73491.0  \n",
       "2674             193384.0             73486.0  \n",
       "2675             193209.0             73481.0  "
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result_df[2670: 2676]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "defined-cancer",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "result_df.to_csv('./data/cleaned_records.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "becoming-mapping",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2022-07-26 14:38:32.404854\n"
     ]
    }
   ],
   "source": [
    "time_end = pd.Timestamp.now()\n",
    "print(time_end)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "distinguished-insight",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Elapsed time: 0 days 00:00:25.212146\n"
     ]
    }
   ],
   "source": [
    "print(f'Elapsed time: {time_end - time_start}')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "asian-grade",
   "metadata": {},
   "source": [
    "## Format Data for Database"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "welsh-citizenship",
   "metadata": {},
   "source": [
    "Format of database  \n",
    "  * batch # (use index - continuous across all states)\n",
    "  * datetime\n",
    "  * candidate\n",
    "  * votes for candidate\n",
    "  * state\n",
    "  * district (empty for now)\n",
    "  * type ( :: accepted, rejected, calculated)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "attractive-stable",
   "metadata": {},
   "outputs": [],
   "source": [
    "batch_records = []\n",
    "dict = {}\n",
    "for i in range(len(result_df)):\n",
    "    datetime = result_df.iloc[i,1]\n",
    "    leader = result_df.iloc[i,2]\n",
    "    leader_votes = result_df.iloc[i,13]\n",
    "    state = result_df.iloc[i,6]\n",
    "    district = \"\"\n",
    "    type_desc = result_df.iloc[i,12]\n",
    "    \n",
    "    if (leader == 'Trump'):\n",
    "        runner_up = 'Biden'\n",
    "    else:\n",
    "        runner_up = 'Trump'\n",
    "    \n",
    "    runner_up_votes = result_df.iloc[i,14]\n",
    "    \n",
    "    dict = {\n",
    "        \"datetime\": datetime,\n",
    "        \"candidate\": leader,\n",
    "        \"votes\": int(leader_votes),\n",
    "        \"state\": state,\n",
    "        \"district\": district,\n",
    "        \"type\": type_desc\n",
    "    }\n",
    "    \n",
    "    batch_records.append(dict)\n",
    "    \n",
    "    dict = {\n",
    "        \"datetime\": datetime,\n",
    "        \"candidate\": runner_up,\n",
    "        \"votes\": int(runner_up_votes),\n",
    "        \"state\": state,\n",
    "        \"district\": district,\n",
    "        \"type\": type_desc\n",
    "    }\n",
    "    \n",
    "    batch_records.append(dict)\n",
    "    \n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "miniature-orientation",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>datetime</th>\n",
       "      <th>candidate</th>\n",
       "      <th>votes</th>\n",
       "      <th>state</th>\n",
       "      <th>district</th>\n",
       "      <th>type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-11-23 20:33:25+00:00</td>\n",
       "      <td>Trump</td>\n",
       "      <td>1095</td>\n",
       "      <td>Alabama</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-11-23 20:33:25+00:00</td>\n",
       "      <td>Biden</td>\n",
       "      <td>1381</td>\n",
       "      <td>Alabama</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-11-23 20:11:23+00:00</td>\n",
       "      <td>Trump</td>\n",
       "      <td>2983</td>\n",
       "      <td>Alabama</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-11-23 20:11:23+00:00</td>\n",
       "      <td>Biden</td>\n",
       "      <td>2911</td>\n",
       "      <td>Alabama</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-11-23 19:48:16+00:00</td>\n",
       "      <td>Trump</td>\n",
       "      <td>2933</td>\n",
       "      <td>Alabama</td>\n",
       "      <td></td>\n",
       "      <td></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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5347</th>\n",
       "      <td>2020-11-11 20:39:54+00:00</td>\n",
       "      <td>Biden</td>\n",
       "      <td>46</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5348</th>\n",
       "      <td>2020-11-06 02:50:08+00:00</td>\n",
       "      <td>Trump</td>\n",
       "      <td>175</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5349</th>\n",
       "      <td>2020-11-06 02:50:08+00:00</td>\n",
       "      <td>Biden</td>\n",
       "      <td>5</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5350</th>\n",
       "      <td>2020-11-04 13:28:22+00:00</td>\n",
       "      <td>Trump</td>\n",
       "      <td>193209</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5351</th>\n",
       "      <td>2020-11-04 13:28:22+00:00</td>\n",
       "      <td>Biden</td>\n",
       "      <td>73481</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5352 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                      datetime candidate   votes    state district type\n",
       "0    2020-11-23 20:33:25+00:00     Trump    1095  Alabama              \n",
       "1    2020-11-23 20:33:25+00:00     Biden    1381  Alabama              \n",
       "2    2020-11-23 20:11:23+00:00     Trump    2983  Alabama              \n",
       "3    2020-11-23 20:11:23+00:00     Biden    2911  Alabama              \n",
       "4    2020-11-23 19:48:16+00:00     Trump    2933  Alabama              \n",
       "...                        ...       ...     ...      ...      ...  ...\n",
       "5347 2020-11-11 20:39:54+00:00     Biden      46  Wyoming              \n",
       "5348 2020-11-06 02:50:08+00:00     Trump     175  Wyoming              \n",
       "5349 2020-11-06 02:50:08+00:00     Biden       5  Wyoming              \n",
       "5350 2020-11-04 13:28:22+00:00     Trump  193209  Wyoming              \n",
       "5351 2020-11-04 13:28:22+00:00     Biden   73481  Wyoming              \n",
       "\n",
       "[5352 rows x 6 columns]"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame(batch_records)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "insured-springer",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv('./data/db_file.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "excellent-phase",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "datetime     datetime64[ns, UTC]\n",
       "candidate                 object\n",
       "votes                      int64\n",
       "state                     object\n",
       "district                  object\n",
       "type                      object\n",
       "dtype: object"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "id": "national-ceramic",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>votes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>5.352000e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>2.892836e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>2.599932e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>-1.018900e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>5.600000e+01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>4.750000e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>2.876750e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>7.721336e+06</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              votes\n",
       "count  5.352000e+03\n",
       "mean   2.892836e+04\n",
       "std    2.599932e+05\n",
       "min   -1.018900e+04\n",
       "25%    5.600000e+01\n",
       "50%    4.750000e+02\n",
       "75%    2.876750e+03\n",
       "max    7.721336e+06"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "id": "brutal-necklace",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       " 1        113\n",
       " 2         91\n",
       " 5         74\n",
       " 4         69\n",
       " 3         69\n",
       "         ... \n",
       " 19235      1\n",
       " 802        1\n",
       " 11039      1\n",
       " 798        1\n",
       "-2          1\n",
       "Name: votes, Length: 2693, dtype: int64"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['votes'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "id": "hearing-tunnel",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 5352 entries, 0 to 5351\n",
      "Data columns (total 6 columns):\n",
      " #   Column     Non-Null Count  Dtype              \n",
      "---  ------     --------------  -----              \n",
      " 0   datetime   5352 non-null   datetime64[ns, UTC]\n",
      " 1   candidate  5352 non-null   object             \n",
      " 2   votes      5352 non-null   int64              \n",
      " 3   state      5352 non-null   object             \n",
      " 4   district   5352 non-null   object             \n",
      " 5   type       5352 non-null   object             \n",
      "dtypes: datetime64[ns, UTC](1), int64(1), object(4)\n",
      "memory usage: 251.0+ KB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "id": "governing-living",
   "metadata": {
    "tags": []
   },
   "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>datetime</th>\n",
       "      <th>candidate</th>\n",
       "      <th>votes</th>\n",
       "      <th>state</th>\n",
       "      <th>district</th>\n",
       "      <th>type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [datetime, candidate, votes, state, district, type]\n",
       "Index: []"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['votes'].isna()]\n",
    "# looks like 2 nan values out of 5352 records\n",
    "# these are the first record for each state and the district of columbia that don't have a calculated candidate votes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "31afdb46-5dee-4052-a79b-f54b4c89b405",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "98ccfebe-d258-4fe6-8869-7b1afd850082",
   "metadata": {
    "tags": []
   },
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "id": "b5ef864c-af42-4ac5-bca1-6ef1804f3909",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Timestamp</th>\n",
       "      <th>In The Lead</th>\n",
       "      <th>Vote Margin</th>\n",
       "      <th>Votes Remaining (est.)</th>\n",
       "      <th>Change</th>\n",
       "      <th>State</th>\n",
       "      <th>Leader Batch Breakdown</th>\n",
       "      <th>Trailer Batch Breakdown</th>\n",
       "      <th>Trailer Batch Trend</th>\n",
       "      <th>Trailer Hurdle</th>\n",
       "      <th>Margin_change_estimate</th>\n",
       "      <th>Type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2020-11-11 03:23:42 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>590686</td>\n",
       "      <td>13386</td>\n",
       "      <td>0</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>44.9</td>\n",
       "      <td>2287.6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>removed-zero-batch</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2020-12-02 19:04:28 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>36173</td>\n",
       "      <td>31816</td>\n",
       "      <td>0</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>49.4</td>\n",
       "      <td>109.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>removed-zero-batch</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2020-12-02 17:18:21 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>36173</td>\n",
       "      <td>31816</td>\n",
       "      <td>0</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>49.4</td>\n",
       "      <td>109.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>removed-zero-batch</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2020-12-02 16:33:22 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>36173</td>\n",
       "      <td>31816</td>\n",
       "      <td>0</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>49.4</td>\n",
       "      <td>109.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>removed-zero-batch</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2020-12-01 18:45:19 UTC</td>\n",
       "      <td>Trump</td>\n",
       "      <td>36494</td>\n",
       "      <td>34120</td>\n",
       "      <td>0</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>48.8</td>\n",
       "      <td>105.9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>removed-zero-batch</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  Timestamp In The Lead  Vote Margin Votes Remaining (est.)  \\\n",
       "5   2020-11-11 03:23:42 UTC       Trump       590686                  13386   \n",
       "14  2020-12-02 19:04:28 UTC       Trump        36173                  31816   \n",
       "15  2020-12-02 17:18:21 UTC       Trump        36173                  31816   \n",
       "16  2020-12-02 16:33:22 UTC       Trump        36173                  31816   \n",
       "18  2020-12-01 18:45:19 UTC       Trump        36494                  34120   \n",
       "\n",
       "   Change    State  Leader Batch Breakdown  Trailer Batch Breakdown  \\\n",
       "5       0  Alabama                     NaN                      NaN   \n",
       "14      0   Alaska                     NaN                      NaN   \n",
       "15      0   Alaska                     NaN                      NaN   \n",
       "16      0   Alaska                     NaN                      NaN   \n",
       "18      0   Alaska                     NaN                      NaN   \n",
       "\n",
       "   Trailer Batch Trend Trailer Hurdle  Margin_change_estimate  \\\n",
       "5                 44.9         2287.6                     NaN   \n",
       "14                49.4          109.5                     NaN   \n",
       "15                49.4          109.5                     NaN   \n",
       "16                49.4          109.5                     NaN   \n",
       "18                48.8          105.9                     NaN   \n",
       "\n",
       "                  Type  \n",
       "5   removed-zero-batch  \n",
       "14  removed-zero-batch  \n",
       "15  removed-zero-batch  \n",
       "16  removed-zero-batch  \n",
       "18  removed-zero-batch  "
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dropped_rows_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6ab30a94-4dbc-49e2-9ac6-991b5eb3a5f0",
   "metadata": {},
   "source": [
    "# Future Work"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "48716392-277d-4314-af08-0b13af539261",
   "metadata": {},
   "source": [
    "## Analyze Trends\n",
    "### Metrics\n",
    "* Need table of each column and how many valid rows, invalid rows, and calculated rows\n",
    "* With data analysis -what is the difference between dropping all 'Unknown' and '0' values for the \"Change\" column and removal of all percentages outside 0 to 100.\n",
    "* Need to show the effect of calculating the Unknown columns versus what the effect would be by dropping it.\n",
    "* Need to show the residual of vote percents (vote_pred - vote_recorded) and vote margins.\n",
    "* Need to show outliers by plotting the change in margin versus the change in candidate votes - are there outliers - what did this look like before cleaning"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ed416747-d2b9-450d-a00e-57167855553f",
   "metadata": {},
   "source": [
    "## Check margin estimate for correctness (as indicated above)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cc7db833-94e6-4491-802a-ac88a56c0ee0",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [conda env:PythonData]",
   "language": "python",
   "name": "conda-env-PythonData-py"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}