LESO Explore

Published

January 6, 2025

John Templon at Buzzfeed wondered why the acquisition_value was different for the same item listed in the LESO data. This explores that concept.

Setup

library(tidyverse)
library(lubridate)
library(readxl)
library(janitor)

Import

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

# columns of interest
cols <- c(
  "agency_name",
  "item_name",
  "quantity",
  "ui",
  "acquisition_value",
  "ship_date",
  "station_type"
  )

nsn_list <- read_excel("data-raw/nsn-extract-3-17-21.xlsx") |> clean_names()

Price differences

A quick look at popular item names in the data.

leso %>% 
  count(item_name) %>% 
  arrange(n %>% desc())

Let’s look at “RIFLE,5.56 MILLIMETER”. Filter for those.

rifles <- leso %>% 
  filter(item_name == "RIFLE,5.56 MILLIMETER")

Apparently rifles of different NSN (National Stock Number) can have different prices.

rifles %>% 
  count(nsn, acquisition_value)

I sent a query on 2020-12-12 to LESO to see if there is a data source for NSN. I believe further searching might reveal, but also might require a level of government access.

Demil codes

There are two Demil codes listed for each item. They relate to the level of destruction needed for each item.

With the list above we could build a lookup table. Not sure if it is warranted.

Looking here at how they are distributed.

leso %>% 
  count(demil_code)

leso %>% 
  count(demil_ic)

Controlled vs non-controlled property

I thought there was a major data issue when numbers from two different data downloads until I read more about controlled vs non-controlled property on the LESO Public Information page. Non-controlled items like boots or blankets (as opposed to guns) are removed from the database after a year, so there is a big drop in the number of records for recent years. Interesting, the number of records from past years does change over time, but not to the same degree. I have not explored those differences.

Import two different downloads from about 6 months apart.

leso_qtr1fy22 <- read_rds("data-processed/leso-qtr1fy22.rds")
leso_qtr3fy21 <- read_rds("data-processed/leso-qtr3fy21.rds")

Newer data seems to have fewer records than the last update so I wondering why.

Count records from the newer data

leso_22 <- leso_qtr1fy22 %>% 
  count(year = year(ship_date), name = "qtr1fy22") %>% 
  arrange(year %>% desc()) %>% 
  mutate(year = year %>% as.character())

leso_22

Count records from older data

leso_21 <- leso_qtr3fy21 %>% 
  count(year = year(ship_date), name = "qtr3fy21") %>% 
  arrange(year %>% desc()) %>% 
  mutate(year =  year %>% as.character())

leso_21

Join the two files together and calculate the difference in records

leso_21 %>% 
  left_join(leso_22) %>% 
  mutate(diff = qtr1fy22 - qtr3fy21)

Joining with `by = join_by(year)`

Exploring non-controlled property

After exploring controlled vs non-controlled property, I corresponded with the DLA in June 2022.

Question: Is there a way to tell from the data itself if the item is a controlled vs non-controlled item? I thought perhaps the DEMIL IC field with value of “A” might be non-controlled, but there seem to be items older than a year with that categorization that could be controlled and accurately remain, like RECON SCOUT XT,SPEC and AIRPLANE,CARGO-TRANSPORT.

Answer: Property with the DEMIL codes A and Q6 are considered non-controlled general property and fall off the LESO property books after one year. All other Demil codes are considered controlled items and stay on the LESO property book until returned to DLA for disposition/disposal.

Followup question: When you say Q6, do you mean a demil_code of “Q” combined with demil_ic of “6”. Would other property with demil_code “Q” but with a demil_ic of other numbers not be controlled? Second question: In the 22Q2 data, there are a number of items older than a year (n = 62) that are DEMIL CODE A and DEMIL IC 1 and they do seem like big items that might be “controlled”. Some examples: RECON SCOUT XT,SPEC (ALABAMA LAW ENFORCEMENT AGENCY, 2016-08-25) and AIRPLANE,CARGO-TRANSPORT (ARIZONA DEPT OF PUBLIC SAFETY, 2015-12-03, $17,000,000). I could see the argument for them to be controlled (though SEAT,AIRCRAFT might be a maybe on that).

Followup answer: The general rule is that property coded A and Q6 (6 being the integrity code) falls off the LESO property book after one year. However, there are some exceptions. For instance, aircraft are always controlled regardless of the demil code. Also, LESO has the discretion to keep items as controlled despite the demil code. This happens with some high value items. The RECON SCOUT XT, SPEC example you cited was actually initially coded incorrectly because the wrong NSN was used when it was coded as A. Once the correct NSN was verified with the manufacturer, is was renamed and recoded as D. The next quarterly report will reflect the new name and code.

So … If we exclude non-controlled, we should keep aircraft.

Exploring the demil_code as possible angle.

leso |> 
  count(demil_code)

Look at combination of demil_code and demil_ic (integrity code)

leso |> 
  count(demil_code, demil_ic)

Looking at demil code docs perhaps “A” is our value. (It is, as confirmed by DLA.)

leso |> 
  filter(demil_code == "A") |> 
  count(item_name, acquisition_value)

This is looking reasonable.

OK, are there any “Old” A records?

leso |> 
  filter(demil_code == "A") |> 
  count(year(ship_date))

What are the old ones?

old_controlled <- leso |> 
  filter(
    (demil_code == "A" | (demil_code == "Q" & demil_ic == 6)),
    date(ship_date) < "2021-03-21"
  ) |> 
  select(state, agency_name, item_name, quantity, acquisition_value, demil_code, demil_ic, ship_date)

old_controlled

smallest value?

old_controlled |> 
  filter(!(str_detect(item_name, "AIR"))) |> 
  summarise(min(acquisition_value))

old_controlled |> arrange(acquisition_value)

From this and discussions with the DLA we know this:

The RECON SCOUT XT,SPEC records are an error. They should’ve been classified as “D”.
Anything with AIRCRAFT should be retained. This is why AIRPLANE,CARGO-TRANSPORT is still on the list. Probably why SEAT,AIRCRAFT is as well, though perhaps needlessly.
Other ’high value” A/Q6 records might need to be retained. Maybe that is why TRUCK,STAKE and RECEIVER,INFRARED remain?

Hoe does this compare to the most recent year (as of 22Q2).

new_AQ6 <- leso |> 
  filter(
    (demil_code == "A" | (demil_code == "Q" & demil_ic == 6)),
    date(ship_date) >= "2021-03-21"
  ) |> 
  select(state, agency_name, item_name, quantity, acquisition_value, demil_code, demil_ic, ship_date)

new_AQ6

new_AQ6 |> 
  filter(acquisition_value > 40000)

Working out non-controlled exclusion

As of right now, I would exclude these items like this:

This doesn’t take aircraft or expensive items into account.

non_controled_explore <- leso |> 
  filter(
    (demil_code == "A" | (demil_code == "Q" & demil_ic == 6))
  ) |> 
  select(agency_name, item_name, demil_code, demil_ic)

non_controled_explore

Controlled category

A better way to handle might be to create a “controlled” category.

Using case_when, it might go like this:

Mark anything with A or Q6 with “AIRPLANE” as controlled.
Mark anything with A or Q6 above $x as controlled (This is case-by-case so we can’t code this)
Mark all other A, Q6 items as non-controlled
Mark everything else controlled.

leso_control <- leso |> 
  mutate(
    control_type = case_when(
      # airplane true
      ((demil_code == "A" | (demil_code == "Q" & demil_ic == 6)) & str_detect(item_name, "AIRPLANE")) ~ T,
      # Other A,Q6 false
      (demil_code == "A" | (demil_code == "Q" & demil_ic == 6)) ~ F,
      TRUE ~ T
    )
  )

leso_control |> 
  # filter(str_detect(item_name, "AIR")) |> 
  filter((demil_code == "A" | (demil_code == "Q" & demil_ic == 6))) |> 
  arrange(desc(acquisition_value)) |> 
  select(item_name, demil_code, demil_ic, control_type, acquisition_value)

Airplanes

DLA said airplanes are always controlled. Let’s look at names to find the best way to isolate them.

It looks like “AIRPLANE” refers to the actual planes, and any related parts use the term “AIRCRAFT”.

leso |> 
  filter(str_detect(item_name, "AIR")) |> 
  count(item_name)

leso |> 
  filter(str_detect(item_name, "AIRPLANE"))

leso |> 
  filter(str_detect(item_name, "HELI")) |> 
  count(item_name, demil_code, demil_ic) |> 
  arrange(demil_code, demil_ic)

NSN exploration

The list I have does not have many matches.

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"…

nsn_list |> glimpse()

Rows: 12,810
Columns: 7
$ nsn         <chr> "3230-01-574-9904", "3510-00-222-1457", "3510-00-273-9738"…
$ rep_office  <chr> "Hardware", "General Products", "General Products", "Gener…
$ common_name <chr> "Reciprocating Saw Blade", "Laundry Bag Pin", "Laundry Bag…
$ description <chr> "Replacement blade for most portable electric or pneumatic…
$ price       <dbl> 10.90, 107.53, 41.36, 90.42, 9.75, 9.88, 227.13, 146.91, 1…
$ ui          <chr> "PG", "HD", "DZ", "DZ", "EA", "EA", "EA", "EA", "EA", "EA"…
$ aac         <chr> "H", "H", "H", "H", "H", "H", "H", "H", "H", "H", "H", "H"…

leso |> 
  left_join(nsn_list, by = "nsn") |> 
  filter(!is.na(common_name))