pivot

knitr::opts_chunk$set(echo = TRUE)

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

library(readxl)
library(dplyr)
library(tidyr)
library(ggplot2)

by_county <- readxl::read_excel("data_prep.xlsx", sheet = "by_county")

# 882 rows
by_county

# create pivot table
county_pivot <- pivot_wider(by_county,
            id_cols = ndays,
            values_from = ends_with("_p"),
            values_fn = sum) %>%
  dplyr::arrange(ndays) %>%
  #dplyr::select(sort(current_vars())) %>%
  dplyr::select(sort(tidyselect::peek_vars())) %>%
  dplyr::select(ndays, everything())

# 34 rows
county_pivot

# add row_sum column
ndays_count <- county_pivot %>%
  mutate(row_sum = rowSums(select(., ends_with("p_"))))

# 34 rows
ndays_count %>%
  select(ndays, 2, last_col())

# sum of row_sum = 882
sum(ndays_count$row_sum)

[1] 882

ndays_prop <- ndays_count %>%
  apply(MARGIN = 1, function(x){x / x['row_sum']}) %>%
  t() %>%
  as.data.frame() %>%
  dplyr::mutate(ndays = ndays_count$ndays)

# 34 rows
ndays_prop

ndays_prop_long <- ndays_prop %>%
  tidyr::pivot_longer(ends_with("p_"))

ggplot(ndays_prop_long,
       aes(x = as.factor(ndays),
           y = value,
           fill = name)) +
  geom_bar(stat="identity")

ndays_count_long <- ndays_count %>%
  tidyr::pivot_longer(ends_with("p_"))

# plot county-drivers by ndays
ggplot(ndays_count_long,
       aes(x = as.factor(ndays),
           y = value,
           fill = name)) +
  geom_bar(stat="identity")

by_week <- readxl::read_excel("data_prep.xlsx", sheet = "by_week")

week_pivot <- pivot_wider(by_week,
                          id_cols = c(Census_4, Week),
                          values_from = ends_with("_p"),
                          values_fn = sum) %>%
  dplyr::arrange(Census_4, Week) %>%
  dplyr::select(sort(current_vars())) %>%
  dplyr::select(Week, Census_4, everything())

# 48 rows
week_pivot

week_count <- week_pivot %>%
  mutate(row_sum = rowSums(select(., ends_with("p_"))))

week_count %>% select(Week, Census_4, 2, last_col())

sum(week_count$row_sum)

[1] 2912

week_prop <- week_count %>%
  select(-Census_4) %>%
  apply(., MARGIN = 1, function(x){x / x['row_sum']}) %>%
  t() %>%
  as.data.frame() %>%
  dplyr::mutate(
    Census_4 = week_count$Census_4,
    Week = week_count$Week
  ) %>%
  dplyr::select(Week, Census_4, everything() )

week_prop

week_prop_long <- week_prop %>%
  tidyr::pivot_longer(ends_with("p_"))


ggplot(week_prop_long, aes(x = as.factor(Week),
                           y = value,
                           fill = name)) +
  geom_bar(stat="identity") +
  facet_wrap(~ Census_4)

week_count_long <- week_count %>%
  tidyr::pivot_longer(ends_with("p_"))

ggplot(week_count_long, aes(x = as.factor(Week),
                            y = value,
                            fill = name)) +
  geom_bar(stat="identity") +
  facet_wrap(~ Census_4)