<jupyter_start><jupyter_text>This page documents the collection of data from the US Census website1.  Download raw data2.  Download and attach column names to codes3.  General cleaning of dataframe<jupyter_code># imports
import pandas as pd
import requests
import pickle as pkl
key = 'YOUR KEY HERE'<jupyter_output><empty_output><jupyter_text>video:  https://www.youtube.com/watch?v=K0-ifZS0mQI&feature=emb_title&ab_channel=U.S.CensusBureauhttps://www.census.gov/data/developers/data-sets/acs-5year.htmlData ProfilesExample Call: api.census.gov/data/2018/acs/acs5/profile?get=group(DP02)&for=us:1&key=YOUR_KEY_GOES_HEREDesired Call DP03:  https://api.census.gov/data/2018/acs/acs5/profile?get=group(DP03),NAME&for=tract:*&in=state:06&in=county:059ORhttps://api.census.gov/data/2018/acs/acs5/profile?get=group(DP03),NAME&for=tract:*&in=state:06%20county:059Can't right click to save as CSVAbove URL   Example API call from Caroline:<jupyter_code># Set base url
# url = 'https://api.census.gov/data/2018/acs/acs5/profile?'
# # Set params
# params = {
#     'get': 'group(DP05),NAME',
#     'for': 'county:*',
#     'in': 'state:06',
#     'key': key
# }
# # Make a request and display the response code.
# res = requests.get(url,params)
# res
# data = res.json()
# data<jupyter_output><empty_output><jupyter_text>API call adjusted to pull Orange County by census tract<jupyter_code># Set base url
url = 'https://api.census.gov/data/2018/acs/acs5/profile?'

params2 = {
    'get': 'group(DP03),NAME',
    'for': 'tract:*',
    'in': 'state:06%20county:059',
    'key': key
}
# Make a request and display the response code.
res2 = requests.get(url,params2)
res2
raw = res2.json()
# data2
#returns a veryvery long list
df_raw = pd.DataFrame(raw)
df_raw.shape
df_raw.tail()
# save it!
df_raw.to_pickle('./Orange_County_Voting_Trends/census_data/DP03_raw.pkl')<jupyter_output><empty_output><jupyter_text>NOTE: raw data was collected from all 4 major Census tables. But only DP03 (economic data) was cleaned.<jupyter_code># confirm that we can read the file back in from pickle
pdf = pd.read_pickle('./Orange_County_Voting_Trends/census_data/DP03_raw.pkl')
pdf.shape
pdf.head(2)<jupyter_output><empty_output><jupyter_text>Attach names to measure codes<jupyter_code>#use ['NAME'] as column headings
pdf.columns = pdf.iloc[0]
pdf.head(2)
#drop first row because duplicate
pdf.drop(0, axis=0, inplace=True)
pdf.head(2)<jupyter_output><empty_output><jupyter_text>Download 'metadata' .csv file from US Census site.  Use this to create dictionary of names of columns, to match codes<jupyter_code>headers = pd.read_csv('./Orange_County_Voting_Trends/census_data/ACSDP5Y2018.DP03_metadata_2020-10-29T195609.csv')
headers.shape
headers.head(4)
labels = {}
key = headers['GEO_ID'].tolist()
value = headers['id'].tolist()
len(value)
#dict(zip(header_cols, descriptions))
labels = dict(zip(key, value))
pdf.rename(columns = labels, inplace=True)
#use pd.set_option in order to view all columns.  uncomment code below.
# pd.set_option('display.max_rows', None)
# pd.set_option('display.max_columns', None)

pdf.head(3).T<jupyter_output><empty_output><jupyter_text>additional cleaning steps1. drop codenames (e.g., DP03_0136MA ...)2. drop extraneous columns:'Margin of Error!!'Percent Margin of Error!!'Percent Estimate!!3. edit name of remaining<jupyter_code>blah_cols = pdf.columns[pdf.columns.str.contains('DP03')]
blah_cols
#df = df.drop(['Q43'], axis=1)
pdf = pdf.drop(blah_cols, axis=1)
pdf.shape
moe_cols = pdf.columns[pdf.columns.str.contains('Margin of Error!!')]
pdf = pdf.drop(moe_cols, axis=1)
pdf.shape
pct_cols = pdf.columns[pdf.columns.str.contains('Percent Estimate!!')]
pdf = pdf.drop(pct_cols, axis=1)
pdf.shape
pdf.head(2)
pdf.columns = pdf.columns.str.replace('!!', " ")
pdf.head(2)<jupyter_output><empty_output><jupyter_text>save clean dataframes to pickleNOTE:  raw data was collected from all 4 major Census tables.  But only DP03 (economic data) was cleaned.<jupyter_code># pdf.to_pickle('./Cap-Census-data/DP02_raw.p')
pdf.to_pickle('./Orange_County_Voting_Trends/census_data/DP03_clean.pkl')
# pdf.to_pickle('./Cap-Census-data/DP04_raw.p')
# pdf.to_pickle('./Cap-Census-data/DP05_raw.p')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14498933901918976, 'fraction_numerical': 0.03245676380004738, 'mean_word_length': 6.0602006688963215, 'pattern_counts': {'":': 0, '<': 33, '<?xml version=': 0, '>': 33, 'https://': 6, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 3, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '8', 'dir': 'jupyter-structured-clean-dedup', 'id': '174958', 'license': 'CC0-1.0', 'n_tokens_mistral': 1652, 'n_tokens_neox': 1564, 'n_words': 316, 'path': '02_census_tables_cleaning.ipynb', 'repo_name': 'git-nmf/Orange_County_Voting_Trends', 'size': '4221'}