Multiply 2 different dataframe with same dimension and repeating rows

Question

I am trying to multiply two data frame

Df1

Name|Key |100|101|102|103|104
Abb   AB   2   6  10   5   1
Bcc   BC   1   3   7   4   2
Abb   AB   5   1  11   3   1
Bcc   BC   7   1   4   5   0

Df2

Key_1|100|101|102|103|104
AB     10  2   1   5   1
BC     1  10   2   2   4

Expected Output

Name|Key |100|101|102|103|104
Abb   AB  20  12   10  25   1
Bcc   BC   1  30   14   8   8
Abb   AB  50   2   11  15  1
Bcc   BC   7  10   8   10   0

I have tried grouping Df1 and then multiplying with Df2 but it didn't work Please help me on how to approach this problem

Answer 1

You can rename the df2 Key_1 to Key(similar to df1) , then set index and mul on level=1

df1.set_index(['Name','Key']).mul(df2.rename(columns={'Key_1':'Key'})
                              .set_index('Key'),level=1).reset_index()

Or similar:

df1.set_index(['Name','Key']).mul(df2.set_index('Key_1')
   .rename_axis('Key'),level=1).reset_index()

As correctly pointed by @QuangHoang , you can do without renaming too:

df1.set_index(['Name','Key']).mul(df2.set_index('Key_1'),level=1).reset_index()

---

  Name Key  100  101  102  103  104
0  Abb  AB   20   12   10   25    1
1  Bcc  BC    1   30   14    8    8
2  Abb  AB   50    2   11   15    1
3  Bcc  BC    7   10    8   10    0

Answer 2

IIUC reindex_like

df1.set_index('Key',inplace=True)
df1=df1.mul(df2.set_index('Key_1').reindex_like(df1).values).fillna(df1)
Out[235]: 
    Name   100   101   102   103  104
Key                                  
AB   Abb  20.0  12.0  10.0  25.0  1.0
BC   Bcc   1.0  30.0  14.0   8.0  8.0
AB   Abb  50.0   2.0  11.0  15.0  1.0
BC   Bcc   7.0  10.0   8.0  10.0  0.0