R语言如何按station分组，基于起止时间戳合并相同id的连续行？

实现逻辑 #

• 先对连续相同id+station的记录打分组标记，和上一行id或station不一致时标记为新组
• 按分组、id、station汇总，取每组最早的start和最晚的stop作为合并后的起止时间

完整代码 #

# 加载依赖包
library(tidyverse)
library(lubridate)

# 构造示例数据（你可以替换成自己的数据集）
df <- tibble(
  id = c(1,2,3,4,1,3,5,6,6,7,3,3,7,8,9),
  station = c(1,1,1,1,1,1,1,1,1,1,1,2,3,3,2),
  start = ymd_hms(c(
    "1899-12-31 00:05:04", "1899-12-31 00:14:04", "1899-12-31 00:21:32",
    "1899-12-31 00:26:57", "1899-12-31 00:38:32", "1899-12-31 00:43:23",
    "1899-12-31 00:53:00", "1899-12-31 00:53:29", "1899-12-31 00:56:15",
    "1899-12-31 01:14:30", "1899-12-31 01:28:09", "1899-12-31 01:34:22",
    "1899-12-31 01:36:44", "1899-12-31 01:41:49", "1899-12-31 01:44:47"
  )),
  stop = ymd_hms(c(
    "1899-12-31 00:13:36", "1899-12-31 00:21:32", "1899-12-31 00:26:56",
    "1899-12-31 00:27:10", "1899-12-31 00:38:38", "1899-12-31 00:43:47",
    "1899-12-31 00:53:15", "1899-12-31 00:55:49", "1899-12-31 00:56:42",
    "1899-12-31 01:28:09", "1899-12-31 01:31:17", "1899-12-31 01:35:11",
    "1899-12-31 01:39:54", "1899-12-31 01:44:45", "1899-12-31 01:52:07"
  ))
)

# 核心处理代码
df_res <- df %>%
  # 生成连续同id同station的分组标记
  mutate(group_flag = cumsum(id != lag(id, default = first(id)) | station != lag(station, default = first(station)))) %>%
  # 分组汇总时间
  group_by(group_flag, id, station) %>%
  summarise(
    start = min(start),
    stop = max(stop),
    .groups = "drop"
  ) %>%
  # 移除辅助分组列
  select(-group_flag)

输出结果预览 #

# A tibble: 14 × 4
      id station start               stop               
   <dbl>   <dbl> <dttm>              <dttm>             
 1     1       1 1899-12-31 00:05:04 1899-12-31 00:13:36
 2     2       1 1899-12-31 00:14:04 1899-12-31 00:21:32
 3     3       1 1899-12-31 00:21:32 1899-12-31 00:26:56
 4     4       1 1899-12-31 00:26:57 1899-12-31 00:27:10
 5     1       1 1899-12-31 00:38:32 1899-12-31 00:38:38
 6     3       1 1899-12-31 00:43:23 1899-12-31 00:43:47
 7     5       1 1899-12-31 00:53:00 1899-12-31 00:53:15
 8     6       1 1899-12-31 00:53:29 1899-12-31 00:56:42
 9     7       1 1899-12-31 01:14:30 1899-12-31 01:28:09
10     3       1 1899-12-31 01:28:09 1899-12-31 01:31:17
11     3       2 1899-12-31 01:34:22 1899-12-31 01:35:11
12     7       3 1899-12-31 01:36:44 1899-12-31 01:39:54
13     8       3 1899-12-31 01:41:49 1899-12-31 01:44:45
14     9       2 1899-12-31 01:44:47 1899-12-31 01:52:07

可以看到符合要求：连续同station的id=6两条记录已合并，id=3分属不同station的两条记录保留独立行。

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