1. A function to retrieve state abbreviations

getState.abb <- function(x){
  ##remove punctuation and twim white space, convert to lower case
  x <- tolower(trimws(gsub("[[:punct:]]", "", x)))
  x[which(x == "washington dc")] <- "district of columbia"
  
  states.abb <- c(state.abb, "DC", "DC", "PR")
  states.name <- c(state.name, "District of Columbia", "District of Columbia", "Puerto Rico")
  
  m.abb <- tolower(states.abb)
  m.name <- tolower(gsub("[[:punct:]]", "", states.name))
  
    x[which(x %in% m.abb)] <- states.abb[match(x[which(x %in% m.abb)] ,m.abb)]
    x[which(x %in% m.name)] <- states.abb[match(x[which(x %in% m.name)],m.name)]

  return(x)
}

2. Load National Census Population

I could have just summed the county-level race population data to get aggregate, national estimates. But here’s another way to do it, directly. I’ll use these to annotate the graph.

library(readr)
library(dplyr)

census_state <- readr::read_csv("https://www2.census.gov/programs-surveys/popest/datasets/2010-2015/state/asrh/sc-est2015-alldata5.csv")  

df_nat <- census_state %>% 
  dplyr::filter(SEX == 0) %>% ## selecting only 2015 data, renaming the columns
  dplyr::transmute(State = NAME, Hisp = ORIGIN, Race = RACE, AGE = AGE, 
                   population = POPESTIMATE2015) %>% 
  dplyr::mutate(State = getState.abb(State), 
                Race = replace(Race, which(Race == 1 & Hisp == 1), "White"), 
                Race = replace(Race, which(Race == 1 & Hisp == 2), "Hispanic"), 
                Race = replace(Race, which(Race == 2 & Hisp == 0), "Black"), 
                Race = replace(Race, which(Race == 3 & Hisp == 0), "Native American"), 
                Race = replace(Race, which(Race == 4 & Hisp == 0), "Asian"), 
                Race = replace(Race, which(Race == 5 & Hisp == 0), "Asian")) %>% 
  dplyr::select(-c(Hisp)) %>% 
  dplyr::filter(Race %in% c("White", "Hispanic", "Black", "Asian", "Native American")) %>% 
  dplyr::group_by(Race) %>% ## Summing across all ages each race within each state
  dplyr::summarise(population = sum(population, na.rm = T)) 

knitr::kable(df_nat)

3. Load County-Level Population and Police Fatality Data

I’m retrieving these from two data files from the figshare dataset. The two files you’ll need are: countyVars.csv and killed_lott_wapo.csv. I’m aggregated the latter and combining with the previous national population estimates by race to get national fatality rates by race.

countyVars <- fst::read.fst(path = choose.files())
dfwl <- dplyr::left_join(countyVars, readr::read_csv(choose.files())) %>% dplyr::filter(pop.c.total_5yr > 
    10000)

killed_nat = dfwl %>% dplyr::mutate_at(vars(dplyr::contains("killed")), funs(replace(., 
    is.na(.), 0))) %>% dplyr::summarise(White = sum(killed.white_nh, na.rm = T), 
    Black = sum(killed.black, na.rm = T), Hispanic = sum(killed.hisp, na.rm = T), 
    Asian = sum(killed.asian, na.rm = T), `Native American` = sum(killed.amerindian, 
        na.rm = T)) %>% tidyr::gather(Race, Killed_nat) %>% dplyr::right_join(df_nat) %>% 
    dplyr::group_by(Race) %>% dplyr::transmute(Killed_RateN = 1e+05 * Killed_nat/population)

knitr::kable(killed_nat)

4. Get County Frequencies by Race, Join National Rates

dfCount <- dfwl %>% 
  dplyr::mutate_at(vars(dplyr::contains("killed")), funs(replace(., is.na(.), 0))) %>% 
  dplyr::transmute(
    FIPS = FIPS,
    ##Total = killed ,
    White = killed.white_nh,
    Black = killed.black ,
    Hispanic = killed.hisp,
    Asian = killed.asian,
    `Native American` = killed.amerindian
  ) %>% 
  dplyr::group_by(FIPS) %>% 
  tidyr::gather(Race, Killed, 2:6) %>% 
  dplyr::ungroup() %>% 
  dplyr::filter(!is.na(Race)) %>% 
  dplyr::right_join(killed_nat, by="Race") %>% 
  dplyr::mutate(Race = factor(Race, levels = c( "White", "Black", "Hispanic", "Asian", "Native American"))) %>% 
  dplyr::group_by(FIPS, Race)

5. Create the Data!

This is the final step, which generates every variable we’ll use to create the next graph.

df <- dfwl %>% 
  dplyr::mutate_at(vars(dplyr::contains("killed")), funs(replace(., is.na(.), 0))) %>% 
  dplyr::mutate(
    State = getState.abb(state),
    County = gsub(pattern = "County", replacement ="", x=county),
    County = trimws(County)
  ) %>% 
  dplyr::transmute(
    address = paste0(County, ", ", State),
    FIPS = FIPS,
    ##Total = 10e04*killed / pop.c.total_5yr,
    White = 10e04*killed.white_nh / pop.c.white_nh_5yr,
    Black = 10e04*killed.black / pop.c.black_5yr,
    Hispanic = 10e04*killed.hisp / pop.c.hispanic_5yr,
    Asian = 10e04*killed.asian / pop.c.asian_5yr,
    `Native American` = 10e04*killed.amerindian/pop.c.amerindian_5yr
  ) %>% 
  dplyr::group_by(FIPS) %>% 
  tidyr::gather(Race, KilledR, 3:7) %>% 
  dplyr::ungroup() %>% 
  dplyr::filter(!is.na(Race)) %>% 
  dplyr::mutate(Race = factor(Race, levels = c("White", "Black", "Hispanic", "Asian", "Native American"))) %>% 
  dplyr::filter(!is.na(Race)) %>% 
  dplyr::group_by(Race) %>% 
  dplyr::mutate(KilledR_mean = mean(KilledR, na.rm = T), 
                n_zero = length(FIPS[which(KilledR>0)])) %>% 
  dplyr::left_join(dfCount, by=c("FIPS", "Race")) %>% 
  dplyr::mutate(Killed_Race = sum(Killed))