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
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