Pivot/unstack a DataFrame with duplicate entries without aggregation

Question

So I got a DataFrame as follow:

        Col_0 Col_1
Index_1        
      A     0     2
      A     3     1
      A     2     2
      B     3     1
      B     4     3
      B     5     1

And I would like to reshape to it into following format:

    Col_0     Col_1
    A    B    A    B
    0    3    2    1
    3    4    1    3
    2    5    2    1

I've try pandas.pivot, pandas.unstack, pandas.groupby. None of it worked. Can anyone help me out with it? Much appreciated.

score 0 · Accepted Answer · answered May 26 '19 at 22:09

Create the index with cumcount then pivot:

df['idx'] = df.groupby(df.index).cumcount()
df.reset_index().pivot(index='idx', columns='Index_1').rename_axis(None)

Output:

        Col_0    Col_1   
Index_1     A  B     A  B
0           0  3     2  1
1           3  4     1  3
2           2  5     2  1

Pivot/unstack a DataFrame with duplicate entries without aggregation

1 Answers1