基于Pandas 1.0.5计算多日时间区间的每日重叠时长

需求：从包含三天时间区间的DataFrame中，找出每天都存在重叠的时间段，最终输出这些时间段的分钟数（示例输出：[57,15]）。

解决方案步骤 #

1. 预处理数据：转换时间为当日分钟数

将每个时间段的开始/结束时间转换为相对于当日0点的分钟数，方便后续区间计算：

import pandas as pd
dat1 = [
    ['2023-12-27','2023-12-27 00:00:00','2023-12-27 02:14:00'],
    ['2023-12-27','2023-12-27 03:16:00','2023-12-27 04:19:00'],
    ['2023-12-27','2023-12-27 18:11:00','2023-12-27 20:13:00'],
    ['2023-12-28','2023-12-28 01:16:00','2023-12-28 02:14:00'],
    ['2023-12-28','2023-12-28 02:16:00','2023-12-28 02:28:00'],
    ['2023-12-28','2023-12-28 02:30:00','2023-12-28 02:56:00'],
    ['2023-12-28','2023-12-28 18:45:00','2023-12-28 19:00:00'],
    ['2023-12-29','2023-12-29 01:16:00','2023-12-29 02:13:00'],
    ['2023-12-29','2023-12-29 04:16:00','2023-12-29 05:09:00'],
    ['2023-12-29','2023-12-29 05:11:00','2023-12-29 05:14:00'],
    ['2023-12-29','2023-12-29 18:00:00','2023-12-29 19:00:00']
       ]
df = pd.DataFrame(dat1,columns = ['date','Start_tmp','End_tmp'])
df["Start_tmp"] = pd.to_datetime(df["Start_tmp"])
df["End_tmp"] = pd.to_datetime(df["End_tmp"])

# 转换为当日0点起的分钟数
df['start_min'] = (df['Start_tmp'] - df['Start_tmp'].dt.floor('D')).dt.total_seconds() // 60
df['end_min'] = (df['End_tmp'] - df['End_tmp'].dt.floor('D')).dt.total_seconds() // 60

2. 合并每日的重叠区间

每个日期可能有多个不连续的时间段，先将同一日期内重叠或相邻的时间段合并：

def merge_intervals(intervals):
    # 按开始时间排序
    sorted_intervals = sorted(intervals, key=lambda x: x[0])
    merged = []
    for interval in sorted_intervals:
        if not merged:
            merged.append(interval)
        else:
            last_start, last_end = merged[-1]
            curr_start, curr_end = interval
            # 重叠或相邻则合并
            if curr_start <= last_end:
                merged[-1] = (last_start, max(last_end, curr_end))
            else:
                merged.append(interval)
    return merged

# 按日期分组获取合并后的区间
daily_merged = df.groupby('date').apply(lambda x: merge_intervals(list(zip(x['start_min'], x['end_min'])))).to_dict()

3. 计算跨日期的公共重叠区间

通过迭代计算三个日期合并区间的交集，得到所有日期都覆盖的时间段：

def intersect_intervals(list_a, list_b):
    intersection = []
    i = j = 0
    while i < len(list_a) and j < len(list_b):
        a_start, a_end = list_a[i]
        b_start, b_end = list_b[j]
        # 计算两个区间的重叠部分
        overlap_start = max(a_start, b_start)
        overlap_end = min(a_end, b_end)
        if overlap_start < overlap_end:
            intersection.append((overlap_start, overlap_end))
        # 移动指针：先结束的区间指针后移
        if a_end < b_end:
            i += 1
        else:
            j += 1
    return intersection

# 计算三个日期的公共交集
first_two = intersect_intervals(daily_merged['2023-12-27'], daily_merged['2023-12-28'])
final_overlap = intersect_intervals(first_two, daily_merged['2023-12-29'])

4. 计算目标分钟数

对最终的公共重叠区间计算时长（结束分钟-开始分钟）：

result = [int(end - start) for start, end in final_overlap]
print(result)  # 输出: [57, 15]

结果说明 #

• 57：对应时间段01:16-02:13的分钟数（133-76=57）
• 15：对应时间段18:45-19:00的分钟数（1140-1125=15）

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