Raj Jain Raj Jain - 28 days ago 6
Python Question

Pivot dataframe to automatically make columns

I have a data frame like this

Date Lag
d1 20
d1 30
d1 40
d2 10
d2 50


and want to convert to something like this

Date <column names>
d1 20 30 40
d2 10 50 None


the columns can be named anything. any way to do this?

Answer

I think you need create new column names by cumcount to new column and then use pivot:

print (df)
  Date  Lag
0   d1   20
1   d1   30
2   d1   40
3   d2   10
4   d2   50

df['g'] = df.groupby('Date')['Lag'].cumcount()
df = df.pivot(index='Date', columns='g', values='Lag')
print (df)
g        0     1     2
Date                  
d1    20.0  30.0  40.0
d2    10.0  50.0   NaN

Also you can change column names:

df['g'] = 'Col' + df.groupby('Date')['Lag'].cumcount().astype(str)
print (df)
  Date  Lag     g
0   d1   20  Col0
1   d1   30  Col1
2   d1   40  Col2
3   d2   10  Col0
4   d2   50  Col1

df = df.pivot(index='Date', columns='g', values='Lag')
print (df)
g     Col0  Col1  Col2
Date                  
d1    20.0  30.0  40.0
d2    10.0  50.0   NaN
Comments