map a metrics in pandas-CodePudding

Give a df as

index  x_1  x_2  x_3  x_4 x_5
  0    50   60   70   30   20
  1    10   20   30   40   50
  2    10   20   20   20   20

I have a mapping that maps each row to the corresponding column in df.

mapping df is

index  x_1  x_2  x_3  x_4 x_5
  0    1     3   4   1   2
  1    2     2   2   3   5
  2    4     1   1   5   2

I want to create a mapped df

Desired output

index  x_1  x_2  x_3  x_4 x_5
  0    50    70   30   50  60
  1    20    20   20   30   50
  2    20    10   20   20   20

CodePudding user response：

You can use second dataframe rows as indices of first dataframe rows.

import pandas as pd

df = pd.DataFrame([[50, 60, 70, 30, 20],
                   [10, 20, 30, 40, 50],
                   [10, 20, 20, 20, 20]])

map_df = pd.DataFrame([[1, 3, 4, 1, 2],
                       [2, 2, 2, 3, 5],
                       [4, 1, 1, 5, 2]])

for i in range(df.shape[0]):
    df.iloc[i] = df.iloc[i][map_df.iloc[i] - 1]

print(df)

Result:

    0   1   2   3   4
0  50  70  30  50  60
1  20  20  20  30  50
2  20  10  10  20  20

EDIT:
Solution without loop:

df.join(map, rsuffix='map').apply(lambda row: pd.Series(np.array(row[:5])[row[5:] - 1]).transpose(), axis=1)

CodePudding user response：

Let's do some transpose replace hack

mapping_df.astype(str).radd('x_').T.replace(df.T).T

       x_1  x_2  x_3  x_4  x_5
index                         
0       50   70   30   50   60
1       20   20   20   30   50
2       20   10   10   20   20

CodePudding user response：

late to the game but an alternative using numpy?

import pandas as pd
import numpy as np

df = pd.DataFrame([[50, 60, 70, 30, 20],
                   [10, 20, 30, 40, 50],
                   [10, 20, 20, 20, 20]])

map_df = pd.DataFrame([[1, 3, 4, 1, 2],
                       [2, 2, 2, 3, 5],
                       [4, 1, 1, 5, 2]])

np.take_along_axis(np.array(df),np.array(map_df)-1,1)

array([[50, 70, 30, 50, 60],
       [20, 20, 20, 30, 50],
       [20, 10, 10, 20, 20]])