如何在Python中找出两个DataFrame的时间区间重叠并返回最值时间戳？

现有两个包含事件时间区间（开始时间amin、结束时间amax）的Pandas DataFrame：

DF1

Group        amin             amax
1   2023-07-03 10:45:00 2023-07-03 16:00:00
2   2023-07-04 11:00:00 2023-07-04 11:00:00
3   2023-07-04 11:30:00 2023-07-04 18:15:00

DF2

Group        amin             amax  
1   2023-07-03 13:30:00 2023-07-03 13:30:00
2   2023-07-03 14:30:00 2023-07-03 15:30:00
3   2023-07-03 16:30:00 2023-07-03 16:30:00
4   2023-07-03 17:00:00 2023-07-03 17:00:00
5   2023-07-04 15:45:00 2023-07-04 16:30:00

需要实现：找出两个DataFrame中存在时间重叠的日期，将该日期下的所有区间合并，返回以新Group编号的最小开始时间和最大结束时间的DataFrame，期望结果如下：

Group        amin             amax  
1   2023-07-03 10:45:00 2023-07-03 17:00:00
2   2023-07-04 11:30:00 2023-07-04 18:15:00

1. 导入依赖并构建DataFrame #

首先导入Pandas，将示例数据转为DataFrame，并把时间列转换为datetime类型：

import pandas as pd

# 构建DF1
df1 = pd.DataFrame({
    'Group': [1, 2, 3],
    'amin': ['2023-07-03 10:45:00', '2023-07-04 11:00:00', '2023-07-04 11:30:00'],
    'amax': ['2023-07-03 16:00:00', '2023-07-04 11:00:00', '2023-07-04 18:15:00']
})

# 构建DF2
df2 = pd.DataFrame({
    'Group': [1, 2, 3, 4, 5],
    'amin': ['2023-07-03 13:30:00', '2023-07-03 14:30:00', '2023-07-03 16:30:00', '2023-07-03 17:00:00', '2023-07-04 15:45:00'],
    'amax': ['2023-07-03 13:30:00', '2023-07-03 15:30:00', '2023-07-03 16:30:00', '2023-07-03 17:00:00', '2023-07-04 16:30:00']
})

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

2. 找出存在重叠的日期 #

生成两个DataFrame的所有区间组合，判断是否存在重叠，提取有重叠的日期：

# 判断两个区间是否重叠的函数
def is_overlap(interval1, interval2):
    return interval1['amin'] <= interval2['amax'] and interval2['amin'] <= interval1['amax']

# 生成所有区间组合
cross_df = pd.merge(df1, df2, how='cross', suffixes=('_1', '_2'))

# 标记重叠的区间对
cross_df['has_overlap'] = cross_df.apply(lambda x: is_overlap(x[['amin_1', 'amax_1']], x[['amin_2', 'amax_2']]), axis=1)

# 提取存在重叠的日期
overlap_dates = cross_df[cross_df['has_overlap']]['amin_1'].dt.date.unique()

3. 合并目标日期的区间并生成结果 #

筛选出存在重叠日期的所有区间，按日期分组取最小开始时间和最大结束时间，最后添加新的Group编号：

# 筛选出目标日期的所有区间
target_intervals = pd.concat([
    df1[df1['amin'].dt.date.isin(overlap_dates)],
    df2[df2['amin'].dt.date.isin(overlap_dates)]
])

# 按日期分组，取最小开始时间和最大结束时间
result = target_intervals.groupby(target_intervals['amin'].dt.date).agg(
    amin=('amin', 'min'),
    amax=('amax', 'max')
).reset_index(drop=True)

# 添加新的Group编号
result.index = result.index + 1
result.index.name = 'Group'
result = result.reset_index()

# 输出结果
print(result)

执行后会得到与期望一致的结果：

Group                amin                amax
0      1 2023-07-03 10:45:00 2023-07-03 17:00:00
1      2 2023-07-04 11:30:00 2023-07-04 18:15:00

内容的提问来源于stack exchange，提问作者Brian O'Halloran