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

Question

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

score 2 · Accepted Answer · answered Nov 23 '21 at 02:27

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

score 1 · Answer 2 · answered Nov 23 '21 at 03:14

The cumcount function allows one to create unique indices before pivoting. This builds on the same idea as @ScottBoston; however, the pivot function is used here:

index = ['name', 'school']

                  # create an extra column for uniqueness          
temp = (df.assign(counter = df.groupby(index)
                              .cumcount()
                              .add(1)
                              .astype(str))
          .pivot(index = index, columns = 'counter')
        )

# flatten the columns
temp.columns = temp.columns.map('_'.join)

temp.reset_index()

    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

Alternatively, you could use the pivot_wider function from pyjanitor, which is syntactic sugar around pd.pivot, with some helpers:

# pip install pyjanitor
import pandas as pd
import janitor
(df.assign(counter = df.groupby(index)
                       .cumcount()
                       .add(1))                              
   .pivot_wider(index = index, 
                names_from = 'counter', 
                names_sep = '_')
)

    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

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

2 Answers2

Linked

Related