Dataframe reshape/reindex

Question

I have a data frame in python that looks like the following:

  A B C
0 a 1 7
1 a 5 8
3 b 2 1
4 c 5 9
5 d 6 1
6 c 7 1
7 b 1 1
8 d 1 1

I want it to look like this:

    C1 c2 
a B 1  5
a C 7  8
b B 2  1
b C 1  1
c B 5  7
c C 9  1
d B 6  1
d C 1  1

Answer 1

You can create a new Column that identifies the row number within each unique A and then transform the data frame to wide format with row number spread along columns direction with the unstack() function:

# create a row id column for each unique value in A
df['X'] = 'X' + (df.groupby('A', group_keys=False).cumcount() + 1).astype(str)    

# transform the data frame to wide format with the row id spread along columns
df.set_index(['X', 'A']).stack().unstack(level=0)