基于多字段及日期匹配合并DataFrame并累加指定数值

核心思路 #

把df1拆成两组：一组是Date等于StartDate的记录，另一组是Date等于EndDate的记录，分别和df2做匹配关联，把对应的n值累加后，再合并回原df2的Totals字段里。

代码实现 #

先确保你已经加载了dplyr包（未安装的话先执行install.packages("dplyr")）：

library(dplyr)

# 处理Date匹配StartDate的情况，计算每个分组的n总和
start_match <- df1 %>%
  inner_join(df2, by = c("Location", "Sub Location")) %>%
  filter(Date == StartDate) %>%
  group_by(Location, `Sub Location`, StartDate, EndDate) %>%
  summarise(start_n = sum(n), .groups = "drop")

# 处理Date匹配EndDate的情况
end_match <- df1 %>%
  inner_join(df2, by = c("Location", "Sub Location")) %>%
  filter(Date == EndDate) %>%
  group_by(Location, `Sub Location`, StartDate, EndDate) %>%
  summarise(end_n = sum(n), .groups = "drop")

# 合并匹配结果到原df2，完成Totals累加
result_df <- df2 %>%
  left_join(start_match, by = c("Location", "Sub Location", "StartDate", "EndDate")) %>%
  left_join(end_match, by = c("Location", "Sub Location", "StartDate", "EndDate")) %>%
  # 填充缺失值为0，避免NA干扰计算
  mutate(
    start_n = ifelse(is.na(start_n), 0, start_n),
    end_n = ifelse(is.na(end_n), 0, end_n),
    Totals = Totals + start_n + end_n
  ) %>%
  # 移除临时生成的字段
  select(-start_n, -end_n)

为什么不用循环/ifelse？ #

循环处理大数据量时效率极低，还容易因为索引匹配出错；单纯的ifelse很难处理多条件的分组累加场景。用dplyr的关联+分组操作，逻辑清晰且高效，能避免手动循环的索引bug，同时支持重复分组的正确累加。

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