CODEWITHSUNDEEP
pythonpandas
Moritz
Moritz

Reputation: 5408

pandas: calculate over multiindex

I have a dataframe like:

enter image description here

I would like to substract the values like:

enter image description here

minus

enter image description here

What I tried so far (the dataframe: http://pastebin.com/PydRHxcz):

index = pd.MultiIndex.from_tuples([key for key in dfdict], names = ['a','b','c','d'])
dfl = pd.DataFrame([dfdict[key] for key in dfdict],index=index)
dfl.columns = ['data']
dfl.sort(inplace=True)
d = dfl.unstack(['a','b'])

I can do:

d[0:5] - d[0:5]

And I get zeros for all values.

But If I do:

d[0:5] - d[5:]

I get Nans for all values. Any Ideas how I can perform such an operation ?

EDIT:

What works is

dfl.unstack(['a','b'])['data'][5:] - dfl.unstack(['a','b'])['data'][0:5].values

But it feels a bit clumsy

Upvotes: 4

Views: 594

Answers (1)

joris
joris

Reputation: 139222

You can use loc to select all rows that correspond to one label in the first level like this:

In [8]: d.loc[0]
Out[8]:
          data                                                        ...
a         0.17                                       1.00
b          0          5         10         500        0          5
d
0.0  11.098909   9.223784  8.003650  10.014445  13.231898  10.372040
0.3  14.349606  11.420565  9.053073  10.252542  26.342501  25.219403
0.5   1.336937   2.522929  3.875139  11.161803   3.168935   6.287555
0.7   0.379158   1.061104  2.053024  12.358577   0.678352   2.133887
1.0   0.210244   0.631631  1.457333  15.117805   0.292904   1.053916

So doing the subtraction looks like:

In [11]: d.loc[0] - d.loc[1000]
Out[11]:
           data                                                         ...
a          0.17                                        1.00
b           0          5          10        500         0          5
d
0.0   -3.870946  -3.239915  -3.504068 -0.722377   -2.335147  -2.460035
0.3  -65.611418 -42.225811 -25.712668 -1.028758  -65.106473 -44.067692
0.5  -84.494748 -55.186368 -34.184425 -1.619957  -89.356417 -69.008567
0.7  -92.681688 -61.636548 -37.386604 -4.227343 -110.501219 -78.925078
1.0 -101.071683 -61.758741 -37.080222 -3.081782 -103.779698 -80.337487

Upvotes: 4

Related Questions