使用dplyr按条件分组汇总时长的技术求助

需要基于分组内行的特定条件（Color为"blue"），计算每组的总时长。

原始数据 #

Group_ID = c(1, 1, 1, 1, 1, 2, 2, 2, 2, 2)
Date = c("2022-01-01", "2022-01-01", "2022-01-01", "2022-01-01", "2022-01-01", "2022-01-02", "2022-01-02", "2022-01-02", "2022-01-02", "2022-01-02")
Time = c("12:01:10", "12:05:22", "12:07:30", "12:30:20", "12:37:20",  "11:10:01", "11:48:19", "11:50:18", "12:08:22", "12:08:57")
Color = c("blue", "red", "red", "green", NA, "blue", "blue", "green", "red", NA)
Status = c("go", "go", "go", "go", "end", "go", "go", "go", "go", "end")

df = data.frame(Group_ID, Date, Time, Color, Status)

数据预览：

# Group_ID       Date     Time Color Status
# 1         1 2022-01-01 12:01:10  blue     go
# 2         1 2022-01-01 12:05:22   red     go
# 3         1 2022-01-01 12:07:30   red     go
# 4         1 2022-01-01 12:30:20 green     go
# 5         1 2022-01-01 12:37:20  <NA>    end
# 6         2 2022-01-02 11:10:01  blue     go
# 7         2 2022-01-02 11:48:19  blue     go
# 8         2 2022-01-02 11:50:18 green     go
# 9         2 2022-01-02 12:08:22   red     go
# 10        2 2022-01-02 12:08:57  <NA>    end

问题分析 #

你尝试的代码输出不符合预期，核心问题：

• summarise中使用Color == "blue"会保留分组内的每一行（因为Color是行级变量），导致输出行数与原数据一致，而非每组一行。
• sum(duration_sec, na.rm=T)计算的是整个分组的时长总和，而非仅筛选Color为"blue"的行对应的时长。

你的尝试代码：

df %>% 
  mutate(duration_sec = case_when(
    lead(Group_ID) == Group_ID ~ as.numeric(difftime(as.POSIXct(lead(Time), format="%H:%M:%S"), as.POSIXct(Time, format="%H:%M:%S")))
  )
  ) %>% 
  group_by(Group_ID) %>% 
  summarise(
    blue_duration = case_when(
      Color == "blue" ~ sum(duration_sec, na.rm=T)
      )
    )

正确解决方案 #

实现代码 #

library(dplyr)

df %>%
  # 合并日期和时间为完整的POSIX时间，避免单独处理时间的潜在问题
  mutate(datetime = as.POSIXct(paste(Date, Time), format = "%Y-%m-%d %H:%M:%S")) %>%
  group_by(Group_ID) %>%
  # 计算当前行到下一行的时间差（秒），仅同组内计算
  mutate(duration_sec = as.numeric(lead(datetime) - datetime)) %>%
  # 筛选Color为blue的行，再分组求和
  filter(Color == "blue") %>%
  summarise(blue_duration = sum(duration_sec, na.rm = TRUE))

输出结果 #

# Group_ID blue_duration
#      <dbl>         <dbl>
# 1        1           252
# 2        2          2417

代码说明 #

1. 合并日期时间：将Date和Time合并为完整的datetime列，确保跨天等场景下时间计算的准确性。
2. 分组计算时间差：按Group_ID分组后，用lead(datetime)获取同组下一行的时间，计算与当前行的时间差并转为秒数。
3. 筛选求和：先筛选出Color为"blue"的行，再按Group_ID分组对对应行的duration_sec求和，得到目标结果。

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