R语言跨数据框按站点日期匹配 多变量滚动汇总指定时间窗统计值

需求说明 #

现有两份数据集：

• trap_data：诱捕记录表，存储站点编号、诱捕日期
• climate：气象观测表，存储站点编号、气象指标V1/V2、观测日期
  需要给trap_data新增统计列：匹配相同站点，筛选诱捕日期前指定长度时间窗口（示例为20天）内的气象记录，分别对V1、V2计算有效样本量N、均值、总和。

示例数据 #

trap_data <- structure(list(site = c(1, 2, 3, 3), trap_date = structure(c(18809, 
18809, 18307, 18322), class = "Date")), class = "data.frame", row.names = c(NA, 
-4L))

climate <- structure(list(site = c(1, 1, 1, 1, 2, 2, 2, 2, 3, 3, 3, 3), 
    V1 = c(0.1, 0.2, 0.1, 0.1, 0.5, 0.2, 0.3, 0.1, 0.2, 0.1, 
    0.1, 0.5), V2 = c(1, 1, 3, 1, 2, 3, 3, 1, 1, 3, 1, 2), date = structure(c(18779, 
    18790, 18789, 18792, 18791, 18790, 18789, 18792, 18305, 18306, 
    18307, 18308), class = "Date")), class = "data.frame", row.names = c(NA, 
-12L))

实现代码 #

方案1：dplyr 逐行计算（易读易调整） #

适合数据量较小的场景，逻辑直观，修改窗口长度、统计指标都很方便：

library(dplyr)
library(purrr)
library(tidyr)

# 自定义时间窗口长度，单位为天
window_length <- 20

result <- trap_data %>%
  mutate(
    cal_res = pmap(
      list(site, trap_date),
      function(s, d) {
        # 筛选匹配的气象记录：同站点、日期在窗口范围内
        sub_clim <- climate %>%
          filter(
            site == s,
            date >= d - window_length,
            date < d # 若需要包含诱捕当日，改为 date <= d
          )
        # 返回统计值
        data.frame(
          v1_n = nrow(sub_clim),
          v1_mean = mean(sub_clim$V1, na.rm = T),
          v1_sum = sum(sub_clim$V1, na.rm = T),
          v2_n = nrow(sub_clim),
          v2_mean = mean(sub_clim$V2, na.rm = T),
          v2_sum = sum(sub_clim$V2, na.rm = T)
        )
      }
    )
  ) %>%
  unnest(cal_res)

方案2：data.table 非等值连接（高性能） #

适合十万行以上的大数据集，用非等值滚动连接代替逐行遍历，运行速度快几个量级：

library(data.table)

setDT(trap_data)
setDT(climate)
window_length <- 20

# 生成时间窗口边界
trap_data[, `:=`(win_start = trap_date - window_length, win_end = trap_date)]

# 非等值连接匹配+分组统计
match_stat <- climate[
  trap_data,
  on = .(site, date >= win_start, date < win_end),
  .(
    v1_n = .N,
    v1_mean = mean(V1, na.rm = T),
    v1_sum = sum(V1, na.rm = T),
    v2_mean = mean(V2, na.rm = T),
    v2_sum = sum(V2, na.rm = T)
  ),
  by = .EACHI
]

# 整理输出结果
result <- match_stat[, .(
  site, 
  trap_date = date.1, 
  v1_n, 
  v1_mean, 
  v1_sum, 
  v2_n = v1_n, 
  v2_mean, 
  v2_sum
)]

提示：如果气象数据存在NA值，代码里已经加了na.rm = T参数自动剔除缺失值计算，不需要额外处理。如果窗口内没有匹配的气象记录，均值和总和会返回NaN，可根据需求替换为0或者其他填充值。

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