notebooks/dataprep/03a-USCensusDP03HealthInsurance.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Selected Economic Characteristics: Health Insurance Coverage 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 5-Year Data (2009-2018).\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 = {# HEALTH INSURANCE COVERAGE\n",
    "             'DP03_0095E': 'civilianNoninstitutionalizedPopulation',\n",
    "             'DP03_0096E': 'withHealthInsuranceCoverage',\n",
    "             'DP03_0096PE': 'withHealthInsuranceCoveragePct',\n",
    "             'DP03_0097E': 'withPrivateHealthInsurance',\n",
    "             'DP03_0097PE': 'withPrivateHealthInsurancePct',\n",
    "             'DP03_0098E': 'withPublicCoverage',\n",
    "             'DP03_0098PE': 'withPublicCoveragePct',\n",
    "             'DP03_0099E': 'noHealthInsuranceCoverage',\n",
    "             'DP03_0099PE': 'noHealthInsuranceCoveragePct',\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": [
      "h.civilianNoninstitutionalizedPopulation = toInteger(row.civilianNoninstitutionalizedPopulation),\n",
      "h.withHealthInsuranceCoverage = toInteger(row.withHealthInsuranceCoverage),\n",
      "h.withHealthInsuranceCoveragePct = toFloat(row.withHealthInsuranceCoveragePct),\n",
      "h.withPrivateHealthInsurance = toInteger(row.withPrivateHealthInsurance),\n",
      "h.withPrivateHealthInsurancePct = toFloat(row.withPrivateHealthInsurancePct),\n",
      "h.withPublicCoverage = toInteger(row.withPublicCoverage),\n",
      "h.withPublicCoveragePct = toFloat(row.withPublicCoveragePct),\n",
      "h.noHealthInsuranceCoverage = toInteger(row.noHealthInsuranceCoverage),\n",
      "h.noHealthInsuranceCoveragePct = toFloat(row.noHealthInsuranceCoveragePct),\n"
     ]
    }
   ],
   "source": [
    "for v in variables.values():\n",
    "    if 'Pct' in v:\n",
    "        print('h.' + v + ' = toFloat(row.' + v + '),')\n",
    "    else:\n",
    "        print('h.' + 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_0095E</td>\n",
       "      <td>DP03_0096E</td>\n",
       "      <td>DP03_0096PE</td>\n",
       "      <td>DP03_0097E</td>\n",
       "      <td>DP03_0097PE</td>\n",
       "      <td>DP03_0098E</td>\n",
       "      <td>DP03_0098PE</td>\n",
       "      <td>DP03_0099E</td>\n",
       "      <td>DP03_0099PE</td>\n",
       "      <td>state</td>\n",
       "      <td>county</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3245</td>\n",
       "      <td>2949</td>\n",
       "      <td>90.9</td>\n",
       "      <td>2047</td>\n",
       "      <td>63.1</td>\n",
       "      <td>1321</td>\n",
       "      <td>40.7</td>\n",
       "      <td>296</td>\n",
       "      <td>9.1</td>\n",
       "      <td>02</td>\n",
       "      <td>195</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8189</td>\n",
       "      <td>6463</td>\n",
       "      <td>78.9</td>\n",
       "      <td>1615</td>\n",
       "      <td>19.7</td>\n",
       "      <td>5330</td>\n",
       "      <td>65.1</td>\n",
       "      <td>1726</td>\n",
       "      <td>21.1</td>\n",
       "      <td>02</td>\n",
       "      <td>158</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4222760</td>\n",
       "      <td>3754381</td>\n",
       "      <td>88.9</td>\n",
       "      <td>2750843</td>\n",
       "      <td>65.1</td>\n",
       "      <td>1432684</td>\n",
       "      <td>33.9</td>\n",
       "      <td>468379</td>\n",
       "      <td>11.1</td>\n",
       "      <td>04</td>\n",
       "      <td>013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>34003</td>\n",
       "      <td>31115</td>\n",
       "      <td>91.5</td>\n",
       "      <td>19916</td>\n",
       "      <td>58.6</td>\n",
       "      <td>14907</td>\n",
       "      <td>43.8</td>\n",
       "      <td>2888</td>\n",
       "      <td>8.5</td>\n",
       "      <td>04</td>\n",
       "      <td>009</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           0           1            2           3            4           5   \\\n",
       "0  DP03_0095E  DP03_0096E  DP03_0096PE  DP03_0097E  DP03_0097PE  DP03_0098E   \n",
       "1        3245        2949         90.9        2047         63.1        1321   \n",
       "2        8189        6463         78.9        1615         19.7        5330   \n",
       "3     4222760     3754381         88.9     2750843         65.1     1432684   \n",
       "4       34003       31115         91.5       19916         58.6       14907   \n",
       "\n",
       "            6           7            8      9       10  \n",
       "0  DP03_0098PE  DP03_0099E  DP03_0099PE  state  county  \n",
       "1         40.7         296          9.1     02     195  \n",
       "2         65.1        1726         21.1     02     158  \n",
       "3         33.9      468379         11.1     04     013  \n",
       "4         43.8        2888          8.5     04     009  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_json(url_county, dtype='str')\n",
    "df.fillna('', inplace=True)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Add column names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df[1:].copy() # skip first row of labels\n",
    "columns = list(variables.values())\n",
    "columns.append('stateFips')\n",
    "columns.append('countyFips')\n",
    "df.columns = columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Remove Puerto Rico (stateFips = 72) to limit data to US States\n",
    "\n",
    "TODO handle data for Puerto Rico (GeoNames represents Puerto Rico as a country)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.query(\"stateFips != '72'\", inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Save list of state fips (required later to get tract data by state)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['01', '02', '04', '05', '06', '08', '09', '10', '11', '12', '13', '15', '16', '17', '18', '19', '20', '21', '22', '23', '24', '25', '26', '27', '28', '29', '30', '31', '32', '33', '34', '35', '36', '37', '38', '39', '40', '41', '42', '44', '45', '46', '47', '48', '49', '50', '51', '53', '54', '55', '56']\n"
     ]
    }
   ],
   "source": [
    "stateFips = list(df['stateFips'].unique())\n",
    "stateFips.sort()\n",
    "print(stateFips)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>civilianNoninstitutionalizedPopulation</th>\n",
       "      <th>withHealthInsuranceCoverage</th>\n",
       "      <th>withHealthInsuranceCoveragePct</th>\n",
       "      <th>withPrivateHealthInsurance</th>\n",
       "      <th>withPrivateHealthInsurancePct</th>\n",
       "      <th>withPublicCoverage</th>\n",
       "      <th>withPublicCoveragePct</th>\n",
       "      <th>noHealthInsuranceCoverage</th>\n",
       "      <th>noHealthInsuranceCoveragePct</th>\n",
       "      <th>stateFips</th>\n",
       "      <th>countyFips</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3245</td>\n",
       "      <td>2949</td>\n",
       "      <td>90.9</td>\n",
       "      <td>2047</td>\n",
       "      <td>63.1</td>\n",
       "      <td>1321</td>\n",
       "      <td>40.7</td>\n",
       "      <td>296</td>\n",
       "      <td>9.1</td>\n",
       "      <td>02</td>\n",
       "      <td>195</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8189</td>\n",
       "      <td>6463</td>\n",
       "      <td>78.9</td>\n",
       "      <td>1615</td>\n",
       "      <td>19.7</td>\n",
       "      <td>5330</td>\n",
       "      <td>65.1</td>\n",
       "      <td>1726</td>\n",
       "      <td>21.1</td>\n",
       "      <td>02</td>\n",
       "      <td>158</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4222760</td>\n",
       "      <td>3754381</td>\n",
       "      <td>88.9</td>\n",
       "      <td>2750843</td>\n",
       "      <td>65.1</td>\n",
       "      <td>1432684</td>\n",
       "      <td>33.9</td>\n",
       "      <td>468379</td>\n",
       "      <td>11.1</td>\n",
       "      <td>04</td>\n",
       "      <td>013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>34003</td>\n",
       "      <td>31115</td>\n",
       "      <td>91.5</td>\n",
       "      <td>19916</td>\n",
       "      <td>58.6</td>\n",
       "      <td>14907</td>\n",
       "      <td>43.8</td>\n",
       "      <td>2888</td>\n",
       "      <td>8.5</td>\n",
       "      <td>04</td>\n",
       "      <td>009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>46251</td>\n",
       "      <td>40902</td>\n",
       "      <td>88.4</td>\n",
       "      <td>22089</td>\n",
       "      <td>47.8</td>\n",
       "      <td>24658</td>\n",
       "      <td>53.3</td>\n",
       "      <td>5349</td>\n",
       "      <td>11.6</td>\n",
       "      <td>04</td>\n",
       "      <td>023</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  civilianNoninstitutionalizedPopulation withHealthInsuranceCoverage  \\\n",
       "1                                   3245                        2949   \n",
       "2                                   8189                        6463   \n",
       "3                                4222760                     3754381   \n",
       "4                                  34003                       31115   \n",
       "5                                  46251                       40902   \n",
       "\n",
       "  withHealthInsuranceCoveragePct withPrivateHealthInsurance  \\\n",
       "1                           90.9                       2047   \n",
       "2                           78.9                       1615   \n",
       "3                           88.9                    2750843   \n",
       "4                           91.5                      19916   \n",
       "5                           88.4                      22089   \n",
       "\n",
       "  withPrivateHealthInsurancePct withPublicCoverage withPublicCoveragePct  \\\n",
       "1                          63.1               1321                  40.7   \n",
       "2                          19.7               5330                  65.1   \n",
       "3                          65.1            1432684                  33.9   \n",
       "4                          58.6              14907                  43.8   \n",
       "5                          47.8              24658                  53.3   \n",
       "\n",
       "  noHealthInsuranceCoverage noHealthInsuranceCoveragePct stateFips countyFips  \n",
       "1                       296                          9.1        02        195  \n",
       "2                      1726                         21.1        02        158  \n",
       "3                    468379                         11.1        04        013  \n",
       "4                      2888                          8.5        04        009  \n",
       "5                      5349                         11.6        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>civilianNoninstitutionalizedPopulation</th>\n",
       "      <th>withHealthInsuranceCoverage</th>\n",
       "      <th>withHealthInsuranceCoveragePct</th>\n",
       "      <th>withPrivateHealthInsurance</th>\n",
       "      <th>withPrivateHealthInsurancePct</th>\n",
       "      <th>withPublicCoverage</th>\n",
       "      <th>withPublicCoveragePct</th>\n",
       "      <th>noHealthInsuranceCoverage</th>\n",
       "      <th>noHealthInsuranceCoveragePct</th>\n",
       "      <th>stateFips</th>\n",
       "      <th>countyFips</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>897</th>\n",
       "      <td>3204470</td>\n",
       "      <td>2924868</td>\n",
       "      <td>91.3</td>\n",
       "      <td>2185866</td>\n",
       "      <td>68.2</td>\n",
       "      <td>1056014</td>\n",
       "      <td>33.0</td>\n",
       "      <td>279602</td>\n",
       "      <td>8.7</td>\n",
       "      <td>06</td>\n",
       "      <td>073</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    civilianNoninstitutionalizedPopulation withHealthInsuranceCoverage  \\\n",
       "897                                3204470                     2924868   \n",
       "\n",
       "    withHealthInsuranceCoveragePct withPrivateHealthInsurance  \\\n",
       "897                           91.3                    2185866   \n",
       "\n",
       "    withPrivateHealthInsurancePct withPublicCoverage withPublicCoveragePct  \\\n",
       "897                          68.2            1056014                  33.0   \n",
       "\n",
       "    noHealthInsuranceCoverage noHealthInsuranceCoveragePct stateFips  \\\n",
       "897                    279602                          8.7        06   \n",
       "\n",
       "    countyFips  \n",
       "897        073  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Example data\n",
    "df[(df['stateFips'] == '06') & (df['countyFips'] == '073')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['source'] = 'American Community Survey 5 year'\n",
    "df['aggregationLevel'] = 'Admin2'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Save data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv(NEO4J_IMPORT / \"03a-USCensusDP03HealthInsuranceAdmin2.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_0095E</td>\n",
       "      <td>DP03_0096E</td>\n",
       "      <td>DP03_0096PE</td>\n",
       "      <td>DP03_0097E</td>\n",
       "      <td>DP03_0097PE</td>\n",
       "      <td>DP03_0098E</td>\n",
       "      <td>DP03_0098PE</td>\n",
       "      <td>DP03_0099E</td>\n",
       "      <td>DP03_0099PE</td>\n",
       "      <td>state</td>\n",
       "      <td>zip code tabulation area</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2271</td>\n",
       "      <td>2137</td>\n",
       "      <td>94.1</td>\n",
       "      <td>1560</td>\n",
       "      <td>68.7</td>\n",
       "      <td>902</td>\n",
       "      <td>39.7</td>\n",
       "      <td>134</td>\n",
       "      <td>5.9</td>\n",
       "      <td>51</td>\n",
       "      <td>23833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1268</td>\n",
       "      <td>1151</td>\n",
       "      <td>90.8</td>\n",
       "      <td>889</td>\n",
       "      <td>70.1</td>\n",
       "      <td>386</td>\n",
       "      <td>30.4</td>\n",
       "      <td>117</td>\n",
       "      <td>9.2</td>\n",
       "      <td>51</td>\n",
       "      <td>23850</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>13132</td>\n",
       "      <td>12151</td>\n",
       "      <td>92.5</td>\n",
       "      <td>9045</td>\n",
       "      <td>68.9</td>\n",
       "      <td>4918</td>\n",
       "      <td>37.5</td>\n",
       "      <td>981</td>\n",
       "      <td>7.5</td>\n",
       "      <td>51</td>\n",
       "      <td>23851</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3584</td>\n",
       "      <td>3312</td>\n",
       "      <td>92.4</td>\n",
       "      <td>2507</td>\n",
       "      <td>69.9</td>\n",
       "      <td>1535</td>\n",
       "      <td>42.8</td>\n",
       "      <td>272</td>\n",
       "      <td>7.6</td>\n",
       "      <td>51</td>\n",
       "      <td>23890</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           0           1            2           3            4           5   \\\n",
       "0  DP03_0095E  DP03_0096E  DP03_0096PE  DP03_0097E  DP03_0097PE  DP03_0098E   \n",
       "1        2271        2137         94.1        1560         68.7         902   \n",
       "2        1268        1151         90.8         889         70.1         386   \n",
       "3       13132       12151         92.5        9045         68.9        4918   \n",
       "4        3584        3312         92.4        2507         69.9        1535   \n",
       "\n",
       "            6           7            8      9                         10  \n",
       "0  DP03_0098PE  DP03_0099E  DP03_0099PE  state  zip code tabulation area  \n",
       "1         39.7         134          5.9     51                     23833  \n",
       "2         30.4         117          9.2     51                     23850  \n",
       "3         37.5         981          7.5     51                     23851  \n",
       "4         42.8         272          7.6     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>civilianNoninstitutionalizedPopulation</th>\n",
       "      <th>withHealthInsuranceCoverage</th>\n",
       "      <th>withHealthInsuranceCoveragePct</th>\n",
       "      <th>withPrivateHealthInsurance</th>\n",
       "      <th>withPrivateHealthInsurancePct</th>\n",
       "      <th>withPublicCoverage</th>\n",
       "      <th>withPublicCoveragePct</th>\n",
       "      <th>noHealthInsuranceCoverage</th>\n",
       "      <th>noHealthInsuranceCoveragePct</th>\n",
       "      <th>stateFips</th>\n",
       "      <th>postalCode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2271</td>\n",
       "      <td>2137</td>\n",
       "      <td>94.1</td>\n",
       "      <td>1560</td>\n",
       "      <td>68.7</td>\n",
       "      <td>902</td>\n",
       "      <td>39.7</td>\n",
       "      <td>134</td>\n",
       "      <td>5.9</td>\n",
       "      <td>51</td>\n",
       "      <td>23833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1268</td>\n",
       "      <td>1151</td>\n",
       "      <td>90.8</td>\n",
       "      <td>889</td>\n",
       "      <td>70.1</td>\n",
       "      <td>386</td>\n",
       "      <td>30.4</td>\n",
       "      <td>117</td>\n",
       "      <td>9.2</td>\n",
       "      <td>51</td>\n",
       "      <td>23850</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>13132</td>\n",
       "      <td>12151</td>\n",
       "      <td>92.5</td>\n",
       "      <td>9045</td>\n",
       "      <td>68.9</td>\n",
       "      <td>4918</td>\n",
       "      <td>37.5</td>\n",
       "      <td>981</td>\n",
       "      <td>7.5</td>\n",
       "      <td>51</td>\n",
       "      <td>23851</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3584</td>\n",
       "      <td>3312</td>\n",
       "      <td>92.4</td>\n",
       "      <td>2507</td>\n",
       "      <td>69.9</td>\n",
       "      <td>1535</td>\n",
       "      <td>42.8</td>\n",
       "      <td>272</td>\n",
       "      <td>7.6</td>\n",
       "      <td>51</td>\n",
       "      <td>23890</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>268</td>\n",
       "      <td>196</td>\n",
       "      <td>73.1</td>\n",
       "      <td>81</td>\n",
       "      <td>30.2</td>\n",
       "      <td>115</td>\n",
       "      <td>42.9</td>\n",
       "      <td>72</td>\n",
       "      <td>26.9</td>\n",
       "      <td>51</td>\n",
       "      <td>23302</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  civilianNoninstitutionalizedPopulation withHealthInsuranceCoverage  \\\n",
       "1                                   2271                        2137   \n",
       "2                                   1268                        1151   \n",
       "3                                  13132                       12151   \n",
       "4                                   3584                        3312   \n",
       "5                                    268                         196   \n",
       "\n",
       "  withHealthInsuranceCoveragePct withPrivateHealthInsurance  \\\n",
       "1                           94.1                       1560   \n",
       "2                           90.8                        889   \n",
       "3                           92.5                       9045   \n",
       "4                           92.4                       2507   \n",
       "5                           73.1                         81   \n",
       "\n",
       "  withPrivateHealthInsurancePct withPublicCoverage withPublicCoveragePct  \\\n",
       "1                          68.7                902                  39.7   \n",
       "2                          70.1                386                  30.4   \n",
       "3                          68.9               4918                  37.5   \n",
       "4                          69.9               1535                  42.8   \n",
       "5                          30.2                115                  42.9   \n",
       "\n",
       "  noHealthInsuranceCoverage noHealthInsuranceCoveragePct stateFips postalCode  \n",
       "1                       134                          5.9        51      23833  \n",
       "2                       117                          9.2        51      23850  \n",
       "3                       981                          7.5        51      23851  \n",
       "4                       272                          7.6        51      23890  \n",
       "5                        72                         26.9        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>civilianNoninstitutionalizedPopulation</th>\n",
       "      <th>withHealthInsuranceCoverage</th>\n",
       "      <th>withHealthInsuranceCoveragePct</th>\n",
       "      <th>withPrivateHealthInsurance</th>\n",
       "      <th>withPrivateHealthInsurancePct</th>\n",
       "      <th>withPublicCoverage</th>\n",
       "      <th>withPublicCoveragePct</th>\n",
       "      <th>noHealthInsuranceCoverage</th>\n",
       "      <th>noHealthInsuranceCoveragePct</th>\n",
       "      <th>stateFips</th>\n",
       "      <th>postalCode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>24526</th>\n",
       "      <td>19866</td>\n",
       "      <td>18989</td>\n",
       "      <td>95.6</td>\n",
       "      <td>16129</td>\n",
       "      <td>81.2</td>\n",
       "      <td>6086</td>\n",
       "      <td>30.6</td>\n",
       "      <td>877</td>\n",
       "      <td>4.4</td>\n",
       "      <td>06</td>\n",
       "      <td>90210</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      civilianNoninstitutionalizedPopulation withHealthInsuranceCoverage  \\\n",
       "24526                                  19866                       18989   \n",
       "\n",
       "      withHealthInsuranceCoveragePct withPrivateHealthInsurance  \\\n",
       "24526                           95.6                      16129   \n",
       "\n",
       "      withPrivateHealthInsurancePct withPublicCoverage withPublicCoveragePct  \\\n",
       "24526                          81.2               6086                  30.6   \n",
       "\n",
       "      noHealthInsuranceCoverage noHealthInsuranceCoveragePct stateFips  \\\n",
       "24526                       877                          4.4        06   \n",
       "\n",
       "      postalCode  \n",
       "24526      90210  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Example data\n",
    "df.query(\"postalCode == '90210'\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['source'] = 'American Community Survey 5 year'\n",
    "df['aggregationLevel'] = 'PostalCode'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Save data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv(NEO4J_IMPORT / \"03a-USCensusDP03HealthInsuranceZip.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>civilianNoninstitutionalizedPopulation</th>\n",
       "      <th>withHealthInsuranceCoverage</th>\n",
       "      <th>withHealthInsuranceCoveragePct</th>\n",
       "      <th>withPrivateHealthInsurance</th>\n",
       "      <th>withPrivateHealthInsurancePct</th>\n",
       "      <th>withPublicCoverage</th>\n",
       "      <th>withPublicCoveragePct</th>\n",
       "      <th>noHealthInsuranceCoverage</th>\n",
       "      <th>noHealthInsuranceCoveragePct</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>2049</td>\n",
       "      <td>1928</td>\n",
       "      <td>94.1</td>\n",
       "      <td>1801</td>\n",
       "      <td>87.9</td>\n",
       "      <td>173</td>\n",
       "      <td>8.4</td>\n",
       "      <td>121</td>\n",
       "      <td>5.9</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>7016</td>\n",
       "      <td>6782</td>\n",
       "      <td>96.7</td>\n",
       "      <td>5473</td>\n",
       "      <td>78.0</td>\n",
       "      <td>2182</td>\n",
       "      <td>31.1</td>\n",
       "      <td>234</td>\n",
       "      <td>3.3</td>\n",
       "      <td>06</td>\n",
       "      <td>073</td>\n",
       "      <td>06073008347</td>\n",
       "      <td>American Community Survey 5 year</td>\n",
       "      <td>Tract</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9756</td>\n",
       "      <td>9535</td>\n",
       "      <td>97.7</td>\n",
       "      <td>8266</td>\n",
       "      <td>84.7</td>\n",
       "      <td>2122</td>\n",
       "      <td>21.8</td>\n",
       "      <td>221</td>\n",
       "      <td>2.3</td>\n",
       "      <td>06</td>\n",
       "      <td>073</td>\n",
       "      <td>06073008354</td>\n",
       "      <td>American Community Survey 5 year</td>\n",
       "      <td>Tract</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>6576</td>\n",
       "      <td>5673</td>\n",
       "      <td>86.3</td>\n",
       "      <td>4454</td>\n",
       "      <td>67.7</td>\n",
       "      <td>1728</td>\n",
       "      <td>26.3</td>\n",
       "      <td>903</td>\n",
       "      <td>13.7</td>\n",
       "      <td>06</td>\n",
       "      <td>073</td>\n",
       "      <td>06073008505</td>\n",
       "      <td>American Community Survey 5 year</td>\n",
       "      <td>Tract</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>7371</td>\n",
       "      <td>6869</td>\n",
       "      <td>93.2</td>\n",
       "      <td>5512</td>\n",
       "      <td>74.8</td>\n",
       "      <td>2305</td>\n",
       "      <td>31.3</td>\n",
       "      <td>502</td>\n",
       "      <td>6.8</td>\n",
       "      <td>06</td>\n",
       "      <td>073</td>\n",
       "      <td>06073017604</td>\n",
       "      <td>American Community Survey 5 year</td>\n",
       "      <td>Tract</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   civilianNoninstitutionalizedPopulation withHealthInsuranceCoverage  \\\n",
       "6                                    2049                        1928   \n",
       "7                                    7016                        6782   \n",
       "8                                    9756                        9535   \n",
       "9                                    6576                        5673   \n",
       "10                                   7371                        6869   \n",
       "\n",
       "   withHealthInsuranceCoveragePct withPrivateHealthInsurance  \\\n",
       "6                            94.1                       1801   \n",
       "7                            96.7                       5473   \n",
       "8                            97.7                       8266   \n",
       "9                            86.3                       4454   \n",
       "10                           93.2                       5512   \n",
       "\n",
       "   withPrivateHealthInsurancePct withPublicCoverage withPublicCoveragePct  \\\n",
       "6                           87.9                173                   8.4   \n",
       "7                           78.0               2182                  31.1   \n",
       "8                           84.7               2122                  21.8   \n",
       "9                           67.7               1728                  26.3   \n",
       "10                          74.8               2305                  31.3   \n",
       "\n",
       "   noHealthInsuranceCoverage noHealthInsuranceCoveragePct stateFips  \\\n",
       "6                        121                          5.9        06   \n",
       "7                        234                          3.3        06   \n",
       "8                        221                          2.3        06   \n",
       "9                        903                         13.7        06   \n",
       "10                       502                          6.8        06   \n",
       "\n",
       "   countyFips        tract                            source aggregationLevel  \n",
       "6         073  06073008339  American Community Survey 5 year            Tract  \n",
       "7         073  06073008347  American Community Survey 5 year            Tract  \n",
       "8         073  06073008354  American Community Survey 5 year            Tract  \n",
       "9         073  06073008505  American Community Survey 5 year            Tract  \n",
       "10        073  06073017604  American Community Survey 5 year            Tract  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Example data for San Diego County\n",
    "df[(df['stateFips'] == '06') & (df['countyFips'] == '073')].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Save data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv(NEO4J_IMPORT / \"03a-USCensusDP03HealthInsuranceTract.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
}