如何在R中为分组数量不同的数据集计算滚动均值？

我有一个包含REGION和TYPE两个分组维度的数据集，需要按月份进一步分组，每月观测值数量在1到8个之间。我想要新增一列计算过去3个月的滚动均值——比如本月2条、上月3条、前月1条时，要计算这6条数据的均值。尝试用zoo、summarise结合slide_dbl和mutate实现，但始终无法正确获取各组的样本量n()。

数据集 #

library(slider)
library(lubridate)
my_data <- structure(list(REGION = c("3226H5", "3226H5", "3226H5", "3226H5", 
"3226H5", "3226H5", "3226H5", "3226H5", "3226H5", "3226H5", "3226H5", 
"3226H5", "3226H5", "3226H5", "3226H5", "3226H5", "3226H5", "3226H5", 
"3226H5", "3226H5", "3226H5", "3226H5", "3226H5", "3226H5", "3226H5", 
"3226H5", "3226H5", "3226H5", "3226H5", "3226H5", "3226H5", "3226H5", 
"3226H5", "3226H5", "3226H5", "3226H5", "3226H5", "3226H5", "3226H5", 
"3226H5", "3285", "3285", "3285", "3285", "3285", "3285", "3285", 
"3285", "3285", "3285"), TYPE = c("Type A", "Type A", "Type A", 
"Type A", "Type A", "Type A", "Type A", "Type A", "Type A", "Type A", 
"Type A", "Type A", "Type A", "Type A", "Type A", "Type A", "Type A", 
"Type A", "Type A", "Type A", "Type B", "Type B", "Type B", "Type B", 
"Type B", "Type B", "Type B", "Type B", "Type B", "Type B", "Type B", 
"Type B", "Type B", "Type B", "Type B", "Type B", "Type B", "Type B", 
"Type B", "Type B", "Type B", "Type B", "Type B", "Type B", "Type B", 
"Type B", "Type B", "Type B", "Type B", "Type B"), DATE = structure(c(1609754580, 
1609755279, 1609755683, 1610011200, 1612173600, 1612174200, 1612174800, 
1612432800, 1614593100, 1614594000, 1614594600, 1614851400, 1617617409, 
1617618215, 1617618894, 1617875485, 1620040008, 1620041400, 1620042300, 
1620295200, 1609754580, 1609755279, 1609755683, 1610011200, 1612173600, 
1612174200, 1612174800, 1612432800, 1614593100, 1614594000, 1614594600, 
1614851400, 1617617409, 1617618215, 1617618894, 1617875485, 1620040008, 
1620041400, 1620042300, 1620295200, 1609748700, 1609758900, 1612169112, 
1612177800, 1614588900, 1614599100, 1617612277, 1617622409, 1620034319, 
1620042163), tzone = "UTC", class = c("POSIXct", "POSIXt")), 
    RESULT = c(0.71, 1.47, 1.28, 0.9, 1.39, 1.42, 1.54, 0.37, 
    2.94, 2.25, 2.01, 0.52, 10.5, 1.02, 1.16, 0.62, 58.67, 0.75, 
    0.94, 2.72, 0.012, 0.004, 0.005, 0.014, 0.014, 0.005, 0.006, 
    0.009, 0.013, 0.007, 0.006, 0.015, 0.022, 0.004, 0.004, 0.009, 
    0.021, 0.005, 0.005, 0.011, 0.012, 0.012, 0.01, 0.012, 0.019, 
    0.011, 0.013, 0.012, 0.016, 0.014)), row.names = c(NA, -50L
), class = "data.frame")

我的尝试 #

grouped_roll <- my_data %>%
  
  add_count(REGION, TYPE, DATE, name = "new_cases") %>%

  arrange(REGION, TYPE, DATE) %>%   
  
  group_by(REGION, TYPE)    %>%        
    
  mutate( 
     MONTHLY = floor_date(DATE, "month"),
    rolling_mean = slide_index_dbl(RESULT,
      .i = MONTHLY,      # 按日期索引
      .f = mean,                      
      .after = months(6)),             
    rolling_sd = slide_index_dbl(
      RESULT,
      .i = MONTHLY,
      .f = ~ sd(.x, na.rm = TRUE),
      .after = months(6)
    )
  )

view(grouped_roll)

grouped_roll <- grouped_roll %>%
  group_by(REGION, TYPE, MONTHLY) %>%
  slice(seq_len(1))
grouped_roll

尝试结果 #

Groups:   REGION, TYPE, MONTHLY [15]
   REGION TYPE   DATE                RESULT new_cases MONTHLY             rolling_mean rolling_sd
   <chr>  <chr>  <dttm>               <dbl>     <int> <dttm>                     <dbl>      <dbl>
 1 3226H5 Type A 2021-01-04 10:03:00  0.71          1 2021-01-01 00:00:00      4.66      12.9    
 2 3226H5 Type A 2021-02-01 10:00:00  1.39          1 2021-02-01 00:00:00      5.55      14.4    
 3 3226H5 Type A 2021-03-01 10:05:00  2.94          1 2021-03-01 00:00:00      7.01      16.5    
 4 3226H5 Type A 2021-04-05 10:10:09 10.5           1 2021-04-01 00:00:00      9.55      20.1    

正确解决方案 #

要实现过去3个月的滚动均值并获取对应样本量，需要调整滑动窗口的参数逻辑，同时自定义函数返回所需统计量。具体代码如下：

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

result <- my_data %>%
  arrange(REGION, TYPE, DATE) %>%
  group_by(REGION, TYPE) %>%
  mutate(MONTHLY = floor_date(DATE, "month")) %>%
  # 滑动窗口：当前月份 + 过去2个月（共3个月）
  mutate(rolling_stats = slide_index(
    .x = RESULT,
    .i = MONTHLY,
    .f = ~ list(
      rolling_mean = mean(.x, na.rm = TRUE),
      rolling_n = length(.x)  # 获取窗口内样本量
    ),
    .before = months(2),  # 指定包含过去2个月
    .complete = FALSE     # 允许窗口数据不完整（比如前2个月无数据的情况）
  )) %>%
  # 展开list列到单独字段
  unnest_wider(rolling_stats) %>%
  # 按月份去重，每个月保留一行统计结果
  group_by(REGION, TYPE, MONTHLY) %>%
  slice(1) %>%
  ungroup()

# 查看结果
head(result)

关键说明 #

1. 窗口范围设置：.before = months(2)确保窗口包含当前月份及过去2个月，正好覆盖过去3个月的需求；
2. 样本量获取：在自定义.f函数中用length(.x)直接统计窗口内的观测数量，解决了之前无法正确获取n()的问题；
3. 不完整窗口处理：.complete = FALSE允许窗口内数据不足3个月时仍计算统计值，避免返回NA。

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