CODEWITHSUNDEEP
pythonpandas
Eric Hansen
Eric Hansen

Reputation: 1809

Converting row in timeseries data to column

Suppose that I have a timeseries like 

In [41]: df = pd.DataFrame(dict(names=list('abcaabcabbcc'), vals=np.random.randint(0, 10, 12)), index
    ...: =pd.date_range('2017-03-01', periods=12))

In [42]: df
Out[42]: 
           names  vals
2017-03-01     a     2
2017-03-02     b     9
2017-03-03     c     6
2017-03-04     a     6
2017-03-05     a     5
2017-03-06     b     2
2017-03-07     c     3
2017-03-08     a     1
2017-03-09     b     1
2017-03-10     b     1
2017-03-11     c     1
2017-03-12     c     0

How can I convert the names row into the column headers, under which the corresponding vals are? e.g.

               a     b     c  
2017-03-01     2   nan   nan
2017-03-02   nan     9   nan
...

I have been playing around with pd.melt as well as trying to add the names to the index and then reset the index so that they become columns somehow, but I am very stuck.

Upvotes: 2

Views: 31

Answers (2)

MaxU - stand with Ukraine
MaxU - stand with Ukraine

Reputation: 210832

you can also use unstack():

In [12]: df.set_index('names', append=True).unstack('names')
Out[12]:
           vals
names         a    b    c
2017-03-01  1.0  NaN  NaN
2017-03-02  NaN  4.0  NaN
2017-03-03  NaN  NaN  5.0
2017-03-04  8.0  NaN  NaN
2017-03-05  8.0  NaN  NaN
2017-03-06  NaN  5.0  NaN
2017-03-07  NaN  NaN  7.0
2017-03-08  5.0  NaN  NaN
2017-03-09  NaN  7.0  NaN
2017-03-10  NaN  4.0  NaN
2017-03-11  NaN  NaN  3.0
2017-03-12  NaN  NaN  4.0

Upvotes: 1

jezrael
jezrael

Reputation: 862511

You can use:

print (pd.pivot(index=df.index, columns=df['names'], values=df['vals']))

Or:

print (df.set_index('names', append=True)['vals'].unstack())
names         a    b    c
2017-03-01  4.0  NaN  NaN
2017-03-02  NaN  0.0  NaN
2017-03-03  NaN  NaN  3.0
2017-03-04  8.0  NaN  NaN
2017-03-05  0.0  NaN  NaN
2017-03-06  NaN  0.0  NaN
2017-03-07  NaN  NaN  9.0
2017-03-08  6.0  NaN  NaN
2017-03-09  NaN  6.0  NaN
2017-03-10  NaN  0.0  NaN
2017-03-11  NaN  NaN  3.0
2017-03-12  NaN  NaN  9.0

print (df.reset_index().pivot_table(index='index', columns='names', values='vals'))

Upvotes: 2

Related Questions