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-raw/AllStatesAndTerritories_04012024.xlsx"
path <- "data-raw/DISP_AllStatesAndTerritories_07012024.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: 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"…leso %>% summary() sheet state agency_name nsn
Length:95056 Length:95056 Length:95056 Length:95056
Class :character Class :character Class :character Class :character
Mode :character Mode :character Mode :character Mode :character
item_name quantity ui acquisition_value
Length:95056 Min. : -72.000 Length:95056 Min. : 0
Class :character 1st Qu.: 1.000 Class :character 1st Qu.: 138
Mode :character Median : 1.000 Mode :character Median : 499
Mean : 3.513 Mean : 16280
3rd Qu.: 1.000 3rd Qu.: 1040
Max. :5000.000 Max. :22000000
demil_code demil_ic ship_date
Length:95056 Length:95056 Min. :1990-05-03 00:00:00.00
Class :character Class :character 1st Qu.:2006-05-04 00:00:00.00
Mode :character Mode :character Median :2012-04-02 00:00:00.00
Mean :2011-09-23 16:07:58.67
3rd Qu.:2016-10-06 00:00:00.00
Max. :2024-09-28 00:00:00.00
station_type
Length:95056
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")