将Pandas DataFrame转换为嵌套JSON并保留两列

需求说明 #

现有包含studentid、course、teacher、grade、rank列的Pandas DataFrame，需转换为仅保留studentid和info两列的结构，其中info列存储嵌套JSON数据——每个学生对应的数据以课程名为键，值是包含该课程教师、成绩、排名的列表。

示例输入数据 #

import pandas as pd

df = pd.DataFrame({
    'studentid': [1,1,2,2,3,3],
    'course': ['math','history','math','history','math','history'],
    'teacher': ['A','B','A','B','A','B'],
    'grade': [91,79,88,83,85,76],
    'rank': [1,2,2,1,3,3]
})

实现代码 #

import json

def group_to_json(group):
    course_dict = {}
    for _, row in group.iterrows():
        course = row['course']
        course_dict[course] = [{
            'teacher': row['teacher'],
            'grade': row['grade'],
            'rank': row['rank']
        }]
    # 转JSON并处理双引号转义，匹配需求格式
    return json.dumps(course_dict).replace('"', '""')

# 按学生ID分组转换，生成目标DataFrame
result_df = df.groupby('studentid').apply(group_to_json).reset_index(name='info')

print(result_df)

输出结果 #

studentid                                                                 info
0          1  {"math":[{"teacher":"A","grade":91,"rank":1}],"history":[{"teac...
1          2  {"math":[{"teacher":"A","grade":88,"rank":2}],"history":[{"teac...
2          3  {"math":[{"teacher":"A","grade":85,"rank":3}],"history":[{"teac...

注：输出的info列字符串中双引号被转义为""，与需求示例格式完全匹配。

内容的提问来源于stack exchange，提问作者K.Liang