data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Code for data work for Medicaid/SNAP paper"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import packages\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import os\n",
    "import json\n",
    "import pickle\n",
    "import openpyxl\n",
    "import geopandas as gpd\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "from bokeh.io import output_file, output_notebook\n",
    "from bokeh.palettes import Viridis256\n",
    "from bokeh.plotting import figure, show\n",
    "from bokeh.models import (ColumnDataSource, Title, Label, LabelSet, Legend,\n",
    "                          LegendItem, CategoricalColorMapper, ColorBar,\n",
    "                          HoverTool, NumeralTickFormatter, GeoJSONDataSource,\n",
    "                          FactorRange)\n",
    "from bokeh.models.tickers import SingleIntervalTicker\n",
    "from bokeh.transform import factor_cmap"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "cur_dir = \"/Users/richardevans/Docs/Economics/OSE/MedicaidSNAP\"\n",
    "data_dir = os.path.join(cur_dir, \"data\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read in data from PovertyEstimates.xlsx file into pandas dataframe\n",
    "df = pd.read_excel(\n",
    "    os.path.join(data_dir, \"PovertyEstimates.xlsx\"),\n",
    "    sheet_name=\"PovertyEstimates\",\n",
    "    header=4,\n",
    "    usecols=[\n",
    "        \"FIPS_Code\",\n",
    "        \"Stabr\",\n",
    "        \"Area_name\",\n",
    "        \"Rural-urban_Continuum_Code_2003\",\n",
    "        \"Urban_Influence_Code_2003\",\n",
    "        \"Rural-urban_Continuum_Code_2013\",\n",
    "        \"Urban_Influence_Code_2013\",\n",
    "        \"PCTPOVALL_2021\",\n",
    "        \"PCTPOV017_2021\",\n",
    "        \"PCTPOV517_2021\",\n",
    "        \"MEDHHINC_2021\",\n",
    "    ]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Change FIPS_Code variable to 5-digit string\n",
    "df[\"FIPS_Code\"] = df[\"FIPS_Code\"].astype(str).str.zfill(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a Python list of US state full names\n",
    "US_states_list = [\n",
    "    \"Alabama\",\n",
    "    \"Alaska\",\n",
    "    \"Arizona\",\n",
    "    \"Arkansas\",\n",
    "    \"California\",\n",
    "    \"Colorado\",\n",
    "    \"Connecticut\",\n",
    "    \"Delaware\",\n",
    "    \"Florida\",\n",
    "    \"Georgia\",\n",
    "    \"Hawaii\",\n",
    "    \"Idaho\",\n",
    "    \"Illinois\",\n",
    "    \"Indiana\",\n",
    "    \"Iowa\",\n",
    "    \"Kansas\",\n",
    "    \"Kentucky\",\n",
    "    \"Louisiana\",\n",
    "    \"Maine\",\n",
    "    \"Maryland\",\n",
    "    \"Massachusetts\",\n",
    "    \"Michigan\",\n",
    "    \"Minnesota\",\n",
    "    \"Mississippi\",\n",
    "    \"Missouri\",\n",
    "    \"Montana\",\n",
    "    \"Nebraska\",\n",
    "    \"Nevada\",\n",
    "    \"New Hampshire\",\n",
    "    \"New Jersey\",\n",
    "    \"New Mexico\",\n",
    "    \"New York\",\n",
    "    \"North Carolina\",\n",
    "    \"North Dakota\",\n",
    "    \"Ohio\",\n",
    "    \"Oklahoma\",\n",
    "    \"Oregon\",\n",
    "    \"Pennsylvania\",\n",
    "    \"Rhode Island\",\n",
    "    \"South Carolina\",\n",
    "    \"South Dakota\",\n",
    "    \"Tennessee\",\n",
    "    \"Texas\",\n",
    "    \"Utah\",\n",
    "    \"Vermont\",\n",
    "    \"Virginia\",\n",
    "    \"Washington\",\n",
    "    \"West Virginia\",\n",
    "    \"Wisconsin\",\n",
    "    \"Wyoming\",\n",
    "]\n",
    "\n",
    "# Delete rows for Area_name equals United States or any of the US states alone\n",
    "delete_list = [\"United States\"] + US_states_list\n",
    "df = df[~df[\"Area_name\"].isin(delete_list)]\n",
    "\n",
    "# Delete the state entry for District of Columbia (FIPS_Code=\"11000\"), but\n",
    "# leave the county entry (FIPS_Code=\"11001\")\n",
    "df = df[df[\"FIPS_Code\"] != \"11000\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Merge in unemployment data\n",
    "df_unemp = pd.read_excel(\n",
    "    os.path.join(data_dir, \"Unemployment.xlsx\"),\n",
    "    sheet_name=\"UnemploymentMedianIncome\",\n",
    "    header=4,\n",
    "    usecols=[\n",
    "        \"FIPS_Code\",\n",
    "        \"State\",\n",
    "        \"Area_Name\",\n",
    "        \"Rural_Urban_Continuum_Code_2013\",\n",
    "        \"Urban_Influence_Code_2013\",\n",
    "        \"Metro_2013\",\n",
    "        \"Unemployment_rate_2021\",\n",
    "        \"Unemployment_rate_2022\"\n",
    "    ]\n",
    ")\n",
    "\n",
    "# Rename column to differentiate from the df dataframe\n",
    "df_unemp.rename(\n",
    "    columns={\n",
    "        \"Urban_Influence_Code_2013\": \"Urban_Influence_Code_2013_unemp\",\n",
    "    },\n",
    "    inplace=True\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>Stabr</th>\n",
       "      <th>Area_name</th>\n",
       "      <th>Rural-urban_Continuum_Code_2003</th>\n",
       "      <th>Urban_Influence_Code_2003</th>\n",
       "      <th>Rural-urban_Continuum_Code_2013</th>\n",
       "      <th>Urban_Influence_Code_2013</th>\n",
       "      <th>PCTPOVALL_2021</th>\n",
       "      <th>PCTPOV017_2021</th>\n",
       "      <th>PCTPOV517_2021</th>\n",
       "      <th>MEDHHINC_2021</th>\n",
       "      <th>Area_Name</th>\n",
       "      <th>Rural_Urban_Continuum_Code_2013</th>\n",
       "      <th>Urban_Influence_Code_2013_unemp</th>\n",
       "      <th>Metro_2013</th>\n",
       "      <th>Unemployment_rate_2021</th>\n",
       "      <th>Unemployment_rate_2022</th>\n",
       "      <th>_merge</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>01001</td>\n",
       "      <td>AL</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>10.7</td>\n",
       "      <td>16.1</td>\n",
       "      <td>15.6</td>\n",
       "      <td>66444.0</td>\n",
       "      <td>Autauga County, AL</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.8</td>\n",
       "      <td>2.3</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>01003</td>\n",
       "      <td>AL</td>\n",
       "      <td>Baldwin County</td>\n",
       "      <td>4.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>10.8</td>\n",
       "      <td>16.4</td>\n",
       "      <td>15.2</td>\n",
       "      <td>65658.0</td>\n",
       "      <td>Baldwin County, AL</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.9</td>\n",
       "      <td>2.4</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>01005</td>\n",
       "      <td>AL</td>\n",
       "      <td>Barbour County</td>\n",
       "      <td>6.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>35.1</td>\n",
       "      <td>33.8</td>\n",
       "      <td>38649.0</td>\n",
       "      <td>Barbour County, AL</td>\n",
       "      <td>6.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.5</td>\n",
       "      <td>4.1</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>01007</td>\n",
       "      <td>AL</td>\n",
       "      <td>Bibb County</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>20.6</td>\n",
       "      <td>29.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>48454.0</td>\n",
       "      <td>Bibb County, AL</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.4</td>\n",
       "      <td>2.5</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>01009</td>\n",
       "      <td>AL</td>\n",
       "      <td>Blount County</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>16.7</td>\n",
       "      <td>15.9</td>\n",
       "      <td>56894.0</td>\n",
       "      <td>Blount County, AL</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.4</td>\n",
       "      <td>2.2</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  FIPS_Code Stabr       Area_name  Rural-urban_Continuum_Code_2003  \\\n",
       "0     01001    AL  Autauga County                              2.0   \n",
       "1     01003    AL  Baldwin County                              4.0   \n",
       "2     01005    AL  Barbour County                              6.0   \n",
       "3     01007    AL     Bibb County                              1.0   \n",
       "4     01009    AL   Blount County                              1.0   \n",
       "\n",
       "   Urban_Influence_Code_2003  Rural-urban_Continuum_Code_2013  \\\n",
       "0                        2.0                              2.0   \n",
       "1                        5.0                              3.0   \n",
       "2                        6.0                              6.0   \n",
       "3                        1.0                              1.0   \n",
       "4                        1.0                              1.0   \n",
       "\n",
       "   Urban_Influence_Code_2013  PCTPOVALL_2021  PCTPOV017_2021  PCTPOV517_2021  \\\n",
       "0                        2.0            10.7            16.1            15.6   \n",
       "1                        2.0            10.8            16.4            15.2   \n",
       "2                        6.0            23.0            35.1            33.8   \n",
       "3                        1.0            20.6            29.0            29.0   \n",
       "4                        1.0            12.0            16.7            15.9   \n",
       "\n",
       "   MEDHHINC_2021           Area_Name  Rural_Urban_Continuum_Code_2013  \\\n",
       "0        66444.0  Autauga County, AL                              2.0   \n",
       "1        65658.0  Baldwin County, AL                              3.0   \n",
       "2        38649.0  Barbour County, AL                              6.0   \n",
       "3        48454.0     Bibb County, AL                              1.0   \n",
       "4        56894.0   Blount County, AL                              1.0   \n",
       "\n",
       "   Urban_Influence_Code_2013_unemp  Metro_2013  Unemployment_rate_2021  \\\n",
       "0                              2.0         1.0                     2.8   \n",
       "1                              2.0         1.0                     2.9   \n",
       "2                              6.0         0.0                     5.5   \n",
       "3                              1.0         1.0                     3.4   \n",
       "4                              1.0         1.0                     2.4   \n",
       "\n",
       "   Unemployment_rate_2022 _merge  \n",
       "0                     2.3   both  \n",
       "1                     2.4   both  \n",
       "2                     4.1   both  \n",
       "3                     2.5   both  \n",
       "4                     2.2   both  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Change FIPS_Code variable to 5-digit string\n",
    "df_unemp[\"FIPS_Code\"] = df_unemp[\"FIPS_Code\"].astype(str).str.zfill(5)\n",
    "\n",
    "# Delete rows for Area_name equals United States or any of the US states alone\n",
    "df_unemp = df_unemp[~df_unemp[\"Area_Name\"].isin(delete_list)]\n",
    "\n",
    "# Drop if state is Puerto Rico (PR)\n",
    "df_unemp = df_unemp[df_unemp[\"State\"] != \"PR\"]\n",
    "\n",
    "# Merge Metro_2013, Unemployment_rate_2021, and Unemployment_rate_2022 into df based on FIPS_Code\n",
    "df = pd.merge(\n",
    "    df,\n",
    "    df_unemp[[\n",
    "        \"FIPS_Code\",\n",
    "        \"Area_Name\",\n",
    "        \"Rural_Urban_Continuum_Code_2013\",\n",
    "        \"Urban_Influence_Code_2013_unemp\",\n",
    "        \"Metro_2013\",\n",
    "        \"Unemployment_rate_2021\",\n",
    "        \"Unemployment_rate_2022\"\n",
    "    ]],\n",
    "    on=\"FIPS_Code\",\n",
    "    how=\"outer\",\n",
    "    indicator=True\n",
    ")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>Rural-urban_Continuum_Code_2003</th>\n",
       "      <th>Urban_Influence_Code_2003</th>\n",
       "      <th>Rural-urban_Continuum_Code_2013</th>\n",
       "      <th>Urban_Influence_Code_2013</th>\n",
       "      <th>PCTPOVALL_2021</th>\n",
       "      <th>PCTPOV017_2021</th>\n",
       "      <th>PCTPOV517_2021</th>\n",
       "      <th>MEDHHINC_2021</th>\n",
       "      <th>Rural_Urban_Continuum_Code_2013</th>\n",
       "      <th>Urban_Influence_Code_2013_unemp</th>\n",
       "      <th>Metro_2013</th>\n",
       "      <th>Unemployment_rate_2021</th>\n",
       "      <th>Unemployment_rate_2022</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>3136.000000</td>\n",
       "      <td>3136.000000</td>\n",
       "      <td>3141.000000</td>\n",
       "      <td>3141.000000</td>\n",
       "      <td>3142.000000</td>\n",
       "      <td>3142.000000</td>\n",
       "      <td>3142.000000</td>\n",
       "      <td>3142.000000</td>\n",
       "      <td>3141.000000</td>\n",
       "      <td>3141.000000</td>\n",
       "      <td>3146.000000</td>\n",
       "      <td>3143.000000</td>\n",
       "      <td>3143.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>5.124362</td>\n",
       "      <td>5.446747</td>\n",
       "      <td>5.007323</td>\n",
       "      <td>5.265839</td>\n",
       "      <td>14.609866</td>\n",
       "      <td>20.050127</td>\n",
       "      <td>18.902355</td>\n",
       "      <td>58941.633355</td>\n",
       "      <td>5.009233</td>\n",
       "      <td>5.268704</td>\n",
       "      <td>0.370312</td>\n",
       "      <td>4.650843</td>\n",
       "      <td>3.594400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>2.681944</td>\n",
       "      <td>3.468386</td>\n",
       "      <td>2.707905</td>\n",
       "      <td>3.498363</td>\n",
       "      <td>5.663115</td>\n",
       "      <td>8.413598</td>\n",
       "      <td>8.138269</td>\n",
       "      <td>15267.686038</td>\n",
       "      <td>2.708605</td>\n",
       "      <td>3.499373</td>\n",
       "      <td>0.482965</td>\n",
       "      <td>1.730044</td>\n",
       "      <td>1.231279</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>2.900000</td>\n",
       "      <td>2.800000</td>\n",
       "      <td>2.400000</td>\n",
       "      <td>25653.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.900000</td>\n",
       "      <td>0.600000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>3.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>10.600000</td>\n",
       "      <td>13.800000</td>\n",
       "      <td>12.800000</td>\n",
       "      <td>49016.250000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3.500000</td>\n",
       "      <td>2.700000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>6.000000</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>13.600000</td>\n",
       "      <td>19.100000</td>\n",
       "      <td>17.800000</td>\n",
       "      <td>56634.000000</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>4.400000</td>\n",
       "      <td>3.400000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>7.000000</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>17.575000</td>\n",
       "      <td>24.800000</td>\n",
       "      <td>23.400000</td>\n",
       "      <td>65687.250000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>5.500000</td>\n",
       "      <td>4.200000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>9.000000</td>\n",
       "      <td>12.000000</td>\n",
       "      <td>9.000000</td>\n",
       "      <td>12.000000</td>\n",
       "      <td>43.900000</td>\n",
       "      <td>58.500000</td>\n",
       "      <td>61.100000</td>\n",
       "      <td>153716.000000</td>\n",
       "      <td>9.000000</td>\n",
       "      <td>12.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>19.500000</td>\n",
       "      <td>14.700000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Rural-urban_Continuum_Code_2003  Urban_Influence_Code_2003  \\\n",
       "count                      3136.000000                3136.000000   \n",
       "mean                          5.124362                   5.446747   \n",
       "std                           2.681944                   3.468386   \n",
       "min                           1.000000                   1.000000   \n",
       "25%                           3.000000                   2.000000   \n",
       "50%                           6.000000                   5.000000   \n",
       "75%                           7.000000                   8.000000   \n",
       "max                           9.000000                  12.000000   \n",
       "\n",
       "       Rural-urban_Continuum_Code_2013  Urban_Influence_Code_2013  \\\n",
       "count                      3141.000000                3141.000000   \n",
       "mean                          5.007323                   5.265839   \n",
       "std                           2.707905                   3.498363   \n",
       "min                           1.000000                   1.000000   \n",
       "25%                           2.000000                   2.000000   \n",
       "50%                           6.000000                   5.000000   \n",
       "75%                           7.000000                   8.000000   \n",
       "max                           9.000000                  12.000000   \n",
       "\n",
       "       PCTPOVALL_2021  PCTPOV017_2021  PCTPOV517_2021  MEDHHINC_2021  \\\n",
       "count     3142.000000     3142.000000     3142.000000    3142.000000   \n",
       "mean        14.609866       20.050127       18.902355   58941.633355   \n",
       "std          5.663115        8.413598        8.138269   15267.686038   \n",
       "min          2.900000        2.800000        2.400000   25653.000000   \n",
       "25%         10.600000       13.800000       12.800000   49016.250000   \n",
       "50%         13.600000       19.100000       17.800000   56634.000000   \n",
       "75%         17.575000       24.800000       23.400000   65687.250000   \n",
       "max         43.900000       58.500000       61.100000  153716.000000   \n",
       "\n",
       "       Rural_Urban_Continuum_Code_2013  Urban_Influence_Code_2013_unemp  \\\n",
       "count                      3141.000000                      3141.000000   \n",
       "mean                          5.009233                         5.268704   \n",
       "std                           2.708605                         3.499373   \n",
       "min                           1.000000                         1.000000   \n",
       "25%                           2.000000                         2.000000   \n",
       "50%                           6.000000                         5.000000   \n",
       "75%                           7.000000                         8.000000   \n",
       "max                           9.000000                        12.000000   \n",
       "\n",
       "        Metro_2013  Unemployment_rate_2021  Unemployment_rate_2022  \n",
       "count  3146.000000             3143.000000             3143.000000  \n",
       "mean      0.370312                4.650843                3.594400  \n",
       "std       0.482965                1.730044                1.231279  \n",
       "min       0.000000                0.900000                0.600000  \n",
       "25%       0.000000                3.500000                2.700000  \n",
       "50%       0.000000                4.400000                3.400000  \n",
       "75%       1.000000                5.500000                4.200000  \n",
       "max       1.000000               19.500000               14.700000  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "_merge\n",
       "both          3142\n",
       "right_only       5\n",
       "left_only        1\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[\"_merge\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>Area_name</th>\n",
       "      <th>Area_Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>554</th>\n",
       "      <td>15005</td>\n",
       "      <td>Kalawao County</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    FIPS_Code       Area_name Area_Name\n",
       "554     15005  Kalawao County       NaN"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[[\"FIPS_Code\", \"Area_name\", \"Area_Name\"]][df[\"_merge\"] == \"left_only\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>Area_name</th>\n",
       "      <th>Area_Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>91</th>\n",
       "      <td>02201</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Prince of Wales-Outer Ketchikan Census Area, AK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>02232</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Skagway-Hoonah-Angoon Census Area, AK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>02261</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Valdez-Cordova Census Area, AK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>02280</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Wrangell-Petersburg Census Area, AK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>324</th>\n",
       "      <td>11000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>District of Columbia</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    FIPS_Code Area_name                                        Area_Name\n",
       "91      02201       NaN  Prince of Wales-Outer Ketchikan Census Area, AK\n",
       "94      02232       NaN            Skagway-Hoonah-Angoon Census Area, AK\n",
       "96      02261       NaN                   Valdez-Cordova Census Area, AK\n",
       "98      02280       NaN              Wrangell-Petersburg Census Area, AK\n",
       "324     11000       NaN                             District of Columbia"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[[\"FIPS_Code\", \"Area_name\", \"Area_Name\"]][df[\"_merge\"] == \"right_only\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>Area_name</th>\n",
       "      <th>Area_Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>91</th>\n",
       "      <td>02201</td>\n",
       "      <td>Prince of Wales-Outer Ketchikan Census Area, AK</td>\n",
       "      <td>Prince of Wales-Outer Ketchikan Census Area, AK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>02232</td>\n",
       "      <td>Skagway-Hoonah-Angoon Census Area, AK</td>\n",
       "      <td>Skagway-Hoonah-Angoon Census Area, AK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>02261</td>\n",
       "      <td>Valdez-Cordova Census Area, AK</td>\n",
       "      <td>Valdez-Cordova Census Area, AK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>02280</td>\n",
       "      <td>Wrangell-Petersburg Census Area, AK</td>\n",
       "      <td>Wrangell-Petersburg Census Area, AK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>324</th>\n",
       "      <td>11000</td>\n",
       "      <td>District of Columbia</td>\n",
       "      <td>District of Columbia</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    FIPS_Code                                        Area_name  \\\n",
       "91      02201  Prince of Wales-Outer Ketchikan Census Area, AK   \n",
       "94      02232            Skagway-Hoonah-Angoon Census Area, AK   \n",
       "96      02261                   Valdez-Cordova Census Area, AK   \n",
       "98      02280              Wrangell-Petersburg Census Area, AK   \n",
       "324     11000                             District of Columbia   \n",
       "\n",
       "                                           Area_Name  \n",
       "91   Prince of Wales-Outer Ketchikan Census Area, AK  \n",
       "94             Skagway-Hoonah-Angoon Census Area, AK  \n",
       "96                    Valdez-Cordova Census Area, AK  \n",
       "98               Wrangell-Petersburg Census Area, AK  \n",
       "324                             District of Columbia  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Replace Area_name with Area_Name for observations in which _merge is \"right_only\"\n",
    "df.loc[df[\"_merge\"] == \"right_only\", \"Area_name\"] = df.loc[df[\"_merge\"] == \"right_only\", \"Area_Name\"]\n",
    "df[[\"FIPS_Code\", \"Area_name\", \"Area_Name\"]][df[\"_merge\"] == \"right_only\"]"
   ]
  },
  {
   "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>Area_name</th>\n",
       "      <th>Rural-urban_Continuum_Code_2013</th>\n",
       "      <th>Rural_Urban_Continuum_Code_2013</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>91</th>\n",
       "      <td>02201</td>\n",
       "      <td>Prince of Wales-Outer Ketchikan Census Area, AK</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>02232</td>\n",
       "      <td>Skagway-Hoonah-Angoon Census Area, AK</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>02261</td>\n",
       "      <td>Valdez-Cordova Census Area, AK</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>02280</td>\n",
       "      <td>Wrangell-Petersburg Census Area, AK</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>324</th>\n",
       "      <td>11000</td>\n",
       "      <td>District of Columbia</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    FIPS_Code                                        Area_name  \\\n",
       "91      02201  Prince of Wales-Outer Ketchikan Census Area, AK   \n",
       "94      02232            Skagway-Hoonah-Angoon Census Area, AK   \n",
       "96      02261                   Valdez-Cordova Census Area, AK   \n",
       "98      02280              Wrangell-Petersburg Census Area, AK   \n",
       "324     11000                             District of Columbia   \n",
       "\n",
       "     Rural-urban_Continuum_Code_2013  Rural_Urban_Continuum_Code_2013  \n",
       "91                               NaN                              NaN  \n",
       "94                               NaN                              NaN  \n",
       "96                               NaN                              9.0  \n",
       "98                               NaN                              NaN  \n",
       "324                              NaN                              NaN  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[[\"FIPS_Code\", \"Area_name\", \"Rural-urban_Continuum_Code_2013\", \"Rural_Urban_Continuum_Code_2013\"]][df[\"_merge\"] == \"right_only\"]"
   ]
  },
  {
   "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>FIPS_Code</th>\n",
       "      <th>Area_name</th>\n",
       "      <th>Rural-urban_Continuum_Code_2013</th>\n",
       "      <th>Rural_Urban_Continuum_Code_2013</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>91</th>\n",
       "      <td>02201</td>\n",
       "      <td>Prince of Wales-Outer Ketchikan Census Area, AK</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>02232</td>\n",
       "      <td>Skagway-Hoonah-Angoon Census Area, AK</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>02261</td>\n",
       "      <td>Valdez-Cordova Census Area, AK</td>\n",
       "      <td>9.0</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>02280</td>\n",
       "      <td>Wrangell-Petersburg Census Area, AK</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>324</th>\n",
       "      <td>11000</td>\n",
       "      <td>District of Columbia</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    FIPS_Code                                        Area_name  \\\n",
       "91      02201  Prince of Wales-Outer Ketchikan Census Area, AK   \n",
       "94      02232            Skagway-Hoonah-Angoon Census Area, AK   \n",
       "96      02261                   Valdez-Cordova Census Area, AK   \n",
       "98      02280              Wrangell-Petersburg Census Area, AK   \n",
       "324     11000                             District of Columbia   \n",
       "\n",
       "     Rural-urban_Continuum_Code_2013  Rural_Urban_Continuum_Code_2013  \n",
       "91                               NaN                              NaN  \n",
       "94                               NaN                              NaN  \n",
       "96                               9.0                              9.0  \n",
       "98                               NaN                              NaN  \n",
       "324                              NaN                              NaN  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Replace ? with Rural_Urban_Continuum_Code_2013 for\n",
    "# observations in which _merge is \"right_only\"\n",
    "df.loc[df[\"_merge\"] == \"right_only\", \"Rural-urban_Continuum_Code_2013\"] = df.loc[df[\"_merge\"] == \"right_only\", \"Rural_Urban_Continuum_Code_2013\"]\n",
    "df[[\"FIPS_Code\", \"Area_name\", \"Rural-urban_Continuum_Code_2013\", \"Rural_Urban_Continuum_Code_2013\"]][df[\"_merge\"] == \"right_only\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>Area_name</th>\n",
       "      <th>Urban_Influence_Code_2013</th>\n",
       "      <th>Urban_Influence_Code_2013_unemp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>91</th>\n",
       "      <td>02201</td>\n",
       "      <td>Prince of Wales-Outer Ketchikan Census Area, AK</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>02232</td>\n",
       "      <td>Skagway-Hoonah-Angoon Census Area, AK</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>02261</td>\n",
       "      <td>Valdez-Cordova Census Area, AK</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>02280</td>\n",
       "      <td>Wrangell-Petersburg Census Area, AK</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>324</th>\n",
       "      <td>11000</td>\n",
       "      <td>District of Columbia</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    FIPS_Code                                        Area_name  \\\n",
       "91      02201  Prince of Wales-Outer Ketchikan Census Area, AK   \n",
       "94      02232            Skagway-Hoonah-Angoon Census Area, AK   \n",
       "96      02261                   Valdez-Cordova Census Area, AK   \n",
       "98      02280              Wrangell-Petersburg Census Area, AK   \n",
       "324     11000                             District of Columbia   \n",
       "\n",
       "     Urban_Influence_Code_2013  Urban_Influence_Code_2013_unemp  \n",
       "91                         NaN                              NaN  \n",
       "94                         NaN                              NaN  \n",
       "96                         NaN                             11.0  \n",
       "98                         NaN                              NaN  \n",
       "324                        NaN                              NaN  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[[\"FIPS_Code\", \"Area_name\", \"Urban_Influence_Code_2013\", \"Urban_Influence_Code_2013_unemp\"]][df[\"_merge\"] == \"right_only\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>Area_name</th>\n",
       "      <th>Urban_Influence_Code_2013</th>\n",
       "      <th>Urban_Influence_Code_2013_unemp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>91</th>\n",
       "      <td>02201</td>\n",
       "      <td>Prince of Wales-Outer Ketchikan Census Area, AK</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>02232</td>\n",
       "      <td>Skagway-Hoonah-Angoon Census Area, AK</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>02261</td>\n",
       "      <td>Valdez-Cordova Census Area, AK</td>\n",
       "      <td>11.0</td>\n",
       "      <td>11.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>02280</td>\n",
       "      <td>Wrangell-Petersburg Census Area, AK</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>324</th>\n",
       "      <td>11000</td>\n",
       "      <td>District of Columbia</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    FIPS_Code                                        Area_name  \\\n",
       "91      02201  Prince of Wales-Outer Ketchikan Census Area, AK   \n",
       "94      02232            Skagway-Hoonah-Angoon Census Area, AK   \n",
       "96      02261                   Valdez-Cordova Census Area, AK   \n",
       "98      02280              Wrangell-Petersburg Census Area, AK   \n",
       "324     11000                             District of Columbia   \n",
       "\n",
       "     Urban_Influence_Code_2013  Urban_Influence_Code_2013_unemp  \n",
       "91                         NaN                              NaN  \n",
       "94                         NaN                              NaN  \n",
       "96                        11.0                             11.0  \n",
       "98                         NaN                              NaN  \n",
       "324                        NaN                              NaN  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Replace Urban_Influence_Code_2013 with Urban_Influence_Code_2013_unemp for\n",
    "# observations in which _merge is \"right_only\"\n",
    "df.loc[df[\"_merge\"] == \"right_only\", \"Urban_Influence_Code_2013\"] = df.loc[df[\"_merge\"] == \"right_only\", \"Urban_Influence_Code_2013_unemp\"]\n",
    "df[[\"FIPS_Code\", \"Area_name\", \"Urban_Influence_Code_2013\", \"Urban_Influence_Code_2013_unemp\"]][df[\"_merge\"] == \"right_only\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['FIPS_Code', 'Stabr', 'Area_name', 'Rural-urban_Continuum_Code_2003',\n",
       "       'Urban_Influence_Code_2003', 'Rural-urban_Continuum_Code_2013',\n",
       "       'Urban_Influence_Code_2013', 'PCTPOVALL_2021', 'PCTPOV017_2021',\n",
       "       'PCTPOV517_2021', 'MEDHHINC_2021', 'Metro_2013',\n",
       "       'Unemployment_rate_2021', 'Unemployment_rate_2022'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Drop the _merge and Area_Name columns\n",
    "df = df.drop(columns=[\"_merge\", \"Area_Name\", \"Rural_Urban_Continuum_Code_2013\", \"Urban_Influence_Code_2013_unemp\"])\n",
    "df.keys()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Create Average Medicaid payout per participant data by state and add it to the county data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "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>Stabr</th>\n",
       "      <th>Area_name</th>\n",
       "      <th>avg_nom_medicaid</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>01000</td>\n",
       "      <td>AL</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>4777.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>02000</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>8640.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>04000</td>\n",
       "      <td>AZ</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>5616.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>05000</td>\n",
       "      <td>AR</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>6480.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>06000</td>\n",
       "      <td>CA</td>\n",
       "      <td>California</td>\n",
       "      <td>5581.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>08000</td>\n",
       "      <td>CO</td>\n",
       "      <td>Colorado</td>\n",
       "      <td>6414.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>09000</td>\n",
       "      <td>CT</td>\n",
       "      <td>Connecticut</td>\n",
       "      <td>8063.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>10000</td>\n",
       "      <td>DE</td>\n",
       "      <td>Delaware</td>\n",
       "      <td>7701.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>11000</td>\n",
       "      <td>DC</td>\n",
       "      <td>District of Columbia</td>\n",
       "      <td>10166.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>12000</td>\n",
       "      <td>FL</td>\n",
       "      <td>Florida</td>\n",
       "      <td>4718.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>13000</td>\n",
       "      <td>GA</td>\n",
       "      <td>Georgia</td>\n",
       "      <td>4396.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>15000</td>\n",
       "      <td>HI</td>\n",
       "      <td>Hawaii</td>\n",
       "      <td>5483.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>16000</td>\n",
       "      <td>ID</td>\n",
       "      <td>Idaho</td>\n",
       "      <td>6823.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>17000</td>\n",
       "      <td>IL</td>\n",
       "      <td>Illinois</td>\n",
       "      <td>5491.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>18000</td>\n",
       "      <td>IN</td>\n",
       "      <td>Indiana</td>\n",
       "      <td>7000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>19000</td>\n",
       "      <td>IA</td>\n",
       "      <td>Iowa</td>\n",
       "      <td>6658.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>20000</td>\n",
       "      <td>KS</td>\n",
       "      <td>Kansas</td>\n",
       "      <td>7626.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>21000</td>\n",
       "      <td>KY</td>\n",
       "      <td>Kentucky</td>\n",
       "      <td>6191.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>22000</td>\n",
       "      <td>LA</td>\n",
       "      <td>Louisiana</td>\n",
       "      <td>5632.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>23000</td>\n",
       "      <td>ME</td>\n",
       "      <td>Maine</td>\n",
       "      <td>8466.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>24000</td>\n",
       "      <td>MD</td>\n",
       "      <td>Maryland</td>\n",
       "      <td>7700.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>25000</td>\n",
       "      <td>MA</td>\n",
       "      <td>Massachusetts</td>\n",
       "      <td>8756.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>26000</td>\n",
       "      <td>MI</td>\n",
       "      <td>Michigan</td>\n",
       "      <td>6268.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>27000</td>\n",
       "      <td>MN</td>\n",
       "      <td>Minnesota</td>\n",
       "      <td>9669.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>28000</td>\n",
       "      <td>MS</td>\n",
       "      <td>Mississippi</td>\n",
       "      <td>6539.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>29000</td>\n",
       "      <td>MO</td>\n",
       "      <td>Missouri</td>\n",
       "      <td>8479.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>30000</td>\n",
       "      <td>MT</td>\n",
       "      <td>Montana</td>\n",
       "      <td>6632.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>31000</td>\n",
       "      <td>NE</td>\n",
       "      <td>Nebraska</td>\n",
       "      <td>7172.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>32000</td>\n",
       "      <td>NV</td>\n",
       "      <td>Nevada</td>\n",
       "      <td>4610.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>33000</td>\n",
       "      <td>NH</td>\n",
       "      <td>New Hampshire</td>\n",
       "      <td>7309.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>34000</td>\n",
       "      <td>NJ</td>\n",
       "      <td>New Jersey</td>\n",
       "      <td>7581.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>35000</td>\n",
       "      <td>NM</td>\n",
       "      <td>New Mexico</td>\n",
       "      <td>5458.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>36000</td>\n",
       "      <td>NY</td>\n",
       "      <td>New York</td>\n",
       "      <td>8148.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>37000</td>\n",
       "      <td>NC</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>5778.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>38000</td>\n",
       "      <td>ND</td>\n",
       "      <td>North Dakota</td>\n",
       "      <td>10034.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>39000</td>\n",
       "      <td>OH</td>\n",
       "      <td>Ohio</td>\n",
       "      <td>6910.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>40000</td>\n",
       "      <td>OK</td>\n",
       "      <td>Oklahoma</td>\n",
       "      <td>5854.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>41000</td>\n",
       "      <td>OR</td>\n",
       "      <td>Oregon</td>\n",
       "      <td>7719.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>42000</td>\n",
       "      <td>PA</td>\n",
       "      <td>Pennsylvania</td>\n",
       "      <td>9372.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>44000</td>\n",
       "      <td>RI</td>\n",
       "      <td>Rhode Island</td>\n",
       "      <td>6462.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>45000</td>\n",
       "      <td>SC</td>\n",
       "      <td>South Carolina</td>\n",
       "      <td>4125.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>46000</td>\n",
       "      <td>SD</td>\n",
       "      <td>South Dakota</td>\n",
       "      <td>6704.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>47000</td>\n",
       "      <td>TN</td>\n",
       "      <td>Tennessee</td>\n",
       "      <td>6157.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>48000</td>\n",
       "      <td>TX</td>\n",
       "      <td>Texas</td>\n",
       "      <td>7101.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>49000</td>\n",
       "      <td>UT</td>\n",
       "      <td>Utah</td>\n",
       "      <td>6835.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>50000</td>\n",
       "      <td>VT</td>\n",
       "      <td>Vermont</td>\n",
       "      <td>9125.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>51000</td>\n",
       "      <td>VA</td>\n",
       "      <td>Virginia</td>\n",
       "      <td>6659.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>53000</td>\n",
       "      <td>WA</td>\n",
       "      <td>Washington</td>\n",
       "      <td>6917.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>54000</td>\n",
       "      <td>WV</td>\n",
       "      <td>West Virginia</td>\n",
       "      <td>6703.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>55000</td>\n",
       "      <td>WI</td>\n",
       "      <td>Wisconsin</td>\n",
       "      <td>7362.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>56000</td>\n",
       "      <td>WY</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>7463.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   FIPS_Code Stabr             Area_name  avg_nom_medicaid\n",
       "0      01000    AL               Alabama            4777.0\n",
       "1      02000    AK                Alaska            8640.0\n",
       "2      04000    AZ               Arizona            5616.0\n",
       "3      05000    AR              Arkansas            6480.0\n",
       "4      06000    CA            California            5581.0\n",
       "5      08000    CO              Colorado            6414.0\n",
       "6      09000    CT           Connecticut            8063.0\n",
       "7      10000    DE              Delaware            7701.0\n",
       "8      11000    DC  District of Columbia           10166.0\n",
       "9      12000    FL               Florida            4718.0\n",
       "10     13000    GA               Georgia            4396.0\n",
       "11     15000    HI                Hawaii            5483.0\n",
       "12     16000    ID                 Idaho            6823.0\n",
       "13     17000    IL              Illinois            5491.0\n",
       "14     18000    IN               Indiana            7000.0\n",
       "15     19000    IA                  Iowa            6658.0\n",
       "16     20000    KS                Kansas            7626.0\n",
       "17     21000    KY              Kentucky            6191.0\n",
       "18     22000    LA             Louisiana            5632.0\n",
       "19     23000    ME                 Maine            8466.0\n",
       "20     24000    MD              Maryland            7700.0\n",
       "21     25000    MA         Massachusetts            8756.0\n",
       "22     26000    MI              Michigan            6268.0\n",
       "23     27000    MN             Minnesota            9669.0\n",
       "24     28000    MS           Mississippi            6539.0\n",
       "25     29000    MO              Missouri            8479.0\n",
       "26     30000    MT               Montana            6632.0\n",
       "27     31000    NE              Nebraska            7172.0\n",
       "28     32000    NV                Nevada            4610.0\n",
       "29     33000    NH         New Hampshire            7309.0\n",
       "30     34000    NJ            New Jersey            7581.0\n",
       "31     35000    NM            New Mexico            5458.0\n",
       "32     36000    NY              New York            8148.0\n",
       "33     37000    NC        North Carolina            5778.0\n",
       "34     38000    ND          North Dakota           10034.0\n",
       "35     39000    OH                  Ohio            6910.0\n",
       "36     40000    OK              Oklahoma            5854.0\n",
       "37     41000    OR                Oregon            7719.0\n",
       "38     42000    PA          Pennsylvania            9372.0\n",
       "39     44000    RI          Rhode Island            6462.0\n",
       "40     45000    SC        South Carolina            4125.0\n",
       "41     46000    SD          South Dakota            6704.0\n",
       "42     47000    TN             Tennessee            6157.0\n",
       "43     48000    TX                 Texas            7101.0\n",
       "44     49000    UT                  Utah            6835.0\n",
       "45     50000    VT               Vermont            9125.0\n",
       "46     51000    VA              Virginia            6659.0\n",
       "47     53000    WA            Washington            6917.0\n",
       "48     54000    WV         West Virginia            6703.0\n",
       "49     55000    WI             Wisconsin            7362.0\n",
       "50     56000    WY               Wyoming            7463.0"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_medicaid_state = pd.read_csv(\n",
    "    os.path.join(data_dir, \"kff_avg_medicaid_payout.csv\"),\n",
    "    header=2,\n",
    "    usecols=[\n",
    "        \"Location\",\t\"All Full or Partial Benefit Enrollees\"\n",
    "    ],\n",
    "    skipfooter=24,\n",
    "    engine=\"python\"\n",
    ")\n",
    "# Rename columns\n",
    "df_medicaid_state.rename(\n",
    "    columns={\n",
    "        \"Location\": \"Area_name\",\n",
    "    },\n",
    "    inplace=True\n",
    ")\n",
    "# Delete \"United States\" row\n",
    "df_medicaid_state = df_medicaid_state[\n",
    "    df_medicaid_state[\"Area_name\"] != \"United States\"\n",
    "]\n",
    "# Create new column that is a numerical version of the All Full or Partial\n",
    "# Benefit Enrollees column\n",
    "df_medicaid_state[\"avg_nom_medicaid\"] = df_medicaid_state[\n",
    "    \"All Full or Partial Benefit Enrollees\"\n",
    "].str.replace(\"$\", \"\").str.replace(\",\", \"\").astype(float)\n",
    "# Drop the All Full or Partial Benefit Enrollees column\n",
    "df_medicaid_state = df_medicaid_state.drop(columns=[\n",
    "    \"All Full or Partial Benefit Enrollees\"\n",
    "])\n",
    "# Get DataFrame with the FIPS_Code and two-digit state abbreviation\n",
    "# Read in data from PovertyEstimates.xlsx file into pandas dataframe\n",
    "df_code = pd.read_excel(\n",
    "    os.path.join(data_dir, \"PovertyEstimates.xlsx\"),\n",
    "    sheet_name=\"PovertyEstimates\",\n",
    "    header=4,\n",
    "    usecols=[\n",
    "        \"FIPS_Code\",\n",
    "        \"Stabr\",\n",
    "        \"Area_name\",\n",
    "    ]\n",
    ")\n",
    "# Change FIPS_Code variable to 5-digit string\n",
    "df_code[\"FIPS_Code\"] = df_code[\"FIPS_Code\"].astype(str).str.zfill(5)\n",
    "# Merge in FIPS_Code and two-digit state abbreviation\n",
    "df_medicaid_state = pd.merge(\n",
    "    df_medicaid_state,\n",
    "    df_code[[\"FIPS_Code\", \"Stabr\", \"Area_name\"]],\n",
    "    on=\"Area_name\",\n",
    "    how=\"left\"\n",
    ")\n",
    "# Drop District of Columbia county (FIPS_Code=\"11001\")\n",
    "df_medicaid_state = df_medicaid_state[\n",
    "    df_medicaid_state[\"FIPS_Code\"] != \"11001\"\n",
    "].reset_index(drop=True)\n",
    "# Reorder columns\n",
    "df_medicaid_state = df_medicaid_state[[\n",
    "    \"FIPS_Code\",  \"Stabr\", \"Area_name\", \"avg_nom_medicaid\"\n",
    "]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Merge avg_nom_medicaid into df based on Stabr\n",
    "df = pd.merge(\n",
    "    df,\n",
    "    df_medicaid_state[[\n",
    "        \"Stabr\",\n",
    "        \"avg_nom_medicaid\"\n",
    "    ]],\n",
    "    on=\"Stabr\",\n",
    "    how=\"left\",\n",
    ")\n",
    "\n",
    "# Save the dataframe to a pickle file\n",
    "df.to_pickle(os.path.join(data_dir, \"full_data.pkl\"))\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Create average Medicaid benefit payout map by state for 2019"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# fig1_title = \"Average Medicaid payouts by state, 2019\"\n",
    "fig1_title = \"\"\n",
    "output_file(\n",
    "    \"./images/medicaid_state_2019.html\", title=fig1_title, mode='inline'\n",
    ")\n",
    "output_notebook()\n",
    "\n",
    "# Download U.S. states shape files from US Census Bureau\n",
    "# https://www.census.gov/geographies/mapping-files/time-series/geo/cartographic-boundary.html\n",
    "us_cnty_shapefile_path = (\"https://github.com/TheCGO/MN-AMT/raw/main/data/\" +\n",
    "                     \"cb_2018_us_state_20m/cb_2018_us_state_20m.shp\")\n",
    "gdf = gpd.GeoDataFrame.from_file(us_shapefile_path)\n",
    "gdf_json = gdf.to_json()\n",
    "gjson = json.loads(gdf_json)\n",
    "\n",
    "# Remove Puerto Rico from data\n",
    "del(gjson[\"features\"][7])\n",
    "\n",
    "# Alaska\n",
    "# Fix positive longitudes\n",
    "min_lat_ak = 180  # initial value that will be adjusted\n",
    "min_abs_lon_ak = 180  # initial value that will be adjusted\n",
    "coords_list = gjson[\"features\"][24][\"geometry\"][\"coordinates\"]\n",
    "for ind_isl, island in enumerate(coords_list):\n",
    "    for ind_pnt, point in enumerate(island[0]):\n",
    "        min_lat_ak = np.minimum(min_lat_ak, point[1])\n",
    "        if point[0] > 0:\n",
    "            gjson[\"features\"][24][\"geometry\"][\n",
    "                \"coordinates\"\n",
    "            ][ind_isl][0][ind_pnt][0] = -180 - (180 - point[0])\n",
    "        else:\n",
    "            min_abs_lon_ak = np.minimum(min_abs_lon_ak, -point[0])\n",
    "\n",
    "# Shrink the size of Alaska relative to its southestern most minimum lattitude\n",
    "# and longitude\n",
    "shrink_pct_ak = 0.65\n",
    "coords_list_ak = gjson[\"features\"][24][\"geometry\"][\"coordinates\"]\n",
    "for ind_isl, island in enumerate(coords_list_ak):\n",
    "    for ind_pnt, point in enumerate(island[0]):\n",
    "        gjson[\"features\"][24][\"geometry\"][\n",
    "            \"coordinates\"\n",
    "        ][ind_isl][0][ind_pnt][0] = point[0] - shrink_pct_ak * (point[0] +\n",
    "                                                                min_abs_lon_ak)\n",
    "        gjson[\"features\"][24][\"geometry\"][\n",
    "            \"coordinates\"\n",
    "        ][ind_isl][0][ind_pnt][1] = point[1] - shrink_pct_ak * (point[1] -\n",
    "                                                                min_lat_ak)\n",
    "\n",
    "# Move Alaska closer to the mainland such that the minimum minimum absolute\n",
    "# longitude and lattitude are (-127, 44)\n",
    "min_lat_ak_new = 44\n",
    "min_abs_lon_ak_new = 127\n",
    "for ind_isl, island in enumerate(coords_list):\n",
    "    for ind_pnt, point in enumerate(island[0]):\n",
    "        gjson[\"features\"][24][\"geometry\"][\n",
    "            \"coordinates\"\n",
    "        ][ind_isl][0][ind_pnt][0] = point[0] + (min_abs_lon_ak -\n",
    "                                                min_abs_lon_ak_new)\n",
    "        gjson[\"features\"][24][\"geometry\"][\n",
    "            \"coordinates\"\n",
    "        ][ind_isl][0][ind_pnt][1] = point[1] - (min_lat_ak - min_lat_ak_new)\n",
    "\n",
    "# Hawaii\n",
    "list_ind_hi = 47\n",
    "# Get minimum lattitude and minimum absolute longitude for Hawaii\n",
    "min_lat_hi = 180  # initial value that will be adjusted\n",
    "min_abs_lon_hi = 180  # initial value that will be adjusted\n",
    "coords_list = gjson[\"features\"][list_ind_hi][\"geometry\"][\"coordinates\"]\n",
    "for ind_isl, island in enumerate(coords_list):\n",
    "    for ind_pnt, point in enumerate(island[0]):\n",
    "        min_lat_hi = np.minimum(min_lat_hi, point[1])\n",
    "        min_abs_lon_hi = np.minimum(min_abs_lon_hi, -point[0])\n",
    "# print(\"Minimum lattitude for Hawaii is\", min_lat_hi)\n",
    "# print(\"Minimum absolute longitude for Hawaii is\", min_abs_lon_hi)\n",
    "\n",
    "# Increase the size of Hawaii\n",
    "incr_pct_hi = 0.4\n",
    "coords_list_hi = gjson[\"features\"][list_ind_hi][\"geometry\"][\"coordinates\"]\n",
    "for ind_isl, island in enumerate(coords_list_hi):\n",
    "    for ind_pnt, point in enumerate(island[0]):\n",
    "        gjson[\"features\"][list_ind_hi][\"geometry\"][\n",
    "            \"coordinates\"\n",
    "        ][ind_isl][0][ind_pnt][0] = point[0] + incr_pct_hi * (point[0] +\n",
    "                                                              min_abs_lon_hi)\n",
    "        gjson[\"features\"][list_ind_hi][\"geometry\"][\n",
    "            \"coordinates\"\n",
    "        ][ind_isl][0][ind_pnt][1] = point[1] + incr_pct_hi * (point[1] -\n",
    "                                                              min_lat_hi)\n",
    "\n",
    "# Move Hawaii closer to the mainland such that the minimum minimum absolute\n",
    "# longitude and lattitude are (-125, 27)\n",
    "min_lat_hi_new = 27.5\n",
    "min_abs_lon_hi_new = 124.5\n",
    "for ind_isl, island in enumerate(coords_list):\n",
    "    for ind_pnt, point in enumerate(island[0]):\n",
    "        gjson[\"features\"][list_ind_hi][\"geometry\"][\n",
    "            \"coordinates\"\n",
    "        ][ind_isl][0][ind_pnt][0] = point[0] + (min_abs_lon_hi -\n",
    "                                                min_abs_lon_hi_new)\n",
    "        gjson[\"features\"][list_ind_hi][\"geometry\"][\n",
    "            \"coordinates\"\n",
    "        ][ind_isl][0][ind_pnt][1] = point[1] - (min_lat_hi - min_lat_hi_new)\n",
    "\n",
    "# Add a state box around Delaware abbreviation DE\n",
    "st_list_num = 2\n",
    "de_coord_list = [gjson[\"features\"][st_list_num][\"geometry\"][\"coordinates\"]]\n",
    "new_box_de = [[\n",
    "    [-75.4, 38.8],\n",
    "    [-72.4, 38.3],\n",
    "    [-72.4, 38.9],\n",
    "    [-70.2, 38.9],\n",
    "    [-70.2, 37.7],\n",
    "    [-72.4, 37.7],\n",
    "    [-72.4, 38.3]\n",
    "]]\n",
    "de_coord_list.append(new_box_de)\n",
    "gjson[\"features\"][st_list_num][\"geometry\"][\"coordinates\"] = de_coord_list\n",
    "gjson[\"features\"][st_list_num][\"geometry\"][\"type\"] = \"MultiPolygon\"\n",
    "\n",
    "# Add a state box around Washington, DC (District of Columbia) abbreviation DC\n",
    "st_list_num = 35\n",
    "dc_coord_list = [gjson[\"features\"][st_list_num][\"geometry\"][\"coordinates\"]]\n",
    "new_box_dc = [[\n",
    "    [-77.0, 38.9],\n",
    "    [-73.3, 35.5],\n",
    "    [-73.3, 36.1],\n",
    "    [-71.1, 36.1],\n",
    "    [-71.1, 34.9],\n",
    "    [-73.3, 34.9],\n",
    "    [-73.3, 35.5]\n",
    "]]\n",
    "dc_coord_list.append(new_box_dc)\n",
    "gjson[\"features\"][st_list_num][\"geometry\"][\"coordinates\"] = dc_coord_list\n",
    "gjson[\"features\"][st_list_num][\"geometry\"][\"type\"] = \"MultiPolygon\"\n",
    "\n",
    "# Add a state box around Massachusetts abbreviation MD\n",
    "st_list_num = 0\n",
    "md_coord_list = gjson[\"features\"][st_list_num][\"geometry\"][\"coordinates\"]\n",
    "new_box_md = [[\n",
    "    [-76.8, 39.3],\n",
    "    [-72.7, 37.0],\n",
    "    [-72.7, 37.6],\n",
    "    [-70.5, 37.6],\n",
    "    [-70.5, 36.4],\n",
    "    [-72.7, 36.4],\n",
    "    [-72.7, 37.0]\n",
    "]]\n",
    "md_coord_list.append(new_box_md)\n",
    "gjson[\"features\"][st_list_num][\"geometry\"][\"coordinates\"] = md_coord_list\n",
    "\n",
    "# Add a state box around Massachusetts abbreviation MA\n",
    "st_list_num = 29\n",
    "ma_coord_list = gjson[\"features\"][st_list_num][\"geometry\"][\"coordinates\"]\n",
    "new_box_ma = [[\n",
    "    [-71.7, 42.2],\n",
    "    [-68.5, 42.2],\n",
    "    [-68.5, 42.8],\n",
    "    [-66.3, 42.8],\n",
    "    [-66.3, 41.6],\n",
    "    [-68.5, 41.6],\n",
    "    [-68.5, 42.2]\n",
    "]]\n",
    "ma_coord_list.append(new_box_ma)\n",
    "gjson[\"features\"][st_list_num][\"geometry\"][\"coordinates\"] = ma_coord_list\n",
    "\n",
    "# Add a state box around New Jersey abbreviation NJ\n",
    "st_list_num = 34\n",
    "nj_coord_list = [gjson[\"features\"][st_list_num][\"geometry\"][\"coordinates\"]]\n",
    "new_box_nj = [[\n",
    "    [-74.4, 40.1],\n",
    "    [-72.0, 39.7],\n",
    "    [-72.0, 40.3],\n",
    "    [-69.8, 40.3],\n",
    "    [-69.8, 39.1],\n",
    "    [-72.0, 39.1],\n",
    "    [-72.0, 39.7]\n",
    "]]\n",
    "nj_coord_list.append(new_box_nj)\n",
    "gjson[\"features\"][st_list_num][\"geometry\"][\"coordinates\"] = nj_coord_list\n",
    "gjson[\"features\"][st_list_num][\"geometry\"][\"type\"] = \"MultiPolygon\"\n",
    "\n",
    "# Add a state box around Rhode Island abbreviation RI\n",
    "st_list_num = 50\n",
    "ri_coord_list = gjson[\"features\"][st_list_num][\"geometry\"][\"coordinates\"]\n",
    "new_box_ri = [[\n",
    "    [-71.5, 41.7],\n",
    "    [-69.5, 40.4],\n",
    "    [-69.5, 41.0],\n",
    "    [-67.3, 41.0],\n",
    "    [-67.3, 39.8],\n",
    "    [-69.5, 39.8],\n",
    "    [-69.5, 40.4]\n",
    "]]\n",
    "ri_coord_list.append(new_box_ri)\n",
    "gjson[\"features\"][st_list_num][\"geometry\"][\"coordinates\"] = ri_coord_list\n",
    "\n",
    "# Merge the state tax type data into gjson for each state\n",
    "state_amt_df\n",
    "for ind_st, state in enumerate(gjson[\"features\"]):\n",
    "    st_abbrev = state[\"properties\"][\"STUSPS\"]\n",
    "    state[\"properties\"][\"state_amt\"] = state_amt_df[\n",
    "        state_amt_df[\"Abbrev\"]==st_abbrev\n",
    "    ][\"StateAMT_str_short\"].iloc[0]\n",
    "\n",
    "amt_labels = [\n",
    "    'No state AMT', 'Has state AMT'\n",
    "]\n",
    "amt_colors = [\"white\", \"purple\"]\n",
    "\n",
    "source_shapes = {}\n",
    "for category in amt_labels:\n",
    "    source_shapes[category] = {\"type\": \"FeatureCollection\", \"features\": []}\n",
    "\n",
    "for item in gjson[\"features\"]:\n",
    "    source_shapes[item[\"properties\"][\"state_amt\"]]['features'].append(item)\n",
    "\n",
    "TOOLS = \"pan, box_zoom, wheel_zoom, hover, save, reset, help\"\n",
    "\n",
    "fig1 = figure(\n",
    "    title=fig1_title,\n",
    "    height=500,\n",
    "    width=1050,\n",
    "    tools=TOOLS,\n",
    "    # tooltips=[\n",
    "    #     (\"State\", @state_names), (\"Tax type\", @tax_type)\n",
    "    # ]\n",
    "    # match_aspect = True,\n",
    "    min_border = 0,\n",
    "    x_axis_location = None, y_axis_location = None,\n",
    "    toolbar_location=\"right\"\n",
    ")\n",
    "fig1.toolbar.logo = None\n",
    "fig1.grid.grid_line_color = None\n",
    "\n",
    "cmap = CategoricalColorMapper(\n",
    "    palette=amt_colors, factors=amt_labels\n",
    ")\n",
    "for category in amt_labels:\n",
    "    source_shape_1 = GeoJSONDataSource(\n",
    "        geojson = json.dumps(source_shapes[category])\n",
    "    )\n",
    "    fig1.patches(\n",
    "        'xs', 'ys', source=source_shape_1, fill_alpha=0.7,\n",
    "        fill_color = {'field': 'state_amt', 'transform': cmap},\n",
    "        line_color ='black', line_width=1.0, line_alpha=0.3,\n",
    "        hover_line_color=\"black\", hover_line_width=3.0, legend_label=category)\n",
    "\n",
    "    hover = fig1.select_one(HoverTool)\n",
    "    hover.point_policy = \"follow_mouse\"\n",
    "    hover.tooltips = [\n",
    "        (\"State\", \"@NAME\"),\n",
    "        (\"State AMT\", \"@state_amt\")\n",
    "    ]\n",
    "\n",
    "# Add 2-letter state abbreviation labels. See Bokeh documentation for labels at\n",
    "# https://docs.bokeh.org/en/latest/docs/user_guide/basic/annotations.html.\n",
    "label_lon_lat =[\n",
    "    [\"AL\",  -87.40, 32.10],\n",
    "    [\"AK\", -135.00, 48.30],\n",
    "    [\"AZ\", -112.40, 34.00],\n",
    "    [\"AR\",  -93.20, 34.30],\n",
    "    [\"CA\", -121.00, 37.00],\n",
    "    [\"CO\", -106.50, 38.50],\n",
    "    [\"CT\",  -73.40, 41.15],\n",
    "    [\"DE\",  -72.10, 37.80],\n",
    "    [\"DC\",  -73.00, 35.00],\n",
    "    [\"FL\",  -82.40, 28.00],\n",
    "    [\"GA\",  -84.20, 32.20],\n",
    "    [\"HI\", -125.00, 29.00],\n",
    "    [\"ID\", -115.00, 43.00],\n",
    "    [\"IL\",  -89.60, 39.50],\n",
    "    [\"IN\",  -86.90, 39.60],\n",
    "    [\"IA\",  -94.40, 41.60],\n",
    "    [\"KS\",  -99.50, 38.00],\n",
    "    [\"KY\",  -86.00, 37.00],\n",
    "    [\"LA\",  -93.00, 30.20],\n",
    "    [\"ME\",  -70.00, 44.50],\n",
    "    [\"MD\",  -72.40, 36.50],\n",
    "    [\"MA\",  -68.20, 41.80],\n",
    "    [\"MI\",  -85.40, 42.70],\n",
    "    [\"MN\",  -95.30, 45.50],\n",
    "    [\"MS\",  -90.60, 32.10],\n",
    "    [\"MO\",  -93.50, 38.00],\n",
    "    [\"MT\", -110.50, 46.50],\n",
    "    [\"NE\", -100.50, 41.00],\n",
    "    [\"NV\", -118.00, 39.00],\n",
    "    [\"NH\",  -72.45, 42.70],\n",
    "    [\"NM\", -107.00, 34.00],\n",
    "    [\"NJ\",  -71.60, 39.20],\n",
    "    [\"NY\",  -76.00, 42.50],\n",
    "    [\"NC\",  -79.20, 35.00],\n",
    "    [\"ND\", -101.50, 46.80],\n",
    "    [\"OH\",  -83.80, 39.90],\n",
    "    [\"OK\",  -98.00, 35.00],\n",
    "    [\"OR\", -121.50, 43.50],\n",
    "    [\"PA\",  -78.50, 40.40],\n",
    "    [\"RI\",  -69.00, 39.90],\n",
    "    [\"SC\",  -81.50, 33.30],\n",
    "    [\"SD\", -101.00, 44.00],\n",
    "    [\"TN\",  -87.10, 35.40],\n",
    "    [\"TX\", -100.00, 31.00],\n",
    "    [\"UT\", -112.50, 39.00],\n",
    "    [\"VT\",  -73.20, 44.10],\n",
    "    [\"VA\",  -79.00, 37.10],\n",
    "    [\"WA\", -121.00, 47.00],\n",
    "    [\"WV\",  -81.90, 38.00],\n",
    "    [\"WI\",  -90.40, 44.00],\n",
    "    [\"WY\", -108.50, 42.50]\n",
    "]\n",
    "label_abbrev = [state[0] for state in label_lon_lat]\n",
    "label_lon = [state[1] for state in label_lon_lat]\n",
    "label_lat = [state[2] for state in label_lon_lat]\n",
    "state_cds = ColumnDataSource(data=dict(\n",
    "    lon=label_lon,\n",
    "    lat=label_lat,\n",
    "    abbrev=label_abbrev\n",
    "))\n",
    "\n",
    "state_labels = LabelSet(\n",
    "    x='lon', y='lat', text='abbrev', text_font_size=\"9pt\",\n",
    "    text_font_style=\"bold\", x_offset=0, y_offset=0, source=state_cds\n",
    ")\n",
    "\n",
    "fig1.add_layout(state_labels)\n",
    "\n",
    "# Legend properties\n",
    "fig1.legend.click_policy = 'mute'\n",
    "fig1.legend.location = \"center_left\"\n",
    "\n",
    "fig1.add_layout(\n",
    "    Title(\n",
    "        text=\"  Source: Richard W. Evans (@RickEcon), updated October 16, 2023.\",\n",
    "        align=\"left\",\n",
    "        text_font_size=\"3mm\",\n",
    "        text_font_style=\"italic\",\n",
    "    ),\n",
    "    \"below\"\n",
    ")\n",
    "show(fig1)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "usempl-plots-dev",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}