library(tidyverse)Analysis
Setup
This chunk is loading in the libraries
Importing the cleaned data
In this code chunk we are importing the cleaned data from the data processed folder
leso <- read_rds("data-processed/01-leso-all.rds")
leso |> glimpse()Rows: 67,852
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> "1005-01-587-7175", "1240-01-411-1265", "2320-01-371…
$ item_name <chr> "MOUNT,RIFLE", "SIGHT,REFLEX", "TRUCK,UTILITY", "BAL…
$ quantity <dbl> 10, 9, 1, 10, 1, 1, 1, 3, 1, 1, 1, 1, 1, 1, 1, 1, 1,…
$ ui <chr> "Each", "Each", "Each", "Kit", "Each", "Each", "Each…
$ acquisition_value <dbl> 1626.00, 371.00, 62627.00, 17264.71, 62627.00, 65800…
$ demil_code <chr> "D", "D", "C", "D", "C", "C", "Q", "D", "C", "D", "D…
$ demil_ic <dbl> 1, 1, 1, 1, 1, 1, 3, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1…
$ ship_date <dttm> 2016-09-19, 2016-09-14, 2016-09-29, 2018-01-30, 201…
$ station_type <chr> "State", "State", "State", "State", "State", "State"…
$ total_value <dbl> 16260.00, 3339.00, 62627.00, 172647.10, 62627.00, 65…
$ control_type <lgl> TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE…Filtering for controlled items
In this code chunk we are filtering to only focus on “controlled” items vs the generic non-controlled items such as boots
leso |>
group_by(control_type) |>
summarize(number_items = n())# A tibble: 2 × 2
control_type number_items
<lgl> <int>
1 FALSE 7918
2 TRUE 59934leso_c <- leso |>
filter(control_type == TRUE)
leso_c |> glimpse()Rows: 59,934
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> "1005-01-587-7175", "1240-01-411-1265", "2320-01-371…
$ item_name <chr> "MOUNT,RIFLE", "SIGHT,REFLEX", "TRUCK,UTILITY", "BAL…
$ quantity <dbl> 10, 9, 1, 10, 1, 1, 1, 3, 1, 1, 1, 1, 1, 1, 1, 1, 1,…
$ ui <chr> "Each", "Each", "Each", "Kit", "Each", "Each", "Each…
$ acquisition_value <dbl> 1626.00, 371.00, 62627.00, 17264.71, 62627.00, 65800…
$ demil_code <chr> "D", "D", "C", "D", "C", "C", "Q", "D", "C", "D", "D…
$ demil_ic <dbl> 1, 1, 1, 1, 1, 1, 3, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1…
$ ship_date <dttm> 2016-09-19, 2016-09-14, 2016-09-29, 2018-01-30, 201…
$ station_type <chr> "State", "State", "State", "State", "State", "State"…
$ total_value <dbl> 16260.00, 3339.00, 62627.00, 172647.10, 62627.00, 65…
$ control_type <lgl> TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE…Filtering for Texas
In this code chunk we are filtering to focus on only Texas data
leso_c_tx <- leso_c |>
filter(state == "TX")
leso_c_tx |> glimpse()Rows: 4,692
Columns: 13
$ state <chr> "TX", "TX", "TX", "TX", "TX", "TX", "TX", "TX", "TX"…
$ agency_name <chr> "ABERNATHY POLICE DEPT", "ABERNATHY POLICE DEPT", "A…
$ nsn <chr> "1240-01-540-3690", "2320-01-371-9584", "2310-01-111…
$ item_name <chr> "SIGHT,REFLEX", "TRUCK,UTILITY", "TRUCK,AMBULANCE", …
$ quantity <dbl> 5, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 4, 1, 1, 1…
$ ui <chr> "Each", "Each", "Each", "Each", "Each", "Each", "Eac…
$ acquisition_value <dbl> 371.00, 62627.00, 96466.00, 120.00, 120.00, 120.00, …
$ demil_code <chr> "Q", "C", "C", "D", "D", "D", "D", "D", "Q", "C", "C…
$ demil_ic <dbl> 3, 1, 1, 1, 1, 1, 1, 1, 3, 1, NA, 1, NA, 1, NA, NA, …
$ ship_date <dttm> 2016-02-02, 2016-03-07, 2020-12-03, 2011-09-13, 201…
$ station_type <chr> "State", "State", "State", "State", "State", "State"…
$ total_value <dbl> 1855.00, 62627.00, 96466.00, 120.00, 120.00, 120.00,…
$ control_type <lgl> TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE…Getting total values
In this code chunk we are using sum to get the total value for quantity and total_value
leso_c_tx |>
summarize(
summed_quantity = sum(quantity),
summed_total_value = sum(total_value)
)# A tibble: 1 × 2
summed_quantity summed_total_value
<dbl> <dbl>
1 19578 125827483.Data takeaway: Texas has received nearly 20k non controlled items from the U.S. with a total value of over $125 million.
For each agency, how many things did they get and how much was it all worth?
In this code chunk we using group_by to isolate just the agency’s and we are going to get the quantity of items they received and how much it was all worth using the quantity function and total_value function.
tx_agency_totals <- leso_c_tx |>
group_by(agency_name) |>
summarize(summed_quantity = sum(quantity), summed_total_value = sum(total_value)) |> arrange(desc(summed_total_value))leso_c_tx |>
filter(agency_name == "ABERNATHY POLICE DEPT") |>
select(agency_name, quantity, total_value)# A tibble: 2 × 3
agency_name quantity total_value
<chr> <dbl> <dbl>
1 ABERNATHY POLICE DEPT 5 1855
2 ABERNATHY POLICE DEPT 1 62627Data takeaway: Houston is the highest on the list in terms of value and quantity of items received, which is not surprising as it is the largest city in Texas. San Marcos is a relatively small city with a population of 63k, but they are third on the list for value of items received, however they are not very high on the list for quantity of items received.
How about local police agencies? What was the total quantity and value?
In this code chunk we are looking at the quantity and value of items received by local agencies. We are using filter within a vector to do so and creating an object for just the local agencies.
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", #NI
"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)# A tibble: 17 × 3
agency_name summed_quantity summed_total_value
<chr> <dbl> <dbl>
1 SAN MARCOS POLICE DEPT 525 3238539.
2 AUSTIN POLICE DEPT 1399 2721969.
3 UNIV OF TEXAS SYSTEM POLICE HI_ED 3 1305000
4 LEANDER POLICE DEPT 212 1190263.
5 GEORGETOWN POLICE DEPT 41 1097977.
6 CALDWELL COUNTY SHERIFFS OFFICE 339 995833.
7 CEDAR PARK POLICE DEPT 106 971886.
8 BASTROP COUNTY SHERIFF'S OFFICE 266 719301.
9 HAYS COUNTY SHERIFFS OFFICE 384 456141.
10 WILLIAMSON COUNTY SHERIFF'S OFFICE 166 75326
11 LOCKHART POLICE DEPT 16 57259.
12 BEE CAVE POLICE DEPT 16 50360.
13 TRAVIS COUNTY SHERIFFS OFFICE 28 36302
14 HUTTO POLICE DEPT 90 13513.
15 BASTROP POLICE DEPT 10 4990
16 LULING POLICE DEPT 16 4700.
17 BUDA POLICE DEPT 16 1736.Data takeaway: San Marcos has received the highest value of items at $3.2 million while only receiving 525 items total. However Austin has received almost three times the amount as San Marcos but their items totaled at $2.7 million.
What specific “controlled” items did each agency get and how much were they worth?
In this code chunk we are looking at the types of controlled items shipped since 2010. We are using group_by and creating an object.
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(desc(summed_total_value))`summarise()` has grouped output by 'agency_name'. You can override using the
`.groups` argument.tx_agency_item_totals# A tibble: 1,339 × 4
# Groups: agency_name [306]
agency_name item_name summed_quantity summed_total_value
<chr> <chr> <dbl> <dbl>
1 HOUSTON POLICE DEPT AIRCRAFT… 1 5390000
2 DPS SWAT- TEXAS RANGERS MINE RES… 4 2611000
3 DEPT OF CRIM JUSTICE OIG TRUCK,CA… 4 1446516
4 UNIV OF TEXAS SYSTEM POLICE HI_… MINE RES… 2 1228000
5 JEFFERSON COUNTY SHERIFFS OFFICE HELICOPT… 1 922704
6 BURKBURNETT POLICE DEPT MINE RES… 1 865000
7 CLEBURNE POLICE DEPT MINE RES… 1 865000
8 CUERO POLICE DEPT MINE RES… 1 865000
9 HARRIS COUNTY CONSTABLE PCT 3 MINE RES… 1 865000
10 MARSHALL POLICE DEPT MINE RES… 1 865000
# ℹ 1,329 more rowsData takeaway: Houston has the highest value item which is an aircraft totaling at $5.4 million.
Items for local agencies
In this code chunk we are using the object we made in our last code chunk and filtering it by agency names in the local_agencies object.
tx_agency_item_totals |>
filter(agency_name %in% local_agencies) |>
arrange(agency_name, desc(summed_total_value))# A tibble: 149 × 4
# Groups: agency_name [17]
agency_name item_name summed_quantity summed_total_value
<chr> <chr> <dbl> <dbl>
1 AUSTIN POLICE DEPT HELICOPTER,FLIGHT TRAI… 1 833400
2 AUSTIN POLICE DEPT IMAGE INTENSIFIER,NIGH… 85 471053.
3 AUSTIN POLICE DEPT SIGHT,THERMAL 29 442310
4 AUSTIN POLICE DEPT PACKBOT 510 WITH FASTA… 4 308000
5 AUSTIN POLICE DEPT SIGHT,REFLEX 420 161177.
6 AUSTIN POLICE DEPT ILLUMINATOR,INTEGRATED… 135 141470
7 AUSTIN POLICE DEPT RECON SCOUT XT 8 92451.
8 AUSTIN POLICE DEPT TELESCOPE,STRAIGHT 40 57960
9 AUSTIN POLICE DEPT TEST SET,NIGHT VISION … 2 55610
10 AUSTIN POLICE DEPT POWER SUPPLY ASSEMBLY 63 21120.
# ℹ 139 more rowsleso_c_tx |>
filter(
item_name == "UNMANNED VEHICLE,GROUND",
agency_name == "SAN MARCOS POLICE DEPT"
) |>
select(item_name, nsn)# A tibble: 3 × 2
item_name nsn
<chr> <chr>
1 UNMANNED VEHICLE,GROUND 2350-01-575-0646
2 UNMANNED VEHICLE,GROUND 2350-01-575-0646
3 UNMANNED VEHICLE,GROUND 2360-01-663-1082Data takeaway: The San Marcos police department has an unmanned ground vehicle. The vehicle can be operated remotely or programmed to operate a certain way. It can be used for many things from combat to transportation and the department of origin is the U.S. Army.