如何将含languages数组列的Dataframe转换为新的结构化Dataframe？

问题场景 #

加载feather文件得到的DataFrame中，languages列每行是包含语言详情的字典数组，需生成仅保留student_id，并将languages数组展开为结构化列（language_id、language_name、optin_at）的新DataFrame。

输入DataFrame示例 #

目标输出DataFrame示例 #

测试用JSON数据（原DataFrame以orient='records'导出） #

[{"student_id":"1","name":"Foo","created_at":"2019-01-03T14:30:32.146Z","languages":[{"language_id":1,"name":"English","optin_at":"2019-01-03T14:30:32.148Z"}]},{"student_id":"2","name":"Bar","created_at":"2019-01-03T14:30:32.146Z","languages":[{"language_id":1,"name":"English","optin_at":"2021-05-30T00:33:02.915Z"},{"language_id":2,"name":"Portuguese","optin_at":"2022-03-07T07:42:07.082Z"}]}]

解决方案 #

使用Pandas的explode方法展开数组列，再通过json_normalize解析字典结构，最后合并并调整列名：

代码实现 #

import pandas as pd

# 加载feather文件（测试时可替换为下方的测试数据）
# df = pd.read_feather('your_file.feather')

# 测试用数据初始化
test_data = [
    {"student_id":"1","name":"Foo","created_at":"2019-01-03T14:30:32.146Z","languages":[{"language_id":1,"name":"English","optin_at":"2019-01-03T14:30:32.148Z"}]},
    {"student_id":"2","name":"Bar","created_at":"2019-01-03T14:30:32.146Z","languages":[{"language_id":1,"name":"English","optin_at":"2021-05-30T00:33:02.915Z"},{"language_id":2,"name":"Portuguese","optin_at":"2022-03-07T07:42:07.082Z"}]}
]
df = pd.DataFrame(test_data)

# 1. 展开languages数组，仅保留student_id和languages列
exploded_df = df[['student_id', 'languages']].explode('languages', ignore_index=True)

# 2. 解析字典列并与student_id合并
languages_detail = pd.json_normalize(exploded_df['languages'])
result_df = pd.concat([exploded_df['student_id'], languages_detail], axis=1)

# 3. 调整列名匹配目标输出
result_df.rename(columns={'name': 'language_name'}, inplace=True)

# 查看最终结果
print(result_df)

运行结果 #

student_id  language_id language_name                  optin_at
0          1            1       English  2019-01-03T14:30:32.148Z
1          2            1       English  2021-05-30T00:33:02.915Z
2          2            2    Portuguese  2022-03-07T07:42:07.082Z

关键步骤说明 #

• explode('languages', ignore_index=True)：将每行的字典数组拆分为独立行，同时保留对应的student_id
• pd.json_normalize()：将嵌套的字典结构展开为扁平化的列
• 列名重命名：把原字典中的name列改为目标输出的language_name，保证结构匹配

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