如何在Pandas中将指定JSON列解析后转换为CSV格式？

问题场景 #

原始数据集（df.head(2)输出）：

Employee_Name    EID        Details 
Lisa            FG546HJ    {"Summary": "Worked as a HR professional.", "Services_List": ["HR", "Marketing", "Hiring"],"On_payroll?": false}
Martin          H5644HH    {"Summary": "Worked as a UI Designer.", "Services_List": ["Frontend", "UI", "UX"],"On_payroll?": True}

需要将Details列的JSON数据展开，转换为如下格式：

Employee_Name     EID      Summary                        Services_List         On_Payroll?
Lisa             FG546HJ   Worked as a HR professional.   HR,Marketing,Hiring    false
Martin           H5644HH   Worked as a UI Designer.       Frontend,UI,UX         True

实现方法 #

方法一：使用pd.json_normalize（高效推荐） #

这是pandas官方推荐的处理JSON列的方法，适配大多数数据规模：

import pandas as pd
import json

# 1. 解析JSON字符串为字典（若Details列本身已是字典类型，可省略此步）
df['Details'] = df['Details'].apply(json.loads)

# 2. 展开JSON格式的Details列
details_expanded = pd.json_normalize(df['Details'])

# 3. 合并原始列与展开后的列，移除原Details列
result = pd.concat([df.drop('Details', axis=1), details_expanded], axis=1)

# 4. 将列表类型的Services_List转为逗号分隔的字符串
result['Services_List'] = result['Services_List'].apply(lambda x: ','.join(x))

# 可选：调整字段名大小写，匹配目标格式的On_Payroll?
result.rename(columns={'On_payroll?': 'On_Payroll?'}, inplace=True)

print(result.head())

方法二：逐行apply处理（小数据量适用） #

如果数据规模较小，也可以通过自定义函数逐行解析字段：

import pandas as pd
import json

def parse_row(row):
    detail_dict = json.loads(row['Details'])
    # 提取JSON中的字段
    row['Summary'] = detail_dict['Summary']
    row['Services_List'] = ','.join(detail_dict['Services_List'])
    row['On_Payroll?'] = detail_dict['On_payroll?']
    # 删除原Details列
    return row.drop('Details')

# 应用函数并重置索引
result = df.apply(parse_row, axis=1).reset_index(drop=True)

print(result.head())

注意事项 #

• 若Details列存储的是Python字典而非字符串，直接跳过json.loads的解析步骤
• 注意JSON字段名与目标列名的大小写差异，可通过rename方法统一调整

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