按ID、2小时时间窗口及CODE=IP条件实现rolling sum需求

1. 构造示例数据集 #

先创建符合需求的测试数据，包含ID、时间、CODE和数值字段：

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

set.seed(123)
df <- tibble(
  ID = rep(c("A", "B"), each = 6),
  Time = ymd_hms("2024-01-01 00:00:00") + hours(0:5) + minutes(sample(0:30, 12, replace = TRUE)),
  CODE = sample(c("IP", "X", "Y", "123", "456"), 12, replace = TRUE),
  Value = sample(1:10, 12, replace = TRUE)
) %>% arrange(ID, Time)

2. 实现核心需求：计算cum_IP列 #

按ID分组，仅当当前行CODE为"IP"时，计算当前时间往前2小时内所有CODE为"IP"的Value滚动和，否则赋值0：

df_processed <- df %>%
  group_by(ID) %>%
  mutate(
    # 生成当前行对应的2小时时间窗口
    time_window = interval(Time - hours(2), Time),
    # 条件判断+滚动求和
    cum_IP = if_else(
      CODE != "IP",
      0,
      slide_dbl(
        .x = tibble(t = Time, v = Value, c = CODE),
        .f = ~ sum(.x$v[.x$c == "IP" & .x$t %within% .y$time_window]),
        .i = cur_group_rows(),
        .before = Inf,
        .complete = FALSE,
        .y = cur_data()
      )
    )
  ) %>%
  ungroup()

代码逻辑说明：

• group_by(ID)确保按用户维度独立计算
• time_window定义每个行的时间范围：当前时间向前推2小时至当前时间
• if_else直接过滤掉非"IP"行，赋值0
• slider::slide_dbl实现逐行滚动计算：通过自定义函数过滤窗口内符合CODE="IP"的行，对Value求和

3. 适配自定义条件（如CODE为数字） #

如果需要计算CODE为纯数字的行的滚动和，只需修改判断条件和过滤逻辑即可，示例如下：

library(stringr)

df_processed_num <- df %>%
  group_by(ID) %>%
  mutate(
    time_window = interval(Time - hours(2), Time),
    cum_Num = if_else(
      # 判断CODE是否为纯数字
      !str_detect(CODE, "^\\d+$"),
      0,
      slide_dbl(
        .x = tibble(t = Time, v = Value, c = CODE),
        .f = ~ sum(.x$v[str_detect(.x$c, "^\\d+$") & .x$t %within% .y$time_window]),
        .i = cur_group_rows(),
        .before = Inf,
        .complete = FALSE,
        .y = cur_data()
      )
    )
  ) %>%
  ungroup()

4. 大数据场景优化：data.table实现 #

如果数据集较大，用data.table效率更高，实现逻辑一致：

library(data.table)
library(lubridate)

setDT(df)
df[, Time := ymd_hms(Time)]

df_processed_dt <- df[, {
  time_start <- Time - hours(2)
  cum_IP <- sapply(seq_len(.N), function(i) {
    if (CODE[i] != "IP") return(0)
    sum(Value[Time >= time_start[i] & Time <= Time[i] & CODE == "IP"])
  })
  .SD[, cum_IP := cum_IP]
}, by = ID]

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