在data.table中使用lead/lag多变量：按多维度统计出站单车数

问题背景 #

我们需要统计每个站点在每天每小时驶出的单车数量，规则是：当当前记录的bikes_available与下一条记录的差值为正时，该差值即为驶出的单车数；差值为负或零时不计入统计，最后按station_id、hour、date分组求和。

数据样例 #

> head(dat, n = 10)
station_id bikes_available time                date       hour
1: 3         2               2018-01-15 01:58:02 2018-01-15 1
2: 3         1               2018-01-15 01:59:01 2018-01-15 1
3: 3         1               2018-01-15 02:00:03 2018-01-15 2
4: 3         4               2018-01-15 02:01:02 2018-01-15 2
5: 3         4               2018-01-15 02:02:02 2018-01-15 2
6: 3         1               2018-01-15 02:03:02 2018-01-15 2
7: 3         1               2018-01-15 02:04:02 2018-01-15 2
8: 3         1               2018-01-15 02:05:02 2018-01-15 2
9: 3         7               2018-01-15 02:06:02 2018-01-15 2
10: 3        3               2018-01-15 02:07:02 2018-01-15 2

解决方案 #

我们可以利用data.table的分组移位和链式操作一步完成计算，不需要单独创建冗余中间列（当然也可以保留中间列用于调试）。核心思路是：

1. 按station_id分组（单车驶出是针对单个站点的时序变化），计算当前记录与下一条记录的bikes_available差值；
2. 筛选出差值为正的记录，这些就是有效的驶出数；
3. 再按station_id、date、hour分组，对有效差值求和，得到最终结果。

完整代码

# 加载data.table（如果未提前加载）
library(data.table)

# 计算驶出单车数并完成聚合
output <- dat[, {
  # 计算当前记录与下一条记录的单车数量差值
  diff_bikes <- bikes_available - shift(bikes_available, 1, type = "lead")
  # 仅保留正差值（有效驶出数），其余置为0
  valid_diff <- ifelse(diff_bikes > 0, diff_bikes, 0)
  # 返回分组求和后的结果列
  .(bikes_taken = sum(valid_diff))
}, by = .(station_id, date, hour)]

# 查看最终结果
output

代码细节解释

• by = .(station_id, date, hour)：指定最终的聚合维度，确保我们得到每个站点每天每小时的统计结果；
• shift(bikes_available, 1, type = "lead")：在每个station_id组内，获取下一条记录的bikes_available值，这是计算驶出数的关键；
• ifelse(diff_bikes > 0, diff_bikes, 0)：只保留正差值（即单车被骑走的情况），其他情况置为0，避免负差值或零值影响求和结果；
• sum(valid_diff)：对每个分组内的有效差值求和，得到该时段驶出的总单车数。

预期输出 #

运行上述代码后，会得到符合需求的结果：

> output
station_id       date hour bikes_taken
1:          3 2018-01-15    1           1
2:          3 2018-01-15    2           7
3:          4 2018-01-15    1           4
4:          4 2018-01-15    2           1
5:          5 2018-01-15    1           0
6:          5 2018-01-15    2           2

调试友好的分步写法 #

如果需要查看每一步的计算过程以便调试，可以拆分步骤并保留中间列：

# 分步处理，保留中间计算列
dat[, diff_bikes := bikes_available - shift(bikes_available, 1, type = "lead"), by = station_id]
dat[, valid_diff := ifelse(diff_bikes > 0, diff_bikes, 0)]
output <- dat[, .(bikes_taken = sum(valid_diff)), by = .(station_id, date, hour)]

这样可以更清晰地追踪每一步的计算结果，方便排查问题。

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