Multilevel column and row indexing and multiplication

user_9244542 0
Viewed 127

I have the following two pandas dataframes. I need to multiply df1 by df2 for corresponding df1 Col 2 and df2 columns.

df1

Col 1                A                B     
Col 2           1    2   3       1    2    3 
date                        
2017-01-01      1    2   1       8    6    2
2017-01-02      2    4   2       3    2    1
2017-01-03      3    3   5       2    3    5

df2

date          1      2    3
2017-01-01   20%    10%   5%
2017-01-02   30%    20%   15%
2017-01-03   40%    30%   12%

I am looking to multiply col 2 of table 1 with corresponding columns of table 2. For eg, for column A column 1 on 2017-01-01, values 1*20% to get 0.2. See the final required table below:

Col 1                   A                  B        
Col 2              1    2    3       1     2      3
date                            
2017-01-01      0.2   0.2   0.05    1.6   0.6   0.1
2017-01-02      0.6   0.8   0.3     0.9   0.4   0.15
2017-01-03      1.2   0.9   0.6     0.8   0.9   0.6

I have tried 3 for loops with date and column ranges, but the df1[i][j].loc[date] is not indexing properly. I have been stuck at this for a while now and would really appreciate help.

pandas dataframe python-3.x python
1 Answers

Use the level parameter

df1.mul(df2, level=1)

Col 1  A      B    
Col 2  1   2  1   2
0      4  10  4  40

Setup

df1 = pd.DataFrame([
    [1, 2, 1, 8]
], columns=pd.MultiIndex.from_product([[*'AB'], [1, 2]], names=['Col 1', 'Col 2']))

df2 = pd.DataFrame([[4, 5]], columns=[1, 2])
user_23366540
Related