如何为多ID时间序列数据集自动插入记录填补时间窗口缺失？

问题背景 #

我手头有一份记录车辆活动（行驶、休息、充电等）时间段的数据集，同一车辆的记录本该保持连续——下一条记录的starttime等于上一条的endtime，但夜间无记录导致数据出现断档。需要针对不同车辆ID自动插入填补缺口的记录，让所有时间段完全连续。

示例数据集：

import pandas as pd
from io import StringIO

csv = """
id,starttime,endtime
1,2022-09-19 17:05:00,2022-09-19 17:26:00
1,2022-09-19 17:26:00,2022-09-19 18:38:00
1,2022-09-19 18:38:00,2022-09-19 19:31:00
1,2022-09-19 19:31:00,2022-09-19 19:38:00
1,2022-09-19 19:38:00,2022-09-19 19:40:00
1,2022-09-19 19:40:00,2022-09-19 19:41:00
1,2022-09-20 07:06:00,2022-09-20 07:06:00
1,2022-09-20 07:06:00,2022-09-20 07:23:00
1,2022-09-20 07:23:00,2022-09-20 07:26:00
1,2022-09-20 07:26:00,2022-09-20 07:37:00
"""

df = pd.read_csv(StringIO(csv))

需要插入的缺口记录示例：

1,2022-09-19 19:41:00,2022-09-20 07:06:00

最优实现方法 #

步骤1：转换时间列类型 #

首先把starttime和endtime转为Pandas的datetime类型，才能进行时间差计算：

df['starttime'] = pd.to_datetime(df['starttime'])
df['endtime'] = pd.to_datetime(df['endtime'])

步骤2：按ID分组检测缺口 #

按车辆ID分组，计算每组内下一条记录的starttime与当前记录endtime的差值，筛选出存在时间缺口的行：

# 分组后获取每组的下一条starttime
df['next_start'] = df.groupby('id')['starttime'].shift(-1)

# 筛选出当前endtime小于下一条starttime的缺口行
gaps = df[df['endtime'] < df['next_start']].copy()

步骤3：生成填补缺口的记录 #

从缺口行中提取ID，用当前记录的endtime作为新记录的starttime，下一条的starttime作为新记录的endtime：

fill_records = gaps[['id', 'endtime', 'next_start']].rename(
    columns={'endtime': 'starttime', 'next_start': 'endtime'}
)

步骤4：合并并排序数据集 #

将原数据和填补记录合并，按ID和starttime排序，得到连续的完整数据集：

# 合并数据并删除临时列
full_df = pd.concat([df.drop(columns=['next_start']), fill_records], ignore_index=True)

# 按ID和starttime排序，保证时序正确
full_df = full_df.sort_values(by=['id', 'starttime'], ignore_index=True)

完整可运行代码 #

import pandas as pd
from io import StringIO

csv = """
id,starttime,endtime
1,2022-09-19 17:05:00,2022-09-19 17:26:00
1,2022-09-19 17:26:00,2022-09-19 18:38:00
1,2022-09-19 18:38:00,2022-09-19 19:31:00
1,2022-09-19 19:31:00,2022-09-19 19:38:00
1,2022-09-19 19:38:00,2022-09-19 19:40:00
1,2022-09-19 19:40:00,2022-09-19 19:41:00
1,2022-09-20 07:06:00,2022-09-20 07:06:00
1,2022-09-20 07:06:00,2022-09-20 07:23:00
1,2022-09-20 07:23:00,2022-09-20 07:26:00
1,2022-09-20 07:26:00,2022-09-20 07:37:00
"""

df = pd.read_csv(StringIO(csv))

# 转换时间列
df['starttime'] = pd.to_datetime(df['starttime'])
df['endtime'] = pd.to_datetime(df['endtime'])

# 分组检测缺口
df['next_start'] = df.groupby('id')['starttime'].shift(-1)
gaps = df[df['endtime'] < df['next_start']].copy()

# 生成填补记录
fill_records = gaps[['id', 'endtime', 'next_start']].rename(
    columns={'endtime': 'starttime', 'next_start': 'endtime'}
)

# 合并排序
full_df = pd.concat([df.drop(columns=['next_start']), fill_records], ignore_index=True)
full_df = full_df.sort_values(by=['id', 'starttime'], ignore_index=True)

print(full_df)

运行后输出的完整数据集中，会自动插入所有需要的缺口记录，所有车辆的时间段都保持连续。

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