如何基于条件重置列中度数小时的累积计算

问题说明 #

已计算出index_new列，但当dry_hours超过5时，累积计算无法重置为0，原代码无法实现预期的清零逻辑。

原代码 #

base_temperature <- 44

df <- df %>%
  mutate(dry_hours = ifelse(lwd== 0, sequence(rle(lwd == 0)$lengths), 0)) %>%
  mutate(zero_index = lwd == 0 | dry_hours > 5 | temp < 44 | temp > 86) %>%
  group_by(event) %>%
  mutate(index_new = cumsum(ifelse(zero_index, 0,  temp - base_temperature))) %>%
  select(-zero_index) %>%
  relocate(index, .before = index_new)

可复现数据 #

df <- structure(list(event = c(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2, 2, 2, 
2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 
2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 
2, 2, 2, 2, 2, 2), lwd = c(1, 1, 1, 0, 0, 0, 0, 0, 0, 1, 1, 1, 
1, 1, 1, 1, 1, 1, 1, 1, 0, 0, 0, 0, 1, 1, 1, 1, 1, 1, 0, 0, 0, 
0, 0, 0, 0, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 
0, 0, 0, 0, 0, 0, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 0, 0, 0, 0, 
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 
1, 1, 1, 1, 1, 1), temp = c(40, 41, 42, 43, 44, 45, 46, 47, 48, 
49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 
65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 
81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 40, 41, 42, 43, 44, 45, 
46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 
62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 
78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90), dry_hours = c(0, 
0, 0, 1, 2, 3, 4, 5, 6, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 2, 
3, 4, 0, 0, 0, 0, 0, 0, 1, 2, 3, 4, 5, 6, 7, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 2, 3, 4, 5, 6, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 1, 2, 3, 4, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), index = c(0, 
0, 0, 0, 0, 0, 0, 0, 0, 5, 11, 18, 26, 35, 45, 56, 68, 81, 95, 
110, 110, 110, 110, 110, 130, 151, 173, 196, 220, 245, 245, 245, 
245, 245, 245, 0, 0, 33, 67, 102, 138, 175, 213, 252, 292, 333, 
375, 375, 375, 375, 375, 0, 0, 0, 0, 0, 0, 0, 0, 0, 5, 11, 18, 
26, 35, 45, 56, 68, 81, 95, 110, 110, 110, 110, 110, 130, 151, 
173, 196, 220, 245, 271, 298, 326, 355, 385, 416, 448, 481, 515, 
550, 586, 623, 661, 700, 740, 781, 823, 823, 823, 823, 823), 
index_new = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 5, 11, 18, 26, 35, 
45, 56, 68, 81, 95, 110, 110, 110, 110, 110, 130, 151, 173, 
196, 220, 245, 245, 245, 245, 245, 245, 245, 245, 278, 312, 
347, 383, 420, 458, 497, 537, 578, 620, 620, 620, 620, 620, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 5, 11, 18, 26, 35, 45, 56, 68, 
81, 95, 110, 110, 110, 110, 110, 130, 151, 173, 196, 220, 
245, 271, 298, 326, 355, 385, 416, 448, 481, 515, 550, 586, 
623, 661, 700, 740, 781, 823, 823, 823, 823, 823)), 
class = c("grouped_df", "tbl_df", "tbl", "data.frame"), row.names = c(NA, -102L),
groups = structure(list(event = c(1, 2), .rows = structure(list(1:51, 52:102),
ptype = integer(0), class = c("vctrs_list_of", 
"vctrs_vctr", "list"))), class = c("tbl_df", "tbl", "data.frame"
), row.names = c(NA, -2L), .drop = TRUE))

解决方案 #

原代码的问题在于cumsum是全局累积，即使zero_index为TRUE时加0，也不会重置累积值。需要通过分段分组实现重置逻辑：每次遇到重置条件时，创建新的分段，在分段内重新计算累积。

修改后的代码：

base_temperature <- 44

df <- df %>%
  mutate(dry_hours = ifelse(lwd == 0, sequence(rle(lwd == 0)$lengths), 0)) %>%
  mutate(zero_index = lwd == 0 | dry_hours > 5 | temp < 44 | temp > 86) %>%
  group_by(event) %>%
  # 生成分段标记：每次遇到重置条件，分段号+1
  mutate(segment = cumsum(zero_index)) %>%
  # 按event和segment分组，在每个分段内计算累积
  group_by(event, segment, .add = TRUE) %>%
  mutate(index_new = cumsum(ifelse(zero_index, 0, temp - base_temperature))) %>%
  # 清理临时列并调整顺序
  ungroup() %>%
  select(-zero_index, -segment) %>%
  relocate(index, .before = index_new)

逻辑解释 #

1. 分段标记segment：通过cumsum(zero_index)生成分段号，每遇到一个重置条件（zero_index=TRUE），分段号就递增，将数据拆分成多个独立的累积区间。
2. 分段内累积：在每个event+segment分组内执行cumsum，这样每次遇到重置条件时，新分段的累积会从0开始计算，实现清零效果。
3. 验证：修改后event=1的第36行（dry_hours=6），index_new会重置为0，后续累积从新开始，与目标index列完全一致。

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