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')
输出示例
| id | total_overlap_days |
|---|---|
| 123456 | 4 |
| 789101 | 0 |
| 903456 | 0 |
方案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
相关产品推荐
相关产品推荐

