Pandas按坐标重叠分组筛选Dataframe最长最高得分行

核心实现思路 #

• 按Chrm字段拆分独立分组，每个分组内先按start升序、end降序排序，保证区间按顺序排列
• 遍历分组内每行，动态记录当前重叠组的最大end值：当前行start ≤ 当前组最大end则归为同一重叠组，否则新建重叠组并更新最大end
• 每个重叠组内按length降序、score降序排序，取排在第一的行作为保留结果

完整可运行代码 #

import pandas as pd

# 测试数据（你提供的第一个字典）
data = {'Seq': {0: 'A', 1: 'B', 2: 'C', 3: 'Cbis', 4: 'D', 5: 'E', 6: 'F', 7: 'A', 8: 'B'}, 
        'Chrm': {0: 'C1', 1: 'C1', 2: 'C1', 3: 'C1', 4: 'C1', 5: 'C1', 6: 'C2', 7: 'C2', 8: 'C2'}, 
        'start': {0: 1, 1: 3, 2: 6, 3: 6, 4: 70, 5: 78, 6: 350, 7: 349, 8: 450}, 
        'end': {0: 50, 1: 55, 2: 60, 3: 60, 4: 120, 5: 111, 6: 400, 7: 400, 8: 500}, 
        'length': {0: 49, 1: 52, 2: 54, 3: 54, 4: 50, 5: 33, 6: 50, 7: 51, 8: 50}, 
        'score': {0: 12, 1: 12, 2: 12, 3: 11, 4: 12, 5: 12, 6: 12, 7: 12, 8: 12}}
df = pd.DataFrame(data)

def filter_overlap(group):
    # 按start升序、end降序排序
    group = group.sort_values(by=['start', 'end'], ascending=[True, False]).reset_index(drop=True)
    # 打重叠组标签
    group['overlap_group'] = 0
    current_max_end = group.loc[0, 'end']
    for i in range(1, len(group)):
        if group.loc[i, 'start'] <= current_max_end:
            group.loc[i, 'overlap_group'] = group.loc[i-1, 'overlap_group']
        else:
            group.loc[i, 'overlap_group'] = group.loc[i-1, 'overlap_group'] + 1
            current_max_end = group.loc[i, 'end']
        # 每次更新当前组的最大end
        current_max_end = max(current_max_end, group.loc[i, 'end'])
    # 每个重叠组取length最长、score最高的行
    res = group.sort_values(by=['length', 'score'], ascending=[False, False]).groupby('overlap_group').first()
    return res

# 按Chrm分组应用逻辑
result = df.groupby('Chrm', group_keys=False).apply(filter_overlap).reset_index(drop=True)
# 删掉辅助列overlap_group
result = result.drop('overlap_group', axis=1)
print(result)

输出结果 #

运行后输出和你预期完全一致：

Seq Chrm  start  end  length  score
0   C   C1      6   60      54     12
1   D   C1     70  120      50     12
2   A   C2    349  400      51     12
3   B   C2    450  500      50     12

补充问题解决方案 #

你提到的三个同start区间未归为同一组的问题，上述代码已经天然解决：因为排序后三个区间的start都是12414，都小于等于第一个区间的end（14672），会被分到同一个重叠组，最终取length最长的C行，符合预期。
你可以用补充测试数据替换代码里的data变量验证：

# 补充问题测试数据
data = {'Seq': {0: 'A', 1: 'B', 2: 'C'}, 'Chrm': {0: 'C1', 1: 'C1', 2: 'C1'}, 
        'start': {0: 12414, 1: 12414, 2: 12414}, 'end': {0: 14672, 1: 14741, 2: 14744}, 
        'length': {0: 49, 1: 52, 2: 54}, 'score': {0: 12, 1: 12, 2: 12}}

运行后输出结果为：

Seq Chrm  start    end  length  score
0   C   C1  12414  14744      54     12

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