如何在R中按ID匹配并对指定日期区间内的dataframe2金额求和

嘿，我懂你要实现的需求了——给dataframe1新增一列，统计对应ID下dataframe2中日期落在date1和date2区间内的amount总和对吧？之前用dplyr结合lubridate没成功，大概率是区间匹配后的逻辑没理顺，我给你几个靠谱的实现方法，附测试代码，你可以直接跑：

1. 先模拟测试数据 #

先搞两个测试用的dataframe，方便你验证代码效果：

library(dplyr)
library(lubridate)

# 模拟dataframe1：ID+日期区间
df1 <- tibble(
  ID = c(1, 2, 3),
  date1 = ymd(c("2023-01-01", "2023-02-01", "2023-03-01")),
  date2 = ymd(c("2023-01-31", "2023-02-28", "2023-03-31"))
)

# 模拟dataframe2：ID+日期+金额（每个ID多条记录）
df2 <- tibble(
  ID = rep(c(1,2,3), each=5),
  date = ymd(c("2023-01-05", "2023-01-15", "2023-02-02", "2023-01-25", "2023-03-10",
               "2023-02-05", "2023-02-15", "2023-01-20", "2023-02-25", "2023-03-05",
               "2023-03-05", "2023-03-15", "2023-02-20", "2023-03-25", "2023-04-05")),
  amount = c(10, 20, 30, 40, 50, 15, 25, 35, 45, 55, 12, 22, 32, 42, 52)
)

2. 方法一：关联+过滤+分组求和（最直观） #

这个方法逻辑清晰，容易调试，适合大多数场景：

result <- df1 %>%
  # 按ID关联两个表，把df2的所有记录和df1对应行匹配
  left_join(df2, by = "ID") %>%
  # 筛选出date落在当前行date1-date2区间内的记录
  filter(between(date, date1, date2)) %>%
  # 按df1的每一行（ID+date1+date2）分组
  group_by(ID, date1, date2) %>%
  # 对每组的amount求和，.na.rm=TRUE避免空值报错
  summarise(total_amount = sum(amount, na.rm = TRUE), .groups = "drop") %>%
  # 确保保留df1的所有行（哪怕没有匹配的df2记录）
  right_join(df1, by = c("ID", "date1", "date2")) %>%
  # 没有匹配的行填充0，避免出现NA
  mutate(total_amount = replace_na(total_amount, 0))

print(result)

3. 方法二：逐行处理（适合大数据集） #

如果df2数据量特别大，全表关联会占用太多内存，用逐行处理更高效：

result <- df1 %>%
  # 开启逐行处理模式
  rowwise() %>%
  # 对每一行，筛选df2中同ID且日期在区间内的记录，求和amount
  mutate(total_amount = df2 %>%
           filter(ID == !!cur_data()$ID, date >= date1, date <= date2) %>%
           pull(amount) %>%
           sum(na.rm = TRUE)) %>%
  # 关闭逐行模式，回到正常dataframe结构
  ungroup()

print(result)

4. 补充：用lubridate的interval实现 #

你之前尝试过用lubridate创建区间列，其实也可以这么写，核心是用%within%判断日期是否在区间内：

result <- df1 %>%
  left_join(df2, by = "ID") %>%
  # 创建日期区间列
  mutate(date_interval = interval(date1, date2)) %>%
  # 筛选日期在区间内的记录
  filter(date %within% date_interval) %>%
  group_by(ID, date1, date2) %>%
  summarise(total_amount = sum(amount, na.rm = TRUE), .groups = "drop") %>%
  right_join(df1, by = c("ID", "date1", "date2")) %>%
  mutate(total_amount = replace_na(total_amount, 0))

之前失败可能是没做right_join导致丢失了df1的原始行，或者没处理空值的情况，试试上面的写法应该就能解决啦～

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