python pandas convert unique rows as new columns with their respective data

Question

I have some data of the below form:

Col1    Col2        Col3    Col4
a       2021-12-11  759     46
a       2021-13-11  803     30
b       2021-12-11  46      10
b       2021-13-11  86      7

and I want to transform it to the below form:

Col2        a-Col3    a-Col4  b-col3 b-col4
2021-12-11  759       46      46     10
2021-13-11  803       30      86     7

I have tried pivot table and similar "grouping" functions but I was unable to find any solution.

Answer 1

You can use:

df2 = df.set_index(['Col2', 'Col1']).unstack().swaplevel(axis=1)
df2.columns = df2.columns.map('-'.join)

output:

>>> df2.reset_index()
         Col2  a-Col3  b-Col3  a-Col4  b-Col4
0  2021-12-11     759      46      46      10
1  2021-13-11     803      86      30       7