notebooks/queries/Housing.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Example queries for Housing Characteristics on COVID-19 Knowledge Graph\n",
    "[Work in progress]\n",
    "\n",
    "This notebook demonstrates how to run Cypher queries to get [Housing Characteristics from the American Community Survey 2018 5-year data](https://data.census.gov/cedsci/table?tid=ACSDP5Y2018.DP04) at multiple levels of geographic granularity. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "from py2neo import Graph"
   ]
  },
  {
   "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": "markdown",
   "metadata": {},
   "source": [
    "#### Connect to COVID-19-Net Knowledge Graph"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "graph = Graph(\"bolt://132.249.238.185:7687\", user=\"reader\", password=\"demo\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Housing Characteristics\n",
    "The following variables are available for queries. Variable names ending with Pct represent values in percent, whereas all other variables represent counts. For details see [Subject Definitions](https://www2.census.gov/programs-surveys/acs/tech_docs/subject_definitions/2018_ACSSubjectDefinitions.pdf)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "             # ROOMS\n",
    "             'DP04_0037E': 'medianRoomsInHousingUnit',\n",
    "             \n",
    "             # HOUSING TENURE\n",
    "             'DP04_0046E': 'ownerOccupiedHousingUnits',\n",
    "             'DP04_0046PE': 'ownerOccupiedHousingUnitsPct',\n",
    "             'DP04_0047E': 'renterOccupiedHousingUnits',\n",
    "             'DP04_0047PE': 'renterOccupiedHousingUnitsPct',\n",
    "             'DP04_0048E': 'averageHouseholdSizeOfOwnerOccupiedUnit',\n",
    "             'DP04_0049E': 'averageHouseholdSizeOfRenterOccupiedUnit',\n",
    "    \n",
    "             # VEHICLES AVAILABLE\n",
    "             'DP04_0057E': 'occupiedHousingUnitsWithVehicles',\n",
    "             'DP04_0058E': 'occupiedHousingUnitsNoVehicles',\n",
    "             'DP04_0058PE': 'occupiedHousingUnitsNoVehiclesPct',\n",
    "    \n",
    "             # OCCUPANTS PER ROOM\n",
    "             Note, the following variables contain special characters and need to be \n",
    "             quoted by backticks in Cypher queries (see examples below)\n",
    "             'DP04_0077E': 'occupantsPerRoom1.00orLess',\n",
    "             'DP04_0077PE': 'occupantsPerRoom1.00orLessPct',\n",
    "             'DP04_0078E': 'occupantsPerRoom1.01to1.50',\n",
    "             'DP04_0078PE': 'occupantsPerRoom1.01to1.50Pct',\n",
    "             'DP04_0079E': 'occupantsPerRoom1.51orMore',\n",
    "             'DP04_0079PE': 'occupantsPerRoom1.51orMorePct'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Geographic granularity\n",
    "Data are available at 3 levels of granularity:\n",
    "* US County\n",
    "* US Zip Code\n",
    "* US Census Tract"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Query Examples"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get Data By US County"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If the state and county fips codes are available, housing characteristics can be retrieved directly.\n",
    "\n",
    "Note, fips codes are represented as strings."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Example: Average household size"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "state_fips = '06'\n",
    "county_fips = '073'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>h.stateFips</th>\n",
       "      <th>h.countyFips</th>\n",
       "      <th>h.averageHouseholdSizeOfOwnerOccupiedUnit</th>\n",
       "      <th>h.averageHouseholdSizeOfRenterOccupiedUnit</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>06</td>\n",
       "      <td>073</td>\n",
       "      <td>2.9</td>\n",
       "      <td>2.83</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  h.stateFips h.countyFips  h.averageHouseholdSizeOfOwnerOccupiedUnit  \\\n",
       "0          06          073                                        2.9   \n",
       "\n",
       "   h.averageHouseholdSizeOfRenterOccupiedUnit  \n",
       "0                                        2.83  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "MATCH (h:Housing{countyFips:$county_fips, stateFips:$state_fips})\n",
    "RETURN h.stateFips, h.countyFips, \n",
    "       h.averageHouseholdSizeOfOwnerOccupiedUnit, h.averageHouseholdSizeOfRenterOccupiedUnit\n",
    "       \n",
    "\"\"\"\n",
    "df = graph.run(query, county_fips=county_fips, state_fips=state_fips).to_data_frame()\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Example: Get the same data by traversing the KG"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "county = 'Los Angeles County'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>a.name</th>\n",
       "      <th>h.stateFips</th>\n",
       "      <th>h.countyFips</th>\n",
       "      <th>h.averageHouseholdSizeOfOwnerOccupiedUnit</th>\n",
       "      <th>h.averageHouseholdSizeOfRenterOccupiedUnit</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Los Angeles County</td>\n",
       "      <td>06</td>\n",
       "      <td>037</td>\n",
       "      <td>3.19</td>\n",
       "      <td>2.84</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               a.name h.stateFips h.countyFips  \\\n",
       "0  Los Angeles County          06          037   \n",
       "\n",
       "   h.averageHouseholdSizeOfOwnerOccupiedUnit  \\\n",
       "0                                       3.19   \n",
       "\n",
       "   h.averageHouseholdSizeOfRenterOccupiedUnit  \n",
       "0                                        2.84  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "MATCH (a:Admin2{name:$admin2})-[:HAS_HOUSING]-(h:Housing)\n",
    "RETURN a.name, h.stateFips, h.countyFips, \n",
    "       h.averageHouseholdSizeOfOwnerOccupiedUnit, h.averageHouseholdSizeOfRenterOccupiedUnit\n",
    "\"\"\"\n",
    "df = graph.run(query, admin2=county).to_data_frame()\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get Data by US Postal Code\n",
    "\n",
    "Note, postal codes are represented as strings."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "zip_code = '92130'"
   ]
  },
  {
   "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>h.postalCode</th>\n",
       "      <th>h.ownerOccupiedHousingUnitsPct</th>\n",
       "      <th>h.renterOccupiedHousingUnitsPct</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>92130</td>\n",
       "      <td>66.5</td>\n",
       "      <td>33.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  h.postalCode  h.ownerOccupiedHousingUnitsPct  \\\n",
       "0        92130                            66.5   \n",
       "\n",
       "   h.renterOccupiedHousingUnitsPct  \n",
       "0                             33.5  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "MATCH (h:Housing{postalCode: $zip_code})\n",
    "RETURN h.postalCode, h.ownerOccupiedHousingUnitsPct, h.renterOccupiedHousingUnitsPct\n",
    "\"\"\"\n",
    "df = graph.run(query, zip_code=zip_code).to_data_frame()\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Example: List occupands per room for Zip codes with a place name\n",
    "\n",
    "Note, Zip code areas may cross city boundaries. Place names are the preferred names used by the US Postal Service."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>Zip code</th>\n",
       "      <th>1.00 or less</th>\n",
       "      <th>1.01 to 1.50</th>\n",
       "      <th>1.51 or more</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>91910</td>\n",
       "      <td>88.9</td>\n",
       "      <td>7.6</td>\n",
       "      <td>3.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>91911</td>\n",
       "      <td>85.8</td>\n",
       "      <td>8.8</td>\n",
       "      <td>5.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>91913</td>\n",
       "      <td>93.9</td>\n",
       "      <td>4.7</td>\n",
       "      <td>1.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>91914</td>\n",
       "      <td>98.5</td>\n",
       "      <td>1.2</td>\n",
       "      <td>0.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>91915</td>\n",
       "      <td>96.5</td>\n",
       "      <td>2.9</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Zip code  1.00 or less  1.01 to 1.50  1.51 or more\n",
       "0    91910          88.9           7.6           3.5\n",
       "1    91911          85.8           8.8           5.3\n",
       "2    91913          93.9           4.7           1.4\n",
       "3    91914          98.5           1.2           0.3\n",
       "4    91915          96.5           2.9           0.6"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "place_name = 'Chula Vista'\n",
    "\n",
    "query = \"\"\"\n",
    "MATCH (p:PostalCode{placeName:$place_name})-[:HAS_HOUSING]-(h:Housing)\n",
    "RETURN p.name AS `Zip code`, \n",
    "       h.`occupantsPerRoom1.00orLessPct` AS `1.00 or less`,\n",
    "       h.`occupantsPerRoom1.01to1.50Pct` AS  `1.01 to 1.50`,\n",
    "       h.`occupantsPerRoom1.51orMorePct` AS `1.51 or more`\n",
    "\"\"\"\n",
    "df = graph.run(query, place_name=place_name).to_data_frame()\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAXcAAAEWCAYAAACdaNcBAAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjMuMSwgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy/d3fzzAAAACXBIWXMAAAsTAAALEwEAmpwYAAAfzUlEQVR4nO3deZgU1d328e/NooACDpuCoCNuIQqiQaMxT8SFuEXQCHGXRKOvT4wGlyjGBZdoxFfzmMsYjU8UETVg0AREoyCKoHGBEcIi8RUVEEEZCeAWkIHf+0cV0I4zMDM9Mz0U9+e6+ura69SZnrtPV3WfUkRgZmbZ0qjQBTAzs9rncDczyyCHu5lZBjnczcwyyOFuZpZBDnczswxyuJuZZZDD3apN0o8lzZL0haQPJd0jaYdCl6smJBVLCklNCl0Ws9rkcLdqkXQZMBT4JdAaOBjYFZggaZtClm1LVpU3F78BWXU43K3KJLUCbgAuiohnImJNRMwHfkQS8GemyzWW9CtJ70j6VFKJpC7pvH0kTZD0b0kfSfpVOv1BSb/O2VdvSYtyxudLukrSm5KWSxomqVk6r0jSOEml6bxxkjrnrDtJ0k2SXk7LM15Su3T25PR5haTPJB0iaQ9JL0paKeljSaMqqY/1rf7zJS2WtCR981s/v5GkwWk9LJP0mKQ25dY9V9JC4PkKtt9b0iJJV0r6EBgmaVtJd6b7W5wOb5uzznmS5qX1O1ZSp5x5Ielnkt5O6+EmSbtLekXSJ2n5/AadEQ53q47vAM2AJ3InRsRnwN+BPumkS4HTgOOAVsA5wBeSWgLPAc8AnYA9gInV2P8ZwNHA7sBewDXp9EbAMJI3mF2A/wC/L7fu6cBPgA7ANsDl6fTvpc87RMT2EfEKcBMwHigCOgN3baZchwN7At8HBks6Kp1+MXAicBjJ8S4H7i637mFAt/S4KrIT0CY9tvOBq0k+LfUE9gMOIq0HSUcAvyF5s+0ILABGltveMcC30m1cAdxHUq9dgH1J/m6WBRHhhx9VepC0zD+sZN6twIR0+C2gXwXLnAZMr2T9B4Ff54z3BhbljM8HLsgZPw54p5Jt9QSW54xPAq7JGf8Z8Ew6XAwE0CRn/kMkodd5M/Wxft1v5Ey7Dbg/HZ4LHJkzryOwBmiSs27XTWy/N/Al0Cxn2jvAcTnjRwPz0+H7gdty5m2f7q84HQ/g0Jz5JcCVOeN3AHcW+nXmR+083HK36vgYaFfJud+O6XxIWoHvVLBMZdOr6v2c4QUkrWEktZD0R0kLJH1CcqplB0mNc5b/MGf4C5Lgq8wVgIDXJc2RdE5NykXS2v6rpBWSVpCE/Vpgx0rWrUhpRKzKGe+U7qOi/X1lXiSfqJYBO+cs/1HO8H8qGN9UvdgWxOFu1fEKsBr4Ye5ESdsBx7LxFMv7JKdOyqtsOsDnQIuc8Z0qWKZLzvAuwOJ0+DJgb+DbEdGKjadaVMm+cn2tW9SI+DAizouITsD/Af4gaY9NbKOycr0PHBsRO+Q8mkXEB5va/2bKt5jkTaOi/X1lXvp3aQvk7s+2Eg53q7KIWElyQfUuScdIaiqpGPgLsAgYkS76J+AmSXsq0UNSW2AcsJOkQemFwZaSvp2uMwM4TlIbSTsBgyoowoWSOqcXJX8FrL/Q2ZKk1bkinTekGodVCqwDuq6fIGlAzgXZ5SQBu3YT27g2/fSwD8l5/fXluhe4WdKu6XbbS+pXjbJV5M/ANem22gHXAQ+n8x4FfiKpZ3qR9RbgtUguettWxuFu1RIRt5EE6+3AJ8BrJC3UIyNidbrYb4HHSC5KfkJyLrh5RHxKctH1BJLTJG+TXIyE5I3hnyTn1sezMSBzPZrOezd9rP92zZ1Ac5LTQq+SXLCt6vF8AdwMvJyePjkYOBB4TdJnwFjgFxHx3iY28yIwj+STy+0RMT6d/rt0/fGSPk3L9u2KN1FlvwamATOBWcAb6TQiYiJwLfA4sITkU9Kpee7PtlCK8M06rOGTNB/4aUQ8V+iyrJd+ankPaBoRZQUujtlXuOVuZpZBDnczswzyaRkzswxyy93MLIMaREdE7dq1i+Li4kIXw8xsi1JSUvJxRLSvaF6DCPfi4mKmTZtW6GKYmW1RJC2obJ5Py5iZZZDD3cwsgxzuZmYZ5HA3M8sgh7uZWQY53M3MMmiz4S7pAUlLJc3OmdZGyX0w306fi3LmXZXew/EtSZXdOszMzOpQVVruD5LcdzHXYGBiROxJ0s3pYABJ3yTpYnSfdJ0/lLsbjpmZ1YPNhntETAb+XW5yP2B4Ojyc5CbA66ePjIjVaf/X80hu4GtmZvWopr9Q3TEilgBExBJJHdLpO5PckGC9RXz1/o0bSDqf5G7u7LLLLjUsxkbdh3fPexu1YdbAWYUugusiR0Ooi4ZQDwBc37rQJYDrVxa6BMDW8bqo7e4HKrpnZYXdTkbEfSR3mKdXr17umtLM6s2s9xYWugh1rqbflvlIUkeA9HlpOn0RX71ZcGc23rzXzMzqSU1b7mOBgcCt6fOYnOmPSvot0AnYE3g930Ka1dTW0EKrquJVjxa6CMwvdAG2IpsNd0l/BnoD7SQtIrmz/K3AY5LOBRYCAwAiYo6kx4A3gTLgwojY1F3jzcysDmw23CPitEpmHVnJ8jeT3E3ezMwKxL9QNTPLoAZxs47a4HOrZlZVW8P1B7fczcwyyOFuZpZBDnczswzKzDl328jXH8zM4W6ZtjVcODOriE/LmJllkMPdzCyDHO5mZhnkcDczyyCHu5lZBjnczcwyyOFuZpZBDnczswxyuJuZZZDD3cwsgzLT/UBD+Jk5+KfmZtYwuOVuZpZBDnczswxyuJuZZZDD3cwsgxzuZmYZlJlvy9hG/uaQmbnlbmaWQQ53M7MMcribmWWQw93MLIMc7mZmGeRwNzPLIIe7mVkGOdzNzDIor3CXdImkOZJmS/qzpGaS2kiaIOnt9LmotgprZmZVU+Nwl7QzcDHQKyL2BRoDpwKDgYkRsScwMR03M7N6lO9pmSZAc0lNgBbAYqAfMDydPxw4Mc99mJlZNdU43CPiA+B2YCGwBFgZEeOBHSNiSbrMEqBDRetLOl/SNEnTSktLa1oMMzOrQD6nZYpIWum7AZ2A7SSdWdX1I+K+iOgVEb3at29f02KYmVkF8jktcxTwXkSURsQa4AngO8BHkjoCpM9L8y+mmZlVRz7hvhA4WFILSQKOBOYCY4GB6TIDgTH5FdHMzKqrxv25R8RrkkYDbwBlwHTgPmB74DFJ55K8AQyojYKamVnV5XWzjogYAgwpN3k1SSvezMwKxL9QNTPLIIe7mVkGOdzNzDLI4W5mlkEOdzOzDHK4m5llkMPdzCyDHO5mZhnkcDczyyCHu5lZBjnczcwyyOFuZpZBDnczswxyuJuZZZDD3cwsgxzuZmYZ5HA3M8sgh7uZWQY53M3MMsjhbmaWQQ53M7MMcribmWWQw93MLIMc7mZmGeRwNzPLIIe7mVkGOdzNzDLI4W5mlkEOdzOzDHK4m5llkMPdzCyDHO5mZhmUV7hL2kHSaEn/kjRX0iGS2kiaIOnt9LmotgprZmZVk2/L/XfAMxHxDWA/YC4wGJgYEXsCE9NxMzOrRzUOd0mtgO8B9wNExJcRsQLoBwxPFxsOnJhfEc3MrLryabl3BUqBYZKmS/qTpO2AHSNiCUD63KEWymlmZtWQT7g3AQ4A7omI/YHPqcYpGEnnS5omaVppaWkexTAzs/LyCfdFwKKIeC0dH00S9h9J6giQPi+taOWIuC8iekVEr/bt2+dRDDMzK6/G4R4RHwLvS9o7nXQk8CYwFhiYThsIjMmrhGZmVm1N8lz/IuARSdsA7wI/IXnDeEzSucBCYECe+zAzs2rKK9wjYgbQq4JZR+azXTMzy49/oWpmlkEOdzOzDHK4m5llkMPdzCyDHO5mZhnkcDczyyCHu5lZBjnczcwyyOFuZpZBDnczswxyuJuZZZDD3cwsgxzuZmYZ5HA3M8sgh7uZWQY53M3MMsjhbmaWQQ53M7MMcribmWWQw93MLIMc7mZmGeRwNzPLIIe7mVkGOdzNzDLI4W5mlkEOdzOzDHK4m5llkMPdzCyDHO5mZhnkcDczyyCHu5lZBjnczcwyyOFuZpZBeYe7pMaSpksal463kTRB0tvpc1H+xTQzs+qojZb7L4C5OeODgYkRsScwMR03M7N6lFe4S+oMHA/8KWdyP2B4OjwcODGffZiZWfXl23K/E7gCWJczbceIWAKQPneoaEVJ50uaJmlaaWlpnsUwM7NcNQ53ST8AlkZESU3Wj4j7IqJXRPRq3759TYthZmYVaJLHuocCfSUdBzQDWkl6GPhIUseIWCKpI7C0NgpqZmZVV+OWe0RcFRGdI6IYOBV4PiLOBMYCA9PFBgJj8i6lmZlVS118z/1WoI+kt4E+6biZmdWjfE7LbBARk4BJ6fAy4Mja2K6ZmdWMf6FqZpZBDnczswxyuJuZZZDD3cwsgxzuZmYZ5HA3M8sgh7uZWQY53M3MMsjhbmaWQQ53M7MMcribmWWQw93MLIMc7mZmGeRwNzPLIIe7mVkGOdzNzDLI4W5mlkEOdzOzDHK4m5llkMPdzCyDHO5mZhnkcDczyyCHu5lZBjnczcwyqEmhC2DZtWbNGhYtWsSqVasKVob/7duxYPteb+7cuYUuAs2aNaPVto34ZPW6QhfF6onD3erMokWLaNmyJcXFxUgqSBnWLFpRkP3m6tZ5h4LuPyJYtmwZF327iJsnLytoWaz++LSM1ZlVq1bRtm3bggW7JSTRtm1bdt2haaGLYvXI4W51ysHeMEhC+G+xNXG4m5llkM+5W70pHvxUrW5v/q3Hb3aZ6y77OZMnPkubtu14YuIrFS4TEQwdMpiXnp9As+bNuem3f6Bb9/0AePmF5xh6/VWsW7uWk047i3MvvKRWyj5p0iRuv/12xo0bVyvbMyvPLXfLtH4DTuOeEaM3ucxLL0xg4Xvv8OSUEq4beie//tVlAKxdu5Zbrvklf3joL/z1+Vd5ZszjvPP//lWjcpSVldVoPbOacrhbpn3r4ENptUPRJpd5YfzTnHDyqUiixwEH8uknKyn96ENmzyihS3FXOu9aTNNttuGYvj9k0vinv7b+4kULOe/UfvTvcyjnndqPJR+8D8C1l/yM/3vD1Rx++OFceeWVle7/888/55xzzuHAAw9k//33Z8yYMQDMmTOHgw46iJ49e9KjRw/efvttPv/8c44//nj2228/9t13X0aNGpVH7ViW1fi0jKQuwEPATsA64L6I+J2kNsAooBiYD/woIpbnX1SzurH0wyXs2GnnDeM7duzE0g+XsPTDJeyUM71Dx07Mml7ytfV/c+0VnHDyqfQdcBp/HfkwQ68bzJ33PwLAgvfm8cJzz9G4ceNK93/zzTdzxBFH8MADD7BixQoOOuggjjrqKO69915+8YtfcMYZZ/Dll1+ydu1ann76aTp16sRTTyWnuFauXFlb1WAZk0/LvQy4LCK6AQcDF0r6JjAYmBgRewIT03Gzhivia5MkEZVML29myVSOPbE/AD84+RSmT311w7zvH3/iJoMdYPz48dx666307NmT3r17s2rVKhYuXMghhxzCLbfcwtChQ1mwYAHNmzene/fuPPfcc1x55ZVMmTKF1q1bV/dobStR43CPiCUR8UY6/CkwF9gZ6AcMTxcbDpyYZxnN6lSHjp34aPEHG8Y/WrKY9jvuxI4dO/FhzvSlSxbTYcedNru93DeA5i1abHb5iODxxx9nxowZzJgxg4ULF9KtWzdOP/10xo4dS/PmzTn66KN5/vnn2WuvvSgpKaF79+5cddVV3HjjjdU8Wtta1Mo5d0nFwP7Aa8COEbEEkjcAoEMl65wvaZqkaaWlpbVRDLMa6d3nWJ58fCQRwcw3prJ9y1a033En9tnvABbOf4dFCxew5ssveWbsExzW59ivrb/ftw7imbGPA/D0X/9CzwMPrtb+jz76aO66664NnxSmT58OwLvvvkvXrl25+OKL6du3LzNnzmTx4sW0aNGCM888k8svv5w33ngjz6O3rMr7q5CStgceBwZFxCdV/dFKRNwH3AfQq1evr3/+tcypylcXa9uVF57LtFdfZsW/l9HnwH3478sG88NTz+KxEQ8A8KOzzuG/jvg+Lz0/gR989wCaNW/OjXfcDUCTJk246qbb+O8zT2bd2rWceMoZ7LF3t6/v48ahDLn85wy/9y6K2rbjxjt+X60yXnvttQwaNIgePXoQERQXFzNu3DhGjRrFww8/TNOmTdlpp5247rrrmDp1Kr/85S9p1KgRTZs25Z577sm/kiyTVNF5xSqvLDUFxgHPRsRv02lvAb0jYomkjsCkiNh7U9vp1atXTJs2rcblgNr/DnVNFSLAymsodfH3gV3p1u3rYVifZjaAvmV6FLhvmfUm/OMNzhu7pKBlaAj/H9Aw/kdqoy4klUREr4rm1fi0jJIm+v3A3PXBnhoLDEyHBwJjaroPMzOrmXxOyxwKnAXMkjQjnfYr4FbgMUnnAguBAXmV0MzMqq3G4R4RL0GlPREdWdPtmplZ/vwLVTOzDHK4m5llkMPdzCyD3OWv1Z/ra/mn8tdvvl+VfLv8rcr6zz/zFLt23Z3d9/pGlYs+efJkBg0axMyZMxk5ciT9+/evcLnevXuzZMkSmjdvDiRdFXTo0IHVq1dz9tlnU1JSQtu2bRk1ahTFxcVV3r9ln1vulmn5dPlb1fVfePYp3n37rWqVa5ddduHBBx/k9NNP3+yyjzzyyIauCTp0SH7wff/991NUVMS8efO45JJLNtnrpG2dHO6Wafl0+VuV9WdMe41JE/7Ob2++jh8d/V+8P/89/jVnFmf27UP/Pocy6Kdnsnz51ztFLS4upkePHjRqVLN/wTFjxjBwYPJzkv79+zNx4sQKOzqzrZfD3bZ6lXX5WxU9e32b3n2O5dKrb+SxZ6fQpXg3rhl0AYOuup7RE15mz298kxtuuCGv8v3kJz+hZ8+e3HTTTRsC/IMPPqBLly5A0k1C69atWbZsWV77sWxxuJtVsWvfqvj0k5V8+slKeh1yKAB9+5/G5MmTa1y0Rx55hFmzZjFlyhSmTJnCiBEj0iLXXpktmxzuttWrrMvfhmDnnZNPFC1btuT000/n9ddfB6Bz5868/35yx6eysjJWrlxJmzZtClZOa3gc7rbVq6zL36pqsf32fP7ZZwC0bNWaVq134I3X/gHAuCdGcdhhh9WoXGVlZXz88ccArFmzhnHjxrHvvvsC0LdvX4YPT26bMHr0aI444gi33O0r/FVIqz9V+Opibcuny99NrZ/rmL4/5MYrB/HosD9yx73Duel/7uHXV13Kqv98Qeddinl85MNfK9fUqVM56aSTWL58OU8++SRDhgxhzpw5APTs2ZMZM2awevVqjj76aNasWcPatWs56qijOO+88wA499xzOeuss9hjjz1o06YNI0eOrKsqtC1UXl3+1hZ3+Vu7GkpduMvfhLv83agh/H9Aw/gfabBd/pqZWcPlcDczyyCHu5lZBjnczcwyyOFuZpZBDnczswzy99yt3nQf3r1Wtzdr4KzNLlOVLnunvvISg849nZ277ArAEceewAWDrqjy+mYNkVvulmlV6bIXYP+DDuGxZ6fw2LNTNgR7ddbfnLKysry3UYht25bLLXfLtG8dfCgfvL+wTtdfvGghQy6/iOXLPqaobTtuvOP3dNy5C9de8jNa7VDEonlvcsABB3DHHXdsWOfBBx/kb3/7G2vXrmX27NlcdtllfPnll4wYMYJtt92Wp59+mjZt2jBjxgwuuOACvvjiC3bffXceeOABioqK6N27N9/5znd4+eWX6du3L7179+bSSy/ls88+o127djz44IN07NixxsdtWz633M2AmSVTGfD97/Kzs/oz76251Vr3N9dewQknn8roCS9z3IkDGHrd4A3zFrw3j+eee+4rwb7e7NmzefTRR3n99de5+uqradGiBdOnT+eQQw7hoYceAuDss89m6NChzJw5k+7du3+l++AVK1bw4osvcvHFF3PRRRcxevRoSkpKOOecc7j66qtrWBOWFW6521av2749eObVmbTYbnumPD+eS356Jk9OKany+jNLpvLb+5KueH9w8incecuQDfO+f/yJNG7cuML1Dj/8cFq2bEnLli1p3bo1J5xwAgDdu3dn5syZrFy5khUrVmzoeGzgwIEMGDBgw/qnnHIKAG+99RazZ8+mT58+AKxdu9atdnO4m23fstWG4f864vvccvXlLP/3MoratK3R9nJ7Z2zeokWly2277bYbhhs1arRhvFGjRlU6j77ddtsBSd/u++yzD6+84gu+tpFPy9hW7+OlH224+cWs6SWsW7eOHYqq3jf6ft86iGfGPg7A03/9Cz0PPLhWytW6dWuKioqYMmUKACNGjKiw++C9996b0tLSDeG+Zs2aDT1M2tbLLXerN1X56mJtq0qXvxOeHsNjI4bRpHFjtm3WnKF337+h9V2VLn+vvHEoQy7/OcPvvWvDBdXaMnz48A0XVLt27cqwYcO+tsw222zD6NGjufjii1m5ciVlZWUMGjSIffbZp9bKYVsed/lbyxpCl6YNpS7c5W/CXf5u1BD+P6Bh/I+4y18zM6s2h7uZWQY53K1ONYTTfpb8HQL/LbYmDnerM82aNWPZsmUO+AKLCJYtW8aCFWsKXRSrR/62jNWZzp07s2jRIkpLSwtWho+W/6dg+15v7qfNC10EmjVrxl2vLS90MaweOdytzjRt2pTddtutoGU4NiPfiqgNn6x+s9BFsHpUZ6dlJB0j6S1J8yQN3vwaZmZWW+ok3CU1Bu4GjgW+CZwm6Zt1sS8zM/u6umq5HwTMi4h3I+JLYCTQr472ZWZm5dTJL1Ql9QeOiYifpuNnAd+OiJ/nLHM+cH46ujfwVq0XpPraAR8XuhANhOtiI9fFRq6LjRpCXewaEe0rmlFXF1RVwbSvvItExH3AfXW0/xqRNK2yn/JubVwXG7kuNnJdbNTQ66KuTsssArrkjHcGFtfRvszMrJy6CvepwJ6SdpO0DXAqMLaO9mVmZuXUyWmZiCiT9HPgWaAx8EBEbAkdTDeo00QF5rrYyHWxketiowZdFw2iy18zM6td7lvGzCyDHO5mZhmUuXCX9AtJsyXNkTQonTYgHV8nqVfOsttIGiZplqR/SuqdM+9mSe9L+qzc9reVNCrtVuE1ScX1c2TVVw918T1Jb0gqS3/b0GDVQ11ckC4/Q9JLDfkX2XVdFznz+0uK3O01NPXwuvixpNL0dTFD0k/r6dDSfp4z8gD2BWYDLUguFj8H7Al0I/mh1CSgV87yFwLD0uEOQAnQKB0/GOgIfFZuHz8D7k2HTwVGFfq4C1gXxUAP4CGgf6GPucB10SpnuC/wTKGPu1B1kc5rCUwGXs3dXkN61NPr4sfA7wtxfFlruXcDXo2ILyKiDHgROCki5kZERb+A/SYwESAilgIrgF7p+KsRUdENJ/sBw9Ph0cCRWn835YalzusiIuZHxExgXR0dQ22pj7r4JGd0O2iwd8aoj/8RgJuA24BVtVz+2lRfdVEQWQv32cD3JLWV1AI4jq/+mKq8fwL9JDWRtBvwrc0sD7Az8D4kX/kEVgJt8y557auPuthS1EtdSLpQ0jskoXZxLZS7LtR5XUjaH+gSEeNqq9B1pL7+R06WNFPSaEn19j+Vqf7cI2KupKHABOAzkj9G2SZWeYDk3XsasAD4x2aWhyp0rdAQ1FNdbBHqqy4i4m7gbkmnA9cAA/Mseq2r67qQ1Aj4H5LTEQ1aPb0ungT+HBGrJV1A8qn/iHzLXhVZa7kTEfdHxAER8T3g38Dbm1i2LCIuiYieEdEP2GFTy6c2dK0gqQnQOt1Pg1MPdbHFqOe6GAmcmE9561Id10VLknPZkyTNJzkXPbahXlSt69dFRCyLiNXp6P+StPbrRebCXVKH9HkX4IfAnzexbAtJ26XDfYCyiNjc7WrGsrFF1h94PtIrJw1NPdTFFqOu60LSnjmjx9OA3xjrsi4iYmVEtIuI4ogoJrmg2jciptXmMdSWenhddMwZ7QvMzbvQVVWIq7h1+QCmAG+SfMQ6Mp12EkmLezXwEfBsOr2YpKvhuSRXynfN2c5t6Trr0ufr0+nNgL8A84DXga6FPuYC1sWB6fjnwDJgTqGPuYB18TtgDjADeAHYp9DHXKi6KLevSTTQb8vU0+viN+nr4p/p6+Ib9XVs7n7AzCyDMndaxszMHO5mZpnkcDczyyCHu5lZBjnczcwyyOFumSHppJze99Y/1kk6VlInSaPrsSzzJbWrr/2ZleevQlpmSTofOAM4PCLqtXOz9NeZvSLi4/rcr9l6brlbJknaC7gOOCsi1kkqljQ7nfdjSWMkPSPpLUlDKtnGMUr6q/+npInptDaS/pZ2BPWqpB7p9LaSxkuaLumP5PRBJOlMSa+nnyT+KKlxnVeAbfUc7pY5kpoCjwKXR8TCShY7iKRV3xMYUL7vE0ntSfoCOTki9gMGpLNuAKZHRA/gVyR92QMMAV6KiP1JuqjYJd1ON+AU4NCI6AmsTfdrVqcy1SukWeomkq4QRm5imQkRsQxA0hPAd0l6+1vvYGByRLwHEBHrO4f7LnByOu35tMXeGvgeSd8kRMRTkpanyx9J0lnU1LTb/+bA0vwP0WzTHO6WKemtz04GDtjMouUvNpUfVwXT1k+vbFuVLT88Iq7aTHnMapVPy1hmSCoChgFnR8Snm1m8T3r+vDlJ97wvl5v/CnBYelMGJLVJp08mPa2SvpF8HMldmHKnHwsUpctPBPrn9D7YRtKuNT1Gs6pyy92y5AKSe1veU+7Oh78BXiu37EvACGAP4NEo1yVtRJSm37Z5Ir0BxVKgD3A9MEzSTOALNnb/fAPwZ0lvkNyubWG6nTclXQOMT7ezhuRenAtq5YjNKuGvQtpWR9KPSb6m+PNCl8Wsrvi0jJlZBrnlbmaWQW65m5llkMPdzCyDHO5mZhnkcDczyyCHu5lZBv1/uHbymTNkyvAAAAAASUVORK5CYII=\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "df.plot.bar(x='Zip code', \n",
    "            y=['1.00 or less', '1.01 to 1.50', '1.51 or more'], \n",
    "            title='Occupants per room', stacked=True, rot=0);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get Data by US Census Tract\n",
    "\n",
    "Note, tracts are represented at strings."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Example: Occupants per room for a tract"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "tract = '06073008324'"
   ]
  },
  {
   "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>h.tract</th>\n",
       "      <th>1.00 or less</th>\n",
       "      <th>1.01 to 1.50</th>\n",
       "      <th>1.51 or more</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>06073008324</td>\n",
       "      <td>100.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       h.tract  1.00 or less  1.01 to 1.50  1.51 or more\n",
       "0  06073008324         100.0           0.0           0.0"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "MATCH (h:Housing{tract: $tract})\n",
    "RETURN h.tract,\n",
    "       h.`occupantsPerRoom1.00orLessPct` AS `1.00 or less`,\n",
    "       h.`occupantsPerRoom1.01to1.50Pct` AS  `1.01 to 1.50`,\n",
    "       h.`occupantsPerRoom1.51orMorePct` AS `1.51 or more`\n",
    "\"\"\"\n",
    "df = graph.run(query, tract=tract).to_data_frame()\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Example: List occupants per from for all tracts in a county"
   ]
  },
  {
   "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>State</th>\n",
       "      <th>County</th>\n",
       "      <th>Tract</th>\n",
       "      <th>1.00 or less</th>\n",
       "      <th>1.01 to 1.50</th>\n",
       "      <th>1.51 or more</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>California</td>\n",
       "      <td>Orange County</td>\n",
       "      <td>06059087505</td>\n",
       "      <td>83.6</td>\n",
       "      <td>11.2</td>\n",
       "      <td>5.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>California</td>\n",
       "      <td>Orange County</td>\n",
       "      <td>06059032061</td>\n",
       "      <td>97.3</td>\n",
       "      <td>1.3</td>\n",
       "      <td>1.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>California</td>\n",
       "      <td>Orange County</td>\n",
       "      <td>06059099903</td>\n",
       "      <td>85.1</td>\n",
       "      <td>9.8</td>\n",
       "      <td>5.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>California</td>\n",
       "      <td>Orange County</td>\n",
       "      <td>06059990100</td>\n",
       "      <td>-666666666.0</td>\n",
       "      <td>-666666666.0</td>\n",
       "      <td>-666666666.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>California</td>\n",
       "      <td>Orange County</td>\n",
       "      <td>06059099902</td>\n",
       "      <td>87.6</td>\n",
       "      <td>9.5</td>\n",
       "      <td>2.9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        State         County        Tract  1.00 or less  1.01 to 1.50  \\\n",
       "0  California  Orange County  06059087505          83.6          11.2   \n",
       "1  California  Orange County  06059032061          97.3           1.3   \n",
       "2  California  Orange County  06059099903          85.1           9.8   \n",
       "3  California  Orange County  06059990100  -666666666.0  -666666666.0   \n",
       "4  California  Orange County  06059099902          87.6           9.5   \n",
       "\n",
       "   1.51 or more  \n",
       "0           5.1  \n",
       "1           1.4  \n",
       "2           5.1  \n",
       "3  -666666666.0  \n",
       "4           2.9  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "state = 'California'\n",
    "county = 'Orange County'\n",
    "\n",
    "query = \"\"\"\n",
    "MATCH (a1:Admin1{name: $admin1})-[:IN]-(a2:Admin2{name: $admin2})-[:IN]-(t:Tract)-[:HAS_HOUSING]-(h:Housing)\n",
    "RETURN a1.name AS State, a2.name AS County, t.name AS Tract,\n",
    "       h.`occupantsPerRoom1.00orLessPct` AS `1.00 or less`,\n",
    "       h.`occupantsPerRoom1.01to1.50Pct` AS  `1.01 to 1.50`,\n",
    "       h.`occupantsPerRoom1.51orMorePct` AS `1.51 or more`\n",
    "\"\"\"\n",
    "df = graph.run(query, admin1=state, admin2=county).to_data_frame()\n",
    "df.head()"
   ]
  }
 ],
 "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.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}