基于不同时间窗口计算分组参数的均值（R语言实现）

原始数据 #

df <- dplyr::tibble(ParameterID = c(1, 1, 1, 1, 1, 1,
                                    2, 2, 2),
                    Time= c(as.POSIXct("2022-01-01 10:05:00"),
                            as.POSIXct("2022-01-01 10:10:00"),
                            as.POSIXct("2022-01-01 10:15:00"),
                            as.POSIXct("2022-01-01 10:20:00"),
                            as.POSIXct("2022-01-01 10:25:00"),
                            as.POSIXct("2022-01-01 10:30:00"),
                            as.POSIXct("2022-01-01 10:05:00"),
                            as.POSIXct("2022-01-01 10:10:00"),
                            as.POSIXct("2022-01-01 10:30:00")),
                    value = c(1, 2, 3, 4, 5, 6, 1, 2, 3))

参数设置 #

low_interval <- 30
high_interval <- 15

需求说明 #

每间隔high_interval（15分钟）划分时间区间，按ParameterID分组计算value的均值：

• ParameterID=1：采用15分钟回溯窗口计算均值
• ParameterID=2：采用30分钟回溯窗口计算均值

当前初步处理代码 #

data <- df %>%
  mutate(diff = as.double.difftime(Time - min(Time), units = "mins")) %>%
  mutate(group_low = cut(diff, breaks = seq(0, max(diff)+low_interval, low_interval), include.lowest = TRUE)) %>%
  mutate(group_high = cut(diff, breaks = seq(0, max(diff)+high_interval, high_interval), include.lowest = TRUE)) %>%
  arrange(group_high)

预期输出 #

# A tibble: 2 × 3
  interval mean_1        mean_2       
  <chr>    <chr>         <chr>        
1 [0,15]   mean(1,2,3,4) mean(1, 2)   
2 (15,30]  mean(5, 6)    mean(1, 2, 3)

非拆分数据的实现思路 #

思路1：结合group_by与条件窗口筛选 #

利用dplyr的分组和条件判断，动态适配不同参数的窗口规则，全程无需拆分数据：

1. 为每条数据标记所属的目标时间区间（即每15分钟一个的interval）
2. 按ParameterID和目标区间分组，根据参数ID筛选对应回溯窗口内的value
3. 格式化均值字符串，最后通过pivot_wider转成宽表格式

示例代码：

result <- df %>%
  mutate(diff = as.double.difftime(Time - min(Time), units = "mins")) %>%
  # 标记目标时间区间
  mutate(interval = cut(diff, breaks = seq(0, max(diff)+high_interval, high_interval), include.lowest = TRUE)) %>%
  group_by(ParameterID, interval) %>%
  summarise(
    mean_str = case_when(
      ParameterID == 1 ~ {
        # 15分钟窗口：取当前区间上限前15分钟内的数据
        window_upper <- max(diff)
        valid_vals <- value[diff >= window_upper - high_interval]
        paste0("mean(", paste(valid_vals, collapse = ","), ")")
      },
      ParameterID == 2 ~ {
        # 30分钟窗口：取当前区间上限前30分钟内的数据
        window_upper <- max(diff)
        valid_vals <- value[diff >= window_upper - low_interval]
        paste0("mean(", paste(valid_vals, collapse = ", "), ")")
      }
    ),
    .groups = "drop"
  ) %>%
  # 转成预期的宽表结构
  pivot_wider(names_from = ParameterID, values_from = mean_str, names_prefix = "mean_") %>%
  arrange(interval)

思路2：使用slider包实现时间滑动窗口 #

slider包提供更灵活的时间窗口控制，可直接基于POSIX时间定义窗口长度：

1. 按ParameterID分组，为每个参数分配对应的窗口时长
2. 遍历目标时间区间，计算每个区间内符合窗口规则的均值
3. 去重并转成宽表格式

示例代码（需先安装slider包）：

library(slider)

# 生成目标时间区间的端点
time_breaks <- seq(min(df$Time), max(df$Time)+high_interval*60, by = paste0(high_interval, " mins"))

result <- df %>%
  group_by(ParameterID) %>%
  mutate(
    # 按参数ID设置窗口长度（转成秒）
    window_sec = case_when(
      ParameterID == 1 ~ high_interval*60,
      ParameterID == 2 ~ low_interval*60
    )
  ) %>%
  summarise(
    interval = cut(as.double(Time - min(Time))/60, breaks = seq(0, max(as.double(Time - min(Time))/60)+high_interval, high_interval), include.lowest = TRUE),
    mean_str = slider::slide_chr(Time, .x = value,
                                 .f = function(times, vals) {
                                   current_end <- max(times)
                                   valid_vals <- vals[times >= current_end - unique(window_sec)]
                                   paste0("mean(", paste(valid_vals, collapse = ifelse(unique(ParameterID)==1, ",", ", ")), ")")
                                 },
                                 .before = unique(window_sec),
                                 .complete = FALSE
    ),
    .groups = "drop"
  ) %>%
  distinct(interval, ParameterID, mean_str) %>%
  pivot_wider(names_from = ParameterID, values_from = mean_str, names_prefix = "mean_")

关键注意点 #

• 全程通过group_by和条件判断实现不同参数的窗口逻辑，无需拆分数据
• 若需计算实际均值而非字符串格式，只需将paste0("mean(",...)替换为mean(valid_vals, na.rm = TRUE)
• 时间窗口的边界判断（如>=或>）可根据需求调整，确保符合业务规则

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