This will walk you through how to analyze a specific police department using data from the FBI Uniform Crime Reporting program and The Washington Post’s Fatal Force project [github]. This was part of the story: As fatal police shootings increase, more go unreported

First, identify your police department’s ORI code

1. Sign up for an API key to access FBI’s data

Before you proceed, please sign up for an API key on data.gov.

Here’s the documentation for the different FBI crime data endpoints.

2. Set up your api key

fbi_key <- "XXXX"

3. Narrow down your state of choice

state_abb <- "CO"
# Loading the appropriate libraries

library(tidyverse)
library(lubridate)
library(jsonlite)
library(DT)
library(knitr)

# Importing the json data based off the API
state_df <- fromJSON(paste0("https://api.usa.gov/crime/fbi/sapi/api/agencies/byStateAbbr/", str_to_upper(state_abb), "?API_KEY=", fbi_key))

state_df <- state_df$results

# dropping a few columns for display purposes
state_df %>% 
  select(-state_name, -nibrs, -nibrs_start_date, -region_desc, -division_name) %>% 
  datatable()

Above, we have all the agency identification numbers from the state we specified.

4. Export the state dataframe as a spreadsheet

Save the spreadsheet for easier reference, if you’d like.

write_csv(state_df, paste0(state_abb, "_oricodes.csv"), na="")

You should be able to narrow down the law enforcement agency of your choice through the agency_name column.

Here’s how.

5. Example: Denver

# saving the name of an agency we want to look up
department_search <- "denver police"

# filtering out the oricode from the imported state data based on the agency name
ori_only <- state_df %>% 
  filter(str_detect(str_to_lower(agency_name), str_to_lower(department_search))) %>% 
  pull(ori)

ori_only
## [1] "CODPD0000"

Now we know that Denver Police’s ori code is CODPD0000.

Next, get employment data on the agency

1. Import the data from a new API endpoint

You’ll need 3 arguments:

  • oricode
  • beginning year
  • end year
start <- 2015
end <- 2021

emp_url <- paste0("https://api.usa.gov/crime/fbi/sapi/api/police-employment/agencies/", ori_only, "/", start, "/", end, "?API_KEY=", fbi_key)

emp_list <- fromJSON(emp_url)


emp_list <- emp_list$results

# dropping a few columns for display purposes

emp_list_df <- emp_list %>% 
  select(ori, agency_name_edit, year=data_year, population, agency_name_edit, female_officer_ct, male_officer_ct) %>% 
  # calculating total officer count by adding female and male officer counts
  mutate(officer_count=female_officer_ct+male_officer_ct) %>% 
  mutate(year=as.numeric(year))


emp_list_df %>% 
  datatable()

Officer counts are necessary to calculate fatal police shootings per officer.

FBI’s agency-level police-involved shootings statistics

The FBI’s API does not give the level of detail necessary to isolate at an agency level officer-involved justifiable homicides from civilian-involved ones.

Going down this path of analysis will require pulling the full Expanded Homicide data set from the FBI’s Crime Data Explorer site. Alternatively, Jacob Kaplan’s Concatenated UCR files from 1976-2020 are also available. A summarized version from the Post of homicide totals and officer shootings by agency by year can be found here.

Downloading Fatal Force data specific to an agency

1. Import the relationship file between Fatal Force ids and oricodes

Before it recently connected department names to federal ori codes, The Post used internal id numbers to identify law enforcement agencies.

Joining the FBI data with the Post data requires the following crosswalk file.

fatal_force_ag <- read_csv("https://github.com/washingtonpost/data-police-shootings/raw/master/v2/fatal-police-shootings-agencies.csv")

# showing the first five rows

fatal_force_ag %>% 
  slice(1:5) %>% 
  datatable()

This table shows the total shootings in the Post’s Fatal Force data by police department starting in 2015 until today.

Grab the id for the police agency we’re interested in based on the oricode identified above. Using the oricode as a way to filter is better than the agency name because there may be inconsistent spellings between the Fatal Force and FBI data sets.

#Using string detect instead of == because there are some agencies we grouped multiple oricodes for simplicity, ie. state police

fatal_force_id <- fatal_force_ag %>% 
  filter(str_detect(ori_only, oricodes)) %>% 
  pull(id) 

fatal_force_id
## [1] 106

1. Download Fatal Force data and filter

Now that we have the department id, we can download the Fatal Force data and filter it to that specific agency.

Some shootings involve more than agency, so we need to transform the data a little to split them up individually.

Note: id from the department data is agency_ids in the shootings data

fatal_force_df <- read_csv("https://github.com/washingtonpost/data-police-shootings/raw/master/v2/fatal-police-shootings-data.csv")

# Splitting out the incidents with multiple departments so they stand alone and can then be filtered successfully

fatal_force_df <- separate_rows(fatal_force_df, agency_ids, convert = TRUE)

# Now we can filter without worry of leaving any out
fatal_force_df <- fatal_force_df %>% 
  filter(agency_ids==fatal_force_id) 

# Showing a selection of the columns (not all)
fatal_force_df %>% 
  select(id, agency_ids, date, flee_status, armed_with, city, state, name, age, gender, race ) %>% 
  kable()
id agency_ids date flee_status armed_with city state name age gender race
20 106 2015-01-26 not vehicle Denver CO Jessie Hernandez 17 female H
628 106 2015-07-12 foot knife Denver CO Paul Castaway 35 male N
1029 106 2015-11-22 not gun Denver CO Miguel Angel Martinez 28 male H
1049 106 2015-12-02 car gun Denver CO Phillip Munoz 35 male H
1164 106 2016-01-11 not gun Denver CO Ramone Lonergan 32 male W
1301 106 2016-02-22 other gun Denver CO Gerardino Cayetano Gonzalez Jr. 33 male H
1456 106 2016-04-12 car unarmed Denver CO Dion Daman 40 male W
1821 106 2016-08-27 not knife Denver CO Terry Salazar 49 male NA
1841 106 2016-08-31 foot gun Denver CO Michael Ferguson 21 male W
2031 106 2016-11-08 other gun Denver CO Juan Ramos 23 male H
2979 106 2017-09-22 not undetermined Denver CO NA NA male W
3130 106 2017-11-10 foot gun Denver CO John Bazemore III 25 male B
3387 106 2018-02-06 not knife Denver CO Alex Duran 29 male H
3411 106 2018-02-13 not gun;knife Denver CO Peter Le 52 male A
3520 106 2018-03-19 car unarmed Denver CO Steven Nguyen 27 male A
3644 106 2018-04-25 car gun Denver CO Charles Boeh 36 male W
3771 106 2018-06-13 foot gun Denver CO NA NA male NA
4548 106 2019-02-25 not gun Denver CO David Litton 40 male W
4823 106 2019-07-01 not gun Denver CO Christopher Barela 22 male H
4856 106 2019-07-04 foot gun Lakewood CO Leonel Mendez 34 male H
4966 106 2019-08-15 foot gun Denver CO Jamie Fernandez 31 female H
5031 106 2019-08-31 not gun Denver CO Juan Carlos Macias 37 male H
5214 106 2019-10-21 car gun Denver CO Adam Martinez 36 male H
5403 106 2020-01-01 not knife Denver CO Gerardo Antonio Conchas-Bustas 20 male H
5355 106 2020-01-06 car gun Aurora CO Nico Descheenie 25 male W
5425 106 2020-01-25 car gun Aurora CO D’ovion Semaj Perkins 19 male B
5776 106 2020-05-01 other gun Denver CO William Debose 21 male B
5938 106 2020-06-22 not gun Aurora CO David Guillen 22 male H
6161 106 2020-09-09 not gun Denver CO Antonio Black Bear 41 male N
6160 106 2020-09-12 other gun Denver CO Christopher Escobedo NA male H
6172 106 2020-09-15 not gun Denver CO NA NA male W
6472 106 2020-12-28 foot gun Denver CO Larry Hamm 47 male B
6855 106 2021-05-14 car gun Denver CO NA NA male NA
6870 106 2021-05-19 not knife Denver CO Raul Rosas-Zarsosa 52 male NA
6901 106 2021-05-30 not gun Denver CO Shannon Wright 29 male B
7611 106 2021-06-04 not gun Denver CO Colton Frederic Wagner 31 male W
6945 106 2021-06-13 not gun Denver CO Duane Manzanares Jr. 30 male H
8244 106 2022-07-13 car gun Denver CO NA NA male NA
8253 106 2022-07-15 not knife Denver CO Chaz Gallegos 33 male W
8466 106 2022-09-29 foot gun Broomfield CO NA NA male NA

These are all the police shootings tracked by the Post for the specific department.

Now, we count up the shootings by year and join it with the FBI data

2. Summarize by year and join with employment data

# creating a new year column based on the year
fatal_force_df_annual <- fatal_force_df %>% 
  mutate(year=year(date)) %>%
  count(year, name="fatal_force")

kable(fatal_force_df_annual)
year fatal_force
2015 4
2016 6
2017 2
2018 5
2019 6
2020 9
2021 5
2022 3 
# join with the FBI data

fatal_force_df_joined <- fatal_force_df_annual %>% 
  full_join(emp_list_df) %>% 
  mutate(per1ko=fatal_force/officer_count*1000) %>% 
  filter(year!=2022) # taking out 2022 data because 2022 FBI data doesn't exist yet
## Joining, by = "year"
kable(fatal_force_df_joined)
year fatal_force ori agency_name_edit population female_officer_ct male_officer_ct officer_count per1ko
2015 4 CODPD0000 DENVER 682418 164 1304 1468 2.724796
2016 6 CODPD0000 DENVER 699259 176 1307 1483 4.045853
2017 2 CODPD0000 DENVER 706616 191 1288 1479 1.352265
2018 5 CODPD0000 DENVER 720745 208 1309 1517 3.295979
2019 6 CODPD0000 DENVER 728941 217 1334 1551 3.868472
2020 9 CODPD0000 DENVER 737709 226 1326 1552 5.798969
2021 5 CODPD0000 DENVER 740209 214 1233 1447 3.455425

3. Calculating average annual rate

It’s useful to have a single number, which is why we average the annual rate. This minimizes the intensity of outlier years but for comparison purposes, we also limit the rates of the smallest 5% of agencies in officer count in our interactive. Here’s how to get the rate.

average_annual <- sum(fatal_force_df_joined$per1ko, na.rm = T)/length(fatal_force_df_joined)

print(paste0(str_to_title(department_search), " average shootings per 1,000 officers: ", round(average_annual, 2)))
## [1] "Denver Police average shootings per 1,000 officers: 2.73"