使用多个索引将多行合并为单行

Question

我有这个数据集

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

并将其转换为:

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

我不确定如何使用pd.pivot()来做到这一点。有没有简单的一行代码?

Answer 1

iuc，可以使用:

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()
)

输出:

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