notebooks/dataprep/00j-USCensusCountyCity2017.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# US 2017 State, County, Minor Civil Division, and Incorporated Place FIPS Codes  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**[Work in progress]**\n",
    "\n",
    "This notebook creates .csv files with US 2017 State and County FIPS codes for ingestion into the Knowledge Graph.\n",
    "\n",
    "Data source: [2017 State, County, Minor Civil Division, and Incorporated Place FIPS Codes](https://www2.census.gov/programs-surveys/popest/geographies/2017/all-geocodes-v2017.xlsx)\n",
    "\n",
    "Author: Peter Rose (pwrose@ucsd.edu)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "from pathlib import Path\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.options.display.max_rows = None  # display all rows\n",
    "pd.options.display.max_columns = None  # display all columsns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "/Users/peter/Library/Application Support/Neo4j Desktop/Application/neo4jDatabases/database-19636412-9e74-4bac-8a4c-c6c8b49bb9d3/installation-4.1.0/import\n"
     ]
    }
   ],
   "source": [
    "NEO4J_IMPORT = Path(os.getenv('NEO4J_IMPORT'))\n",
    "print(NEO4J_IMPORT)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "census_url = 'https://www2.census.gov/programs-surveys/popest/geographies/2017/all-geocodes-v2017.xlsx'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_excel(census_url, dtype='str', skiprows=4)"
   ]
  },
  {
   "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>Summary Level</th>\n",
       "      <th>State Code (FIPS)</th>\n",
       "      <th>County Code (FIPS)</th>\n",
       "      <th>County Subdivision Code (FIPS)</th>\n",
       "      <th>Place Code (FIPS)</th>\n",
       "      <th>Consolidtated City Code (FIPS)</th>\n",
       "      <th>Area Name (including legal/statistical area description)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>43831</th>\n",
       "      <td>040</td>\n",
       "      <td>72</td>\n",
       "      <td>000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Puerto Rico</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43832</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>001</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Adjuntas Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43833</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>003</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Aguada Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43834</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>005</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Aguadilla Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43835</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>007</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Aguas Buenas Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43836</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>009</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Aibonito Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43837</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>011</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Añasco Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43838</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>013</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Arecibo Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43839</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>015</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Arroyo Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43840</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>017</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Barceloneta Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43841</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>019</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Barranquitas Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43842</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>021</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Bayamón Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43843</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>023</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Cabo Rojo Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43844</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>025</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Caguas Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43845</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>027</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Camuy Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43846</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>029</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Canóvanas Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43847</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>031</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Carolina Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43848</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>033</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Cataño Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43849</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>035</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Cayey Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43850</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>037</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Ceiba Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43851</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>039</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Ciales Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43852</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>041</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Cidra Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43853</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>043</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Coamo Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43854</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>045</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Comerío Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43855</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>047</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Corozal Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43856</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>049</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Culebra Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43857</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>051</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Dorado Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43858</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>053</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Fajardo Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43859</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>054</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Florida Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43860</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>055</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Guánica Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43861</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>057</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Guayama Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43862</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>059</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Guayanilla Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43863</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>061</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Guaynabo Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43864</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>063</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Gurabo Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43865</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>065</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Hatillo Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43866</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>067</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Hormigueros Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43867</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>069</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Humacao Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43868</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>071</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Isabela Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43869</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>073</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Jayuya Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43870</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>075</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Juana Díaz Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43871</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>077</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Juncos Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43872</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>079</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Lajas Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43873</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>081</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Lares Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43874</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>083</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Las Marías Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43875</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>085</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Las Piedras Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43876</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>087</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Loíza Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43877</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>089</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Luquillo Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43878</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>091</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Manatí Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43879</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>093</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Maricao Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43880</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>095</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Maunabo Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43881</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>097</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Mayagüez Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43882</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>099</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Moca Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43883</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>101</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Morovis Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43884</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>103</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Naguabo Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43885</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>105</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Naranjito Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43886</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>107</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Orocovis Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43887</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>109</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Patillas Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43888</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>111</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Peñuelas Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43889</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>113</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Ponce Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43890</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>115</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Quebradillas Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43891</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>117</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Rincón Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43892</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>119</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Río Grande Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43893</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>121</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Sabana Grande Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43894</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>123</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Salinas Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43895</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>125</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>San Germán Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43896</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>127</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>San Juan Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43897</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>129</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>San Lorenzo Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43898</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>131</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>San Sebastián Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43899</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>133</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Santa Isabel Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43900</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>135</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Toa Alta Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43901</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>137</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Toa Baja Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43902</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>139</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Trujillo Alto Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43903</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>141</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Utuado Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43904</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>143</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Vega Alta Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43905</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>145</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Vega Baja Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43906</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>147</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Vieques Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43907</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>149</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Villalba Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43908</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>151</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Yabucoa Municipio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43909</th>\n",
       "      <td>050</td>\n",
       "      <td>72</td>\n",
       "      <td>153</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>00000</td>\n",
       "      <td>Yauco Municipio</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Summary Level State Code (FIPS) County Code (FIPS)  \\\n",
       "43831           040                72                000   \n",
       "43832           050                72                001   \n",
       "43833           050                72                003   \n",
       "43834           050                72                005   \n",
       "43835           050                72                007   \n",
       "43836           050                72                009   \n",
       "43837           050                72                011   \n",
       "43838           050                72                013   \n",
       "43839           050                72                015   \n",
       "43840           050                72                017   \n",
       "43841           050                72                019   \n",
       "43842           050                72                021   \n",
       "43843           050                72                023   \n",
       "43844           050                72                025   \n",
       "43845           050                72                027   \n",
       "43846           050                72                029   \n",
       "43847           050                72                031   \n",
       "43848           050                72                033   \n",
       "43849           050                72                035   \n",
       "43850           050                72                037   \n",
       "43851           050                72                039   \n",
       "43852           050                72                041   \n",
       "43853           050                72                043   \n",
       "43854           050                72                045   \n",
       "43855           050                72                047   \n",
       "43856           050                72                049   \n",
       "43857           050                72                051   \n",
       "43858           050                72                053   \n",
       "43859           050                72                054   \n",
       "43860           050                72                055   \n",
       "43861           050                72                057   \n",
       "43862           050                72                059   \n",
       "43863           050                72                061   \n",
       "43864           050                72                063   \n",
       "43865           050                72                065   \n",
       "43866           050                72                067   \n",
       "43867           050                72                069   \n",
       "43868           050                72                071   \n",
       "43869           050                72                073   \n",
       "43870           050                72                075   \n",
       "43871           050                72                077   \n",
       "43872           050                72                079   \n",
       "43873           050                72                081   \n",
       "43874           050                72                083   \n",
       "43875           050                72                085   \n",
       "43876           050                72                087   \n",
       "43877           050                72                089   \n",
       "43878           050                72                091   \n",
       "43879           050                72                093   \n",
       "43880           050                72                095   \n",
       "43881           050                72                097   \n",
       "43882           050                72                099   \n",
       "43883           050                72                101   \n",
       "43884           050                72                103   \n",
       "43885           050                72                105   \n",
       "43886           050                72                107   \n",
       "43887           050                72                109   \n",
       "43888           050                72                111   \n",
       "43889           050                72                113   \n",
       "43890           050                72                115   \n",
       "43891           050                72                117   \n",
       "43892           050                72                119   \n",
       "43893           050                72                121   \n",
       "43894           050                72                123   \n",
       "43895           050                72                125   \n",
       "43896           050                72                127   \n",
       "43897           050                72                129   \n",
       "43898           050                72                131   \n",
       "43899           050                72                133   \n",
       "43900           050                72                135   \n",
       "43901           050                72                137   \n",
       "43902           050                72                139   \n",
       "43903           050                72                141   \n",
       "43904           050                72                143   \n",
       "43905           050                72                145   \n",
       "43906           050                72                147   \n",
       "43907           050                72                149   \n",
       "43908           050                72                151   \n",
       "43909           050                72                153   \n",
       "\n",
       "      County Subdivision Code (FIPS) Place Code (FIPS)  \\\n",
       "43831                          00000             00000   \n",
       "43832                          00000             00000   \n",
       "43833                          00000             00000   \n",
       "43834                          00000             00000   \n",
       "43835                          00000             00000   \n",
       "43836                          00000             00000   \n",
       "43837                          00000             00000   \n",
       "43838                          00000             00000   \n",
       "43839                          00000             00000   \n",
       "43840                          00000             00000   \n",
       "43841                          00000             00000   \n",
       "43842                          00000             00000   \n",
       "43843                          00000             00000   \n",
       "43844                          00000             00000   \n",
       "43845                          00000             00000   \n",
       "43846                          00000             00000   \n",
       "43847                          00000             00000   \n",
       "43848                          00000             00000   \n",
       "43849                          00000             00000   \n",
       "43850                          00000             00000   \n",
       "43851                          00000             00000   \n",
       "43852                          00000             00000   \n",
       "43853                          00000             00000   \n",
       "43854                          00000             00000   \n",
       "43855                          00000             00000   \n",
       "43856                          00000             00000   \n",
       "43857                          00000             00000   \n",
       "43858                          00000             00000   \n",
       "43859                          00000             00000   \n",
       "43860                          00000             00000   \n",
       "43861                          00000             00000   \n",
       "43862                          00000             00000   \n",
       "43863                          00000             00000   \n",
       "43864                          00000             00000   \n",
       "43865                          00000             00000   \n",
       "43866                          00000             00000   \n",
       "43867                          00000             00000   \n",
       "43868                          00000             00000   \n",
       "43869                          00000             00000   \n",
       "43870                          00000             00000   \n",
       "43871                          00000             00000   \n",
       "43872                          00000             00000   \n",
       "43873                          00000             00000   \n",
       "43874                          00000             00000   \n",
       "43875                          00000             00000   \n",
       "43876                          00000             00000   \n",
       "43877                          00000             00000   \n",
       "43878                          00000             00000   \n",
       "43879                          00000             00000   \n",
       "43880                          00000             00000   \n",
       "43881                          00000             00000   \n",
       "43882                          00000             00000   \n",
       "43883                          00000             00000   \n",
       "43884                          00000             00000   \n",
       "43885                          00000             00000   \n",
       "43886                          00000             00000   \n",
       "43887                          00000             00000   \n",
       "43888                          00000             00000   \n",
       "43889                          00000             00000   \n",
       "43890                          00000             00000   \n",
       "43891                          00000             00000   \n",
       "43892                          00000             00000   \n",
       "43893                          00000             00000   \n",
       "43894                          00000             00000   \n",
       "43895                          00000             00000   \n",
       "43896                          00000             00000   \n",
       "43897                          00000             00000   \n",
       "43898                          00000             00000   \n",
       "43899                          00000             00000   \n",
       "43900                          00000             00000   \n",
       "43901                          00000             00000   \n",
       "43902                          00000             00000   \n",
       "43903                          00000             00000   \n",
       "43904                          00000             00000   \n",
       "43905                          00000             00000   \n",
       "43906                          00000             00000   \n",
       "43907                          00000             00000   \n",
       "43908                          00000             00000   \n",
       "43909                          00000             00000   \n",
       "\n",
       "      Consolidtated City Code (FIPS)  \\\n",
       "43831                          00000   \n",
       "43832                          00000   \n",
       "43833                          00000   \n",
       "43834                          00000   \n",
       "43835                          00000   \n",
       "43836                          00000   \n",
       "43837                          00000   \n",
       "43838                          00000   \n",
       "43839                          00000   \n",
       "43840                          00000   \n",
       "43841                          00000   \n",
       "43842                          00000   \n",
       "43843                          00000   \n",
       "43844                          00000   \n",
       "43845                          00000   \n",
       "43846                          00000   \n",
       "43847                          00000   \n",
       "43848                          00000   \n",
       "43849                          00000   \n",
       "43850                          00000   \n",
       "43851                          00000   \n",
       "43852                          00000   \n",
       "43853                          00000   \n",
       "43854                          00000   \n",
       "43855                          00000   \n",
       "43856                          00000   \n",
       "43857                          00000   \n",
       "43858                          00000   \n",
       "43859                          00000   \n",
       "43860                          00000   \n",
       "43861                          00000   \n",
       "43862                          00000   \n",
       "43863                          00000   \n",
       "43864                          00000   \n",
       "43865                          00000   \n",
       "43866                          00000   \n",
       "43867                          00000   \n",
       "43868                          00000   \n",
       "43869                          00000   \n",
       "43870                          00000   \n",
       "43871                          00000   \n",
       "43872                          00000   \n",
       "43873                          00000   \n",
       "43874                          00000   \n",
       "43875                          00000   \n",
       "43876                          00000   \n",
       "43877                          00000   \n",
       "43878                          00000   \n",
       "43879                          00000   \n",
       "43880                          00000   \n",
       "43881                          00000   \n",
       "43882                          00000   \n",
       "43883                          00000   \n",
       "43884                          00000   \n",
       "43885                          00000   \n",
       "43886                          00000   \n",
       "43887                          00000   \n",
       "43888                          00000   \n",
       "43889                          00000   \n",
       "43890                          00000   \n",
       "43891                          00000   \n",
       "43892                          00000   \n",
       "43893                          00000   \n",
       "43894                          00000   \n",
       "43895                          00000   \n",
       "43896                          00000   \n",
       "43897                          00000   \n",
       "43898                          00000   \n",
       "43899                          00000   \n",
       "43900                          00000   \n",
       "43901                          00000   \n",
       "43902                          00000   \n",
       "43903                          00000   \n",
       "43904                          00000   \n",
       "43905                          00000   \n",
       "43906                          00000   \n",
       "43907                          00000   \n",
       "43908                          00000   \n",
       "43909                          00000   \n",
       "\n",
       "      Area Name (including legal/statistical area description)  \n",
       "43831                                        Puerto Rico        \n",
       "43832                                 Adjuntas Municipio        \n",
       "43833                                   Aguada Municipio        \n",
       "43834                                Aguadilla Municipio        \n",
       "43835                             Aguas Buenas Municipio        \n",
       "43836                                 Aibonito Municipio        \n",
       "43837                                   Añasco Municipio        \n",
       "43838                                  Arecibo Municipio        \n",
       "43839                                   Arroyo Municipio        \n",
       "43840                              Barceloneta Municipio        \n",
       "43841                             Barranquitas Municipio        \n",
       "43842                                  Bayamón Municipio        \n",
       "43843                                Cabo Rojo Municipio        \n",
       "43844                                   Caguas Municipio        \n",
       "43845                                    Camuy Municipio        \n",
       "43846                                Canóvanas Municipio        \n",
       "43847                                 Carolina Municipio        \n",
       "43848                                   Cataño Municipio        \n",
       "43849                                    Cayey Municipio        \n",
       "43850                                    Ceiba Municipio        \n",
       "43851                                   Ciales Municipio        \n",
       "43852                                    Cidra Municipio        \n",
       "43853                                    Coamo Municipio        \n",
       "43854                                  Comerío Municipio        \n",
       "43855                                  Corozal Municipio        \n",
       "43856                                  Culebra Municipio        \n",
       "43857                                   Dorado Municipio        \n",
       "43858                                  Fajardo Municipio        \n",
       "43859                                  Florida Municipio        \n",
       "43860                                  Guánica Municipio        \n",
       "43861                                  Guayama Municipio        \n",
       "43862                               Guayanilla Municipio        \n",
       "43863                                 Guaynabo Municipio        \n",
       "43864                                   Gurabo Municipio        \n",
       "43865                                  Hatillo Municipio        \n",
       "43866                              Hormigueros Municipio        \n",
       "43867                                  Humacao Municipio        \n",
       "43868                                  Isabela Municipio        \n",
       "43869                                   Jayuya Municipio        \n",
       "43870                               Juana Díaz Municipio        \n",
       "43871                                   Juncos Municipio        \n",
       "43872                                    Lajas Municipio        \n",
       "43873                                    Lares Municipio        \n",
       "43874                               Las Marías Municipio        \n",
       "43875                              Las Piedras Municipio        \n",
       "43876                                    Loíza Municipio        \n",
       "43877                                 Luquillo Municipio        \n",
       "43878                                   Manatí Municipio        \n",
       "43879                                  Maricao Municipio        \n",
       "43880                                  Maunabo Municipio        \n",
       "43881                                 Mayagüez Municipio        \n",
       "43882                                     Moca Municipio        \n",
       "43883                                  Morovis Municipio        \n",
       "43884                                  Naguabo Municipio        \n",
       "43885                                Naranjito Municipio        \n",
       "43886                                 Orocovis Municipio        \n",
       "43887                                 Patillas Municipio        \n",
       "43888                                 Peñuelas Municipio        \n",
       "43889                                    Ponce Municipio        \n",
       "43890                             Quebradillas Municipio        \n",
       "43891                                   Rincón Municipio        \n",
       "43892                               Río Grande Municipio        \n",
       "43893                            Sabana Grande Municipio        \n",
       "43894                                  Salinas Municipio        \n",
       "43895                               San Germán Municipio        \n",
       "43896                                 San Juan Municipio        \n",
       "43897                              San Lorenzo Municipio        \n",
       "43898                            San Sebastián Municipio        \n",
       "43899                             Santa Isabel Municipio        \n",
       "43900                                 Toa Alta Municipio        \n",
       "43901                                 Toa Baja Municipio        \n",
       "43902                            Trujillo Alto Municipio        \n",
       "43903                                   Utuado Municipio        \n",
       "43904                                Vega Alta Municipio        \n",
       "43905                                Vega Baja Municipio        \n",
       "43906                                  Vieques Municipio        \n",
       "43907                                 Villalba Municipio        \n",
       "43908                                  Yabucoa Municipio        \n",
       "43909                                    Yauco Municipio        "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# TODO how to handle data for Puerto Rico (considered a country by GeoNames and state by US Census)?\n",
    "df.query(\"`State Code (FIPS)` > '56'\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.rename(columns={'Area Name (including legal/statistical area description)': 'name'}, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create list of US Counties"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "counties = df.query(\"`County Code (FIPS)` != '000'\").query(\"`County Subdivision Code (FIPS)` == '00000'\").copy()\n",
    "counties.rename(columns={'County Code (FIPS)': 'fips'}, inplace=True)\n",
    "counties['stateFips'] = counties['State Code (FIPS)']\n",
    "counties = counties[['name', 'fips', 'stateFips']]\n",
    "counties['geoId'] = counties['stateFips'] + counties['fips']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['01', '02', '04', '05', '06', '08', '09', '10', '11', '12', '13',\n",
       "       '15', '16', '17', '18', '19', '20', '21', '22', '23', '24', '25',\n",
       "       '26', '27', '28', '29', '30', '31', '32', '33', '34', '35', '36',\n",
       "       '37', '38', '39', '40', '41', '42', '44', '45', '46', '47', '48',\n",
       "       '49', '50', '51', '53', '54', '55', '56', '72'], dtype=object)"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "counties['stateFips'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(3142, 4)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "counties.query(\"stateFips != '72'\").shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>name</th>\n",
       "      <th>fips</th>\n",
       "      <th>stateFips</th>\n",
       "      <th>geoId</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Autauga County</td>\n",
       "      <td>001</td>\n",
       "      <td>01</td>\n",
       "      <td>01001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Baldwin County</td>\n",
       "      <td>003</td>\n",
       "      <td>01</td>\n",
       "      <td>01003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Barbour County</td>\n",
       "      <td>005</td>\n",
       "      <td>01</td>\n",
       "      <td>01005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Bibb County</td>\n",
       "      <td>007</td>\n",
       "      <td>01</td>\n",
       "      <td>01007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Blount County</td>\n",
       "      <td>009</td>\n",
       "      <td>01</td>\n",
       "      <td>01009</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             name fips stateFips  geoId\n",
       "2  Autauga County  001        01  01001\n",
       "3  Baldwin County  003        01  01003\n",
       "4  Barbour County  005        01  01005\n",
       "5     Bibb County  007        01  01007\n",
       "6   Blount County  009        01  01009"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "counties.head()"
   ]
  },
  {
   "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>name</th>\n",
       "      <th>fips</th>\n",
       "      <th>stateFips</th>\n",
       "      <th>geoId</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Covington County</td>\n",
       "      <td>039</td>\n",
       "      <td>01</td>\n",
       "      <td>01039</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                name fips stateFips  geoId\n",
       "21  Covington County  039        01  01039"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "counties.query(\"fips == '039'\").query(\"stateFips == '01'\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "counties.to_csv(NEO4J_IMPORT / \"00j-USCensus2017County.csv\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create list of US Cities"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "cities = df.query(\"`Place Code (FIPS)` != '00000'\").copy()\n",
    "cities.rename(columns={'Place Code (FIPS)': 'fips'}, inplace=True)\n",
    "cities.rename(columns={'State Code (FIPS)': 'stateFips'}, inplace=True)\n",
    "cities = cities[['name', 'fips', 'stateFips']]"
   ]
  },
  {
   "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>name</th>\n",
       "      <th>fips</th>\n",
       "      <th>stateFips</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>69</th>\n",
       "      <td>Abbeville city</td>\n",
       "      <td>00124</td>\n",
       "      <td>01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>Adamsville city</td>\n",
       "      <td>00460</td>\n",
       "      <td>01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71</th>\n",
       "      <td>Addison town</td>\n",
       "      <td>00484</td>\n",
       "      <td>01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72</th>\n",
       "      <td>Akron town</td>\n",
       "      <td>00676</td>\n",
       "      <td>01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73</th>\n",
       "      <td>Alabaster city</td>\n",
       "      <td>00820</td>\n",
       "      <td>01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               name   fips stateFips\n",
       "69   Abbeville city  00124        01\n",
       "70  Adamsville city  00460        01\n",
       "71     Addison town  00484        01\n",
       "72       Akron town  00676        01\n",
       "73   Alabaster city  00820        01"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cities.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Remove lower case city or town, they are not part of the city name. This does not effect city names that have \"City\" as part of their name, e.g., Atlantic City."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>name</th>\n",
       "      <th>fips</th>\n",
       "      <th>stateFips</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>69</th>\n",
       "      <td>Abbeville</td>\n",
       "      <td>00124</td>\n",
       "      <td>01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>Adamsville</td>\n",
       "      <td>00460</td>\n",
       "      <td>01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71</th>\n",
       "      <td>Addison</td>\n",
       "      <td>00484</td>\n",
       "      <td>01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72</th>\n",
       "      <td>Akron</td>\n",
       "      <td>00676</td>\n",
       "      <td>01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73</th>\n",
       "      <td>Alabaster</td>\n",
       "      <td>00820</td>\n",
       "      <td>01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          name   fips stateFips\n",
       "69   Abbeville  00124        01\n",
       "70  Adamsville  00460        01\n",
       "71     Addison  00484        01\n",
       "72       Akron  00676        01\n",
       "73   Alabaster  00820        01"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cities['name'] = cities['name'].str.replace(' city','')\n",
    "cities['name'] = cities['name'].str.replace(' town','')\n",
    "cities.head()"
   ]
  },
  {
   "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>name</th>\n",
       "      <th>fips</th>\n",
       "      <th>stateFips</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>23490</th>\n",
       "      <td>Atlantic City</td>\n",
       "      <td>02080</td>\n",
       "      <td>34</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                name   fips stateFips\n",
       "23490  Atlantic City  02080        34"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cities.query(\"name == 'Atlantic City'\")"
   ]
  },
  {
   "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>name</th>\n",
       "      <th>fips</th>\n",
       "      <th>stateFips</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3306</th>\n",
       "      <td>Covington</td>\n",
       "      <td>20064</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8018</th>\n",
       "      <td>Covington</td>\n",
       "      <td>15490</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11936</th>\n",
       "      <td>Covington</td>\n",
       "      <td>17848</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12393</th>\n",
       "      <td>Covington</td>\n",
       "      <td>18125</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31243</th>\n",
       "      <td>Covington</td>\n",
       "      <td>17700</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37811</th>\n",
       "      <td>Covington</td>\n",
       "      <td>17680</td>\n",
       "      <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38588</th>\n",
       "      <td>Covington</td>\n",
       "      <td>17372</td>\n",
       "      <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40337</th>\n",
       "      <td>Covington</td>\n",
       "      <td>19728</td>\n",
       "      <td>51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40603</th>\n",
       "      <td>Covington</td>\n",
       "      <td>15290</td>\n",
       "      <td>53</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            name   fips stateFips\n",
       "3306   Covington  20064        13\n",
       "8018   Covington  15490        18\n",
       "11936  Covington  17848        21\n",
       "12393  Covington  18125        22\n",
       "31243  Covington  17700        40\n",
       "37811  Covington  17680        47\n",
       "38588  Covington  17372        48\n",
       "40337  Covington  19728        51\n",
       "40603  Covington  15290        53"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cities.query(\"name == 'Covington'\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "cities.to_csv(NEO4J_IMPORT / \"00j-USCensus2017City.csv\", index=False)"
   ]
  }
 ],
 "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.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}