notebooks/cleaning_pt2.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Projecting Food Insecurity Rates in the US by County\n",
    "## Secondary Data Cleaning\n",
    "The following process imports a cleaned dataset produced from cleaning_pt1.ipynb. This notebook is used for additional cleaning, such as adjusting datatypes, imputing missing data, and removing/averaging duplicate observations.\n",
    "### Flatiron School Data Science Capstone<br>By Khyatee Desai"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os\n",
    "pd.set_option('display.max_columns', None)\n",
    "import warnings\n",
    "import pickle\n",
    "warnings.filterwarnings('ignore')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Import dataset produced from first cleaning notebook\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open('../pickled/partially_cleaned_data.pickle', \"rb\") as input_file:\n",
    "    df = pickle.load(input_file) \n",
    "    \n",
    "#  drop un needed features\n",
    "df.drop(['FIPS_state', 'FIPS_county'], axis=1, inplace=True)\n",
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Unemployment Dataset: Fix Datatypes\n",
    "Unemployment data was saved as a string due to the presence of special characters \"-\". The following cell drops these characters, converts the column to a float datatype, then adds it back into the main df\n",
    "### remove special characters '-' and change numeric datatypes to floats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Total Workforce Column\n",
    "df_workf = df[['FIPS','Year','Total_workforce']]\n",
    "df_workf = df_workf.dropna()\n",
    "df_workf.drop(df_workf[df_workf['Total_workforce'].values == '–'].index,inplace=True)\n",
    "df_workf = df_workf['Total_workforce'].astype(float)\n",
    "\n",
    "# Employed column\n",
    "df_employed = df[['FIPS','Year','Employed']]\n",
    "df_employed = df_employed.dropna()\n",
    "df_employed.drop(df_employed[df_employed['Employed'].values == '–'].index,inplace=True)\n",
    "df_employed = df_employed['Employed'].astype(float)\n",
    "\n",
    "# Unemployed column\n",
    "df_unemployed = df[['FIPS','Year','Unemployed']]\n",
    "df_unemployed = df_unemployed.dropna()\n",
    "df_unemployed.drop(df_unemployed[df_unemployed['Unemployed'].values == '–'].index,inplace=True)\n",
    "df_unemployed = df_unemployed['Unemployed'].astype(float)\n",
    "\n",
    "# Unemployment Rate column\n",
    "df_unemploy_rate = df[['FIPS','Year','Unemployment_rate']]\n",
    "df_unemploy_rate = df_unemploy_rate.dropna()\n",
    "df_unemploy_rate.drop(df_unemploy_rate[df_unemploy_rate['Unemployment_rate'].values == '–'].index,inplace=True)\n",
    "df_unemploy_rate = df_unemploy_rate['Unemployment_rate'].astype(float)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Drop old version from main df, and add float versions back in"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.drop(['Total_workforce','Employed','Unemployed','Unemployment_rate'],axis=1)\n",
    "df = pd.concat([df,df_workf,df_employed,df_unemployed,df_unemploy_rate],axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "FIPS                    object\n",
       "Rent                   float64\n",
       "Year                    object\n",
       "coc_number              object\n",
       "Houseless_rate         float64\n",
       "Sheltered_rate         float64\n",
       "Unsheltered_rate       float64\n",
       "hh_med_income          float64\n",
       "pop_disabled           float64\n",
       "pop_hs_grad            float64\n",
       "pop_bachelors          float64\n",
       "pop_grad_degree        float64\n",
       "pop_priv_health        float64\n",
       "pop_public_health      float64\n",
       "pop_no_health          float64\n",
       "pop_total              float64\n",
       "percent_hh_poverty     float64\n",
       "hh_avg_size            float64\n",
       "pop_65+                float64\n",
       "hh_no_vehicle          float64\n",
       "num_hh                 float64\n",
       "pop_non_citizen        float64\n",
       "hh_SNAP                float64\n",
       "State                   object\n",
       "County                  object\n",
       "TOT_POP                float64\n",
       "TOT_MALE               float64\n",
       "TOT_FEMALE             float64\n",
       "TOT_WHITE              float64\n",
       "TOT_BLACK              float64\n",
       "TOT_NATIVE             float64\n",
       "TOT_ASIAN              float64\n",
       "TOT_PACIFIC            float64\n",
       "TOT_LATINX             float64\n",
       "State/County            object\n",
       "FI Rate                float64\n",
       "Low Threshold Type      object\n",
       "High Threshold Type     object\n",
       "Cost Per Meal          float64\n",
       "Num_wholesale          float64\n",
       "Num_restaraunts        float64\n",
       "Num_grocery            float64\n",
       "Total_workforce        float64\n",
       "Employed               float64\n",
       "Unemployed             float64\n",
       "Unemployment_rate      float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Feeding America Dataset: Impute Missing Values\n",
    "The Feeding America datasets on food insecurity only contain data at the State level for the years 2011, 2012, and 2013, however County-level data is needed for mapping, and to have enough observations for modeling. The following process uses data from 2010 and 2014 to calculate average yearly change per county, and then fills in the missing values.\n",
    "### Isolate rows from main df with missing values for Feeding America data, as well as 2010 and 2014, which will be used to calculate average yearly change"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create separate dataframe for each year, including features that need to be added\n",
    "fi_10 = df[df.Year=='2010'][['FIPS','FI Rate','Cost Per Meal']]\n",
    "fi_11 = df[df.Year=='2011'][['FIPS','FI Rate','Cost Per Meal']]\n",
    "fi_12 = df[df.Year=='2012'][['FIPS','FI Rate','Cost Per Meal']]\n",
    "fi_13 = df[df.Year=='2013'][['FIPS','FI Rate','Cost Per Meal']]\n",
    "fi_14 = df[df.Year=='2014'][['FIPS','FI Rate','Cost Per Meal']]\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Merge together 2010 and 2014 dataframes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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</th>\n",
       "      <th>FI Rate 10</th>\n",
       "      <th>Meal cost 10</th>\n",
       "      <th>FI Rate 14</th>\n",
       "      <th>Meal cost 14</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>02020</td>\n",
       "      <td>0.123</td>\n",
       "      <td>2.64</td>\n",
       "      <td>0.127</td>\n",
       "      <td>3.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>02020</td>\n",
       "      <td>0.123</td>\n",
       "      <td>2.64</td>\n",
       "      <td>0.127</td>\n",
       "      <td>3.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>02020</td>\n",
       "      <td>0.123</td>\n",
       "      <td>2.64</td>\n",
       "      <td>0.127</td>\n",
       "      <td>3.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>02013</td>\n",
       "      <td>0.145</td>\n",
       "      <td>2.69</td>\n",
       "      <td>0.143</td>\n",
       "      <td>4.13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>02013</td>\n",
       "      <td>0.145</td>\n",
       "      <td>2.69</td>\n",
       "      <td>0.143</td>\n",
       "      <td>4.13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10399</th>\n",
       "      <td>25999</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10400</th>\n",
       "      <td>26999</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10401</th>\n",
       "      <td>02999</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10402</th>\n",
       "      <td>04999</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10403</th>\n",
       "      <td>16999</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10404 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        FIPS  FI Rate 10  Meal cost 10  FI Rate 14  Meal cost 14\n",
       "0      02020       0.123          2.64       0.127          3.30\n",
       "1      02020       0.123          2.64       0.127          3.30\n",
       "2      02020       0.123          2.64       0.127          3.30\n",
       "3      02013       0.145          2.69       0.143          4.13\n",
       "4      02013       0.145          2.69       0.143          4.13\n",
       "...      ...         ...           ...         ...           ...\n",
       "10399  25999         NaN           NaN         NaN           NaN\n",
       "10400  26999         NaN           NaN         NaN           NaN\n",
       "10401  02999         NaN           NaN         NaN           NaN\n",
       "10402  04999         NaN           NaN         NaN           NaN\n",
       "10403  16999         NaN           NaN         NaN           NaN\n",
       "\n",
       "[10404 rows x 5 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# merge and rename columns for interpretability\n",
    "fi_10_and_14 = fi_10.merge(fi_14, on='FIPS',how='inner')\n",
    "fi_10_and_14.rename(columns={'FI Rate_x':'FI Rate 10', 'Cost Per Meal_x': 'Meal cost 10', \n",
    "                             'FI Rate_y':'FI Rate 14', 'Cost Per Meal_y': 'Meal cost 14'}, inplace=True)\n",
    "fi_10_and_14"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Impute missing values for each year\n",
    "This cell finds the difference between 2014 and 2010, and add 1/4 of the difference consecutively for each year with missing data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "# calculate difference between 2014 and 2010 for each feature of interest\n",
    "fi_10_and_14['Rate_diff'] = fi_10_and_14['FI Rate 14'] - fi_10_and_14['FI Rate 10']\n",
    "fi_10_and_14['Meal_cost_dif'] = fi_10_and_14['Meal cost 14'] - fi_10_and_14['Meal cost 10']\n",
    "\n",
    "# fill in the missing values for each feature and each of the three years\n",
    "fi_10_and_14['FI Rate 11'] = fi_10_and_14['FI Rate 10'] + fi_10_and_14['Rate_diff']/4\n",
    "fi_10_and_14['FI Rate 12'] = fi_10_and_14['FI Rate 11'] + fi_10_and_14['Rate_diff']/4\n",
    "fi_10_and_14['FI Rate 13'] = fi_10_and_14['FI Rate 12'] + fi_10_and_14['Rate_diff']/4\n",
    "\n",
    "fi_10_and_14['Meal cost 11'] = fi_10_and_14['Meal cost 10'] + fi_10_and_14['Meal_cost_dif']/4\n",
    "fi_10_and_14['Meal cost 12'] = fi_10_and_14['Meal cost 11'] + fi_10_and_14['Meal_cost_dif']/4\n",
    "fi_10_and_14['Meal cost 13'] = fi_10_and_14['Meal cost 12'] + fi_10_and_14['Meal_cost_dif']/4\n",
    "\n",
    "fi_10_and_14.drop_duplicates(inplace=True)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create new df for each year with newly created values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_11 = df[df.Year=='2011']\n",
    "df_11 = df_11.merge(fi_10_and_14[['FIPS','FI Rate 11','Meal cost 11']], on='FIPS')\n",
    "df_11.drop(['FI Rate','Cost Per Meal'],axis=1,inplace=True)\n",
    "df_11.rename(columns={'FI Rate 11':'FI Rate','Meal cost 11':'Cost Per Meal'},inplace=True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_12 = df[df.Year=='2012']\n",
    "df_12 = df_12.merge(fi_10_and_14[['FIPS','FI Rate 12','Meal cost 12']], on='FIPS')\n",
    "df_12.drop(['FI Rate','Cost Per Meal'],axis=1,inplace=True)\n",
    "df_12.rename(columns={'FI Rate 12':'FI Rate','Meal cost 12':'Cost Per Meal'},inplace=True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_13 = df[df.Year=='2013']\n",
    "df_13 = df_13.merge(fi_10_and_14[['FIPS','FI Rate 13','Meal cost 13']], on='FIPS')\n",
    "df_13.drop(['FI Rate','Cost Per Meal'],axis=1,inplace=True)\n",
    "df_13.rename(columns={'FI Rate 13':'FI Rate','Meal cost 13':'Cost Per Meal'},inplace=True)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Concatenate new dfs back with main df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "FIPS                      0\n",
       "Rent                   3375\n",
       "Year                      0\n",
       "coc_number              202\n",
       "Houseless_rate          202\n",
       "Sheltered_rate          202\n",
       "Unsheltered_rate        202\n",
       "hh_med_income            95\n",
       "pop_disabled            173\n",
       "pop_hs_grad             173\n",
       "pop_bachelors           173\n",
       "pop_grad_degree         173\n",
       "pop_priv_health          95\n",
       "pop_public_health        95\n",
       "pop_no_health            95\n",
       "pop_total               173\n",
       "percent_hh_poverty       95\n",
       "hh_avg_size             173\n",
       "pop_65+                 173\n",
       "hh_no_vehicle            95\n",
       "num_hh                  173\n",
       "pop_non_citizen         173\n",
       "hh_SNAP                  95\n",
       "State                   173\n",
       "County                  173\n",
       "TOT_POP                 173\n",
       "TOT_MALE                173\n",
       "TOT_FEMALE              173\n",
       "TOT_WHITE               173\n",
       "TOT_BLACK               173\n",
       "TOT_NATIVE              173\n",
       "TOT_ASIAN               173\n",
       "TOT_PACIFIC             173\n",
       "TOT_LATINX              173\n",
       "State/County             96\n",
       "Low Threshold Type     3375\n",
       "High Threshold Type    3375\n",
       "Num_wholesale          1825\n",
       "Num_restaraunts        1825\n",
       "Num_grocery            1825\n",
       "Total_workforce          96\n",
       "Employed                 96\n",
       "Unemployed               96\n",
       "Unemployment_rate        96\n",
       "FI Rate                 173\n",
       "Cost Per Meal           173\n",
       "dtype: int64"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_11.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>FIPS</th>\n",
       "      <th>Rent</th>\n",
       "      <th>Year</th>\n",
       "      <th>coc_number</th>\n",
       "      <th>Houseless_rate</th>\n",
       "      <th>Sheltered_rate</th>\n",
       "      <th>Unsheltered_rate</th>\n",
       "      <th>hh_med_income</th>\n",
       "      <th>pop_disabled</th>\n",
       "      <th>pop_hs_grad</th>\n",
       "      <th>pop_bachelors</th>\n",
       "      <th>pop_grad_degree</th>\n",
       "      <th>pop_priv_health</th>\n",
       "      <th>pop_public_health</th>\n",
       "      <th>pop_no_health</th>\n",
       "      <th>pop_total</th>\n",
       "      <th>percent_hh_poverty</th>\n",
       "      <th>hh_avg_size</th>\n",
       "      <th>pop_65+</th>\n",
       "      <th>hh_no_vehicle</th>\n",
       "      <th>num_hh</th>\n",
       "      <th>pop_non_citizen</th>\n",
       "      <th>hh_SNAP</th>\n",
       "      <th>State</th>\n",
       "      <th>County</th>\n",
       "      <th>TOT_POP</th>\n",
       "      <th>TOT_MALE</th>\n",
       "      <th>TOT_FEMALE</th>\n",
       "      <th>TOT_WHITE</th>\n",
       "      <th>TOT_BLACK</th>\n",
       "      <th>TOT_NATIVE</th>\n",
       "      <th>TOT_ASIAN</th>\n",
       "      <th>TOT_PACIFIC</th>\n",
       "      <th>TOT_LATINX</th>\n",
       "      <th>State/County</th>\n",
       "      <th>FI Rate</th>\n",
       "      <th>Low Threshold Type</th>\n",
       "      <th>High Threshold Type</th>\n",
       "      <th>Cost Per Meal</th>\n",
       "      <th>Num_wholesale</th>\n",
       "      <th>Num_restaraunts</th>\n",
       "      <th>Num_grocery</th>\n",
       "      <th>Total_workforce</th>\n",
       "      <th>Employed</th>\n",
       "      <th>Unemployed</th>\n",
       "      <th>Unemployment_rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>01073</td>\n",
       "      <td>1019.995960</td>\n",
       "      <td>2014</td>\n",
       "      <td>AL-500</td>\n",
       "      <td>0.001396</td>\n",
       "      <td>0.000961</td>\n",
       "      <td>0.000435</td>\n",
       "      <td>45239.0</td>\n",
       "      <td>94584.0</td>\n",
       "      <td>117854.0</td>\n",
       "      <td>81626.0</td>\n",
       "      <td>52774.0</td>\n",
       "      <td>431638.0</td>\n",
       "      <td>211570.0</td>\n",
       "      <td>81336.0</td>\n",
       "      <td>658834.0</td>\n",
       "      <td>14.8</td>\n",
       "      <td>2.48</td>\n",
       "      <td>87036.0</td>\n",
       "      <td>93630.0</td>\n",
       "      <td>259397.0</td>\n",
       "      <td>17519.0</td>\n",
       "      <td>39967.0</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Jefferson County</td>\n",
       "      <td>659972.0</td>\n",
       "      <td>312491.0</td>\n",
       "      <td>347481.0</td>\n",
       "      <td>355635.0</td>\n",
       "      <td>284082.0</td>\n",
       "      <td>2029.0</td>\n",
       "      <td>10378.0</td>\n",
       "      <td>347.0</td>\n",
       "      <td>24099.0</td>\n",
       "      <td>Jefferson County, AL</td>\n",
       "      <td>0.197</td>\n",
       "      <td>SNAP</td>\n",
       "      <td>Other Nutrition Program</td>\n",
       "      <td>2.93</td>\n",
       "      <td>483.0</td>\n",
       "      <td>2693.0</td>\n",
       "      <td>400.0</td>\n",
       "      <td>312131.0</td>\n",
       "      <td>292505.0</td>\n",
       "      <td>19626.0</td>\n",
       "      <td>6.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>01117</td>\n",
       "      <td>1229.755051</td>\n",
       "      <td>2014</td>\n",
       "      <td>AL-500</td>\n",
       "      <td>0.001396</td>\n",
       "      <td>0.000961</td>\n",
       "      <td>0.000435</td>\n",
       "      <td>69723.0</td>\n",
       "      <td>22792.0</td>\n",
       "      <td>28911.0</td>\n",
       "      <td>35773.0</td>\n",
       "      <td>18511.0</td>\n",
       "      <td>159655.0</td>\n",
       "      <td>42429.0</td>\n",
       "      <td>19175.0</td>\n",
       "      <td>201168.0</td>\n",
       "      <td>6.2</td>\n",
       "      <td>2.65</td>\n",
       "      <td>23404.0</td>\n",
       "      <td>19762.0</td>\n",
       "      <td>74790.0</td>\n",
       "      <td>7624.0</td>\n",
       "      <td>4706.0</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Shelby County</td>\n",
       "      <td>206280.0</td>\n",
       "      <td>100304.0</td>\n",
       "      <td>105976.0</td>\n",
       "      <td>174094.0</td>\n",
       "      <td>24247.0</td>\n",
       "      <td>805.0</td>\n",
       "      <td>4403.0</td>\n",
       "      <td>101.0</td>\n",
       "      <td>11872.0</td>\n",
       "      <td>Shelby County, AL</td>\n",
       "      <td>0.105</td>\n",
       "      <td>SNAP</td>\n",
       "      <td>Other Nutrition Program</td>\n",
       "      <td>3.37</td>\n",
       "      <td>1.0</td>\n",
       "      <td>743.0</td>\n",
       "      <td>2706.0</td>\n",
       "      <td>107208.0</td>\n",
       "      <td>102400.0</td>\n",
       "      <td>4808.0</td>\n",
       "      <td>4.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>04003</td>\n",
       "      <td>1051.250000</td>\n",
       "      <td>2014</td>\n",
       "      <td>AZ-500</td>\n",
       "      <td>0.001469</td>\n",
       "      <td>0.000826</td>\n",
       "      <td>0.000643</td>\n",
       "      <td>45974.0</td>\n",
       "      <td>20341.0</td>\n",
       "      <td>21109.0</td>\n",
       "      <td>12968.0</td>\n",
       "      <td>7566.0</td>\n",
       "      <td>76099.0</td>\n",
       "      <td>50498.0</td>\n",
       "      <td>14868.0</td>\n",
       "      <td>130807.0</td>\n",
       "      <td>13.1</td>\n",
       "      <td>2.47</td>\n",
       "      <td>23593.0</td>\n",
       "      <td>16328.0</td>\n",
       "      <td>48846.0</td>\n",
       "      <td>7947.0</td>\n",
       "      <td>7812.0</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>Cochise County</td>\n",
       "      <td>127314.0</td>\n",
       "      <td>64661.0</td>\n",
       "      <td>62653.0</td>\n",
       "      <td>112238.0</td>\n",
       "      <td>5737.0</td>\n",
       "      <td>2165.0</td>\n",
       "      <td>2757.0</td>\n",
       "      <td>510.0</td>\n",
       "      <td>44374.0</td>\n",
       "      <td>Cochise County, AZ</td>\n",
       "      <td>0.161</td>\n",
       "      <td>SNAP, Other Nutrition Programs</td>\n",
       "      <td>SNAP, Other Nutrition Programs</td>\n",
       "      <td>2.81</td>\n",
       "      <td>800.0</td>\n",
       "      <td>72.0</td>\n",
       "      <td>340.0</td>\n",
       "      <td>50969.0</td>\n",
       "      <td>46682.0</td>\n",
       "      <td>4287.0</td>\n",
       "      <td>8.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>04013</td>\n",
       "      <td>1095.670228</td>\n",
       "      <td>2014</td>\n",
       "      <td>AZ-502</td>\n",
       "      <td>0.001318</td>\n",
       "      <td>0.001084</td>\n",
       "      <td>0.000235</td>\n",
       "      <td>53689.0</td>\n",
       "      <td>399455.0</td>\n",
       "      <td>593094.0</td>\n",
       "      <td>490927.0</td>\n",
       "      <td>273108.0</td>\n",
       "      <td>2444443.0</td>\n",
       "      <td>1204681.0</td>\n",
       "      <td>646167.0</td>\n",
       "      <td>3947382.0</td>\n",
       "      <td>12.7</td>\n",
       "      <td>2.74</td>\n",
       "      <td>507428.0</td>\n",
       "      <td>546028.0</td>\n",
       "      <td>1424244.0</td>\n",
       "      <td>373532.0</td>\n",
       "      <td>171581.0</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>Maricopa County</td>\n",
       "      <td>4093648.0</td>\n",
       "      <td>2024659.0</td>\n",
       "      <td>2068989.0</td>\n",
       "      <td>3449404.0</td>\n",
       "      <td>235660.0</td>\n",
       "      <td>112383.0</td>\n",
       "      <td>172425.0</td>\n",
       "      <td>11190.0</td>\n",
       "      <td>1239835.0</td>\n",
       "      <td>Maricopa County, AZ</td>\n",
       "      <td>0.158</td>\n",
       "      <td>SNAP, Other Nutrition Programs</td>\n",
       "      <td>SNAP, Other Nutrition Programs</td>\n",
       "      <td>2.90</td>\n",
       "      <td>2389.0</td>\n",
       "      <td>16857.0</td>\n",
       "      <td>6320.0</td>\n",
       "      <td>1961997.0</td>\n",
       "      <td>1848119.0</td>\n",
       "      <td>113878.0</td>\n",
       "      <td>5.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>04019</td>\n",
       "      <td>928.546429</td>\n",
       "      <td>2014</td>\n",
       "      <td>AZ-501</td>\n",
       "      <td>0.002170</td>\n",
       "      <td>0.001705</td>\n",
       "      <td>0.000465</td>\n",
       "      <td>46233.0</td>\n",
       "      <td>133694.0</td>\n",
       "      <td>149147.0</td>\n",
       "      <td>115392.0</td>\n",
       "      <td>81406.0</td>\n",
       "      <td>592298.0</td>\n",
       "      <td>364938.0</td>\n",
       "      <td>141211.0</td>\n",
       "      <td>993144.0</td>\n",
       "      <td>13.2</td>\n",
       "      <td>2.50</td>\n",
       "      <td>162075.0</td>\n",
       "      <td>149710.0</td>\n",
       "      <td>386155.0</td>\n",
       "      <td>69636.0</td>\n",
       "      <td>57099.0</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>Pima County</td>\n",
       "      <td>1004229.0</td>\n",
       "      <td>494684.0</td>\n",
       "      <td>509545.0</td>\n",
       "      <td>858334.0</td>\n",
       "      <td>41043.0</td>\n",
       "      <td>42683.0</td>\n",
       "      <td>31905.0</td>\n",
       "      <td>2266.0</td>\n",
       "      <td>363063.0</td>\n",
       "      <td>Pima County, AZ</td>\n",
       "      <td>0.154</td>\n",
       "      <td>SNAP, Other Nutrition Programs</td>\n",
       "      <td>SNAP, Other Nutrition Programs</td>\n",
       "      <td>2.85</td>\n",
       "      <td>3591.0</td>\n",
       "      <td>838.0</td>\n",
       "      <td>1604.0</td>\n",
       "      <td>463126.0</td>\n",
       "      <td>435183.0</td>\n",
       "      <td>27943.0</td>\n",
       "      <td>6.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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3371</th>\n",
       "      <td>21999</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2013</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3372</th>\n",
       "      <td>22999</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2013</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>131.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3373</th>\n",
       "      <td>25999</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2013</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>104.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3374</th>\n",
       "      <td>16999</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2013</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>51.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3375</th>\n",
       "      <td>24999</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2013</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>146.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>85821 rows × 46 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       FIPS         Rent  Year coc_number  Houseless_rate  Sheltered_rate  \\\n",
       "0     01073  1019.995960  2014     AL-500        0.001396        0.000961   \n",
       "1     01117  1229.755051  2014     AL-500        0.001396        0.000961   \n",
       "2     04003  1051.250000  2014     AZ-500        0.001469        0.000826   \n",
       "3     04013  1095.670228  2014     AZ-502        0.001318        0.001084   \n",
       "4     04019   928.546429  2014     AZ-501        0.002170        0.001705   \n",
       "...     ...          ...   ...        ...             ...             ...   \n",
       "3371  21999          NaN  2013        NaN             NaN             NaN   \n",
       "3372  22999          NaN  2013        NaN             NaN             NaN   \n",
       "3373  25999          NaN  2013        NaN             NaN             NaN   \n",
       "3374  16999          NaN  2013        NaN             NaN             NaN   \n",
       "3375  24999          NaN  2013        NaN             NaN             NaN   \n",
       "\n",
       "      Unsheltered_rate  hh_med_income  pop_disabled  pop_hs_grad  \\\n",
       "0             0.000435        45239.0       94584.0     117854.0   \n",
       "1             0.000435        69723.0       22792.0      28911.0   \n",
       "2             0.000643        45974.0       20341.0      21109.0   \n",
       "3             0.000235        53689.0      399455.0     593094.0   \n",
       "4             0.000465        46233.0      133694.0     149147.0   \n",
       "...                ...            ...           ...          ...   \n",
       "3371               NaN            NaN           NaN          NaN   \n",
       "3372               NaN            NaN           NaN          NaN   \n",
       "3373               NaN            NaN           NaN          NaN   \n",
       "3374               NaN            NaN           NaN          NaN   \n",
       "3375               NaN            NaN           NaN          NaN   \n",
       "\n",
       "      pop_bachelors  pop_grad_degree  pop_priv_health  pop_public_health  \\\n",
       "0           81626.0          52774.0         431638.0           211570.0   \n",
       "1           35773.0          18511.0         159655.0            42429.0   \n",
       "2           12968.0           7566.0          76099.0            50498.0   \n",
       "3          490927.0         273108.0        2444443.0          1204681.0   \n",
       "4          115392.0          81406.0         592298.0           364938.0   \n",
       "...             ...              ...              ...                ...   \n",
       "3371            NaN              NaN              NaN                NaN   \n",
       "3372            NaN              NaN              NaN                NaN   \n",
       "3373            NaN              NaN              NaN                NaN   \n",
       "3374            NaN              NaN              NaN                NaN   \n",
       "3375            NaN              NaN              NaN                NaN   \n",
       "\n",
       "      pop_no_health  pop_total  percent_hh_poverty  hh_avg_size   pop_65+  \\\n",
       "0           81336.0   658834.0                14.8         2.48   87036.0   \n",
       "1           19175.0   201168.0                 6.2         2.65   23404.0   \n",
       "2           14868.0   130807.0                13.1         2.47   23593.0   \n",
       "3          646167.0  3947382.0                12.7         2.74  507428.0   \n",
       "4          141211.0   993144.0                13.2         2.50  162075.0   \n",
       "...             ...        ...                 ...          ...       ...   \n",
       "3371            NaN        NaN                 NaN          NaN       NaN   \n",
       "3372            NaN        NaN                 NaN          NaN       NaN   \n",
       "3373            NaN        NaN                 NaN          NaN       NaN   \n",
       "3374            NaN        NaN                 NaN          NaN       NaN   \n",
       "3375            NaN        NaN                 NaN          NaN       NaN   \n",
       "\n",
       "      hh_no_vehicle     num_hh  pop_non_citizen   hh_SNAP    State  \\\n",
       "0           93630.0   259397.0          17519.0   39967.0  Alabama   \n",
       "1           19762.0    74790.0           7624.0    4706.0  Alabama   \n",
       "2           16328.0    48846.0           7947.0    7812.0  Arizona   \n",
       "3          546028.0  1424244.0         373532.0  171581.0  Arizona   \n",
       "4          149710.0   386155.0          69636.0   57099.0  Arizona   \n",
       "...             ...        ...              ...       ...      ...   \n",
       "3371            NaN        NaN              NaN       NaN      NaN   \n",
       "3372            NaN        NaN              NaN       NaN      NaN   \n",
       "3373            NaN        NaN              NaN       NaN      NaN   \n",
       "3374            NaN        NaN              NaN       NaN      NaN   \n",
       "3375            NaN        NaN              NaN       NaN      NaN   \n",
       "\n",
       "                County    TOT_POP   TOT_MALE  TOT_FEMALE  TOT_WHITE  \\\n",
       "0     Jefferson County   659972.0   312491.0    347481.0   355635.0   \n",
       "1        Shelby County   206280.0   100304.0    105976.0   174094.0   \n",
       "2       Cochise County   127314.0    64661.0     62653.0   112238.0   \n",
       "3      Maricopa County  4093648.0  2024659.0   2068989.0  3449404.0   \n",
       "4          Pima County  1004229.0   494684.0    509545.0   858334.0   \n",
       "...                ...        ...        ...         ...        ...   \n",
       "3371               NaN        NaN        NaN         NaN        NaN   \n",
       "3372               NaN        NaN        NaN         NaN        NaN   \n",
       "3373               NaN        NaN        NaN         NaN        NaN   \n",
       "3374               NaN        NaN        NaN         NaN        NaN   \n",
       "3375               NaN        NaN        NaN         NaN        NaN   \n",
       "\n",
       "      TOT_BLACK  TOT_NATIVE  TOT_ASIAN  TOT_PACIFIC  TOT_LATINX  \\\n",
       "0      284082.0      2029.0    10378.0        347.0     24099.0   \n",
       "1       24247.0       805.0     4403.0        101.0     11872.0   \n",
       "2        5737.0      2165.0     2757.0        510.0     44374.0   \n",
       "3      235660.0    112383.0   172425.0      11190.0   1239835.0   \n",
       "4       41043.0     42683.0    31905.0       2266.0    363063.0   \n",
       "...         ...         ...        ...          ...         ...   \n",
       "3371        NaN         NaN        NaN          NaN         NaN   \n",
       "3372        NaN         NaN        NaN          NaN         NaN   \n",
       "3373        NaN         NaN        NaN          NaN         NaN   \n",
       "3374        NaN         NaN        NaN          NaN         NaN   \n",
       "3375        NaN         NaN        NaN          NaN         NaN   \n",
       "\n",
       "              State/County  FI Rate              Low Threshold Type  \\\n",
       "0     Jefferson County, AL    0.197                            SNAP   \n",
       "1        Shelby County, AL    0.105                            SNAP   \n",
       "2       Cochise County, AZ    0.161  SNAP, Other Nutrition Programs   \n",
       "3      Maricopa County, AZ    0.158  SNAP, Other Nutrition Programs   \n",
       "4          Pima County, AZ    0.154  SNAP, Other Nutrition Programs   \n",
       "...                    ...      ...                             ...   \n",
       "3371                   NaN      NaN                             NaN   \n",
       "3372                   NaN      NaN                             NaN   \n",
       "3373                   NaN      NaN                             NaN   \n",
       "3374                   NaN      NaN                             NaN   \n",
       "3375                   NaN      NaN                             NaN   \n",
       "\n",
       "                 High Threshold Type  Cost Per Meal  Num_wholesale  \\\n",
       "0            Other Nutrition Program           2.93          483.0   \n",
       "1            Other Nutrition Program           3.37            1.0   \n",
       "2     SNAP, Other Nutrition Programs           2.81          800.0   \n",
       "3     SNAP, Other Nutrition Programs           2.90         2389.0   \n",
       "4     SNAP, Other Nutrition Programs           2.85         3591.0   \n",
       "...                              ...            ...            ...   \n",
       "3371                             NaN            NaN            0.0   \n",
       "3372                             NaN            NaN            0.0   \n",
       "3373                             NaN            NaN            0.0   \n",
       "3374                             NaN            NaN            0.0   \n",
       "3375                             NaN            NaN            0.0   \n",
       "\n",
       "      Num_restaraunts  Num_grocery  Total_workforce   Employed  Unemployed  \\\n",
       "0              2693.0        400.0         312131.0   292505.0     19626.0   \n",
       "1               743.0       2706.0         107208.0   102400.0      4808.0   \n",
       "2                72.0        340.0          50969.0    46682.0      4287.0   \n",
       "3             16857.0       6320.0        1961997.0  1848119.0    113878.0   \n",
       "4               838.0       1604.0         463126.0   435183.0     27943.0   \n",
       "...               ...          ...              ...        ...         ...   \n",
       "3371             17.0         16.0              NaN        NaN         NaN   \n",
       "3372            131.0          0.0              NaN        NaN         NaN   \n",
       "3373            104.0          3.0              NaN        NaN         NaN   \n",
       "3374              0.0         51.0              NaN        NaN         NaN   \n",
       "3375              0.0        146.0              NaN        NaN         NaN   \n",
       "\n",
       "      Unemployment_rate  \n",
       "0                   6.3  \n",
       "1                   4.5  \n",
       "2                   8.4  \n",
       "3                   5.8  \n",
       "4                   6.0  \n",
       "...                 ...  \n",
       "3371                NaN  \n",
       "3372                NaN  \n",
       "3373                NaN  \n",
       "3374                NaN  \n",
       "3375                NaN  \n",
       "\n",
       "[85821 rows x 46 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# drop older missing data from main df\n",
    "df_trimmed = df.drop(df[df.Year.isin(['2011', '2012','2013'])].index)\n",
    "\n",
    "# add new data\n",
    "df = pd.concat([df_trimmed, df_11, df_12, df_13])\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2020    32502\n",
       "2019    13207\n",
       "2010     9787\n",
       "2018     3406\n",
       "2017     3395\n",
       "2016     3386\n",
       "2014     3386\n",
       "2015     3384\n",
       "2012     3378\n",
       "2013     3376\n",
       "2011     3375\n",
       "2009     3239\n",
       "Name: Year, dtype: int64"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check to see that number of observations is retained\n",
    "df.Year.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Remove Duplicates per County\n",
    "Some FIPS (counties) are present multiple times with slightly varying observations (likely taken from different sources.) The following process groups the dataframe by FIPS, while taking the mean of the observations, so that there is one row per county."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 234,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "06037    12\n",
       "25017    12\n",
       "17197    12\n",
       "17031    12\n",
       "25005     9\n",
       "         ..\n",
       "02270     1\n",
       "26999     1\n",
       "23999     1\n",
       "12999     1\n",
       "02999     1\n",
       "Name: FIPS, Length: 3245, dtype: int64"
      ]
     },
     "execution_count": 234,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check value counts per FIPS code, indicating there are multiple entries in some cases\n",
    "df[df.Year=='2010'].FIPS.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 235,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# perform groupby's on the years containing duplicates\n",
    "no_dups_10 = df[df.Year=='2010'].groupby('FIPS').mean().reset_index()\n",
    "no_dups_19 = df[df.Year=='2019'].groupby('FIPS').mean().reset_index()\n",
    "no_dups_20 = df[df.Year=='2020'].groupby('FIPS').mean().reset_index()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 236,
   "metadata": {},
   "outputs": [],
   "source": [
    "# re-add in the columns that were removed during groupby operation\n",
    "no_dups_10 = no_dups_10.merge(df[df.Year=='2010'][['Year','FIPS','Low Threshold Type','High Threshold Type', 'State',\n",
    "                'coc_number','County','State/County']],on='FIPS').drop_duplicates().dropna(subset=['State/County'])\n",
    "no_dups_19 = no_dups_19.merge(df[df.Year=='2019'][['Year','FIPS','Low Threshold Type','High Threshold Type', 'State',\n",
    "                'coc_number','County','State/County']],on='FIPS').drop_duplicates().dropna(subset=['State/County'])\n",
    "no_dups_20 = no_dups_20.merge(df[df.Year=='2020'][['Year','FIPS','Low Threshold Type','High Threshold Type', 'State',\n",
    "                'coc_number','County','State/County']],on='FIPS').drop_duplicates().dropna(subset=['State/County'])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 237,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# drop older data from main df, which contained duplicates\n",
    "df_trimmed = df.drop(df[df.Year.isin(['2010', '2019','2020'])].index)\n",
    "\n",
    "# add newly grouped data with no duplicates back into main df\n",
    "df = pd.concat([df_trimmed, no_dups_10, no_dups_19, no_dups_20])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 238,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2018    3406\n",
       "2017    3395\n",
       "2016    3386\n",
       "2014    3386\n",
       "2015    3384\n",
       "2019    3282\n",
       "2010    3279\n",
       "2009    3239\n",
       "2020    3219\n",
       "2012    1785\n",
       "2013    1785\n",
       "2011    1785\n",
       "Name: Year, dtype: int64"
      ]
     },
     "execution_count": 238,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.Year.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Impute HH Size 2019\n",
    "Household Size data, collected from the ACS Census API, is drastically higher on average in 2019 than all other years. Other years report an average of 2.52 people per household, while in 2019 the reported average is 13,464.72 people per household. For this reason, 2019 values will be imputed by using the 2018 value for 2019, given that the rate of change year-by-year is negligible. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Inspect Yearly Averages"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 239,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Year\n",
       "2009             NaN\n",
       "2010        2.515342\n",
       "2011        2.517657\n",
       "2012        2.517472\n",
       "2013        2.524417\n",
       "2014        2.531881\n",
       "2015        2.533775\n",
       "2016        2.533969\n",
       "2017        2.522340\n",
       "2018        2.523214\n",
       "2019    13464.727216\n",
       "2020             NaN\n",
       "Name: hh_avg_size, dtype: float64"
      ]
     },
     "execution_count": 239,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby('Year')['hh_avg_size'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 240,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(35331, 46)"
      ]
     },
     "execution_count": 240,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Drop 2019 data and insert 2018 values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 241,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_19 = df[df.Year=='2019']\n",
    "df_not_19 = df[df.Year!='2019']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 242,
   "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</th>\n",
       "      <th>Rent</th>\n",
       "      <th>Year</th>\n",
       "      <th>coc_number</th>\n",
       "      <th>Houseless_rate</th>\n",
       "      <th>Sheltered_rate</th>\n",
       "      <th>Unsheltered_rate</th>\n",
       "      <th>hh_med_income</th>\n",
       "      <th>pop_disabled</th>\n",
       "      <th>pop_hs_grad</th>\n",
       "      <th>pop_bachelors</th>\n",
       "      <th>pop_grad_degree</th>\n",
       "      <th>pop_priv_health</th>\n",
       "      <th>pop_public_health</th>\n",
       "      <th>pop_no_health</th>\n",
       "      <th>pop_total</th>\n",
       "      <th>percent_hh_poverty</th>\n",
       "      <th>hh_avg_size</th>\n",
       "      <th>pop_65+</th>\n",
       "      <th>hh_no_vehicle</th>\n",
       "      <th>num_hh</th>\n",
       "      <th>pop_non_citizen</th>\n",
       "      <th>hh_SNAP</th>\n",
       "      <th>State</th>\n",
       "      <th>County</th>\n",
       "      <th>TOT_POP</th>\n",
       "      <th>TOT_MALE</th>\n",
       "      <th>TOT_FEMALE</th>\n",
       "      <th>TOT_WHITE</th>\n",
       "      <th>TOT_BLACK</th>\n",
       "      <th>TOT_NATIVE</th>\n",
       "      <th>TOT_ASIAN</th>\n",
       "      <th>TOT_PACIFIC</th>\n",
       "      <th>TOT_LATINX</th>\n",
       "      <th>State/County</th>\n",
       "      <th>FI Rate</th>\n",
       "      <th>Low Threshold Type</th>\n",
       "      <th>High Threshold Type</th>\n",
       "      <th>Cost Per Meal</th>\n",
       "      <th>Num_wholesale</th>\n",
       "      <th>Num_restaraunts</th>\n",
       "      <th>Num_grocery</th>\n",
       "      <th>Total_workforce</th>\n",
       "      <th>Employed</th>\n",
       "      <th>Unemployed</th>\n",
       "      <th>Unemployment_rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>01073</td>\n",
       "      <td>1019.995960</td>\n",
       "      <td>2014</td>\n",
       "      <td>AL-500</td>\n",
       "      <td>0.001396</td>\n",
       "      <td>0.000961</td>\n",
       "      <td>0.000435</td>\n",
       "      <td>45239.0</td>\n",
       "      <td>94584.0</td>\n",
       "      <td>117854.0</td>\n",
       "      <td>81626.0</td>\n",
       "      <td>52774.0</td>\n",
       "      <td>431638.0</td>\n",
       "      <td>211570.0</td>\n",
       "      <td>81336.0</td>\n",
       "      <td>658834.0</td>\n",
       "      <td>14.8</td>\n",
       "      <td>2.48</td>\n",
       "      <td>87036.0</td>\n",
       "      <td>93630.0</td>\n",
       "      <td>259397.0</td>\n",
       "      <td>17519.0</td>\n",
       "      <td>39967.0</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Jefferson County</td>\n",
       "      <td>659972.0</td>\n",
       "      <td>312491.0</td>\n",
       "      <td>347481.0</td>\n",
       "      <td>355635.0</td>\n",
       "      <td>284082.0</td>\n",
       "      <td>2029.0</td>\n",
       "      <td>10378.0</td>\n",
       "      <td>347.0</td>\n",
       "      <td>24099.0</td>\n",
       "      <td>Jefferson County, AL</td>\n",
       "      <td>0.197</td>\n",
       "      <td>SNAP</td>\n",
       "      <td>Other Nutrition Program</td>\n",
       "      <td>2.93</td>\n",
       "      <td>483.0</td>\n",
       "      <td>2693.0</td>\n",
       "      <td>400.0</td>\n",
       "      <td>312131.000</td>\n",
       "      <td>292505.000</td>\n",
       "      <td>19626.000</td>\n",
       "      <td>6.3000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>01117</td>\n",
       "      <td>1229.755051</td>\n",
       "      <td>2014</td>\n",
       "      <td>AL-500</td>\n",
       "      <td>0.001396</td>\n",
       "      <td>0.000961</td>\n",
       "      <td>0.000435</td>\n",
       "      <td>69723.0</td>\n",
       "      <td>22792.0</td>\n",
       "      <td>28911.0</td>\n",
       "      <td>35773.0</td>\n",
       "      <td>18511.0</td>\n",
       "      <td>159655.0</td>\n",
       "      <td>42429.0</td>\n",
       "      <td>19175.0</td>\n",
       "      <td>201168.0</td>\n",
       "      <td>6.2</td>\n",
       "      <td>2.65</td>\n",
       "      <td>23404.0</td>\n",
       "      <td>19762.0</td>\n",
       "      <td>74790.0</td>\n",
       "      <td>7624.0</td>\n",
       "      <td>4706.0</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Shelby County</td>\n",
       "      <td>206280.0</td>\n",
       "      <td>100304.0</td>\n",
       "      <td>105976.0</td>\n",
       "      <td>174094.0</td>\n",
       "      <td>24247.0</td>\n",
       "      <td>805.0</td>\n",
       "      <td>4403.0</td>\n",
       "      <td>101.0</td>\n",
       "      <td>11872.0</td>\n",
       "      <td>Shelby County, AL</td>\n",
       "      <td>0.105</td>\n",
       "      <td>SNAP</td>\n",
       "      <td>Other Nutrition Program</td>\n",
       "      <td>3.37</td>\n",
       "      <td>1.0</td>\n",
       "      <td>743.0</td>\n",
       "      <td>2706.0</td>\n",
       "      <td>107208.000</td>\n",
       "      <td>102400.000</td>\n",
       "      <td>4808.000</td>\n",
       "      <td>4.5000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>04003</td>\n",
       "      <td>1051.250000</td>\n",
       "      <td>2014</td>\n",
       "      <td>AZ-500</td>\n",
       "      <td>0.001469</td>\n",
       "      <td>0.000826</td>\n",
       "      <td>0.000643</td>\n",
       "      <td>45974.0</td>\n",
       "      <td>20341.0</td>\n",
       "      <td>21109.0</td>\n",
       "      <td>12968.0</td>\n",
       "      <td>7566.0</td>\n",
       "      <td>76099.0</td>\n",
       "      <td>50498.0</td>\n",
       "      <td>14868.0</td>\n",
       "      <td>130807.0</td>\n",
       "      <td>13.1</td>\n",
       "      <td>2.47</td>\n",
       "      <td>23593.0</td>\n",
       "      <td>16328.0</td>\n",
       "      <td>48846.0</td>\n",
       "      <td>7947.0</td>\n",
       "      <td>7812.0</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>Cochise County</td>\n",
       "      <td>127314.0</td>\n",
       "      <td>64661.0</td>\n",
       "      <td>62653.0</td>\n",
       "      <td>112238.0</td>\n",
       "      <td>5737.0</td>\n",
       "      <td>2165.0</td>\n",
       "      <td>2757.0</td>\n",
       "      <td>510.0</td>\n",
       "      <td>44374.0</td>\n",
       "      <td>Cochise County, AZ</td>\n",
       "      <td>0.161</td>\n",
       "      <td>SNAP, Other Nutrition Programs</td>\n",
       "      <td>SNAP, Other Nutrition Programs</td>\n",
       "      <td>2.81</td>\n",
       "      <td>800.0</td>\n",
       "      <td>72.0</td>\n",
       "      <td>340.0</td>\n",
       "      <td>50969.000</td>\n",
       "      <td>46682.000</td>\n",
       "      <td>4287.000</td>\n",
       "      <td>8.4000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>04013</td>\n",
       "      <td>1095.670228</td>\n",
       "      <td>2014</td>\n",
       "      <td>AZ-502</td>\n",
       "      <td>0.001318</td>\n",
       "      <td>0.001084</td>\n",
       "      <td>0.000235</td>\n",
       "      <td>53689.0</td>\n",
       "      <td>399455.0</td>\n",
       "      <td>593094.0</td>\n",
       "      <td>490927.0</td>\n",
       "      <td>273108.0</td>\n",
       "      <td>2444443.0</td>\n",
       "      <td>1204681.0</td>\n",
       "      <td>646167.0</td>\n",
       "      <td>3947382.0</td>\n",
       "      <td>12.7</td>\n",
       "      <td>2.74</td>\n",
       "      <td>507428.0</td>\n",
       "      <td>546028.0</td>\n",
       "      <td>1424244.0</td>\n",
       "      <td>373532.0</td>\n",
       "      <td>171581.0</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>Maricopa County</td>\n",
       "      <td>4093648.0</td>\n",
       "      <td>2024659.0</td>\n",
       "      <td>2068989.0</td>\n",
       "      <td>3449404.0</td>\n",
       "      <td>235660.0</td>\n",
       "      <td>112383.0</td>\n",
       "      <td>172425.0</td>\n",
       "      <td>11190.0</td>\n",
       "      <td>1239835.0</td>\n",
       "      <td>Maricopa County, AZ</td>\n",
       "      <td>0.158</td>\n",
       "      <td>SNAP, Other Nutrition Programs</td>\n",
       "      <td>SNAP, Other Nutrition Programs</td>\n",
       "      <td>2.90</td>\n",
       "      <td>2389.0</td>\n",
       "      <td>16857.0</td>\n",
       "      <td>6320.0</td>\n",
       "      <td>1961997.000</td>\n",
       "      <td>1848119.000</td>\n",
       "      <td>113878.000</td>\n",
       "      <td>5.8000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>04019</td>\n",
       "      <td>928.546429</td>\n",
       "      <td>2014</td>\n",
       "      <td>AZ-501</td>\n",
       "      <td>0.002170</td>\n",
       "      <td>0.001705</td>\n",
       "      <td>0.000465</td>\n",
       "      <td>46233.0</td>\n",
       "      <td>133694.0</td>\n",
       "      <td>149147.0</td>\n",
       "      <td>115392.0</td>\n",
       "      <td>81406.0</td>\n",
       "      <td>592298.0</td>\n",
       "      <td>364938.0</td>\n",
       "      <td>141211.0</td>\n",
       "      <td>993144.0</td>\n",
       "      <td>13.2</td>\n",
       "      <td>2.50</td>\n",
       "      <td>162075.0</td>\n",
       "      <td>149710.0</td>\n",
       "      <td>386155.0</td>\n",
       "      <td>69636.0</td>\n",
       "      <td>57099.0</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>Pima County</td>\n",
       "      <td>1004229.0</td>\n",
       "      <td>494684.0</td>\n",
       "      <td>509545.0</td>\n",
       "      <td>858334.0</td>\n",
       "      <td>41043.0</td>\n",
       "      <td>42683.0</td>\n",
       "      <td>31905.0</td>\n",
       "      <td>2266.0</td>\n",
       "      <td>363063.0</td>\n",
       "      <td>Pima County, AZ</td>\n",
       "      <td>0.154</td>\n",
       "      <td>SNAP, Other Nutrition Programs</td>\n",
       "      <td>SNAP, Other Nutrition Programs</td>\n",
       "      <td>2.85</td>\n",
       "      <td>3591.0</td>\n",
       "      <td>838.0</td>\n",
       "      <td>1604.0</td>\n",
       "      <td>463126.000</td>\n",
       "      <td>435183.000</td>\n",
       "      <td>27943.000</td>\n",
       "      <td>6.0000</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32452</th>\n",
       "      <td>72145</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2020</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Vega Baja Municipio, PR</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12646.625</td>\n",
       "      <td>11223.375</td>\n",
       "      <td>1423.250</td>\n",
       "      <td>11.2375</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32462</th>\n",
       "      <td>72147</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2020</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Vieques Municipio, PR</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2476.875</td>\n",
       "      <td>2252.000</td>\n",
       "      <td>224.875</td>\n",
       "      <td>9.0875</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32472</th>\n",
       "      <td>72149</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2020</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Villalba Municipio, PR</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6819.875</td>\n",
       "      <td>6013.500</td>\n",
       "      <td>806.375</td>\n",
       "      <td>11.6500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32482</th>\n",
       "      <td>72151</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2020</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yabucoa Municipio, PR</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8106.625</td>\n",
       "      <td>7232.250</td>\n",
       "      <td>874.375</td>\n",
       "      <td>10.7500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32492</th>\n",
       "      <td>72153</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2020</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yauco Municipio, PR</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9261.500</td>\n",
       "      <td>8086.875</td>\n",
       "      <td>1174.625</td>\n",
       "      <td>12.5500</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>32049 rows × 46 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        FIPS         Rent  Year coc_number  Houseless_rate  Sheltered_rate  \\\n",
       "0      01073  1019.995960  2014     AL-500        0.001396        0.000961   \n",
       "1      01117  1229.755051  2014     AL-500        0.001396        0.000961   \n",
       "2      04003  1051.250000  2014     AZ-500        0.001469        0.000826   \n",
       "3      04013  1095.670228  2014     AZ-502        0.001318        0.001084   \n",
       "4      04019   928.546429  2014     AZ-501        0.002170        0.001705   \n",
       "...      ...          ...   ...        ...             ...             ...   \n",
       "32452  72145          NaN  2020        NaN             NaN             NaN   \n",
       "32462  72147          NaN  2020        NaN             NaN             NaN   \n",
       "32472  72149          NaN  2020        NaN             NaN             NaN   \n",
       "32482  72151          NaN  2020        NaN             NaN             NaN   \n",
       "32492  72153          NaN  2020        NaN             NaN             NaN   \n",
       "\n",
       "       Unsheltered_rate  hh_med_income  pop_disabled  pop_hs_grad  \\\n",
       "0              0.000435        45239.0       94584.0     117854.0   \n",
       "1              0.000435        69723.0       22792.0      28911.0   \n",
       "2              0.000643        45974.0       20341.0      21109.0   \n",
       "3              0.000235        53689.0      399455.0     593094.0   \n",
       "4              0.000465        46233.0      133694.0     149147.0   \n",
       "...                 ...            ...           ...          ...   \n",
       "32452               NaN            NaN           NaN          NaN   \n",
       "32462               NaN            NaN           NaN          NaN   \n",
       "32472               NaN            NaN           NaN          NaN   \n",
       "32482               NaN            NaN           NaN          NaN   \n",
       "32492               NaN            NaN           NaN          NaN   \n",
       "\n",
       "       pop_bachelors  pop_grad_degree  pop_priv_health  pop_public_health  \\\n",
       "0            81626.0          52774.0         431638.0           211570.0   \n",
       "1            35773.0          18511.0         159655.0            42429.0   \n",
       "2            12968.0           7566.0          76099.0            50498.0   \n",
       "3           490927.0         273108.0        2444443.0          1204681.0   \n",
       "4           115392.0          81406.0         592298.0           364938.0   \n",
       "...              ...              ...              ...                ...   \n",
       "32452            NaN              NaN              NaN                NaN   \n",
       "32462            NaN              NaN              NaN                NaN   \n",
       "32472            NaN              NaN              NaN                NaN   \n",
       "32482            NaN              NaN              NaN                NaN   \n",
       "32492            NaN              NaN              NaN                NaN   \n",
       "\n",
       "       pop_no_health  pop_total  percent_hh_poverty  hh_avg_size   pop_65+  \\\n",
       "0            81336.0   658834.0                14.8         2.48   87036.0   \n",
       "1            19175.0   201168.0                 6.2         2.65   23404.0   \n",
       "2            14868.0   130807.0                13.1         2.47   23593.0   \n",
       "3           646167.0  3947382.0                12.7         2.74  507428.0   \n",
       "4           141211.0   993144.0                13.2         2.50  162075.0   \n",
       "...              ...        ...                 ...          ...       ...   \n",
       "32452            NaN        NaN                 NaN          NaN       NaN   \n",
       "32462            NaN        NaN                 NaN          NaN       NaN   \n",
       "32472            NaN        NaN                 NaN          NaN       NaN   \n",
       "32482            NaN        NaN                 NaN          NaN       NaN   \n",
       "32492            NaN        NaN                 NaN          NaN       NaN   \n",
       "\n",
       "       hh_no_vehicle     num_hh  pop_non_citizen   hh_SNAP    State  \\\n",
       "0            93630.0   259397.0          17519.0   39967.0  Alabama   \n",
       "1            19762.0    74790.0           7624.0    4706.0  Alabama   \n",
       "2            16328.0    48846.0           7947.0    7812.0  Arizona   \n",
       "3           546028.0  1424244.0         373532.0  171581.0  Arizona   \n",
       "4           149710.0   386155.0          69636.0   57099.0  Arizona   \n",
       "...              ...        ...              ...       ...      ...   \n",
       "32452            NaN        NaN              NaN       NaN      NaN   \n",
       "32462            NaN        NaN              NaN       NaN      NaN   \n",
       "32472            NaN        NaN              NaN       NaN      NaN   \n",
       "32482            NaN        NaN              NaN       NaN      NaN   \n",
       "32492            NaN        NaN              NaN       NaN      NaN   \n",
       "\n",
       "                 County    TOT_POP   TOT_MALE  TOT_FEMALE  TOT_WHITE  \\\n",
       "0      Jefferson County   659972.0   312491.0    347481.0   355635.0   \n",
       "1         Shelby County   206280.0   100304.0    105976.0   174094.0   \n",
       "2        Cochise County   127314.0    64661.0     62653.0   112238.0   \n",
       "3       Maricopa County  4093648.0  2024659.0   2068989.0  3449404.0   \n",
       "4           Pima County  1004229.0   494684.0    509545.0   858334.0   \n",
       "...                 ...        ...        ...         ...        ...   \n",
       "32452               NaN        NaN        NaN         NaN        NaN   \n",
       "32462               NaN        NaN        NaN         NaN        NaN   \n",
       "32472               NaN        NaN        NaN         NaN        NaN   \n",
       "32482               NaN        NaN        NaN         NaN        NaN   \n",
       "32492               NaN        NaN        NaN         NaN        NaN   \n",
       "\n",
       "       TOT_BLACK  TOT_NATIVE  TOT_ASIAN  TOT_PACIFIC  TOT_LATINX  \\\n",
       "0       284082.0      2029.0    10378.0        347.0     24099.0   \n",
       "1        24247.0       805.0     4403.0        101.0     11872.0   \n",
       "2         5737.0      2165.0     2757.0        510.0     44374.0   \n",
       "3       235660.0    112383.0   172425.0      11190.0   1239835.0   \n",
       "4        41043.0     42683.0    31905.0       2266.0    363063.0   \n",
       "...          ...         ...        ...          ...         ...   \n",
       "32452        NaN         NaN        NaN          NaN         NaN   \n",
       "32462        NaN         NaN        NaN          NaN         NaN   \n",
       "32472        NaN         NaN        NaN          NaN         NaN   \n",
       "32482        NaN         NaN        NaN          NaN         NaN   \n",
       "32492        NaN         NaN        NaN          NaN         NaN   \n",
       "\n",
       "                  State/County  FI Rate              Low Threshold Type  \\\n",
       "0         Jefferson County, AL    0.197                            SNAP   \n",
       "1            Shelby County, AL    0.105                            SNAP   \n",
       "2           Cochise County, AZ    0.161  SNAP, Other Nutrition Programs   \n",
       "3          Maricopa County, AZ    0.158  SNAP, Other Nutrition Programs   \n",
       "4              Pima County, AZ    0.154  SNAP, Other Nutrition Programs   \n",
       "...                        ...      ...                             ...   \n",
       "32452  Vega Baja Municipio, PR      NaN                             NaN   \n",
       "32462    Vieques Municipio, PR      NaN                             NaN   \n",
       "32472   Villalba Municipio, PR      NaN                             NaN   \n",
       "32482    Yabucoa Municipio, PR      NaN                             NaN   \n",
       "32492      Yauco Municipio, PR      NaN                             NaN   \n",
       "\n",
       "                  High Threshold Type  Cost Per Meal  Num_wholesale  \\\n",
       "0             Other Nutrition Program           2.93          483.0   \n",
       "1             Other Nutrition Program           3.37            1.0   \n",
       "2      SNAP, Other Nutrition Programs           2.81          800.0   \n",
       "3      SNAP, Other Nutrition Programs           2.90         2389.0   \n",
       "4      SNAP, Other Nutrition Programs           2.85         3591.0   \n",
       "...                               ...            ...            ...   \n",
       "32452                             NaN            NaN            NaN   \n",
       "32462                             NaN            NaN            NaN   \n",
       "32472                             NaN            NaN            NaN   \n",
       "32482                             NaN            NaN            NaN   \n",
       "32492                             NaN            NaN            NaN   \n",
       "\n",
       "       Num_restaraunts  Num_grocery  Total_workforce     Employed  Unemployed  \\\n",
       "0               2693.0        400.0       312131.000   292505.000   19626.000   \n",
       "1                743.0       2706.0       107208.000   102400.000    4808.000   \n",
       "2                 72.0        340.0        50969.000    46682.000    4287.000   \n",
       "3              16857.0       6320.0      1961997.000  1848119.000  113878.000   \n",
       "4                838.0       1604.0       463126.000   435183.000   27943.000   \n",
       "...                ...          ...              ...          ...         ...   \n",
       "32452              NaN          NaN        12646.625    11223.375    1423.250   \n",
       "32462              NaN          NaN         2476.875     2252.000     224.875   \n",
       "32472              NaN          NaN         6819.875     6013.500     806.375   \n",
       "32482              NaN          NaN         8106.625     7232.250     874.375   \n",
       "32492              NaN          NaN         9261.500     8086.875    1174.625   \n",
       "\n",
       "       Unemployment_rate  \n",
       "0                 6.3000  \n",
       "1                 4.5000  \n",
       "2                 8.4000  \n",
       "3                 5.8000  \n",
       "4                 6.0000  \n",
       "...                  ...  \n",
       "32452            11.2375  \n",
       "32462             9.0875  \n",
       "32472            11.6500  \n",
       "32482            10.7500  \n",
       "32492            12.5500  \n",
       "\n",
       "[32049 rows x 46 columns]"
      ]
     },
     "execution_count": 242,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_not_19"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 243,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_18 = df[df.Year=='2018']\n",
    "df_19 = df_19.merge(df_18[['FIPS','hh_avg_size']],on='FIPS',how='outer').drop('hh_avg_size_x',axis=1).rename(columns={'hh_avg_size_y':'hh_avg_size'})\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 244,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.concat([df_not_19,df_19])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Save the fully cleaned dataframe using pickle"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 246,
   "metadata": {},
   "outputs": [],
   "source": [
    "# save with pickle to preserve datatypes\n",
    "with open('../pickled/fully_cleaned_data.pickle', \"wb\") as output_file:\n",
    "    pickle.dump(df, output_file)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}