---
title: "BLS and UI data after COVID"
author: "Robby"
date: "March 25, 2020"
output:
  word_document: default
  html_document:
    df_print: paged
---

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

```

```{r}
# Load libraries
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

# Note this will probably cause errors when knitting
file.path <- "C:/Users/Robert Habans/OneDrive - Nonprofit Knowledge Works/covid/Rproject/covid-19"
source(paste0(file.path,"/03_graphics themes.R"))

```



## Objective

UI claims data:

- Pull in historical trend from FRED.
- Come up with a workflow for manually adding the most recent data from either LWC or [OUI](https://oui.doleta.gov/unemploy/claims.asp) (or both). 

Additional employment data:

- BLS series from the LAUS and SAE

For figures:

- Pull in graphics templates.
- Export figures in 1200 dpi. Rachel said she figured out how to do this. 


## Unemployment insurance initial and continued claims

LWC has a table but automated scraping could be difficult. You may be able to save it locally and then scrape the HTML. Federal OUI latter has the updated version in PDF format. You may be able to load this and extract the text in the appendix using PDF tools. 

First, let's pull in the historical data. This is available on OUI website as excel, but I can't find a stable feed. Probably best to use FRED to ensure replication. Sidenote: fredr is super easy to use. Because state postal codes are used in the series ID LACLAIMS, you could probably paste `state.abb` and "ICLAIMS" to download all states and make a nice long dataframe.

```{r}
## set api key
fredr_set_key("1ef9bc6f124dd4a5eda9bf1879404017")

la.initialUIclaims <- fredr(
  series_id = "LAICLAIMS",
  observation_start = as.Date("2000-01-01")) %>%
  mutate(datasource = "OUI/FRED")
```

Map functions can be used to download multiple data series at the same time.

```{r, eval=FALSE}
# This should work but it will download everything.
initialUIclaims.test <- 
  map_dfr(c("LAICLAIMS", "CAICLAIMS"), fredr)

# It's better to define parameters. This works great.
params <- list(
  series_id = c("LAICLAIMS", "CAICLAIMS"),
  observation_start = as.Date("2020-01-01")
)

pmap_dfr(
  .l = params,
  .f = ~ fredr(series_id = .x, observation_start = .y)
)


```

Note that this is not seasonally adjusted. I think the state date is not seasonally adjusted. I had verified that the FRED data matches the data on the LWC website. 


### Manual update

Now comes the tricky part. You want to develop a replicable way to update the data with the least effort possible. First, try manually updating. Be sure to enter the date for "the week ending in" not the date of the news release. 

Update with latest Thursday number in this PDF: https://www.dol.gov/ui/data.pdf

6/26/2020 UPDATE: FRED seems to be posting the claims on the current basis, so there appears to be no need for manually updating. I was a little behind the Thursday update, so not sure if it just takes a day now. In any case, it's good! 

```{r}
# Manual data entry
#UIlastWeek.date.oui <- ymd("2020-07-11")
UIlastWeek.date.oui <- la.initialUIclaims %>% filter(date == max(date)) %>% pull(date)

#UIlastWeek.claims.oui <- 23242
#UIlastWeek.source.oui <- "OUI"

# Append table
# la.initialUIclaims.updated <- la.initialUIclaims %>% 
#   add_row(date = UIlastWeek.date.oui,
#           value = UIlastWeek.claims.oui,
#           datasource = UIlastWeek.source.oui) 

# test
# la.initialUIclaims.updated %>% 
#   filter(year(date) == "2020") %>% print(n=100)

# How many in total?
# la.initialUIclaims.updated %>% 
#   filter(date > as.Date("2020-03-14")) %>% 
#   summarise(sum(value))

la.initialUIclaims %>%
  filter(date > as.Date("2020-03-14")) %>%
  summarise(sum(value))


```

Next, try scraping from the PDF? It would be better than manual data entry.

### Make a graphic

Design decisions:

- There should be annotations. 
- will the title make it clear what the Y axis is? If so, you don't need a y-axis. If not, might need a detailed axis label.
- Right now, I'm using geom_text_repel to label before and the most recent point, but this might not be the best strategy. 
- Rachel did some custom themes for her charts that specified the size of the text etc. 

```{r}

# UIclaimsCaptionText <- 
#   "Sources: \n- Most recent week: US Department of Labor U.S. Employment and Training Administration; https://www.dol.gov/ui/data.pdf.\n- Previous weeks: U.S. Employment and Training Administration, Initial Claims in Louisiana [LAICLAIMS], \nretrieved from FRED, Federal Reserve Bank of St. Louis; https://fred.stlouisfed.org/series/LAICLAIMS.\n \nNote that claims for the most recent week is an advance figure that is not directly comparable to claims reported in previous weeks\nand is subject to revision."

# Define labelled points
la.initialUIclaims.updated.labels <- #la.initialUIclaims.updated %>% 
  la.initialUIclaims %>% 
  filter(date == "2020-03-07" |
         #date == "2020-04-04" |
         #date == UIlastWeek.date.oui
         date == max(date)) 

## Katrina and Recession view

la.initialUIclaims.updated.since2000 <-
  #la.initialUIclaims.updated %>% 
  la.initialUIclaims %>% 
  
  # Geoms
  ggplot(aes(date, value)) + 
  #geom_vline(xintercept = ymd("2020-03-14"), color="grey70") + 
  geom_line(color = DCcolor.p1mediumblue) + 
  
  # Annotations and labels
  annotate("text", x = ymd("2005-08-29"), y = 65000, label = "Hurricane Katrina", hjust = -.05, family = "Asap", color="grey40") + 
  annotate("text", x = ymd("2008-08-29"), y = 30000, label = "Great Recession", hjust = -.05, family = "Asap", color="grey40") + 
  annotate("text", x = ymd("2020-03-07"), y = 75000, label = "COVID-19", hjust = 1.05, family = "Asap", color="grey40") + 
  #geom_text_repel(data = la.initialUIclaims.updated.labels, aes(label=value), family = "Asap") + 
  geom_text(data = la.initialUIclaims.updated.labels, aes(label=scales::comma(value)), family = "Asap", vjust = -.5) + 

  
  # Styles and themes
  #xlim(ymd("2008-06-01"),ymd("2008-12-31")) # Just for zooming in.
  scale_y_continuous(labels = scales::comma, limits = c(0,110000)) +
  themeDC_horizontal() +
  coord_cartesian(clip = 'off') + 
  theme(plot.margin = margin(0, 1, 0, 0, "cm")) + 

  #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 in Louisiana since 2000",
       subtitle=paste0("Advance state claims through ",format(as.Date(UIlastWeek.date.oui), "%A, %b %d, %Y"), (" (not seasonally adjusted)"))) #+ 
  ggsave(filename = "outputs/UIclaimsSince2k.png", type = "cairo",
       width = 8, height = 5, units = "in")
  

## Recent view

la.initialUIclaims.updated.lastyear <-
  #la.initialUIclaims.updated %>% 
  la.initialUIclaims %>% 
  
  #filter(date >= ymd("2015-01-01")) %>% # Filter by arbitrary amount 
  filter(date >= UIlastWeek.date.oui - years(1)) %>% # filter by one year
  
  # Geoms
  ggplot(aes(date, value)) + 
  geom_vline(xintercept = ymd("2020-03-07"), color="grey80") + 
  geom_line(color = DCcolor.p1mediumblue) + 
  
  # Annotations and labels
  #annotate("text", x = ymd("2005-08-29"), y = 60000, label = "Hurricane Katrina", hjust = -.05, family = "Asap", color="grey40") + 
  #annotate("text", x = ymd("2008-08-29"), y = 30000, label = "Great Recession", hjust = -.05, family = "Asap", color="grey40") + 
  annotate("text", x = ymd("2020-03-03"), y = 50000, label = "The initial effect of COVID-19\nbegan the week of March 7", 
           hjust = 1, 
           family = "Asap", color="grey40") + 
  geom_text(data = la.initialUIclaims.updated.labels, aes(label=scales::comma(value)), family = "Asap", vjust = -.5) + 
  
  # Styles and themes
  #xlim(ymd("2008-06-01"),ymd("2008-12-31")) # Just for zooming in.
  scale_y_continuous(labels = scales::comma, limits = c(0,110000)) +
  coord_cartesian(clip = 'off') + 
  themeDC_horizontal() +
  theme(plot.margin = margin(0, 1, 0, 0, "cm")) + 
  #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 in Louisiana, previous 12 months",
       subtitle=paste0("Advance state claims through ",format(as.Date(UIlastWeek.date.oui), "%A, %b %d, %Y"), (" (not seasonally adjusted)"))) #+
  ggsave(filename = "outputs/UIclaimsLastyear.png", type = "cairo",
       width = 8, height = 5, units = "in")


```

## 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.

Import the spreadsheets:

```{r}
url <- "http://www.laworks.net/Downloads/LMI/Claims_by_Industry_Parish.xlsx"
destfile <- "Claims_by_Industry_Parish.xlsx"
curl::curl_download(url, destfile)
lwc.ui.Industry.excel <- read_excel(destfile, 
                                        sheet = "Industry",
    col_types = c("text", "text", "numeric", 
        "numeric", "numeric"), skip = 5)

lwc.ui.Parish.excel <- read_excel(destfile, 
                                        sheet = "Parish",
    col_types = c("text", "text", "numeric", 
        "numeric", "numeric"), skip = 5)


```


Here is the workflow for the tableau data:

- Download the full tableau workbook. 
- Put it in a new folder here: C:\Users\Robert Habans\OneDrive - Nonprofit Knowledge Works\covid\tableau\lwcUIDashboard_20200409
- Open the workbook and export the Industry, Main, and Parish data to CSV.
- Delete the "(Louisiana Unemployment Compensation...)" from the file name.
- Copy and paste it into inputs/LWC/

Then load the data here. 

```{r}
lwc.ui.Industry <- read_csv("inputs/LWC/Industry.csv",
                            col_types = cols(Date = 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)) # fix typo

lwc.ui.Main <- read_csv("inputs/LWC/Main.csv", 
                        col_types = cols(`Week Ending` = col_date(format = "%m/%d/%Y")))

lwc.ui.Parish <- read_csv("inputs/LWC/Parish.csv", 
                          col_types = cols(`Week Ending` = col_date(format = "%m/%d/%Y"))) %>% 
    select(-`Number of Records`)

```

What should the denominator be for the Industry versions? I could use EMSI data or QCEW directly. Probably the latter would be preferable. Area codes for BLS are [here](https://data.bls.gov/cew/doc/titles/area/area_titles.htm). 

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.
- 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?

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

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

```

Process the industries from QCEW and LWC Dashboard and join.

```{r}
# State QCEW employment total
qcew.preCOVID.LA %>% 
  filter(industry_code == "10" & own_code == "0") %>% 
  select(month3_emplvl) %>% pull()

# State QCEW Indusry sector total
qcew.preCOVID.LA %>% 
  filter(agglvl_code == 54) %>% 
  group_by(industry_code) %>% 
  summarise(emp = sum(month3_emplvl, na.rm = TRUE))

# Post-COVID UI claims totals
lwc.ui.Industry.postCOVID <- lwc.ui.Industry %>% 
  group_by(NAICS, Industry) %>% 
  filter(Date >= "2020-03-21") %>% 
  summarise(UIclaims = sum(Value))

# Bars as ratio of pre-COVID QCEW employment
lwc.ui.vsQCEW_bars <- qcew.preCOVID.LA %>% 
  filter(agglvl_code == 54) %>% 
  group_by(industry_code) %>% 
  summarise(emp = sum(month3_emplvl, na.rm = TRUE)) %>% 
  left_join(lwc.ui.Industry.postCOVID, by=c("industry_code"="NAICS")) %>% 
  filter(Industry != "Unclassified") %>% 
  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), limits = c(0,.5)) +
  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 Mar 15-April4, 2020 and covered employment in Sep, 2019")

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


```

Compare pre-COVID with post-COVID in absolute numbers.

```{r}
# Before/after bars
lwc.ui.beforeAfter_bars <- lwc.ui.Industry %>% 
  mutate(period = ifelse(Date >= as.Date("2020-03-07") & Date <= as.Date("2020-03-14"), 
                         "Before COVID-19",
                         "After COVID-19")) %>% 
  group_by(Industry, period) %>% 
  summarise(avgClaims = mean(Value)) %>% 
  ggplot(aes(x=reorder(Industry, avgClaims), avgClaims)) + 
  geom_col(aes(fill = period), position = "dodge") +
  scale_fill_manual(values = c("Before COVID-19"=DCcolor.p1mediumblue,
                               "After COVID-19" =DCcolor.p2yellow)) +
  scale_y_continuous(labels = scales::comma, limits = c(0,30000)) +
  coord_flip() + 
  themeDC_vertical() +
  theme(legend.position = "top",
        legend.title = element_blank(),
        legend.justification = "left",
        legend.direction = "vertical") +
  labs(x="", 
       y="Average weekly claims",
       title = "Unemployment insurance claims by industry, Louisiana",
       subtitle = "Two weeks before March 15 and three weeks after March 15")

  ggsave(lwc.ui.beforeAfter_bars, filename = "outputs/lwcUIclaimsByIndustry.png", type = "cairo",
       width = 8, height = 5, units = "in")


```

And by parish and metro.

```{r}
# Claims for NO MSA
lwc.ui.Parish %>% 
  filter(`Week Ending` >= as.Date("2020-03-21")) %>% 
  group_by(Parish) %>% 
  summarise(UIclaims = sum(`Initial Claims`)) %>% 
  mutate(NOmsa = ifelse(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"),
                        "New Orleans MSA",
                        "Other")) %>% 
  group_by(NOmsa) %>% 
  summarise(UIclaims = sum(UIclaims))

# NO MSA QCEW employment
qcew.preCOVID.NOmsa %>% 
  filter(industry_code == "10" & own_code == "0") %>% 
  select(month3_emplvl) %>% pull()

# NO MSA
104762/545383

# Rest of state 
163745/(1913503-545383)

# Statewide
(104762+163745)/1913503

```

### Update: UI by industry sector and parish from the Excel tables

Let's revisit this data set using the updated Excel files. These seem to be released with every Thursday press release on the UI numbers. The format of the Excel files changes, so a weekly replicable process is not in the cards. Still, these questions and this data merit a periodic update. This time, let's look at the following questions:

- Is New Orleans more succeptible to job loss because of its employment composition?
- How has the distribution of job losses by industry changed over time?
- How has the distribution of job losses by geography changed over time?

Level setting: Should this analysis be performed in this UI Rmd, with the "dashboard" content, or with an updated draft of the prospective job loss analysis? I'm initially leaning toward the prospective job loss analysis because there isn't an implied need to update (with manual work) and it's directly relevant to that issue. The UI stuff should eventually move to the dashboard content anyway, so this is now for exploratory analysis.

What are the appropriate time and geography units?

- Try every week, then maybe pivot to binned before/after time periods.
- Try the every parish, largest parishes, metros, Orleans/Jefferson/EBR/StTammany/Caddo/Lafayette/Calcasieu?

First, import the Excel tables. Should this be pulled in locally or from the LWC press releases? It actually looks like the Excel file might be a fixed link, presmably always with the most updated data. 

```{r}
# Download, make long, clean the dates

#library(readxl)
#library(janitor)
url <- "http://www.laworks.net/Downloads/LMI/claims_by_industry_Parish.xlsx"
destfile <- "claims_by_industry_Parish.xlsx"
curl::curl_download(url, destfile)

claims_by_industry <- read_excel(destfile, sheet = "Industry",
    col_types = c("text", "text", "numeric", 
        "numeric", "numeric", "skip", "numeric", 
        "numeric", "numeric", "numeric", 
        "numeric"), skip = 5) %>% 
  gather(-`NAICS Code`, -Industry, key = date, value = claims) %>% 
  mutate(date = excel_numeric_to_date(as.numeric(as.character(date)), date_system = "modern"))

claims_by_parish <- read_excel(destfile, sheet = "Parish",
    col_types = c("text", "text", "numeric", 
        "numeric", "numeric", "skip", "numeric", 
        "numeric", "numeric", "numeric", 
        "numeric"), skip = 5) %>% 
  gather(-RLMA, -Parish, key = date, value = claims) %>% 
  mutate(date = excel_numeric_to_date(as.numeric(as.character(date)), date_system = "modern"))

```

How have the industry losses changed over time?

```{r}
claims_by_industry %>% 
  ggplot(aes(date, claims)) + 
  geom_line() + 
  facet_wrap(~Industry, scales = "free")
  
```

How have the parish losses changed over time? 

With the update on 5/28, LWC ceased to publish the spreadsheet, so let's try this again...

### Update again: Initial, contiuning, and PUC claims by Parish and BLS April data

Unfortunately, this version will return to using Tableau exports since LWC stopped publishing the excel file. More information is coming to light about the shortcomings of simply counting aggregate initial claims ([EPI](https://www.epi.org/blog/more-than-one-in-five-workers-are-either-receiving-unemployment-benefits-or-waiting-for-approval-congress-must-do-much-much-more/)). See also this [Brookings analysis](https://www.brookings.edu/blog/the-avenue/2020/05/13/what-weekly-unemployment-claims-reveal-about-the-local-impacts-of-the-covid-19-recession/) that replicated some of my methods using QCEW as the denominator. 

One question concerns the number of people for whom benefits will sunset and when this will happen. How to interpret continued claims? This passage from the Chetty et al. Tracker paper suggests that continued is equivalent to receiving claims:

> We provide both new employment claims and total employment claims. Total claims are the count of new claims plus the count of people receiving unemployment insurance benefits in the same period of eligibility as when they last received the benefits.

Weirdly, they use 2019 population estimates as the denominator for initial + continued claims.

Let's replicate EPI's analysis of Pandemic Unemployment Assistance (PUA) claims -- the program that expands access to UI benefits to more workers. This is now reported in the DOL-OUI weekly bulletins. The Louisiana numbers are all over the place. 

[EPI's analysis](https://www.epi.org/blog/more-than-one-in-five-workers-are-either-receiving-unemployment-benefits-or-waiting-for-approval-congress-must-do-much-much-more/) is calculated from the DOL-OUI's bulletin as follows, using *not* seasonally adjusted because the adjustments are biased in this situation: 

- Insured unemployment/continued claims for May 23 = 19M, 
- Regular state UI claims is initial claims for weeks ending May 9, 16 = Looks like it would be close to 4.1 but double check with the older numbers not in the release.
- PUA initial claims for May 9, 16, 23 = 3.3M might be 1 week plus current 2.4M for last 2 weeks. 
- PUA continued claims for week ending May 9(?) = 7.8

PAY ATTENTION: There must be a typo here in the EPI method. The claims from May 16 and May 9 add up to 4.5M (see [old release](https://oui.doleta.gov/press/2020/052120.pdf)) but May 16 and May 9 are 4.1M in the May 28 release. 

To reconstruct this same chart for LA, you may be able to do it with last week's release. Here is the LA version:

- Insured unemployment/continued claims for May 23 (16!) = 5/16: 320536
- Regular state UI claims is initial claims for weeks ending May 9, 16 (and 23) = 5/23: 23853, 5/16: 28545, 5/9: 40125
- PUA initial claims for May 9, 16, 23 = 5/23:   25188, 5/16: 3396, 5/9: 17556
- PUA continued claims for week ending May 9(?) = 118817

Why the two week lag? Is it because continuing is released with an additional week of lag? So continued plus same week initial plus more recent week initial? Yes, this appears to be the case. NOTE: You did all of this manually, but EPI is providing this by state now [here](https://www.epi.org/blog/six-states-have-at-least-1-million-workers-either-receiving-regular-unemployment-benefits-or-waiting-for-their-claim-to-be-approved/). 

Update: After talking to Ali Bustamante at LWC, 


Compare this total 538 thousand with 651,640 total since Mid-march. 


```{r}
modifiedTally.LA.UI.EPImethod <- tibble(
  key = c("PUA initial claims", "PUA continued claims",
          "Regular state initial claims", "Regular state continued claims"),
  value = c(25188+3396+17556,
            118817, # May 9
            23853+28545,
            320536), # May 16
  releaseWeekEnding = as.Date("2020-05-23")
)

# After talking to LWC, use their data and most current weeks for all, week ending May 30
modifiedTally.LA.UI <- tibble(
  key = c("PUA initial claims", "PUA continued claims",
          "Regular state initial claims", "Regular state continued claims"),
  value = c(5723,
            161553, 
            19810,
            301753), 
  releaseWeekEnding = as.Date("2020-05-23")
)

# Replication of the EPI chart, manual
# modifiedTally.LA.UI %>% 
#   mutate(key = factor(key, levels = c("PUA initial claims", "PUA continued claims",
#           "Regular state initial claims", "Regular state continued claims")),
#          value = round(.001*value)) %>% 
#   ggplot(aes(releaseWeekEnding, value, fill = key)) + 
#   geom_col(position = "stack") + 
#   geom_text(aes(label = value), position = position_stack(vjust = .5), family = "Asap") +
#   geom_text(aes(label = paste0("Total: ", stat(y), " thousand"), group = releaseWeekEnding),
#             stat = "summary", fun.y = sum, vjust = -1, family = "Asap") +
#   scale_fill_manual(values = c("PUA initial claims" = DCcolor.p2blue20,
#                                "PUA continued claims" = DCcolor.p2blue50,
#                                "Regular state initial claims" = DCcolor.p2green50, 
#                                "Regular state continued claims" = DCcolor.p2green80)) + 
#   scale_y_continuous(limits = c(0,600)) + 
#   themeDC_horizontal() +
#   theme(axis.title.y = element_blank(),
#         axis.title.x = element_blank(),
#         axis.text.x = element_blank(),
#         legend.title = element_blank()) +
#   labs(y="Thousands",x="",
#        title = "Regular and PUA claims in Louisiana (in thousands)",
#        subtitle = "Louisiana Workforce Commission claims for the week ending May 30") + 
# ggsave(filename = "outputs/emp data June update/modifiedTotalClaimsWPUA.png", type = "cairo",
#        width = 5, height = 5, units = "in")

# Breakdown PUA vs Regular
(321+52)/(46+119+321+52)

# Total per state from QCEW september
537891/1913503

# Rewriting to automatically update with LWC data. Note that the date formats change from week to week.
lwc.LA.claimsIncludePUA <- 
read_csv("inputs/LWC/lwcUIDashboard_20200611/Main (Louisiana Unemployment Compensation Claims for Week Ending on March 28 2020 ).csv") %>% 
  mutate(`Week Ending` = dmy(paste0(`Week Ending`, "-2020")))

lwc.LA.claimsIncludePUA %>% 
  filter(Indicator %in% c("Initial Claims",
                          "Continued Claims",
                          "PUA Initial Claims",
                          "PUA Continued Claims"),
         `Week Ending` == last(`Week Ending`)) %>% 
  mutate(key = NA,
         key = ifelse(Indicator == "Initial Claims", "Regular state initial claims", key),
         key = ifelse(Indicator == "Continued Claims", "Regular state continued claims", key),
         key = ifelse(Indicator == "PUA Initial Claims", "PUA initial claims", key),
         key = ifelse(Indicator == "PUA Continued Claims", "PUA continued claims", key),
         key = factor(key, levels = c("PUA initial claims", "PUA continued claims",
          "Regular state initial claims", "Regular state continued claims")),
         Value = round(.001*Value)) %>% 
  ggplot(aes(`Week Ending`, Value, fill = key)) + 
  geom_col(position = "stack") + 
  geom_text(aes(label = Value), position = position_stack(vjust = .5), family = "Asap") +
  geom_text(aes(label = paste0("Total: ", stat(y), " thousand"), group = `Week Ending`),
            stat = "summary", fun.y = sum, vjust = -1, family = "Asap") +
  scale_fill_manual(values = c("PUA initial claims" = DCcolor.p2blue20,
                               "PUA continued claims" = DCcolor.p2blue50,
                               "Regular state initial claims" = DCcolor.p2green50, 
                               "Regular state continued claims" = DCcolor.p2green80)) + 
  scale_y_continuous(limits = c(0,600)) + 
  themeDC_horizontal() +
  theme(axis.title.y = element_blank(),
        axis.title.x = element_blank(),
        axis.text.x = element_blank(),
        legend.title = element_blank()) +
  labs(y="Thousands",x="",
       title = "Regular and PUA claims in Louisiana (in thousands)",
       subtitle = "Louisiana Workforce Commission claims for the week ending June 6") +
  ggsave(filename = "outputs/emp data June update/modifiedTotalClaimsWPUA.png", type = "cairo",
         width = 5, height = 5, units = "in")




```



First, let's look at unemployment rates from the LWC and the BLS-LAUS. Note some minor discrepancies when comparing this [visualization tool](https://data.bls.gov/lausmap/showMap.jsp;jsessionid=5D8C1735EA530C594D2C36307DDCC7C2._t3_06v), the LWC press release, and [FRED](https://fred.stlouisfed.org/series/NEWO322URN). This is actually pretty interesting. New Orleans has the highest rate and the biggest increase either year-over-year or Feb-April. Yikes. 

(Note: I originally did manual data entry here, but I updated it with the FRED series. The numbers changed a little bit. See Hammond.)

```{r}
# Download previous unemployment rates from FREDR
# Note these are not seasonally adjusted
la.msa.unemploymentRate <- pmap_dfr(
  .l = list(
    series_id = c("LAURN", 
                  "NEWO322URN", "BATO922URN", "ALEX722URN", 
                  "SHRE322URN", "LAFA122URN", "MONR722UR", 
                  "LAUMT222522000000003", "LAKE322URN", "HOUM322URN"),
    observation_start = as.Date("2019-01-01")
    ),
  .f = ~ fredr(series_id = .x, observation_start = .y)
) 

#Crosswalk for labels
fredSeries.msa.crosswalk <- tibble(
  series_id = c("LAURN",
                "ALEX722URN",
                "BATO922URN",
                "HOUM322URN",
                "LAFA122URN",
                "LAKE322URN",
                "LAUMT222522000000003",
                "MONR722UR",
                "NEWO322URN",
                "SHRE322URN"),
  msaName = c("Louisiana",
              "Alexandria", 
              "Baton Rouge", 
              "Houma", 
              "Lafayette", 
              "Lake Charles", 
              "Hammond", 
              "Monroe", 
              "New Orleans", 
              "Shreveport")
)

# Manual data entry for April -- REPLACE AFTER RELEASE JUNE 3
# manualEntry.AprilUnempByMetro <- tibble(
#   msaName = c("Alexandria", 
#               "Baton Rouge", 
#               "Houma", 
#               "Lafayette", 
#               "Lake Charles", 
#               "Hammond", 
#               "Monroe", 
#               "New Orleans", 
#               "Shreveport"),
#   date = as.Date("2020-04-01"),
#   unemp = c(9.9, 13, 17.1, 12.3, 13.1, 14.5, 11.6, 18.8, 13)
# )

# In progress... order MSAs, make it difference Feb20/Apr20 and Apr19/Apr20
# la.msa.unemploymentRate %>% 
#   left_join(fredSeries.msa.crosswalk) %>% 
#   full_join(manualEntry.AprilUnempByMetro) %>% 
#   mutate(unemp = ifelse(is.na(unemp), value, unemp)) %>% 
#   filter(as.character(date) %in% c("2020-04-01",
#                                    "2020-02-01",
#                                    "2019-04-01")) %>%
#   mutate(date.label = paste0(month(date, label = TRUE)," ", year(date)),
#          date.label = factor(date.label,
#                              levels = c("Apr 2019", "Feb 2020", "Apr 2020"))) %>% 
#   ggplot(aes(msaName, unemp)) + 
#   geom_bar(aes(fill=date.label), position = "dodge", stat="identity") +
#   scale_fill_brewer(palette = "Purples") + 
#   coord_flip() +
#   themeDC_vertical()

# Alternative method to show percent point change  
la.msa.unemploymentRate %>% 
  left_join(fredSeries.msa.crosswalk) %>% 
  #full_join(manualEntry.AprilUnempByMetro) %>% 
  # mutate(unemp = ifelse(is.na(unemp), value, unemp),
  #        unemp = .01*unemp) %>% 
  mutate(unemp = .01*value) %>% 
  filter(as.character(date) %in% c("2020-04-01",
                                   "2020-02-01",
                                   "2019-04-01")) %>%
  mutate(date.label = paste0(month(date, label = TRUE)," ", year(date)),
         date.label = factor(date.label,
                             levels = c("Apr 2019", "Feb 2020", "Apr 2020"))) %>% 
  group_by(msaName) %>% 
  arrange(date) %>% 
  summarise(`Apr 2020 unemployment` = last(unemp), 
            `Change (Apr '19-Apr '20)` = last(unemp)-first(unemp),
            `Change (Feb '20-Apr '20)` = last(unemp)-nth(unemp, 2)) %>% 
  gather(-msaName, key=measure, value=unemp) %>% 
  mutate(measure = factor(measure, levels = c("Apr 2020 unemployment",
                                              "Change (Apr '19-Apr '20)",
                                              "Change (Feb '20-Apr '20)")),
         msaName = factor(msaName, levels = c("New Orleans",
                                              "Louisiana",
                                              "Alexandria", 
                                              "Baton Rouge", 
                                              "Houma", 
                                              "Lafayette", 
                                              "Lake Charles", 
                                              "Hammond", 
                                              "Monroe", 
                                              "Shreveport")),
         msaName = fct_rev(msaName)) %>% 
  ggplot(aes(msaName, unemp)) + 
  geom_bar(aes(fill=measure), position = "dodge", stat="identity") +
  scale_fill_manual(values = c(DCcolor.p1darkblue, DCcolor.p2limegreen, DCcolor.p2green)) + 
  scale_y_continuous(labels = scales::percent_format(accuracy = 1)) +
  coord_flip() +
  themeDC_vertical() + 
  theme(legend.position = "right",
        legend.title = element_blank()) +
  labs(x="", y="",
       title = "Unemployment rate and change in percentage \npoints by metro",
       subtitle = "Preliminary estimates for April, 2020")
ggsave(filename = "outputs/emp data June update/unemploymentRateByMetro.png", type = "cairo",
       width = 5, height = 5, units = "in")

```

Look at employment change, total and in hospitality, for New Orleans. Hopefully, FRED will update on Wednesday morning, and I can re-run the analysis there. Just the simple drop in employment is illustrative. Unfortunately, the names of the FRED series are a little nonstandard, but let's try pulling the relevant supersectors then just make a table. 

- 00 Total nonfarm
- 10 Mining and logging
- 20 Construction
- 30 Manufacturing
- 40 Trade, transportation, and utilities
- 41 Wholesale Trade
- 42 Retail trade
- 42445100 Grocery stores
- 42452000 General merchandise stores
- 43 Transportation and utilities
- 50 Information
- 55 Financial activities
- 60 Professional and business services
- 60540000 Professional, scientific, and technical services
- 60550000 Management of companies and enterprises
- 60560000 Administrative and support and waste management
- 65 Education and health services
- 65620000 Health care and social assistance
- 65621000 Ambulatory health care services
- 65622000 Hospitals
- 70 Leisure and hospitality
- 70710000 Arts, entertainment and recreation
- 70720000 Accomodation and food services
- 70721000 Accomodation
- 70722000 Food services and drinking places
- 80 Other services
- 90 Government

(WORKING IN SUBLIME TO GET THE CODES BUT THEN PULL THEM IN HERE)

```{r}
# Make crosswalk for city and state for labeling and identifying the series
la.sectors <- tibble(
  series_id = c("LANAN",
                  "LANRMNN",
                  "LACONSN",
                  "LAMFGN",
                  "LATRADN",
                  "LAINFON",
                  "LAFIREN",
                  "LAPBSVN",
                  "LAEDUHN",
                  "LALEIHN",
                  "LASRVON",
                  "LAGOVTN"),
  sector = c("Total Nonfarm",
             "Mining and Logging",
             "Construction",
             "Manufacturing",
             "Trade, Transportation, and Utilities",
             "Information",
             "Financial Activities",
             "Professional and Business Services",
             "Education and Health Services",
             "Leisure and Hospitality",
             "Other Services",
             "Government"),
  geog = "Louisiana",
  order = 1:12
)

NOmsa.sectors <- tibble(
  series_id = c("NEWO322NAN",
                  "SMU22353801000000001",
                  "SMU22353802000000001",
                  "NEWO322MFGN",
                  "NEWO322TRADN",
                  "NEWO322INFON",
                  "NEWO322FIREN",
                  "NEWO322PBSVN",
                  "NEWO322EDUHN",
                  "NEWO322LEIHN",
                  "NEWO322SRVON",
                  "NEWO322GOVTN"),
  sector = c("Total Nonfarm",
             "Mining and Logging",
             "Construction",
             "Manufacturing",
             "Trade, Transportation, and Utilities",
             "Information",
             "Financial Activities",
             "Professional and Business Services",
             "Education and Health Services",
             "Leisure and Hospitality",
             "Other Services",
             "Government"),
  geog = "New Orleans metro",
  order = 1:12
)


# download all sectors for LA and NO MSA.
la.nonfarm.sector <- pmap_dfr(
  .l = list(
    series_id = c(la.sectors$series_id),
    observation_start = as.Date("2019-01-01")
    ),
  .f = ~ fredr(series_id = .x, observation_start = .y)
) %>% 
  left_join(la.sectors, by="series_id")

NOmsa.nonfarm.sector <- pmap_dfr(
  .l = list(
    series_id = c(NOmsa.sectors$series_id),
    observation_start = as.Date("2019-01-01")
    ),
  .f = ~ fredr(series_id = .x, observation_start = .y)
) %>% 
  left_join(NOmsa.sectors, by="series_id")

# Bind and analyze. I can't figure out how to make grouped column lables, so keep tables separate
la.nonfarm.sector %>% 
  filter(as.character(date) %in% c("2020-04-01",
                                   "2020-02-01")) %>%
  group_by(sector, order) %>% 
  summarise(`Feb 2020` = first(value),
            `Apr 2020` = last(value),
            `change` = last(value) - first(value),
            `change (%)` = 100*(last(value) - first(value))/first(value)) %>% 
  arrange(order) %>% 
  select(-order) %>% kableExtra::kable(digits = 1) %>% kableExtra::save_kable(file = "outputs/emp data June update/LAempBySectortable.html")

NOmsa.nonfarm.sector %>% 
  filter(as.character(date) %in% c("2020-04-01",
                                   "2020-02-01")) %>%
  group_by(sector, order) %>% 
  summarise(`Feb 2020` = first(value),
            `Apr 2020` = last(value),
            `change` = last(value) - first(value),
            `change (%)` = 100*(last(value) - first(value))/first(value)) %>% 
  arrange(order) %>% 
  select(-order) %>% kableExtra::kable(digits = 1) %>% kableExtra::save_kable(file = "outputs/emp data June update/NOempBySectortable.html")
  

# What happens when we remove hospitality tourism
NOmsa.nonfarm.sector %>% 
  bind_rows(la.nonfarm.sector) %>% 
  filter(as.character(date) %in% c("2020-04-01",
                                   "2020-02-01")) %>%
  filter(sector %in% c("Leisure and Hospitality", "Total Nonfarm")) %>% 
  group_by(sector, geog) %>% 
  summarise(`Feb 2020` = first(value),
            `Apr 2020` = last(value),
            `change` = last(value) - first(value),
            `change (%)` = 100*(last(value) - first(value))/first(value)) %>% 
  group_by(geog) %>% 
  mutate(febmodified = max(`Feb 2020`) - min(`Feb 2020`),
         aprmodified = max(`Apr 2020`) - min(`Apr 2020`),
         pctchangemodified = (aprmodified-febmodified)/febmodified) %>% 
  select(pctchangemodified)




```

Pull out specific industries.

```{r}
# Download some employment series for leisure and hospitality, none seasonally adjusted
specific.bls.industries <- tibble(
  series_id = c("NEWO322NAN", 
                  "LANAN",
                  "NEWO322LEIHN",
                  "LALEIHN",
                  "SMU22353807072100001", # Accomodation
                  "SMU22353807072200001", # Food services
                  "SMU22353807071000001", # Arts, entertainment, recreation
                  "SMU22353804200000001SA" # Retail trade
                  ),
  sector = c("Total nonfarm, NO MSA",
             "TOtal nonfarm, LA",
             "Leisure and hospitality, NO MSA",
             "Leisure and hospitality, LA",
             "Accomodation, NO MSA",
             "Food services, NO MSA",
             "Arts, Entertainment, and recreation, NO MSA",
             "Retail trade, NO MSA"
             )
)

la.msa.nonfarm.ind <- pmap_dfr(
  .l = list(
    series_id = c(specific.bls.industries$series_id),
    observation_start = as.Date("2019-01-01")
    ),
  .f = ~ fredr(series_id = .x, observation_start = .y)
) 

# la.msa.nonfarm.ind %>% 
#   mutate(geog = ifelse(series_id %in% c("NEWO322NAN", "NEWO322LEIHN"), "New Orleans metro", "Louisiana")) %>% 
#   ggplot(aes(date, value)) + 
#   geom_line(aes(color = series_id, group = series_id)) + 
#   facet_wrap(~geog, scales = "free_y")

# loss of employment between Feb and Apr
la.msa.nonfarm.ind %>% 
  left_join(specific.bls.industries, by="series_id") %>% 
  filter(as.character(date) %in% c("2020-04-01",
                                   "2020-02-01")) %>%
  group_by(series_id, sector) %>% 
  summarise(feb = first(value),
            apr = last(value),
            loss = first(value)-last(value)) %>% 
  mutate(lossPct = loss/feb)

```




Let's load the updated data from LWC. NOTE: I had to convert the format to "general" in excel -- for some reason, it was parsing 3/21/2020 as Mar-21 or something like that. Converting to general fixed it and let me parse by the other strange but workable excel date code. 

```{r}
claims.lwc.parish <- 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")))

claims.lwc.ind <- read_csv("inputs/LWC/lwcUIDashboard_20200611/Industry (Louisiana Unemployment Compensation Claims for Week Ending on March 28 2020 ).csv")  %>%
  mutate(`Week Ending` = dmy(paste0(`Week Ending`, "-2020"))) %>% 
  mutate(Industry = ifelse(Industry == "AccomModation & Food Services", "Accommodation & Food Services", Industry))
  #mutate(`Week Ending` = excel_numeric_to_date(as.numeric(as.character(`Week Ending`)), date_system = "modern"))
```

Chart initial and continous for some of the top parishes. 

```{r}
# Download QCEW for every parish then joinn to UI by parish ###
# qcew.preCOVID.LA %>% 
#   filter(agglvl_code == 54) %>% 
#   group_by(industry_code) %>% 
#   summarise(emp = sum(month3_emplvl, na.rm = TRUE)) %>% 
#   left_join(lwc.ui.Industry.postCOVID, by=c("industry_code"="NAICS"))

# load_variables(2017, "acs5", cache = TRUE) %>% view()

# Get population from ACS
parish.population.acs <- get_acs(geography = "county", 
              variables = c(population = "B01003_001"), 
              state = "LA", 
              year = 2018) %>% 
  arrange(desc(estimate)) %>% 
  mutate(Parish = str_remove(NAME, ", Louisiana")) 

# Get population from PEP -- STILL NEED TO ADAPT
parish.population.pep <- get_estimates(geography = "county",
                                product = "population",
                                year = 2018,
                                state = c("LA")) %>%
  filter(variable == "POP") %>% 
  mutate(Parish = str_remove(NAME, ", Louisiana")) 


# Initial and continuous separate
claims.lwc.parish %>% 
  left_join(parish.population.acs) %>% 
  filter(estimate > 95000) %>% 
  ggplot(aes(`Week Ending`, Claims, group=`Claim Type`)) + 
  geom_line(aes(color = `Claim Type`)) + 
  facet_wrap(~Parish, scales = "free_y")

# Total claims as share of population
# Initial and continuous combined -- lines
claims.lwc.parish %>% 
  left_join(parish.population.acs) %>% 
  filter(estimate > 95000) %>% 
  group_by(`Week Ending`, Parish) %>% 
  summarise(totalClaims = sum(Claims),
            population = max(estimate)) %>% 
  ungroup() %>% 
  mutate(claimsPerPop = 100*totalClaims/population) %>% 
  ggplot(aes(`Week Ending`, claimsPerPop)) + 
  geom_line(color = DCcolor.p1darkblue) + 
  facet_wrap(~Parish, scales = "free_y")

# Initial and continuous combined -- bars
claims.lwc.parish %>% 
  left_join(parish.population.pep) %>% 
  filter(value > 95000) %>% 
  group_by(`Week Ending`, Parish) %>% 
  summarise(totalClaims = sum(Claims),
            population = max(value)) %>% 
  ungroup() %>% 
  filter(`Week Ending` == last(`Week Ending`)) %>% 
  mutate(claimsPerPop = 100*totalClaims/population) %>% 
  ggplot(aes(x=reorder(Parish, claimsPerPop), claimsPerPop)) + 
  geom_col(fill = DCcolor.p1grayblue) + 
  scale_y_continuous(limits = c(0,15)) +
  coord_flip() + 
  themeDC_vertical() + 
  labs(x="",
       y="Total claims per 100 population (2018)",
       title = "Total claims per 100 population",
       subtitle = "Initial and continuous claims for the week ending June 6,\n15 most populous parishes")
ggsave(filename = "outputs/emp data June update/UItotalByParishPop.png", type = "cairo",
       width = 7, height = 5, units = "in")

##### This is adapted from Jenna's code: Pull in QCEW
# 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.fips.crosswalk <- tibble(
  area_fips = c("22000",
                "C3538",
                "22071",
                "22051"),
  geog = c("Louisiana",
           "New Orleans metro",
           "Orleans Parish",
           "Jefferson Parish")
) %>% 
  mutate(geog.fac = factor(geog, levels = c("Jefferson Parish",
                                            "Orleans Parish",
                                            "New Orleans metro",
                                            "Louisiana"
                                            )))

qcew.preCOVID.LA <- blsQCEW(method = "Area",
        year = "2019",
        quarter = 3,
        area = "22000") %>% 
  mutate(area_fips = as.character(area_fips))

qcew.preCOVID.NOmsa <- blsQCEW(method = "Area",
        year = "2019",
        quarter = 3,
        area = "C3538") %>% 
  mutate(area_fips = as.character(area_fips))

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

qcew.preCOVID.JP <- blsQCEW(method = "Area",
        year = "2019",
        quarter = 3,
        area = "22051") %>% 
  mutate(area_fips = as.character(area_fips))



# Get last week of total claims for state
lwc.LA.lastClaims <- read_csv("inputs/LWC/lwcUIDashboard_20200608/Historical (Louisiana Unemployment Compensation Claims for Week Ending on March 28 2020 ).csv", 
    col_types = cols(`Ending Week` = col_character())) %>% 
  select(1:3) %>% 
  mutate(totalClaims2 = `Continued Claims`+`Initial Claims`) %>% 
  filter(`Ending Week` == "May 23, 2020") %>% 
  mutate(grouping = "Louisiana") %>% 
  select(grouping, totalClaims2)

# Bind with parish and metro total claims
lwc.parishMet.lastClaims <- claims.lwc.parish %>% 
  filter(str_sub(Region, 1, 6) == "RLMA 1") %>% 
  mutate(grouping = "New Orleans metro",
         grouping = ifelse(Parish == "Orleans Parish", "Orleans Parish", grouping),
         grouping = ifelse(Parish == "Jefferson Parish", "Jefferson Parish", grouping)) %>% 
  group_by(`Week Ending`, grouping) %>% 
  summarise(totalClaims = sum(Claims)) %>% 
  mutate(totalClaims2 = ifelse(grouping == "New Orleans metro", sum(totalClaims), totalClaims),
         grouping = factor(grouping, levels = c("New Orleans metro",
                                                "Orleans Parish",
                                                "Jefferson Parish"))) %>% 
  ungroup() %>% 
  filter(`Week Ending` == max(`Week Ending`)) %>% 
  bind_rows(lwc.LA.lastClaims) 

# Join with QCEW
filter(qcew.preCOVID.LA, agglvl_code == "50") %>% 
  bind_rows(filter(qcew.preCOVID.NOmsa, agglvl_code == "40"),
            filter(qcew.preCOVID.OP, agglvl_code == "70"),
            filter(qcew.preCOVID.JP, agglvl_code == "70")) %>% 
  select(area_fips, month3_emplvl) %>% 
  left_join(qcew.fips.crosswalk) %>% 
  left_join(lwc.parishMet.lastClaims, by=c("geog"="grouping")) %>% 
  mutate(claimsPerEmp = 100*totalClaims2/month3_emplvl) %>% 
  filter(geog %in% c("Louisiana", "New Orleans metro")) %>% 
  ggplot(aes(geog.fac, claimsPerEmp)) + 
  geom_col(fill = DCcolor.p1grayblue) + 
  scale_y_continuous(limits = c(0,25)) +
  coord_flip() +
  themeDC_vertical() + 
  labs(x="",
       y="Total claims per 100 employment (2019)",
       title = "Total claims per 100 pre-COVID covered employees",
       subtitle = "Initial and continuous claims for the week ending June 6,\nCovered employment for Sep 2019 ")
ggsave(filename = "outputs/emp data June update/UItotalByParishMetroEmp.png", type = "cairo",
       width = 7, height = 2, units = "in")

```




See about projecting out the sunset of benefits. Use lubridate math and continued claims. 

```{r}
# Trying to make a chart of people on claims then falling off. Failing.
claims.lwc.parish %>% 
  filter(Parish == "Orleans Parish", `Claim Type` == "Continued") %>% 
  arrange(`Week Ending`) %>% 
  mutate(newContClaims = Claims - lag(Claims, n=1L),
         expDatePEUC = `Week Ending` + weeks(38),
         expDateNoPEUC = `Week Ending` + weeks(25)) %>% 
  ggplot() + 
  geom_line(aes(x=`Week Ending`, y=Claims)) + 
  geom_line(aes(x=expDatePEUC, y=newContClaims)) +
  geom_line(aes(x=expDateNoPEUC, y=newContClaims))

# Just try adding X number of weeks to the increase in continuing claims.


```


Chart initial and continued by industry. 

```{r}

claims.lwc.ind %>%
  ggplot(aes(`Week Ending`, Claims, group=`Claim Type`)) +
  geom_line(aes(color = `Claim Type`)) +
  facet_wrap(~Industry, scales = "free_y")


```

How many folks are receiving benefits?

```{r}
# Define labelled points
totalClaims.labels <- claims.lwc.parish %>% 
  filter(str_sub(Region, 1, 6) == "RLMA 1") %>% 
  mutate(grouping = "Metro",
         grouping = ifelse(Parish == "Orleans Parish", "Orleans", grouping),
         grouping = ifelse(Parish == "Jefferson Parish", "Jefferson", grouping)) %>% 
  group_by(`Week Ending`, grouping) %>% 
  summarise(totalClaims = sum(Claims)) %>% 
  mutate(totalClaims2 = ifelse(grouping == "Metro", sum(totalClaims), totalClaims),
         grouping = factor(grouping, levels = c("Metro",
                                                "Orleans",
                                                "Jefferson"))) %>% 
  filter(`Week Ending` == "2020-06-06") 


# Line graphs
claims.lwc.parish %>% 
  filter(str_sub(Region, 1, 6) == "RLMA 1") %>% 
  mutate(grouping = "Metro",
         grouping = ifelse(Parish == "Orleans Parish", "Orleans", grouping),
         grouping = ifelse(Parish == "Jefferson Parish", "Jefferson", grouping)) %>% 
  group_by(`Week Ending`, grouping) %>% 
  summarise(totalClaims = sum(Claims)) %>% 
  mutate(totalClaims2 = ifelse(grouping == "Metro", sum(totalClaims), totalClaims),
         grouping = factor(grouping, levels = c("Metro",
                                                "Orleans",
                                                "Jefferson"))) %>% 
  ungroup() %>% 
  ggplot(aes(`Week Ending`, totalClaims2)) + 
  geom_line(color = DCcolor.p2limegreen) + 
  scale_y_continuous(labels = scales::comma, expand = expand_scale(mult = c(0, .3))) + 
  geom_text(data = totalClaims.labels, aes(label=scales::comma(totalClaims2)), family = "Asap", hjust = 1, vjust = -1, size = 3) +
  facet_wrap(~grouping, nrow = 1, scales = "free_y") + 
  themeDC_horizontal() + 
  theme(strip.text = element_text(hjust = 0)) +
  labs(y="", x="",
       title = "Total weekly unemployment claims (initial plus continuous)",
       subtitle = "Through week ending June 6") + 
  ggsave(filename = "outputs/emp data June update/totalClaimsMetroOrlJeff.png", type = "cairo",
       width = 8.5, height = 3.5, units = "in")
  


```

Make an output table of last week's claims.

```{r}
claims.lwc.ind %>% 
  filter(`Week Ending` == "2020-06-06") %>% 
  select(-`Number of Records`, -`Week Ending`) %>%
  spread(key = `Claim Type`, value = Claims) %>% 
  write.csv(file = "outputs/emp data June update/juneClaimsByIndustry.csv")
```




## Content for the website -- initial Knit here, edit in shared document

The COVID-19 crisis is causing unprecedented disruptions to the economy, both locally and nationally.[^nationalData] We should learn more about the effects on our local economy over the coming months. In the meantime, initial unemployment insurance (UI) claims in Louisiana show how many newly unemployed workers have applied for benefits.[^datafromOUI] Initial claims don't reflect a comprehensive count of joblessness, but they do offer a nearly up-to-date gauge for how many individuals are experiencing separation from employment and then applying for benefits on a week-by-week basis. 

```{r}
la.initialUIclaims.updated.lastyear
```

Nationally, UI claims have already shattered the previous record.[^nationalData] In Louisiana, the only recent precedent for COVID-19's effect on UI claims occurred in the weeks after Hurricane Katrina. Weekly employment losses have already far surpassed the peak of the Great Recession.

```{r}
la.initialUIclaims.updated.since2000
```

Unemployment insurance is a joint state-federal program that provides temporary income to workers during spells of unemployment. UI plays a critical role in the labor market, especially in times of economic distress.[^RothsteinUIstudy] Eligible workers receive cash benefits that are typically less than their salary for up to 26 weeks.[^UIeligibility] Unemployment insurance also typically doesn't cover workers who don't receive a W-2, like freelancers, gig workers, and musicians. Less than 20 percent of jobless workers receive unemployment benefits in our state, according to the Center on Budget and Policy Priorities.[^weakUILouisiana] Louisiana is also one of only 6 states with a maximum weekly benefit below $300.[^ouiStateUnemploymentLaws]

The spike in weekly UI claims shows how Louisiana's system for administrating unemployment insurance may be stressed beyond its usual capacity due to the impacts of COVID-19. A likely federal stimulus package will dramatically increase UI benefits and extend eligibility to cover more workers who are not normally eligible. The UI system will serve a critical role in how workers cope with the impacts of COVID-19. 

[^datafromOUI]: Advance claims are from the [U.S. Department of Labor](https://oui.doleta.gov/unemploy/claims_arch.asp). This week's UI claims were not available from the [Louisiana Workforce Commission](http://www.laworks.net/LaborMarketInfo/LMI_UIClaimsReportMenu.asp) at the time of writing. Advance claims may differ slightly.

[^nationalData]: Nationally, during the week ending in March 14, initial unemployment insurance claims jumped from 211,000 to 282,000. The next week, initial claims soared to nearly 3.3 million, shattering the previous record. 

[^ouiStateUnemploymentLaws]: U.S. Department of Labor Employment and Training Administration. (2019). "Chapter 3 Monetary Entitlement" in *Comparison of State Unemployment Laws 2019.* https://oui.doleta.gov/unemploy/comparison/2010-2019/comparison2019.asp

[^UIeligibility]: To continue collecting benefits for the full 26 weeks of eligibility, individuals must be able, available, and actively seeking to work.

[^RothsteinUIstudy]: Rothstein, J. and Valetta, R.G. (2017). "Scraping by: Income and program participation after the loss of extended unemployment benefts." Washington Center for Equitable Growth Working Paper Series. https://equitablegrowth.org/working-papers/income-after-loss-of-extended-unemployment-benefits/; Chikhale, N. (2017). "The importance of unemployment benefits for protecting against income drops." Washington Center for Equitable Growth. https://equitablegrowth.org/the-importance-of-unemployment-benefits-for-protecting-against-income-drops/

[^weakUILouisiana]: Leachman, M. and Sullivan, J. (2020). "Some States Much Better Prepared Than Others for Recession." Center on Budget and Priorities. https://www.cbpp.org/research/state-budget-and-tax/some-states-much-better-prepared-than-others-for-recession

### Initial unemployment claims by industry

The Louisiana Workforce Commission has made additional data on UI claims available by industry sector and parish.[^lwcSource] We pooled together the first three weeks of the COVID-19 spike (for the weeks ending in March 21, March 28, and April 4) to give a more detailed look at the early effects of COVID-19 on employment on different parts of the economy. As many have expected, the data shows that the biggest spike occurred in the Accomodation and Food Services sector, with a weekly average of nearly 24,000 claims over the three weeks included in the analysis. 

```{r}
lwc.ui.beforeAfter_bars
```

To provide a common reference point for sectors with different employment levels, initial claims can be compared with the most recent available UI-based employment data from September, 2019.[^qcewUI] Over the three weeks included in the analysis, initial UI claims met or exceeded one-third of pre-COVID-19 covered employment in three sectors: Accomodation and Food Services; Arts, Entertainment, and Recreation; and "Other Services." According to the Bureau of Labor Statistics, "Other Services" includes businesses primarily engaged in a wide range of activities: "equipment and machinery repairing, promoting or administering religious activities, grantmaking, advocacy, and providing drycleaning and laundry services, personal care services, death care services, pet care services, photofinishing services, temporary parking services, and dating services."[^blsOtherServices]

```{r}
lwc.ui.vsQCEW_bars
```

Detailed industry data is not available by Parish. However, in the 8-parish New Orleans metro area, the three weeks of initial UI claims add up to 20 percent of pre-COVID-19 employment. While initial claims are a crude measure for joblessness, these numbers help to illustrate the scale of COVID-19's impact on the workforce. 



[^lwcSource]: The Louisiana Workforce Commission data is published as a [Tableau dashboard](https://public.tableau.com/profile/louisiana.workforce.commission.lmi#!/vizhome/LWCUIDashboard/Dashboard1) and as a spreadsheet in an [April 9th press release](http://www.laworks.net/PublicRelations/PR_PressReleaseDetails.asp?SeqNo=2483&Year=2020&Month=4).

[^qcewUI]: This data comes from the federal [Quarterly Census of Employment and Wages](https://www.bls.gov/cew/overview.htm), which includes all workers covered by state unemployment insurance laws, as well as some federal workers. This data covers 95 percent of jobs nationally and provides the most appropriate reference point for UI initial claims. 

[^blsOtherServices]: Bureau of Labor Statistics. (2020). ["Industries at a Glance: Other Services (except Public Administration): NAICS 81."](https://www.bls.gov/iag/tgs/iag81.htm)