Pandas: Unstack Rows Into New Columns

i have a df that looks like this: a date c 0 ABC 2020-06-01 0.1 1 ABC 2020-05-01 0.2 2 DEF 2020-07-01 0.3 3 DEF 2020-01-01 0.4 4 DEF 2020-02-01 0.5 5

Solution 1:

Use GroupBy.cumcount for helper counter for MultiIndex and reshape by DataFrame.unstack, then for correct order is used DataFrame.sort_index with map for flatten MultiIndex:

df = (df.set_index(['a',df.groupby('a').cumcount().add(1)])
        .unstack()
        .sort_index(axis=1, level=[1, 0], ascending=[True, False]))
df.columns = df.columns.map(lambda x: f'{x[0]}{x[1]}')
df = df.reset_index()
print (df)
     a       date1   c1       date2   c2       date3   c3       date4   c4
0  ABC  2020-06-01  0.12020-05-01  0.2         NaN  NaN         NaN  NaN
1  DEF  2020-07-01  0.32020-01-01  0.42020-02-01  0.52020-07-01  0.6

Or if sorting is not possible because different columns names one idea is use DataFrame.reindex:

df1 = df.set_index(['a',df.groupby('a').cumcount().add(1)])
mux = pd.MultiIndex.from_product([df1.index.levels[1], ['date','c']])
df = df1.unstack().swaplevel(1,0, axis=1).reindex(mux, axis=1)
df.columns = df.columns.map(lambda x: f'{x[1]}{x[0]}')
df = df.reset_index()
print (df)
     a       date1   c1       date2   c2       date3   c3       date4   c4
0  ABC  2020-06-01  0.12020-05-01  0.2         NaN  NaN         NaN  NaN
1  DEF  2020-07-01  0.32020-01-01  0.42020-02-01  0.52020-07-01  0.6