In jacciz/shiny_wisdot_crash_dashboard: WisDOTcrashdashboard

This is an R Markdown Notebook. When you execute code within the notebook, the results appear beneath the code.

Try executing this chunk by clicking the Run button within the chunk or by placing your cursor inside it and pressing Ctrl+Shift+Enter.

pool <- pool::dbPool(RSQLite::SQLite(), dbname = "inst/app/www/crash_db.db")

crash_df %>% select(lng, lat, CRSHSVR) %>%  dplyr::filter(!is.na(lng), !is.na(lat))

# pool <- dbConnect(RSQLite::SQLite(), dbname = "data/vaccine_inventory.db")
crash_df <-DBI::dbReadTable(pool, "2017crash")
person_df <-DBI::dbReadTable(pool, "2017person")
# crash_df %>% mutate(CRSHDATE = lubridate::mdy(CRSHDATE))

library(magrittr)
wisinj_factor_levels <- c("Possible Injury", "Suspected Minor Injury", "Suspected Serious Injury", "Fatal Injury")

table_sum <- function(df_here) {
        df = df_here %>%
                dplyr::filter(WISINJ != "No Apparent Injury") %>% dplyr::mutate(CRSHDATE = lubridate::mdy(CRSHDATE))
        table(
                year = lubridate::year(df$CRSHDATE),
                inj = df$WISINJ
        ) %>%
                tibble::as_tibble()
}


table_count <- function(df_here) {
        df_here %>%
                dplyr::filter(WISINJ != "No Apparent Injury") %>% dplyr::mutate(CRSHDATE = lubridate::mdy(CRSHDATE)) %>% dplyr::count(year = lubridate::year(CRSHDATE),WISINJ)}
table_baset <- function(df_here) {
df_here["WISINJ" !=  "No Apparent Injury"] %>% dplyr::mutate(CRSHDATE = lubridate::mdy(CRSHDATE)) %>% dplyr::count(year = lubridate::year(CRSHDATE),WISINJ)}

microbenchmark::microbenchmark(
table_sum(df_here = person_df),
table_count(df_here = person_df),
table_baset(df_here = person_df)
)

wisinj_table = table_count(df_here = person_df)

test = function(t = "2018crash") {
  dt = dplyr::tbl(pool, t) |> dtplyr::lazy_dt()
  dt |> dplyr::filter(CNTYCODE %in% c(1, 2, 3, 40), CRSHSVR %in%
                        "Property Damage") |> dplyr::collect()
}

test2 <- function(t = "2018crash") {
  dplyr::tbl(pool, t) |> dplyr::filter(CNTYCODE %in% c(1,2,3, 40), CRSHSVR %in%
                                         "Property Damage") |> dplyr::collect()
}

microbenchmark::microbenchmark(
  test, 
  test2
)

df <- crash_df %>% filter(MUNICODE == 1163)
df <-
        df %>% mutate(
                newtime = factor(newtime, levels = newtime_factor_levels),
                DAYNMBR = factor(DAYNMBR, levels = day_factor_levels)
        )

day_time_data = df %>%  dplyr::count(newtime, DAYNMBR, .drop = FALSE)

# get blue color gradient
vals <- unique(scales::rescale(day_time_data$n))
o <- order(vals, decreasing = FALSE)
cols <- scales::col_numeric("Blues", domain = NULL)(vals)
colz <- stats::setNames(data.frame(vals[o], cols[o]), NULL)

day_time_data %>%
        plotly::plot_ly(
                x = ~ DAYNMBR,
                y =  ~ newtime,
                z = ~ n,
                # crash count
                colorscale = colz,
                type = "heatmap",
                # showscale = FALSE, # No legend
                hovertemplate = paste('%{x} %{y}<br>',
                                      '<b>%{z:.0f} Crashes')
        ) %>%
        layout(
                title = list(
                        title = "",
                        text = "Time of Day",
                        font = chart_title,
                        x = 0
                ),
                margin = list(r = 0, l = 0, b = 0),
                xaxis = list(
                        tickfont = chart_axis,
                        tickangle = 0,
                        tickcolor = "white"
                ),
                yaxis = list(
                        title = "",
                        tickfont = chart_axis,
                        tickcolor = "white"
                ),
                plot_bgcolor = 'rgba(0,0,0,0)',
                # make transparent background
                paper_bgcolor = 'rgba(0,0,0,0)'
        ) %>%
        config(
                toImageButtonOptions = list(
                        width = 800,
                        height = 800,
                        filename = "Time of Day Crashes",
                        scale = 2
                )
        )

        # get blue color gradient
        vals <- unique(scales::rescale(day_time_data$n))
        o <- order(vals, decreasing = FALSE)
        cols <- scales::col_numeric("Blues", domain = NULL)(vals)
        colz <- stats::setNames(data.frame(vals[o], cols[o]), NULL)

day_time_data %>% 
plotly::plot_ly(
          x = ~ DAYNMBR,
          y =~ newtime,
          z = ~n, # crash count
          colorscale = colz,
          type = "heatmap")
day_time_data

crash = crash_df %>%
        filter(MUNICODE == 1163) %>% 
        dplyr::mutate(svr = factor(CRSHSVR, levels = crshsvr_factor_levels),
                      CRSHMTH = factor(CRSHMTH, levels = month.name))

crash$CRSHMTH <- month.abb[crash$CRSHMTH]
crshsvr_table <- 
crash %>%
        dplyr::mutate(CRSHMTH = factor(CRSHMTH, levels = base::month.abb)) %>%
        dplyr::count(month = CRSHMTH, svr = svr, .drop = FALSE)

microbenchmark::microbenchmark(
# DBI::dbReadTable(pool, "2017crash") %>% filter(CNTYCODE %in% 11, CRSHSVR %in% "Property Damage"), # 450
pool::dbReadTable(pool, "2017crash") %>% filter(CNTYCODE %in% 11, CRSHSVR %in% "Property Damage"), # 448
dplyr::tbl(pool, "2017crash") %>% filter(CNTYCODE %in% 11, CRSHSVR %in% "Property Damage") %>% as.data.frame() # 7 #with df 48
)