notebooks/dataprep/03a-USCensusDP03Occupation.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Selected Economic Characteristics: Occupation 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 = {# OCCUPATION\n",
    "             'DP03_0026E': 'civilianEmployedPopulation16YearsAndOver',\n",
    "             'DP03_0027E': 'managementBusinessScienceAndArtsOccupations',\n",
    "             'DP03_0027PE': 'managementBusinessScienceAndArtsOccupationsPct',\n",
    "             'DP03_0028E': 'serviceOccupations',\n",
    "             'DP03_0028PE': 'serviceOccupationsPct',\n",
    "             'DP03_0029E': 'salesAndOfficeOccupations',\n",
    "             'DP03_0029PE': 'salesAndOfficeOccupationsPct',\n",
    "             'DP03_0030E': 'naturalResourcesConstructionAndMaintenanceOccupations',\n",
    "             'DP03_0030PE': 'naturalResourcesConstructionAndMaintenanceOccupationsPct',\n",
    "             'DP03_0031E': 'productionTransportationAndMaterialMovingOccupations',\n",
    "             'DP03_0031PE': 'productionTransportationAndMaterialMovingOccupationsPct'\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": [
      "o.civilianEmployedPopulation16YearsAndOver = toInteger(row.civilianEmployedPopulation16YearsAndOver),\n",
      "o.managementBusinessScienceAndArtsOccupations = toInteger(row.managementBusinessScienceAndArtsOccupations),\n",
      "o.managementBusinessScienceAndArtsOccupationsPct = toFloat(row.managementBusinessScienceAndArtsOccupationsPct),\n",
      "o.serviceOccupations = toInteger(row.serviceOccupations),\n",
      "o.serviceOccupationsPct = toFloat(row.serviceOccupationsPct),\n",
      "o.salesAndOfficeOccupations = toInteger(row.salesAndOfficeOccupations),\n",
      "o.salesAndOfficeOccupationsPct = toFloat(row.salesAndOfficeOccupationsPct),\n",
      "o.naturalResourcesConstructionAndMaintenanceOccupations = toInteger(row.naturalResourcesConstructionAndMaintenanceOccupations),\n",
      "o.naturalResourcesConstructionAndMaintenanceOccupationsPct = toFloat(row.naturalResourcesConstructionAndMaintenanceOccupationsPct),\n",
      "o.productionTransportationAndMaterialMovingOccupations = toInteger(row.productionTransportationAndMaterialMovingOccupations),\n",
      "o.productionTransportationAndMaterialMovingOccupationsPct = toFloat(row.productionTransportationAndMaterialMovingOccupationsPct),\n"
     ]
    }
   ],
   "source": [
    "for v in variables.values():\n",
    "    if 'Pct' in v:\n",
    "        print('o.' + v + ' = toFloat(row.' + v + '),')\n",
    "    else:\n",
    "        print('o.' + v + ' = toInteger(row.' + v + '),')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "11\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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>DP03_0026E</td>\n",
       "      <td>DP03_0027E</td>\n",
       "      <td>DP03_0027PE</td>\n",
       "      <td>DP03_0028E</td>\n",
       "      <td>DP03_0028PE</td>\n",
       "      <td>DP03_0029E</td>\n",
       "      <td>DP03_0029PE</td>\n",
       "      <td>DP03_0030E</td>\n",
       "      <td>DP03_0030PE</td>\n",
       "      <td>DP03_0031E</td>\n",
       "      <td>DP03_0031PE</td>\n",
       "      <td>state</td>\n",
       "      <td>county</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1577</td>\n",
       "      <td>418</td>\n",
       "      <td>26.5</td>\n",
       "      <td>236</td>\n",
       "      <td>15.0</td>\n",
       "      <td>253</td>\n",
       "      <td>16.0</td>\n",
       "      <td>393</td>\n",
       "      <td>24.9</td>\n",
       "      <td>277</td>\n",
       "      <td>17.6</td>\n",
       "      <td>02</td>\n",
       "      <td>195</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2088</td>\n",
       "      <td>623</td>\n",
       "      <td>29.8</td>\n",
       "      <td>536</td>\n",
       "      <td>25.7</td>\n",
       "      <td>388</td>\n",
       "      <td>18.6</td>\n",
       "      <td>240</td>\n",
       "      <td>11.5</td>\n",
       "      <td>301</td>\n",
       "      <td>14.4</td>\n",
       "      <td>02</td>\n",
       "      <td>158</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2004236</td>\n",
       "      <td>753860</td>\n",
       "      <td>37.6</td>\n",
       "      <td>360919</td>\n",
       "      <td>18.0</td>\n",
       "      <td>503710</td>\n",
       "      <td>25.1</td>\n",
       "      <td>168119</td>\n",
       "      <td>8.4</td>\n",
       "      <td>217628</td>\n",
       "      <td>10.9</td>\n",
       "      <td>04</td>\n",
       "      <td>013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>12837</td>\n",
       "      <td>3563</td>\n",
       "      <td>27.8</td>\n",
       "      <td>2693</td>\n",
       "      <td>21.0</td>\n",
       "      <td>2934</td>\n",
       "      <td>22.9</td>\n",
       "      <td>1920</td>\n",
       "      <td>15.0</td>\n",
       "      <td>1727</td>\n",
       "      <td>13.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_0026E  DP03_0027E  DP03_0027PE  DP03_0028E  DP03_0028PE  DP03_0029E   \n",
       "1        1577         418         26.5         236         15.0         253   \n",
       "2        2088         623         29.8         536         25.7         388   \n",
       "3     2004236      753860         37.6      360919         18.0      503710   \n",
       "4       12837        3563         27.8        2693         21.0        2934   \n",
       "\n",
       "            6           7            8           9            10     11  \\\n",
       "0  DP03_0029PE  DP03_0030E  DP03_0030PE  DP03_0031E  DP03_0031PE  state   \n",
       "1         16.0         393         24.9         277         17.6     02   \n",
       "2         18.6         240         11.5         301         14.4     02   \n",
       "3         25.1      168119          8.4      217628         10.9     04   \n",
       "4         22.9        1920         15.0        1727         13.5     04   \n",
       "\n",
       "       12  \n",
       "0  county  \n",
       "1     195  \n",
       "2     158  \n",
       "3     013  \n",
       "4     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>civilianEmployedPopulation16YearsAndOver</th>\n",
       "      <th>managementBusinessScienceAndArtsOccupations</th>\n",
       "      <th>managementBusinessScienceAndArtsOccupationsPct</th>\n",
       "      <th>serviceOccupations</th>\n",
       "      <th>serviceOccupationsPct</th>\n",
       "      <th>salesAndOfficeOccupations</th>\n",
       "      <th>salesAndOfficeOccupationsPct</th>\n",
       "      <th>naturalResourcesConstructionAndMaintenanceOccupations</th>\n",
       "      <th>naturalResourcesConstructionAndMaintenanceOccupationsPct</th>\n",
       "      <th>productionTransportationAndMaterialMovingOccupations</th>\n",
       "      <th>productionTransportationAndMaterialMovingOccupationsPct</th>\n",
       "      <th>stateFips</th>\n",
       "      <th>countyFips</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1577</td>\n",
       "      <td>418</td>\n",
       "      <td>26.5</td>\n",
       "      <td>236</td>\n",
       "      <td>15.0</td>\n",
       "      <td>253</td>\n",
       "      <td>16.0</td>\n",
       "      <td>393</td>\n",
       "      <td>24.9</td>\n",
       "      <td>277</td>\n",
       "      <td>17.6</td>\n",
       "      <td>02</td>\n",
       "      <td>195</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2088</td>\n",
       "      <td>623</td>\n",
       "      <td>29.8</td>\n",
       "      <td>536</td>\n",
       "      <td>25.7</td>\n",
       "      <td>388</td>\n",
       "      <td>18.6</td>\n",
       "      <td>240</td>\n",
       "      <td>11.5</td>\n",
       "      <td>301</td>\n",
       "      <td>14.4</td>\n",
       "      <td>02</td>\n",
       "      <td>158</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2004236</td>\n",
       "      <td>753860</td>\n",
       "      <td>37.6</td>\n",
       "      <td>360919</td>\n",
       "      <td>18.0</td>\n",
       "      <td>503710</td>\n",
       "      <td>25.1</td>\n",
       "      <td>168119</td>\n",
       "      <td>8.4</td>\n",
       "      <td>217628</td>\n",
       "      <td>10.9</td>\n",
       "      <td>04</td>\n",
       "      <td>013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>12837</td>\n",
       "      <td>3563</td>\n",
       "      <td>27.8</td>\n",
       "      <td>2693</td>\n",
       "      <td>21.0</td>\n",
       "      <td>2934</td>\n",
       "      <td>22.9</td>\n",
       "      <td>1920</td>\n",
       "      <td>15.0</td>\n",
       "      <td>1727</td>\n",
       "      <td>13.5</td>\n",
       "      <td>04</td>\n",
       "      <td>009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>17233</td>\n",
       "      <td>4370</td>\n",
       "      <td>25.4</td>\n",
       "      <td>3667</td>\n",
       "      <td>21.3</td>\n",
       "      <td>4674</td>\n",
       "      <td>27.1</td>\n",
       "      <td>1975</td>\n",
       "      <td>11.5</td>\n",
       "      <td>2547</td>\n",
       "      <td>14.8</td>\n",
       "      <td>04</td>\n",
       "      <td>023</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  civilianEmployedPopulation16YearsAndOver  \\\n",
       "1                                     1577   \n",
       "2                                     2088   \n",
       "3                                  2004236   \n",
       "4                                    12837   \n",
       "5                                    17233   \n",
       "\n",
       "  managementBusinessScienceAndArtsOccupations  \\\n",
       "1                                         418   \n",
       "2                                         623   \n",
       "3                                      753860   \n",
       "4                                        3563   \n",
       "5                                        4370   \n",
       "\n",
       "  managementBusinessScienceAndArtsOccupationsPct serviceOccupations  \\\n",
       "1                                           26.5                236   \n",
       "2                                           29.8                536   \n",
       "3                                           37.6             360919   \n",
       "4                                           27.8               2693   \n",
       "5                                           25.4               3667   \n",
       "\n",
       "  serviceOccupationsPct salesAndOfficeOccupations  \\\n",
       "1                  15.0                       253   \n",
       "2                  25.7                       388   \n",
       "3                  18.0                    503710   \n",
       "4                  21.0                      2934   \n",
       "5                  21.3                      4674   \n",
       "\n",
       "  salesAndOfficeOccupationsPct  \\\n",
       "1                         16.0   \n",
       "2                         18.6   \n",
       "3                         25.1   \n",
       "4                         22.9   \n",
       "5                         27.1   \n",
       "\n",
       "  naturalResourcesConstructionAndMaintenanceOccupations  \\\n",
       "1                                                393      \n",
       "2                                                240      \n",
       "3                                             168119      \n",
       "4                                               1920      \n",
       "5                                               1975      \n",
       "\n",
       "  naturalResourcesConstructionAndMaintenanceOccupationsPct  \\\n",
       "1                                               24.9         \n",
       "2                                               11.5         \n",
       "3                                                8.4         \n",
       "4                                               15.0         \n",
       "5                                               11.5         \n",
       "\n",
       "  productionTransportationAndMaterialMovingOccupations  \\\n",
       "1                                                277     \n",
       "2                                                301     \n",
       "3                                             217628     \n",
       "4                                               1727     \n",
       "5                                               2547     \n",
       "\n",
       "  productionTransportationAndMaterialMovingOccupationsPct stateFips countyFips  \n",
       "1                                               17.6             02        195  \n",
       "2                                               14.4             02        158  \n",
       "3                                               10.9             04        013  \n",
       "4                                               13.5             04        009  \n",
       "5                                               14.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>civilianEmployedPopulation16YearsAndOver</th>\n",
       "      <th>managementBusinessScienceAndArtsOccupations</th>\n",
       "      <th>managementBusinessScienceAndArtsOccupationsPct</th>\n",
       "      <th>serviceOccupations</th>\n",
       "      <th>serviceOccupationsPct</th>\n",
       "      <th>salesAndOfficeOccupations</th>\n",
       "      <th>salesAndOfficeOccupationsPct</th>\n",
       "      <th>naturalResourcesConstructionAndMaintenanceOccupations</th>\n",
       "      <th>naturalResourcesConstructionAndMaintenanceOccupationsPct</th>\n",
       "      <th>productionTransportationAndMaterialMovingOccupations</th>\n",
       "      <th>productionTransportationAndMaterialMovingOccupationsPct</th>\n",
       "      <th>stateFips</th>\n",
       "      <th>countyFips</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>897</th>\n",
       "      <td>1564930</td>\n",
       "      <td>652475</td>\n",
       "      <td>41.7</td>\n",
       "      <td>304726</td>\n",
       "      <td>19.5</td>\n",
       "      <td>340038</td>\n",
       "      <td>21.7</td>\n",
       "      <td>119478</td>\n",
       "      <td>7.6</td>\n",
       "      <td>148213</td>\n",
       "      <td>9.5</td>\n",
       "      <td>06</td>\n",
       "      <td>073</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    civilianEmployedPopulation16YearsAndOver  \\\n",
       "897                                  1564930   \n",
       "\n",
       "    managementBusinessScienceAndArtsOccupations  \\\n",
       "897                                      652475   \n",
       "\n",
       "    managementBusinessScienceAndArtsOccupationsPct serviceOccupations  \\\n",
       "897                                           41.7             304726   \n",
       "\n",
       "    serviceOccupationsPct salesAndOfficeOccupations  \\\n",
       "897                  19.5                    340038   \n",
       "\n",
       "    salesAndOfficeOccupationsPct  \\\n",
       "897                         21.7   \n",
       "\n",
       "    naturalResourcesConstructionAndMaintenanceOccupations  \\\n",
       "897                                             119478      \n",
       "\n",
       "    naturalResourcesConstructionAndMaintenanceOccupationsPct  \\\n",
       "897                                                7.6         \n",
       "\n",
       "    productionTransportationAndMaterialMovingOccupations  \\\n",
       "897                                             148213     \n",
       "\n",
       "    productionTransportationAndMaterialMovingOccupationsPct stateFips  \\\n",
       "897                                                9.5             06   \n",
       "\n",
       "    countyFips  \n",
       "897        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-USCensusDP03OccupationAdmin2.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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>DP03_0026E</td>\n",
       "      <td>DP03_0027E</td>\n",
       "      <td>DP03_0027PE</td>\n",
       "      <td>DP03_0028E</td>\n",
       "      <td>DP03_0028PE</td>\n",
       "      <td>DP03_0029E</td>\n",
       "      <td>DP03_0029PE</td>\n",
       "      <td>DP03_0030E</td>\n",
       "      <td>DP03_0030PE</td>\n",
       "      <td>DP03_0031E</td>\n",
       "      <td>DP03_0031PE</td>\n",
       "      <td>state</td>\n",
       "      <td>zip code tabulation area</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>934</td>\n",
       "      <td>276</td>\n",
       "      <td>29.6</td>\n",
       "      <td>122</td>\n",
       "      <td>13.1</td>\n",
       "      <td>176</td>\n",
       "      <td>18.8</td>\n",
       "      <td>154</td>\n",
       "      <td>16.5</td>\n",
       "      <td>206</td>\n",
       "      <td>22.1</td>\n",
       "      <td>51</td>\n",
       "      <td>23833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>563</td>\n",
       "      <td>204</td>\n",
       "      <td>36.2</td>\n",
       "      <td>104</td>\n",
       "      <td>18.5</td>\n",
       "      <td>70</td>\n",
       "      <td>12.4</td>\n",
       "      <td>111</td>\n",
       "      <td>19.7</td>\n",
       "      <td>74</td>\n",
       "      <td>13.1</td>\n",
       "      <td>51</td>\n",
       "      <td>23850</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5684</td>\n",
       "      <td>1309</td>\n",
       "      <td>23.0</td>\n",
       "      <td>1187</td>\n",
       "      <td>20.9</td>\n",
       "      <td>1351</td>\n",
       "      <td>23.8</td>\n",
       "      <td>867</td>\n",
       "      <td>15.3</td>\n",
       "      <td>970</td>\n",
       "      <td>17.1</td>\n",
       "      <td>51</td>\n",
       "      <td>23851</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1531</td>\n",
       "      <td>524</td>\n",
       "      <td>34.2</td>\n",
       "      <td>264</td>\n",
       "      <td>17.2</td>\n",
       "      <td>250</td>\n",
       "      <td>16.3</td>\n",
       "      <td>132</td>\n",
       "      <td>8.6</td>\n",
       "      <td>361</td>\n",
       "      <td>23.6</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_0026E  DP03_0027E  DP03_0027PE  DP03_0028E  DP03_0028PE  DP03_0029E   \n",
       "1         934         276         29.6         122         13.1         176   \n",
       "2         563         204         36.2         104         18.5          70   \n",
       "3        5684        1309         23.0        1187         20.9        1351   \n",
       "4        1531         524         34.2         264         17.2         250   \n",
       "\n",
       "            6           7            8           9            10     11  \\\n",
       "0  DP03_0029PE  DP03_0030E  DP03_0030PE  DP03_0031E  DP03_0031PE  state   \n",
       "1         18.8         154         16.5         206         22.1     51   \n",
       "2         12.4         111         19.7          74         13.1     51   \n",
       "3         23.8         867         15.3         970         17.1     51   \n",
       "4         16.3         132          8.6         361         23.6     51   \n",
       "\n",
       "                         12  \n",
       "0  zip code tabulation area  \n",
       "1                     23833  \n",
       "2                     23850  \n",
       "3                     23851  \n",
       "4                     23890  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_json(url_zip, dtype='str')\n",
    "df.fillna('', inplace=True)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Add column names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df[1:].copy() # skip first row\n",
    "columns = list(variables.values())\n",
    "columns.append('stateFips')\n",
    "columns.append('postalCode')\n",
    "df.columns = columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>civilianEmployedPopulation16YearsAndOver</th>\n",
       "      <th>managementBusinessScienceAndArtsOccupations</th>\n",
       "      <th>managementBusinessScienceAndArtsOccupationsPct</th>\n",
       "      <th>serviceOccupations</th>\n",
       "      <th>serviceOccupationsPct</th>\n",
       "      <th>salesAndOfficeOccupations</th>\n",
       "      <th>salesAndOfficeOccupationsPct</th>\n",
       "      <th>naturalResourcesConstructionAndMaintenanceOccupations</th>\n",
       "      <th>naturalResourcesConstructionAndMaintenanceOccupationsPct</th>\n",
       "      <th>productionTransportationAndMaterialMovingOccupations</th>\n",
       "      <th>productionTransportationAndMaterialMovingOccupationsPct</th>\n",
       "      <th>stateFips</th>\n",
       "      <th>postalCode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>934</td>\n",
       "      <td>276</td>\n",
       "      <td>29.6</td>\n",
       "      <td>122</td>\n",
       "      <td>13.1</td>\n",
       "      <td>176</td>\n",
       "      <td>18.8</td>\n",
       "      <td>154</td>\n",
       "      <td>16.5</td>\n",
       "      <td>206</td>\n",
       "      <td>22.1</td>\n",
       "      <td>51</td>\n",
       "      <td>23833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>563</td>\n",
       "      <td>204</td>\n",
       "      <td>36.2</td>\n",
       "      <td>104</td>\n",
       "      <td>18.5</td>\n",
       "      <td>70</td>\n",
       "      <td>12.4</td>\n",
       "      <td>111</td>\n",
       "      <td>19.7</td>\n",
       "      <td>74</td>\n",
       "      <td>13.1</td>\n",
       "      <td>51</td>\n",
       "      <td>23850</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5684</td>\n",
       "      <td>1309</td>\n",
       "      <td>23.0</td>\n",
       "      <td>1187</td>\n",
       "      <td>20.9</td>\n",
       "      <td>1351</td>\n",
       "      <td>23.8</td>\n",
       "      <td>867</td>\n",
       "      <td>15.3</td>\n",
       "      <td>970</td>\n",
       "      <td>17.1</td>\n",
       "      <td>51</td>\n",
       "      <td>23851</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1531</td>\n",
       "      <td>524</td>\n",
       "      <td>34.2</td>\n",
       "      <td>264</td>\n",
       "      <td>17.2</td>\n",
       "      <td>250</td>\n",
       "      <td>16.3</td>\n",
       "      <td>132</td>\n",
       "      <td>8.6</td>\n",
       "      <td>361</td>\n",
       "      <td>23.6</td>\n",
       "      <td>51</td>\n",
       "      <td>23890</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>120</td>\n",
       "      <td>98</td>\n",
       "      <td>81.7</td>\n",
       "      <td>22</td>\n",
       "      <td>18.3</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>51</td>\n",
       "      <td>23302</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  civilianEmployedPopulation16YearsAndOver  \\\n",
       "1                                      934   \n",
       "2                                      563   \n",
       "3                                     5684   \n",
       "4                                     1531   \n",
       "5                                      120   \n",
       "\n",
       "  managementBusinessScienceAndArtsOccupations  \\\n",
       "1                                         276   \n",
       "2                                         204   \n",
       "3                                        1309   \n",
       "4                                         524   \n",
       "5                                          98   \n",
       "\n",
       "  managementBusinessScienceAndArtsOccupationsPct serviceOccupations  \\\n",
       "1                                           29.6                122   \n",
       "2                                           36.2                104   \n",
       "3                                           23.0               1187   \n",
       "4                                           34.2                264   \n",
       "5                                           81.7                 22   \n",
       "\n",
       "  serviceOccupationsPct salesAndOfficeOccupations  \\\n",
       "1                  13.1                       176   \n",
       "2                  18.5                        70   \n",
       "3                  20.9                      1351   \n",
       "4                  17.2                       250   \n",
       "5                  18.3                         0   \n",
       "\n",
       "  salesAndOfficeOccupationsPct  \\\n",
       "1                         18.8   \n",
       "2                         12.4   \n",
       "3                         23.8   \n",
       "4                         16.3   \n",
       "5                          0.0   \n",
       "\n",
       "  naturalResourcesConstructionAndMaintenanceOccupations  \\\n",
       "1                                                154      \n",
       "2                                                111      \n",
       "3                                                867      \n",
       "4                                                132      \n",
       "5                                                  0      \n",
       "\n",
       "  naturalResourcesConstructionAndMaintenanceOccupationsPct  \\\n",
       "1                                               16.5         \n",
       "2                                               19.7         \n",
       "3                                               15.3         \n",
       "4                                                8.6         \n",
       "5                                                0.0         \n",
       "\n",
       "  productionTransportationAndMaterialMovingOccupations  \\\n",
       "1                                                206     \n",
       "2                                                 74     \n",
       "3                                                970     \n",
       "4                                                361     \n",
       "5                                                  0     \n",
       "\n",
       "  productionTransportationAndMaterialMovingOccupationsPct stateFips postalCode  \n",
       "1                                               22.1             51      23833  \n",
       "2                                               13.1             51      23850  \n",
       "3                                               17.1             51      23851  \n",
       "4                                               23.6             51      23890  \n",
       "5                                                0.0             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>civilianEmployedPopulation16YearsAndOver</th>\n",
       "      <th>managementBusinessScienceAndArtsOccupations</th>\n",
       "      <th>managementBusinessScienceAndArtsOccupationsPct</th>\n",
       "      <th>serviceOccupations</th>\n",
       "      <th>serviceOccupationsPct</th>\n",
       "      <th>salesAndOfficeOccupations</th>\n",
       "      <th>salesAndOfficeOccupationsPct</th>\n",
       "      <th>naturalResourcesConstructionAndMaintenanceOccupations</th>\n",
       "      <th>naturalResourcesConstructionAndMaintenanceOccupationsPct</th>\n",
       "      <th>productionTransportationAndMaterialMovingOccupations</th>\n",
       "      <th>productionTransportationAndMaterialMovingOccupationsPct</th>\n",
       "      <th>stateFips</th>\n",
       "      <th>postalCode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>24526</th>\n",
       "      <td>8687</td>\n",
       "      <td>5773</td>\n",
       "      <td>66.5</td>\n",
       "      <td>676</td>\n",
       "      <td>7.8</td>\n",
       "      <td>1943</td>\n",
       "      <td>22.4</td>\n",
       "      <td>59</td>\n",
       "      <td>0.7</td>\n",
       "      <td>236</td>\n",
       "      <td>2.7</td>\n",
       "      <td>06</td>\n",
       "      <td>90210</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      civilianEmployedPopulation16YearsAndOver  \\\n",
       "24526                                     8687   \n",
       "\n",
       "      managementBusinessScienceAndArtsOccupations  \\\n",
       "24526                                        5773   \n",
       "\n",
       "      managementBusinessScienceAndArtsOccupationsPct serviceOccupations  \\\n",
       "24526                                           66.5                676   \n",
       "\n",
       "      serviceOccupationsPct salesAndOfficeOccupations  \\\n",
       "24526                   7.8                      1943   \n",
       "\n",
       "      salesAndOfficeOccupationsPct  \\\n",
       "24526                         22.4   \n",
       "\n",
       "      naturalResourcesConstructionAndMaintenanceOccupations  \\\n",
       "24526                                                 59      \n",
       "\n",
       "      naturalResourcesConstructionAndMaintenanceOccupationsPct  \\\n",
       "24526                                                0.7         \n",
       "\n",
       "      productionTransportationAndMaterialMovingOccupations  \\\n",
       "24526                                                236     \n",
       "\n",
       "      productionTransportationAndMaterialMovingOccupationsPct stateFips  \\\n",
       "24526                                                2.7             06   \n",
       "\n",
       "      postalCode  \n",
       "24526      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-USCensusDP03OccupationZip.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>civilianEmployedPopulation16YearsAndOver</th>\n",
       "      <th>managementBusinessScienceAndArtsOccupations</th>\n",
       "      <th>managementBusinessScienceAndArtsOccupationsPct</th>\n",
       "      <th>serviceOccupations</th>\n",
       "      <th>serviceOccupationsPct</th>\n",
       "      <th>salesAndOfficeOccupations</th>\n",
       "      <th>salesAndOfficeOccupationsPct</th>\n",
       "      <th>naturalResourcesConstructionAndMaintenanceOccupations</th>\n",
       "      <th>naturalResourcesConstructionAndMaintenanceOccupationsPct</th>\n",
       "      <th>productionTransportationAndMaterialMovingOccupations</th>\n",
       "      <th>productionTransportationAndMaterialMovingOccupationsPct</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>1282</td>\n",
       "      <td>718</td>\n",
       "      <td>56.0</td>\n",
       "      <td>197</td>\n",
       "      <td>15.4</td>\n",
       "      <td>260</td>\n",
       "      <td>20.3</td>\n",
       "      <td>23</td>\n",
       "      <td>1.8</td>\n",
       "      <td>84</td>\n",
       "      <td>6.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>3720</td>\n",
       "      <td>2017</td>\n",
       "      <td>54.2</td>\n",
       "      <td>678</td>\n",
       "      <td>18.2</td>\n",
       "      <td>563</td>\n",
       "      <td>15.1</td>\n",
       "      <td>189</td>\n",
       "      <td>5.1</td>\n",
       "      <td>273</td>\n",
       "      <td>7.3</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>5535</td>\n",
       "      <td>3160</td>\n",
       "      <td>57.1</td>\n",
       "      <td>761</td>\n",
       "      <td>13.7</td>\n",
       "      <td>1078</td>\n",
       "      <td>19.5</td>\n",
       "      <td>147</td>\n",
       "      <td>2.7</td>\n",
       "      <td>389</td>\n",
       "      <td>7.0</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>3349</td>\n",
       "      <td>1374</td>\n",
       "      <td>41.0</td>\n",
       "      <td>802</td>\n",
       "      <td>23.9</td>\n",
       "      <td>617</td>\n",
       "      <td>18.4</td>\n",
       "      <td>236</td>\n",
       "      <td>7.0</td>\n",
       "      <td>320</td>\n",
       "      <td>9.6</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>3501</td>\n",
       "      <td>1972</td>\n",
       "      <td>56.3</td>\n",
       "      <td>508</td>\n",
       "      <td>14.5</td>\n",
       "      <td>802</td>\n",
       "      <td>22.9</td>\n",
       "      <td>126</td>\n",
       "      <td>3.6</td>\n",
       "      <td>93</td>\n",
       "      <td>2.7</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": [
       "   civilianEmployedPopulation16YearsAndOver  \\\n",
       "6                                      1282   \n",
       "7                                      3720   \n",
       "8                                      5535   \n",
       "9                                      3349   \n",
       "10                                     3501   \n",
       "\n",
       "   managementBusinessScienceAndArtsOccupations  \\\n",
       "6                                          718   \n",
       "7                                         2017   \n",
       "8                                         3160   \n",
       "9                                         1374   \n",
       "10                                        1972   \n",
       "\n",
       "   managementBusinessScienceAndArtsOccupationsPct serviceOccupations  \\\n",
       "6                                            56.0                197   \n",
       "7                                            54.2                678   \n",
       "8                                            57.1                761   \n",
       "9                                            41.0                802   \n",
       "10                                           56.3                508   \n",
       "\n",
       "   serviceOccupationsPct salesAndOfficeOccupations  \\\n",
       "6                   15.4                       260   \n",
       "7                   18.2                       563   \n",
       "8                   13.7                      1078   \n",
       "9                   23.9                       617   \n",
       "10                  14.5                       802   \n",
       "\n",
       "   salesAndOfficeOccupationsPct  \\\n",
       "6                          20.3   \n",
       "7                          15.1   \n",
       "8                          19.5   \n",
       "9                          18.4   \n",
       "10                         22.9   \n",
       "\n",
       "   naturalResourcesConstructionAndMaintenanceOccupations  \\\n",
       "6                                                  23      \n",
       "7                                                 189      \n",
       "8                                                 147      \n",
       "9                                                 236      \n",
       "10                                                126      \n",
       "\n",
       "   naturalResourcesConstructionAndMaintenanceOccupationsPct  \\\n",
       "6                                                 1.8         \n",
       "7                                                 5.1         \n",
       "8                                                 2.7         \n",
       "9                                                 7.0         \n",
       "10                                                3.6         \n",
       "\n",
       "   productionTransportationAndMaterialMovingOccupations  \\\n",
       "6                                                  84     \n",
       "7                                                 273     \n",
       "8                                                 389     \n",
       "9                                                 320     \n",
       "10                                                 93     \n",
       "\n",
       "   productionTransportationAndMaterialMovingOccupationsPct stateFips  \\\n",
       "6                                                 6.6             06   \n",
       "7                                                 7.3             06   \n",
       "8                                                 7.0             06   \n",
       "9                                                 9.6             06   \n",
       "10                                                2.7             06   \n",
       "\n",
       "   countyFips        tract                            source aggregationLevel  \n",
       "6         073  06073008339  American Community Survey 5 year            Tract  \n",
       "7         073  06073008347  American Community Survey 5 year            Tract  \n",
       "8         073  06073008354  American Community Survey 5 year            Tract  \n",
       "9         073  06073008505  American Community Survey 5 year            Tract  \n",
       "10        073  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-USCensusDP03OccupationTract.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(73056, 16)"
      ]
     },
     "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
}