notebooks/dataprep/03a-USCensusDP02Education.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Selected Social Characteristics: Educational Attainment from the American Community Survey\n",
    "\n",
    "**[Work in progress]**\n",
    "\n",
    "This notebook downloads [selected social characteristics (DP02)](https://data.census.gov/cedsci/table?q=DP02&tid=ACSDP5Y2018.DP02) 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": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "from pathlib import Path\n",
    "import time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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": 21,
   "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 social characteristics for US](https://data.census.gov/cedsci/table?q=DP02&tid=ACSDP5Y2018.DP02)\n",
    "\n",
    "* [List of variables as HTML](https://api.census.gov/data/2018/acs/acs5/profile/groups/DP02.html) or [JSON](https://api.census.gov/data/2018/acs/acs5/profile/groups/DP02/)\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 DP02 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": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "variables = {# EDUCATIONAL ATTAINMENT\n",
    "             'DP02_0058E': 'population25YearsAndOver',\n",
    "             'DP02_0059E': 'lessThan9thGrade',\n",
    "             'DP02_0059PE': 'lessThan9thGradePct',\n",
    "             'DP02_0060E': 'grade9thTo12thNoDiploma',\n",
    "             'DP02_0060PE': 'grade9thTo12thNoDiplomaPct',\n",
    "             'DP02_0061E': 'highSchoolGraduate',\n",
    "             'DP02_0061PE': 'highSchoolGraduatePct',\n",
    "             'DP02_0062E': 'someCollegeNoDegree',\n",
    "             'DP02_0062PE': 'someCollegeNoDegreePct',\n",
    "             'DP02_0063E': 'associatesDegree',\n",
    "             'DP02_0063PE': 'associatesDegreePct',\n",
    "             'DP02_0064E': 'bachelorsDegree',\n",
    "             'DP02_0064PE': 'bachelorsDegreePct',\n",
    "             'DP02_0065E': 'graduateOrProfessionalDegree',\n",
    "             'DP02_0065PE': 'graduateOrProfessionalDegreePct',\n",
    "             'DP02_0066E': 'highSchoolGraduateOrHigher',\n",
    "             'DP02_0066PE': 'highSchoolGraduateOrHigherPct',\n",
    "             'DP02_0067E': 'bachelorsDegreeOrHigher',\n",
    "             'DP02_0067PE': 'bachelorsDegreeOrHigherPct',\n",
    "            }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "fields = \",\".join(variables.keys())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "e.population25YearsAndOver = toInteger(row.population25YearsAndOver),\n",
      "e.lessThan9thGrade = toInteger(row.lessThan9thGrade),\n",
      "e.lessThan9thGradePct = toFloat(row.lessThan9thGradePct),\n",
      "e.grade9thTo12thNoDiploma = toInteger(row.grade9thTo12thNoDiploma),\n",
      "e.grade9thTo12thNoDiplomaPct = toFloat(row.grade9thTo12thNoDiplomaPct),\n",
      "e.highSchoolGraduate = toInteger(row.highSchoolGraduate),\n",
      "e.highSchoolGraduatePct = toFloat(row.highSchoolGraduatePct),\n",
      "e.someCollegeNoDegree = toInteger(row.someCollegeNoDegree),\n",
      "e.someCollegeNoDegreePct = toFloat(row.someCollegeNoDegreePct),\n",
      "e.associatesDegree = toInteger(row.associatesDegree),\n",
      "e.associatesDegreePct = toFloat(row.associatesDegreePct),\n",
      "e.bachelorsDegree = toInteger(row.bachelorsDegree),\n",
      "e.bachelorsDegreePct = toFloat(row.bachelorsDegreePct),\n",
      "e.graduateOrProfessionalDegree = toInteger(row.graduateOrProfessionalDegree),\n",
      "e.graduateOrProfessionalDegreePct = toFloat(row.graduateOrProfessionalDegreePct),\n",
      "e.highSchoolGraduateOrHigher = toInteger(row.highSchoolGraduateOrHigher),\n",
      "e.highSchoolGraduateOrHigherPct = toFloat(row.highSchoolGraduateOrHigherPct),\n",
      "e.bachelorsDegreeOrHigher = toInteger(row.bachelorsDegreeOrHigher),\n",
      "e.bachelorsDegreeOrHigherPct = toFloat(row.bachelorsDegreeOrHigherPct),\n"
     ]
    }
   ],
   "source": [
    "for v in variables.values():\n",
    "    if 'Pct' in v:\n",
    "        print('e.' + v + ' = toFloat(row.' + v + '),')\n",
    "    else:\n",
    "        print('e.' + v + ' = toInteger(row.' + v + '),')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Download county-level data using US Census API"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "url_county = f'https://api.census.gov/data/2018/acs/acs5/profile?get={fields}&for=county:*'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</th>\n",
       "      <th>10</th>\n",
       "      <th>11</th>\n",
       "      <th>12</th>\n",
       "      <th>13</th>\n",
       "      <th>14</th>\n",
       "      <th>15</th>\n",
       "      <th>16</th>\n",
       "      <th>17</th>\n",
       "      <th>18</th>\n",
       "      <th>19</th>\n",
       "      <th>20</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>DP02_0058E</td>\n",
       "      <td>DP02_0059E</td>\n",
       "      <td>DP02_0059PE</td>\n",
       "      <td>DP02_0060E</td>\n",
       "      <td>DP02_0060PE</td>\n",
       "      <td>DP02_0061E</td>\n",
       "      <td>DP02_0061PE</td>\n",
       "      <td>DP02_0062E</td>\n",
       "      <td>DP02_0062PE</td>\n",
       "      <td>DP02_0063E</td>\n",
       "      <td>DP02_0063PE</td>\n",
       "      <td>DP02_0064E</td>\n",
       "      <td>DP02_0064PE</td>\n",
       "      <td>DP02_0065E</td>\n",
       "      <td>DP02_0065PE</td>\n",
       "      <td>DP02_0066E</td>\n",
       "      <td>DP02_0066PE</td>\n",
       "      <td>DP02_0067E</td>\n",
       "      <td>DP02_0067PE</td>\n",
       "      <td>state</td>\n",
       "      <td>county</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2135</td>\n",
       "      <td>70</td>\n",
       "      <td>3.3</td>\n",
       "      <td>149</td>\n",
       "      <td>7.0</td>\n",
       "      <td>680</td>\n",
       "      <td>31.9</td>\n",
       "      <td>472</td>\n",
       "      <td>22.1</td>\n",
       "      <td>175</td>\n",
       "      <td>8.2</td>\n",
       "      <td>469</td>\n",
       "      <td>22.0</td>\n",
       "      <td>120</td>\n",
       "      <td>5.6</td>\n",
       "      <td>1916</td>\n",
       "      <td>89.7</td>\n",
       "      <td>589</td>\n",
       "      <td>27.6</td>\n",
       "      <td>02</td>\n",
       "      <td>195</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3947</td>\n",
       "      <td>297</td>\n",
       "      <td>7.5</td>\n",
       "      <td>583</td>\n",
       "      <td>14.8</td>\n",
       "      <td>2117</td>\n",
       "      <td>53.6</td>\n",
       "      <td>654</td>\n",
       "      <td>16.6</td>\n",
       "      <td>106</td>\n",
       "      <td>2.7</td>\n",
       "      <td>132</td>\n",
       "      <td>3.3</td>\n",
       "      <td>58</td>\n",
       "      <td>1.5</td>\n",
       "      <td>3067</td>\n",
       "      <td>77.7</td>\n",
       "      <td>190</td>\n",
       "      <td>4.8</td>\n",
       "      <td>02</td>\n",
       "      <td>158</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2811491</td>\n",
       "      <td>165555</td>\n",
       "      <td>5.9</td>\n",
       "      <td>188958</td>\n",
       "      <td>6.7</td>\n",
       "      <td>634810</td>\n",
       "      <td>22.6</td>\n",
       "      <td>685299</td>\n",
       "      <td>24.4</td>\n",
       "      <td>237799</td>\n",
       "      <td>8.5</td>\n",
       "      <td>571408</td>\n",
       "      <td>20.3</td>\n",
       "      <td>327662</td>\n",
       "      <td>11.7</td>\n",
       "      <td>2456978</td>\n",
       "      <td>87.4</td>\n",
       "      <td>899070</td>\n",
       "      <td>32.0</td>\n",
       "      <td>04</td>\n",
       "      <td>013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>23531</td>\n",
       "      <td>1054</td>\n",
       "      <td>4.5</td>\n",
       "      <td>2325</td>\n",
       "      <td>9.9</td>\n",
       "      <td>7028</td>\n",
       "      <td>29.9</td>\n",
       "      <td>7105</td>\n",
       "      <td>30.2</td>\n",
       "      <td>2421</td>\n",
       "      <td>10.3</td>\n",
       "      <td>2272</td>\n",
       "      <td>9.7</td>\n",
       "      <td>1326</td>\n",
       "      <td>5.6</td>\n",
       "      <td>20152</td>\n",
       "      <td>85.6</td>\n",
       "      <td>3598</td>\n",
       "      <td>15.3</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  DP02_0058E  DP02_0059E  DP02_0059PE  DP02_0060E  DP02_0060PE  DP02_0061E   \n",
       "1        2135          70          3.3         149          7.0         680   \n",
       "2        3947         297          7.5         583         14.8        2117   \n",
       "3     2811491      165555          5.9      188958          6.7      634810   \n",
       "4       23531        1054          4.5        2325          9.9        7028   \n",
       "\n",
       "            6           7            8           9            10          11  \\\n",
       "0  DP02_0061PE  DP02_0062E  DP02_0062PE  DP02_0063E  DP02_0063PE  DP02_0064E   \n",
       "1         31.9         472         22.1         175          8.2         469   \n",
       "2         53.6         654         16.6         106          2.7         132   \n",
       "3         22.6      685299         24.4      237799          8.5      571408   \n",
       "4         29.9        7105         30.2        2421         10.3        2272   \n",
       "\n",
       "            12          13           14          15           16          17  \\\n",
       "0  DP02_0064PE  DP02_0065E  DP02_0065PE  DP02_0066E  DP02_0066PE  DP02_0067E   \n",
       "1         22.0         120          5.6        1916         89.7         589   \n",
       "2          3.3          58          1.5        3067         77.7         190   \n",
       "3         20.3      327662         11.7     2456978         87.4      899070   \n",
       "4          9.7        1326          5.6       20152         85.6        3598   \n",
       "\n",
       "            18     19      20  \n",
       "0  DP02_0067PE  state  county  \n",
       "1         27.6     02     195  \n",
       "2          4.8     02     158  \n",
       "3         32.0     04     013  \n",
       "4         15.3     04     009  "
      ]
     },
     "execution_count": 26,
     "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": 27,
   "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": 28,
   "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": 29,
   "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": 30,
   "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>population25YearsAndOver</th>\n",
       "      <th>lessThan9thGrade</th>\n",
       "      <th>lessThan9thGradePct</th>\n",
       "      <th>grade9thTo12thNoDiploma</th>\n",
       "      <th>grade9thTo12thNoDiplomaPct</th>\n",
       "      <th>highSchoolGraduate</th>\n",
       "      <th>highSchoolGraduatePct</th>\n",
       "      <th>someCollegeNoDegree</th>\n",
       "      <th>someCollegeNoDegreePct</th>\n",
       "      <th>associatesDegree</th>\n",
       "      <th>associatesDegreePct</th>\n",
       "      <th>bachelorsDegree</th>\n",
       "      <th>bachelorsDegreePct</th>\n",
       "      <th>graduateOrProfessionalDegree</th>\n",
       "      <th>graduateOrProfessionalDegreePct</th>\n",
       "      <th>highSchoolGraduateOrHigher</th>\n",
       "      <th>highSchoolGraduateOrHigherPct</th>\n",
       "      <th>bachelorsDegreeOrHigher</th>\n",
       "      <th>bachelorsDegreeOrHigherPct</th>\n",
       "      <th>stateFips</th>\n",
       "      <th>countyFips</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2135</td>\n",
       "      <td>70</td>\n",
       "      <td>3.3</td>\n",
       "      <td>149</td>\n",
       "      <td>7.0</td>\n",
       "      <td>680</td>\n",
       "      <td>31.9</td>\n",
       "      <td>472</td>\n",
       "      <td>22.1</td>\n",
       "      <td>175</td>\n",
       "      <td>8.2</td>\n",
       "      <td>469</td>\n",
       "      <td>22.0</td>\n",
       "      <td>120</td>\n",
       "      <td>5.6</td>\n",
       "      <td>1916</td>\n",
       "      <td>89.7</td>\n",
       "      <td>589</td>\n",
       "      <td>27.6</td>\n",
       "      <td>02</td>\n",
       "      <td>195</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3947</td>\n",
       "      <td>297</td>\n",
       "      <td>7.5</td>\n",
       "      <td>583</td>\n",
       "      <td>14.8</td>\n",
       "      <td>2117</td>\n",
       "      <td>53.6</td>\n",
       "      <td>654</td>\n",
       "      <td>16.6</td>\n",
       "      <td>106</td>\n",
       "      <td>2.7</td>\n",
       "      <td>132</td>\n",
       "      <td>3.3</td>\n",
       "      <td>58</td>\n",
       "      <td>1.5</td>\n",
       "      <td>3067</td>\n",
       "      <td>77.7</td>\n",
       "      <td>190</td>\n",
       "      <td>4.8</td>\n",
       "      <td>02</td>\n",
       "      <td>158</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2811491</td>\n",
       "      <td>165555</td>\n",
       "      <td>5.9</td>\n",
       "      <td>188958</td>\n",
       "      <td>6.7</td>\n",
       "      <td>634810</td>\n",
       "      <td>22.6</td>\n",
       "      <td>685299</td>\n",
       "      <td>24.4</td>\n",
       "      <td>237799</td>\n",
       "      <td>8.5</td>\n",
       "      <td>571408</td>\n",
       "      <td>20.3</td>\n",
       "      <td>327662</td>\n",
       "      <td>11.7</td>\n",
       "      <td>2456978</td>\n",
       "      <td>87.4</td>\n",
       "      <td>899070</td>\n",
       "      <td>32.0</td>\n",
       "      <td>04</td>\n",
       "      <td>013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>23531</td>\n",
       "      <td>1054</td>\n",
       "      <td>4.5</td>\n",
       "      <td>2325</td>\n",
       "      <td>9.9</td>\n",
       "      <td>7028</td>\n",
       "      <td>29.9</td>\n",
       "      <td>7105</td>\n",
       "      <td>30.2</td>\n",
       "      <td>2421</td>\n",
       "      <td>10.3</td>\n",
       "      <td>2272</td>\n",
       "      <td>9.7</td>\n",
       "      <td>1326</td>\n",
       "      <td>5.6</td>\n",
       "      <td>20152</td>\n",
       "      <td>85.6</td>\n",
       "      <td>3598</td>\n",
       "      <td>15.3</td>\n",
       "      <td>04</td>\n",
       "      <td>009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>29228</td>\n",
       "      <td>3404</td>\n",
       "      <td>11.6</td>\n",
       "      <td>3723</td>\n",
       "      <td>12.7</td>\n",
       "      <td>7957</td>\n",
       "      <td>27.2</td>\n",
       "      <td>6308</td>\n",
       "      <td>21.6</td>\n",
       "      <td>1698</td>\n",
       "      <td>5.8</td>\n",
       "      <td>3993</td>\n",
       "      <td>13.7</td>\n",
       "      <td>2145</td>\n",
       "      <td>7.3</td>\n",
       "      <td>22101</td>\n",
       "      <td>75.6</td>\n",
       "      <td>6138</td>\n",
       "      <td>21.0</td>\n",
       "      <td>04</td>\n",
       "      <td>023</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  population25YearsAndOver lessThan9thGrade lessThan9thGradePct  \\\n",
       "1                     2135               70                 3.3   \n",
       "2                     3947              297                 7.5   \n",
       "3                  2811491           165555                 5.9   \n",
       "4                    23531             1054                 4.5   \n",
       "5                    29228             3404                11.6   \n",
       "\n",
       "  grade9thTo12thNoDiploma grade9thTo12thNoDiplomaPct highSchoolGraduate  \\\n",
       "1                     149                        7.0                680   \n",
       "2                     583                       14.8               2117   \n",
       "3                  188958                        6.7             634810   \n",
       "4                    2325                        9.9               7028   \n",
       "5                    3723                       12.7               7957   \n",
       "\n",
       "  highSchoolGraduatePct someCollegeNoDegree someCollegeNoDegreePct  \\\n",
       "1                  31.9                 472                   22.1   \n",
       "2                  53.6                 654                   16.6   \n",
       "3                  22.6              685299                   24.4   \n",
       "4                  29.9                7105                   30.2   \n",
       "5                  27.2                6308                   21.6   \n",
       "\n",
       "  associatesDegree associatesDegreePct bachelorsDegree bachelorsDegreePct  \\\n",
       "1              175                 8.2             469               22.0   \n",
       "2              106                 2.7             132                3.3   \n",
       "3           237799                 8.5          571408               20.3   \n",
       "4             2421                10.3            2272                9.7   \n",
       "5             1698                 5.8            3993               13.7   \n",
       "\n",
       "  graduateOrProfessionalDegree graduateOrProfessionalDegreePct  \\\n",
       "1                          120                             5.6   \n",
       "2                           58                             1.5   \n",
       "3                       327662                            11.7   \n",
       "4                         1326                             5.6   \n",
       "5                         2145                             7.3   \n",
       "\n",
       "  highSchoolGraduateOrHigher highSchoolGraduateOrHigherPct  \\\n",
       "1                       1916                          89.7   \n",
       "2                       3067                          77.7   \n",
       "3                    2456978                          87.4   \n",
       "4                      20152                          85.6   \n",
       "5                      22101                          75.6   \n",
       "\n",
       "  bachelorsDegreeOrHigher bachelorsDegreeOrHigherPct stateFips countyFips  \n",
       "1                     589                       27.6        02        195  \n",
       "2                     190                        4.8        02        158  \n",
       "3                  899070                       32.0        04        013  \n",
       "4                    3598                       15.3        04        009  \n",
       "5                    6138                       21.0        04        023  "
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "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>population25YearsAndOver</th>\n",
       "      <th>lessThan9thGrade</th>\n",
       "      <th>lessThan9thGradePct</th>\n",
       "      <th>grade9thTo12thNoDiploma</th>\n",
       "      <th>grade9thTo12thNoDiplomaPct</th>\n",
       "      <th>highSchoolGraduate</th>\n",
       "      <th>highSchoolGraduatePct</th>\n",
       "      <th>someCollegeNoDegree</th>\n",
       "      <th>someCollegeNoDegreePct</th>\n",
       "      <th>associatesDegree</th>\n",
       "      <th>associatesDegreePct</th>\n",
       "      <th>bachelorsDegree</th>\n",
       "      <th>bachelorsDegreePct</th>\n",
       "      <th>graduateOrProfessionalDegree</th>\n",
       "      <th>graduateOrProfessionalDegreePct</th>\n",
       "      <th>highSchoolGraduateOrHigher</th>\n",
       "      <th>highSchoolGraduateOrHigherPct</th>\n",
       "      <th>bachelorsDegreeOrHigher</th>\n",
       "      <th>bachelorsDegreeOrHigherPct</th>\n",
       "      <th>stateFips</th>\n",
       "      <th>countyFips</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>897</th>\n",
       "      <td>2223376</td>\n",
       "      <td>149313</td>\n",
       "      <td>6.7</td>\n",
       "      <td>136695</td>\n",
       "      <td>6.1</td>\n",
       "      <td>409272</td>\n",
       "      <td>18.4</td>\n",
       "      <td>498566</td>\n",
       "      <td>22.4</td>\n",
       "      <td>181508</td>\n",
       "      <td>8.2</td>\n",
       "      <td>521525</td>\n",
       "      <td>23.5</td>\n",
       "      <td>326497</td>\n",
       "      <td>14.7</td>\n",
       "      <td>1937368</td>\n",
       "      <td>87.1</td>\n",
       "      <td>848022</td>\n",
       "      <td>38.1</td>\n",
       "      <td>06</td>\n",
       "      <td>073</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    population25YearsAndOver lessThan9thGrade lessThan9thGradePct  \\\n",
       "897                  2223376           149313                 6.7   \n",
       "\n",
       "    grade9thTo12thNoDiploma grade9thTo12thNoDiplomaPct highSchoolGraduate  \\\n",
       "897                  136695                        6.1             409272   \n",
       "\n",
       "    highSchoolGraduatePct someCollegeNoDegree someCollegeNoDegreePct  \\\n",
       "897                  18.4              498566                   22.4   \n",
       "\n",
       "    associatesDegree associatesDegreePct bachelorsDegree bachelorsDegreePct  \\\n",
       "897           181508                 8.2          521525               23.5   \n",
       "\n",
       "    graduateOrProfessionalDegree graduateOrProfessionalDegreePct  \\\n",
       "897                       326497                            14.7   \n",
       "\n",
       "    highSchoolGraduateOrHigher highSchoolGraduateOrHigherPct  \\\n",
       "897                    1937368                          87.1   \n",
       "\n",
       "    bachelorsDegreeOrHigher bachelorsDegreeOrHigherPct stateFips countyFips  \n",
       "897                  848022                       38.1        06        073  "
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Example data\n",
    "df[(df['stateFips'] == '06') & (df['countyFips'] == '073')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "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": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv(NEO4J_IMPORT / \"03a-USCensusDP02EducationAdmin2.csv\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Download zip-level data using US Census API"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "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": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</th>\n",
       "      <th>10</th>\n",
       "      <th>11</th>\n",
       "      <th>12</th>\n",
       "      <th>13</th>\n",
       "      <th>14</th>\n",
       "      <th>15</th>\n",
       "      <th>16</th>\n",
       "      <th>17</th>\n",
       "      <th>18</th>\n",
       "      <th>19</th>\n",
       "      <th>20</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>DP02_0058E</td>\n",
       "      <td>DP02_0059E</td>\n",
       "      <td>DP02_0059PE</td>\n",
       "      <td>DP02_0060E</td>\n",
       "      <td>DP02_0060PE</td>\n",
       "      <td>DP02_0061E</td>\n",
       "      <td>DP02_0061PE</td>\n",
       "      <td>DP02_0062E</td>\n",
       "      <td>DP02_0062PE</td>\n",
       "      <td>DP02_0063E</td>\n",
       "      <td>DP02_0063PE</td>\n",
       "      <td>DP02_0064E</td>\n",
       "      <td>DP02_0064PE</td>\n",
       "      <td>DP02_0065E</td>\n",
       "      <td>DP02_0065PE</td>\n",
       "      <td>DP02_0066E</td>\n",
       "      <td>DP02_0066PE</td>\n",
       "      <td>DP02_0067E</td>\n",
       "      <td>DP02_0067PE</td>\n",
       "      <td>state</td>\n",
       "      <td>zip code tabulation area</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1709</td>\n",
       "      <td>94</td>\n",
       "      <td>5.5</td>\n",
       "      <td>171</td>\n",
       "      <td>10.0</td>\n",
       "      <td>620</td>\n",
       "      <td>36.3</td>\n",
       "      <td>379</td>\n",
       "      <td>22.2</td>\n",
       "      <td>186</td>\n",
       "      <td>10.9</td>\n",
       "      <td>131</td>\n",
       "      <td>7.7</td>\n",
       "      <td>128</td>\n",
       "      <td>7.5</td>\n",
       "      <td>1444</td>\n",
       "      <td>84.5</td>\n",
       "      <td>259</td>\n",
       "      <td>15.2</td>\n",
       "      <td>51</td>\n",
       "      <td>23833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>863</td>\n",
       "      <td>43</td>\n",
       "      <td>5.0</td>\n",
       "      <td>186</td>\n",
       "      <td>21.6</td>\n",
       "      <td>327</td>\n",
       "      <td>37.9</td>\n",
       "      <td>137</td>\n",
       "      <td>15.9</td>\n",
       "      <td>107</td>\n",
       "      <td>12.4</td>\n",
       "      <td>63</td>\n",
       "      <td>7.3</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>634</td>\n",
       "      <td>73.5</td>\n",
       "      <td>63</td>\n",
       "      <td>7.3</td>\n",
       "      <td>51</td>\n",
       "      <td>23850</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>9191</td>\n",
       "      <td>471</td>\n",
       "      <td>5.1</td>\n",
       "      <td>1132</td>\n",
       "      <td>12.3</td>\n",
       "      <td>2688</td>\n",
       "      <td>29.2</td>\n",
       "      <td>2199</td>\n",
       "      <td>23.9</td>\n",
       "      <td>914</td>\n",
       "      <td>9.9</td>\n",
       "      <td>1148</td>\n",
       "      <td>12.5</td>\n",
       "      <td>639</td>\n",
       "      <td>7.0</td>\n",
       "      <td>7588</td>\n",
       "      <td>82.6</td>\n",
       "      <td>1787</td>\n",
       "      <td>19.4</td>\n",
       "      <td>51</td>\n",
       "      <td>23851</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2654</td>\n",
       "      <td>205</td>\n",
       "      <td>7.7</td>\n",
       "      <td>243</td>\n",
       "      <td>9.2</td>\n",
       "      <td>1047</td>\n",
       "      <td>39.4</td>\n",
       "      <td>517</td>\n",
       "      <td>19.5</td>\n",
       "      <td>192</td>\n",
       "      <td>7.2</td>\n",
       "      <td>274</td>\n",
       "      <td>10.3</td>\n",
       "      <td>176</td>\n",
       "      <td>6.6</td>\n",
       "      <td>2206</td>\n",
       "      <td>83.1</td>\n",
       "      <td>450</td>\n",
       "      <td>17.0</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  DP02_0058E  DP02_0059E  DP02_0059PE  DP02_0060E  DP02_0060PE  DP02_0061E   \n",
       "1        1709          94          5.5         171         10.0         620   \n",
       "2         863          43          5.0         186         21.6         327   \n",
       "3        9191         471          5.1        1132         12.3        2688   \n",
       "4        2654         205          7.7         243          9.2        1047   \n",
       "\n",
       "            6           7            8           9            10          11  \\\n",
       "0  DP02_0061PE  DP02_0062E  DP02_0062PE  DP02_0063E  DP02_0063PE  DP02_0064E   \n",
       "1         36.3         379         22.2         186         10.9         131   \n",
       "2         37.9         137         15.9         107         12.4          63   \n",
       "3         29.2        2199         23.9         914          9.9        1148   \n",
       "4         39.4         517         19.5         192          7.2         274   \n",
       "\n",
       "            12          13           14          15           16          17  \\\n",
       "0  DP02_0064PE  DP02_0065E  DP02_0065PE  DP02_0066E  DP02_0066PE  DP02_0067E   \n",
       "1          7.7         128          7.5        1444         84.5         259   \n",
       "2          7.3           0          0.0         634         73.5          63   \n",
       "3         12.5         639          7.0        7588         82.6        1787   \n",
       "4         10.3         176          6.6        2206         83.1         450   \n",
       "\n",
       "            18     19                        20  \n",
       "0  DP02_0067PE  state  zip code tabulation area  \n",
       "1         15.2     51                     23833  \n",
       "2          7.3     51                     23850  \n",
       "3         19.4     51                     23851  \n",
       "4         17.0     51                     23890  "
      ]
     },
     "execution_count": 35,
     "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": 38,
   "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": 39,
   "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>population25YearsAndOver</th>\n",
       "      <th>lessThan9thGrade</th>\n",
       "      <th>lessThan9thGradePct</th>\n",
       "      <th>grade9thTo12thNoDiploma</th>\n",
       "      <th>grade9thTo12thNoDiplomaPct</th>\n",
       "      <th>highSchoolGraduate</th>\n",
       "      <th>highSchoolGraduatePct</th>\n",
       "      <th>someCollegeNoDegree</th>\n",
       "      <th>someCollegeNoDegreePct</th>\n",
       "      <th>associatesDegree</th>\n",
       "      <th>associatesDegreePct</th>\n",
       "      <th>bachelorsDegree</th>\n",
       "      <th>bachelorsDegreePct</th>\n",
       "      <th>graduateOrProfessionalDegree</th>\n",
       "      <th>graduateOrProfessionalDegreePct</th>\n",
       "      <th>highSchoolGraduateOrHigher</th>\n",
       "      <th>highSchoolGraduateOrHigherPct</th>\n",
       "      <th>bachelorsDegreeOrHigher</th>\n",
       "      <th>bachelorsDegreeOrHigherPct</th>\n",
       "      <th>stateFips</th>\n",
       "      <th>postalCode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>863</td>\n",
       "      <td>43</td>\n",
       "      <td>5.0</td>\n",
       "      <td>186</td>\n",
       "      <td>21.6</td>\n",
       "      <td>327</td>\n",
       "      <td>37.9</td>\n",
       "      <td>137</td>\n",
       "      <td>15.9</td>\n",
       "      <td>107</td>\n",
       "      <td>12.4</td>\n",
       "      <td>63</td>\n",
       "      <td>7.3</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>634</td>\n",
       "      <td>73.5</td>\n",
       "      <td>63</td>\n",
       "      <td>7.3</td>\n",
       "      <td>51</td>\n",
       "      <td>23850</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>9191</td>\n",
       "      <td>471</td>\n",
       "      <td>5.1</td>\n",
       "      <td>1132</td>\n",
       "      <td>12.3</td>\n",
       "      <td>2688</td>\n",
       "      <td>29.2</td>\n",
       "      <td>2199</td>\n",
       "      <td>23.9</td>\n",
       "      <td>914</td>\n",
       "      <td>9.9</td>\n",
       "      <td>1148</td>\n",
       "      <td>12.5</td>\n",
       "      <td>639</td>\n",
       "      <td>7.0</td>\n",
       "      <td>7588</td>\n",
       "      <td>82.6</td>\n",
       "      <td>1787</td>\n",
       "      <td>19.4</td>\n",
       "      <td>51</td>\n",
       "      <td>23851</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2654</td>\n",
       "      <td>205</td>\n",
       "      <td>7.7</td>\n",
       "      <td>243</td>\n",
       "      <td>9.2</td>\n",
       "      <td>1047</td>\n",
       "      <td>39.4</td>\n",
       "      <td>517</td>\n",
       "      <td>19.5</td>\n",
       "      <td>192</td>\n",
       "      <td>7.2</td>\n",
       "      <td>274</td>\n",
       "      <td>10.3</td>\n",
       "      <td>176</td>\n",
       "      <td>6.6</td>\n",
       "      <td>2206</td>\n",
       "      <td>83.1</td>\n",
       "      <td>450</td>\n",
       "      <td>17.0</td>\n",
       "      <td>51</td>\n",
       "      <td>23890</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>164</td>\n",
       "      <td>49</td>\n",
       "      <td>29.9</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>17</td>\n",
       "      <td>10.4</td>\n",
       "      <td>11</td>\n",
       "      <td>6.7</td>\n",
       "      <td>46</td>\n",
       "      <td>28.0</td>\n",
       "      <td>28</td>\n",
       "      <td>17.1</td>\n",
       "      <td>13</td>\n",
       "      <td>7.9</td>\n",
       "      <td>115</td>\n",
       "      <td>70.1</td>\n",
       "      <td>41</td>\n",
       "      <td>25.0</td>\n",
       "      <td>51</td>\n",
       "      <td>23302</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>406</td>\n",
       "      <td>10</td>\n",
       "      <td>2.5</td>\n",
       "      <td>82</td>\n",
       "      <td>20.2</td>\n",
       "      <td>194</td>\n",
       "      <td>47.8</td>\n",
       "      <td>104</td>\n",
       "      <td>25.6</td>\n",
       "      <td>16</td>\n",
       "      <td>3.9</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>314</td>\n",
       "      <td>77.3</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>51</td>\n",
       "      <td>23307</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  population25YearsAndOver lessThan9thGrade lessThan9thGradePct  \\\n",
       "2                      863               43                 5.0   \n",
       "3                     9191              471                 5.1   \n",
       "4                     2654              205                 7.7   \n",
       "5                      164               49                29.9   \n",
       "6                      406               10                 2.5   \n",
       "\n",
       "  grade9thTo12thNoDiploma grade9thTo12thNoDiplomaPct highSchoolGraduate  \\\n",
       "2                     186                       21.6                327   \n",
       "3                    1132                       12.3               2688   \n",
       "4                     243                        9.2               1047   \n",
       "5                       0                        0.0                 17   \n",
       "6                      82                       20.2                194   \n",
       "\n",
       "  highSchoolGraduatePct someCollegeNoDegree someCollegeNoDegreePct  \\\n",
       "2                  37.9                 137                   15.9   \n",
       "3                  29.2                2199                   23.9   \n",
       "4                  39.4                 517                   19.5   \n",
       "5                  10.4                  11                    6.7   \n",
       "6                  47.8                 104                   25.6   \n",
       "\n",
       "  associatesDegree associatesDegreePct bachelorsDegree bachelorsDegreePct  \\\n",
       "2              107                12.4              63                7.3   \n",
       "3              914                 9.9            1148               12.5   \n",
       "4              192                 7.2             274               10.3   \n",
       "5               46                28.0              28               17.1   \n",
       "6               16                 3.9               0                0.0   \n",
       "\n",
       "  graduateOrProfessionalDegree graduateOrProfessionalDegreePct  \\\n",
       "2                            0                             0.0   \n",
       "3                          639                             7.0   \n",
       "4                          176                             6.6   \n",
       "5                           13                             7.9   \n",
       "6                            0                             0.0   \n",
       "\n",
       "  highSchoolGraduateOrHigher highSchoolGraduateOrHigherPct  \\\n",
       "2                        634                          73.5   \n",
       "3                       7588                          82.6   \n",
       "4                       2206                          83.1   \n",
       "5                        115                          70.1   \n",
       "6                        314                          77.3   \n",
       "\n",
       "  bachelorsDegreeOrHigher bachelorsDegreeOrHigherPct stateFips postalCode  \n",
       "2                      63                        7.3        51      23850  \n",
       "3                    1787                       19.4        51      23851  \n",
       "4                     450                       17.0        51      23890  \n",
       "5                      41                       25.0        51      23302  \n",
       "6                       0                        0.0        51      23307  "
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "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>population25YearsAndOver</th>\n",
       "      <th>lessThan9thGrade</th>\n",
       "      <th>lessThan9thGradePct</th>\n",
       "      <th>grade9thTo12thNoDiploma</th>\n",
       "      <th>grade9thTo12thNoDiplomaPct</th>\n",
       "      <th>highSchoolGraduate</th>\n",
       "      <th>highSchoolGraduatePct</th>\n",
       "      <th>someCollegeNoDegree</th>\n",
       "      <th>someCollegeNoDegreePct</th>\n",
       "      <th>associatesDegree</th>\n",
       "      <th>associatesDegreePct</th>\n",
       "      <th>bachelorsDegree</th>\n",
       "      <th>bachelorsDegreePct</th>\n",
       "      <th>graduateOrProfessionalDegree</th>\n",
       "      <th>graduateOrProfessionalDegreePct</th>\n",
       "      <th>highSchoolGraduateOrHigher</th>\n",
       "      <th>highSchoolGraduateOrHigherPct</th>\n",
       "      <th>bachelorsDegreeOrHigher</th>\n",
       "      <th>bachelorsDegreeOrHigherPct</th>\n",
       "      <th>stateFips</th>\n",
       "      <th>postalCode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>24526</th>\n",
       "      <td>15118</td>\n",
       "      <td>390</td>\n",
       "      <td>2.6</td>\n",
       "      <td>414</td>\n",
       "      <td>2.7</td>\n",
       "      <td>1720</td>\n",
       "      <td>11.4</td>\n",
       "      <td>2019</td>\n",
       "      <td>13.4</td>\n",
       "      <td>791</td>\n",
       "      <td>5.2</td>\n",
       "      <td>4935</td>\n",
       "      <td>32.6</td>\n",
       "      <td>4849</td>\n",
       "      <td>32.1</td>\n",
       "      <td>14314</td>\n",
       "      <td>94.7</td>\n",
       "      <td>9784</td>\n",
       "      <td>64.7</td>\n",
       "      <td>06</td>\n",
       "      <td>90210</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      population25YearsAndOver lessThan9thGrade lessThan9thGradePct  \\\n",
       "24526                    15118              390                 2.6   \n",
       "\n",
       "      grade9thTo12thNoDiploma grade9thTo12thNoDiplomaPct highSchoolGraduate  \\\n",
       "24526                     414                        2.7               1720   \n",
       "\n",
       "      highSchoolGraduatePct someCollegeNoDegree someCollegeNoDegreePct  \\\n",
       "24526                  11.4                2019                   13.4   \n",
       "\n",
       "      associatesDegree associatesDegreePct bachelorsDegree bachelorsDegreePct  \\\n",
       "24526              791                 5.2            4935               32.6   \n",
       "\n",
       "      graduateOrProfessionalDegree graduateOrProfessionalDegreePct  \\\n",
       "24526                         4849                            32.1   \n",
       "\n",
       "      highSchoolGraduateOrHigher highSchoolGraduateOrHigherPct  \\\n",
       "24526                      14314                          94.7   \n",
       "\n",
       "      bachelorsDegreeOrHigher bachelorsDegreeOrHigherPct stateFips postalCode  \n",
       "24526                    9784                       64.7        06      90210  "
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Example data\n",
    "df.query(\"postalCode == '90210'\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "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": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv(NEO4J_IMPORT / \"03a-USCensusDP02EducationZip.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": 43,
   "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": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.concat((get_tract_data(state) for state in stateFips))\n",
    "df.fillna('', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['tract'] = df['stateFips'] + df['countyFips'] + df['tract']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['source'] = 'American Community Survey 5 year'\n",
    "df['aggregationLevel'] = 'Tract'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "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>population25YearsAndOver</th>\n",
       "      <th>lessThan9thGrade</th>\n",
       "      <th>lessThan9thGradePct</th>\n",
       "      <th>grade9thTo12thNoDiploma</th>\n",
       "      <th>grade9thTo12thNoDiplomaPct</th>\n",
       "      <th>highSchoolGraduate</th>\n",
       "      <th>highSchoolGraduatePct</th>\n",
       "      <th>someCollegeNoDegree</th>\n",
       "      <th>someCollegeNoDegreePct</th>\n",
       "      <th>associatesDegree</th>\n",
       "      <th>associatesDegreePct</th>\n",
       "      <th>bachelorsDegree</th>\n",
       "      <th>bachelorsDegreePct</th>\n",
       "      <th>graduateOrProfessionalDegree</th>\n",
       "      <th>graduateOrProfessionalDegreePct</th>\n",
       "      <th>highSchoolGraduateOrHigher</th>\n",
       "      <th>highSchoolGraduateOrHigherPct</th>\n",
       "      <th>bachelorsDegreeOrHigher</th>\n",
       "      <th>bachelorsDegreeOrHigherPct</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>1000</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>30</td>\n",
       "      <td>3.0</td>\n",
       "      <td>74</td>\n",
       "      <td>7.4</td>\n",
       "      <td>114</td>\n",
       "      <td>11.4</td>\n",
       "      <td>31</td>\n",
       "      <td>3.1</td>\n",
       "      <td>418</td>\n",
       "      <td>41.8</td>\n",
       "      <td>333</td>\n",
       "      <td>33.3</td>\n",
       "      <td>970</td>\n",
       "      <td>97.0</td>\n",
       "      <td>751</td>\n",
       "      <td>75.1</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>5183</td>\n",
       "      <td>340</td>\n",
       "      <td>6.6</td>\n",
       "      <td>156</td>\n",
       "      <td>3.0</td>\n",
       "      <td>901</td>\n",
       "      <td>17.4</td>\n",
       "      <td>1016</td>\n",
       "      <td>19.6</td>\n",
       "      <td>443</td>\n",
       "      <td>8.5</td>\n",
       "      <td>1305</td>\n",
       "      <td>25.2</td>\n",
       "      <td>1022</td>\n",
       "      <td>19.7</td>\n",
       "      <td>4687</td>\n",
       "      <td>90.4</td>\n",
       "      <td>2327</td>\n",
       "      <td>44.9</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>6942</td>\n",
       "      <td>248</td>\n",
       "      <td>3.6</td>\n",
       "      <td>269</td>\n",
       "      <td>3.9</td>\n",
       "      <td>368</td>\n",
       "      <td>5.3</td>\n",
       "      <td>1199</td>\n",
       "      <td>17.3</td>\n",
       "      <td>730</td>\n",
       "      <td>10.5</td>\n",
       "      <td>2759</td>\n",
       "      <td>39.7</td>\n",
       "      <td>1369</td>\n",
       "      <td>19.7</td>\n",
       "      <td>6425</td>\n",
       "      <td>92.6</td>\n",
       "      <td>4128</td>\n",
       "      <td>59.5</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>4799</td>\n",
       "      <td>347</td>\n",
       "      <td>7.2</td>\n",
       "      <td>270</td>\n",
       "      <td>5.6</td>\n",
       "      <td>1038</td>\n",
       "      <td>21.6</td>\n",
       "      <td>1065</td>\n",
       "      <td>22.2</td>\n",
       "      <td>328</td>\n",
       "      <td>6.8</td>\n",
       "      <td>1080</td>\n",
       "      <td>22.5</td>\n",
       "      <td>671</td>\n",
       "      <td>14.0</td>\n",
       "      <td>4182</td>\n",
       "      <td>87.1</td>\n",
       "      <td>1751</td>\n",
       "      <td>36.5</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>5187</td>\n",
       "      <td>201</td>\n",
       "      <td>3.9</td>\n",
       "      <td>163</td>\n",
       "      <td>3.1</td>\n",
       "      <td>525</td>\n",
       "      <td>10.1</td>\n",
       "      <td>1063</td>\n",
       "      <td>20.5</td>\n",
       "      <td>452</td>\n",
       "      <td>8.7</td>\n",
       "      <td>1682</td>\n",
       "      <td>32.4</td>\n",
       "      <td>1101</td>\n",
       "      <td>21.2</td>\n",
       "      <td>4823</td>\n",
       "      <td>93.0</td>\n",
       "      <td>2783</td>\n",
       "      <td>53.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": [
       "   population25YearsAndOver lessThan9thGrade lessThan9thGradePct  \\\n",
       "6                      1000                0                 0.0   \n",
       "7                      5183              340                 6.6   \n",
       "8                      6942              248                 3.6   \n",
       "9                      4799              347                 7.2   \n",
       "10                     5187              201                 3.9   \n",
       "\n",
       "   grade9thTo12thNoDiploma grade9thTo12thNoDiplomaPct highSchoolGraduate  \\\n",
       "6                       30                        3.0                 74   \n",
       "7                      156                        3.0                901   \n",
       "8                      269                        3.9                368   \n",
       "9                      270                        5.6               1038   \n",
       "10                     163                        3.1                525   \n",
       "\n",
       "   highSchoolGraduatePct someCollegeNoDegree someCollegeNoDegreePct  \\\n",
       "6                    7.4                 114                   11.4   \n",
       "7                   17.4                1016                   19.6   \n",
       "8                    5.3                1199                   17.3   \n",
       "9                   21.6                1065                   22.2   \n",
       "10                  10.1                1063                   20.5   \n",
       "\n",
       "   associatesDegree associatesDegreePct bachelorsDegree bachelorsDegreePct  \\\n",
       "6                31                 3.1             418               41.8   \n",
       "7               443                 8.5            1305               25.2   \n",
       "8               730                10.5            2759               39.7   \n",
       "9               328                 6.8            1080               22.5   \n",
       "10              452                 8.7            1682               32.4   \n",
       "\n",
       "   graduateOrProfessionalDegree graduateOrProfessionalDegreePct  \\\n",
       "6                           333                            33.3   \n",
       "7                          1022                            19.7   \n",
       "8                          1369                            19.7   \n",
       "9                           671                            14.0   \n",
       "10                         1101                            21.2   \n",
       "\n",
       "   highSchoolGraduateOrHigher highSchoolGraduateOrHigherPct  \\\n",
       "6                         970                          97.0   \n",
       "7                        4687                          90.4   \n",
       "8                        6425                          92.6   \n",
       "9                        4182                          87.1   \n",
       "10                       4823                          93.0   \n",
       "\n",
       "   bachelorsDegreeOrHigher bachelorsDegreeOrHigherPct stateFips countyFips  \\\n",
       "6                      751                       75.1        06        073   \n",
       "7                     2327                       44.9        06        073   \n",
       "8                     4128                       59.5        06        073   \n",
       "9                     1751                       36.5        06        073   \n",
       "10                    2783                       53.7        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": 47,
     "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": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv(NEO4J_IMPORT / \"03a-USCensusDP02EducationTract.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(73056, 24)"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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
}