如何基于坐标距离阈值为Sps和Gps分组并添加Threshold_gps列？

现有结构化数据如下：

Sps       Gps start  end
SP1       G1      2    322
SP1       G1    318   1368
SP1       G1  21125  22297
SP2       G2      2    313
SP2       G2    334   1359
SP2       G2  11716  11964
SP2       G2  20709  20885
SP2       G2  21080  22297
SP3       G3      2    313
SP3       G3    328   1368
SP3       G3  21116  22294 
SP4       G4    346   1356
SP4       G4  21131  22282

需要为每个Sps+Gps的分组添加新列Threshold_gps，分组规则：

• 计算当前行start与上一行end的差值（start - end）
• 若差值的绝对值小于500，两行归为同一组；否则创建新组
• 组名依次命名为G1、G2……

示例（SP1-G1分组）：
原始数据：

Sps       Gps start  end
SP1       G1      2    322
SP1       G1    318   1368
SP1       G1  21125  22297

• 318-322=-4，绝对值<500，归为同一组G1
• 21125-1368=19757，绝对值>500，创建新组G2

处理后结果：

Sps       Gps start  end    Threshold_gps
SP1       G1      2    322  G1
SP1       G1    318   1368  G1
SP1       G1  21125  22297  G2 

最终期望结果：

Sps       Gps start  end    Threshold_gps
SP1       G1      2    322  G1
SP1       G1    318   1368  G1
SP1       G1  21125  22297  G2 
SP2       G2      2    313  G1
SP2       G2    334   1359  G1
SP2       G2  11716  11964  G2
SP2       G2  20709  20885  G3 
SP2       G2  21080  22297  G3
SP3       G3      2    313  G1
SP3       G3    328   1368  G1
SP3       G3  21116  22294  G2
SP4       G4    346   1356  G1
SP4       G4  21131  22282  G2

附数据字典格式：

{'Sps': {0: 'SP1', 1: 'SP1', 2: 'SP1', 3: 'SP2', 4: 'SP2', 5: 'SP2', 6: 'SP2', 7: 'SP2', 8: 'SP3', 9: 'SP3', 10: 'SP3', 11: 'SP4', 12: 'SP4'}, 'Gps': {0: 'G1', 1: 'G1', 2: 'G1', 3: 'G2', 4: 'G2', 5: 'G2', 6: 'G2', 7: 'G2', 8: 'G3', 9: 'G3', 10: 'G3', 11: 'G4', 12: 'G4'}, 'start': {0: 2, 1: 318, 2: 21125, 3: 2, 4: 334, 5: 11716, 6: 20709, 7: 21080, 8: 2, 9: 328, 10: 21116, 11: 346, 12: 21131}, 'end': {0: 322, 1: 1368, 2: 22297, 3: 313, 4: 1359, 5: 11964, 6: 20885, 7: 22297, 8: 313, 9: 1368, 10: 22294, 11: 1356, 12: 22282}}

可以用Python的pandas库快速实现，步骤如下：

1. 导入库并加载数据 #

import pandas as pd

# 加载给定的字典数据
data = {'Sps': {0: 'SP1', 1: 'SP1', 2: 'SP1', 3: 'SP2', 4: 'SP2', 5: 'SP2', 6: 'SP2', 7: 'SP2', 8: 'SP3', 9: 'SP3', 10: 'SP3', 11: 'SP4', 12: 'SP4'}, 'Gps': {0: 'G1', 1: 'G1', 2: 'G1', 3: 'G2', 4: 'G2', 5: 'G2', 6: 'G2', 7: 'G2', 8: 'G3', 9: 'G3', 10: 'G3', 11: 'G4', 12: 'G4'}, 'start': {0: 2, 1: 318, 2: 21125, 3: 2, 4: 334, 5: 11716, 6: 20709, 7: 21080, 8: 2, 9: 328, 10: 21116, 11: 346, 12: 21131}, 'end': {0: 322, 1: 1368, 2: 22297, 3: 313, 4: 1359, 5: 11964, 6: 20885, 7: 22297, 8: 313, 9: 1368, 10: 22294, 11: 1356, 12: 22282}}
df = pd.DataFrame(data)

2. 按分组规则生成Threshold_gps列 #

核心逻辑：对每个Sps+Gps分组，计算行间差值判断是否创建新组，累计生成分组编号后转为G1、G2格式。

def assign_threshold_group(group):
    # 计算当前行start与上一行end的差值
    diff = group['start'] - group['end'].shift(1)
    # 标记新组起点：第一行或差值绝对值>=500的行
    new_group = (diff.abs() >= 500) | (diff.isna())
    # 累计新组数量得到分组编号
    group_num = new_group.cumsum()
    # 转为G1、G2格式的组名
    group['Threshold_gps'] = 'G' + group_num.astype(str)
    return group

# 按Sps和Gps分组应用函数
df = df.groupby(['Sps', 'Gps'], group_keys=False).apply(assign_threshold_group)

3. 查看结果 #

print(df.to_string(index=False))

运行后输出结果与期望完全一致：

Sps Gps  start    end Threshold_gps
SP1  G1      2    322            G1
SP1  G1    318   1368            G1
SP1  G1  21125  22297            G2
SP2  G2      2    313            G1
SP2  G2    334   1359            G1
SP2  G2  11716  11964            G2
SP2  G2  20709  20885            G3
SP2  G2  21080  22297            G3
SP3  G3      2    313            G1
SP3  G3    328   1368            G1
SP3  G3  21116  22294            G2
SP4  G4    346   1356            G1
SP4  G4  21131  22282            G2

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