R语言按区间计算列均值：处理周前后12周暴力事件周均数计算

你可以使用tidyverse系列包完成需求，首先运行如下命令安装依赖包：

install.packages("tidyverse")

步骤1：构造全局连续周索引 #

由于周数按年重置，直接加减周数会出现跨年计算错误，首先给所有年-周组合分配全局唯一的连续序号：

library(tidyverse)

# 加载数据后先构造连续周索引
df <- df %>%
  arrange(Year, Week) %>%
  mutate(global_week = group_indices(., Year, Week))

如果你的数据存在缺失的年-周组合，推荐用lubridate包将年+周转为实际日期后计算周差，精度更高。

步骤2：提取所有待计算的处理点 #

筛选出Treatment为0或1的行作为待计算的处理观测：

treat_points <- df %>%
  filter(Treatment %in% c(0, 1)) %>%
  select(Country, Year, Week, Event_Count, Treatment, global_week) %>%
  rename(treat_global_week = global_week)

步骤3：定义区间均值计算函数 #

自定义函数，输入单个处理点的信息，返回其前后12周内各4周区间的均值：

calc_interval_means <- function(treat_row) {
  # 筛选当前国家、处理点前后12周范围内的所有数据
  country_sub <- df %>%
    filter(
      Country == treat_row$Country,
      global_week >= treat_row$treat_global_week - 12,
      global_week <= treat_row$treat_global_week + 12
    ) %>%
    # 计算相对于处理周的偏移量
    mutate(week_offset = global_week - treat_row$treat_global_week) %>%
    # 分配区间标签
    mutate(interval = case_when(
      between(week_offset, -12, -9) ~ "-12to-9",
      between(week_offset, -8, -5) ~ "-8to-5",
      between(week_offset, -4, -1) ~ "-4to-1",
      week_offset == 0 ~ "0",
      between(week_offset, 1, 4) ~ "1to4",
      between(week_offset, 5, 8) ~ "5to8",
      between(week_offset, 9, 12) ~ "9to12"
    )) %>%
    filter(!is.na(interval))
  
  # 计算各区间均值并转宽格式
  country_sub %>%
    group_by(interval) %>%
    summarise(mean_val = mean(Event_Count, na.rm = T), .groups = "drop") %>%
    pivot_wider(names_from = interval, values_from = mean_val)
}

步骤4：批量计算所有处理点的区间均值 #

对所有处理点应用上述函数，合并得到最终输出表：

final_result <- treat_points %>%
  mutate(interval_res = pmap(., ~calc_interval_means(tibble(...)))) %>%
  unnest(interval_res)

输出的final_result就是你需要的格式，每个处理点占一行，包含所有区间的均值列。如果运行速度较慢，可以将数据转为data.table格式优化执行效率。

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