如何基于日期条件计算DataFrame累计值？（模拟Excel SUMIFS）

背景与数据生成 #

用户通过以下R代码生成包含日期区间与数值的tibble：

library(tidyverse)
set.seed(1)
start <- mdy("01/01/2022")
end <- start + as.difftime(99, units = "days")
days <- seq(from = start, to = end, by = 1)
days <- sample(days, 100, replace = T)
expiry <- seq(from = start, to = end + 300, by  = 1)
expiry <- sample(expiry, 100, replace = T)
x <- sample(1:10, 100, replace = T)
df <- tibble(days = days, expiry = expiry, value = x)
df <- df %>% filter(days != expiry) %>% arrange(days)
df$expiry[2] = mdy("01-02-2022") 
df

生成的数据前10行如下：

# A tibble: 100 × 3
   days       expiry     value
   <date>     <date>     <int>
 1 2022-01-01 2022-07-23    10
 2 2022-01-01 2022-01-02     3
 3 2022-01-02 2022-10-27     4
 4 2022-01-06 2022-12-22     2
 5 2022-01-07 2022-03-19     5
 6 2022-01-10 2023-01-08     2
 7 2022-01-13 2022-06-23     8
 8 2022-01-13 2022-05-02     5
 9 2022-01-14 2022-08-02     5
10 2022-01-14 2022-03-06     1
# ℹ 90 more rows
# ℹ Use `print(n = ...)` to see more rows

需求说明 #

需要生成一个新的tibble，包含2022年的每一天日期，以及对应日期下所有满足days <= 该日期且expiry >= 该日期的value列之和，模拟Excel的SUMIFS函数逻辑。

原代码问题分析 #

用户尝试用循环实现但结果全为0，问题出在：

• 变量名不一致：定义了days但循环用day，初始化s时引用未定义的day导致长度错误
• 循环遍历对象错误：for (v in df)是遍历df的列而非行，每次循环的v是整列向量，不是单行数据
• 索引错误：i初始为0，而R的向量索引从1开始，导致第一次循环赋值给无效的s[0]
• 条件判断逻辑错误：用列向量和单个日期比较得到布尔向量，无法正确触发单行求和判断

正确实现方法 #

方法1：向量化操作（高效推荐） #

利用R的向量运算特性，避免循环直接计算每个日期的条件和：

library(tidyverse)

# 生成2022年所有日期
target_days <- seq(from = mdy("01/01/2022"), to = mdy("12/31/2022"), by = 1)

# 对每个日期计算符合条件的value之和
sum_values <- map_dbl(target_days, function(d) {
  df %>% 
    filter(days <= d, expiry >= d) %>% 
    pull(value) %>% 
    sum()
})

# 生成结果tibble
result <- tibble(day = target_days, sum_value = sum_values)

# 查看前10行
result %>% slice(1:10)

方法2：dplyr交叉连接（tidyverse风格） #

通过交叉连接合并目标日期与原数据，再分组求和：

library(tidyverse)

target_days <- tibble(day = seq(mdy("01/01/2022"), mdy("12/31/2022"), by = 1))

result <- target_days %>%
  cross_join(df) %>%
  filter(days <= day, expiry >= day) %>%
  group_by(day) %>%
  summarise(sum_value = sum(value)) %>%
  ungroup()

result %>% slice(1:10)

方法3：修复原循环代码 #

如果坚持用循环，修正变量、遍历对象和索引问题：

library(tidyverse)

target_days <- seq(from = mdy("01/01/2022"), to = mdy("12/31/2022"), by = 1)
s <- rep(0, length(target_days))

for (i in seq_along(target_days)) {
  d <- target_days[i]
  total <- 0
  for (j in seq_len(nrow(df))) {
    row <- df[j, ]
    if (row$days <= d && row$expiry >= d) {
      total <- total + row$value
    }
  }
  s[i] <- total
}

result <- tibble(day = target_days, sum_value = s)
result %>% slice(1:10)

验证结果 #

以上方法生成的前10行结果均符合预期：

# A tibble: 10 × 2
   day        sum_value
   <date>         <dbl>
 1 2022-01-01        13
 2 2022-01-02        17
 3 2022-01-03        14
 4 2022-01-04        14
 5 2022-01-05        14
 6 2022-01-06        16
 7 2022-01-07        21
 8 2022-01-08        21
 9 2022-01-09        21
10 2022-01-10        23

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