加拿大新冠每周累计死亡率转月度非累计方法正确性咨询

我拥有加拿大新冠疫情每周累计死亡率数据集（该死亡率为普通人群死亡率，而非感染者死亡率），希望将其转换为月度非累计数据，以下是我的处理过程：

初始数据下载与可视化 #

使用R语言下载数据并绘制不同省份的每周累计死亡率折线图：

temp_file <- tempfile()
download.file(
    url = "https://health-infobase.canada.ca/src/data/covidLive/covid19-download.csv",
    destfile = temp_file
)

covid_data <- read.csv(temp_file)

library(ggplot2)
library(dplyr)
library(scales) 

ggplot(covid_data, aes(x = as.Date(date), y = ratedeaths, color = prname)) +
    geom_line() +
    labs(
        x = "Date",
        y = "Death Rate",
        color = "Province"
    ) +
    scale_x_date(date_breaks = "6 months", date_labels = "%b %Y") + theme_bw() + ggtitle("Original Data: Cumulative Weekly")

转换为非累计周度数据 #

将累计数据转换为非累计周度数据并可视化：

library(ggplot2)
library(dplyr)
library(scales)

covid_data <- covid_data %>%
    mutate(date = as.Date(date)) %>%
    group_by(prname) %>%
    arrange(date) %>%
    mutate(daily_death_rate = ratedeaths - lag(ratedeaths, default = first(ratedeaths))) %>%
    mutate(daily_death_rate = pmax(daily_death_rate, 0))


ggplot(covid_data, aes(x = date, y = daily_death_rate, color = prname)) +
    geom_line() +
    labs(
        x = "Date",
        y = "Daily Death Rate",
        color = "Province"
    ) +
    scale_x_date(date_breaks = "6 months", date_labels = "%b %Y") + theme_bw() + ggtitle("Transformed Data: Non-Cumulative Weekly")

转换为非累计月度数据 #

进一步转换为非累计月度平均值数据并可视化：

library(ggplot2)
library(dplyr)
library(scales)
library(lubridate)  


covid_data_monthly <- covid_data %>%
    mutate(date = as.Date(date)) %>%
    mutate(year_month = floor_date(date, "month")) %>%
    group_by(prname) %>%
    arrange(date) %>%
    mutate(daily_death_rate = ratedeaths - lag(ratedeaths, default = first(ratedeaths))) %>%
    mutate(daily_death_rate = pmax(daily_death_rate, 0)) %>%
    group_by(prname, year_month) %>%
    summarise(
        monthly_avg_death_rate = mean(daily_death_rate, na.rm = TRUE),
        .groups = "drop"
    )

ggplot(covid_data_monthly, 
       aes(x = year_month, y = monthly_avg_death_rate, color = prname)) +
    geom_line() +
    geom_point() +
    labs(
        x = "Date",
        y = "Average Daily Death Rate",
        color = "Province"
    ) +
    scale_x_date(date_breaks = "6 months", date_labels = "%b %Y") + theme_bw() + ggtitle("Final Data: Non-Cumulative Monthly")

疑问 #

上述转换方法是否正确？

更新后的R代码 #

library(ggplot2)
library(dplyr)
library(scales)
library(lubridate)

temp_file <- tempfile()
download.file(
    url = "https://health-infobase.canada.ca/src/data/covidLive/covid19-download.csv",
    destfile = temp_file
)
covid_data <- read.csv(temp_file)

# convert weekly rates to interpolated daily rates
covid_data_daily <- covid_data %>%
    mutate(
        date = as.Date(date),
        # Use the provided weekly rate column
        weekly_rate = ratedeaths_last7
    ) %>%
    # create 7 rows for each week with daily rates
    group_by(prname) %>%
    arrange(date) %>%
    # expand each week into 7 days
    slice(rep(1:n(), each = 7)) %>%
    # adjust dates to spread across the week
    mutate(
        # subtract 6 days to get to start of week, then add row number within week
        actual_date = date - 6 + (row_number() - 1) %% 7,
        # convert weekly rate to daily rate
        daily_rate = weekly_rate / 7
    )

# calculate monthly rates
covid_data_monthly <- covid_data_daily %>%
    mutate(year_month = floor_date(actual_date, "month")) %>%
    group_by(prname, year_month) %>%
    # sum daily rates to get monthly rate
    summarise(
        monthly_death_rate = sum(daily_rate, na.rm = TRUE),
        .groups = "drop"
    )


ggplot(covid_data_monthly, 
       aes(x = year_month, y = monthly_death_rate, color = prname)) +
    geom_line() +
    geom_point() +
    labs(
        x = "Date",
        y = "Monthly Death Rate per 100,000 population",
        color = "Province",
        title = "Monthly COVID-19 Death Rates by Province",
        subtitle = "Based on interpolated daily rates from weekly data"
    ) +
    scale_x_date(date_breaks = "6 months", date_labels = "%b %Y") +
    theme_bw()

内容的提问来源于stack exchange，提问作者nov62024