如何基于另一数据框的日期区间计算目标数据框的数值总和？

我有两个R语言数据框，需要计算data数据框中日期落在date数据框每行的dat1与dat2区间内的所有values值的总和，区间支持>=和<=的逻辑（即包含边界的between判断）。

数据示例 #

data <- data.frame(
  dat1 = c('2021-01-01', '2021-01-02', '2021-01-04', '2021-01-10',
           '2021-02-01', '2021-02-19', '2021-02-20', '2021-05-10',
           '2021-05-15', '2021-05-30', '2021-06-04', '2021-06-10',
           '2021-06-11', '2021-06-12', '2021-06-13', '2021-06-14',
           '2021-06-15'),
  values = c(34,56,78,32,2,3,5,4,6,9,8,7,1,2,5,4,7)
)

date <- data.frame(
  dat1 = c('2021-01-01', '2021-04-05', '2021-06-04', '2021-06-10'),
  dat2 = c('2021-01-10', '2021-04-16', '2021-06-09', '2021-06-20')
)

预期输出 #

dat1       dat2 res
1 2021-01-01 2021-01-10 200
2 2021-04-05 2021-04-16  NA
3 2021-06-04 2021-06-09   8
4 2021-06-10 2021-06-20  26

解决方案 #

首先需要将所有日期列转换为Date类型，避免字符串比较导致的错误，以下是三种常用实现方式：

1. Base R 实现 #

# 转换日期格式
data$dat1 <- as.Date(data$dat1)
date$dat1 <- as.Date(date$dat1)
date$dat2 <- as.Date(date$dat2)

# 遍历计算每行区间内的values总和
date$res <- sapply(1:nrow(date), function(i) {
  match_idx <- data$dat1 >= date$dat1[i] & data$dat1 <= date$dat2[i]
  total <- sum(data$values[match_idx])
  if (total == 0) NA else total
})

# 输出结果
print(date)

2. dplyr + lubridate 实现 #

需先安装并加载依赖包：

library(dplyr)
library(lubridate)

date <- date %>%
  # 转换日期格式
  mutate(across(c(dat1, dat2), ymd)) %>%
  # 按行处理
  rowwise() %>%
  mutate(
    # 计算符合条件的values总和
    res = sum(data$values[ymd(data$dat1) >= dat1 & ymd(data$dat1) <= dat2], na.rm = FALSE),
    # 无匹配时替换为NA
    res = ifelse(res == 0, NA, res)
  ) %>%
  ungroup()

# 输出结果
print(date)

3. data.table 高效实现 #

适合大数据量场景，性能更优：

library(data.table)

# 转换为data.table并处理日期
setDT(data)[, dat1 := as.Date(dat1)]
setDT(date)[, c("dat1", "dat2") := lapply(.SD, as.Date), .SDcols = c("dat1", "dat2")]

# 非等连接计算区间总和
date[, res := data[date, on = .(dat1 >= dat1, dat1 <= dat2), sum(values), by = .EACHI]$V1]
# 无匹配时替换为NA
date[res == 0, res := NA]

# 输出结果
print(date)

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