CODEWITHSUNDEEP
pythonpandasdataframe
Mohamed Taher Alrefaie
Mohamed Taher Alrefaie

Reputation: 16243

Merging multiple rows into a single row using multiple indexes

So I have this dataset

FirstName    LastName    CourseName    CourseGrade
Joseph       Hanso       CS101         A+
Joseph       Hanso       CS102         D
....

And want it converted to this:

FirstName1    LastName1    CourseName1    CourseGrade1     CourseName2    CourseGrade2
Joseph       Hanso         CS101          A+               CS102          D

I'm not sure how to use pd.pivot() to do this. Are there any easy one liner to do this?

Upvotes: 0

Views: 33

Answers (1)

mozway
mozway

Reputation: 260640

IIUC, you can use:

cols = ['FirstName', 'LastName']

out = (df
 # add new column with incremental count
 .assign(col=df.groupby(cols).cumcount().add(1).astype(str))
 # pivot using new col value as column level
 .pivot(index=cols, columns='col')
 # sort new column level
 .sort_index(level=1, axis=1, sort_remaining=False)
 # merge the 2 MultiIndex levels
 .pipe(lambda d: d.set_axis(d.columns.map(''.join), axis=1))
 # index to columns
 .reset_index()
)

output:

  FirstName LastName CourseName1 CourseGrade1 CourseName2 CourseGrade2
0    Joseph    Hanso       CS101           A+       CS102            D

Upvotes: 1

Related Questions