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

So problem is add Sales and create MultiIndex in columns? — jezrael
– jezrael, Commented Apr 10, 2017 at 14:08
The problem is I have multiindex columns which was returned to me by the code I just edit and not I want to get the data in the above mentioned form — muazfaiz
– muazfaiz, Commented Apr 10, 2017 at 14:30

jezrael · Accepted Answer · 2017-04-10 14:37:18Z

1

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

edited Apr 10, 2017 at 14:37

answered Apr 10, 2017 at 14:31

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jezrael Over a year ago

Maybe a few words about 1 - it means get second (python counts from 0) level (dates) and create columns from it. Thank you.

MaxU - stand with Ukraine · Accepted Answer · 2017-04-10 14:43:55Z

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

edited Apr 10, 2017 at 14:43

answered Apr 10, 2017 at 14:08

MaxU - stand with Ukraine

212k37 gold badges402 silver badges436 bronze badges

1 Comment

muazfaiz Over a year ago

Please see I have made the edit to make it more understandable. I have already indexed the columns but now I want to make one index as it is and the other index 'Date' to be pivoted in the column

Collectives™ on Stack Overflow

handling multi-indexing in Pandas Python

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related