Skip to content

2018-11-18 Datascience

Pandas How To Make Column Values Into Column Index

Pandas: make column values into columns#

I have the following data:

        dt          cls         cd
0       1999-10-25  845.0   DSY
1       1999-10-26  830.0   DSY
2       1999-10-27  830.0   DSY
...
4759    2015-02-05  25633.0     MRP
4760    2015-02-06  25670.0     MRP
4761    2015-02-09  25892.0     MRP
...
9261    1991-03-06  2.59    USDZAR
9262    1991-03-07  2.59    USDZAR
9263    1991-03-08  2.60    USDZAR

I would like to reshape the data on the dt index:

        dt          DSY         MRP   USDZAR
0       1999-10-25  845.0   NaN   Nan
1       1999-10-26  830.0   NaN   Nan
2       1999-10-27  830.0   Nan   Nan
...
4759    2015-02-05  25633.0     MRP
4760    2015-02-06  25670.0     MRP
4761    2015-02-09  25892.0     MRP
...
9261    1991-03-06  2.59    USDZAR
9262    1991-03-07  2.59    USDZAR
9263    1991-03-08  2.60    USDZAR

There are various ways of doing this.

Before pivoting, identify:

  • The index. In this case, the date column dt The value. In this case, cls The column labels. In this case, the cd values Using pivot_table:
df = df.pivot_table(values='cls', index=df.dt, columns='cd', aggfunc='first')

Using pivot:

df = df.pivot(index=df.dt, columns='cd')['cls']

There must be no duplicate index and column combinations for pivot to work.

Source#