data-raw/2_data_import.R

In jacciz/shiny_wisdot_crash_dashboard: WisDOTcrashdashboard

library(data.table)
library(dplyr)
library(lubridate) ### MAY have to change date to mdy, ugh formatting
# library(memisc)
library(wisdotcrashdatabase)
# library(sjmisc)

year = "17"
# TODO now has year, countyname, veh. Redo do SQLITE?
# This script basically readies the data for the dashboard and is exported into teh SQLite database.

# This script imports data from a CSV, selects certain columns, add new columns (such as newtime and age group)


# file_loc = "Data Prep for R Shiny/"
# file = "W:/HSSA/Keep/Jaclyn Ziebert/R/Data Prep for R Shiny/"
db_dir = "C:/data/crashes_duck.duckdb" # this is where the raw CSVs are and where data will be saved

import_all_crashes <- function() {
  all_crashes <-
    import_db_data(
      db_dir,
      db_type = "crash",
      years = year,
      columns = c("INJSVR", "CRSHTIME", "CRSHMTH", "TOTINJ", "TOTFATL", "TOTUNIT", "DAYNMBR", "CNTYCODE", "MUNICODE", "URBRURAL", "MNRCOLL", "LATDECDG", "LONDECDG", "RLTNTRWY")
    )
  all_crashes = all_crashes |> dplyr::mutate(CRSHDATE = as.character(CRSHDATE)) |> get_crash_times()
 
  setnames(all_crashes, "LONDECDG", "lng") # rename so leaflet grabs correct columns
  setnames(all_crashes, "LATDECDG", "lat")
  all_crashes
 # saveRDS(all_crashes, file = paste0(file_loc, csv_name, ".rds"), compress = FALSE)
 # write_fst(all_crashes, path = paste0(file_loc, csv_name, ".fst"), compress = 0)
}

import_all_persons <- function() {
  all_persons <-
    import_db_data(
      db_dir,
      db_type = "person",
      years = year,
      columns = c( 
                   "INJSVR",
                   "CNTYCODE",
                   "MUNICODE",
                   "WISINJ",
                   "DRVRFLAG",
                   "ROLE",
                   "SEX",
                   "AGE",
                   "HLMTUSE",
                   "NMTACT",
                   "NMTLOC",
                   "DRVRPC")
    )

  all_persons <-
    all_persons |> dplyr::mutate(CRSHDATE = as.character(CRSHDATE)) |> get_age_groups()

  all_persons <- all_persons |> mutate(  # relabel ROLE
    ROLE = case_when(
      UNITTYPE == "Pedestrian" ~ "Pedestrian",
      UNITTYPE == "Bicycle" ~ "Bicycle",
      DRVRFLAG == "Y" ~ "Driver",
      ROLE == "Passenger" ~ "Passenger",
      TRUE ~ "Other"
    )
  )
  all_persons <- all_persons |> mutate(SEX = case_when(SEX == "F" ~ "Female", # relabel SEX
                                                        SEX == "M" ~ "Male",
                                                        SEX == "U" ~ "Unknown")) |> 
    dplyr::mutate_at(dplyr::vars(dplyr::starts_with(c("DRVRPC","NMTACT"))), as.character)
}

import_all_vehicles <- function() {
  all_vehicles <-
    import_db_data(
      db_dir,
      db_type = "vehicle",
      years = year,
      columns = c("INJSVR", "MUNICODE", "VEHTYPE")
    )
  all_vehicles <-
    all_vehicles %>% mutate(
      CRSHDATE = as.character(CRSHDATE)
      # vehcate = case_when(
      #   VEHTYPE == "Passenger Car" ~ "Passenger Veh.",
      #   VEHTYPE == "(Sport) Utility Vehicle" ~ "Passenger Veh.",
      #   VEHTYPE == "Cargo Van (10,000 Lbs or Less)" ~ "Passenger Veh.",
      #   VEHTYPE == "Passenger Van" ~ "Passenger Veh.",
      #   VEHTYPE == "Utility Truck/Pickup Truck" ~ "Light Trucks",
      #   VEHTYPE == "Straight Truck" ~ "Large Trucks",
      #   VEHTYPE == "Truck Tractor (Trailer Not Attached)" ~ "Large Trucks",
      #   VEHTYPE == "Truck Tractor (Trailer Attached)" ~ "Large Trucks",
      #   VEHTYPE == "Truck Tractor (More Than One Trailer)" ~ "Large Trucks",
      #   VEHTYPE == VEHTYPE ~ "Other"
      ) |> relabel_vehicle_variables()
    # convert to date type
  # saveRDS(all_vehicles, file = paste0(file_loc, csv_name, ".rds"), compress = FALSE)
  # write_fst(all_vehicles, path = paste0(file_loc, csv_name, ".fst"), compress = 0)
}

# input is name of csv, just change year
all_crashes <- import_all_crashes()
# Note: Creates a newtime field. time of 0 and 999 will be NA
#
all_persons <- import_all_persons()
# Note: Creates a age_group field, relabels ROLE, SEX

all_vehicles <- import_all_vehicles()

# To import county and muni recode to get names
# county_recode <- fread("Data Prep for R Shiny/county_recode.csv")
# muni_recode <- fread("Data Prep for R Shiny/muni_recode.csv")
#
# saveRDS(county_recode, file = "Shiny_Crashes_Dashboard/data/county_recode.rds")
# saveRDS(muni_recode, file = "Shiny_Crashes_Dashboard/data/muni_recode.rds")


#### SAVE TO SQLITE
fname = paste0("20", year)
pool <- pool::dbPool(RSQLite::SQLite(), dbname = "inst/app/www/crash_db.db")

DBI::dbWriteTable(pool, paste0("crash", fname), all_crashes, overwrite = TRUE)
DBI::dbWriteTable(pool, paste0("person", fname), all_persons, overwrite = TRUE)
DBI::dbWriteTable(pool, paste0("vehicle", fname), all_vehicles, overwrite = TRUE)
# DBI::dbDisconnect(pool)
# make CRSHNMBR primary key