This data notebook contains the analysis that generated facts in the podcast “In the Air: Airport workers faced COVID risks as airlines advertised passenger safety measures” from the series “Essential and Exposed”. For each sentence in the story generated by original data analysis, we have provided the corresponding code and results.
# For general data science
library(tidyverse)
# For data cleaning
library(janitor)
# For loading Excel files
library(readxl)
# For working with datetime
library(lubridate)
# For pretty tables
library(kableExtra)
library(knitr)
# For pulling Bureau of Labor Statistics data
library(blscrapeR)
## Function for formatted table output
output_formatted_table = function(table, text){
table %>%
kable(caption = text) %>%
kable_styling(bootstrap_options = c("striped", "hover", "condensed", "responsive"), font_size = 14, fixed_thead = T) %>%
scroll_box(width = "100%")
}We examined COVID-19 complaints published by the federal Occupational Safety and Health Administration. The data includes all valid complaints received by OSHA since the start of the pandemic. Federal and state OSHA offices submit their data to the federal database. However, certain data may be unique to the state’s operating system or may not be transferred to federal OSHA. This means that, while state OSHA complaints are accessible via the federal OSHA website, totals may not always be comprehensive. OSHA updates the complaints data weekly.Variable names involving “OIS,” which stands for OSHA Information System, indicate the federal database.
## Load and Clean Data
# This loads the complaints to OSHA
ois_closed_complaints_20210328 = read_excel("../etl/federal_osha_complaints/input_data/Closed_Federal_State_Plan_Valid_COVID-19_Complaints_Through_0328_2021.xlsx")
ois_open_complaints_20210328 = read_excel("../etl/federal_osha_complaints/input_data/Open_Federal_State_Plan_Valid_COVID-19_Complaints_Through_0328_2021.xlsx")
# Clean open complaints --------------------
open_complaints <- ois_open_complaints_20210328 %>%
# Format the data set -- take out the first two rows because they aren't row headings.
remove_empty() %>%
slice(-1) %>%
# Bring row headings to the first line of the data set.
row_to_names(1) %>%
clean_names() %>%
# Create a new column to check the status of the complaint -- open or closed.
mutate(complaint_status = "open_complaint")
# Clean closed complaints --------------------
closed_complaints <- ois_closed_complaints_20210328 %>%
# Same as part 1 -- take out the first two rows because they aren't row headings.
remove_empty() %>%
slice(-1) %>%
# Bring row headings to the first line of the data set.
row_to_names(1) %>%
clean_names() %>%
# Create a new column to check the status of the complaint -- open or closed.
mutate(complaint_status = "closed_complaint")
# Combine open and closed complaints --------------------
ois_all_20210328 <- closed_complaints %>%
# Use bind_rows to join the two data sets
bind_rows(open_complaints) %>%
# Because Excel stores dates in a different format, we convert it to a number.
mutate(upa_receipt_date = as.numeric(upa_receipt_date)) %>%
# Then, we use the "excel_numeric_to_date" function to return an actual date
mutate(upa_receipt_date = excel_numeric_to_date(upa_receipt_date)) %>%
# We also need to separate the number of alleged hazards and number of employees exposed into two columns.
separate(number_alleged_hazards_emp_exposed, sep = "/", into = c("number_alleged_hazards","number_employees_exposed"))%>%
# Next, we take out the spaces after alleged hazards using str_trim.
mutate(number_alleged_hazards = str_trim(number_alleged_hazards, side = "right")) %>%
# These columns are stored as characters, so we convert them to numbers.
mutate(number_alleged_hazards = as.numeric(number_alleged_hazards)) %>%
mutate(number_employees_exposed = as.numeric(number_employees_exposed)) %>%
# In this step, we separate the two NAICS codes into two columns: primary_site_naics_1 and primary_site_naics_2
separate(primary_site_naics, sep = "/", into = c("primary_site_naics_1","primary_site_naics_2"))%>%
# Next, we take out the spaces before and after the codes using str_trim.
mutate(primary_site_naics_1 = str_trim(primary_site_naics_1, side = "both"))%>%
mutate(primary_site_naics_2 = str_trim(primary_site_naics_2, side = "both"))%>%
# If/else statement: if primary_site_naics_1 equals primary_site_naics_2, return true in another column, else, return false.
mutate(naics_check = case_when(
primary_site_naics_1 == primary_site_naics_2 ~ "true",
TRUE ~ "false"
)) %>%
#breaking apart the RID to interpret: first two numbers are OSHA office region, if the 3rd digit is 5 it is a state_plan office, else its a federal plan office
mutate(osha_office_region = substr(rid, 1,2), office_type_number = substr(rid, 3,3), office_type = ifelse(office_type_number == "5", "state_plan_office", "federal_plan_office") ) %>%
#make a column to identify if an inspection occurred
mutate(inspection_occured = ifelse(is.na(insp_number) == FALSE,'yes', 'no')) The Howard Center received COVID-19 related worker compensation claims from Oregon Workers’ Compensation Division through a public records request. The data covers claims through March 15, 2021.
# Load data and clean names
oregon_covid_worker_comp = read_excel("data/oregon_worker_comp/Oregon-covidclaims2.xlsx",
sheet = "COVID related claims", skip = 1) %>% clean_names()“About 170 federal OSHA complaints related to COVID-19 have been made against air travel companies.”
We looked at complaints against companies involved in passenger air travel. We selected these companies and agencies from the complaint data by filtering for the following NAICS industry code:
This method may have excluded a small number of airport workers like private security guards, restaurant workers, and others who work for companies at the airport that are classified under different NAICS codes.
# Air travel NAICS codes
air_travel_naics = c("488119","488190","481111","481211", "488111")
air_travel_complaints = ois_all_20210328 %>%
# Filtered both NAICS columns for codes in air travel
filter((primary_site_naics_1 %in% air_travel_naics) | (primary_site_naics_2 %in% air_travel_naics )) %>%
#Check if the two NAICS codes are the same
mutate(not_same_naics = ifelse(primary_site_naics_1 != primary_site_naics_2, TRUE, FALSE)) %>%
# Filter out company Amentum
filter(primary_site_naics_1 != '336611') %>%
# Filter out Leading Edge Aviation Inc
filter(primary_site_naics_1 != "611512") %>%
# Filter out open complaint who has two different naics codes that are not in our aviation list
filter(primary_site_naics_1 != "541512")
# Count number of air travel complaints
num_air_travel_complaints = air_travel_complaints %>%
summarise(number_complaints = n())
output_formatted_table(num_air_travel_complaints, "About 170 COVID-19 OSHA complaints against air travel companies" )| number_complaints |
|---|
| 171 |
The following statements about complaints against airlines appear in the podcast.
The complaint descriptions were taken from OSHA complaints.
call_outs = air_travel_complaints %>%
filter(hazard_desc_location == "Flight crew employees exposed on 3/4/2020 to a COVID-19 infected passenger without preventative controls in place, or being informed by the employer about the passenger's diagnosis." |
hazard_desc_location == "T8 CCR 3203 An employee tested positive for COVID-19, but the employer did not trace and notify other employees that the infected person came into contact with." |
hazard_desc_location == "Boston Logan Airport 1. Employer has not developed a plan for when employees come to work sick. Yesterday there were several employees (baggage handlers and customer service agents) with flu symptoms that were not sent home, exposing employees to illnesses. 2. Employees have not been informed or trained regarding precautions and protective measures against the coronavirus. Resources: https://www.osha.gov/SLTC/novel_coronavirus/ https://www.cdc.gov/coronavirus/2019-ncov/guidance-business-response.html - Interim Guidance for Business and Employers to Plan and Respond to 2019 Novel Coronavirus https://www.cdc.gov/coronavirus/2019-ncov/travelers/faqs.html - Travel FAQs" |
hazard_desc_location == "1. Employees are potentially exposed to health hazards associated with COVID-19. It was reported that that the employer has not implemented precautions such as but not limited to social distancing, limiting amount of people inside an aircraft at a time, PPE, or training workers about the hazards of COVID-19. It was further reported that employees who are sick are required to come to work. Contrary to 29 CFR 1910.132 and 29 CFR 1910.134 and Section 5(a)(1) of the OSH Act." |
hazard_desc_location == "1. TSA management is not following proper CDC guidelines for proper or sufficient isolation of employees who test positive for COVID-19. Employees are being forced back to work days after testing positive for COVID 19 thereby, exposing hundreds of employees and even more passengers! 2. TSA is also not properly sanitizing areas daily including areas that have been infected by a positive case. 3. All TSA screening checkpoints and break rooms are being visited regularly by employees who have tested positive. PPE is very limited and heavily monitored to one mask per day and social distancing is NOT being practiced at all."
) %>%
select(estab_name, site_city, site_state, hazard_desc_location)
output_formatted_table(call_outs, "Air travel complaints that were read in podcast")| estab_name | site_city | site_state | hazard_desc_location |
|---|---|---|---|
| United Airlines Inc. | BOSTON | MA | Boston Logan Airport 1. Employer has not developed a plan for when employees come to work sick. Yesterday there were several employees (baggage handlers and customer service agents) with flu symptoms that were not sent home, exposing employees to illnesses. 2. Employees have not been informed or trained regarding precautions and protective measures against the coronavirus. Resources: https://www.osha.gov/SLTC/novel_coronavirus/ https://www.cdc.gov/coronavirus/2019-ncov/guidance-business-response.html - Interim Guidance for Business and Employers to Plan and Respond to 2019 Novel Coronavirus https://www.cdc.gov/coronavirus/2019-ncov/travelers/faqs.html - Travel FAQs |
| Delta Air Lines Inc | NEWARK | NJ | Flight crew employees exposed on 3/4/2020 to a COVID-19 infected passenger without preventative controls in place, or being informed by the employer about the passenger’s diagnosis. |
| US Dept of Homeland Security | CHARLOTTE | NC |
|
| United Airlines | DENVER | CO |
|
| American Airlines | SAN FRANCISCO | CA | T8 CCR 3203 An employee tested positive for COVID-19, but the employer did not trace and notify other employees that the infected person came into contact with. |
“Three COVID-19 worker compensation claims were filed [in Oregon] by flight attendants working for major air travel companies – Alaska and Delta Airlines. All three were denied. Two of those denied compensation claims were categorized as ’disabling.”
We filtered worker compensation data for the NAICS industry codes related to air travel companies as described before.
air_worker_comp = oregon_covid_worker_comp %>%
filter(naics_code %in% air_travel_naics) %>%
filter(employer_name == "Delta Air Lines Inc" | employer_name == "Alaska Airlines Inc") %>%
select(employer_name, occupation_description, disabling_status, acceptance_status)
output_formatted_table(air_worker_comp, "Oregon COVID-19 worker compensation claims against air travel companies")| employer_name | occupation_description | disabling_status | acceptance_status |
|---|---|---|---|
| Delta Air Lines Inc | Flight Attendants | Non-Disabling | Denied |
| Alaska Airlines Inc | Flight Attendants | Disabling | Denied |
| Alaska Airlines Inc | Flight Attendants | Disabling | Denied |