You need to enable JavaScript to run this app. Python按DataFrame的id分组计算多日期区间重叠天数的实现方法
优惠活动
大模型
产品
解决方案
定价
更多

Python按DataFrame的id分组计算多日期区间重叠天数的实现方法

Pandas按ID统计日期区间重叠天数实现方案

前置依赖导入

import pandas as pd
from collections import namedtuple
from datetime import datetime

Range = namedtuple('Range', ['start', 'end'])

方案1:计算同ID下所有区间两两重叠总天数

适用于需要统计每对区间重叠之和的场景:

def calc_group_overlap(group):
    # 单条记录无重叠
    if len(group) == 1:
        return 0
    total_overlap = 0
    ranges = [Range(start=row['starts_on'], end=row['ends_on']) for _, row in group.iterrows()]
    # 遍历所有两两区间组合
    for i in range(len(ranges)):
        for j in range(i+1, len(ranges)):
            r1, r2 = ranges[i], ranges[j]
            latest_start = max(r1.start, r2.start)
            earliest_end = min(r1.end, r2.end)
            delta = (earliest_end - latest_start).days + 1
            total_overlap += max(0, delta)
    return total_overlap

# 按ID分组调用计算
overlap_result = df.groupby('id').apply(calc_group_overlap).reset_index(name='total_overlap_days')

输出示例

idtotal_overlap_days
1234564
7891010
9034560

方案2:高效计算同ID下重叠覆盖总天数(推荐大规模数据使用)

如果只需要统计重叠区域的总天数,不需要两两求和,使用区间合并优化算法,时间复杂度更低,适合大样本:

def calc_merge_overlap(group):
    if len(group) == 1:
        return 0
    # 按区间开始时间排序
    sorted_group = group.sort_values('starts_on')
    merged = [Range(start=sorted_group.iloc[0]['starts_on'], end=sorted_group.iloc[0]['ends_on'])]
    total_overlap = 0
    for _, row in sorted_group.iloc[1:].iterrows():
        last_range = merged[-1]
        current_range = Range(start=row['starts_on'], end=row['ends_on'])
        if current_range.start <= last_range.end:
            # 计算重叠天数
            overlap_start = max(current_range.start, last_range.start)
            overlap_end = min(current_range.end, last_range.end)
            total_overlap += (overlap_end - overlap_start).days + 1
            # 合并区间
            merged[-1] = Range(start=last_range.start, end=max(last_range.end, current_range.end))
        else:
            merged.append(current_range)
    return total_overlap

# 按ID分组调用计算
overlap_result = df.groupby('id').apply(calc_merge_overlap).reset_index(name='overlap_cover_days')

内容的提问来源于stack exchange,提问作者sarisinhache

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.10.08 08:54:01