library(tidyverse)
library(readxl)
library(janitor)LESO wrangling
Warning
March 31, 2023 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_06302025.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: 85,337
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> "1005-01-587-7175", "1240-01-411-1265", "2540-01-565…
$ item_name <chr> "MOUNT,RIFLE", "SIGHT,REFLEX", "BALLISTIC BLANKET KI…
$ quantity <dbl> 10, 9, 10, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,…
$ ui <chr> "Each", "Each", "Kit", "Each", "Each", "Each", "Each…
$ acquisition_value <dbl> 1626.00, 342.00, 18906.60, 658000.00, 10000.00, 6262…
$ demil_code <chr> "D", "D", "D", "C", "Q", "C", "C", "D", "D", "D", "D…
$ demil_ic <chr> "1", "0", "1", "1", "3", "1", "1", "1", "1", "1", "1…
$ ship_date <dttm> 2016-09-19, 2016-09-14, 2018-01-30, 2016-11-09, 201…
$ station_type <chr> "State", "State", "State", "State", "State", "State"…leso %>% summary() sheet state agency_name nsn
Length:85337 Length:85337 Length:85337 Length:85337
Class :character Class :character Class :character Class :character
Mode :character Mode :character Mode :character Mode :character
item_name quantity ui acquisition_value
Length:85337 Min. : 1.00 Length:85337 Min. : 0
Class :character 1st Qu.: 1.00 Class :character 1st Qu.: 138
Mode :character Median : 1.00 Mode :character Median : 499
Mean : 3.76 Mean : 17656
3rd Qu.: 1.00 3rd Qu.: 1185
Max. :38631.00 Max. :22000000
demil_code demil_ic ship_date
Length:85337 Length:85337 Min. :1990-05-03 00:00:00.00
Class :character Class :character 1st Qu.:2006-09-06 00:00:00.00
Mode :character Mode :character Median :2012-04-26 00:00:00.00
Mean :2011-12-20 01:14:22.68
3rd Qu.:2016-12-19 00:00:00.00
Max. :2025-06-30 00:00:00.00
station_type
Length:85337
Class :character
Mode :character
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")