如何使用Pandas将CSV数据转换为按指定字段分组的嵌套字典列表

需要将包含约3000条记录、共5列的CSV格式数据转换为字典列表，最初选用Pandas处理但当前输出结果不符合预期。

CSV文件结构示例 #

readTimestamp   school_subject  graduate    full_name   term
1611658200000   mathematics 3   Edd Ston    2
1611658200000   physics 5   Edd Ston    2
1611658200000   foreign language    5   Edd Ston    2
1611658200000   geography   4   Edd Ston    2
1611658200000   history 3   Edd Ston    2
1611658200000   Informatics 4   Kate Slow   1
1611658200000   chemistry   5   Kate Slow   1
1611658200000   mathematics 5   Kate Slow   1
1611658200000   foreign language    5   Kate Slow   1

期望输出结构 #

[
  {
    "readTimestamp": 123123123,
    "full_name": "Edd Ston",
    "term": 2,
    "schools_subject": [
      {
        "mathematics": 3,
        "physics": 5,
        "foreign language": 5,
        "geography": 4,
        "history": 3
      }
    ]
  },
  {
    "readTimestamp": 345345345,
    "full_name": "Kate Slow",
    "term": 1,
    "schools_subject": [
      {
        "Informatics": 4,
        "chemistry": 5,
        "mathematics": 5,
        "foreign language": 5
      }
    ]
  }
]

当前代码及实际输出 #

df = df.groupby(['readTimestamp','full_name','term']).apply(lambda x: x[['school_subject', 'graduate']].to_dict(orient='records')).to_dict()    

# 实际输出
{(1611658200000, 'Edd Ston', 2): [{'school_subject': 'mathematics', 'graduate': 3}, {'school_subject': 'physics', 'graduate': 5}, {'school_subject': 'foreign language', 'graduate': 5}, {'school_subject': 'geography', 'graduate': 4}, {'school_subject': 'history', 'graduate': 3}], (1611658200000, 'Kate Slow', 1): [{'school_subject': 'Informatics', 'graduate': 4}, {'school_subject': 'chemistry', 'graduate': 5}, {'school_subject': 'mathematics', 'graduate': 5}, {'school_subject': 'foreign language', 'graduate': 5}]}

1. 分组内转换逻辑错误：你用了to_dict(orient='records')，会把分组内每一行的两个字段拆成独立的双键值对字典，没有实现「学科名作为key、分数作为value合并为单个字典」的需求。
2. 分组后输出结构错误：直接对groupby结果调用to_dict()会把三个分组字段拼成元组作为外层key，不会拆成独立属性放到最终的字典中，所以输出的是嵌套字典而非你需要的字典列表。

3000条数据量很小，以下两种写法都可以正常运行：

写法1：遍历分组（可读性更高） #

result = []
# 按指定字段分组遍历
for (timestamp, name, term), group in df.groupby(['readTimestamp', 'full_name', 'term']):
    # 拼接学科-分数字典
    subject_map = dict(zip(group['school_subject'], group['graduate']))
    # 组装目标结构加入结果
    result.append({
        "readTimestamp": timestamp,
        "full_name": name,
        "term": term,
        "schools_subject": [subject_map]
    })

写法2：链式Pandas写法 #

result = (
    df.groupby(['readTimestamp','full_name','term'])
    # 分组内生成要求的学科数组结构
    .apply(lambda x: [dict(zip(x['school_subject'], x['graduate']))])
    .reset_index(name='schools_subject')
    # 转成目标字典列表
    .to_dict('records')
)

运行后得到的result就是你需要的结构。

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