Texas Analysis

Goals of this notebook

This page is dedicated to exploring local law enforcement surplus military equipment from the “1033 program” handled through the Law Enforcement Support Office. All answers will be based on controlled items given to Texas agencies from Jan. 1, 2010 to present.

The questions we want to answer:

  • How many total “controlled” items were transferred to Texas agencies, and what are they all worth?
  • How many total “controlled” items did each agency get and how much was it all worth? Which agency got the most stuff?
  • How about local police agencies? What was the total quantity and value?
  • What specific “controlled” items did each agency get and how much were they worth?
  • What did local agencies get?

Setup

As usual, the first step is setting up packages.

library(tidyverse)
library(janitor)

Import

We will load the data from the previous notebook into a tibble.

leso <- read_rds("data-processed/01-leso-all.rds")

leso |> glimpse()
Rows: 65,172
Columns: 13
$ 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          <dbl> 1, 3, 1, 1, 1, 1, 1, 3, 3, 3, 3, 3, 3, 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"…
$ total_value       <dbl> 168542.40, 10000.00, 16260.00, 3285.00, 62627.00, 65…
$ control_type      <lgl> TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE…

Get the controlled items

For this analysis we want to focus on “controlled” items instead of the more generic non-controlled items we learned about during cleaning Let’s filter to capture just the controlled data for our analysis.

The function nrow() just counts the number of rows that we can verify.

leso_c <- leso |> 
  filter(control_type == TRUE)

leso_c |> nrow()
[1] 57915

Filter for Texas

In this analysis we only want to focus on Texas data for locality.

leso_c_tx <- leso_c |>
  filter(state == "TX")

leso_c_tx

Summarize across the data

For getting total values, we will use the function sum(quantity) which will add all the values in quantity column together. We should end up with the number of items all Texas law enforcement agencies have received and how much they are worth (note that acquisition_ value is invalid because it only accounts for individual items).

We can rename output columns inside their functions.

leso_c_tx |>
  summarize(
    summed_quantity = sum(quantity),
    summed_total_value = sum(total_value)
  )

Data Takeaway: Since 2010, Texas law enforcement agencies have received 19 thousand pieces of equipment from surplus U.S. military units, all worth approximately 124.5 million dollars.

Totals by agency

The next question is: For each agency, how many things did they get and how much was it all worth? In other words, we group our table by agencies to find their quantity and total value.

Don’t forget to check the math by filtering an example agency and checking the math of total values. Then, we can arrange the numbers in a descending order.

Saving the result into a new tibble will allow us to reuse it later.

tx_agency_totals <- leso_c_tx |>
  group_by(agency_name) |>
  summarize(
    summed_quantity = sum(quantity), 
    summed_total_value = sum(total_value)
  ) |>
  arrange(summed_total_value |> desc())

tx_agency_totals

Data Takeaway: Houston, the largest city in Texas, tops the number of surplus military equipment received in the state with 2,359 items since 2010. The total worth of the equipment is roughly 5 million dollars. Jefferson County Sheriffs Office takes second place with 206 items worth $922,700.

Filtering local agencies

By creating a vector, we can filter the data with multiple items. To use the result, we will save it into a new tibble once again.

local_agencies <- c(
  "AUSTIN PARKS POLICE DEPT", #NI
  "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", #NI
  "GEORGETOWN POLICE DEPT",
  "GRANGER POLICE DEPT", #NI
  "HAYS CO CONSTABLE PRECINCT 4",
  "HAYS COUNTY SHERIFFS OFFICE",
  "HUTTO POLICE DEPT",
  "JARRELL POLICE DEPT", #NI
  "JONESTOWN POLICE DEPT", #NI
  "KYLE POLICE DEPT",
  "LAGO VISTA POLICE DEPT",
  "LAKEWAY POLICE DEPT",
  "LEANDER POLICE DEPT",
  "LIBERTY HILL POLICE DEPT", #NI
  "LOCKHART POLICE DEPT",
  "LULING POLICE DEPT",
  "MANOR POLICE DEPT",
  "MARTINDALE POLICE DEPT", #NI
  "PFLUGERVILLE POLICE DEPT",
  "ROLLINGWOOD POLICE DEPT", #NI
  "SAN MARCOS POLICE DEPT",
  "SMITHVILLE POLICE DEPT", #NI
  "SUNSET VALLEY POLICE DEPT", #NI
  "TAYLOR POLICE DEPT", #NI
  "THRALL POLICE DEPT", #NI
  # TEXAS STATE UNIVERSITY HI_ED
  "TRAVIS COUNTY SHERIFFS OFFICE",
  # TRAVIS CONSTABLE OFFICE,
  # SOUTHWESTERN UNIVERSITY HI_ID
  "WESTLAKE HILLS POLICE DEPT", #NI
  "UNIV OF TEXAS SYSTEM POLICE HI_ED",
  "WILLIAMSON COUNTY SHERIFF'S OFFICE"
)

tx_agency_totals |>
  filter(agency_name %in% local_agencies) |>
  arrange(summed_quantity |> desc())

Data Takeaway: Despite its lower population among Central Texas agencies, the San Marcos Police Department has the second-most number of U.S. military surplus equipment worth nearly 3.2 million dollars.

Types of items

Our question is this: What specific “controlled” items did each agency get and how much were they worth? This allows us to look up information about specific items on Google or elsewhere.

tx_agency_item_totals <- leso_c_tx |>
  group_by(agency_name, item_name) |>
  summarize(
    summed_quantity = sum(quantity),
    summed_total_value = sum(total_value)
  ) |> 
  arrange(summed_total_value |> desc())
`summarise()` has grouped output by 'agency_name'. You can override using the
`.groups` argument.
tx_agency_item_totals

Items for local agencies

Using the previous summary of items, let’s filter it to focus on just local agencies.

tx_agency_item_totals |> 
  filter(agency_name %in% local_agencies)

Data Takeaway: Although the most expensive military surplus item - an 8.3 million dollar helicopter - among Central Texas counties belongs to the Austin Police Department, the second most pricey gadget is a mine resistant vehicle. According to Google, these transportation devices are deployed during SWAT responses, with agencies at San Marcos and Bastrop each having one in possession.