data_aggregation_pipeline.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Aggregation Pipeline\n",
    "Due to the time constraints of this competition, we have decided to focus primarily on retrieving relevant demographic and economic data for zip codes in the U.S. \n",
    "\n",
    "We retrieved data from the following sources:\n",
    "* [United States Census Bureau American Community Survey 5-Year Data](https://www.census.gov/data/developers/data-sets/acs-5year.html)\n",
    "* [Centers for Disease Control Small-area Life Expectancies Project](https://www.cdc.gov/nchs/nvss/usaleep/usaleep.html)\n",
    "* [HUD USPS Zip Code Crosswalk Files](https://www.huduser.gov/portal/datasets/usps_crosswalk.html)\n",
    "* [Internal Revenue Service's Individual Income Tax Statistics For 2016](https://www.irs.gov/statistics/soi-tax-stats-individual-income-tax-statistics-2016-zip-code-data-soi)\n",
    "* [Bureau of Economic Analysis's Regional Price Parities by State and Metro Area](https://www.bea.gov/data/prices-inflation/regional-price-parities-state-and-metro-area)\n",
    "\n",
    "This notebook serves as a reproducible guide to our data aggregation process."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import re\n",
    "\n",
    "import numpy as np\n",
    "import us\n",
    "from tqdm import tqdm_notebook\n",
    "import pandas as pd\n",
    "from census import Census, CensusException"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "zip_data = pd.read_csv(\"zipcodes.csv\", index_col=None)\n",
    "\n",
    "# Remove pesky \"Unnamed\" column\n",
    "zip_data = zip_data.loc[:, ~zip_data.columns.str.contains('Unnamed')]\n",
    "\n",
    "# Change zips to str and pad with 0s\n",
    "zip_data[\"zip\"] = zip_data[\"zip\"].apply(lambda x: str(x).zfill(5))\n",
    "\n",
    "# Create Census object with API key\n",
    "cens = Census(\"your_api_key\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "zip_data contains basic geographic zip code data for 44,336 zip codes in the US."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>zip</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00210</td>\n",
       "      <td>Portsmouth</td>\n",
       "      <td>NH</td>\n",
       "      <td>43.005895</td>\n",
       "      <td>-71.013202</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>00211</td>\n",
       "      <td>Portsmouth</td>\n",
       "      <td>NH</td>\n",
       "      <td>43.005895</td>\n",
       "      <td>-71.013202</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>00212</td>\n",
       "      <td>Portsmouth</td>\n",
       "      <td>NH</td>\n",
       "      <td>43.005895</td>\n",
       "      <td>-71.013202</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>00213</td>\n",
       "      <td>Portsmouth</td>\n",
       "      <td>NH</td>\n",
       "      <td>43.005895</td>\n",
       "      <td>-71.013202</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>00214</td>\n",
       "      <td>Portsmouth</td>\n",
       "      <td>NH</td>\n",
       "      <td>43.005895</td>\n",
       "      <td>-71.013202</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     zip        city state   latitude  longitude\n",
       "0  00210  Portsmouth    NH  43.005895 -71.013202\n",
       "1  00211  Portsmouth    NH  43.005895 -71.013202\n",
       "2  00212  Portsmouth    NH  43.005895 -71.013202\n",
       "3  00213  Portsmouth    NH  43.005895 -71.013202\n",
       "4  00214  Portsmouth    NH  43.005895 -71.013202"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "zip_data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This function, get_census_val() is used to retrieve data from the Census's [5-year American Community Survey](https://www.census.gov/data/developers/data-sets/acs-5year.html), which provides accurate estimates for a huge number of variables for US ZIP codes. We are using this data for a number of features in our model."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Still needs to be tested with every variable change\n",
    "def get_census_val(cens_obj, variable, zipcode):\n",
    "    try:\n",
    "        result = cens_obj.acs5.zipcode(variable, zipcode)\n",
    "        if len(result) > 0:\n",
    "            return result[0].get(variable)\n",
    "        else:\n",
    "            return 0.0\n",
    "    except ConnectionError:\n",
    "        return None\n",
    "    except CensusException:\n",
    "        return None"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Population Demographics\n",
    "We want to find out the total population for each zip code and the populations of the target markets. To do this, we use the U.S. Census API by way of the Census package for Python.\n",
    "\n",
    "First, we get total population values for each zip code."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Put zip codes into a list for ease of processing\n",
    "zips = [[zipcode, None] for zipcode in zip_data[\"zip\"]]\n",
    "\n",
    "# Get populations for zip codes if value is None. I did it this way to be able\n",
    "# non-redundantly call the API in batches in case of the common ConnectionError\n",
    "for zipcode in tqdm_notebook(zips):\n",
    "    if zipcode[1] is None:\n",
    "        zipcode[1] = get_census_val(cens, \"B01003_001E\", zipcode[0])\n",
    "        \n",
    "pops = pd.DataFrame(zips, columns=[\"zip\", \"population\"])\n",
    "\n",
    "zip_data = pd.merge(zip_data, pops, how=\"inner\", on=\"zip\")\n",
    "\n",
    "# Save, because the API pulls take a long time\n",
    "zip_data.to_csv(\"zip_data.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next, we request population data from the API for the age range that we need (45-85)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "populated_zips = zip_data[zip_data[\"population\"] > 0]\n",
    "\n",
    "zips = [[zipcode, None] for zipcode in populated_zips[\"zip\"]]\n",
    "for zipcode in tqdm_notebook(zips):\n",
    "    if zipcode[1] is None:\n",
    "        zipcode[1] = get_census_val(cens, \"B07001_010E\", zipcode[0])       \n",
    "pops = pd.DataFrame(zips, columns=[\"zip\", \"pop_45-49\"])\n",
    "zip_data = pd.merge(zip_data, pops, how=\"left\", on=\"zip\")\n",
    "\n",
    "zips = [[zipcode, None] for zipcode in populated_zips[\"zip\"]]\n",
    "for zipcode in tqdm_notebook(zips):\n",
    "    if zipcode[1] is None:\n",
    "        zipcode[1] = get_census_val(cens, \"B07001_011E\", zipcode[0])      \n",
    "pops = pd.DataFrame(zips, columns=[\"zip\", \"pop_50-54\"])\n",
    "zip_data = pd.merge(zip_data, pops, how=\"left\", on=\"zip\")\n",
    "\n",
    "zips = [[zipcode, None] for zipcode in populated_zips[\"zip\"]]\n",
    "for zipcode in tqdm_notebook(zips):\n",
    "    if zipcode[1] is None:\n",
    "        zipcode[1] = get_census_val(cens, \"B07001_012E\", zipcode[0])\n",
    "pops = pd.DataFrame(zips, columns=[\"zip\", \"pop_55-59\"])\n",
    "zip_data = pd.merge(zip_data, pops, how=\"left\", on=\"zip\")\n",
    "\n",
    "zips = [[zipcode, None] for zipcode in populated_zips[\"zip\"]]\n",
    "for zipcode in tqdm_notebook(zips):\n",
    "    if zipcode[1] is None:\n",
    "        zipcode[1] = get_census_val(cens, \"B07001_013E\", zipcode[0])\n",
    "pops = pd.DataFrame(zips, columns=[\"zip\", \"pop_60-64\"])\n",
    "zip_data = pd.merge(zip_data, pops, how=\"left\", on=\"zip\")\n",
    "\n",
    "zips = [[zipcode, None] for zipcode in populated_zips[\"zip\"]]\n",
    "for zipcode in tqdm_notebook(zips):\n",
    "    if zipcode[1] is None:\n",
    "        zipcode[1] = get_census_val(cens, \"B07001_014E\", zipcode[0])\n",
    "pops = pd.DataFrame(zips, columns=[\"zip\", \"pop_65-69\"])\n",
    "zip_data = pd.merge(zip_data, pops, how=\"left\", on=\"zip\")\n",
    "\n",
    "zips = [[zipcode, None] for zipcode in populated_zips[\"zip\"]]\n",
    "for zipcode in tqdm_notebook(zips):\n",
    "    if zipcode[1] is None:\n",
    "        zipcode[1] = get_census_val(cens, \"B07001_015E\", zipcode[0])\n",
    "pops = pd.DataFrame(zips, columns=[\"zip\", \"pop_70-74\"])\n",
    "zip_data = pd.merge(zip_data, pops, how=\"left\", on=\"zip\")\n",
    "\n",
    "zips = [[zipcode, None] for zipcode in populated_zips[\"zip\"]]\n",
    "for zipcode in tqdm_notebook(zips):\n",
    "    if zipcode[1] is None:\n",
    "        zipcode[1] = get_census_val(cens, \"B07001_016E\", zipcode[0])\n",
    "pops = pd.DataFrame(zips, columns=[\"zip\", \"pop_75-inf\"])\n",
    "zip_data = pd.merge(zip_data, pops, how=\"left\", on=\"zip\")\n",
    "\n",
    "# Save, because the API pulls take a long time\n",
    "zip_data.to_csv(\"zip_data.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We also obtained the median individual income for each zip code."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "zips = [[zipcode, None] for zipcode in populated_zips[\"zip\"]]\n",
    "for zipcode in tqdm_notebook(zips):\n",
    "    if zipcode[1] is None:\n",
    "        zipcode[1] = get_census_val(cens, \"B19326_002E\", zipcode[0])\n",
    "incs = pd.DataFrame(zips, columns=[\"zip\", \"median_indiv_income\"])\n",
    "zip_data = pd.merge(zip_data, incs, how=\"left\", on=\"zip\")\n",
    "\n",
    "# Save, because the API pulls take a long time\n",
    "zip_data.to_csv(\"zip_data.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This next cell is just a load point to be used when reproducing results or when adding data after the last API pull was completed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read in \n",
    "zip_data = pd.read_csv(\"zip_data.csv\", index_col=None)\n",
    "\n",
    "# remove pesky \"Unnamed\" column\n",
    "zip_data = zip_data.loc[:, ~zip_data.columns.str.contains('Unnamed')]\n",
    "\n",
    "# Change zips to str and pad with 0s\n",
    "zip_data[\"zip\"] = zip_data[\"zip\"].apply(lambda x: str(x).zfill(5))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We obtained life expectancy data by Census tract from the [CDC](https://www.cdc.gov/nchs/nvss/usaleep/usaleep.html) and roughly converted these values to their zip codes with [HUD data](https://www.huduser.gov/portal/datasets/usps_crosswalk.html). Converting arbitrarily-defined geographic areas is not perfect - there are many single zip codes containing multiple census tracts as well as single census tracts containing multiple zip codes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# read in\n",
    "life_exp = pd.read_csv(\"life_expect_by_census_tract.csv\", index_col=None)\n",
    "\n",
    "# Drop unneeded columns\n",
    "life_exp = pd.DataFrame(life_exp, columns=['Tract ID', 'e(0)'])\n",
    "life_exp = life_exp.rename(index=str, columns={\"Tract ID\": \"tract\",\n",
    "                                               \"e(0)\": \"life_expectancy\"})\n",
    "\n",
    "# Convert Census tracts to zip codes\n",
    "# Read in\n",
    "zip_to_tract = pd.read_csv(\"zip_to_tract.csv\", index_col=None)\n",
    "\n",
    "# Change zips to str and pad with 0s\n",
    "zip_to_tract[\"zip\"] = zip_to_tract[\"zip\"].apply(lambda x: str(x).zfill(5))\n",
    "zip_to_tract = pd.DataFrame(zip_to_tract, columns=['zip', 'tract'])\n",
    "\n",
    "# Merge and drop duplicates\n",
    "life_exp = pd.merge(life_exp, zip_to_tract, how=\"left\", on=\"tract\")\n",
    "life_exp = life_exp.drop_duplicates(\"zip\")\n",
    "life_exp = pd.DataFrame(life_exp, columns=['zip', 'life_expectancy'])\n",
    "\n",
    "# Merge with zip_data\n",
    "zip_data = pd.merge(zip_data, life_exp, how=\"left\", on=\"zip\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Income Tax Statistics\n",
    "The files 16zpallagi.csv and 16zpallnoagi.csv, used below, were obtained from the [IRS's Individual Income Tax Statistics](https://www.irs.gov/statistics/soi-tax-stats-individual-income-tax-statistics-2016-zip-code-data-soi) organized by zip code for 2016.\n",
    "\n",
    "Using this data, we add the count of returns in the provided income ranges for every ZIP code to the zip_data dataframe:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "income = pd.read_csv(\"16zpallagi.csv\", index_col=None)\n",
    "\n",
    "agis = pd.DataFrame(income, columns=[\"zipcode\", \"agi_stub\", \"N1\"])\n",
    "\n",
    "agis = pd.pivot_table(agis, values=\"N1\", index=\"zipcode\", columns=\"agi_stub\")\n",
    "agis = agis.reset_index()\n",
    "\n",
    "agis[\"zipcode\"] = agis[\"zipcode\"].apply(lambda x: str(x).zfill(5))\n",
    "\n",
    "agis = agis.rename(index=str, columns={\"zipcode\": \"zip\",\n",
    "                                       1: \"num_tax_returns_0-25k\",\n",
    "                                       2: \"num_tax_returns_25k-50k\",\n",
    "                                       3: \"num_tax_returns_50k-75k\",\n",
    "                                       4: \"num_tax_returns_75k-100k\",\n",
    "                                       5: \"num_tax_returns_100k-200k\",\n",
    "                                       6: \"num_tax_returns_200k-inf\"})\n",
    "\n",
    "agis = agis[1:]\n",
    "\n",
    "zip_data = pd.merge(zip_data, agis, how=\"left\", on=\"zip\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here we add the number of elderly tax returns for each zip code:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "income_noagi = pd.read_csv(\"16zpallnoagi.csv\", index_col=None)\n",
    "\n",
    "income_noagi = pd.DataFrame(income_noagi, columns=[\"ZIPCODE\", \"ELDERLY\"])\n",
    "income_noagi = income_noagi.rename(index=str, columns={\"ZIPCODE\": \"zip\",\n",
    "                                                       \"ELDERLY\": \"num_elderly_tax_returns\"})\n",
    "income_noagi[\"zip\"] = income_noagi[\"zip\"].apply(lambda x: str(x).zfill(5))\n",
    "\n",
    "zip_data = pd.merge(zip_data, income_noagi, how=\"left\", on=\"zip\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Some random samples to see what the dataframe looks like after adding the population, median income, and income tax information:"
   ]
  },
  {
   "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>zip</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>population</th>\n",
       "      <th>pop_45-49</th>\n",
       "      <th>pop_50-54</th>\n",
       "      <th>pop_55-59</th>\n",
       "      <th>pop_60-64</th>\n",
       "      <th>...</th>\n",
       "      <th>pop_75-inf</th>\n",
       "      <th>median_indiv_income</th>\n",
       "      <th>life_expectancy</th>\n",
       "      <th>num_tax_returns_0-25k</th>\n",
       "      <th>num_tax_returns_25k-50k</th>\n",
       "      <th>num_tax_returns_50k-75k</th>\n",
       "      <th>num_tax_returns_75k-100k</th>\n",
       "      <th>num_tax_returns_100k-200k</th>\n",
       "      <th>num_tax_returns_200k-inf</th>\n",
       "      <th>num_elderly_tax_returns</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>28530</th>\n",
       "      <td>62081</td>\n",
       "      <td>Rockbridge</td>\n",
       "      <td>IL</td>\n",
       "      <td>39.261499</td>\n",
       "      <td>-90.23039</td>\n",
       "      <td>386.0</td>\n",
       "      <td>37.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>...</td>\n",
       "      <td>26.0</td>\n",
       "      <td>39000.0</td>\n",
       "      <td>83.2</td>\n",
       "      <td>60.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27866</th>\n",
       "      <td>60683</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>IL</td>\n",
       "      <td>41.811929</td>\n",
       "      <td>-87.68732</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37099</th>\n",
       "      <td>80755</td>\n",
       "      <td>Vernon</td>\n",
       "      <td>CO</td>\n",
       "      <td>39.940858</td>\n",
       "      <td>-102.36417</td>\n",
       "      <td>162.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>-666666666.0</td>\n",
       "      <td>78.7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27619</th>\n",
       "      <td>60192</td>\n",
       "      <td>Schaumburg</td>\n",
       "      <td>IL</td>\n",
       "      <td>42.065827</td>\n",
       "      <td>-88.21399</td>\n",
       "      <td>16026.0</td>\n",
       "      <td>1266.0</td>\n",
       "      <td>1276.0</td>\n",
       "      <td>1565.0</td>\n",
       "      <td>1383.0</td>\n",
       "      <td>...</td>\n",
       "      <td>523.0</td>\n",
       "      <td>75594.0</td>\n",
       "      <td>85.2</td>\n",
       "      <td>1860.0</td>\n",
       "      <td>1090.0</td>\n",
       "      <td>830.0</td>\n",
       "      <td>830.0</td>\n",
       "      <td>2240.0</td>\n",
       "      <td>840.0</td>\n",
       "      <td>1940.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34970</th>\n",
       "      <td>76622</td>\n",
       "      <td>Aquilla</td>\n",
       "      <td>TX</td>\n",
       "      <td>31.824495</td>\n",
       "      <td>-97.23306</td>\n",
       "      <td>1110.0</td>\n",
       "      <td>55.0</td>\n",
       "      <td>101.0</td>\n",
       "      <td>117.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>...</td>\n",
       "      <td>65.0</td>\n",
       "      <td>31250.0</td>\n",
       "      <td>74.9</td>\n",
       "      <td>160.0</td>\n",
       "      <td>140.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>130.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43149</th>\n",
       "      <td>97525</td>\n",
       "      <td>Gold Hill</td>\n",
       "      <td>OR</td>\n",
       "      <td>42.432422</td>\n",
       "      <td>-123.08639</td>\n",
       "      <td>4435.0</td>\n",
       "      <td>227.0</td>\n",
       "      <td>390.0</td>\n",
       "      <td>480.0</td>\n",
       "      <td>523.0</td>\n",
       "      <td>...</td>\n",
       "      <td>367.0</td>\n",
       "      <td>32917.0</td>\n",
       "      <td>80.2</td>\n",
       "      <td>900.0</td>\n",
       "      <td>600.0</td>\n",
       "      <td>340.0</td>\n",
       "      <td>210.0</td>\n",
       "      <td>220.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>840.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8128</th>\n",
       "      <td>19025</td>\n",
       "      <td>Dresher</td>\n",
       "      <td>PA</td>\n",
       "      <td>40.149164</td>\n",
       "      <td>-75.16047</td>\n",
       "      <td>5426.0</td>\n",
       "      <td>459.0</td>\n",
       "      <td>430.0</td>\n",
       "      <td>369.0</td>\n",
       "      <td>462.0</td>\n",
       "      <td>...</td>\n",
       "      <td>506.0</td>\n",
       "      <td>81964.0</td>\n",
       "      <td>81.3</td>\n",
       "      <td>750.0</td>\n",
       "      <td>370.0</td>\n",
       "      <td>300.0</td>\n",
       "      <td>250.0</td>\n",
       "      <td>720.0</td>\n",
       "      <td>600.0</td>\n",
       "      <td>1010.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22395</th>\n",
       "      <td>48853</td>\n",
       "      <td>Maple Rapids</td>\n",
       "      <td>MI</td>\n",
       "      <td>43.102399</td>\n",
       "      <td>-84.69278</td>\n",
       "      <td>664.0</td>\n",
       "      <td>53.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>...</td>\n",
       "      <td>29.0</td>\n",
       "      <td>31154.0</td>\n",
       "      <td>79.3</td>\n",
       "      <td>140.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13705</th>\n",
       "      <td>30370</td>\n",
       "      <td>Atlanta</td>\n",
       "      <td>GA</td>\n",
       "      <td>33.844371</td>\n",
       "      <td>-84.47405</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38435</th>\n",
       "      <td>85139</td>\n",
       "      <td>Maricopa</td>\n",
       "      <td>AZ</td>\n",
       "      <td>32.980000</td>\n",
       "      <td>-112.11000</td>\n",
       "      <td>18327.0</td>\n",
       "      <td>1216.0</td>\n",
       "      <td>1091.0</td>\n",
       "      <td>1063.0</td>\n",
       "      <td>1051.0</td>\n",
       "      <td>...</td>\n",
       "      <td>671.0</td>\n",
       "      <td>32396.0</td>\n",
       "      <td>78.1</td>\n",
       "      <td>2410.0</td>\n",
       "      <td>2190.0</td>\n",
       "      <td>1130.0</td>\n",
       "      <td>680.0</td>\n",
       "      <td>630.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>1400.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         zip          city state   latitude  longitude  population  pop_45-49  \\\n",
       "28530  62081    Rockbridge    IL  39.261499  -90.23039       386.0       37.0   \n",
       "27866  60683       Chicago    IL  41.811929  -87.68732         0.0        0.0   \n",
       "37099  80755        Vernon    CO  39.940858 -102.36417       162.0       17.0   \n",
       "27619  60192    Schaumburg    IL  42.065827  -88.21399     16026.0     1266.0   \n",
       "34970  76622       Aquilla    TX  31.824495  -97.23306      1110.0       55.0   \n",
       "43149  97525     Gold Hill    OR  42.432422 -123.08639      4435.0      227.0   \n",
       "8128   19025       Dresher    PA  40.149164  -75.16047      5426.0      459.0   \n",
       "22395  48853  Maple Rapids    MI  43.102399  -84.69278       664.0       53.0   \n",
       "13705  30370       Atlanta    GA  33.844371  -84.47405         0.0        0.0   \n",
       "38435  85139      Maricopa    AZ  32.980000 -112.11000     18327.0     1216.0   \n",
       "\n",
       "       pop_50-54  pop_55-59  pop_60-64           ...             pop_75-inf  \\\n",
       "28530       31.0        6.0       16.0           ...                   26.0   \n",
       "27866        0.0        0.0        NaN           ...                    NaN   \n",
       "37099        4.0        4.0       13.0           ...                    1.0   \n",
       "27619     1276.0     1565.0     1383.0           ...                  523.0   \n",
       "34970      101.0      117.0       62.0           ...                   65.0   \n",
       "43149      390.0      480.0      523.0           ...                  367.0   \n",
       "8128       430.0      369.0      462.0           ...                  506.0   \n",
       "22395       70.0       45.0       35.0           ...                   29.0   \n",
       "13705        0.0        0.0        NaN           ...                    NaN   \n",
       "38435     1091.0     1063.0     1051.0           ...                  671.0   \n",
       "\n",
       "       median_indiv_income  life_expectancy  num_tax_returns_0-25k  \\\n",
       "28530              39000.0             83.2                   60.0   \n",
       "27866                  NaN              NaN                    NaN   \n",
       "37099         -666666666.0             78.7                    NaN   \n",
       "27619              75594.0             85.2                 1860.0   \n",
       "34970              31250.0             74.9                  160.0   \n",
       "43149              32917.0             80.2                  900.0   \n",
       "8128               81964.0             81.3                  750.0   \n",
       "22395              31154.0             79.3                  140.0   \n",
       "13705                  NaN              NaN                    NaN   \n",
       "38435              32396.0             78.1                 2410.0   \n",
       "\n",
       "       num_tax_returns_25k-50k  num_tax_returns_50k-75k  \\\n",
       "28530                     60.0                      0.0   \n",
       "27866                      NaN                      NaN   \n",
       "37099                      NaN                      NaN   \n",
       "27619                   1090.0                    830.0   \n",
       "34970                    140.0                     70.0   \n",
       "43149                    600.0                    340.0   \n",
       "8128                     370.0                    300.0   \n",
       "22395                     80.0                     40.0   \n",
       "13705                      NaN                      NaN   \n",
       "38435                   2190.0                   1130.0   \n",
       "\n",
       "       num_tax_returns_75k-100k  num_tax_returns_100k-200k  \\\n",
       "28530                      30.0                        0.0   \n",
       "27866                       NaN                        NaN   \n",
       "37099                       NaN                        NaN   \n",
       "27619                     830.0                     2240.0   \n",
       "34970                      50.0                       50.0   \n",
       "43149                     210.0                      220.0   \n",
       "8128                      250.0                      720.0   \n",
       "22395                      30.0                        0.0   \n",
       "13705                       NaN                        NaN   \n",
       "38435                     680.0                      630.0   \n",
       "\n",
       "       num_tax_returns_200k-inf  num_elderly_tax_returns  \n",
       "28530                       0.0                     40.0  \n",
       "27866                       NaN                      NaN  \n",
       "37099                       NaN                      NaN  \n",
       "27619                     840.0                   1940.0  \n",
       "34970                       0.0                    130.0  \n",
       "43149                      60.0                    840.0  \n",
       "8128                      600.0                   1010.0  \n",
       "22395                       0.0                     70.0  \n",
       "13705                       NaN                      NaN  \n",
       "38435                      60.0                   1400.0  \n",
       "\n",
       "[10 rows x 22 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "zip_data.sample(n=10, random_state=2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are lots of ZIP codes with 0 population and no tax returns. ZIP codes are not really meaningful representations of geographical areas. ZIP codes designate postal delivery points, and as such, there are *lots* of delivery points in the U.S. that can't represent a geographic area containing permanent inhabitants. Some examples of these delivery points are: governmental agencies, universities, businesses, or simply any building that receives a huge amount of mail.\n",
    "\n",
    "For some reason, the API returned median income values of -6... for a number of smaller zip codes. These will have to be cleaned up prior to usage in our model."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Regional Price Parity Values\n",
    "Next, we add the Regional Price Parity for our ZIP codes. Regional Price Parity is a relative metric to describe the cost of goods, rent, and services in comparison to the national average. For more information and the source of our data, see the [Bureau of Economic Analysis's website](https://www.bea.gov/data/prices-inflation/regional-price-parities-state-and-metro-area).\n",
    "\n",
    "The RPP data was available for a limited number of CBSAs (core-based statistical area), and we roughly convert these to zip codes using [HUD data](https://www.huduser.gov/portal/datasets/usps_crosswalk.html). For zipcodes with RPP data, we used the obtained data. For ZIP codes without RPP data, we used the state-level RPP data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read in RPP CBSA-specific data and clean up\n",
    "rpp = pd.read_csv(\"RegionalPriceParities.csv\")\n",
    "rpp = rpp[rpp[\"LineCode\"]==1]\n",
    "rpp = pd.DataFrame(rpp, columns=[\"GeoFips\", \"2016\"])\n",
    "rpp = rpp.rename(index=str, columns={\"GeoFips\": \"cbsa\", \"2016\": \"rpp\"})\n",
    "\n",
    "# cbsa_to_zip to convert between CBSA and zip codes\n",
    "cbsa_to_zip = pd.read_csv(\"cbsa_to_zip.csv\")\n",
    "cbsa_to_zip[\"zip\"] = cbsa_to_zip[\"zip\"].apply(lambda x: str(x).zfill(5))\n",
    "cbsa_to_zip = pd.DataFrame(cbsa_to_zip, columns=[\"zip\", \"cbsa\"])\n",
    "\n",
    "# merge CBSAs to zip codes, drop duplicates due to redundant CBSAs (probably due to \n",
    "# abritrary geographic decisions, some zip codes have multiple CBSAs)\n",
    "zip_data = pd.merge(zip_data, cbsa_to_zip, how=\"left\", on=\"zip\")\n",
    "zip_data = zip_data.drop_duplicates(\"zip\")\n",
    "\n",
    "# merge in RPP data\n",
    "zip_data = pd.merge(zip_data, rpp, how=\"left\", on=\"cbsa\")\n",
    "# drop CBSA column\n",
    "zip_data = zip_data.loc[:, ~zip_data.columns.str.contains(\"cbsa\")]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We still need to get the state-level data for ZIP codes without metro-area RPP data. In this case, we downloaded the portioned state RPP data and used the non-metropolitan values because we have already captured data for major metropolitan areas and we want to get more accurate values for non-metro areas (which were significantly lower than metro area RPP values)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# read in\n",
    "rpp_state = pd.read_csv(\"RPP_by_state_w_portions.csv\", index_col=None)\n",
    "\n",
    "# Drop Metropolitan data\n",
    "rpp_state = rpp_state.loc[rpp_state.GeoName.str.contains(\"Nonmetropolitan\"), :]\n",
    "rpp_state = rpp_state[rpp_state[\"LineCode\"]==1]\n",
    "\n",
    "# Clean up state names\n",
    "pat = r\"^(.*)\\s\\(Nonmetropolitan Portion\\)\"\n",
    "rpp_state[\"GeoName\"] = rpp_state[\"GeoName\"].apply(lambda x: re.sub(pat, r\"\\1\", x))\n",
    "\n",
    "# Need to convert names to two letter codes\n",
    "states_dict = us.states.mapping(\"name\", \"abbr\")\n",
    "rpp_state[\"GeoName\"] = rpp_state[\"GeoName\"].apply(lambda x: states_dict[x])\n",
    "rpp_state = pd.DataFrame(rpp_state, columns=[\"GeoName\", \"2016\"])\n",
    "\n",
    "# Some states don't have nonmetro areas, setting these values manually to the metro area values\n",
    "rpp_state.loc[[64], [\"2016\"]] = 100.3\n",
    "rpp_state.loc[[72], [\"2016\"]] = 116.4\n",
    "rpp_state.loc[[248], [\"2016\"]] = 113.5\n",
    "rpp_state.loc[[320], [\"2016\"]] = 99.8\n",
    "\n",
    "# Change rpp_state to dict to allow us to easily replace NAs in the DF with the state\n",
    "# non-metro RPP \n",
    "rpp_state = rpp_state.set_index(\"GeoName\").T.to_dict(\"list\")\n",
    "\n",
    "# Fill NAs with their state abbreviation\n",
    "zip_data.rpp = zip_data.rpp.fillna(zip_data.state)\n",
    "\n",
    "# Get RPP function\n",
    "# Above, we filled NAs for the RPP variable with the containing state abbreviation, this function\n",
    "# replaces that state abbreviation with the non-metro RPP for the state\n",
    "def get_rpp_for_state(rpp):\n",
    "    if re.match(\"[^\\d]{2}\", rpp):\n",
    "        if rpp in rpp_state.keys():\n",
    "            return rpp_state[rpp][0]\n",
    "        else:\n",
    "            return np.nan\n",
    "    else:\n",
    "        return rpp\n",
    "\n",
    "zip_data[\"rpp\"] = zip_data[\"rpp\"].apply(lambda x: get_rpp_for_state(x))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, after adding the RPP data:"
   ]
  },
  {
   "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>zip</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>population</th>\n",
       "      <th>pop_45-49</th>\n",
       "      <th>pop_50-54</th>\n",
       "      <th>pop_55-59</th>\n",
       "      <th>pop_60-64</th>\n",
       "      <th>...</th>\n",
       "      <th>median_indiv_income</th>\n",
       "      <th>life_expectancy</th>\n",
       "      <th>num_tax_returns_0-25k</th>\n",
       "      <th>num_tax_returns_25k-50k</th>\n",
       "      <th>num_tax_returns_50k-75k</th>\n",
       "      <th>num_tax_returns_75k-100k</th>\n",
       "      <th>num_tax_returns_100k-200k</th>\n",
       "      <th>num_tax_returns_200k-inf</th>\n",
       "      <th>num_elderly_tax_returns</th>\n",
       "      <th>rpp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>28530</th>\n",
       "      <td>62081</td>\n",
       "      <td>Rockbridge</td>\n",
       "      <td>IL</td>\n",
       "      <td>39.261499</td>\n",
       "      <td>-90.23039</td>\n",
       "      <td>386.0</td>\n",
       "      <td>37.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>...</td>\n",
       "      <td>39000.0</td>\n",
       "      <td>83.2</td>\n",
       "      <td>60.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>84.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27866</th>\n",
       "      <td>60683</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>IL</td>\n",
       "      <td>41.811929</td>\n",
       "      <td>-87.68732</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>84.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37099</th>\n",
       "      <td>80755</td>\n",
       "      <td>Vernon</td>\n",
       "      <td>CO</td>\n",
       "      <td>39.940858</td>\n",
       "      <td>-102.36417</td>\n",
       "      <td>162.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>...</td>\n",
       "      <td>-666666666.0</td>\n",
       "      <td>78.7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>96.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27619</th>\n",
       "      <td>60192</td>\n",
       "      <td>Schaumburg</td>\n",
       "      <td>IL</td>\n",
       "      <td>42.065827</td>\n",
       "      <td>-88.21399</td>\n",
       "      <td>16026.0</td>\n",
       "      <td>1266.0</td>\n",
       "      <td>1276.0</td>\n",
       "      <td>1565.0</td>\n",
       "      <td>1383.0</td>\n",
       "      <td>...</td>\n",
       "      <td>75594.0</td>\n",
       "      <td>85.2</td>\n",
       "      <td>1860.0</td>\n",
       "      <td>1090.0</td>\n",
       "      <td>830.0</td>\n",
       "      <td>830.0</td>\n",
       "      <td>2240.0</td>\n",
       "      <td>840.0</td>\n",
       "      <td>1940.0</td>\n",
       "      <td>103.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34970</th>\n",
       "      <td>76622</td>\n",
       "      <td>Aquilla</td>\n",
       "      <td>TX</td>\n",
       "      <td>31.824495</td>\n",
       "      <td>-97.23306</td>\n",
       "      <td>1110.0</td>\n",
       "      <td>55.0</td>\n",
       "      <td>101.0</td>\n",
       "      <td>117.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>...</td>\n",
       "      <td>31250.0</td>\n",
       "      <td>74.9</td>\n",
       "      <td>160.0</td>\n",
       "      <td>140.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>130.0</td>\n",
       "      <td>87.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43149</th>\n",
       "      <td>97525</td>\n",
       "      <td>Gold Hill</td>\n",
       "      <td>OR</td>\n",
       "      <td>42.432422</td>\n",
       "      <td>-123.08639</td>\n",
       "      <td>4435.0</td>\n",
       "      <td>227.0</td>\n",
       "      <td>390.0</td>\n",
       "      <td>480.0</td>\n",
       "      <td>523.0</td>\n",
       "      <td>...</td>\n",
       "      <td>32917.0</td>\n",
       "      <td>80.2</td>\n",
       "      <td>900.0</td>\n",
       "      <td>600.0</td>\n",
       "      <td>340.0</td>\n",
       "      <td>210.0</td>\n",
       "      <td>220.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>840.0</td>\n",
       "      <td>97.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8128</th>\n",
       "      <td>19025</td>\n",
       "      <td>Dresher</td>\n",
       "      <td>PA</td>\n",
       "      <td>40.149164</td>\n",
       "      <td>-75.16047</td>\n",
       "      <td>5426.0</td>\n",
       "      <td>459.0</td>\n",
       "      <td>430.0</td>\n",
       "      <td>369.0</td>\n",
       "      <td>462.0</td>\n",
       "      <td>...</td>\n",
       "      <td>81964.0</td>\n",
       "      <td>81.3</td>\n",
       "      <td>750.0</td>\n",
       "      <td>370.0</td>\n",
       "      <td>300.0</td>\n",
       "      <td>250.0</td>\n",
       "      <td>720.0</td>\n",
       "      <td>600.0</td>\n",
       "      <td>1010.0</td>\n",
       "      <td>105.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22395</th>\n",
       "      <td>48853</td>\n",
       "      <td>Maple Rapids</td>\n",
       "      <td>MI</td>\n",
       "      <td>43.102399</td>\n",
       "      <td>-84.69278</td>\n",
       "      <td>664.0</td>\n",
       "      <td>53.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>...</td>\n",
       "      <td>31154.0</td>\n",
       "      <td>79.3</td>\n",
       "      <td>140.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>93.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13705</th>\n",
       "      <td>30370</td>\n",
       "      <td>Atlanta</td>\n",
       "      <td>GA</td>\n",
       "      <td>33.844371</td>\n",
       "      <td>-84.47405</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>83.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38435</th>\n",
       "      <td>85139</td>\n",
       "      <td>Maricopa</td>\n",
       "      <td>AZ</td>\n",
       "      <td>32.980000</td>\n",
       "      <td>-112.11000</td>\n",
       "      <td>18327.0</td>\n",
       "      <td>1216.0</td>\n",
       "      <td>1091.0</td>\n",
       "      <td>1063.0</td>\n",
       "      <td>1051.0</td>\n",
       "      <td>...</td>\n",
       "      <td>32396.0</td>\n",
       "      <td>78.1</td>\n",
       "      <td>2410.0</td>\n",
       "      <td>2190.0</td>\n",
       "      <td>1130.0</td>\n",
       "      <td>680.0</td>\n",
       "      <td>630.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>1400.0</td>\n",
       "      <td>97.1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         zip          city state   latitude  longitude  population  pop_45-49  \\\n",
       "28530  62081    Rockbridge    IL  39.261499  -90.23039       386.0       37.0   \n",
       "27866  60683       Chicago    IL  41.811929  -87.68732         0.0        0.0   \n",
       "37099  80755        Vernon    CO  39.940858 -102.36417       162.0       17.0   \n",
       "27619  60192    Schaumburg    IL  42.065827  -88.21399     16026.0     1266.0   \n",
       "34970  76622       Aquilla    TX  31.824495  -97.23306      1110.0       55.0   \n",
       "43149  97525     Gold Hill    OR  42.432422 -123.08639      4435.0      227.0   \n",
       "8128   19025       Dresher    PA  40.149164  -75.16047      5426.0      459.0   \n",
       "22395  48853  Maple Rapids    MI  43.102399  -84.69278       664.0       53.0   \n",
       "13705  30370       Atlanta    GA  33.844371  -84.47405         0.0        0.0   \n",
       "38435  85139      Maricopa    AZ  32.980000 -112.11000     18327.0     1216.0   \n",
       "\n",
       "       pop_50-54  pop_55-59  pop_60-64  ...    median_indiv_income  \\\n",
       "28530       31.0        6.0       16.0  ...                39000.0   \n",
       "27866        0.0        0.0        NaN  ...                    NaN   \n",
       "37099        4.0        4.0       13.0  ...           -666666666.0   \n",
       "27619     1276.0     1565.0     1383.0  ...                75594.0   \n",
       "34970      101.0      117.0       62.0  ...                31250.0   \n",
       "43149      390.0      480.0      523.0  ...                32917.0   \n",
       "8128       430.0      369.0      462.0  ...                81964.0   \n",
       "22395       70.0       45.0       35.0  ...                31154.0   \n",
       "13705        0.0        0.0        NaN  ...                    NaN   \n",
       "38435     1091.0     1063.0     1051.0  ...                32396.0   \n",
       "\n",
       "       life_expectancy  num_tax_returns_0-25k  num_tax_returns_25k-50k  \\\n",
       "28530             83.2                   60.0                     60.0   \n",
       "27866              NaN                    NaN                      NaN   \n",
       "37099             78.7                    NaN                      NaN   \n",
       "27619             85.2                 1860.0                   1090.0   \n",
       "34970             74.9                  160.0                    140.0   \n",
       "43149             80.2                  900.0                    600.0   \n",
       "8128              81.3                  750.0                    370.0   \n",
       "22395             79.3                  140.0                     80.0   \n",
       "13705              NaN                    NaN                      NaN   \n",
       "38435             78.1                 2410.0                   2190.0   \n",
       "\n",
       "       num_tax_returns_50k-75k  num_tax_returns_75k-100k  \\\n",
       "28530                      0.0                      30.0   \n",
       "27866                      NaN                       NaN   \n",
       "37099                      NaN                       NaN   \n",
       "27619                    830.0                     830.0   \n",
       "34970                     70.0                      50.0   \n",
       "43149                    340.0                     210.0   \n",
       "8128                     300.0                     250.0   \n",
       "22395                     40.0                      30.0   \n",
       "13705                      NaN                       NaN   \n",
       "38435                   1130.0                     680.0   \n",
       "\n",
       "       num_tax_returns_100k-200k  num_tax_returns_200k-inf  \\\n",
       "28530                        0.0                       0.0   \n",
       "27866                        NaN                       NaN   \n",
       "37099                        NaN                       NaN   \n",
       "27619                     2240.0                     840.0   \n",
       "34970                       50.0                       0.0   \n",
       "43149                      220.0                      60.0   \n",
       "8128                       720.0                     600.0   \n",
       "22395                        0.0                       0.0   \n",
       "13705                        NaN                       NaN   \n",
       "38435                      630.0                      60.0   \n",
       "\n",
       "       num_elderly_tax_returns    rpp  \n",
       "28530                     40.0   84.6  \n",
       "27866                      NaN   84.6  \n",
       "37099                      NaN   96.4  \n",
       "27619                   1940.0  103.8  \n",
       "34970                    130.0   87.3  \n",
       "43149                    840.0   97.6  \n",
       "8128                    1010.0  105.9  \n",
       "22395                     70.0   93.0  \n",
       "13705                      NaN   83.7  \n",
       "38435                   1400.0   97.1  \n",
       "\n",
       "[10 rows x 23 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "zip_data.sample(n=10, random_state=2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Cleanup and Feature Engineering\n",
    "We still need to do some cleanup and some minor feature engineering. It is probably good to know the frequency of age groups in populations, in addition to the total counts. We also need to fix the extreme negative values for median individual income that the Census API sometimes returned. \n",
    "\n",
    "We have *lots* of NAs. For the purposes of this competition, we are just going to fill them with 0.0. The ZIP codes with NA values for variables almost exclusively are those ZIP codes with 0 population - and these zip codes are not important for our scoring model."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get relative age group frequencies\n",
    "zip_data[\"freq_pop_45-49\"] = zip_data[\"pop_45-49\"] / zip_data[\"population\"]\n",
    "zip_data[\"freq_pop_50-54\"] = zip_data[\"pop_50-54\"] / zip_data[\"population\"]\n",
    "zip_data[\"freq_pop_55-59\"] = zip_data[\"pop_55-59\"] / zip_data[\"population\"]\n",
    "zip_data[\"freq_pop_60-64\"] = zip_data[\"pop_60-64\"] / zip_data[\"population\"]\n",
    "zip_data[\"freq_pop_65-69\"] = zip_data[\"pop_65-69\"] / zip_data[\"population\"]\n",
    "zip_data[\"freq_pop_70-74\"] = zip_data[\"pop_70-74\"] / zip_data[\"population\"]\n",
    "zip_data[\"freq_pop_75-inf\"] = zip_data[\"pop_75-inf\"] / zip_data[\"population\"]\n",
    "\n",
    "# Set negative median individual incomes to 0\n",
    "zip_data[\"median_indiv_income\"] = zip_data[\"median_indiv_income\"].clip(lower=0)\n",
    "\n",
    "# Fill NAs with 0.0\n",
    "zip_data = zip_data.fillna(0.0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This leaves us with the following features for the final dataset:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 44336 entries, 0 to 44335\n",
      "Data columns (total 30 columns):\n",
      "zip                          44336 non-null object\n",
      "city                         44336 non-null object\n",
      "state                        44336 non-null object\n",
      "latitude                     44336 non-null float64\n",
      "longitude                    44336 non-null float64\n",
      "population                   44336 non-null float64\n",
      "pop_45-49                    44336 non-null float64\n",
      "pop_50-54                    44336 non-null float64\n",
      "pop_55-59                    44336 non-null float64\n",
      "pop_60-64                    44336 non-null float64\n",
      "pop_65-69                    44336 non-null float64\n",
      "pop_70-74                    44336 non-null float64\n",
      "pop_75-inf                   44336 non-null float64\n",
      "median_indiv_income          44336 non-null float64\n",
      "life_expectancy              44336 non-null float64\n",
      "num_tax_returns_0-25k        44336 non-null float64\n",
      "num_tax_returns_25k-50k      44336 non-null float64\n",
      "num_tax_returns_50k-75k      44336 non-null float64\n",
      "num_tax_returns_75k-100k     44336 non-null float64\n",
      "num_tax_returns_100k-200k    44336 non-null float64\n",
      "num_tax_returns_200k-inf     44336 non-null float64\n",
      "num_elderly_tax_returns      44336 non-null float64\n",
      "rpp                          44336 non-null object\n",
      "freq_pop_45-49               44336 non-null float64\n",
      "freq_pop_50-54               44336 non-null float64\n",
      "freq_pop_55-59               44336 non-null float64\n",
      "freq_pop_60-64               44336 non-null float64\n",
      "freq_pop_65-69               44336 non-null float64\n",
      "freq_pop_70-74               44336 non-null float64\n",
      "freq_pop_75-inf              44336 non-null float64\n",
      "dtypes: float64(26), object(4)\n",
      "memory usage: 10.5+ MB\n"
     ]
    }
   ],
   "source": [
    "zip_data.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save everything\n",
    "zip_data.to_csv(\"zip_data_final.csv\")"
   ]
  }
 ],
 "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.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}