Python Data Transformation using Pandas

Question

My dataset looks like this

    Name
83   A
84   A
85   A
97   C
98   C
99   C
131  D
132  D
133  D

and going on with random index.

While the output I want is;

Instance Name RowID1 RowID2 RowID3
1        A    83     84     85
2        C    97     98     99
3        D    131    132    133

**Please note- Names and indexes are random, with no relation between them. How can I do it using Python (Pandas).

Answer 1

First step is convert df.index to df['index'] column by DataFrame.reset_index, then create helper column g by GroupBy.cumcount in DataFrame.assign, pivoting by DataFrame.pivot, rename columns by DataFrame.add_prefix.

Last use DataFrame.insert for column Instance:

df = (df.reset_index()
        .assign(g = lambda x: x.groupby('Name').cumcount().add(1))
        .pivot('Name', 'g', 'index')
        .add_prefix('RowID')
        .reset_index()
        .rename_axis(None, axis=1))

df.insert(0, 'Instance', range(1, len(df) + 1))
print (df)
   Instance Name  RowID1  RowID2  RowID3
0         1    A      83      84      85
1         2    C      97      98      99
2         3    D     131     132     133