R语言dplyr按实体分组基于筛选条件计算3天滚动平均值

输入数据框dput结果：

structure(list(Entity = c("A", "A", "A", "A", "A", "A", "A", 
"A", "A", "A", "B", "B", "B", "B", "B", "B", "B", "B", "B", "B"
), Date = structure(c(1640995200, 1641081600, 1641168000, 1641254400, 
1641340800, 1641427200, 1641513600, 1641600000, 1641686400, 1641772800, 
1640995200, 1641081600, 1641168000, 1641254400, 1641340800, 1641427200, 
1641513600, 1641600000, 1641686400, 1641772800), tzone = "UTC", class = c("POSIXct", 
"POSIXt")), Test = c("Y", "Y", "N", "Y", "N", "N", "Y", "Y", 
"Y", "Y", "Y", "Y", "N", "Y", "N", "N", "Y", "Y", "Y", "Y"), 
    Value = c(5, 10, 5, 10, 10, 5, 5, 5, 5, 20, 10, 10, 5, 20, 
    20, 5, 5, 20, 20, 20), COUNTER = c(1L, 2L, 3L, 4L, 5L, 6L, 
    7L, 8L, 9L, 10L, 1L, 2L, 3L, 4L, 5L, 6L, 7L, 8L, 9L, 10L)), class = c("tbl_df", 
"tbl", "data.frame"), row.names = c(NA, -20L))

需求：按Entity分组，筛选Test = 'Y'的记录，计算每个实体的3天滚动平均值（running 3 day average）。实体A的计算参考规则如下：

Entity   Counter_Running_avg     Running_Avg
A         1                     7.5 (15/2)
A         2                     10 (10/1)
A         3                     5   (15/3)    后续以此类推

原有错误实现代码：

dt %>%      
   arrange(Entity, Date) %>%
   group_by(Entity) %>%
   filter(Test = 'Y') %>%       
   summarise(Avg = mean(head(Value, 3), na.rm = TRUE))

• filter中判断相等需要用==，单等号=是赋值操作，会直接报错或得到错误筛选结果
• head(Value,3)只会取分组内前3条记录，无法实现逐行滚动计算
• summarise会将每个分组压缩为单行结果，无法保留每条筛选后记录对应的滚动均值
• 没有处理时间窗口逻辑，直接按行取数不符合3天滚动的时间维度要求

使用dplyr做数据处理，slider包做滑动窗口计算（适配时间维度滚动、自定义窗口大小，适配tidyverse语法）。
首先安装依赖包（已安装可跳过）：

install.packages(c("dplyr", "slider", "lubridate"))

方案1：按自然日3天窗口计算（常规running 3 day average定义：当前日期+往前2天共3天，自动忽略Test=N的记录） #

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

result <- dt %>%
  # 按实体、日期排序
  arrange(Entity, Date) %>%
  # 按实体分组
  group_by(Entity) %>%
  # 筛选Test为Y的记录，相等判断用==
  filter(Test == "Y") %>%
  # 生成筛选后的运行计数
  mutate(Counter_Running_avg = row_number(),
         # 按日期做滑动窗口，窗口范围是当前日期往前2天到当日，共3天
         Running_Avg = slide_index_dbl(
           .x = Value,
           .i = Date,
           .f = ~mean(.x, na.rm = TRUE),
           .before = days(2),
           .after = days(0),
           .complete = FALSE
         )) %>%
  ungroup()

如果需要匹配示例中遇到Test=N就重置滚动窗口的规则，可以在筛选后先用consecutive_id()给连续Y片段单独分组，再按连续片段做滚动计算即可。

运行代码后，每条Test='Y'的记录都会对应独立的滚动均值，Counter_Running_avg是每个实体下筛选后记录的连续序号，Running_Avg为对应窗口内的Value平均值。

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