python - 相当于多列的vlookup的Pandas

我想在多个用户列下为每个用户返回total_points列。

解释清楚一点

{'secondBoxer1': {0: 'Cody',
  1: 'Billy',
  2: 'Jennifer',
  3: 'Franc',
  4: 'Mark'},
 'secondBoxer2': {0: 'Tamis',
  1: 'Danye',
  2: 'Leesa',
  3: 'Hector',
  4: 'Coy'},
 'secondBoxer3': {0: 'Davin',
  1: 'Delbert',
  2: 'Kanisca',
  3: 'Luis',
  4: 'nan'},
 'secondBoxer4': {0: 'Caro',
  1: 'John',
  2: 'nan',
  3: 'Jose',
  4: 'nan'},
 'secondBoxer5': {0: 'Caro',
  1: 'Ryan',
  2: 'nan',
  3: 'Jose',
  4: 'nan'},
 'secondBoxer6': {0: 'nan', 1: 'nan', 2: 'nan', 3: 'Luis', 4: 'nan'}}

我有五个secondBoxer列，对于每个Boxer列，我希望将该列与来自与secondBoxer列下的名称相对应的不同数据框的总点数列合并

    name            total_points
0   Hector            50.000
1   John              48.000
2   Jose              30.000
3   Luis              31.875
4   Billy             27.500

在这种情况下，所需的输出为

secondBoxer1  total_points1  secondBoxer2  total_points2  ....
  Cody                          Tamis
  Billy          27.500         Danye
  Jeniffer                      Leesa
  Franc                         Hector        50.000
  Mark                          Coy

我尝试将for循环合并以遍历所有列（实际数据集具有50多个secondBoxer cols）并与第二个数据集合并以获得total_points，但未成功。

listen = ['secondBoxer1','secondBoxer2','secondBoxer3','secondBoxer4','secondBoxer5','secondBoxer6']
for i in listen:
    df=df.merge(df2[['name','total_points']],left_on=i,right_on='name')

但是，这将返回一个空的数据集

最佳答案

IIUC依次为map和concat

out1=out.apply(lambda x : x.map(dict(zip(df.name,df.total_points))))
out1.columns='total_points'+out1.columns.str.strip('secondBoxer')
out=pd.concat([out,out1],axis=1)

在这里，我们需要argsort重新排列数字

out=out.iloc[:,out.columns.str.extract('(\d+)')[0].argsort()]

out
Out[151]:
  secondBoxer1  total_points1  ... secondBoxer6  total_points6
0         Cody            NaN  ...          nan            NaN
1        Billy           27.5  ...          nan            NaN
2     Jennifer            NaN  ...          nan            NaN
3        Franc            NaN  ...         Luis         31.875
4         Mark            NaN  ...          nan            NaN
[5 rows x 12 columns]

关于python - 相当于多列的vlookup的Pandas，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/59036969/