Employment and Labor Force Participation

This vignette explains the process done to get the data for Percent of Employment, Unemployment and Not in Labor Force Persons With Disability and Without for all Counties in the State of Iowa. Followed by the data being used in the Services dashboard, that includes the OASDI and SSI Dataset — title: “Employment Status” output: html_document date: ‘2022-06-17’ —

Downloading libraries Used To Gather, Clean and Merge Employment Data

library(tidyverse)
library(tidycensus)
library(dplyr)
library(ggplot2)

Step One: Pulling Data

The sources from which the Data is retrieved include, ACS, and SSA (OASDI and SSI) for 2020

Loaded county based data such as population, parameters with respect to Employment and Earnings for persons with Disability from ACS (American Community Survey) Followed by OASDI (Old-Age, Survivors, and Disability Insurance) and SSI (Supplemental Security Income) data from SSA (Social Security Administration)

new_table= read.csv("lookup_county_regions.csv")
View(new_table)
pop_table <- read.csv("Population_by_County.csv")
View(pop_table)
oasdi_table <- read.csv("oasdi_2020.csv")
View(oasdi_table)
oasdi_amount_table <- read.csv("oasdi_amount_2020.csv")
View(oasdi_amount_table)
ssi_table <- read.csv("SSI.csv")
View(ssi_table)

Loading Variables from the ACS (5-year estimatesfor) for the year 2020

var<-load_variables(2020,"acs5")
View(var)

Categorized data based on Employment Status for persons with disability, and Employment Status for persons without disability. Used Table C18120 that gives data about Employment Status by Disability Status.

emp_stat_disability <-c("In the Labor Force,Employed with disability"= "C18120_004", "In the Labor force,unemployed with a disability" = "C18120_007",  "Not in the Labor force, with a disability" = "C18120_010")

emp_stat_no_disability <- c("In the Labor Force,Employed with no disability"= "C18120_005", "In the Labor force,unemployed with no disability" = "C18120_008", "Not in the Labor Force, with no disability" = "C18120_011")

Getting data from ACS about Employment Status for Persons with and Without Disability, for all Counties in Iowa, 2020

empl_status_disability <- get_acs(
  geography= "county",
  year = 2020,
  variables = emp_stat_disability,
  state = "IA",
  output = "wide"
)
View(empl_status_disability)

empl_status_no_disability <- get_acs(
  geography= "county",
  year = 2020,
  variables = emp_stat_no_disability,
  state= "IA",
  output= "wide"
)
View(empl_status_no_disability)

Joing the two datasets i.e. Employment Status for Persons with Disability and Employment Status for Persons without Disability

all_emp_stat= full_join(empl_status_disability,empl_status_no_disability)
View(all_emp_stat)

Step Two: Calculating values

Calculating the Disabled Population Total and Non Disabled Population Total for all counties in Iowa, and for the entire state.

all_emp_stat$disability_total <- (all_emp_stat$`In the Labor Force,Employed with disabilityE`+ all_emp_stat$`Not in the Labor force, with a disabilityE`+ all_emp_stat$`In the Labor force,unemployed with a disabilityE`)
all_emp_stat$no_disability_total <- (all_emp_stat$`In the Labor Force,Employed with no disabilityE`+ all_emp_stat$`In the Labor force,unemployed with no disabilityE`+all_emp_stat$`Not in the Labor Force, with no disabilityE`)

Checking Reliability of estimates based on Margin of Error for Employed persosn with Disability. First by Calculating the Confidence Interval, then checking - If it is greater than or equal to 30, then it has low reliability - If it is betwween 15 and 30, then it has median reliability - If it is lower than or equal to 15, then it has high reliability

CV_employed_disabled = ((all_emp_stat$`In the Labor Force,Employed with disabilityM`/1.645)/all_emp_stat$`In the Labor Force,Employed with disabilityE`)*100

CV_unemployed_disabled = ((all_emp_stat$`In the Labor force,unemployed with a disabilityM`/1.645)/all_emp_stat$`Not in the Labor force, with a disabilityE`)*100

CV_not_in_Labor_force_disabled = ((all_emp_stat$`Not in the Labor force, with a disabilityM`/1.645)/all_emp_stat$`Not in the Labor force, with a disabilityE`)*100

all_emp_stat$CV_employed_disabled = ifelse(CV_employed_disabled > 30 , " low reliability",
                  ifelse(CV_employed_disabled <= 15, "high reliability",
                         "median reliability"))

all_emp_stat$CV_unemployed_disabled = ifelse(CV_unemployed_disabled > 30 , " low reliability",
                  ifelse(CV_unemployed_disabled <= 15, "high reliability",
                         "median reliability"))

all_emp_stat$CV_not_in_Labor_force_disabled = ifelse(CV_not_in_Labor_force_disabled > 30 , " low reliability",
                  ifelse(CV_not_in_Labor_force_disabled <= 15, "high reliability",
                         "median reliability"))

Checking Reliability of estimates based on Margin of Error for Employed persosn with No Disability. First by Calculating the Confidence Interval, then checking - If it is greater than or equal to 30, then it has low reliability - If it is betwween 15 and 30, then it has median reliability - If it is lower than or equal to 15, then it has high reliability


CV_employed_not_disabled = ((all_emp_stat$`In the Labor Force,Employed with no disabilityM`/1.645)/all_emp_stat$`In the Labor Force,Employed with no disabilityE`)*100

CV_unemployed_not_disabled = ((all_emp_stat$`In the Labor force,unemployed with no disabilityM`/1.645)/all_emp_stat$`In the Labor force,unemployed with no disabilityE`)*100

CV_not_in_Labor_force_not_disabled = ((all_emp_stat$`Not in the Labor Force, with no disabilityM`/1.645)/all_emp_stat$`Not in the Labor Force, with no disabilityE`)*100

all_emp_stat$CV_employed_not_disabled = ifelse(CV_employed_not_disabled > 30 , " low reliability",
                  ifelse(CV_employed_not_disabled <= 15, "high reliability",
                         "median reliability"))

all_emp_stat$CV_unemployed_not_disabled = ifelse(CV_unemployed_not_disabled > 30 , " low reliability",
                  ifelse(CV_unemployed_not_disabled <= 15, "high reliability",
                         "median reliability"))

all_emp_stat$CV_not_in_Labor_force_not_disabled = ifelse(CV_not_in_Labor_force_not_disabled > 30 , " low reliability",
                  ifelse(CV_not_in_Labor_force_not_disabled <= 15, "high reliability",
                         "median reliability"))

View(all_emp_stat)

Calculating the percentage of Employed, Unemployed and Not In Labor Force Persons with and Without a Disability, for all Counties im Iowa


all_emp_stat = all_emp_stat %>% mutate(percent_in_labor_force_employed_disabled = 100 * (all_emp_stat$`In the Labor Force,Employed with disabilityE`/all_emp_stat$disability_total),
percent_in_labor_force_employed_not_disabled = 100 * (all_emp_stat$`In the Labor Force,Employed with no disabilityE`/all_emp_stat$no_disability_total), percent_in_labor_force_unemployed_disabled = 100 * (all_emp_stat$`In the Labor force,unemployed with a disabilityE`/all_emp_stat$disability_total),
percent_in_labor_force_unemployed_not_disabled = 100 * (all_emp_stat$`In the Labor force,unemployed with no disabilityE`/all_emp_stat$no_disability_total), 
percent_not_in_labor_force_disabled = 100 * (all_emp_stat$`Not in the Labor force, with a disabilityE`/all_emp_stat$disability_total),
percent_not_in_labor_force_not_disabled = 100 * (all_emp_stat$`Not in the Labor Force, with no disabilityE`/all_emp_stat$no_disability_total))


all_emp_stat = all_emp_stat %>% mutate(percent_in_labor_force_employed_gap = percent_in_labor_force_employed_not_disabled - percent_in_labor_force_employed_disabled, percent_in_labor_force_unemployed_gap = percent_in_labor_force_unemployed_not_disabled - percent_in_labor_force_unemployed_disabled , percent_not_in_labor_force_gap =    percent_not_in_labor_force_not_disabled - percent_not_in_labor_force_disabled )
View(all_emp_stat)

Comparing the Percent of Employment for persons with No Disability to the State Average

max(all_emp_stat$percent_in_labor_force_employed_not_disabled)
state_avg=mean(all_emp_stat$percent_in_labor_force_employed_not_disabled)
min(all_emp_stat$percent_in_labor_force_employed_not_disabled)

all_emp_stat$compared_to_state_avg_end= ifelse( all_emp_stat$percent_in_labor_force_employed_not_disabled > state_avg , "above average",
                  ifelse(all_emp_stat$percent_in_labor_force_employed_not_disabled < state_avg, "below average",
                         "average"))

Comparing the Percent of Employment for persons with Disability to the State Average - If County percent is greater than 46%, it is above state average - If County percent is equal to 46%, it is equal to state average - If County percent is lower than 46%, it is below state average

max(all_emp_stat$percent_in_labor_force_employed_disabled)
state_avg2=mean(all_emp_stat$percent_in_labor_force_employed_disabled)
min(all_emp_stat$percent_in_labor_force_employed_disabled)

all_emp_stat$compared_to_state_avg_ed= ifelse( all_emp_stat$percent_in_labor_force_employed_disabled > state_avg2 , "above average",
                  ifelse(all_emp_stat$percent_in_labor_force_employed_disabled < state_avg2, "below average",
                         "average"))

Comparing the Percent of Unemployment for persons with Disability to the State Average

max(all_emp_stat$percent_in_labor_force_unemployed_disabled)
state_avg3=mean(all_emp_stat$percent_in_labor_force_unemployed_disabled)
min(all_emp_stat$percent_in_labor_force_unemployed_disabled)

all_emp_stat$compared_to_state_avg_ud= ifelse(all_emp_stat$percent_in_labor_force_unemployed_disabled > state_avg3 , "above average",
                  ifelse(all_emp_stat$percent_in_labor_force_unemployed_disabled < state_avg3, "below average",
                         "average"))

Comparing the Percent Not in Labor Force persons with Disability to the State Average

max(all_emp_stat$percent_not_in_labor_force_disabled)
state_avg4=mean(all_emp_stat$percent_not_in_labor_force_disabled)
min(all_emp_stat$percent_not_in_labor_force_disabled)

all_emp_stat$compared_to_state_avg_nd = ifelse(all_emp_stat$percent_not_in_labor_force_disabled > state_avg4 , "above average",
                  ifelse(all_emp_stat$percent_not_in_labor_force_disabled < state_avg4, "below average",
                         "average"))
View(all_emp_stat)

Step Three: Cleaning and Formatting

Re-naming Columns for clear and easy understanding

colnames(all_emp_stat)[2] <-"County"
colnames(all_emp_stat)[3] <- "In the Labor Force, Employed with Disability (Estimate)"
colnames(all_emp_stat)[4] <- "In the Labor Force, Employed with Disability (MOE)"
colnames(all_emp_stat)[5] <- "In the Labor Force, Unemployed with Disability (Estimate)"
colnames(all_emp_stat)[6] <- "In the Labor Force, Unemployed with Disability (MOE)"
colnames(all_emp_stat)[7] <- "Not in the Labor Force, with Disability (Estimate)"
colnames(all_emp_stat)[8] <- "Not in the Labor Force, with Disability (MOE)"
colnames(all_emp_stat)[9] <- "In the Labor Force, Employed with NO Disability (Estimate)"
colnames(all_emp_stat)[10] <- "In the Labor Force, Employed with NO Disability (MOE)"
colnames(all_emp_stat)[11] <- "In the Labor Force, Unemployed with NO Disability (Estimate)"
colnames(all_emp_stat)[12] <- "In the Labor Force, Unemployed with NO Disability (MOE)"
colnames(all_emp_stat)[13] <- "Not in the Labor Force, with NO Disability (Estimate)"
colnames(all_emp_stat)[14] <- "Not in the Labor Force, with NO Disability (MOE)"

Formatting the county names. Example- Changing “Adams County, Iowa” to “Adams”

all_emp_stat$County<-gsub("County, Iowa","",as.character(all_emp_stat$County))

Step Four: Merging and Loading Datasets

Merging the Employment Status Table to Counties of Iowa based on - Metropolitan - Micropolitan - Non-Metropolitan

new_all_emp_stat = merge(x = all_emp_stat, y = new_table, by = "GEOID", all.x = TRUE)
View(new_all_emp_stat)

Loading the OASDI dataset from SSA Merging the OASDI dataset with Counties of Iowa based on - Metropolitan - Micropolitan - Non-Metropolitan

oasdi_metro_stat = merge(x = oasdi_table, y = new_table, by = "GEOID", all.x =  TRUE)
view(oasdi_metro_stat)

Merging the OASDI Amount dataset with Employment Status Dataset

merged_oasdi_amount_table = merge(x = all_emp_stat, y = oasdi_amount_table, by = "GEOID", all.x = TRUE)
View(merged_oasdi_amount_table)

Loading the SSI dataset from SSA Merging the SSI dataset with Employment Status Dataset

merged_ssi_table = merge(x = all_emp_stat, y = ssi_table, by = "GEOID", all.x = TRUE)
View(merged_ssi_table)

Step Five: Coverting files to csv

Converting all the datasets into csv files, and saving them to the Local Directory