为DataFrame中连续TRUE值分配组ID并计算check_time总和

1. 生成连续TRUE组的ID #

可以用dplyr包实现，核心思路是通过cumsum()识别每个连续TRUE组的起始点，再将非TRUE行的ID设为NA：

library(dplyr)

# 生成组ID
mydata_with_id <- mydata %>%
  mutate(
    # 标记每个连续TRUE组的起始行
    start_group = below & (lag(below, default = FALSE) == FALSE),
    # 生成组序号
    ID = cumsum(start_group)
  ) %>%
  # 将非TRUE行的ID设为NA
  mutate(ID = ifelse(below, ID, NA)) %>%
  select(-start_group) # 移除中间辅助列

运行后得到的结果与期望输出一致：

# A tibble: 10 × 10
      Nr   sgv Date                 Year Weekday  Week  mmol check_time   below    ID
   <int> <int> <dttm>              <dbl>   <dbl> <dbl> <dbl> <drtn>       <lgl> <dbl>
 1     1    72 2020-11-21 15:41:35  2020       7    47  4.00 294.695 secs FALSE    NA
 2     2    72 2020-11-21 15:41:40  2020       7    47  4.00   5.306 secs FALSE    NA
 3     3    68 2020-11-21 15:46:40  2020       7    47  3.77 300.287 secs TRUE      1
 4     4    62 2020-11-21 15:51:40  2020       7    47  3.44 300.000 secs TRUE      1
 5     5    83 2020-11-21 15:56:35  2020       7    47  4.61 294.407 secs FALSE    NA
 6     6    83 2020-11-21 15:56:40  2020       7    47  4.61   5.594 secs FALSE    NA
 7     7    86 2020-11-21 16:01:35  2020       7    47  4.77 294.406 secs FALSE    NA
 8     8    86 2020-11-21 16:01:40  2020       7    47  3.8    5.417 secs TRUE      2
 9     9    85 2020-11-21 16:06:35  2020       7    47  4.72 294.583 secs FALSE    NA
10    10    85 2020-11-21 16:06:40  2020       7    47  4.72   5.243 secs FALSE    NA

2. 按组ID计算check_time总和 #

基于生成的ID列，直接分组求和即可：

check_time_summary <- mydata_with_id %>%
  filter(!is.na(ID)) %>% # 只保留有ID的行
  group_by(ID) %>%
  summarise(total_check_time = sum(check_time)) %>%
  ungroup()

运行结果：

# A tibble: 2 × 2
     ID total_check_time
  <dbl>           <drtn>
1     1        600.287 secs
2     2          5.417 secs

针对60万行数据的优化建议 #

如果数据量达到60万行，使用data.table包会比dplyr更高效，对应的实现代码如下：

library(data.table)

# 转换为data.table格式
setDT(mydata)

# 生成组ID
mydata[, start_group := below & shift(below, fill = FALSE) == FALSE]
mydata[, ID := cumsum(start_group)]
mydata[!below, ID := NA_integer_]
mydata[, start_group := NULL]

# 计算每组check_time总和
check_time_summary <- mydata[!is.na(ID), .(total_check_time = sum(check_time)), by = ID]

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