如何优雅提取Pandas时间序列DataFrame中缺失的日期范围

问题背景 #

现有1分钟粒度的时间序列DataFrame，包含Start_Date、End_Date、Avg_Price三个字段，样例数据如下：

+-----------------+-----------------+-------+
|   Start_Date    |    End_Date     | Price |
+-----------------+-----------------+-------+
| 01/01/2021 0:00 | 01/01/2021 0:59 |    10 |
| 01/01/2021 0:01 | 01/01/2021 0:01 |    20 |
| 01/01/2021 0:02 | 01/01/2021 0:02 |    24 |
| 01/01/2021 0:03 | 01/01/2021 0:03 |    23 |
| 01/01/2021 0:07 | 01/01/2021 0:07 |    34 |
| 01/01/2021 0:08 | 01/01/2021 0:08 |    37 |
| 01/01/2021 0:10 | 01/01/2021 0:10 |    21 |
| 01/01/2021 0:12 | 01/01/2021 0:12 |    22 |
| 01/01/2021 0:14 | 01/01/2021 0:14 |    56 |
+-----------------+-----------------+-------+

样例数据生成代码：

import pandas as pd

data = {'Start_Date':['2021-01-01 00:00:00', '2021-01-01 00:01:00', '2021-01-01 00:02:00', '2021-01-01 00:03:00', '2021-01-01 00:07:00',
                      '2021-01-01 00:08:00', '2021-01-01 00:10:00', '2021-01-01 00:12:00', '2021-01-01 00:14:00'],
        'End_Date':['2021-01-01 00:59:00', '2021-01-01 00:01:59', '2021-01-01 00:02:59', '2021-01-01 00:03:59', '2021-01-01 00:07:59',
                      '2021-01-01 00:08:59', '2021-01-01 00:10:59', '2021-01-01 00:12:59', '2021-01-01 00:14:59'],
        'Avg_Price':[10, 20, 24, 23, 34, 37, 21, 22, 56]}
df1 = pd.DataFrame(data)
df1['Start_Date'] = pd.to_datetime(df1['Start_Date'])
df1['End_Date'] = pd.to_datetime(df1['End_Date'])

数据补全操作 #

由于原始数据存在缺失的时间点，先按1分钟粒度补全时间索引，补全后的数据如下：

+---------------------+---------------------+-------+
|     Start_Date      |      End_Date       | Price |
+---------------------+---------------------+-------+
| 2021-01-01 00:00:00 | 2021-01-01 00:59:00 | 10    |
| 2021-01-01 00:01:00 | 2021-01-01 00:01:59 | 20    |
| 2021-01-01 00:02:00 | 2021-01-01 00:02:59 | 24    |
| 2021-01-01 00:03:00 | 2021-01-01 00:03:59 | 23    |
| 2021-01-01 00:04:00 | NaT                 | NaN   |
| 2021-01-01 00:05:00 | NaT                 | NaN   |
| 2021-01-01 00:06:00 | NaT                 | NaN   |
| 2021-01-01 00:07:00 | 2021-01-01 00:07:59 | 34    |
| 2021-01-01 00:08:00 | 2021-01-01 00:08:59 | 37    |
| 2021-01-01 00:09:00 | NaT                 | NaN   |
| 2021-01-01 00:10:00 | 2021-01-01 00:10:59 | 21    |
| 2021-01-01 00:11:00 | NaT                 | NaN   |
| 2021-01-01 00:12:00 | 2021-01-01 00:12:59 | 22    |
| 2021-01-01 00:13:00 | NaT                 | NaN   |
| 2021-01-01 00:14:00 | 2021-01-01 00:14:59 | 56    |
+---------------------+---------------------+-------+

补全操作代码：

df2 = pd.DataFrame(index=pd.date_range('2021-01-01 00:00:00', '2021-01-01 00:14:00', freq='min'))
df2 = df2.join(df1.set_index('Start_Date'))

需求描述 #

需要将所有连续缺失的时间区间整理为嵌套列表格式，每个子列表第一个元素为缺失区间的起始时间，第二个为结束时间，预期输出如下：

result = [['2021-01-01 00:04:00','2021-01-01 00:06:00'], ['2021-01-01 00:09:00','2021-01-01 00:09:00'],
['2021-01-01 00:11:00', '2021-01-01 00:11:00'], ['2021-01-01 00:13:00','2021-01-01 00:13:00']]

实现方案 #

可以利用Pandas的分组聚合能力，对连续缺失的时间点进行自动分组，代码如下：

# 筛选出所有缺失行的时间索引
missing_idx = df2[df2['End_Date'].isna()].index
# 对连续的时间点分组：相邻时间差超过1分钟则判定为新的分组
groups = missing_idx.to_series().diff().dt.total_seconds().gt(60).cumsum()
# 按组取首尾时间，转成字符串列表
result = groups.groupby(groups).agg(['first', 'last']).astype(str).values.tolist()

执行后得到的result和预期输出完全一致。

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