如何将含重复Student ID的Pandas DataFrame转换为指定格式？

你的需求本质是将长格式数据转换为宽格式，可以通过Pandas的pivot()函数快速实现，无需手动构造列表。以下是完整实现步骤：

完整代码 #

import pandas as pd 
import numpy as np

# 原始数据构造（你提供的代码）
student_id = [1, 2, 2, 4, 5, 5]
student_names = ["Bob", "Alex", "Alex", "Alice", "Sharon", "Sharon"]
student_status = ["Inactive", "Full Time", "Full Time", "Inactive", "Inactive", "Inactive"]
course_description = [np.nan, "Physics", "History", np.nan, "Physics", "History"]
course_paid = [np.nan, "Yes", "No", np.nan, "No", "Yes"]
enrollement = [np.nan, "Enrolled", "Not Enrolled", np.nan, "Not Enrolled", "Enrolled"]

df = pd.DataFrame(data = student_id, columns=["Student ID"])
df["Student Name"] = student_names
df["Student Status"] = student_status
df["Course Description"] = course_description
df["Course paid"] = course_paid
df["Enrollment"] = enrollement

# 核心转换步骤
# 1. 透视数据：以学生唯一标识为索引，课程为列，提取报名状态和付费状态
pivoted = df.pivot(
    index=['Student ID', 'Student Name', 'Student Status'],
    columns='Course Description',
    values=['Enrollment', 'Course paid']
)

# 2. 整理列名，匹配你需要的格式
pivoted.columns = [
    f'{col[1]}' if col[0] == 'Enrollment' else f'{col[1]} Paid' 
    for col in pivoted.columns
]

# 3. 重置索引，将索引列转为普通列
df2 = pivoted.reset_index()

# 查看结果
print(df2)

代码解释 #

• 透视数据：pivot()函数指定index为学生的唯一标识列（保证每个学生只占一行），columns为课程名称（将不同课程转为列），values为需要展开的两个字段（报名状态、付费状态）。
• 整理列名：透视后列名是多层结构，通过列表推导式将其合并为Physics、Physics Paid这类符合需求的名称。
• 重置索引：将原本作为索引的学生信息列转回普通数据列，得到最终的宽格式DataFrame。

输出结果 #

Student ID Student Name Student Status      Physics Physics Paid      History History Paid
0           1          Bob      Inactive          NaN          NaN          NaN          NaN
1           2         Alex     Full Time     Enrolled          Yes  Not Enrolled           No
2           4        Alice      Inactive          NaN          NaN          NaN          NaN
3           5        Sharon      Inactive  Not Enrolled           No     Enrolled          Yes

特殊情况处理 #

如果存在同一学生同一课程有多行数据的情况，pivot()会报错，此时可以改用pivot_table()并指定聚合函数，例如取第一个有效值：

pivoted = df.pivot_table(
    index=['Student ID', 'Student Name', 'Student Status'],
    columns='Course Description',
    values=['Enrollment', 'Course paid'],
    aggfunc='first'
)

内容的提问来源于stack exchange，提问作者AspiringDSer