Pivot Dataframe With No Unique Column

My DataFrame looks like this, df = index | A | B | C 0 |00456|text1|date1 1 |00443|text1|date2 2 |00456|text1|date3 3 |00231|text2|date4 4 |00231|text3|dat

Solution 1:

Use GroupBy.cumcount for counter with reshape by DataFrame.set_index and DataFrame.unstack, last flatten MultiIndex in columns and convert index to column:

g = df.groupby('A').cumcount() + 1
df1 = df.set_index(['A', g]).unstack()
df1.columns = [f'{a}_{b}' for a, b in df1.columns]
df1 = df1.reset_index()
print (df1)
     A    B_1    B_2    B_3    C_1    C_2    C_3
0  231  text2  text3    NaN  date4  date1    NaN
1  443  text1    NaN    NaN  date2    NaN    NaN
2  456  text1  text1  text2  date1  date3  date1