如何在R中按两列分组并基于日期范围对指定列求和？

问题背景 #

需按Class和Code分组，对每行计算当前行Date起30个工作日范围内（含当前Date）同组的Pay总和。现有代码未正确应用日期范围筛选，导致直接求和整个分组的Pay。

原始数据 #

Class|      Code|       Date|       Pay|
1   x0001       y0001       2022-01-01  10
2   x0002       y0001       2022-01-03  5
3   x0003       y0002       2022-01-15  5
4   x0003       y0002       2022-01-21  15
5   x0003       y0002       2022-02-22  10
6   x0003       y0002       2022-03-04  10
7   x0004       y0001       2022-01-04  20
8   x0004       y0001       2022-02-03  5
9   x0004       y0001       2022-03-15  30

期望输出 #

Class|      Code|       Date|       Pay|    end_date|   summing|    
1   x0001       y0001       2022-01-01  10      2022-02-14  10
2   x0002       y0001       2022-01-03  5       2022-02-14  5
3   x0003       y0002       2022-01-15  5       2022-02-28  30
4   x0003       y0002       2022-01-21  15      2022-03-03  35
5   x0003       y0002       2022-02-22  10      2022-04-04  20
6   x0003       y0002       2022-03-04  10      2022-04-14  10
7   x0004       y0001       2022-01-04  20      2022-02-15  25
8   x0004       y0001       2022-02-03  5       2022-03-16  35
9   x0004       y0001       2022-03-15  30      2022-04-25  30

当前错误输出 #

Class|      Code|       Date|       Pay|    end_date|   summing |
1   x0001       y0001       2022-01-01  10      2022-02-14  10
2   x0002       y0001       2022-01-03  5       2022-02-14  5
3   x0003       y0002       2022-01-15  5       2022-02-28  40
4   x0003       y0002       2022-01-21  15      2022-03-03  40
5   x0003       y0002       2022-02-22  10      2022-04-04  40
6   x0003       y0002       2022-03-04  10      2022-04-14  40
7   x0004       y0001       2022-01-04  20      2022-02-15  55
8   x0004       y0001       2022-02-03  5       2022-03-16  55
9   x0004       y0001       2022-03-15  30      2022-04-25  55

问题原因 #

原始代码中summing列的计算逻辑错误：

mutate(summing = ifelse(Date <= end_date, sum(Pay), 0))

sum(Pay)会直接对整个分组的Pay求和，ifelse仅判断当前行的Date是否小于等于end_date，并未筛选分组内符合日期范围的行。

另外，原始代码存在两个小问题：

1. create.calendar中holidays参数使用了赋值符号<-，应改为=；
2. Date列初始为字符型，需先转换为Date类型，避免后续日期判断出错。

修正代码 #

library(tidyverse)
library(bizdays)

# 构造数据并转换Date类型
Class <- c("x0001", "x0002", "x0003", "x0003", "x0003", "x0003","x0004", "x0004", "x0004")
Code <- c("y0001", "y0001", "y0002", "y0002", "y0002", "y0002", "y0001", "y0001", "y0001")
Date <- as.Date(c("2022-01-01", "2022-01-03", "2022-01-15", "2022-01-21", "2022-02-22", "2022-03-04",
                  "2022-01-04", "2022-02-03", "2022-03-15"))
Pay <- c(10, 5, 5, 15, 10, 10, 20, 5, 30)
df <- data.frame(Class, Code, Date, Pay)

# 创建工作日历，修正holidays参数的赋值符号
holidays <- as.Date(c("2022-01-17", "2022-02-12"))
calen <- create.calendar("Calendar", 
                         weekdays=c('sunday', 'saturday'), 
                         holidays = holidays, 
                         start.date = "2022-01-01", 
                         end.date = "2022-12-31", 
                         financial = FALSE)

# 修正求和逻辑：对每行，筛选分组内日期在[当前Date, 当前end_date]的Pay求和
df %>%
  group_by(Class, Code) %>%
  arrange(Date) %>%
  mutate(
    # 计算30个工作日后的结束日期，用is.bizday简化判断
    end_date = ifelse(
      is.bizday(Date, cal = "Calendar"),
      offset(Date, 29, cal = "Calendar"),  # 工作日：加29个工作日凑够30天（含当天）
      offset(Date, 30, cal = "Calendar")   # 非工作日：加30个工作日
    ) %>% as.Date(origin = "1970-01-01"),
    # 用map2_dbl遍历每行，筛选同组内符合日期范围的Pay求和
    summing = map2_dbl(Date, end_date, ~sum(Pay[Date >= .x & Date <= .y]))
  ) %>%
  ungroup()

关键修正说明 #

1. 日期类型转换：提前将Date列转为Date类型，避免字符型日期的判断错误；
2. 工作日判断优化：使用is.bizday()替代手动判断周末和节假日，逻辑更简洁可靠；
3. 行级范围求和：通过purrr::map2_dbl对每行的起始/结束日期，在分组内筛选符合Date >= 当前行Date且Date <= 当前行end_date的记录，再求和Pay，实现精准的行级范围求和。

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