<jupyter_start><jupyter_text>USDA Population Data<jupyter_code>import pandas as pd
import numpy as np
import os<jupyter_output><empty_output><jupyter_text>Data US Population data by countyEconomic Research Service  U.S. Department of Agriculture  link:  Contants<jupyter_code>"""
Duplicate check 3
from 
https://thispointer.com/python-3-ways-to-check-if-there-are-duplicates-in-a-list/
"""
def checkIfDuplicates_3(listOfElems):
    ''' Check if given list contains any duplicates '''    
    for elem in listOfElems:
        if listOfElems.count(elem) > 1:
            return True
    return False<jupyter_output><empty_output><jupyter_text>File managment<jupyter_code>path = "../data_raw/USDA_population/"
files = os.listdir(path)
# remove mac file 
#files.remove('.DS_Store')
files<jupyter_output><empty_output><jupyter_text>Example of the csv files<jupyter_code># random peek
df = pd.read_excel('../data_raw/USDA_population/PopulationReport (15).xlsx', skiprows=2)
df.shape
df.head()
df.tail()
df.iloc[:3,:8]
# columns for master data frame
columns = df.columns<jupyter_output><empty_output><jupyter_text>Create master DataFrame<jupyter_code># Concat 

# create master file 
master_df = pd.DataFrame(columns = columns)
state_name_list = []

# LOOP 
for file in files:
    # read excel file
    _df = pd.read_excel(path + file, skiprows=2)
    # read state_name
    state_name = _df.iloc[0,1]
    

    
# DROP
    #drop row 0
    _df.drop(0, inplace = True)
    
    # Drop last 2 rows 
    _df.drop(_df.tail(1).index, inplace = True)

    # work around to drop NaN column 
    _df = _df.iloc[:,:8]
    print(_df)
    break
    
# add Column for STATE name
    # add state column 
    _df['state'] = state_name
    state_name_list.append(state_name)
    
    # Concat 
    master_df = pd.concat([master_df, _df])
    
master_df.shape
master_df.head(2)<jupyter_output><empty_output><jupyter_text>Dataframe clean up<jupyter_code># reset Index
master_df.reset_index(drop = True, inplace = True )
master_df.columns
# Rename columns 
master_df.rename(columns = {'FIPS*':'FIPS'}, inplace = True)<jupyter_output><empty_output><jupyter_text>Remove rows with all nan's<jupyter_code>master_df.isna().sum()
master_df[ master_df['FIPS'].isnull()].head()
nan_rows = master_df[ master_df['FIPS'].isnull()].index
nan_rows
len(nan_rows)
# remove rows with all Nans
master_df.drop(nan_rows, inplace = True)
master_df.isna().sum()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12024539877300613, 'fraction_numerical': 0.007361963190184049, 'mean_word_length': 4.5972540045766594, 'pattern_counts': {'":': 0, '<': 29, '<?xml version=': 0, '>': 30, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, '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': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '208445', 'license': 'MIT', 'n_tokens_mistral': 924, 'n_tokens_neox': 891, 'n_words': 182, 'path': 'code/USDA notebooks /EDA_for_USDA_population.ipynb', 'repo_name': 'STRIDES-Codes/Supporting-Pandemic-Recovery-Building-insights-on-vaccine-hesitancy-in-the-communities-hardest-hit-', 'size': '2445'}