notebooks/dataprep/03a-USCensusDP03Employment.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Selected Economic Characteristics: Employment Status 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 = {# EMPLOYMENT STATUS\n",
    "             'DP03_0001E': 'population16YearsAndOver',\n",
    "             'DP03_0002E': 'population16YearsAndOverInLaborForce',\n",
    "             'DP03_0002PE': 'population16YearsAndOverInLaborForcePct',\n",
    "             'DP03_0003E': 'population16YearsAndOverInCivilianLaborForce',\n",
    "             'DP03_0003PE': 'population16YearsAndOverInCivilianLaborForcePct',\n",
    "             'DP03_0006E': 'population16YearsAndOverInArmedForces',\n",
    "             'DP03_0006PE': 'population16YearsAndOverInArmedForcesPct',\n",
    "             'DP03_0007E': 'population16YearsAndOverNotInLaborForce',\n",
    "             'DP03_0007PE': 'population16YearsAndOverNotInLaborForcePct'\n",
    "             #'DP03_0014E': 'ownChildrenOfTheHouseholderUnder6Years',\n",
    "             #'DP03_0015E': 'ownChildrenOfTheHouseholderUnder6YearsAllParentsInLaborForce',\n",
    "             #'DP03_0016E': 'ownChildrenOfTheHouseholder6To17Years',\n",
    "             #'DP03_0017E': 'ownChildrenOfTheHouseholder6To17YearsAllParentsInLaborForce',\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": [
      "e.population16YearsAndOver = toInteger(row.population16YearsAndOver),\n",
      "e.population16YearsAndOverInLaborForce = toInteger(row.population16YearsAndOverInLaborForce),\n",
      "e.population16YearsAndOverInLaborForcePct = toInteger(row.population16YearsAndOverInLaborForcePct),\n",
      "e.population16YearsAndOverInCivilianLaborForce = toInteger(row.population16YearsAndOverInCivilianLaborForce),\n",
      "e.population16YearsAndOverInCivilianLaborForcePct = toInteger(row.population16YearsAndOverInCivilianLaborForcePct),\n",
      "e.population16YearsAndOverInArmedForces = toInteger(row.population16YearsAndOverInArmedForces),\n",
      "e.population16YearsAndOverInArmedForcesPct = toInteger(row.population16YearsAndOverInArmedForcesPct),\n",
      "e.population16YearsAndOverNotInLaborForce = toInteger(row.population16YearsAndOverNotInLaborForce),\n",
      "e.population16YearsAndOverNotInLaborForcePct = toInteger(row.population16YearsAndOverNotInLaborForcePct),\n"
     ]
    }
   ],
   "source": [
    "for v in variables.values():\n",
    "    print('e.' + v + ' = toInteger(row.' + v + '),')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "9\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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>DP03_0001E</td>\n",
       "      <td>DP03_0002E</td>\n",
       "      <td>DP03_0002PE</td>\n",
       "      <td>DP03_0003E</td>\n",
       "      <td>DP03_0003PE</td>\n",
       "      <td>DP03_0006E</td>\n",
       "      <td>DP03_0006PE</td>\n",
       "      <td>DP03_0007E</td>\n",
       "      <td>DP03_0007PE</td>\n",
       "      <td>state</td>\n",
       "      <td>county</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2597</td>\n",
       "      <td>1657</td>\n",
       "      <td>63.8</td>\n",
       "      <td>1647</td>\n",
       "      <td>63.4</td>\n",
       "      <td>10</td>\n",
       "      <td>0.4</td>\n",
       "      <td>940</td>\n",
       "      <td>36.2</td>\n",
       "      <td>02</td>\n",
       "      <td>195</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5184</td>\n",
       "      <td>2945</td>\n",
       "      <td>56.8</td>\n",
       "      <td>2937</td>\n",
       "      <td>56.7</td>\n",
       "      <td>8</td>\n",
       "      <td>0.2</td>\n",
       "      <td>2239</td>\n",
       "      <td>43.2</td>\n",
       "      <td>02</td>\n",
       "      <td>158</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3329961</td>\n",
       "      <td>2126196</td>\n",
       "      <td>63.9</td>\n",
       "      <td>2121295</td>\n",
       "      <td>63.7</td>\n",
       "      <td>4901</td>\n",
       "      <td>0.1</td>\n",
       "      <td>1203765</td>\n",
       "      <td>36.1</td>\n",
       "      <td>04</td>\n",
       "      <td>013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>28750</td>\n",
       "      <td>14166</td>\n",
       "      <td>49.3</td>\n",
       "      <td>14166</td>\n",
       "      <td>49.3</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>14584</td>\n",
       "      <td>50.7</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_0001E  DP03_0002E  DP03_0002PE  DP03_0003E  DP03_0003PE  DP03_0006E   \n",
       "1        2597        1657         63.8        1647         63.4          10   \n",
       "2        5184        2945         56.8        2937         56.7           8   \n",
       "3     3329961     2126196         63.9     2121295         63.7        4901   \n",
       "4       28750       14166         49.3       14166         49.3           0   \n",
       "\n",
       "            6           7            8      9       10  \n",
       "0  DP03_0006PE  DP03_0007E  DP03_0007PE  state  county  \n",
       "1          0.4         940         36.2     02     195  \n",
       "2          0.2        2239         43.2     02     158  \n",
       "3          0.1     1203765         36.1     04     013  \n",
       "4          0.0       14584         50.7     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>population16YearsAndOver</th>\n",
       "      <th>population16YearsAndOverInLaborForce</th>\n",
       "      <th>population16YearsAndOverInLaborForcePct</th>\n",
       "      <th>population16YearsAndOverInCivilianLaborForce</th>\n",
       "      <th>population16YearsAndOverInCivilianLaborForcePct</th>\n",
       "      <th>population16YearsAndOverInArmedForces</th>\n",
       "      <th>population16YearsAndOverInArmedForcesPct</th>\n",
       "      <th>population16YearsAndOverNotInLaborForce</th>\n",
       "      <th>population16YearsAndOverNotInLaborForcePct</th>\n",
       "      <th>stateFips</th>\n",
       "      <th>countyFips</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2597</td>\n",
       "      <td>1657</td>\n",
       "      <td>63.8</td>\n",
       "      <td>1647</td>\n",
       "      <td>63.4</td>\n",
       "      <td>10</td>\n",
       "      <td>0.4</td>\n",
       "      <td>940</td>\n",
       "      <td>36.2</td>\n",
       "      <td>02</td>\n",
       "      <td>195</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5184</td>\n",
       "      <td>2945</td>\n",
       "      <td>56.8</td>\n",
       "      <td>2937</td>\n",
       "      <td>56.7</td>\n",
       "      <td>8</td>\n",
       "      <td>0.2</td>\n",
       "      <td>2239</td>\n",
       "      <td>43.2</td>\n",
       "      <td>02</td>\n",
       "      <td>158</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3329961</td>\n",
       "      <td>2126196</td>\n",
       "      <td>63.9</td>\n",
       "      <td>2121295</td>\n",
       "      <td>63.7</td>\n",
       "      <td>4901</td>\n",
       "      <td>0.1</td>\n",
       "      <td>1203765</td>\n",
       "      <td>36.1</td>\n",
       "      <td>04</td>\n",
       "      <td>013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>28750</td>\n",
       "      <td>14166</td>\n",
       "      <td>49.3</td>\n",
       "      <td>14166</td>\n",
       "      <td>49.3</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>14584</td>\n",
       "      <td>50.7</td>\n",
       "      <td>04</td>\n",
       "      <td>009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>35433</td>\n",
       "      <td>18821</td>\n",
       "      <td>53.1</td>\n",
       "      <td>18798</td>\n",
       "      <td>53.1</td>\n",
       "      <td>23</td>\n",
       "      <td>0.1</td>\n",
       "      <td>16612</td>\n",
       "      <td>46.9</td>\n",
       "      <td>04</td>\n",
       "      <td>023</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  population16YearsAndOver population16YearsAndOverInLaborForce  \\\n",
       "1                     2597                                 1657   \n",
       "2                     5184                                 2945   \n",
       "3                  3329961                              2126196   \n",
       "4                    28750                                14166   \n",
       "5                    35433                                18821   \n",
       "\n",
       "  population16YearsAndOverInLaborForcePct  \\\n",
       "1                                    63.8   \n",
       "2                                    56.8   \n",
       "3                                    63.9   \n",
       "4                                    49.3   \n",
       "5                                    53.1   \n",
       "\n",
       "  population16YearsAndOverInCivilianLaborForce  \\\n",
       "1                                         1647   \n",
       "2                                         2937   \n",
       "3                                      2121295   \n",
       "4                                        14166   \n",
       "5                                        18798   \n",
       "\n",
       "  population16YearsAndOverInCivilianLaborForcePct  \\\n",
       "1                                            63.4   \n",
       "2                                            56.7   \n",
       "3                                            63.7   \n",
       "4                                            49.3   \n",
       "5                                            53.1   \n",
       "\n",
       "  population16YearsAndOverInArmedForces  \\\n",
       "1                                    10   \n",
       "2                                     8   \n",
       "3                                  4901   \n",
       "4                                     0   \n",
       "5                                    23   \n",
       "\n",
       "  population16YearsAndOverInArmedForcesPct  \\\n",
       "1                                      0.4   \n",
       "2                                      0.2   \n",
       "3                                      0.1   \n",
       "4                                      0.0   \n",
       "5                                      0.1   \n",
       "\n",
       "  population16YearsAndOverNotInLaborForce  \\\n",
       "1                                     940   \n",
       "2                                    2239   \n",
       "3                                 1203765   \n",
       "4                                   14584   \n",
       "5                                   16612   \n",
       "\n",
       "  population16YearsAndOverNotInLaborForcePct stateFips countyFips  \n",
       "1                                       36.2        02        195  \n",
       "2                                       43.2        02        158  \n",
       "3                                       36.1        04        013  \n",
       "4                                       50.7        04        009  \n",
       "5                                       46.9        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>population16YearsAndOver</th>\n",
       "      <th>population16YearsAndOverInLaborForce</th>\n",
       "      <th>population16YearsAndOverInLaborForcePct</th>\n",
       "      <th>population16YearsAndOverInCivilianLaborForce</th>\n",
       "      <th>population16YearsAndOverInCivilianLaborForcePct</th>\n",
       "      <th>population16YearsAndOverInArmedForces</th>\n",
       "      <th>population16YearsAndOverInArmedForcesPct</th>\n",
       "      <th>population16YearsAndOverNotInLaborForce</th>\n",
       "      <th>population16YearsAndOverNotInLaborForcePct</th>\n",
       "      <th>stateFips</th>\n",
       "      <th>countyFips</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>897</th>\n",
       "      <td>2656740</td>\n",
       "      <td>1745186</td>\n",
       "      <td>65.7</td>\n",
       "      <td>1671892</td>\n",
       "      <td>62.9</td>\n",
       "      <td>73294</td>\n",
       "      <td>2.8</td>\n",
       "      <td>911554</td>\n",
       "      <td>34.3</td>\n",
       "      <td>06</td>\n",
       "      <td>073</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    population16YearsAndOver population16YearsAndOverInLaborForce  \\\n",
       "897                  2656740                              1745186   \n",
       "\n",
       "    population16YearsAndOverInLaborForcePct  \\\n",
       "897                                    65.7   \n",
       "\n",
       "    population16YearsAndOverInCivilianLaborForce  \\\n",
       "897                                      1671892   \n",
       "\n",
       "    population16YearsAndOverInCivilianLaborForcePct  \\\n",
       "897                                            62.9   \n",
       "\n",
       "    population16YearsAndOverInArmedForces  \\\n",
       "897                                 73294   \n",
       "\n",
       "    population16YearsAndOverInArmedForcesPct  \\\n",
       "897                                      2.8   \n",
       "\n",
       "    population16YearsAndOverNotInLaborForce  \\\n",
       "897                                  911554   \n",
       "\n",
       "    population16YearsAndOverNotInLaborForcePct stateFips countyFips  \n",
       "897                                       34.3        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-USCensusDP03EmploymentAdmin2.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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>DP03_0001E</td>\n",
       "      <td>DP03_0002E</td>\n",
       "      <td>DP03_0002PE</td>\n",
       "      <td>DP03_0003E</td>\n",
       "      <td>DP03_0003PE</td>\n",
       "      <td>DP03_0006E</td>\n",
       "      <td>DP03_0006PE</td>\n",
       "      <td>DP03_0007E</td>\n",
       "      <td>DP03_0007PE</td>\n",
       "      <td>state</td>\n",
       "      <td>zip code tabulation area</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1853</td>\n",
       "      <td>1097</td>\n",
       "      <td>59.2</td>\n",
       "      <td>1067</td>\n",
       "      <td>57.6</td>\n",
       "      <td>30</td>\n",
       "      <td>1.6</td>\n",
       "      <td>756</td>\n",
       "      <td>40.8</td>\n",
       "      <td>51</td>\n",
       "      <td>23833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1063</td>\n",
       "      <td>563</td>\n",
       "      <td>53.0</td>\n",
       "      <td>563</td>\n",
       "      <td>53.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>500</td>\n",
       "      <td>47.0</td>\n",
       "      <td>51</td>\n",
       "      <td>23850</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10374</td>\n",
       "      <td>6436</td>\n",
       "      <td>62.0</td>\n",
       "      <td>6422</td>\n",
       "      <td>61.9</td>\n",
       "      <td>14</td>\n",
       "      <td>0.1</td>\n",
       "      <td>3938</td>\n",
       "      <td>38.0</td>\n",
       "      <td>51</td>\n",
       "      <td>23851</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3032</td>\n",
       "      <td>1686</td>\n",
       "      <td>55.6</td>\n",
       "      <td>1678</td>\n",
       "      <td>55.3</td>\n",
       "      <td>8</td>\n",
       "      <td>0.3</td>\n",
       "      <td>1346</td>\n",
       "      <td>44.4</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_0001E  DP03_0002E  DP03_0002PE  DP03_0003E  DP03_0003PE  DP03_0006E   \n",
       "1        1853        1097         59.2        1067         57.6          30   \n",
       "2        1063         563         53.0         563         53.0           0   \n",
       "3       10374        6436         62.0        6422         61.9          14   \n",
       "4        3032        1686         55.6        1678         55.3           8   \n",
       "\n",
       "            6           7            8      9                         10  \n",
       "0  DP03_0006PE  DP03_0007E  DP03_0007PE  state  zip code tabulation area  \n",
       "1          1.6         756         40.8     51                     23833  \n",
       "2          0.0         500         47.0     51                     23850  \n",
       "3          0.1        3938         38.0     51                     23851  \n",
       "4          0.3        1346         44.4     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>population16YearsAndOver</th>\n",
       "      <th>population16YearsAndOverInLaborForce</th>\n",
       "      <th>population16YearsAndOverInLaborForcePct</th>\n",
       "      <th>population16YearsAndOverInCivilianLaborForce</th>\n",
       "      <th>population16YearsAndOverInCivilianLaborForcePct</th>\n",
       "      <th>population16YearsAndOverInArmedForces</th>\n",
       "      <th>population16YearsAndOverInArmedForcesPct</th>\n",
       "      <th>population16YearsAndOverNotInLaborForce</th>\n",
       "      <th>population16YearsAndOverNotInLaborForcePct</th>\n",
       "      <th>stateFips</th>\n",
       "      <th>postalCode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1853</td>\n",
       "      <td>1097</td>\n",
       "      <td>59.2</td>\n",
       "      <td>1067</td>\n",
       "      <td>57.6</td>\n",
       "      <td>30</td>\n",
       "      <td>1.6</td>\n",
       "      <td>756</td>\n",
       "      <td>40.8</td>\n",
       "      <td>51</td>\n",
       "      <td>23833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1063</td>\n",
       "      <td>563</td>\n",
       "      <td>53.0</td>\n",
       "      <td>563</td>\n",
       "      <td>53.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>500</td>\n",
       "      <td>47.0</td>\n",
       "      <td>51</td>\n",
       "      <td>23850</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10374</td>\n",
       "      <td>6436</td>\n",
       "      <td>62.0</td>\n",
       "      <td>6422</td>\n",
       "      <td>61.9</td>\n",
       "      <td>14</td>\n",
       "      <td>0.1</td>\n",
       "      <td>3938</td>\n",
       "      <td>38.0</td>\n",
       "      <td>51</td>\n",
       "      <td>23851</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3032</td>\n",
       "      <td>1686</td>\n",
       "      <td>55.6</td>\n",
       "      <td>1678</td>\n",
       "      <td>55.3</td>\n",
       "      <td>8</td>\n",
       "      <td>0.3</td>\n",
       "      <td>1346</td>\n",
       "      <td>44.4</td>\n",
       "      <td>51</td>\n",
       "      <td>23890</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>170</td>\n",
       "      <td>120</td>\n",
       "      <td>70.6</td>\n",
       "      <td>120</td>\n",
       "      <td>70.6</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>50</td>\n",
       "      <td>29.4</td>\n",
       "      <td>51</td>\n",
       "      <td>23302</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  population16YearsAndOver population16YearsAndOverInLaborForce  \\\n",
       "1                     1853                                 1097   \n",
       "2                     1063                                  563   \n",
       "3                    10374                                 6436   \n",
       "4                     3032                                 1686   \n",
       "5                      170                                  120   \n",
       "\n",
       "  population16YearsAndOverInLaborForcePct  \\\n",
       "1                                    59.2   \n",
       "2                                    53.0   \n",
       "3                                    62.0   \n",
       "4                                    55.6   \n",
       "5                                    70.6   \n",
       "\n",
       "  population16YearsAndOverInCivilianLaborForce  \\\n",
       "1                                         1067   \n",
       "2                                          563   \n",
       "3                                         6422   \n",
       "4                                         1678   \n",
       "5                                          120   \n",
       "\n",
       "  population16YearsAndOverInCivilianLaborForcePct  \\\n",
       "1                                            57.6   \n",
       "2                                            53.0   \n",
       "3                                            61.9   \n",
       "4                                            55.3   \n",
       "5                                            70.6   \n",
       "\n",
       "  population16YearsAndOverInArmedForces  \\\n",
       "1                                    30   \n",
       "2                                     0   \n",
       "3                                    14   \n",
       "4                                     8   \n",
       "5                                     0   \n",
       "\n",
       "  population16YearsAndOverInArmedForcesPct  \\\n",
       "1                                      1.6   \n",
       "2                                      0.0   \n",
       "3                                      0.1   \n",
       "4                                      0.3   \n",
       "5                                      0.0   \n",
       "\n",
       "  population16YearsAndOverNotInLaborForce  \\\n",
       "1                                     756   \n",
       "2                                     500   \n",
       "3                                    3938   \n",
       "4                                    1346   \n",
       "5                                      50   \n",
       "\n",
       "  population16YearsAndOverNotInLaborForcePct stateFips postalCode  \n",
       "1                                       40.8        51      23833  \n",
       "2                                       47.0        51      23850  \n",
       "3                                       38.0        51      23851  \n",
       "4                                       44.4        51      23890  \n",
       "5                                       29.4        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>population16YearsAndOver</th>\n",
       "      <th>population16YearsAndOverInLaborForce</th>\n",
       "      <th>population16YearsAndOverInLaborForcePct</th>\n",
       "      <th>population16YearsAndOverInCivilianLaborForce</th>\n",
       "      <th>population16YearsAndOverInCivilianLaborForcePct</th>\n",
       "      <th>population16YearsAndOverInArmedForces</th>\n",
       "      <th>population16YearsAndOverInArmedForcesPct</th>\n",
       "      <th>population16YearsAndOverNotInLaborForce</th>\n",
       "      <th>population16YearsAndOverNotInLaborForcePct</th>\n",
       "      <th>stateFips</th>\n",
       "      <th>postalCode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>24526</th>\n",
       "      <td>16439</td>\n",
       "      <td>9181</td>\n",
       "      <td>55.8</td>\n",
       "      <td>9138</td>\n",
       "      <td>55.6</td>\n",
       "      <td>43</td>\n",
       "      <td>0.3</td>\n",
       "      <td>7258</td>\n",
       "      <td>44.2</td>\n",
       "      <td>06</td>\n",
       "      <td>90210</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      population16YearsAndOver population16YearsAndOverInLaborForce  \\\n",
       "24526                    16439                                 9181   \n",
       "\n",
       "      population16YearsAndOverInLaborForcePct  \\\n",
       "24526                                    55.8   \n",
       "\n",
       "      population16YearsAndOverInCivilianLaborForce  \\\n",
       "24526                                         9138   \n",
       "\n",
       "      population16YearsAndOverInCivilianLaborForcePct  \\\n",
       "24526                                            55.6   \n",
       "\n",
       "      population16YearsAndOverInArmedForces  \\\n",
       "24526                                    43   \n",
       "\n",
       "      population16YearsAndOverInArmedForcesPct  \\\n",
       "24526                                      0.3   \n",
       "\n",
       "      population16YearsAndOverNotInLaborForce  \\\n",
       "24526                                    7258   \n",
       "\n",
       "      population16YearsAndOverNotInLaborForcePct stateFips postalCode  \n",
       "24526                                       44.2        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-USCensusDP03EmploymentZip.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>population16YearsAndOver</th>\n",
       "      <th>population16YearsAndOverInLaborForce</th>\n",
       "      <th>population16YearsAndOverInLaborForcePct</th>\n",
       "      <th>population16YearsAndOverInCivilianLaborForce</th>\n",
       "      <th>population16YearsAndOverInCivilianLaborForcePct</th>\n",
       "      <th>population16YearsAndOverInArmedForces</th>\n",
       "      <th>population16YearsAndOverInArmedForcesPct</th>\n",
       "      <th>population16YearsAndOverNotInLaborForce</th>\n",
       "      <th>population16YearsAndOverNotInLaborForcePct</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>1885</td>\n",
       "      <td>1404</td>\n",
       "      <td>74.5</td>\n",
       "      <td>1400</td>\n",
       "      <td>74.3</td>\n",
       "      <td>4</td>\n",
       "      <td>0.2</td>\n",
       "      <td>481</td>\n",
       "      <td>25.5</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>5661</td>\n",
       "      <td>3904</td>\n",
       "      <td>69.0</td>\n",
       "      <td>3883</td>\n",
       "      <td>68.6</td>\n",
       "      <td>21</td>\n",
       "      <td>0.4</td>\n",
       "      <td>1757</td>\n",
       "      <td>31.0</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>7894</td>\n",
       "      <td>5952</td>\n",
       "      <td>75.4</td>\n",
       "      <td>5846</td>\n",
       "      <td>74.1</td>\n",
       "      <td>106</td>\n",
       "      <td>1.3</td>\n",
       "      <td>1942</td>\n",
       "      <td>24.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>5653</td>\n",
       "      <td>3695</td>\n",
       "      <td>65.4</td>\n",
       "      <td>3695</td>\n",
       "      <td>65.4</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1958</td>\n",
       "      <td>34.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>5867</td>\n",
       "      <td>3629</td>\n",
       "      <td>61.9</td>\n",
       "      <td>3629</td>\n",
       "      <td>61.9</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2238</td>\n",
       "      <td>38.1</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": [
       "   population16YearsAndOver population16YearsAndOverInLaborForce  \\\n",
       "6                      1885                                 1404   \n",
       "7                      5661                                 3904   \n",
       "8                      7894                                 5952   \n",
       "9                      5653                                 3695   \n",
       "10                     5867                                 3629   \n",
       "\n",
       "   population16YearsAndOverInLaborForcePct  \\\n",
       "6                                     74.5   \n",
       "7                                     69.0   \n",
       "8                                     75.4   \n",
       "9                                     65.4   \n",
       "10                                    61.9   \n",
       "\n",
       "   population16YearsAndOverInCivilianLaborForce  \\\n",
       "6                                          1400   \n",
       "7                                          3883   \n",
       "8                                          5846   \n",
       "9                                          3695   \n",
       "10                                         3629   \n",
       "\n",
       "   population16YearsAndOverInCivilianLaborForcePct  \\\n",
       "6                                             74.3   \n",
       "7                                             68.6   \n",
       "8                                             74.1   \n",
       "9                                             65.4   \n",
       "10                                            61.9   \n",
       "\n",
       "   population16YearsAndOverInArmedForces  \\\n",
       "6                                      4   \n",
       "7                                     21   \n",
       "8                                    106   \n",
       "9                                      0   \n",
       "10                                     0   \n",
       "\n",
       "   population16YearsAndOverInArmedForcesPct  \\\n",
       "6                                       0.2   \n",
       "7                                       0.4   \n",
       "8                                       1.3   \n",
       "9                                       0.0   \n",
       "10                                      0.0   \n",
       "\n",
       "   population16YearsAndOverNotInLaborForce  \\\n",
       "6                                      481   \n",
       "7                                     1757   \n",
       "8                                     1942   \n",
       "9                                     1958   \n",
       "10                                    2238   \n",
       "\n",
       "   population16YearsAndOverNotInLaborForcePct stateFips countyFips  \\\n",
       "6                                        25.5        06        073   \n",
       "7                                        31.0        06        073   \n",
       "8                                        24.6        06        073   \n",
       "9                                        34.6        06        073   \n",
       "10                                       38.1        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-USCensusDP03EmploymentTract.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(73056, 14)"
      ]
     },
     "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
}