notebooks/dataprep/03a-USCensusDP03Commuting.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Selected Economic Characteristics: Commuting to Work 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 = {# COMMUTING TO WORK\n",
    "             'DP03_0018E': 'workers16YearsAndOver',\n",
    "             'DP03_0019E': 'droveAloneToWorkInCarTruckOrVan',\n",
    "             'DP03_0019PE': 'droveAloneToWorkInCarTruckOrVanPct',\n",
    "             'DP03_0020E': 'carpooledToWorkInCarTruckOrVan',\n",
    "             'DP03_0020PE': 'carpooledToWorkInCarTruckOrVanPct',\n",
    "             'DP03_0021E': 'publicTransportToWork',\n",
    "             'DP03_0021PE': 'publicTransportToWorkPct',\n",
    "             'DP03_0022E': 'walkedToWork',\n",
    "             'DP03_0022PE': 'walkedToWorkPct',\n",
    "             'DP03_0023E': 'otherMeansOfCommutingToWork',\n",
    "             'DP03_0023PE': 'otherMeansOfCommutingToWorkPct',\n",
    "             'DP03_0024E': 'workedAtHome',\n",
    "             'DP03_0024PE': 'workedAtHomePct',\n",
    "             'DP03_0025E': 'meanTravelTimeToWorkMinutes',\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": [
      "c.workers16YearsAndOver = toInteger(row.workers16YearsAndOver),\n",
      "c.droveAloneToWorkInCarTruckOrVan = toInteger(row.droveAloneToWorkInCarTruckOrVan),\n",
      "c.droveAloneToWorkInCarTruckOrVanPct = toFloat(row.droveAloneToWorkInCarTruckOrVanPct),\n",
      "c.carpooledToWorkInCarTruckOrVan = toInteger(row.carpooledToWorkInCarTruckOrVan),\n",
      "c.carpooledToWorkInCarTruckOrVanPct = toFloat(row.carpooledToWorkInCarTruckOrVanPct),\n",
      "c.publicTransportToWork = toInteger(row.publicTransportToWork),\n",
      "c.publicTransportToWorkPct = toFloat(row.publicTransportToWorkPct),\n",
      "c.walkedToWork = toInteger(row.walkedToWork),\n",
      "c.walkedToWorkPct = toFloat(row.walkedToWorkPct),\n",
      "c.otherMeansOfCommutingToWork = toInteger(row.otherMeansOfCommutingToWork),\n",
      "c.otherMeansOfCommutingToWorkPct = toFloat(row.otherMeansOfCommutingToWorkPct),\n",
      "c.workedAtHome = toInteger(row.workedAtHome),\n",
      "c.workedAtHomePct = toFloat(row.workedAtHomePct),\n",
      "c.meanTravelTimeToWorkMinutes = toInteger(row.meanTravelTimeToWorkMinutes),\n"
     ]
    }
   ],
   "source": [
    "for v in variables.values():\n",
    "    if 'Pct' in v:\n",
    "        print('c.' + v + ' = toFloat(row.' + v + '),')\n",
    "    else:\n",
    "        print('c.' + v + ' = toInteger(row.' + v + '),')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "14\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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>DP03_0018E</td>\n",
       "      <td>DP03_0019E</td>\n",
       "      <td>DP03_0019PE</td>\n",
       "      <td>DP03_0020E</td>\n",
       "      <td>DP03_0020PE</td>\n",
       "      <td>DP03_0021E</td>\n",
       "      <td>DP03_0021PE</td>\n",
       "      <td>DP03_0022E</td>\n",
       "      <td>DP03_0022PE</td>\n",
       "      <td>DP03_0023E</td>\n",
       "      <td>DP03_0023PE</td>\n",
       "      <td>DP03_0024E</td>\n",
       "      <td>DP03_0024PE</td>\n",
       "      <td>DP03_0025E</td>\n",
       "      <td>state</td>\n",
       "      <td>county</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1520</td>\n",
       "      <td>741</td>\n",
       "      <td>48.8</td>\n",
       "      <td>313</td>\n",
       "      <td>20.6</td>\n",
       "      <td>14</td>\n",
       "      <td>0.9</td>\n",
       "      <td>311</td>\n",
       "      <td>20.5</td>\n",
       "      <td>74</td>\n",
       "      <td>4.9</td>\n",
       "      <td>67</td>\n",
       "      <td>4.4</td>\n",
       "      <td>8.2</td>\n",
       "      <td>02</td>\n",
       "      <td>195</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2038</td>\n",
       "      <td>100</td>\n",
       "      <td>4.9</td>\n",
       "      <td>89</td>\n",
       "      <td>4.4</td>\n",
       "      <td>2</td>\n",
       "      <td>0.1</td>\n",
       "      <td>840</td>\n",
       "      <td>41.2</td>\n",
       "      <td>950</td>\n",
       "      <td>46.6</td>\n",
       "      <td>57</td>\n",
       "      <td>2.8</td>\n",
       "      <td>6.7</td>\n",
       "      <td>02</td>\n",
       "      <td>158</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1976339</td>\n",
       "      <td>1507253</td>\n",
       "      <td>76.3</td>\n",
       "      <td>217539</td>\n",
       "      <td>11.0</td>\n",
       "      <td>41442</td>\n",
       "      <td>2.1</td>\n",
       "      <td>29957</td>\n",
       "      <td>1.5</td>\n",
       "      <td>49571</td>\n",
       "      <td>2.5</td>\n",
       "      <td>130577</td>\n",
       "      <td>6.6</td>\n",
       "      <td>26.0</td>\n",
       "      <td>04</td>\n",
       "      <td>013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>12365</td>\n",
       "      <td>9848</td>\n",
       "      <td>79.6</td>\n",
       "      <td>1681</td>\n",
       "      <td>13.6</td>\n",
       "      <td>41</td>\n",
       "      <td>0.3</td>\n",
       "      <td>257</td>\n",
       "      <td>2.1</td>\n",
       "      <td>133</td>\n",
       "      <td>1.1</td>\n",
       "      <td>405</td>\n",
       "      <td>3.3</td>\n",
       "      <td>20.5</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_0018E  DP03_0019E  DP03_0019PE  DP03_0020E  DP03_0020PE  DP03_0021E   \n",
       "1        1520         741         48.8         313         20.6          14   \n",
       "2        2038         100          4.9          89          4.4           2   \n",
       "3     1976339     1507253         76.3      217539         11.0       41442   \n",
       "4       12365        9848         79.6        1681         13.6          41   \n",
       "\n",
       "            6           7            8           9            10          11  \\\n",
       "0  DP03_0021PE  DP03_0022E  DP03_0022PE  DP03_0023E  DP03_0023PE  DP03_0024E   \n",
       "1          0.9         311         20.5          74          4.9          67   \n",
       "2          0.1         840         41.2         950         46.6          57   \n",
       "3          2.1       29957          1.5       49571          2.5      130577   \n",
       "4          0.3         257          2.1         133          1.1         405   \n",
       "\n",
       "            12          13     14      15  \n",
       "0  DP03_0024PE  DP03_0025E  state  county  \n",
       "1          4.4         8.2     02     195  \n",
       "2          2.8         6.7     02     158  \n",
       "3          6.6        26.0     04     013  \n",
       "4          3.3        20.5     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>workers16YearsAndOver</th>\n",
       "      <th>droveAloneToWorkInCarTruckOrVan</th>\n",
       "      <th>droveAloneToWorkInCarTruckOrVanPct</th>\n",
       "      <th>carpooledToWorkInCarTruckOrVan</th>\n",
       "      <th>carpooledToWorkInCarTruckOrVanPct</th>\n",
       "      <th>publicTransportToWork</th>\n",
       "      <th>publicTransportToWorkPct</th>\n",
       "      <th>walkedToWork</th>\n",
       "      <th>walkedToWorkPct</th>\n",
       "      <th>otherMeansOfCommutingToWork</th>\n",
       "      <th>otherMeansOfCommutingToWorkPct</th>\n",
       "      <th>workedAtHome</th>\n",
       "      <th>workedAtHomePct</th>\n",
       "      <th>meanTravelTimeToWorkMinutes</th>\n",
       "      <th>stateFips</th>\n",
       "      <th>countyFips</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1520</td>\n",
       "      <td>741</td>\n",
       "      <td>48.8</td>\n",
       "      <td>313</td>\n",
       "      <td>20.6</td>\n",
       "      <td>14</td>\n",
       "      <td>0.9</td>\n",
       "      <td>311</td>\n",
       "      <td>20.5</td>\n",
       "      <td>74</td>\n",
       "      <td>4.9</td>\n",
       "      <td>67</td>\n",
       "      <td>4.4</td>\n",
       "      <td>8.2</td>\n",
       "      <td>02</td>\n",
       "      <td>195</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2038</td>\n",
       "      <td>100</td>\n",
       "      <td>4.9</td>\n",
       "      <td>89</td>\n",
       "      <td>4.4</td>\n",
       "      <td>2</td>\n",
       "      <td>0.1</td>\n",
       "      <td>840</td>\n",
       "      <td>41.2</td>\n",
       "      <td>950</td>\n",
       "      <td>46.6</td>\n",
       "      <td>57</td>\n",
       "      <td>2.8</td>\n",
       "      <td>6.7</td>\n",
       "      <td>02</td>\n",
       "      <td>158</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1976339</td>\n",
       "      <td>1507253</td>\n",
       "      <td>76.3</td>\n",
       "      <td>217539</td>\n",
       "      <td>11.0</td>\n",
       "      <td>41442</td>\n",
       "      <td>2.1</td>\n",
       "      <td>29957</td>\n",
       "      <td>1.5</td>\n",
       "      <td>49571</td>\n",
       "      <td>2.5</td>\n",
       "      <td>130577</td>\n",
       "      <td>6.6</td>\n",
       "      <td>26.0</td>\n",
       "      <td>04</td>\n",
       "      <td>013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>12365</td>\n",
       "      <td>9848</td>\n",
       "      <td>79.6</td>\n",
       "      <td>1681</td>\n",
       "      <td>13.6</td>\n",
       "      <td>41</td>\n",
       "      <td>0.3</td>\n",
       "      <td>257</td>\n",
       "      <td>2.1</td>\n",
       "      <td>133</td>\n",
       "      <td>1.1</td>\n",
       "      <td>405</td>\n",
       "      <td>3.3</td>\n",
       "      <td>20.5</td>\n",
       "      <td>04</td>\n",
       "      <td>009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>17036</td>\n",
       "      <td>14404</td>\n",
       "      <td>84.6</td>\n",
       "      <td>1324</td>\n",
       "      <td>7.8</td>\n",
       "      <td>54</td>\n",
       "      <td>0.3</td>\n",
       "      <td>237</td>\n",
       "      <td>1.4</td>\n",
       "      <td>357</td>\n",
       "      <td>2.1</td>\n",
       "      <td>660</td>\n",
       "      <td>3.9</td>\n",
       "      <td>22.8</td>\n",
       "      <td>04</td>\n",
       "      <td>023</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  workers16YearsAndOver droveAloneToWorkInCarTruckOrVan  \\\n",
       "1                  1520                             741   \n",
       "2                  2038                             100   \n",
       "3               1976339                         1507253   \n",
       "4                 12365                            9848   \n",
       "5                 17036                           14404   \n",
       "\n",
       "  droveAloneToWorkInCarTruckOrVanPct carpooledToWorkInCarTruckOrVan  \\\n",
       "1                               48.8                            313   \n",
       "2                                4.9                             89   \n",
       "3                               76.3                         217539   \n",
       "4                               79.6                           1681   \n",
       "5                               84.6                           1324   \n",
       "\n",
       "  carpooledToWorkInCarTruckOrVanPct publicTransportToWork  \\\n",
       "1                              20.6                    14   \n",
       "2                               4.4                     2   \n",
       "3                              11.0                 41442   \n",
       "4                              13.6                    41   \n",
       "5                               7.8                    54   \n",
       "\n",
       "  publicTransportToWorkPct walkedToWork walkedToWorkPct  \\\n",
       "1                      0.9          311            20.5   \n",
       "2                      0.1          840            41.2   \n",
       "3                      2.1        29957             1.5   \n",
       "4                      0.3          257             2.1   \n",
       "5                      0.3          237             1.4   \n",
       "\n",
       "  otherMeansOfCommutingToWork otherMeansOfCommutingToWorkPct workedAtHome  \\\n",
       "1                          74                            4.9           67   \n",
       "2                         950                           46.6           57   \n",
       "3                       49571                            2.5       130577   \n",
       "4                         133                            1.1          405   \n",
       "5                         357                            2.1          660   \n",
       "\n",
       "  workedAtHomePct meanTravelTimeToWorkMinutes stateFips countyFips  \n",
       "1             4.4                         8.2        02        195  \n",
       "2             2.8                         6.7        02        158  \n",
       "3             6.6                        26.0        04        013  \n",
       "4             3.3                        20.5        04        009  \n",
       "5             3.9                        22.8        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>workers16YearsAndOver</th>\n",
       "      <th>droveAloneToWorkInCarTruckOrVan</th>\n",
       "      <th>droveAloneToWorkInCarTruckOrVanPct</th>\n",
       "      <th>carpooledToWorkInCarTruckOrVan</th>\n",
       "      <th>carpooledToWorkInCarTruckOrVanPct</th>\n",
       "      <th>publicTransportToWork</th>\n",
       "      <th>publicTransportToWorkPct</th>\n",
       "      <th>walkedToWork</th>\n",
       "      <th>walkedToWorkPct</th>\n",
       "      <th>otherMeansOfCommutingToWork</th>\n",
       "      <th>otherMeansOfCommutingToWorkPct</th>\n",
       "      <th>workedAtHome</th>\n",
       "      <th>workedAtHomePct</th>\n",
       "      <th>meanTravelTimeToWorkMinutes</th>\n",
       "      <th>stateFips</th>\n",
       "      <th>countyFips</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>897</th>\n",
       "      <td>1603486</td>\n",
       "      <td>1223159</td>\n",
       "      <td>76.3</td>\n",
       "      <td>138748</td>\n",
       "      <td>8.7</td>\n",
       "      <td>46506</td>\n",
       "      <td>2.9</td>\n",
       "      <td>46313</td>\n",
       "      <td>2.9</td>\n",
       "      <td>36799</td>\n",
       "      <td>2.3</td>\n",
       "      <td>111961</td>\n",
       "      <td>7.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>06</td>\n",
       "      <td>073</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    workers16YearsAndOver droveAloneToWorkInCarTruckOrVan  \\\n",
       "897               1603486                         1223159   \n",
       "\n",
       "    droveAloneToWorkInCarTruckOrVanPct carpooledToWorkInCarTruckOrVan  \\\n",
       "897                               76.3                         138748   \n",
       "\n",
       "    carpooledToWorkInCarTruckOrVanPct publicTransportToWork  \\\n",
       "897                               8.7                 46506   \n",
       "\n",
       "    publicTransportToWorkPct walkedToWork walkedToWorkPct  \\\n",
       "897                      2.9        46313             2.9   \n",
       "\n",
       "    otherMeansOfCommutingToWork otherMeansOfCommutingToWorkPct workedAtHome  \\\n",
       "897                       36799                            2.3       111961   \n",
       "\n",
       "    workedAtHomePct meanTravelTimeToWorkMinutes stateFips countyFips  \n",
       "897             7.0                        26.0        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-USCensusDP03CommutingAdmin2.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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>DP03_0018E</td>\n",
       "      <td>DP03_0019E</td>\n",
       "      <td>DP03_0019PE</td>\n",
       "      <td>DP03_0020E</td>\n",
       "      <td>DP03_0020PE</td>\n",
       "      <td>DP03_0021E</td>\n",
       "      <td>DP03_0021PE</td>\n",
       "      <td>DP03_0022E</td>\n",
       "      <td>DP03_0022PE</td>\n",
       "      <td>DP03_0023E</td>\n",
       "      <td>DP03_0023PE</td>\n",
       "      <td>DP03_0024E</td>\n",
       "      <td>DP03_0024PE</td>\n",
       "      <td>DP03_0025E</td>\n",
       "      <td>state</td>\n",
       "      <td>zip code tabulation area</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>947</td>\n",
       "      <td>906</td>\n",
       "      <td>95.7</td>\n",
       "      <td>11</td>\n",
       "      <td>1.2</td>\n",
       "      <td>17</td>\n",
       "      <td>1.8</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6</td>\n",
       "      <td>0.6</td>\n",
       "      <td>7</td>\n",
       "      <td>0.7</td>\n",
       "      <td>41.3</td>\n",
       "      <td>51</td>\n",
       "      <td>23833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>563</td>\n",
       "      <td>563</td>\n",
       "      <td>100.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>38.1</td>\n",
       "      <td>51</td>\n",
       "      <td>23850</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5645</td>\n",
       "      <td>5018</td>\n",
       "      <td>88.9</td>\n",
       "      <td>287</td>\n",
       "      <td>5.1</td>\n",
       "      <td>42</td>\n",
       "      <td>0.7</td>\n",
       "      <td>35</td>\n",
       "      <td>0.6</td>\n",
       "      <td>73</td>\n",
       "      <td>1.3</td>\n",
       "      <td>190</td>\n",
       "      <td>3.4</td>\n",
       "      <td>25.2</td>\n",
       "      <td>51</td>\n",
       "      <td>23851</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1484</td>\n",
       "      <td>1282</td>\n",
       "      <td>86.4</td>\n",
       "      <td>58</td>\n",
       "      <td>3.9</td>\n",
       "      <td>11</td>\n",
       "      <td>0.7</td>\n",
       "      <td>32</td>\n",
       "      <td>2.2</td>\n",
       "      <td>54</td>\n",
       "      <td>3.6</td>\n",
       "      <td>47</td>\n",
       "      <td>3.2</td>\n",
       "      <td>36.1</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_0018E  DP03_0019E  DP03_0019PE  DP03_0020E  DP03_0020PE  DP03_0021E   \n",
       "1         947         906         95.7          11          1.2          17   \n",
       "2         563         563        100.0           0          0.0           0   \n",
       "3        5645        5018         88.9         287          5.1          42   \n",
       "4        1484        1282         86.4          58          3.9          11   \n",
       "\n",
       "            6           7            8           9            10          11  \\\n",
       "0  DP03_0021PE  DP03_0022E  DP03_0022PE  DP03_0023E  DP03_0023PE  DP03_0024E   \n",
       "1          1.8           0          0.0           6          0.6           7   \n",
       "2          0.0           0          0.0           0          0.0           0   \n",
       "3          0.7          35          0.6          73          1.3         190   \n",
       "4          0.7          32          2.2          54          3.6          47   \n",
       "\n",
       "            12          13     14                        15  \n",
       "0  DP03_0024PE  DP03_0025E  state  zip code tabulation area  \n",
       "1          0.7        41.3     51                     23833  \n",
       "2          0.0        38.1     51                     23850  \n",
       "3          3.4        25.2     51                     23851  \n",
       "4          3.2        36.1     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>workers16YearsAndOver</th>\n",
       "      <th>droveAloneToWorkInCarTruckOrVan</th>\n",
       "      <th>droveAloneToWorkInCarTruckOrVanPct</th>\n",
       "      <th>carpooledToWorkInCarTruckOrVan</th>\n",
       "      <th>carpooledToWorkInCarTruckOrVanPct</th>\n",
       "      <th>publicTransportToWork</th>\n",
       "      <th>publicTransportToWorkPct</th>\n",
       "      <th>walkedToWork</th>\n",
       "      <th>walkedToWorkPct</th>\n",
       "      <th>otherMeansOfCommutingToWork</th>\n",
       "      <th>otherMeansOfCommutingToWorkPct</th>\n",
       "      <th>workedAtHome</th>\n",
       "      <th>workedAtHomePct</th>\n",
       "      <th>meanTravelTimeToWorkMinutes</th>\n",
       "      <th>stateFips</th>\n",
       "      <th>postalCode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>947</td>\n",
       "      <td>906</td>\n",
       "      <td>95.7</td>\n",
       "      <td>11</td>\n",
       "      <td>1.2</td>\n",
       "      <td>17</td>\n",
       "      <td>1.8</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6</td>\n",
       "      <td>0.6</td>\n",
       "      <td>7</td>\n",
       "      <td>0.7</td>\n",
       "      <td>41.3</td>\n",
       "      <td>51</td>\n",
       "      <td>23833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>563</td>\n",
       "      <td>563</td>\n",
       "      <td>100.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>38.1</td>\n",
       "      <td>51</td>\n",
       "      <td>23850</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5645</td>\n",
       "      <td>5018</td>\n",
       "      <td>88.9</td>\n",
       "      <td>287</td>\n",
       "      <td>5.1</td>\n",
       "      <td>42</td>\n",
       "      <td>0.7</td>\n",
       "      <td>35</td>\n",
       "      <td>0.6</td>\n",
       "      <td>73</td>\n",
       "      <td>1.3</td>\n",
       "      <td>190</td>\n",
       "      <td>3.4</td>\n",
       "      <td>25.2</td>\n",
       "      <td>51</td>\n",
       "      <td>23851</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1484</td>\n",
       "      <td>1282</td>\n",
       "      <td>86.4</td>\n",
       "      <td>58</td>\n",
       "      <td>3.9</td>\n",
       "      <td>11</td>\n",
       "      <td>0.7</td>\n",
       "      <td>32</td>\n",
       "      <td>2.2</td>\n",
       "      <td>54</td>\n",
       "      <td>3.6</td>\n",
       "      <td>47</td>\n",
       "      <td>3.2</td>\n",
       "      <td>36.1</td>\n",
       "      <td>51</td>\n",
       "      <td>23890</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>120</td>\n",
       "      <td>120</td>\n",
       "      <td>100.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>16.8</td>\n",
       "      <td>51</td>\n",
       "      <td>23302</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  workers16YearsAndOver droveAloneToWorkInCarTruckOrVan  \\\n",
       "1                   947                             906   \n",
       "2                   563                             563   \n",
       "3                  5645                            5018   \n",
       "4                  1484                            1282   \n",
       "5                   120                             120   \n",
       "\n",
       "  droveAloneToWorkInCarTruckOrVanPct carpooledToWorkInCarTruckOrVan  \\\n",
       "1                               95.7                             11   \n",
       "2                              100.0                              0   \n",
       "3                               88.9                            287   \n",
       "4                               86.4                             58   \n",
       "5                              100.0                              0   \n",
       "\n",
       "  carpooledToWorkInCarTruckOrVanPct publicTransportToWork  \\\n",
       "1                               1.2                    17   \n",
       "2                               0.0                     0   \n",
       "3                               5.1                    42   \n",
       "4                               3.9                    11   \n",
       "5                               0.0                     0   \n",
       "\n",
       "  publicTransportToWorkPct walkedToWork walkedToWorkPct  \\\n",
       "1                      1.8            0             0.0   \n",
       "2                      0.0            0             0.0   \n",
       "3                      0.7           35             0.6   \n",
       "4                      0.7           32             2.2   \n",
       "5                      0.0            0             0.0   \n",
       "\n",
       "  otherMeansOfCommutingToWork otherMeansOfCommutingToWorkPct workedAtHome  \\\n",
       "1                           6                            0.6            7   \n",
       "2                           0                            0.0            0   \n",
       "3                          73                            1.3          190   \n",
       "4                          54                            3.6           47   \n",
       "5                           0                            0.0            0   \n",
       "\n",
       "  workedAtHomePct meanTravelTimeToWorkMinutes stateFips postalCode  \n",
       "1             0.7                        41.3        51      23833  \n",
       "2             0.0                        38.1        51      23850  \n",
       "3             3.4                        25.2        51      23851  \n",
       "4             3.2                        36.1        51      23890  \n",
       "5             0.0                        16.8        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>workers16YearsAndOver</th>\n",
       "      <th>droveAloneToWorkInCarTruckOrVan</th>\n",
       "      <th>droveAloneToWorkInCarTruckOrVanPct</th>\n",
       "      <th>carpooledToWorkInCarTruckOrVan</th>\n",
       "      <th>carpooledToWorkInCarTruckOrVanPct</th>\n",
       "      <th>publicTransportToWork</th>\n",
       "      <th>publicTransportToWorkPct</th>\n",
       "      <th>walkedToWork</th>\n",
       "      <th>walkedToWorkPct</th>\n",
       "      <th>otherMeansOfCommutingToWork</th>\n",
       "      <th>otherMeansOfCommutingToWorkPct</th>\n",
       "      <th>workedAtHome</th>\n",
       "      <th>workedAtHomePct</th>\n",
       "      <th>meanTravelTimeToWorkMinutes</th>\n",
       "      <th>stateFips</th>\n",
       "      <th>postalCode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>24526</th>\n",
       "      <td>8489</td>\n",
       "      <td>6386</td>\n",
       "      <td>75.2</td>\n",
       "      <td>491</td>\n",
       "      <td>5.8</td>\n",
       "      <td>91</td>\n",
       "      <td>1.1</td>\n",
       "      <td>123</td>\n",
       "      <td>1.4</td>\n",
       "      <td>139</td>\n",
       "      <td>1.6</td>\n",
       "      <td>1259</td>\n",
       "      <td>14.8</td>\n",
       "      <td>25.9</td>\n",
       "      <td>06</td>\n",
       "      <td>90210</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      workers16YearsAndOver droveAloneToWorkInCarTruckOrVan  \\\n",
       "24526                  8489                            6386   \n",
       "\n",
       "      droveAloneToWorkInCarTruckOrVanPct carpooledToWorkInCarTruckOrVan  \\\n",
       "24526                               75.2                            491   \n",
       "\n",
       "      carpooledToWorkInCarTruckOrVanPct publicTransportToWork  \\\n",
       "24526                               5.8                    91   \n",
       "\n",
       "      publicTransportToWorkPct walkedToWork walkedToWorkPct  \\\n",
       "24526                      1.1          123             1.4   \n",
       "\n",
       "      otherMeansOfCommutingToWork otherMeansOfCommutingToWorkPct workedAtHome  \\\n",
       "24526                         139                            1.6         1259   \n",
       "\n",
       "      workedAtHomePct meanTravelTimeToWorkMinutes stateFips postalCode  \n",
       "24526            14.8                        25.9        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-USCensusDP03CommutingZip.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>workers16YearsAndOver</th>\n",
       "      <th>droveAloneToWorkInCarTruckOrVan</th>\n",
       "      <th>droveAloneToWorkInCarTruckOrVanPct</th>\n",
       "      <th>carpooledToWorkInCarTruckOrVan</th>\n",
       "      <th>carpooledToWorkInCarTruckOrVanPct</th>\n",
       "      <th>publicTransportToWork</th>\n",
       "      <th>publicTransportToWorkPct</th>\n",
       "      <th>walkedToWork</th>\n",
       "      <th>walkedToWorkPct</th>\n",
       "      <th>otherMeansOfCommutingToWork</th>\n",
       "      <th>otherMeansOfCommutingToWorkPct</th>\n",
       "      <th>workedAtHome</th>\n",
       "      <th>workedAtHomePct</th>\n",
       "      <th>meanTravelTimeToWorkMinutes</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>1252</td>\n",
       "      <td>876</td>\n",
       "      <td>70.0</td>\n",
       "      <td>110</td>\n",
       "      <td>8.8</td>\n",
       "      <td>98</td>\n",
       "      <td>7.8</td>\n",
       "      <td>56</td>\n",
       "      <td>4.5</td>\n",
       "      <td>72</td>\n",
       "      <td>5.8</td>\n",
       "      <td>40</td>\n",
       "      <td>3.2</td>\n",
       "      <td>18.6</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>3644</td>\n",
       "      <td>2971</td>\n",
       "      <td>81.5</td>\n",
       "      <td>363</td>\n",
       "      <td>10.0</td>\n",
       "      <td>42</td>\n",
       "      <td>1.2</td>\n",
       "      <td>9</td>\n",
       "      <td>0.2</td>\n",
       "      <td>141</td>\n",
       "      <td>3.9</td>\n",
       "      <td>118</td>\n",
       "      <td>3.2</td>\n",
       "      <td>26.5</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>5529</td>\n",
       "      <td>4445</td>\n",
       "      <td>80.4</td>\n",
       "      <td>650</td>\n",
       "      <td>11.8</td>\n",
       "      <td>47</td>\n",
       "      <td>0.9</td>\n",
       "      <td>43</td>\n",
       "      <td>0.8</td>\n",
       "      <td>135</td>\n",
       "      <td>2.4</td>\n",
       "      <td>209</td>\n",
       "      <td>3.8</td>\n",
       "      <td>23.6</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>3234</td>\n",
       "      <td>2596</td>\n",
       "      <td>80.3</td>\n",
       "      <td>163</td>\n",
       "      <td>5.0</td>\n",
       "      <td>121</td>\n",
       "      <td>3.7</td>\n",
       "      <td>52</td>\n",
       "      <td>1.6</td>\n",
       "      <td>26</td>\n",
       "      <td>0.8</td>\n",
       "      <td>276</td>\n",
       "      <td>8.5</td>\n",
       "      <td>20.7</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>3462</td>\n",
       "      <td>2417</td>\n",
       "      <td>69.8</td>\n",
       "      <td>229</td>\n",
       "      <td>6.6</td>\n",
       "      <td>17</td>\n",
       "      <td>0.5</td>\n",
       "      <td>133</td>\n",
       "      <td>3.8</td>\n",
       "      <td>214</td>\n",
       "      <td>6.2</td>\n",
       "      <td>452</td>\n",
       "      <td>13.1</td>\n",
       "      <td>24.8</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": [
       "   workers16YearsAndOver droveAloneToWorkInCarTruckOrVan  \\\n",
       "6                   1252                             876   \n",
       "7                   3644                            2971   \n",
       "8                   5529                            4445   \n",
       "9                   3234                            2596   \n",
       "10                  3462                            2417   \n",
       "\n",
       "   droveAloneToWorkInCarTruckOrVanPct carpooledToWorkInCarTruckOrVan  \\\n",
       "6                                70.0                            110   \n",
       "7                                81.5                            363   \n",
       "8                                80.4                            650   \n",
       "9                                80.3                            163   \n",
       "10                               69.8                            229   \n",
       "\n",
       "   carpooledToWorkInCarTruckOrVanPct publicTransportToWork  \\\n",
       "6                                8.8                    98   \n",
       "7                               10.0                    42   \n",
       "8                               11.8                    47   \n",
       "9                                5.0                   121   \n",
       "10                               6.6                    17   \n",
       "\n",
       "   publicTransportToWorkPct walkedToWork walkedToWorkPct  \\\n",
       "6                       7.8           56             4.5   \n",
       "7                       1.2            9             0.2   \n",
       "8                       0.9           43             0.8   \n",
       "9                       3.7           52             1.6   \n",
       "10                      0.5          133             3.8   \n",
       "\n",
       "   otherMeansOfCommutingToWork otherMeansOfCommutingToWorkPct workedAtHome  \\\n",
       "6                           72                            5.8           40   \n",
       "7                          141                            3.9          118   \n",
       "8                          135                            2.4          209   \n",
       "9                           26                            0.8          276   \n",
       "10                         214                            6.2          452   \n",
       "\n",
       "   workedAtHomePct meanTravelTimeToWorkMinutes stateFips countyFips  \\\n",
       "6              3.2                        18.6        06        073   \n",
       "7              3.2                        26.5        06        073   \n",
       "8              3.8                        23.6        06        073   \n",
       "9              8.5                        20.7        06        073   \n",
       "10            13.1                        24.8        06        073   \n",
       "\n",
       "          tract                            source aggregationLevel  \n",
       "6   06073008339  American Community Survey 5 year            Tract  \n",
       "7   06073008347  American Community Survey 5 year            Tract  \n",
       "8   06073008354  American Community Survey 5 year            Tract  \n",
       "9   06073008505  American Community Survey 5 year            Tract  \n",
       "10  06073017604  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-USCensusDP03CommutingTract.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(73056, 19)"
      ]
     },
     "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
}