如何用Pythonic方法处理Pandas中数值范围重叠并修正length列求和？

核心思路是：先按ID和target分组，对每组内的区间进行重叠合并，再计算合并后所有区间的总长度，这种方式比修改原length列更严谨（能避免原length与end-start数值不一致的情况）。

代码实现 #

import pandas as pd

# 示例数据集
df = pd.DataFrame({
    'ID': ['A','A','A','A'],
    'target': ['B','B','B','B'],
    'length':[208,315,1987,3775],
    'start':[139403,140668,141726,143705],
    'end':[139609,140982,143711,147467]
})

def merge_overlapping_intervals(group):
    # 按start排序区间，保证处理顺序正确
    sorted_group = group.sort_values('start')
    merged_intervals = []
    for _, row in sorted_group.iterrows():
        current_start, current_end = row['start'], row['end']
        if not merged_intervals:
            merged_intervals.append([current_start, current_end])
        else:
            last_start, last_end = merged_intervals[-1]
            # 检查当前区间与上一个合并区间是否重叠
            if current_start <= last_end:
                # 合并区间，更新为两个区间中最大的end值
                merged_intervals[-1][1] = max(last_end, current_end)
            else:
                merged_intervals.append([current_start, current_end])
    # 计算合并后所有区间的总长度
    total_length = sum(end - start for start, end in merged_intervals)
    return pd.Series({'total_length': total_length})

# 分组计算去重后的总长度
result = df.groupby(['ID', 'target']).apply(merge_overlapping_intervals)
print(result)

输出结果 #

total_length
ID target              
A  B               6279

如果需要保留原数据结构并调整length列（和你示例中的逻辑一致），可以用以下代码：

def adjust_length_for_overlap(group):
    sorted_group = group.sort_values('start').copy()
    # 获取前一个区间的end值
    sorted_group['prev_end'] = sorted_group['end'].shift(1)
    # 计算当前区间与前一个区间的重叠长度
    sorted_group['overlap'] = sorted_group.apply(
        lambda x: max(0, min(x['end'], x['prev_end']) - x['start']) if pd.notna(x['prev_end']) else 0,
        axis=1
    )
    # 调整length：原length减去重叠部分
    sorted_group['adjusted_length'] = sorted_group['length'] - sorted_group['overlap']
    return sorted_group

# 应用函数生成调整后的数据集
adjusted_df = df.groupby(['ID', 'target'], group_keys=False).apply(adjust_length_for_overlap)
print(adjusted_df)

# 按分组求和
print(adjusted_df.groupby(['ID', 'target'])['adjusted_length'].sum())

调整后的数据集输出 #

ID target  length   start     end  prev_end  overlap  adjusted_length
0  A      B     208  139403  139609       NaN        0              208
1  A      B     315  140668  140982  139609.0        0              315
2  A      B    1987  141726  143711  140982.0        0             1987
3  A      B    3775  143705  147467  143711.0        6             3769

求和结果同样为6279。

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