I have a pandas dataframe with two multiindex levels
df
Out[202]:
A B C D
first second
1 1 -0.080810 0.865259 -0.371148 0.346480
2 -0.026636 1.259460 -1.109295 -0.871204
3 0.372008 -1.778272 0.727838 0.620727
4 0.918075 0.564741 2.027432 -1.614162
5 -0.373527 -0.186027 0.225399 0.722733
2 1 0.344241 0.170596 -0.050763 2.692102
2 -1.665413 0.357033 -0.691327 -0.983103
3 1.277470 -1.841702 0.582107 -0.454315
4 2.374108 -0.557879 0.797296 0.803622
5 -1.001092 0.131217 0.131378 -0.636299
I also have a second dataframe consisting of two rows out of the original dataframe, corresponding to the multiindex level 'second == 1'
to_subtract = df.query('second == 1')
to_subtract
Out[200]:
A B C D
first second
1 1 -0.080810 0.865259 -0.371148 0.346480
2 1 0.344241 0.170596 -0.050763 2.692102
I want to subtract 'df' by 'to_subtract' on the first level, but for all values that correspond to the first level of the multiindex. Pandas knows how to subtract based on matching ALL levels of the multiindex, as shown below
df.sub(to_subtract)
Out[201]:
A B C D
first second
1 1 0.0 0.0 0.0 0.0
2 NaN NaN NaN NaN
3 NaN NaN NaN NaN
4 NaN NaN NaN NaN
5 NaN NaN NaN NaN
2 1 0.0 0.0 0.0 0.0
2 NaN NaN NaN NaN
3 NaN NaN NaN NaN
4 NaN NaN NaN NaN
5 NaN NaN NaN NaN
Question is, how do I subtract all values from df[first===1] by to_subtract[first==1], and all values from df[first==2] by to_subtract[first==2]. I assume I can do a for loop and loop through everything, but I would rather avoid that if I can, since these dataframes may get large in the future.
Thanks in advance
I think need remove second level of MultiIndex and then add parameter level=0 for align by first levels in DataFrame.sub:
to_subtract = df.query('second == 1').reset_index(level=1, drop=True)
#same as
#to_subtract = df.xs(1, level=1)
print (to_subtract)
A B C D
first
1 -0.080810 0.865259 -0.371148 0.346480
2 0.344241 0.170596 -0.050763 2.692102
df1 = df.sub(to_subtract, level=0)
print (df1)
A B C D
first second
1 1 0.000000 0.000000 0.000000 0.000000
2 0.054174 0.394201 -0.738147 -1.217684
3 0.452818 -2.643531 1.098986 0.274247
4 0.998885 -0.300518 2.398580 -1.960642
5 -0.292717 -1.051286 0.596547 0.376253
2 1 0.000000 0.000000 0.000000 0.000000
2 -2.009654 0.186437 -0.640564 -3.675205
3 0.933229 -2.012298 0.632870 -3.146417
4 2.029867 -0.728475 0.848059 -1.888480
5 -1.345333 -0.039379 0.182141 -3.328401