LPD Traffic Stop Data

library(readxl)

#verify that file exists
path <- "data/lpd-stops.xlsx"
file.exists(path)  # This should return TRUE if the file exists

[1] TRUE

# if it exists, read the sheet names
sheet_names <- excel_sheets(path)
print(sheet_names)

 [1] "2023" "2022" "2021" "2020" "2019" "2018" "2017" "2016" "2015" "2014"

# if that also works, try reading sheet names one at a time
for (sheet in sheet_names) {
  tryCatch({
    data <- read_excel(path, sheet = sheet)
    print(paste("Successfully read sheet:", sheet))
    print(str(data))  # This will print the structure of the data
  }, error = function(e) {
    print(paste("Error reading sheet:", sheet))
    print(e)
  })
}

[1] "Successfully read sheet: 2023"
tibble [36,631 × 8] (S3: tbl_df/tbl/data.frame)
 $ DATE   : POSIXct[1:36631], format: "2023-01-01" "2023-01-01" ...
 $ TIME   : POSIXct[1:36631], format: "1899-12-31 00:13:00" "1899-12-31 00:17:00" ...
 $ RACE   : num [1:36631] 1 1 1 1 1 1 4 1 2 2 ...
 $ SEX    : num [1:36631] 1 1 2 1 1 1 1 1 1 1 ...
 $ REASON : num [1:36631] 1 1 1 1 1 1 1 1 1 1 ...
 $ OUTCOME: num [1:36631] 1 1 1 2 1 1 3 5 2 2 ...
 $ SEARCH : num [1:36631] 1 1 1 1 1 1 1 1 1 5 ...
 $ FID    : num [1:36631] 1 2 3 4 5 6 7 8 9 10 ...
NULL
[1] "Successfully read sheet: 2022"
tibble [25,015 × 8] (S3: tbl_df/tbl/data.frame)
 $ DATE   : POSIXct[1:25015], format: "2022-01-01" "2022-01-01" ...
 $ TIME   : POSIXct[1:25015], format: "1899-12-31 00:14:00" "1899-12-31 00:29:00" ...
 $ RACE   : num [1:25015] 1 2 1 3 1 4 2 1 1 1 ...
 $ SEX    : num [1:25015] 1 1 1 1 1 2 2 1 1 1 ...
 $ REASON : num [1:25015] 1 2 1 1 1 1 1 1 1 1 ...
 $ OUTCOME: logi [1:25015] NA NA NA NA NA NA ...
 $ SEARCH : num [1:25015] 1 5 1 1 1 1 1 1 1 1 ...
 $ FID    : num [1:25015] 1 2 3 4 5 6 7 8 9 10 ...
NULL
[1] "Successfully read sheet: 2021"
tibble [28,064 × 8] (S3: tbl_df/tbl/data.frame)
 $ DATE   : POSIXct[1:28064], format: "2021-01-04" "2021-01-01" ...
 $ TIME   : POSIXct[1:28064], format: "1899-12-31 01:11:00" "1899-12-31 00:00:00" ...
 $ RACE   : num [1:28064] 1 4 1 1 1 1 2 1 1 2 ...
 $ SEX    : num [1:28064] 1 1 1 2 1 2 1 2 1 1 ...
 $ REASON : num [1:28064] 1 1 1 1 1 1 1 1 1 1 ...
 $ OUTCOME: num [1:28064] 2 1 1 2 1 2 2 2 1 1 ...
 $ SEARCH : num [1:28064] 1 1 1 5 1 1 1 1 1 1 ...
 $ FID    : num [1:28064] 29607 29616 29665 29668 29669 ...
NULL
[1] "Successfully read sheet: 2020"
tibble [29,754 × 8] (S3: tbl_df/tbl/data.frame)
 $ DATE   : POSIXct[1:29754], format: "2020-01-01" "2020-01-01" ...
 $ TIME   : POSIXct[1:29754], format: "1899-12-31 00:21:00" "1899-12-31 00:33:00" ...
 $ RACE   : num [1:29754] 1 1 1 1 5 2 1 1 1 6 ...
 $ SEX    : num [1:29754] 2 2 1 1 2 1 2 2 1 1 ...
 $ REASON : num [1:29754] 1 1 1 1 1 1 1 1 1 1 ...
 $ OUTCOME: num [1:29754] 1 1 1 5 2 1 1 3 3 1 ...
 $ SEARCH : num [1:29754] 1 1 1 1 1 1 1 5 3 1 ...
 $ FID    : num [1:29754] 1 2 3 4 5 6 7 8 9 10 ...
NULL
[1] "Successfully read sheet: 2019"
tibble [43,311 × 8] (S3: tbl_df/tbl/data.frame)
 $ DATE   : POSIXct[1:43311], format: "2019-01-03" "2019-01-03" ...
 $ TIME   : POSIXct[1:43311], format: "1899-12-31 18:37:00" "1899-12-31 18:40:00" ...
 $ RACE   : num [1:43311] 1 1 2 1 3 4 1 1 2 1 ...
 $ SEX    : num [1:43311] 1 1 1 1 1 2 2 2 1 1 ...
 $ REASON : num [1:43311] 1 1 1 1 1 1 1 1 1 1 ...
 $ OUTCOME: num [1:43311] 1 1 1 1 2 1 2 1 1 1 ...
 $ SEARCH : num [1:43311] 1 1 4 1 1 1 1 1 1 1 ...
 $ FID    : num [1:43311] 1 2 3 4 5 6 7 8 9 10 ...
NULL
[1] "Successfully read sheet: 2018"
tibble [50,771 × 8] (S3: tbl_df/tbl/data.frame)
 $ DATE   : POSIXct[1:50771], format: "2018-01-01" "2018-01-01" ...
 $ TIME   : POSIXct[1:50771], format: "1899-12-31 00:15:00" "1899-12-31 00:20:00" ...
 $ RACE   : num [1:50771] 2 1 1 1 6 1 2 6 1 1 ...
 $ SEX    : num [1:50771] 1 1 1 1 1 2 1 1 2 1 ...
 $ REASON : num [1:50771] 1 1 1 1 1 1 1 1 1 1 ...
 $ OUTCOME: num [1:50771] 4 1 5 5 5 5 5 5 1 3 ...
 $ SEARCH : num [1:50771] 5 1 1 1 1 1 1 1 1 5 ...
 $ FID    : num [1:50771] 1 2 3 4 5 6 7 8 9 10 ...
NULL
[1] "Successfully read sheet: 2017"
tibble [48,340 × 8] (S3: tbl_df/tbl/data.frame)
 $ DATE   : POSIXct[1:48340], format: "2017-01-01" "2017-01-01" ...
 $ TIME   : POSIXct[1:48340], format: "1899-12-31 00:00:00" "1899-12-31 00:14:00" ...
 $ RACE   : num [1:48340] 1 1 1 3 1 3 1 1 1 3 ...
 $ SEX    : num [1:48340] 1 1 2 2 2 1 1 1 1 1 ...
 $ REASON : num [1:48340] 1 1 1 1 1 1 1 1 1 1 ...
 $ OUTCOME: num [1:48340] 1 2 1 2 1 5 2 1 2 2 ...
 $ SEARCH : num [1:48340] 1 1 1 1 1 1 1 1 1 1 ...
 $ FID    : num [1:48340] 1 2 3 4 5 6 7 8 9 10 ...
NULL
[1] "Successfully read sheet: 2016"
tibble [47,073 × 8] (S3: tbl_df/tbl/data.frame)
 $ DATE   : POSIXct[1:47073], format: "2016-01-04" "2016-01-04" ...
 $ TIME   : POSIXct[1:47073], format: "1899-12-31 01:00:00" "1899-12-31 01:00:00" ...
 $ RACE   : num [1:47073] 1 2 1 1 2 1 1 1 2 1 ...
 $ SEX    : num [1:47073] 2 1 1 2 1 2 1 1 1 1 ...
 $ REASON : num [1:47073] 1 1 1 1 1 1 1 1 1 1 ...
 $ OUTCOME: num [1:47073] 1 1 1 3 5 1 1 2 4 3 ...
 $ SEARCH : num [1:47073] 1 1 1 1 1 1 1 1 1 5 ...
 $ FID    : num [1:47073] 1 2 3 4 5 6 7 8 9 10 ...
NULL
[1] "Successfully read sheet: 2015"
tibble [45,073 × 8] (S3: tbl_df/tbl/data.frame)
 $ DATE   : POSIXct[1:45073], format: "2015-08-18" "2015-08-22" ...
 $ TIME   : POSIXct[1:45073], format: "1899-12-31 23:55:00" "1899-12-31 23:55:00" ...
 $ RACE   : num [1:45073] 1 1 1 1 1 3 1 1 1 1 ...
 $ SEX    : num [1:45073] 1 1 2 2 1 1 2 1 1 2 ...
 $ REASON : num [1:45073] 1 1 1 2 1 1 1 1 1 1 ...
 $ OUTCOME: num [1:45073] 1 1 1 5 3 1 2 1 2 3 ...
 $ SEARCH : num [1:45073] 1 1 1 1 1 1 1 1 1 1 ...
 $ FID    : num [1:45073] 1 2 3 4 5 6 7 8 9 10 ...
NULL
[1] "Successfully read sheet: 2014"
tibble [54,256 × 8] (S3: tbl_df/tbl/data.frame)
 $ DATE   : POSIXct[1:54256], format: "2014-01-02" "2014-01-02" ...
 $ TIME   : POSIXct[1:54256], format: "1899-12-31 16:32:00" "1899-12-31 16:45:00" ...
 $ RACE   : num [1:54256] 1 1 1 1 1 2 1 1 1 4 ...
 $ SEX    : num [1:54256] 1 1 2 1 1 1 1 1 2 1 ...
 $ REASON : num [1:54256] 1 1 1 1 1 1 1 1 1 1 ...
 $ OUTCOME: num [1:54256] 2 2 2 5 5 3 2 2 2 1 ...
 $ SEARCH : num [1:54256] 1 1 1 1 1 5 1 1 1 1 ...
 $ FID    : num [1:54256] 1 2 3 4 5 6 7 8 9 10 ...
NULL

library(tidyverse)

── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
✔ dplyr     1.1.4     ✔ readr     2.1.5
✔ forcats   1.0.0     ✔ stringr   1.5.2
✔ ggplot2   4.0.0     ✔ tibble    3.3.0
✔ lubridate 1.9.4     ✔ tidyr     1.3.1
✔ purrr     1.1.0     
── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
✖ dplyr::filter() masks stats::filter()
✖ dplyr::lag()    masks stats::lag()
ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors

library(readxl)

## credit to 
## set file-path

path <- "data/lpd-stops.xlsx"
# Import all each sheet, assign sheet name to each row, and bind the sheets into one data frame

data_raw <- path |> 
  excel_sheets() |> 
  set_names() |> 
  map(read_excel, path = path) |> 
  list_rbind(names_to = "file_name")

Convert Dates and Times to usable formats

library(dplyr)
library(lubridate)

data <- data_raw |>
  mutate(
    # Month as character
    Month = month(DATE, label = TRUE, abbr = FALSE),
    
    # Date in yyyy-mm-dd format
    Date_YMD = as.Date(DATE),
    
    # Time of day category
    Time_Category = case_when(
      hour(TIME) >= 5 & hour(TIME) < 12 ~ "Early-Morning",
      hour(TIME) >= 12 & hour(TIME) < 17 ~ "Mid-Day",
      hour(TIME) >= 17 & hour(TIME) < 21 ~ "Evening",
      TRUE ~ "Night"
    ),
    
    # AM/PM indicator
    AM_PM = if_else(hour(TIME) < 12, "AM", "PM"),
    
    # Time in hr:mm AM/PM format
    Time_HHMM = format(TIME, "%I:%M %p")
  )

glimpse(data)

Rows: 408,288
Columns: 14
$ file_name     <chr> "2023", "2023", "2023", "2023", "2023", "2023", "2023", …
$ DATE          <dttm> 2023-01-01, 2023-01-01, 2023-01-01, 2023-01-01, 2023-01…
$ TIME          <dttm> 1899-12-31 00:13:00, 1899-12-31 00:17:00, 1899-12-31 00…
$ RACE          <dbl> 1, 1, 1, 1, 1, 1, 4, 1, 2, 2, 1, 1, 1, 2, 1, 2, 1, 2, 3,…
$ SEX           <dbl> 1, 1, 2, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2, 1, 1, 2, 2, 1,…
$ REASON        <dbl> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,…
$ OUTCOME       <dbl> 1, 1, 1, 2, 1, 1, 3, 5, 2, 2, 2, 2, 4, 2, 2, 2, 2, 1, 2,…
$ SEARCH        <dbl> 1, 1, 1, 1, 1, 1, 1, 1, 1, 5, 1, 1, 5, 1, 1, 1, 2, 1, 1,…
$ FID           <dbl> 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 1…
$ Month         <ord> January, January, January, January, January, January, Ja…
$ Date_YMD      <date> 2023-01-01, 2023-01-01, 2023-01-01, 2023-01-01, 2023-01…
$ Time_Category <chr> "Night", "Night", "Night", "Night", "Night", "Night", "N…
$ AM_PM         <chr> "AM", "AM", "AM", "AM", "AM", "AM", "AM", "AM", "AM", "A…
$ Time_HHMM     <chr> "12:13 AM", "12:17 AM", "12:20 AM", "12:47 AM", "12:47 A…

Create data set with only 2023 data for workshop

```{r}
library(haven)

data2023 <- data |> 
  filter(file_name == "2023")

write_sav(data2023, "data/lpd_2023.sav")
```