熊猫-按2列进行排序并比较另一列中的值

Question

请考虑这一数据框架：

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']

})

在这个数据框架中，我们有两个区域，在每个区域中有多个员工角色。“薪资”列包含该区域中该角色的薪资。假设所有薪资数字都具有相同的货币。

现在，我想确保，对于任何角色，CAN地区的工资必须至少和美国一样高--而欧盟的工资必须至少和美国的工资一样高。

如何解决这个问题，以便得到以下数据框架？

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

})

请注意，这是一个示例数据框架-在实际数据框架中，我有一些额外的列，我想保持不变。谢谢!

Answer 1

另一种使用groupby和累积最大值的解决方案。我喜欢这种方法，因为您可以通过向自定义排序顺序中添加其他区域来相对容易地扩展需要支持的区域数。

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']})

# Replace the region with a categorical variable to ensure sorting order is US, CAN, EU
df["REGION"] = pd.Categorical(df["REGION"], ["US", "CAN", "EU"])
df = df.sort_values(["ROLE", "REGION"])
df = df.groupby("ROLE").apply(lambda x: x.assign(SALARY=x["SALARY"].cummax()))
# if you need your data in the original order again
df = df.sort_index()

Answer 2

import pandas as pd

data = 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']})

pt = pd.pivot_table(data, values=['salary'], index=['role'], columns=['region'])

df = pt['salary'].fillna(0)

df['CAN'] = df.apply(lambda x: max(x['US'], x['CAN']), axis=1)
df['EU'] = df.apply(lambda x: max(x['CAN'], x['EU']), axis=1)

data['salary'] = data.apply(lambda x: df[x['region']][x['role']], axis=1)

print(data)

Answer 3

通过在MultiIndex中映射和切片解决方案，对于设置值，我使用Series.clip

df = df.set_index(['REGION','ROLE'])
df1 = df.copy()

us = df.loc['US', 'SALARY']
can = df.loc['CAN', 'SALARY']
eu = df.loc['EU', 'SALARY']

df.loc['CAN', 'SALARY'] = can.clip(lower=can.index.map(us)).to_numpy()
df.loc['EU', 'SALARY'] = eu.clip(lower=eu.index.map(can)).to_numpy()
df = df.fillna(df1).reset_index()
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

另一种解决办法是旋转和不枢轴：

df1 = df.pivot('ROLE','REGION','SALARY')
df1['CAN'] = df1[['CAN','US']].max(axis=1)
df1['EU'] = df1[['CAN','EU']].max(axis=1)
   
df = df.join(df1.stack().rename('new'), on=['ROLE','REGION'])
df['SALARY'] = df.pop('new')
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