如何用R dplyr计算时间序列中多组起止日期间的数值平均值

基础数据设置 #

library(tidyverse)
library(lubridate)

time_series <- tibble(dates=seq.Date(from=ymd('20240101'), to=ymd('20241231'), 'day'),
                      value = runif(366,max=10))
                      
target_dates <- tibble(id=c('A','B','C'),
                      start=ymd(c('20240104','20240504','20240704')),
                      end  =ymd(c('20240201','20240524','20240924'))) %>% 
               mutate(ndays=as.integer(end-start))

需求：计算time_series$value在target_dates每组start和end日期之间的平均值，最终输出包含各区间平均值的tibble/data frame。

尝试过的无效方法 #

1. 分组汇总写法：

target_dates %>% 
  group_by(start) %>% 
  summarise(avg = mean(time_series %>% slice(first(which(dates==start)):slice(last(which(dates==end)))) %>% select(values)))

2. 使用between函数（因between不支持向量匹配失效）：

time_series %>% 
  summarise(avg = mean(value[between(target_dates$start,target_dates$end)]))

3. 尝试slider包的slide_idx_double但不知如何整合数据：

time_series %>%
    summarise(avg = slide_index_dbl(WHAT GOES HERE??,start,.after=ndays,mean))

解决方案 #

方法1：dplyr逐行计算 #

通过rowwise()让代码逐行处理target_dates的每个区间，筛选对应日期的数值后计算均值：

target_dates %>%
  rowwise() %>%
  mutate(avg_value = mean(time_series$value[time_series$dates >= start & time_series$dates <= end])) %>%
  ungroup()

方法2：fuzzyjoin模糊连接后汇总 #

先按日期区间匹配两个数据集，再分组计算平均值：

library(fuzzyjoin)

fuzzy_inner_join(
  time_series,
  target_dates,
  by = c("dates" = "start", "dates" = "end"),
  match_fun = list(`>=`, `<=`)
) %>%
  group_by(id, start, end, ndays) %>%
  summarise(avg_value = mean(value), .groups = "drop")

方法3：slider包正确实现 #

利用slide_index_dbl针对每个区间直接计算均值：

library(slider)

target_dates %>%
  mutate(avg_value = slide_index_dbl(
    .x = time_series$value,
    .i = time_series$dates,
    .start = start,
    .end = end,
    .f = mean
  ))

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