drivers-1

knitr::opts_chunk$set(echo = TRUE)

rm(list = ls(all.names = TRUE))

library(readxl)
library(janitor)
library(dplyr)
library(tidyr)
library(lubridate)
library(forcats)
library(ggplot2)

# all counties
master_all <- readxl::read_excel("master_data.xlsx", sheet = "MASTER")

# view master columns
names(master_all)

 [1] "Region"        "Week"          "Report_Date"   "Data_Date"     "County"       
 [6] "State"         "Pop"           "FIPS"          "Census_4"      "Urban_3"      
[11] "Urban_6"       "New_Status"    "Tribal"        "Driver_1"      "Driver_2"     
[16] "Driver_3"      "Driver_4"      "Driver_5"      "Drivers"       "Demographics" 
[21] "Cases"         "Testing"       "Location"      "Prev_Report"   "Narrative"    
[26] "Outreach_Date" "Outreach_Team" "Outreach_Type"

# 7,794 rows
master_all

table(master_all$Driver_1, useNA = "always")

   C    D    F    L    M    P    S    T   TR    U   UU    W <NA> 
3584   60  253  512   83  334    5  363    9 2256  218  117    0 

table(master_all$Driver_2, useNA = "always")

   C    D    F    L    M    P    S    T   TR    U    W <NA> 
 506  153  188  748  112  257   17  479   40   83  279 4932 

table(master_all$Driver_3, useNA = "always")

   C    D    F    L    M    P    S    T   TR    U    W <NA> 
  70   14   45  171   39   89    7   36   22   20   60 7221 

table(master_all$Driver_4, useNA = "always")

   C    D    F    L    M    P    S    T    W <NA> 
  18    1    5   51   12    9   10   11    5 7672 

table(master_all$Driver_5, useNA = "always")

<NA> 
7794 

# 882 = total counties
summarise(master_all, n_distinct(FIPS))

# 735 = new counties
filter(master_all, New_Status == 1) %>%
  summarise(n_distinct(FIPS))

# set dataframe to analyze drivers
master <- master_all

# subset long
long <- dplyr::select(master, c(Region:Driver_5)) %>%
  tidyr::pivot_longer(Driver_1:Driver_5) %>%  # group driver cols together
  #tidyr::drop_na() %>%
  dplyr::mutate(Month = lubridate::month(Data_Date)) %>%
  dplyr::mutate(Week = lubridate::epiweek(Data_Date)) # add in month

# 38,970 rows
long

# 882 unique counties with drivers
summarise(long, n_distinct(FIPS))

# count of each driver by FIPS
drivers_fips <- long %>%
  dplyr::group_by(FIPS, Region, State, County, Tribal, Census_4, Urban_3, Urban_6) %>%
  dplyr::count(value) %>%
  tidyr::drop_na() %>%
  tidyr::pivot_wider(names_from = value, values_from = n)

# 882 rows
drivers_fips

# 882 unique counties
summarise(long, n_distinct(FIPS))

# count number of days per county
ndays_fips <- long %>%
  dplyr::group_by(FIPS, Region, State, County, Tribal, Census_4, Urban_3, Urban_6) %>%
  dplyr::summarise(ndays = n_distinct(Data_Date)) %>%
  arrange(desc(ndays))  # sort by ndays

`summarise()` regrouping output by 'FIPS', 'Region', 'State', 'County', 'Tribal', 'Census_4', 'Urban_3' (override with `.groups` argument)

# 882 rows
ndays_fips

# join tables
ndays_drivers_fips <- dplyr::full_join(drivers_fips, ndays_fips,
                                           by = c("FIPS", "Region", "State", "County", "Tribal",
                                                  "Census_4", "Urban_3", "Urban_6")) %>%
  dplyr::select(Census_4, Urban_3, Urban_6, FIPS, ndays, everything()) # reorder columns

# 822 rows
ndays_drivers_fips

#write.csv(ndays_drivers_fips, "by_county.csv")

# create table of drivers with time periods
week_drivers_regions <- long %>%
  dplyr::group_by(Week, Month, FIPS, Region, State, County, Tribal, Census_4, Urban_3, Urban_6) %>%
  dplyr::count(value) %>%
  tidyr::drop_na() %>%
  tidyr::pivot_wider(names_from = value, values_from = n)

# 2,912 rows
week_drivers_regions

#write.csv(week_drivers_regions, "by_week.csv")

## plot ndays histogram

# number of unique counties by ndays
ndays_drivers_fips  # 822 rows

p_hist <- ggplot(ndays_drivers_fips) +
  geom_bar(mapping = aes(x = ndays))
p_hist

# facet ndays histogram by region
p_hist_region <- p_hist + facet_wrap(~Region)
p_hist_region

# more histograms
p_hist + facet_wrap(~Census_4)

p_hist + facet_wrap(~Urban_3)

p_hist + facet_wrap(~Urban_6)

p_hist + facet_grid(Census_4 ~ Urban_3)

p_hist + facet_grid(Census_4 ~ Urban_6)