Python Dataframe: Transpose One Column Into Multiple Column

I have a dataframe like below: df = pd.DataFrame({'month':['2017-09-27','2017-09-27','2017-09-28','2017-09-29'],'Cost':[100,500,200,300]}) How can I get a df like this: 2017-09-2

Solution 1:

Use cumcount to compute a "cumulative count" of the items within each group. We'll use these values (below) as index labels.

In [97]: df['index'] = df.groupby('month').cumcount()

In [98]: df
Out[98]: 
   Cost       month  index
01002017-09-27015002017-09-27122002017-09-28033002017-09-290

Then the desired result can be obtained by pivoting:

In [99]: df.pivot(index='index', columns='month', values='Cost')
Out[99]: 
month  2017-09-272017-09-282017-09-29index0100.0200.0300.01500.0         NaN         NaN

Solution 2:

Option 1zip_longest

from itertools importzip_longests= df.groupby('month').Cost.apply(list)
pd.DataFrame(list(zip_longest(*s)), columns=s.index)

month  2017-09-272017-09-282017-09-290100200.0300.01500         NaN         NaN

---

Option 2pd.concat

pd.concat(
    {k:g.reset_index(drop=True)fork, gindf.groupby('month').Cost},axis=1)2017-09-27  2017-09-28  2017-09-290100200.0300.01500NaNNaN

---

Option 3 Similar to @unutbu in that it uses cumcount. However, I use set_index and unstack to do the pivoting.

df.set_index([df.groupby('month').cumcount(), 'month']).Cost.unstack()

month  2017-09-272017-09-282017-09-290100.0200.0300.01500.0         NaN         NaN