<jupyter_start><jupyter_text>26_extended_census data This notebook handles making an extended subset of Census data for use in modeling<jupyter_code># imports
import pandas as pd
import numpy as np
import requests
import pickle as pkl
import matplotlib.pyplot as plt
plt.style.use('ggplot')
# pd.set_option('display.max_columns', None)
# pd.set_option('display.max_rows', None)<jupyter_output><empty_output><jupyter_text>Select desired columns from Census data for a subset- Voting data contains 'srprec' or voting precints.- Statewidedatabase.org provides conversion tables between precints and census tracts.  Bring in census DP03 data and select columns.<jupyter_code>dp03_18 = pd.read_pickle('./census_data/DP03_clean.pkl')
dp03_18.shape
dp03_18.head(3)
dp03_18['tract'] = dp03_18['tract'].astype('int64')
dp03_18['tract'].dtype
# to select columns, read a (very long) list of column names, and select which to keep.
# helpful to copy into a .py file named 'variables' for future reference
#uncomment in order to see full list of columns names

# dp03_18.columns.tolist()             
dp03_cols = [
             'GEO_ID',
             'tract',
             'Estimate EMPLOYMENT STATUS Population 16 years and over In labor force Civilian labor force Employed',
             'Estimate EMPLOYMENT STATUS Population 16 years and over In labor force Civilian labor force Unemployed',
             'Estimate EMPLOYMENT STATUS Population 16 years and over In labor force Armed Forces',
             'Estimate EMPLOYMENT STATUS Population 16 years and over Not in labor force',
             'Estimate EMPLOYMENT STATUS Females 16 years and over In labor force Civilian labor force Employed',
             'Estimate EMPLOYMENT STATUS Own children of the householder under 6 years All parents in family in labor force',
             'Estimate EMPLOYMENT STATUS Own children of the householder 6 to 17 years All parents in family in labor force',
             'Estimate OCCUPATION Civilian employed population 16 years and over Management, business, science, and arts occupations',
             'Estimate OCCUPATION Civilian employed population 16 years and over Service occupations',
             'Estimate OCCUPATION Civilian employed population 16 years and over Sales and office occupations',
             'Estimate OCCUPATION Civilian employed population 16 years and over Natural resources, construction, and maintenance occupations',
             'Estimate OCCUPATION Civilian employed population 16 years and over Production, transportation, and material moving occupations',
             'Estimate INCOME AND BENEFITS (IN 2018 INFLATION-ADJUSTED DOLLARS) Total households Median household income (dollars)',
             'Estimate HEALTH INSURANCE COVERAGE Civilian noninstitutionalized population With health insurance coverage With private health insurance',
             'Estimate HEALTH INSURANCE COVERAGE Civilian noninstitutionalized population With health insurance coverage With public coverage',
             'Estimate HEALTH INSURANCE COVERAGE Civilian noninstitutionalized population No health insurance coverage'
             ]
dp03_18sub = dp03_18[dp03_cols]
dp03_18sub.head(3)
dp03_18sub.info()
# change multiple columns to numeric

num_cols = ['Estimate EMPLOYMENT STATUS Population 16 years and over In labor force Civilian labor force Employed',
             'Estimate EMPLOYMENT STATUS Population 16 years and over In labor force Civilian labor force Unemployed',
             'Estimate EMPLOYMENT STATUS Population 16 years and over In labor force Armed Forces',
             'Estimate EMPLOYMENT STATUS Population 16 years and over Not in labor force',
             'Estimate EMPLOYMENT STATUS Females 16 years and over In labor force Civilian labor force Employed',
             'Estimate EMPLOYMENT STATUS Own children of the householder under 6 years All parents in family in labor force',
             'Estimate EMPLOYMENT STATUS Own children of the householder 6 to 17 years All parents in family in labor force',
             'Estimate OCCUPATION Civilian employed population 16 years and over Management, business, science, and arts occupations',
             'Estimate OCCUPATION Civilian employed population 16 years and over Service occupations',
             'Estimate OCCUPATION Civilian employed population 16 years and over Sales and office occupations',
             'Estimate OCCUPATION Civilian employed population 16 years and over Natural resources, construction, and maintenance occupations',
             'Estimate OCCUPATION Civilian employed population 16 years and over Production, transportation, and material moving occupations',
             'Estimate INCOME AND BENEFITS (IN 2018 INFLATION-ADJUSTED DOLLARS) Total households Median household income (dollars)',
             'Estimate HEALTH INSURANCE COVERAGE Civilian noninstitutionalized population With health insurance coverage With private health insurance',
             'Estimate HEALTH INSURANCE COVERAGE Civilian noninstitutionalized population With health insurance coverage With public coverage',
             'Estimate HEALTH INSURANCE COVERAGE Civilian noninstitutionalized population No health insurance coverage']
for col in num_cols:
    dp03_18sub[col] = pd.to_numeric(dp03_18sub[col])

# will generate a warning (not an error) about making a change a copy of a slide of a dataframe.  
# we will just proceed.
dp03_18sub.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 583 entries, 1 to 583
Data columns (total 18 columns):
 #   Column                                                                                                                                    Non-Null Count  Dtype 
---  ------                                                                                                                                    --------------  ----- 
 0   GEO_ID                                                                                                                                    583 non-null    object
 1   tract                                                                                                                                     583 non-null    int64 
 2   Estimate EMPLOYMENT STATUS Population 16 years and over In labor force Civilian labor force Employed                                      583 non-null    int64 
 3   Estimate EMPLOYMENT STATUS Population 16 years and over In l[...]<jupyter_text>create a dictionary to rename columns for readability<jupyter_code>col_names = {'Estimate EMPLOYMENT STATUS Population 16 years and over In labor force Civilian labor force Employed': 'employed',
             'Estimate EMPLOYMENT STATUS Population 16 years and over In labor force Civilian labor force Unemployed':'unemployed',
             'Estimate EMPLOYMENT STATUS Population 16 years and over In labor force Armed Forces':'empl_military',
             'Estimate EMPLOYMENT STATUS Population 16 years and over Not in labor force': 'not_inlaborforce',
             'Estimate EMPLOYMENT STATUS Females 16 years and over In labor force Civilian labor force Employed': 'working_women',
             'Estimate EMPLOYMENT STATUS Own children of the householder under 6 years All parents in family in labor force': 'parents_work_under6',
             'Estimate EMPLOYMENT STATUS Own children of the householder 6 to 17 years All parents in family in labor force': 'parents_work_0617',
             'Estimate OCCUPATION Civilian employed population 16 years and over Management, business, science, and arts occupations': 'occ_mgmt_sci_art',
             'Estimate OCCUPATION Civilian employed population 16 years and over Service occupations': 'occ_service_sector',
             'Estimate OCCUPATION Civilian employed population 16 years and over Sales and office occupations': 'occ_sales_gen_office',
             'Estimate OCCUPATION Civilian employed population 16 years and over Natural resources, construction, and maintenance occupations': 'occ_constr_maintc',
             'Estimate OCCUPATION Civilian employed population 16 years and over Production, transportation, and material moving occupations': 'occ_manuf_transpo',
             'Estimate INCOME AND BENEFITS (IN 2018 INFLATION-ADJUSTED DOLLARS) Total households Median household income (dollars)': 'hh_med_income',
             'Estimate HEALTH INSURANCE COVERAGE Civilian noninstitutionalized population With health insurance coverage With private health insurance': 'hlthins_priv',
             'Estimate HEALTH INSURANCE COVERAGE Civilian noninstitutionalized population With health insurance coverage With public coverage': 'hlthins_public',
             'Estimate HEALTH INSURANCE COVERAGE Civilian noninstitutionalized population No health insurance coverage': 'hlthins_none'}
# df.rename(columns={"A": "a", "B": "c"})
dp03_18sub.rename(columns=col_names, inplace=True)

#will generate a warning (not an error) about "set on a copy of a slice from a DataFrame".  we will continue.  
dp03_18sub.head(3)
pd.set_option('display.max_rows', 10)
dp03_18sub['tract'].value_counts()
#save subset of Census data to pickle file for enhancing voter data, and for modeling
dp03_18sub.to_pickle('./census_data/DP03_extended.pkl')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.05186141596729073, 'fraction_numerical': 0.022810415321712935, 'mean_word_length': 2.9722222222222223, 'pattern_counts': {'":': 2, '<': 13, '<?xml version=': 0, '>': 13, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 26, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '192163', 'license': 'CC0-1.0', 'n_tokens_mistral': 2460, 'n_tokens_neox': 2188, 'n_words': 974, 'path': '26_extended_census data.ipynb', 'repo_name': 'git-nmf/Orange_County_Voting_Trends', 'size': '9294'}