Simple arithmetic on two cross-sections returns al

Question

My original question was how to add columns in all the sublevels of a multindex column. This solution worked fine for something like a rolling mean, or a difference versus a shifted value.

But it refuses to work when applied to math on same-dimension cross-sections of different columns. It returns all nan.

My hypothesis is that pandas is unhappy because the columns are named differently, so it cannot match them to subtract? So I should do some on-the-fly renaming (seems gross) or does this mean I'm missing something more fundamental about this operation.

index = pd.DatetimeIndex(start='2018-1-1',periods=5,freq="M")

persons = ['mike', 'dave', 'matt']
measures = ['spin', 'drag', 'bezel']
cols = pd.MultiIndex.from_product([persons, measures],names=['human', 'measure'])

xf = pd.DataFrame(index=index, data=np.random.rand(5,9), columns=cols)

idx = pd.IndexSlice
#this shows that both cross sections have data
print(xf.xs('spin', axis=1, level=1, drop_level=False))
print(xf.xs('drag', axis=1, level=1, drop_level=False))

#this works fine.  
zf = xf.xs('spin', axis=1, level=1, drop_level=False) - xf.xs('spin', axis=1, level=1, drop_level=False).shift(1)

#but this returns all NaN
qf = xf.xs('spin', axis=1, level=1, drop_level=False)+xf.xs('drag', axis=1, level=1, drop_level=False)
zf

Answer 1

Problem is data alignment - need same values of MultiIndex, else get NaNs:

print(xf.xs('spin', axis=1, level=1, drop_level=False))
human           mike      dave      matt
measure         spin      spin      spin
2018-01-31  0.248756  0.808523  0.885702
2018-02-28  0.150169  0.575710  0.468804
2018-03-31  0.723341  0.118158  0.360068
2018-04-30  0.857103  0.213594  0.533785
2018-05-31  0.288276  0.729455  0.153546

print(xf.xs('drag', axis=1, level=1, drop_level=False).rename(columns={'drag':'spin'}))
human           mike      dave      matt
measure         spin      spin      spin
2018-01-31  0.163067  0.625628  0.759117
2018-02-28  0.435679  0.146091  0.569999
2018-03-31  0.680671  0.242734  0.146042
2018-04-30  0.200212  0.973156  0.434459
2018-05-31  0.627167  0.556988  0.896226

qf = (xf.xs('spin', axis=1, level=1, drop_level=False)+
          xf.xs('drag', axis=1, level=1, drop_level=False).rename(columns={'drag':'spin'}))
print (qf)

human           mike      dave      matt
measure         spin      spin      spin
2018-01-31  0.411823  1.434152  1.644819
2018-02-28  0.585849  0.721801  1.038803
2018-03-31  1.404011  0.360893  0.506110
2018-04-30  1.057316  1.186749  0.968244
2018-05-31  0.915443  1.286444  1.049771

So if remove drop_level=False then columns are same, but is necessary create MultiIndex:

np.random.seed(456)

index = pd.date_range(start='2018-1-1',periods=5,freq="M")

persons = ['mike', 'dave', 'matt']
measures = ['spin', 'drag', 'bezel']
cols = pd.MultiIndex.from_product([persons, measures],names=['human', 'measure'])

xf = pd.DataFrame(index=index, data=np.random.rand(5,9), columns=cols)

---

idx = pd.IndexSlice
#this shows that both cross sections have data
print(xf.xs('spin', axis=1, level=1))
human           mike      dave      matt
2018-01-31  0.248756  0.808523  0.885702
2018-02-28  0.150169  0.575710  0.468804
2018-03-31  0.723341  0.118158  0.360068
2018-04-30  0.857103  0.213594  0.533785
2018-05-31  0.288276  0.729455  0.153546

print(xf.xs('drag', axis=1, level=1))
human           mike      dave      matt
2018-01-31  0.163067  0.625628  0.759117
2018-02-28  0.435679  0.146091  0.569999
2018-03-31  0.680671  0.242734  0.146042
2018-04-30  0.200212  0.973156  0.434459
2018-05-31  0.627167  0.556988  0.896226

qf = xf.xs('spin', axis=1, level=1)+ xf.xs('drag', axis=1, level=1)
qf.columns = [qf.columns, ['new'] * len(qf.columns)]
print (qf)
human           mike      dave      matt
                 new       new       new
2018-01-31  0.411823  1.434152  1.644819
2018-02-28  0.585849  0.721801  1.038803
2018-03-31  1.404011  0.360893  0.506110
2018-04-30  1.057316  1.186749  0.968244
2018-05-31  0.915443  1.286444  1.049771