notebooks/dataprep/00m-USHUDCrosswalk.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Mapping of US Census tracts to US Postal Service ZIP codes and US Counties"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**[Work in progress]**\n",
    "\n",
    "This notebook creates .csv files with mappings between US Postal Service ZIP codes and US Census Tracts provide by the U.S. Department of Housing and Urban Development (HUD), and mappings between US Counties and US Census Tracts provided by the US Census American Community Survey 5-Year Data (2009-2018).\n",
    "\n",
    "Reference: Wilson, Ron and Din, Alexander, 2018. “Understanding and Enhancing the U.S. Department of Housing and Urban Development’s ZIP Code Crosswalk Files”, [Cityscape: A Journal of Policy Development and Research, Volume 20 Number 2, 277 – 294.]( https://www.huduser.gov/portal/periodicals/cityscpe/vol20num2/ch16.pdf)\n",
    "\n",
    "Data sources: \n",
    "\n",
    "[HUD USPS ZIP CODE CROSSWALK FILES](https://www.huduser.gov/portal/datasets/usps_crosswalk.html)\n",
    "\n",
    "[American Community Survey 5-Year Data (2009-2018)](https://www.census.gov/data/developers/data-sets/acs-5year.html)\n",
    "\n",
    "Author: Peter Rose (pwrose@ucsd.edu)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import time\n",
    "from pathlib import Path\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.options.display.max_rows = None  # display all rows\n",
    "pd.options.display.max_columns = None  # display all columsns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "/Users/peter/Library/Application Support/com.Neo4j.Relate/data/dbmss/dbms-8bf637fc-0d20-4d9f-9c6f-f7e72e92a4da/import\n"
     ]
    }
   ],
   "source": [
    "NEO4J_IMPORT = Path(os.getenv('NEO4J_IMPORT'))\n",
    "print(NEO4J_IMPORT)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2020Q1 USPS ZIP code to US County mappings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "zip_to_county_url = 'https://www.huduser.gov/portal/datasets/usps/ZIP_COUNTY_032020.xlsx'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "zip_to_county = pd.read_excel(zip_to_county_url, dtype='str')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>COUNTY</th>\n",
       "      <th>RES_RATIO</th>\n",
       "      <th>BUS_RATIO</th>\n",
       "      <th>OTH_RATIO</th>\n",
       "      <th>TOT_RATIO</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00501</td>\n",
       "      <td>36103</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>00601</td>\n",
       "      <td>72113</td>\n",
       "      <td>0.16072360410506176</td>\n",
       "      <td>0.20098039215686275</td>\n",
       "      <td>0.12883435582822086</td>\n",
       "      <td>0.1625</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>00601</td>\n",
       "      <td>72001</td>\n",
       "      <td>0.8392763958949383</td>\n",
       "      <td>0.7990196078431373</td>\n",
       "      <td>0.8711656441717791</td>\n",
       "      <td>0.8375</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>00602</td>\n",
       "      <td>72003</td>\n",
       "      <td>1</td>\n",
       "      <td>0.9987995198079231</td>\n",
       "      <td>1</td>\n",
       "      <td>0.9999193483345431</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>00602</td>\n",
       "      <td>72005</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0012004801920768306</td>\n",
       "      <td>0</td>\n",
       "      <td>8.065166545689168e-05</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     ZIP COUNTY            RES_RATIO              BUS_RATIO  \\\n",
       "0  00501  36103                    0                      1   \n",
       "1  00601  72113  0.16072360410506176    0.20098039215686275   \n",
       "2  00601  72001   0.8392763958949383     0.7990196078431373   \n",
       "3  00602  72003                    1     0.9987995198079231   \n",
       "4  00602  72005                    0  0.0012004801920768306   \n",
       "\n",
       "             OTH_RATIO              TOT_RATIO  \n",
       "0                    0                      1  \n",
       "1  0.12883435582822086                 0.1625  \n",
       "2   0.8711656441717791                 0.8375  \n",
       "3                    1     0.9999193483345431  \n",
       "4                    0  8.065166545689168e-05  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "zip_to_county.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "zip_to_county.rename(columns={'COUNTY': 'countyFips', 'ZIP': 'zip', 'RES_RATIO': 'resRatio'}, inplace=True)\n",
    "zip_to_county.rename(columns={'BUS_RATIO': 'busRatio', 'OTH_RATIO': 'othRatio', 'TOT_RATIO': 'totRatio'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "zip_to_county['stateFips'] = zip_to_county['countyFips'].str[:2]\n",
    "zip_to_county['countyFips'] = zip_to_county['countyFips'].str[2:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>countyFips</th>\n",
       "      <th>resRatio</th>\n",
       "      <th>busRatio</th>\n",
       "      <th>othRatio</th>\n",
       "      <th>totRatio</th>\n",
       "      <th>stateFips</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>50514</th>\n",
       "      <td>92301</td>\n",
       "      <td>037</td>\n",
       "      <td>7.441029838529652e-05</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>6.807351940095303e-05</td>\n",
       "      <td>06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50515</th>\n",
       "      <td>92301</td>\n",
       "      <td>071</td>\n",
       "      <td>0.9999255897016147</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0.999931926480599</td>\n",
       "      <td>06</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         zip countyFips               resRatio busRatio othRatio  \\\n",
       "50514  92301        037  7.441029838529652e-05        0        0   \n",
       "50515  92301        071     0.9999255897016147        1        1   \n",
       "\n",
       "                    totRatio stateFips  \n",
       "50514  6.807351940095303e-05        06  \n",
       "50515      0.999931926480599        06  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "zip_to_county.query(\"zip == '92301'\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Example of one Zip area that maps to multiple counties."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>zip</th>\n",
       "      <th>countyFips</th>\n",
       "      <th>resRatio</th>\n",
       "      <th>busRatio</th>\n",
       "      <th>othRatio</th>\n",
       "      <th>totRatio</th>\n",
       "      <th>stateFips</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>9710</th>\n",
       "      <td>21771</td>\n",
       "      <td>027</td>\n",
       "      <td>0.10808996307485734</td>\n",
       "      <td>0.035147392290249435</td>\n",
       "      <td>0.024193548387096774</td>\n",
       "      <td>0.10230614455966569</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9711</th>\n",
       "      <td>21771</td>\n",
       "      <td>013</td>\n",
       "      <td>0.46273917421953675</td>\n",
       "      <td>0.8140589569160998</td>\n",
       "      <td>0.7580645161290323</td>\n",
       "      <td>0.48955270082030644</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9712</th>\n",
       "      <td>21771</td>\n",
       "      <td>031</td>\n",
       "      <td>0.011581067472306143</td>\n",
       "      <td>0.003401360544217687</td>\n",
       "      <td>0</td>\n",
       "      <td>0.010911623587679926</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9713</th>\n",
       "      <td>21771</td>\n",
       "      <td>021</td>\n",
       "      <td>0.41758979523329975</td>\n",
       "      <td>0.1473922902494331</td>\n",
       "      <td>0.21774193548387097</td>\n",
       "      <td>0.3972295310323479</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        zip countyFips              resRatio              busRatio  \\\n",
       "9710  21771        027   0.10808996307485734  0.035147392290249435   \n",
       "9711  21771        013   0.46273917421953675    0.8140589569160998   \n",
       "9712  21771        031  0.011581067472306143  0.003401360544217687   \n",
       "9713  21771        021   0.41758979523329975    0.1473922902494331   \n",
       "\n",
       "                  othRatio              totRatio stateFips  \n",
       "9710  0.024193548387096774   0.10230614455966569        24  \n",
       "9711    0.7580645161290323   0.48955270082030644        24  \n",
       "9712                     0  0.010911623587679926        24  \n",
       "9713   0.21774193548387097    0.3972295310323479        24  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "zip_to_county.query(\"zip == '21771'\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "zip_to_county.to_csv(NEO4J_IMPORT / \"00m-USHUDCrosswalkZipToCounty2020Q1.csv\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2020Q1 USPS ZIP to US Census Tract mappings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "zip_to_tract_url = 'https://www.huduser.gov/portal/datasets/usps/ZIP_TRACT_032020.xlsx'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "zip_to_tract = pd.read_excel(zip_to_tract_url, dtype='str')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "zip_to_tract.rename(columns={'TRACT': 'tract', 'ZIP': 'zip', 'RES_RATIO': 'resRatio'}, inplace=True)\n",
    "zip_to_tract.rename(columns={'BUS_RATIO': 'busRatio', 'OTH_RATIO': 'othRatio', 'TOT_RATIO': 'totRatio'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>zip</th>\n",
       "      <th>tract</th>\n",
       "      <th>resRatio</th>\n",
       "      <th>busRatio</th>\n",
       "      <th>othRatio</th>\n",
       "      <th>totRatio</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00501</td>\n",
       "      <td>36103158607</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>00601</td>\n",
       "      <td>72001956800</td>\n",
       "      <td>0.01481610597873453</td>\n",
       "      <td>0.01256281407035176</td>\n",
       "      <td>0.0440251572327044</td>\n",
       "      <td>0.015411503018748014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>00601</td>\n",
       "      <td>72113071700</td>\n",
       "      <td>0.16105978734530244</td>\n",
       "      <td>0.20603015075376885</td>\n",
       "      <td>0.1320754716981132</td>\n",
       "      <td>0.16317127422942485</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>00601</td>\n",
       "      <td>72001956600</td>\n",
       "      <td>0.17291267212829003</td>\n",
       "      <td>0.3542713567839196</td>\n",
       "      <td>0.3584905660377358</td>\n",
       "      <td>0.1890689545598983</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>00601</td>\n",
       "      <td>72001956700</td>\n",
       "      <td>0.651211434547673</td>\n",
       "      <td>0.4271356783919598</td>\n",
       "      <td>0.46540880503144655</td>\n",
       "      <td>0.6323482681919288</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     zip        tract             resRatio             busRatio  \\\n",
       "0  00501  36103158607                    0                    1   \n",
       "1  00601  72001956800  0.01481610597873453  0.01256281407035176   \n",
       "2  00601  72113071700  0.16105978734530244  0.20603015075376885   \n",
       "3  00601  72001956600  0.17291267212829003   0.3542713567839196   \n",
       "4  00601  72001956700    0.651211434547673   0.4271356783919598   \n",
       "\n",
       "              othRatio              totRatio  \n",
       "0                    0                     1  \n",
       "1   0.0440251572327044  0.015411503018748014  \n",
       "2   0.1320754716981132   0.16317127422942485  \n",
       "3   0.3584905660377358    0.1890689545598983  \n",
       "4  0.46540880503144655    0.6323482681919288  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "zip_to_tract.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>zip</th>\n",
       "      <th>tract</th>\n",
       "      <th>resRatio</th>\n",
       "      <th>busRatio</th>\n",
       "      <th>othRatio</th>\n",
       "      <th>totRatio</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>156595</th>\n",
       "      <td>92121</td>\n",
       "      <td>06073008346</td>\n",
       "      <td>0.5238353196099675</td>\n",
       "      <td>0.2380638788277906</td>\n",
       "      <td>0.2620481927710843</td>\n",
       "      <td>0.29728601252609604</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156596</th>\n",
       "      <td>92121</td>\n",
       "      <td>06073008350</td>\n",
       "      <td>0</td>\n",
       "      <td>0.49078037537043134</td>\n",
       "      <td>0.4716867469879518</td>\n",
       "      <td>0.39290187891440503</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156597</th>\n",
       "      <td>92121</td>\n",
       "      <td>06073009400</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00032927230819888045</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00020876826722338206</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156598</th>\n",
       "      <td>92121</td>\n",
       "      <td>06073008339</td>\n",
       "      <td>0.4761646803900325</td>\n",
       "      <td>0.2708264734935792</td>\n",
       "      <td>0.26626506024096386</td>\n",
       "      <td>0.3096033402922756</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          zip        tract            resRatio                busRatio  \\\n",
       "156595  92121  06073008346  0.5238353196099675      0.2380638788277906   \n",
       "156596  92121  06073008350                   0     0.49078037537043134   \n",
       "156597  92121  06073009400                   0  0.00032927230819888045   \n",
       "156598  92121  06073008339  0.4761646803900325      0.2708264734935792   \n",
       "\n",
       "                   othRatio                totRatio  \n",
       "156595   0.2620481927710843     0.29728601252609604  \n",
       "156596   0.4716867469879518     0.39290187891440503  \n",
       "156597                    0  0.00020876826722338206  \n",
       "156598  0.26626506024096386      0.3096033402922756  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "zip_to_tract.query(\"zip == '92121'\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "zip_to_tract.to_csv(NEO4J_IMPORT / \"00m-USHUDCrosswalkZipToTract2020Q1.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "73467"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(zip_to_tract['tract'].unique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Census Tract to County mappings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "url_county = f'https://api.census.gov/data/2018/acs/acs5/profile?get=NAME&for=county:*'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_json(url_county, dtype='str')\n",
    "# skip first row of labels\n",
    "df = df[1:].copy() \n",
    "columns = []\n",
    "columns.append('Name')\n",
    "columns.append('stateFips')\n",
    "columns.append('countyFips')\n",
    "df.columns = columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Remove Puerto Rico (stateFips = 72) to limit data to US States.\n",
    "# GeoNames represents Puerto Rico as a country, rather than a US state.\n",
    "# TODO how to handle data for Puerto Rico?\n",
    "df.query(\"stateFips != '72'\", inplace=True) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['01', '02', '04', '05', '06', '08', '09', '10', '11', '12', '13', '15', '16', '17', '18', '19', '20', '21', '22', '23', '24', '25', '26', '27', '28', '29', '30', '31', '32', '33', '34', '35', '36', '37', '38', '39', '40', '41', '42', '44', '45', '46', '47', '48', '49', '50', '51', '53', '54', '55', '56']\n"
     ]
    }
   ],
   "source": [
    "stateFips = list(df['stateFips'].unique())\n",
    "stateFips.sort()\n",
    "print(stateFips)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_tract_data(state):\n",
    "    url_tract = f'https://api.census.gov/data/2018/acs/acs5/profile?get=NAME&for=tract:*&in=state:{state}'\n",
    "    df = pd.read_json(url_tract, dtype='str')\n",
    "    # skip first row of labels\n",
    "    df = df[1:].copy()\n",
    "    # Add column names\n",
    "    columns = []\n",
    "    columns.append('NAME')\n",
    "    columns.append('stateFips')\n",
    "    columns.append('countyFips')\n",
    "    columns.append('tract')\n",
    "    df.columns = columns\n",
    "    time.sleep(1) # time delay to avoid timeouts\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "tract_to_county = pd.concat((get_tract_data(state) for state in stateFips))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "tract_to_county['tract'] = tract_to_county['stateFips'] + tract_to_county['countyFips'] + tract_to_county['tract']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NAME</th>\n",
       "      <th>stateFips</th>\n",
       "      <th>countyFips</th>\n",
       "      <th>tract</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Census Tract 57.01, Jefferson County, Alabama</td>\n",
       "      <td>01</td>\n",
       "      <td>073</td>\n",
       "      <td>01073005701</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Census Tract 107.04, Jefferson County, Alabama</td>\n",
       "      <td>01</td>\n",
       "      <td>073</td>\n",
       "      <td>01073010704</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Census Tract 129.08, Jefferson County, Alabama</td>\n",
       "      <td>01</td>\n",
       "      <td>073</td>\n",
       "      <td>01073012908</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Census Tract 53.02, Jefferson County, Alabama</td>\n",
       "      <td>01</td>\n",
       "      <td>073</td>\n",
       "      <td>01073005302</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Census Tract 111.07, Jefferson County, Alabama</td>\n",
       "      <td>01</td>\n",
       "      <td>073</td>\n",
       "      <td>01073011107</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             NAME stateFips countyFips  \\\n",
       "1   Census Tract 57.01, Jefferson County, Alabama        01        073   \n",
       "2  Census Tract 107.04, Jefferson County, Alabama        01        073   \n",
       "3  Census Tract 129.08, Jefferson County, Alabama        01        073   \n",
       "4   Census Tract 53.02, Jefferson County, Alabama        01        073   \n",
       "5  Census Tract 111.07, Jefferson County, Alabama        01        073   \n",
       "\n",
       "         tract  \n",
       "1  01073005701  \n",
       "2  01073010704  \n",
       "3  01073012908  \n",
       "4  01073005302  \n",
       "5  01073011107  "
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tract_to_county.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "tract_to_county.to_csv(NEO4J_IMPORT / \"00m-USHUDCrosswalkTractToCounty.csv\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Data checks"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Tracts in San Diego county"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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>NAME</th>\n",
       "      <th>stateFips</th>\n",
       "      <th>countyFips</th>\n",
       "      <th>tract</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>Census Tract 83.24, San Diego County, California</td>\n",
       "      <td>06</td>\n",
       "      <td>073</td>\n",
       "      <td>06073008324</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>Census Tract 83.39, San Diego County, California</td>\n",
       "      <td>06</td>\n",
       "      <td>073</td>\n",
       "      <td>06073008339</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>Census Tract 83.47, San Diego County, California</td>\n",
       "      <td>06</td>\n",
       "      <td>073</td>\n",
       "      <td>06073008347</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>Census Tract 83.54, San Diego County, California</td>\n",
       "      <td>06</td>\n",
       "      <td>073</td>\n",
       "      <td>06073008354</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td>Census Tract 85.05, San Diego County, California</td>\n",
       "      <td>06</td>\n",
       "      <td>073</td>\n",
       "      <td>06073008505</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                NAME stateFips countyFips  \\\n",
       "56  Census Tract 83.24, San Diego County, California        06        073   \n",
       "57  Census Tract 83.39, San Diego County, California        06        073   \n",
       "58  Census Tract 83.47, San Diego County, California        06        073   \n",
       "59  Census Tract 83.54, San Diego County, California        06        073   \n",
       "60  Census Tract 85.05, San Diego County, California        06        073   \n",
       "\n",
       "          tract  \n",
       "56  06073008324  \n",
       "57  06073008339  \n",
       "58  06073008347  \n",
       "59  06073008354  \n",
       "60  06073008505  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tract_to_county[(tract_to_county['stateFips'] == '06') & (tract_to_county['countyFips'] == '073')].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Number of counties in US states"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3142"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tract_to_county['f'] = tract_to_county['countyFips'] + tract_to_county['stateFips']\n",
    "len(tract_to_county['f'].unique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Number of tracts in US states"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "73056"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(tract_to_county['tract'].unique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Tracts in common between data from HUD vs ACS 2018 5-year\n",
    "\n",
    "TODO Where does this difference come from??? None of the HUD tracts (different dates) match the ACS 2018 5-year data exactly."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "intersection = zip_to_tract.merge(tract_to_county, left_on='tract', right_on='tract', how='inner')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "72616"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(intersection['tract'].unique())"
   ]
  }
 ],
 "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.7.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}