0

I have two dataframes: (i) One has two indexes and two headers, and (ii) the other one has one index and one header. The second level of each axis in the first dataframe relates to each axis of the second dataframe. I need to multiply both dataframes based on that relation between the axis.

Dataframe 1:

Dataframe 1

Dataframe 2:

Dataframe 2

Expected result (multiplication by index/header):

enter image description here

Javier Lorenzini
  • 23
  • 1
  • 4
  • Image samples cannot be copy and pasted, therefore are not easily re-create-able. Please read [this](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) article on how to post a reproducible `pandas` example. – Ukrainian-serge Mar 08 '20 at 01:55

1 Answers1

1

Try using pd.DataFrame.mul with the level parameter:

import pandas as pd

df = pd.DataFrame([[9,10,2,1,6,5],
                   [4, 0,3,4,6,6],
                   [9, 3,9,1,2,3],
                   [3, 5,9,3,9,0],
                   [4,4,8,5,10,5],
                   [5, 3,1,8,5,6]])

df.columns = pd.MultiIndex.from_arrays([[2020]*3+[2021]*3,[1,2,3,1,2,3]])

df.index = pd.MultiIndex.from_arrays([[1]*3+[2]*3,[1,2,3,1,2,3]])

print(df)
print('\n')
df2 = pd.DataFrame([[.1,.3,.6],[.4,.4,.3],[.5,.4,.1]], index=[1,2,3], columns=[1,2,3])

print(df2)
print('\n')
df_out = df.mul(df2, level=1)

print(df_out)

Output:

   2020        2021       
       1   2  3    1   2  3
1 1    9  10  2    1   6  5
  2    4   0  3    4   6  6
  3    9   3  9    1   2  3
2 1    3   5  9    3   9  0
  2    4   4  8    5  10  5
  3    5   3  1    8   5  6


     1    2    3
1  0.1  0.3  0.6
2  0.4  0.4  0.3
3  0.5  0.4  0.1


    2020           2021          
       1    2    3    1    2    3
1 1  0.9  3.0  1.2  0.1  1.8  3.0
  2  1.6  0.0  0.9  1.6  2.4  1.8
  3  4.5  1.2  0.9  0.5  0.8  0.3
2 1  0.3  1.5  5.4  0.3  2.7  0.0
  2  1.6  1.6  2.4  2.0  4.0  1.5
  3  2.5  1.2  0.1  4.0  2.0  0.6
Scott Boston
  • 147,308
  • 15
  • 139
  • 187