如何高效为数据集观测值匹配另一数据集截至指定日期的汇总值？

问题背景 #

有两个百万级观测的数据集：

• df1：2022年各ID的周度观测数据，包含ID、Date、Var1
• df2：相同ID的无固定时间间隔观测数据，包含ID、Date、Var2、Var3

需要给df1新增三列：

• Mean_Var2：对应ID下，截至当前行Date的Var2均值
• Mean_Var3：对应ID下，截至当前行Date的Var3均值
• N：参与计算均值的df2样本量

要求避免逐行迭代，保证处理效率。

示例输入数据：

df1 <- data.frame(ID = c(1, 1, 1, 1, 2, 2, 2, 3, 3),
                  Date = as.Date(c('2022-07-04', '2022-07-11', '2022-07-18', '2022-08-01',
                                   '2022-06-27', '2022-07-18', '2022-08-01',
                                   '2022-12-01', '2022-12-03')),
                  Var1 = c(200, 45, 70, 90, 50, 70, 90, 70, 90))

df2 <- data.frame(ID = c(1, 1, 2, 2, 2, 2),
                  Date = as.Date(c('2010-12-06', '2022-07-29', 
                                   '2015-06-27', '2016-07-04', '2016-07-05', '2018-09-21')),
                  Var2 = c(200, 300, 100, 150, 200, 600),
                  Var3 = c(0.5, 0.6, 0.1, 0.1, 0.4, 0.0))

高效解决方案 #

方法一：dplyr + tidyr（tidyverse生态） #

核心思路：先对df2按ID分组计算累积统计量，再通过非等值连接匹配到df1对应日期的最新统计值。

library(dplyr)
library(tidyr)

# 预处理df2：按ID分组，计算截至每个日期的累积均值和样本量
df2_cum <- df2 %>%
  group_by(ID) %>%
  arrange(Date) %>%
  mutate(
    Cum_Mean_Var2 = cummean(Var2),
    Cum_Mean_Var3 = cummean(Var3),
    Cum_N = row_number()
  ) %>%
  ungroup()

# 非等值连接匹配：找到df1每个行对应的、截至当前日期的最新累积统计
df1_result <- df1 %>%
  left_join(df2_cum, by = "ID") %>%
  filter(Date.x >= Date.y | is.na(Date.y)) %>%
  group_by(ID, Date.x, Var1) %>%
  slice_max(Date.y, n = 1, with_ties = FALSE) %>%
  ungroup() %>%
  rename(Date = Date.x) %>%
  # 处理无匹配的情况（比如ID=3），可按需调整默认值
  mutate(
    Mean_Var2 = replace_na(Cum_Mean_Var2, 0),
    Mean_Var3 = replace_na(Cum_Mean_Var3, 0),
    N = replace_na(Cum_N, 0)
  ) %>%
  select(ID, Date, Var1, Mean_Var2, Mean_Var3, N)

方法二：data.table（百万级数据最优选择） #

data.table的非等值连接和分组计算效率远高于基础R，是处理超大规模数据的首选。

library(data.table)

# 转换为data.table格式
setDT(df1)
setDT(df2)

# 对df2按ID分组，计算累积统计量
df2[, `:=`(
  Cum_Mean_Var2 = cummean(Var2),
  Cum_Mean_Var3 = cummean(Var3),
  Cum_N = .I
), by = ID]
setorder(df2, ID, Date)

# 直接通过非等值连接匹配，获取每个df1行对应的最新累积统计
df1_result <- df2[df1, on = .(ID, Date <= Date), mult = "last",
                  .(ID, Date = i.Date, Var1, Mean_Var2 = Cum_Mean_Var2, Mean_Var3 = Cum_Mean_Var3, N = Cum_N)]

# 处理无匹配的情况
df1_result[, `:=`(
  Mean_Var2 = fifelse(is.na(Mean_Var2), 0, Mean_Var2),
  Mean_Var3 = fifelse(is.na(Mean_Var3), 0, Mean_Var3),
  N = fifelse(is.na(N), 0, N)
)]

示例输出 #

> df1_result
   ID       Date Var1 Mean_Var2 Mean_Var3 N
1:  1 2022-07-04  200      200.0      0.50 1
2:  1 2022-07-11   45      200.0      0.50 1
3:  1 2022-07-18   70      200.0      0.50 1
4:  1 2022-08-01   90      250.0      0.55 2
5:  2 2022-06-27   50      212.5      0.15 4
6:  2 2022-07-18   70      212.5      0.15 4
7:  2 2022-08-01   90      212.5      0.15 4
8:  3 2022-12-01   70        0.0      0.00 0
9:  3 2022-12-03   90        0.0      0.00 0

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