Skip to content

Instantly share code, notes, and snippets.

@dawaldron

dawaldron/Get CFOI Data.R

Created June 23, 2017 03:50
Show Gist options
  • Save dawaldron/e4e505ed3786041765906985996d4ce1 to your computer and use it in GitHub Desktop.
Save dawaldron/e4e505ed3786041765906985996d4ce1 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
Get CFOI Data.R
library(data.table)
library(readxl)
# Read data and lookup files from BLS' CFOI database
d <- fread('https://download.bls.gov/pub/time.series/fw/fw.data.1.AllData')
category2 <- fread('https://download.bls.gov/pub/time.series/fw/fw.category2')[, -3, with = F]
names(category2) <- c('category_code', 'category_text')
occupation <- fread('https://download.bls.gov/pub/time.series/fw/fw.occupation')[, -6, with = F]
names(occupation) <- c('occupation_code', 'occupation_text', 'display_level', 'selectable', 'sort_sequence')
# Select national data by detailed occupation
d <- d[substr(series_id, 14, 14) == 'O' & substr(series_id, 15, 17) == 'N00' & year >= 2011]
d[, category_code := substr(series_id, 4, 6)]
d[, occupation_code := substr(series_id, 7, 12)]
d <- merge(d, category2, by = 'category_code', all.x = T)
d <- merge(d, occupation, by = 'occupation_code', all.x = T)
d <- d[display_level == 4]
# Summarize by occupation, ad-hoc fix for Hunters and Trappers
d.sum <- d[, .(fatalities = sum(value)/5),
.(occupation_code, occupation_text, category_code, category_text)]
d.sum[occupation_code %in% c('453011', '453021'), occupation_code := '453000']
# Merge with NEM data for 2014 employment and calculate fatality rate
occ <- data.table(read_excel('occupation.xlsx', sheet = 'Table 1.2', skip = 3))
occ <- occ[, .(occupation_code = gsub('-', '', V2), employment_2014 = `2014`)]
d.sum <- merge(d.sum, occ, by = 'occupation_code', all.x = T)
d.sum[, fatality_rate := fatalities / employment_2014]
d.sum[, employment_2014 := employment_2014 * 1000]
write.csv(d.sum, 'fatalities by occupation and category.csv', row.names = F)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment