I have a dataframe "A" which is multi indexed shown below
LL SK Di Co
Bracket yr_wk
1 121 2 2 4 3
122 3 6 5 4
123 3 2 6 2
124 2 5 5 3
125 3 5 6 3
2 121 4 7 1 6
122 1 5 1 7
123 3 9 6 4
124 5 1 5 6
125 8 7 7 2
Another dataframe "B" which is single index
Factor
yr_wk
121 0.98
122 1.045
123 0.92
124 0.99
125 0.95
I am trying to multiple the factor column of dataframe B with columns of A, grouped by the yr_wk column. Below is the resultant dataframe which I am trying to calculate
LL SK Di Co
Bracket yr_wk
1 121 2*0.98 2*0.98 4*0.98 3*0.98
122 3*1.045 6*1.045 5*1.045 4*1.045
123 3*0.92 2*0.92 6*0.92 2*0.92
124 2*0.99 5*0.99 5*0.99 3*0.99
125 3*0.95 5*0.95 6*0.95 3*0.95
2 121 4*0.98 7*0.98 1*0.98 6*0.98
122 1*1.045 5*1.045 1*1.045 7*1.045
123 3*0.92 9*0.92 6*0.92 4*0.92
124 5*0.99 1*0.99 5*0.99 6*0.99
125 8*0.95 7*0.95 7*0.95 2*0.95
Below is what I tried but it is not working because I am messing up the index
C= A.multiply(B)
CodePudding user response：
import pandas as pd
df = pd.DataFrame({'yr_wk': [121, 122,123], 'LL': [2, 3, 3], 'SK':[2,6,2]})
df2 = pd.DataFrame({'yr_wk': [121, 122,123], 'Factor': [0.98, 1.045, 0.92]})
df = df.merge(df2, on = 'yr_wk')
for key in ['LL', 'SK']:
df[key] = df[key] * df['Factor']
df = df.drop('Factor', axis=1)
df
CodePudding user response：
try this:
factor = dfA.merge(
dfB,
how='left',
left_on=dfA.index.get_level_values(1),
right_index=True)['Factor']
result = dfA.mul(factor, axis=0)
print(result)
>>>
LL SK Di Co
Bracket yr_wk
1 121 1.960 1.960 3.920 2.940
122 3.135 6.270 5.225 4.180
123 2.760 1.840 5.520 1.840
124 1.980 4.950 4.950 2.970
125 2.850 4.750 5.700 2.850
2 121 3.920 6.860 0.980 5.880
122 1.045 5.225 1.045 7.315
123 2.760 8.280 5.520 3.680
124 4.950 0.990 4.950 5.940
125 7.600 6.650 6.650 1.900
CodePudding user response：
First
use broad casting
df_A.unstack(0).mul(df_B['Factor'], axis=0).stack()
LL SK Di Co
yr_wk Bracket
121 1 1.96 1.96 3.92 2.94
2 3.92 6.86 0.98 5.88
122 1 3.13 6.27 5.22 4.18
2 1.04 5.22 1.04 7.31
123 1 2.76 1.84 5.52 1.84
2 2.76 8.28 5.52 3.68
124 1 1.98 4.95 4.95 2.97
2 4.95 0.99 4.95 5.94
125 1 2.85 4.75 5.70 2.85
2 7.60 6.65 6.65 1.90
Second
swaplevel and sort_index to make desired output (include First Code)
df_A.unstack(0).mul(df_B['Factor'], axis=0).stack().swaplevel(0, 1).sort_index()
output:
LL SK Di Co
Bracket yr_wk
1 121 1.96 1.96 3.92 2.94
122 3.13 6.27 5.22 4.18
123 2.76 1.84 5.52 1.84
124 1.98 4.95 4.95 2.97
125 2.85 4.75 5.70 2.85
2 121 3.92 6.86 0.98 5.88
122 1.04 5.22 1.04 7.31
123 2.76 8.28 5.52 3.68
124 4.95 0.99 4.95 5.94
125 7.60 6.65 6.65 1.90