pandas: how to run a pivot with a multi-index?

You can group and then unstack.

>>> df.groupby(['year', 'month', 'item'])['value'].sum().unstack('item')item        item 1  item 2year month                2004 1          33     250     2          44     224     3          41     268     4          29     232     5          57     252     6          61     255     7          28     254     8          15     229     9          29     258     10         49     207     11         36     254     12         23     209

Or use pivot_table:

>>> df.pivot_table(        values='value',         index=['year', 'month'],         columns='item',         aggfunc=np.sum)item        item 1  item 2year month                2004 1          33     250     2          44     224     3          41     268     4          29     232     5          57     252     6          61     255     7          28     254     8          15     229     9          29     258     10         49     207     11         36     254     12         23     209

I believe if you include item in your MultiIndex, then you can just unstack:

df.set_index(['year', 'month', 'item']).unstack(level=-1)

This yields:

                value      item       item 1 item 2year month              2004 1         21    277     2         43    244     3         12    262     4         80    201     5         22    287     6         52    284     7         90    249     8         14    229     9         52    205     10        76    207     11        88    259     12        90    200

It's a bit faster than using pivot_table, and about the same speed or slightly slower than using groupby.

The following worked for me:

mypiv = df.pivot(index=['year','month'],columns='item')[['values1','values2']]