germanfox germanfox - 29 days ago 18
Python Question

Transposing one column in python dataframe

I have the following:

Index ID speed _avg_val
245 1 10 30.5
246 1 2 25.1


I want to transpose the column ID and then have the following:

ID (Index) speed _avg_val speed_y _avg_val_y
1 10 30.5 2 25.1


I tried to use this method Transposing one column in python pandas with the simplest index possible but could not get this to work with multiple columns.

Answer

I think you can first remove column Index, then add column ID to index, unstack and sort second level of MultiIndex in columns by sort_index:

print (df)
   Index  ID  speed  _avg_val
0    245   1     10      30.5
1    246   1      2      25.1


df = df.drop('Index', axis=1)
       .set_index('ID', append=True)
       .unstack(0)
       .sort_index(axis=1, level=1)

#remove MultiIndex from columns
df.columns = ['_'.join((col[0], str(col[1]))) for col in df.columns]

print (df)
    speed_0  _avg_val_0  speed_1  _avg_val_1
ID                                          
1        10        30.5        2        25.1