handling multi-indexing in Pandas Python

Question

Consider a the data in following way where ProductId and Date are indexes. I used the following code

>>> df = df.set_index('Date').groupby('productID').resample('W').sum()

                   Sales
productID   Date    
1000    01/01/2017  10
        01/08/2017  15
        01/15/2017  64
        01/22/2017  21
        01/29/2017  21
1001    01/01/2017  15
        01/08/2017  54
        01/15/2017  51
        01/22/2017  19
        01/29/2017  56

I want to pivot the results in the following way

                                 Sales              
productID   01/01/2017  01/08/2017  01/15/2017  01/22/2017  01/29/2017
1000           10          15         64           21         21
1001           15          54         51           19         56

I am trying to do this by using the pivot function in Pandas

df = df.pivot(index='ProductID', columns='Date', values='Sales')

but it returns the following error

ValueError: cannot insert ProductID, already exists

Please guide me how can I transform the results in the given way. Thanks

Answer 1

Try this:

In [98]: df.pivot_table(index=df.index, columns='Date', values='Sales', aggfunc='sum')
Out[98]:
Date       01/01/2017  01/08/2017  01/15/2017  01/22/2017  01/29/2017
productID
1000               10          15          64          21          21
1001               15          54          51          19          56

Answer 2

I think you need add ['Sales'] for convert to Series with unstack:

df = df.set_index('Date').groupby('productID').resample('W')['Sales'].sum().unstack(1)
#same as
#df = df.set_index('Date').groupby('productID').resample('W').sum()['Sales'].unstack(1)


print (df)
Date       01/01/2017  01/08/2017  01/15/2017  01/22/2017  01/29/2017
productID                                                            
1000               10          15          64          21          21
1001               15          54          51          19          56