如何用Pandas从可选时段与已预订时段重建空闲时段DataFrame？

现有两个Pandas DataFrame：df1为可选时段列表，df2为已预订时段列表，需高效实现从df1中剔除被df2占用的时段，生成空闲时段的df3。

具体数据 #

可选时段（df1） #

>>> df1
                  start                  end
0   2023-02-28 08:00:00  2023-02-28 08:30:00
1   2023-02-28 08:30:00  2023-02-28 09:00:00
2   2023-02-28 09:00:00  2023-02-28 09:30:00
3   2023-02-28 09:30:00  2023-02-28 10:00:00
4   2023-02-28 10:00:00  2023-02-28 10:30:00
5   2023-02-28 10:30:00  2023-02-28 11:00:00
6   2023-02-28 11:00:00  2023-02-28 11:30:00
7   2023-02-28 11:30:00  2023-02-28 12:00:00
8   2023-02-28 12:00:00  2023-02-28 12:30:00
9   2023-02-28 12:30:00  2023-02-28 13:00:00
10  2023-02-28 13:00:00  2023-02-28 13:30:00
11  2023-02-28 13:30:00  2023-02-28 14:00:00
12  2023-02-28 14:00:00  2023-02-28 14:30:00
13  2023-02-28 14:30:00  2023-02-28 15:00:00
14  2023-02-28 15:00:00  2023-02-28 15:30:00
15  2023-02-28 15:30:00  2023-02-28 16:00:00
>>>

已预订时段（df2） #

>>> df2
                 start                  end
0  2023-02-28 08:00:00  2023-02-28 08:15:00
1  2023-02-28 08:15:00  2023-02-28 08:30:00
2  2023-02-28 09:00:00  2023-02-28 09:30:00
3  2023-02-28 12:00:00  2023-02-28 12:45:00
4  2023-02-28 13:15:00  2023-02-28 14:45:00
>>>

预期结果（df3） #

>>> df3
                  start                  end
0   2023-02-28 08:30:00  2023-02-28 09:00:00
1   2023-02-28 09:30:00  2023-02-28 10:00:00
2   2023-02-28 10:00:00  2023-02-28 10:30:00
3   2023-02-28 10:30:00  2023-02-28 11:00:00
4   2023-02-28 11:00:00  2023-02-28 11:30:00
5   2023-02-28 11:30:00  2023-02-28 12:00:00
6   2023-02-28 12:45:00  2023-02-28 13:00:00
7   2023-02-28 13:00:00  2023-02-28 13:15:00
8   2023-02-28 14:45:00  2023-02-28 15:00:00
9   2023-02-28 15:00:00  2023-02-28 15:30:00
10  2023-02-28 15:30:00  2023-02-28 16:00:00

高效实现方案 #

步骤说明 #

1. 转换时间列为datetime类型，避免字符串操作误差。
2. 合并df2中重叠或连续的预订时段，减少后续计算量。
3. 基于df1的整体时间范围，用合并后的预订时段切割出空闲区间。
4. 将空闲区间转换为目标DataFramedf3。

代码实现 #

import pandas as pd

# 1. 转换时间列为datetime类型
df1[['start', 'end']] = df1[['start', 'end']].apply(pd.to_datetime)
df2[['start', 'end']] = df2[['start', 'end']].apply(pd.to_datetime)

# 2. 合并df2中重叠/连续的预订时段
df2_sorted = df2.sort_values('start').reset_index(drop=True)
merged_booked = []
if not df2_sorted.empty:
    current_start, current_end = df2_sorted.iloc[0][['start', 'end']]
    for _, row in df2_sorted.iloc[1:].iterrows():
        if row['start'] <= current_end:
            # 重叠或连续，更新结束时间
            current_end = max(current_end, row['end'])
        else:
            merged_booked.append({'start': current_start, 'end': current_end})
            current_start, current_end = row['start'], row['end']
    merged_booked.append({'start': current_start, 'end': current_end})
merged_booked = pd.DataFrame(merged_booked)

# 3. 生成空闲区间
free_intervals = []
total_start = df1['start'].min()
total_end = df1['end'].max()
prev_end = total_start

for _, row in merged_booked.iterrows():
    if row['start'] > prev_end:
        free_intervals.append({'start': prev_end, 'end': row['start']})
    prev_end = max(prev_end, row['end'])

# 处理最后一段空闲时间
if prev_end < total_end:
    free_intervals.append({'start': prev_end, 'end': total_end})

# 4. 转换为目标DataFrame
df3 = pd.DataFrame(free_intervals)

方案优势 #

• 时间复杂度主要由排序决定（O(n log n)），远高于逐行比对的O(n*m)，数据量越大优势越明显。
• 自动处理跨时段的预订区间，精准切割出实际空闲时段，完全匹配预期结果。

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