如何在R中基于起止日期计算每日活跃rate的求和值？

我们有一个包含rate、startDate（起始日期）、endDate（结束日期）的数据框，需要计算指定日期列表中每一天的所有活跃rate总和——活跃指的是日期处于startDate（含）到endDate（不含）之间。

数据示例 #

df <- data.frame( 
  startDate=c('2023-11-28', '2023-11-29', '2023-12-01', '2023-12-03'), 
  endDate=c('2023-11-30', '2023-12-06', '2023-12-05', '2023-12-05'), 
  rate=c(1, 2, 3, 5)
)
dates <- seq(as.Date('2023-11-27'), by = "day", length.out = 10) %>% map_df(as_tibble) %>% rename(myDate=value)

可视化示意 #

比如2023-11-29当天，第一行（rate=1）和第二行（rate=2）的时间段都包含该日期，所以当天的活跃rate总和是1+2=3。

问题重现 #

尝试用以下代码计算时，出现警告longer object length is not a multiple of shorter object length，且结果不符合预期：

dates %>% mutate(sum = sum(df$rate[myDate>=df$startDate & myDate<df$endDate],na.rm = TRUE))

问题原因 #

myDate是dates中的列（长度10），而df$startDate/df$endDate是df中的列（长度4）。R在处理不等长向量的比较时，会重复较短的向量来匹配较长的，导致日期和时间段的配对完全错误，最终求和结果也不是按每个日期单独计算的。

解决方法 #

方法1：使用rowwise()逐行计算 #

先把所有日期列转为Date类型（原数据中是字符格式，必须先转换），再逐行对每个myDate筛选符合条件的rate并求和：

library(dplyr)

# 转换日期格式
df <- df %>% 
  mutate(across(c(startDate, endDate), as.Date))
dates <- dates %>% 
  mutate(myDate = as.Date(myDate))

# 逐行计算每日活跃rate总和
dates <- dates %>%
  rowwise() %>%
  mutate(sum_rate = sum(df$rate[myDate >= df$startDate & myDate < df$endDate], na.rm = TRUE)) %>%
  ungroup()

方法2：使用purrr::map_dbl()遍历日期 #

用map_dbl对每个myDate单独处理，逻辑和逐行计算一致：

library(dplyr)
library(purrr)

# 转换日期格式
df <- df %>% mutate(across(c(startDate, endDate), as.Date))
dates <- dates %>% mutate(myDate = as.Date(myDate))

# 遍历每个日期计算总和
dates <- dates %>%
  mutate(sum_rate = map_dbl(myDate, ~sum(df$rate[.x >= df$startDate & .x < df$endDate], na.rm = TRUE)))

方法3：非等值连接（适合大数据场景） #

用dplyr的非等值连接，先把每个日期和所有包含它的时间段关联，再按日期分组求和，效率比逐行计算更高：

library(dplyr)

# 转换日期格式
df <- df %>% mutate(across(c(startDate, endDate), as.Date))
dates <- dates %>% mutate(myDate = as.Date(myDate))

# 非等值连接+分组求和
dates <- dates %>%
  left_join(df, by = join_by(myDate >= startDate, myDate < endDate)) %>%
  group_by(myDate) %>%
  summarise(sum_rate = sum(rate, na.rm = TRUE)) %>%
  right_join(dates, by = "myDate") %>%
  mutate(sum_rate = replace_na(sum_rate, 0))

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