CODEWITHSUNDEEP
python-3.xpandasdataframe
table_101
table_101

Reputation: 139

Multiply 2 different dataframe with same dimension and repeating rows

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

Upvotes: 3

Views: 41

Answers (3)

anky
anky

Reputation: 75080

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

Upvotes: 4

ansev
ansev

Reputation: 30920

We could also use DataFrame.merge with pd.Index.difference to select columns.

mul_cols = df1.columns.difference(['Name','Key'])                                                                                
df1.assign(**df1[mul_cols].mul(df2.merge(df1[['Key']],
                                             left_on = 'Key_1',
                                             right_on = 'Key')[mul_cols]))

  Name Key  100  101  102  103  104
0  Abb  AB   20   12   10   25    1
1  Bcc  BC   10    6    7   20    2
2  Abb  AB    5   10   22    6    4
3  Bcc  BC    7   10    8   10    0

Upvotes: 3

BENY
BENY

Reputation: 323276

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

Upvotes: 3

Related Questions