在R中按条件累加连续行time_spent值生成gopast列

原始数据 #

subject,item,region_num,region,region_count,time_spent
ID_2965,14,1,begin,1,2109
ID_2965,14,2,ambig,1,206
ID_2965,14,3,location,1,920
ID_2965,14,4,disamb,1,127
ID_2965,14,5,follow,1,228
ID_2965,14,6,end,1,2238
ID_2965,14,5,follow,2,62
ID_2965,14,4,disamb,2,71
ID_2965,14,2,ambig,2,89
ID_2965,14,1,begin,2,2192
ID_2965,14,2,ambig,3,83
ID_2965,14,3,location,2,133
ID_2965,14,4,disamb,3,625
ID_2965,14,5,follow,3,57
ID_2965,14,6,end,2,1079
ID_2965,34,1,begin,1,57
ID_2965,34,2,ambig,1,224
ID_2965,34,1,begin,2,2136
ID_2965,34,2,ambig,2,98
ID_2965,34,3,location,1,127
ID_2965,34,4,disamb,1,415
ID_2965,34,5,follow,1,477
ID_2965,34,6,end,1,2445
ID_2965,34,5,follow,2,70
ID_2965,34,3,location,2,72
ID_2965,34,1,begin,3,3160
ID_2965,34,2,ambig,3,96
ID_2965,34,4,disamb,2,85
ID_2965,34,5,follow,3,213
ID_2965,34,6,end,2,482
ID_2965,34,5,follow,4,100
ID_2965,34,6,end,3,157

需求说明 #

需要生成gopast列，规则如下：

• 仅对region_count==1的行生效，其余行设为NA
• 对目标行，在相同subject和item范围内，找到后续所有行中region_num≤当前行region_num的行，直到遇到第一个region_num>当前行的行
• 若无符合条件的后续行，gopast等于当前行的time_spent
• 若有符合条件的后续行，累加当前行和这些后续行的time_spent

期望结果 #

subject,item,region_num,region,region_count,time_spent,gopast
ID_2965,14,1,begin,1,2109,2109
ID_2965,14,2,ambig,1,206,206
ID_2965,14,3,location,1,920,920
ID_2965,14,4,disamb,1,127,127
ID_2965,14,5,follow,1,228,228
ID_2965,14,6,end,1,2238,6629
ID_2965,14,5,follow,2,62,NA
ID_2965,14,4,disamb,2,71,NA
ID_2965,14,2,ambig,2,89,NA
ID_2965,14,1,begin,2,2192,NA
ID_2965,14,2,ambig,3,83,NA
ID_2965,14,3,location,2,133,NA
ID_2965,14,4,disamb,3,625,NA
ID_2965,14,5,follow,3,57,NA
ID_2965,14,6,end,2,1079,NA
ID_2965,34,1,begin,1,57,57
ID_2965,34,2,ambig,1,224,2458
ID_2965,34,1,begin,2,2136,NA
ID_2965,34,2,ambig,2,98,NA
ID_2965,34,3,location,1,127,127
ID_2965,34,4,disamb,1,415,415
ID_2965,34,5,follow,1,477,477
ID_2965,34,6,end,1,2445,6880
ID_2965,34,5,follow,2,70,NA
ID_2965,34,3,location,2,72,NA
ID_2965,34,1,begin,3,3160,NA
ID_2965,34,2,ambig,3,96,NA
ID_2965,34,4,disamb,2,85,NA
ID_2965,34,5,follow,3,213,NA
ID_2965,34,6,end,2,482,NA
ID_2965,34,5,follow,4,100,NA
ID_2965,34,6,end,3,157,NA

用户现有问题代码 #

用户编写的函数无法在遇到第一个region_num大于当前行的后续行时停止累加，代码如下：

add_gopast <- function(df) {
  df$gopast <- 0
  for (i in 1:nrow(df)) {
    time_spent <- df$time_spent[i]
    region_num <- df$region_num[i]
    region <- df$region[i]
    lower_region_rows <- which(df$rowN > df$rowN[i] & df$region_num <= region_num & df$subject == df$subject[i] & df$item == df$item[i]& df$region_count >= df$region_count[i])
    if (length(lower_region_rows) > 0) {
      if (df$regout[i] == 0) {
        gopast_value <- time_spent
      } else if (df$regout[i] == 1) {
        gopast_value <- time_spent + sum(df$time_spent[lower_region_rows])
      }
      df$gopast[i] <- gopast_value
    } else {
      df$gopast[i] <- time_spent
    }
  }
  return(df)
}

解决方案 #

使用dplyr包实现分组处理，核心是对每个目标行，找到后续行中直到第一个region_num超过当前值的位置，再进行累加：

library(dplyr)

# 给数据添加行号，用于定位后续行的顺序
df <- df %>% mutate(row_id = row_number())

# 分组计算gopast列
df_result <- df %>%
  group_by(subject, item) %>%
  mutate(
    gopast = case_when(
      region_count != 1 ~ NA_real_,
      TRUE ~ {
        current_rn <- region_num
        current_row <- row_id
        # 筛选当前分组中在当前行之后的所有行
        subsequent_rows <- filter(cur_data(), row_id > current_row)
        
        if(nrow(subsequent_rows) == 0){
          # 无后续行，直接返回当前time_spent
          time_spent
        } else {
          # 找到第一个region_num大于当前值的行的位置
          first_exceed_pos <- which(subsequent_rows$region_num > current_rn)[1]
          if(is.na(first_exceed_pos)){
            # 所有后续行都符合条件，累加全部
            sum(time_spent, subsequent_rows$time_spent)
          } else {
            # 只累加第一个超过位置之前的行
            sum(time_spent, subsequent_rows$time_spent[1:(first_exceed_pos - 1)])
          }
        }
      }
    )
  ) %>%
  ungroup() %>%
  select(-row_id) # 移除临时行号列

代码说明 #

1. 添加行号：确保后续行的顺序不会因分组打乱，准确定位当前行之后的记录
2. 分组处理：按subject和item分组，保证计算范围仅在同一组内
3. 条件分支：
   • 对region_count !=1的行直接赋值NA
   • 对目标行，先筛选出所有后续行，再判断是否存在region_num超过当前值的行
   • 根据判断结果，选择累加全部后续行或第一个超过位置之前的行
4. 清理临时列：移除用于定位的row_id，还原原始数据结构

运行上述代码后，即可得到符合需求的gopast列。

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