Python : Pandas pivot table for multiple columns at once which has duplicate values-CodePudding

have a pandas dataframme with columns name , school and marks

name  school  marks

tom     HBS     55
tom     HBS     55
tom     HBS     14
mark    HBS     28
mark    HBS     19
lewis   HBS     88

How to transpose and convert into like this

name  school  marks_1 marks_2 marks_3

tom     HBS     55     55       14
mark    HBS     28     19
lewis   HBS     88

tried this:

df = df.pivot_table(index='name', values='marks', columns='school') \
    .reset_index() \
    .rename_axis(None, axis=1)

print(df)

df = df.pivot('name','marks','school')

checked these links

https://stackoverflow.com/questions/22798934/pandas-long-to-wide-reshape-by-two-variables
https://stackoverflow.com/questions/62391419/pandas-group-by-and-convert-rows-into-multiple-columns
https://stackoverflow.com/questions/60698109/pandas-multiple-rows-to-single-row-with-multiple-columns-on-2-indexes

getting this error due to duplicate values. how to handle if duplicate exists and we have to keep them

ValueError: Index contains duplicate entries, cannot reshape

CodePudding user response：

Try using set_index and unstack with groupby and cumcount:

df_out = df.set_index(['name',
                       'school',
                       df.groupby(['name','school'])\
           .cumcount()  1]).unstack()
df_out.columns = [f'{i}_{j}' for i, j in df_out.columns]
df_out = df_out.reset_index()
df_out

Output:

    name school  marks_1  marks_2  marks_3
0  lewis    HBS     88.0      NaN      NaN
1   mark    HBS     28.0     19.0      NaN
2    tom    HBS     55.0     55.0     14.0