notebooks/queries/Locations.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Example queries for geographic locations on COVID-19 Knowledge Graph\n",
    "[Work in progress]\n",
    "\n",
    "This notebook demonstrates how to run Cypher queries in a Jupyter Notebook by connecting to a database server."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "import datetime\n",
    "import pandas as pd\n",
    "from py2neo import Graph"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "graph = Graph(\"bolt://132.249.238.185:7687\", user=\"reader\", password=\"demo\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### List Node Metadata\n",
    "The metadata describe the Node types and the data sources used to populate the knowledge graph."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>n.name</th>\n",
       "      <th>n.shortDescription</th>\n",
       "      <th>n.description</th>\n",
       "      <th>n.example</th>\n",
       "      <th>n.definitionSource</th>\n",
       "      <th>n.dataProviders</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Location</td>\n",
       "      <td>Geographic location</td>\n",
       "      <td>A geographic location</td>\n",
       "      <td>World, ..., Country, State, Country, City, Cru...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>World</td>\n",
       "      <td>The World</td>\n",
       "      <td>Top level location</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>UNRegion</td>\n",
       "      <td>Continental regions</td>\n",
       "      <td>Continental regions according to the M49 stan...</td>\n",
       "      <td>Americas</td>\n",
       "      <td>https://unstats.un.org/unsd/methodology/m49/</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>UNSubRegion</td>\n",
       "      <td>Subcontinental regions</td>\n",
       "      <td>Subcontinental regions according to the M49 st...</td>\n",
       "      <td>Latin America and the Caribbean</td>\n",
       "      <td>https://unstats.un.org/unsd/methodology/m49/</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>UNIntermediateRegion</td>\n",
       "      <td>Subdivisions of subcontinental regions</td>\n",
       "      <td>Subdivisions of subcontinental regions accordi...</td>\n",
       "      <td>Caribbean</td>\n",
       "      <td>https://unstats.un.org/unsd/methodology/m49/</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Country</td>\n",
       "      <td>Countries and dependent Territories</td>\n",
       "      <td>Countries and dependent Territories defined b...</td>\n",
       "      <td>United States</td>\n",
       "      <td>http://www.geonames.org/</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Admin1</td>\n",
       "      <td>State, Province, Municipality</td>\n",
       "      <td>First administrative divisions, e.g, State, Pr...</td>\n",
       "      <td>California</td>\n",
       "      <td>http://www.geonames.org/</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Admin2</td>\n",
       "      <td>County</td>\n",
       "      <td>Second administrative divisions: County in the US</td>\n",
       "      <td>San Diego County</td>\n",
       "      <td>http://www.geonames.org/</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>City</td>\n",
       "      <td>City</td>\n",
       "      <td>City</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>http://www.geonames.org/</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>PostalCode</td>\n",
       "      <td>Postal Code</td>\n",
       "      <td>E.g., a ZIP Code is a postal code used by the ...</td>\n",
       "      <td>92121</td>\n",
       "      <td>http://purl.obolibrary.org/obo/OPMI_0000120</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Tract</td>\n",
       "      <td>Census tract</td>\n",
       "      <td>Census tracts are small, relatively permanent ...</td>\n",
       "      <td>06073008346</td>\n",
       "      <td>https://www2.census.gov/geo/pdfs/education/Cen...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Demographics</td>\n",
       "      <td>Population statistics</td>\n",
       "      <td>Population statistics from the American Commun...</td>\n",
       "      <td>Race</td>\n",
       "      <td>https://www2.census.gov/programs-surveys/acs/t...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>CruiseShip</td>\n",
       "      <td>Cruise ship</td>\n",
       "      <td>Cruise ship</td>\n",
       "      <td>Diamond Princess</td>\n",
       "      <td>http://www.productontology.org/doc/Cruise_ship</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>USRegion</td>\n",
       "      <td>Regions of the US</td>\n",
       "      <td>Regions defined by the US Census Bureau</td>\n",
       "      <td>West Region</td>\n",
       "      <td>https://www.census.gov/geographies/reference-f...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>USDivision</td>\n",
       "      <td>Subregions of US</td>\n",
       "      <td>Divisions defined by the US Census Burea</td>\n",
       "      <td>Pacific Division</td>\n",
       "      <td>https://www.census.gov/geographies/reference-f...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Cases</td>\n",
       "      <td>Case counts</td>\n",
       "      <td>Number of case for outbreaks or diseases at sp...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Outbreak</td>\n",
       "      <td>Increase in disease occurance</td>\n",
       "      <td>Statistically significant increase in disease ...</td>\n",
       "      <td>COVID-19</td>\n",
       "      <td>http://purl.obolibrary.org/obo/IDO_0000502</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Organism</td>\n",
       "      <td>Living system</td>\n",
       "      <td>Individual living system, such as animal, plan...</td>\n",
       "      <td>Homo sapiens</td>\n",
       "      <td>http://purl.obolibrary.org/obo/OBI_0100026</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Genome</td>\n",
       "      <td>Genetic material within a cell or virion</td>\n",
       "      <td>A genome is the sum of genetic material within...</td>\n",
       "      <td>Homo sapiens GRCh38.p13</td>\n",
       "      <td>http://purl.obolibrary.org/obo/SO_0001026</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Chromosome</td>\n",
       "      <td>Part of a cell or virion that contains genetic...</td>\n",
       "      <td>Structural unit composed of a nucleic acid mol...</td>\n",
       "      <td>Chromosome 1</td>\n",
       "      <td>http://purl.obolibrary.org/obo/SO_0000340</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Strain</td>\n",
       "      <td>Genetic variant of an organism</td>\n",
       "      <td>A population of organisms that is geneticaly d...</td>\n",
       "      <td>COVID-19 strain</td>\n",
       "      <td>https://semanticscience.org/resource/SIO_01005...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Lineage</td>\n",
       "      <td>Line of descent</td>\n",
       "      <td>Descent in a line from a common progenitor or ...</td>\n",
       "      <td>B.1.1.7</td>\n",
       "      <td>http://purl.obolibrary.org/obo/NCIT_C60792</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Gene</td>\n",
       "      <td>Sequence of nucleotides</td>\n",
       "      <td>Sequence of nucleotides in DNA or RNA that enc...</td>\n",
       "      <td>SARS-CoV-2 S gene</td>\n",
       "      <td>http://purl.obolibrary.org/obo/SO_0000704</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>GeneName</td>\n",
       "      <td>Name and accession number of a protein</td>\n",
       "      <td>Preferred name or synonym and associated acces...</td>\n",
       "      <td>ORF1ab</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Variant</td>\n",
       "      <td>Natural sequence variation</td>\n",
       "      <td>Natural sequence variants due to polymorphisms...</td>\n",
       "      <td>ORF8:c.184Gtg&gt;Ctg</td>\n",
       "      <td>http://purl.obolibrary.org/obo/SO_0001147</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Protein</td>\n",
       "      <td>Sequence of amino acids</td>\n",
       "      <td>Proteins are large biomolecules consisting of ...</td>\n",
       "      <td>Spike protein</td>\n",
       "      <td>http://purl.obolibrary.org/obo/SO_0000104</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>ProteinName</td>\n",
       "      <td>Name and accession number of a protein</td>\n",
       "      <td>Preferred name or synonym and associated acces...</td>\n",
       "      <td>Spike glycoprotein</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>ProteinDomain</td>\n",
       "      <td>A structurally or functionally defined protein...</td>\n",
       "      <td>A specific physical region or amino acid seque...</td>\n",
       "      <td>PF01234</td>\n",
       "      <td>http://purl.obolibrary.org/obo/NCIT_C13379</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>ProteinFamily</td>\n",
       "      <td>Collection of proteins that are evolutionarily...</td>\n",
       "      <td>Collection of proteins that are evolutionarily...</td>\n",
       "      <td>GPCR_A</td>\n",
       "      <td>http://purl.obolibrary.org/obo/NCIT_C20130</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Chain</td>\n",
       "      <td>3D coordinate and associated data for the tert...</td>\n",
       "      <td>3D coordinate and associated data for the tert...</td>\n",
       "      <td>pdb:7JR3.A</td>\n",
       "      <td>http://edamontology.org/data_1467</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>Structure</td>\n",
       "      <td>3D coordinate and associated data for a macrom...</td>\n",
       "      <td>3D coordinate and associated data for a macrom...</td>\n",
       "      <td>pdb:7JR3</td>\n",
       "      <td>http://edamontology.org/data_0883</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>Publication</td>\n",
       "      <td>Published document</td>\n",
       "      <td>A document that has been accepted by a publisher</td>\n",
       "      <td>PMC7106203</td>\n",
       "      <td>http://purl.obolibrary.org/obo/IAO_0000311</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>SocialCharacteristics</td>\n",
       "      <td>Social characteristics</td>\n",
       "      <td>Social characteristics from the American Commu...</td>\n",
       "      <td>Educational attainment</td>\n",
       "      <td>https://www2.census.gov/programs-surveys/acs/t...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>Education</td>\n",
       "      <td>Education attainment</td>\n",
       "      <td>Education attainment from the American Communi...</td>\n",
       "      <td>Highest degree</td>\n",
       "      <td>https://www2.census.gov/programs-surveys/acs/t...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>Computers</td>\n",
       "      <td>Computer and internet use</td>\n",
       "      <td>Households that use computers and internet fro...</td>\n",
       "      <td>Broadband internet</td>\n",
       "      <td>https://www2.census.gov/programs-surveys/acs/t...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>Economics</td>\n",
       "      <td>Economic characteristics</td>\n",
       "      <td>Economic characteristics from the American Com...</td>\n",
       "      <td>Income</td>\n",
       "      <td>https://www2.census.gov/programs-surveys/acs/t...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>Commuting</td>\n",
       "      <td>Commuting to work</td>\n",
       "      <td>Commuting to work from the American Community ...</td>\n",
       "      <td>Public transport</td>\n",
       "      <td>https://www2.census.gov/programs-surveys/acs/t...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>Income</td>\n",
       "      <td>Household income</td>\n",
       "      <td>Household income from the American Community S...</td>\n",
       "      <td>Mean household income</td>\n",
       "      <td>https://www2.census.gov/programs-surveys/acs/t...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>Poverty</td>\n",
       "      <td>Income below poverty level</td>\n",
       "      <td>Families and people whose income in the past 1...</td>\n",
       "      <td>Percent of people in poverty</td>\n",
       "      <td>https://www2.census.gov/programs-surveys/acs/t...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>Employment</td>\n",
       "      <td>Employment status</td>\n",
       "      <td>Employment status from the American Community ...</td>\n",
       "      <td>Civilian labor force</td>\n",
       "      <td>https://www2.census.gov/programs-surveys/acs/t...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>HealthInsurance</td>\n",
       "      <td>Health insurance coverage</td>\n",
       "      <td>Health insurance coverage from the American Co...</td>\n",
       "      <td>No health insurance</td>\n",
       "      <td>https://www2.census.gov/programs-surveys/acs/t...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>Occupation</td>\n",
       "      <td>Type of occupation</td>\n",
       "      <td>Type of coccupation from the American Communit...</td>\n",
       "      <td>Service occupation</td>\n",
       "      <td>https://www2.census.gov/programs-surveys/acs/t...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>Housing</td>\n",
       "      <td>Housing characteristics</td>\n",
       "      <td>Housing characteristics from the American Comm...</td>\n",
       "      <td>Occupants per room</td>\n",
       "      <td>https://www2.census.gov/programs-surveys/acs/t...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>MetaNode</td>\n",
       "      <td>Node metadata</td>\n",
       "      <td>Description about a node in a graph</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>DataSource</td>\n",
       "      <td>Source of data</td>\n",
       "      <td>Authoritative body who provided the information</td>\n",
       "      <td>NCBI</td>\n",
       "      <td>http://purl.obolibrary.org/obo/NCIT_C16493</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   n.name                                 n.shortDescription  \\\n",
       "0                Location                                Geographic location   \n",
       "1                   World                                          The World   \n",
       "2                UNRegion                                Continental regions   \n",
       "3             UNSubRegion                             Subcontinental regions   \n",
       "4    UNIntermediateRegion             Subdivisions of subcontinental regions   \n",
       "5                 Country                Countries and dependent Territories   \n",
       "6                  Admin1                      State, Province, Municipality   \n",
       "7                  Admin2                                             County   \n",
       "8                    City                                               City   \n",
       "9              PostalCode                                        Postal Code   \n",
       "10                  Tract                                       Census tract   \n",
       "11           Demographics                              Population statistics   \n",
       "12             CruiseShip                                        Cruise ship   \n",
       "13               USRegion                                  Regions of the US   \n",
       "14             USDivision                                   Subregions of US   \n",
       "15                  Cases                                        Case counts   \n",
       "16               Outbreak                      Increase in disease occurance   \n",
       "17               Organism                                      Living system   \n",
       "18                 Genome           Genetic material within a cell or virion   \n",
       "19             Chromosome  Part of a cell or virion that contains genetic...   \n",
       "20                 Strain                     Genetic variant of an organism   \n",
       "21                Lineage                                    Line of descent   \n",
       "22                   Gene                            Sequence of nucleotides   \n",
       "23               GeneName             Name and accession number of a protein   \n",
       "24                Variant                         Natural sequence variation   \n",
       "25                Protein                            Sequence of amino acids   \n",
       "26            ProteinName             Name and accession number of a protein   \n",
       "27          ProteinDomain  A structurally or functionally defined protein...   \n",
       "28          ProteinFamily  Collection of proteins that are evolutionarily...   \n",
       "29                  Chain  3D coordinate and associated data for the tert...   \n",
       "30              Structure  3D coordinate and associated data for a macrom...   \n",
       "31            Publication                                 Published document   \n",
       "32  SocialCharacteristics                             Social characteristics   \n",
       "33              Education                               Education attainment   \n",
       "34              Computers                          Computer and internet use   \n",
       "35              Economics                           Economic characteristics   \n",
       "36              Commuting                                  Commuting to work   \n",
       "37                 Income                                   Household income   \n",
       "38                Poverty                         Income below poverty level   \n",
       "39             Employment                                  Employment status   \n",
       "40        HealthInsurance                          Health insurance coverage   \n",
       "41             Occupation                                 Type of occupation   \n",
       "42                Housing                            Housing characteristics   \n",
       "43               MetaNode                                      Node metadata   \n",
       "44             DataSource                                     Source of data   \n",
       "\n",
       "                                        n.description  \\\n",
       "0                               A geographic location   \n",
       "1                                  Top level location   \n",
       "2    Continental regions according to the M49 stan...   \n",
       "3   Subcontinental regions according to the M49 st...   \n",
       "4   Subdivisions of subcontinental regions accordi...   \n",
       "5    Countries and dependent Territories defined b...   \n",
       "6   First administrative divisions, e.g, State, Pr...   \n",
       "7   Second administrative divisions: County in the US   \n",
       "8                                                City   \n",
       "9   E.g., a ZIP Code is a postal code used by the ...   \n",
       "10  Census tracts are small, relatively permanent ...   \n",
       "11  Population statistics from the American Commun...   \n",
       "12                                        Cruise ship   \n",
       "13            Regions defined by the US Census Bureau   \n",
       "14           Divisions defined by the US Census Burea   \n",
       "15  Number of case for outbreaks or diseases at sp...   \n",
       "16  Statistically significant increase in disease ...   \n",
       "17  Individual living system, such as animal, plan...   \n",
       "18  A genome is the sum of genetic material within...   \n",
       "19  Structural unit composed of a nucleic acid mol...   \n",
       "20  A population of organisms that is geneticaly d...   \n",
       "21  Descent in a line from a common progenitor or ...   \n",
       "22  Sequence of nucleotides in DNA or RNA that enc...   \n",
       "23  Preferred name or synonym and associated acces...   \n",
       "24  Natural sequence variants due to polymorphisms...   \n",
       "25  Proteins are large biomolecules consisting of ...   \n",
       "26  Preferred name or synonym and associated acces...   \n",
       "27  A specific physical region or amino acid seque...   \n",
       "28  Collection of proteins that are evolutionarily...   \n",
       "29  3D coordinate and associated data for the tert...   \n",
       "30  3D coordinate and associated data for a macrom...   \n",
       "31   A document that has been accepted by a publisher   \n",
       "32  Social characteristics from the American Commu...   \n",
       "33  Education attainment from the American Communi...   \n",
       "34  Households that use computers and internet fro...   \n",
       "35  Economic characteristics from the American Com...   \n",
       "36  Commuting to work from the American Community ...   \n",
       "37  Household income from the American Community S...   \n",
       "38  Families and people whose income in the past 1...   \n",
       "39  Employment status from the American Community ...   \n",
       "40  Health insurance coverage from the American Co...   \n",
       "41  Type of coccupation from the American Communit...   \n",
       "42  Housing characteristics from the American Comm...   \n",
       "43                Description about a node in a graph   \n",
       "44    Authoritative body who provided the information   \n",
       "\n",
       "                                            n.example  \\\n",
       "0   World, ..., Country, State, Country, City, Cru...   \n",
       "1                                                None   \n",
       "2                                            Americas   \n",
       "3                     Latin America and the Caribbean   \n",
       "4                                           Caribbean   \n",
       "5                                       United States   \n",
       "6                                          California   \n",
       "7                                    San Diego County   \n",
       "8                                           San Diego   \n",
       "9                                               92121   \n",
       "10                                        06073008346   \n",
       "11                                               Race   \n",
       "12                                   Diamond Princess   \n",
       "13                                        West Region   \n",
       "14                                   Pacific Division   \n",
       "15                                               None   \n",
       "16                                           COVID-19   \n",
       "17                                       Homo sapiens   \n",
       "18                            Homo sapiens GRCh38.p13   \n",
       "19                                       Chromosome 1   \n",
       "20                                    COVID-19 strain   \n",
       "21                                            B.1.1.7   \n",
       "22                                  SARS-CoV-2 S gene   \n",
       "23                                             ORF1ab   \n",
       "24                                  ORF8:c.184Gtg>Ctg   \n",
       "25                                      Spike protein   \n",
       "26                                 Spike glycoprotein   \n",
       "27                                            PF01234   \n",
       "28                                             GPCR_A   \n",
       "29                                         pdb:7JR3.A   \n",
       "30                                           pdb:7JR3   \n",
       "31                                         PMC7106203   \n",
       "32                             Educational attainment   \n",
       "33                                     Highest degree   \n",
       "34                                 Broadband internet   \n",
       "35                                             Income   \n",
       "36                                   Public transport   \n",
       "37                              Mean household income   \n",
       "38                       Percent of people in poverty   \n",
       "39                               Civilian labor force   \n",
       "40                                No health insurance   \n",
       "41                                 Service occupation   \n",
       "42                                 Occupants per room   \n",
       "43                                               None   \n",
       "44                                               NCBI   \n",
       "\n",
       "                                   n.definitionSource n.dataProviders  \n",
       "0                                                None            None  \n",
       "1                                                None            None  \n",
       "2        https://unstats.un.org/unsd/methodology/m49/            None  \n",
       "3        https://unstats.un.org/unsd/methodology/m49/            None  \n",
       "4        https://unstats.un.org/unsd/methodology/m49/            None  \n",
       "5                            http://www.geonames.org/            None  \n",
       "6                            http://www.geonames.org/            None  \n",
       "7                            http://www.geonames.org/            None  \n",
       "8                            http://www.geonames.org/            None  \n",
       "9         http://purl.obolibrary.org/obo/OPMI_0000120            None  \n",
       "10  https://www2.census.gov/geo/pdfs/education/Cen...            None  \n",
       "11  https://www2.census.gov/programs-surveys/acs/t...            None  \n",
       "12     http://www.productontology.org/doc/Cruise_ship            None  \n",
       "13  https://www.census.gov/geographies/reference-f...            None  \n",
       "14  https://www.census.gov/geographies/reference-f...            None  \n",
       "15                                               None            None  \n",
       "16         http://purl.obolibrary.org/obo/IDO_0000502            None  \n",
       "17         http://purl.obolibrary.org/obo/OBI_0100026            None  \n",
       "18          http://purl.obolibrary.org/obo/SO_0001026            None  \n",
       "19          http://purl.obolibrary.org/obo/SO_0000340            None  \n",
       "20  https://semanticscience.org/resource/SIO_01005...            None  \n",
       "21         http://purl.obolibrary.org/obo/NCIT_C60792            None  \n",
       "22          http://purl.obolibrary.org/obo/SO_0000704            None  \n",
       "23                                               None            None  \n",
       "24          http://purl.obolibrary.org/obo/SO_0001147            None  \n",
       "25          http://purl.obolibrary.org/obo/SO_0000104            None  \n",
       "26                                               None            None  \n",
       "27         http://purl.obolibrary.org/obo/NCIT_C13379            None  \n",
       "28         http://purl.obolibrary.org/obo/NCIT_C20130            None  \n",
       "29                  http://edamontology.org/data_1467            None  \n",
       "30                  http://edamontology.org/data_0883            None  \n",
       "31         http://purl.obolibrary.org/obo/IAO_0000311            None  \n",
       "32  https://www2.census.gov/programs-surveys/acs/t...            None  \n",
       "33  https://www2.census.gov/programs-surveys/acs/t...            None  \n",
       "34  https://www2.census.gov/programs-surveys/acs/t...            None  \n",
       "35  https://www2.census.gov/programs-surveys/acs/t...            None  \n",
       "36  https://www2.census.gov/programs-surveys/acs/t...            None  \n",
       "37  https://www2.census.gov/programs-surveys/acs/t...            None  \n",
       "38  https://www2.census.gov/programs-surveys/acs/t...            None  \n",
       "39  https://www2.census.gov/programs-surveys/acs/t...            None  \n",
       "40  https://www2.census.gov/programs-surveys/acs/t...            None  \n",
       "41  https://www2.census.gov/programs-surveys/acs/t...            None  \n",
       "42  https://www2.census.gov/programs-surveys/acs/t...            None  \n",
       "43                                               None            None  \n",
       "44         http://purl.obolibrary.org/obo/NCIT_C16493            None  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "MATCH (n:MetaNode)\n",
    "RETURN n.name, n.shortDescription, n.description, n.example, n.definitionSource, n.dataProviders\n",
    "\"\"\"\n",
    "graph.run(query).to_data_frame()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Run a fuzzy full text search for locations\n",
    "Results are ordered by match score."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>node.name</th>\n",
       "      <th>node.location</th>\n",
       "      <th>node.population</th>\n",
       "      <th>labels(node)</th>\n",
       "      <th>score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>(-72.35366, -37.46973)</td>\n",
       "      <td>12543.0</td>\n",
       "      <td>[City, Location]</td>\n",
       "      <td>5.832285</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>(-95.1654, 17.39485)</td>\n",
       "      <td>1144.0</td>\n",
       "      <td>[City, Location]</td>\n",
       "      <td>5.832285</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>(-118.24368, 34.05223)</td>\n",
       "      <td>3971883.0</td>\n",
       "      <td>[City, Location]</td>\n",
       "      <td>5.832285</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>(-108.48095, 25.60289)</td>\n",
       "      <td>4217.0</td>\n",
       "      <td>[City, Location]</td>\n",
       "      <td>5.832285</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>(-103.56757, 25.52206)</td>\n",
       "      <td>1616.0</td>\n",
       "      <td>[City, Location]</td>\n",
       "      <td>5.832285</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>(-100.94167, 20.55361)</td>\n",
       "      <td>178.0</td>\n",
       "      <td>[City, Location]</td>\n",
       "      <td>5.832285</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>(-82.20286, 8.52571)</td>\n",
       "      <td>25.0</td>\n",
       "      <td>[City, Location]</td>\n",
       "      <td>5.832285</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>(-80.35497, 7.88463)</td>\n",
       "      <td>342.0</td>\n",
       "      <td>[City, Location]</td>\n",
       "      <td>5.832285</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>(125.60806, 9.0125)</td>\n",
       "      <td>454.0</td>\n",
       "      <td>[City, Location]</td>\n",
       "      <td>5.832285</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Fraccionamiento los Angeles</td>\n",
       "      <td>(-106.33, 23.18833)</td>\n",
       "      <td>6282.0</td>\n",
       "      <td>[City, Location]</td>\n",
       "      <td>4.644566</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Lake Los Angeles</td>\n",
       "      <td>(-117.82812, 34.61249)</td>\n",
       "      <td>12328.0</td>\n",
       "      <td>[City, Location]</td>\n",
       "      <td>4.644566</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Los Angeles Tetela</td>\n",
       "      <td>(-98.16712, 18.88995)</td>\n",
       "      <td>2237.0</td>\n",
       "      <td>[City, Location]</td>\n",
       "      <td>4.644566</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>East Los Angeles</td>\n",
       "      <td>(-118.17202, 34.0239)</td>\n",
       "      <td>126496.0</td>\n",
       "      <td>[City, Location]</td>\n",
       "      <td>4.644566</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Colonia los Angeles</td>\n",
       "      <td>(-100.48828, 20.53523)</td>\n",
       "      <td>439.0</td>\n",
       "      <td>[City, Location]</td>\n",
       "      <td>4.644566</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Los Angeles County</td>\n",
       "      <td>(-118.26102, 34.19801)</td>\n",
       "      <td>981865.0</td>\n",
       "      <td>[Admin2, Location]</td>\n",
       "      <td>4.644566</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Angeles</td>\n",
       "      <td>(-84.05126, 9.99591)</td>\n",
       "      <td>1355.0</td>\n",
       "      <td>[City, Location]</td>\n",
       "      <td>4.555374</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>General Felipe Angeles (Los Angeles)</td>\n",
       "      <td>(-98.92611, 19.8975)</td>\n",
       "      <td>3776.0</td>\n",
       "      <td>[City, Location]</td>\n",
       "      <td>4.546025</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>90001</td>\n",
       "      <td>(-118.2479, 33.9731)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[PostalCode, Location]</td>\n",
       "      <td>4.385465</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>90002</td>\n",
       "      <td>(-118.2462, 33.9497)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[PostalCode, Location]</td>\n",
       "      <td>4.385465</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>90003</td>\n",
       "      <td>(-118.2727, 33.9653)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[PostalCode, Location]</td>\n",
       "      <td>4.385465</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>90004</td>\n",
       "      <td>(-118.3029, 34.0762)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[PostalCode, Location]</td>\n",
       "      <td>4.385465</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>90005</td>\n",
       "      <td>(-118.3012, 34.0585)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[PostalCode, Location]</td>\n",
       "      <td>4.385465</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>90006</td>\n",
       "      <td>(-118.2917, 34.0493)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[PostalCode, Location]</td>\n",
       "      <td>4.385465</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>90007</td>\n",
       "      <td>(-118.2871, 34.0294)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[PostalCode, Location]</td>\n",
       "      <td>4.385465</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>90008</td>\n",
       "      <td>(-118.3411, 34.0116)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[PostalCode, Location]</td>\n",
       "      <td>4.385465</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               node.name           node.location  \\\n",
       "0                            Los Angeles  (-72.35366, -37.46973)   \n",
       "1                            Los Angeles    (-95.1654, 17.39485)   \n",
       "2                            Los Angeles  (-118.24368, 34.05223)   \n",
       "3                            Los Angeles  (-108.48095, 25.60289)   \n",
       "4                            Los Angeles  (-103.56757, 25.52206)   \n",
       "5                            Los Angeles  (-100.94167, 20.55361)   \n",
       "6                            Los Angeles    (-82.20286, 8.52571)   \n",
       "7                            Los Angeles    (-80.35497, 7.88463)   \n",
       "8                            Los Angeles     (125.60806, 9.0125)   \n",
       "9            Fraccionamiento los Angeles     (-106.33, 23.18833)   \n",
       "10                      Lake Los Angeles  (-117.82812, 34.61249)   \n",
       "11                    Los Angeles Tetela   (-98.16712, 18.88995)   \n",
       "12                      East Los Angeles   (-118.17202, 34.0239)   \n",
       "13                   Colonia los Angeles  (-100.48828, 20.53523)   \n",
       "14                    Los Angeles County  (-118.26102, 34.19801)   \n",
       "15                               Angeles    (-84.05126, 9.99591)   \n",
       "16  General Felipe Angeles (Los Angeles)    (-98.92611, 19.8975)   \n",
       "17                                 90001    (-118.2479, 33.9731)   \n",
       "18                                 90002    (-118.2462, 33.9497)   \n",
       "19                                 90003    (-118.2727, 33.9653)   \n",
       "20                                 90004    (-118.3029, 34.0762)   \n",
       "21                                 90005    (-118.3012, 34.0585)   \n",
       "22                                 90006    (-118.2917, 34.0493)   \n",
       "23                                 90007    (-118.2871, 34.0294)   \n",
       "24                                 90008    (-118.3411, 34.0116)   \n",
       "\n",
       "    node.population            labels(node)     score  \n",
       "0           12543.0        [City, Location]  5.832285  \n",
       "1            1144.0        [City, Location]  5.832285  \n",
       "2         3971883.0        [City, Location]  5.832285  \n",
       "3            4217.0        [City, Location]  5.832285  \n",
       "4            1616.0        [City, Location]  5.832285  \n",
       "5             178.0        [City, Location]  5.832285  \n",
       "6              25.0        [City, Location]  5.832285  \n",
       "7             342.0        [City, Location]  5.832285  \n",
       "8             454.0        [City, Location]  5.832285  \n",
       "9            6282.0        [City, Location]  4.644566  \n",
       "10          12328.0        [City, Location]  4.644566  \n",
       "11           2237.0        [City, Location]  4.644566  \n",
       "12         126496.0        [City, Location]  4.644566  \n",
       "13            439.0        [City, Location]  4.644566  \n",
       "14         981865.0      [Admin2, Location]  4.644566  \n",
       "15           1355.0        [City, Location]  4.555374  \n",
       "16           3776.0        [City, Location]  4.546025  \n",
       "17              NaN  [PostalCode, Location]  4.385465  \n",
       "18              NaN  [PostalCode, Location]  4.385465  \n",
       "19              NaN  [PostalCode, Location]  4.385465  \n",
       "20              NaN  [PostalCode, Location]  4.385465  \n",
       "21              NaN  [PostalCode, Location]  4.385465  \n",
       "22              NaN  [PostalCode, Location]  4.385465  \n",
       "23              NaN  [PostalCode, Location]  4.385465  \n",
       "24              NaN  [PostalCode, Location]  4.385465  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "CALL db.index.fulltext.queryNodes('locations', 'Los Angeles') YIELD node, score\n",
    "RETURN node.name, node.location, node.population, labels(node), score\n",
    "\"\"\"\n",
    "df = graph.run(query).to_data_frame()\n",
    "df.head(25)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Lookup by Geographical IDs (iso, iso3, fips, zip, census tract, geonameId)"
   ]
  },
  {
   "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>node.name</th>\n",
       "      <th>labels(node)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Canada</td>\n",
       "      <td>[Country, Location]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>California</td>\n",
       "      <td>[Admin1, Location]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Capellen</td>\n",
       "      <td>[Admin1, Location]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    node.name         labels(node)\n",
       "0      Canada  [Country, Location]\n",
       "1  California   [Admin1, Location]\n",
       "2    Capellen   [Admin1, Location]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "CALL db.index.fulltext.queryNodes('geoids', 'CA') YIELD node\n",
    "RETURN node.name, labels(node)\n",
    "\"\"\"\n",
    "graph.run(query).to_data_frame()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### List all US States"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>a.code</th>\n",
       "      <th>a.fips</th>\n",
       "      <th>a.population</th>\n",
       "      <th>a.elevation</th>\n",
       "      <th>a.location</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>01</td>\n",
       "      <td>453315</td>\n",
       "      <td>141.0</td>\n",
       "      <td>(-86.75026, 32.75041)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>02</td>\n",
       "      <td>66633</td>\n",
       "      <td>421.0</td>\n",
       "      <td>(-150.00028, 64.00028)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Arizona</td>\n",
       "      <td>AZ</td>\n",
       "      <td>04</td>\n",
       "      <td>586389</td>\n",
       "      <td>1927.0</td>\n",
       "      <td>(-111.50098, 34.5003)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Arkansas</td>\n",
       "      <td>AR</td>\n",
       "      <td>05</td>\n",
       "      <td>2757631</td>\n",
       "      <td>185.0</td>\n",
       "      <td>(-92.50044, 34.75037)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>California</td>\n",
       "      <td>CA</td>\n",
       "      <td>06</td>\n",
       "      <td>39512223</td>\n",
       "      <td>469.0</td>\n",
       "      <td>(-119.75126, 37.25022)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Colorado</td>\n",
       "      <td>CO</td>\n",
       "      <td>08</td>\n",
       "      <td>467863</td>\n",
       "      <td>3049.0</td>\n",
       "      <td>(-105.50083, 39.00027)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Connecticut</td>\n",
       "      <td>CT</td>\n",
       "      <td>09</td>\n",
       "      <td>3527249</td>\n",
       "      <td>45.0</td>\n",
       "      <td>(-72.66648, 41.66704)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Delaware</td>\n",
       "      <td>DE</td>\n",
       "      <td>10</td>\n",
       "      <td>838549</td>\n",
       "      <td>9.0</td>\n",
       "      <td>(-75.49992, 39.00039)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>District of Columbia</td>\n",
       "      <td>DC</td>\n",
       "      <td>11</td>\n",
       "      <td>552433</td>\n",
       "      <td>27.0</td>\n",
       "      <td>(-77.00025, 38.91706)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Florida</td>\n",
       "      <td>FL</td>\n",
       "      <td>12</td>\n",
       "      <td>17671452</td>\n",
       "      <td>26.0</td>\n",
       "      <td>(-82.5001, 28.75054)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Georgia</td>\n",
       "      <td>GA</td>\n",
       "      <td>13</td>\n",
       "      <td>8975842</td>\n",
       "      <td>155.0</td>\n",
       "      <td>(-83.50018, 32.75042)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Hawaii</td>\n",
       "      <td>HI</td>\n",
       "      <td>15</td>\n",
       "      <td>128422</td>\n",
       "      <td>0.0</td>\n",
       "      <td>(-156.38612, 20.78785)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Idaho</td>\n",
       "      <td>ID</td>\n",
       "      <td>16</td>\n",
       "      <td>1416564</td>\n",
       "      <td>1681.0</td>\n",
       "      <td>(-114.25118, 44.5002)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Illinois</td>\n",
       "      <td>IL</td>\n",
       "      <td>17</td>\n",
       "      <td>12772888</td>\n",
       "      <td>191.0</td>\n",
       "      <td>(-89.25037, 40.00032)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Indiana</td>\n",
       "      <td>IN</td>\n",
       "      <td>18</td>\n",
       "      <td>6265933</td>\n",
       "      <td>272.0</td>\n",
       "      <td>(-86.25027, 40.00032)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Iowa</td>\n",
       "      <td>IA</td>\n",
       "      <td>19</td>\n",
       "      <td>29551</td>\n",
       "      <td>292.0</td>\n",
       "      <td>(-93.50049, 42.00027)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Kansas</td>\n",
       "      <td>KS</td>\n",
       "      <td>20</td>\n",
       "      <td>274759</td>\n",
       "      <td>545.0</td>\n",
       "      <td>(-98.50063, 38.50029)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Kentucky</td>\n",
       "      <td>KY</td>\n",
       "      <td>21</td>\n",
       "      <td>42674</td>\n",
       "      <td>NaN</td>\n",
       "      <td>(-84.87762, 38.20042)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Louisiana</td>\n",
       "      <td>LA</td>\n",
       "      <td>22</td>\n",
       "      <td>4515939</td>\n",
       "      <td>14.0</td>\n",
       "      <td>(-92.0004, 31.00047)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Maine</td>\n",
       "      <td>ME</td>\n",
       "      <td>23</td>\n",
       "      <td>1325518</td>\n",
       "      <td>362.0</td>\n",
       "      <td>(-69.24977, 45.50032)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Maryland</td>\n",
       "      <td>MD</td>\n",
       "      <td>24</td>\n",
       "      <td>5624246</td>\n",
       "      <td>42.0</td>\n",
       "      <td>(-76.74997, 39.00039)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Massachusetts</td>\n",
       "      <td>MA</td>\n",
       "      <td>25</td>\n",
       "      <td>6433422</td>\n",
       "      <td>NaN</td>\n",
       "      <td>(-71.10832, 42.36565)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Michigan</td>\n",
       "      <td>MI</td>\n",
       "      <td>26</td>\n",
       "      <td>988336</td>\n",
       "      <td>394.0</td>\n",
       "      <td>(-85.50033, 44.25029)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Minnesota</td>\n",
       "      <td>MN</td>\n",
       "      <td>27</td>\n",
       "      <td>5141953</td>\n",
       "      <td>367.0</td>\n",
       "      <td>(-94.25055, 46.25024)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Mississippi</td>\n",
       "      <td>MS</td>\n",
       "      <td>28</td>\n",
       "      <td>291371</td>\n",
       "      <td>110.0</td>\n",
       "      <td>(-89.75036, 32.75041)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Missouri</td>\n",
       "      <td>MO</td>\n",
       "      <td>29</td>\n",
       "      <td>5768151</td>\n",
       "      <td>238.0</td>\n",
       "      <td>(-92.50046, 38.25031)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Montana</td>\n",
       "      <td>MT</td>\n",
       "      <td>30</td>\n",
       "      <td>93698</td>\n",
       "      <td>1202.0</td>\n",
       "      <td>(-109.75102, 47.00025)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Nebraska</td>\n",
       "      <td>NE</td>\n",
       "      <td>31</td>\n",
       "      <td>1757399</td>\n",
       "      <td>817.0</td>\n",
       "      <td>(-99.75067, 41.50028)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Nevada</td>\n",
       "      <td>NV</td>\n",
       "      <td>32</td>\n",
       "      <td>2399532</td>\n",
       "      <td>2260.0</td>\n",
       "      <td>(-116.75119, 39.25021)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>New Hampshire</td>\n",
       "      <td>NH</td>\n",
       "      <td>33</td>\n",
       "      <td>1316216</td>\n",
       "      <td>164.0</td>\n",
       "      <td>(-71.4998, 43.66702)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>New Jersey</td>\n",
       "      <td>NJ</td>\n",
       "      <td>34</td>\n",
       "      <td>8751436</td>\n",
       "      <td>42.0</td>\n",
       "      <td>(-74.49987, 40.16706)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>New Mexico</td>\n",
       "      <td>NM</td>\n",
       "      <td>35</td>\n",
       "      <td>1912684</td>\n",
       "      <td>1889.0</td>\n",
       "      <td>(-106.00085, 34.50034)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>New York</td>\n",
       "      <td>NY</td>\n",
       "      <td>36</td>\n",
       "      <td>19274244</td>\n",
       "      <td>281.0</td>\n",
       "      <td>(-75.4999, 43.00035)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>North Carolina</td>\n",
       "      <td>NC</td>\n",
       "      <td>37</td>\n",
       "      <td>148884</td>\n",
       "      <td>137.0</td>\n",
       "      <td>(-80.00032, 35.50069)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>North Dakota</td>\n",
       "      <td>ND</td>\n",
       "      <td>38</td>\n",
       "      <td>63529</td>\n",
       "      <td>582.0</td>\n",
       "      <td>(-100.00068, 47.50027)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>Ohio</td>\n",
       "      <td>OH</td>\n",
       "      <td>39</td>\n",
       "      <td>11467123</td>\n",
       "      <td>290.0</td>\n",
       "      <td>(-83.00018, 40.25034)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>Oklahoma</td>\n",
       "      <td>OK</td>\n",
       "      <td>40</td>\n",
       "      <td>354749</td>\n",
       "      <td>369.0</td>\n",
       "      <td>(-97.50328, 35.49209)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>Oregon</td>\n",
       "      <td>OR</td>\n",
       "      <td>41</td>\n",
       "      <td>3642919</td>\n",
       "      <td>1460.0</td>\n",
       "      <td>(-120.50139, 44.00013)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>Pennsylvania</td>\n",
       "      <td>PA</td>\n",
       "      <td>42</td>\n",
       "      <td>1244621</td>\n",
       "      <td>NaN</td>\n",
       "      <td>(-77.7, 40.8)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>Rhode Island</td>\n",
       "      <td>RI</td>\n",
       "      <td>44</td>\n",
       "      <td>15292</td>\n",
       "      <td>69.0</td>\n",
       "      <td>(-71.49978, 41.75038)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>South Carolina</td>\n",
       "      <td>SC</td>\n",
       "      <td>45</td>\n",
       "      <td>4229842</td>\n",
       "      <td>92.0</td>\n",
       "      <td>(-81.00009, 34.00043)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>South Dakota</td>\n",
       "      <td>SD</td>\n",
       "      <td>46</td>\n",
       "      <td>77184</td>\n",
       "      <td>548.0</td>\n",
       "      <td>(-100.25069, 44.50026)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>Tennessee</td>\n",
       "      <td>TN</td>\n",
       "      <td>47</td>\n",
       "      <td>593599</td>\n",
       "      <td>325.0</td>\n",
       "      <td>(-86.25027, 35.75035)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>Texas</td>\n",
       "      <td>TX</td>\n",
       "      <td>48</td>\n",
       "      <td>22875689</td>\n",
       "      <td>559.0</td>\n",
       "      <td>(-99.25061, 31.25044)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>Utah</td>\n",
       "      <td>UT</td>\n",
       "      <td>49</td>\n",
       "      <td>242734</td>\n",
       "      <td>1808.0</td>\n",
       "      <td>(-111.75103, 39.25024)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>Vermont</td>\n",
       "      <td>VT</td>\n",
       "      <td>50</td>\n",
       "      <td>62451</td>\n",
       "      <td>271.0</td>\n",
       "      <td>(-72.74983, 44.00034)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>Virginia</td>\n",
       "      <td>VA</td>\n",
       "      <td>51</td>\n",
       "      <td>7642884</td>\n",
       "      <td>NaN</td>\n",
       "      <td>(-77.44675, 37.54812)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>Washington</td>\n",
       "      <td>WA</td>\n",
       "      <td>53</td>\n",
       "      <td>6271775</td>\n",
       "      <td>555.0</td>\n",
       "      <td>(-120.50147, 47.50012)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>West Virginia</td>\n",
       "      <td>WV</td>\n",
       "      <td>54</td>\n",
       "      <td>1817871</td>\n",
       "      <td>705.0</td>\n",
       "      <td>(-80.50009, 38.50038)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>Wisconsin</td>\n",
       "      <td>WI</td>\n",
       "      <td>55</td>\n",
       "      <td>5535168</td>\n",
       "      <td>340.0</td>\n",
       "      <td>(-90.00041, 44.50024)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>WY</td>\n",
       "      <td>56</td>\n",
       "      <td>5597</td>\n",
       "      <td>1889.0</td>\n",
       "      <td>(-107.5009, 43.00024)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  a.name a.code a.fips  a.population  a.elevation  \\\n",
       "0                Alabama     AL     01        453315        141.0   \n",
       "1                 Alaska     AK     02         66633        421.0   \n",
       "2                Arizona     AZ     04        586389       1927.0   \n",
       "3               Arkansas     AR     05       2757631        185.0   \n",
       "4             California     CA     06      39512223        469.0   \n",
       "5               Colorado     CO     08        467863       3049.0   \n",
       "6            Connecticut     CT     09       3527249         45.0   \n",
       "7               Delaware     DE     10        838549          9.0   \n",
       "8   District of Columbia     DC     11        552433         27.0   \n",
       "9                Florida     FL     12      17671452         26.0   \n",
       "10               Georgia     GA     13       8975842        155.0   \n",
       "11                Hawaii     HI     15        128422          0.0   \n",
       "12                 Idaho     ID     16       1416564       1681.0   \n",
       "13              Illinois     IL     17      12772888        191.0   \n",
       "14               Indiana     IN     18       6265933        272.0   \n",
       "15                  Iowa     IA     19         29551        292.0   \n",
       "16                Kansas     KS     20        274759        545.0   \n",
       "17              Kentucky     KY     21         42674          NaN   \n",
       "18             Louisiana     LA     22       4515939         14.0   \n",
       "19                 Maine     ME     23       1325518        362.0   \n",
       "20              Maryland     MD     24       5624246         42.0   \n",
       "21         Massachusetts     MA     25       6433422          NaN   \n",
       "22              Michigan     MI     26        988336        394.0   \n",
       "23             Minnesota     MN     27       5141953        367.0   \n",
       "24           Mississippi     MS     28        291371        110.0   \n",
       "25              Missouri     MO     29       5768151        238.0   \n",
       "26               Montana     MT     30         93698       1202.0   \n",
       "27              Nebraska     NE     31       1757399        817.0   \n",
       "28                Nevada     NV     32       2399532       2260.0   \n",
       "29         New Hampshire     NH     33       1316216        164.0   \n",
       "30            New Jersey     NJ     34       8751436         42.0   \n",
       "31            New Mexico     NM     35       1912684       1889.0   \n",
       "32              New York     NY     36      19274244        281.0   \n",
       "33        North Carolina     NC     37        148884        137.0   \n",
       "34          North Dakota     ND     38         63529        582.0   \n",
       "35                  Ohio     OH     39      11467123        290.0   \n",
       "36              Oklahoma     OK     40        354749        369.0   \n",
       "37                Oregon     OR     41       3642919       1460.0   \n",
       "38          Pennsylvania     PA     42       1244621          NaN   \n",
       "39          Rhode Island     RI     44         15292         69.0   \n",
       "40        South Carolina     SC     45       4229842         92.0   \n",
       "41          South Dakota     SD     46         77184        548.0   \n",
       "42             Tennessee     TN     47        593599        325.0   \n",
       "43                 Texas     TX     48      22875689        559.0   \n",
       "44                  Utah     UT     49        242734       1808.0   \n",
       "45               Vermont     VT     50         62451        271.0   \n",
       "46              Virginia     VA     51       7642884          NaN   \n",
       "47            Washington     WA     53       6271775        555.0   \n",
       "48         West Virginia     WV     54       1817871        705.0   \n",
       "49             Wisconsin     WI     55       5535168        340.0   \n",
       "50               Wyoming     WY     56          5597       1889.0   \n",
       "\n",
       "                a.location  \n",
       "0    (-86.75026, 32.75041)  \n",
       "1   (-150.00028, 64.00028)  \n",
       "2    (-111.50098, 34.5003)  \n",
       "3    (-92.50044, 34.75037)  \n",
       "4   (-119.75126, 37.25022)  \n",
       "5   (-105.50083, 39.00027)  \n",
       "6    (-72.66648, 41.66704)  \n",
       "7    (-75.49992, 39.00039)  \n",
       "8    (-77.00025, 38.91706)  \n",
       "9     (-82.5001, 28.75054)  \n",
       "10   (-83.50018, 32.75042)  \n",
       "11  (-156.38612, 20.78785)  \n",
       "12   (-114.25118, 44.5002)  \n",
       "13   (-89.25037, 40.00032)  \n",
       "14   (-86.25027, 40.00032)  \n",
       "15   (-93.50049, 42.00027)  \n",
       "16   (-98.50063, 38.50029)  \n",
       "17   (-84.87762, 38.20042)  \n",
       "18    (-92.0004, 31.00047)  \n",
       "19   (-69.24977, 45.50032)  \n",
       "20   (-76.74997, 39.00039)  \n",
       "21   (-71.10832, 42.36565)  \n",
       "22   (-85.50033, 44.25029)  \n",
       "23   (-94.25055, 46.25024)  \n",
       "24   (-89.75036, 32.75041)  \n",
       "25   (-92.50046, 38.25031)  \n",
       "26  (-109.75102, 47.00025)  \n",
       "27   (-99.75067, 41.50028)  \n",
       "28  (-116.75119, 39.25021)  \n",
       "29    (-71.4998, 43.66702)  \n",
       "30   (-74.49987, 40.16706)  \n",
       "31  (-106.00085, 34.50034)  \n",
       "32    (-75.4999, 43.00035)  \n",
       "33   (-80.00032, 35.50069)  \n",
       "34  (-100.00068, 47.50027)  \n",
       "35   (-83.00018, 40.25034)  \n",
       "36   (-97.50328, 35.49209)  \n",
       "37  (-120.50139, 44.00013)  \n",
       "38           (-77.7, 40.8)  \n",
       "39   (-71.49978, 41.75038)  \n",
       "40   (-81.00009, 34.00043)  \n",
       "41  (-100.25069, 44.50026)  \n",
       "42   (-86.25027, 35.75035)  \n",
       "43   (-99.25061, 31.25044)  \n",
       "44  (-111.75103, 39.25024)  \n",
       "45   (-72.74983, 44.00034)  \n",
       "46   (-77.44675, 37.54812)  \n",
       "47  (-120.50147, 47.50012)  \n",
       "48   (-80.50009, 38.50038)  \n",
       "49   (-90.00041, 44.50024)  \n",
       "50   (-107.5009, 43.00024)  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "MATCH (a:Admin1)-[:IN]->(:Country{name: 'United States'})\n",
    "RETURN a.name, a.code, a.fips, a.population, a.elevation, a.location\n",
    "ORDER by a.fips\n",
    "\"\"\"\n",
    "graph.run(query).to_data_frame()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Postal Codes for Carlsbad, California"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>p.name</th>\n",
       "      <th>p.placeName</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>92008</td>\n",
       "      <td>Carlsbad</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>92009</td>\n",
       "      <td>Carlsbad</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>92010</td>\n",
       "      <td>Carlsbad</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>92011</td>\n",
       "      <td>Carlsbad</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>92013</td>\n",
       "      <td>Carlsbad</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>92018</td>\n",
       "      <td>Carlsbad</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  p.name p.placeName\n",
       "0  92008    Carlsbad\n",
       "1  92009    Carlsbad\n",
       "2  92010    Carlsbad\n",
       "3  92011    Carlsbad\n",
       "4  92013    Carlsbad\n",
       "5  92018    Carlsbad"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "MATCH (p:PostalCode{placeName:'Carlsbad'})-[:IN*]->(a:Admin1{name: 'California'})\n",
    "RETURN p.name, p.placeName\n",
    "\"\"\"\n",
    "graph.run(query).to_data_frame()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Calculate Distances between Locations"
   ]
  },
  {
   "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>a.name</th>\n",
       "      <th>a.elevation</th>\n",
       "      <th>a.location</th>\n",
       "      <th>b.name</th>\n",
       "      <th>b.elevation</th>\n",
       "      <th>b.location</th>\n",
       "      <th>distance_KM</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Denver</td>\n",
       "      <td>1609</td>\n",
       "      <td>(-104.9847, 39.73915)</td>\n",
       "      <td>Houston</td>\n",
       "      <td>12</td>\n",
       "      <td>(-95.36327, 29.76328)</td>\n",
       "      <td>1415.017179</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   a.name  a.elevation             a.location   b.name  b.elevation  \\\n",
       "0  Denver         1609  (-104.9847, 39.73915)  Houston           12   \n",
       "\n",
       "              b.location  distance_KM  \n",
       "0  (-95.36327, 29.76328)  1415.017179  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "MATCH (a:City {name:\"Denver\"}), (b: City {name:\"Houston\"})\n",
    "WHERE a.elevation > 1000 AND b.elevation < 25 // meters\n",
    "RETURN a.name, a.elevation, a.location, b.name, b.elevation, b.location, distance(a.location, b.location) / 1000.0 as distance_KM\n",
    "\"\"\"\n",
    "graph.run(query).to_data_frame()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Find nearby locations"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Find nearby postal codes"
   ]
  },
  {
   "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>p1.name</th>\n",
       "      <th>p2.name</th>\n",
       "      <th>distance_KM</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>92123</td>\n",
       "      <td>92108</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>92123</td>\n",
       "      <td>92111</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>92123</td>\n",
       "      <td>92116</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>92123</td>\n",
       "      <td>92123</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>92123</td>\n",
       "      <td>92124</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  p1.name p2.name  distance_KM\n",
       "0   92123   92108            2\n",
       "1   92123   92111            2\n",
       "2   92123   92116            4\n",
       "3   92123   92123            0\n",
       "4   92123   92124            4"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "MATCH (p1:PostalCode{name:'92123'}), (p2:PostalCode)\n",
    "WHERE distance(p1.location, p2.location) <= 5000 // meters\n",
    "RETURN p1.name, p2.name, toInteger(distance(p1.location, p2.location)  / 1000.0) as distance_KM\n",
    "\"\"\"\n",
    "graph.run(query).to_data_frame()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Find nearby cities"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>c2.name</th>\n",
       "      <th>c2.location</th>\n",
       "      <th>distance_KM</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Fremont</td>\n",
       "      <td>(-121.98857, 37.54827)</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Hayward</td>\n",
       "      <td>(-122.0808, 37.66882)</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Newark</td>\n",
       "      <td>(-122.04024, 37.52966)</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Union City</td>\n",
       "      <td>(-122.01913, 37.59577)</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Fairview</td>\n",
       "      <td>(-122.0458, 37.67854)</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      c2.name             c2.location  distance_KM\n",
       "0     Fremont  (-121.98857, 37.54827)            5\n",
       "1     Hayward   (-122.0808, 37.66882)            9\n",
       "2      Newark  (-122.04024, 37.52966)            7\n",
       "3  Union City  (-122.01913, 37.59577)            0\n",
       "4    Fairview   (-122.0458, 37.67854)            9"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Example from: https://maxdemarzi.com/2018/03/21/neo4j-geospatial-queries/\n",
    "query = \"\"\"\n",
    "MATCH (c:City)-[:IN*]->(a:Admin1), (c2:City)\n",
    "WHERE c.name = \"Union City\"\n",
    "  AND a.name = \"California\"\n",
    "  AND distance(c.location, c2.location) <= 10000 // meters\n",
    "RETURN c2.name, c2.location, toInteger(distance(c.location, c2.location) / 1000.0) as distance_KM\n",
    "\"\"\"\n",
    "graph.run(query).to_data_frame()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}