在R语言中按组计算5天内的条件累积和问题求助

给定以下数据框，需要按group分组，为每一行计算**当前日期往前5天内（含当天）**的number累积和：

library(lubridate)

df <- data.frame(
  date = ymd( c( "2022-01-02","2022-01-03","2022-01-05","2022-01-07","2022-01-11","2022-01-14","2022-01-17","2022-01-18","2022-01-24","2022-01-27","2022-01-01","2022-01-04","2022-01-04","2022-01-08","2022-01-12","2022-01-14","2022-01-19","2022-01-24","2022-01-25","2022-01-28")),
  group = c("A","A","A","A","A","A","A","A","A","A","B","B","B","B","B","B","B","B","B","B"),
  number = c(10,30,20,50,30,50,40,50,30,50,55,10,30,20,50,30,40,30,40,30))

期望得到包含cumsum(s)列的结果：

# 期望结果示例
date       group number cumsum(s)
2022-01-02 A     10     10
2022-01-03 A     30     40
2022-01-05 A     20     60
2022-01-07 A     50     110
2022-01-11 A     30     80
2022-01-14 A     50     80
2022-01-17 A     40     90
2022-01-18 A     50     140
2022-01-24 A     30     30
2022-01-27 A     50     80
2022-01-01 B     55     55
2022-01-04 B     10     65
2022-01-04 B     30     95
2022-01-08 B     20     60
2022-01-12 B     50     70
2022-01-14 B     30     80
2022-01-19 B     40     70
2022-01-24 B     30     70
2022-01-25 B     40     70
2022-01-28 B     30     100

解决方案 #

推荐使用dplyr结合slider包实现高效的滑动窗口求和，步骤如下：

1. 加载所需包 #

library(lubridate)
library(dplyr)
library(slider)

2. 先按分组和日期排序 #

确保数据按group和date有序，保证窗口计算的准确性：

df_sorted <- df %>% 
  arrange(group, date)

3. 计算分组的5天滑动累积和 #

使用slide_index_sum函数，基于日期索引计算指定时间范围内的求和：

result <- df_sorted %>% 
  group_by(group) %>% 
  mutate(`cumsum(s)` = slide_index_sum(
    .x = number,          # 需要求和的列
    .i = date,            # 索引列（日期）
    .before = days(5),    # 窗口范围：当前日期往前5天
    .complete = FALSE     # 不需要窗口填满5天，只要在范围内的行都纳入计算
  )) %>% 
  ungroup()

替代方案（无需slider包） #

如果不想额外安装slider，可以用sapply结合日期范围判断实现，适合小数据集：

result <- df_sorted %>% 
  group_by(group) %>% 
  mutate(`cumsum(s)` = sapply(date, function(current_date) {
    sum(number[date >= current_date - days(5) & date <= current_date])
  })) %>% 
  ungroup()

运行后得到的result即为符合期望的数据集。

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