Transpose by grouping a Dataframe having both numeric and string variables

Question

I have a DataFrame and I want to convert it into the following:

import pandas as pd
df = pd.DataFrame({'ID':[111,111,111,222,222,333],
                   'class':['merc','humvee','bmw','vw','bmw','merc'],
                   'imp':[1,2,3,1,2,1]})
print(df)
    ID   class  imp
0  111    merc    1
1  111  humvee    2
2  111     bmw    3
3  222      vw    1
4  222     bmw    2
5  333    merc    1

Desired output:

    ID       0        1       2
0  111    merc   humvee     bmw
1  111       1        2       3
2  222      vw      bmw
3  222       1        2
4  333    merc      
5  333       1

I wish to transpose the entire dataframe, but grouped by a particular column, ID in this case and maintaining the row order.

My attempt: I tried using .set_index() und .unstack(), but it did not work.

Answer 1

Use GroupBy.cumcount for counter and then reshape by DataFrame.stack and Series.unstack:

df1 = (df.set_index(['ID',df.groupby('ID').cumcount()])
         .stack()
         .unstack(1, fill_value='')
         .reset_index(level=1, drop=True)
         .reset_index())
print (df1)
    ID     0       1    2
0  111  merc  humvee  bmw
1  111     1       2    3
2  222    vw     bmw     
3  222     1       2     
4  333  merc             
5  333     1