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?
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
