Pandas按两列排序对比薪资 实现同岗位跨区域薪资递进要求

核心思路是给区域定义优先级，同岗位下按优先级升序排列后对薪资取累计最大值，即可自动满足EU ≥ CAN ≥ US的薪资要求，其余列不会被修改。

完整实现代码 #

1. 构造原始数据集 #

import pandas as pd

df = pd.DataFrame({
    'REGION':['US','US','CAN','CAN', 'EU','EU','EU'],
    'ROLE': ['mgr','dir','mgr','dir','mgr','dir','CEO'],
    'SALARY' : [4,5,3.7,6,4.1,5.5,8],
    'other_columns':['random_val1','random_val2','random_val3','random_val4','random_val5','random_val6','random_val7']
})

2. 薪资调整逻辑 #

# 定义区域优先级，数值越小要求的薪资基准越低
region_order = {'US': 1, 'CAN': 2, 'EU': 3}

# 新增辅助列：区域优先级、原始索引（用于后续恢复数据顺序）
df['region_priority'] = df['REGION'].map(region_order)
df['original_idx'] = df.index

# 按岗位分组，组内按区域优先级升序排序，薪资取累计最大值
df = df.sort_values(['ROLE', 'region_priority'])
df['SALARY'] = df.groupby('ROLE')['SALARY'].cummax()

# 恢复原始数据顺序，删除辅助列
df = df.sort_values('original_idx').drop(['region_priority', 'original_idx'], axis=1).reset_index(drop=True)

3. 输出验证 #

print(df)

运行后输出与目标结果完全一致：

REGION ROLE  SALARY other_columns
0     US  mgr     4.0   random_val1
1     US  dir     5.0   random_val2
2    CAN  mgr     4.0   random_val3
3    CAN  dir     6.0   random_val4
4     EU  mgr     4.1   random_val5
5     EU  dir     6.0   random_val6
6     EU  CEO     8.0   random_val7

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