pulling QWI information for Winston Salem

ws_qwi.R

library(tidyverse)
library(tidyqwi)

# see census.gov/population/estimates/metro-city/0312msa.txt

yearz <- 2009:2018

state <- 37

arguments <- data.frame(yearz, state)
arguments
qwi_data <- map2(arguments$state, arguments$yearz, ~
                   get_qwi(
                     states = .x,
                     years  = .y ,
                     industry_level = "2",
                     all_groups = FALSE,
                     endpoint = "se",
                     geography = "cbsa",
                     processing = "multiprocess",
                     apikey = CENSUS_KEY))

out <- qwi_data %>% 
  bind_rows()


statz <- get_qwi(
  states = "NC",
  years  = "2017" ,
  industry_level = "2",quarters = 1,
  all_groups = TRUE,
  endpoint = "rh",
  geography = "cbsa",
  processing = "sequential",
  apikey = CENSUS_KEY)

# https://lehd.ces.census.gov/data/schema/latest/lehd_public_use_schema.html#_identifiers_for_qwi
education_table <- tribble(
  ~"education",	~"education_label",
  "E0", "All Education Categories",
  
  "E1", "Less than high school",
  
  "E2", "High school or equivalent, no college",
  
  "E3", "Some college or Associate degree",
  
  "E4", "Bachelor’s degree or advanced degree",
  
  "E5", "Educational attainment not available (workers aged 24 or younger)"
)

# WS 49180

statz2 <- tidyqwi::add_qwi_labels(out) %>% 
  filter(`metropolitan statistical area/micropolitan statistical area` == 49180)

write_rds(statz2, "2009-2018-se-winstonsalem.rds")

statz2 %>% 
  group_by(year, education, industry) %>% 
  summarise(mean_earnings = mean(EarnBeg, na.rm = T),
            mean_employees = mean(EmpS)) %>% 
  mutate(generated_income = mean_earnings * mean_employees) %>%
  mutate(year_num =case_when(
    year == 2009~1,
    year == 2010~ 2,
    year == 2011~ 3,
    year == 2012~ 4,
    year == 2013~ 5,
    year == 2014~ 6,
    year == 2015~ 7,
    year == 2016~ 8,
    year == 2017~ 9,
    year == 2018~ 10,
  )) %>% 
  mutate(generated_income = generated_income * (1-.02)^year_num) %>% 
  left_join(industry_labels) %>% 
  left_join(education_table) %>% 
  filter(industry=="31-33") %>% 
  ungroup() %>% 
  ggplot(aes(year, generated_income, color = education_label))+
  geom_line()+
  theme(legend.position = "top")