notebooks/dataprep/03a-USCensusDP05.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Demographic data from the American Community Survey\n",
    "\n",
    "**[Work in progress]**\n",
    "\n",
    "This notebook downloads [selected demographic data (DP05)](https://data.census.gov/cedsci/table?tid=ACSDP5Y2018.DP05) 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 demographic estimates for US](https://data.census.gov/cedsci/table?tid=ACSDP5Y2018.DP05)\n",
    "\n",
    "* [List of variables as HTML](https://api.census.gov/data/2018/acs/acs5/profile/groups/DP05.html) or [JSON](https://api.census.gov/data/2018/acs/acs5/profile/groups/DP05/)\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 DP05 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).\n",
    "\n",
    "The numbers in each subgroup below adds up to the total population"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "variables = {'DP05_0001E': 'totalPopulation',\n",
    "             \n",
    "             # sex\n",
    "             'DP05_0002E': 'male',\n",
    "             'DP05_0003E': 'female',\n",
    "             \n",
    "             # age\n",
    "             'DP05_0005E': 'age0_4',\n",
    "             'DP05_0006E': 'age5_9',\n",
    "             'DP05_0007E': 'age10_14',\n",
    "             'DP05_0008E': 'age15_19',\n",
    "             'DP05_0009E': 'age20_24',\n",
    "             'DP05_0010E': 'age25_34',\n",
    "             'DP05_0011E': 'age35_44',\n",
    "             'DP05_0012E': 'age45_54',\n",
    "             'DP05_0013E': 'age55_59',\n",
    "             'DP05_0014E': 'age60_64',\n",
    "             'DP05_0015E': 'age65_74',\n",
    "             'DP05_0016E': 'age75_84',\n",
    "             'DP05_0017E': 'age85_',\n",
    "\n",
    "              # race\n",
    "             'DP05_0037E': 'white',\n",
    "             'DP05_0038E': 'blackOrAfricanAmerican',\n",
    "             'DP05_0039E': 'americanIndianAndAlaskaNative',\n",
    "             'DP05_0044E': 'asian',\n",
    "             'DP05_0052E': 'nativeHawaiianAndOtherPacificIslander',\n",
    "             'DP05_0057E': 'otherRace',\n",
    "             'DP05_0058E': 'twoOrMoreRaces',\n",
    "             \n",
    "              # ethnicity\n",
    "             'DP05_0071E': 'hispanicOrLatino',\n",
    "             'DP05_0076E': 'notHispanicOrLatino'\n",
    "            }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "fields = \",\".join(variables.keys())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Download county-level data using US Census API"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "url_county = f'https://api.census.gov/data/2018/acs/acs5/profile?get={fields}&for=county:*'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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",
       "      <th>25</th>\n",
       "      <th>26</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>DP05_0001E</td>\n",
       "      <td>DP05_0002E</td>\n",
       "      <td>DP05_0003E</td>\n",
       "      <td>DP05_0005E</td>\n",
       "      <td>DP05_0006E</td>\n",
       "      <td>DP05_0007E</td>\n",
       "      <td>DP05_0008E</td>\n",
       "      <td>DP05_0009E</td>\n",
       "      <td>DP05_0010E</td>\n",
       "      <td>DP05_0011E</td>\n",
       "      <td>DP05_0012E</td>\n",
       "      <td>DP05_0013E</td>\n",
       "      <td>DP05_0014E</td>\n",
       "      <td>DP05_0015E</td>\n",
       "      <td>DP05_0016E</td>\n",
       "      <td>DP05_0017E</td>\n",
       "      <td>DP05_0037E</td>\n",
       "      <td>DP05_0038E</td>\n",
       "      <td>DP05_0039E</td>\n",
       "      <td>DP05_0044E</td>\n",
       "      <td>DP05_0052E</td>\n",
       "      <td>DP05_0057E</td>\n",
       "      <td>DP05_0058E</td>\n",
       "      <td>DP05_0071E</td>\n",
       "      <td>DP05_0076E</td>\n",
       "      <td>state</td>\n",
       "      <td>county</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3255</td>\n",
       "      <td>1649</td>\n",
       "      <td>1606</td>\n",
       "      <td>206</td>\n",
       "      <td>217</td>\n",
       "      <td>197</td>\n",
       "      <td>198</td>\n",
       "      <td>302</td>\n",
       "      <td>320</td>\n",
       "      <td>334</td>\n",
       "      <td>334</td>\n",
       "      <td>259</td>\n",
       "      <td>187</td>\n",
       "      <td>462</td>\n",
       "      <td>188</td>\n",
       "      <td>51</td>\n",
       "      <td>2346</td>\n",
       "      <td>23</td>\n",
       "      <td>237</td>\n",
       "      <td>302</td>\n",
       "      <td>17</td>\n",
       "      <td>6</td>\n",
       "      <td>324</td>\n",
       "      <td>337</td>\n",
       "      <td>2918</td>\n",
       "      <td>02</td>\n",
       "      <td>195</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8198</td>\n",
       "      <td>4388</td>\n",
       "      <td>3810</td>\n",
       "      <td>1086</td>\n",
       "      <td>867</td>\n",
       "      <td>894</td>\n",
       "      <td>750</td>\n",
       "      <td>654</td>\n",
       "      <td>1262</td>\n",
       "      <td>724</td>\n",
       "      <td>788</td>\n",
       "      <td>448</td>\n",
       "      <td>269</td>\n",
       "      <td>299</td>\n",
       "      <td>138</td>\n",
       "      <td>19</td>\n",
       "      <td>319</td>\n",
       "      <td>60</td>\n",
       "      <td>7480</td>\n",
       "      <td>32</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>302</td>\n",
       "      <td>81</td>\n",
       "      <td>8117</td>\n",
       "      <td>02</td>\n",
       "      <td>158</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4253913</td>\n",
       "      <td>2104145</td>\n",
       "      <td>2149768</td>\n",
       "      <td>279161</td>\n",
       "      <td>288352</td>\n",
       "      <td>298170</td>\n",
       "      <td>288250</td>\n",
       "      <td>288489</td>\n",
       "      <td>616000</td>\n",
       "      <td>557229</td>\n",
       "      <td>541839</td>\n",
       "      <td>251853</td>\n",
       "      <td>229293</td>\n",
       "      <td>358732</td>\n",
       "      <td>183578</td>\n",
       "      <td>72967</td>\n",
       "      <td>3301814</td>\n",
       "      <td>232690</td>\n",
       "      <td>82699</td>\n",
       "      <td>176740</td>\n",
       "      <td>9405</td>\n",
       "      <td>296569</td>\n",
       "      <td>153996</td>\n",
       "      <td>1311091</td>\n",
       "      <td>2942822</td>\n",
       "      <td>04</td>\n",
       "      <td>013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>37879</td>\n",
       "      <td>20330</td>\n",
       "      <td>17549</td>\n",
       "      <td>2819</td>\n",
       "      <td>2781</td>\n",
       "      <td>2965</td>\n",
       "      <td>2957</td>\n",
       "      <td>2826</td>\n",
       "      <td>5722</td>\n",
       "      <td>4751</td>\n",
       "      <td>4161</td>\n",
       "      <td>1814</td>\n",
       "      <td>2071</td>\n",
       "      <td>2882</td>\n",
       "      <td>1562</td>\n",
       "      <td>568</td>\n",
       "      <td>29917</td>\n",
       "      <td>752</td>\n",
       "      <td>4808</td>\n",
       "      <td>246</td>\n",
       "      <td>51</td>\n",
       "      <td>996</td>\n",
       "      <td>1109</td>\n",
       "      <td>12453</td>\n",
       "      <td>25426</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  DP05_0001E  DP05_0002E  DP05_0003E  DP05_0005E  DP05_0006E  DP05_0007E   \n",
       "1        3255        1649        1606         206         217         197   \n",
       "2        8198        4388        3810        1086         867         894   \n",
       "3     4253913     2104145     2149768      279161      288352      298170   \n",
       "4       37879       20330       17549        2819        2781        2965   \n",
       "\n",
       "           6           7           8           9           10          11  \\\n",
       "0  DP05_0008E  DP05_0009E  DP05_0010E  DP05_0011E  DP05_0012E  DP05_0013E   \n",
       "1         198         302         320         334         334         259   \n",
       "2         750         654        1262         724         788         448   \n",
       "3      288250      288489      616000      557229      541839      251853   \n",
       "4        2957        2826        5722        4751        4161        1814   \n",
       "\n",
       "           12          13          14          15          16          17  \\\n",
       "0  DP05_0014E  DP05_0015E  DP05_0016E  DP05_0017E  DP05_0037E  DP05_0038E   \n",
       "1         187         462         188          51        2346          23   \n",
       "2         269         299         138          19         319          60   \n",
       "3      229293      358732      183578       72967     3301814      232690   \n",
       "4        2071        2882        1562         568       29917         752   \n",
       "\n",
       "           18          19          20          21          22          23  \\\n",
       "0  DP05_0039E  DP05_0044E  DP05_0052E  DP05_0057E  DP05_0058E  DP05_0071E   \n",
       "1         237         302          17           6         324         337   \n",
       "2        7480          32           2           3         302          81   \n",
       "3       82699      176740        9405      296569      153996     1311091   \n",
       "4        4808         246          51         996        1109       12453   \n",
       "\n",
       "           24     25      26  \n",
       "0  DP05_0076E  state  county  \n",
       "1        2918     02     195  \n",
       "2        8117     02     158  \n",
       "3     2942822     04     013  \n",
       "4       25426     04     009  "
      ]
     },
     "execution_count": 7,
     "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": 8,
   "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": 9,
   "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": 10,
   "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": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>totalPopulation</th>\n",
       "      <th>male</th>\n",
       "      <th>female</th>\n",
       "      <th>age0_4</th>\n",
       "      <th>age5_9</th>\n",
       "      <th>age10_14</th>\n",
       "      <th>age15_19</th>\n",
       "      <th>age20_24</th>\n",
       "      <th>age25_34</th>\n",
       "      <th>age35_44</th>\n",
       "      <th>age45_54</th>\n",
       "      <th>age55_59</th>\n",
       "      <th>age60_64</th>\n",
       "      <th>age65_74</th>\n",
       "      <th>age75_84</th>\n",
       "      <th>age85_</th>\n",
       "      <th>white</th>\n",
       "      <th>blackOrAfricanAmerican</th>\n",
       "      <th>americanIndianAndAlaskaNative</th>\n",
       "      <th>asian</th>\n",
       "      <th>nativeHawaiianAndOtherPacificIslander</th>\n",
       "      <th>otherRace</th>\n",
       "      <th>twoOrMoreRaces</th>\n",
       "      <th>hispanicOrLatino</th>\n",
       "      <th>notHispanicOrLatino</th>\n",
       "      <th>stateFips</th>\n",
       "      <th>countyFips</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3255</td>\n",
       "      <td>1649</td>\n",
       "      <td>1606</td>\n",
       "      <td>206</td>\n",
       "      <td>217</td>\n",
       "      <td>197</td>\n",
       "      <td>198</td>\n",
       "      <td>302</td>\n",
       "      <td>320</td>\n",
       "      <td>334</td>\n",
       "      <td>334</td>\n",
       "      <td>259</td>\n",
       "      <td>187</td>\n",
       "      <td>462</td>\n",
       "      <td>188</td>\n",
       "      <td>51</td>\n",
       "      <td>2346</td>\n",
       "      <td>23</td>\n",
       "      <td>237</td>\n",
       "      <td>302</td>\n",
       "      <td>17</td>\n",
       "      <td>6</td>\n",
       "      <td>324</td>\n",
       "      <td>337</td>\n",
       "      <td>2918</td>\n",
       "      <td>02</td>\n",
       "      <td>195</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8198</td>\n",
       "      <td>4388</td>\n",
       "      <td>3810</td>\n",
       "      <td>1086</td>\n",
       "      <td>867</td>\n",
       "      <td>894</td>\n",
       "      <td>750</td>\n",
       "      <td>654</td>\n",
       "      <td>1262</td>\n",
       "      <td>724</td>\n",
       "      <td>788</td>\n",
       "      <td>448</td>\n",
       "      <td>269</td>\n",
       "      <td>299</td>\n",
       "      <td>138</td>\n",
       "      <td>19</td>\n",
       "      <td>319</td>\n",
       "      <td>60</td>\n",
       "      <td>7480</td>\n",
       "      <td>32</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>302</td>\n",
       "      <td>81</td>\n",
       "      <td>8117</td>\n",
       "      <td>02</td>\n",
       "      <td>158</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4253913</td>\n",
       "      <td>2104145</td>\n",
       "      <td>2149768</td>\n",
       "      <td>279161</td>\n",
       "      <td>288352</td>\n",
       "      <td>298170</td>\n",
       "      <td>288250</td>\n",
       "      <td>288489</td>\n",
       "      <td>616000</td>\n",
       "      <td>557229</td>\n",
       "      <td>541839</td>\n",
       "      <td>251853</td>\n",
       "      <td>229293</td>\n",
       "      <td>358732</td>\n",
       "      <td>183578</td>\n",
       "      <td>72967</td>\n",
       "      <td>3301814</td>\n",
       "      <td>232690</td>\n",
       "      <td>82699</td>\n",
       "      <td>176740</td>\n",
       "      <td>9405</td>\n",
       "      <td>296569</td>\n",
       "      <td>153996</td>\n",
       "      <td>1311091</td>\n",
       "      <td>2942822</td>\n",
       "      <td>04</td>\n",
       "      <td>013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>37879</td>\n",
       "      <td>20330</td>\n",
       "      <td>17549</td>\n",
       "      <td>2819</td>\n",
       "      <td>2781</td>\n",
       "      <td>2965</td>\n",
       "      <td>2957</td>\n",
       "      <td>2826</td>\n",
       "      <td>5722</td>\n",
       "      <td>4751</td>\n",
       "      <td>4161</td>\n",
       "      <td>1814</td>\n",
       "      <td>2071</td>\n",
       "      <td>2882</td>\n",
       "      <td>1562</td>\n",
       "      <td>568</td>\n",
       "      <td>29917</td>\n",
       "      <td>752</td>\n",
       "      <td>4808</td>\n",
       "      <td>246</td>\n",
       "      <td>51</td>\n",
       "      <td>996</td>\n",
       "      <td>1109</td>\n",
       "      <td>12453</td>\n",
       "      <td>25426</td>\n",
       "      <td>04</td>\n",
       "      <td>009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>46584</td>\n",
       "      <td>22285</td>\n",
       "      <td>24299</td>\n",
       "      <td>3342</td>\n",
       "      <td>3836</td>\n",
       "      <td>3350</td>\n",
       "      <td>3793</td>\n",
       "      <td>3035</td>\n",
       "      <td>4924</td>\n",
       "      <td>5204</td>\n",
       "      <td>5500</td>\n",
       "      <td>2784</td>\n",
       "      <td>2925</td>\n",
       "      <td>4644</td>\n",
       "      <td>2396</td>\n",
       "      <td>851</td>\n",
       "      <td>39656</td>\n",
       "      <td>248</td>\n",
       "      <td>362</td>\n",
       "      <td>430</td>\n",
       "      <td>0</td>\n",
       "      <td>5376</td>\n",
       "      <td>512</td>\n",
       "      <td>38899</td>\n",
       "      <td>7685</td>\n",
       "      <td>04</td>\n",
       "      <td>023</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  totalPopulation     male   female  age0_4  age5_9 age10_14 age15_19  \\\n",
       "1            3255     1649     1606     206     217      197      198   \n",
       "2            8198     4388     3810    1086     867      894      750   \n",
       "3         4253913  2104145  2149768  279161  288352   298170   288250   \n",
       "4           37879    20330    17549    2819    2781     2965     2957   \n",
       "5           46584    22285    24299    3342    3836     3350     3793   \n",
       "\n",
       "  age20_24 age25_34 age35_44 age45_54 age55_59 age60_64 age65_74 age75_84  \\\n",
       "1      302      320      334      334      259      187      462      188   \n",
       "2      654     1262      724      788      448      269      299      138   \n",
       "3   288489   616000   557229   541839   251853   229293   358732   183578   \n",
       "4     2826     5722     4751     4161     1814     2071     2882     1562   \n",
       "5     3035     4924     5204     5500     2784     2925     4644     2396   \n",
       "\n",
       "  age85_    white blackOrAfricanAmerican americanIndianAndAlaskaNative  \\\n",
       "1     51     2346                     23                           237   \n",
       "2     19      319                     60                          7480   \n",
       "3  72967  3301814                 232690                         82699   \n",
       "4    568    29917                    752                          4808   \n",
       "5    851    39656                    248                           362   \n",
       "\n",
       "    asian nativeHawaiianAndOtherPacificIslander otherRace twoOrMoreRaces  \\\n",
       "1     302                                    17         6            324   \n",
       "2      32                                     2         3            302   \n",
       "3  176740                                  9405    296569         153996   \n",
       "4     246                                    51       996           1109   \n",
       "5     430                                     0      5376            512   \n",
       "\n",
       "  hispanicOrLatino notHispanicOrLatino stateFips countyFips  \n",
       "1              337                2918        02        195  \n",
       "2               81                8117        02        158  \n",
       "3          1311091             2942822        04        013  \n",
       "4            12453               25426        04        009  \n",
       "5            38899                7685        04        023  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>totalPopulation</th>\n",
       "      <th>male</th>\n",
       "      <th>female</th>\n",
       "      <th>age0_4</th>\n",
       "      <th>age5_9</th>\n",
       "      <th>age10_14</th>\n",
       "      <th>age15_19</th>\n",
       "      <th>age20_24</th>\n",
       "      <th>age25_34</th>\n",
       "      <th>age35_44</th>\n",
       "      <th>age45_54</th>\n",
       "      <th>age55_59</th>\n",
       "      <th>age60_64</th>\n",
       "      <th>age65_74</th>\n",
       "      <th>age75_84</th>\n",
       "      <th>age85_</th>\n",
       "      <th>white</th>\n",
       "      <th>blackOrAfricanAmerican</th>\n",
       "      <th>americanIndianAndAlaskaNative</th>\n",
       "      <th>asian</th>\n",
       "      <th>nativeHawaiianAndOtherPacificIslander</th>\n",
       "      <th>otherRace</th>\n",
       "      <th>twoOrMoreRaces</th>\n",
       "      <th>hispanicOrLatino</th>\n",
       "      <th>notHispanicOrLatino</th>\n",
       "      <th>stateFips</th>\n",
       "      <th>countyFips</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>897</th>\n",
       "      <td>3302833</td>\n",
       "      <td>1661931</td>\n",
       "      <td>1640902</td>\n",
       "      <td>211969</td>\n",
       "      <td>198148</td>\n",
       "      <td>197726</td>\n",
       "      <td>209496</td>\n",
       "      <td>262118</td>\n",
       "      <td>541385</td>\n",
       "      <td>436855</td>\n",
       "      <td>420221</td>\n",
       "      <td>201666</td>\n",
       "      <td>183654</td>\n",
       "      <td>251516</td>\n",
       "      <td>127904</td>\n",
       "      <td>60175</td>\n",
       "      <td>2335447</td>\n",
       "      <td>166412</td>\n",
       "      <td>20980</td>\n",
       "      <td>390418</td>\n",
       "      <td>13903</td>\n",
       "      <td>205307</td>\n",
       "      <td>170366</td>\n",
       "      <td>1106925</td>\n",
       "      <td>2195908</td>\n",
       "      <td>06</td>\n",
       "      <td>073</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    totalPopulation     male   female  age0_4  age5_9 age10_14 age15_19  \\\n",
       "897         3302833  1661931  1640902  211969  198148   197726   209496   \n",
       "\n",
       "    age20_24 age25_34 age35_44 age45_54 age55_59 age60_64 age65_74 age75_84  \\\n",
       "897   262118   541385   436855   420221   201666   183654   251516   127904   \n",
       "\n",
       "    age85_    white blackOrAfricanAmerican americanIndianAndAlaskaNative  \\\n",
       "897  60175  2335447                 166412                         20980   \n",
       "\n",
       "      asian nativeHawaiianAndOtherPacificIslander otherRace twoOrMoreRaces  \\\n",
       "897  390418                                 13903    205307         170366   \n",
       "\n",
       "    hispanicOrLatino notHispanicOrLatino stateFips countyFips  \n",
       "897          1106925             2195908        06        073  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Example data\n",
    "df[(df['stateFips'] == '06') & (df['countyFips'] == '073')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv(NEO4J_IMPORT / \"03a-USCensusDP05Admin2.csv\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Data Checks for US\n",
    "50 states + District of Columbia"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of states: 51\n"
     ]
    }
   ],
   "source": [
    "print('Number of states:', len(stateFips)) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "322903030"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['totalPopulation'] = df['totalPopulation'].astype(int)\n",
    "df['totalPopulation'].sum()"
   ]
  },
  {
   "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",
       "      <th>25</th>\n",
       "      <th>26</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>DP05_0001E</td>\n",
       "      <td>DP05_0002E</td>\n",
       "      <td>DP05_0003E</td>\n",
       "      <td>DP05_0005E</td>\n",
       "      <td>DP05_0006E</td>\n",
       "      <td>DP05_0007E</td>\n",
       "      <td>DP05_0008E</td>\n",
       "      <td>DP05_0009E</td>\n",
       "      <td>DP05_0010E</td>\n",
       "      <td>DP05_0011E</td>\n",
       "      <td>DP05_0012E</td>\n",
       "      <td>DP05_0013E</td>\n",
       "      <td>DP05_0014E</td>\n",
       "      <td>DP05_0015E</td>\n",
       "      <td>DP05_0016E</td>\n",
       "      <td>DP05_0017E</td>\n",
       "      <td>DP05_0037E</td>\n",
       "      <td>DP05_0038E</td>\n",
       "      <td>DP05_0039E</td>\n",
       "      <td>DP05_0044E</td>\n",
       "      <td>DP05_0052E</td>\n",
       "      <td>DP05_0057E</td>\n",
       "      <td>DP05_0058E</td>\n",
       "      <td>DP05_0071E</td>\n",
       "      <td>DP05_0076E</td>\n",
       "      <td>state</td>\n",
       "      <td>zip code tabulation area</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2301</td>\n",
       "      <td>1148</td>\n",
       "      <td>1153</td>\n",
       "      <td>131</td>\n",
       "      <td>209</td>\n",
       "      <td>52</td>\n",
       "      <td>120</td>\n",
       "      <td>80</td>\n",
       "      <td>325</td>\n",
       "      <td>232</td>\n",
       "      <td>340</td>\n",
       "      <td>186</td>\n",
       "      <td>134</td>\n",
       "      <td>319</td>\n",
       "      <td>121</td>\n",
       "      <td>52</td>\n",
       "      <td>1931</td>\n",
       "      <td>350</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>16</td>\n",
       "      <td>72</td>\n",
       "      <td>2229</td>\n",
       "      <td>51</td>\n",
       "      <td>23833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1268</td>\n",
       "      <td>543</td>\n",
       "      <td>725</td>\n",
       "      <td>16</td>\n",
       "      <td>53</td>\n",
       "      <td>126</td>\n",
       "      <td>145</td>\n",
       "      <td>65</td>\n",
       "      <td>56</td>\n",
       "      <td>196</td>\n",
       "      <td>225</td>\n",
       "      <td>93</td>\n",
       "      <td>37</td>\n",
       "      <td>215</td>\n",
       "      <td>41</td>\n",
       "      <td>0</td>\n",
       "      <td>851</td>\n",
       "      <td>417</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1268</td>\n",
       "      <td>51</td>\n",
       "      <td>23850</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>13262</td>\n",
       "      <td>6184</td>\n",
       "      <td>7078</td>\n",
       "      <td>771</td>\n",
       "      <td>999</td>\n",
       "      <td>854</td>\n",
       "      <td>720</td>\n",
       "      <td>727</td>\n",
       "      <td>1616</td>\n",
       "      <td>1336</td>\n",
       "      <td>1718</td>\n",
       "      <td>692</td>\n",
       "      <td>1275</td>\n",
       "      <td>1500</td>\n",
       "      <td>682</td>\n",
       "      <td>372</td>\n",
       "      <td>6348</td>\n",
       "      <td>6352</td>\n",
       "      <td>18</td>\n",
       "      <td>27</td>\n",
       "      <td>0</td>\n",
       "      <td>90</td>\n",
       "      <td>427</td>\n",
       "      <td>92</td>\n",
       "      <td>13170</td>\n",
       "      <td>51</td>\n",
       "      <td>23851</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3708</td>\n",
       "      <td>1487</td>\n",
       "      <td>2221</td>\n",
       "      <td>242</td>\n",
       "      <td>158</td>\n",
       "      <td>203</td>\n",
       "      <td>187</td>\n",
       "      <td>264</td>\n",
       "      <td>323</td>\n",
       "      <td>233</td>\n",
       "      <td>600</td>\n",
       "      <td>258</td>\n",
       "      <td>290</td>\n",
       "      <td>551</td>\n",
       "      <td>194</td>\n",
       "      <td>205</td>\n",
       "      <td>1503</td>\n",
       "      <td>2158</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>17</td>\n",
       "      <td>30</td>\n",
       "      <td>108</td>\n",
       "      <td>3600</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  DP05_0001E  DP05_0002E  DP05_0003E  DP05_0005E  DP05_0006E  DP05_0007E   \n",
       "1        2301        1148        1153         131         209          52   \n",
       "2        1268         543         725          16          53         126   \n",
       "3       13262        6184        7078         771         999         854   \n",
       "4        3708        1487        2221         242         158         203   \n",
       "\n",
       "           6           7           8           9           10          11  \\\n",
       "0  DP05_0008E  DP05_0009E  DP05_0010E  DP05_0011E  DP05_0012E  DP05_0013E   \n",
       "1         120          80         325         232         340         186   \n",
       "2         145          65          56         196         225          93   \n",
       "3         720         727        1616        1336        1718         692   \n",
       "4         187         264         323         233         600         258   \n",
       "\n",
       "           12          13          14          15          16          17  \\\n",
       "0  DP05_0014E  DP05_0015E  DP05_0016E  DP05_0017E  DP05_0037E  DP05_0038E   \n",
       "1         134         319         121          52        1931         350   \n",
       "2          37         215          41           0         851         417   \n",
       "3        1275        1500         682         372        6348        6352   \n",
       "4         290         551         194         205        1503        2158   \n",
       "\n",
       "           18          19          20          21          22          23  \\\n",
       "0  DP05_0039E  DP05_0044E  DP05_0052E  DP05_0057E  DP05_0058E  DP05_0071E   \n",
       "1           0           4           0           0          16          72   \n",
       "2           0           0           0           0           0           0   \n",
       "3          18          27           0          90         427          92   \n",
       "4           0           0           0          17          30         108   \n",
       "\n",
       "           24     25                        26  \n",
       "0  DP05_0076E  state  zip code tabulation area  \n",
       "1        2229     51                     23833  \n",
       "2        1268     51                     23850  \n",
       "3       13170     51                     23851  \n",
       "4        3600     51                     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>totalPopulation</th>\n",
       "      <th>male</th>\n",
       "      <th>female</th>\n",
       "      <th>age0_4</th>\n",
       "      <th>age5_9</th>\n",
       "      <th>age10_14</th>\n",
       "      <th>age15_19</th>\n",
       "      <th>age20_24</th>\n",
       "      <th>age25_34</th>\n",
       "      <th>age35_44</th>\n",
       "      <th>age45_54</th>\n",
       "      <th>age55_59</th>\n",
       "      <th>age60_64</th>\n",
       "      <th>age65_74</th>\n",
       "      <th>age75_84</th>\n",
       "      <th>age85_</th>\n",
       "      <th>white</th>\n",
       "      <th>blackOrAfricanAmerican</th>\n",
       "      <th>americanIndianAndAlaskaNative</th>\n",
       "      <th>asian</th>\n",
       "      <th>nativeHawaiianAndOtherPacificIslander</th>\n",
       "      <th>otherRace</th>\n",
       "      <th>twoOrMoreRaces</th>\n",
       "      <th>hispanicOrLatino</th>\n",
       "      <th>notHispanicOrLatino</th>\n",
       "      <th>stateFips</th>\n",
       "      <th>postalCode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2301</td>\n",
       "      <td>1148</td>\n",
       "      <td>1153</td>\n",
       "      <td>131</td>\n",
       "      <td>209</td>\n",
       "      <td>52</td>\n",
       "      <td>120</td>\n",
       "      <td>80</td>\n",
       "      <td>325</td>\n",
       "      <td>232</td>\n",
       "      <td>340</td>\n",
       "      <td>186</td>\n",
       "      <td>134</td>\n",
       "      <td>319</td>\n",
       "      <td>121</td>\n",
       "      <td>52</td>\n",
       "      <td>1931</td>\n",
       "      <td>350</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>16</td>\n",
       "      <td>72</td>\n",
       "      <td>2229</td>\n",
       "      <td>51</td>\n",
       "      <td>23833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1268</td>\n",
       "      <td>543</td>\n",
       "      <td>725</td>\n",
       "      <td>16</td>\n",
       "      <td>53</td>\n",
       "      <td>126</td>\n",
       "      <td>145</td>\n",
       "      <td>65</td>\n",
       "      <td>56</td>\n",
       "      <td>196</td>\n",
       "      <td>225</td>\n",
       "      <td>93</td>\n",
       "      <td>37</td>\n",
       "      <td>215</td>\n",
       "      <td>41</td>\n",
       "      <td>0</td>\n",
       "      <td>851</td>\n",
       "      <td>417</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1268</td>\n",
       "      <td>51</td>\n",
       "      <td>23850</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>13262</td>\n",
       "      <td>6184</td>\n",
       "      <td>7078</td>\n",
       "      <td>771</td>\n",
       "      <td>999</td>\n",
       "      <td>854</td>\n",
       "      <td>720</td>\n",
       "      <td>727</td>\n",
       "      <td>1616</td>\n",
       "      <td>1336</td>\n",
       "      <td>1718</td>\n",
       "      <td>692</td>\n",
       "      <td>1275</td>\n",
       "      <td>1500</td>\n",
       "      <td>682</td>\n",
       "      <td>372</td>\n",
       "      <td>6348</td>\n",
       "      <td>6352</td>\n",
       "      <td>18</td>\n",
       "      <td>27</td>\n",
       "      <td>0</td>\n",
       "      <td>90</td>\n",
       "      <td>427</td>\n",
       "      <td>92</td>\n",
       "      <td>13170</td>\n",
       "      <td>51</td>\n",
       "      <td>23851</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3708</td>\n",
       "      <td>1487</td>\n",
       "      <td>2221</td>\n",
       "      <td>242</td>\n",
       "      <td>158</td>\n",
       "      <td>203</td>\n",
       "      <td>187</td>\n",
       "      <td>264</td>\n",
       "      <td>323</td>\n",
       "      <td>233</td>\n",
       "      <td>600</td>\n",
       "      <td>258</td>\n",
       "      <td>290</td>\n",
       "      <td>551</td>\n",
       "      <td>194</td>\n",
       "      <td>205</td>\n",
       "      <td>1503</td>\n",
       "      <td>2158</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>17</td>\n",
       "      <td>30</td>\n",
       "      <td>108</td>\n",
       "      <td>3600</td>\n",
       "      <td>51</td>\n",
       "      <td>23890</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>268</td>\n",
       "      <td>114</td>\n",
       "      <td>154</td>\n",
       "      <td>45</td>\n",
       "      <td>26</td>\n",
       "      <td>27</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>70</td>\n",
       "      <td>51</td>\n",
       "      <td>26</td>\n",
       "      <td>0</td>\n",
       "      <td>17</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>268</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>147</td>\n",
       "      <td>121</td>\n",
       "      <td>51</td>\n",
       "      <td>23302</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  totalPopulation  male female age0_4 age5_9 age10_14 age15_19 age20_24  \\\n",
       "1            2301  1148   1153    131    209       52      120       80   \n",
       "2            1268   543    725     16     53      126      145       65   \n",
       "3           13262  6184   7078    771    999      854      720      727   \n",
       "4            3708  1487   2221    242    158      203      187      264   \n",
       "5             268   114    154     45     26       27        6        0   \n",
       "\n",
       "  age25_34 age35_44 age45_54 age55_59 age60_64 age65_74 age75_84 age85_ white  \\\n",
       "1      325      232      340      186      134      319      121     52  1931   \n",
       "2       56      196      225       93       37      215       41      0   851   \n",
       "3     1616     1336     1718      692     1275     1500      682    372  6348   \n",
       "4      323      233      600      258      290      551      194    205  1503   \n",
       "5        0       70       51       26        0       17        0      0   268   \n",
       "\n",
       "  blackOrAfricanAmerican americanIndianAndAlaskaNative asian  \\\n",
       "1                    350                             0     4   \n",
       "2                    417                             0     0   \n",
       "3                   6352                            18    27   \n",
       "4                   2158                             0     0   \n",
       "5                      0                             0     0   \n",
       "\n",
       "  nativeHawaiianAndOtherPacificIslander otherRace twoOrMoreRaces  \\\n",
       "1                                     0         0             16   \n",
       "2                                     0         0              0   \n",
       "3                                     0        90            427   \n",
       "4                                     0        17             30   \n",
       "5                                     0         0              0   \n",
       "\n",
       "  hispanicOrLatino notHispanicOrLatino stateFips postalCode  \n",
       "1               72                2229        51      23833  \n",
       "2                0                1268        51      23850  \n",
       "3               92               13170        51      23851  \n",
       "4              108                3600        51      23890  \n",
       "5              147                 121        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>totalPopulation</th>\n",
       "      <th>male</th>\n",
       "      <th>female</th>\n",
       "      <th>age0_4</th>\n",
       "      <th>age5_9</th>\n",
       "      <th>age10_14</th>\n",
       "      <th>age15_19</th>\n",
       "      <th>age20_24</th>\n",
       "      <th>age25_34</th>\n",
       "      <th>age35_44</th>\n",
       "      <th>age45_54</th>\n",
       "      <th>age55_59</th>\n",
       "      <th>age60_64</th>\n",
       "      <th>age65_74</th>\n",
       "      <th>age75_84</th>\n",
       "      <th>age85_</th>\n",
       "      <th>white</th>\n",
       "      <th>blackOrAfricanAmerican</th>\n",
       "      <th>americanIndianAndAlaskaNative</th>\n",
       "      <th>asian</th>\n",
       "      <th>nativeHawaiianAndOtherPacificIslander</th>\n",
       "      <th>otherRace</th>\n",
       "      <th>twoOrMoreRaces</th>\n",
       "      <th>hispanicOrLatino</th>\n",
       "      <th>notHispanicOrLatino</th>\n",
       "      <th>stateFips</th>\n",
       "      <th>postalCode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>24526</th>\n",
       "      <td>19909</td>\n",
       "      <td>9599</td>\n",
       "      <td>10310</td>\n",
       "      <td>710</td>\n",
       "      <td>1014</td>\n",
       "      <td>1482</td>\n",
       "      <td>1066</td>\n",
       "      <td>519</td>\n",
       "      <td>1956</td>\n",
       "      <td>2292</td>\n",
       "      <td>3143</td>\n",
       "      <td>1282</td>\n",
       "      <td>1093</td>\n",
       "      <td>2562</td>\n",
       "      <td>1695</td>\n",
       "      <td>1095</td>\n",
       "      <td>16911</td>\n",
       "      <td>198</td>\n",
       "      <td>21</td>\n",
       "      <td>1782</td>\n",
       "      <td>0</td>\n",
       "      <td>313</td>\n",
       "      <td>684</td>\n",
       "      <td>829</td>\n",
       "      <td>19080</td>\n",
       "      <td>06</td>\n",
       "      <td>90210</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      totalPopulation  male female age0_4 age5_9 age10_14 age15_19 age20_24  \\\n",
       "24526           19909  9599  10310    710   1014     1482     1066      519   \n",
       "\n",
       "      age25_34 age35_44 age45_54 age55_59 age60_64 age65_74 age75_84 age85_  \\\n",
       "24526     1956     2292     3143     1282     1093     2562     1695   1095   \n",
       "\n",
       "       white blackOrAfricanAmerican americanIndianAndAlaskaNative asian  \\\n",
       "24526  16911                    198                            21  1782   \n",
       "\n",
       "      nativeHawaiianAndOtherPacificIslander otherRace twoOrMoreRaces  \\\n",
       "24526                                     0       313            684   \n",
       "\n",
       "      hispanicOrLatino notHispanicOrLatino stateFips postalCode  \n",
       "24526              829               19080        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-USCensusDP05Zip.csv\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Check Data\n",
    "\n",
    "Note, this includes zip codes for Puerto Rico"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "326274356"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['totalPopulation'] = df['totalPopulation'].astype(int)\n",
    "df['totalPopulation'].sum()"
   ]
  },
  {
   "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": 25,
   "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": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.concat((get_tract_data(state) for state in stateFips))\n",
    "df.fillna('', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['tract'] = df['stateFips'] + df['countyFips'] + df['tract']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['source'] = 'American Community Survey 5 year'\n",
    "df['aggregationLevel'] = 'Tract'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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>totalPopulation</th>\n",
       "      <th>male</th>\n",
       "      <th>female</th>\n",
       "      <th>age0_4</th>\n",
       "      <th>age5_9</th>\n",
       "      <th>age10_14</th>\n",
       "      <th>age15_19</th>\n",
       "      <th>age20_24</th>\n",
       "      <th>age25_34</th>\n",
       "      <th>age35_44</th>\n",
       "      <th>age45_54</th>\n",
       "      <th>age55_59</th>\n",
       "      <th>age60_64</th>\n",
       "      <th>age65_74</th>\n",
       "      <th>age75_84</th>\n",
       "      <th>age85_</th>\n",
       "      <th>white</th>\n",
       "      <th>blackOrAfricanAmerican</th>\n",
       "      <th>americanIndianAndAlaskaNative</th>\n",
       "      <th>asian</th>\n",
       "      <th>nativeHawaiianAndOtherPacificIslander</th>\n",
       "      <th>otherRace</th>\n",
       "      <th>twoOrMoreRaces</th>\n",
       "      <th>hispanicOrLatino</th>\n",
       "      <th>notHispanicOrLatino</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>2053</td>\n",
       "      <td>1195</td>\n",
       "      <td>858</td>\n",
       "      <td>79</td>\n",
       "      <td>42</td>\n",
       "      <td>47</td>\n",
       "      <td>107</td>\n",
       "      <td>778</td>\n",
       "      <td>479</td>\n",
       "      <td>262</td>\n",
       "      <td>151</td>\n",
       "      <td>39</td>\n",
       "      <td>23</td>\n",
       "      <td>31</td>\n",
       "      <td>10</td>\n",
       "      <td>5</td>\n",
       "      <td>1154</td>\n",
       "      <td>56</td>\n",
       "      <td>0</td>\n",
       "      <td>614</td>\n",
       "      <td>3</td>\n",
       "      <td>134</td>\n",
       "      <td>92</td>\n",
       "      <td>324</td>\n",
       "      <td>1729</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>7037</td>\n",
       "      <td>3708</td>\n",
       "      <td>3329</td>\n",
       "      <td>461</td>\n",
       "      <td>510</td>\n",
       "      <td>360</td>\n",
       "      <td>317</td>\n",
       "      <td>206</td>\n",
       "      <td>1237</td>\n",
       "      <td>970</td>\n",
       "      <td>943</td>\n",
       "      <td>488</td>\n",
       "      <td>449</td>\n",
       "      <td>752</td>\n",
       "      <td>316</td>\n",
       "      <td>28</td>\n",
       "      <td>2388</td>\n",
       "      <td>216</td>\n",
       "      <td>12</td>\n",
       "      <td>3166</td>\n",
       "      <td>29</td>\n",
       "      <td>551</td>\n",
       "      <td>675</td>\n",
       "      <td>974</td>\n",
       "      <td>6063</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>9862</td>\n",
       "      <td>4507</td>\n",
       "      <td>5355</td>\n",
       "      <td>958</td>\n",
       "      <td>561</td>\n",
       "      <td>380</td>\n",
       "      <td>347</td>\n",
       "      <td>674</td>\n",
       "      <td>2658</td>\n",
       "      <td>1434</td>\n",
       "      <td>1336</td>\n",
       "      <td>366</td>\n",
       "      <td>411</td>\n",
       "      <td>423</td>\n",
       "      <td>200</td>\n",
       "      <td>114</td>\n",
       "      <td>2812</td>\n",
       "      <td>452</td>\n",
       "      <td>0</td>\n",
       "      <td>4791</td>\n",
       "      <td>121</td>\n",
       "      <td>535</td>\n",
       "      <td>1151</td>\n",
       "      <td>1489</td>\n",
       "      <td>8373</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>6576</td>\n",
       "      <td>3398</td>\n",
       "      <td>3178</td>\n",
       "      <td>387</td>\n",
       "      <td>329</td>\n",
       "      <td>192</td>\n",
       "      <td>194</td>\n",
       "      <td>675</td>\n",
       "      <td>1130</td>\n",
       "      <td>1005</td>\n",
       "      <td>955</td>\n",
       "      <td>518</td>\n",
       "      <td>447</td>\n",
       "      <td>326</td>\n",
       "      <td>180</td>\n",
       "      <td>238</td>\n",
       "      <td>3914</td>\n",
       "      <td>178</td>\n",
       "      <td>26</td>\n",
       "      <td>1655</td>\n",
       "      <td>0</td>\n",
       "      <td>562</td>\n",
       "      <td>241</td>\n",
       "      <td>1791</td>\n",
       "      <td>4785</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>7427</td>\n",
       "      <td>3468</td>\n",
       "      <td>3959</td>\n",
       "      <td>635</td>\n",
       "      <td>455</td>\n",
       "      <td>334</td>\n",
       "      <td>527</td>\n",
       "      <td>289</td>\n",
       "      <td>1075</td>\n",
       "      <td>842</td>\n",
       "      <td>1062</td>\n",
       "      <td>389</td>\n",
       "      <td>255</td>\n",
       "      <td>656</td>\n",
       "      <td>526</td>\n",
       "      <td>382</td>\n",
       "      <td>6085</td>\n",
       "      <td>255</td>\n",
       "      <td>73</td>\n",
       "      <td>269</td>\n",
       "      <td>0</td>\n",
       "      <td>398</td>\n",
       "      <td>347</td>\n",
       "      <td>1375</td>\n",
       "      <td>6052</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": [
       "   totalPopulation  male female age0_4 age5_9 age10_14 age15_19 age20_24  \\\n",
       "6             2053  1195    858     79     42       47      107      778   \n",
       "7             7037  3708   3329    461    510      360      317      206   \n",
       "8             9862  4507   5355    958    561      380      347      674   \n",
       "9             6576  3398   3178    387    329      192      194      675   \n",
       "10            7427  3468   3959    635    455      334      527      289   \n",
       "\n",
       "   age25_34 age35_44 age45_54 age55_59 age60_64 age65_74 age75_84 age85_  \\\n",
       "6       479      262      151       39       23       31       10      5   \n",
       "7      1237      970      943      488      449      752      316     28   \n",
       "8      2658     1434     1336      366      411      423      200    114   \n",
       "9      1130     1005      955      518      447      326      180    238   \n",
       "10     1075      842     1062      389      255      656      526    382   \n",
       "\n",
       "   white blackOrAfricanAmerican americanIndianAndAlaskaNative asian  \\\n",
       "6   1154                     56                             0   614   \n",
       "7   2388                    216                            12  3166   \n",
       "8   2812                    452                             0  4791   \n",
       "9   3914                    178                            26  1655   \n",
       "10  6085                    255                            73   269   \n",
       "\n",
       "   nativeHawaiianAndOtherPacificIslander otherRace twoOrMoreRaces  \\\n",
       "6                                      3       134             92   \n",
       "7                                     29       551            675   \n",
       "8                                    121       535           1151   \n",
       "9                                      0       562            241   \n",
       "10                                     0       398            347   \n",
       "\n",
       "   hispanicOrLatino notHispanicOrLatino stateFips countyFips        tract  \\\n",
       "6               324                1729        06        073  06073008339   \n",
       "7               974                6063        06        073  06073008347   \n",
       "8              1489                8373        06        073  06073008354   \n",
       "9              1791                4785        06        073  06073008505   \n",
       "10             1375                6052        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": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Example data\n",
    "df[(df['stateFips'] == '06') & (df['countyFips'] == '073')].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Save data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv(NEO4J_IMPORT / \"03a-USCensusDP05Tract.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(73056, 30)"
      ]
     },
     "execution_count": 31,
     "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
}