LESO Texas and Austin

Published

January 6, 2025

Setup

library(tidyverse)

Import

leso <- read_rds("data-processed/leso.rds")
austin_msa_agencies <- read_csv("data-raw/austin_msa_agencies.csv")

Rows: 35 Columns: 3
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr (3): agency, county_name, agency_type_name

ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.

cols <- c(
  "agency_name",
  "item_name",
  "quantity",
  "ui",
  "acquisition_value",
  "ship_date"
  )

Glimpse the data

leso %>% glimpse()

Rows: 95,056
Columns: 12
$ sheet             <chr> "1", "1", "1", "1", "1", "1", "1", "1", "1", "1", "1…
$ state             <chr> "AL", "AL", "AL", "AL", "AL", "AL", "AL", "AL", "AL"…
$ agency_name       <chr> "ABBEVILLE POLICE DEPT", "ABBEVILLE POLICE DEPT", "A…
$ nsn               <chr> "2540-01-565-4700", "1385-01-574-4707", "1005-01-587…
$ item_name         <chr> "BALLISTIC BLANKET KIT", "UNMANNED VEHICLE,GROUND", …
$ quantity          <dbl> 10, 1, 10, 9, 1, 1, 1, 18, 1, 2, 3, 10, 1, 1, 2, 1, …
$ ui                <chr> "Kit", "Each", "Each", "Each", "Each", "Each", "Each…
$ acquisition_value <dbl> 16854.24, 10000.00, 1626.00, 365.00, 62627.00, 65800…
$ demil_code        <chr> "D", "Q", "D", "D", "C", "C", "C", "Q", "Q", "Q", "Q…
$ demil_ic          <chr> "1", "3", "1", "1", "1", "1", "1", "3", "3", "3", "3…
$ ship_date         <dttm> 2018-01-30 00:00:00, 2017-03-28 00:00:00, 2016-09-1…
$ station_type      <chr> "State", "State", "State", "State", "State", "State"…

Checking date range …

leso$ship_date |> summary()

                      Min.                    1st Qu. 
"1990-05-03 00:00:00.0000" "2006-05-04 00:00:00.0000" 
                    Median                       Mean 
"2012-04-02 00:00:00.0000" "2011-09-23 16:07:58.6670" 
                   3rd Qu.                       Max. 
"2016-10-06 00:00:00.0000" "2024-09-28 00:00:00.0000"

Filter to TX

tx <- leso %>% 
  filter(state == "TX")

Central Texas

This is a list of agencies from utdata/rwd-police-agencies for comparison.

austin_msa_agencies %>% 
  arrange(agency)

Make a list of Austin MSA agencies

This is a very suspect list because it comes from looking through the available agencies “by hand” from the Sept 2020 data. If an item has #NC by it that means it has not been confirmed by a record in the LESO data and may not match. Does not look for public school districts.

Last checked for new items in 04012024 release.

aus_msa_list <- c(
  "AUSTIN PARKS POLICE DEPT", #NC
  "AUSTIN POLICE DEPT",
  "BASTROP COUNTY SHERIFF'S OFFICE",
  "BASTROP POLICE DEPT",
  "BEE CAVE POLICE DEPT",
  "BUDA POLICE DEPT",
  "CALDWELL COUNTY SHERIFFS OFFICE",
  "CEDAR PARK POLICE DEPT",
  "ELGIN POLICE DEPARTMENT",
  "FLORENCE POLICE DEPT", #NC
  "GEORGETOWN POLICE DEPT",
  "GRANGER POLICE DEPT", #NC
  "HAYS CO CONSTABLE PRECINCT 4",
  "HAYS COUNTY SHERIFFS OFFICE",
  "HUTTO POLICE DEPT",
  "JARRELL POLICE DEPT", #NC
  "JONESTOWN POLICE DEPT", #NC
  "KYLE POLICE DEPT",
  "LAGO VISTA POLICE DEPT",
  "LAKEWAY POLICE DEPT", 
  "LEANDER POLICE DEPT",
  "LIBERTY HILL POLICE DEPT", #NC
  "LOCKHART POLICE DEPT",
  "LULING POLICE DEPT",
  "MANOR POLICE DEPT",
  "MARTINDALE POLICE DEPT", #NC
  "PFLUGERVILLE POLICE DEPT",
  "ROLLINGWOOD POLICE DEPT", #NC
  "SAN MARCOS POLICE DEPT",
  "SMITHVILLE POLICE DEPT", #NC
  "SUNSET VALLEY POLICE DEPT", #NC
  "TAYLOR POLICE DEPT", #NC
  "THRALL POLICE DEPT", #NC
  # TEXAS STATE UNIVERSITY HI_ED
  "TRAVIS COUNTY SHERIFFS OFFICE",
  # TRAVIS CONSTABLE OFFICE,
  # SOUTHWESTERN UNIVERSITY HI_ID
  "WESTLAKE HILLS POLICE DEPT", #NC
  "UNIV OF TEXAS SYSTEM POLICE HI_ED",
  "WILLIAMSON COUNTY SHERIFF'S OFFICE"
)

aus_msa <- tx %>% 
  filter(agency_name %in% aus_msa_list)

For running checks

tx %>% 
  count(agency_name)

Austin MSA

aus_msa %>% 
  group_by(agency_name) %>% 
  summarize(
    cnt = n(),
    total_value = sum(acquisition_value * quantity)
  ) %>% 
  arrange(total_value %>% desc())

Williamson County Sheriff

wilco <- aus_msa %>% 
  filter(agency_name == "WILLIAMSON COUNTY SHERIFF'S OFFICE")

wilco %>% 
  select(cols) %>% 
  group_by(item_name) %>% 
  summarize(
    cnt = sum(quantity),
    total_value = sum(acquisition_value * quantity)
    ) %>% 
  arrange(total_value %>% desc())

Warning: Using an external vector in selections was deprecated in tidyselect 1.1.0.
ℹ Please use `all_of()` or `any_of()` instead.
  # Was:
  data %>% select(cols)

  # Now:
  data %>% select(all_of(cols))

See <https://tidyselect.r-lib.org/reference/faq-external-vector.html>.

wilco %>% 
  filter(
    item_name == "RIFLE,7.62 MILLIMETER"
  ) %>% 
  select(cols)

Texas higher education

Look for K-12 and HI_ED

tx %>% 
  filter(
    str_detect(agency_name, "HI_ED")
  ) %>% 
  group_by(agency_name) %>% 
  summarise(
    cnt = sum(quantity),
    total_value = sum(quantity * acquisition_value)
  ) %>% 
  arrange(total_value %>% desc())

Texas local school districts

tx %>% 
  filter(
    str_detect(agency_name, "K-12")
  ) %>% 
  group_by(agency_name) %>% 
  summarise(
    cnt = sum(quantity),
    total_value = sum(quantity * acquisition_value)
  ) %>% 
  arrange(total_value %>% desc())

Spring Branch

tx %>% 
  filter(
    agency_name == "SPRING BRANCH ISD PD K-12"
  ) %>% 
  group_by(item_name) %>% 
  summarise(
    cnt = sum(quantity),
    total_value = sum(quantity * acquisition_value)
  )

Checking pistol

tx %>% 
  filter(
    agency_name == "SPRING BRANCH ISD PD K-12",
    item_name == "PISTOL,CALIBER .45,AUTOMATIC"
  ) %>% select(cols, -agency_name)