library(tidyverse)
library(readxl)
library(janitor)LESO wrangling
Warning
December 31, 2025 update. With each update, check the “Austin MSA agencies” in the analysis notebook.
This notebook processes data by the Defense Logistics Agency about military surplus transfers to local law enforcement through the Law Enforcement Support Office (LESO) or LESO Program.
The data is updated quarterly. As of this date, the file name is linked from the headline “ALASKA - WYOMING AND US TERRITORIES”.
The spreadsheet has a a sheet for each state. This notebook puts all the sheets together into a single dataset for easier analysis.
There is some cleaning done here based on June 2022 research. See the README.
Setup
Import
# set path to data
## change name to update to new file
# path <- "data-original/AllStatesAndTerritories_04012024.xlsx"
path <- "data-original/DISP_AllStatesAndTerritories_06302026.xlsx"
# import and combine sheets
leso <- path %>%
excel_sheets() %>%
map_df(~ read_excel(path = path, sheet = .x), .id = "sheet") %>%
clean_names()
# peek at names
leso %>% names() [1] "sheet" "state" "agency_name"
[4] "nsn" "item_name" "quantity"
[7] "ui" "acquisition_value" "demil_code"
[10] "demil_ic" "ship_date" "station_type" # peek at df
leso %>% head()# glimpse at df
leso %>% glimpse()Rows: 75,087
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> "2320-01-371-9584", "1385-01-574-4707", "2540-01-565…
$ item_name <chr> "TRUCK,UTILITY", "UNMANNED VEHICLE,GROUND", "BALLIST…
$ quantity <dbl> 1, 1, 10, 10, 1, 9, 1, 1, 7, 1, 6, 1, 11, 1, 1, 1, 1…
$ ui <chr> "Each", "Each", "Kit", "Each", "Each", "Each", "Each…
$ acquisition_value <dbl> 62627.00, 10000.00, 16568.15, 1626.00, 658000.00, 33…
$ demil_code <chr> "C", "Q", "D", "D", "C", "Q", "C", "D", "Q", "D", "Q…
$ demil_ic <chr> "1", "3", "1", "1", "1", "3", "1", "1", "3", "1", "3…
$ ship_date <dttm> 2016-09-29, 2017-03-28, 2018-01-30, 2016-09-19, 201…
$ station_type <chr> "State", "State", "State", "State", "State", "State"…leso %>% summary() sheet state agency_name nsn
Length :75087 Length :75087 Length :75087 Length :75087
N.unique : 52 N.unique : 52 N.unique : 4365 N.unique : 3358
N.blank : 0 N.blank : 0 N.blank : 0 N.blank : 0
Min.nchar: 1 Min.nchar: 2 Min.nchar: 6 Min.nchar: 16
Max.nchar: 2 Max.nchar: 2 Max.nchar: 35 Max.nchar: 16
item_name quantity ui acquisition_value
Length :75087 Min. : 1.000 Length :75087 Min. : 0
N.unique : 1834 1st Qu.: 1.000 N.unique : 11 1st Qu.: 138
N.blank : 0 Median : 1.000 N.blank : 0 Median : 499
Min.nchar: 3 Mean : 2.826 Min.nchar: 3 Mean : 19633
Max.nchar: 70 3rd Qu.: 1.000 Max.nchar: 8 3rd Qu.: 1656
Max. :1600.000 Max. :22000000
demil_code demil_ic ship_date
Length :75087 Length :75087 Min. :1990-05-03 00:00:00
N.unique : 6 N.unique : 6 1st Qu.:2006-11-13 00:00:00
N.blank : 0 N.blank : 0 Median :2012-04-24 00:00:00
Min.nchar: 1 Min.nchar: 1 Mean :2011-10-16 17:12:13
Max.nchar: 1 Max.nchar: 1 3rd Qu.:2016-09-08 00:00:00
NAs : 3577 Max. :2026-06-26 00:00:00
station_type
Length :75087
N.unique : 1
N.blank : 0
Min.nchar: 5
Max.nchar: 5
Clean up RECON SCOUT XT,SPEC
At one point, I found a mistake in the data where a recon robit was not considered controlled. I questioned DLA about it and they said it was a mistake. I fixed it in that version of the data, but it isn’t in the most recent version. It is worth checking.
leso |>
filter(str_detect(item_name, "RECON SCOUT")) |>
count(item_name, demil_code)If anything in that list shows up as demil_code “A” or “Q6” then it should be changed to “D”.
You can use the code below to change them. It’s commented out for now. Update the export tibble if needed.
# leso_fixed <- leso |>
# mutate(
# demil_code = case_when(
# item_name == "RECON SCOUT XT,SPEC" ~ "D",
# TRUE ~ demil_code
# )
# )Some basic checks
This just makes sure we didn’t miss a bunch of states.
leso |>
count(state)Write files
Write files for export for future notebooks or other reasons.
# write data to csv
leso %>%
write_csv("data-processed/leso.csv")
leso %>%
write_rds("data-processed/leso.rds")