Data Preparation and cleansing was a multi-step process, requiring cleaning
raw data to prepare it to be merged together, as well as initial exploration to
identify the features to include and any data noise/missing data issues. Below
are the steps I took on each individual set of data, as well as what was needed
once the data were combined.
Step 1:
US Census Bureau: American Community Survey 5-year
Medicaid/Means-Tested Public Coverage by Sex by Age (Table C27007)
County-level annual data (5-year running average) for all 50 U.S. States, from
2012-2020
Step
Tool
Description
Drop Unneeded Variables
(Columns)
Excel/
Table provided source, margin of error for each
variable; these columns were dropped.
Rename Variables
(Columns)
Excel
Original variable names were long and cumbersome;
they were simplified (e.g. from
“Estimate!!Total:!!Male:!!Under 19 years:” to Male
Youth Pop)
Create Percentage
Variables (Columns)
Excel
Create new columns that convert raw population
numbers into percentage shares of population or
subpopulation (e.g. MaleYouthMed_Rate = Male
Youth Medicaid/Male Youth Pop)
Add Year
Excel
Added column to capture year of data (to facilitate
merging multiple years into one table)
Create Index
Excel
Merge Year with 5-digit County FIPS code to create a
unique identifier for each row of data
Add Medicaid Expansion
Status
Excel
One-to-Many merge of State/year of expansion to
each County/State row
Merge Annual Data Sets
Excel
Merge all years using index (ID-Year) as the primary
key (Union of Data Sets)
Remove Duplicates
Excel
Adding Medicaid Expansion Status step created
duplicate records for Arkansas and Kansas; these were
removed
Health Insurance by Age and Race (TablesC27001B-E, H)
County-level annual data (5-year running average) for all 50 U.S. States from
2012-2020 for American Indian/Native, Asian, Black, Latino/Hispanic, Native
Hawaiian/Pacific Islander, and White Non-Hispanic sub-populations.
Excluded ‘some other race’ and ‘two or more races’ – visual inspection
suggested too few records.
Step
Tool
Description
Drop Unneeded Variables
(Columns)
Excel/
Table provided source, margin of error for each
variable; these columns were dropped.
Rename Variables
(Columns)
Excel
Original variable names were long and cumbersome;
they were simplified (e.g. from C27001B “Margin of
Error!!Total:!!Under 19 years:” to Pop_Youth_Black)
Create Percentage
Variables (Columns)
Excel
Create new columns that convert raw population
numbers into percentage shares of population or
subpopulation (e.g. YouthBlackIns_Rate =
Youth_Black_Ins/Pop_Youth_Black)
Add Year
Excel
Added column to capture year of data (to facilitate
merging multiple years into one table)
Create Index
Excel
Merge Year with 5-digit County FIPS code to create a
unique identifier for each row of data
Merge Annual Data Sets
Excel
Merge all years using index (ID-Year) as the primary
key (Union of Data Sets)
Initial data exploration
R
Reveals many null values for American Indian, Asian,
and Native Hawaiian/Pacific Islander records (more
than half are null)
Delete sparse variables
Excel
Remove columns of data for Asian, American Indian,
and Native Hawaiian/Pacific Islander variables.
Merge with Medicaid
Dataset to create
Combined Dataset
Excel
Merge all rows using index (ID-Year) as the primary
key (Full Join)
Income Data (Table S1903)
County-level annual data (5-year running average) for all 50 U.S. States from
2012-2020
Step
Tool
Description
Drop Unneeded Variables
(Columns)
Excel/
Retain only County, FIPS ID, State, and Median
Household Income Variable
Rename Variable
(Columns)
Excel
From: “Estimate!!Number!!HOUSEHOLD INCOME
BY RACE AND HISPANIC OR LATINO ORIGIN
OF HOUSEHOLDER!!Households” to Median
Income
Add Year
Excel
Added column to capture year of data (to facilitate
merging multiple years into one table)
Create Index
Excel
Merge Year with 5-digit County FIPS code to create a
unique identifier for each row of data
Merge Annual Data Sets
Excel
Merge all years using index (ID-Year) as the primary
key (Union of Data Sets)
Merge with Combined
Dataset
Excel
Merge all rows using index (ID-Year) as the primary
key (Full Join)
Employment & Education Data (Table S2301)
County-level annual data (5-year running average) for all 50 U.S. States from
2012-2020
Step
Tool
Description
Drop Unneeded Variables
(Columns)
Excel/
Retain only County, FIPS ID, State; Population 20-64
years, Labor Force Participation Rate, Unemployment
Rate, Poverty Status; Population 25-64, Educational
Attainment-Less than HS, HS/Equivalent, Some
College/Associates, Bachelors or higher
Rename Variable
(Columns)
Excel
Original variable names were long and cumbersome;
they were simplified (e.g. from “
Estimate!!Labor Force Participation Rate!!Population
20 to 64 years” to LaborForceRate
Add Year
Excel
Added column to capture year of data (to facilitate
merging multiple years into one table)
Create Index
Excel
Merge Year with 5-digit County FIPS code to create a
unique identifier for each row of data
Merge Annual Data Sets
Excel
Merge all years using index (ID-Year) as the primary
key (Union of Data Sets)
Merge with Combined
Dataset
Excel
Merge all rows using index (ID-Year) as the primary
key (Full Join)
County Health Rankings
County-level annual data (5-year running average) for all 50 U.S. States from
2012-2020
Step
Tool
Description
Drop Unneeded Variables
(Columns)
Excel/
Full dataset contains 786 variables. Retain only
County, FIPS ID, State; Life Lost Rate, Adult
Reported Fair/Poor Health (%), Avg Poor Physical
Health Days/Month; Avg Poor Mental Health
Days/Month, %Low Birth Weight (live births), Adult
Obesity Rate, Sexually Transmitted Infections (STIs)
per 100K, Teen Birth Rate, Primary Care Physicians
per 100K, Preventable Hospitalizations per 100K
Medicare Enrollees, Percent Child Poverty, Percent
Single Parent Households, Violent Crime Rate,
Percent Smokers, Percent Excess Alcohol
Consumption, Flu Vaccination Percent
Rename Variable
(Columns)
Excel
Adjust Variable Names, e.g. from “% Vaccinated” to
“Flu Vaccination Percent”
Add Year
Excel
Added column to capture year of data (to facilitate
merging multiple years into one table)
Create Index
Excel
Merge Year with 5-digit County FIPS code to create a
unique identifier for each row of data
Merge Annual Data Sets
Excel
Merge all years using index (ID-Year) as the primary
key (Union of Data Sets)
Merge with Combined
Dataset
Excel
Merge all rows using index (ID-Year) as the primary
key (Full Join)
Centers for Disease Control
Diabetes Atlas: Diagnosed Diabetes Among Adults 20+ Years, Age-Adjusted
Percentage
County-level annual data (5-year running average) for all 50 U.S. States from
2012-2020*
2020 data taken from County Health Rankings (not available via CDC
Diabetes Atlas)
Step
Tool
Description
Drop Unneeded Variables
(Columns)
Excel/
Drop State FIPS
Rename Variable
(Columns)
Excel
Change “Percentage” to “Diabetes Prevalence”
Create Index
Excel
Merge Year with 5-digit County FIPS code to create a
unique identifier for each row of data
Merge Annual Data Sets
Excel
Merge all years using index (ID-Year) as the primary
key (Union of Data Sets)
Merge with Combined
Dataset
Excel
Merge all rows using index (ID-Year) as the primary
key (Full Join)
COVID Data – USA Facts
County-level daily data from 1/1/2019 to 6/9/22 for all 50 U.S. States
Step
Tool
Description
Create New Data Table
Excel
Summary Data Table Shell for annual data
Add only year-end values
Excel
Copy data for county, state, FIPS, and last day of each
year (or last available day) into a column for cases or
deaths by the year
Rename Variable
(Columns)
Excel
Data combined from 3 sheets (deaths, cases,
population), with columns renamed with Death or
Cases, plus year (2020, 2021, 2022 from
“12/31/2020”, “12/31/2021”, “6/09/22”)
Create Percentage
Variables (Columns)
Excel
Create new columns that convert raw population
numbers into percentage shares of population (e.g.,
2020 Cases = 2020 Cases/Pop)
Create Index
Excel
Merge Year with 5-digit County FIPS code to create a
unique identifier for each row of data
Merge with Combined
Dataset
Excel
Merge all rows using index (ID-Year) as the primary
key (Left Join)
Step 2:
Use R to make sure all data are in correct format (e.g. numeric, factor, etc).
Use R to generate summary statistics and structure of data, and produce
histograms/bar charts/box charts to find:
• Missing/Null Values; significant number of nulls in data by race (Asian,
American Indian, and Native Hawaiian) led to focus on Black, Latino,
and White populations for health insurance data.
• Skewness
• Outliers
Use R to create correlation matrices to identify correlations, which can be used
to reduce the number of features in the model.
