ks2020.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pandas import read_excel\n",
    "import pandas as pd\n",
    "\n",
    "courtofapp_sheet = 'COURT OF APPEALS' \n",
    "stateHouse_sheet = 'KANSAS HOUSE OF REPRESENTATIVES'\n",
    "stateSenate_sheet = 'OfficialPrecinctLevelResults'\n",
    "president_sheet = 'PRESIDENT'\n",
    "supcourt_sheet = 'SUPREME COURT'\n",
    "ushouse_sheet = 'OfficialPrecinctLevelResults'\n",
    "ussenate_sheet = 'U.S. Senate'\n",
    "\n",
    "courtappeals_file_name = 'raw/2020_General_Election_Court_of_Appeals_results_by_precinct.xlsx' \n",
    "statehouse_file_name = 'raw/2020_General_Election_Kansas_House_of_Representatives_results_by_precinct.xlsx'\n",
    "statesenate_file_name = 'raw/2020_General_Election_Kansas_Senate_results_by_precinct.xlsx' \n",
    "president_file_name = 'raw/2020_General_Election_President_results_by_precinct.xlsx' \n",
    "supremecourt_file_name = 'raw/2020_General_Election_Supreme_Court_Justice_results_by_precinct.xlsx'\n",
    "ushouse_file_name = 'raw/2020_General_Election_US_House_of_Representatives_results_by_precinct.xlsx' \n",
    "ussenate_file_name = 'raw/2020_General_Election_US_Senate_results_by_precinct.xlsx' \n",
    "\n",
    "appeals = read_excel(courtappeals_file_name, sheet_name = courtofapp_sheet)\n",
    "ksHouse = read_excel(statehouse_file_name, sheet_name = stateHouse_sheet)\n",
    "ksSen = read_excel(statesenate_file_name, sheet_name = stateSenate_sheet)\n",
    "pres = read_excel(president_file_name, sheet_name = president_sheet)\n",
    "supreme = read_excel(supremecourt_file_name, sheet_name = supcourt_sheet)\n",
    "usHouse = read_excel(ushouse_file_name, sheet_name = ushouse_sheet)\n",
    "usSen = read_excel(ussenate_file_name, sheet_name = ussenate_sheet)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "frames = [appeals, ksHouse, ksSen, pres, supreme, usHouse, usSen]\n",
    "\n",
    "df = pd.concat(frames)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "\n",
    "df = df.rename(columns={'County': 'county', 'Precinct': 'precinct', 'Race': 'race',\n",
    "                            'Candidate': 'candidate', 'Party': 'party', 'Votes': 'votes'}) \n",
    "\n",
    "df = df.drop(['VTD'], axis=1)\n",
    "\n",
    "df = df.replace(np.nan, '', regex = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>county</th>\n",
       "      <th>precinct</th>\n",
       "      <th>race</th>\n",
       "      <th>candidate</th>\n",
       "      <th>party</th>\n",
       "      <th>votes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Allen</td>\n",
       "      <td>Carlyle Township</td>\n",
       "      <td>Court of Appeals Judge 4</td>\n",
       "      <td>Warner, Sarah E. - \"YES\"</td>\n",
       "      <td></td>\n",
       "      <td>114</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Allen</td>\n",
       "      <td>Carlyle Township</td>\n",
       "      <td>Court of Appeals Judge 4</td>\n",
       "      <td>Warner, Sarah E. - \"NO\"</td>\n",
       "      <td></td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Allen</td>\n",
       "      <td>Carlyle Township</td>\n",
       "      <td>Court of Appeals Judge 6</td>\n",
       "      <td>Bruns, David E. - \"YES\"</td>\n",
       "      <td></td>\n",
       "      <td>111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Allen</td>\n",
       "      <td>Carlyle Township</td>\n",
       "      <td>Court of Appeals Judge 6</td>\n",
       "      <td>Bruns, David E. - \"NO\"</td>\n",
       "      <td></td>\n",
       "      <td>44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Allen</td>\n",
       "      <td>Carlyle Township</td>\n",
       "      <td>Court of Appeals Judge 8</td>\n",
       "      <td>Atcheson, G. Gordon - \"YES\"</td>\n",
       "      <td></td>\n",
       "      <td>101</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>8260</th>\n",
       "      <td>Woodson</td>\n",
       "      <td>Yates Center Ward 2</td>\n",
       "      <td>United States Senate</td>\n",
       "      <td>Marshall, Roger</td>\n",
       "      <td>Republican</td>\n",
       "      <td>205</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8261</th>\n",
       "      <td>Woodson</td>\n",
       "      <td>Yates Center Ward 2</td>\n",
       "      <td>United States Senate</td>\n",
       "      <td>Buckley, Jason</td>\n",
       "      <td>Libertarian</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8262</th>\n",
       "      <td>Woodson</td>\n",
       "      <td>Yates Center Ward 2 Exclave</td>\n",
       "      <td>United States Senate</td>\n",
       "      <td>Bollier, Barbara</td>\n",
       "      <td>Democratic</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8263</th>\n",
       "      <td>Woodson</td>\n",
       "      <td>Yates Center Ward 2 Exclave</td>\n",
       "      <td>United States Senate</td>\n",
       "      <td>Marshall, Roger</td>\n",
       "      <td>Republican</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8264</th>\n",
       "      <td>Woodson</td>\n",
       "      <td>Yates Center Ward 2 Exclave</td>\n",
       "      <td>United States Senate</td>\n",
       "      <td>Buckley, Jason</td>\n",
       "      <td>Libertarian</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>63884 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       county                     precinct                      race  \\\n",
       "0       Allen             Carlyle Township  Court of Appeals Judge 4   \n",
       "1       Allen             Carlyle Township  Court of Appeals Judge 4   \n",
       "2       Allen             Carlyle Township  Court of Appeals Judge 6   \n",
       "3       Allen             Carlyle Township  Court of Appeals Judge 6   \n",
       "4       Allen             Carlyle Township  Court of Appeals Judge 8   \n",
       "...       ...                          ...                       ...   \n",
       "8260  Woodson          Yates Center Ward 2      United States Senate   \n",
       "8261  Woodson          Yates Center Ward 2      United States Senate   \n",
       "8262  Woodson  Yates Center Ward 2 Exclave      United States Senate   \n",
       "8263  Woodson  Yates Center Ward 2 Exclave      United States Senate   \n",
       "8264  Woodson  Yates Center Ward 2 Exclave      United States Senate   \n",
       "\n",
       "                        candidate        party votes  \n",
       "0        Warner, Sarah E. - \"YES\"                114  \n",
       "1         Warner, Sarah E. - \"NO\"                 45  \n",
       "2         Bruns, David E. - \"YES\"                111  \n",
       "3          Bruns, David E. - \"NO\"                 44  \n",
       "4     Atcheson, G. Gordon - \"YES\"                101  \n",
       "...                           ...          ...   ...  \n",
       "8260              Marshall, Roger   Republican   205  \n",
       "8261               Buckley, Jason  Libertarian    18  \n",
       "8262             Bollier, Barbara   Democratic     0  \n",
       "8263              Marshall, Roger   Republican     0  \n",
       "8264               Buckley, Jason  Libertarian     0  \n",
       "\n",
       "[63884 rows x 6 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "path = 'raw'\n",
    "os.chdir(path)\n",
    "\n",
    "all_xlxs = [file_name for file_name in os.listdir(os.getcwd()) if '.xlsx' in file_name]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['2020_General_Election_Court_of_Appeals_results_by_precinct.xlsx',\n",
       " '2020_General_Election_Kansas_House_of_Representatives_results_by_precinct.xlsx',\n",
       " '2020_General_Election_Kansas_Senate_results_by_precinct.xlsx',\n",
       " '2020_General_Election_President_results_by_precinct.xlsx',\n",
       " '2020_General_Election_Supreme_Court_Justice_results_by_precinct.xlsx',\n",
       " '2020_General_Election_US_House_of_Representatives_results_by_precinct.xlsx',\n",
       " '2020_General_Election_US_Senate_results_by_precinct.xlsx',\n",
       " '~$2020_General_Election_Kansas_Senate_results_by_precinct.xlsx',\n",
       " '~$2020_General_Election_President_results_by_precinct.xlsx',\n",
       " '._2020_General_Election_Kansas_House_of_Representatives_results_by_precinct.xlsx',\n",
       " '._~$2020_General_Election_President_results_by_precinct.xlsx',\n",
       " '._2020_General_Election_President_results_by_precinct.xlsx',\n",
       " '._2020_General_Election_US_House_of_Representatives_results_by_precinct.xlsx',\n",
       " '._2020_General_Election_US_Senate_results_by_precinct.xlsx']"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_xlxs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# items to be removed\n",
    "unwanted = {'~$2020_General_Election_Kansas_Senate_results_by_precinct.xlsx',\n",
    " '~$2020_General_Election_President_results_by_precinct.xlsx',\n",
    " '._2020_General_Election_Kansas_House_of_Representatives_results_by_precinct.xlsx',\n",
    " '._~$2020_General_Election_President_results_by_precinct.xlsx',\n",
    " '._2020_General_Election_President_results_by_precinct.xlsx',\n",
    " '._2020_General_Election_US_House_of_Representatives_results_by_precinct.xlsx',\n",
    " '._2020_General_Election_US_Senate_results_by_precinct.xlsx'}\n",
    " \n",
    "all_xlxs = [ele for ele in all_xlxs if ele not in unwanted]\n",
    " "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "john_sheet = 'JOHNSON' \n",
    "shawnee_sheet = 'SHAWNEE'\n",
    "li1 = []\n",
    "li2 = []\n",
    "\n",
    "for filename in all_xlxs:\n",
    "    johnData = pd.read_excel(filename, sheet_name=john_sheet)\n",
    "    shawneeData = pd.read_excel(filename, sheet_name=shawnee_sheet)\n",
    "    li1.append(johnData)\n",
    "    li2.append(shawneeData)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_john = pd.concat(li1, axis=0, ignore_index=True)\n",
    "\n",
    "df_john = df_john.replace(np.nan, '', regex = True)\n",
    "\n",
    "df_john = df_john.drop([516, 517, 518, 1033, 1034, 1035, 1550, 1551, 1552, 2067, 2068, 2069, 2584, 2585, 2586, 3101, 3102, 3103, 3618])\n",
    "\n",
    "df_john = df_john.drop(['PRECINCT CODE'], axis=1)\n",
    "\n",
    "# Rename Precinct name and US Senate Columns \n",
    "df_john = df_john.rename(columns={'PRECINCT NAME': 'precinct', 'US Senate': 'US SEN DEM Barbara Bollier', 'US Senate.1': 'US SEN LIB Jason Buckley',\n",
    "                            'US Senate.2': 'US SEN REP Roger Marshall'}) \n",
    "\n",
    "# Rename Court of Appeals Columns \n",
    "df_john = df_john.rename(columns={'KS Court of Appeals 4': 'Judge 4: Sarah E Warner - YES',\n",
    "                            'KS Court of Appeals 4.1': 'Judge 4: Sarah E Warner - NO',\n",
    "                            'KS Court of Appeals 4.2': 'Judge 4: Sarah E Warner - OVER VOTES',\n",
    "                            'KS Court of Appeals 4.3': 'Judge 4: Sarah E Warner - UNDER VOTES',\n",
    "                            'KS Court of Appeals 6': 'Judge 6: David E Bruns - YES',\n",
    "                            'KS Court of Appeals 6.1': 'Judge 6: David E Bruns - NO',\n",
    "                            'KS Court of Appeals 6.2': 'Judge 6: David E Bruns - OVER VOTES',\n",
    "                            'KS Court of Appeals 6.3': 'Judge 6: David E Bruns - UNDER VOTES',\n",
    "                            'KS Court of Appeals 8': 'Judge 8: Gordon G Atcheson - YES',\n",
    "                            'KS Court of Appeals 8.1': 'Judge 8: Gordon G Atcheson - NO',\n",
    "                            'KS Court of Appeals 8.2': 'Judge 8: Gordon G Atcheson - OVER VOTES',\n",
    "                            'KS Court of Appeals 8.3': 'Judge 8: Gordon G Atcheson - UNDER VOTES',\n",
    "                            'KS Court of Appeals 9': 'Judge 9: Karen M Arnold-Burger - YES',\n",
    "                            'KS Court of Appeals 9.1': 'Judge 9: Karen M Arnold-Burger - NO',\n",
    "                            'KS Court of Appeals 9.2': 'Judge 9: Karen M Arnold-Burger - OVER VOTES',\n",
    "                            'KS Court of Appeals 9.3': 'Judge 9: Karen M Arnold-Burger - UNDER VOTES',\n",
    "                            'KS Court of Appeals 14': 'Judge 14: Kathryn Gardner - YES',\n",
    "                            'KS Court of Appeals 14.1': 'Judge 14: Kathryn Gardner - NO',\n",
    "                            'KS Court of Appeals 14.2': 'Judge 14: Kathryn Gardner - OVER VOTES',\n",
    "                            'KS Court of Appeals 14.3': 'Judge 14: Kathryn Gardner - UNDER VOTES',\n",
    "                           }) \n",
    "\n",
    "# Rename State Senate Columns \n",
    "df_john = df_john.rename(columns={'State Senate 6': 'STATE SEN 6 DEM Pat Pettey', 'State Senate 6.1': 'STATE SEN 6 REP Diana Whittington',\n",
    "                            'State Senate 6.2': 'STATE SEN 6 WRITEIN', 'State Senate 6.3': 'STATE SEN 6 OVER VOTES', 'State Senate 6.4': 'STATE SEN 6 UNDER VOTES',\n",
    "                            'State Senate 7': 'STATE SEN 7 DEM Ethan Corson', 'State Senate 7.1': 'STATE SEN 7 REP Laura McConwell',\n",
    "                            'State Senate 7.2': 'STATE SEN 7 WRITEIN', 'State Senate 7.3': 'STATE SEN 7 OVER VOTES', 'State Senate 7.4': 'STATE SEN 7 UNDER VOTES',\n",
    "                            'State Senate 8': 'STATE SEN 8 DEM Cindy Holscher', 'State Senate 8.1': 'STATE SEN 8 REP James Todd',\n",
    "                            'State Senate 8.2': 'STATE SEN 8 WRITEIN', 'State Senate 8.3': 'STATE SEN 8 OVER VOTES', 'State Senate 8.4': 'STATE SEN 8 UNDER VOTES',\n",
    "                            'State Senate 9': 'STATE SEN 9 REP Beverly Gossage', 'State Senate 9.1': 'STATE SEN 9 DEM Stacey Knoell',\n",
    "                            'State Senate 9.2': 'STATE SEN 9 WRITEIN', 'State Senate 9.3': 'STATE SEN 9 OVER VOTES', 'State Senate 9.4': 'STATE SEN 9 UNDER VOTES',\n",
    "                            'State Senate 10': 'STATE SEN 10 DEM Lindsey Constance', 'State Senate 10.1': 'STATE SEN 10 REP Mike Thompson',\n",
    "                            'State Senate 10.2': 'STATE SEN 10 WRITEIN', 'State Senate 10.3': 'STATE SEN 10 OVER VOTES', 'State Senate 10.4': 'STATE SEN 10 UNDER VOTES',\n",
    "                            'State Senate 11': 'STATE SEN 11 DEM Joy Koesten', 'State Senate 11.1': 'STATE SEN 11 REP Kellie Warren',\n",
    "                            'State Senate 11.2': 'STATE SEN 11 WRITEIN', 'State Senate 11.3': 'STATE SEN 11 OVER VOTES', 'State Senate 11.4': 'STATE SEN 11 UNDER VOTES',\n",
    "                            'State Senate 21': 'STATE SEN 21 REP Tom Bickimer', 'State Senate 21.1': 'STATE SEN 21 DEM Dinah Sykes',\n",
    "                            'State Senate 21.2': 'STATE SEN 21 WRITEIN', 'State Senate 21.3': 'STATE SEN 21 OVER VOTES', 'State Senate 21.4': 'STATE SEN 21 UNDER VOTES',\n",
    "                            'State Senate 23': 'STATE SEN 23 DEM Wendy Budetti', 'State Senate 23.1': 'STATE SEN 23 REP Rob Olson',\n",
    "                            'State Senate 23.2': 'STATE SEN 23 WRITEIN', 'State Senate 23.3': 'STATE SEN 23 OVER VOTES', 'State Senate 23.4': 'STATE SEN 23 UNDER VOTES',\n",
    "                            'State Senate 37': 'STATE SEN 37 REP Molly Baumgardner', 'State Senate 37.1': 'STATE SEN 37 DEM Becca Peck',\n",
    "                            'State Senate 37.2': 'STATE SEN 37 WRITEIN', 'State Senate 37.3': 'STATE SEN 37 OVER VOTES', 'State Senate 37.4': 'STATE SEN 37 UNDER VOTES'\n",
    "                           }) \n",
    "\n",
    "# Rename Supreme Court Justice &  US Rep & President Columns  \n",
    "df_john = df_john.rename(columns={'KS Supreme Court Justice 4': 'Eric S Rosen - YES', 'KS Supreme Court Justice 4.1': 'Eric S Rosen - NO',\n",
    "                            'US Rep 3': 'US HOUSE 3 REP Amanda L Adkins', 'US Rep 3.1': 'US HOUSE 3 DEM Sharice L Davids', 'US Rep 3.2': 'US HOUSE 3 LIB Steven A Hohe', 'US Rep 3.3': 'US HOUSE 3 WRITEIN',\n",
    "                            'President / Vice-President': 'PRES DEM Biden and Harris',\n",
    "                            'President / Vice-President.1': 'PRES LIB Jorgensen and Cohen', \n",
    "                           'President / Vice-President.2': 'PRES REP Trump and Pence'})\n",
    "                            \n",
    "df_john = df_john.rename(columns={'State Representative 8': 'STATE HOUSE 8 REP Chris Croft', 'State Representative 8.1': 'STATE HOUSE 8 WRITEIN', 'State Representative 8.2': 'STATE HOUSE 8 OVER VOTES', 'State Representative 8.3': 'STATE HOUSE 8 UNDER VOTES',\n",
    "                           'State Representative 14': 'STATE HOUSE 14 REP Charlotte Esau', 'State Representative 14.1': 'STATE HOUSE 14 DEM Angela Justus Schweller', 'State Representative 14.2': 'STATE HOUSE 14 WRITEIN', 'State Representative 14.3': 'STATE HOUSE 14 OVER VOTES', 'State Representative 14.4': 'STATE HOUSE 14 UNDER VOTES',\n",
    "                           'State Representative 15': 'STATE HOUSE 15 DEM Cole Fine', 'State Representative 15.1': 'STATE HOUSE 15 REP John Toplikar', 'State Representative 15.2': 'STATE HOUSE 15 WRITEIN', 'State Representative 15.3': 'STATE HOUSE 15 OVER VOTES', 'State Representative 15.4': 'STATE HOUSE 15 UNDER VOTES',\n",
    "                           'State Representative 16': 'STATE HOUSE 16 DEM Linda Featherston', 'State Representative 16.1': 'STATE HOUSE 16 REP Rashard Young', 'State Representative 16.2': 'STATE HOUSE 16 WRITEIN', 'State Representative 16.3': 'STATE HOUSE 16 OVER VOTES', 'State Representative 16.4': 'STATE HOUSE 16 UNDER VOTES',\n",
    "                           'State Representative 17': 'STATE HOUSE 17 DEM Jo Ella Hoye', 'State Representative 17.1': 'STATE HOUSE 17 LIB Michael Kerner', 'State Representative 17.2': 'STATE HOUSE 17 REP Kristine Sapp', 'State Representative 17.3': 'STATE HOUSE 17 WRITEIN', 'State Representative 17.4': 'STATE HOUSE 17 OVER VOTES', 'State Representative 17.5': 'STATE HOUSE 17 UNDER VOTES',\n",
    "                            'State Representative 18': 'STATE HOUSE 18 REP Cathy Gordon', 'State Representative 18.1': 'STATE HOUSE 18 DEM Cindy Neighbor', 'State Representative 18.2': 'STATE HOUSE 18 WRITEIN', 'State Representative 18.3': 'STATE HOUSE 18 OVER VOTES', 'State Representative 18.4': 'STATE HOUSE 18 UNDER VOTES',\n",
    "                            'State Representative 19': 'STATE HOUSE 19 DEM Stephanie Sawyer Clayton', 'State Representative 19.1': 'STATE HOUSE 19 WRITEIN', 'State Representative 19.2': 'STATE HOUSE 19 OVER VOTES', 'State Representative 19.3': 'STATE HOUSE 19 UNDER VOTES',\n",
    "                            'State Representative 20': 'STATE HOUSE 20 REP Jane Dirks', 'State Representative 20.1': 'STATE HOUSE 20 DEM Mari-Lynn Poskin', 'State Representative 20.2': 'STATE HOUSE 20 WRITEIN', 'State Representative 20.3': 'STATE HOUSE 20 OVER VOTES', 'State Representative 20.4': 'STATE HOUSE 20 UNDER VOTES',\n",
    "                            'State Representative 21': 'STATE HOUSE 21 REP Bob Reese', 'State Representative 21.1': 'STATE HOUSE 21 DEM Jerry Stogsdill', 'State Representative 21.2': 'STATE HOUSE 21 WRITEIN', 'State Representative 21.3': 'STATE HOUSE 21 OVER VOTES', 'State Representative 21.4': 'STATE HOUSE 21 UNDER VOTES',\n",
    "                            'State Representative 22': 'STATE HOUSE 22 DEM Lindsay Vaughn', 'State Representative 22.1': 'STATE HOUSE 22 WRITEIN', 'State Representative 22.2': 'STATE HOUSE 22 OVER VOTES', 'State Representative 22.3': 'STATE HOUSE 22 UNDER VOTES',\n",
    "                            'State Representative 23': 'STATE HOUSE 23 LIB Matthew Clark', 'State Representative 23.1': 'STATE HOUSE 23 DEM Susan Ruiz', 'State Representative 23.2': 'STATE HOUSE 23 REP Jeff Shull', 'State Representative 23.3': 'STATE HOUSE 23 WRITEIN', 'State Representative 23.4': 'STATE HOUSE 23 OVER VOTES', 'State Representative 23.5': 'STATE HOUSE 23 UNDER VOTES',\n",
    "                            'State Representative 24': 'STATE HOUSE 24 DEM Jarrod Ousley', 'State Representative 24.1': 'STATE HOUSE 24 WRITEIN', 'State Representative 24.2': 'STATE HOUSE 24 OVER VOTES', 'State Representative 24.3': 'STATE HOUSE 24 UNDER VOTES',\n",
    "                            'State Representative 25': 'STATE HOUSE 25 DEM Rui Xu', 'State Representative 25.1': 'STATE HOUSE 25 WRITEIN', 'State Representative 25.2': 'STATE HOUSE 25 OVER VOTES', 'State Representative 25.3': 'STATE HOUSE 25 UNDER VOTES',\n",
    "                            'State Representative 26': 'STATE HOUSE 26 DEM W Michael Shimeall', 'State Representative 26.1': 'STATE HOUSE 26 REP Adam T Thomas', 'State Representative 26.2': 'STATE HOUSE 26 WRITEIN', 'State Representative 26.3': 'STATE HOUSE 26 OVER VOTES', 'State Representative 26.4': 'STATE HOUSE 26 UNDER VOTES',\n",
    "                            'State Representative 27': 'STATE HOUSE 27 DEM Sona Patel', 'State Representative 27.1': 'STATE HOUSE 27 REP Sean E Tarwater Sr', 'State Representative 27.2': 'STATE HOUSE 27 WRITEIN', 'State Representative 27.3': 'STATE HOUSE 27 OVER VOTES', 'State Representative 27.4': 'STATE HOUSE 27 UNDER VOTES',\n",
    "                            'State Representative 28': 'STATE HOUSE 28 DEM Sally Jercha', 'State Representative 28.1': 'STATE HOUSE 28 REP Carl Turner', 'State Representative 28.2': 'STATE HOUSE 28 WRITEIN', 'State Representative 28.3': 'STATE HOUSE 28 OVER VOTES', 'State Representative 28.4': 'STATE HOUSE 28 UNDER VOTES',\n",
    "                            'State Representative 29': 'STATE HOUSE 29 REP Jerry Clinton', 'State Representative 29.1': 'STATE HOUSE 29 DEM Brett Parker', 'State Representative 29.2': 'STATE HOUSE 29 WRITEIN', 'State Representative 29.3': 'STATE HOUSE 29 OVER VOTES', 'State Representative 29.4': 'STATE HOUSE 29 UNDER VOTES',\n",
    "                            'State Representative 30': 'STATE HOUSE 30 REP Laura Williams', 'State Representative 30.1': 'STATE HOUSE 30 DEM Brandon Woodard', 'State Representative 30.2': 'STATE HOUSE 30 WRITEIN', 'State Representative 30.3': 'STATE HOUSE 30 OVER VOTES', 'State Representative 30.4': 'STATE HOUSE 30 UNDER VOTES',\n",
    "                            'State Representative 38': 'STATE HOUSE 38 DEM Sherri D Grogan', 'State Representative 38.1': 'STATE HOUSE 38 REP Timothy H Johnson', 'State Representative 38.2': 'STATE HOUSE 38 WRITEIN', 'State Representative 38.3': 'STATE HOUSE 38 OVER VOTES', 'State Representative 38.4': 'STATE HOUSE 38 UNDER VOTES',\n",
    "                            'State Representative 39': 'STATE HOUSE 39 REP Owen Donohoe', 'State Representative 39.1': 'STATE HOUSE 39 DEM Les Lampe', 'State Representative 39.2': 'STATE HOUSE 39 WRITEIN', 'State Representative 39.3': 'STATE HOUSE 39 OVER VOTES', 'State Representative 39.4': 'STATE HOUSE 39 UNDER VOTES',\n",
    "                            'State Representative 43': 'STATE HOUSE 43 DEM Pamela Finley', 'State Representative 43.1': 'STATE HOUSE 43 REP Bill Sutton', 'State Representative 43.2': 'STATE HOUSE 43 WRITEIN', 'State Representative 43.3': 'STATE HOUSE 43 OVER VOTES', 'State Representative 43.4': 'STATE HOUSE 43 UNDER VOTES',\n",
    "                            'State Representative 48': 'STATE HOUSE 48 DEM Jennifer Day', 'State Representative 48.1': 'STATE HOUSE 48 REP Terry Frederick', 'State Representative 48.2': 'STATE HOUSE 48 WRITEIN', 'State Representative 48.3': 'STATE HOUSE 48 OVER VOTES', 'State Representative 48.4': 'STATE HOUSE 48 UNDER VOTES',\n",
    "                            'State Representative 49': 'STATE HOUSE 49 DEM Katie Dixon', 'State Representative 49.1': 'STATE HOUSE 49 REP Megan Lynn', 'State Representative 49.2': 'STATE HOUSE 49 WRITEIN', 'State Representative 49.3': 'STATE HOUSE 49 OVER VOTES', 'State Representative 49.4': 'STATE HOUSE 49 UNDER VOTES',\n",
    "                            'State Representative 78': 'STATE HOUSE 78 DEM Kathy Meyer', 'State Representative 78.1': 'STATE HOUSE 78 REP Ron Ryckman', 'State Representative 78.2': 'STATE HOUSE 78 WRITEIN', 'State Representative 78.3': 'STATE HOUSE 78 OVER VOTES', 'State Representative 78.4': 'STATE HOUSE 78 UNDER VOTES',\n",
    "                            'State Representative 121': 'STATE HOUSE 121 REP John Resman', 'State Representative 121.1': 'STATE HOUSE 121 DEM James Rexford', 'State Representative 121.2': 'STATE HOUSE 121 WRITEIN', 'State Representative 121.3': 'STATE HOUSE 121 OVER VOTES', 'State Representative 121.4': 'STATE HOUSE 121 UNDER VOTES'\n",
    "                           })\n",
    "                           \n",
    "df_john = df_john.iloc[2:]\n",
    "\n",
    "df_john=pd.melt(df_john, id_vars=['precinct'] , var_name='candidate', value_name='votes')\n",
    "\n",
    "\n",
    "df_john['county'] = 'Johnson'\n",
    "df_john['party'] = 'party'\n",
    "df_john['race'] = 'race'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_shawnee = pd.concat(li2, axis=0, ignore_index=True)\n",
    "\n",
    "df_shawnee = df_shawnee.replace(np.nan, '', regex = True)\n",
    "\n",
    "df_shawnee = df_shawnee.drop([218, 219, 220, 437, 438, 439, 656, 657, 658, 875, 876, 877, 1094, 1095, 1096, 1313, 1314, 1315, 1532])\n",
    "\n",
    "df_shawnee = df_shawnee.drop(['PRECINCT CODE'], axis=1)\n",
    "\n",
    "# Rename Precinct name and US Senate Columns \n",
    "df_shawnee = df_shawnee.rename(columns={'PRECINCT NAME': 'precinct', 'For United States Senator': 'US SEN DEM Barbara Bollier', 'For United States Senator.1': 'US SEN LIB Jason Buckley',\n",
    "                            'For United States Senator.2': 'US SEN REP Roger Marshall', 'For United States Senator.3': 'US SEN WRITEIN'}) \n",
    "\n",
    "# Rename Court of Appeals Columns \n",
    "df_shawnee = df_shawnee.rename(columns={'Court of Appeals, Position No. 4': 'Judge 4: Sarah E Warner - YES',\n",
    "                            'Court of Appeals, Position No. 4.1': 'Judge 4: Sarah E Warner - NO',\n",
    "                            'Court of Appeals, Position No. 6': 'Judge 6: David E Bruns - YES',\n",
    "                            'Court of Appeals, Position No. 6.1': 'Judge 6: David E Bruns - NO',\n",
    "                            'Court of Appeals, Position No. 8': 'Judge 8: Gordon G Atcheson - YES',\n",
    "                            'Court of Appeals, Position No. 8.1': 'Judge 8: Gordon G Atcheson - NO',\n",
    "                            'Court of Appeals, Position No. 9': 'Judge 9: Karen M Arnold-Burger - YES',\n",
    "                            'Court of Appeals, Position No. 9.1': 'Judge 9: Karen M Arnold-Burger - NO',\n",
    "                            'Court of Appeals, Position No. 14': 'Judge 14: Kathryn Gardner - YES',\n",
    "                            'Court of Appeals, Position No. 14.1': 'Judge 14: Kathryn Gardner - NO'\n",
    "                           }) \n",
    "\n",
    "# Rename State Senate Columns \n",
    "df_shawnee = df_shawnee.rename(columns={'For State Senator 18th District': \"STATE SEN 18 REP Kristen O'Shea\", 'For State Senator 18th District.1': 'STATE SEN 18 DEM Tobias Schlingensiepen',\n",
    "                                        'For State Senator 18th District.2': 'STATE SEN 18 WRITEIN', 'For State Senator 19th District': 'STATE SEN 19 DEM Anthony Hensley', \n",
    "                                        'For State Senator 19th District.1': 'STATE SEN 19 REP Rick Kloos', 'For State Senator 19th District.2': 'STATE SEN 19 WRITEIN', \n",
    "                                        'For State Senator 20th District': 'STATE SEN 20 REP Brenda S Dietrich', 'For State Senator 20th District.1': 'STATE SEN 20 DEM Rachel Willis',\n",
    "                                        'For State Senator 20th District.2': 'STATE SEN 20 WRITEIN'\n",
    "                           }) \n",
    "\n",
    "# Rename Supreme Court Justice & US Rep & President Columns \n",
    "df_shawnee = df_shawnee.rename(columns={'Kansas Supreme Court, Position No. 4': 'Eric S Rosen - YES', 'Kansas Supreme Court, Position No. 4.1': 'Eric S Rosen - NO',\n",
    "                            'For Presidential Electors for President and Vice-President': 'PRES DEM Biden and Harris', 'For Presidential Electors for President and Vice-President.1': 'PRES LIB Jorgensen and Cohen', \n",
    "                           'For Presidential Electors for President and Vice-President.2': 'PRES REP Trump and Pence', 'For United States Representative 2nd District': 'US HOUSE 2ND DIST DEM Michelle De La Isla',\n",
    "                            'For United States Representative 2nd District.1': 'US HOUSE 2ND DIST LIB Robert Garrard', 'For United States Representative 2nd District.2': 'US HOUSE 2ND DIST REP Jake LaTurner', \n",
    "                            'For United States Representative 2nd District.3': 'US HOUSE 2ND DIST WRITEIN'})\n",
    "\n",
    "# Rename State Rep Columns                          \n",
    "df_shawnee = df_shawnee.rename(columns={'For State Representative 47th District': 'STATE HOUSE 47 DEM Michael Caddell', 'For State Representative 47th District.1': 'STATE HOUSE 47 REP Ronald B Ellis', 'For State Representative 47th District.2': 'STATE HOUSE 47 WRITEIN',\n",
    "                                        'For State Representative 50th District': 'STATE HOUSE 50 REP Fred C Patton', 'For State Representative 50th District.1': 'STATE HOUSE 50 DEM Timothy B Reed', 'For State Representative 50th District.2': 'STATE HOUSE 50 WRITEIN',\n",
    "                                        'For State Representative 51st District': 'STATE HOUSE 51 REP Ron Highland', 'For State Representative 51st District.1': 'STATE HOUSE 51 WRITEIN',\n",
    "                                        'For State Representative 52nd District': 'STATE HOUSE 52 REP Jesse Borjon', 'For State Representative 52nd District.1': 'STATE HOUSE 52 DEM Mary Lou Davis', 'For State Representative 52nd District.2': 'STATE HOUSE 52 WRITEIN',\n",
    "                                        'For State Representative 53rd District': 'STATE HOUSE 53 REP Jeff Coen', 'For State Representative 53rd District.1': 'STATE HOUSE 53 DEM Jim Gartner', 'For State Representative 53rd District.2': 'STATE HOUSE 53 WRITEIN',\n",
    "                                        'For State Representative 54th District': 'STATE HOUSE 54 DEM John Brosz', 'For State Representative 54th District.1': 'STATE HOUSE 54 REP Ken Corbet', 'For State Representative 54th District.2': 'STATE HOUSE 54 WRITEIN',\n",
    "                                        'For State Representative 55th District': 'STATE HOUSE 55 DEM Annie Kuether', 'For State Representative 55th District.1': 'STATE HOUSE 55 REP Janlyn Nesbett Tucker', 'For State Representative 55th District.2': 'STATE HOUSE 55 WRITEIN',\n",
    "                                        'For State Representative 56th District': 'STATE HOUSE 56 REP Tim Clothier', 'For State Representative 56th District.1': 'STATE HOUSE 56 DEM Virgil Weigel', 'For State Representative 56th District.2': 'STATE HOUSE 56 WRITEIN',\n",
    "                                        'For State Representative 57th District': 'STATE HOUSE 57 DEM John Alcala', 'For State Representative 57th District.1': 'STATE HOUSE 57 REP Michael Martin', 'For State Representative 57th District.2': 'STATE HOUSE 57 WRITEIN',\n",
    "                                        'For State Representative 58th District': 'STATE HOUSE 58 REP Geoffrey H Gawdun', 'For State Representative 58th District.1': 'STATE HOUSE 58 DEM Vic Miller', 'For State Representative 58th District.2': 'STATE HOUSE 58 WRITEIN'\n",
    "                           })\n",
    "                            \n",
    "df_shawnee = df_shawnee.iloc[2:]\n",
    "\n",
    "df_shawnee=pd.melt(df_shawnee, id_vars=['precinct'] , var_name='candidate', value_name='votes')\n",
    "\n",
    "df_shawnee['county'] = 'Shawnee'\n",
    "df_shawnee['party'] = 'party'\n",
    "df_shawnee['race'] = 'race'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "wyandotte_sheet = 'WYANDOTTE' \n",
    "sedgwick_sheet = 'SEDGWICK'\n",
    "\n",
    "courtappeals_file_name = '2020_General_Election_Court_of_Appeals_results_by_precinct.xlsx' \n",
    "statehouse_file_name = '2020_General_Election_Kansas_House_of_Representatives_results_by_precinct.xlsx'\n",
    "statesenate_file_name = '2020_General_Election_Kansas_Senate_results_by_precinct.xlsx' \n",
    "president_file_name = '2020_General_Election_President_results_by_precinct.xlsx' \n",
    "supremecourt_file_name = '2020_General_Election_Supreme_Court_Justice_results_by_precinct.xlsx'\n",
    "ushouse_file_name = '2020_General_Election_US_House_of_Representatives_results_by_precinct.xlsx' \n",
    "ussenate_file_name = '2020_General_Election_US_Senate_results_by_precinct.xlsx' \n",
    "\n",
    "w_appeals = read_excel(courtappeals_file_name, sheet_name = wyandotte_sheet) # done \n",
    "w_ksHouse = read_excel(statehouse_file_name, sheet_name = wyandotte_sheet) # done \n",
    "w_ksSen = read_excel(statesenate_file_name, sheet_name = wyandotte_sheet) # done \n",
    "w_pres = read_excel(president_file_name, sheet_name = wyandotte_sheet) # done \n",
    "# court of appeals and supreme court are the same sheet \n",
    "w_usHouse = read_excel(ushouse_file_name, sheet_name = wyandotte_sheet) # in w_ksSen, don't repeat\n",
    "w_usSen = read_excel(ussenate_file_name, sheet_name = wyandotte_sheet) # done \n",
    "\n",
    "s_appeals = read_excel(courtappeals_file_name, sheet_name = sedgwick_sheet) # done \n",
    "s_ksHouse = read_excel(statehouse_file_name, sheet_name = sedgwick_sheet) # done \n",
    "s_ksSen = read_excel(statesenate_file_name, sheet_name = sedgwick_sheet) # done \n",
    "s_pres = read_excel(president_file_name, sheet_name = sedgwick_sheet) # done\n",
    "s_supreme = read_excel(supremecourt_file_name, sheet_name = sedgwick_sheet) # done \n",
    "s_usHouse = read_excel(ushouse_file_name, sheet_name = sedgwick_sheet) # done \n",
    "s_usSen = read_excel(ussenate_file_name, sheet_name = sedgwick_sheet) # done "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "s_usHouse = s_usHouse.iloc[3:272]\n",
    "\n",
    "s_usHouse = s_usHouse.drop(['Unnamed: 4'], axis=1)\n",
    "\n",
    "s_usHouse = s_usHouse.rename(columns={'Precinct': 'precinct', 'For United States Representative, 4th District': 'US HOUSE 4 REP Ron Estes',\n",
    "                                      'Unnamed: 2': 'US HOUSE 4 DEM Laura Lombard', \n",
    "                                       'Unnamed: 3': 'US HOUSE 4 WRITEIN'})\n",
    "\n",
    "s_usHouse=pd.melt(s_usHouse, id_vars=['precinct'] , var_name='candidate', value_name='votes')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "s_supreme = s_supreme.iloc[3:272]\n",
    "\n",
    "s_supreme = s_supreme.drop(['Unnamed: 3'], axis=1)\n",
    "\n",
    "s_supreme = s_supreme.rename(columns={'Precinct': 'precinct', 'Supreme Court Pos. 4': 'Eric S Rosen - YES',\n",
    "                                      'Unnamed: 2': 'Eric S Rosen - NO', \n",
    "                           })\n",
    "\n",
    "s_supreme=pd.melt(s_supreme, id_vars=['precinct'] , var_name='candidate', value_name='votes')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "s_usSen = s_usSen.iloc[3:272]\n",
    "\n",
    "s_usSen = s_usSen.drop(['Unnamed: 5'], axis=1)\n",
    "\n",
    "s_usSen = s_usSen.rename(columns={'Precinct': 'precinct', 'For United States Senator': 'US SEN DEM Barbara Bollier', 'Unnamed: 2': 'US SEN LIB Jason Buckley', \n",
    "                           'Unnamed: 3': 'US SEN REP Roger Marshall', 'Unnamed: 4': 'US SEN WRITEIN'})\n",
    "\n",
    "s_usSen=pd.melt(s_usSen, id_vars=['precinct'] , var_name='candidate', value_name='votes')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "s_pres = s_pres.iloc[3:272]\n",
    "\n",
    "s_pres = s_pres.rename(columns={'Precinct': 'precinct', 'Electors for President / Vice President': 'PRES DEM Biden and Harris', 'Unnamed: 2': 'PRES LIB Jorgensen and Cohen', \n",
    "                           'Unnamed: 3': 'PRES REP Trump and Pence', 'Unnamed: 4': 'PRES WRITEIN'})\n",
    "\n",
    "s_pres=pd.melt(s_pres, id_vars=['precinct'] , var_name='candidate', value_name='votes')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "s_ksSen = s_ksSen.iloc[3:272]\n",
    "\n",
    "s_ksSen = s_ksSen.drop(['Unnamed: 4', 'Unnamed: 8', 'Unnamed: 11', 'Unnamed: 15', 'Unnamed: 19', 'Unnamed: 23',\n",
    "                       'Unnamed: 27', 'Unnamed: 30', 'Unnamed: 33'], axis=1)\n",
    "\n",
    "s_ksSen = s_ksSen.rename(columns={'Precinct': 'precinct',\n",
    "                              'For State Senator 16th District': 'STATE SEN 16 DEM Timothy Don Fry II',\n",
    "                              'Unnamed: 2': 'STATE SEN 16 REP Ty Masterson', \n",
    "                              'Unnamed: 3': 'STATE SEN 16 WRITEIN', \n",
    "                              'For State Senator 25th District': 'STATE SEN 25 REP Vail Fruechting', \n",
    "                              'Unnamed: 6': 'STATE SEN 25 DEM Mary Ware',\n",
    "                              'Unnamed: 7': 'STATE SEN 25 WRITEIN', \n",
    "                              'For State Senator 26th District': 'STATE SEN 26 REP Dan Kerschen', \n",
    "                              'Unnamed: 10': 'STATE SEN 26 WRITEIN',\n",
    "                              'For State Senator 27th District': 'STATE SEN 27 DEM Mike McCorkle',\n",
    "                              'Unnamed: 13': 'STATE SEN 27 REP Gene Suellentrop', \n",
    "                              'Unnamed: 14': 'STATE SEN 27 WRITEIN', \n",
    "                              'For State Senator 28th District': 'STATE SEN 28 REP Mike Petersen',\n",
    "                              'Unnamed: 17': 'STATE SEN 28 DEM Jim Ward',\n",
    "                              'Unnamed: 18': 'STATE SEN 28 WRITEIN', \n",
    "                              'For State Senator 29th District': 'STATE SEN 29 REP Myron L Ackerman', \n",
    "                              'Unnamed: 21': 'STATE SEN 29 DEM Oletha Faust Goudeau',\n",
    "                              'Unnamed: 22': 'STATE SEN 29 WRITEIN',\n",
    "                              'For State Senator 30th District': 'STATE SEN 30 REP Renee Erickson', \n",
    "                              'Unnamed: 25': 'STATE SEN 30 DEM Melissa Gregory', \n",
    "                              'Unnamed: 26': 'STATE SEN 30 WRITEIN', \n",
    "                              'For State Senator 31st District': 'STATE SEN 31 REP Carolyn McGinn',\n",
    "                              'Unnamed: 29': 'STATE SEN 31 WRITEIN', \n",
    "                              'For State Senator 32nd District': 'STATE SEN 32 REP Larry W Alley', \n",
    "                              'Unnamed: 32': 'STATE SEN 32 WRITEIN'\n",
    "                                  \n",
    "                       }) \n",
    "\n",
    "s_ksSen=pd.melt(s_ksSen, id_vars=['precinct'] , var_name='candidate', value_name='votes')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "s_ksHouse = s_ksHouse.iloc[3:272]\n",
    "\n",
    "s_ksHouse = s_ksHouse.drop(['Unnamed: 4', 'Unnamed: 8', 'Unnamed: 12', 'Unnamed: 16', 'Unnamed: 20', \n",
    "                            'Unnamed: 24', 'Unnamed: 28', 'Unnamed: 31', 'Unnamed: 35', 'Unnamed: 38',\n",
    "                            'Unnamed: 41', 'Unnamed: 45', 'Unnamed: 48', 'Unnamed: 52', 'Unnamed: 56',\n",
    "                           'Unnamed: 60', 'Unnamed: 64', 'Unnamed: 68', 'Unnamed: 72', 'Unnamed: 76',\n",
    "                           'Unnamed: 80', 'Unnamed: 85', 'Unnamed: 89'], axis=1)\n",
    "\n",
    "s_ksHouse = s_ksHouse.rename(columns={'Precinct': 'precinct',\n",
    "                              'For State Representative 81st District': 'STATE HOUSE 81 REP Blake Carpenter',\n",
    "                              'Unnamed: 2': 'STATE HOUSE 81 DEM Matthew Joyce',\n",
    "                              'Unnamed: 3': 'STATE HOUSE 81 WRITEIN',\n",
    "                              'For State Representative 82nd District': 'STATE HOUSE 82 REP Jesse Burris',\n",
    "                              'Unnamed: 6': 'STATE HOUSE 82 DEM Edward Hackerott',\n",
    "                              'Unnamed: 7': 'STATE HOUSE 82 WRITEIN',\n",
    "                              'For State Representative 83rd District': 'STATE HOUSE 83 DEM Henry Helgerson',\n",
    "                              'Unnamed: 10': 'STATE HOUSE 83 REP David Robbins',\n",
    "                              'Unnamed: 11': 'STATE HOUSE 83 WRITEIN',\n",
    "                              'For State Representative 84th District': 'STATE HOUSE 84 DEM Gail Finney',\n",
    "                              'Unnamed: 14': 'STATE HOUSE 84 REP Janet Sue Rine',\n",
    "                              'Unnamed: 15': 'STATE HOUSE 84 WRITEIN',\n",
    "                              'For State Representative 85th District': 'STATE HOUSE 85 DEM Marcey Gregory',\n",
    "                              'Unnamed: 18': 'STATE HOUSE 85 REP Patrick Penn',\n",
    "                              'Unnamed: 19': 'STATE HOUSE 85 WRITEIN',\n",
    "                              'For State Representative 86th District': 'STATE HOUSE 86 DEM Stephanie Byers',\n",
    "                              'Unnamed: 22': 'STATE HOUSE 86 REP Cyndi Howerton',\n",
    "                              'Unnamed: 23': 'STATE HOUSE 86 WRITEIN',\n",
    "                              'For State Representative 87th District': 'STATE HOUSE 87 REP Susan Oliver Estes',\n",
    "                              'Unnamed: 26': 'STATE HOUSE 87 DEM Matt Fox',\n",
    "                              'Unnamed: 27': 'STATE HOUSE 87 WRITEIN',\n",
    "                              'For State Representative 88th District': 'STATE HOUSE 88 DEM Elizabeth Bishop',\n",
    "                              'Unnamed: 30': 'STATE HOUSE 88 WRITEIN',\n",
    "                              'For State Representative 89th District': 'STATE HOUSE 89 DEM KC Ohaebosim',\n",
    "                              'Unnamed: 33': 'STATE HOUSE 89 REP Robert W Herrick Jr',\n",
    "                              'Unnamed: 34': 'STATE HOUSE 89 WRITEIN',\n",
    "                              'For State Representative 90th District': 'STATE HOUSE 90 REP Steve Huebert',\n",
    "                              'Unnamed: 37': 'STATE HOUSE 90 WRITEIN',   \n",
    "                              'For State Representative 91st District': 'STATE HOUSE 91 REP Emil M. Bergquist',\n",
    "                              'Unnamed: 40': 'STATE HOUSE 91 WRITEIN',\n",
    "                              'For State Representative 92nd District': 'STATE HOUSE 92 DEM John Carmichael',\n",
    "                              'Unnamed: 43': 'STATE HOUSE 92 REP Patrick McCormack',\n",
    "                              'Unnamed: 44': 'STATE HOUSE 92 WRITEIN',\n",
    "                              'For State Representative 93rd District': 'STATE HOUSE 93 REP Brian Bergkamp',\n",
    "                              'Unnamed: 47': 'STATE HOUSE 93 WRITEIN',\n",
    "                              'For State Representative 94th District': 'STATE HOUSE 94 REP Leo G. Delperdang',\n",
    "                              'Unnamed: 50': 'STATE HOUSE 94 DEM Derek Milligan',   \n",
    "                              'Unnamed: 51': 'STATE HOUSE 94 WRITEIN',\n",
    "                              'For State Representative 95th District': 'STATE HOUSE 95 REP Christopher Parisho',\n",
    "                              'Unnamed: 54': 'STATE HOUSE 95 DEM Tom Sawyer',\n",
    "                              'Unnamed: 55': 'STATE HOUSE 95 WRITEIN',\n",
    "                              'For State Representative 96th District': 'STATE HOUSE 96 REP Tom Kessler',\n",
    "                              'Unnamed: 58': 'STATE HOUSE 96 DEM Stephanie Yeager',\n",
    "                              'Unnamed: 59': 'STATE HOUSE 96 WRITEIN',\n",
    "                              'For State Representative 97th District': 'STATE HOUSE 97 REP Nick Hoheisel',\n",
    "                              'Unnamed: 62': 'STATE HOUSE 97 DEM Kim Webb',   \n",
    "                              'Unnamed: 63': 'STATE HOUSE 97 WRITEIN',\n",
    "                              'For State Representative 98th District': 'STATE HOUSE 98 DEM Steven G Crum',\n",
    "                              'Unnamed: 66': 'STATE HOUSE 98 REP Ron Howard',\n",
    "                              'Unnamed: 67': 'STATE HOUSE 98 WRITEIN',\n",
    "                              'For State Representative 99th District': 'STATE HOUSE 99 DEM Phil Hodson',\n",
    "                              'Unnamed: 70': 'STATE HOUSE 99 REP Susan Humphries',\n",
    "                              'Unnamed: 71': 'STATE HOUSE 99 WRITEIN',\n",
    "                              'For State Representative 100th District': 'STATE HOUSE 100 REP Daniel Hawkins',\n",
    "                              'Unnamed: 74': 'STATE HOUSE 100 DEM Chad Smith',   \n",
    "                              'Unnamed: 75': 'STATE HOUSE 100 WRITEIN',\n",
    "                              'For State Representative 101st District': 'STATE HOUSE 101 DEM Elliott R. Adams',\n",
    "                              'Unnamed: 78': 'STATE HOUSE 101 REP Joe Seiwert',\n",
    "                              'Unnamed: 79': 'STATE HOUSE 101 WRITEIN',\n",
    "                              'For State Representative 103rd District': 'STATE HOUSE 103 REP Susanne Haynes',   \n",
    "                              'Unnamed: 82': 'STATE HOUSE 103 LIB Loren John Hermreck',\n",
    "                              'Unnamed: 83': 'STATE HOUSE 103 DEM Ponka-We Victors',\n",
    "                              'Unnamed: 84': 'STATE HOUSE 103 WRITEIN',   \n",
    "                              'For State Representative 105th District': 'STATE HOUSE 105 REP Brenda Landwehr',\n",
    "                              'Unnamed: 87': 'STATE HOUSE 105 DEM Michelle Snyder',\n",
    "                              'Unnamed: 88': 'STATE HOUSE 105 WRITEIN'\n",
    "                       }) \n",
    "\n",
    "s_ksHouse=pd.melt(s_ksHouse, id_vars=['precinct'] , var_name='candidate', value_name='votes')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "s_appeals = s_appeals.iloc[3:272]\n",
    "\n",
    "s_appeals = s_appeals.drop(['Unnamed: 3', 'Unnamed: 6', 'Unnamed: 9', 'Unnamed: 12', 'Unnamed: 15'], axis=1)\n",
    "\n",
    "s_appeals = s_appeals.rename(columns={'Precinct': 'precinct',\n",
    "                              'Court of Appeals Pos. 4': 'Judge 4: Sarah E Warner - YES',\n",
    "                              'Unnamed: 2': 'Judge 4: Sarah E Warner - NO',\n",
    "                              'Court of Appeals Pos. 6': 'Judge 6: David E Bruns - YES',\n",
    "                              'Unnamed: 5': 'Judge 6: David E Bruns - NO',\n",
    "                              'Court of Appeals Pos. 8': 'Judge 8: Gordon G Atcheson - YES',\n",
    "                              'Unnamed: 8': 'Judge 8: Gordon G Atcheson - NO',\n",
    "                              'Court of Appeals Pos. 9': 'Judge 9: Karen M Arnold-Burger - YES',\n",
    "                              'Unnamed: 11': 'Judge 9: Karen M Arnold-Burger - NO',\n",
    "                              'Court of Appeals Pos. 14': 'Judge 14: Kathryn Gardner - YES',\n",
    "                              'Unnamed: 14': 'Judge 14: Kathryn Gardner - NO'   \n",
    "                       }) \n",
    "\n",
    "s_appeals=pd.melt(s_appeals, id_vars=['precinct'] , var_name='candidate', value_name='votes')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "### again relocating inappropriate ordering\n",
    "sedFrames = [s_usSen, s_usHouse, s_supreme, s_pres, s_ksSen, s_ksHouse, s_appeals]\n",
    "\n",
    "sedDataframes = pd.concat(sedFrames)\n",
    "\n",
    "# add county name -> sedgwick\n",
    "sedDataframes['county'] = 'Sedgwick'\n",
    "sedDataframes['party'] = 'party'\n",
    "sedDataframes['race'] = 'race'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***WYANDOTTE US SENATE*** -> w_usSen"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "w_usSen = w_usSen.drop(['Unnamed: 0', 'Unnamed: 2', 'Unnamed: 3', 'Unnamed: 6', 'Unnamed: 9', 'Unnamed: 10'], axis=1)\n",
    "\n",
    "w_usSen = w_usSen.iloc[:129]\n",
    "\n",
    "w_usSen = w_usSen.rename(columns={'Unnamed: 1': 'precinct',\n",
    "                              'Unnamed: 4': 'US SEN DEM Barbara Bollier',\n",
    "                              'Unnamed: 5': 'US SEN LIB Jason Buckley',\n",
    "                              'Unnamed: 7': 'US SEN REP Roger Marshall',\n",
    "                              'Unnamed: 8': 'US SEN WRITEIN'\n",
    "                       }) \n",
    "\n",
    "w_usSen = w_usSen.drop([0, 1, 2, 3, 15, 16, 17, 50, 51, 52, 85, 86, 87, 120, 121, 122])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "w_usSen=pd.melt(w_usSen, id_vars=['precinct'] , var_name='candidate', value_name='votes')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***WYANDOTTE PRESIDENT*** -> w_pres"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "w_pres = w_pres.drop(['Unnamed: 1', 'Unnamed: 3', 'Unnamed: 5', 'Unnamed: 7', 'Unnamed: 9'], axis=1)\n",
    "\n",
    "w_pres = w_pres.iloc[:127]\n",
    "\n",
    "w_pres = w_pres.rename(columns={'Unnamed: 0': 'precinct',\n",
    "                              'President and Vice-President': 'PRES DEM Biden and Harris',\n",
    "                              'Unnamed: 4': 'PRES LIB Jorgensen and Cohen',\n",
    "                              'Unnamed: 6': 'PRES REP Trump and Pence',\n",
    "                              'Unnamed: 8': 'PRES WRITEIN'\n",
    "                       }) \n",
    "\n",
    "w_pres = w_pres.drop([0, 1, 13, 14, 15, 48, 49, 50, 83, 84, 85, 118, 119, 120])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "w_pres=pd.melt(w_pres, id_vars=['precinct'] , var_name='candidate', value_name='votes')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***WYANDOTTE STATE SENATE*** -> w6"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "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>Unnamed: 0</th>\n",
       "      <th>Unnamed: 1</th>\n",
       "      <th>Unnamed: 2</th>\n",
       "      <th>US REP 3rd District</th>\n",
       "      <th>Unnamed: 4</th>\n",
       "      <th>Unnamed: 5</th>\n",
       "      <th>Unnamed: 6</th>\n",
       "      <th>Unnamed: 7</th>\n",
       "      <th>Unnamed: 8</th>\n",
       "      <th>Unnamed: 9</th>\n",
       "      <th>Unnamed: 10</th>\n",
       "      <th>State Senator 4th District</th>\n",
       "      <th>Unnamed: 12</th>\n",
       "      <th>Unnamed: 13</th>\n",
       "      <th>Unnamed: 14</th>\n",
       "      <th>State Senator 5th District</th>\n",
       "      <th>Unnamed: 16</th>\n",
       "      <th>Unnamed: 17</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>VOTE FOR 1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>VOTE FOR 1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>VOTE FOR 1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Rep Amanda L. Adkins</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Dem Sharice L. Davids</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Lib Steven A. Hohe</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Write-in Totals</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Dem David Haley</td>\n",
       "      <td>Rep Sam Stillwell</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Write-in Totals</td>\n",
       "      <td>Rep Kevin Braun</td>\n",
       "      <td>Dem Jeff Pittman</td>\n",
       "      <td>Write-in Totals</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>BS 1-1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>397</td>\n",
       "      <td>NaN</td>\n",
       "      <td>385</td>\n",
       "      <td>NaN</td>\n",
       "      <td>47</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>444</td>\n",
       "      <td>375</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>BS 2-1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>306</td>\n",
       "      <td>NaN</td>\n",
       "      <td>302</td>\n",
       "      <td>NaN</td>\n",
       "      <td>35</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>350</td>\n",
       "      <td>287</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>BS 3-1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>428</td>\n",
       "      <td>NaN</td>\n",
       "      <td>433</td>\n",
       "      <td>NaN</td>\n",
       "      <td>36</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>464</td>\n",
       "      <td>422</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>173</th>\n",
       "      <td>NaN</td>\n",
       "      <td>KC 12-10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>172</td>\n",
       "      <td>NaN</td>\n",
       "      <td>231</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>174</th>\n",
       "      <td>NaN</td>\n",
       "      <td>KC 12-11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>79</td>\n",
       "      <td>NaN</td>\n",
       "      <td>134</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>175</th>\n",
       "      <td>NaN</td>\n",
       "      <td>KC 14-16</td>\n",
       "      <td>NaN</td>\n",
       "      <td>228</td>\n",
       "      <td>NaN</td>\n",
       "      <td>140</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>176</th>\n",
       "      <td>NaN</td>\n",
       "      <td>QC 1-1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>177</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Totals</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12,441</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6,681</td>\n",
       "      <td>NaN</td>\n",
       "      <td>54</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2,070</td>\n",
       "      <td>5,211</td>\n",
       "      <td>31</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>178 rows × 18 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     Unnamed: 0 Unnamed: 1  Unnamed: 2   US REP 3rd District  Unnamed: 4  \\\n",
       "0           NaN        NaN         NaN            VOTE FOR 1         NaN   \n",
       "1           NaN        NaN         NaN  Rep Amanda L. Adkins         NaN   \n",
       "2           NaN     BS 1-1         NaN                   397         NaN   \n",
       "3           NaN     BS 2-1         NaN                   306         NaN   \n",
       "4           NaN     BS 3-1         NaN                   428         NaN   \n",
       "..          ...        ...         ...                   ...         ...   \n",
       "173         NaN   KC 12-10         NaN                   172         NaN   \n",
       "174         NaN   KC 12-11         NaN                    79         NaN   \n",
       "175         NaN   KC 14-16         NaN                   228         NaN   \n",
       "176         NaN     QC 1-1         NaN                   NaN         NaN   \n",
       "177         NaN     Totals         NaN                12,441         NaN   \n",
       "\n",
       "                Unnamed: 5  Unnamed: 6          Unnamed: 7  Unnamed: 8  \\\n",
       "0                      NaN         NaN                 NaN         NaN   \n",
       "1    Dem Sharice L. Davids         NaN  Lib Steven A. Hohe         NaN   \n",
       "2                      385         NaN                  47         NaN   \n",
       "3                      302         NaN                  35         NaN   \n",
       "4                      433         NaN                  36         NaN   \n",
       "..                     ...         ...                 ...         ...   \n",
       "173                    231         NaN                   1         NaN   \n",
       "174                    134         NaN                   0         NaN   \n",
       "175                    140         NaN                   0         NaN   \n",
       "176                    NaN         NaN                 NaN         NaN   \n",
       "177                  6,681         NaN                  54         NaN   \n",
       "\n",
       "          Unnamed: 9  Unnamed: 10 State Senator 4th District  \\\n",
       "0                NaN          NaN                 VOTE FOR 1   \n",
       "1    Write-in Totals          NaN            Dem David Haley   \n",
       "2                  1          NaN                        NaN   \n",
       "3                  1          NaN                        NaN   \n",
       "4                  2          NaN                        NaN   \n",
       "..               ...          ...                        ...   \n",
       "173              NaN          NaN                        NaN   \n",
       "174              NaN          NaN                        NaN   \n",
       "175              NaN          NaN                        NaN   \n",
       "176               10          NaN                         15   \n",
       "177               10          NaN                         15   \n",
       "\n",
       "           Unnamed: 12  Unnamed: 13      Unnamed: 14  \\\n",
       "0                  NaN          NaN              NaN   \n",
       "1    Rep Sam Stillwell          NaN  Write-in Totals   \n",
       "2                  NaN          NaN              NaN   \n",
       "3                  NaN          NaN              NaN   \n",
       "4                  NaN          NaN              NaN   \n",
       "..                 ...          ...              ...   \n",
       "173                NaN          NaN              NaN   \n",
       "174                NaN          NaN              NaN   \n",
       "175                NaN          NaN              NaN   \n",
       "176                  0          NaN              NaN   \n",
       "177                  0          NaN            2,070   \n",
       "\n",
       "    State Senator 5th District       Unnamed: 16      Unnamed: 17  \n",
       "0                   VOTE FOR 1               NaN              NaN  \n",
       "1              Rep Kevin Braun  Dem Jeff Pittman  Write-in Totals  \n",
       "2                          444               375                2  \n",
       "3                          350               287                0  \n",
       "4                          464               422                1  \n",
       "..                         ...               ...              ...  \n",
       "173                        NaN               NaN              NaN  \n",
       "174                        NaN               NaN              NaN  \n",
       "175                        NaN               NaN              NaN  \n",
       "176                        NaN               NaN              NaN  \n",
       "177                      5,211                31              NaN  \n",
       "\n",
       "[178 rows x 18 columns]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "w_ksSen"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "w_ksSen = w_ksSen.drop(['Unnamed: 0', 'Unnamed: 2', 'Unnamed: 4', 'Unnamed: 6', 'Unnamed: 8', 'Unnamed: 10', 'Unnamed: 13'], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "# first table of state senate - wyandotte\n",
    "\n",
    "w6 = w_ksSen.iloc[:124]\n",
    "\n",
    "w6 = w6.rename(columns={'Unnamed: 1': 'precinct',\n",
    "                              'US REP 3rd District': 'US HOUSE 3 REP Amanda L Adkins',\n",
    "                              'Unnamed: 5': 'US HOUSE 3 DEM Sharice L Davids',\n",
    "                              'Unnamed: 7': 'US HOUSE 3 LIB Steven A Hohe',\n",
    "                              'Unnamed: 9': 'US HOUSE 3 WRITEIN',\n",
    "                              'State Senator 4th District': 'STATE SEN 4 DEM David Haley',\n",
    "                              'Unnamed: 12': 'STATE SEN 4 REP Sam Stillwell',\n",
    "                              'Unnamed: 14': 'STATE SEN 4 WRITEIN',\n",
    "                              'State Senator 5th District': 'STATE SEN 5 REP Kevin Braun',\n",
    "                              'Unnamed: 16': 'STATE SEN 5 DEM Jeff Pittman',\n",
    "                              'Unnamed: 17': 'STATE SEN 5 WRITEIN'\n",
    "                       }) \n",
    "\n",
    "w6 = w6.drop([0, 1, 34, 35, 36, 69, 70, 71, 104, 105, 106])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "w6=pd.melt(w6, id_vars=['precinct'] , var_name='candidate', value_name='votes')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***WYANDOTTE STATE HOUSE*** -> w3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "w_ksHouse = w_ksHouse.drop(['Unnamed: 0', 'Unnamed: 2', 'Unnamed: 4', 'Unnamed: 6', 'Unnamed: 8', 'Unnamed: 10', 'Unnamed: 13'], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "# first table of house of rep - wyandotte\n",
    "\n",
    "w3 = w_ksHouse.iloc[:51]\n",
    "\n",
    "w3 = w3.drop(['Unnamed: 17'], axis=1)\n",
    "\n",
    "w3 = w3.rename(columns={'Unnamed: 1': 'precinct',\n",
    "                              'State Senator 6th District': 'STATE SEN 6 DEM Pat Pettey',\n",
    "                              'Unnamed: 5': 'STATE SEN 6 REP Diana Whittington',\n",
    "                              'Unnamed: 7': 'STATE SEN 6 WRITEIN',\n",
    "                              'State Senator 10th District': 'STATE SEN 10 DEM Lindsey Constance',\n",
    "                              'Unnamed: 11': 'STATE SEN 10 REP Mike Thompson',\n",
    "                              'Unnamed: 12': 'STATE SEN 10 WRITEIN',\n",
    "                              'State Representative 31st District': 'STATE HOUSE 31 REP Landon Griffith',\n",
    "                              'Unnamed: 15': 'STATE HOUSE 31 DEM Louis E Ruiz',\n",
    "                              'Unnamed: 16': 'STATE HOUSE 31 WRITEIN'\n",
    "                       }) \n",
    "\n",
    "w3 = w3.drop([0, 1, 2, 34, 35, 36, 37])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "# second table of house of rep - wyandotte\n",
    "\n",
    "w4 = w_ksHouse.iloc[52:108]\n",
    "\n",
    "w4 = w4.drop(['Unnamed: 17'], axis=1)\n",
    "\n",
    "w4 = w4.rename(columns={'Unnamed: 1': 'precinct',\n",
    "                              'State Senator 6th District': 'STATE HOUSE 32 REP Greg Conchola',\n",
    "                              'Unnamed: 5': 'STATE HOUSE 32 DEM Pam Curtis',\n",
    "                              'Unnamed: 7': 'STATE HOUSE 32 WRITEIN',\n",
    "                              'State Senator 10th District': 'STATE HOUSE 33 DEM Tom Burroughs',\n",
    "                              'Unnamed: 11': 'STATE HOUSE 33 REP Jordan Michael Mackey',\n",
    "                              'Unnamed: 12': 'STATE HOUSE 33 LIB Rick Parsons',\n",
    "                              'State Representative 31st District': 'STATE HOUSE 33 WRITEIN',\n",
    "                              'Unnamed: 15': 'STATE HOUSE 34 DEM Valdenia C Winn',\n",
    "                              'Unnamed: 16': 'STATE HOUSE 34 WRITEIN'\n",
    "                       }) \n",
    "\n",
    "w4 = w4.drop([52, 53, 54, 55, 87, 88, 89, 90])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "# third table of house of rep - wyandotte\n",
    "\n",
    "w5 = w_ksHouse.iloc[109: 234]\n",
    "\n",
    "w5 = w5.rename(columns={'Unnamed: 1': 'precinct',\n",
    "                              'State Senator 6th District': 'STATE HOUSE 35 DEM Broderick Henderson',\n",
    "                              'Unnamed: 5': 'STATE HOUSE 35 REP Mark David Snelson',\n",
    "                              'Unnamed: 7': 'STATE HOUSE 35 WRITEIN',\n",
    "                              'State Senator 10th District': 'STATE HOUSE 36 REP Mark Gilstrap',\n",
    "                              'Unnamed: 11': 'STATE HOUSE 36 DEM Kathy Wolfe Moore',\n",
    "                              'Unnamed: 12': 'STATE HOUSE 36 WRITEIN',\n",
    "                              'State Representative 31st District': 'STATE HOUSE 37 DEM Aaron Coleman',\n",
    "                              'Unnamed: 15': 'STATE HOUSE 37 WRITEIN',\n",
    "                              'Unnamed: 16': '29TH DISTRICT JUDGE DIV 1 DEM Robert P Burns',\n",
    "                              'Unnamed: 17': '29TH DISTRICT JUDGE DIV 1 WRITEIN'\n",
    "                       }) \n",
    "\n",
    "w5 = w5.drop([109, 110, 111, 143, 144, 145, 177, 178, 179, 211, 212, 213])\n",
    "#w5 = w5.drop(columns = ['Unnamed: 18','Unnamed: 19','Unnamed: 20','Unnamed: 21'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "w3=pd.melt(w3, id_vars=['precinct'] , var_name='candidate', value_name='votes')\n",
    "w4=pd.melt(w4, id_vars=['precinct'] , var_name='candidate', value_name='votes')\n",
    "w5=pd.melt(w5, id_vars=['precinct'] , var_name='candidate', value_name='votes')\n",
    "### deleted previous mistake, where merged on left merged precinct column,\n",
    "### instead, melt first and then concat\n",
    "w3=pd.concat([w3,w4,w5])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***WYANDOTTE COURT OF APPEALS*** -> w1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "w_appeals = w_appeals.drop(['Unnamed: 0', 'Unnamed: 2', 'Unnamed: 4', 'Unnamed: 6', 'Unnamed: 8', 'Unnamed: 10', 'Unnamed: 13'], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "# first table of court of appeals - wyandotte\n",
    "\n",
    "w1 = w_appeals.iloc[:127]\n",
    "\n",
    "w1 = w1.rename(columns={'Unnamed: 1': 'precinct',\n",
    "                              'Pos. 4 KS Supreme Court Justice- Eric S. Rosen': 'Eric S Rosen - YES',\n",
    "                              'Unnamed: 5': 'Eric S Rosen - NO',\n",
    "                              'Pos. 4 KS Court of Appeals Judge-Sarah E. Warner': 'Judge 4: Sarah E Warner - YES',\n",
    "                              'Unnamed: 9': 'Judge 4: Sarah E Warner - NO',\n",
    "                              'Pos. 6 KS Court of Appeals Judge-David E. Bruns': 'Judge 6: David E Bruns - YES',\n",
    "                              'Unnamed: 12': 'Judge 6: David E Bruns - NO',\n",
    "                              'Pos. 8 KS Court of Appeals Judge-G. Gordon Atcheson': 'Gordon G Atcheson - YES',\n",
    "                              'Unnamed: 15': 'Gordon G Atcheson - NO'}) \n",
    "\n",
    "w1 = w1.drop([0, 1, 12, 13, 14, 45, 46, 47, 78, 79, 80, 111, 112, 113])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "# second table of court of appeals - wyandotte\n",
    "\n",
    "w2 = w_appeals.iloc[128:257]\n",
    "\n",
    "w2 = w2.drop(['Pos. 6 KS Court of Appeals Judge-David E. Bruns', 'Unnamed: 12', 'Pos. 8 KS Court of Appeals Judge-G. Gordon Atcheson', 'Unnamed: 15'], axis=1)\n",
    "\n",
    "w2 = w2.rename(columns={'Unnamed: 1': 'precinct',\n",
    "                              'Pos. 4 KS Supreme Court Justice- Eric S. Rosen': 'Judge 9: Karen M Arnold-Burger - YES',\n",
    "                              'Unnamed: 5': 'Judge 9: Karen M Arnold-Burger - NO',\n",
    "                              'Pos. 4 KS Court of Appeals Judge-Sarah E. Warner': 'Judge 14: Kathryn Gardner - YES',\n",
    "                              'Unnamed: 9': 'Judge 14: Kathryn Gardner - NO'}) \n",
    "\n",
    "w2 = w2.drop([128, 129, 130, 131, 161, 162, 163, 164, 194, 195, 196, 197, 227, 228, 229, 230])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "w1=pd.melt(w1, id_vars=['precinct'] , var_name='candidate', value_name='votes')\n",
    "w2=pd.melt(w2, id_vars=['precinct'] , var_name='candidate', value_name='votes')\n",
    "\n",
    "### deleted previous mistake, where merged on left merged precinct column,\n",
    "### instead, melt first and then concat\n",
    "w1=pd.concat([w1,w2])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "### relocate concat to after dfs created\n",
    "\n",
    "wyanFrames = [w_usSen, w_pres, w6, w3, w1]\n",
    "\n",
    "wyanDataframes = pd.concat(wyanFrames)\n",
    "\n",
    "# add county name -> Wyandotte\n",
    "wyanDataframes['county'] = 'Wyandotte'\n",
    "wyanDataframes['party'] = 'party'\n",
    "wyanDataframes['race'] = 'race'\n",
    "wyanDataframes = wyanDataframes.replace(np.nan, 0, regex = True)\n",
    "wyanDataframes = wyanDataframes[wyanDataframes.votes != 'nan']\n",
    "sumsWyan = wyanDataframes.groupby([\"candidate\"]).sum()\n",
    "wyanDataframes['votes'] = wyanDataframes['votes'].str.replace(',','').fillna(0).astype(int)\n",
    "index = wyanDataframes[wyanDataframes.votes == 0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***COMBINE ALL DATAFRAMES***\n",
    "\n",
    "- wyanDataframes, sedDataframes, df_shawnee, df_john, df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "allFrames = [wyanDataframes, sedDataframes, df_shawnee, df_john, df]\n",
    "\n",
    "alldf = pd.concat(allFrames, ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "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>precinct</th>\n",
       "      <th>candidate</th>\n",
       "      <th>votes</th>\n",
       "      <th>county</th>\n",
       "      <th>party</th>\n",
       "      <th>race</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BS 1-1</td>\n",
       "      <td>US SEN DEM Barbara Bollier</td>\n",
       "      <td>353</td>\n",
       "      <td>Wyandotte</td>\n",
       "      <td>party</td>\n",
       "      <td>race</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BS 2-1</td>\n",
       "      <td>US SEN DEM Barbara Bollier</td>\n",
       "      <td>285</td>\n",
       "      <td>Wyandotte</td>\n",
       "      <td>party</td>\n",
       "      <td>race</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BS 3-1</td>\n",
       "      <td>US SEN DEM Barbara Bollier</td>\n",
       "      <td>405</td>\n",
       "      <td>Wyandotte</td>\n",
       "      <td>party</td>\n",
       "      <td>race</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BS 4-1</td>\n",
       "      <td>US SEN DEM Barbara Bollier</td>\n",
       "      <td>589</td>\n",
       "      <td>Wyandotte</td>\n",
       "      <td>party</td>\n",
       "      <td>race</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>DE 1-1</td>\n",
       "      <td>US SEN DEM Barbara Bollier</td>\n",
       "      <td>11</td>\n",
       "      <td>Wyandotte</td>\n",
       "      <td>party</td>\n",
       "      <td>race</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>907858</th>\n",
       "      <td>Yates Center Ward 2</td>\n",
       "      <td>Marshall, Roger</td>\n",
       "      <td>205</td>\n",
       "      <td>Woodson</td>\n",
       "      <td>Republican</td>\n",
       "      <td>United States Senate</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>907859</th>\n",
       "      <td>Yates Center Ward 2</td>\n",
       "      <td>Buckley, Jason</td>\n",
       "      <td>18</td>\n",
       "      <td>Woodson</td>\n",
       "      <td>Libertarian</td>\n",
       "      <td>United States Senate</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>907860</th>\n",
       "      <td>Yates Center Ward 2 Exclave</td>\n",
       "      <td>Bollier, Barbara</td>\n",
       "      <td>0</td>\n",
       "      <td>Woodson</td>\n",
       "      <td>Democratic</td>\n",
       "      <td>United States Senate</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>907861</th>\n",
       "      <td>Yates Center Ward 2 Exclave</td>\n",
       "      <td>Marshall, Roger</td>\n",
       "      <td>0</td>\n",
       "      <td>Woodson</td>\n",
       "      <td>Republican</td>\n",
       "      <td>United States Senate</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>907862</th>\n",
       "      <td>Yates Center Ward 2 Exclave</td>\n",
       "      <td>Buckley, Jason</td>\n",
       "      <td>0</td>\n",
       "      <td>Woodson</td>\n",
       "      <td>Libertarian</td>\n",
       "      <td>United States Senate</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>907863 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                           precinct                   candidate votes  \\\n",
       "0                            BS 1-1  US SEN DEM Barbara Bollier   353   \n",
       "1                            BS 2-1  US SEN DEM Barbara Bollier   285   \n",
       "2                            BS 3-1  US SEN DEM Barbara Bollier   405   \n",
       "3                            BS 4-1  US SEN DEM Barbara Bollier   589   \n",
       "4                            DE 1-1  US SEN DEM Barbara Bollier    11   \n",
       "...                             ...                         ...   ...   \n",
       "907858          Yates Center Ward 2             Marshall, Roger   205   \n",
       "907859          Yates Center Ward 2              Buckley, Jason    18   \n",
       "907860  Yates Center Ward 2 Exclave            Bollier, Barbara     0   \n",
       "907861  Yates Center Ward 2 Exclave             Marshall, Roger     0   \n",
       "907862  Yates Center Ward 2 Exclave              Buckley, Jason     0   \n",
       "\n",
       "           county        party                  race  \n",
       "0       Wyandotte        party                  race  \n",
       "1       Wyandotte        party                  race  \n",
       "2       Wyandotte        party                  race  \n",
       "3       Wyandotte        party                  race  \n",
       "4       Wyandotte        party                  race  \n",
       "...           ...          ...                   ...  \n",
       "907858    Woodson   Republican  United States Senate  \n",
       "907859    Woodson  Libertarian  United States Senate  \n",
       "907860    Woodson   Democratic  United States Senate  \n",
       "907861    Woodson   Republican  United States Senate  \n",
       "907862    Woodson  Libertarian  United States Senate  \n",
       "\n",
       "[907863 rows x 6 columns]"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "alldf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "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>precinct</th>\n",
       "      <th>candidate</th>\n",
       "      <th>votes</th>\n",
       "      <th>county</th>\n",
       "      <th>party</th>\n",
       "      <th>race</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [precinct, candidate, votes, county, party, race]\n",
       "Index: []"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#alldf[alldf.isna().any(axis=1)]\n",
    "alldf[alldf['precinct'].isnull()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>precinct</th>\n",
       "      <th>candidate</th>\n",
       "      <th>votes</th>\n",
       "      <th>county</th>\n",
       "      <th>party</th>\n",
       "      <th>race</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>40281</th>\n",
       "      <td>1 East Rossville</td>\n",
       "      <td>Judge 6: David E Bruns - NO</td>\n",
       "      <td>98</td>\n",
       "      <td>Shawnee</td>\n",
       "      <td>party</td>\n",
       "      <td>race</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40497</th>\n",
       "      <td>1 East Rossville</td>\n",
       "      <td>Judge 6: David E Bruns - NO</td>\n",
       "      <td></td>\n",
       "      <td>Shawnee</td>\n",
       "      <td>party</td>\n",
       "      <td>race</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40713</th>\n",
       "      <td>1 East Rossville</td>\n",
       "      <td>Judge 6: David E Bruns - NO</td>\n",
       "      <td></td>\n",
       "      <td>Shawnee</td>\n",
       "      <td>party</td>\n",
       "      <td>race</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40929</th>\n",
       "      <td>1 East Rossville</td>\n",
       "      <td>Judge 6: David E Bruns - NO</td>\n",
       "      <td></td>\n",
       "      <td>Shawnee</td>\n",
       "      <td>party</td>\n",
       "      <td>race</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41145</th>\n",
       "      <td>1 East Rossville</td>\n",
       "      <td>Judge 6: David E Bruns - NO</td>\n",
       "      <td></td>\n",
       "      <td>Shawnee</td>\n",
       "      <td>party</td>\n",
       "      <td>race</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41361</th>\n",
       "      <td>1 East Rossville</td>\n",
       "      <td>Judge 6: David E Bruns - NO</td>\n",
       "      <td></td>\n",
       "      <td>Shawnee</td>\n",
       "      <td>party</td>\n",
       "      <td>race</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41577</th>\n",
       "      <td>1 East Rossville</td>\n",
       "      <td>Judge 6: David E Bruns - NO</td>\n",
       "      <td></td>\n",
       "      <td>Shawnee</td>\n",
       "      <td>party</td>\n",
       "      <td>race</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               precinct                    candidate votes   county  party  \\\n",
       "40281  1 East Rossville  Judge 6: David E Bruns - NO    98  Shawnee  party   \n",
       "40497  1 East Rossville  Judge 6: David E Bruns - NO        Shawnee  party   \n",
       "40713  1 East Rossville  Judge 6: David E Bruns - NO        Shawnee  party   \n",
       "40929  1 East Rossville  Judge 6: David E Bruns - NO        Shawnee  party   \n",
       "41145  1 East Rossville  Judge 6: David E Bruns - NO        Shawnee  party   \n",
       "41361  1 East Rossville  Judge 6: David E Bruns - NO        Shawnee  party   \n",
       "41577  1 East Rossville  Judge 6: David E Bruns - NO        Shawnee  party   \n",
       "\n",
       "       race  \n",
       "40281  race  \n",
       "40497  race  \n",
       "40713  race  \n",
       "40929  race  \n",
       "41145  race  \n",
       "41361  race  \n",
       "41577  race  "
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "alldf.loc[(alldf['precinct'] == '1 East Rossville') & (alldf['candidate'].str.contains('Judge 6: David E Bruns - NO'))]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note to self: The supreme court judges excel file has a sheet 'WYANDOTTE' that contains the same info as the court of appelas 'WYANDOTTE' sheet so watch for double counting and weird data. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "def getDistrictDF(x):\n",
    "    if any(char.isdigit() for char in x) and \"Judge\" not in x and 'Supreme Court Justice' not in x:\n",
    "        dist = x.split(' ').pop()\n",
    "        return dist\n",
    "    else:\n",
    "        return ''\n",
    "            \n",
    "alldf['district2'] = alldf['race'].apply(getDistrictDF)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "alldf.loc[alldf['candidate'].str.contains(' DEM '), 'party'] = 'DEMOCRATIC'\n",
    "alldf.loc[alldf['candidate'].str.contains(' REP '), 'party'] = 'REPUBLICAN'\n",
    "alldf.loc[alldf['candidate'].str.contains(' LIB '), 'party'] = 'LIBERTARIAN'\n",
    "\n",
    "alldf.loc[alldf['candidate'].str.contains('STATE SEN'), 'race'] = 'STATE SENATE'\n",
    "alldf.loc[alldf['candidate'].str.contains('STATE HOUSE'), 'race'] = 'STATE HOUSE'\n",
    "alldf.loc[alldf['candidate'].str.contains('US SEN'), 'race'] = 'US SENATE'\n",
    "alldf.loc[alldf['candidate'].str.contains('US HOUSE'), 'race'] = 'US HOUSE'\n",
    "alldf.loc[alldf['candidate'].str.contains('Eric S Rosen'), 'race'] = 'Kansas Supreme Court, Position No. 4'\n",
    "alldf.loc[alldf['candidate'].str.contains('PRES '), 'race'] = 'PRESIDENT'\n",
    "alldf.loc[alldf['candidate'].str.contains('Judge'), 'race'] = 'Court of Appeals ' + alldf['candidate'][:9]\n",
    "alldf.loc[alldf['candidate'].str.contains('29TH DISTRICT JUDGE DIV 1'), 'race'] = '29TH DISTRICT JUDGE DIV 1'\n",
    "\n",
    "alldf['writein'] = 'FALSE'\n",
    "alldf.loc[alldf['candidate'].str.contains('WRITEIN'), 'writein'] = 'TRUE'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "def cleanCand(x):\n",
    "\n",
    "    if \"Biden\" in x:\n",
    "        return \"JOSEPH R BIDEN JR\"\n",
    "    elif \"Trump\" in x:\n",
    "        return \"DONALD J TRUMP\"\n",
    "    elif \"Jorgensen\" in x:\n",
    "        return \"JO JORGENSEN\"\n",
    "    elif 'US SEN ' in x and \"WRITEIN\" not in x:\n",
    "        return x[11:]\n",
    "    elif \"US HOUSE \" in x and \"WRITEIN\" not in x: \n",
    "        if ' DIST ' in x: \n",
    "            return x[22:]\n",
    "        elif '3' in x or '4' in x:\n",
    "            return x[15:]\n",
    "        else:\n",
    "            return x[13:]\n",
    "    elif \"STATE SEN \" in x and \"WRITEIN\" not in x and \"VOTE\" not in x:\n",
    "        if \" DEM \" in x: \n",
    "            index = x.index(\"DEM\")\n",
    "            return x[index+4:]\n",
    "        else:\n",
    "            index = x.index(\"REP\")\n",
    "            return x[index+4:]\n",
    "    elif \"STATE HOUSE \" in x and \"WRITEIN\" not in x and \"VOTE\" not in x:\n",
    "        if \" DEM \" in x: \n",
    "            index = x.index(\"DEM\")\n",
    "            return x[index+4:]\n",
    "        elif \"REP \" in x:\n",
    "            index = x.index(\"REP\")\n",
    "            return x[index+4:]\n",
    "        else: \n",
    "            index = x.index(\"LIB\")\n",
    "            return x[index+4:]\n",
    "    elif \"Robert P Burns\" in x: \n",
    "        return \"Robert P Burns\"\n",
    "    elif \"WRITEIN\" in x: \n",
    "        return \"WRITEIN TOTAL\"\n",
    "    else: \n",
    "        return x\n",
    "\n",
    "alldf['candidate2'] = alldf['candidate'].apply(cleanCand)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "# I think i want to remove this line\n",
    "alldf = alldf[~alldf.candidate2.str.contains(\"VOTES\")].copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "def cleanCand2(x):\n",
    "    if \"Judge\" in x: \n",
    "        index = x.index(\":\")\n",
    "        x = x[index+2:]\n",
    "    if \",\" in x and '\"NO\"' not in x and '\"YES\"' not in x:\n",
    "        index = x.index(\",\")\n",
    "        x = x[index+2:] + ' ' + x[:index]\n",
    "    if \",\" in x and '\"NO\"' in x or '\"YES\"' in x:\n",
    "        index = x.index(\",\")\n",
    "        index2 = x.index(\" - \")\n",
    "        x = x[index+2:index2] + ' ' + x[:index] + x[index2:]\n",
    "    if '\"NO\"' in x or '\"YES\"' in x:\n",
    "        x = x.replace('\"', '') \n",
    "    if '.' in x:\n",
    "        x = x.replace('.', '') \n",
    "    if '(' in x:\n",
    "        x = x.replace('(', '\"')\n",
    "    if ')' in x:\n",
    "        x = x.replace(')', '\"')\n",
    "    \n",
    "    else: \n",
    "        return x.upper()\n",
    "    return x.upper()\n",
    "\n",
    "alldf['candidate2'] = alldf['candidate2'].apply(cleanCand2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "def getDistrict(x):\n",
    "    if any(char.isdigit() for char in x) and \"Judge\" not in x:\n",
    "        if \"2ND \" not in x and \"TH \" not in x:\n",
    "            for word in x.split():\n",
    "                if word.isdigit():\n",
    "                    return word\n",
    "        else:\n",
    "            if \"2ND\" in x:\n",
    "                return \"2\"\n",
    "            else: return \"29\"\n",
    "    else:\n",
    "        return \"\"\n",
    "    \n",
    "alldf['district'] = alldf['candidate'].apply(getDistrict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "alldf.loc[alldf['district'] == '' , 'district'] = alldf['district2']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "alldf['party_detailed'] = alldf['party'].replace({'party': 'NONPARTISAN',\n",
    "                                'Democratic': 'DEMOCRAT',\n",
    "                                'DEMOCRATIC': 'DEMOCRAT',\n",
    "                                'Republican': 'REPUBLICAN',\n",
    "                                'Libertarian': 'LIBERTARIAN'})\n",
    "\n",
    "alldf['party_simplified'] = alldf['party_detailed']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>precinct</th>\n",
       "      <th>candidate</th>\n",
       "      <th>votes</th>\n",
       "      <th>county</th>\n",
       "      <th>party</th>\n",
       "      <th>race</th>\n",
       "      <th>district2</th>\n",
       "      <th>writein</th>\n",
       "      <th>candidate2</th>\n",
       "      <th>district</th>\n",
       "      <th>party_detailed</th>\n",
       "      <th>party_simplified</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [precinct, candidate, votes, county, party, race, district2, writein, candidate2, district, party_detailed, party_simplified]\n",
       "Index: []"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "index = alldf[alldf.candidate == 'STATE HOUSE 23 UNDER VOTES']\n",
    "index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "for x in index['district'].unique():\n",
    "    print(x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "index2 = sedDataframes[sedDataframes.precinct == 'President']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "ename": "AttributeError",
     "evalue": "'DataFrame' object has no attribute 'office'",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mAttributeError\u001b[0m                            Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-59-27fd4d22ba07>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mindex4\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mindex2\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mindex2\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0moffice\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mstr\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcontains\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'COURT OF APPEALS'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/pandas/core/generic.py\u001b[0m in \u001b[0;36m__getattr__\u001b[0;34m(self, name)\u001b[0m\n\u001b[1;32m   5463\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_info_axis\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_can_hold_identifiers_and_holds_name\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mname\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   5464\u001b[0m                 \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mname\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 5465\u001b[0;31m             \u001b[0;32mreturn\u001b[0m \u001b[0mobject\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m__getattribute__\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mname\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   5466\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   5467\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m__setattr__\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mname\u001b[0m\u001b[0;34m:\u001b[0m \u001b[0mstr\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mvalue\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;34m->\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mAttributeError\u001b[0m: 'DataFrame' object has no attribute 'office'"
     ]
    }
   ],
   "source": [
    "index4 = index2[index2.office.str.contains('COURT OF APPEALS')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "ename": "AttributeError",
     "evalue": "'DataFrame' object has no attribute 'office'",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mAttributeError\u001b[0m                            Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-60-02f8a4244993>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mindex3\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0malldf\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0malldf\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0moffice\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mstr\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcontains\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'JUDICIAL DISTRICT COURT'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/pandas/core/generic.py\u001b[0m in \u001b[0;36m__getattr__\u001b[0;34m(self, name)\u001b[0m\n\u001b[1;32m   5463\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_info_axis\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_can_hold_identifiers_and_holds_name\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mname\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   5464\u001b[0m                 \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mname\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 5465\u001b[0;31m             \u001b[0;32mreturn\u001b[0m \u001b[0mobject\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m__getattribute__\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mname\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   5466\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   5467\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m__setattr__\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mname\u001b[0m\u001b[0;34m:\u001b[0m \u001b[0mstr\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mvalue\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;34m->\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mAttributeError\u001b[0m: 'DataFrame' object has no attribute 'office'"
     ]
    }
   ],
   "source": [
    "index3 = alldf[alldf.office.str.contains('JUDICIAL DISTRICT COURT')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "lst = [] # cands with district\n",
    "for item in index['candidate2'].unique():\n",
    "    lst.append(item)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "alldf.loc[alldf['candidate2'].str.contains('WRITE-IN'), 'writein'] = 'TRUE'\n",
    "\n",
    "alldf.loc[alldf['candidate2'].str.contains('WRITE-IN'), 'candidate2'] = 'JIM KARLESKINT'\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "# want to drop party and candidate (replace candidate2 with 'candidate' name)\n",
    "\n",
    "alldf = alldf.drop(['party', 'candidate', 'district2'], axis=1)\n",
    "alldf = alldf.rename(columns={\"candidate2\": \"candidate\", \"race\": \"office\"})\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "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>precinct</th>\n",
       "      <th>votes</th>\n",
       "      <th>county</th>\n",
       "      <th>office</th>\n",
       "      <th>writein</th>\n",
       "      <th>candidate</th>\n",
       "      <th>district</th>\n",
       "      <th>party_detailed</th>\n",
       "      <th>party_simplified</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BS 1-1</td>\n",
       "      <td>353</td>\n",
       "      <td>Wyandotte</td>\n",
       "      <td>US SENATE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>BARBARA BOLLIER</td>\n",
       "      <td></td>\n",
       "      <td>DEMOCRAT</td>\n",
       "      <td>DEMOCRAT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BS 2-1</td>\n",
       "      <td>285</td>\n",
       "      <td>Wyandotte</td>\n",
       "      <td>US SENATE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>BARBARA BOLLIER</td>\n",
       "      <td></td>\n",
       "      <td>DEMOCRAT</td>\n",
       "      <td>DEMOCRAT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BS 3-1</td>\n",
       "      <td>405</td>\n",
       "      <td>Wyandotte</td>\n",
       "      <td>US SENATE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>BARBARA BOLLIER</td>\n",
       "      <td></td>\n",
       "      <td>DEMOCRAT</td>\n",
       "      <td>DEMOCRAT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BS 4-1</td>\n",
       "      <td>589</td>\n",
       "      <td>Wyandotte</td>\n",
       "      <td>US SENATE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>BARBARA BOLLIER</td>\n",
       "      <td></td>\n",
       "      <td>DEMOCRAT</td>\n",
       "      <td>DEMOCRAT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>DE 1-1</td>\n",
       "      <td>11</td>\n",
       "      <td>Wyandotte</td>\n",
       "      <td>US SENATE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>BARBARA BOLLIER</td>\n",
       "      <td></td>\n",
       "      <td>DEMOCRAT</td>\n",
       "      <td>DEMOCRAT</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>907858</th>\n",
       "      <td>Yates Center Ward 2</td>\n",
       "      <td>205</td>\n",
       "      <td>Woodson</td>\n",
       "      <td>United States Senate</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>ROGER MARSHALL</td>\n",
       "      <td></td>\n",
       "      <td>REPUBLICAN</td>\n",
       "      <td>REPUBLICAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>907859</th>\n",
       "      <td>Yates Center Ward 2</td>\n",
       "      <td>18</td>\n",
       "      <td>Woodson</td>\n",
       "      <td>United States Senate</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>JASON BUCKLEY</td>\n",
       "      <td></td>\n",
       "      <td>LIBERTARIAN</td>\n",
       "      <td>LIBERTARIAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>907860</th>\n",
       "      <td>Yates Center Ward 2 Exclave</td>\n",
       "      <td>0</td>\n",
       "      <td>Woodson</td>\n",
       "      <td>United States Senate</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>BARBARA BOLLIER</td>\n",
       "      <td></td>\n",
       "      <td>DEMOCRAT</td>\n",
       "      <td>DEMOCRAT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>907861</th>\n",
       "      <td>Yates Center Ward 2 Exclave</td>\n",
       "      <td>0</td>\n",
       "      <td>Woodson</td>\n",
       "      <td>United States Senate</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>ROGER MARSHALL</td>\n",
       "      <td></td>\n",
       "      <td>REPUBLICAN</td>\n",
       "      <td>REPUBLICAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>907862</th>\n",
       "      <td>Yates Center Ward 2 Exclave</td>\n",
       "      <td>0</td>\n",
       "      <td>Woodson</td>\n",
       "      <td>United States Senate</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>JASON BUCKLEY</td>\n",
       "      <td></td>\n",
       "      <td>LIBERTARIAN</td>\n",
       "      <td>LIBERTARIAN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>907863 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                           precinct votes     county                office  \\\n",
       "0                            BS 1-1   353  Wyandotte             US SENATE   \n",
       "1                            BS 2-1   285  Wyandotte             US SENATE   \n",
       "2                            BS 3-1   405  Wyandotte             US SENATE   \n",
       "3                            BS 4-1   589  Wyandotte             US SENATE   \n",
       "4                            DE 1-1    11  Wyandotte             US SENATE   \n",
       "...                             ...   ...        ...                   ...   \n",
       "907858          Yates Center Ward 2   205    Woodson  United States Senate   \n",
       "907859          Yates Center Ward 2    18    Woodson  United States Senate   \n",
       "907860  Yates Center Ward 2 Exclave     0    Woodson  United States Senate   \n",
       "907861  Yates Center Ward 2 Exclave     0    Woodson  United States Senate   \n",
       "907862  Yates Center Ward 2 Exclave     0    Woodson  United States Senate   \n",
       "\n",
       "       writein        candidate district party_detailed party_simplified  \n",
       "0        FALSE  BARBARA BOLLIER                DEMOCRAT         DEMOCRAT  \n",
       "1        FALSE  BARBARA BOLLIER                DEMOCRAT         DEMOCRAT  \n",
       "2        FALSE  BARBARA BOLLIER                DEMOCRAT         DEMOCRAT  \n",
       "3        FALSE  BARBARA BOLLIER                DEMOCRAT         DEMOCRAT  \n",
       "4        FALSE  BARBARA BOLLIER                DEMOCRAT         DEMOCRAT  \n",
       "...        ...              ...      ...            ...              ...  \n",
       "907858   FALSE   ROGER MARSHALL              REPUBLICAN       REPUBLICAN  \n",
       "907859   FALSE    JASON BUCKLEY             LIBERTARIAN      LIBERTARIAN  \n",
       "907860   FALSE  BARBARA BOLLIER                DEMOCRAT         DEMOCRAT  \n",
       "907861   FALSE   ROGER MARSHALL              REPUBLICAN       REPUBLICAN  \n",
       "907862   FALSE    JASON BUCKLEY             LIBERTARIAN      LIBERTARIAN  \n",
       "\n",
       "[907863 rows x 9 columns]"
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "alldf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "# clean office \n",
    "alldf['office'] = alldf['office'].astype(str)\n",
    "\n",
    "def cleanOffice(x):\n",
    "    if '29TH DISTRICT JUDGE DIV 1' in x:\n",
    "        return 'JUDICIAL DISTRICT COURT'\n",
    "    elif 'Kansas Senate' in x:\n",
    "        return 'STATE SENATE'\n",
    "    elif 'Court of Appeals' in x: \n",
    "        return 'COURT OF APPEALS'\n",
    "    elif 'Supreme Court' in x:\n",
    "        return 'SUPREME COURT'\n",
    "    elif 'Kansas House of Representatives' in x:\n",
    "        return 'STATE HOUSE'\n",
    "    elif 'President' in x:\n",
    "        return 'US PRESIDENT'\n",
    "    elif 'United States House of Representatives' in x:\n",
    "        return 'US HOUSE'\n",
    "    elif 'United States Senate' in x: \n",
    "        return 'US SENATE'\n",
    "    elif 'race' in x or 'nan' in x:\n",
    "        return 'COURT OF APPEALS'\n",
    "    else:\n",
    "        return x\n",
    "    \n",
    "alldf['office'] = alldf['office'].apply(cleanOffice)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "alldf['candidate'] = alldf['candidate'].replace({'GORDON G ATCHESON - NO': 'G GORDON ATCHESON - NO'})\n",
    "alldf['candidate'] = alldf['candidate'].replace({'GORDON G ATCHESON - YES': 'G GORDON ATCHESON - YES'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "US SENATE\n",
      "COURT OF APPEALS\n",
      "PRESIDENT\n",
      "US HOUSE\n",
      "STATE SENATE\n",
      "STATE HOUSE\n",
      "JUDICIAL DISTRICT COURT\n",
      "SUPREME COURT\n",
      "US PRESIDENT\n"
     ]
    }
   ],
   "source": [
    "for item in alldf.office.unique():\n",
    "    print(item)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "alldf.loc[alldf['candidate'].str.contains('ERIC S ROSEN'), 'district'] = '4'\n",
    "\n",
    "alldf.loc[alldf['candidate'].str.contains('SARAH E WARNER'), 'district'] = '4'\n",
    "alldf.loc[alldf['candidate'].str.contains('DAVID E BRUNS'), 'district'] = '6'\n",
    "alldf.loc[alldf['candidate'].str.contains('G GORDON ATCHESON'), 'district'] = '8'\n",
    "alldf.loc[alldf['candidate'].str.contains('KAREN M ARNOLD-BURGER'), 'district'] = '9'\n",
    "alldf.loc[alldf['candidate'].str.contains('KATHRYN GARDNER'), 'district'] = '14'\n",
    "\n",
    "alldf.loc[alldf['office'].str.contains('SUPREME COURT'), 'party_detailed'] = 'NONPARTISAN'\n",
    "alldf.loc[alldf['office'].str.contains('SUPREME COURT'), 'party_simplified'] = 'NONPARTISAN'\n",
    "alldf.loc[alldf['office'].str.contains('COURT OF APPEALS'), 'party_detailed'] = 'NONPARTISAN'\n",
    "alldf.loc[alldf['office'].str.contains('COURT OF APPEALS'), 'party_simplified'] = 'NONPARTISAN'\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "US SENATE\n",
      "COURT OF APPEALS\n",
      "PRESIDENT\n",
      "US HOUSE\n",
      "STATE SENATE\n",
      "STATE HOUSE\n",
      "JUDICIAL DISTRICT COURT\n",
      "SUPREME COURT\n",
      "US PRESIDENT\n"
     ]
    }
   ],
   "source": [
    "for item in alldf.office.unique():\n",
    "    print(item)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "alldf[\"state\"] = 'KANSAS'\n",
    "alldf[\"state_po\"] = 'KS'\n",
    "alldf[\"state_fips\"] = '20'\n",
    "alldf[\"state_cen\"] = '47'\n",
    "alldf[\"state_ic\"] = '32'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [],
   "source": [
    "alldf = alldf.rename(columns={\"county\": \"county_name\"})\n",
    "alldf['county_name'] = alldf['county_name'].str.upper() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [],
   "source": [
    "fips = pd.read_csv('/Volumes/MEDSL/2020-precincts/help-files/county-fips-codes.csv')\n",
    "\n",
    "fips['state'] = fips['state'].str.upper()\n",
    "fips = fips.applymap(str)\n",
    "alldf=alldf.applymap(lambda x: x.strip() if type(x)==str else x)\n",
    "alldf = pd.merge(alldf, fips, on = ['state','county_name'], \n",
    "             how = 'left')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* mode (DONE)\n",
    "* stage (DONE)\n",
    "* year (DONE)\n",
    "* jusrisdiction name (DONE)\n",
    "* jurisdiction fips (DONE)\n",
    "* special (DONE)\n",
    "* date (DONE)\n",
    "* readme_check (DONE)\n",
    "* fix distric (pad) (DONE)\n",
    "* dataverse"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "alldf['mode'] = 'TOTAL'\n",
    "alldf[\"stage\"] = \"GEN\"\n",
    "alldf[\"date\"] = \"2020-11-03\"\n",
    "alldf[\"year\"] = \"2020\"\n",
    "alldf[\"special\"] = \"FALSE\"\n",
    "alldf['readme_check'] = \"FALSE\"\n",
    "\n",
    "alldf['jurisdiction_name'] = alldf['county_name']\n",
    "alldf['jurisdiction_fips'] = alldf['county_fips']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "alldf[\"district\"]= alldf[\"district\"].str.zfill(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [],
   "source": [
    "# change 'PRESIDENT' in office to 'US PRESIDENT'\n",
    "\n",
    "alldf['office'] = alldf['office'].replace({'PRESIDENT': 'US PRESIDENT'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [],
   "source": [
    "alldf['candidate'] = alldf['candidate'].replace({'WRITEIN TOTAL': 'WRITEIN'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [],
   "source": [
    "def getDataverse(x):\n",
    "    if 'US PRESIDENT' in x:\n",
    "        return 'PRESIDENT'\n",
    "    elif 'US SENATE' in x:\n",
    "        return 'SENATE'\n",
    "    elif 'US HOUSE' in x:\n",
    "        return 'HOUSE'\n",
    "    elif 'STATE' in x or 'COURT' in x:\n",
    "        return 'STATE'\n",
    "    elif 'JUDICIAL DISTRICT COURT' in x:\n",
    "        return 'LOCAL'\n",
    "    else:\n",
    "        return x\n",
    "    \n",
    "alldf['dataverse'] = alldf['office'].apply(getDataverse)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [],
   "source": [
    "# change '000' in district to ' '\n",
    "\n",
    "alldf['district'] = alldf['district'].replace({'000': ''})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [],
   "source": [
    "alldf['votes'] = alldf['votes'].astype(str).replace('\\.0', '', regex=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [],
   "source": [
    "alldf['votes'] = alldf['votes'].astype(str).replace('', '0', regex=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [],
   "source": [
    "alldf['votes'] = alldf['votes'].replace({'nan': 0})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [],
   "source": [
    "alldf['votes'] = alldf['votes'].astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [],
   "source": [
    "alldf['magnitude'] = 1\n",
    "#alldf['precinct'] = alldf['precinct'].astype(str)\n",
    "alldf['precinct'] = alldf['precinct'].str.strip()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [],
   "source": [
    "#alldf['precinct'] = alldf['precinct'].astype(str)\n",
    "alldf['precinct'] = alldf['precinct'].str.strip()\n",
    "alldf['precinct'] = alldf['precinct'].str.upper()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [],
   "source": [
    "alldf=alldf[[\"precinct\", \"office\", \"party_detailed\", \"party_simplified\", \"mode\", \"votes\", \"county_name\", \"county_fips\", \"jurisdiction_name\",\n",
    "                      \"jurisdiction_fips\", \"candidate\", \"district\", \"dataverse\", \"year\", \"stage\", \"state\", \"special\", \"writein\", \"state_po\",\n",
    "                      \"state_fips\", \"state_cen\", \"state_ic\", \"date\", \"readme_check\", \"magnitude\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "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>precinct</th>\n",
       "      <th>office</th>\n",
       "      <th>party_detailed</th>\n",
       "      <th>party_simplified</th>\n",
       "      <th>mode</th>\n",
       "      <th>votes</th>\n",
       "      <th>county_name</th>\n",
       "      <th>county_fips</th>\n",
       "      <th>jurisdiction_name</th>\n",
       "      <th>jurisdiction_fips</th>\n",
       "      <th>...</th>\n",
       "      <th>state</th>\n",
       "      <th>special</th>\n",
       "      <th>writein</th>\n",
       "      <th>state_po</th>\n",
       "      <th>state_fips</th>\n",
       "      <th>state_cen</th>\n",
       "      <th>state_ic</th>\n",
       "      <th>date</th>\n",
       "      <th>readme_check</th>\n",
       "      <th>magnitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BS 1-1</td>\n",
       "      <td>US SENATE</td>\n",
       "      <td>DEMOCRAT</td>\n",
       "      <td>DEMOCRAT</td>\n",
       "      <td>TOTAL</td>\n",
       "      <td>353</td>\n",
       "      <td>WYANDOTTE</td>\n",
       "      <td>20209</td>\n",
       "      <td>WYANDOTTE</td>\n",
       "      <td>20209</td>\n",
       "      <td>...</td>\n",
       "      <td>KANSAS</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>KS</td>\n",
       "      <td>20</td>\n",
       "      <td>47</td>\n",
       "      <td>32</td>\n",
       "      <td>2020-11-03</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BS 2-1</td>\n",
       "      <td>US SENATE</td>\n",
       "      <td>DEMOCRAT</td>\n",
       "      <td>DEMOCRAT</td>\n",
       "      <td>TOTAL</td>\n",
       "      <td>285</td>\n",
       "      <td>WYANDOTTE</td>\n",
       "      <td>20209</td>\n",
       "      <td>WYANDOTTE</td>\n",
       "      <td>20209</td>\n",
       "      <td>...</td>\n",
       "      <td>KANSAS</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>KS</td>\n",
       "      <td>20</td>\n",
       "      <td>47</td>\n",
       "      <td>32</td>\n",
       "      <td>2020-11-03</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BS 3-1</td>\n",
       "      <td>US SENATE</td>\n",
       "      <td>DEMOCRAT</td>\n",
       "      <td>DEMOCRAT</td>\n",
       "      <td>TOTAL</td>\n",
       "      <td>405</td>\n",
       "      <td>WYANDOTTE</td>\n",
       "      <td>20209</td>\n",
       "      <td>WYANDOTTE</td>\n",
       "      <td>20209</td>\n",
       "      <td>...</td>\n",
       "      <td>KANSAS</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>KS</td>\n",
       "      <td>20</td>\n",
       "      <td>47</td>\n",
       "      <td>32</td>\n",
       "      <td>2020-11-03</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BS 4-1</td>\n",
       "      <td>US SENATE</td>\n",
       "      <td>DEMOCRAT</td>\n",
       "      <td>DEMOCRAT</td>\n",
       "      <td>TOTAL</td>\n",
       "      <td>589</td>\n",
       "      <td>WYANDOTTE</td>\n",
       "      <td>20209</td>\n",
       "      <td>WYANDOTTE</td>\n",
       "      <td>20209</td>\n",
       "      <td>...</td>\n",
       "      <td>KANSAS</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>KS</td>\n",
       "      <td>20</td>\n",
       "      <td>47</td>\n",
       "      <td>32</td>\n",
       "      <td>2020-11-03</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>DE 1-1</td>\n",
       "      <td>US SENATE</td>\n",
       "      <td>DEMOCRAT</td>\n",
       "      <td>DEMOCRAT</td>\n",
       "      <td>TOTAL</td>\n",
       "      <td>11</td>\n",
       "      <td>WYANDOTTE</td>\n",
       "      <td>20209</td>\n",
       "      <td>WYANDOTTE</td>\n",
       "      <td>20209</td>\n",
       "      <td>...</td>\n",
       "      <td>KANSAS</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>KS</td>\n",
       "      <td>20</td>\n",
       "      <td>47</td>\n",
       "      <td>32</td>\n",
       "      <td>2020-11-03</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>907858</th>\n",
       "      <td>Yates Center Ward 2</td>\n",
       "      <td>US SENATE</td>\n",
       "      <td>REPUBLICAN</td>\n",
       "      <td>REPUBLICAN</td>\n",
       "      <td>TOTAL</td>\n",
       "      <td>205</td>\n",
       "      <td>WOODSON</td>\n",
       "      <td>20207</td>\n",
       "      <td>WOODSON</td>\n",
       "      <td>20207</td>\n",
       "      <td>...</td>\n",
       "      <td>KANSAS</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>KS</td>\n",
       "      <td>20</td>\n",
       "      <td>47</td>\n",
       "      <td>32</td>\n",
       "      <td>2020-11-03</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>907859</th>\n",
       "      <td>Yates Center Ward 2</td>\n",
       "      <td>US SENATE</td>\n",
       "      <td>LIBERTARIAN</td>\n",
       "      <td>LIBERTARIAN</td>\n",
       "      <td>TOTAL</td>\n",
       "      <td>18</td>\n",
       "      <td>WOODSON</td>\n",
       "      <td>20207</td>\n",
       "      <td>WOODSON</td>\n",
       "      <td>20207</td>\n",
       "      <td>...</td>\n",
       "      <td>KANSAS</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>KS</td>\n",
       "      <td>20</td>\n",
       "      <td>47</td>\n",
       "      <td>32</td>\n",
       "      <td>2020-11-03</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>907860</th>\n",
       "      <td>Yates Center Ward 2 Exclave</td>\n",
       "      <td>US SENATE</td>\n",
       "      <td>DEMOCRAT</td>\n",
       "      <td>DEMOCRAT</td>\n",
       "      <td>TOTAL</td>\n",
       "      <td>0</td>\n",
       "      <td>WOODSON</td>\n",
       "      <td>20207</td>\n",
       "      <td>WOODSON</td>\n",
       "      <td>20207</td>\n",
       "      <td>...</td>\n",
       "      <td>KANSAS</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>KS</td>\n",
       "      <td>20</td>\n",
       "      <td>47</td>\n",
       "      <td>32</td>\n",
       "      <td>2020-11-03</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>907861</th>\n",
       "      <td>Yates Center Ward 2 Exclave</td>\n",
       "      <td>US SENATE</td>\n",
       "      <td>REPUBLICAN</td>\n",
       "      <td>REPUBLICAN</td>\n",
       "      <td>TOTAL</td>\n",
       "      <td>0</td>\n",
       "      <td>WOODSON</td>\n",
       "      <td>20207</td>\n",
       "      <td>WOODSON</td>\n",
       "      <td>20207</td>\n",
       "      <td>...</td>\n",
       "      <td>KANSAS</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>KS</td>\n",
       "      <td>20</td>\n",
       "      <td>47</td>\n",
       "      <td>32</td>\n",
       "      <td>2020-11-03</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>907862</th>\n",
       "      <td>Yates Center Ward 2 Exclave</td>\n",
       "      <td>US SENATE</td>\n",
       "      <td>LIBERTARIAN</td>\n",
       "      <td>LIBERTARIAN</td>\n",
       "      <td>TOTAL</td>\n",
       "      <td>0</td>\n",
       "      <td>WOODSON</td>\n",
       "      <td>20207</td>\n",
       "      <td>WOODSON</td>\n",
       "      <td>20207</td>\n",
       "      <td>...</td>\n",
       "      <td>KANSAS</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>KS</td>\n",
       "      <td>20</td>\n",
       "      <td>47</td>\n",
       "      <td>32</td>\n",
       "      <td>2020-11-03</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>907863 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                           precinct     office party_detailed  \\\n",
       "0                            BS 1-1  US SENATE       DEMOCRAT   \n",
       "1                            BS 2-1  US SENATE       DEMOCRAT   \n",
       "2                            BS 3-1  US SENATE       DEMOCRAT   \n",
       "3                            BS 4-1  US SENATE       DEMOCRAT   \n",
       "4                            DE 1-1  US SENATE       DEMOCRAT   \n",
       "...                             ...        ...            ...   \n",
       "907858          Yates Center Ward 2  US SENATE     REPUBLICAN   \n",
       "907859          Yates Center Ward 2  US SENATE    LIBERTARIAN   \n",
       "907860  Yates Center Ward 2 Exclave  US SENATE       DEMOCRAT   \n",
       "907861  Yates Center Ward 2 Exclave  US SENATE     REPUBLICAN   \n",
       "907862  Yates Center Ward 2 Exclave  US SENATE    LIBERTARIAN   \n",
       "\n",
       "       party_simplified   mode  votes county_name county_fips  \\\n",
       "0              DEMOCRAT  TOTAL    353   WYANDOTTE       20209   \n",
       "1              DEMOCRAT  TOTAL    285   WYANDOTTE       20209   \n",
       "2              DEMOCRAT  TOTAL    405   WYANDOTTE       20209   \n",
       "3              DEMOCRAT  TOTAL    589   WYANDOTTE       20209   \n",
       "4              DEMOCRAT  TOTAL     11   WYANDOTTE       20209   \n",
       "...                 ...    ...    ...         ...         ...   \n",
       "907858       REPUBLICAN  TOTAL    205     WOODSON       20207   \n",
       "907859      LIBERTARIAN  TOTAL     18     WOODSON       20207   \n",
       "907860         DEMOCRAT  TOTAL      0     WOODSON       20207   \n",
       "907861       REPUBLICAN  TOTAL      0     WOODSON       20207   \n",
       "907862      LIBERTARIAN  TOTAL      0     WOODSON       20207   \n",
       "\n",
       "       jurisdiction_name jurisdiction_fips  ...   state special writein  \\\n",
       "0              WYANDOTTE             20209  ...  KANSAS   FALSE   FALSE   \n",
       "1              WYANDOTTE             20209  ...  KANSAS   FALSE   FALSE   \n",
       "2              WYANDOTTE             20209  ...  KANSAS   FALSE   FALSE   \n",
       "3              WYANDOTTE             20209  ...  KANSAS   FALSE   FALSE   \n",
       "4              WYANDOTTE             20209  ...  KANSAS   FALSE   FALSE   \n",
       "...                  ...               ...  ...     ...     ...     ...   \n",
       "907858           WOODSON             20207  ...  KANSAS   FALSE   FALSE   \n",
       "907859           WOODSON             20207  ...  KANSAS   FALSE   FALSE   \n",
       "907860           WOODSON             20207  ...  KANSAS   FALSE   FALSE   \n",
       "907861           WOODSON             20207  ...  KANSAS   FALSE   FALSE   \n",
       "907862           WOODSON             20207  ...  KANSAS   FALSE   FALSE   \n",
       "\n",
       "       state_po state_fips state_cen state_ic        date readme_check  \\\n",
       "0            KS         20        47       32  2020-11-03        FALSE   \n",
       "1            KS         20        47       32  2020-11-03        FALSE   \n",
       "2            KS         20        47       32  2020-11-03        FALSE   \n",
       "3            KS         20        47       32  2020-11-03        FALSE   \n",
       "4            KS         20        47       32  2020-11-03        FALSE   \n",
       "...         ...        ...       ...      ...         ...          ...   \n",
       "907858       KS         20        47       32  2020-11-03        FALSE   \n",
       "907859       KS         20        47       32  2020-11-03        FALSE   \n",
       "907860       KS         20        47       32  2020-11-03        FALSE   \n",
       "907861       KS         20        47       32  2020-11-03        FALSE   \n",
       "907862       KS         20        47       32  2020-11-03        FALSE   \n",
       "\n",
       "       magnitude  \n",
       "0              1  \n",
       "1              1  \n",
       "2              1  \n",
       "3              1  \n",
       "4              1  \n",
       "...          ...  \n",
       "907858         1  \n",
       "907859         1  \n",
       "907860         1  \n",
       "907861         1  \n",
       "907862         1  \n",
       "\n",
       "[907863 rows x 25 columns]"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "alldf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "for x in alldf.candidate.unique():\n",
    "    if 'VOTES' in x:\n",
    "        print(x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [],
   "source": [
    "alldf.loc[alldf['candidate'].str.contains('STATE') & alldf['candidate'].str.contains('OVER VOTES'), 'candidate'] = 'OVERVOTES'\n",
    "alldf.loc[alldf['candidate'].str.contains('STATE') & alldf['candidate'].str.contains('UNDER VOTES'), 'candidate'] = 'UNDERVOTES'\n",
    "\n",
    "alldf.loc[alldf['candidate'].str.contains('SARAH E WARNER - OVER VOTES'), 'candidate'] = 'SARAH E WARNER - OVERVOTES'\n",
    "alldf.loc[alldf['candidate'].str.contains('SARAH E WARNER - UNDER VOTES'), 'candidate'] = 'SARAH E WARNER - UNDERVOTES'\n",
    "\n",
    "alldf.loc[alldf['candidate'].str.contains('DAVID E BRUNS - OVER VOTES'), 'candidate'] = 'DAVID E BRUNS - OVERVOTES'\n",
    "alldf.loc[alldf['candidate'].str.contains('DAVID E BRUNS - UNDER VOTES'), 'candidate'] = 'DAVID E BRUNS - UNDERVOTES'\n",
    "\n",
    "alldf.loc[alldf['candidate'].str.contains('GORDON G ATCHESON - OVER VOTES'), 'candidate'] = 'GORDON G ATCHESON - OVERVOTES'\n",
    "alldf.loc[alldf['candidate'].str.contains('GORDON G ATCHESON - UNDER VOTES'), 'candidate'] = 'GORDON G ATCHESON - UNDERVOTES'\n",
    "\n",
    "alldf.loc[alldf['candidate'].str.contains('KAREN M ARNOLD-BURGER - OVER VOTES'), 'candidate'] = 'KAREN M ARNOLD-BURGER - OVERVOTES'\n",
    "alldf.loc[alldf['candidate'].str.contains('KAREN M ARNOLD-BURGER - UNDER VOTES'), 'candidate'] = 'KAREN M ARNOLD-BURGER - UNDERVOTES'\n",
    "\n",
    "alldf.loc[alldf['candidate'].str.contains('KATHRYN GARDNER - OVER VOTES'), 'candidate'] = 'KATHRYN GARDNER - OVERVOTES'\n",
    "alldf.loc[alldf['candidate'].str.contains('KATHRYN GARDNER - UNDER VOTES'), 'candidate'] = 'KATHRYN GARDNER - UNDERVOTES'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [],
   "source": [
    "alldf.loc[alldf['office'].str.contains('US SENATE') | alldf['office'].str.contains('US PRESIDENT'), 'district'] = 'STATEWIDE'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "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>precinct</th>\n",
       "      <th>office</th>\n",
       "      <th>party_detailed</th>\n",
       "      <th>party_simplified</th>\n",
       "      <th>mode</th>\n",
       "      <th>votes</th>\n",
       "      <th>county_name</th>\n",
       "      <th>county_fips</th>\n",
       "      <th>jurisdiction_name</th>\n",
       "      <th>jurisdiction_fips</th>\n",
       "      <th>...</th>\n",
       "      <th>state</th>\n",
       "      <th>special</th>\n",
       "      <th>writein</th>\n",
       "      <th>state_po</th>\n",
       "      <th>state_fips</th>\n",
       "      <th>state_cen</th>\n",
       "      <th>state_ic</th>\n",
       "      <th>date</th>\n",
       "      <th>readme_check</th>\n",
       "      <th>magnitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>0 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [precinct, office, party_detailed, party_simplified, mode, votes, county_name, county_fips, jurisdiction_name, jurisdiction_fips, candidate, district, dataverse, year, stage, state, special, writein, state_po, state_fips, state_cen, state_ic, date, readme_check, magnitude]\n",
       "Index: []\n",
       "\n",
       "[0 rows x 25 columns]"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "alldf[alldf.isna().any(axis=1)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "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>precinct</th>\n",
       "      <th>office</th>\n",
       "      <th>party_detailed</th>\n",
       "      <th>party_simplified</th>\n",
       "      <th>mode</th>\n",
       "      <th>votes</th>\n",
       "      <th>county_name</th>\n",
       "      <th>county_fips</th>\n",
       "      <th>jurisdiction_name</th>\n",
       "      <th>jurisdiction_fips</th>\n",
       "      <th>...</th>\n",
       "      <th>state</th>\n",
       "      <th>special</th>\n",
       "      <th>writein</th>\n",
       "      <th>state_po</th>\n",
       "      <th>state_fips</th>\n",
       "      <th>state_cen</th>\n",
       "      <th>state_ic</th>\n",
       "      <th>date</th>\n",
       "      <th>readme_check</th>\n",
       "      <th>magnitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5348</th>\n",
       "      <td>101</td>\n",
       "      <td>US SENATE</td>\n",
       "      <td>DEMOCRAT</td>\n",
       "      <td>DEMOCRAT</td>\n",
       "      <td>TOTAL</td>\n",
       "      <td>595</td>\n",
       "      <td>SEDGWICK</td>\n",
       "      <td>20173</td>\n",
       "      <td>SEDGWICK</td>\n",
       "      <td>20173</td>\n",
       "      <td>...</td>\n",
       "      <td>KANSAS</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>KS</td>\n",
       "      <td>20</td>\n",
       "      <td>47</td>\n",
       "      <td>32</td>\n",
       "      <td>2020-11-03</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7236</th>\n",
       "      <td>109</td>\n",
       "      <td>SUPREME COURT</td>\n",
       "      <td>NONPARTISAN</td>\n",
       "      <td>NONPARTISAN</td>\n",
       "      <td>TOTAL</td>\n",
       "      <td>595</td>\n",
       "      <td>SEDGWICK</td>\n",
       "      <td>20173</td>\n",
       "      <td>SEDGWICK</td>\n",
       "      <td>20173</td>\n",
       "      <td>...</td>\n",
       "      <td>KANSAS</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>KS</td>\n",
       "      <td>20</td>\n",
       "      <td>47</td>\n",
       "      <td>32</td>\n",
       "      <td>2020-11-03</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8110</th>\n",
       "      <td>313</td>\n",
       "      <td>US PRESIDENT</td>\n",
       "      <td>REPUBLICAN</td>\n",
       "      <td>REPUBLICAN</td>\n",
       "      <td>TOTAL</td>\n",
       "      <td>595</td>\n",
       "      <td>SEDGWICK</td>\n",
       "      <td>20173</td>\n",
       "      <td>SEDGWICK</td>\n",
       "      <td>20173</td>\n",
       "      <td>...</td>\n",
       "      <td>KANSAS</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>KS</td>\n",
       "      <td>20</td>\n",
       "      <td>47</td>\n",
       "      <td>32</td>\n",
       "      <td>2020-11-03</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9652</th>\n",
       "      <td>101</td>\n",
       "      <td>STATE SENATE</td>\n",
       "      <td>DEMOCRAT</td>\n",
       "      <td>DEMOCRAT</td>\n",
       "      <td>TOTAL</td>\n",
       "      <td>595</td>\n",
       "      <td>SEDGWICK</td>\n",
       "      <td>20173</td>\n",
       "      <td>SEDGWICK</td>\n",
       "      <td>20173</td>\n",
       "      <td>...</td>\n",
       "      <td>KANSAS</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>KS</td>\n",
       "      <td>20</td>\n",
       "      <td>47</td>\n",
       "      <td>32</td>\n",
       "      <td>2020-11-03</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33363</th>\n",
       "      <td>216</td>\n",
       "      <td>COURT OF APPEALS</td>\n",
       "      <td>NONPARTISAN</td>\n",
       "      <td>NONPARTISAN</td>\n",
       "      <td>TOTAL</td>\n",
       "      <td>595</td>\n",
       "      <td>SEDGWICK</td>\n",
       "      <td>20173</td>\n",
       "      <td>SEDGWICK</td>\n",
       "      <td>20173</td>\n",
       "      <td>...</td>\n",
       "      <td>KANSAS</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>KS</td>\n",
       "      <td>20</td>\n",
       "      <td>47</td>\n",
       "      <td>32</td>\n",
       "      <td>2020-11-03</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33873</th>\n",
       "      <td>112</td>\n",
       "      <td>COURT OF APPEALS</td>\n",
       "      <td>NONPARTISAN</td>\n",
       "      <td>NONPARTISAN</td>\n",
       "      <td>TOTAL</td>\n",
       "      <td>595</td>\n",
       "      <td>SEDGWICK</td>\n",
       "      <td>20173</td>\n",
       "      <td>SEDGWICK</td>\n",
       "      <td>20173</td>\n",
       "      <td>...</td>\n",
       "      <td>KANSAS</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>KS</td>\n",
       "      <td>20</td>\n",
       "      <td>47</td>\n",
       "      <td>32</td>\n",
       "      <td>2020-11-03</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34764</th>\n",
       "      <td>410</td>\n",
       "      <td>COURT OF APPEALS</td>\n",
       "      <td>NONPARTISAN</td>\n",
       "      <td>NONPARTISAN</td>\n",
       "      <td>TOTAL</td>\n",
       "      <td>595</td>\n",
       "      <td>SEDGWICK</td>\n",
       "      <td>20173</td>\n",
       "      <td>SEDGWICK</td>\n",
       "      <td>20173</td>\n",
       "      <td>...</td>\n",
       "      <td>KANSAS</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>KS</td>\n",
       "      <td>20</td>\n",
       "      <td>47</td>\n",
       "      <td>32</td>\n",
       "      <td>2020-11-03</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34946</th>\n",
       "      <td>109</td>\n",
       "      <td>COURT OF APPEALS</td>\n",
       "      <td>NONPARTISAN</td>\n",
       "      <td>NONPARTISAN</td>\n",
       "      <td>TOTAL</td>\n",
       "      <td>595</td>\n",
       "      <td>SEDGWICK</td>\n",
       "      <td>20173</td>\n",
       "      <td>SEDGWICK</td>\n",
       "      <td>20173</td>\n",
       "      <td>...</td>\n",
       "      <td>KANSAS</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>KS</td>\n",
       "      <td>20</td>\n",
       "      <td>47</td>\n",
       "      <td>32</td>\n",
       "      <td>2020-11-03</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>171361</th>\n",
       "      <td>Shawnee Ward 2 Precinct 03</td>\n",
       "      <td>COURT OF APPEALS</td>\n",
       "      <td>NONPARTISAN</td>\n",
       "      <td>NONPARTISAN</td>\n",
       "      <td>TOTAL</td>\n",
       "      <td>595</td>\n",
       "      <td>JOHNSON</td>\n",
       "      <td>20091</td>\n",
       "      <td>JOHNSON</td>\n",
       "      <td>20091</td>\n",
       "      <td>...</td>\n",
       "      <td>KANSAS</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>KS</td>\n",
       "      <td>20</td>\n",
       "      <td>47</td>\n",
       "      <td>32</td>\n",
       "      <td>2020-11-03</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>809655</th>\n",
       "      <td>Overland Park Ward 4 Precinct 04</td>\n",
       "      <td>US PRESIDENT</td>\n",
       "      <td>REPUBLICAN</td>\n",
       "      <td>REPUBLICAN</td>\n",
       "      <td>TOTAL</td>\n",
       "      <td>595</td>\n",
       "      <td>JOHNSON</td>\n",
       "      <td>20091</td>\n",
       "      <td>JOHNSON</td>\n",
       "      <td>20091</td>\n",
       "      <td>...</td>\n",
       "      <td>KANSAS</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>KS</td>\n",
       "      <td>20</td>\n",
       "      <td>47</td>\n",
       "      <td>32</td>\n",
       "      <td>2020-11-03</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>809675</th>\n",
       "      <td>Overland Park Ward 5 Precinct 05</td>\n",
       "      <td>US PRESIDENT</td>\n",
       "      <td>REPUBLICAN</td>\n",
       "      <td>REPUBLICAN</td>\n",
       "      <td>TOTAL</td>\n",
       "      <td>595</td>\n",
       "      <td>JOHNSON</td>\n",
       "      <td>20091</td>\n",
       "      <td>JOHNSON</td>\n",
       "      <td>20091</td>\n",
       "      <td>...</td>\n",
       "      <td>KANSAS</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>KS</td>\n",
       "      <td>20</td>\n",
       "      <td>47</td>\n",
       "      <td>32</td>\n",
       "      <td>2020-11-03</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>843615</th>\n",
       "      <td>Overland Park Ward 6 Precinct 04</td>\n",
       "      <td>US SENATE</td>\n",
       "      <td>REPUBLICAN</td>\n",
       "      <td>REPUBLICAN</td>\n",
       "      <td>TOTAL</td>\n",
       "      <td>595</td>\n",
       "      <td>JOHNSON</td>\n",
       "      <td>20091</td>\n",
       "      <td>JOHNSON</td>\n",
       "      <td>20091</td>\n",
       "      <td>...</td>\n",
       "      <td>KANSAS</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>KS</td>\n",
       "      <td>20</td>\n",
       "      <td>47</td>\n",
       "      <td>32</td>\n",
       "      <td>2020-11-03</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>850626</th>\n",
       "      <td>Lawrence Precinct 31</td>\n",
       "      <td>COURT OF APPEALS</td>\n",
       "      <td>NONPARTISAN</td>\n",
       "      <td>NONPARTISAN</td>\n",
       "      <td>TOTAL</td>\n",
       "      <td>595</td>\n",
       "      <td>DOUGLAS</td>\n",
       "      <td>20045</td>\n",
       "      <td>DOUGLAS</td>\n",
       "      <td>20045</td>\n",
       "      <td>...</td>\n",
       "      <td>KANSAS</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>KS</td>\n",
       "      <td>20</td>\n",
       "      <td>47</td>\n",
       "      <td>32</td>\n",
       "      <td>2020-11-03</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>868492</th>\n",
       "      <td>Liberal Ward 6 Precinct 1</td>\n",
       "      <td>COURT OF APPEALS</td>\n",
       "      <td>NONPARTISAN</td>\n",
       "      <td>NONPARTISAN</td>\n",
       "      <td>TOTAL</td>\n",
       "      <td>595</td>\n",
       "      <td>SEWARD</td>\n",
       "      <td>20175</td>\n",
       "      <td>SEWARD</td>\n",
       "      <td>20175</td>\n",
       "      <td>...</td>\n",
       "      <td>KANSAS</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>KS</td>\n",
       "      <td>20</td>\n",
       "      <td>47</td>\n",
       "      <td>32</td>\n",
       "      <td>2020-11-03</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>874946</th>\n",
       "      <td>Colby Ward 4</td>\n",
       "      <td>STATE HOUSE</td>\n",
       "      <td>REPUBLICAN</td>\n",
       "      <td>REPUBLICAN</td>\n",
       "      <td>TOTAL</td>\n",
       "      <td>595</td>\n",
       "      <td>THOMAS</td>\n",
       "      <td>20193</td>\n",
       "      <td>THOMAS</td>\n",
       "      <td>20193</td>\n",
       "      <td>...</td>\n",
       "      <td>KANSAS</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>KS</td>\n",
       "      <td>20</td>\n",
       "      <td>47</td>\n",
       "      <td>32</td>\n",
       "      <td>2020-11-03</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>15 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                precinct            office party_detailed  \\\n",
       "5348                                 101         US SENATE       DEMOCRAT   \n",
       "7236                                 109     SUPREME COURT    NONPARTISAN   \n",
       "8110                                 313      US PRESIDENT     REPUBLICAN   \n",
       "9652                                 101      STATE SENATE       DEMOCRAT   \n",
       "33363                                216  COURT OF APPEALS    NONPARTISAN   \n",
       "33873                                112  COURT OF APPEALS    NONPARTISAN   \n",
       "34764                                410  COURT OF APPEALS    NONPARTISAN   \n",
       "34946                                109  COURT OF APPEALS    NONPARTISAN   \n",
       "171361        Shawnee Ward 2 Precinct 03  COURT OF APPEALS    NONPARTISAN   \n",
       "809655  Overland Park Ward 4 Precinct 04      US PRESIDENT     REPUBLICAN   \n",
       "809675  Overland Park Ward 5 Precinct 05      US PRESIDENT     REPUBLICAN   \n",
       "843615  Overland Park Ward 6 Precinct 04         US SENATE     REPUBLICAN   \n",
       "850626              Lawrence Precinct 31  COURT OF APPEALS    NONPARTISAN   \n",
       "868492         Liberal Ward 6 Precinct 1  COURT OF APPEALS    NONPARTISAN   \n",
       "874946                      Colby Ward 4       STATE HOUSE     REPUBLICAN   \n",
       "\n",
       "       party_simplified   mode  votes county_name county_fips  \\\n",
       "5348           DEMOCRAT  TOTAL    595    SEDGWICK       20173   \n",
       "7236        NONPARTISAN  TOTAL    595    SEDGWICK       20173   \n",
       "8110         REPUBLICAN  TOTAL    595    SEDGWICK       20173   \n",
       "9652           DEMOCRAT  TOTAL    595    SEDGWICK       20173   \n",
       "33363       NONPARTISAN  TOTAL    595    SEDGWICK       20173   \n",
       "33873       NONPARTISAN  TOTAL    595    SEDGWICK       20173   \n",
       "34764       NONPARTISAN  TOTAL    595    SEDGWICK       20173   \n",
       "34946       NONPARTISAN  TOTAL    595    SEDGWICK       20173   \n",
       "171361      NONPARTISAN  TOTAL    595     JOHNSON       20091   \n",
       "809655       REPUBLICAN  TOTAL    595     JOHNSON       20091   \n",
       "809675       REPUBLICAN  TOTAL    595     JOHNSON       20091   \n",
       "843615       REPUBLICAN  TOTAL    595     JOHNSON       20091   \n",
       "850626      NONPARTISAN  TOTAL    595     DOUGLAS       20045   \n",
       "868492      NONPARTISAN  TOTAL    595      SEWARD       20175   \n",
       "874946       REPUBLICAN  TOTAL    595      THOMAS       20193   \n",
       "\n",
       "       jurisdiction_name jurisdiction_fips  ...   state special writein  \\\n",
       "5348            SEDGWICK             20173  ...  KANSAS   FALSE   FALSE   \n",
       "7236            SEDGWICK             20173  ...  KANSAS   FALSE   FALSE   \n",
       "8110            SEDGWICK             20173  ...  KANSAS   FALSE   FALSE   \n",
       "9652            SEDGWICK             20173  ...  KANSAS   FALSE   FALSE   \n",
       "33363           SEDGWICK             20173  ...  KANSAS   FALSE   FALSE   \n",
       "33873           SEDGWICK             20173  ...  KANSAS   FALSE   FALSE   \n",
       "34764           SEDGWICK             20173  ...  KANSAS   FALSE   FALSE   \n",
       "34946           SEDGWICK             20173  ...  KANSAS   FALSE   FALSE   \n",
       "171361           JOHNSON             20091  ...  KANSAS   FALSE   FALSE   \n",
       "809655           JOHNSON             20091  ...  KANSAS   FALSE   FALSE   \n",
       "809675           JOHNSON             20091  ...  KANSAS   FALSE   FALSE   \n",
       "843615           JOHNSON             20091  ...  KANSAS   FALSE   FALSE   \n",
       "850626           DOUGLAS             20045  ...  KANSAS   FALSE   FALSE   \n",
       "868492            SEWARD             20175  ...  KANSAS   FALSE   FALSE   \n",
       "874946            THOMAS             20193  ...  KANSAS   FALSE   FALSE   \n",
       "\n",
       "       state_po state_fips state_cen state_ic        date readme_check  \\\n",
       "5348         KS         20        47       32  2020-11-03        FALSE   \n",
       "7236         KS         20        47       32  2020-11-03        FALSE   \n",
       "8110         KS         20        47       32  2020-11-03        FALSE   \n",
       "9652         KS         20        47       32  2020-11-03        FALSE   \n",
       "33363        KS         20        47       32  2020-11-03        FALSE   \n",
       "33873        KS         20        47       32  2020-11-03        FALSE   \n",
       "34764        KS         20        47       32  2020-11-03        FALSE   \n",
       "34946        KS         20        47       32  2020-11-03        FALSE   \n",
       "171361       KS         20        47       32  2020-11-03        FALSE   \n",
       "809655       KS         20        47       32  2020-11-03        FALSE   \n",
       "809675       KS         20        47       32  2020-11-03        FALSE   \n",
       "843615       KS         20        47       32  2020-11-03        FALSE   \n",
       "850626       KS         20        47       32  2020-11-03        FALSE   \n",
       "868492       KS         20        47       32  2020-11-03        FALSE   \n",
       "874946       KS         20        47       32  2020-11-03        FALSE   \n",
       "\n",
       "       magnitude  \n",
       "5348           1  \n",
       "7236           1  \n",
       "8110           1  \n",
       "9652           1  \n",
       "33363          1  \n",
       "33873          1  \n",
       "34764          1  \n",
       "34946          1  \n",
       "171361         1  \n",
       "809655         1  \n",
       "809675         1  \n",
       "843615         1  \n",
       "850626         1  \n",
       "868492         1  \n",
       "874946         1  \n",
       "\n",
       "[15 rows x 25 columns]"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "alldf.loc[alldf.votes == 595]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [],
   "source": [
    "for x in alldf['candidate'].unique():\n",
    "    if \"UNNAMED\" in x: \n",
    "        print(x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>precinct</th>\n",
       "      <th>office</th>\n",
       "      <th>party_detailed</th>\n",
       "      <th>party_simplified</th>\n",
       "      <th>mode</th>\n",
       "      <th>votes</th>\n",
       "      <th>county_name</th>\n",
       "      <th>county_fips</th>\n",
       "      <th>jurisdiction_name</th>\n",
       "      <th>jurisdiction_fips</th>\n",
       "      <th>...</th>\n",
       "      <th>state</th>\n",
       "      <th>special</th>\n",
       "      <th>writein</th>\n",
       "      <th>state_po</th>\n",
       "      <th>state_fips</th>\n",
       "      <th>state_cen</th>\n",
       "      <th>state_ic</th>\n",
       "      <th>date</th>\n",
       "      <th>readme_check</th>\n",
       "      <th>magnitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>35774</th>\n",
       "      <td>Ward 2 Precinct 12</td>\n",
       "      <td>COURT OF APPEALS</td>\n",
       "      <td>NONPARTISAN</td>\n",
       "      <td>NONPARTISAN</td>\n",
       "      <td>TOTAL</td>\n",
       "      <td>0</td>\n",
       "      <td>SHAWNEE</td>\n",
       "      <td>20177</td>\n",
       "      <td>SHAWNEE</td>\n",
       "      <td>20177</td>\n",
       "      <td>...</td>\n",
       "      <td>KANSAS</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>KS</td>\n",
       "      <td>20</td>\n",
       "      <td>47</td>\n",
       "      <td>32</td>\n",
       "      <td>2020-11-03</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35908</th>\n",
       "      <td>1 East Rossville</td>\n",
       "      <td>COURT OF APPEALS</td>\n",
       "      <td>NONPARTISAN</td>\n",
       "      <td>NONPARTISAN</td>\n",
       "      <td>TOTAL</td>\n",
       "      <td>0</td>\n",
       "      <td>SHAWNEE</td>\n",
       "      <td>20177</td>\n",
       "      <td>SHAWNEE</td>\n",
       "      <td>20177</td>\n",
       "      <td>...</td>\n",
       "      <td>KANSAS</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>KS</td>\n",
       "      <td>20</td>\n",
       "      <td>47</td>\n",
       "      <td>32</td>\n",
       "      <td>2020-11-03</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35909</th>\n",
       "      <td>2 West Rossville</td>\n",
       "      <td>COURT OF APPEALS</td>\n",
       "      <td>NONPARTISAN</td>\n",
       "      <td>NONPARTISAN</td>\n",
       "      <td>TOTAL</td>\n",
       "      <td>0</td>\n",
       "      <td>SHAWNEE</td>\n",
       "      <td>20177</td>\n",
       "      <td>SHAWNEE</td>\n",
       "      <td>20177</td>\n",
       "      <td>...</td>\n",
       "      <td>KANSAS</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>KS</td>\n",
       "      <td>20</td>\n",
       "      <td>47</td>\n",
       "      <td>32</td>\n",
       "      <td>2020-11-03</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35910</th>\n",
       "      <td>9 East Silver Lake</td>\n",
       "      <td>COURT OF APPEALS</td>\n",
       "      <td>NONPARTISAN</td>\n",
       "      <td>NONPARTISAN</td>\n",
       "      <td>TOTAL</td>\n",
       "      <td>0</td>\n",
       "      <td>SHAWNEE</td>\n",
       "      <td>20177</td>\n",
       "      <td>SHAWNEE</td>\n",
       "      <td>20177</td>\n",
       "      <td>...</td>\n",
       "      <td>KANSAS</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>KS</td>\n",
       "      <td>20</td>\n",
       "      <td>47</td>\n",
       "      <td>32</td>\n",
       "      <td>2020-11-03</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35911</th>\n",
       "      <td>10 West Silver Lake</td>\n",
       "      <td>COURT OF APPEALS</td>\n",
       "      <td>NONPARTISAN</td>\n",
       "      <td>NONPARTISAN</td>\n",
       "      <td>TOTAL</td>\n",
       "      <td>0</td>\n",
       "      <td>SHAWNEE</td>\n",
       "      <td>20177</td>\n",
       "      <td>SHAWNEE</td>\n",
       "      <td>20177</td>\n",
       "      <td>...</td>\n",
       "      <td>KANSAS</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>KS</td>\n",
       "      <td>20</td>\n",
       "      <td>47</td>\n",
       "      <td>32</td>\n",
       "      <td>2020-11-03</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>899846</th>\n",
       "      <td>Fort Scott Ward 4 Exclave C</td>\n",
       "      <td>US SENATE</td>\n",
       "      <td>REPUBLICAN</td>\n",
       "      <td>REPUBLICAN</td>\n",
       "      <td>TOTAL</td>\n",
       "      <td>0</td>\n",
       "      <td>BOURBON</td>\n",
       "      <td>20011</td>\n",
       "      <td>BOURBON</td>\n",
       "      <td>20011</td>\n",
       "      <td>...</td>\n",
       "      <td>KANSAS</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>KS</td>\n",
       "      <td>20</td>\n",
       "      <td>47</td>\n",
       "      <td>32</td>\n",
       "      <td>2020-11-03</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>899847</th>\n",
       "      <td>Fort Scott Ward 4 Exclave C</td>\n",
       "      <td>US SENATE</td>\n",
       "      <td>LIBERTARIAN</td>\n",
       "      <td>LIBERTARIAN</td>\n",
       "      <td>TOTAL</td>\n",
       "      <td>0</td>\n",
       "      <td>BOURBON</td>\n",
       "      <td>20011</td>\n",
       "      <td>BOURBON</td>\n",
       "      <td>20011</td>\n",
       "      <td>...</td>\n",
       "      <td>KANSAS</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>KS</td>\n",
       "      <td>20</td>\n",
       "      <td>47</td>\n",
       "      <td>32</td>\n",
       "      <td>2020-11-03</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>900370</th>\n",
       "      <td>Spring Valley Township Spring Valley Enclave</td>\n",
       "      <td>US SENATE</td>\n",
       "      <td>DEMOCRAT</td>\n",
       "      <td>DEMOCRAT</td>\n",
       "      <td>TOTAL</td>\n",
       "      <td>0</td>\n",
       "      <td>CHEROKEE</td>\n",
       "      <td>20021</td>\n",
       "      <td>CHEROKEE</td>\n",
       "      <td>20021</td>\n",
       "      <td>...</td>\n",
       "      <td>KANSAS</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>KS</td>\n",
       "      <td>20</td>\n",
       "      <td>47</td>\n",
       "      <td>32</td>\n",
       "      <td>2020-11-03</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>900371</th>\n",
       "      <td>Spring Valley Township Spring Valley Enclave</td>\n",
       "      <td>US SENATE</td>\n",
       "      <td>REPUBLICAN</td>\n",
       "      <td>REPUBLICAN</td>\n",
       "      <td>TOTAL</td>\n",
       "      <td>0</td>\n",
       "      <td>CHEROKEE</td>\n",
       "      <td>20021</td>\n",
       "      <td>CHEROKEE</td>\n",
       "      <td>20021</td>\n",
       "      <td>...</td>\n",
       "      <td>KANSAS</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>KS</td>\n",
       "      <td>20</td>\n",
       "      <td>47</td>\n",
       "      <td>32</td>\n",
       "      <td>2020-11-03</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>900372</th>\n",
       "      <td>Spring Valley Township Spring Valley Enclave</td>\n",
       "      <td>US SENATE</td>\n",
       "      <td>LIBERTARIAN</td>\n",
       "      <td>LIBERTARIAN</td>\n",
       "      <td>TOTAL</td>\n",
       "      <td>0</td>\n",
       "      <td>CHEROKEE</td>\n",
       "      <td>20021</td>\n",
       "      <td>CHEROKEE</td>\n",
       "      <td>20021</td>\n",
       "      <td>...</td>\n",
       "      <td>KANSAS</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>KS</td>\n",
       "      <td>20</td>\n",
       "      <td>47</td>\n",
       "      <td>32</td>\n",
       "      <td>2020-11-03</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>672109 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                            precinct            office  \\\n",
       "35774                             Ward 2 Precinct 12  COURT OF APPEALS   \n",
       "35908                               1 East Rossville  COURT OF APPEALS   \n",
       "35909                               2 West Rossville  COURT OF APPEALS   \n",
       "35910                             9 East Silver Lake  COURT OF APPEALS   \n",
       "35911                            10 West Silver Lake  COURT OF APPEALS   \n",
       "...                                              ...               ...   \n",
       "899846                   Fort Scott Ward 4 Exclave C         US SENATE   \n",
       "899847                   Fort Scott Ward 4 Exclave C         US SENATE   \n",
       "900370  Spring Valley Township Spring Valley Enclave         US SENATE   \n",
       "900371  Spring Valley Township Spring Valley Enclave         US SENATE   \n",
       "900372  Spring Valley Township Spring Valley Enclave         US SENATE   \n",
       "\n",
       "       party_detailed party_simplified   mode  votes county_name county_fips  \\\n",
       "35774     NONPARTISAN      NONPARTISAN  TOTAL      0     SHAWNEE       20177   \n",
       "35908     NONPARTISAN      NONPARTISAN  TOTAL      0     SHAWNEE       20177   \n",
       "35909     NONPARTISAN      NONPARTISAN  TOTAL      0     SHAWNEE       20177   \n",
       "35910     NONPARTISAN      NONPARTISAN  TOTAL      0     SHAWNEE       20177   \n",
       "35911     NONPARTISAN      NONPARTISAN  TOTAL      0     SHAWNEE       20177   \n",
       "...               ...              ...    ...    ...         ...         ...   \n",
       "899846     REPUBLICAN       REPUBLICAN  TOTAL      0     BOURBON       20011   \n",
       "899847    LIBERTARIAN      LIBERTARIAN  TOTAL      0     BOURBON       20011   \n",
       "900370       DEMOCRAT         DEMOCRAT  TOTAL      0    CHEROKEE       20021   \n",
       "900371     REPUBLICAN       REPUBLICAN  TOTAL      0    CHEROKEE       20021   \n",
       "900372    LIBERTARIAN      LIBERTARIAN  TOTAL      0    CHEROKEE       20021   \n",
       "\n",
       "       jurisdiction_name jurisdiction_fips  ...   state special writein  \\\n",
       "35774            SHAWNEE             20177  ...  KANSAS   FALSE   FALSE   \n",
       "35908            SHAWNEE             20177  ...  KANSAS   FALSE   FALSE   \n",
       "35909            SHAWNEE             20177  ...  KANSAS   FALSE   FALSE   \n",
       "35910            SHAWNEE             20177  ...  KANSAS   FALSE   FALSE   \n",
       "35911            SHAWNEE             20177  ...  KANSAS   FALSE   FALSE   \n",
       "...                  ...               ...  ...     ...     ...     ...   \n",
       "899846           BOURBON             20011  ...  KANSAS   FALSE   FALSE   \n",
       "899847           BOURBON             20011  ...  KANSAS   FALSE   FALSE   \n",
       "900370          CHEROKEE             20021  ...  KANSAS   FALSE   FALSE   \n",
       "900371          CHEROKEE             20021  ...  KANSAS   FALSE   FALSE   \n",
       "900372          CHEROKEE             20021  ...  KANSAS   FALSE   FALSE   \n",
       "\n",
       "       state_po state_fips state_cen state_ic        date readme_check  \\\n",
       "35774        KS         20        47       32  2020-11-03        FALSE   \n",
       "35908        KS         20        47       32  2020-11-03        FALSE   \n",
       "35909        KS         20        47       32  2020-11-03        FALSE   \n",
       "35910        KS         20        47       32  2020-11-03        FALSE   \n",
       "35911        KS         20        47       32  2020-11-03        FALSE   \n",
       "...         ...        ...       ...      ...         ...          ...   \n",
       "899846       KS         20        47       32  2020-11-03        FALSE   \n",
       "899847       KS         20        47       32  2020-11-03        FALSE   \n",
       "900370       KS         20        47       32  2020-11-03        FALSE   \n",
       "900371       KS         20        47       32  2020-11-03        FALSE   \n",
       "900372       KS         20        47       32  2020-11-03        FALSE   \n",
       "\n",
       "       magnitude  \n",
       "35774          1  \n",
       "35908          1  \n",
       "35909          1  \n",
       "35910          1  \n",
       "35911          1  \n",
       "...          ...  \n",
       "899846         1  \n",
       "899847         1  \n",
       "900370         1  \n",
       "900371         1  \n",
       "900372         1  \n",
       "\n",
       "[672109 rows x 25 columns]"
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "x = alldf.loc[alldf.duplicated(),:]\n",
    "x"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>precinct</th>\n",
       "      <th>office</th>\n",
       "      <th>party_detailed</th>\n",
       "      <th>party_simplified</th>\n",
       "      <th>mode</th>\n",
       "      <th>votes</th>\n",
       "      <th>county_name</th>\n",
       "      <th>county_fips</th>\n",
       "      <th>jurisdiction_name</th>\n",
       "      <th>jurisdiction_fips</th>\n",
       "      <th>...</th>\n",
       "      <th>state</th>\n",
       "      <th>special</th>\n",
       "      <th>writein</th>\n",
       "      <th>state_po</th>\n",
       "      <th>state_fips</th>\n",
       "      <th>state_cen</th>\n",
       "      <th>state_ic</th>\n",
       "      <th>date</th>\n",
       "      <th>readme_check</th>\n",
       "      <th>magnitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BS 1-1</td>\n",
       "      <td>US SENATE</td>\n",
       "      <td>DEMOCRAT</td>\n",
       "      <td>DEMOCRAT</td>\n",
       "      <td>TOTAL</td>\n",
       "      <td>353</td>\n",
       "      <td>WYANDOTTE</td>\n",
       "      <td>20209</td>\n",
       "      <td>WYANDOTTE</td>\n",
       "      <td>20209</td>\n",
       "      <td>...</td>\n",
       "      <td>KANSAS</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>KS</td>\n",
       "      <td>20</td>\n",
       "      <td>47</td>\n",
       "      <td>32</td>\n",
       "      <td>2020-11-03</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BS 2-1</td>\n",
       "      <td>US SENATE</td>\n",
       "      <td>DEMOCRAT</td>\n",
       "      <td>DEMOCRAT</td>\n",
       "      <td>TOTAL</td>\n",
       "      <td>285</td>\n",
       "      <td>WYANDOTTE</td>\n",
       "      <td>20209</td>\n",
       "      <td>WYANDOTTE</td>\n",
       "      <td>20209</td>\n",
       "      <td>...</td>\n",
       "      <td>KANSAS</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>KS</td>\n",
       "      <td>20</td>\n",
       "      <td>47</td>\n",
       "      <td>32</td>\n",
       "      <td>2020-11-03</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BS 3-1</td>\n",
       "      <td>US SENATE</td>\n",
       "      <td>DEMOCRAT</td>\n",
       "      <td>DEMOCRAT</td>\n",
       "      <td>TOTAL</td>\n",
       "      <td>405</td>\n",
       "      <td>WYANDOTTE</td>\n",
       "      <td>20209</td>\n",
       "      <td>WYANDOTTE</td>\n",
       "      <td>20209</td>\n",
       "      <td>...</td>\n",
       "      <td>KANSAS</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>KS</td>\n",
       "      <td>20</td>\n",
       "      <td>47</td>\n",
       "      <td>32</td>\n",
       "      <td>2020-11-03</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BS 4-1</td>\n",
       "      <td>US SENATE</td>\n",
       "      <td>DEMOCRAT</td>\n",
       "      <td>DEMOCRAT</td>\n",
       "      <td>TOTAL</td>\n",
       "      <td>589</td>\n",
       "      <td>WYANDOTTE</td>\n",
       "      <td>20209</td>\n",
       "      <td>WYANDOTTE</td>\n",
       "      <td>20209</td>\n",
       "      <td>...</td>\n",
       "      <td>KANSAS</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>KS</td>\n",
       "      <td>20</td>\n",
       "      <td>47</td>\n",
       "      <td>32</td>\n",
       "      <td>2020-11-03</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>DE 1-1</td>\n",
       "      <td>US SENATE</td>\n",
       "      <td>DEMOCRAT</td>\n",
       "      <td>DEMOCRAT</td>\n",
       "      <td>TOTAL</td>\n",
       "      <td>11</td>\n",
       "      <td>WYANDOTTE</td>\n",
       "      <td>20209</td>\n",
       "      <td>WYANDOTTE</td>\n",
       "      <td>20209</td>\n",
       "      <td>...</td>\n",
       "      <td>KANSAS</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>KS</td>\n",
       "      <td>20</td>\n",
       "      <td>47</td>\n",
       "      <td>32</td>\n",
       "      <td>2020-11-03</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>907852</th>\n",
       "      <td>Yates Center Ward 1</td>\n",
       "      <td>US SENATE</td>\n",
       "      <td>REPUBLICAN</td>\n",
       "      <td>REPUBLICAN</td>\n",
       "      <td>TOTAL</td>\n",
       "      <td>206</td>\n",
       "      <td>WOODSON</td>\n",
       "      <td>20207</td>\n",
       "      <td>WOODSON</td>\n",
       "      <td>20207</td>\n",
       "      <td>...</td>\n",
       "      <td>KANSAS</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>KS</td>\n",
       "      <td>20</td>\n",
       "      <td>47</td>\n",
       "      <td>32</td>\n",
       "      <td>2020-11-03</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>907853</th>\n",
       "      <td>Yates Center Ward 1</td>\n",
       "      <td>US SENATE</td>\n",
       "      <td>LIBERTARIAN</td>\n",
       "      <td>LIBERTARIAN</td>\n",
       "      <td>TOTAL</td>\n",
       "      <td>37</td>\n",
       "      <td>WOODSON</td>\n",
       "      <td>20207</td>\n",
       "      <td>WOODSON</td>\n",
       "      <td>20207</td>\n",
       "      <td>...</td>\n",
       "      <td>KANSAS</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>KS</td>\n",
       "      <td>20</td>\n",
       "      <td>47</td>\n",
       "      <td>32</td>\n",
       "      <td>2020-11-03</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>907857</th>\n",
       "      <td>Yates Center Ward 2</td>\n",
       "      <td>US SENATE</td>\n",
       "      <td>DEMOCRAT</td>\n",
       "      <td>DEMOCRAT</td>\n",
       "      <td>TOTAL</td>\n",
       "      <td>70</td>\n",
       "      <td>WOODSON</td>\n",
       "      <td>20207</td>\n",
       "      <td>WOODSON</td>\n",
       "      <td>20207</td>\n",
       "      <td>...</td>\n",
       "      <td>KANSAS</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>KS</td>\n",
       "      <td>20</td>\n",
       "      <td>47</td>\n",
       "      <td>32</td>\n",
       "      <td>2020-11-03</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>907858</th>\n",
       "      <td>Yates Center Ward 2</td>\n",
       "      <td>US SENATE</td>\n",
       "      <td>REPUBLICAN</td>\n",
       "      <td>REPUBLICAN</td>\n",
       "      <td>TOTAL</td>\n",
       "      <td>205</td>\n",
       "      <td>WOODSON</td>\n",
       "      <td>20207</td>\n",
       "      <td>WOODSON</td>\n",
       "      <td>20207</td>\n",
       "      <td>...</td>\n",
       "      <td>KANSAS</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>KS</td>\n",
       "      <td>20</td>\n",
       "      <td>47</td>\n",
       "      <td>32</td>\n",
       "      <td>2020-11-03</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>907859</th>\n",
       "      <td>Yates Center Ward 2</td>\n",
       "      <td>US SENATE</td>\n",
       "      <td>LIBERTARIAN</td>\n",
       "      <td>LIBERTARIAN</td>\n",
       "      <td>TOTAL</td>\n",
       "      <td>18</td>\n",
       "      <td>WOODSON</td>\n",
       "      <td>20207</td>\n",
       "      <td>WOODSON</td>\n",
       "      <td>20207</td>\n",
       "      <td>...</td>\n",
       "      <td>KANSAS</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>KS</td>\n",
       "      <td>20</td>\n",
       "      <td>47</td>\n",
       "      <td>32</td>\n",
       "      <td>2020-11-03</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>84194 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                   precinct     office party_detailed party_simplified   mode  \\\n",
       "0                    BS 1-1  US SENATE       DEMOCRAT         DEMOCRAT  TOTAL   \n",
       "1                    BS 2-1  US SENATE       DEMOCRAT         DEMOCRAT  TOTAL   \n",
       "2                    BS 3-1  US SENATE       DEMOCRAT         DEMOCRAT  TOTAL   \n",
       "3                    BS 4-1  US SENATE       DEMOCRAT         DEMOCRAT  TOTAL   \n",
       "4                    DE 1-1  US SENATE       DEMOCRAT         DEMOCRAT  TOTAL   \n",
       "...                     ...        ...            ...              ...    ...   \n",
       "907852  Yates Center Ward 1  US SENATE     REPUBLICAN       REPUBLICAN  TOTAL   \n",
       "907853  Yates Center Ward 1  US SENATE    LIBERTARIAN      LIBERTARIAN  TOTAL   \n",
       "907857  Yates Center Ward 2  US SENATE       DEMOCRAT         DEMOCRAT  TOTAL   \n",
       "907858  Yates Center Ward 2  US SENATE     REPUBLICAN       REPUBLICAN  TOTAL   \n",
       "907859  Yates Center Ward 2  US SENATE    LIBERTARIAN      LIBERTARIAN  TOTAL   \n",
       "\n",
       "        votes county_name county_fips jurisdiction_name jurisdiction_fips  \\\n",
       "0         353   WYANDOTTE       20209         WYANDOTTE             20209   \n",
       "1         285   WYANDOTTE       20209         WYANDOTTE             20209   \n",
       "2         405   WYANDOTTE       20209         WYANDOTTE             20209   \n",
       "3         589   WYANDOTTE       20209         WYANDOTTE             20209   \n",
       "4          11   WYANDOTTE       20209         WYANDOTTE             20209   \n",
       "...       ...         ...         ...               ...               ...   \n",
       "907852    206     WOODSON       20207           WOODSON             20207   \n",
       "907853     37     WOODSON       20207           WOODSON             20207   \n",
       "907857     70     WOODSON       20207           WOODSON             20207   \n",
       "907858    205     WOODSON       20207           WOODSON             20207   \n",
       "907859     18     WOODSON       20207           WOODSON             20207   \n",
       "\n",
       "        ...   state special writein state_po state_fips state_cen state_ic  \\\n",
       "0       ...  KANSAS   FALSE   FALSE       KS         20        47       32   \n",
       "1       ...  KANSAS   FALSE   FALSE       KS         20        47       32   \n",
       "2       ...  KANSAS   FALSE   FALSE       KS         20        47       32   \n",
       "3       ...  KANSAS   FALSE   FALSE       KS         20        47       32   \n",
       "4       ...  KANSAS   FALSE   FALSE       KS         20        47       32   \n",
       "...     ...     ...     ...     ...      ...        ...       ...      ...   \n",
       "907852  ...  KANSAS   FALSE   FALSE       KS         20        47       32   \n",
       "907853  ...  KANSAS   FALSE   FALSE       KS         20        47       32   \n",
       "907857  ...  KANSAS   FALSE   FALSE       KS         20        47       32   \n",
       "907858  ...  KANSAS   FALSE   FALSE       KS         20        47       32   \n",
       "907859  ...  KANSAS   FALSE   FALSE       KS         20        47       32   \n",
       "\n",
       "              date readme_check magnitude  \n",
       "0       2020-11-03        FALSE         1  \n",
       "1       2020-11-03        FALSE         1  \n",
       "2       2020-11-03        FALSE         1  \n",
       "3       2020-11-03        FALSE         1  \n",
       "4       2020-11-03        FALSE         1  \n",
       "...            ...          ...       ...  \n",
       "907852  2020-11-03        FALSE         1  \n",
       "907853  2020-11-03        FALSE         1  \n",
       "907857  2020-11-03        FALSE         1  \n",
       "907858  2020-11-03        FALSE         1  \n",
       "907859  2020-11-03        FALSE         1  \n",
       "\n",
       "[84194 rows x 25 columns]"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "alldf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [],
   "source": [
    "alldf = alldf.drop_duplicates(keep='last')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [],
   "source": [
    "alldf = alldf.loc[~((alldf['mode'] == 'TOTAL') & (alldf['votes'] == (0)))]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "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>precinct</th>\n",
       "      <th>office</th>\n",
       "      <th>party_detailed</th>\n",
       "      <th>party_simplified</th>\n",
       "      <th>mode</th>\n",
       "      <th>votes</th>\n",
       "      <th>county_name</th>\n",
       "      <th>county_fips</th>\n",
       "      <th>jurisdiction_name</th>\n",
       "      <th>jurisdiction_fips</th>\n",
       "      <th>...</th>\n",
       "      <th>state</th>\n",
       "      <th>special</th>\n",
       "      <th>writein</th>\n",
       "      <th>state_po</th>\n",
       "      <th>state_fips</th>\n",
       "      <th>state_cen</th>\n",
       "      <th>state_ic</th>\n",
       "      <th>date</th>\n",
       "      <th>readme_check</th>\n",
       "      <th>magnitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>0 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [precinct, office, party_detailed, party_simplified, mode, votes, county_name, county_fips, jurisdiction_name, jurisdiction_fips, candidate, district, dataverse, year, stage, state, special, writein, state_po, state_fips, state_cen, state_ic, date, readme_check, magnitude]\n",
       "Index: []\n",
       "\n",
       "[0 rows x 25 columns]"
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "alldf.loc[((alldf['mode'] == 'TOTAL') & (alldf['votes'] == (0)))]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Final step: Remove all trailing white space and put columns in correct order. \n",
    "alldf=alldf.applymap(lambda x: x.strip() if type(x)==str else x)\n",
    "#alldf = alldf[~((alldf['candidate'].str.contains('UNNAMED'))&(alldf['votes']==0))]\n",
    "\n",
    "import csv\n",
    "alldf.to_csv('2020-ks-precinct-general-updated.csv',quoting=csv.QUOTE_NONNUMERIC,index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Things left to do:\n",
    "- polish overall data\n",
    "    * office column (done)\n",
    "    * district column (done)\n",
    "    * candidate column (done)\n",
    "    * office (done)\n",
    "    * check \"Write-in\" in cleaned candidate2 column and mark as writein and take off writein in cand column (done)\n",
    "   \n",
    "Done:\n",
    "- general sheets are all in clean dataframe \n",
    "- johnson, shawnee are all in clean dataframe\n",
    "- wyandotte (supreme court and court of appeals) in clean dataframe \n",
    "- clean rest of wyandotte sheets \n",
    "- start and finish sedgwick sheets \n",
    "- merge all data together "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}