<jupyter_start><jupyter_text>US Census Bureau Region and Division Codes and State FIPS Codes **[Work in progress]**This notebook creates .csv files with US regions, divisions, and assigns state FIPS codes for ingestion into the Knowledge Graph.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)Author: <NAME> (<EMAIL>)<jupyter_code>import os
from pathlib import Path
import pandas as pd
pd.options.display.max_rows = None  # display all rows
pd.options.display.max_columns = None  # display all columsns
NEO4J_HOME = Path(os.getenv('NEO4J_HOME'))
print(NEO4J_HOME)<jupyter_output>/Users/peter/Library/Application Support/Neo4j Desktop/Application/neo4jDatabases/database-4af96121-2328-4e2f-ba60-6d8b728a26d5/installation-4.0.3<jupyter_text>Create List of US Divisions<jupyter_code>census_url = 'https://www2.census.gov/programs-surveys/popest/geographies/2017/state-geocodes-v2017.xlsx'
df = pd.read_excel(census_url, dtype='str', skiprows=5)
df.head()
df.rename(columns={'State (FIPS)': 'fips'}, inplace=True)
df.rename(columns={'Name': 'name'}, inplace=True)<jupyter_output><empty_output><jupyter_text>Example<jupyter_code>df.query("name == 'California'")<jupyter_output><empty_output><jupyter_text>Create list of US Regions<jupyter_code>regions = df.query("Division == '0'").copy()
regions.rename(columns={'Region': 'id'}, inplace=True)
regions['id'] = 'US.' + regions['id']
regions['parentId'] = 'US'
regions = regions[['id', 'name', 'parentId']]
regions.head()
regions.to_csv(NEO4J_HOME / "import/00i-USCensus2017Region.csv", index=False)<jupyter_output><empty_output><jupyter_text>Create list of US Divisions<jupyter_code>divisions = df.query("Division != '0'").query("fips == '00'").copy()
divisions.rename(columns={'Division': 'id'}, inplace=True)
divisions['parentId'] = 'US.' + divisions['Region']
divisions['id'] = 'US.' + divisions['Region']  + '.' + divisions['id']
divisions = divisions[['id', 'name', 'parentId']]
divisions.head(20)
divisions.to_csv(NEO4J_HOME / "import/00i-USCensus2017Division.csv", index=False)<jupyter_output><empty_output><jupyter_text>Create list of US State FIPS codes<jupyter_code>states = df.query("Division != '0'").query("fips != '00'").copy()
states['parentId'] = 'US.' + states['Region']  + '.' + states['Division']
states = states[['name', 'fips', 'parentId']]
states.head(100)
states.to_csv(NEO4J_HOME / "import/00i-USCensus2017State.csv", index=False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.16869838773102636, 'fraction_numerical': 0.03342508847817539, 'mean_word_length': 7.802768166089965, 'pattern_counts': {'":': 0, '<': 26, '<?xml version=': 0, '>': 26, 'https://': 2, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '200077', 'license': 'MIT', 'n_tokens_mistral': 977, 'n_tokens_neox': 920, 'n_words': 157, 'path': 'notebooks/00i-USCensusRegionDivisionState2017.ipynb', 'repo_name': 'Krganapa/covid-19-community', 'size': '2555'}