notebooks/dataprep/03a-USCensusDP03Income.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Selected Economic Characteristics: Income from the American Community Survey\n",
    "\n",
    "**[Work in progress]**\n",
    "\n",
    "This notebook downloads [selected economic characteristics (DP03)](https://data.census.gov/cedsci/table?tid=ACSDP5Y2018.DP03) from the American Community Survey 2018 5-Year Data.\n",
    "\n",
    "Data source: [American Community Survey 5-Year Data 2018](https://www.census.gov/data/developers/data-sets/acs-5year.html)\n",
    "\n",
    "Authors: Peter Rose (pwrose@ucsd.edu), Ilya Zaslavsky (zaslavsk@sdsc.edu)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "from pathlib import Path\n",
    "import time"
   ]
  },
  {
   "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": [
    "## Download selected variables\n",
    "\n",
    "* [Selected economic characteristics for US](https://data.census.gov/cedsci/table?tid=ACSDP5Y2018.DP03)\n",
    "\n",
    "* [List of variables as HTML](https://api.census.gov/data/2018/acs/acs5/profile/groups/DP03.html) or [JSON](https://api.census.gov/data/2018/acs/acs5/profile/groups/DP03/)\n",
    "\n",
    "* [Description of variables](https://www2.census.gov/programs-surveys/acs/tech_docs/subject_definitions/2018_ACSSubjectDefinitions.pdf)\n",
    "\n",
    "* [Example URLs for API](https://api.census.gov/data/2018/acs/acs5/profile/examples.html)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Specify variables from DP03 group and assign property names\n",
    "\n",
    "Names must follow the [Neo4j property naming conventions](https://neo4j.com/docs/getting-started/current/graphdb-concepts/#graphdb-naming-rules-and-recommendations)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "variables = {# INCOME AND BENEFITS\n",
    "             'DP03_0051E': 'totalHouseholds',\n",
    "             'DP03_0052E': 'householdIncomeLessThan10000USD',\n",
    "             'DP03_0052PE': 'householdIncomeLessThan10000USDPct',\n",
    "             'DP03_0053E': 'householdIncome10000To14999USD',\n",
    "             'DP03_0053PE': 'householdIncome10000To14999USDPct',\n",
    "             'DP03_0054E': 'householdIncome15000To24999USD',\n",
    "             'DP03_0054PE': 'householdIncome15000To24999USDPct',\n",
    "             'DP03_0055E': 'householdIncome25000To34999USD',\n",
    "             'DP03_0055PE': 'householdIncome25000To34999USDPct',\n",
    "             'DP03_0056E': 'householdIncome35000To49999USD',\n",
    "             'DP03_0056PE': 'householdIncome35000To49999USDPct',\n",
    "             'DP03_0057E': 'householdIncome50000To74999USD',\n",
    "             'DP03_0057PE': 'householdIncome50000To74999USDPct',\n",
    "             'DP03_0058E': 'householdIncome75000To99999USD',\n",
    "             'DP03_0058PE': 'householdIncome75000To99999USDPct',\n",
    "             'DP03_0059E': 'householdIncome100000To149999USD',\n",
    "             'DP03_0059PE': 'householdIncome100000To149999USDPct',\n",
    "             'DP03_0060E': 'householdIncome150000To199999USD',\n",
    "             'DP03_0060PE': 'householdIncome150000To199999USDPct',\n",
    "             'DP03_0061E': 'householdIncomeMoreThan200000USD',\n",
    "             'DP03_0061PE': 'householdIncomeMoreThan200000USDPct',\n",
    "             'DP03_0062E': 'medianHouseholdIncomeUSD',\n",
    "             'DP03_0063E': 'meanHouseholdIncomeUSD',\n",
    "            }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "fields = \",\".join(variables.keys())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "i.totalHouseholds = toInteger(row.totalHouseholds),\n",
      "i.householdIncomeLessThan10000USD = toInteger(row.householdIncomeLessThan10000USD),\n",
      "i.householdIncomeLessThan10000USDPct = toFloat(row.householdIncomeLessThan10000USDPct),\n",
      "i.householdIncome10000To14999USD = toInteger(row.householdIncome10000To14999USD),\n",
      "i.householdIncome10000To14999USDPct = toFloat(row.householdIncome10000To14999USDPct),\n",
      "i.householdIncome15000To24999USD = toInteger(row.householdIncome15000To24999USD),\n",
      "i.householdIncome15000To24999USDPct = toFloat(row.householdIncome15000To24999USDPct),\n",
      "i.householdIncome25000To34999USD = toInteger(row.householdIncome25000To34999USD),\n",
      "i.householdIncome25000To34999USDPct = toFloat(row.householdIncome25000To34999USDPct),\n",
      "i.householdIncome35000To49999USD = toInteger(row.householdIncome35000To49999USD),\n",
      "i.householdIncome35000To49999USDPct = toFloat(row.householdIncome35000To49999USDPct),\n",
      "i.householdIncome50000To74999USD = toInteger(row.householdIncome50000To74999USD),\n",
      "i.householdIncome50000To74999USDPct = toFloat(row.householdIncome50000To74999USDPct),\n",
      "i.householdIncome75000To99999USD = toInteger(row.householdIncome75000To99999USD),\n",
      "i.householdIncome75000To99999USDPct = toFloat(row.householdIncome75000To99999USDPct),\n",
      "i.householdIncome100000To149999USD = toInteger(row.householdIncome100000To149999USD),\n",
      "i.householdIncome100000To149999USDPct = toFloat(row.householdIncome100000To149999USDPct),\n",
      "i.householdIncome150000To199999USD = toInteger(row.householdIncome150000To199999USD),\n",
      "i.householdIncome150000To199999USDPct = toFloat(row.householdIncome150000To199999USDPct),\n",
      "i.householdIncomeMoreThan200000USD = toInteger(row.householdIncomeMoreThan200000USD),\n",
      "i.householdIncomeMoreThan200000USDPct = toFloat(row.householdIncomeMoreThan200000USDPct),\n",
      "i.medianHouseholdIncomeUSD = toInteger(row.medianHouseholdIncomeUSD),\n",
      "i.meanHouseholdIncomeUSD = toInteger(row.meanHouseholdIncomeUSD),\n"
     ]
    }
   ],
   "source": [
    "for v in variables.values():\n",
    "    if 'Pct' in v:\n",
    "        print('i.' + v + ' = toFloat(row.' + v + '),')\n",
    "    else:\n",
    "        print('i.' + v + ' = toInteger(row.' + v + '),')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "23\n"
     ]
    }
   ],
   "source": [
    "print(len(variables.keys()))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Download county-level data using US Census API"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "url_county = f'https://api.census.gov/data/2018/acs/acs5/profile?get={fields}&for=county:*'"
   ]
  },
  {
   "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>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</th>\n",
       "      <th>10</th>\n",
       "      <th>11</th>\n",
       "      <th>12</th>\n",
       "      <th>13</th>\n",
       "      <th>14</th>\n",
       "      <th>15</th>\n",
       "      <th>16</th>\n",
       "      <th>17</th>\n",
       "      <th>18</th>\n",
       "      <th>19</th>\n",
       "      <th>20</th>\n",
       "      <th>21</th>\n",
       "      <th>22</th>\n",
       "      <th>23</th>\n",
       "      <th>24</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>DP03_0051E</td>\n",
       "      <td>DP03_0052E</td>\n",
       "      <td>DP03_0052PE</td>\n",
       "      <td>DP03_0053E</td>\n",
       "      <td>DP03_0053PE</td>\n",
       "      <td>DP03_0054E</td>\n",
       "      <td>DP03_0054PE</td>\n",
       "      <td>DP03_0055E</td>\n",
       "      <td>DP03_0055PE</td>\n",
       "      <td>DP03_0056E</td>\n",
       "      <td>DP03_0056PE</td>\n",
       "      <td>DP03_0057E</td>\n",
       "      <td>DP03_0057PE</td>\n",
       "      <td>DP03_0058E</td>\n",
       "      <td>DP03_0058PE</td>\n",
       "      <td>DP03_0059E</td>\n",
       "      <td>DP03_0059PE</td>\n",
       "      <td>DP03_0060E</td>\n",
       "      <td>DP03_0060PE</td>\n",
       "      <td>DP03_0061E</td>\n",
       "      <td>DP03_0061PE</td>\n",
       "      <td>DP03_0062E</td>\n",
       "      <td>DP03_0063E</td>\n",
       "      <td>state</td>\n",
       "      <td>county</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1170</td>\n",
       "      <td>55</td>\n",
       "      <td>4.7</td>\n",
       "      <td>23</td>\n",
       "      <td>2.0</td>\n",
       "      <td>120</td>\n",
       "      <td>10.3</td>\n",
       "      <td>122</td>\n",
       "      <td>10.4</td>\n",
       "      <td>100</td>\n",
       "      <td>8.5</td>\n",
       "      <td>240</td>\n",
       "      <td>20.5</td>\n",
       "      <td>150</td>\n",
       "      <td>12.8</td>\n",
       "      <td>174</td>\n",
       "      <td>14.9</td>\n",
       "      <td>96</td>\n",
       "      <td>8.2</td>\n",
       "      <td>90</td>\n",
       "      <td>7.7</td>\n",
       "      <td>66907</td>\n",
       "      <td>87391</td>\n",
       "      <td>02</td>\n",
       "      <td>195</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1692</td>\n",
       "      <td>183</td>\n",
       "      <td>10.8</td>\n",
       "      <td>124</td>\n",
       "      <td>7.3</td>\n",
       "      <td>257</td>\n",
       "      <td>15.2</td>\n",
       "      <td>271</td>\n",
       "      <td>16.0</td>\n",
       "      <td>271</td>\n",
       "      <td>16.0</td>\n",
       "      <td>303</td>\n",
       "      <td>17.9</td>\n",
       "      <td>123</td>\n",
       "      <td>7.3</td>\n",
       "      <td>104</td>\n",
       "      <td>6.1</td>\n",
       "      <td>45</td>\n",
       "      <td>2.7</td>\n",
       "      <td>11</td>\n",
       "      <td>0.7</td>\n",
       "      <td>35539</td>\n",
       "      <td>46732</td>\n",
       "      <td>02</td>\n",
       "      <td>158</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1520767</td>\n",
       "      <td>89567</td>\n",
       "      <td>5.9</td>\n",
       "      <td>57241</td>\n",
       "      <td>3.8</td>\n",
       "      <td>128278</td>\n",
       "      <td>8.4</td>\n",
       "      <td>139146</td>\n",
       "      <td>9.1</td>\n",
       "      <td>202904</td>\n",
       "      <td>13.3</td>\n",
       "      <td>281762</td>\n",
       "      <td>18.5</td>\n",
       "      <td>195265</td>\n",
       "      <td>12.8</td>\n",
       "      <td>230321</td>\n",
       "      <td>15.1</td>\n",
       "      <td>94440</td>\n",
       "      <td>6.2</td>\n",
       "      <td>101843</td>\n",
       "      <td>6.7</td>\n",
       "      <td>61606</td>\n",
       "      <td>84884</td>\n",
       "      <td>04</td>\n",
       "      <td>013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10782</td>\n",
       "      <td>1059</td>\n",
       "      <td>9.8</td>\n",
       "      <td>604</td>\n",
       "      <td>5.6</td>\n",
       "      <td>1073</td>\n",
       "      <td>10.0</td>\n",
       "      <td>974</td>\n",
       "      <td>9.0</td>\n",
       "      <td>1516</td>\n",
       "      <td>14.1</td>\n",
       "      <td>2213</td>\n",
       "      <td>20.5</td>\n",
       "      <td>1615</td>\n",
       "      <td>15.0</td>\n",
       "      <td>1326</td>\n",
       "      <td>12.3</td>\n",
       "      <td>288</td>\n",
       "      <td>2.7</td>\n",
       "      <td>114</td>\n",
       "      <td>1.1</td>\n",
       "      <td>51352</td>\n",
       "      <td>59370</td>\n",
       "      <td>04</td>\n",
       "      <td>009</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           0           1            2           3            4           5   \\\n",
       "0  DP03_0051E  DP03_0052E  DP03_0052PE  DP03_0053E  DP03_0053PE  DP03_0054E   \n",
       "1        1170          55          4.7          23          2.0         120   \n",
       "2        1692         183         10.8         124          7.3         257   \n",
       "3     1520767       89567          5.9       57241          3.8      128278   \n",
       "4       10782        1059          9.8         604          5.6        1073   \n",
       "\n",
       "            6           7            8           9            10          11  \\\n",
       "0  DP03_0054PE  DP03_0055E  DP03_0055PE  DP03_0056E  DP03_0056PE  DP03_0057E   \n",
       "1         10.3         122         10.4         100          8.5         240   \n",
       "2         15.2         271         16.0         271         16.0         303   \n",
       "3          8.4      139146          9.1      202904         13.3      281762   \n",
       "4         10.0         974          9.0        1516         14.1        2213   \n",
       "\n",
       "            12          13           14          15           16          17  \\\n",
       "0  DP03_0057PE  DP03_0058E  DP03_0058PE  DP03_0059E  DP03_0059PE  DP03_0060E   \n",
       "1         20.5         150         12.8         174         14.9          96   \n",
       "2         17.9         123          7.3         104          6.1          45   \n",
       "3         18.5      195265         12.8      230321         15.1       94440   \n",
       "4         20.5        1615         15.0        1326         12.3         288   \n",
       "\n",
       "            18          19           20          21          22     23      24  \n",
       "0  DP03_0060PE  DP03_0061E  DP03_0061PE  DP03_0062E  DP03_0063E  state  county  \n",
       "1          8.2          90          7.7       66907       87391     02     195  \n",
       "2          2.7          11          0.7       35539       46732     02     158  \n",
       "3          6.2      101843          6.7       61606       84884     04     013  \n",
       "4          2.7         114          1.1       51352       59370     04     009  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_json(url_county, dtype='str')\n",
    "df.fillna('', inplace=True)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Add column names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df[1:].copy() # skip first row of labels\n",
    "columns = list(variables.values())\n",
    "columns.append('stateFips')\n",
    "columns.append('countyFips')\n",
    "df.columns = columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Remove Puerto Rico (stateFips = 72) to limit data to US States\n",
    "\n",
    "TODO handle data for Puerto Rico (GeoNames represents Puerto Rico as a country)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.query(\"stateFips != '72'\", inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Save list of state fips (required later to get tract data by state)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>totalHouseholds</th>\n",
       "      <th>householdIncomeLessThan10000USD</th>\n",
       "      <th>householdIncomeLessThan10000USDPct</th>\n",
       "      <th>householdIncome10000To14999USD</th>\n",
       "      <th>householdIncome10000To14999USDPct</th>\n",
       "      <th>householdIncome15000To24999USD</th>\n",
       "      <th>householdIncome15000To24999USDPct</th>\n",
       "      <th>householdIncome25000To34999USD</th>\n",
       "      <th>householdIncome25000To34999USDPct</th>\n",
       "      <th>householdIncome35000To49999USD</th>\n",
       "      <th>householdIncome35000To49999USDPct</th>\n",
       "      <th>householdIncome50000To74999USD</th>\n",
       "      <th>householdIncome50000To74999USDPct</th>\n",
       "      <th>householdIncome75000To99999USD</th>\n",
       "      <th>householdIncome75000To99999USDPct</th>\n",
       "      <th>householdIncome100000To149999USD</th>\n",
       "      <th>householdIncome100000To149999USDPct</th>\n",
       "      <th>householdIncome150000To199999USD</th>\n",
       "      <th>householdIncome150000To199999USDPct</th>\n",
       "      <th>householdIncomeMoreThan200000USD</th>\n",
       "      <th>householdIncomeMoreThan200000USDPct</th>\n",
       "      <th>medianHouseholdIncomeUSD</th>\n",
       "      <th>meanHouseholdIncomeUSD</th>\n",
       "      <th>stateFips</th>\n",
       "      <th>countyFips</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1170</td>\n",
       "      <td>55</td>\n",
       "      <td>4.7</td>\n",
       "      <td>23</td>\n",
       "      <td>2.0</td>\n",
       "      <td>120</td>\n",
       "      <td>10.3</td>\n",
       "      <td>122</td>\n",
       "      <td>10.4</td>\n",
       "      <td>100</td>\n",
       "      <td>8.5</td>\n",
       "      <td>240</td>\n",
       "      <td>20.5</td>\n",
       "      <td>150</td>\n",
       "      <td>12.8</td>\n",
       "      <td>174</td>\n",
       "      <td>14.9</td>\n",
       "      <td>96</td>\n",
       "      <td>8.2</td>\n",
       "      <td>90</td>\n",
       "      <td>7.7</td>\n",
       "      <td>66907</td>\n",
       "      <td>87391</td>\n",
       "      <td>02</td>\n",
       "      <td>195</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1692</td>\n",
       "      <td>183</td>\n",
       "      <td>10.8</td>\n",
       "      <td>124</td>\n",
       "      <td>7.3</td>\n",
       "      <td>257</td>\n",
       "      <td>15.2</td>\n",
       "      <td>271</td>\n",
       "      <td>16.0</td>\n",
       "      <td>271</td>\n",
       "      <td>16.0</td>\n",
       "      <td>303</td>\n",
       "      <td>17.9</td>\n",
       "      <td>123</td>\n",
       "      <td>7.3</td>\n",
       "      <td>104</td>\n",
       "      <td>6.1</td>\n",
       "      <td>45</td>\n",
       "      <td>2.7</td>\n",
       "      <td>11</td>\n",
       "      <td>0.7</td>\n",
       "      <td>35539</td>\n",
       "      <td>46732</td>\n",
       "      <td>02</td>\n",
       "      <td>158</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1520767</td>\n",
       "      <td>89567</td>\n",
       "      <td>5.9</td>\n",
       "      <td>57241</td>\n",
       "      <td>3.8</td>\n",
       "      <td>128278</td>\n",
       "      <td>8.4</td>\n",
       "      <td>139146</td>\n",
       "      <td>9.1</td>\n",
       "      <td>202904</td>\n",
       "      <td>13.3</td>\n",
       "      <td>281762</td>\n",
       "      <td>18.5</td>\n",
       "      <td>195265</td>\n",
       "      <td>12.8</td>\n",
       "      <td>230321</td>\n",
       "      <td>15.1</td>\n",
       "      <td>94440</td>\n",
       "      <td>6.2</td>\n",
       "      <td>101843</td>\n",
       "      <td>6.7</td>\n",
       "      <td>61606</td>\n",
       "      <td>84884</td>\n",
       "      <td>04</td>\n",
       "      <td>013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10782</td>\n",
       "      <td>1059</td>\n",
       "      <td>9.8</td>\n",
       "      <td>604</td>\n",
       "      <td>5.6</td>\n",
       "      <td>1073</td>\n",
       "      <td>10.0</td>\n",
       "      <td>974</td>\n",
       "      <td>9.0</td>\n",
       "      <td>1516</td>\n",
       "      <td>14.1</td>\n",
       "      <td>2213</td>\n",
       "      <td>20.5</td>\n",
       "      <td>1615</td>\n",
       "      <td>15.0</td>\n",
       "      <td>1326</td>\n",
       "      <td>12.3</td>\n",
       "      <td>288</td>\n",
       "      <td>2.7</td>\n",
       "      <td>114</td>\n",
       "      <td>1.1</td>\n",
       "      <td>51352</td>\n",
       "      <td>59370</td>\n",
       "      <td>04</td>\n",
       "      <td>009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>15430</td>\n",
       "      <td>1455</td>\n",
       "      <td>9.4</td>\n",
       "      <td>1032</td>\n",
       "      <td>6.7</td>\n",
       "      <td>2185</td>\n",
       "      <td>14.2</td>\n",
       "      <td>2236</td>\n",
       "      <td>14.5</td>\n",
       "      <td>2323</td>\n",
       "      <td>15.1</td>\n",
       "      <td>2519</td>\n",
       "      <td>16.3</td>\n",
       "      <td>1485</td>\n",
       "      <td>9.6</td>\n",
       "      <td>1314</td>\n",
       "      <td>8.5</td>\n",
       "      <td>507</td>\n",
       "      <td>3.3</td>\n",
       "      <td>374</td>\n",
       "      <td>2.4</td>\n",
       "      <td>40467</td>\n",
       "      <td>59164</td>\n",
       "      <td>04</td>\n",
       "      <td>023</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  totalHouseholds householdIncomeLessThan10000USD  \\\n",
       "1            1170                              55   \n",
       "2            1692                             183   \n",
       "3         1520767                           89567   \n",
       "4           10782                            1059   \n",
       "5           15430                            1455   \n",
       "\n",
       "  householdIncomeLessThan10000USDPct householdIncome10000To14999USD  \\\n",
       "1                                4.7                             23   \n",
       "2                               10.8                            124   \n",
       "3                                5.9                          57241   \n",
       "4                                9.8                            604   \n",
       "5                                9.4                           1032   \n",
       "\n",
       "  householdIncome10000To14999USDPct householdIncome15000To24999USD  \\\n",
       "1                               2.0                            120   \n",
       "2                               7.3                            257   \n",
       "3                               3.8                         128278   \n",
       "4                               5.6                           1073   \n",
       "5                               6.7                           2185   \n",
       "\n",
       "  householdIncome15000To24999USDPct householdIncome25000To34999USD  \\\n",
       "1                              10.3                            122   \n",
       "2                              15.2                            271   \n",
       "3                               8.4                         139146   \n",
       "4                              10.0                            974   \n",
       "5                              14.2                           2236   \n",
       "\n",
       "  householdIncome25000To34999USDPct householdIncome35000To49999USD  \\\n",
       "1                              10.4                            100   \n",
       "2                              16.0                            271   \n",
       "3                               9.1                         202904   \n",
       "4                               9.0                           1516   \n",
       "5                              14.5                           2323   \n",
       "\n",
       "  householdIncome35000To49999USDPct householdIncome50000To74999USD  \\\n",
       "1                               8.5                            240   \n",
       "2                              16.0                            303   \n",
       "3                              13.3                         281762   \n",
       "4                              14.1                           2213   \n",
       "5                              15.1                           2519   \n",
       "\n",
       "  householdIncome50000To74999USDPct householdIncome75000To99999USD  \\\n",
       "1                              20.5                            150   \n",
       "2                              17.9                            123   \n",
       "3                              18.5                         195265   \n",
       "4                              20.5                           1615   \n",
       "5                              16.3                           1485   \n",
       "\n",
       "  householdIncome75000To99999USDPct householdIncome100000To149999USD  \\\n",
       "1                              12.8                              174   \n",
       "2                               7.3                              104   \n",
       "3                              12.8                           230321   \n",
       "4                              15.0                             1326   \n",
       "5                               9.6                             1314   \n",
       "\n",
       "  householdIncome100000To149999USDPct householdIncome150000To199999USD  \\\n",
       "1                                14.9                               96   \n",
       "2                                 6.1                               45   \n",
       "3                                15.1                            94440   \n",
       "4                                12.3                              288   \n",
       "5                                 8.5                              507   \n",
       "\n",
       "  householdIncome150000To199999USDPct householdIncomeMoreThan200000USD  \\\n",
       "1                                 8.2                               90   \n",
       "2                                 2.7                               11   \n",
       "3                                 6.2                           101843   \n",
       "4                                 2.7                              114   \n",
       "5                                 3.3                              374   \n",
       "\n",
       "  householdIncomeMoreThan200000USDPct medianHouseholdIncomeUSD  \\\n",
       "1                                 7.7                    66907   \n",
       "2                                 0.7                    35539   \n",
       "3                                 6.7                    61606   \n",
       "4                                 1.1                    51352   \n",
       "5                                 2.4                    40467   \n",
       "\n",
       "  meanHouseholdIncomeUSD stateFips countyFips  \n",
       "1                  87391        02        195  \n",
       "2                  46732        02        158  \n",
       "3                  84884        04        013  \n",
       "4                  59370        04        009  \n",
       "5                  59164        04        023  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>totalHouseholds</th>\n",
       "      <th>householdIncomeLessThan10000USD</th>\n",
       "      <th>householdIncomeLessThan10000USDPct</th>\n",
       "      <th>householdIncome10000To14999USD</th>\n",
       "      <th>householdIncome10000To14999USDPct</th>\n",
       "      <th>householdIncome15000To24999USD</th>\n",
       "      <th>householdIncome15000To24999USDPct</th>\n",
       "      <th>householdIncome25000To34999USD</th>\n",
       "      <th>householdIncome25000To34999USDPct</th>\n",
       "      <th>householdIncome35000To49999USD</th>\n",
       "      <th>householdIncome35000To49999USDPct</th>\n",
       "      <th>householdIncome50000To74999USD</th>\n",
       "      <th>householdIncome50000To74999USDPct</th>\n",
       "      <th>householdIncome75000To99999USD</th>\n",
       "      <th>householdIncome75000To99999USDPct</th>\n",
       "      <th>householdIncome100000To149999USD</th>\n",
       "      <th>householdIncome100000To149999USDPct</th>\n",
       "      <th>householdIncome150000To199999USD</th>\n",
       "      <th>householdIncome150000To199999USDPct</th>\n",
       "      <th>householdIncomeMoreThan200000USD</th>\n",
       "      <th>householdIncomeMoreThan200000USDPct</th>\n",
       "      <th>medianHouseholdIncomeUSD</th>\n",
       "      <th>meanHouseholdIncomeUSD</th>\n",
       "      <th>stateFips</th>\n",
       "      <th>countyFips</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>897</th>\n",
       "      <td>1118980</td>\n",
       "      <td>51029</td>\n",
       "      <td>4.6</td>\n",
       "      <td>39441</td>\n",
       "      <td>3.5</td>\n",
       "      <td>78317</td>\n",
       "      <td>7.0</td>\n",
       "      <td>84271</td>\n",
       "      <td>7.5</td>\n",
       "      <td>122572</td>\n",
       "      <td>11.0</td>\n",
       "      <td>184805</td>\n",
       "      <td>16.5</td>\n",
       "      <td>144979</td>\n",
       "      <td>13.0</td>\n",
       "      <td>197632</td>\n",
       "      <td>17.7</td>\n",
       "      <td>98349</td>\n",
       "      <td>8.8</td>\n",
       "      <td>117585</td>\n",
       "      <td>10.5</td>\n",
       "      <td>74855</td>\n",
       "      <td>101302</td>\n",
       "      <td>06</td>\n",
       "      <td>073</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    totalHouseholds householdIncomeLessThan10000USD  \\\n",
       "897         1118980                           51029   \n",
       "\n",
       "    householdIncomeLessThan10000USDPct householdIncome10000To14999USD  \\\n",
       "897                                4.6                          39441   \n",
       "\n",
       "    householdIncome10000To14999USDPct householdIncome15000To24999USD  \\\n",
       "897                               3.5                          78317   \n",
       "\n",
       "    householdIncome15000To24999USDPct householdIncome25000To34999USD  \\\n",
       "897                               7.0                          84271   \n",
       "\n",
       "    householdIncome25000To34999USDPct householdIncome35000To49999USD  \\\n",
       "897                               7.5                         122572   \n",
       "\n",
       "    householdIncome35000To49999USDPct householdIncome50000To74999USD  \\\n",
       "897                              11.0                         184805   \n",
       "\n",
       "    householdIncome50000To74999USDPct householdIncome75000To99999USD  \\\n",
       "897                              16.5                         144979   \n",
       "\n",
       "    householdIncome75000To99999USDPct householdIncome100000To149999USD  \\\n",
       "897                              13.0                           197632   \n",
       "\n",
       "    householdIncome100000To149999USDPct householdIncome150000To199999USD  \\\n",
       "897                                17.7                            98349   \n",
       "\n",
       "    householdIncome150000To199999USDPct householdIncomeMoreThan200000USD  \\\n",
       "897                                 8.8                           117585   \n",
       "\n",
       "    householdIncomeMoreThan200000USDPct medianHouseholdIncomeUSD  \\\n",
       "897                                10.5                    74855   \n",
       "\n",
       "    meanHouseholdIncomeUSD stateFips countyFips  \n",
       "897                 101302        06        073  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Example data\n",
    "df[(df['stateFips'] == '06') & (df['countyFips'] == '073')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['source'] = 'American Community Survey 5 year'\n",
    "df['aggregationLevel'] = 'Admin2'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Save data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv(NEO4J_IMPORT / \"03a-USCensusDP03IncomeAdmin2.csv\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Download zip-level data using US Census API"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "url_zip = f'https://api.census.gov/data/2018/acs/acs5/profile?get={fields}&for=zip%20code%20tabulation%20area:*'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</th>\n",
       "      <th>10</th>\n",
       "      <th>11</th>\n",
       "      <th>12</th>\n",
       "      <th>13</th>\n",
       "      <th>14</th>\n",
       "      <th>15</th>\n",
       "      <th>16</th>\n",
       "      <th>17</th>\n",
       "      <th>18</th>\n",
       "      <th>19</th>\n",
       "      <th>20</th>\n",
       "      <th>21</th>\n",
       "      <th>22</th>\n",
       "      <th>23</th>\n",
       "      <th>24</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>DP03_0051E</td>\n",
       "      <td>DP03_0052E</td>\n",
       "      <td>DP03_0052PE</td>\n",
       "      <td>DP03_0053E</td>\n",
       "      <td>DP03_0053PE</td>\n",
       "      <td>DP03_0054E</td>\n",
       "      <td>DP03_0054PE</td>\n",
       "      <td>DP03_0055E</td>\n",
       "      <td>DP03_0055PE</td>\n",
       "      <td>DP03_0056E</td>\n",
       "      <td>DP03_0056PE</td>\n",
       "      <td>DP03_0057E</td>\n",
       "      <td>DP03_0057PE</td>\n",
       "      <td>DP03_0058E</td>\n",
       "      <td>DP03_0058PE</td>\n",
       "      <td>DP03_0059E</td>\n",
       "      <td>DP03_0059PE</td>\n",
       "      <td>DP03_0060E</td>\n",
       "      <td>DP03_0060PE</td>\n",
       "      <td>DP03_0061E</td>\n",
       "      <td>DP03_0061PE</td>\n",
       "      <td>DP03_0062E</td>\n",
       "      <td>DP03_0063E</td>\n",
       "      <td>state</td>\n",
       "      <td>zip code tabulation area</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>852</td>\n",
       "      <td>112</td>\n",
       "      <td>13.1</td>\n",
       "      <td>33</td>\n",
       "      <td>3.9</td>\n",
       "      <td>141</td>\n",
       "      <td>16.5</td>\n",
       "      <td>68</td>\n",
       "      <td>8.0</td>\n",
       "      <td>88</td>\n",
       "      <td>10.3</td>\n",
       "      <td>170</td>\n",
       "      <td>20.0</td>\n",
       "      <td>96</td>\n",
       "      <td>11.3</td>\n",
       "      <td>59</td>\n",
       "      <td>6.9</td>\n",
       "      <td>41</td>\n",
       "      <td>4.8</td>\n",
       "      <td>44</td>\n",
       "      <td>5.2</td>\n",
       "      <td>46167</td>\n",
       "      <td>62595</td>\n",
       "      <td>51</td>\n",
       "      <td>23833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>467</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>15</td>\n",
       "      <td>3.2</td>\n",
       "      <td>89</td>\n",
       "      <td>19.1</td>\n",
       "      <td>6</td>\n",
       "      <td>1.3</td>\n",
       "      <td>57</td>\n",
       "      <td>12.2</td>\n",
       "      <td>98</td>\n",
       "      <td>21.0</td>\n",
       "      <td>119</td>\n",
       "      <td>25.5</td>\n",
       "      <td>83</td>\n",
       "      <td>17.8</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>70852</td>\n",
       "      <td>66879</td>\n",
       "      <td>51</td>\n",
       "      <td>23850</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5507</td>\n",
       "      <td>349</td>\n",
       "      <td>6.3</td>\n",
       "      <td>300</td>\n",
       "      <td>5.4</td>\n",
       "      <td>888</td>\n",
       "      <td>16.1</td>\n",
       "      <td>599</td>\n",
       "      <td>10.9</td>\n",
       "      <td>795</td>\n",
       "      <td>14.4</td>\n",
       "      <td>966</td>\n",
       "      <td>17.5</td>\n",
       "      <td>684</td>\n",
       "      <td>12.4</td>\n",
       "      <td>535</td>\n",
       "      <td>9.7</td>\n",
       "      <td>264</td>\n",
       "      <td>4.8</td>\n",
       "      <td>127</td>\n",
       "      <td>2.3</td>\n",
       "      <td>46049</td>\n",
       "      <td>60628</td>\n",
       "      <td>51</td>\n",
       "      <td>23851</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1617</td>\n",
       "      <td>98</td>\n",
       "      <td>6.1</td>\n",
       "      <td>70</td>\n",
       "      <td>4.3</td>\n",
       "      <td>180</td>\n",
       "      <td>11.1</td>\n",
       "      <td>201</td>\n",
       "      <td>12.4</td>\n",
       "      <td>316</td>\n",
       "      <td>19.5</td>\n",
       "      <td>316</td>\n",
       "      <td>19.5</td>\n",
       "      <td>128</td>\n",
       "      <td>7.9</td>\n",
       "      <td>260</td>\n",
       "      <td>16.1</td>\n",
       "      <td>38</td>\n",
       "      <td>2.4</td>\n",
       "      <td>10</td>\n",
       "      <td>0.6</td>\n",
       "      <td>46265</td>\n",
       "      <td>59332</td>\n",
       "      <td>51</td>\n",
       "      <td>23890</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           0           1            2           3            4           5   \\\n",
       "0  DP03_0051E  DP03_0052E  DP03_0052PE  DP03_0053E  DP03_0053PE  DP03_0054E   \n",
       "1         852         112         13.1          33          3.9         141   \n",
       "2         467           0          0.0          15          3.2          89   \n",
       "3        5507         349          6.3         300          5.4         888   \n",
       "4        1617          98          6.1          70          4.3         180   \n",
       "\n",
       "            6           7            8           9            10          11  \\\n",
       "0  DP03_0054PE  DP03_0055E  DP03_0055PE  DP03_0056E  DP03_0056PE  DP03_0057E   \n",
       "1         16.5          68          8.0          88         10.3         170   \n",
       "2         19.1           6          1.3          57         12.2          98   \n",
       "3         16.1         599         10.9         795         14.4         966   \n",
       "4         11.1         201         12.4         316         19.5         316   \n",
       "\n",
       "            12          13           14          15           16          17  \\\n",
       "0  DP03_0057PE  DP03_0058E  DP03_0058PE  DP03_0059E  DP03_0059PE  DP03_0060E   \n",
       "1         20.0          96         11.3          59          6.9          41   \n",
       "2         21.0         119         25.5          83         17.8           0   \n",
       "3         17.5         684         12.4         535          9.7         264   \n",
       "4         19.5         128          7.9         260         16.1          38   \n",
       "\n",
       "            18          19           20          21          22     23  \\\n",
       "0  DP03_0060PE  DP03_0061E  DP03_0061PE  DP03_0062E  DP03_0063E  state   \n",
       "1          4.8          44          5.2       46167       62595     51   \n",
       "2          0.0           0          0.0       70852       66879     51   \n",
       "3          4.8         127          2.3       46049       60628     51   \n",
       "4          2.4          10          0.6       46265       59332     51   \n",
       "\n",
       "                         24  \n",
       "0  zip code tabulation area  \n",
       "1                     23833  \n",
       "2                     23850  \n",
       "3                     23851  \n",
       "4                     23890  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_json(url_zip, dtype='str')\n",
    "df.fillna('', inplace=True)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Add column names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df[1:].copy() # skip first row\n",
    "columns = list(variables.values())\n",
    "columns.append('stateFips')\n",
    "columns.append('postalCode')\n",
    "df.columns = columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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>totalHouseholds</th>\n",
       "      <th>householdIncomeLessThan10000USD</th>\n",
       "      <th>householdIncomeLessThan10000USDPct</th>\n",
       "      <th>householdIncome10000To14999USD</th>\n",
       "      <th>householdIncome10000To14999USDPct</th>\n",
       "      <th>householdIncome15000To24999USD</th>\n",
       "      <th>householdIncome15000To24999USDPct</th>\n",
       "      <th>householdIncome25000To34999USD</th>\n",
       "      <th>householdIncome25000To34999USDPct</th>\n",
       "      <th>householdIncome35000To49999USD</th>\n",
       "      <th>householdIncome35000To49999USDPct</th>\n",
       "      <th>householdIncome50000To74999USD</th>\n",
       "      <th>householdIncome50000To74999USDPct</th>\n",
       "      <th>householdIncome75000To99999USD</th>\n",
       "      <th>householdIncome75000To99999USDPct</th>\n",
       "      <th>householdIncome100000To149999USD</th>\n",
       "      <th>householdIncome100000To149999USDPct</th>\n",
       "      <th>householdIncome150000To199999USD</th>\n",
       "      <th>householdIncome150000To199999USDPct</th>\n",
       "      <th>householdIncomeMoreThan200000USD</th>\n",
       "      <th>householdIncomeMoreThan200000USDPct</th>\n",
       "      <th>medianHouseholdIncomeUSD</th>\n",
       "      <th>meanHouseholdIncomeUSD</th>\n",
       "      <th>stateFips</th>\n",
       "      <th>postalCode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>852</td>\n",
       "      <td>112</td>\n",
       "      <td>13.1</td>\n",
       "      <td>33</td>\n",
       "      <td>3.9</td>\n",
       "      <td>141</td>\n",
       "      <td>16.5</td>\n",
       "      <td>68</td>\n",
       "      <td>8.0</td>\n",
       "      <td>88</td>\n",
       "      <td>10.3</td>\n",
       "      <td>170</td>\n",
       "      <td>20.0</td>\n",
       "      <td>96</td>\n",
       "      <td>11.3</td>\n",
       "      <td>59</td>\n",
       "      <td>6.9</td>\n",
       "      <td>41</td>\n",
       "      <td>4.8</td>\n",
       "      <td>44</td>\n",
       "      <td>5.2</td>\n",
       "      <td>46167</td>\n",
       "      <td>62595</td>\n",
       "      <td>51</td>\n",
       "      <td>23833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>467</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>15</td>\n",
       "      <td>3.2</td>\n",
       "      <td>89</td>\n",
       "      <td>19.1</td>\n",
       "      <td>6</td>\n",
       "      <td>1.3</td>\n",
       "      <td>57</td>\n",
       "      <td>12.2</td>\n",
       "      <td>98</td>\n",
       "      <td>21.0</td>\n",
       "      <td>119</td>\n",
       "      <td>25.5</td>\n",
       "      <td>83</td>\n",
       "      <td>17.8</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>70852</td>\n",
       "      <td>66879</td>\n",
       "      <td>51</td>\n",
       "      <td>23850</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5507</td>\n",
       "      <td>349</td>\n",
       "      <td>6.3</td>\n",
       "      <td>300</td>\n",
       "      <td>5.4</td>\n",
       "      <td>888</td>\n",
       "      <td>16.1</td>\n",
       "      <td>599</td>\n",
       "      <td>10.9</td>\n",
       "      <td>795</td>\n",
       "      <td>14.4</td>\n",
       "      <td>966</td>\n",
       "      <td>17.5</td>\n",
       "      <td>684</td>\n",
       "      <td>12.4</td>\n",
       "      <td>535</td>\n",
       "      <td>9.7</td>\n",
       "      <td>264</td>\n",
       "      <td>4.8</td>\n",
       "      <td>127</td>\n",
       "      <td>2.3</td>\n",
       "      <td>46049</td>\n",
       "      <td>60628</td>\n",
       "      <td>51</td>\n",
       "      <td>23851</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1617</td>\n",
       "      <td>98</td>\n",
       "      <td>6.1</td>\n",
       "      <td>70</td>\n",
       "      <td>4.3</td>\n",
       "      <td>180</td>\n",
       "      <td>11.1</td>\n",
       "      <td>201</td>\n",
       "      <td>12.4</td>\n",
       "      <td>316</td>\n",
       "      <td>19.5</td>\n",
       "      <td>316</td>\n",
       "      <td>19.5</td>\n",
       "      <td>128</td>\n",
       "      <td>7.9</td>\n",
       "      <td>260</td>\n",
       "      <td>16.1</td>\n",
       "      <td>38</td>\n",
       "      <td>2.4</td>\n",
       "      <td>10</td>\n",
       "      <td>0.6</td>\n",
       "      <td>46265</td>\n",
       "      <td>59332</td>\n",
       "      <td>51</td>\n",
       "      <td>23890</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>90</td>\n",
       "      <td>17</td>\n",
       "      <td>18.9</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>22</td>\n",
       "      <td>24.4</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>13</td>\n",
       "      <td>14.4</td>\n",
       "      <td>10</td>\n",
       "      <td>11.1</td>\n",
       "      <td>28</td>\n",
       "      <td>31.1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-666666666</td>\n",
       "      <td>85980</td>\n",
       "      <td>51</td>\n",
       "      <td>23302</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  totalHouseholds householdIncomeLessThan10000USD  \\\n",
       "1             852                             112   \n",
       "2             467                               0   \n",
       "3            5507                             349   \n",
       "4            1617                              98   \n",
       "5              90                              17   \n",
       "\n",
       "  householdIncomeLessThan10000USDPct householdIncome10000To14999USD  \\\n",
       "1                               13.1                             33   \n",
       "2                                0.0                             15   \n",
       "3                                6.3                            300   \n",
       "4                                6.1                             70   \n",
       "5                               18.9                              0   \n",
       "\n",
       "  householdIncome10000To14999USDPct householdIncome15000To24999USD  \\\n",
       "1                               3.9                            141   \n",
       "2                               3.2                             89   \n",
       "3                               5.4                            888   \n",
       "4                               4.3                            180   \n",
       "5                               0.0                              0   \n",
       "\n",
       "  householdIncome15000To24999USDPct householdIncome25000To34999USD  \\\n",
       "1                              16.5                             68   \n",
       "2                              19.1                              6   \n",
       "3                              16.1                            599   \n",
       "4                              11.1                            201   \n",
       "5                               0.0                             22   \n",
       "\n",
       "  householdIncome25000To34999USDPct householdIncome35000To49999USD  \\\n",
       "1                               8.0                             88   \n",
       "2                               1.3                             57   \n",
       "3                              10.9                            795   \n",
       "4                              12.4                            316   \n",
       "5                              24.4                              0   \n",
       "\n",
       "  householdIncome35000To49999USDPct householdIncome50000To74999USD  \\\n",
       "1                              10.3                            170   \n",
       "2                              12.2                             98   \n",
       "3                              14.4                            966   \n",
       "4                              19.5                            316   \n",
       "5                               0.0                              0   \n",
       "\n",
       "  householdIncome50000To74999USDPct householdIncome75000To99999USD  \\\n",
       "1                              20.0                             96   \n",
       "2                              21.0                            119   \n",
       "3                              17.5                            684   \n",
       "4                              19.5                            128   \n",
       "5                               0.0                             13   \n",
       "\n",
       "  householdIncome75000To99999USDPct householdIncome100000To149999USD  \\\n",
       "1                              11.3                               59   \n",
       "2                              25.5                               83   \n",
       "3                              12.4                              535   \n",
       "4                               7.9                              260   \n",
       "5                              14.4                               10   \n",
       "\n",
       "  householdIncome100000To149999USDPct householdIncome150000To199999USD  \\\n",
       "1                                 6.9                               41   \n",
       "2                                17.8                                0   \n",
       "3                                 9.7                              264   \n",
       "4                                16.1                               38   \n",
       "5                                11.1                               28   \n",
       "\n",
       "  householdIncome150000To199999USDPct householdIncomeMoreThan200000USD  \\\n",
       "1                                 4.8                               44   \n",
       "2                                 0.0                                0   \n",
       "3                                 4.8                              127   \n",
       "4                                 2.4                               10   \n",
       "5                                31.1                                0   \n",
       "\n",
       "  householdIncomeMoreThan200000USDPct medianHouseholdIncomeUSD  \\\n",
       "1                                 5.2                    46167   \n",
       "2                                 0.0                    70852   \n",
       "3                                 2.3                    46049   \n",
       "4                                 0.6                    46265   \n",
       "5                                 0.0               -666666666   \n",
       "\n",
       "  meanHouseholdIncomeUSD stateFips postalCode  \n",
       "1                  62595        51      23833  \n",
       "2                  66879        51      23850  \n",
       "3                  60628        51      23851  \n",
       "4                  59332        51      23890  \n",
       "5                  85980        51      23302  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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>totalHouseholds</th>\n",
       "      <th>householdIncomeLessThan10000USD</th>\n",
       "      <th>householdIncomeLessThan10000USDPct</th>\n",
       "      <th>householdIncome10000To14999USD</th>\n",
       "      <th>householdIncome10000To14999USDPct</th>\n",
       "      <th>householdIncome15000To24999USD</th>\n",
       "      <th>householdIncome15000To24999USDPct</th>\n",
       "      <th>householdIncome25000To34999USD</th>\n",
       "      <th>householdIncome25000To34999USDPct</th>\n",
       "      <th>householdIncome35000To49999USD</th>\n",
       "      <th>householdIncome35000To49999USDPct</th>\n",
       "      <th>householdIncome50000To74999USD</th>\n",
       "      <th>householdIncome50000To74999USDPct</th>\n",
       "      <th>householdIncome75000To99999USD</th>\n",
       "      <th>householdIncome75000To99999USDPct</th>\n",
       "      <th>householdIncome100000To149999USD</th>\n",
       "      <th>householdIncome100000To149999USDPct</th>\n",
       "      <th>householdIncome150000To199999USD</th>\n",
       "      <th>householdIncome150000To199999USDPct</th>\n",
       "      <th>householdIncomeMoreThan200000USD</th>\n",
       "      <th>householdIncomeMoreThan200000USDPct</th>\n",
       "      <th>medianHouseholdIncomeUSD</th>\n",
       "      <th>meanHouseholdIncomeUSD</th>\n",
       "      <th>stateFips</th>\n",
       "      <th>postalCode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>24526</th>\n",
       "      <td>8036</td>\n",
       "      <td>509</td>\n",
       "      <td>6.3</td>\n",
       "      <td>269</td>\n",
       "      <td>3.3</td>\n",
       "      <td>366</td>\n",
       "      <td>4.6</td>\n",
       "      <td>214</td>\n",
       "      <td>2.7</td>\n",
       "      <td>258</td>\n",
       "      <td>3.2</td>\n",
       "      <td>485</td>\n",
       "      <td>6.0</td>\n",
       "      <td>745</td>\n",
       "      <td>9.3</td>\n",
       "      <td>1287</td>\n",
       "      <td>16.0</td>\n",
       "      <td>729</td>\n",
       "      <td>9.1</td>\n",
       "      <td>3174</td>\n",
       "      <td>39.5</td>\n",
       "      <td>143542</td>\n",
       "      <td>285015</td>\n",
       "      <td>06</td>\n",
       "      <td>90210</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      totalHouseholds householdIncomeLessThan10000USD  \\\n",
       "24526            8036                             509   \n",
       "\n",
       "      householdIncomeLessThan10000USDPct householdIncome10000To14999USD  \\\n",
       "24526                                6.3                            269   \n",
       "\n",
       "      householdIncome10000To14999USDPct householdIncome15000To24999USD  \\\n",
       "24526                               3.3                            366   \n",
       "\n",
       "      householdIncome15000To24999USDPct householdIncome25000To34999USD  \\\n",
       "24526                               4.6                            214   \n",
       "\n",
       "      householdIncome25000To34999USDPct householdIncome35000To49999USD  \\\n",
       "24526                               2.7                            258   \n",
       "\n",
       "      householdIncome35000To49999USDPct householdIncome50000To74999USD  \\\n",
       "24526                               3.2                            485   \n",
       "\n",
       "      householdIncome50000To74999USDPct householdIncome75000To99999USD  \\\n",
       "24526                               6.0                            745   \n",
       "\n",
       "      householdIncome75000To99999USDPct householdIncome100000To149999USD  \\\n",
       "24526                               9.3                             1287   \n",
       "\n",
       "      householdIncome100000To149999USDPct householdIncome150000To199999USD  \\\n",
       "24526                                16.0                              729   \n",
       "\n",
       "      householdIncome150000To199999USDPct householdIncomeMoreThan200000USD  \\\n",
       "24526                                 9.1                             3174   \n",
       "\n",
       "      householdIncomeMoreThan200000USDPct medianHouseholdIncomeUSD  \\\n",
       "24526                                39.5                   143542   \n",
       "\n",
       "      meanHouseholdIncomeUSD stateFips postalCode  \n",
       "24526                 285015        06      90210  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Example data\n",
    "df.query(\"postalCode == '90210'\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['source'] = 'American Community Survey 5 year'\n",
    "df['aggregationLevel'] = 'PostalCode'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Save data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv(NEO4J_IMPORT / \"03a-USCensusDP03IncomeZip.csv\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Download tract-level data using US Census API\n",
    "Tract-level data are only available by state, so we need to loop over all states."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_tract_data(state):\n",
    "    url_tract = f'https://api.census.gov/data/2018/acs/acs5/profile?get={fields}&for=tract:*&in=state:{state}'\n",
    "    df = pd.read_json(url_tract, dtype='str')\n",
    "    time.sleep(1)\n",
    "    # skip first row of labels\n",
    "    df = df[1:].copy()\n",
    "    # Add column names\n",
    "    columns = list(variables.values())\n",
    "    columns.append('stateFips')\n",
    "    columns.append('countyFips')\n",
    "    columns.append('tract')\n",
    "    df.columns = columns\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.concat((get_tract_data(state) for state in stateFips))\n",
    "df.fillna('', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['tract'] = df['stateFips'] + df['countyFips'] + df['tract']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['source'] = 'American Community Survey 5 year'\n",
    "df['aggregationLevel'] = 'Tract'"
   ]
  },
  {
   "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>totalHouseholds</th>\n",
       "      <th>householdIncomeLessThan10000USD</th>\n",
       "      <th>householdIncomeLessThan10000USDPct</th>\n",
       "      <th>householdIncome10000To14999USD</th>\n",
       "      <th>householdIncome10000To14999USDPct</th>\n",
       "      <th>householdIncome15000To24999USD</th>\n",
       "      <th>householdIncome15000To24999USDPct</th>\n",
       "      <th>householdIncome25000To34999USD</th>\n",
       "      <th>householdIncome25000To34999USDPct</th>\n",
       "      <th>householdIncome35000To49999USD</th>\n",
       "      <th>householdIncome35000To49999USDPct</th>\n",
       "      <th>householdIncome50000To74999USD</th>\n",
       "      <th>householdIncome50000To74999USDPct</th>\n",
       "      <th>householdIncome75000To99999USD</th>\n",
       "      <th>householdIncome75000To99999USDPct</th>\n",
       "      <th>householdIncome100000To149999USD</th>\n",
       "      <th>householdIncome100000To149999USDPct</th>\n",
       "      <th>householdIncome150000To199999USD</th>\n",
       "      <th>householdIncome150000To199999USDPct</th>\n",
       "      <th>householdIncomeMoreThan200000USD</th>\n",
       "      <th>householdIncomeMoreThan200000USDPct</th>\n",
       "      <th>medianHouseholdIncomeUSD</th>\n",
       "      <th>meanHouseholdIncomeUSD</th>\n",
       "      <th>stateFips</th>\n",
       "      <th>countyFips</th>\n",
       "      <th>tract</th>\n",
       "      <th>source</th>\n",
       "      <th>aggregationLevel</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>819</td>\n",
       "      <td>137</td>\n",
       "      <td>16.7</td>\n",
       "      <td>52</td>\n",
       "      <td>6.3</td>\n",
       "      <td>46</td>\n",
       "      <td>5.6</td>\n",
       "      <td>70</td>\n",
       "      <td>8.5</td>\n",
       "      <td>26</td>\n",
       "      <td>3.2</td>\n",
       "      <td>63</td>\n",
       "      <td>7.7</td>\n",
       "      <td>162</td>\n",
       "      <td>19.8</td>\n",
       "      <td>157</td>\n",
       "      <td>19.2</td>\n",
       "      <td>70</td>\n",
       "      <td>8.5</td>\n",
       "      <td>36</td>\n",
       "      <td>4.4</td>\n",
       "      <td>77039</td>\n",
       "      <td>80888</td>\n",
       "      <td>06</td>\n",
       "      <td>073</td>\n",
       "      <td>06073008339</td>\n",
       "      <td>American Community Survey 5 year</td>\n",
       "      <td>Tract</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2180</td>\n",
       "      <td>49</td>\n",
       "      <td>2.2</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>102</td>\n",
       "      <td>4.7</td>\n",
       "      <td>161</td>\n",
       "      <td>7.4</td>\n",
       "      <td>103</td>\n",
       "      <td>4.7</td>\n",
       "      <td>267</td>\n",
       "      <td>12.2</td>\n",
       "      <td>290</td>\n",
       "      <td>13.3</td>\n",
       "      <td>508</td>\n",
       "      <td>23.3</td>\n",
       "      <td>352</td>\n",
       "      <td>16.1</td>\n",
       "      <td>348</td>\n",
       "      <td>16.0</td>\n",
       "      <td>112368</td>\n",
       "      <td>130806</td>\n",
       "      <td>06</td>\n",
       "      <td>073</td>\n",
       "      <td>06073008347</td>\n",
       "      <td>American Community Survey 5 year</td>\n",
       "      <td>Tract</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>3200</td>\n",
       "      <td>37</td>\n",
       "      <td>1.2</td>\n",
       "      <td>51</td>\n",
       "      <td>1.6</td>\n",
       "      <td>71</td>\n",
       "      <td>2.2</td>\n",
       "      <td>87</td>\n",
       "      <td>2.7</td>\n",
       "      <td>336</td>\n",
       "      <td>10.5</td>\n",
       "      <td>466</td>\n",
       "      <td>14.6</td>\n",
       "      <td>422</td>\n",
       "      <td>13.2</td>\n",
       "      <td>839</td>\n",
       "      <td>26.2</td>\n",
       "      <td>572</td>\n",
       "      <td>17.9</td>\n",
       "      <td>319</td>\n",
       "      <td>10.0</td>\n",
       "      <td>110455</td>\n",
       "      <td>121883</td>\n",
       "      <td>06</td>\n",
       "      <td>073</td>\n",
       "      <td>06073008354</td>\n",
       "      <td>American Community Survey 5 year</td>\n",
       "      <td>Tract</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2116</td>\n",
       "      <td>177</td>\n",
       "      <td>8.4</td>\n",
       "      <td>89</td>\n",
       "      <td>4.2</td>\n",
       "      <td>103</td>\n",
       "      <td>4.9</td>\n",
       "      <td>138</td>\n",
       "      <td>6.5</td>\n",
       "      <td>255</td>\n",
       "      <td>12.1</td>\n",
       "      <td>381</td>\n",
       "      <td>18.0</td>\n",
       "      <td>252</td>\n",
       "      <td>11.9</td>\n",
       "      <td>428</td>\n",
       "      <td>20.2</td>\n",
       "      <td>165</td>\n",
       "      <td>7.8</td>\n",
       "      <td>128</td>\n",
       "      <td>6.0</td>\n",
       "      <td>63625</td>\n",
       "      <td>90850</td>\n",
       "      <td>06</td>\n",
       "      <td>073</td>\n",
       "      <td>06073008505</td>\n",
       "      <td>American Community Survey 5 year</td>\n",
       "      <td>Tract</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>3096</td>\n",
       "      <td>231</td>\n",
       "      <td>7.5</td>\n",
       "      <td>207</td>\n",
       "      <td>6.7</td>\n",
       "      <td>170</td>\n",
       "      <td>5.5</td>\n",
       "      <td>195</td>\n",
       "      <td>6.3</td>\n",
       "      <td>369</td>\n",
       "      <td>11.9</td>\n",
       "      <td>449</td>\n",
       "      <td>14.5</td>\n",
       "      <td>271</td>\n",
       "      <td>8.8</td>\n",
       "      <td>335</td>\n",
       "      <td>10.8</td>\n",
       "      <td>302</td>\n",
       "      <td>9.8</td>\n",
       "      <td>567</td>\n",
       "      <td>18.3</td>\n",
       "      <td>71875</td>\n",
       "      <td>142032</td>\n",
       "      <td>06</td>\n",
       "      <td>073</td>\n",
       "      <td>06073017604</td>\n",
       "      <td>American Community Survey 5 year</td>\n",
       "      <td>Tract</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   totalHouseholds householdIncomeLessThan10000USD  \\\n",
       "6              819                             137   \n",
       "7             2180                              49   \n",
       "8             3200                              37   \n",
       "9             2116                             177   \n",
       "10            3096                             231   \n",
       "\n",
       "   householdIncomeLessThan10000USDPct householdIncome10000To14999USD  \\\n",
       "6                                16.7                             52   \n",
       "7                                 2.2                              0   \n",
       "8                                 1.2                             51   \n",
       "9                                 8.4                             89   \n",
       "10                                7.5                            207   \n",
       "\n",
       "   householdIncome10000To14999USDPct householdIncome15000To24999USD  \\\n",
       "6                                6.3                             46   \n",
       "7                                0.0                            102   \n",
       "8                                1.6                             71   \n",
       "9                                4.2                            103   \n",
       "10                               6.7                            170   \n",
       "\n",
       "   householdIncome15000To24999USDPct householdIncome25000To34999USD  \\\n",
       "6                                5.6                             70   \n",
       "7                                4.7                            161   \n",
       "8                                2.2                             87   \n",
       "9                                4.9                            138   \n",
       "10                               5.5                            195   \n",
       "\n",
       "   householdIncome25000To34999USDPct householdIncome35000To49999USD  \\\n",
       "6                                8.5                             26   \n",
       "7                                7.4                            103   \n",
       "8                                2.7                            336   \n",
       "9                                6.5                            255   \n",
       "10                               6.3                            369   \n",
       "\n",
       "   householdIncome35000To49999USDPct householdIncome50000To74999USD  \\\n",
       "6                                3.2                             63   \n",
       "7                                4.7                            267   \n",
       "8                               10.5                            466   \n",
       "9                               12.1                            381   \n",
       "10                              11.9                            449   \n",
       "\n",
       "   householdIncome50000To74999USDPct householdIncome75000To99999USD  \\\n",
       "6                                7.7                            162   \n",
       "7                               12.2                            290   \n",
       "8                               14.6                            422   \n",
       "9                               18.0                            252   \n",
       "10                              14.5                            271   \n",
       "\n",
       "   householdIncome75000To99999USDPct householdIncome100000To149999USD  \\\n",
       "6                               19.8                              157   \n",
       "7                               13.3                              508   \n",
       "8                               13.2                              839   \n",
       "9                               11.9                              428   \n",
       "10                               8.8                              335   \n",
       "\n",
       "   householdIncome100000To149999USDPct householdIncome150000To199999USD  \\\n",
       "6                                 19.2                               70   \n",
       "7                                 23.3                              352   \n",
       "8                                 26.2                              572   \n",
       "9                                 20.2                              165   \n",
       "10                                10.8                              302   \n",
       "\n",
       "   householdIncome150000To199999USDPct householdIncomeMoreThan200000USD  \\\n",
       "6                                  8.5                               36   \n",
       "7                                 16.1                              348   \n",
       "8                                 17.9                              319   \n",
       "9                                  7.8                              128   \n",
       "10                                 9.8                              567   \n",
       "\n",
       "   householdIncomeMoreThan200000USDPct medianHouseholdIncomeUSD  \\\n",
       "6                                  4.4                    77039   \n",
       "7                                 16.0                   112368   \n",
       "8                                 10.0                   110455   \n",
       "9                                  6.0                    63625   \n",
       "10                                18.3                    71875   \n",
       "\n",
       "   meanHouseholdIncomeUSD stateFips countyFips        tract  \\\n",
       "6                   80888        06        073  06073008339   \n",
       "7                  130806        06        073  06073008347   \n",
       "8                  121883        06        073  06073008354   \n",
       "9                   90850        06        073  06073008505   \n",
       "10                 142032        06        073  06073017604   \n",
       "\n",
       "                              source aggregationLevel  \n",
       "6   American Community Survey 5 year            Tract  \n",
       "7   American Community Survey 5 year            Tract  \n",
       "8   American Community Survey 5 year            Tract  \n",
       "9   American Community Survey 5 year            Tract  \n",
       "10  American Community Survey 5 year            Tract  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Example data for San Diego County\n",
    "df[(df['stateFips'] == '06') & (df['countyFips'] == '073')].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Save data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv(NEO4J_IMPORT / \"03a-USCensusDP03IncomeTract.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(73056, 28)"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  }
 ],
 "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.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}