---
title: "UIdata jenna"
author: "Jenna"
date: "May 29, 2020"

output: rmarkdown::github_document
---

```{r setup, include=FALSE}
#knitr::opts_chunk$set(echo = TRUE)
knitr::opts_chunk$set(echo = FALSE, warning=FALSE, message=FALSE)

```

```{r}
# Load 
library(tidyverse)
library(blscrapeR)
library(fredr)
library(lubridate)
library(purrr)
library(directlabels)
library(ggrepel)
library(tidycensus)
library(readxl)
library(readr)
library(blsAPI)
library(janitor) # Excel dates
library(here)
source(here("03_graphics themes.R"))

```



## Objective

Given New Orleans's industry composition, is unemployment higher than you'd expect? 

```{r}
## Louisiana Workforce Commission

### UI by industry sector and parish from the Tableau dashboard

# The Tableau dashboard can be found [here](https://public.tableau.com/profile/louisiana.workforce.commission.lmi#!/vizhome/LWCUIDashboard/Dashboard1), or linked from the press releases of new UI data [here](http://www.laworks.net/PublicRelations/PR_PressRelease.asp?Year=2020&Month=4). Actually, the press release gives a download to a spreadsheet, so switch to using that.
# 
# The spreadsheets were moved out of the press releases & may no longer be published. I'm importing the spreadsheets Robby added from Tableau here:

lwc.ui.Industry.611 <- read_csv("inputs/LWC/lwcUIDashboard_20200611/Industry (Louisiana Unemployment Compensation Claims for Week Ending on March 28 2020 ).csv",
                            col_types = cols(`Week Ending` = col_date(format = "%m/%d/%Y"))) %>% 
  mutate(NAICS = str_replace(`NAICS Code`, "NAICS ","")) %>% 
  select(-`Number of Records`, -`NAICS Code`) %>% 
  mutate(Industry = ifelse(Industry == "Health Care and Social Assistence", "Health Care and Social Assistance", Industry),
         Industry = ifelse(Industry == "AccomModation & Food Services", "Accommodation & Food Services", Industry)) 

lwc.ui.Parish.611 <- read_csv("inputs/LWC/lwcUIDashboard_20200611/Parish (Louisiana Unemployment Compensation Claims for Week Ending on March 28 2020 ).csv", 
                          col_types = cols(`Week Ending` = col_date(format = "%m/%d/%Y"))) %>% 
    select(-`Number of Records`)


```

```{r}
# Details:
# 
# - Louisiana is 22000, New Orleans MSA is C3538.
# - As of April 10, 2020, 3rd quarter 2019 is the most recent QCEW. 
# - agglvl_code is 54 for 2-digit NAICS, state-level; 74 for parish-level.
# - own_code: 5 is private, 2 is state gov't, 3 is local gov't.
# 
# This pulls Jul, Aug, and Sep for 2019 (verified using NAICS 23). Note that this is long on ownership -- How does EMSI handle ownership?

qcew.preCOVID.LA <- blsQCEW(method = "Area",
        year = "2019",
        quarter = 3,
        area = "22000")

qcew.preCOVID.NOmsa <- blsQCEW(method = "Area",
        year = "2019",
        quarter = 3,
        area = "C3538")

## getting numbers by parish to combine for the netro
qcew.preCOVID.075 <- blsQCEW(method = "Area",
        year = "2019",
        quarter = 3,
        area = "22075")
qcew.preCOVID.087 <- blsQCEW(method = "Area",
        year = "2019",
        quarter = 3,
        area ="22087")
qcew.preCOVID.089 <- blsQCEW(method = "Area",
        year = "2019",
        quarter = 3,
        area = "22089")
qcew.preCOVID.093 <- blsQCEW(method = "Area",
        year = "2019",
        quarter = 3,
        area = "22093")
qcew.preCOVID.095 <- blsQCEW(method = "Area",
        year = "2019",
        quarter = 3,
        area = "22095")
qcew.preCOVID.103 <- blsQCEW(method = "Area",
        year = "2019",
        quarter = 3,
        area = "22103")

## these parishes also get used on their own
qcew.preCOVID.OP <- blsQCEW(method = "Area",
        year = "2019",
        quarter = 3,
        area = "22071")

qcew.preCOVID.JP <- blsQCEW(method = "Area",
        year = "2019",
        quarter = 3,
        area = "22051")

qcew.preCOVID.NOmsa.par <- bind_rows(qcew.preCOVID.JP,qcew.preCOVID.075,qcew.preCOVID.087,qcew.preCOVID.089,qcew.preCOVID.093,qcew.preCOVID.095,qcew.preCOVID.103,qcew.preCOVID.OP)

```

```{r}

## qcew employment, either overall or by ".ind." (industry)
LA.ind.emp <- qcew.preCOVID.LA %>% 
  filter(agglvl_code == 54) %>% 
  group_by(industry_code) %>% 
  summarise(LAemp = sum(month3_emplvl, na.rm = TRUE))

LA.emp <- qcew.preCOVID.LA %>% 
  filter(agglvl_code == 50) %>% 
  select(totLAemp = month3_emplvl)

OP.ind.emp <- qcew.preCOVID.OP %>% 
  filter(agglvl_code == 74) %>% 
  group_by(industry_code) %>% 
  summarise(OPemp = sum(month3_emplvl, na.rm = TRUE))

OP.emp <- qcew.preCOVID.OP %>% 
  filter(agglvl_code == 70) %>% 
  select(totOPemp = month3_emplvl)

JP.emp <- qcew.preCOVID.JP %>% 
  filter(agglvl_code == 74) %>% 
  summarise(totJPemp = sum(month3_emplvl, na.rm = TRUE))

JP.ind.emp <- qcew.preCOVID.JP %>% 
  filter(agglvl_code == 74) %>% 
  group_by(industry_code) %>% 
  summarise(JPemp = sum(month3_emplvl, na.rm = TRUE))

## use combined parishes because overall MSA numbers seem weird
NOmsa.ind.emp <- qcew.preCOVID.NOmsa.par %>% 
  filter(agglvl_code == 74) %>% 
  group_by(industry_code) %>% 
  summarise(NOmsaemp = sum(month3_emplvl, na.rm = TRUE))

NOmsa.emp <-qcew.preCOVID.NOmsa.par %>%
  filter(agglvl_code == 70) %>% 
  summarise(totMSAemp = sum(month3_emplvl))

```


```{r}
## employment by indutry supersector for comparison barchart

ss.codes <- data.frame(code = c("1011","1012", "1013", "1021", "1022", "1023", "1024", "1025", "1026", "1027", "1028", "1029"), Industry = c("Natural Resources and Mining", "Construction", "Manufacturing", "Trade, Transportation, and Utilities", "Information", "Financial Activities", "Professional and Business Services", "Education and Health Services", "Leisure and Hospitality", "Other Services", "Public Administration", "Unclassified"))

LA.ss.emp <- qcew.preCOVID.LA %>% 
  filter(agglvl_code == 53) %>% 
  group_by(industry_code) %>% 
  summarise(LAemp = sum(month3_emplvl, na.rm = TRUE))

OP.ss.emp <- qcew.preCOVID.OP %>% 
  filter(agglvl_code == 73) %>% 
  group_by(industry_code) %>% 
  summarise(OPemp = sum(month3_emplvl, na.rm = TRUE))

JP.ss.emp <- qcew.preCOVID.JP %>% 
  filter(agglvl_code == 73) %>% 
  group_by(industry_code) %>% 
  summarise(JPemp = sum(month3_emplvl, na.rm = TRUE))

NOmsa.ss.emp <- qcew.preCOVID.NOmsa.par %>% 
  filter(agglvl_code == 73) %>% 
  group_by(industry_code) %>% 
  summarise(NOmsaemp = sum(month3_emplvl, na.rm = TRUE))
```

```{r}

## industry UI claims
LA.ind.claims <- lwc.ui.Industry.611 %>% 
  filter(`Claim Type` == "Initial") %>%
  group_by(NAICS, Industry, `Week Ending`) %>% 
  # filter(`Week Ending` >= "2020-03-21") %>% 
  summarise(LAUIclaims = sum(Claims))

LA.ind.totclaims <- lwc.ui.Industry.611 %>% 
  group_by(NAICS, Industry, `Week Ending`) %>% 
  # filter(`Week Ending` >= "2020-03-21") %>% 
  summarise(LAUIclaims = sum(Claims))

## overall claims
LA.claims <- lwc.ui.Parish.611 %>% 
  filter(`Claim Type` == "Initial") %>%
  group_by(`Week Ending`) %>%
  summarise(totLAclaims = sum(Claims))

LA.totclaims <- lwc.ui.Parish.611 %>% 
  group_by(`Week Ending`) %>%
  summarise(totLAclaims = sum(Claims))

OP.claims <- lwc.ui.Parish.611 %>% 
  filter(`Claim Type` == "Initial") %>%
  filter(Parish == "Orleans Parish")

OP.totclaims <- lwc.ui.Parish.611 %>% 
  filter(Parish == "Orleans Parish")%>%
  group_by(`Week Ending`) %>%
  summarise(totOPclaims = sum(Claims))

JP.claims <- lwc.ui.Parish.611 %>% 
  filter(`Claim Type` == "Initial") %>%
  filter(Parish == "Jefferson Parish")

JP.totclaims <- lwc.ui.Parish.611 %>% 
  filter(Parish == "Jefferson Parish")%>%
  group_by(`Week Ending`) %>%
  summarise(totJPclaims = sum(Claims))


NOmsa.claims <- lwc.ui.Parish.611 %>% 
  filter(`Claim Type` == "Initial",
         Parish %in% c("Orleans Parish",
                                      "Jefferson Parish",
                                      "St. Tammany Parish",
                                      "St. John the Baptist Parish",
                                      "St. Bernard Parish",
                                      "Plaquemines Parish",
                                      "St. Charles Parish",
                                      "St. James Parish")) %>%
  group_by(`Week Ending`) %>%
  summarise(Claims = sum(Claims))

## both continued & initial claims
NOmsa.totclaims <- lwc.ui.Parish.611 %>% 
  filter(Parish %in% c("Orleans Parish",
                                      "Jefferson Parish",
                                      "St. Tammany Parish",
                                      "St. John the Baptist Parish",
                                      "St. Bernard Parish",
                                      "Plaquemines Parish",
                                      "St. Charles Parish",
                                      "St. James Parish")) %>%
  group_by(`Week Ending`) %>%
  summarise(totNOmsaclaims = sum(Claims))


```

```{r}

## expected number of weekly initial claims
OP.claims.ex <- LA.ind.claims %>% 
  left_join(LA.ind.emp, by=c("NAICS" = "industry_code")) %>% 
  filter(Industry != "Unclassified") %>% 
  mutate(LAUIclaims.p = LAUIclaims/LAemp) %>%
  left_join(OP.ind.emp, by =c("NAICS" = "industry_code")) %>%
  mutate(expOPclaims.ind = OPemp * LAUIclaims.p) %>%
  ungroup() %>%
  group_by(`Week Ending`) %>%
  summarise(expOPclaims = sum(expOPclaims.ind)) %>%
  left_join(OP.claims, by = c("Week Ending")) %>%
  left_join(LA.claims, by = c("Week Ending")) %>%
  mutate(totLAemp = LA.emp$totLAemp,
         totOPemp = OP.emp$totOPemp) %>%
  mutate(pctLAclaims = totLAclaims/totLAemp,
         expOPclaims.overall = pctLAclaims * totOPemp)

OP.totclaims.ex <- LA.ind.totclaims %>% 
  left_join(LA.ind.emp, by=c("NAICS" = "industry_code")) %>% 
  filter(Industry != "Unclassified") %>% 
  mutate(LAUIclaims.p = LAUIclaims/LAemp) %>%
  left_join(OP.ind.emp, by =c("NAICS" = "industry_code")) %>%
  mutate(expOPclaims.ind = OPemp * LAUIclaims.p) %>%
  ungroup() %>%
  group_by(`Week Ending`) %>%
  summarise(expOPclaims = sum(expOPclaims.ind)) %>%
  left_join(OP.totclaims, by = c("Week Ending")) %>%
  left_join(LA.totclaims, by = c("Week Ending")) %>%
  mutate(totLAemp = LA.emp$totLAemp,
         totOPemp = OP.emp$totOPemp) %>%
  mutate(pctLAclaims = totLAclaims/totLAemp,
         expOPclaims.overall = pctLAclaims * totOPemp)

### by industry
OP.ind.claims.ex <- LA.ind.claims %>% 
  left_join(LA.ind.emp, by=c("NAICS" = "industry_code")) %>% 
  filter(Industry != "Unclassified") %>% 
  mutate(LAUIclaims.p = LAUIclaims/LAemp) %>%
  left_join(OP.ind.emp, by =c("NAICS" = "industry_code")) %>%
  mutate(expOPclaims.ind = OPemp * LAUIclaims.p) 

### including continued claims
OP.ind.totclaims.ex <- LA.ind.totclaims %>% 
  left_join(LA.ind.emp, by=c("NAICS" = "industry_code")) %>% 
  filter(Industry != "Unclassified") %>% 
  mutate(LAUIclaims.p = LAUIclaims/LAemp) %>%
  left_join(OP.ind.emp, by =c("NAICS" = "industry_code")) %>%
  mutate(expOPclaims.ind = OPemp * LAUIclaims.p) 

JP.claims.ex <- LA.ind.claims %>% 
  left_join(LA.ind.emp, by=c("NAICS" = "industry_code")) %>% 
  filter(Industry != "Unclassified") %>% 
  mutate(LAUIclaims.p = LAUIclaims/LAemp) %>%
  left_join(JP.ind.emp, by =c("NAICS" = "industry_code")) %>%
  mutate(expJPclaims.ind = JPemp * LAUIclaims.p) %>%
  ungroup() %>%
  group_by(`Week Ending`) %>%
  summarise(expJPclaims = sum(expJPclaims.ind)) %>%
  left_join(JP.claims, by = c("Week Ending"))

JP.ind.totclaims.ex <- LA.ind.totclaims %>% 
  left_join(LA.ind.emp, by=c("NAICS" = "industry_code")) %>% 
  filter(Industry != "Unclassified") %>% 
  mutate(LAUIclaims.p = LAUIclaims/LAemp) %>%
  left_join(JP.ind.emp, by =c("NAICS" = "industry_code")) %>%
  mutate(expJPclaims.ind = JPemp * LAUIclaims.p) 

JP.totclaims.ex <- LA.ind.totclaims %>% 
  left_join(LA.ind.emp, by=c("NAICS" = "industry_code")) %>% 
  filter(Industry != "Unclassified") %>% 
  mutate(LAUIclaims.p = LAUIclaims/LAemp) %>%
  left_join(JP.ind.emp, by =c("NAICS" = "industry_code")) %>%
  mutate(expJPclaims.ind = JPemp * LAUIclaims.p) %>%
  ungroup() %>%
  group_by(`Week Ending`) %>%
  summarise(expJPclaims = sum(expJPclaims.ind)) %>%
  left_join(JP.totclaims, by = c("Week Ending")) %>%
  left_join(LA.totclaims, by = c("Week Ending")) %>%
  mutate(totLAemp = LA.emp$totLAemp,
         totJPemp = JP.emp$totJPemp) %>%
  mutate(pctLAclaims = totLAclaims/totLAemp,
         expJPclaims.overall = pctLAclaims * totJPemp)

NOmsa.claims.ex <- LA.ind.claims %>% 
  left_join(LA.ind.emp, by=c("NAICS" = "industry_code")) %>% 
  filter(Industry != "Unclassified") %>% 
  mutate(LAUIclaims.p = LAUIclaims/LAemp) %>%
  left_join(NOmsa.ind.emp, by =c("NAICS" = "industry_code")) %>%
  mutate(expNOmsaclaims.ind = NOmsaemp * LAUIclaims.p) %>%
  ungroup() %>%
  group_by(`Week Ending`) %>%
  summarise(expNOmsaclaims = sum(expNOmsaclaims.ind)) %>%
  left_join(NOmsa.claims, by = c("Week Ending")) %>%
  left_join(LA.claims, by = c("Week Ending")) %>%
  mutate(totLAemp = LA.emp$totLAemp,
         totMSAemp = NOmsa.emp$totMSAemp) %>%
  mutate(pctLAclaims = totLAclaims/totLAemp,
         expNOmsaclaims.overall = pctLAclaims * totMSAemp)

NOmsa.totclaims.ex <- LA.ind.totclaims %>% 
  left_join(LA.ind.emp, by=c("NAICS" = "industry_code")) %>% 
  filter(Industry != "Unclassified") %>% 
  mutate(LAUIclaims.p = LAUIclaims/LAemp) %>%
  left_join(NOmsa.ind.emp, by =c("NAICS" = "industry_code")) %>%
  mutate(expNOmsaclaims.ind = NOmsaemp * LAUIclaims.p) %>%
  ungroup() %>%
  group_by(`Week Ending`) %>%
  summarise(expNOmsaclaims = sum(expNOmsaclaims.ind)) %>%
  left_join(NOmsa.totclaims, by = c("Week Ending")) %>%
  left_join(LA.totclaims, by = c("Week Ending")) %>%
  mutate(totLAemp = LA.emp$totLAemp,
         totMSAemp = NOmsa.emp$totMSAemp) %>%
  mutate(pctLAclaims = totLAclaims/totLAemp,
         expNOmsaclaims.overall = pctLAclaims * totMSAemp)


NOmsa.ind.claims.ex <- LA.ind.claims %>% 
  left_join(LA.ind.emp, by=c("NAICS" = "industry_code")) %>% 
  filter(Industry != "Unclassified") %>% 
  mutate(LAUIclaims.p = LAUIclaims/LAemp) %>%
  left_join(NOmsa.ind.emp, by =c("NAICS" = "industry_code")) %>%
  mutate(expMSAclaims.ind = NOmsaemp * LAUIclaims.p) 

### including continued claims
NOmsa.ind.totclaims.ex <- LA.ind.totclaims %>% 
  left_join(LA.ind.emp, by=c("NAICS" = "industry_code")) %>% 
  filter(Industry != "Unclassified") %>% 
  mutate(LAUIclaims.p = LAUIclaims/LAemp) %>%
  left_join(NOmsa.ind.emp, by =c("NAICS" = "industry_code")) %>%
  mutate(expMSAclaims.ind = NOmsaemp * LAUIclaims.p) 
```

The expected claims is the sum of claims across industries if, at the parish level, the industry's workers were claiming at the same rate as workers at the state level. This, so far, seems like the most straightforward and practical way to project claim rates down to the local level. Not very exciting.   

So, yes, especially at the beginning, New Orleans had more claims than you'd expect given our industry composition.   

```{r}
actVexp.OP.line <- OP.claims.ex %>%
  filter(`Week Ending` != "2020-03-07") %>%
  select(`Week Ending`, expOPclaims, Claims, expOPclaims.overall) %>%
  gather(-`Week Ending`, key = expORact, value = claims) %>%
  ggplot(aes(`Week Ending`, claims, fill = expORact)) +
    geom_line(aes(color = expORact), size = 1) +
    scale_y_continuous(labels = scales::comma, limits = c(0,15000)) +
  scale_color_manual(name = "",labels = c("Actual", "Expected by industry", "Expected by overall"),values = c(DCcolor.p1darkblue70, DCcolor.p1skyblue, DCcolor.p2blue80)) +
  coord_cartesian(clip = 'off') + 
  themeDC_horizontal() +
  theme(plot.margin = margin(0, 1, 0, 0, "cm"),
        legend.title = element_blank()) + 
  #theme(axis.title.y = element_text(angle=0, vjust = .5, hjust=0)) + 
  #theme(plot.caption = element_text( face = "italic", size = 7.5, family = "Asap")) +
  labs(x="",
       y="",
       #y="Unemployment insurance\ninitial claims by week"
       title = "Unemployment insurance initial weekly claims, New Orleans",
       subtitle="Actual vs. expected, based on state-wide claims by industry")

  ggsave(actVexp.OP.line, filename = "outputs/emp data June update/actVexpClaimsOP.png", type = "cairo",
       width = 8, height = 5, units = "in")
actVexp.OP.line
```

```{r}
actVexp.OP.diffline <- OP.totclaims.ex %>%
  mutate(claimDiff = abs(expOPclaims.overall - totOPclaims)) %>%
  filter(claimDiff == max(claimDiff, na.rm = TRUE))

actVexpTot.OP.line <- OP.totclaims.ex %>%
  filter(`Week Ending` != "2020-03-07") %>%
  select(`Week Ending`, expOPclaims, totOPclaims, expOPclaims.overall) %>%
  gather(-`Week Ending`, key = expORact, value = claims) %>%
  mutate(numType = NA,
         numType = ifelse(expORact == "totOPclaims", "Actual", numType),
         numType = ifelse(expORact == "expOPclaims", "Expected by industry", numType),
         numType = ifelse(expORact == "expOPclaims.overall", "Expected by overall", numType)) %>%
  ggplot(aes(x=`Week Ending`, y=claims, fill = numType)) +
    geom_line(aes(color = numType), size = 1) +
  geom_segment(data = actVexp.OP.diffline, inherit.aes = FALSE, aes(x = `Week Ending`, y = expOPclaims.overall, xend = `Week Ending`, yend = totOPclaims), linetype = "dotted") + 
  annotate("text", x = actVexp.OP.diffline$`Week Ending`, y = 38000, label = "Over 11,000 more claims than expected", size = 3, hjust = -.04, family = "Asap") +
    scale_y_continuous(labels = scales::comma) +
  scale_color_manual(name = "", values = c(DCcolor.p1darkblue70, DCcolor.p1skyblue, DCcolor.p2blue80)) +
  coord_cartesian(clip = 'off') + 
  themeDC_horizontal() +
  theme(plot.margin = margin(0, 1, 0, 0, "cm"),
        legend.title = element_blank()) + 
  #theme(axis.title.y = element_text(angle=0, vjust = .5, hjust=0)) + 
  #theme(plot.caption = element_text( face = "italic", size = 7.5, family = "Asap")) +
  labs(x="",
       y="",
       #y="Unemployment insurance\ninitial claims by week"
       title = "Unemployment insurance total weekly claims, New Orleans",
       subtitle="Actual vs. expected, based on state-wide claims by industry")

  ggsave(actVexpTot.OP.line, filename = "outputs/emp data June update/actVexpTotalClaimsOP.png", type = "cairo",
       width = 8, height = 5, units = "in")
actVexpTot.OP.line
```

```{r}
topNAICS <- OP.ind.totclaims.ex %>% ungroup() %>% distinct(NAICS, OPemp) %>% mutate(OPemp = as.numeric(OPemp)) %>% top_n(n=4,wt=OPemp)
expTot.ind.OP.line <- OP.ind.totclaims.ex %>%
  mutate(top.ind = ifelse(NAICS %in% topNAICS$NAICS, "top","not")) %>%
  ungroup() %>%
  filter(`Week Ending` != "2020-03-07") %>%
  # select(`Week Ending`, expOPclaims.ind, In) %>%
  ggplot(aes(x=`Week Ending`, y=expOPclaims.ind, group = Industry)) +
    geom_line(aes(color = top.ind), size = 1) +
    scale_y_continuous(labels = scales::comma) +
  scale_color_manual(name = "", labels = c("Not top 4", "Top 4 industry\nby employment"), values = c(DCcolor.p1grayblue20,DCcolor.p2blue80)) +
  geom_text(data = subset(OP.ind.totclaims.ex, `Week Ending` == "2020-04-25" & NAICS %in% topNAICS$NAICS), aes(label = Industry), size = 3, vjust = -.7) +
  coord_cartesian(clip = 'off') + 
  themeDC_horizontal() +
  theme(plot.margin = margin(0, 1, 0, 0, "cm"),
        legend.title = element_blank()) + 
  #theme(axis.title.y = element_text(angle=0, vjust = .5, hjust=0)) + 
  #theme(plot.caption = element_text( face = "italic", size = 7.5, family = "Asap")) +
  labs(x="",
       y="",
       #y="Unemployment insurance\ninitial claims by week"
       title = "Expected unemployment insurance total weekly claims by industry",
       subtitle="New Orleans")

  ggsave(expTot.ind.OP.line, filename = "outputs/emp data June update/expTotalIndustryClaimsOP.png", type = "cairo",
       width = 8, height = 5, units = "in")
expTot.ind.OP.line
```

```{r}
topNAICS <- JP.ind.totclaims.ex %>% ungroup() %>% distinct(NAICS, JPemp) %>% mutate(JPemp = as.numeric(JPemp)) %>% top_n(n=4,wt=JPemp)
expTot.ind.JP.line <- JP.ind.totclaims.ex %>%
  mutate(top.ind = ifelse(NAICS %in% topNAICS$NAICS, "top","not")) %>%
  ungroup() %>%
  filter(`Week Ending` != "2020-03-07") %>%
  # select(`Week Ending`, expOPclaims.ind, In) %>%
  ggplot(aes(x=`Week Ending`, y=expJPclaims.ind, group = Industry)) +
    geom_line(aes(color = top.ind), size = 1) +
    scale_y_continuous(labels = scales::comma) +
  scale_color_manual(name = "", labels = c("Not top 4", "Top 4 industry\nby employment"), values = c(DCcolor.p1grayblue20,DCcolor.p2blue80)) +
  geom_text(data = subset(JP.ind.totclaims.ex, `Week Ending` == "2020-04-25" & NAICS %in% topNAICS$NAICS), aes(label = Industry), size = 3, vjust = -.7) +
  coord_cartesian(clip = 'off') + 
  themeDC_horizontal() +
  theme(plot.margin = margin(0, 1, 0, 0, "cm"),
        legend.title = element_blank()) + 
  #theme(axis.title.y = element_text(angle=0, vjust = .5, hjust=0)) + 
  #theme(plot.caption = element_text( face = "italic", size = 7.5, family = "Asap")) +
  labs(x="",
       y="",
       #y="Unemployment insurance\ninitial claims by week"
       title = "Expected unemployment insurance total weekly claims by industry",
       subtitle="Jefferson Parish")

  ggsave(expTot.ind.JP.line, filename = "outputs/emp data June update/expTotalIndustryClaimsJP.png", type = "cairo",
       width = 8, height = 5, units = "in")
expTot.ind.JP.line
```

```{r}
topNAICS <- NOmsa.ind.totclaims.ex %>% ungroup() %>% distinct(NAICS, NOmsaemp) %>% mutate(NOmsaemp = as.numeric(NOmsaemp)) %>% top_n(n=4,wt=NOmsaemp)
expTot.ind.NOmsa.line <- NOmsa.ind.totclaims.ex %>%
  mutate(top.ind = ifelse(NAICS %in% topNAICS$NAICS, "top","not")) %>%
  ungroup() %>%
  filter(`Week Ending` != "2020-03-07") %>%
  # select(`Week Ending`, expNOmsaclaims.ind, In) %>%
  ggplot(aes(x=`Week Ending`, y=expMSAclaims.ind, group = Industry)) +
    geom_line(aes(color = top.ind), size = 1) +
    scale_y_continuous(labels = scales::comma) +
  scale_color_manual(name = "", labels = c("Not top 4", "Top 4 industry\nby employment"), values = c(DCcolor.p1grayblue20,DCcolor.p2blue80)) +
  geom_text(data = subset(NOmsa.ind.totclaims.ex, `Week Ending` == "2020-04-25" & NAICS %in% topNAICS$NAICS), aes(label = Industry), size = 3, vjust = -.7) +
  coord_cartesian(clip = 'off') + 
  themeDC_horizontal() +
  theme(plot.margin = margin(0, 1, 0, 0, "cm"),
        legend.title = element_blank()) + 
  #theme(axis.title.y = element_text(angle=0, vjust = .5, hjust=0)) + 
  #theme(plot.caption = element_text( face = "italic", size = 7.5, family = "Asap")) +
  labs(x="",
       y="",
       #y="Unemployment insurance\ninitial claims by week"
       title = "Expected unemployment insurance total weekly claims by industry",
       subtitle="Metro New Orleans")

  ggsave(expTot.ind.NOmsa.line, filename = "outputs/emp data June update/expTotalIndustryClaimsNOmsa.png", type = "cairo",
       width = 8, height = 5, units = "in")
expTot.ind.NOmsa.line
```

```{r}
NOmsa.ind.totclaims.ex %>%
  mutate(top.ind = ifelse(NAICS %in% topNAICS$NAICS, "top","not")) %>%
  ungroup() %>%
  filter(`Week Ending` != "2020-03-07") %>%
  group_by(`Week Ending`) %>%
  summarise(totEx = sum(expMSAclaims.ind)) %>%
  # select(`Week Ending`, expNOmsaclaims.ind, In) %>%
  ggplot(aes(x=`Week Ending`, y=totEx)) +
    geom_line(size = 1) +
    scale_y_continuous(labels = scales::comma) +
  # scale_color_manual(name = "", labels = c("Not top 4", "Top 4 industry\nby employment"), values = c(DCcolor.p1grayblue20,DCcolor.p2blue80)) +
  # geom_text(data = subset(NOmsa.ind.totclaims.ex, `Week Ending` == "2020-04-25" & NAICS %in% topNAICS$NAICS), aes(label = Industry), size = 3, vjust = -.7) +
  coord_cartesian(clip = 'off') + 
  themeDC_horizontal() +
  theme(plot.margin = margin(0, 1, 0, 0, "cm"),
        legend.title = element_blank()) + 
  #theme(axis.title.y = element_text(angle=0, vjust = .5, hjust=0)) + 
  #theme(plot.caption = element_text( face = "italic", size = 7.5, family = "Asap")) +
  labs(x="",
       y="",
       #y="Unemployment insurance\ninitial claims by week"
       title = "Expected unemployment insurance total weekly claims by industry",
       subtitle="Metro New Orleans")
```

```{r}
actVexp.JP.diffline <- JP.totclaims.ex %>%
  mutate(claimDiff = abs(expJPclaims.overall - totJPclaims)) %>%
  filter(claimDiff == max(claimDiff, na.rm = TRUE))

actVexpTot.JP.line <- JP.totclaims.ex %>%
  filter(`Week Ending` != "2020-03-07") %>%
  select(`Week Ending`, expJPclaims, totJPclaims, expJPclaims.overall) %>%
  gather(-`Week Ending`, key = expORact, value = claims) %>%
  mutate(numType = NA,
         numType = ifelse(expORact == "totJPclaims", "Actual", numType),
         numType = ifelse(expORact == "expJPclaims", "Expected by industry", numType),
         numType = ifelse(expORact == "expJPclaims.overall", "Expected by overall", numType)) %>%
  ggplot(aes(x=`Week Ending`, y=claims, fill = numType)) +
    geom_line(aes(color = numType), size = 1) +
  geom_segment(data = actVexp.JP.diffline, inherit.aes = FALSE, aes(x = `Week Ending`, y = expJPclaims.overall, xend = `Week Ending`, yend = totJPclaims), linetype = "dotted") + 
  annotate("text", x = actVexp.JP.diffline$`Week Ending`, y = 38000, label = "Over 11,000 more claims than expected", size = 3, hjust = -.04, family = "Asap") +
    scale_y_continuous(labels = scales::comma) +
  scale_color_manual(name = "", values = c(DCcolor.p1darkblue70, DCcolor.p1skyblue, DCcolor.p2blue80)) +
  coord_cartesian(clip = 'off') + 
  themeDC_horizontal() +
  theme(plot.margin = margin(0, 1, 0, 0, "cm"),
        legend.title = element_blank()) + 
  #theme(axis.title.y = element_text(angle=0, vjust = .5, hjust=0)) + 
  #theme(plot.caption = element_text( face = "italic", size = 7.5, family = "Asap")) +
  labs(x="",
       y="",
       #y="Unemployment insurance\ninitial claims by week"
       title = "Unemployment insurance total weekly claims, Jefferson Parish",
       subtitle="Actual vs. expected, based on state-wide claims by industry")

  # ggsave(actVexpTot.JP.line, filename = "outputs/emp data June update/actVexpTotalClaimsJP.png", type = "cairo",
  #      width = 8, height = 5, units = "in")
actVexpTot.JP.line
```

```{r}
indClaims.est.OP <- OP.ind.claims.ex %>%
  ungroup() %>%
  filter(NAICS %in% c("71","72")) %>%
  filter(`Week Ending` != "2020-03-07") %>%
  select(`Week Ending`, expOPclaims.ind, Industry) %>%
  ggplot(aes(`Week Ending`, expOPclaims.ind, fill = Industry)) +
    geom_line(aes(color = Industry), size = 1) +
    scale_y_continuous(labels = scales::comma) +
  scale_color_manual(values = c(DCcolor.p2purple90, DCcolor.p2violet60)) +
  geom_text(data = subset(OP.ind.claims.ex, `Week Ending` == "2020-03-28" & NAICS %in% c("71","72")), aes(label = Industry), vjust = -1) +
  themeDC_horizontal() +
  theme(legend.position = "none") + 
  #theme(axis.title.y = element_text(angle=0, vjust = .5, hjust=0)) + 
  #theme(plot.caption = element_text( face = "italic", size = 7.5, family = "Asap")) +
  labs(x="",
       y="",
       #y="Unemployment insurance\ninitial claims by week"
       title = "Expected industry-level unemployment insurance initial weekly claims, New Orleans",
       subtitle="Based on state-wide claims by industry")

ggsave(indClaims.est.OP, filename = "outputs/emp data June update/estimatedIndustryClaimsOP.png", type = "cairo",
       width = 8, height = 5, units = "in")
  
indClaims.est.OP
```

```{r}
indClaims.est.NOmsa <- NOmsa.ind.claims.ex %>%
  ungroup() %>%
  filter(NAICS %in% c("71","72")) %>%
  filter(`Week Ending` != "2020-03-07") %>%
  select(`Week Ending`, expMSAclaims.ind, Industry) %>%
  ggplot(aes(`Week Ending`, expMSAclaims.ind, fill = Industry)) +
    geom_line(aes(color = Industry), size = 1) +
    scale_y_continuous(labels = scales::comma) +
  scale_color_manual(values = c(DCcolor.p2purple90, DCcolor.p2violet70)) +
  geom_text(data = subset(NOmsa.ind.claims.ex, `Week Ending` == "2020-03-28" & NAICS %in% c("71","72")), aes(label = Industry), vjust = -1) +
  themeDC_horizontal() +
  theme(legend.position = "none") + 
  #theme(axis.title.y = element_text(angle=0, vjust = .5, hjust=0)) + 
  #theme(plot.caption = element_text( face = "italic", size = 7.5, family = "Asap")) +
  labs(x="",
       y="",
       #y="Unemployment insurance\ninitial claims by week"
       title = "Expected industry-level unemployment insurance initial weekly claims, Metro New Orleans",
       subtitle="Based on state-wide claims by industry")

ggsave(indClaims.est.NOmsa, filename = "outputs/emp data June update/estimatedIndustryClaimsNOmsa.png", type = "cairo",
       width = 8, height = 5, units = "in")
indClaims.est.NOmsa  
```

```{r}
indTotClaims.est.NOmsa  <- NOmsa.ind.totclaims.ex %>%
  ungroup() %>%
  filter(NAICS %in% c("71","72")) %>%
  filter(`Week Ending` != "2020-03-07") %>%
  select(`Week Ending`, expMSAclaims.ind, Industry) %>%
  ggplot(aes(`Week Ending`, expMSAclaims.ind, fill = Industry)) +
    geom_line(aes(color = Industry), size = 1) +
    scale_y_continuous(labels = scales::comma) +
  scale_color_manual(values = c(DCcolor.p2purple90, DCcolor.p2violet70)) +
  geom_text(data = subset(NOmsa.ind.totclaims.ex, `Week Ending` == "2020-03-28" & NAICS %in% c("71","72")), aes(label = Industry)) +
  themeDC_horizontal() +
  theme(legend.position = "none") + 
  #theme(axis.title.y = element_text(angle=0, vjust = .5, hjust=0)) + 
  #theme(plot.caption = element_text( face = "italic", size = 7.5, family = "Asap")) +
  labs(x="",
       y="",
       #y="Unemployment insurance\ninitial claims by week"
       title = "Expected industry-level unemployment insurance total weekly claims, Metro New Orleans",
       subtitle="Based on state-wide claims by industry")

ggsave(indTotClaims.est.NOmsa, filename = "outputs/emp data June update/estimatedTotalIndustryClaimsNOmsa.png", type = "cairo",
       width = 8, height = 5, units = "in")
indTotClaims.est.NOmsa 
```

```{r}
JP.claims.ex %>%
  filter(`Week Ending` != "2020-03-07") %>%
  select(`Week Ending`, expJPclaims, Claims) %>%
  gather(-`Week Ending`, key = expORact, value = claims) %>%
  ggplot(aes(`Week Ending`, claims, fill = expORact)) +
    geom_line(aes(color = expORact)) +
    scale_y_continuous(labels = scales::comma, limits = c(0,15000)) +
  scale_color_hue(name = "",labels = c("actual", "expected")) +
  coord_cartesian(clip = 'off') + 
  themeDC_horizontal() +
  theme(plot.margin = margin(0, 1, 0, 0, "cm"),
        legend.title = element_blank()) + 
  #theme(axis.title.y = element_text(angle=0, vjust = .5, hjust=0)) + 
  #theme(plot.caption = element_text( face = "italic", size = 7.5, family = "Asap")) +
  labs(x="",
       y="",
       #y="Unemployment insurance\ninitial claims by week"
       title = "Unemployment insurance initial weekly claims, Jefferson Parish",
       subtitle="Actual vs. expected, based on state-wide claims by industry")
```

```{r}
actVexp.NOmsa.line <- NOmsa.claims.ex %>%
  filter(`Week Ending` != "2020-03-07") %>%
  select(`Week Ending`, expNOmsaclaims, Claims, expNOmsaclaims.overall) %>%
  gather(-`Week Ending`, key = expORact, value = claims) %>%
  ggplot(aes(`Week Ending`, claims, fill = expORact)) +
    geom_line(aes(color = expORact), size = 1) +
    scale_y_continuous(labels = scales::comma) +
  scale_color_manual(name = "",labels = c("Actual", "Expected by industry", "Expected by overall"),values = c(DCcolor.p1darkblue70, DCcolor.p1skyblue, DCcolor.p2blue80)) +
  coord_cartesian(clip = 'off') + 
  themeDC_horizontal() +
  theme(plot.margin = margin(0, 1, 0, 0, "cm"),
        legend.title = element_blank()) + 
  #theme(axis.title.y = element_text(angle=0, vjust = .5, hjust=0)) + 
  #theme(plot.caption = element_text( face = "italic", size = 7.5, family = "Asap")) +
  labs(x="",
       y="",
       #y="Unemployment insurance\ninitial claims by week"
       title = "Unemployment insurance initial weekly claims, New Orleans metro",
       subtitle="Actual vs. expected, based on state-wide claims by industry")

ggsave(actVexp.NOmsa.line, filename = "outputs/emp data June update/actVexpClaimsNOmsa.png", type = "cairo",
       width = 8, height = 5, units = "in")

actVexp.NOmsa.line
```

```{r}
actVexpTot.NOmsa.diffline <- NOmsa.totclaims.ex %>%
  mutate(claimDiff = abs(expNOmsaclaims.overall - totNOmsaclaims)) %>%
  filter(claimDiff == max(claimDiff, na.rm = TRUE))

actVexpTot.NOmsa.line <- NOmsa.totclaims.ex %>%
  filter(`Week Ending` != "2020-03-07") %>%
  select(`Week Ending`, expNOmsaclaims, totNOmsaclaims, expNOmsaclaims.overall) %>%
  gather(-`Week Ending`, key = expORact, value = claims) %>%
  mutate(numType = NA,
         numType = ifelse(expORact == "totNOmsaclaims", "Actual", numType),
         numType = ifelse(expORact == "expNOmsaclaims", "Expected by industry", numType),
         numType = ifelse(expORact == "expNOmsaclaims.overall", "Expected by overall", numType),
         lnsz = ifelse(numType == "Actual", 1.1, .7)) %>%
  ggplot(aes(x=`Week Ending`, y=claims, fill = numType)) +
    geom_line(aes(color = numType, size = lnsz)) +
  scale_size(range = c(.7, 1.1), guide="none") +
  geom_segment(data = actVexpTot.NOmsa.diffline, inherit.aes = FALSE, aes(x = `Week Ending`, y = expNOmsaclaims.overall, xend = `Week Ending`, yend = totNOmsaclaims), linetype = "dotted") + 
  annotate("text", x = actVexpTot.NOmsa.diffline$`Week Ending`, y = 115000, label = "Over 30,000 more claims than expected", size = 3, hjust = -.04, family = "Asap") +
    scale_y_continuous(labels = scales::comma) +
  scale_color_manual(name = "", values = c(DCcolor.p1darkblue80, DCcolor.p2orangered40, DCcolor.p2magenta60)) +
  coord_cartesian(clip = 'off') + 
  themeDC_horizontal() +
  theme(plot.margin = margin(0, 1, 0, 0, "cm"),
        legend.title = element_blank()) + 
  #theme(axis.title.y = element_text(angle=0, vjust = .5, hjust=0)) + 
  #theme(plot.caption = element_text( face = "italic", size = 7.5, family = "Asap")) +
  labs(x="",
       y="",
       #y="Unemployment insurance\ninitial claims by week"
       title = "Unemployment insurance total weekly claims, Metro New Orleans",
       subtitle="'Expected' numbers based on state-wide claims by industry")

ggsave(actVexpTot.NOmsa.line, filename = "outputs/emp data June update/actVexpTotalClaimsNOmsa.png", type = "cairo",
       width = 8, height = 5, units = "in")

actVexpTot.NOmsa.line
```

You can see that in claims over time, accommodation & food services took the hugest initial hit, then was tapering off as other industries' claims surged.   


```{r}
# LA.ind.claims %>%
#   ungroup() %>%
#   mutate(Industry = str_sub(Industry,1,15)) %>%
#   mutate(ind.lev = ifelse(Industry == ))
#   ggplot(aes(`Week Ending`,LAUIclaims), group = Industry) +
#     geom_line(aes(color = Industry)) +
#   geom_text_repel(data = subset(LA.ind.plot, LAUIclaims > 10000 & `Week Ending` == "2020-03-28"), aes(label = Industry)) +
#     scale_y_continuous(labels = scales::comma) +
#   coord_cartesian(clip = 'off') + 
#   themeDC_horizontal() +
#   theme(legend.position = "none") + 
#   labs(x="",
#        y="",
#        #y="Unemployment insurance\ninitial claims by week"
#        title = "Unemployment insurance initial weekly claims in LA by industrys")
```

```{r}
LA.ind.plot <- LA.ind.claims %>%
  ungroup() %>%
  left_join(LA.ind.emp, by = c("NAICS" = "industry_code")) %>%
  filter(Industry != "Unclassified") %>% 
  mutate(pctClaiming = LAUIclaims/LAemp)

LA.ind.plot %>%
  ggplot(aes(`Week Ending`,pctClaiming), group = Industry) +
    geom_line(aes(color = Industry)) +
  geom_text_repel(data = subset(LA.ind.plot, pctClaiming > .07 & `Week Ending` == "2020-03-28"), aes(label = Industry)) +
  themeDC_horizontal() +
  theme(legend.position = "none") + 
  labs(x="",
       y="",
       #y="Unemployment insurance\ninitial claims by week"
       title = "Percent of industry workers making initial weekly unemployment insurance claims")
```


See below the industry employment breakdown. New Orleans, of course, has a concentration of accommodation & food services.  

```{r}
workforceComp.bars <- OP.ss.emp %>%
  left_join(LA.ss.emp) %>%
  left_join(JP.ss.emp) %>%
  left_join(NOmsa.ss.emp) %>%
  mutate(totOP = sum(OPemp),
         totJP = sum(JPemp),
         totLA = sum(LAemp),
         totNOmsa = sum(NOmsaemp),
         pctJP = JPemp/totJP,
         pctOP = OPemp/totOP,
         pctNOmsa = NOmsaemp/totNOmsa,
         pctLA = LAemp/totLA) %>%
  left_join(ss.codes, by = c("industry_code" = "code")) %>%
  filter(Industry != "Unclassified") %>%
  select(Industry, LA = pctLA, OP = pctOP, JP = pctJP, NOmetro = pctNOmsa) %>%
  distinct() %>%
  ungroup() %>%
  # mutate(Industry = str_sub(Industry,1,20)) %>%
  gather(-Industry, key = comp, value = pct) %>%
  mutate(geo = NA,
         geo = ifelse(comp == "LA", "Louisiana",geo),
         geo = ifelse(comp == "OP", "New Orleans", geo),
         geo = ifelse(comp == "JP", "Jefferson Parish", geo),
         geo = ifelse(comp == "NOmetro", "Metro New Orleans", geo)) %>%
  mutate(geo.fac = factor(.$geo, levels = c("Louisiana", "Metro New Orleans", "New Orleans", "Jefferson Parish"))) %>%
  ggplot(aes(geo.fac, pct, fill = Industry)) +
  scale_fill_manual(values = c(DCcolor.p1darkblue70,
                               DCcolor.p2purple70,
                               DCcolor.p1lightskyblue,
                               DCcolor.p2blue80,
                               DCcolor.p2orangered90,
                               DCcolor.p2teal80,
                               DCcolor.p2green80,
                               DCcolor.p2limegreen,
                               DCcolor.p2yellow90,
                               DCcolor.p2violet80,
                               DCcolor.p1skyblue)) +
    scale_y_continuous(labels = scales::percent) +
  geom_bar(stat = "identity",
           position = position_stack()) +
  themeDC_horizontal() +
  theme(legend.title = element_blank(),
        # legend.position = "right",
        legend.key.size = unit(.3,"cm")) +
  labs(x = "",
       y = "",
       title = "Industry composition of workforce, September 2019")
  
ggsave(workforceComp.bars, filename = "outputs/emp data June update/workforceComp.png", type = "cairo",
       width = 8, height = 5, units = "in")

workforceComp.bars  
```   



#### Updating Robby's

```{r}

lwc.ui.geoComp_bars <- lwc.ui.Industry.611 %>% 
  filter(`Week Ending` == max(as.Date(`Week Ending`))) %>%
  group_by(Industry, `Week Ending`) %>% 
  summarise(totClaims = sum(Claims)) %>%
  ungroup() %>%
  group_by(Industry) %>%
  summarise(avgClaims= mean(totClaims)) %>% 
  left_join(NOmsa.ind.totclaims.ex %>% 
              filter(`Week Ending` == max(as.Date(`Week Ending`))) %>%
              group_by(Industry, `Week Ending`) %>%   
              summarise(totClaims = sum(expMSAclaims.ind)) %>%
              ungroup() %>%
              group_by(Industry) %>%
              summarise(avgMSAClaims= mean(totClaims)), by = "Industry") %>%
  left_join(OP.ind.totclaims.ex %>% 
              filter(`Week Ending` == max(as.Date(`Week Ending`))) %>%
              group_by(Industry, `Week Ending`) %>%   
              summarise(totClaims = sum(expOPclaims.ind)) %>%
              ungroup() %>%
              group_by(Industry) %>%
              summarise(avgOPClaims= mean(totClaims)), by = "Industry")%>%
  filter(Industry != "Unclassified") %>%
  select(Industry, avgClaims, avgMSAClaims, avgOPClaims) %>%
  gather(-Industry, key = geo, value = claims) %>%
  mutate(geo = ifelse(geo == "avgClaims", "Louisiana", geo),
         geo = ifelse(geo == "avgMSAClaims", "Metro New Orleans", geo),
         geo = ifelse(geo == "avgOPClaims", "New Orleans", geo)) %>%
  ggplot(aes(x=reorder(Industry, claims), claims)) + 
  geom_col(aes(fill = geo), position = "dodge") +
  scale_fill_manual(values = c("Louisiana"=DCcolor.p1mediumblue,
                               "Metro New Orleans" =DCcolor.p2yellow,
                               "New Orleans" = DCcolor.p2purple70)) +
  scale_y_continuous(labels = scales::comma) +
  coord_flip() + 
  themeDC_vertical() +
  theme(legend.position = "top",
        legend.title = element_blank(),
        legend.justification = "left",
        legend.key.size = unit(.3,"cm")) +
  labs(x="", 
       y="Total claims",
       title = "Unemployment insurance claims by industry, week ending June 6",
       subtitle = "Metro and city claims extrapolated from state data")

  ggsave(lwc.ui.geoComp_bars, filename = "outputs/emp data June update/lwc.ui.geoComp_bars.png", type = "cairo",
       width = 8, height = 5, units = "in")

lwc.ui.geoComp_bars
```

```{r}
 qcew.preCOVID.LA %>% 
  filter(agglvl_code == 54) %>% 
  group_by(industry_code) %>% 
  summarise(emp = sum(month3_emplvl, na.rm = TRUE)) %>% 
  left_join(lwc.ui.Industry.611, by=c("industry_code"="NAICS")) %>% 
  filter(Industry != "Unclassified",
         `Week Ending` == "2020-04-11") %>% 
  group_by(Industry) %>%
  summarise(emp = sum(emp)/2,
            UIclaims = sum(Claims))%>% 
  mutate(UIclaims.p = UIclaims/emp) %>% 
  ggplot(aes(x=reorder(Industry, UIclaims.p), UIclaims.p)) + 
  geom_col(fill = DCcolor.p1mediumblue) + 
  scale_y_continuous(labels = scales::percent_format(accuracy = 1)) +
  coord_flip() + 
  themeDC_vertical() +
  labs(x="", 
       y="Number of UI claims relative to \neligible employment in Sep 2019",
       title = "Comparing unemployment insurance claims by industry \nto pre-COVID covered employment, Louisiana",
       subtitle = "Claims from week ending April 18, 2020 and covered employment in Sep, 2019")
  

lwc.ui.vsQCEW_bars.updated <- qcew.preCOVID.LA %>% 
  filter(agglvl_code == 54) %>% 
  group_by(industry_code) %>% 
  summarise(emp = sum(month3_emplvl, na.rm = TRUE)) %>% 
  left_join(lwc.ui.Industry.611, by=c("industry_code"="NAICS")) %>% 
  filter(Industry != "Unclassified",
         `Week Ending` == max(as.Date(`Week Ending`))) %>% 
  group_by(Industry) %>%
  summarise(emp = sum(emp)/2,
            UIclaims = sum(Claims))%>% 
  mutate(UIclaims.p = UIclaims/emp) %>% 
  ggplot(aes(x=reorder(Industry, UIclaims.p), UIclaims.p)) + 
  geom_col(fill = DCcolor.p1mediumblue) + 
  scale_y_continuous(labels = scales::percent_format(accuracy = 1)) +
  coord_flip() + 
  themeDC_vertical() +
  labs(x="", 
       y="Number of UI claims relative to \neligible employment in Sep 2019",
       title = "Comparing unemployment insurance claims by industry \nto pre-COVID covered employment, Louisiana",
       subtitle = "Claims from week ending June 6, 2020 and covered employment in Sep, 2019")

ggsave(lwc.ui.vsQCEW_bars.updated, filename ="outputs/emp data June update/lwcUIclaimsByIndustryRelativeQCEW.png", type = "cairo",
       width = 8, height = 4.5, units = "in")

lwc.ui.vsQCEW_bars.updated
```

