CODEWITHSUNDEEP
pythonpandasmulti-index
Tahnoon Pasha
Tahnoon Pasha

Reputation: 6018

Use Column and Row Multi-Index values in Pandas Groupby without unstacking

I have a multi-index hierarchy set up as follows:

import numpy as np

sectors = ['A','B','C','D']
ports = ['pf','bm']
dates = range(1,11)*2
wts, pchg = zip(*np.random.randn(20,2))
df = pd.DataFrame(dict(dates=dates,port=sorted(ports*10),
                       sector=np.random.choice(sectors,20), wts=wts,
                       pchg=pchg))

df = df.set_index(['port','sector','dates'])
df = df.unstack('port')
df = df.fillna(0)

I'd like group by dates and port , and sum pchg * wts

I've been through the docs but I'm struggling to figure this out.

Any help greatly appreciated. Thanks

Upvotes: 1

Views: 417

Answers (1)

joris
joris

Reputation: 139242

You indeed do not need to unstack to get what you want, using the product method to do the multiplication you want. Step by step:

Starting from this dataframe:

In [50]: df.head()
Out[50]:
                  pchg                 wts
port                bm        pf        bm        pf
sector dates
A      1      0.138996  0.451688  0.763287 -1.863401
       3      1.081863  0.000000  0.956807  0.000000
       4      0.207065  0.000000 -0.663175  0.000000
       5      0.258293 -0.868822  0.109336 -0.784900
       6     -1.016700  0.900241 -0.054077 -1.253191

We can first do the pchg * wts part with the product method, multiplying over axis 1, but only for the second level:

In [51]: df.product(axis=1, level=1).head()
Out[51]:
port                bm        pf
sector dates
A      1      0.106094 -0.841675
       3      1.035134  0.000000
       4     -0.137320  0.000000
       5      0.028241  0.681938
       6      0.054980 -1.128174

And then we can just group by dates (and no grouping by port needed anymore) and take the sum:

In [52]: df.product(axis=1, level=1).groupby(level='dates').sum()
Out[52]:
port         bm        pf
dates
1      0.106094 -0.841675
2      0.024968  1.357746
3      1.035134  1.776464
4     -0.137320  0.392312
5      0.028241  0.681938
6      0.054980 -1.128174
7      0.140183 -0.338828
8      1.296028 -1.526065
9     -0.213989  0.469104
10     0.058369 -0.006564

This gives the same output as 

df.stack('port').groupby(level=[1,2]).apply(lambda x: (x['wts']*x["pchg"]).sum()).unstack('port')

Upvotes: 1

Related Questions