在Pandas中按组计算坐标重叠数值的实现方法

我有如下DataFrame：

Gps1    Gps2   start end
G1      GA     106   205
G1      GA     102   203
G1      GA     106   203
G1      GA     106   203
G2      GB       9    51
G2      GB      48   135
G2      GB     131   207
G2      GB     207   279
G3      GC     330   419
G3      GC     266   315
G3      GC     257   315
G3      GC     266   407
G4      GC     10    30
G4      GC     60    90

我需要针对每个['Gps1','Gps2']分组，为每行计算重叠坐标数值，计算规则为：当前行的end减去下一行的start。最后一行的数值需与倒数第二行保持一致。

计算示例 #

以G1-GA组为例：

• 第一行：205 - 102 = 103，填入结果列
• 第二行：203 - 106 = 97，填入结果列
• 第三行：203 - 106 = 113，填入结果列
• 第四行：与第三行数值相同，即113

处理后G1-GA组结果：

Gps1    Gps2   start end  Nb_overlapping
G1      GA     106   205  103
G1      GA     102   203  97
G1      GA     106   203  113
G1      GA     106   203  113

G2-GB组处理后结果：

Gps1    Gps2   start end  Nb_overlapping
G2      GB       9    51  3
G2      GB      48   135  4
G2      GB     131   207  0
G2      GB     207   279  0

最终期望结果 #

Gps1    Gps2   start end  Nb_overlapping
G1      GA     106   205  103
G1      GA     102   203  97
G1      GA     106   203  113
G1      GA     106   203  113
G2      GB       9    51  3
G2      GB      48   135  4
G2      GB     131   207  0
G2      GB     207   279  0
G3      GC     330   419  153
G3      GC     266   315  58
G3      GC     257   315  49
G3      GC     266   407  49
G4      GC     10    30   -30
G4      GC     60    90   -30

DataFrame的字典格式参考：

{'Gps1': {0: 'G1', 1: 'G1', 2: 'G1', 3: 'G1', 4: 'G2', 5: 'G2', 6: 'G2', 7: 'G2', 8: 'G3', 9: 'G3', 10: 'G3', 11: 'G3', 12: 'G4', 13: 'G4'}, 'Gps2': {0: 'GA', 1: 'GA', 2: 'GA', 3: 'GA', 4: 'GB', 5: 'GB', 6: 'GB', 7: 'GB', 8: 'GC', 9: 'GC', 10: 'GC', 11: 'GC', 12: 'GC', 13: 'GC'}, 'start': {0: 106, 1: 102, 2: 106, 3: 106, 4: 9, 5: 48, 6: 131, 7: 207, 8: 330, 9: 266, 10: 257, 11: 266, 12: 10, 13: 60}, 'end': {0: 205, 1: 203, 2: 203, 3: 203, 4: 51, 5: 135, 6: 207, 7: 279, 8: 419, 9: 315, 10: 315, 11: 407, 12: 30, 13: 90}}

解决方案 #

可以用Pandas的分组和移位操作实现，步骤如下：

1. 按['Gps1','Gps2']分组，对start列执行向下移位（获取下一行的start值）
2. 计算当前行end与移位后start的差值，得到初步的Nb_overlapping
3. 对每个分组，将最后一行的Nb_overlapping填充为倒数第二行的值

代码实现：

import pandas as pd

# 构造DataFrame
df = pd.DataFrame({
    'Gps1': {0: 'G1', 1: 'G1', 2: 'G1', 3: 'G1', 4: 'G2', 5: 'G2', 6: 'G2', 7: 'G2', 8: 'G3', 9: 'G3', 10: 'G3', 11: 'G3', 12: 'G4', 13: 'G4'},
    'Gps2': {0: 'GA', 1: 'GA', 2: 'GA', 3: 'GA', 4: 'GB', 5: 'GB', 6: 'GB', 7: 'GB', 8: 'GC', 9: 'GC', 10: 'GC', 11: 'GC', 12: 'GC', 13: 'GC'},
    'start': {0: 106, 1: 102, 2: 106, 3: 106, 4: 9, 5: 48, 6: 131, 7: 207, 8: 330, 9: 266, 10: 257, 11: 266, 12: 10, 13: 60},
    'end': {0: 205, 1: 203, 2: 203, 3: 203, 4: 51, 5: 135, 6: 207, 7: 279, 8: 419, 9: 315, 10: 315, 11: 407, 12: 30, 13: 90}
})

# 计算分组内下一行的start值
df['next_start'] = df.groupby(['Gps1', 'Gps2'])['start'].shift(-1)
# 计算初始重叠值
df['Nb_overlapping'] = df['end'] - df['next_start']

# 填充每个分组的最后一行值为倒数第二行的值
def fill_last_row(group):
    if len(group) > 1:
        group.iloc[-1, group.columns.get_loc('Nb_overlapping')] = group.iloc[-2]['Nb_overlapping']
    return group

df = df.groupby(['Gps1', 'Gps2'], group_keys=False).apply(fill_last_row)
# 移除临时列next_start
df.drop('next_start', axis=1, inplace=True)

print(df)

执行上述代码后，即可得到期望的结果。

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