R语言实现干预前后25周冲突事件与伤亡数据周度聚合问询

核心思路 #

1. 统一日期格式，定位每个国家的干预日期
2. 计算每日数据相对干预日的天数差，按需求划分干预前/后各周、干预当天的区间
3. 按国家+干预日期分组，对每个区间的冲突事件数和伤亡数求和
4. 将长格式聚合结果转为目标宽格式，匹配需求的数据集结构

完整代码 #

library(dplyr)
library(lubridate)
library(tidyr)

aggregate_treatment_data <- function(df) {
  # 统一日期格式，提取每个国家的干预日期
  df_clean <- df %>%
    mutate(
      conflict_date = as_date(conflict_date),
      intervention_date = as_date(intervention.date)
    ) %>%
    filter(!is.na(intervention_date)) %>%
    select(country, intervention_date) %>%
    distinct()
  
  # 为所有日度数据匹配对应国家的干预日期，计算相对天数差
  df_with_period <- df %>%
    mutate(conflict_date = as_date(conflict_date)) %>%
    left_join(df_clean, by = "country") %>%
    filter(!is.na(conflict_date), !is.na(intervention_date)) %>%
    mutate(
      days_diff = as.integer(conflict_date - intervention_date),
      # 定义周区间标签：干预前n周、干预当天、干预后n周
      period = case_when(
        days_diff == 0 ~ "intervention_day",
        days_diff > 0 ~ paste0("week", ceiling(days_diff / 7), "_post"),
        days_diff < 0 ~ paste0("week", ceiling(abs(days_diff) / 7), "_pre")
      )
    ) %>%
    # 仅保留干预前后25周及干预当天的数据
    filter(
      period == "intervention_day" |
        (str_detect(period, "_pre") & as.integer(substr(period, 5, nchar(period)-4)) <=25) |
        (str_detect(period, "_post") & as.integer(substr(period, 5, nchar(period)-5)) <=25)
    )
  
  # 按国家+干预日期+周区间聚合数据
  aggregated_long <- df_with_period %>%
    group_by(country, intervention_date, period) %>%
    summarise(
      event_total = sum(conflict_event_count, na.rm = TRUE),
      casualties_total = sum(sum_casualties, na.rm = TRUE),
      # 提取干预日对应的year和day字段
      year = first(year[conflict_date == intervention_date]),
      day = first(day[conflict_date == intervention_date]),
      .groups = "drop"
    )
  
  # 转换为宽格式，匹配目标数据集结构
  aggregated_wide <- aggregated_long %>%
    pivot_wider(
      id_cols = c(country, year, day),
      names_from = period,
      values_from = c(event_total, casualties_total),
      names_glue = "{period}_{.value}"
    ) %>%
    # 重命名列以匹配需求命名规则
    rename_with(
      ~ gsub("event_total", "event_total", .x) %>%
        gsub("casualties_total", "casualties", .x)
    ) %>%
    # 按逻辑顺序排序列：干预前25-1周 → 干预当天 → 干预后1-25周
    select(
      country, year, day,
      paste0("week", 25:1, "_pre_event_total"),
      paste0("week", 25:1, "_pre_casualties"),
      intervention_day_event_total,
      intervention_day_casualties,
      paste0("week", 1:25, "_post_event_total"),
      paste0("week", 1:25, "_post_casualties")
    )
  
  return(aggregated_wide)
}

# 生成目标聚合数据集
conflict_data_aggregated <- aggregate_treatment_data(conflict_data)

关键说明 #

1. 日期兼容性：用lubridate包统一转换日期格式，避免字符型日期引发的计算错误
2. 区间精准划分：通过days_diff计算每日与干预日的天数差，严格匹配“干预前一周=干预日前7天至前1天”“干预后一周=干预日后1天至后7天”的需求定义
3. 多国家适配：通过left_join为每个国家的所有日度数据自动匹配对应干预日期，支持批量处理多国家数据
4. 列顺序对齐：最终宽格式的列按“干预前从远到近→干预当天→干预后从近到远”排序，完全匹配示例数据集的结构逻辑

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