dplyr中使用if_else实现分组内计数重置为1的问题求助

需求概述 #

• 按id分组，计数不跨组
• 当deaths ≥ 25时，civ_int设为1，同时low_years重置为0
• 当civ_int回到0时，low_years从1开始重新递增计数

现有问题 #

当前代码在civ_int从1切换回0时，low_years未重置为1，而是继续累加（如示例中1996年low_years从23开始，不符合预期）

问题代码 #

merged <- merged %>%
  mutate(civ_int = if_else(
    deaths >= 25, 1, 0
  )) %>%
  group_by(id) %>%
  mutate(low_years = as.numeric(row_number()
  )) %>%
  mutate(low_years = cumsum(if_else(
    civ_int == 0, 1, 0
  ))) %>%
  mutate(low_years = if_else(
    civ_int == 1, 0, low_years
  )) %>%
  ungroup()

问题示例数据 #

# A tibble: 20 × 5
#      id  year deaths civ_int low_years
#   <dbl> <dbl>  <dbl>   <dbl>     <dbl>
# 1     1  1983      0       0        17
# 2     1  1984      0       0        18
# 3     1  1985      0       0        19
# 4     1  1986      0       0        20
# 5     1  1987      0       0        21
# 6     1  1988      0       0        22
# 7     1  1989    363       1         0
# 8     1  1990    522       1         0
# 9     1  1991    308       1         0
#10     1  1992    273       1         0
#11     1  1993    132       1         0
#12     1  1994    226       1         0
#13     1  1995     74       1         0
#14     1  1996      2       0        23
#15     1  1997      2       0        24
#16     1  1998      1       0        25
#17     1  1999      0       0        26
#18     1  2000      0       0        27
#19     1  2001      0       0        28
#20     1  2002      2       0        29

解决方案 #

核心是先识别每个id内连续的civ_int=0区间，再在每个区间内独立计数：

merged <- merged %>%
  # 第一步：生成civ_int标记
  mutate(civ_int = if_else(deaths >= 25, 1, 0)) %>%
  group_by(id) %>%
  # 第二步：标记每个连续的civ_int=0区间
  # 当前一行是1且当前行是0时，生成新的区间编号
  mutate(interval = cumsum(lag(civ_int, default = 1) == 1 & civ_int == 0)) %>%
  # 第三步：在每个id+interval分组内计数
  group_by(id, interval) %>%
  mutate(low_years = if_else(civ_int == 0, row_number(), 0)) %>%
  # 清理临时列并取消分组
  ungroup() %>%
  select(-interval)

预期结果 #

处理后示例数据的1996年起low_years会从1开始递增：

# A tibble: 20 × 5
#      id  year deaths civ_int low_years
#   <dbl> <dbl>  <dbl>   <dbl>     <dbl>
# 1     1  1983      0       0        17
# 2     1  1984      0       0        18
# 3     1  1985      0       0        19
# 4     1  1986      0       0        20
# 5     1  1987      0       0        21
# 6     1  1988      0       0        22
# 7     1  1989    363       1         0
# 8     1  1990    522       1         0
# 9     1  1991    308       1         0
#10     1  1992    273       1         0
#11     1  1993    132       1         0
#12     1  1994    226       1         0
#13     1  1995     74       1         0
#14     1  1996      2       0         1
#15     1  1997      2       0         2
#16     1  1998      1       0         3
#17     1  1999      0       0         4
#18     1  2000      0       0         5
#19     1  2001      0       0         6
#20     1  2002      2       0         7

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