7-Day Moving Average of Cases, Deaths, and CFR [Santa Clara County, CA]

chart_santa-clara-ca_covid19_cases_deaths_cfr.r

library(readr)
library(ggplot2)
library(slider)
library(scales)
library(dplyr)

sf <- 2
width <- 600 * sf
height <- 335 * sf

options(vsc.dev.args = list(width = width, height = height, res = 72 * sf))

# COVID-19
deaths <- read_csv("https://data.sccgov.org/resource/tg4j-23y2.csv") |>
  select(date, ltcf) |>
  setNames(c("date", "deaths"))
cases <- read_csv("https://data.sccgov.org/resource/ksyb-fnbm.csv") |>
  select(dtepisode, new_cases) |>
  setNames(c("date", "cases"))
cases$date <- as.Date(cases$date)

# Calculate 7-day moving averages and plot with dual y-axes
chart <- deaths |>
  inner_join(cases, by = join_by(date)) |>
  mutate(
    cases = as.numeric(cases),
    deaths = as.numeric(deaths),
    # Calculate 7-day moving averages
    cases_7d_avg = slide_mean(cases, before = 6, complete = TRUE),
    deaths_7d_avg = slide_mean(deaths, before = 6, complete = TRUE),
    # Calculate CFR and scale it by 100 for plotting
    cfr_7d = (deaths_7d_avg / cases_7d_avg) * 100
  ) |>
  ggplot(aes(x = date)) +
  # Plot cases, deaths, and CFR with different colors
  geom_line(aes(y = cases_7d_avg, color = "Cases (7-day avg)")) +
  geom_line(aes(y = deaths_7d_avg, color = "Deaths (7-day avg)")) +
  geom_line(aes(y = cfr_7d, color = "CFR (7-day avg)")) +
  # Dual y-axis setup
  scale_y_continuous(
    name = "Cases / Deaths",
    sec.axis = sec_axis(~.,
      name = "CFR (%)",
      labels = percent_format(scale = 1)
    )
  ) +
  labs(
    title =
      "7-Day Moving Average of Cases, Deaths, and CFR [Santa Clara County, CA]",
    x = "Date",
    color = "Metric"
  ) +
  # Add a vertical line for Dec 17, 2020
  geom_vline(
    xintercept = as.Date("2020-12-17"),
    linetype = "dashed", color = "black", size = 0.5
  ) +
  # Add annotation text for the vertical line
  annotate(
    "text",
    x = as.Date("2020-12-17"), y = Inf, label = "First Vaccine\nGiven",
    vjust = 1.5, angle = 0, size = 3.5, hjust = 1.1
  ) +
  # Custom color mapping for each metric
  scale_color_manual(
    values = c(
      "Cases (7-day avg)" = "green",
      "Deaths (7-day avg)" = "red",
      "CFR (7-day avg)" = "blue"
    )
  ) +
  theme_minimal() +
  theme(
    axis.title.y.right = element_text(color = "black"),
    legend.position = "bottom", # Move legend to bottom
    legend.title = element_blank(), # Remove legend title
    legend.direction = "horizontal" # Arrange legend items horizontally
  )
ggplot2::ggsave(
  filename = "chart1.png", plot = chart, width = width, height = height,
  units = "px", dpi = 72 * sf, device = grDevices::png, type = c("cairo")
)


# Aligned Peaks

# Calculate 7-day moving averages and peak offsets
data_shifted <- deaths |>
  inner_join(cases, by = join_by(date)) |>
  mutate(
    cases = as.numeric(cases),
    deaths = as.numeric(deaths),
    # Calculate 7-day moving averages
    cases_7d_avg = slide_mean(cases, before = 6, complete = TRUE),
    deaths_7d_avg = slide_mean(deaths, before = 6, complete = TRUE)
  ) |>
  # Calculate peak dates and offset
  summarize(
    peak_cases_date = date[which.max(cases_7d_avg)],
    peak_deaths_date = date[which.max(deaths_7d_avg)],
    days_offset = as.integer(peak_deaths_date - peak_cases_date)
  ) |>
  pull(days_offset) -> offset_days

# Shift cases using the calculated offset
data_shifted <- deaths |>
  inner_join(cases, by = join_by(date)) |>
  mutate(
    cases = as.numeric(cases),
    deaths = as.numeric(deaths),
    # Calculate 7-day moving averages
    cases_7d_avg = slide_mean(cases, before = 6, complete = TRUE),
    deaths_7d_avg = slide_mean(deaths, before = 6, complete = TRUE),
    # Shift the cases by the calculated offset
    cases_7d_avg_shifted = lag(cases_7d_avg, offset_days)
  )

# Plot with shifted cases
chart <- data_shifted |>
  ggplot(aes(x = date)) +
  # Plot shifted cases and deaths on the left y-axis
  geom_line(aes(
    y = cases_7d_avg,
    color = "Cases (7-day avg)"
  )) +
  geom_line(aes(y = deaths_7d_avg, color = "Deaths (7-day avg)")) +
  # Plot CFR on the right y-axis
  geom_line(aes(
    y = (deaths_7d_avg / cases_7d_avg_shifted) * 100,
    color = "CFR (7-day avg)"
  )) +
  # Dual y-axis setup
  scale_y_continuous(
    name = "Cases / Deaths",
    sec.axis =
      sec_axis(~., name = "CFR (%)", labels = percent_format(scale = 1))
  ) +
  labs(
    title =
      "7-Day Moving Average of Cases, Deaths, and CFR [Santa Clara County, CA]",
    x = "Date",
    color = "Metric",
    caption = paste0(
      "For the CFR calculation cases were shifted ", offset_days,
      "d forward."
    )
  ) +
  # Add a vertical line for Dec 17, 2020
  geom_vline(
    xintercept = as.Date("2020-12-17"),
    linetype = "dashed", color = "black", size = 0.5
  ) +
  # Add annotation text for the vertical line
  annotate(
    "text",
    x = as.Date("2020-12-17"), y = Inf, label = "First Vaccine\nGiven",
    vjust = 1.5, angle = 0, size = 3.5, hjust = 1.1
  ) +
  # Custom color mapping for each metric
  scale_color_manual(
    values = c(
      "Cases (7-day avg)" = "green",
      "Deaths (7-day avg)" = "red",
      "CFR (7-day avg)" = "blue"
    )
  ) +
  theme_minimal() +
  theme(
    axis.title.y.right = element_text(color = "black"),
    legend.position = "bottom", # Move legend to bottom
    legend.title = element_blank(), # Remove legend title
    legend.direction = "horizontal" # Arrange legend items horizontally
  )
ggplot2::ggsave(
  filename = "chart2.png", plot = chart, width = width, height = height,
  units = "px", dpi = 72 * sf, device = grDevices::png, type = c("cairo")
)

https://www.mortality.watch/charts/list.html#7-day-moving-average-of-cases-deaths-and-cfr-santa-clara-county-ca