如何在R中基于Value列生成Number和Duration递增新列

需求说明 #

基于events数据框的Value列创建两个新列：

• Number列：每当Value出现新的0序列（从非0切换到0）时编号递增，非0位置填0
• Duration列：每个0序列从1开始逐次加1，非0位置填0

示例数据集 #

events <- data.frame(Frame = seq(from = 1001, to = 1033, by = 1),
                     Value = c(2.05, 0, 2.26, 2.38, 0, 0, 2.88, 0.32, 0.85, 2.85, 2.09, 0, 0, 0, 1.11, 0, 0,
                               0, 2.46, 2.85, 0, 0, 0.38, 1.91, 0, 0, 0, 2.23, 0, 0.48, 1.83, 0.23, 1.49))

理想输出 #

events_final <- data.frame(Frame = seq(from = 1001, to = 1033, by = 1),
                           Value = c(2.05, 0, 2.26, 2.38, 0, 0, 2.88, 0.32, 0.85, 2.85, 2.09, 0, 0, 0, 1.11, 0, 0,
                                     0, 2.46, 2.85, 0, 0, 0.38, 1.91, 0, 0, 0, 2.23, 0, 0.48, 1.83, 0.23, 1.49),
                           Number = c(0, 1, 0, 0, 2, 2, 0, 0, 0, 0, 0, 3, 3, 3, 0, 4, 4,
                                      4, 0, 0, 5, 5, 0, 0, 6, 6, 6, 0, 7, 0, 0, 0, 0),
                           Duration = c(0, 1, 0, 0, 1, 2, 0, 0, 0, 0, 0, 1, 2, 3, 0, 1, 2,
                                        3, 0, 0, 1, 2, 0, 0, 1, 2, 3, 0, 1, 0, 0, 0, 0))

问题尝试（未达预期） #

events %>%
  mutate(Number = ifelse(Value > 0, NA, 1),
         Duration = case_when(Value == 0 & lag(Value, n = 1) != 0 ~ 1,
                              Value == 0 & lag(Value, n = 1) == 0 ~ 2))

解决方案（tidyverse实现） #

library(tidyverse)

events_processed <- events %>%
  # 标记每个0序列的起始位置：当前为0且前一个非0，或第一行是0
  mutate(is_zero_start = Value == 0 & (lag(Value, default = -1) != 0)) %>%
  # 生成Number列：起始点累加编号，非0位置设为0
  mutate(Number = cumsum(is_zero_start)) %>%
  mutate(Number = ifelse(Value != 0, 0, Number)) %>%
  # 生成Duration列：按Number分组，组内0序列从1递增，非0位置设为0
  group_by(Number) %>%
  mutate(Duration = ifelse(Value != 0, 0, row_number())) %>%
  ungroup() %>%
  # 移除辅助列
  select(-is_zero_start)

# 验证结果与目标一致
all.equal(events_processed, events_final)

代码解释 #

1. is_zero_start：识别每个0序列的第一个元素，用lag(Value, default = -1)处理第一行的边界情况
2. Number列生成：通过cumsum()对起始点累加得到递增编号，再将非0位置的编号替换为0
3. Duration列生成：按Number分组后，用row_number()生成组内的递增序列，非0位置替换为0

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