Vaccine_Data_Cleanup.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Import dependencies\n",
    "import pandas as pd\n",
    "from sqlalchemy import create_engine\n",
    "import psycopg2\n",
    "\n",
    "from config import db_password"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Force pandas to show all columns\n",
    "pd.set_option('display.max_columns', None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>FIPS Code</th>\n",
       "      <th>County Name</th>\n",
       "      <th>State</th>\n",
       "      <th>Estimated hesitant</th>\n",
       "      <th>Estimated strongly hesitant</th>\n",
       "      <th>Social Vulnerability Index (SVI)</th>\n",
       "      <th>SVI Category</th>\n",
       "      <th>Ability to handle a COVID-19 outbreak (CVAC)</th>\n",
       "      <th>CVAC Category</th>\n",
       "      <th>Percent adults fully vaccinated against COVID-19</th>\n",
       "      <th>Percent Hispanic</th>\n",
       "      <th>Percent non-Hispanic American Indian/Alaska Native</th>\n",
       "      <th>Percent non-Hispanic Asian</th>\n",
       "      <th>Percent non-Hispanic Black</th>\n",
       "      <th>Percent non-Hispanic Native Hawaiian/Pacific Islander</th>\n",
       "      <th>Percent non-Hispanic White</th>\n",
       "      <th>Geographical Point</th>\n",
       "      <th>State Code</th>\n",
       "      <th>County Boundary</th>\n",
       "      <th>State Boundary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1123</td>\n",
       "      <td>Tallapoosa County, Alabama</td>\n",
       "      <td>ALABAMA</td>\n",
       "      <td>0.23</td>\n",
       "      <td>0.12</td>\n",
       "      <td>0.89</td>\n",
       "      <td>Very High Vulnerability</td>\n",
       "      <td>0.64</td>\n",
       "      <td>High Vulnerability</td>\n",
       "      <td>0.161</td>\n",
       "      <td>0.0242</td>\n",
       "      <td>0.0022</td>\n",
       "      <td>0.0036</td>\n",
       "      <td>0.2697</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.6887</td>\n",
       "      <td>POINT (-86.844516 32.756889)</td>\n",
       "      <td>AL</td>\n",
       "      <td>MULTIPOLYGON (((-85.841259 33.104456, -85.8409...</td>\n",
       "      <td>MULTIPOLYGON (((-88.139988 34.581703, -88.1352...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1121</td>\n",
       "      <td>Talladega County, Alabama</td>\n",
       "      <td>ALABAMA</td>\n",
       "      <td>0.23</td>\n",
       "      <td>0.11</td>\n",
       "      <td>0.87</td>\n",
       "      <td>Very High Vulnerability</td>\n",
       "      <td>0.84</td>\n",
       "      <td>Very High Vulnerability</td>\n",
       "      <td>0.133</td>\n",
       "      <td>0.0229</td>\n",
       "      <td>0.0043</td>\n",
       "      <td>0.0061</td>\n",
       "      <td>0.3237</td>\n",
       "      <td>0.0003</td>\n",
       "      <td>0.6263</td>\n",
       "      <td>POINT (-86.844516 32.756889)</td>\n",
       "      <td>AL</td>\n",
       "      <td>MULTIPOLYGON (((-86.303069 33.46316, -86.30306...</td>\n",
       "      <td>MULTIPOLYGON (((-88.139988 34.581703, -88.1352...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1131</td>\n",
       "      <td>Wilcox County, Alabama</td>\n",
       "      <td>ALABAMA</td>\n",
       "      <td>0.23</td>\n",
       "      <td>0.11</td>\n",
       "      <td>0.93</td>\n",
       "      <td>Very High Vulnerability</td>\n",
       "      <td>0.94</td>\n",
       "      <td>Very High Vulnerability</td>\n",
       "      <td>0.228</td>\n",
       "      <td>0.0053</td>\n",
       "      <td>0.0009</td>\n",
       "      <td>0.0003</td>\n",
       "      <td>0.6938</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.2684</td>\n",
       "      <td>POINT (-86.844516 32.756889)</td>\n",
       "      <td>AL</td>\n",
       "      <td>MULTIPOLYGON (((-87.52534299999999 32.132773, ...</td>\n",
       "      <td>MULTIPOLYGON (((-88.139988 34.581703, -88.1352...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1129</td>\n",
       "      <td>Washington County, Alabama</td>\n",
       "      <td>ALABAMA</td>\n",
       "      <td>0.23</td>\n",
       "      <td>0.11</td>\n",
       "      <td>0.73</td>\n",
       "      <td>High Vulnerability</td>\n",
       "      <td>0.82</td>\n",
       "      <td>Very High Vulnerability</td>\n",
       "      <td>0.192</td>\n",
       "      <td>0.0146</td>\n",
       "      <td>0.0731</td>\n",
       "      <td>0.0025</td>\n",
       "      <td>0.2354</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.6495</td>\n",
       "      <td>POINT (-86.844516 32.756889)</td>\n",
       "      <td>AL</td>\n",
       "      <td>MULTIPOLYGON (((-88.45317899999999 31.505388, ...</td>\n",
       "      <td>MULTIPOLYGON (((-88.139988 34.581703, -88.1352...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1133</td>\n",
       "      <td>Winston County, Alabama</td>\n",
       "      <td>ALABAMA</td>\n",
       "      <td>0.22</td>\n",
       "      <td>0.11</td>\n",
       "      <td>0.70</td>\n",
       "      <td>High Vulnerability</td>\n",
       "      <td>0.80</td>\n",
       "      <td>High Vulnerability</td>\n",
       "      <td>0.085</td>\n",
       "      <td>0.0315</td>\n",
       "      <td>0.0034</td>\n",
       "      <td>0.0016</td>\n",
       "      <td>0.0073</td>\n",
       "      <td>0.0005</td>\n",
       "      <td>0.9370</td>\n",
       "      <td>POINT (-86.844516 32.756889)</td>\n",
       "      <td>AL</td>\n",
       "      <td>MULTIPOLYGON (((-87.63656399999999 34.120908, ...</td>\n",
       "      <td>MULTIPOLYGON (((-88.139988 34.581703, -88.1352...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   FIPS Code                 County Name    State  Estimated hesitant  \\\n",
       "0       1123  Tallapoosa County, Alabama  ALABAMA                0.23   \n",
       "1       1121   Talladega County, Alabama  ALABAMA                0.23   \n",
       "2       1131      Wilcox County, Alabama  ALABAMA                0.23   \n",
       "3       1129  Washington County, Alabama  ALABAMA                0.23   \n",
       "4       1133     Winston County, Alabama  ALABAMA                0.22   \n",
       "\n",
       "   Estimated strongly hesitant  Social Vulnerability Index (SVI)  \\\n",
       "0                         0.12                              0.89   \n",
       "1                         0.11                              0.87   \n",
       "2                         0.11                              0.93   \n",
       "3                         0.11                              0.73   \n",
       "4                         0.11                              0.70   \n",
       "\n",
       "              SVI Category  Ability to handle a COVID-19 outbreak (CVAC)  \\\n",
       "0  Very High Vulnerability                                          0.64   \n",
       "1  Very High Vulnerability                                          0.84   \n",
       "2  Very High Vulnerability                                          0.94   \n",
       "3       High Vulnerability                                          0.82   \n",
       "4       High Vulnerability                                          0.80   \n",
       "\n",
       "             CVAC Category  Percent adults fully vaccinated against COVID-19  \\\n",
       "0       High Vulnerability                                             0.161   \n",
       "1  Very High Vulnerability                                             0.133   \n",
       "2  Very High Vulnerability                                             0.228   \n",
       "3  Very High Vulnerability                                             0.192   \n",
       "4       High Vulnerability                                             0.085   \n",
       "\n",
       "   Percent Hispanic  Percent non-Hispanic American Indian/Alaska Native  \\\n",
       "0            0.0242                                             0.0022    \n",
       "1            0.0229                                             0.0043    \n",
       "2            0.0053                                             0.0009    \n",
       "3            0.0146                                             0.0731    \n",
       "4            0.0315                                             0.0034    \n",
       "\n",
       "   Percent non-Hispanic Asian  Percent non-Hispanic Black  \\\n",
       "0                      0.0036                      0.2697   \n",
       "1                      0.0061                      0.3237   \n",
       "2                      0.0003                      0.6938   \n",
       "3                      0.0025                      0.2354   \n",
       "4                      0.0016                      0.0073   \n",
       "\n",
       "   Percent non-Hispanic Native Hawaiian/Pacific Islander  \\\n",
       "0                                             0.0000       \n",
       "1                                             0.0003       \n",
       "2                                             0.0000       \n",
       "3                                             0.0000       \n",
       "4                                             0.0005       \n",
       "\n",
       "   Percent non-Hispanic White            Geographical Point State Code  \\\n",
       "0                      0.6887  POINT (-86.844516 32.756889)         AL   \n",
       "1                      0.6263  POINT (-86.844516 32.756889)         AL   \n",
       "2                      0.2684  POINT (-86.844516 32.756889)         AL   \n",
       "3                      0.6495  POINT (-86.844516 32.756889)         AL   \n",
       "4                      0.9370  POINT (-86.844516 32.756889)         AL   \n",
       "\n",
       "                                     County Boundary  \\\n",
       "0  MULTIPOLYGON (((-85.841259 33.104456, -85.8409...   \n",
       "1  MULTIPOLYGON (((-86.303069 33.46316, -86.30306...   \n",
       "2  MULTIPOLYGON (((-87.52534299999999 32.132773, ...   \n",
       "3  MULTIPOLYGON (((-88.45317899999999 31.505388, ...   \n",
       "4  MULTIPOLYGON (((-87.63656399999999 34.120908, ...   \n",
       "\n",
       "                                      State Boundary  \n",
       "0  MULTIPOLYGON (((-88.139988 34.581703, -88.1352...  \n",
       "1  MULTIPOLYGON (((-88.139988 34.581703, -88.1352...  \n",
       "2  MULTIPOLYGON (((-88.139988 34.581703, -88.1352...  \n",
       "3  MULTIPOLYGON (((-88.139988 34.581703, -88.1352...  \n",
       "4  MULTIPOLYGON (((-88.139988 34.581703, -88.1352...  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Load Vaccine Hesitancy dataset and check dataframe\n",
    "file_path = \"Vaccine_Hesitancy_for_COVID-19__County_and_local_estimates.csv\"\n",
    "vaccine_hesitancy_df = pd.read_csv(file_path)\n",
    "vaccine_hesitancy_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "TEXAS                   254\n",
       "GEORGIA                 159\n",
       "VIRGINIA                133\n",
       "KENTUCKY                120\n",
       "MISSOURI                115\n",
       "KANSAS                  105\n",
       "ILLINOIS                102\n",
       "NORTH CAROLINA          100\n",
       "IOWA                     99\n",
       "TENNESSEE                95\n",
       "NEBRASKA                 93\n",
       "INDIANA                  92\n",
       "OHIO                     88\n",
       "MINNESOTA                87\n",
       "MICHIGAN                 83\n",
       "MISSISSIPPI              82\n",
       "OKLAHOMA                 77\n",
       "ARKANSAS                 75\n",
       "WISCONSIN                72\n",
       "FLORIDA                  67\n",
       "PENNSYLVANIA             67\n",
       "ALABAMA                  67\n",
       "SOUTH DAKOTA             66\n",
       "LOUISIANA                64\n",
       "COLORADO                 64\n",
       "NEW YORK                 62\n",
       "CALIFORNIA               58\n",
       "MONTANA                  56\n",
       "WEST VIRGINIA            55\n",
       "NORTH DAKOTA             53\n",
       "SOUTH CAROLINA           46\n",
       "IDAHO                    44\n",
       "WASHINGTON               39\n",
       "OREGON                   36\n",
       "NEW MEXICO               33\n",
       "UTAH                     29\n",
       "ALASKA                   29\n",
       "MARYLAND                 24\n",
       "WYOMING                  23\n",
       "NEW JERSEY               21\n",
       "NEVADA                   17\n",
       "MAINE                    16\n",
       "ARIZONA                  15\n",
       "VERMONT                  14\n",
       "MASSACHUSETTS            14\n",
       "NEW HAMPSHIRE            10\n",
       "CONNECTICUT               8\n",
       "RHODE ISLAND              5\n",
       "HAWAII                    5\n",
       "DELAWARE                  3\n",
       "DISTRICT OF COLUMBIA      1\n",
       "Name: State, dtype: int64"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check number of records per state\n",
    "vaccine_hesitancy_df[\"State\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "FIPS Code                                                  0\n",
       "County Name                                                0\n",
       "State                                                      0\n",
       "Estimated hesitant                                         0\n",
       "Estimated strongly hesitant                                0\n",
       "Social Vulnerability Index (SVI)                           1\n",
       "SVI Category                                               0\n",
       "Ability to handle a COVID-19 outbreak (CVAC)               0\n",
       "CVAC Category                                              0\n",
       "Percent adults fully vaccinated against COVID-19         316\n",
       "Percent Hispanic                                           0\n",
       "Percent non-Hispanic American Indian/Alaska Native         0\n",
       "Percent non-Hispanic Asian                                 0\n",
       "Percent non-Hispanic Black                                 0\n",
       "Percent non-Hispanic Native Hawaiian/Pacific Islander      0\n",
       "Percent non-Hispanic White                                 0\n",
       "Geographical Point                                         0\n",
       "State Code                                                 0\n",
       "County Boundary                                            1\n",
       "State Boundary                                             1\n",
       "dtype: int64"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check for null values\n",
    "vaccine_hesitancy_df.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "#vaccine_hesitancy_df = vaccine_hesitancy_df[vaccine_hesitancy_df['Social Vulnerability Index (SVI)'].notna()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "FIPS Code                                                  0\n",
       "County Name                                                0\n",
       "State                                                      0\n",
       "Estimated hesitant                                         0\n",
       "Estimated strongly hesitant                                0\n",
       "Social Vulnerability Index (SVI)                           1\n",
       "SVI Category                                               0\n",
       "Ability to handle a COVID-19 outbreak (CVAC)               0\n",
       "CVAC Category                                              0\n",
       "Percent adults fully vaccinated against COVID-19         316\n",
       "Percent Hispanic                                           0\n",
       "Percent non-Hispanic American Indian/Alaska Native         0\n",
       "Percent non-Hispanic Asian                                 0\n",
       "Percent non-Hispanic Black                                 0\n",
       "Percent non-Hispanic Native Hawaiian/Pacific Islander      0\n",
       "Percent non-Hispanic White                                 0\n",
       "Geographical Point                                         0\n",
       "State Code                                                 0\n",
       "County Boundary                                            1\n",
       "State Boundary                                             1\n",
       "dtype: int64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check for null values again\n",
    "vaccine_hesitancy_df.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['FIPS Code', 'County Name', 'State', 'Estimated hesitant',\n",
       "       'Estimated strongly hesitant', 'Social Vulnerability Index (SVI)',\n",
       "       'SVI Category', 'Ability to handle a COVID-19 outbreak (CVAC)',\n",
       "       'CVAC Category', 'Percent adults fully vaccinated against COVID-19',\n",
       "       'Percent Hispanic',\n",
       "       'Percent non-Hispanic American Indian/Alaska Native',\n",
       "       'Percent non-Hispanic Asian', 'Percent non-Hispanic Black',\n",
       "       'Percent non-Hispanic Native Hawaiian/Pacific Islander',\n",
       "       'Percent non-Hispanic White', 'Geographical Point', 'State Code',\n",
       "       'County Boundary', 'State Boundary'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Show all columns in df\n",
    "vaccine_hesitancy_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "FIPS Code                                                  int64\n",
       "County Name                                               object\n",
       "State                                                     object\n",
       "Estimated hesitant                                       float64\n",
       "Estimated strongly hesitant                              float64\n",
       "Social Vulnerability Index (SVI)                         float64\n",
       "SVI Category                                              object\n",
       "Ability to handle a COVID-19 outbreak (CVAC)             float64\n",
       "CVAC Category                                             object\n",
       "Percent adults fully vaccinated against COVID-19         float64\n",
       "Percent Hispanic                                         float64\n",
       "Percent non-Hispanic American Indian/Alaska Native       float64\n",
       "Percent non-Hispanic Asian                               float64\n",
       "Percent non-Hispanic Black                               float64\n",
       "Percent non-Hispanic Native Hawaiian/Pacific Islander    float64\n",
       "Percent non-Hispanic White                               float64\n",
       "Geographical Point                                        object\n",
       "State Code                                                object\n",
       "County Boundary                                           object\n",
       "State Boundary                                            object\n",
       "dtype: object"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check columns data types\n",
    "vaccine_hesitancy_df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "FIPS Code                                                3142\n",
       "County Name                                              3142\n",
       "State                                                    3142\n",
       "Estimated hesitant                                       3142\n",
       "Estimated strongly hesitant                              3142\n",
       "Social Vulnerability Index (SVI)                         3141\n",
       "SVI Category                                             3142\n",
       "Ability to handle a COVID-19 outbreak (CVAC)             3142\n",
       "CVAC Category                                            3142\n",
       "Percent adults fully vaccinated against COVID-19         2826\n",
       "Percent Hispanic                                         3142\n",
       "Percent non-Hispanic American Indian/Alaska Native       3142\n",
       "Percent non-Hispanic Asian                               3142\n",
       "Percent non-Hispanic Black                               3142\n",
       "Percent non-Hispanic Native Hawaiian/Pacific Islander    3142\n",
       "Percent non-Hispanic White                               3142\n",
       "Geographical Point                                       3142\n",
       "State Code                                               3142\n",
       "County Boundary                                          3141\n",
       "State Boundary                                           3141\n",
       "dtype: int64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "vaccine_hesitancy_df.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    " # Rename the columns in the vaccine_hesitancy DataFrame.\n",
    "vaccine_hesitancy_df.rename({'FIPS Code':'fips_code',\n",
    "                  'County Name':'county',\n",
    "                  'State':'state',\n",
    "                  'Estimated hesitant':'est_hesitant',\n",
    "                  'Estimated strongly hesitant':'est_strongly_hesitant',\n",
    "                  'Social Vulnerability Index (SVI)':'svi',\n",
    "                  'SVI Category':'svi_category',\n",
    "                  'Ability to handle a COVID-19 outbreak (CVAC)':'cvac',\n",
    "                  'CVAC Category':'cvac_category',\n",
    "                  'Percent adults fully vaccinated against COVID-19':'percent_fully_vaccinated',\n",
    "                  'Percent Hispanic':'percent_hispanic',\n",
    "                  'Percent non-Hispanic American Indian/Alaska Native':'percent_american_indian_alaska_native',\n",
    "                  'Percent non-Hispanic Asian':'percent_asian',\n",
    "                  'Percent non-Hispanic Black':'percent_black',\n",
    "                  'Percent non-Hispanic Native Hawaiian/Pacific Islander':'percent_hawaiian_pacific',\n",
    "                  'Percent non-Hispanic White':'percent_white',\n",
    "                  'Geographical Point':'geographical_point',\n",
    "                  'State Code':'state_code',\n",
    "                  'County Boundary':'county_boundary',\n",
    "                  'State Boundary':'state_boundary',\n",
    "                 }, axis='columns', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "fips_code                                  int64\n",
       "county                                    object\n",
       "state                                     object\n",
       "est_hesitant                             float64\n",
       "est_strongly_hesitant                    float64\n",
       "svi                                      float64\n",
       "svi_category                              object\n",
       "cvac                                     float64\n",
       "cvac_category                             object\n",
       "percent_fully_vaccinated                 float64\n",
       "percent_hispanic                         float64\n",
       "percent_american_indian_alaska_native    float64\n",
       "percent_asian                            float64\n",
       "percent_black                            float64\n",
       "percent_hawaiian_pacific                 float64\n",
       "percent_white                            float64\n",
       "geographical_point                        object\n",
       "state_code                                object\n",
       "county_boundary                           object\n",
       "state_boundary                            object\n",
       "dtype: object"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check new column names\n",
    "vaccine_hesitancy_df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>fips_code</th>\n",
       "      <th>county</th>\n",
       "      <th>state</th>\n",
       "      <th>est_hesitant</th>\n",
       "      <th>est_strongly_hesitant</th>\n",
       "      <th>svi</th>\n",
       "      <th>svi_category</th>\n",
       "      <th>cvac</th>\n",
       "      <th>cvac_category</th>\n",
       "      <th>percent_fully_vaccinated</th>\n",
       "      <th>percent_hispanic</th>\n",
       "      <th>percent_american_indian_alaska_native</th>\n",
       "      <th>percent_asian</th>\n",
       "      <th>percent_black</th>\n",
       "      <th>percent_hawaiian_pacific</th>\n",
       "      <th>percent_white</th>\n",
       "      <th>geographical_point</th>\n",
       "      <th>state_code</th>\n",
       "      <th>county_boundary</th>\n",
       "      <th>state_boundary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>273</th>\n",
       "      <td>1001</td>\n",
       "      <td>Autauga County, Alabama</td>\n",
       "      <td>ALABAMA</td>\n",
       "      <td>0.22</td>\n",
       "      <td>0.10</td>\n",
       "      <td>0.44</td>\n",
       "      <td>Moderate Vulnerability</td>\n",
       "      <td>0.61</td>\n",
       "      <td>High Vulnerability</td>\n",
       "      <td>0.114</td>\n",
       "      <td>0.0283</td>\n",
       "      <td>0.0025</td>\n",
       "      <td>0.0103</td>\n",
       "      <td>0.1900</td>\n",
       "      <td>0.0001</td>\n",
       "      <td>0.7460</td>\n",
       "      <td>POINT (-86.844516 32.756889)</td>\n",
       "      <td>AL</td>\n",
       "      <td>MULTIPOLYGON (((-86.90309599999999 32.54062599...</td>\n",
       "      <td>MULTIPOLYGON (((-88.139988 34.581703, -88.1352...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>282</th>\n",
       "      <td>1003</td>\n",
       "      <td>Baldwin County, Alabama</td>\n",
       "      <td>ALABAMA</td>\n",
       "      <td>0.20</td>\n",
       "      <td>0.10</td>\n",
       "      <td>0.22</td>\n",
       "      <td>Low Vulnerability</td>\n",
       "      <td>0.23</td>\n",
       "      <td>Low Vulnerability</td>\n",
       "      <td>0.176</td>\n",
       "      <td>0.0456</td>\n",
       "      <td>0.0065</td>\n",
       "      <td>0.0092</td>\n",
       "      <td>0.0917</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.8307</td>\n",
       "      <td>POINT (-86.844516 32.756889)</td>\n",
       "      <td>AL</td>\n",
       "      <td>MULTIPOLYGON (((-87.990684 30.55549, -87.98783...</td>\n",
       "      <td>MULTIPOLYGON (((-88.139988 34.581703, -88.1352...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>309</th>\n",
       "      <td>1005</td>\n",
       "      <td>Barbour County, Alabama</td>\n",
       "      <td>ALABAMA</td>\n",
       "      <td>0.23</td>\n",
       "      <td>0.11</td>\n",
       "      <td>1.00</td>\n",
       "      <td>Very High Vulnerability</td>\n",
       "      <td>0.89</td>\n",
       "      <td>Very High Vulnerability</td>\n",
       "      <td>0.128</td>\n",
       "      <td>0.0436</td>\n",
       "      <td>0.0029</td>\n",
       "      <td>0.0048</td>\n",
       "      <td>0.4744</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.4581</td>\n",
       "      <td>POINT (-86.844516 32.756889)</td>\n",
       "      <td>AL</td>\n",
       "      <td>MULTIPOLYGON (((-85.429819 32.045983, -85.4303...</td>\n",
       "      <td>MULTIPOLYGON (((-88.139988 34.581703, -88.1352...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>338</th>\n",
       "      <td>1007</td>\n",
       "      <td>Bibb County, Alabama</td>\n",
       "      <td>ALABAMA</td>\n",
       "      <td>0.24</td>\n",
       "      <td>0.12</td>\n",
       "      <td>0.60</td>\n",
       "      <td>High Vulnerability</td>\n",
       "      <td>0.76</td>\n",
       "      <td>High Vulnerability</td>\n",
       "      <td>0.115</td>\n",
       "      <td>0.0257</td>\n",
       "      <td>0.0013</td>\n",
       "      <td>0.0012</td>\n",
       "      <td>0.2214</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.7453</td>\n",
       "      <td>POINT (-86.844516 32.756889)</td>\n",
       "      <td>AL</td>\n",
       "      <td>MULTIPOLYGON (((-87.312265 33.086219, -87.3121...</td>\n",
       "      <td>MULTIPOLYGON (((-88.139988 34.581703, -88.1352...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>375</th>\n",
       "      <td>1009</td>\n",
       "      <td>Blount County, Alabama</td>\n",
       "      <td>ALABAMA</td>\n",
       "      <td>0.23</td>\n",
       "      <td>0.11</td>\n",
       "      <td>0.42</td>\n",
       "      <td>Moderate Vulnerability</td>\n",
       "      <td>0.80</td>\n",
       "      <td>High Vulnerability</td>\n",
       "      <td>0.095</td>\n",
       "      <td>0.0926</td>\n",
       "      <td>0.0007</td>\n",
       "      <td>0.0037</td>\n",
       "      <td>0.0153</td>\n",
       "      <td>0.0004</td>\n",
       "      <td>0.8689</td>\n",
       "      <td>POINT (-86.844516 32.756889)</td>\n",
       "      <td>AL</td>\n",
       "      <td>MULTIPOLYGON (((-86.74918799999999 33.997596, ...</td>\n",
       "      <td>MULTIPOLYGON (((-88.139988 34.581703, -88.1352...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     fips_code                   county    state  est_hesitant  \\\n",
       "273       1001  Autauga County, Alabama  ALABAMA          0.22   \n",
       "282       1003  Baldwin County, Alabama  ALABAMA          0.20   \n",
       "309       1005  Barbour County, Alabama  ALABAMA          0.23   \n",
       "338       1007     Bibb County, Alabama  ALABAMA          0.24   \n",
       "375       1009   Blount County, Alabama  ALABAMA          0.23   \n",
       "\n",
       "     est_strongly_hesitant   svi             svi_category  cvac  \\\n",
       "273                   0.10  0.44   Moderate Vulnerability  0.61   \n",
       "282                   0.10  0.22        Low Vulnerability  0.23   \n",
       "309                   0.11  1.00  Very High Vulnerability  0.89   \n",
       "338                   0.12  0.60       High Vulnerability  0.76   \n",
       "375                   0.11  0.42   Moderate Vulnerability  0.80   \n",
       "\n",
       "               cvac_category  percent_fully_vaccinated  percent_hispanic  \\\n",
       "273       High Vulnerability                     0.114            0.0283   \n",
       "282        Low Vulnerability                     0.176            0.0456   \n",
       "309  Very High Vulnerability                     0.128            0.0436   \n",
       "338       High Vulnerability                     0.115            0.0257   \n",
       "375       High Vulnerability                     0.095            0.0926   \n",
       "\n",
       "     percent_american_indian_alaska_native  percent_asian  percent_black  \\\n",
       "273                                 0.0025         0.0103         0.1900   \n",
       "282                                 0.0065         0.0092         0.0917   \n",
       "309                                 0.0029         0.0048         0.4744   \n",
       "338                                 0.0013         0.0012         0.2214   \n",
       "375                                 0.0007         0.0037         0.0153   \n",
       "\n",
       "     percent_hawaiian_pacific  percent_white            geographical_point  \\\n",
       "273                    0.0001         0.7460  POINT (-86.844516 32.756889)   \n",
       "282                    0.0000         0.8307  POINT (-86.844516 32.756889)   \n",
       "309                    0.0000         0.4581  POINT (-86.844516 32.756889)   \n",
       "338                    0.0000         0.7453  POINT (-86.844516 32.756889)   \n",
       "375                    0.0004         0.8689  POINT (-86.844516 32.756889)   \n",
       "\n",
       "    state_code                                    county_boundary  \\\n",
       "273         AL  MULTIPOLYGON (((-86.90309599999999 32.54062599...   \n",
       "282         AL  MULTIPOLYGON (((-87.990684 30.55549, -87.98783...   \n",
       "309         AL  MULTIPOLYGON (((-85.429819 32.045983, -85.4303...   \n",
       "338         AL  MULTIPOLYGON (((-87.312265 33.086219, -87.3121...   \n",
       "375         AL  MULTIPOLYGON (((-86.74918799999999 33.997596, ...   \n",
       "\n",
       "                                        state_boundary  \n",
       "273  MULTIPOLYGON (((-88.139988 34.581703, -88.1352...  \n",
       "282  MULTIPOLYGON (((-88.139988 34.581703, -88.1352...  \n",
       "309  MULTIPOLYGON (((-88.139988 34.581703, -88.1352...  \n",
       "338  MULTIPOLYGON (((-88.139988 34.581703, -88.1352...  \n",
       "375  MULTIPOLYGON (((-88.139988 34.581703, -88.1352...  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Re order fips column\n",
    "vaccine_hesitancy_df = vaccine_hesitancy_df.sort_values(by='fips_code', ascending=True)\n",
    "vaccine_hesitancy_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>county</th>\n",
       "      <th>state</th>\n",
       "      <th>percentage16_Donald_Trump</th>\n",
       "      <th>percentage16_Hillary_Clinton</th>\n",
       "      <th>total_votes16</th>\n",
       "      <th>votes16_Donald_Trump</th>\n",
       "      <th>votes16_Hillary_Clinton</th>\n",
       "      <th>percentage20_Donald_Trump</th>\n",
       "      <th>percentage20_Joe_Biden</th>\n",
       "      <th>total_votes20</th>\n",
       "      <th>votes20_Donald_Trump</th>\n",
       "      <th>votes20_Joe_Biden</th>\n",
       "      <th>lat</th>\n",
       "      <th>long</th>\n",
       "      <th>cases</th>\n",
       "      <th>deaths</th>\n",
       "      <th>TotalPop</th>\n",
       "      <th>Men</th>\n",
       "      <th>Women</th>\n",
       "      <th>Hispanic</th>\n",
       "      <th>White</th>\n",
       "      <th>Black</th>\n",
       "      <th>Native</th>\n",
       "      <th>Asian</th>\n",
       "      <th>Pacific</th>\n",
       "      <th>VotingAgeCitizen</th>\n",
       "      <th>Income</th>\n",
       "      <th>IncomeErr</th>\n",
       "      <th>IncomePerCap</th>\n",
       "      <th>IncomePerCapErr</th>\n",
       "      <th>Poverty</th>\n",
       "      <th>ChildPoverty</th>\n",
       "      <th>Professional</th>\n",
       "      <th>Service</th>\n",
       "      <th>Office</th>\n",
       "      <th>Construction</th>\n",
       "      <th>Production</th>\n",
       "      <th>Drive</th>\n",
       "      <th>Carpool</th>\n",
       "      <th>Transit</th>\n",
       "      <th>Walk</th>\n",
       "      <th>OtherTransp</th>\n",
       "      <th>WorkAtHome</th>\n",
       "      <th>MeanCommute</th>\n",
       "      <th>Employed</th>\n",
       "      <th>PrivateWork</th>\n",
       "      <th>PublicWork</th>\n",
       "      <th>SelfEmployed</th>\n",
       "      <th>FamilyWork</th>\n",
       "      <th>Unemployment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>Abbeville</td>\n",
       "      <td>SC</td>\n",
       "      <td>0.629</td>\n",
       "      <td>0.346</td>\n",
       "      <td>10724.0</td>\n",
       "      <td>6742.0</td>\n",
       "      <td>3712.0</td>\n",
       "      <td>0.661</td>\n",
       "      <td>0.330</td>\n",
       "      <td>12433.0</td>\n",
       "      <td>8215.0</td>\n",
       "      <td>4101.0</td>\n",
       "      <td>34.223334</td>\n",
       "      <td>-82.461707</td>\n",
       "      <td>805.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>24788.0</td>\n",
       "      <td>12044.0</td>\n",
       "      <td>12744.0</td>\n",
       "      <td>1.3</td>\n",
       "      <td>68.9</td>\n",
       "      <td>27.6</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>19452.0</td>\n",
       "      <td>35254.0</td>\n",
       "      <td>2259.0</td>\n",
       "      <td>19234.0</td>\n",
       "      <td>799.0</td>\n",
       "      <td>22.7</td>\n",
       "      <td>32.1</td>\n",
       "      <td>27.2</td>\n",
       "      <td>20.7</td>\n",
       "      <td>20.8</td>\n",
       "      <td>10.6</td>\n",
       "      <td>20.7</td>\n",
       "      <td>78.3</td>\n",
       "      <td>11.1</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1.8</td>\n",
       "      <td>1.8</td>\n",
       "      <td>6.5</td>\n",
       "      <td>25.8</td>\n",
       "      <td>9505.0</td>\n",
       "      <td>78.8</td>\n",
       "      <td>13.3</td>\n",
       "      <td>7.8</td>\n",
       "      <td>0.1</td>\n",
       "      <td>9.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Acadia</td>\n",
       "      <td>LA</td>\n",
       "      <td>0.773</td>\n",
       "      <td>0.206</td>\n",
       "      <td>27386.0</td>\n",
       "      <td>21159.0</td>\n",
       "      <td>5638.0</td>\n",
       "      <td>0.795</td>\n",
       "      <td>0.191</td>\n",
       "      <td>28425.0</td>\n",
       "      <td>22596.0</td>\n",
       "      <td>5443.0</td>\n",
       "      <td>30.295065</td>\n",
       "      <td>-92.414197</td>\n",
       "      <td>3182.0</td>\n",
       "      <td>102.0</td>\n",
       "      <td>62607.0</td>\n",
       "      <td>30433.0</td>\n",
       "      <td>32174.0</td>\n",
       "      <td>2.4</td>\n",
       "      <td>77.5</td>\n",
       "      <td>17.6</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>45197.0</td>\n",
       "      <td>40492.0</td>\n",
       "      <td>2544.0</td>\n",
       "      <td>21591.0</td>\n",
       "      <td>1002.0</td>\n",
       "      <td>21.5</td>\n",
       "      <td>27.6</td>\n",
       "      <td>27.6</td>\n",
       "      <td>16.9</td>\n",
       "      <td>25.7</td>\n",
       "      <td>15.0</td>\n",
       "      <td>14.8</td>\n",
       "      <td>83.2</td>\n",
       "      <td>10.3</td>\n",
       "      <td>0.2</td>\n",
       "      <td>1.6</td>\n",
       "      <td>2.2</td>\n",
       "      <td>2.5</td>\n",
       "      <td>27.6</td>\n",
       "      <td>24982.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>12.1</td>\n",
       "      <td>7.6</td>\n",
       "      <td>0.3</td>\n",
       "      <td>8.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>Accomack</td>\n",
       "      <td>VA</td>\n",
       "      <td>0.545</td>\n",
       "      <td>0.428</td>\n",
       "      <td>15755.0</td>\n",
       "      <td>8582.0</td>\n",
       "      <td>6737.0</td>\n",
       "      <td>0.542</td>\n",
       "      <td>0.447</td>\n",
       "      <td>16938.0</td>\n",
       "      <td>9172.0</td>\n",
       "      <td>7578.0</td>\n",
       "      <td>37.767072</td>\n",
       "      <td>-75.632346</td>\n",
       "      <td>1227.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>32840.0</td>\n",
       "      <td>16079.0</td>\n",
       "      <td>16761.0</td>\n",
       "      <td>8.8</td>\n",
       "      <td>60.3</td>\n",
       "      <td>28.3</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.7</td>\n",
       "      <td>0.0</td>\n",
       "      <td>24408.0</td>\n",
       "      <td>42260.0</td>\n",
       "      <td>2253.0</td>\n",
       "      <td>24266.0</td>\n",
       "      <td>1564.0</td>\n",
       "      <td>19.8</td>\n",
       "      <td>31.8</td>\n",
       "      <td>31.1</td>\n",
       "      <td>17.7</td>\n",
       "      <td>18.8</td>\n",
       "      <td>15.1</td>\n",
       "      <td>17.3</td>\n",
       "      <td>80.0</td>\n",
       "      <td>10.6</td>\n",
       "      <td>0.5</td>\n",
       "      <td>2.6</td>\n",
       "      <td>1.8</td>\n",
       "      <td>4.5</td>\n",
       "      <td>22.0</td>\n",
       "      <td>13837.0</td>\n",
       "      <td>74.6</td>\n",
       "      <td>18.1</td>\n",
       "      <td>7.1</td>\n",
       "      <td>0.2</td>\n",
       "      <td>5.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>Ada</td>\n",
       "      <td>ID</td>\n",
       "      <td>0.479</td>\n",
       "      <td>0.387</td>\n",
       "      <td>195587.0</td>\n",
       "      <td>93748.0</td>\n",
       "      <td>75676.0</td>\n",
       "      <td>0.504</td>\n",
       "      <td>0.465</td>\n",
       "      <td>259389.0</td>\n",
       "      <td>130699.0</td>\n",
       "      <td>120539.0</td>\n",
       "      <td>43.452658</td>\n",
       "      <td>-116.241552</td>\n",
       "      <td>17451.0</td>\n",
       "      <td>181.0</td>\n",
       "      <td>435117.0</td>\n",
       "      <td>217999.0</td>\n",
       "      <td>217118.0</td>\n",
       "      <td>7.9</td>\n",
       "      <td>85.2</td>\n",
       "      <td>1.2</td>\n",
       "      <td>0.4</td>\n",
       "      <td>2.6</td>\n",
       "      <td>0.1</td>\n",
       "      <td>316189.0</td>\n",
       "      <td>60151.0</td>\n",
       "      <td>1294.0</td>\n",
       "      <td>31642.0</td>\n",
       "      <td>725.0</td>\n",
       "      <td>11.8</td>\n",
       "      <td>13.1</td>\n",
       "      <td>43.0</td>\n",
       "      <td>16.6</td>\n",
       "      <td>25.0</td>\n",
       "      <td>6.9</td>\n",
       "      <td>8.4</td>\n",
       "      <td>80.7</td>\n",
       "      <td>7.7</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1.5</td>\n",
       "      <td>2.8</td>\n",
       "      <td>6.9</td>\n",
       "      <td>20.4</td>\n",
       "      <td>214984.0</td>\n",
       "      <td>78.3</td>\n",
       "      <td>15.0</td>\n",
       "      <td>6.6</td>\n",
       "      <td>0.1</td>\n",
       "      <td>4.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>Adair</td>\n",
       "      <td>IA</td>\n",
       "      <td>0.653</td>\n",
       "      <td>0.300</td>\n",
       "      <td>3759.0</td>\n",
       "      <td>2456.0</td>\n",
       "      <td>1127.0</td>\n",
       "      <td>0.697</td>\n",
       "      <td>0.286</td>\n",
       "      <td>4183.0</td>\n",
       "      <td>2917.0</td>\n",
       "      <td>1197.0</td>\n",
       "      <td>41.330756</td>\n",
       "      <td>-94.471059</td>\n",
       "      <td>222.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>7192.0</td>\n",
       "      <td>3552.0</td>\n",
       "      <td>3640.0</td>\n",
       "      <td>1.7</td>\n",
       "      <td>96.6</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5572.0</td>\n",
       "      <td>49477.0</td>\n",
       "      <td>2633.0</td>\n",
       "      <td>28861.0</td>\n",
       "      <td>2055.0</td>\n",
       "      <td>9.5</td>\n",
       "      <td>12.1</td>\n",
       "      <td>28.2</td>\n",
       "      <td>16.9</td>\n",
       "      <td>20.0</td>\n",
       "      <td>17.3</td>\n",
       "      <td>17.6</td>\n",
       "      <td>77.9</td>\n",
       "      <td>12.4</td>\n",
       "      <td>0.3</td>\n",
       "      <td>2.8</td>\n",
       "      <td>0.4</td>\n",
       "      <td>6.2</td>\n",
       "      <td>22.3</td>\n",
       "      <td>3680.0</td>\n",
       "      <td>73.8</td>\n",
       "      <td>15.3</td>\n",
       "      <td>10.4</td>\n",
       "      <td>0.5</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>Adair</td>\n",
       "      <td>KY</td>\n",
       "      <td>0.806</td>\n",
       "      <td>0.161</td>\n",
       "      <td>8231.0</td>\n",
       "      <td>6637.0</td>\n",
       "      <td>1323.0</td>\n",
       "      <td>0.830</td>\n",
       "      <td>0.159</td>\n",
       "      <td>8766.0</td>\n",
       "      <td>7275.0</td>\n",
       "      <td>1391.0</td>\n",
       "      <td>37.104598</td>\n",
       "      <td>-85.281297</td>\n",
       "      <td>517.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>19304.0</td>\n",
       "      <td>9632.0</td>\n",
       "      <td>9672.0</td>\n",
       "      <td>1.8</td>\n",
       "      <td>93.4</td>\n",
       "      <td>3.6</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>15280.0</td>\n",
       "      <td>36575.0</td>\n",
       "      <td>3426.0</td>\n",
       "      <td>18408.0</td>\n",
       "      <td>1010.0</td>\n",
       "      <td>21.5</td>\n",
       "      <td>27.1</td>\n",
       "      <td>28.5</td>\n",
       "      <td>15.9</td>\n",
       "      <td>19.7</td>\n",
       "      <td>12.2</td>\n",
       "      <td>23.8</td>\n",
       "      <td>84.5</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.6</td>\n",
       "      <td>0.5</td>\n",
       "      <td>3.4</td>\n",
       "      <td>22.2</td>\n",
       "      <td>7988.0</td>\n",
       "      <td>74.1</td>\n",
       "      <td>15.8</td>\n",
       "      <td>9.9</td>\n",
       "      <td>0.1</td>\n",
       "      <td>6.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "      <td>Adair</td>\n",
       "      <td>MO</td>\n",
       "      <td>0.594</td>\n",
       "      <td>0.345</td>\n",
       "      <td>10137.0</td>\n",
       "      <td>6019.0</td>\n",
       "      <td>3495.0</td>\n",
       "      <td>0.618</td>\n",
       "      <td>0.358</td>\n",
       "      <td>10337.0</td>\n",
       "      <td>6391.0</td>\n",
       "      <td>3705.0</td>\n",
       "      <td>40.190586</td>\n",
       "      <td>-92.600782</td>\n",
       "      <td>578.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>25437.0</td>\n",
       "      <td>12013.0</td>\n",
       "      <td>13424.0</td>\n",
       "      <td>2.3</td>\n",
       "      <td>90.5</td>\n",
       "      <td>2.4</td>\n",
       "      <td>0.2</td>\n",
       "      <td>2.3</td>\n",
       "      <td>0.1</td>\n",
       "      <td>20169.0</td>\n",
       "      <td>38750.0</td>\n",
       "      <td>2130.0</td>\n",
       "      <td>21778.0</td>\n",
       "      <td>1702.0</td>\n",
       "      <td>26.2</td>\n",
       "      <td>20.7</td>\n",
       "      <td>36.8</td>\n",
       "      <td>18.2</td>\n",
       "      <td>24.1</td>\n",
       "      <td>9.4</td>\n",
       "      <td>11.5</td>\n",
       "      <td>77.3</td>\n",
       "      <td>12.1</td>\n",
       "      <td>0.1</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.6</td>\n",
       "      <td>4.0</td>\n",
       "      <td>17.1</td>\n",
       "      <td>11274.0</td>\n",
       "      <td>73.6</td>\n",
       "      <td>20.9</td>\n",
       "      <td>5.3</td>\n",
       "      <td>0.2</td>\n",
       "      <td>5.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7</td>\n",
       "      <td>Adair</td>\n",
       "      <td>OK</td>\n",
       "      <td>0.735</td>\n",
       "      <td>0.212</td>\n",
       "      <td>6468.0</td>\n",
       "      <td>4753.0</td>\n",
       "      <td>1374.0</td>\n",
       "      <td>0.786</td>\n",
       "      <td>0.195</td>\n",
       "      <td>7108.0</td>\n",
       "      <td>5585.0</td>\n",
       "      <td>1387.0</td>\n",
       "      <td>35.884942</td>\n",
       "      <td>-94.658593</td>\n",
       "      <td>855.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>22136.0</td>\n",
       "      <td>10987.0</td>\n",
       "      <td>11149.0</td>\n",
       "      <td>6.4</td>\n",
       "      <td>40.8</td>\n",
       "      <td>0.3</td>\n",
       "      <td>41.7</td>\n",
       "      <td>0.6</td>\n",
       "      <td>0.2</td>\n",
       "      <td>16050.0</td>\n",
       "      <td>33366.0</td>\n",
       "      <td>1560.0</td>\n",
       "      <td>16576.0</td>\n",
       "      <td>820.0</td>\n",
       "      <td>30.8</td>\n",
       "      <td>43.0</td>\n",
       "      <td>23.9</td>\n",
       "      <td>17.6</td>\n",
       "      <td>19.4</td>\n",
       "      <td>14.0</td>\n",
       "      <td>25.2</td>\n",
       "      <td>84.4</td>\n",
       "      <td>8.5</td>\n",
       "      <td>0.1</td>\n",
       "      <td>2.8</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.2</td>\n",
       "      <td>23.1</td>\n",
       "      <td>8130.0</td>\n",
       "      <td>71.6</td>\n",
       "      <td>20.4</td>\n",
       "      <td>7.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>5.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>8</td>\n",
       "      <td>Adams</td>\n",
       "      <td>CO</td>\n",
       "      <td>0.421</td>\n",
       "      <td>0.494</td>\n",
       "      <td>175125.0</td>\n",
       "      <td>73807.0</td>\n",
       "      <td>86471.0</td>\n",
       "      <td>0.404</td>\n",
       "      <td>0.567</td>\n",
       "      <td>234599.0</td>\n",
       "      <td>94874.0</td>\n",
       "      <td>132951.0</td>\n",
       "      <td>39.874321</td>\n",
       "      <td>-104.336258</td>\n",
       "      <td>15538.0</td>\n",
       "      <td>263.0</td>\n",
       "      <td>487850.0</td>\n",
       "      <td>245840.0</td>\n",
       "      <td>242010.0</td>\n",
       "      <td>39.3</td>\n",
       "      <td>51.1</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>3.8</td>\n",
       "      <td>0.1</td>\n",
       "      <td>306416.0</td>\n",
       "      <td>64087.0</td>\n",
       "      <td>991.0</td>\n",
       "      <td>27487.0</td>\n",
       "      <td>366.0</td>\n",
       "      <td>12.2</td>\n",
       "      <td>16.4</td>\n",
       "      <td>29.8</td>\n",
       "      <td>18.9</td>\n",
       "      <td>24.1</td>\n",
       "      <td>13.7</td>\n",
       "      <td>13.5</td>\n",
       "      <td>77.7</td>\n",
       "      <td>11.2</td>\n",
       "      <td>3.8</td>\n",
       "      <td>1.2</td>\n",
       "      <td>1.1</td>\n",
       "      <td>5.0</td>\n",
       "      <td>29.2</td>\n",
       "      <td>246450.0</td>\n",
       "      <td>83.6</td>\n",
       "      <td>11.2</td>\n",
       "      <td>5.1</td>\n",
       "      <td>0.1</td>\n",
       "      <td>5.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9</td>\n",
       "      <td>Adams</td>\n",
       "      <td>IA</td>\n",
       "      <td>0.669</td>\n",
       "      <td>0.271</td>\n",
       "      <td>2082.0</td>\n",
       "      <td>1393.0</td>\n",
       "      <td>565.0</td>\n",
       "      <td>0.708</td>\n",
       "      <td>0.273</td>\n",
       "      <td>2158.0</td>\n",
       "      <td>1528.0</td>\n",
       "      <td>590.0</td>\n",
       "      <td>41.029036</td>\n",
       "      <td>-94.699326</td>\n",
       "      <td>87.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3785.0</td>\n",
       "      <td>1870.0</td>\n",
       "      <td>1915.0</td>\n",
       "      <td>1.2</td>\n",
       "      <td>96.8</td>\n",
       "      <td>0.4</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.6</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2992.0</td>\n",
       "      <td>49745.0</td>\n",
       "      <td>3996.0</td>\n",
       "      <td>27022.0</td>\n",
       "      <td>1948.0</td>\n",
       "      <td>11.2</td>\n",
       "      <td>8.6</td>\n",
       "      <td>37.4</td>\n",
       "      <td>14.3</td>\n",
       "      <td>17.0</td>\n",
       "      <td>13.1</td>\n",
       "      <td>18.3</td>\n",
       "      <td>80.7</td>\n",
       "      <td>9.6</td>\n",
       "      <td>0.2</td>\n",
       "      <td>3.3</td>\n",
       "      <td>0.8</td>\n",
       "      <td>5.3</td>\n",
       "      <td>19.6</td>\n",
       "      <td>1796.0</td>\n",
       "      <td>72.0</td>\n",
       "      <td>12.1</td>\n",
       "      <td>15.5</td>\n",
       "      <td>0.3</td>\n",
       "      <td>4.2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0     county state  percentage16_Donald_Trump  \\\n",
       "0           0  Abbeville    SC                      0.629   \n",
       "1           1     Acadia    LA                      0.773   \n",
       "2           2   Accomack    VA                      0.545   \n",
       "3           3        Ada    ID                      0.479   \n",
       "4           4      Adair    IA                      0.653   \n",
       "5           5      Adair    KY                      0.806   \n",
       "6           6      Adair    MO                      0.594   \n",
       "7           7      Adair    OK                      0.735   \n",
       "8           8      Adams    CO                      0.421   \n",
       "9           9      Adams    IA                      0.669   \n",
       "\n",
       "   percentage16_Hillary_Clinton  total_votes16  votes16_Donald_Trump  \\\n",
       "0                         0.346        10724.0                6742.0   \n",
       "1                         0.206        27386.0               21159.0   \n",
       "2                         0.428        15755.0                8582.0   \n",
       "3                         0.387       195587.0               93748.0   \n",
       "4                         0.300         3759.0                2456.0   \n",
       "5                         0.161         8231.0                6637.0   \n",
       "6                         0.345        10137.0                6019.0   \n",
       "7                         0.212         6468.0                4753.0   \n",
       "8                         0.494       175125.0               73807.0   \n",
       "9                         0.271         2082.0                1393.0   \n",
       "\n",
       "   votes16_Hillary_Clinton  percentage20_Donald_Trump  percentage20_Joe_Biden  \\\n",
       "0                   3712.0                      0.661                   0.330   \n",
       "1                   5638.0                      0.795                   0.191   \n",
       "2                   6737.0                      0.542                   0.447   \n",
       "3                  75676.0                      0.504                   0.465   \n",
       "4                   1127.0                      0.697                   0.286   \n",
       "5                   1323.0                      0.830                   0.159   \n",
       "6                   3495.0                      0.618                   0.358   \n",
       "7                   1374.0                      0.786                   0.195   \n",
       "8                  86471.0                      0.404                   0.567   \n",
       "9                    565.0                      0.708                   0.273   \n",
       "\n",
       "   total_votes20  votes20_Donald_Trump  votes20_Joe_Biden        lat  \\\n",
       "0        12433.0                8215.0             4101.0  34.223334   \n",
       "1        28425.0               22596.0             5443.0  30.295065   \n",
       "2        16938.0                9172.0             7578.0  37.767072   \n",
       "3       259389.0              130699.0           120539.0  43.452658   \n",
       "4         4183.0                2917.0             1197.0  41.330756   \n",
       "5         8766.0                7275.0             1391.0  37.104598   \n",
       "6        10337.0                6391.0             3705.0  40.190586   \n",
       "7         7108.0                5585.0             1387.0  35.884942   \n",
       "8       234599.0               94874.0           132951.0  39.874321   \n",
       "9         2158.0                1528.0              590.0  41.029036   \n",
       "\n",
       "         long    cases  deaths  TotalPop       Men     Women  Hispanic  White  \\\n",
       "0  -82.461707    805.0    17.0   24788.0   12044.0   12744.0       1.3   68.9   \n",
       "1  -92.414197   3182.0   102.0   62607.0   30433.0   32174.0       2.4   77.5   \n",
       "2  -75.632346   1227.0    19.0   32840.0   16079.0   16761.0       8.8   60.3   \n",
       "3 -116.241552  17451.0   181.0  435117.0  217999.0  217118.0       7.9   85.2   \n",
       "4  -94.471059    222.0     1.0    7192.0    3552.0    3640.0       1.7   96.6   \n",
       "5  -85.281297    517.0    22.0   19304.0    9632.0    9672.0       1.8   93.4   \n",
       "6  -92.600782    578.0     0.0   25437.0   12013.0   13424.0       2.3   90.5   \n",
       "7  -94.658593    855.0    11.0   22136.0   10987.0   11149.0       6.4   40.8   \n",
       "8 -104.336258  15538.0   263.0  487850.0  245840.0  242010.0      39.3   51.1   \n",
       "9  -94.699326     87.0     1.0    3785.0    1870.0    1915.0       1.2   96.8   \n",
       "\n",
       "   Black  Native  Asian  Pacific  VotingAgeCitizen   Income  IncomeErr  \\\n",
       "0   27.6     0.1    0.3      0.0           19452.0  35254.0     2259.0   \n",
       "1   17.6     0.1    0.1      0.0           45197.0  40492.0     2544.0   \n",
       "2   28.3     0.3    0.7      0.0           24408.0  42260.0     2253.0   \n",
       "3    1.2     0.4    2.6      0.1          316189.0  60151.0     1294.0   \n",
       "4    0.3     0.0    0.4      0.0            5572.0  49477.0     2633.0   \n",
       "5    3.6     0.1    0.1      0.0           15280.0  36575.0     3426.0   \n",
       "6    2.4     0.2    2.3      0.1           20169.0  38750.0     2130.0   \n",
       "7    0.3    41.7    0.6      0.2           16050.0  33366.0     1560.0   \n",
       "8    3.0     0.5    3.8      0.1          306416.0  64087.0      991.0   \n",
       "9    0.4     0.1    0.6      0.0            2992.0  49745.0     3996.0   \n",
       "\n",
       "   IncomePerCap  IncomePerCapErr  Poverty  ChildPoverty  Professional  \\\n",
       "0       19234.0            799.0     22.7          32.1          27.2   \n",
       "1       21591.0           1002.0     21.5          27.6          27.6   \n",
       "2       24266.0           1564.0     19.8          31.8          31.1   \n",
       "3       31642.0            725.0     11.8          13.1          43.0   \n",
       "4       28861.0           2055.0      9.5          12.1          28.2   \n",
       "5       18408.0           1010.0     21.5          27.1          28.5   \n",
       "6       21778.0           1702.0     26.2          20.7          36.8   \n",
       "7       16576.0            820.0     30.8          43.0          23.9   \n",
       "8       27487.0            366.0     12.2          16.4          29.8   \n",
       "9       27022.0           1948.0     11.2           8.6          37.4   \n",
       "\n",
       "   Service  Office  Construction  Production  Drive  Carpool  Transit  Walk  \\\n",
       "0     20.7    20.8          10.6        20.7   78.3     11.1      0.5   1.8   \n",
       "1     16.9    25.7          15.0        14.8   83.2     10.3      0.2   1.6   \n",
       "2     17.7    18.8          15.1        17.3   80.0     10.6      0.5   2.6   \n",
       "3     16.6    25.0           6.9         8.4   80.7      7.7      0.5   1.5   \n",
       "4     16.9    20.0          17.3        17.6   77.9     12.4      0.3   2.8   \n",
       "5     15.9    19.7          12.2        23.8   84.5      9.0      0.0   2.6   \n",
       "6     18.2    24.1           9.4        11.5   77.3     12.1      0.1   4.0   \n",
       "7     17.6    19.4          14.0        25.2   84.4      8.5      0.1   2.8   \n",
       "8     18.9    24.1          13.7        13.5   77.7     11.2      3.8   1.2   \n",
       "9     14.3    17.0          13.1        18.3   80.7      9.6      0.2   3.3   \n",
       "\n",
       "   OtherTransp  WorkAtHome  MeanCommute  Employed  PrivateWork  PublicWork  \\\n",
       "0          1.8         6.5         25.8    9505.0         78.8        13.3   \n",
       "1          2.2         2.5         27.6   24982.0         80.0        12.1   \n",
       "2          1.8         4.5         22.0   13837.0         74.6        18.1   \n",
       "3          2.8         6.9         20.4  214984.0         78.3        15.0   \n",
       "4          0.4         6.2         22.3    3680.0         73.8        15.3   \n",
       "5          0.5         3.4         22.2    7988.0         74.1        15.8   \n",
       "6          2.6         4.0         17.1   11274.0         73.6        20.9   \n",
       "7          1.0         3.2         23.1    8130.0         71.6        20.4   \n",
       "8          1.1         5.0         29.2  246450.0         83.6        11.2   \n",
       "9          0.8         5.3         19.6    1796.0         72.0        12.1   \n",
       "\n",
       "   SelfEmployed  FamilyWork  Unemployment  \n",
       "0           7.8         0.1           9.4  \n",
       "1           7.6         0.3           8.9  \n",
       "2           7.1         0.2           5.4  \n",
       "3           6.6         0.1           4.3  \n",
       "4          10.4         0.5           3.0  \n",
       "5           9.9         0.1           6.2  \n",
       "6           5.3         0.2           5.5  \n",
       "7           7.5         0.5           5.5  \n",
       "8           5.1         0.1           5.1  \n",
       "9          15.5         0.3           4.2  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Load county statistics (presidential election data) dataset and check dataframe\n",
    "file = \"county_statistics.csv\"\n",
    "voting_pres_df = pd.read_csv(file)\n",
    "voting_pres_df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Unnamed: 0', 'county', 'state', 'percentage16_Donald_Trump',\n",
       "       'percentage16_Hillary_Clinton', 'total_votes16', 'votes16_Donald_Trump',\n",
       "       'votes16_Hillary_Clinton', 'percentage20_Donald_Trump',\n",
       "       'percentage20_Joe_Biden', 'total_votes20', 'votes20_Donald_Trump',\n",
       "       'votes20_Joe_Biden', 'lat', 'long', 'cases', 'deaths', 'TotalPop',\n",
       "       'Men', 'Women', 'Hispanic', 'White', 'Black', 'Native', 'Asian',\n",
       "       'Pacific', 'VotingAgeCitizen', 'Income', 'IncomeErr', 'IncomePerCap',\n",
       "       'IncomePerCapErr', 'Poverty', 'ChildPoverty', 'Professional', 'Service',\n",
       "       'Office', 'Construction', 'Production', 'Drive', 'Carpool', 'Transit',\n",
       "       'Walk', 'OtherTransp', 'WorkAtHome', 'MeanCommute', 'Employed',\n",
       "       'PrivateWork', 'PublicWork', 'SelfEmployed', 'FamilyWork',\n",
       "       'Unemployment'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Show all columns in df\n",
    "voting_pres_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop unnamed: 0 column and empty 2020 percentage columns\n",
    "voting_pres_df.drop(\"Unnamed: 0\",inplace=True, axis=1)\n",
    "voting_pres_df = voting_pres_df[voting_pres_df['percentage20_Donald_Trump'].notna()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "county                             0\n",
       "state                              0\n",
       "percentage16_Donald_Trump       1404\n",
       "percentage16_Hillary_Clinton    1404\n",
       "total_votes16                   1404\n",
       "votes16_Donald_Trump            1404\n",
       "votes16_Hillary_Clinton         1404\n",
       "percentage20_Donald_Trump          0\n",
       "percentage20_Joe_Biden             0\n",
       "total_votes20                      0\n",
       "votes20_Donald_Trump               0\n",
       "votes20_Joe_Biden                  0\n",
       "lat                             1442\n",
       "long                            1442\n",
       "cases                           1442\n",
       "deaths                          1442\n",
       "TotalPop                        1402\n",
       "Men                             1402\n",
       "Women                           1402\n",
       "Hispanic                        1402\n",
       "White                           1402\n",
       "Black                           1402\n",
       "Native                          1402\n",
       "Asian                           1402\n",
       "Pacific                         1402\n",
       "VotingAgeCitizen                1402\n",
       "Income                          1402\n",
       "IncomeErr                       1402\n",
       "IncomePerCap                    1402\n",
       "IncomePerCapErr                 1402\n",
       "Poverty                         1402\n",
       "ChildPoverty                    1402\n",
       "Professional                    1402\n",
       "Service                         1402\n",
       "Office                          1402\n",
       "Construction                    1402\n",
       "Production                      1402\n",
       "Drive                           1402\n",
       "Carpool                         1402\n",
       "Transit                         1402\n",
       "Walk                            1402\n",
       "OtherTransp                     1402\n",
       "WorkAtHome                      1402\n",
       "MeanCommute                     1402\n",
       "Employed                        1402\n",
       "PrivateWork                     1402\n",
       "PublicWork                      1402\n",
       "SelfEmployed                    1402\n",
       "FamilyWork                      1402\n",
       "Unemployment                    1402\n",
       "dtype: int64"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check for null values\n",
    "voting_pres_df.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "county                           object\n",
       "state                            object\n",
       "percentage16_Donald_Trump       float64\n",
       "percentage16_Hillary_Clinton    float64\n",
       "total_votes16                   float64\n",
       "votes16_Donald_Trump            float64\n",
       "votes16_Hillary_Clinton         float64\n",
       "percentage20_Donald_Trump       float64\n",
       "percentage20_Joe_Biden          float64\n",
       "total_votes20                   float64\n",
       "votes20_Donald_Trump            float64\n",
       "votes20_Joe_Biden               float64\n",
       "lat                             float64\n",
       "long                            float64\n",
       "cases                           float64\n",
       "deaths                          float64\n",
       "TotalPop                        float64\n",
       "Men                             float64\n",
       "Women                           float64\n",
       "Hispanic                        float64\n",
       "White                           float64\n",
       "Black                           float64\n",
       "Native                          float64\n",
       "Asian                           float64\n",
       "Pacific                         float64\n",
       "VotingAgeCitizen                float64\n",
       "Income                          float64\n",
       "IncomeErr                       float64\n",
       "IncomePerCap                    float64\n",
       "IncomePerCapErr                 float64\n",
       "Poverty                         float64\n",
       "ChildPoverty                    float64\n",
       "Professional                    float64\n",
       "Service                         float64\n",
       "Office                          float64\n",
       "Construction                    float64\n",
       "Production                      float64\n",
       "Drive                           float64\n",
       "Carpool                         float64\n",
       "Transit                         float64\n",
       "Walk                            float64\n",
       "OtherTransp                     float64\n",
       "WorkAtHome                      float64\n",
       "MeanCommute                     float64\n",
       "Employed                        float64\n",
       "PrivateWork                     float64\n",
       "PublicWork                      float64\n",
       "SelfEmployed                    float64\n",
       "FamilyWork                      float64\n",
       "Unemployment                    float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Show columns data type\n",
    "voting_pres_df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['SC', 'LA', 'VA', 'ID', 'IA', 'KY', 'MO', 'OK', 'CO', 'IL', 'IN',\n",
       "       'MS', 'ND', 'NE', 'OH', 'PA', 'WA', 'WI', 'VT', 'MN', 'FL', 'NC',\n",
       "       'CA', 'NY', 'WY', 'MI', 'MD', 'KS', 'TN', 'TX', 'AZ', 'GA', 'AR',\n",
       "       'NJ', 'SD', 'AL', 'OR', 'WV', 'MA', 'UT', 'MT', 'NM', 'RI', 'NH',\n",
       "       'NV', 'ME', 'DC', 'CT', 'HI', 'DE', 'AK'], dtype=object)"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "voting_pres_df[\"state\"].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ME    375\n",
       "MA    333\n",
       "TX    254\n",
       "VT    245\n",
       "NH    237\n",
       "CT    169\n",
       "GA    159\n",
       "VA    133\n",
       "KY    120\n",
       "MO    115\n",
       "KS    105\n",
       "IL    102\n",
       "NC    100\n",
       "IA     99\n",
       "TN     95\n",
       "NE     93\n",
       "IN     92\n",
       "OH     88\n",
       "MN     87\n",
       "MI     83\n",
       "MS     82\n",
       "OK     77\n",
       "AR     75\n",
       "WI     72\n",
       "FL     67\n",
       "AL     67\n",
       "PA     67\n",
       "SD     66\n",
       "CO     64\n",
       "LA     64\n",
       "NY     62\n",
       "CA     58\n",
       "MT     56\n",
       "WV     55\n",
       "ND     53\n",
       "SC     46\n",
       "ID     44\n",
       "AK     40\n",
       "WA     39\n",
       "RI     39\n",
       "OR     36\n",
       "NM     33\n",
       "UT     29\n",
       "MD     24\n",
       "WY     23\n",
       "NJ     21\n",
       "NV     17\n",
       "AZ     15\n",
       "DC      8\n",
       "HI      4\n",
       "DE      3\n",
       "Name: state, dtype: int64"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check number of records per state\n",
    "voting_pres_df[\"state\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Update state column to show full state name\n",
    "# Create state dictionary\n",
    "us_state_full = {\n",
    "'AL': 'Alabama', 'AK': 'Alaska', 'AZ': 'Arizona', 'AR': 'Arkansas', 'CA': 'California', 'CO': 'Colorado',\n",
    "'CT': 'Connecticut', 'DE': 'Delaware','DC': 'District of Columbia', 'FL': 'Florida', 'GA': 'Georgia', 'HI': 'Hawaii', 'ID': 'Idaho',\n",
    "'IL': 'Illinois', 'IN': 'Indiana', 'IA': 'Iowa', 'KS': 'Kansas', 'KY': 'Kentucky', 'LA': 'Louisiana',\n",
    "'ME': 'Maine', 'MD': 'Maryland', 'MA': 'Massachusetts', 'MI': 'Michigan', 'MN': 'Minnesota', 'MS': 'Mississippi',\n",
    "'MO': 'Missouri', 'MT': 'Montana', 'NE': 'Nebraska', 'NV': 'Nevada', 'NH': 'New Hampshire', 'NJ': 'New Jersey',\n",
    "'NM': 'New Mexico', 'NY': 'New York', 'NC': 'North Carolina', 'ND': 'North Dakota', 'OH': 'Ohio', 'OK': 'Oklahoma',\n",
    "'OR': 'Oregon', 'PA': 'Pennsylvania','PR': 'Puerto Rico', 'RI': 'Rhode Island', 'SC': 'South Carolina', 'SD': 'South Dakota',\n",
    "'TN': 'Tennessee', 'TX': 'Texas', 'UT': 'Utah', 'VT': 'Vermont', 'VA': 'Virginia', 'WA': 'Washington',\n",
    "'WV': 'West Virginia', 'WI': 'Wisconsin', 'WY': 'Wyoming'}\n",
    "\n",
    "us_state_abbrev = {\n",
    "'Alabama': 'AL', 'Alaska': 'AK', 'Arizona': 'AZ', 'Arkansas': 'AR', 'California': 'CA', 'Colorado': 'CO',\n",
    "'Connecticut': 'CT', 'Delaware': 'DE','District of Columbia':'DC', 'Florida': 'FL', 'Georgia': 'GA', 'Hawaii': 'HI', 'Idaho': 'ID',\n",
    "'Illinois': 'IL', 'Indiana': 'IN', 'Iowa': 'IA', 'Kansas': 'KS', 'Kentucky': 'KY', 'Louisiana': 'LA',\n",
    "'Maine': 'ME', 'Maryland': 'MD', 'Massachusetts': 'MA', 'Michigan': 'MI', 'Minnesota': 'MN', 'Mississippi': 'MS',\n",
    "'Missouri': 'MO', 'Montana': 'MT', 'Nebraska': 'NE', 'Nevada': 'NV', 'New Hampshire': 'NH', 'New Jersey': 'NJ',\n",
    "'New Mexico': 'NM', 'New York': 'NY', 'North Carolina': 'NC', 'North Dakota': 'ND', 'Ohio': 'OH', 'Oklahoma': 'OK',\n",
    "'Oregon': 'OR', 'Pennsylvania': 'PA','Puerto Rico':'PR', 'Rhode Island': 'RI', 'South Carolina': 'SC', 'South Dakota': 'SD',\n",
    "'Tennessee': 'TN', 'Texas': 'TX', 'Utah': 'UT', 'Vermont': 'VT', 'Virginia': 'VA', 'Washington': 'WA',\n",
    "'West Virginia': 'WV', 'Wisconsin': 'WI', 'Wyoming': 'WY'}\n",
    "# (Not Needed) Update values in state column\n",
    "#voting_pres_df[\"state\"] = voting_pres_df[\"state\"].map(us_state_full).fillna(voting_pres_df[\"state\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0       SC\n",
       "1       LA\n",
       "2       VA\n",
       "3       ID\n",
       "4       IA\n",
       "        ..\n",
       "4651    RI\n",
       "4652    VT\n",
       "4654    MA\n",
       "4655    MA\n",
       "4656    ME\n",
       "Name: state, Length: 4490, dtype: object"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "voting_pres_df[\"state\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Add \"County\" to county name column\n",
    "voting_pres_df[\"county\"] = voting_pres_df[\"county\"] + ' ' + 'County'\n",
    "voting_pres_df[\"county\"] = voting_pres_df[\"county\"].str.lower()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "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>state</th>\n",
       "      <th>percentage16_Donald_Trump</th>\n",
       "      <th>percentage16_Hillary_Clinton</th>\n",
       "      <th>total_votes16</th>\n",
       "      <th>votes16_Donald_Trump</th>\n",
       "      <th>votes16_Hillary_Clinton</th>\n",
       "      <th>percentage20_Donald_Trump</th>\n",
       "      <th>percentage20_Joe_Biden</th>\n",
       "      <th>total_votes20</th>\n",
       "      <th>votes20_Donald_Trump</th>\n",
       "      <th>votes20_Joe_Biden</th>\n",
       "      <th>lat</th>\n",
       "      <th>long</th>\n",
       "      <th>cases</th>\n",
       "      <th>deaths</th>\n",
       "      <th>TotalPop</th>\n",
       "      <th>Men</th>\n",
       "      <th>Women</th>\n",
       "      <th>Hispanic</th>\n",
       "      <th>White</th>\n",
       "      <th>Black</th>\n",
       "      <th>Native</th>\n",
       "      <th>Asian</th>\n",
       "      <th>Pacific</th>\n",
       "      <th>VotingAgeCitizen</th>\n",
       "      <th>Income</th>\n",
       "      <th>IncomeErr</th>\n",
       "      <th>IncomePerCap</th>\n",
       "      <th>IncomePerCapErr</th>\n",
       "      <th>Poverty</th>\n",
       "      <th>ChildPoverty</th>\n",
       "      <th>Professional</th>\n",
       "      <th>Service</th>\n",
       "      <th>Office</th>\n",
       "      <th>Construction</th>\n",
       "      <th>Production</th>\n",
       "      <th>Drive</th>\n",
       "      <th>Carpool</th>\n",
       "      <th>Transit</th>\n",
       "      <th>Walk</th>\n",
       "      <th>OtherTransp</th>\n",
       "      <th>WorkAtHome</th>\n",
       "      <th>MeanCommute</th>\n",
       "      <th>Employed</th>\n",
       "      <th>PrivateWork</th>\n",
       "      <th>PublicWork</th>\n",
       "      <th>SelfEmployed</th>\n",
       "      <th>FamilyWork</th>\n",
       "      <th>Unemployment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>abbeville county</td>\n",
       "      <td>SC</td>\n",
       "      <td>0.629</td>\n",
       "      <td>0.346</td>\n",
       "      <td>10724.0</td>\n",
       "      <td>6742.0</td>\n",
       "      <td>3712.0</td>\n",
       "      <td>0.661</td>\n",
       "      <td>0.330</td>\n",
       "      <td>12433.0</td>\n",
       "      <td>8215.0</td>\n",
       "      <td>4101.0</td>\n",
       "      <td>34.223334</td>\n",
       "      <td>-82.461707</td>\n",
       "      <td>805.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>24788.0</td>\n",
       "      <td>12044.0</td>\n",
       "      <td>12744.0</td>\n",
       "      <td>1.3</td>\n",
       "      <td>68.9</td>\n",
       "      <td>27.6</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>19452.0</td>\n",
       "      <td>35254.0</td>\n",
       "      <td>2259.0</td>\n",
       "      <td>19234.0</td>\n",
       "      <td>799.0</td>\n",
       "      <td>22.7</td>\n",
       "      <td>32.1</td>\n",
       "      <td>27.2</td>\n",
       "      <td>20.7</td>\n",
       "      <td>20.8</td>\n",
       "      <td>10.6</td>\n",
       "      <td>20.7</td>\n",
       "      <td>78.3</td>\n",
       "      <td>11.1</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1.8</td>\n",
       "      <td>1.8</td>\n",
       "      <td>6.5</td>\n",
       "      <td>25.8</td>\n",
       "      <td>9505.0</td>\n",
       "      <td>78.8</td>\n",
       "      <td>13.3</td>\n",
       "      <td>7.8</td>\n",
       "      <td>0.1</td>\n",
       "      <td>9.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>acadia county</td>\n",
       "      <td>LA</td>\n",
       "      <td>0.773</td>\n",
       "      <td>0.206</td>\n",
       "      <td>27386.0</td>\n",
       "      <td>21159.0</td>\n",
       "      <td>5638.0</td>\n",
       "      <td>0.795</td>\n",
       "      <td>0.191</td>\n",
       "      <td>28425.0</td>\n",
       "      <td>22596.0</td>\n",
       "      <td>5443.0</td>\n",
       "      <td>30.295065</td>\n",
       "      <td>-92.414197</td>\n",
       "      <td>3182.0</td>\n",
       "      <td>102.0</td>\n",
       "      <td>62607.0</td>\n",
       "      <td>30433.0</td>\n",
       "      <td>32174.0</td>\n",
       "      <td>2.4</td>\n",
       "      <td>77.5</td>\n",
       "      <td>17.6</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>45197.0</td>\n",
       "      <td>40492.0</td>\n",
       "      <td>2544.0</td>\n",
       "      <td>21591.0</td>\n",
       "      <td>1002.0</td>\n",
       "      <td>21.5</td>\n",
       "      <td>27.6</td>\n",
       "      <td>27.6</td>\n",
       "      <td>16.9</td>\n",
       "      <td>25.7</td>\n",
       "      <td>15.0</td>\n",
       "      <td>14.8</td>\n",
       "      <td>83.2</td>\n",
       "      <td>10.3</td>\n",
       "      <td>0.2</td>\n",
       "      <td>1.6</td>\n",
       "      <td>2.2</td>\n",
       "      <td>2.5</td>\n",
       "      <td>27.6</td>\n",
       "      <td>24982.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>12.1</td>\n",
       "      <td>7.6</td>\n",
       "      <td>0.3</td>\n",
       "      <td>8.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>accomack county</td>\n",
       "      <td>VA</td>\n",
       "      <td>0.545</td>\n",
       "      <td>0.428</td>\n",
       "      <td>15755.0</td>\n",
       "      <td>8582.0</td>\n",
       "      <td>6737.0</td>\n",
       "      <td>0.542</td>\n",
       "      <td>0.447</td>\n",
       "      <td>16938.0</td>\n",
       "      <td>9172.0</td>\n",
       "      <td>7578.0</td>\n",
       "      <td>37.767072</td>\n",
       "      <td>-75.632346</td>\n",
       "      <td>1227.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>32840.0</td>\n",
       "      <td>16079.0</td>\n",
       "      <td>16761.0</td>\n",
       "      <td>8.8</td>\n",
       "      <td>60.3</td>\n",
       "      <td>28.3</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.7</td>\n",
       "      <td>0.0</td>\n",
       "      <td>24408.0</td>\n",
       "      <td>42260.0</td>\n",
       "      <td>2253.0</td>\n",
       "      <td>24266.0</td>\n",
       "      <td>1564.0</td>\n",
       "      <td>19.8</td>\n",
       "      <td>31.8</td>\n",
       "      <td>31.1</td>\n",
       "      <td>17.7</td>\n",
       "      <td>18.8</td>\n",
       "      <td>15.1</td>\n",
       "      <td>17.3</td>\n",
       "      <td>80.0</td>\n",
       "      <td>10.6</td>\n",
       "      <td>0.5</td>\n",
       "      <td>2.6</td>\n",
       "      <td>1.8</td>\n",
       "      <td>4.5</td>\n",
       "      <td>22.0</td>\n",
       "      <td>13837.0</td>\n",
       "      <td>74.6</td>\n",
       "      <td>18.1</td>\n",
       "      <td>7.1</td>\n",
       "      <td>0.2</td>\n",
       "      <td>5.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ada county</td>\n",
       "      <td>ID</td>\n",
       "      <td>0.479</td>\n",
       "      <td>0.387</td>\n",
       "      <td>195587.0</td>\n",
       "      <td>93748.0</td>\n",
       "      <td>75676.0</td>\n",
       "      <td>0.504</td>\n",
       "      <td>0.465</td>\n",
       "      <td>259389.0</td>\n",
       "      <td>130699.0</td>\n",
       "      <td>120539.0</td>\n",
       "      <td>43.452658</td>\n",
       "      <td>-116.241552</td>\n",
       "      <td>17451.0</td>\n",
       "      <td>181.0</td>\n",
       "      <td>435117.0</td>\n",
       "      <td>217999.0</td>\n",
       "      <td>217118.0</td>\n",
       "      <td>7.9</td>\n",
       "      <td>85.2</td>\n",
       "      <td>1.2</td>\n",
       "      <td>0.4</td>\n",
       "      <td>2.6</td>\n",
       "      <td>0.1</td>\n",
       "      <td>316189.0</td>\n",
       "      <td>60151.0</td>\n",
       "      <td>1294.0</td>\n",
       "      <td>31642.0</td>\n",
       "      <td>725.0</td>\n",
       "      <td>11.8</td>\n",
       "      <td>13.1</td>\n",
       "      <td>43.0</td>\n",
       "      <td>16.6</td>\n",
       "      <td>25.0</td>\n",
       "      <td>6.9</td>\n",
       "      <td>8.4</td>\n",
       "      <td>80.7</td>\n",
       "      <td>7.7</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1.5</td>\n",
       "      <td>2.8</td>\n",
       "      <td>6.9</td>\n",
       "      <td>20.4</td>\n",
       "      <td>214984.0</td>\n",
       "      <td>78.3</td>\n",
       "      <td>15.0</td>\n",
       "      <td>6.6</td>\n",
       "      <td>0.1</td>\n",
       "      <td>4.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>adair county</td>\n",
       "      <td>IA</td>\n",
       "      <td>0.653</td>\n",
       "      <td>0.300</td>\n",
       "      <td>3759.0</td>\n",
       "      <td>2456.0</td>\n",
       "      <td>1127.0</td>\n",
       "      <td>0.697</td>\n",
       "      <td>0.286</td>\n",
       "      <td>4183.0</td>\n",
       "      <td>2917.0</td>\n",
       "      <td>1197.0</td>\n",
       "      <td>41.330756</td>\n",
       "      <td>-94.471059</td>\n",
       "      <td>222.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>7192.0</td>\n",
       "      <td>3552.0</td>\n",
       "      <td>3640.0</td>\n",
       "      <td>1.7</td>\n",
       "      <td>96.6</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5572.0</td>\n",
       "      <td>49477.0</td>\n",
       "      <td>2633.0</td>\n",
       "      <td>28861.0</td>\n",
       "      <td>2055.0</td>\n",
       "      <td>9.5</td>\n",
       "      <td>12.1</td>\n",
       "      <td>28.2</td>\n",
       "      <td>16.9</td>\n",
       "      <td>20.0</td>\n",
       "      <td>17.3</td>\n",
       "      <td>17.6</td>\n",
       "      <td>77.9</td>\n",
       "      <td>12.4</td>\n",
       "      <td>0.3</td>\n",
       "      <td>2.8</td>\n",
       "      <td>0.4</td>\n",
       "      <td>6.2</td>\n",
       "      <td>22.3</td>\n",
       "      <td>3680.0</td>\n",
       "      <td>73.8</td>\n",
       "      <td>15.3</td>\n",
       "      <td>10.4</td>\n",
       "      <td>0.5</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>adair county</td>\n",
       "      <td>KY</td>\n",
       "      <td>0.806</td>\n",
       "      <td>0.161</td>\n",
       "      <td>8231.0</td>\n",
       "      <td>6637.0</td>\n",
       "      <td>1323.0</td>\n",
       "      <td>0.830</td>\n",
       "      <td>0.159</td>\n",
       "      <td>8766.0</td>\n",
       "      <td>7275.0</td>\n",
       "      <td>1391.0</td>\n",
       "      <td>37.104598</td>\n",
       "      <td>-85.281297</td>\n",
       "      <td>517.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>19304.0</td>\n",
       "      <td>9632.0</td>\n",
       "      <td>9672.0</td>\n",
       "      <td>1.8</td>\n",
       "      <td>93.4</td>\n",
       "      <td>3.6</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>15280.0</td>\n",
       "      <td>36575.0</td>\n",
       "      <td>3426.0</td>\n",
       "      <td>18408.0</td>\n",
       "      <td>1010.0</td>\n",
       "      <td>21.5</td>\n",
       "      <td>27.1</td>\n",
       "      <td>28.5</td>\n",
       "      <td>15.9</td>\n",
       "      <td>19.7</td>\n",
       "      <td>12.2</td>\n",
       "      <td>23.8</td>\n",
       "      <td>84.5</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.6</td>\n",
       "      <td>0.5</td>\n",
       "      <td>3.4</td>\n",
       "      <td>22.2</td>\n",
       "      <td>7988.0</td>\n",
       "      <td>74.1</td>\n",
       "      <td>15.8</td>\n",
       "      <td>9.9</td>\n",
       "      <td>0.1</td>\n",
       "      <td>6.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>adair county</td>\n",
       "      <td>MO</td>\n",
       "      <td>0.594</td>\n",
       "      <td>0.345</td>\n",
       "      <td>10137.0</td>\n",
       "      <td>6019.0</td>\n",
       "      <td>3495.0</td>\n",
       "      <td>0.618</td>\n",
       "      <td>0.358</td>\n",
       "      <td>10337.0</td>\n",
       "      <td>6391.0</td>\n",
       "      <td>3705.0</td>\n",
       "      <td>40.190586</td>\n",
       "      <td>-92.600782</td>\n",
       "      <td>578.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>25437.0</td>\n",
       "      <td>12013.0</td>\n",
       "      <td>13424.0</td>\n",
       "      <td>2.3</td>\n",
       "      <td>90.5</td>\n",
       "      <td>2.4</td>\n",
       "      <td>0.2</td>\n",
       "      <td>2.3</td>\n",
       "      <td>0.1</td>\n",
       "      <td>20169.0</td>\n",
       "      <td>38750.0</td>\n",
       "      <td>2130.0</td>\n",
       "      <td>21778.0</td>\n",
       "      <td>1702.0</td>\n",
       "      <td>26.2</td>\n",
       "      <td>20.7</td>\n",
       "      <td>36.8</td>\n",
       "      <td>18.2</td>\n",
       "      <td>24.1</td>\n",
       "      <td>9.4</td>\n",
       "      <td>11.5</td>\n",
       "      <td>77.3</td>\n",
       "      <td>12.1</td>\n",
       "      <td>0.1</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.6</td>\n",
       "      <td>4.0</td>\n",
       "      <td>17.1</td>\n",
       "      <td>11274.0</td>\n",
       "      <td>73.6</td>\n",
       "      <td>20.9</td>\n",
       "      <td>5.3</td>\n",
       "      <td>0.2</td>\n",
       "      <td>5.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>adair county</td>\n",
       "      <td>OK</td>\n",
       "      <td>0.735</td>\n",
       "      <td>0.212</td>\n",
       "      <td>6468.0</td>\n",
       "      <td>4753.0</td>\n",
       "      <td>1374.0</td>\n",
       "      <td>0.786</td>\n",
       "      <td>0.195</td>\n",
       "      <td>7108.0</td>\n",
       "      <td>5585.0</td>\n",
       "      <td>1387.0</td>\n",
       "      <td>35.884942</td>\n",
       "      <td>-94.658593</td>\n",
       "      <td>855.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>22136.0</td>\n",
       "      <td>10987.0</td>\n",
       "      <td>11149.0</td>\n",
       "      <td>6.4</td>\n",
       "      <td>40.8</td>\n",
       "      <td>0.3</td>\n",
       "      <td>41.7</td>\n",
       "      <td>0.6</td>\n",
       "      <td>0.2</td>\n",
       "      <td>16050.0</td>\n",
       "      <td>33366.0</td>\n",
       "      <td>1560.0</td>\n",
       "      <td>16576.0</td>\n",
       "      <td>820.0</td>\n",
       "      <td>30.8</td>\n",
       "      <td>43.0</td>\n",
       "      <td>23.9</td>\n",
       "      <td>17.6</td>\n",
       "      <td>19.4</td>\n",
       "      <td>14.0</td>\n",
       "      <td>25.2</td>\n",
       "      <td>84.4</td>\n",
       "      <td>8.5</td>\n",
       "      <td>0.1</td>\n",
       "      <td>2.8</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.2</td>\n",
       "      <td>23.1</td>\n",
       "      <td>8130.0</td>\n",
       "      <td>71.6</td>\n",
       "      <td>20.4</td>\n",
       "      <td>7.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>5.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>adams county</td>\n",
       "      <td>CO</td>\n",
       "      <td>0.421</td>\n",
       "      <td>0.494</td>\n",
       "      <td>175125.0</td>\n",
       "      <td>73807.0</td>\n",
       "      <td>86471.0</td>\n",
       "      <td>0.404</td>\n",
       "      <td>0.567</td>\n",
       "      <td>234599.0</td>\n",
       "      <td>94874.0</td>\n",
       "      <td>132951.0</td>\n",
       "      <td>39.874321</td>\n",
       "      <td>-104.336258</td>\n",
       "      <td>15538.0</td>\n",
       "      <td>263.0</td>\n",
       "      <td>487850.0</td>\n",
       "      <td>245840.0</td>\n",
       "      <td>242010.0</td>\n",
       "      <td>39.3</td>\n",
       "      <td>51.1</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>3.8</td>\n",
       "      <td>0.1</td>\n",
       "      <td>306416.0</td>\n",
       "      <td>64087.0</td>\n",
       "      <td>991.0</td>\n",
       "      <td>27487.0</td>\n",
       "      <td>366.0</td>\n",
       "      <td>12.2</td>\n",
       "      <td>16.4</td>\n",
       "      <td>29.8</td>\n",
       "      <td>18.9</td>\n",
       "      <td>24.1</td>\n",
       "      <td>13.7</td>\n",
       "      <td>13.5</td>\n",
       "      <td>77.7</td>\n",
       "      <td>11.2</td>\n",
       "      <td>3.8</td>\n",
       "      <td>1.2</td>\n",
       "      <td>1.1</td>\n",
       "      <td>5.0</td>\n",
       "      <td>29.2</td>\n",
       "      <td>246450.0</td>\n",
       "      <td>83.6</td>\n",
       "      <td>11.2</td>\n",
       "      <td>5.1</td>\n",
       "      <td>0.1</td>\n",
       "      <td>5.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>adams county</td>\n",
       "      <td>IA</td>\n",
       "      <td>0.669</td>\n",
       "      <td>0.271</td>\n",
       "      <td>2082.0</td>\n",
       "      <td>1393.0</td>\n",
       "      <td>565.0</td>\n",
       "      <td>0.708</td>\n",
       "      <td>0.273</td>\n",
       "      <td>2158.0</td>\n",
       "      <td>1528.0</td>\n",
       "      <td>590.0</td>\n",
       "      <td>41.029036</td>\n",
       "      <td>-94.699326</td>\n",
       "      <td>87.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3785.0</td>\n",
       "      <td>1870.0</td>\n",
       "      <td>1915.0</td>\n",
       "      <td>1.2</td>\n",
       "      <td>96.8</td>\n",
       "      <td>0.4</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.6</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2992.0</td>\n",
       "      <td>49745.0</td>\n",
       "      <td>3996.0</td>\n",
       "      <td>27022.0</td>\n",
       "      <td>1948.0</td>\n",
       "      <td>11.2</td>\n",
       "      <td>8.6</td>\n",
       "      <td>37.4</td>\n",
       "      <td>14.3</td>\n",
       "      <td>17.0</td>\n",
       "      <td>13.1</td>\n",
       "      <td>18.3</td>\n",
       "      <td>80.7</td>\n",
       "      <td>9.6</td>\n",
       "      <td>0.2</td>\n",
       "      <td>3.3</td>\n",
       "      <td>0.8</td>\n",
       "      <td>5.3</td>\n",
       "      <td>19.6</td>\n",
       "      <td>1796.0</td>\n",
       "      <td>72.0</td>\n",
       "      <td>12.1</td>\n",
       "      <td>15.5</td>\n",
       "      <td>0.3</td>\n",
       "      <td>4.2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             county state  percentage16_Donald_Trump  \\\n",
       "0  abbeville county    SC                      0.629   \n",
       "1     acadia county    LA                      0.773   \n",
       "2   accomack county    VA                      0.545   \n",
       "3        ada county    ID                      0.479   \n",
       "4      adair county    IA                      0.653   \n",
       "5      adair county    KY                      0.806   \n",
       "6      adair county    MO                      0.594   \n",
       "7      adair county    OK                      0.735   \n",
       "8      adams county    CO                      0.421   \n",
       "9      adams county    IA                      0.669   \n",
       "\n",
       "   percentage16_Hillary_Clinton  total_votes16  votes16_Donald_Trump  \\\n",
       "0                         0.346        10724.0                6742.0   \n",
       "1                         0.206        27386.0               21159.0   \n",
       "2                         0.428        15755.0                8582.0   \n",
       "3                         0.387       195587.0               93748.0   \n",
       "4                         0.300         3759.0                2456.0   \n",
       "5                         0.161         8231.0                6637.0   \n",
       "6                         0.345        10137.0                6019.0   \n",
       "7                         0.212         6468.0                4753.0   \n",
       "8                         0.494       175125.0               73807.0   \n",
       "9                         0.271         2082.0                1393.0   \n",
       "\n",
       "   votes16_Hillary_Clinton  percentage20_Donald_Trump  percentage20_Joe_Biden  \\\n",
       "0                   3712.0                      0.661                   0.330   \n",
       "1                   5638.0                      0.795                   0.191   \n",
       "2                   6737.0                      0.542                   0.447   \n",
       "3                  75676.0                      0.504                   0.465   \n",
       "4                   1127.0                      0.697                   0.286   \n",
       "5                   1323.0                      0.830                   0.159   \n",
       "6                   3495.0                      0.618                   0.358   \n",
       "7                   1374.0                      0.786                   0.195   \n",
       "8                  86471.0                      0.404                   0.567   \n",
       "9                    565.0                      0.708                   0.273   \n",
       "\n",
       "   total_votes20  votes20_Donald_Trump  votes20_Joe_Biden        lat  \\\n",
       "0        12433.0                8215.0             4101.0  34.223334   \n",
       "1        28425.0               22596.0             5443.0  30.295065   \n",
       "2        16938.0                9172.0             7578.0  37.767072   \n",
       "3       259389.0              130699.0           120539.0  43.452658   \n",
       "4         4183.0                2917.0             1197.0  41.330756   \n",
       "5         8766.0                7275.0             1391.0  37.104598   \n",
       "6        10337.0                6391.0             3705.0  40.190586   \n",
       "7         7108.0                5585.0             1387.0  35.884942   \n",
       "8       234599.0               94874.0           132951.0  39.874321   \n",
       "9         2158.0                1528.0              590.0  41.029036   \n",
       "\n",
       "         long    cases  deaths  TotalPop       Men     Women  Hispanic  White  \\\n",
       "0  -82.461707    805.0    17.0   24788.0   12044.0   12744.0       1.3   68.9   \n",
       "1  -92.414197   3182.0   102.0   62607.0   30433.0   32174.0       2.4   77.5   \n",
       "2  -75.632346   1227.0    19.0   32840.0   16079.0   16761.0       8.8   60.3   \n",
       "3 -116.241552  17451.0   181.0  435117.0  217999.0  217118.0       7.9   85.2   \n",
       "4  -94.471059    222.0     1.0    7192.0    3552.0    3640.0       1.7   96.6   \n",
       "5  -85.281297    517.0    22.0   19304.0    9632.0    9672.0       1.8   93.4   \n",
       "6  -92.600782    578.0     0.0   25437.0   12013.0   13424.0       2.3   90.5   \n",
       "7  -94.658593    855.0    11.0   22136.0   10987.0   11149.0       6.4   40.8   \n",
       "8 -104.336258  15538.0   263.0  487850.0  245840.0  242010.0      39.3   51.1   \n",
       "9  -94.699326     87.0     1.0    3785.0    1870.0    1915.0       1.2   96.8   \n",
       "\n",
       "   Black  Native  Asian  Pacific  VotingAgeCitizen   Income  IncomeErr  \\\n",
       "0   27.6     0.1    0.3      0.0           19452.0  35254.0     2259.0   \n",
       "1   17.6     0.1    0.1      0.0           45197.0  40492.0     2544.0   \n",
       "2   28.3     0.3    0.7      0.0           24408.0  42260.0     2253.0   \n",
       "3    1.2     0.4    2.6      0.1          316189.0  60151.0     1294.0   \n",
       "4    0.3     0.0    0.4      0.0            5572.0  49477.0     2633.0   \n",
       "5    3.6     0.1    0.1      0.0           15280.0  36575.0     3426.0   \n",
       "6    2.4     0.2    2.3      0.1           20169.0  38750.0     2130.0   \n",
       "7    0.3    41.7    0.6      0.2           16050.0  33366.0     1560.0   \n",
       "8    3.0     0.5    3.8      0.1          306416.0  64087.0      991.0   \n",
       "9    0.4     0.1    0.6      0.0            2992.0  49745.0     3996.0   \n",
       "\n",
       "   IncomePerCap  IncomePerCapErr  Poverty  ChildPoverty  Professional  \\\n",
       "0       19234.0            799.0     22.7          32.1          27.2   \n",
       "1       21591.0           1002.0     21.5          27.6          27.6   \n",
       "2       24266.0           1564.0     19.8          31.8          31.1   \n",
       "3       31642.0            725.0     11.8          13.1          43.0   \n",
       "4       28861.0           2055.0      9.5          12.1          28.2   \n",
       "5       18408.0           1010.0     21.5          27.1          28.5   \n",
       "6       21778.0           1702.0     26.2          20.7          36.8   \n",
       "7       16576.0            820.0     30.8          43.0          23.9   \n",
       "8       27487.0            366.0     12.2          16.4          29.8   \n",
       "9       27022.0           1948.0     11.2           8.6          37.4   \n",
       "\n",
       "   Service  Office  Construction  Production  Drive  Carpool  Transit  Walk  \\\n",
       "0     20.7    20.8          10.6        20.7   78.3     11.1      0.5   1.8   \n",
       "1     16.9    25.7          15.0        14.8   83.2     10.3      0.2   1.6   \n",
       "2     17.7    18.8          15.1        17.3   80.0     10.6      0.5   2.6   \n",
       "3     16.6    25.0           6.9         8.4   80.7      7.7      0.5   1.5   \n",
       "4     16.9    20.0          17.3        17.6   77.9     12.4      0.3   2.8   \n",
       "5     15.9    19.7          12.2        23.8   84.5      9.0      0.0   2.6   \n",
       "6     18.2    24.1           9.4        11.5   77.3     12.1      0.1   4.0   \n",
       "7     17.6    19.4          14.0        25.2   84.4      8.5      0.1   2.8   \n",
       "8     18.9    24.1          13.7        13.5   77.7     11.2      3.8   1.2   \n",
       "9     14.3    17.0          13.1        18.3   80.7      9.6      0.2   3.3   \n",
       "\n",
       "   OtherTransp  WorkAtHome  MeanCommute  Employed  PrivateWork  PublicWork  \\\n",
       "0          1.8         6.5         25.8    9505.0         78.8        13.3   \n",
       "1          2.2         2.5         27.6   24982.0         80.0        12.1   \n",
       "2          1.8         4.5         22.0   13837.0         74.6        18.1   \n",
       "3          2.8         6.9         20.4  214984.0         78.3        15.0   \n",
       "4          0.4         6.2         22.3    3680.0         73.8        15.3   \n",
       "5          0.5         3.4         22.2    7988.0         74.1        15.8   \n",
       "6          2.6         4.0         17.1   11274.0         73.6        20.9   \n",
       "7          1.0         3.2         23.1    8130.0         71.6        20.4   \n",
       "8          1.1         5.0         29.2  246450.0         83.6        11.2   \n",
       "9          0.8         5.3         19.6    1796.0         72.0        12.1   \n",
       "\n",
       "   SelfEmployed  FamilyWork  Unemployment  \n",
       "0           7.8         0.1           9.4  \n",
       "1           7.6         0.3           8.9  \n",
       "2           7.1         0.2           5.4  \n",
       "3           6.6         0.1           4.3  \n",
       "4          10.4         0.5           3.0  \n",
       "5           9.9         0.1           6.2  \n",
       "6           5.3         0.2           5.5  \n",
       "7           7.5         0.5           5.5  \n",
       "8           5.1         0.1           5.1  \n",
       "9          15.5         0.3           4.2  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check the dataframe\n",
    "voting_pres_df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead 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>CountyId</th>\n",
       "      <th>State</th>\n",
       "      <th>County</th>\n",
       "      <th>TotalPop</th>\n",
       "      <th>Men</th>\n",
       "      <th>Women</th>\n",
       "      <th>Hispanic</th>\n",
       "      <th>White</th>\n",
       "      <th>Black</th>\n",
       "      <th>Native</th>\n",
       "      <th>Asian</th>\n",
       "      <th>Pacific</th>\n",
       "      <th>VotingAgeCitizen</th>\n",
       "      <th>Income</th>\n",
       "      <th>IncomeErr</th>\n",
       "      <th>IncomePerCap</th>\n",
       "      <th>IncomePerCapErr</th>\n",
       "      <th>Poverty</th>\n",
       "      <th>ChildPoverty</th>\n",
       "      <th>Professional</th>\n",
       "      <th>Service</th>\n",
       "      <th>Office</th>\n",
       "      <th>Construction</th>\n",
       "      <th>Production</th>\n",
       "      <th>Drive</th>\n",
       "      <th>Carpool</th>\n",
       "      <th>Transit</th>\n",
       "      <th>Walk</th>\n",
       "      <th>OtherTransp</th>\n",
       "      <th>WorkAtHome</th>\n",
       "      <th>MeanCommute</th>\n",
       "      <th>Employed</th>\n",
       "      <th>PrivateWork</th>\n",
       "      <th>PublicWork</th>\n",
       "      <th>SelfEmployed</th>\n",
       "      <th>FamilyWork</th>\n",
       "      <th>Unemployment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>55036</td>\n",
       "      <td>26899</td>\n",
       "      <td>28137</td>\n",
       "      <td>2.7</td>\n",
       "      <td>75.4</td>\n",
       "      <td>18.9</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.9</td>\n",
       "      <td>0.0</td>\n",
       "      <td>41016</td>\n",
       "      <td>55317</td>\n",
       "      <td>2838</td>\n",
       "      <td>27824</td>\n",
       "      <td>2024</td>\n",
       "      <td>13.7</td>\n",
       "      <td>20.1</td>\n",
       "      <td>35.3</td>\n",
       "      <td>18.0</td>\n",
       "      <td>23.2</td>\n",
       "      <td>8.1</td>\n",
       "      <td>15.4</td>\n",
       "      <td>86.0</td>\n",
       "      <td>9.6</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.6</td>\n",
       "      <td>1.3</td>\n",
       "      <td>2.5</td>\n",
       "      <td>25.8</td>\n",
       "      <td>24112</td>\n",
       "      <td>74.1</td>\n",
       "      <td>20.2</td>\n",
       "      <td>5.6</td>\n",
       "      <td>0.1</td>\n",
       "      <td>5.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1003</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Baldwin County</td>\n",
       "      <td>203360</td>\n",
       "      <td>99527</td>\n",
       "      <td>103833</td>\n",
       "      <td>4.4</td>\n",
       "      <td>83.1</td>\n",
       "      <td>9.5</td>\n",
       "      <td>0.8</td>\n",
       "      <td>0.7</td>\n",
       "      <td>0.0</td>\n",
       "      <td>155376</td>\n",
       "      <td>52562</td>\n",
       "      <td>1348</td>\n",
       "      <td>29364</td>\n",
       "      <td>735</td>\n",
       "      <td>11.8</td>\n",
       "      <td>16.1</td>\n",
       "      <td>35.7</td>\n",
       "      <td>18.2</td>\n",
       "      <td>25.6</td>\n",
       "      <td>9.7</td>\n",
       "      <td>10.8</td>\n",
       "      <td>84.7</td>\n",
       "      <td>7.6</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.8</td>\n",
       "      <td>1.1</td>\n",
       "      <td>5.6</td>\n",
       "      <td>27.0</td>\n",
       "      <td>89527</td>\n",
       "      <td>80.7</td>\n",
       "      <td>12.9</td>\n",
       "      <td>6.3</td>\n",
       "      <td>0.1</td>\n",
       "      <td>5.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1005</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Barbour County</td>\n",
       "      <td>26201</td>\n",
       "      <td>13976</td>\n",
       "      <td>12225</td>\n",
       "      <td>4.2</td>\n",
       "      <td>45.7</td>\n",
       "      <td>47.8</td>\n",
       "      <td>0.2</td>\n",
       "      <td>0.6</td>\n",
       "      <td>0.0</td>\n",
       "      <td>20269</td>\n",
       "      <td>33368</td>\n",
       "      <td>2551</td>\n",
       "      <td>17561</td>\n",
       "      <td>798</td>\n",
       "      <td>27.2</td>\n",
       "      <td>44.9</td>\n",
       "      <td>25.0</td>\n",
       "      <td>16.8</td>\n",
       "      <td>22.6</td>\n",
       "      <td>11.5</td>\n",
       "      <td>24.1</td>\n",
       "      <td>83.4</td>\n",
       "      <td>11.1</td>\n",
       "      <td>0.3</td>\n",
       "      <td>2.2</td>\n",
       "      <td>1.7</td>\n",
       "      <td>1.3</td>\n",
       "      <td>23.4</td>\n",
       "      <td>8878</td>\n",
       "      <td>74.1</td>\n",
       "      <td>19.1</td>\n",
       "      <td>6.5</td>\n",
       "      <td>0.3</td>\n",
       "      <td>12.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1007</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Bibb County</td>\n",
       "      <td>22580</td>\n",
       "      <td>12251</td>\n",
       "      <td>10329</td>\n",
       "      <td>2.4</td>\n",
       "      <td>74.6</td>\n",
       "      <td>22.0</td>\n",
       "      <td>0.4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>17662</td>\n",
       "      <td>43404</td>\n",
       "      <td>3431</td>\n",
       "      <td>20911</td>\n",
       "      <td>1889</td>\n",
       "      <td>15.2</td>\n",
       "      <td>26.6</td>\n",
       "      <td>24.4</td>\n",
       "      <td>17.6</td>\n",
       "      <td>19.7</td>\n",
       "      <td>15.9</td>\n",
       "      <td>22.4</td>\n",
       "      <td>86.4</td>\n",
       "      <td>9.5</td>\n",
       "      <td>0.7</td>\n",
       "      <td>0.3</td>\n",
       "      <td>1.7</td>\n",
       "      <td>1.5</td>\n",
       "      <td>30.0</td>\n",
       "      <td>8171</td>\n",
       "      <td>76.0</td>\n",
       "      <td>17.4</td>\n",
       "      <td>6.3</td>\n",
       "      <td>0.3</td>\n",
       "      <td>8.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1009</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Blount County</td>\n",
       "      <td>57667</td>\n",
       "      <td>28490</td>\n",
       "      <td>29177</td>\n",
       "      <td>9.0</td>\n",
       "      <td>87.4</td>\n",
       "      <td>1.5</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>42513</td>\n",
       "      <td>47412</td>\n",
       "      <td>2630</td>\n",
       "      <td>22021</td>\n",
       "      <td>850</td>\n",
       "      <td>15.6</td>\n",
       "      <td>25.4</td>\n",
       "      <td>28.5</td>\n",
       "      <td>12.9</td>\n",
       "      <td>23.3</td>\n",
       "      <td>15.8</td>\n",
       "      <td>19.5</td>\n",
       "      <td>86.8</td>\n",
       "      <td>10.2</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.4</td>\n",
       "      <td>0.4</td>\n",
       "      <td>2.1</td>\n",
       "      <td>35.0</td>\n",
       "      <td>21380</td>\n",
       "      <td>83.9</td>\n",
       "      <td>11.9</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.1</td>\n",
       "      <td>4.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1011</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Bullock County</td>\n",
       "      <td>10478</td>\n",
       "      <td>5616</td>\n",
       "      <td>4862</td>\n",
       "      <td>0.3</td>\n",
       "      <td>21.6</td>\n",
       "      <td>75.6</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.7</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8212</td>\n",
       "      <td>29655</td>\n",
       "      <td>5376</td>\n",
       "      <td>20856</td>\n",
       "      <td>2355</td>\n",
       "      <td>28.5</td>\n",
       "      <td>50.4</td>\n",
       "      <td>19.7</td>\n",
       "      <td>17.1</td>\n",
       "      <td>18.6</td>\n",
       "      <td>14.0</td>\n",
       "      <td>30.6</td>\n",
       "      <td>73.1</td>\n",
       "      <td>15.7</td>\n",
       "      <td>0.3</td>\n",
       "      <td>6.2</td>\n",
       "      <td>1.7</td>\n",
       "      <td>3.0</td>\n",
       "      <td>29.8</td>\n",
       "      <td>4290</td>\n",
       "      <td>81.4</td>\n",
       "      <td>13.6</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>12.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1013</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Butler County</td>\n",
       "      <td>20126</td>\n",
       "      <td>9416</td>\n",
       "      <td>10710</td>\n",
       "      <td>0.3</td>\n",
       "      <td>52.2</td>\n",
       "      <td>44.7</td>\n",
       "      <td>0.1</td>\n",
       "      <td>1.1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>15459</td>\n",
       "      <td>36326</td>\n",
       "      <td>2701</td>\n",
       "      <td>19004</td>\n",
       "      <td>943</td>\n",
       "      <td>24.4</td>\n",
       "      <td>34.8</td>\n",
       "      <td>26.9</td>\n",
       "      <td>17.3</td>\n",
       "      <td>18.5</td>\n",
       "      <td>11.6</td>\n",
       "      <td>25.7</td>\n",
       "      <td>83.6</td>\n",
       "      <td>12.6</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.9</td>\n",
       "      <td>0.9</td>\n",
       "      <td>2.0</td>\n",
       "      <td>23.2</td>\n",
       "      <td>7727</td>\n",
       "      <td>79.1</td>\n",
       "      <td>15.3</td>\n",
       "      <td>5.3</td>\n",
       "      <td>0.3</td>\n",
       "      <td>7.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1015</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Calhoun County</td>\n",
       "      <td>115527</td>\n",
       "      <td>55593</td>\n",
       "      <td>59934</td>\n",
       "      <td>3.6</td>\n",
       "      <td>72.7</td>\n",
       "      <td>20.4</td>\n",
       "      <td>0.2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>88383</td>\n",
       "      <td>43686</td>\n",
       "      <td>1491</td>\n",
       "      <td>23638</td>\n",
       "      <td>793</td>\n",
       "      <td>18.6</td>\n",
       "      <td>26.6</td>\n",
       "      <td>29.0</td>\n",
       "      <td>17.5</td>\n",
       "      <td>23.7</td>\n",
       "      <td>10.4</td>\n",
       "      <td>19.4</td>\n",
       "      <td>85.0</td>\n",
       "      <td>9.2</td>\n",
       "      <td>0.2</td>\n",
       "      <td>1.3</td>\n",
       "      <td>1.1</td>\n",
       "      <td>3.2</td>\n",
       "      <td>24.8</td>\n",
       "      <td>47392</td>\n",
       "      <td>74.9</td>\n",
       "      <td>19.9</td>\n",
       "      <td>5.1</td>\n",
       "      <td>0.1</td>\n",
       "      <td>10.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1017</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Chambers County</td>\n",
       "      <td>33895</td>\n",
       "      <td>16320</td>\n",
       "      <td>17575</td>\n",
       "      <td>2.2</td>\n",
       "      <td>56.2</td>\n",
       "      <td>39.3</td>\n",
       "      <td>0.3</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>26259</td>\n",
       "      <td>37342</td>\n",
       "      <td>2011</td>\n",
       "      <td>22002</td>\n",
       "      <td>1205</td>\n",
       "      <td>18.8</td>\n",
       "      <td>29.1</td>\n",
       "      <td>24.3</td>\n",
       "      <td>13.5</td>\n",
       "      <td>23.0</td>\n",
       "      <td>11.6</td>\n",
       "      <td>27.6</td>\n",
       "      <td>87.1</td>\n",
       "      <td>9.7</td>\n",
       "      <td>0.2</td>\n",
       "      <td>0.6</td>\n",
       "      <td>0.5</td>\n",
       "      <td>2.0</td>\n",
       "      <td>23.6</td>\n",
       "      <td>14527</td>\n",
       "      <td>84.5</td>\n",
       "      <td>11.8</td>\n",
       "      <td>3.7</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1019</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Cherokee County</td>\n",
       "      <td>25855</td>\n",
       "      <td>12862</td>\n",
       "      <td>12993</td>\n",
       "      <td>1.6</td>\n",
       "      <td>91.8</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>20620</td>\n",
       "      <td>40041</td>\n",
       "      <td>2316</td>\n",
       "      <td>23010</td>\n",
       "      <td>1354</td>\n",
       "      <td>16.1</td>\n",
       "      <td>20.0</td>\n",
       "      <td>28.8</td>\n",
       "      <td>14.8</td>\n",
       "      <td>18.1</td>\n",
       "      <td>11.9</td>\n",
       "      <td>26.5</td>\n",
       "      <td>85.0</td>\n",
       "      <td>12.1</td>\n",
       "      <td>0.4</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.3</td>\n",
       "      <td>2.0</td>\n",
       "      <td>26.5</td>\n",
       "      <td>9879</td>\n",
       "      <td>74.8</td>\n",
       "      <td>17.1</td>\n",
       "      <td>8.1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   CountyId    State           County  TotalPop    Men   Women  Hispanic  \\\n",
       "0      1001  Alabama   Autauga County     55036  26899   28137       2.7   \n",
       "1      1003  Alabama   Baldwin County    203360  99527  103833       4.4   \n",
       "2      1005  Alabama   Barbour County     26201  13976   12225       4.2   \n",
       "3      1007  Alabama      Bibb County     22580  12251   10329       2.4   \n",
       "4      1009  Alabama    Blount County     57667  28490   29177       9.0   \n",
       "5      1011  Alabama   Bullock County     10478   5616    4862       0.3   \n",
       "6      1013  Alabama    Butler County     20126   9416   10710       0.3   \n",
       "7      1015  Alabama   Calhoun County    115527  55593   59934       3.6   \n",
       "8      1017  Alabama  Chambers County     33895  16320   17575       2.2   \n",
       "9      1019  Alabama  Cherokee County     25855  12862   12993       1.6   \n",
       "\n",
       "   White  Black  Native  Asian  Pacific  VotingAgeCitizen  Income  IncomeErr  \\\n",
       "0   75.4   18.9     0.3    0.9      0.0             41016   55317       2838   \n",
       "1   83.1    9.5     0.8    0.7      0.0            155376   52562       1348   \n",
       "2   45.7   47.8     0.2    0.6      0.0             20269   33368       2551   \n",
       "3   74.6   22.0     0.4    0.0      0.0             17662   43404       3431   \n",
       "4   87.4    1.5     0.3    0.1      0.0             42513   47412       2630   \n",
       "5   21.6   75.6     1.0    0.7      0.0              8212   29655       5376   \n",
       "6   52.2   44.7     0.1    1.1      0.0             15459   36326       2701   \n",
       "7   72.7   20.4     0.2    1.0      0.0             88383   43686       1491   \n",
       "8   56.2   39.3     0.3    1.0      0.0             26259   37342       2011   \n",
       "9   91.8    5.0     0.5    0.1      0.0             20620   40041       2316   \n",
       "\n",
       "   IncomePerCap  IncomePerCapErr  Poverty  ChildPoverty  Professional  \\\n",
       "0         27824             2024     13.7          20.1          35.3   \n",
       "1         29364              735     11.8          16.1          35.7   \n",
       "2         17561              798     27.2          44.9          25.0   \n",
       "3         20911             1889     15.2          26.6          24.4   \n",
       "4         22021              850     15.6          25.4          28.5   \n",
       "5         20856             2355     28.5          50.4          19.7   \n",
       "6         19004              943     24.4          34.8          26.9   \n",
       "7         23638              793     18.6          26.6          29.0   \n",
       "8         22002             1205     18.8          29.1          24.3   \n",
       "9         23010             1354     16.1          20.0          28.8   \n",
       "\n",
       "   Service  Office  Construction  Production  Drive  Carpool  Transit  Walk  \\\n",
       "0     18.0    23.2           8.1        15.4   86.0      9.6      0.1   0.6   \n",
       "1     18.2    25.6           9.7        10.8   84.7      7.6      0.1   0.8   \n",
       "2     16.8    22.6          11.5        24.1   83.4     11.1      0.3   2.2   \n",
       "3     17.6    19.7          15.9        22.4   86.4      9.5      0.7   0.3   \n",
       "4     12.9    23.3          15.8        19.5   86.8     10.2      0.1   0.4   \n",
       "5     17.1    18.6          14.0        30.6   73.1     15.7      0.3   6.2   \n",
       "6     17.3    18.5          11.6        25.7   83.6     12.6      0.0   0.9   \n",
       "7     17.5    23.7          10.4        19.4   85.0      9.2      0.2   1.3   \n",
       "8     13.5    23.0          11.6        27.6   87.1      9.7      0.2   0.6   \n",
       "9     14.8    18.1          11.9        26.5   85.0     12.1      0.4   0.3   \n",
       "\n",
       "   OtherTransp  WorkAtHome  MeanCommute  Employed  PrivateWork  PublicWork  \\\n",
       "0          1.3         2.5         25.8     24112         74.1        20.2   \n",
       "1          1.1         5.6         27.0     89527         80.7        12.9   \n",
       "2          1.7         1.3         23.4      8878         74.1        19.1   \n",
       "3          1.7         1.5         30.0      8171         76.0        17.4   \n",
       "4          0.4         2.1         35.0     21380         83.9        11.9   \n",
       "5          1.7         3.0         29.8      4290         81.4        13.6   \n",
       "6          0.9         2.0         23.2      7727         79.1        15.3   \n",
       "7          1.1         3.2         24.8     47392         74.9        19.9   \n",
       "8          0.5         2.0         23.6     14527         84.5        11.8   \n",
       "9          0.3         2.0         26.5      9879         74.8        17.1   \n",
       "\n",
       "   SelfEmployed  FamilyWork  Unemployment  \n",
       "0           5.6         0.1           5.2  \n",
       "1           6.3         0.1           5.5  \n",
       "2           6.5         0.3          12.4  \n",
       "3           6.3         0.3           8.2  \n",
       "4           4.0         0.1           4.9  \n",
       "5           5.0         0.0          12.1  \n",
       "6           5.3         0.3           7.6  \n",
       "7           5.1         0.1          10.1  \n",
       "8           3.7         0.0           6.4  \n",
       "9           8.1         0.0           5.3  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Load census demographic dataset and check dataframe\n",
    "new_file = \"acs2017_county_data.csv\"\n",
    "census_demographic_df = pd.read_csv(new_file)\n",
    "census_demographic_df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "CountyId            3220\n",
       "State               3220\n",
       "County              3220\n",
       "TotalPop            3220\n",
       "Men                 3220\n",
       "Women               3220\n",
       "Hispanic            3220\n",
       "White               3220\n",
       "Black               3220\n",
       "Native              3220\n",
       "Asian               3220\n",
       "Pacific             3220\n",
       "VotingAgeCitizen    3220\n",
       "Income              3220\n",
       "IncomeErr           3220\n",
       "IncomePerCap        3220\n",
       "IncomePerCapErr     3220\n",
       "Poverty             3220\n",
       "ChildPoverty        3219\n",
       "Professional        3220\n",
       "Service             3220\n",
       "Office              3220\n",
       "Construction        3220\n",
       "Production          3220\n",
       "Drive               3220\n",
       "Carpool             3220\n",
       "Transit             3220\n",
       "Walk                3220\n",
       "OtherTransp         3220\n",
       "WorkAtHome          3220\n",
       "MeanCommute         3220\n",
       "Employed            3220\n",
       "PrivateWork         3220\n",
       "PublicWork          3220\n",
       "SelfEmployed        3220\n",
       "FamilyWork          3220\n",
       "Unemployment        3220\n",
       "dtype: int64"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "census_demographic_df.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Texas                   254\n",
       "Georgia                 159\n",
       "Virginia                133\n",
       "Kentucky                120\n",
       "Missouri                115\n",
       "Kansas                  105\n",
       "Illinois                102\n",
       "North Carolina          100\n",
       "Iowa                     99\n",
       "Tennessee                95\n",
       "Nebraska                 93\n",
       "Indiana                  92\n",
       "Ohio                     88\n",
       "Minnesota                87\n",
       "Michigan                 83\n",
       "Mississippi              82\n",
       "Puerto Rico              78\n",
       "Oklahoma                 77\n",
       "Arkansas                 75\n",
       "Wisconsin                72\n",
       "Pennsylvania             67\n",
       "Alabama                  67\n",
       "Florida                  67\n",
       "South Dakota             66\n",
       "Louisiana                64\n",
       "Colorado                 64\n",
       "New York                 62\n",
       "California               58\n",
       "Montana                  56\n",
       "West Virginia            55\n",
       "North Dakota             53\n",
       "South Carolina           46\n",
       "Idaho                    44\n",
       "Washington               39\n",
       "Oregon                   36\n",
       "New Mexico               33\n",
       "Alaska                   29\n",
       "Utah                     29\n",
       "Maryland                 24\n",
       "Wyoming                  23\n",
       "New Jersey               21\n",
       "Nevada                   17\n",
       "Maine                    16\n",
       "Arizona                  15\n",
       "Vermont                  14\n",
       "Massachusetts            14\n",
       "New Hampshire            10\n",
       "Connecticut               8\n",
       "Rhode Island              5\n",
       "Hawaii                    5\n",
       "Delaware                  3\n",
       "District of Columbia      1\n",
       "Name: State, dtype: int64"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "census_demographic_df[\"State\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['CountyId', 'State', 'County', 'TotalPop', 'Men', 'Women', 'Hispanic',\n",
       "       'White', 'Black', 'Native', 'Asian', 'Pacific', 'VotingAgeCitizen',\n",
       "       'Income', 'IncomeErr', 'IncomePerCap', 'IncomePerCapErr', 'Poverty',\n",
       "       'ChildPoverty', 'Professional', 'Service', 'Office', 'Construction',\n",
       "       'Production', 'Drive', 'Carpool', 'Transit', 'Walk', 'OtherTransp',\n",
       "       'WorkAtHome', 'MeanCommute', 'Employed', 'PrivateWork', 'PublicWork',\n",
       "       'SelfEmployed', 'FamilyWork', 'Unemployment'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Show all columns in df\n",
    "census_demographic_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "CountyId              int64\n",
       "State                object\n",
       "County               object\n",
       "TotalPop              int64\n",
       "Men                   int64\n",
       "Women                 int64\n",
       "Hispanic            float64\n",
       "White               float64\n",
       "Black               float64\n",
       "Native              float64\n",
       "Asian               float64\n",
       "Pacific             float64\n",
       "VotingAgeCitizen      int64\n",
       "Income                int64\n",
       "IncomeErr             int64\n",
       "IncomePerCap          int64\n",
       "IncomePerCapErr       int64\n",
       "Poverty             float64\n",
       "ChildPoverty        float64\n",
       "Professional        float64\n",
       "Service             float64\n",
       "Office              float64\n",
       "Construction        float64\n",
       "Production          float64\n",
       "Drive               float64\n",
       "Carpool             float64\n",
       "Transit             float64\n",
       "Walk                float64\n",
       "OtherTransp         float64\n",
       "WorkAtHome          float64\n",
       "MeanCommute         float64\n",
       "Employed              int64\n",
       "PrivateWork         float64\n",
       "PublicWork          float64\n",
       "SelfEmployed        float64\n",
       "FamilyWork          float64\n",
       "Unemployment        float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check columns data type\n",
    "census_demographic_df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "CountyId            0\n",
       "State               0\n",
       "County              0\n",
       "TotalPop            0\n",
       "Men                 0\n",
       "Women               0\n",
       "Hispanic            0\n",
       "White               0\n",
       "Black               0\n",
       "Native              0\n",
       "Asian               0\n",
       "Pacific             0\n",
       "VotingAgeCitizen    0\n",
       "Income              0\n",
       "IncomeErr           0\n",
       "IncomePerCap        0\n",
       "IncomePerCapErr     0\n",
       "Poverty             0\n",
       "ChildPoverty        1\n",
       "Professional        0\n",
       "Service             0\n",
       "Office              0\n",
       "Construction        0\n",
       "Production          0\n",
       "Drive               0\n",
       "Carpool             0\n",
       "Transit             0\n",
       "Walk                0\n",
       "OtherTransp         0\n",
       "WorkAtHome          0\n",
       "MeanCommute         0\n",
       "Employed            0\n",
       "PrivateWork         0\n",
       "PublicWork          0\n",
       "SelfEmployed        0\n",
       "FamilyWork          0\n",
       "Unemployment        0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check for null values\n",
    "census_demographic_df.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load Vaccine Hesitancy dataset and check dataframe\n",
    "new_file_path = \"County_Codes_FIPS.csv\"\n",
    "county_state_df = pd.read_csv(new_file_path)\n",
    "\n",
    "# Rename and reorder columns to match the database\n",
    "county_state_df.rename({'FIPS':'fips_code', 'Name': 'county', 'State': 'state'}, axis='columns', inplace=True)\n",
    "cols = ['county', 'state','fips_code']\n",
    "county_state_df = county_state_df[cols]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "county_state_df[\"county\"] = county_state_df[\"county\"] + ' ' + 'County'\n",
    "county_state_df[\"county\"] = county_state_df[\"county\"].str.lower()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "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>state</th>\n",
       "      <th>fips_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>anderson county</td>\n",
       "      <td>TX</td>\n",
       "      <td>48001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>andrews county</td>\n",
       "      <td>TX</td>\n",
       "      <td>48003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>angelina county</td>\n",
       "      <td>TX</td>\n",
       "      <td>48005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>aransas county</td>\n",
       "      <td>TX</td>\n",
       "      <td>48007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>archer county</td>\n",
       "      <td>TX</td>\n",
       "      <td>48009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>armstrong county</td>\n",
       "      <td>TX</td>\n",
       "      <td>48011</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>atascosa county</td>\n",
       "      <td>TX</td>\n",
       "      <td>48013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>austin county</td>\n",
       "      <td>TX</td>\n",
       "      <td>48015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>bailey county</td>\n",
       "      <td>TX</td>\n",
       "      <td>48017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>bandera county</td>\n",
       "      <td>TX</td>\n",
       "      <td>48019</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             county state  fips_code\n",
       "0   anderson county    TX      48001\n",
       "1    andrews county    TX      48003\n",
       "2   angelina county    TX      48005\n",
       "3    aransas county    TX      48007\n",
       "4     archer county    TX      48009\n",
       "5  armstrong county    TX      48011\n",
       "6   atascosa county    TX      48013\n",
       "7     austin county    TX      48015\n",
       "8     bailey county    TX      48017\n",
       "9    bandera county    TX      48019"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check the new dataframe\n",
    "county_state_df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "county       0\n",
       "state        0\n",
       "fips_code    0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check for null values\n",
    "county_state_df.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "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>state</th>\n",
       "      <th>fips_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>autauga county</td>\n",
       "      <td>AL</td>\n",
       "      <td>1001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>baldwin county</td>\n",
       "      <td>AL</td>\n",
       "      <td>1003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>barbour county</td>\n",
       "      <td>AL</td>\n",
       "      <td>1005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>bibb county</td>\n",
       "      <td>AL</td>\n",
       "      <td>1007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>blount county</td>\n",
       "      <td>AL</td>\n",
       "      <td>1009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3215</th>\n",
       "      <td>vega baja municipio</td>\n",
       "      <td>PR</td>\n",
       "      <td>72145</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3216</th>\n",
       "      <td>vieques municipio</td>\n",
       "      <td>PR</td>\n",
       "      <td>72147</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3217</th>\n",
       "      <td>villalba municipio</td>\n",
       "      <td>PR</td>\n",
       "      <td>72149</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3218</th>\n",
       "      <td>yabucoa municipio</td>\n",
       "      <td>PR</td>\n",
       "      <td>72151</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3219</th>\n",
       "      <td>yauco municipio</td>\n",
       "      <td>PR</td>\n",
       "      <td>72153</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3220 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                   county state  fips_code\n",
       "0          autauga county    AL       1001\n",
       "1          baldwin county    AL       1003\n",
       "2          barbour county    AL       1005\n",
       "3             bibb county    AL       1007\n",
       "4           blount county    AL       1009\n",
       "...                   ...   ...        ...\n",
       "3215  vega baja municipio    PR      72145\n",
       "3216    vieques municipio    PR      72147\n",
       "3217   villalba municipio    PR      72149\n",
       "3218    yabucoa municipio    PR      72151\n",
       "3219      yauco municipio    PR      72153\n",
       "\n",
       "[3220 rows x 3 columns]"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "census_county_states_df = census_demographic_df.filter([\"County\", \"State\", \"CountyId\"])\n",
    "census_county_states_df.rename({'County':'county', 'State': 'state', 'CountyId': 'fips_code'}, axis='columns', inplace=True)\n",
    "census_county_states_df[\"state\"] = census_county_states_df[\"state\"].map(us_state_abbrev).fillna(census_county_states_df[\"state\"])\n",
    "census_county_states_df[\"county\"] = census_county_states_df[\"county\"].str.lower()\n",
    "census_county_states_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "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>state</th>\n",
       "      <th>fips_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>anderson county</td>\n",
       "      <td>TX</td>\n",
       "      <td>48001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>andrews county</td>\n",
       "      <td>TX</td>\n",
       "      <td>48003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>angelina county</td>\n",
       "      <td>TX</td>\n",
       "      <td>48005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>aransas county</td>\n",
       "      <td>TX</td>\n",
       "      <td>48007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>archer county</td>\n",
       "      <td>TX</td>\n",
       "      <td>48009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3215</th>\n",
       "      <td>vega baja municipio</td>\n",
       "      <td>PR</td>\n",
       "      <td>72145</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3216</th>\n",
       "      <td>vieques municipio</td>\n",
       "      <td>PR</td>\n",
       "      <td>72147</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3217</th>\n",
       "      <td>villalba municipio</td>\n",
       "      <td>PR</td>\n",
       "      <td>72149</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3218</th>\n",
       "      <td>yabucoa municipio</td>\n",
       "      <td>PR</td>\n",
       "      <td>72151</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3219</th>\n",
       "      <td>yauco municipio</td>\n",
       "      <td>PR</td>\n",
       "      <td>72153</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3471 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                   county state  fips_code\n",
       "0         anderson county    TX      48001\n",
       "1          andrews county    TX      48003\n",
       "2         angelina county    TX      48005\n",
       "3          aransas county    TX      48007\n",
       "4           archer county    TX      48009\n",
       "...                   ...   ...        ...\n",
       "3215  vega baja municipio    PR      72145\n",
       "3216    vieques municipio    PR      72147\n",
       "3217   villalba municipio    PR      72149\n",
       "3218    yabucoa municipio    PR      72151\n",
       "3219      yauco municipio    PR      72153\n",
       "\n",
       "[3471 rows x 3 columns]"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_county_codes_df = county_state_df.append(census_county_states_df)\n",
    "merged_county_codes_df.drop_duplicates(keep='first', inplace=True)\n",
    "merged_county_codes_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "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>state</th>\n",
       "      <th>fips_code</th>\n",
       "      <th>percentage16_Donald_Trump</th>\n",
       "      <th>percentage16_Hillary_Clinton</th>\n",
       "      <th>total_votes16</th>\n",
       "      <th>votes16_Donald_Trump</th>\n",
       "      <th>votes16_Hillary_Clinton</th>\n",
       "      <th>percentage20_Donald_Trump</th>\n",
       "      <th>percentage20_Joe_Biden</th>\n",
       "      <th>total_votes20</th>\n",
       "      <th>votes20_Donald_Trump</th>\n",
       "      <th>votes20_Joe_Biden</th>\n",
       "      <th>lat</th>\n",
       "      <th>long</th>\n",
       "      <th>cases</th>\n",
       "      <th>deaths</th>\n",
       "      <th>TotalPop</th>\n",
       "      <th>Men</th>\n",
       "      <th>Women</th>\n",
       "      <th>Hispanic</th>\n",
       "      <th>White</th>\n",
       "      <th>Black</th>\n",
       "      <th>Native</th>\n",
       "      <th>Asian</th>\n",
       "      <th>Pacific</th>\n",
       "      <th>VotingAgeCitizen</th>\n",
       "      <th>Income</th>\n",
       "      <th>IncomeErr</th>\n",
       "      <th>IncomePerCap</th>\n",
       "      <th>IncomePerCapErr</th>\n",
       "      <th>Poverty</th>\n",
       "      <th>ChildPoverty</th>\n",
       "      <th>Professional</th>\n",
       "      <th>Service</th>\n",
       "      <th>Office</th>\n",
       "      <th>Construction</th>\n",
       "      <th>Production</th>\n",
       "      <th>Drive</th>\n",
       "      <th>Carpool</th>\n",
       "      <th>Transit</th>\n",
       "      <th>Walk</th>\n",
       "      <th>OtherTransp</th>\n",
       "      <th>WorkAtHome</th>\n",
       "      <th>MeanCommute</th>\n",
       "      <th>Employed</th>\n",
       "      <th>PrivateWork</th>\n",
       "      <th>PublicWork</th>\n",
       "      <th>SelfEmployed</th>\n",
       "      <th>FamilyWork</th>\n",
       "      <th>Unemployment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>anderson county</td>\n",
       "      <td>TX</td>\n",
       "      <td>48001</td>\n",
       "      <td>0.780</td>\n",
       "      <td>0.199</td>\n",
       "      <td>16887.0</td>\n",
       "      <td>13165.0</td>\n",
       "      <td>3358.0</td>\n",
       "      <td>0.786</td>\n",
       "      <td>0.205</td>\n",
       "      <td>19155.0</td>\n",
       "      <td>15062.0</td>\n",
       "      <td>3934.0</td>\n",
       "      <td>31.815347</td>\n",
       "      <td>-95.653548</td>\n",
       "      <td>2979.0</td>\n",
       "      <td>39.0</td>\n",
       "      <td>57747.0</td>\n",
       "      <td>35292.0</td>\n",
       "      <td>22455.0</td>\n",
       "      <td>17.3</td>\n",
       "      <td>59.7</td>\n",
       "      <td>20.9</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>44095.0</td>\n",
       "      <td>42313.0</td>\n",
       "      <td>2337.0</td>\n",
       "      <td>17466.0</td>\n",
       "      <td>894.0</td>\n",
       "      <td>15.7</td>\n",
       "      <td>23.0</td>\n",
       "      <td>24.4</td>\n",
       "      <td>23.5</td>\n",
       "      <td>23.4</td>\n",
       "      <td>12.9</td>\n",
       "      <td>15.9</td>\n",
       "      <td>85.1</td>\n",
       "      <td>9.9</td>\n",
       "      <td>0.2</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.7</td>\n",
       "      <td>3.6</td>\n",
       "      <td>23.7</td>\n",
       "      <td>19102.0</td>\n",
       "      <td>73.2</td>\n",
       "      <td>20.4</td>\n",
       "      <td>6.2</td>\n",
       "      <td>0.2</td>\n",
       "      <td>4.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>andrews county</td>\n",
       "      <td>TX</td>\n",
       "      <td>48003</td>\n",
       "      <td>0.797</td>\n",
       "      <td>0.170</td>\n",
       "      <td>4926.0</td>\n",
       "      <td>3925.0</td>\n",
       "      <td>836.0</td>\n",
       "      <td>0.843</td>\n",
       "      <td>0.145</td>\n",
       "      <td>5856.0</td>\n",
       "      <td>4937.0</td>\n",
       "      <td>849.0</td>\n",
       "      <td>32.304686</td>\n",
       "      <td>-102.637655</td>\n",
       "      <td>667.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>17577.0</td>\n",
       "      <td>8980.0</td>\n",
       "      <td>8597.0</td>\n",
       "      <td>55.4</td>\n",
       "      <td>41.1</td>\n",
       "      <td>1.5</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.2</td>\n",
       "      <td>0.1</td>\n",
       "      <td>10695.0</td>\n",
       "      <td>70753.0</td>\n",
       "      <td>6115.0</td>\n",
       "      <td>29903.0</td>\n",
       "      <td>3147.0</td>\n",
       "      <td>11.9</td>\n",
       "      <td>15.5</td>\n",
       "      <td>21.3</td>\n",
       "      <td>13.0</td>\n",
       "      <td>24.2</td>\n",
       "      <td>22.7</td>\n",
       "      <td>18.8</td>\n",
       "      <td>79.8</td>\n",
       "      <td>16.8</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.8</td>\n",
       "      <td>2.5</td>\n",
       "      <td>19.8</td>\n",
       "      <td>8054.0</td>\n",
       "      <td>85.3</td>\n",
       "      <td>8.7</td>\n",
       "      <td>5.7</td>\n",
       "      <td>0.3</td>\n",
       "      <td>4.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>angelina county</td>\n",
       "      <td>TX</td>\n",
       "      <td>48005</td>\n",
       "      <td>0.725</td>\n",
       "      <td>0.252</td>\n",
       "      <td>29870.0</td>\n",
       "      <td>21666.0</td>\n",
       "      <td>7538.0</td>\n",
       "      <td>0.724</td>\n",
       "      <td>0.264</td>\n",
       "      <td>34628.0</td>\n",
       "      <td>25070.0</td>\n",
       "      <td>9136.0</td>\n",
       "      <td>31.254573</td>\n",
       "      <td>-94.609015</td>\n",
       "      <td>2317.0</td>\n",
       "      <td>93.0</td>\n",
       "      <td>87700.0</td>\n",
       "      <td>42707.0</td>\n",
       "      <td>44993.0</td>\n",
       "      <td>21.5</td>\n",
       "      <td>61.2</td>\n",
       "      <td>14.7</td>\n",
       "      <td>0.1</td>\n",
       "      <td>1.1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>60533.0</td>\n",
       "      <td>46472.0</td>\n",
       "      <td>1452.0</td>\n",
       "      <td>21974.0</td>\n",
       "      <td>758.0</td>\n",
       "      <td>19.1</td>\n",
       "      <td>27.1</td>\n",
       "      <td>28.2</td>\n",
       "      <td>20.9</td>\n",
       "      <td>24.0</td>\n",
       "      <td>12.3</td>\n",
       "      <td>14.6</td>\n",
       "      <td>82.9</td>\n",
       "      <td>11.9</td>\n",
       "      <td>0.4</td>\n",
       "      <td>1.7</td>\n",
       "      <td>0.9</td>\n",
       "      <td>2.2</td>\n",
       "      <td>18.7</td>\n",
       "      <td>36164.0</td>\n",
       "      <td>77.2</td>\n",
       "      <td>16.7</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.1</td>\n",
       "      <td>7.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>aransas county</td>\n",
       "      <td>TX</td>\n",
       "      <td>48007</td>\n",
       "      <td>0.739</td>\n",
       "      <td>0.235</td>\n",
       "      <td>10467.0</td>\n",
       "      <td>7730.0</td>\n",
       "      <td>2458.0</td>\n",
       "      <td>0.752</td>\n",
       "      <td>0.237</td>\n",
       "      <td>12241.0</td>\n",
       "      <td>9210.0</td>\n",
       "      <td>2896.0</td>\n",
       "      <td>28.105562</td>\n",
       "      <td>-96.999505</td>\n",
       "      <td>351.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>24832.0</td>\n",
       "      <td>12448.0</td>\n",
       "      <td>12384.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>67.7</td>\n",
       "      <td>1.6</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>19441.0</td>\n",
       "      <td>44601.0</td>\n",
       "      <td>3698.0</td>\n",
       "      <td>29999.0</td>\n",
       "      <td>2914.0</td>\n",
       "      <td>17.8</td>\n",
       "      <td>26.8</td>\n",
       "      <td>28.2</td>\n",
       "      <td>23.3</td>\n",
       "      <td>22.1</td>\n",
       "      <td>16.5</td>\n",
       "      <td>9.8</td>\n",
       "      <td>81.4</td>\n",
       "      <td>8.4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.8</td>\n",
       "      <td>0.5</td>\n",
       "      <td>6.8</td>\n",
       "      <td>20.8</td>\n",
       "      <td>10387.0</td>\n",
       "      <td>72.9</td>\n",
       "      <td>12.3</td>\n",
       "      <td>14.6</td>\n",
       "      <td>0.2</td>\n",
       "      <td>6.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>archer county</td>\n",
       "      <td>TX</td>\n",
       "      <td>48009</td>\n",
       "      <td>0.887</td>\n",
       "      <td>0.092</td>\n",
       "      <td>4269.0</td>\n",
       "      <td>3785.0</td>\n",
       "      <td>394.0</td>\n",
       "      <td>0.897</td>\n",
       "      <td>0.093</td>\n",
       "      <td>4796.0</td>\n",
       "      <td>4300.0</td>\n",
       "      <td>446.0</td>\n",
       "      <td>33.615700</td>\n",
       "      <td>-98.687546</td>\n",
       "      <td>165.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>8793.0</td>\n",
       "      <td>4335.0</td>\n",
       "      <td>4458.0</td>\n",
       "      <td>8.3</td>\n",
       "      <td>88.6</td>\n",
       "      <td>0.8</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6712.0</td>\n",
       "      <td>63192.0</td>\n",
       "      <td>4765.0</td>\n",
       "      <td>31103.0</td>\n",
       "      <td>2291.0</td>\n",
       "      <td>8.8</td>\n",
       "      <td>10.1</td>\n",
       "      <td>34.2</td>\n",
       "      <td>13.4</td>\n",
       "      <td>25.3</td>\n",
       "      <td>12.9</td>\n",
       "      <td>14.2</td>\n",
       "      <td>86.0</td>\n",
       "      <td>6.9</td>\n",
       "      <td>0.3</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.1</td>\n",
       "      <td>4.7</td>\n",
       "      <td>20.4</td>\n",
       "      <td>4344.0</td>\n",
       "      <td>75.6</td>\n",
       "      <td>14.5</td>\n",
       "      <td>9.8</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <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>3350</th>\n",
       "      <td>st. lawrence county</td>\n",
       "      <td>NY</td>\n",
       "      <td>36089</td>\n",
       "      <td>0.525</td>\n",
       "      <td>0.417</td>\n",
       "      <td>35154.0</td>\n",
       "      <td>18450.0</td>\n",
       "      <td>14659.0</td>\n",
       "      <td>0.616</td>\n",
       "      <td>0.367</td>\n",
       "      <td>35519.0</td>\n",
       "      <td>21862.0</td>\n",
       "      <td>13036.0</td>\n",
       "      <td>44.497618</td>\n",
       "      <td>-75.065500</td>\n",
       "      <td>423.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>110817.0</td>\n",
       "      <td>56381.0</td>\n",
       "      <td>54436.0</td>\n",
       "      <td>2.3</td>\n",
       "      <td>92.1</td>\n",
       "      <td>2.2</td>\n",
       "      <td>0.7</td>\n",
       "      <td>1.1</td>\n",
       "      <td>0.1</td>\n",
       "      <td>85770.0</td>\n",
       "      <td>48330.0</td>\n",
       "      <td>1867.0</td>\n",
       "      <td>23554.0</td>\n",
       "      <td>606.0</td>\n",
       "      <td>19.4</td>\n",
       "      <td>29.0</td>\n",
       "      <td>34.2</td>\n",
       "      <td>22.6</td>\n",
       "      <td>21.4</td>\n",
       "      <td>11.4</td>\n",
       "      <td>10.4</td>\n",
       "      <td>77.1</td>\n",
       "      <td>9.8</td>\n",
       "      <td>0.5</td>\n",
       "      <td>6.1</td>\n",
       "      <td>1.3</td>\n",
       "      <td>5.1</td>\n",
       "      <td>20.9</td>\n",
       "      <td>44013.0</td>\n",
       "      <td>68.3</td>\n",
       "      <td>24.0</td>\n",
       "      <td>7.5</td>\n",
       "      <td>0.3</td>\n",
       "      <td>8.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3351</th>\n",
       "      <td>lamoure county</td>\n",
       "      <td>ND</td>\n",
       "      <td>38045</td>\n",
       "      <td>0.697</td>\n",
       "      <td>0.236</td>\n",
       "      <td>2115.0</td>\n",
       "      <td>1475.0</td>\n",
       "      <td>500.0</td>\n",
       "      <td>0.741</td>\n",
       "      <td>0.237</td>\n",
       "      <td>2219.0</td>\n",
       "      <td>1645.0</td>\n",
       "      <td>527.0</td>\n",
       "      <td>46.456811</td>\n",
       "      <td>-98.535424</td>\n",
       "      <td>224.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>4106.0</td>\n",
       "      <td>2129.0</td>\n",
       "      <td>1977.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>97.1</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.7</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3175.0</td>\n",
       "      <td>57463.0</td>\n",
       "      <td>3896.0</td>\n",
       "      <td>36653.0</td>\n",
       "      <td>3293.0</td>\n",
       "      <td>10.7</td>\n",
       "      <td>15.3</td>\n",
       "      <td>40.0</td>\n",
       "      <td>12.7</td>\n",
       "      <td>18.1</td>\n",
       "      <td>14.5</td>\n",
       "      <td>14.7</td>\n",
       "      <td>69.9</td>\n",
       "      <td>6.6</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9.8</td>\n",
       "      <td>0.7</td>\n",
       "      <td>13.0</td>\n",
       "      <td>17.5</td>\n",
       "      <td>2019.0</td>\n",
       "      <td>64.5</td>\n",
       "      <td>13.4</td>\n",
       "      <td>20.5</td>\n",
       "      <td>1.6</td>\n",
       "      <td>0.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3352</th>\n",
       "      <td>oglala lakota county</td>\n",
       "      <td>SD</td>\n",
       "      <td>46102</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.093</td>\n",
       "      <td>0.884</td>\n",
       "      <td>3200.0</td>\n",
       "      <td>297.0</td>\n",
       "      <td>2829.0</td>\n",
       "      <td>43.337492</td>\n",
       "      <td>-102.555550</td>\n",
       "      <td>1051.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>14291.0</td>\n",
       "      <td>6934.0</td>\n",
       "      <td>7357.0</td>\n",
       "      <td>3.7</td>\n",
       "      <td>4.1</td>\n",
       "      <td>0.1</td>\n",
       "      <td>90.3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8940.0</td>\n",
       "      <td>27804.0</td>\n",
       "      <td>2880.0</td>\n",
       "      <td>9334.0</td>\n",
       "      <td>703.0</td>\n",
       "      <td>51.9</td>\n",
       "      <td>59.8</td>\n",
       "      <td>33.4</td>\n",
       "      <td>32.2</td>\n",
       "      <td>16.5</td>\n",
       "      <td>7.0</td>\n",
       "      <td>10.9</td>\n",
       "      <td>64.8</td>\n",
       "      <td>12.2</td>\n",
       "      <td>2.0</td>\n",
       "      <td>11.9</td>\n",
       "      <td>4.5</td>\n",
       "      <td>4.6</td>\n",
       "      <td>17.0</td>\n",
       "      <td>3219.0</td>\n",
       "      <td>31.1</td>\n",
       "      <td>64.8</td>\n",
       "      <td>4.1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>22.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3353</th>\n",
       "      <td>dewitt county</td>\n",
       "      <td>TX</td>\n",
       "      <td>48123</td>\n",
       "      <td>0.808</td>\n",
       "      <td>0.170</td>\n",
       "      <td>6822.0</td>\n",
       "      <td>5510.0</td>\n",
       "      <td>1161.0</td>\n",
       "      <td>0.809</td>\n",
       "      <td>0.184</td>\n",
       "      <td>8118.0</td>\n",
       "      <td>6567.0</td>\n",
       "      <td>1494.0</td>\n",
       "      <td>29.081019</td>\n",
       "      <td>-97.356812</td>\n",
       "      <td>1087.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>20474.0</td>\n",
       "      <td>10697.0</td>\n",
       "      <td>9777.0</td>\n",
       "      <td>34.5</td>\n",
       "      <td>55.6</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>15285.0</td>\n",
       "      <td>50960.0</td>\n",
       "      <td>5037.0</td>\n",
       "      <td>28116.0</td>\n",
       "      <td>2303.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>20.2</td>\n",
       "      <td>30.5</td>\n",
       "      <td>18.3</td>\n",
       "      <td>19.8</td>\n",
       "      <td>15.5</td>\n",
       "      <td>15.9</td>\n",
       "      <td>85.0</td>\n",
       "      <td>7.6</td>\n",
       "      <td>0.1</td>\n",
       "      <td>2.3</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.9</td>\n",
       "      <td>23.8</td>\n",
       "      <td>7965.0</td>\n",
       "      <td>76.9</td>\n",
       "      <td>16.1</td>\n",
       "      <td>6.7</td>\n",
       "      <td>0.2</td>\n",
       "      <td>6.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3392</th>\n",
       "      <td>st. croix county</td>\n",
       "      <td>WI</td>\n",
       "      <td>55109</td>\n",
       "      <td>0.558</td>\n",
       "      <td>0.374</td>\n",
       "      <td>46819.0</td>\n",
       "      <td>26123.0</td>\n",
       "      <td>17496.0</td>\n",
       "      <td>0.568</td>\n",
       "      <td>0.409</td>\n",
       "      <td>56707.0</td>\n",
       "      <td>32199.0</td>\n",
       "      <td>23190.0</td>\n",
       "      <td>45.034375</td>\n",
       "      <td>-92.450001</td>\n",
       "      <td>2103.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>87142.0</td>\n",
       "      <td>43468.0</td>\n",
       "      <td>43674.0</td>\n",
       "      <td>2.3</td>\n",
       "      <td>94.2</td>\n",
       "      <td>0.9</td>\n",
       "      <td>0.4</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>63960.0</td>\n",
       "      <td>77768.0</td>\n",
       "      <td>2715.0</td>\n",
       "      <td>36561.0</td>\n",
       "      <td>995.0</td>\n",
       "      <td>4.8</td>\n",
       "      <td>5.2</td>\n",
       "      <td>38.9</td>\n",
       "      <td>14.9</td>\n",
       "      <td>22.7</td>\n",
       "      <td>9.3</td>\n",
       "      <td>14.2</td>\n",
       "      <td>83.4</td>\n",
       "      <td>7.6</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1.5</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6.1</td>\n",
       "      <td>27.9</td>\n",
       "      <td>47132.0</td>\n",
       "      <td>82.0</td>\n",
       "      <td>11.8</td>\n",
       "      <td>6.1</td>\n",
       "      <td>0.1</td>\n",
       "      <td>3.3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3076 rows × 51 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                    county state  fips_code  percentage16_Donald_Trump  \\\n",
       "0          anderson county    TX      48001                      0.780   \n",
       "1           andrews county    TX      48003                      0.797   \n",
       "2          angelina county    TX      48005                      0.725   \n",
       "3           aransas county    TX      48007                      0.739   \n",
       "4            archer county    TX      48009                      0.887   \n",
       "...                    ...   ...        ...                        ...   \n",
       "3350   st. lawrence county    NY      36089                      0.525   \n",
       "3351        lamoure county    ND      38045                      0.697   \n",
       "3352  oglala lakota county    SD      46102                        NaN   \n",
       "3353         dewitt county    TX      48123                      0.808   \n",
       "3392      st. croix county    WI      55109                      0.558   \n",
       "\n",
       "      percentage16_Hillary_Clinton  total_votes16  votes16_Donald_Trump  \\\n",
       "0                            0.199        16887.0               13165.0   \n",
       "1                            0.170         4926.0                3925.0   \n",
       "2                            0.252        29870.0               21666.0   \n",
       "3                            0.235        10467.0                7730.0   \n",
       "4                            0.092         4269.0                3785.0   \n",
       "...                            ...            ...                   ...   \n",
       "3350                         0.417        35154.0               18450.0   \n",
       "3351                         0.236         2115.0                1475.0   \n",
       "3352                           NaN            NaN                   NaN   \n",
       "3353                         0.170         6822.0                5510.0   \n",
       "3392                         0.374        46819.0               26123.0   \n",
       "\n",
       "      votes16_Hillary_Clinton  percentage20_Donald_Trump  \\\n",
       "0                      3358.0                      0.786   \n",
       "1                       836.0                      0.843   \n",
       "2                      7538.0                      0.724   \n",
       "3                      2458.0                      0.752   \n",
       "4                       394.0                      0.897   \n",
       "...                       ...                        ...   \n",
       "3350                  14659.0                      0.616   \n",
       "3351                    500.0                      0.741   \n",
       "3352                      NaN                      0.093   \n",
       "3353                   1161.0                      0.809   \n",
       "3392                  17496.0                      0.568   \n",
       "\n",
       "      percentage20_Joe_Biden  total_votes20  votes20_Donald_Trump  \\\n",
       "0                      0.205        19155.0               15062.0   \n",
       "1                      0.145         5856.0                4937.0   \n",
       "2                      0.264        34628.0               25070.0   \n",
       "3                      0.237        12241.0                9210.0   \n",
       "4                      0.093         4796.0                4300.0   \n",
       "...                      ...            ...                   ...   \n",
       "3350                   0.367        35519.0               21862.0   \n",
       "3351                   0.237         2219.0                1645.0   \n",
       "3352                   0.884         3200.0                 297.0   \n",
       "3353                   0.184         8118.0                6567.0   \n",
       "3392                   0.409        56707.0               32199.0   \n",
       "\n",
       "      votes20_Joe_Biden        lat        long   cases  deaths  TotalPop  \\\n",
       "0                3934.0  31.815347  -95.653548  2979.0    39.0   57747.0   \n",
       "1                 849.0  32.304686 -102.637655   667.0    10.0   17577.0   \n",
       "2                9136.0  31.254573  -94.609015  2317.0    93.0   87700.0   \n",
       "3                2896.0  28.105562  -96.999505   351.0    18.0   24832.0   \n",
       "4                 446.0  33.615700  -98.687546   165.0     1.0    8793.0   \n",
       "...                 ...        ...         ...     ...     ...       ...   \n",
       "3350            13036.0  44.497618  -75.065500   423.0     4.0  110817.0   \n",
       "3351              527.0  46.456811  -98.535424   224.0    11.0    4106.0   \n",
       "3352             2829.0  43.337492 -102.555550  1051.0     9.0   14291.0   \n",
       "3353             1494.0  29.081019  -97.356812  1087.0    44.0   20474.0   \n",
       "3392            23190.0  45.034375  -92.450001  2103.0    11.0   87142.0   \n",
       "\n",
       "          Men    Women  Hispanic  White  Black  Native  Asian  Pacific  \\\n",
       "0     35292.0  22455.0      17.3   59.7   20.9     0.3    0.5      0.0   \n",
       "1      8980.0   8597.0      55.4   41.1    1.5     0.1    0.2      0.1   \n",
       "2     42707.0  44993.0      21.5   61.2   14.7     0.1    1.1      0.0   \n",
       "3     12448.0  12384.0      27.0   67.7    1.6     0.0    2.0      0.0   \n",
       "4      4335.0   4458.0       8.3   88.6    0.8     0.3    0.4      0.0   \n",
       "...       ...      ...       ...    ...    ...     ...    ...      ...   \n",
       "3350  56381.0  54436.0       2.3   92.1    2.2     0.7    1.1      0.1   \n",
       "3351   2129.0   1977.0       1.0   97.1    0.1     0.7    0.0      0.0   \n",
       "3352   6934.0   7357.0       3.7    4.1    0.1    90.3    0.0      0.0   \n",
       "3353  10697.0   9777.0      34.5   55.6    9.0     0.0    0.0      0.0   \n",
       "3392  43468.0  43674.0       2.3   94.2    0.9     0.4    1.0      0.0   \n",
       "\n",
       "      VotingAgeCitizen   Income  IncomeErr  IncomePerCap  IncomePerCapErr  \\\n",
       "0              44095.0  42313.0     2337.0       17466.0            894.0   \n",
       "1              10695.0  70753.0     6115.0       29903.0           3147.0   \n",
       "2              60533.0  46472.0     1452.0       21974.0            758.0   \n",
       "3              19441.0  44601.0     3698.0       29999.0           2914.0   \n",
       "4               6712.0  63192.0     4765.0       31103.0           2291.0   \n",
       "...                ...      ...        ...           ...              ...   \n",
       "3350           85770.0  48330.0     1867.0       23554.0            606.0   \n",
       "3351            3175.0  57463.0     3896.0       36653.0           3293.0   \n",
       "3352            8940.0  27804.0     2880.0        9334.0            703.0   \n",
       "3353           15285.0  50960.0     5037.0       28116.0           2303.0   \n",
       "3392           63960.0  77768.0     2715.0       36561.0            995.0   \n",
       "\n",
       "      Poverty  ChildPoverty  Professional  Service  Office  Construction  \\\n",
       "0        15.7          23.0          24.4     23.5    23.4          12.9   \n",
       "1        11.9          15.5          21.3     13.0    24.2          22.7   \n",
       "2        19.1          27.1          28.2     20.9    24.0          12.3   \n",
       "3        17.8          26.8          28.2     23.3    22.1          16.5   \n",
       "4         8.8          10.1          34.2     13.4    25.3          12.9   \n",
       "...       ...           ...           ...      ...     ...           ...   \n",
       "3350     19.4          29.0          34.2     22.6    21.4          11.4   \n",
       "3351     10.7          15.3          40.0     12.7    18.1          14.5   \n",
       "3352     51.9          59.8          33.4     32.2    16.5           7.0   \n",
       "3353     16.0          20.2          30.5     18.3    19.8          15.5   \n",
       "3392      4.8           5.2          38.9     14.9    22.7           9.3   \n",
       "\n",
       "      Production  Drive  Carpool  Transit  Walk  OtherTransp  WorkAtHome  \\\n",
       "0           15.9   85.1      9.9      0.2   0.5          0.7         3.6   \n",
       "1           18.8   79.8     16.8      0.0   0.0          0.8         2.5   \n",
       "2           14.6   82.9     11.9      0.4   1.7          0.9         2.2   \n",
       "3            9.8   81.4      8.4      0.0   2.8          0.5         6.8   \n",
       "4           14.2   86.0      6.9      0.3   1.0          1.1         4.7   \n",
       "...          ...    ...      ...      ...   ...          ...         ...   \n",
       "3350        10.4   77.1      9.8      0.5   6.1          1.3         5.1   \n",
       "3351        14.7   69.9      6.6      0.0   9.8          0.7        13.0   \n",
       "3352        10.9   64.8     12.2      2.0  11.9          4.5         4.6   \n",
       "3353        15.9   85.0      7.6      0.1   2.3          1.0         3.9   \n",
       "3392        14.2   83.4      7.6      0.5   1.5          1.0         6.1   \n",
       "\n",
       "      MeanCommute  Employed  PrivateWork  PublicWork  SelfEmployed  \\\n",
       "0            23.7   19102.0         73.2        20.4           6.2   \n",
       "1            19.8    8054.0         85.3         8.7           5.7   \n",
       "2            18.7   36164.0         77.2        16.7           6.0   \n",
       "3            20.8   10387.0         72.9        12.3          14.6   \n",
       "4            20.4    4344.0         75.6        14.5           9.8   \n",
       "...           ...       ...          ...         ...           ...   \n",
       "3350         20.9   44013.0         68.3        24.0           7.5   \n",
       "3351         17.5    2019.0         64.5        13.4          20.5   \n",
       "3352         17.0    3219.0         31.1        64.8           4.1   \n",
       "3353         23.8    7965.0         76.9        16.1           6.7   \n",
       "3392         27.9   47132.0         82.0        11.8           6.1   \n",
       "\n",
       "      FamilyWork  Unemployment  \n",
       "0            0.2           4.3  \n",
       "1            0.3           4.5  \n",
       "2            0.1           7.6  \n",
       "3            0.2           6.7  \n",
       "4            0.0           3.0  \n",
       "...          ...           ...  \n",
       "3350         0.3           8.8  \n",
       "3351         1.6           0.8  \n",
       "3352         0.0          22.7  \n",
       "3353         0.2           6.5  \n",
       "3392         0.1           3.3  \n",
       "\n",
       "[3076 rows x 51 columns]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_voting_pres_df = pd.merge(merged_county_codes_df, voting_pres_df, on=[\"county\", \"state\"], how=\"left\")\n",
    "merged_voting_pres_df = merged_voting_pres_df[merged_voting_pres_df['percentage20_Donald_Trump'].notna()]\n",
    "merged_voting_pres_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "county                           0\n",
       "state                            0\n",
       "fips_code                        0\n",
       "percentage16_Donald_Trump        1\n",
       "percentage16_Hillary_Clinton     1\n",
       "total_votes16                    1\n",
       "votes16_Donald_Trump             1\n",
       "votes16_Hillary_Clinton          1\n",
       "percentage20_Donald_Trump        0\n",
       "percentage20_Joe_Biden           0\n",
       "total_votes20                    0\n",
       "votes20_Donald_Trump             0\n",
       "votes20_Joe_Biden                0\n",
       "lat                             39\n",
       "long                            39\n",
       "cases                           39\n",
       "deaths                          39\n",
       "TotalPop                         0\n",
       "Men                              0\n",
       "Women                            0\n",
       "Hispanic                         0\n",
       "White                            0\n",
       "Black                            0\n",
       "Native                           0\n",
       "Asian                            0\n",
       "Pacific                          0\n",
       "VotingAgeCitizen                 0\n",
       "Income                           0\n",
       "IncomeErr                        0\n",
       "IncomePerCap                     0\n",
       "IncomePerCapErr                  0\n",
       "Poverty                          0\n",
       "ChildPoverty                     0\n",
       "Professional                     0\n",
       "Service                          0\n",
       "Office                           0\n",
       "Construction                     0\n",
       "Production                       0\n",
       "Drive                            0\n",
       "Carpool                          0\n",
       "Transit                          0\n",
       "Walk                             0\n",
       "OtherTransp                      0\n",
       "WorkAtHome                       0\n",
       "MeanCommute                      0\n",
       "Employed                         0\n",
       "PrivateWork                      0\n",
       "PublicWork                       0\n",
       "SelfEmployed                     0\n",
       "FamilyWork                       0\n",
       "Unemployment                     0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check for null values\n",
    "merged_voting_pres_df.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "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>state</th>\n",
       "      <th>fips_code</th>\n",
       "      <th>percentage16_Donald_Trump</th>\n",
       "      <th>percentage16_Hillary_Clinton</th>\n",
       "      <th>total_votes16</th>\n",
       "      <th>votes16_Donald_Trump</th>\n",
       "      <th>votes16_Hillary_Clinton</th>\n",
       "      <th>percentage20_Donald_Trump</th>\n",
       "      <th>percentage20_Joe_Biden</th>\n",
       "      <th>total_votes20</th>\n",
       "      <th>votes20_Donald_Trump</th>\n",
       "      <th>votes20_Joe_Biden</th>\n",
       "      <th>lat</th>\n",
       "      <th>long</th>\n",
       "      <th>cases</th>\n",
       "      <th>deaths</th>\n",
       "      <th>TotalPop</th>\n",
       "      <th>Men</th>\n",
       "      <th>Women</th>\n",
       "      <th>Hispanic</th>\n",
       "      <th>White</th>\n",
       "      <th>Black</th>\n",
       "      <th>Native</th>\n",
       "      <th>Asian</th>\n",
       "      <th>Pacific</th>\n",
       "      <th>VotingAgeCitizen</th>\n",
       "      <th>Income</th>\n",
       "      <th>IncomeErr</th>\n",
       "      <th>IncomePerCap</th>\n",
       "      <th>IncomePerCapErr</th>\n",
       "      <th>Poverty</th>\n",
       "      <th>ChildPoverty</th>\n",
       "      <th>Professional</th>\n",
       "      <th>Service</th>\n",
       "      <th>Office</th>\n",
       "      <th>Construction</th>\n",
       "      <th>Production</th>\n",
       "      <th>Drive</th>\n",
       "      <th>Carpool</th>\n",
       "      <th>Transit</th>\n",
       "      <th>Walk</th>\n",
       "      <th>OtherTransp</th>\n",
       "      <th>WorkAtHome</th>\n",
       "      <th>MeanCommute</th>\n",
       "      <th>Employed</th>\n",
       "      <th>PrivateWork</th>\n",
       "      <th>PublicWork</th>\n",
       "      <th>SelfEmployed</th>\n",
       "      <th>FamilyWork</th>\n",
       "      <th>Unemployment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2110</th>\n",
       "      <td>autauga county</td>\n",
       "      <td>AL</td>\n",
       "      <td>1001</td>\n",
       "      <td>0.734</td>\n",
       "      <td>0.240</td>\n",
       "      <td>24661.0</td>\n",
       "      <td>18110.0</td>\n",
       "      <td>5908.0</td>\n",
       "      <td>0.715</td>\n",
       "      <td>0.270</td>\n",
       "      <td>27639.0</td>\n",
       "      <td>19764.0</td>\n",
       "      <td>7450.0</td>\n",
       "      <td>32.539527</td>\n",
       "      <td>-86.644082</td>\n",
       "      <td>2173.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>55036.0</td>\n",
       "      <td>26899.0</td>\n",
       "      <td>28137.0</td>\n",
       "      <td>2.7</td>\n",
       "      <td>75.4</td>\n",
       "      <td>18.9</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.9</td>\n",
       "      <td>0.0</td>\n",
       "      <td>41016.0</td>\n",
       "      <td>55317.0</td>\n",
       "      <td>2838.0</td>\n",
       "      <td>27824.0</td>\n",
       "      <td>2024.0</td>\n",
       "      <td>13.7</td>\n",
       "      <td>20.1</td>\n",
       "      <td>35.3</td>\n",
       "      <td>18.0</td>\n",
       "      <td>23.2</td>\n",
       "      <td>8.1</td>\n",
       "      <td>15.4</td>\n",
       "      <td>86.0</td>\n",
       "      <td>9.6</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.6</td>\n",
       "      <td>1.3</td>\n",
       "      <td>2.5</td>\n",
       "      <td>25.8</td>\n",
       "      <td>24112.0</td>\n",
       "      <td>74.1</td>\n",
       "      <td>20.2</td>\n",
       "      <td>5.6</td>\n",
       "      <td>0.1</td>\n",
       "      <td>5.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2111</th>\n",
       "      <td>baldwin county</td>\n",
       "      <td>AL</td>\n",
       "      <td>1003</td>\n",
       "      <td>0.774</td>\n",
       "      <td>0.196</td>\n",
       "      <td>94090.0</td>\n",
       "      <td>72780.0</td>\n",
       "      <td>18409.0</td>\n",
       "      <td>0.762</td>\n",
       "      <td>0.223</td>\n",
       "      <td>108945.0</td>\n",
       "      <td>83055.0</td>\n",
       "      <td>24344.0</td>\n",
       "      <td>30.727750</td>\n",
       "      <td>-87.722071</td>\n",
       "      <td>6966.0</td>\n",
       "      <td>71.0</td>\n",
       "      <td>203360.0</td>\n",
       "      <td>99527.0</td>\n",
       "      <td>103833.0</td>\n",
       "      <td>4.4</td>\n",
       "      <td>83.1</td>\n",
       "      <td>9.5</td>\n",
       "      <td>0.8</td>\n",
       "      <td>0.7</td>\n",
       "      <td>0.0</td>\n",
       "      <td>155376.0</td>\n",
       "      <td>52562.0</td>\n",
       "      <td>1348.0</td>\n",
       "      <td>29364.0</td>\n",
       "      <td>735.0</td>\n",
       "      <td>11.8</td>\n",
       "      <td>16.1</td>\n",
       "      <td>35.7</td>\n",
       "      <td>18.2</td>\n",
       "      <td>25.6</td>\n",
       "      <td>9.7</td>\n",
       "      <td>10.8</td>\n",
       "      <td>84.7</td>\n",
       "      <td>7.6</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.8</td>\n",
       "      <td>1.1</td>\n",
       "      <td>5.6</td>\n",
       "      <td>27.0</td>\n",
       "      <td>89527.0</td>\n",
       "      <td>80.7</td>\n",
       "      <td>12.9</td>\n",
       "      <td>6.3</td>\n",
       "      <td>0.1</td>\n",
       "      <td>5.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2112</th>\n",
       "      <td>barbour county</td>\n",
       "      <td>AL</td>\n",
       "      <td>1005</td>\n",
       "      <td>0.523</td>\n",
       "      <td>0.467</td>\n",
       "      <td>10390.0</td>\n",
       "      <td>5431.0</td>\n",
       "      <td>4848.0</td>\n",
       "      <td>0.536</td>\n",
       "      <td>0.456</td>\n",
       "      <td>10457.0</td>\n",
       "      <td>5605.0</td>\n",
       "      <td>4772.0</td>\n",
       "      <td>31.868263</td>\n",
       "      <td>-85.387129</td>\n",
       "      <td>1061.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>26201.0</td>\n",
       "      <td>13976.0</td>\n",
       "      <td>12225.0</td>\n",
       "      <td>4.2</td>\n",
       "      <td>45.7</td>\n",
       "      <td>47.8</td>\n",
       "      <td>0.2</td>\n",
       "      <td>0.6</td>\n",
       "      <td>0.0</td>\n",
       "      <td>20269.0</td>\n",
       "      <td>33368.0</td>\n",
       "      <td>2551.0</td>\n",
       "      <td>17561.0</td>\n",
       "      <td>798.0</td>\n",
       "      <td>27.2</td>\n",
       "      <td>44.9</td>\n",
       "      <td>25.0</td>\n",
       "      <td>16.8</td>\n",
       "      <td>22.6</td>\n",
       "      <td>11.5</td>\n",
       "      <td>24.1</td>\n",
       "      <td>83.4</td>\n",
       "      <td>11.1</td>\n",
       "      <td>0.3</td>\n",
       "      <td>2.2</td>\n",
       "      <td>1.7</td>\n",
       "      <td>1.3</td>\n",
       "      <td>23.4</td>\n",
       "      <td>8878.0</td>\n",
       "      <td>74.1</td>\n",
       "      <td>19.1</td>\n",
       "      <td>6.5</td>\n",
       "      <td>0.3</td>\n",
       "      <td>12.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2113</th>\n",
       "      <td>bibb county</td>\n",
       "      <td>AL</td>\n",
       "      <td>1007</td>\n",
       "      <td>0.770</td>\n",
       "      <td>0.214</td>\n",
       "      <td>8748.0</td>\n",
       "      <td>6733.0</td>\n",
       "      <td>1874.0</td>\n",
       "      <td>0.784</td>\n",
       "      <td>0.207</td>\n",
       "      <td>9573.0</td>\n",
       "      <td>7508.0</td>\n",
       "      <td>1982.0</td>\n",
       "      <td>32.996421</td>\n",
       "      <td>-87.125115</td>\n",
       "      <td>878.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>22580.0</td>\n",
       "      <td>12251.0</td>\n",
       "      <td>10329.0</td>\n",
       "      <td>2.4</td>\n",
       "      <td>74.6</td>\n",
       "      <td>22.0</td>\n",
       "      <td>0.4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>17662.0</td>\n",
       "      <td>43404.0</td>\n",
       "      <td>3431.0</td>\n",
       "      <td>20911.0</td>\n",
       "      <td>1889.0</td>\n",
       "      <td>15.2</td>\n",
       "      <td>26.6</td>\n",
       "      <td>24.4</td>\n",
       "      <td>17.6</td>\n",
       "      <td>19.7</td>\n",
       "      <td>15.9</td>\n",
       "      <td>22.4</td>\n",
       "      <td>86.4</td>\n",
       "      <td>9.5</td>\n",
       "      <td>0.7</td>\n",
       "      <td>0.3</td>\n",
       "      <td>1.7</td>\n",
       "      <td>1.5</td>\n",
       "      <td>30.0</td>\n",
       "      <td>8171.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>17.4</td>\n",
       "      <td>6.3</td>\n",
       "      <td>0.3</td>\n",
       "      <td>8.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2114</th>\n",
       "      <td>blount county</td>\n",
       "      <td>AL</td>\n",
       "      <td>1009</td>\n",
       "      <td>0.899</td>\n",
       "      <td>0.085</td>\n",
       "      <td>25384.0</td>\n",
       "      <td>22808.0</td>\n",
       "      <td>2150.0</td>\n",
       "      <td>0.896</td>\n",
       "      <td>0.096</td>\n",
       "      <td>27459.0</td>\n",
       "      <td>24595.0</td>\n",
       "      <td>2627.0</td>\n",
       "      <td>33.982109</td>\n",
       "      <td>-86.567906</td>\n",
       "      <td>2095.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>57667.0</td>\n",
       "      <td>28490.0</td>\n",
       "      <td>29177.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>87.4</td>\n",
       "      <td>1.5</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>42513.0</td>\n",
       "      <td>47412.0</td>\n",
       "      <td>2630.0</td>\n",
       "      <td>22021.0</td>\n",
       "      <td>850.0</td>\n",
       "      <td>15.6</td>\n",
       "      <td>25.4</td>\n",
       "      <td>28.5</td>\n",
       "      <td>12.9</td>\n",
       "      <td>23.3</td>\n",
       "      <td>15.8</td>\n",
       "      <td>19.5</td>\n",
       "      <td>86.8</td>\n",
       "      <td>10.2</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.4</td>\n",
       "      <td>0.4</td>\n",
       "      <td>2.1</td>\n",
       "      <td>35.0</td>\n",
       "      <td>21380.0</td>\n",
       "      <td>83.9</td>\n",
       "      <td>11.9</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.1</td>\n",
       "      <td>4.9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              county state  fips_code  percentage16_Donald_Trump  \\\n",
       "2110  autauga county    AL       1001                      0.734   \n",
       "2111  baldwin county    AL       1003                      0.774   \n",
       "2112  barbour county    AL       1005                      0.523   \n",
       "2113     bibb county    AL       1007                      0.770   \n",
       "2114   blount county    AL       1009                      0.899   \n",
       "\n",
       "      percentage16_Hillary_Clinton  total_votes16  votes16_Donald_Trump  \\\n",
       "2110                         0.240        24661.0               18110.0   \n",
       "2111                         0.196        94090.0               72780.0   \n",
       "2112                         0.467        10390.0                5431.0   \n",
       "2113                         0.214         8748.0                6733.0   \n",
       "2114                         0.085        25384.0               22808.0   \n",
       "\n",
       "      votes16_Hillary_Clinton  percentage20_Donald_Trump  \\\n",
       "2110                   5908.0                      0.715   \n",
       "2111                  18409.0                      0.762   \n",
       "2112                   4848.0                      0.536   \n",
       "2113                   1874.0                      0.784   \n",
       "2114                   2150.0                      0.896   \n",
       "\n",
       "      percentage20_Joe_Biden  total_votes20  votes20_Donald_Trump  \\\n",
       "2110                   0.270        27639.0               19764.0   \n",
       "2111                   0.223       108945.0               83055.0   \n",
       "2112                   0.456        10457.0                5605.0   \n",
       "2113                   0.207         9573.0                7508.0   \n",
       "2114                   0.096        27459.0               24595.0   \n",
       "\n",
       "      votes20_Joe_Biden        lat       long   cases  deaths  TotalPop  \\\n",
       "2110             7450.0  32.539527 -86.644082  2173.0    31.0   55036.0   \n",
       "2111            24344.0  30.727750 -87.722071  6966.0    71.0  203360.0   \n",
       "2112             4772.0  31.868263 -85.387129  1061.0     9.0   26201.0   \n",
       "2113             1982.0  32.996421 -87.125115   878.0    15.0   22580.0   \n",
       "2114             2627.0  33.982109 -86.567906  2095.0    25.0   57667.0   \n",
       "\n",
       "          Men     Women  Hispanic  White  Black  Native  Asian  Pacific  \\\n",
       "2110  26899.0   28137.0       2.7   75.4   18.9     0.3    0.9      0.0   \n",
       "2111  99527.0  103833.0       4.4   83.1    9.5     0.8    0.7      0.0   \n",
       "2112  13976.0   12225.0       4.2   45.7   47.8     0.2    0.6      0.0   \n",
       "2113  12251.0   10329.0       2.4   74.6   22.0     0.4    0.0      0.0   \n",
       "2114  28490.0   29177.0       9.0   87.4    1.5     0.3    0.1      0.0   \n",
       "\n",
       "      VotingAgeCitizen   Income  IncomeErr  IncomePerCap  IncomePerCapErr  \\\n",
       "2110           41016.0  55317.0     2838.0       27824.0           2024.0   \n",
       "2111          155376.0  52562.0     1348.0       29364.0            735.0   \n",
       "2112           20269.0  33368.0     2551.0       17561.0            798.0   \n",
       "2113           17662.0  43404.0     3431.0       20911.0           1889.0   \n",
       "2114           42513.0  47412.0     2630.0       22021.0            850.0   \n",
       "\n",
       "      Poverty  ChildPoverty  Professional  Service  Office  Construction  \\\n",
       "2110     13.7          20.1          35.3     18.0    23.2           8.1   \n",
       "2111     11.8          16.1          35.7     18.2    25.6           9.7   \n",
       "2112     27.2          44.9          25.0     16.8    22.6          11.5   \n",
       "2113     15.2          26.6          24.4     17.6    19.7          15.9   \n",
       "2114     15.6          25.4          28.5     12.9    23.3          15.8   \n",
       "\n",
       "      Production  Drive  Carpool  Transit  Walk  OtherTransp  WorkAtHome  \\\n",
       "2110        15.4   86.0      9.6      0.1   0.6          1.3         2.5   \n",
       "2111        10.8   84.7      7.6      0.1   0.8          1.1         5.6   \n",
       "2112        24.1   83.4     11.1      0.3   2.2          1.7         1.3   \n",
       "2113        22.4   86.4      9.5      0.7   0.3          1.7         1.5   \n",
       "2114        19.5   86.8     10.2      0.1   0.4          0.4         2.1   \n",
       "\n",
       "      MeanCommute  Employed  PrivateWork  PublicWork  SelfEmployed  \\\n",
       "2110         25.8   24112.0         74.1        20.2           5.6   \n",
       "2111         27.0   89527.0         80.7        12.9           6.3   \n",
       "2112         23.4    8878.0         74.1        19.1           6.5   \n",
       "2113         30.0    8171.0         76.0        17.4           6.3   \n",
       "2114         35.0   21380.0         83.9        11.9           4.0   \n",
       "\n",
       "      FamilyWork  Unemployment  \n",
       "2110         0.1           5.2  \n",
       "2111         0.1           5.5  \n",
       "2112         0.3          12.4  \n",
       "2113         0.3           8.2  \n",
       "2114         0.1           4.9  "
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Re order fips column\n",
    "merged_voting_pres_df = merged_voting_pres_df.sort_values(by='fips_code', ascending=True)\n",
    "merged_voting_pres_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "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>FIPS code</th>\n",
       "      <th>State Abr.</th>\n",
       "      <th>County name</th>\n",
       "      <th>CBSA title</th>\n",
       "      <th>CBSA 2012 pop</th>\n",
       "      <th>County 2012 pop</th>\n",
       "      <th>2013 code</th>\n",
       "      <th>2006 code</th>\n",
       "      <th>1990-based code</th>\n",
       "      <th>Unnamed: 9</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>AL</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>Montgomery, AL</td>\n",
       "      <td>377149</td>\n",
       "      <td>55514</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1003</td>\n",
       "      <td>AL</td>\n",
       "      <td>Baldwin County</td>\n",
       "      <td>Daphne-Fairhope-Foley, AL</td>\n",
       "      <td>190790</td>\n",
       "      <td>190790</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1005</td>\n",
       "      <td>AL</td>\n",
       "      <td>Barbour County</td>\n",
       "      <td>NaN</td>\n",
       "      <td>.</td>\n",
       "      <td>27201</td>\n",
       "      <td>6</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1007</td>\n",
       "      <td>AL</td>\n",
       "      <td>Bibb County</td>\n",
       "      <td>Birmingham-Hoover, AL</td>\n",
       "      <td>1136650</td>\n",
       "      <td>22597</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1009</td>\n",
       "      <td>AL</td>\n",
       "      <td>Blount County</td>\n",
       "      <td>Birmingham-Hoover, AL</td>\n",
       "      <td>1136650</td>\n",
       "      <td>57826</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1011</td>\n",
       "      <td>AL</td>\n",
       "      <td>Bullock County</td>\n",
       "      <td>NaN</td>\n",
       "      <td>.</td>\n",
       "      <td>10474</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1013</td>\n",
       "      <td>AL</td>\n",
       "      <td>Butler County</td>\n",
       "      <td>NaN</td>\n",
       "      <td>.</td>\n",
       "      <td>20307</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1015</td>\n",
       "      <td>AL</td>\n",
       "      <td>Calhoun County</td>\n",
       "      <td>Anniston-Oxford-Jacksonville, AL</td>\n",
       "      <td>117296</td>\n",
       "      <td>117296</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1017</td>\n",
       "      <td>AL</td>\n",
       "      <td>Chambers County</td>\n",
       "      <td>Valley, AL</td>\n",
       "      <td>34064</td>\n",
       "      <td>34064</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1019</td>\n",
       "      <td>AL</td>\n",
       "      <td>Cherokee County</td>\n",
       "      <td>NaN</td>\n",
       "      <td>.</td>\n",
       "      <td>26021</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   FIPS code State Abr.      County name                        CBSA title  \\\n",
       "0       1001         AL   Autauga County                    Montgomery, AL   \n",
       "1       1003         AL   Baldwin County         Daphne-Fairhope-Foley, AL   \n",
       "2       1005         AL   Barbour County                               NaN   \n",
       "3       1007         AL      Bibb County             Birmingham-Hoover, AL   \n",
       "4       1009         AL    Blount County             Birmingham-Hoover, AL   \n",
       "5       1011         AL   Bullock County                               NaN   \n",
       "6       1013         AL    Butler County                               NaN   \n",
       "7       1015         AL   Calhoun County  Anniston-Oxford-Jacksonville, AL   \n",
       "8       1017         AL  Chambers County                        Valley, AL   \n",
       "9       1019         AL  Cherokee County                               NaN   \n",
       "\n",
       "  CBSA 2012 pop County 2012 pop  2013 code  2006 code 1990-based code  \\\n",
       "0        377149           55514          3          3               3   \n",
       "1        190790          190790          4          5               3   \n",
       "2             .           27201          6          5               5   \n",
       "3       1136650           22597          2          2               6   \n",
       "4       1136650           57826          2          2               3   \n",
       "5             .           10474          6          6               6   \n",
       "6             .           20307          6          6               6   \n",
       "7        117296          117296          4          4               4   \n",
       "8         34064           34064          5          5               6   \n",
       "9             .           26021          6          6               6   \n",
       "\n",
       "   Unnamed: 9  \n",
       "0         NaN  \n",
       "1         NaN  \n",
       "2         NaN  \n",
       "3         NaN  \n",
       "4         NaN  \n",
       "5         NaN  \n",
       "6         NaN  \n",
       "7         NaN  \n",
       "8         NaN  \n",
       "9         NaN  "
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Load county size (will be used for rural vs urban classification) dataset and check dataframe\n",
    "file = \"NCHSURCodes2013.xlsx\"\n",
    "county_size_df = pd.read_excel(file, index_col=False)\n",
    "county_size_df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop unnamed columns. Code from https://stackoverflow.com/questions/49645135/python-pandas-display-extra-unnamed-columns-for-an-excel-file\n",
    "county_size_df = county_size_df[county_size_df.filter(regex='^(?!Unnamed)').columns]\n",
    "\n",
    "# Drop columns we don't need\n",
    "county_size_df = county_size_df.drop(columns={'CBSA title', 'CBSA 2012 pop', '2006 code', '1990-based code'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Rename and reorder columns \n",
    "county_size_df.rename({'FIPS code':'fips_code', 'State Abr.': 'state', 'County name':'county', 'County 2012 pop':'population', '2013 code':'density'}, axis='columns', inplace=True)\n",
    "size_cols = ['fips_code', 'county', 'state', 'population', 'density']\n",
    "county_size_df = county_size_df[size_cols]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Code the density column so 1-3 = urban and 4-6 = rural\n",
    "density_dict = {1:'urban', 2:'urban', 3:'urban', 4:'urban', 5:'rural', 6:'rural'}\n",
    "county_size_df['density'] = county_size_df['density'].replace(density_dict)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "fips_code     0\n",
       "county        0\n",
       "state         0\n",
       "population    0\n",
       "density       0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check for null values and other things we don't want in the data\n",
    "county_size_df.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       ".        4\n",
       "14008    3\n",
       "8983     2\n",
       "11187    2\n",
       "22058    2\n",
       "        ..\n",
       "16996    1\n",
       "58723    1\n",
       "13668    1\n",
       "13798    1\n",
       "23807    1\n",
       "Name: population, Length: 3092, dtype: int64"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Realized there are periods in the population column. \n",
    "county_size_df['population'].value_counts()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "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>fips_code</th>\n",
       "      <th>county</th>\n",
       "      <th>state</th>\n",
       "      <th>population</th>\n",
       "      <th>density</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>2201</td>\n",
       "      <td>Prince of Wales-Outer Ket</td>\n",
       "      <td>AK</td>\n",
       "      <td>.</td>\n",
       "      <td>rural</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92</th>\n",
       "      <td>2232</td>\n",
       "      <td>Skagway-Hoonah-Angoon Census Area</td>\n",
       "      <td>AK</td>\n",
       "      <td>.</td>\n",
       "      <td>rural</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>2280</td>\n",
       "      <td>Wrangell-Petersburg Census Area</td>\n",
       "      <td>AK</td>\n",
       "      <td>.</td>\n",
       "      <td>rural</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2926</th>\n",
       "      <td>51560</td>\n",
       "      <td>Clifton Forge city</td>\n",
       "      <td>VA</td>\n",
       "      <td>.</td>\n",
       "      <td>rural</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      fips_code                             county state population density\n",
       "89         2201          Prince of Wales-Outer Ket    AK          .   rural\n",
       "92         2232  Skagway-Hoonah-Angoon Census Area    AK          .   rural\n",
       "97         2280    Wrangell-Petersburg Census Area    AK          .   rural\n",
       "2926      51560                 Clifton Forge city    VA          .   rural"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Locate the period colunms\n",
    "county_size_df.loc[county_size_df['population'] == '.']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "# These seem to be census outliers according to google. Dropping them. \n",
    "\n",
    "county_size_df = county_size_df.drop([89, 92, 97, 2926])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "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>fips_code</th>\n",
       "      <th>county</th>\n",
       "      <th>state</th>\n",
       "      <th>population</th>\n",
       "      <th>density</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [fips_code, county, state, population, density]\n",
       "Index: []"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Double checking those period ones are gone. \n",
    "county_size_df.loc[county_size_df['population'] == '.']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create SQL engine that connects to an AWS database. \n",
    "db_string = f\"postgres://postgres:{db_password}@vaccine-hesitancy.czxeoxdbrl4n.us-east-1.rds.amazonaws.com:5432/vaccine_hesitancy\"                                                                                        \n",
    "engine = create_engine(db_string)\n",
    "\n",
    "# Create SQL engine that uses a local database - leaving code commented out in case needed. \n",
    "### db_string = f\"postgres://postgres:{db_password}@127.0.0.1:5432/vaccine_hesitancy\"                                                                                        \n",
    "### engine = create_engine(db_string)\n",
    "\n",
    "# Import all 3 tables to Postgres DB\n",
    "census_demographic_df.to_sql(name='census_demographic', con=engine, index=False, if_exists='append')\n",
    "vaccine_hesitancy_df.to_sql(name='vaccine_hesitancy_covid', con=engine, index=False, if_exists='append')   \n",
    "merged_voting_pres_df.to_sql(name='county_statistics', con=engine, index=False, if_exists='append')  \n",
    "county_size_df.to_sql(name='county_size', con=engine, index=False, if_exists='append')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "PythonData",
   "language": "python",
   "name": "pythondata"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}