This notebook explores and ranks Texas police agencies according to Police Employee Data from the FBI Crime Data Explorer. See the README.md for more information.

Dependencies

I had trouble importing the original data into RStudeio, so you have to run the “01-file-process.ipyng” notebook first, which needs csvkit and jupyter notebooks.

library(tidyverse)
library(janitor)

Import data

There are some duplicate rows, so we include the unique() function.

tx_2018 <- read_csv("data/pe_texas_2018.csv") %>% unique()
## Parsed with column specification:
## cols(
##   .default = col_double(),
##   ori = col_character(),
##   pub_agency_name = col_character(),
##   pub_agency_unit = col_character(),
##   state_abbr = col_character(),
##   division_name = col_character(),
##   region_name = col_character(),
##   county_name = col_character(),
##   agency_type_name = col_character(),
##   population_group_desc = col_character()
## )
## See spec(...) for full column specifications.

Explore

Peek at the data

tx_2018 %>% head()

Get the column names

tx_2018 %>% names()
##  [1] "data_year"             "ori"                   "pub_agency_name"      
##  [4] "pub_agency_unit"       "state_abbr"            "division_name"        
##  [7] "region_name"           "county_name"           "agency_type_name"     
## [10] "population_group_desc" "population"            "male_officer_ct"      
## [13] "male_civilian_ct"      "male_total_ct"         "female_officer_ct"    
## [16] "female_civilian_ct"    "female_total_ct"       "officer_ct"           
## [19] "civilian_ct"           "total_pe_ct"           "pe_ct_per_1000"

Types of agencies

tx_2018 %>% 
  count(agency_type_name)

Rank all agencies by officer count

tx_2018 %>% 
  arrange(officer_ct %>% desc()) %>% 
  select(pub_agency_name, agency_type_name, pub_agency_unit, officer_ct) %>% 
  head(50)

filter to just city & county types

tx_local_2018 <- tx_2018 %>% 
  filter(agency_type_name %in% c("City","County")) %>% 
  arrange(officer_ct %>% desc()) %>% 
  select(pub_agency_name, agency_type_name, pub_agency_unit, officer_ct)

tx_local_2018
tx_local_2018 %>% write_csv("data/tx_local_2018.csv")

Explore Independent School District:

tx_ed_2018 <- tx_2018 %>% 
  filter(pub_agency_name == "Independent School District:", officer_ct > 50) %>% 
  arrange(officer_ct %>% desc()) %>% 
  select(pub_agency_name, agency_type_name, pub_agency_unit, officer_ct) %>% 
  head(10)

tx_ed_2018

What is the United school district?

tx_2018 %>% 
  filter(pub_agency_name == "Independent School District:",  pub_agency_unit == "United") %>% 
  arrange(officer_ct %>% desc()) %>% 
  # select(pub_agency_name, agency_type_name, pub_agency_unit, officer_ct) %>% 
  head()

Explore University police

tx_uni_2018 <- tx_2018 %>% 
  filter(agency_type_name == "University or College") %>% 
  arrange(officer_ct %>% desc()) %>% 
    select(pub_agency_name, agency_type_name, pub_agency_unit, officer_ct) %>% 
  head(10)

tx_uni_2018

Austin MSA agencies

Putting together a list of Austin MSA agencies for another project, utdata/rwd-r-leso.

centex_name <- c(
  "BASTROP",
  "CALDWELL",
  "HAYS",
  "TRAVIS",
  "WILLIAMSON"
)

# study to combine name and unit
# needs removal of NA
tx_2018 %>% 
  mutate(
    agency = paste(pub_agency_name, pub_agency_unit)
  ) %>%
  # select(agency) %>% # remove late
  mutate(
    agency = str_remove(agency, " NA")
  )
# create list of non-state agencies in ausitn msa
austin_msa <- tx_2018 %>% 
  filter(county_name %in% centex_name) %>% 
  select(
    pub_agency_name,
    pub_agency_unit,
    county_name,
    agency_type_name
  ) %>% 
  # fix agency name
  mutate(
    agency = paste(pub_agency_name, pub_agency_unit),
    agency = str_remove(agency, " NA")
  ) %>%
  select(agency, county_name, agency_type_name) %>% 
  filter(
    agency_type_name != "Other State Agency"
  ) %>% 
  arrange(county_name, agency_type_name, agency)

## list it
austin_msa

Output files for PIRs

We’re going to look at several kinds of agencies:

tx_pir_police_agencies <- bind_rows(
  tx_uni_2018,
  tx_local_2018
)

tx_pir_police_agencies %>% 
  write_csv("data/tx_pir_police_agencies.csv")

austin_msa %>% 
  write_csv("data/austin_msa_agencies.csv")

Of note, I’m not using school districts in the above list,