Multiply 1 Dataframe by a row in another one selected based on its index value-CodePudding

I am pulling my hair on this one.

I have 2 Dataframes:

df1 holds data for Players with their position (zone) (Forward, Midfield or Defender) and some of their

game stats.

df1 = pd.DataFrame({'Zone': ['DEF', 'MID', 'FWD'], 'Tackles': [5, 10, 5], 'Goals': [0, 1, 1], 'Shots': [10, 5, 2]} , index=(['Player A', 'Player B', 'Player C']))

         Zone  Tackles  Goals  Shots
Player A  DEF        5      0     10
Player B  MID       10      1      5
Player C  FWD        5      1      2

df2 holds the weight that I want to apply to calculate a performance index for each player. The weights depend on the players positions

df2 = pd.DataFrame({'Tackles': [1, 2, 4], 'Goals': [10, 5, 2], 'Shots': [3, 3, 1]}, index=(['FWD', 'MID', 'DEF']))

     Tackles  Goals  Shots
FWD        1     10      3
MID        2      5      3
DEF        4      2      1

I would like to multiply each row in df1 by its corresponding row in df2

This is what I want to get:

         Zone  Tackles  Goals  Shots  Index
Player A  DEF        5      0     10    30.0 (5*4   0*2   10*1)
Player B  MID       10      1      5    40.0 (10*2   1*5  5*3)
Player C  FWD        5      1      2    21.0 (5*1   1*10  2*3)

What I have tried is this:

df1['Index'] = (df1 * df2.loc[df1['Zone']]).sum(axis=1)

But it doesn't work...

Thank you very much in advance for your help

CodePudding user response：

Append temporary Zone as index of df1:

df1['Index'] = df1.set_index('Zone', append=True).mul(df2, level=1).sum(axis=1).values
print(df1)

# Output
         Zone  Tackles  Goals  Shots  Index
Player A  DEF        5      0     10     30
Player B  MID       10      1      5     40
Player C  FWD        5      1      2     21