<jupyter_start><jupyter_text>County Supplemental Data<jupyter_code>""" Notebook Setup/Imports
"""

# Project path setup
from pathlib import Path
project_dir = Path(__name__).resolve().parents[1]

# Library import
import pandas as pd
import numpy as np

# Print strings as markdown
from IPython.display import Markdown

# Data variables from previous notebook
dataset = pd.read_pickle(Path.joinpath(project_dir, "data/processed/1_data.pickle"))
data_dictionary = pd.read_pickle(Path.joinpath(project_dir, "data/processed/1_data_dictionary.pickle"))<jupyter_output><empty_output><jupyter_text>Additional County Data> https://www.ers.usda.gov/data-products/county-level-data-sets/download-data/<jupyter_code>education = pd.read_csv("../data/external/Education.csv",
                                 encoding='latin-1').set_index("FIPS Code")
poverty = pd.read_csv("../data/external/PovertyEstimates.csv",
                                 encoding='latin-1').set_index("FIPStxt")
unemployment = pd.read_excel("../data/external/Unemployment.xls", skiprows=7,
                                 encoding='latin-1').set_index("FIPStxt")
ed_keep_cols = []
ed_cols = education.columns.values.tolist()
for col in ed_cols:
    if (col.find("1970") < 0 and col.find("1980") < 0 
        and col.find("1990") < 0 and col.find("2000") < 0
       and col.find("2003") < 0 and col != 'State'
       and col != "Area name" and col.find("2013 ") < 0
       and col.find("Unnamed") < 0):
        ed_keep_cols.append(col)
education = education[ed_keep_cols]
pov_keep_cols = []
pov_cols = poverty.columns.values.tolist()
for col in pov_cols:
    if (col.find("1970") < 0 and col.find("1980") < 0 
        and col.find("1990") < 0 and col.find("2000") < 0
       and col.find("2003") < 0 and col != 'State'
       and col.find("Area") < 0 and col.find("2013 ") < 0
       and col.find("Unnamed") < 0 and col.find("_2013") < 0):
        pov_keep_cols.append(col)
poverty = poverty[pov_keep_cols]
unemployment_keep_cols = []
unemployment_cols = unemployment.columns.values.tolist()
for col in unemployment_cols:
    if (col.find("1970") < 0 and col.find("1980") < 0 
        and col.find("1990") < 0 and col.find("2000") < 0
       and col.find("2003") < 0 and col != 'State'
       and col.find("Area") < 0 and col.find("2013 ") < 0
       and col.find("Unnamed") < 0 and col.find("_2013") < 0
       and col.find("_2007") < 0 and col.find("_2008") < 0
       and col.find("_2009") < 0 and col.find("_2010") < 0
       and col.find("_2011") < 0 and col.find("_2012") < 0
       and col.find("_2014") < 0 and col.find("_2016") < 0
       and col.find("_2017") < 0):
        unemployment_keep_cols.append(col)
unemployment = unemployment[unemployment_keep_cols]
county_data = education.join(
    poverty,rsuffix="_").join(
    unemployment,rsuffix="_")
full_dataset = dataset.join(county_data)
pov_dd = pd.read_excel("../data/external/PovertyEstimates.xls",sheet_name=1).loc[:33]
pov_dd = pov_dd.rename({
    "Column variable name":"Variable Code",
    "Description":"Variable Name"
},axis="columns").drop("Notes",axis="columns")
data_dictionary = pd.concat([data_dictionary,pov_dd],sort=False)
un_dd = pd.read_excel("../data/external/Unemployment.xls",sheet_name=1)
un_dd = un_dd.loc[:51].rename({
    "Variable":"Variable Code",
    "Description":"Variable Name"
},axis="columns")
data_dictionary = pd.concat([data_dictionary,un_dd],sort=False)
# Save data
full_dataset.to_pickle("../data/production/data.pickle")
data_dictionary.to_pickle("../data/production/data_dictionary.pickle")
# Save data
full_dataset.to_csv("../data/production/data.csv")
data_dictionary.to_csv("../data/production/data_dictionary.csv")<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.16034436373419425, 'fraction_numerical': 0.04385256927629809, 'mean_word_length': 3.9310344827586206, 'pattern_counts': {'":': 4, '<': 43, '<?xml version=': 0, '>': 10, 'https://': 1, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 4, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '2', 'dir': 'jupyter-structured-clean-dedup', 'id': '466997', 'license': 'MIT', 'n_tokens_mistral': 1398, 'n_tokens_neox': 1248, 'n_words': 239, 'path': 'notebooks/2.ipynb', 'repo_name': 'noahbjohnson/DS420', 'size': '3717'}