基于data.table按用户分组统计返回起点所需行程段数

需求分析 #

按用户ID分组，统计每次行程返回之前到达过的地点时，之前经过的行程段数：

• 若当前行程的终点是该用户之前某行程的起点，计算当前行程序号与最近一次从该地点出发的行程序号的差值，作为返回段数
• 否则标记为NA

解决方案代码 #

library(data.table)

# 转换为data.table格式
dt <- as.data.table(df)

# 1. 按ID分组生成行程序号（保持原行程顺序）
dt[, trip_num := seq_len(.N), by = ID]

# 2. 处理坐标精度（可选，针对浮点数坐标避免匹配误差）
dt[, `:=`(
  trip_start_lat = round(trip_start_lat, 6),
  trip_start_lon = round(trip_start_lon, 6),
  trip_end_lat = round(trip_end_lat, 6),
  trip_end_lon = round(trip_end_lon, 6)
)]

# 3. 创建起点/终点的唯一标识
dt[, start_id := paste(trip_start_lat, trip_start_lon, sep = "_")]
dt[, end_id := paste(trip_end_lat, trip_end_lon, sep = "_")]

# 4. 准备用于滚动连接的起点表
start_dt <- dt[, .(ID, start_id, trip_num)]
setorder(start_dt, ID, start_id, trip_num)

# 5. 滚动连接：匹配用户ID，终点=起点，找到最近的出发行程序号（小于当前行程）
dt <- start_dt[dt, on = .(ID, start_id = end_id, trip_num < trip_num), roll = -Inf,
               .(ID, trip_start_lat, trip_start_lon, trip_end_lat, trip_end_lon, 
                 current_trip = i.trip_num, last_depart_trip = x.trip_num)]

# 6. 计算返回段数
dt[, desired_output := ifelse(!is.na(last_depart_trip), current_trip - last_depart_trip, NA)]

# 7. 清理中间列并恢复原顺序
dt[, c("current_trip", "last_depart_trip") := NULL]
setorder(dt, ID, trip_num)

代码说明 #

1. 行程序号：按用户分组生成连续序号，确保行程顺序与原始数据一致
2. 坐标精度处理：对浮点数经纬度四舍五入，避免因微小精度差异导致的地点匹配失败
3. 唯一标识：将经纬度拼接为字符串，作为地点的唯一识别符，简化匹配逻辑
4. 滚动连接：利用data.table高效的滚动连接特性，快速定位每个返回地点的最近出发行程，时间复杂度接近O(n)，适配百万级数据规模
5. 结果计算：通过行程序号差值得到返回段数，无匹配记录则标记为NA

验证示例 #

运行代码后，示例数据会生成与期望完全一致的desired_output列：

• individual1第3段行程返回(1,2)，对应最近出发行程是第2段，差值为1
• individual1第6段行程返回(1,2)，对应最近出发行程是第4段，差值为2
• individual2第3段行程返回(4,5)，对应最近出发行程是第1段，差值为2

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