How to multiply each column in a dataframe with a row from another dataframe pandas?

Question

I have two dataframes shown below; I want to multiply each column of df1 with each value in the corresponding row in df2. Each time should produce a new column. (It is better explained with an example).

Df1 (in my actual problem there are +1000 rows)

    'Fert 1'  'Fert 2'   'Fert 3'
A     1000      900        800
B     100       90         80
C     10        9          8
D     0.1       0.9        0.8

Df2 (smaller df where the row names are the same as df1 column names)

            'L1'  'L2' 
'Fert 1'     1     0.5   
'Fert 2'     2     0
'Fert 3'     1     0.5

Desired result: Basically I want df1 that has been multiplied by df2 and expanded (I want the column names to be multi-index). I can do it with a nested loop although I can't get the correct column headers that way. And looping seems like an inefficient way of doing it because it gets slow with my bigger dataset. I am hoping it can be done using a merge or concat but I just can't work it out.

        'Fert 1'      'Fert 2'        'Fert 3'
      'L1'    'L2'  'L1'    'L2'    'L1'    'L2'
A     1000    500    1800     0      800     400
B     100     50     180      0      80      40
C     10      5      18       0      8       4
D     0.1     0.05   1.8      0      0.8     0.4

Thanks for any help!

jezrael · Accepted Answer · 2019-11-21 12:37:48Z

3

Create MultiIndex by DataFrame.stack, repeat columns by DataFrame.reindex so possible multiple by Series with DataFrame.mul:

s = Df2.stack()
df = Df1.reindex(s.index, axis=1, level=0).mul(s)
print (df)
  'Fert 1'         'Fert 2'      'Fert 3'       
      'L1'    'L2'     'L1' 'L2'     'L1'   'L2'
A   1000.0  500.00   1800.0  0.0    800.0  400.0
B    100.0   50.00    180.0  0.0     80.0   40.0
C     10.0    5.00     18.0  0.0      8.0    4.0
D      0.1    0.05      1.8  0.0      0.8    0.4

answered Nov 21, 2019 at 12:37

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to multiply each column in a dataframe with a row from another dataframe pandas?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related