Convert the following table:
| Type | s | h |
|---|---|---|
| A | 1 | 4 |
| A | 2 | 5 |
| B | 4 | 7 |
| B | 5 | 2 |
into this:
| Type | s_1 | h_1 | s_2 | h_2 |
|---|---|---|---|---|
| A | 1 | 4 | 2 | 5 |
| B | 4 | 7 | 5 | 2 |
>Solution :
In your case doing the cumcount with groupby get the key then we can pivot
out = df.assign(key = df.groupby('Type').cumcount()+1).pivot(index='Type',columns='key').sort_index(level=1,axis=1)
out.columns = out.columns.map('{0[0]}_{0[1]}'.format)
out = out.reset_index()
out
Out[581]:
Type h_1 s_1 h_2 s_2
0 A 4 1 5 2
1 B 7 4 2 5