R语言中日期关联的复杂累计值求和问题

可以通过分组标记和累计求和的方式实现需求，核心思路是先识别出diff为0的区间，再计算每个区间对应的累计唯一值总和，最后映射到对应行中。以下是具体实现代码：

步骤1：加载依赖并定义原始数据集 #

library(dplyr)

df <- tibble(
  date= seq.Date(as.Date("2021-01-01"), as.Date("2022-02-01"), by = "month"),
  val1 = c(105, 105, 105, 125, 125, 125, 125, 132, 132, 132, 135, 150, 150, 150),
  val2 = c(100, 100, 100, 125, 125, 125, 125, 125, 125, 125, 125, 150, 150, 150),
  diff = val1-val2)

步骤2：生成diff_calc列 #

output <- df %>%
  # 标记0区间的起始位置：当diff从非0变为0时标记为1
  mutate(zero_start = ifelse(diff == 0 & lag(diff, default = 1) != 0, 1, 0)) %>%
  # 按0区间起始点分组，划分出不同的计算段
  mutate(group_id = cumsum(zero_start)) %>%
  group_by(group_id) %>%
  # 计算当前分组对应的累计唯一diff值总和
  mutate(diff_calc = sum(unique(ifelse(diff != 0 & group_id > 0, diff, 0)), na.rm = TRUE)) %>%
  ungroup() %>%
  # 第一个非0区间的diff_calc设为0
  mutate(diff_calc = ifelse(group_id == 0, 0, diff_calc)) %>%
  # 移除中间辅助列
  select(-zero_start, -group_id)

验证结果 #

运行上述代码后，output的diff_calc列与需求结果完全一致：

# 查看output
output
#> # A tibble: 14 × 5
#>    date       val1  val2  diff diff_calc
#>    <date>    <dbl> <dbl> <dbl>     <dbl>
#>  1 2021-01-01   105   100     5         0
#>  2 2021-02-01   105   100     5         0
#>  3 2021-03-01   105   100     5         0
#>  4 2021-04-01   125   125     0         5
#>  5 2021-05-01   125   125     0         5
#>  6 2021-06-01   125   125     0         5
#>  7 2021-07-01   125   125     0         5
#>  8 2021-08-01   132   125     7         5
#>  9 2021-09-01   132   125     7         5
#> 10 2021-10-01   132   125     7         5
#> 11 2021-11-01   135   125    10         5
#> 12 2021-12-01   150   150     0        22
#> 13 2022-01-01   150   150     0        22
#> 14 2022-02-01   150   150     0        22

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