按ID分组，基于最小日期每3天聚合数值的R语言实现问题

需求说明 #

按ID分组，从每个ID的最小DATE开始，每3天对READING_IN和READING_OUT进行求和聚合。

原始数据 #

structure(list(ID = c("pqr", "abc", "ort", "kkg", "ssc", "ccv", 
"xyz", "xyz", "xyz"), DATE = c("2022-06-07", "2022-06-24", "2022-06-02", 
"2022-06-01", "2022-06-16", "2022-06-07", "2022-06-11", "2022-06-13", "2022-06-27"
), READING_IN = c(150, 2800, 600, 500, 1395.94, 500, 800, 179, 200
), READING_OUT = c(150, 2800, 600, 500, 1400, 501.4, 371.34, 
556.47, 462.75)), class = "data.frame", row.names = c(NA, -9L))

原尝试代码及问题 #

原代码使用cut()函数按全局固定3天窗口分组，导致每个ID的分组不是从自身最小日期开始，比如ID=xyz的2022-06-13被错误分到单独组：

DT$DATE = as.Date(DT$DATE, format = "%Y-%m-%d")
DT1 = DT %>%
  group_by(ID, group = cut(as.Date(DT$DATE, format = "%Y-%m-%d"), '3 days')) %>%
  summarise(date_range = paste(min(DATE), min(DATE) + 2, sep = ' to '), 
            sum_in =  sum(READING_IN),
            sum_out = sum(READING_OUT), .groups = 'drop') %>%
  select(-group)

原错误结果中ID=xyz的部分：

期望输出（ID=xyz部分） #

正确解决方案 #

核心思路是按ID分组后，以每个ID的最小日期为起点，计算每个日期与最小日期的天数差，再按每3天为一组进行聚合：

library(dplyr)

# 转换日期格式
DT$DATE <- as.Date(DT$DATE, format = "%Y-%m-%d")

DT_result <- DT %>%
  group_by(ID) %>%
  # 计算每个ID的最小日期，以及当前日期与最小日期的天数差
  mutate(min_date = min(DATE),
         day_diff = as.integer(DATE - min_date),
         # 按每3天一组分组（从最小日期开始）
         group_num = floor(day_diff / 3)) %>%
  # 按ID和组号分组聚合
  group_by(ID, group_num, .add = TRUE) %>%
  summarise(
    date_range = paste(min(DATE), min(DATE) + 2, sep = " to "),
    sum_in = sum(READING_IN),
    sum_out = sum(READING_OUT),
    .groups = "drop"
  ) %>%
  # 移除组号列
  select(-group_num) %>%
  # 按ID和日期范围排序
  arrange(ID, date_range)

验证结果 #

运行上述代码后，ID=xyz的输出符合期望：

# 查看xyz的结果
filter(DT_result, ID == "xyz")
#> # A tibble: 2 × 4
#>   ID    date_range              sum_in sum_out
#>   <chr> <chr>                   <dbl>   <dbl>
#> 1 xyz   2022-06-11 to 2022-06-13   979    928.
#> 2 xyz   2022-06-27 to 2022-06-29   200    463.

所有ID的聚合结果都将以自身最小日期为起点，每3天为一个窗口进行求和。

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