notebooks/dataprep/00i-USCensusRegionDivisionState2017.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# US Census Bureau Region and Division Codes and State FIPS Codes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**[Work in progress]**\n",
    "\n",
    "This notebook creates .csv files with US regions, divisions, and assigns state FIPS codes for ingestion into the Knowledge Graph.\n",
    "\n",
    "![](../../docs/USRegionsDivisions.png)\n",
    "\n",
    "Data source: [2017 Census Bureau Region and Division Codes and State FIPS Codes](https://www.census.gov/geographies/reference-files/2017/demo/popest/2017-fips.html)\n",
    "\n",
    "Author: Peter Rose (pwrose@ucsd.edu)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "from pathlib import Path\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.options.display.max_rows = None  # display all rows\n",
    "pd.options.display.max_columns = None  # display all columsns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "/Users/peter/Library/Application Support/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": "markdown",
   "metadata": {},
   "source": [
    "### Create List of US Divisions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "census_url = 'https://www2.census.gov/programs-surveys/popest/geographies/2017/state-geocodes-v2017.xlsx'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_excel(census_url, dtype='str', skiprows=5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>Region</th>\n",
       "      <th>Division</th>\n",
       "      <th>State (FIPS)</th>\n",
       "      <th>Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>00</td>\n",
       "      <td>Northeast Region</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>00</td>\n",
       "      <td>New England Division</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>09</td>\n",
       "      <td>Connecticut</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>23</td>\n",
       "      <td>Maine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>25</td>\n",
       "      <td>Massachusetts</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Region Division State (FIPS)                  Name\n",
       "0      1        0           00      Northeast Region\n",
       "1      1        1           00  New England Division\n",
       "2      1        1           09           Connecticut\n",
       "3      1        1           23                 Maine\n",
       "4      1        1           25         Massachusetts"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.rename(columns={'State (FIPS)': 'fips'}, inplace=True)\n",
    "df.rename(columns={'Name': 'name'}, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Example"
   ]
  },
  {
   "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>Region</th>\n",
       "      <th>Division</th>\n",
       "      <th>fips</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>01</td>\n",
       "      <td>Alabama</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Region Division fips     name\n",
       "39      3        6   01  Alabama"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.query(\"name == 'Alabama'\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create list of US Regions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "regions = df.query(\"Division == '0'\").copy()\n",
    "regions.rename(columns={'Region': 'id'}, inplace=True)\n",
    "regions['id'] = 'US.' + regions['id']\n",
    "regions['parentId'] = 'US'\n",
    "regions = regions[['id', 'name', 'parentId']]"
   ]
  },
  {
   "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>id</th>\n",
       "      <th>name</th>\n",
       "      <th>parentId</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>US.1</td>\n",
       "      <td>Northeast Region</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>US.2</td>\n",
       "      <td>Midwest Region</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>US.3</td>\n",
       "      <td>South Region</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>US.4</td>\n",
       "      <td>West Region</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      id              name parentId\n",
       "0   US.1  Northeast Region       US\n",
       "12  US.2    Midwest Region       US\n",
       "27  US.3      South Region       US\n",
       "48  US.4       West Region       US"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "regions.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "regions.to_csv(NEO4J_IMPORT / \"00i-USCensus2017Region.csv\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create list of US Divisions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "divisions = df.query(\"Division != '0'\").query(\"fips == '00'\").copy()\n",
    "divisions.rename(columns={'Division': 'id'}, inplace=True)\n",
    "divisions['parentId'] = 'US.' + divisions['Region']\n",
    "divisions['id'] = 'US.' + divisions['Region']  + '.' + divisions['id']\n",
    "divisions = divisions[['id', 'name', 'parentId']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>parentId</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>US.1.1</td>\n",
       "      <td>New England Division</td>\n",
       "      <td>US.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>US.1.2</td>\n",
       "      <td>Middle Atlantic Division</td>\n",
       "      <td>US.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>US.2.3</td>\n",
       "      <td>East North Central Division</td>\n",
       "      <td>US.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>US.2.4</td>\n",
       "      <td>West North Central Division</td>\n",
       "      <td>US.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>US.3.5</td>\n",
       "      <td>South Atlantic Division</td>\n",
       "      <td>US.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>US.3.6</td>\n",
       "      <td>East South Central Division</td>\n",
       "      <td>US.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>US.3.7</td>\n",
       "      <td>West South Central Division</td>\n",
       "      <td>US.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>US.4.8</td>\n",
       "      <td>Mountain Division</td>\n",
       "      <td>US.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>US.4.9</td>\n",
       "      <td>Pacific Division</td>\n",
       "      <td>US.4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        id                         name parentId\n",
       "1   US.1.1         New England Division     US.1\n",
       "8   US.1.2     Middle Atlantic Division     US.1\n",
       "13  US.2.3  East North Central Division     US.2\n",
       "19  US.2.4  West North Central Division     US.2\n",
       "28  US.3.5      South Atlantic Division     US.3\n",
       "38  US.3.6  East South Central Division     US.3\n",
       "43  US.3.7  West South Central Division     US.3\n",
       "49  US.4.8            Mountain Division     US.4\n",
       "58  US.4.9             Pacific Division     US.4"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "divisions.head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "divisions.to_csv(NEO4J_IMPORT / \"00i-USCensus2017Division.csv\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create list of US State FIPS codes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "states = df.query(\"Division != '0'\").query(\"fips != '00'\").copy()\n",
    "states['parentId'] = 'US.' + states['Region']  + '.' + states['Division']\n",
    "states = states[['name', 'fips', 'parentId']]"
   ]
  },
  {
   "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>parentId</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Connecticut</td>\n",
       "      <td>09</td>\n",
       "      <td>US.1.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Maine</td>\n",
       "      <td>23</td>\n",
       "      <td>US.1.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Massachusetts</td>\n",
       "      <td>25</td>\n",
       "      <td>US.1.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>New Hampshire</td>\n",
       "      <td>33</td>\n",
       "      <td>US.1.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Rhode Island</td>\n",
       "      <td>44</td>\n",
       "      <td>US.1.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Vermont</td>\n",
       "      <td>50</td>\n",
       "      <td>US.1.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>New Jersey</td>\n",
       "      <td>34</td>\n",
       "      <td>US.1.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>New York</td>\n",
       "      <td>36</td>\n",
       "      <td>US.1.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Pennsylvania</td>\n",
       "      <td>42</td>\n",
       "      <td>US.1.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Illinois</td>\n",
       "      <td>17</td>\n",
       "      <td>US.2.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Indiana</td>\n",
       "      <td>18</td>\n",
       "      <td>US.2.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Michigan</td>\n",
       "      <td>26</td>\n",
       "      <td>US.2.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Ohio</td>\n",
       "      <td>39</td>\n",
       "      <td>US.2.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Wisconsin</td>\n",
       "      <td>55</td>\n",
       "      <td>US.2.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Iowa</td>\n",
       "      <td>19</td>\n",
       "      <td>US.2.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Kansas</td>\n",
       "      <td>20</td>\n",
       "      <td>US.2.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Minnesota</td>\n",
       "      <td>27</td>\n",
       "      <td>US.2.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Missouri</td>\n",
       "      <td>29</td>\n",
       "      <td>US.2.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Nebraska</td>\n",
       "      <td>31</td>\n",
       "      <td>US.2.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>North Dakota</td>\n",
       "      <td>38</td>\n",
       "      <td>US.2.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>South Dakota</td>\n",
       "      <td>46</td>\n",
       "      <td>US.2.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Delaware</td>\n",
       "      <td>10</td>\n",
       "      <td>US.3.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>District of Columbia</td>\n",
       "      <td>11</td>\n",
       "      <td>US.3.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>Florida</td>\n",
       "      <td>12</td>\n",
       "      <td>US.3.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>Georgia</td>\n",
       "      <td>13</td>\n",
       "      <td>US.3.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>Maryland</td>\n",
       "      <td>24</td>\n",
       "      <td>US.3.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>North Carolina</td>\n",
       "      <td>37</td>\n",
       "      <td>US.3.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>South Carolina</td>\n",
       "      <td>45</td>\n",
       "      <td>US.3.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>Virginia</td>\n",
       "      <td>51</td>\n",
       "      <td>US.3.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>West Virginia</td>\n",
       "      <td>54</td>\n",
       "      <td>US.3.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>01</td>\n",
       "      <td>US.3.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>Kentucky</td>\n",
       "      <td>21</td>\n",
       "      <td>US.3.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>Mississippi</td>\n",
       "      <td>28</td>\n",
       "      <td>US.3.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>Tennessee</td>\n",
       "      <td>47</td>\n",
       "      <td>US.3.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>Arkansas</td>\n",
       "      <td>05</td>\n",
       "      <td>US.3.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>Louisiana</td>\n",
       "      <td>22</td>\n",
       "      <td>US.3.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>Oklahoma</td>\n",
       "      <td>40</td>\n",
       "      <td>US.3.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>Texas</td>\n",
       "      <td>48</td>\n",
       "      <td>US.3.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>Arizona</td>\n",
       "      <td>04</td>\n",
       "      <td>US.4.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>Colorado</td>\n",
       "      <td>08</td>\n",
       "      <td>US.4.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>Idaho</td>\n",
       "      <td>16</td>\n",
       "      <td>US.4.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>Montana</td>\n",
       "      <td>30</td>\n",
       "      <td>US.4.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>Nevada</td>\n",
       "      <td>32</td>\n",
       "      <td>US.4.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>New Mexico</td>\n",
       "      <td>35</td>\n",
       "      <td>US.4.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>Utah</td>\n",
       "      <td>49</td>\n",
       "      <td>US.4.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>56</td>\n",
       "      <td>US.4.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>Alaska</td>\n",
       "      <td>02</td>\n",
       "      <td>US.4.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td>California</td>\n",
       "      <td>06</td>\n",
       "      <td>US.4.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61</th>\n",
       "      <td>Hawaii</td>\n",
       "      <td>15</td>\n",
       "      <td>US.4.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62</th>\n",
       "      <td>Oregon</td>\n",
       "      <td>41</td>\n",
       "      <td>US.4.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63</th>\n",
       "      <td>Washington</td>\n",
       "      <td>53</td>\n",
       "      <td>US.4.9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    name fips parentId\n",
       "2            Connecticut   09   US.1.1\n",
       "3                  Maine   23   US.1.1\n",
       "4          Massachusetts   25   US.1.1\n",
       "5          New Hampshire   33   US.1.1\n",
       "6           Rhode Island   44   US.1.1\n",
       "7                Vermont   50   US.1.1\n",
       "9             New Jersey   34   US.1.2\n",
       "10              New York   36   US.1.2\n",
       "11          Pennsylvania   42   US.1.2\n",
       "14              Illinois   17   US.2.3\n",
       "15               Indiana   18   US.2.3\n",
       "16              Michigan   26   US.2.3\n",
       "17                  Ohio   39   US.2.3\n",
       "18             Wisconsin   55   US.2.3\n",
       "20                  Iowa   19   US.2.4\n",
       "21                Kansas   20   US.2.4\n",
       "22             Minnesota   27   US.2.4\n",
       "23              Missouri   29   US.2.4\n",
       "24              Nebraska   31   US.2.4\n",
       "25          North Dakota   38   US.2.4\n",
       "26          South Dakota   46   US.2.4\n",
       "29              Delaware   10   US.3.5\n",
       "30  District of Columbia   11   US.3.5\n",
       "31               Florida   12   US.3.5\n",
       "32               Georgia   13   US.3.5\n",
       "33              Maryland   24   US.3.5\n",
       "34        North Carolina   37   US.3.5\n",
       "35        South Carolina   45   US.3.5\n",
       "36              Virginia   51   US.3.5\n",
       "37         West Virginia   54   US.3.5\n",
       "39               Alabama   01   US.3.6\n",
       "40              Kentucky   21   US.3.6\n",
       "41           Mississippi   28   US.3.6\n",
       "42             Tennessee   47   US.3.6\n",
       "44              Arkansas   05   US.3.7\n",
       "45             Louisiana   22   US.3.7\n",
       "46              Oklahoma   40   US.3.7\n",
       "47                 Texas   48   US.3.7\n",
       "50               Arizona   04   US.4.8\n",
       "51              Colorado   08   US.4.8\n",
       "52                 Idaho   16   US.4.8\n",
       "53               Montana   30   US.4.8\n",
       "54                Nevada   32   US.4.8\n",
       "55            New Mexico   35   US.4.8\n",
       "56                  Utah   49   US.4.8\n",
       "57               Wyoming   56   US.4.8\n",
       "59                Alaska   02   US.4.9\n",
       "60            California   06   US.4.9\n",
       "61                Hawaii   15   US.4.9\n",
       "62                Oregon   41   US.4.9\n",
       "63            Washington   53   US.4.9"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "states.head(100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "states.to_csv(NEO4J_IMPORT / \"00i-USCensus2017State.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.7.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}