基于label与trip_num分组计算时间差的data.table数据处理需求

示例DataFrame #

label timestamp           trip_num trip_direction 
1001  2023-10-05 03:43:18 8        0                 
1001  2023-10-05 03:44:42 8        0              
1001  2023-10-05 03:46:29 8        1              
1001  2023-10-05 03:47:54 8        1              
1001  2023-10-05 03:56:29 8        1              
1001  2023-10-05 03:57:24 8        0              
1001  2023-10-05 03:58:55 8        0              
1001  2023-10-05 04:12:33 8        0          
1001  2023-10-05 04:13:45 8        1            
1001  2023-10-05 04:53:08 8        1         
1003  2023-10-05 05:53:18 10       0            
1003  2023-10-05 05:54:42 10       0              
1003  2023-10-05 05:56:29 10       0              
1003  2023-10-05 05:57:54 10       1           
1003  2023-10-05 06:06:29 10       1           
1003  2023-10-05 06:07:24 10       0          
1003  2023-10-05 06:08:55 10       0          
1003  2023-10-05 06:22:33 10       0          
1003  2023-10-05 06:23:45 10       1          
1003  2023-10-05 06:54:08 10       1      

数据创建代码 #

library(data.table)
df <- data.table(label = c(1001, 1001, 1001, 1001, 1001, 1001, 1001, 1001, 1001, 1001,
                           1003, 1003, 1003, 1003, 1003, 1003, 1003, 1003, 1003, 1003),
                 timestamp = c('2023-10-05 03:43:18', '2023-10-05 03:44:42', '2023-10-05 03:46:29', '2023-10-05 03:47:54', '2023-10-05 03:56:29',
                               '2023-10-05 03:57:24', '2023-10-05 03:58:55', '2023-10-05 04:12:33', '2023-10-05 04:13:45', '2023-10-05 04:53:08',
                               '2023-10-05 05:53:18', '2023-10-05 05:54:42', '2023-10-05 05:56:29', '2023-10-05 05:57:54', '2023-10-05 06:06:29',
                               '2023-10-05 06:07:24', '2023-10-05 06:08:55', '2023-10-05 06:22:33', '2023-10-05 06:23:45', '2023-10-05 06:54:08'),
                 trip_num = c(8,8,8,8,8,8,8,8,8,8,10,10,10,10,10,10,10,10,10,10),
                 trip_direction = c(0,0,1,1,1,0,0,0,1,1,0,0,0,1,1,0,0,0,1,1))

需求描述 #

按label和trip_num分组，每组内完成以下操作：

• 提取trip_direction = 0的连续段最早的timestamp；
• 提取该0段之后紧随的trip_direction = 1连续段的最晚timestamp（即该1段切换回0之前的最后一条记录）；
• 计算上述两个时间的差值delta_t；
• 最终仅保留trip_direction = 0的行，预期输出如下：

label timestamp           trip_num trip_direction delta_t 
1001  2023-10-05 03:43:18 8        0              00:13:11   
1001  2023-10-05 03:57:24 8        0              00:55:44
1003  2023-10-05 05:53:18 10       0              00:13:11   
1003  2023-10-05 06:07:24 10       0              00:46:44

解决方案 #

基于data.table的分组和窗口函数实现，步骤如下：

1. 将timestamp转换为时间类型，便于后续计算；
2. 按label和trip_num分组，标记连续的trip_direction段；
3. 提取每个段的起始、结束时间及方向；
4. 匹配每个0段对应的下一个1段，计算时间差并整理输出格式。

代码实现：

library(data.table)

# 转换timestamp为POSIXct时间类型
df[, timestamp := as.POSIXct(timestamp)]

# 按分组标记连续的方向段ID
df[, segment_id := rleid(trip_direction), by = .(label, trip_num)]

# 提取每个段的关键信息
segments <- df[, .(start_time = min(timestamp), end_time = max(timestamp), direction = first(trip_direction)), 
               by = .(label, trip_num, segment_id)]

# 匹配每个0段对应的下一个1段的结束时间
segments[, next_segment_end := shift(end_time, type = "lead"), by = .(label, trip_num)]
# 筛选出符合条件的0段（下一段必须是1段）
segments <- segments[direction == 0 & shift(direction, type = "lead") == 1]

# 计算时间差并格式化为HH:MM:SS
segments[, delta_t := format(next_segment_end - start_time, "%H:%M:%S")]

# 整理成预期输出结构
result <- segments[, .(label, timestamp = start_time, trip_num, trip_direction = direction, delta_t)]
setcolorder(result, c("label", "timestamp", "trip_num", "trip_direction", "delta_t"))

print(result)

运行后输出：

label           timestamp trip_num trip_direction delta_t
1:  1001 2023-10-05 03:43:18        8              0 00:13:11
2:  1001 2023-10-05 03:57:24        8              0 00:55:44
3:  1003 2023-10-05 05:53:18       10              0 00:13:11
4:  1003 2023-10-05 06:07:24       10              0 00:46:44

内容的提问来源于stack exchange，提问作者barney stinson 2