<jupyter_start><jupyter_text>US 2017 State, County, Minor Civil Division, and Incorporated Place FIPS Codes   **[Work in progress]**This notebook creates .csv files with US 2017 State and County FIPS codes for ingestion into the Knowledge Graph.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)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)
census_url = 'https://www2.census.gov/programs-surveys/popest/geographies/2017/all-geocodes-v2017.xlsx'
df = pd.read_excel(census_url, dtype='str', skiprows=4)
df.head()
df.rename(columns={'Area Name (including legal/statistical area description)': 'name'}, inplace=True)<jupyter_output><empty_output><jupyter_text>Create list of US Counties<jupyter_code>counties = df.query("`County Code (FIPS)` != '000'").query("`County Subdivision Code (FIPS)` == '00000'").copy()
counties.rename(columns={'County Code (FIPS)': 'fips'}, inplace=True)
counties['stateFips'] = counties['State Code (FIPS)']
counties = counties[['name', 'fips', 'stateFips']]
counties.count()
counties.head()
counties.query("fips == '039'").query("stateFips == '01'")
counties.to_csv(NEO4J_HOME / "import/00j-USCensus2017County.csv", index=False)<jupyter_output><empty_output><jupyter_text>Create list of US Cities<jupyter_code>cities = df.query("`Place Code (FIPS)` != '00000'").copy()
cities.rename(columns={'Place Code (FIPS)': 'fips'}, inplace=True)
cities['stateFips'] = cities['State Code (FIPS)']
cities = cities[['name', 'fips', 'stateFips']]
cities.count()
cities.head()
cities['name'] = cities['name'].str.replace(' city','')
cities['name'] = cities['name'].str.replace(' town','')
cities.head()
cities.query("name == 'Covington'")
cities.to_csv(NEO4J_HOME / "import/00j-USCensus2017City.csv", index=False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.16927453769559034, 'fraction_numerical': 0.031294452347083924, 'mean_word_length': 7.5425101214574894, 'pattern_counts': {'":': 0, '<': 15, '<?xml version=': 0, '>': 15, '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': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '537888', 'license': 'MIT', 'n_tokens_mistral': 816, 'n_tokens_neox': 754, 'n_words': 152, 'path': 'notebooks/00j-USCensusCountyCity2017.ipynb', 'repo_name': 'Krganapa/covid-19-community', 'size': '2121'}