如何在Pandas中按Groups-Scaff分组合并重叠起止坐标

原始数据 #

表格形式 #

Groups Scaff  start    end
G1     Scaff1 2278     4437
G1     Scaff1 2788     3048
G1     Scaff1 3959     4183
G1     Scaff1 4201     4407
G1     Scaff2 4553     5000
G1     Scaff2 6321     7700
G1     Scaff3 2870     5083
G1     Scaff4 1923     2042
G1     Scaff5 663      2885
G1     Scaff5 2145     2825

字典格式 #

{
    'Groups': {0: 'G1', 1: 'G1', 2: 'G1', 3: 'G1', 4: 'G1', 5: 'G1', 6: 'G1', 7: 'G1', 8: 'G1', 9: 'G1'},
    'Scaff': {0: 'Scaff1', 1: 'Scaff1', 2: 'Scaff1', 3: 'Scaff1', 4: 'Scaff2', 5: 'Scaff2', 6: 'Scaff3', 7: 'Scaff4', 8: 'Scaff5', 9: 'Scaff5'},
    'start': {0: 2278, 1: 2788, 2: 3959, 3: 4201, 4: 4553, 5: 6321, 6: 2870, 7: 1923, 8: 663, 9: 2145},
    'end': {0: 4437, 1: 3048, 2: 4183, 3: 4407, 4: 5000, 5: 7700, 6: 5083, 7: 2042, 8: 2885, 9: 2825}
}

需求说明 #

按Groups-Scaff组合分组，将直接重叠或间接重叠的坐标区间归为同一个New_group：

• G1-Scaff1的所有区间属于间接重叠，应同属一个组
• G1-Scaff2的两个区间无重叠，需分属不同组

现有代码问题 #

用户尝试的代码仅能识别直接不重叠的情况，无法处理间接重叠场景，导致G1-Scaff1的区间被错误拆分到多个组：

is_overlapped = lambda x: x['start'] >= x['end'].shift(fill_value=-1)
tab['New_group'] = tab.sort_values(['Groups','Scaff','start','end']).groupby(['Groups','Scaff'],as_index=False).apply(is_overlapped).droplevel(0).cumsum()

修正方案 #

方法一：向量化实现（高效简洁） #

通过累积最大值跟踪当前组的最大结束位置，判断是否开启新组：

import pandas as pd

# 构造DataFrame
tab = pd.DataFrame({
    'Groups': {0: 'G1', 1: 'G1', 2: 'G1', 3: 'G1', 4: 'G1', 5: 'G1', 6: 'G1', 7: 'G1', 8: 'G1', 9: 'G1'},
    'Scaff': {0: 'Scaff1', 1: 'Scaff1', 2: 'Scaff1', 3: 'Scaff1', 4: 'Scaff2', 5: 'Scaff2', 6: 'Scaff3', 7: 'Scaff4', 8: 'Scaff5', 9: 'Scaff5'},
    'start': {0: 2278, 1: 2788, 2: 3959, 3: 4201, 4: 4553, 5: 6321, 6: 2870, 7: 1923, 8: 663, 9: 2145},
    'end': {0: 4437, 1: 3048, 2: 4183, 3: 4407, 4: 5000, 5: 7700, 6: 5083, 7: 2042, 8: 2885, 9: 2825}
})

# 先按分组键和区间起始排序
tab_sorted = tab.sort_values(['Groups', 'Scaff', 'start', 'end']).reset_index(drop=True)

# 分组计算到当前行的最大结束值（处理间接重叠）
tab_sorted['max_end_so_far'] = tab_sorted.groupby(['Groups', 'Scaff'])['end'].cummax().shift(fill_value=-1)
# 判断是否需要开启新组：当前起始 > 之前所有区间的最大结束值
tab_sorted['new_group_flag'] = (tab_sorted['start'] > tab_sorted['max_end_so_far']).astype(int)
# 累积求和生成分组ID
tab_sorted['New_group'] = tab_sorted.groupby(['Groups', 'Scaff'])['new_group_flag'].cumsum()

# 清理中间列，得到最终结果
result = tab_sorted.drop(['max_end_so_far', 'new_group_flag'], axis=1)
print(result)

方法二：循环实现（逻辑直观） #

逐行判断区间是否和当前组重叠，手动维护最大结束值：

import pandas as pd

tab = pd.DataFrame({
    'Groups': {0: 'G1', 1: 'G1', 2: 'G1', 3: 'G1', 4: 'G1', 5: 'G1', 6: 'G1', 7: 'G1', 8: 'G1', 9: 'G1'},
    'Scaff': {0: 'Scaff1', 1: 'Scaff1', 2: 'Scaff1', 3: 'Scaff1', 4: 'Scaff2', 5: 'Scaff2', 6: 'Scaff3', 7: 'Scaff4', 8: 'Scaff5', 9: 'Scaff5'},
    'start': {0: 2278, 1: 2788, 2: 3959, 3: 4201, 4: 4553, 5: 6321, 6: 2870, 7: 1923, 8: 663, 9: 2145},
    'end': {0: 4437, 1: 3048, 2: 4183, 3: 4407, 4: 5000, 5: 7700, 6: 5083, 7: 2042, 8: 2885, 9: 2825}
})

tab_sorted = tab.sort_values(['Groups', 'Scaff', 'start', 'end']).reset_index(drop=True)

def assign_groups(group):
    group['New_group'] = 1
    max_end = group['end'].iloc[0]
    for i in range(1, len(group)):
        if group['start'].iloc[i] > max_end:
            group['New_group'].iloc[i] = group['New_group'].iloc[i-1] + 1
            max_end = group['end'].iloc[i]
        else:
            group['New_group'].iloc[i] = group['New_group'].iloc[i-1]
            if group['end'].iloc[i] > max_end:
                max_end = group['end'].iloc[i]
    return group

result = tab_sorted.groupby(['Groups', 'Scaff'], group_keys=False).apply(assign_groups)
print(result)

预期结果 #

最终输出的New_group列符合需求：

• G1-Scaff1的所有行New_group为1
• G1-Scaff2的两行New_group分别为1、2
• G1-Scaff5的两行New_group为1（区间重叠）

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