如何将DataFrame中JSON格式列的取值转为逗号分隔字符串？

我有一个包含rawrecord JSON格式列的DataFrame，示例数据如下：

filedate    code    errorID rawrecord                                                                                                                       errortype
20230811    8003    100     {"Action":"NEW","ID":"30811-195552-952","EventType":"MMMM","Date":"20230811T000000.000000","orderID":"111111111111",flag:False} BBBB
20230811    8003    101     {"Action":"NEW","ID":"20811-195555-952","EventType":"MMMM","Date":"20230811T000000.000000","orderID":"111111111112",flag:False} BBBB
20230811    8003    102     {"Action":"NEW","ID":"50811-195552-952","EventType":"MMMM","Date":"20230811T000000.000000","orderID":"411111111111",flag:False} BBBB
20230811    8003    103     {"Action":"NEW","ID":"60811-195552-952","EventType":"MMMM","Date":"20230811T000000.000000","orderID":"511111111111",flag:False} BBBB
20230811    8003    104     {"Action":"NEW","ID":"40811-195552-952","EventType":"MMMM","Date":"20230811T000000.000000","orderID":"611111111111",flag:False} BBBB
20230811    8003    105     {"Action":"NEW","ID":"80811-195552-952","EventType":"MMMM","Date":"20230811T000000.000000","orderID":"811111111111",flag:False} BBBB
20230811    8003    106     {"Action":"NEW","ID":"70811-195552-952","EventType":"MMMM","Date":"20230811T000000.000000","orderID":"911111111111",flag:False} AAAA

希望提取rawrecord列中所有JSON的取值，转为逗号分隔的字符串，期望输出如下：

filedate    code    errorID rawrecord                                                                                                                       errortype
20230811    8003    100     NEW,30811-195552-952,MMMM,20230811T000000.000000,111111111111,False                                                             BBBB
20230811    8003    101     NEW,20811-195555-952,MMMM,20230811T000000.000000,111111111112,False                                                             BBBB
20230811    8003    102     NEW,50811-195552-952,MMMM,20230811T000000.000000,411111111111,False                                                             BBBB
20230811    8003    103     NEW,60811-195552-952,MMMM,20230811T000000.000000,511111111111,False                                                             BBBB
20230811    8003    104     NEW,40811-195552-952,MMMM,20230811T000000.000000,611111111111,False                                                             BBBB
20230811    8003    105     NEW,80811-195552-952,MMMM,20230811T000000.000000,811111111111,False                                                             BBBB
20230811    8003    106     NEW,70811-195552-952,MMMM,20230811T000000.000000,911111111111,False                                                             AAAA

可以通过以下步骤实现需求：

1. 导入pandas和JSON解析相关库
2. 解析rawrecord列的JSON字符串为字典对象
3. 提取字典中的所有值，拼接成逗号分隔的字符串
4. 用处理后的字符串替换原rawrecord列

代码实现 #

import pandas as pd
import json

# 构建示例DataFrame（实际场景可从文件/数据库读取）
data = {
    "filedate": ["20230811"]*7,
    "code": ["8003"]*7,
    "errorID": [100,101,102,103,104,105,106],
    "rawrecord": [
        '{"Action":"NEW","ID":"30811-195552-952","EventType":"MMMM","Date":"20230811T000000.000000","orderID":"111111111111","flag":false}',
        '{"Action":"NEW","ID":"20811-195555-952","EventType":"MMMM","Date":"20230811T000000.000000","orderID":"111111111112","flag":false}',
        '{"Action":"NEW","ID":"50811-195552-952","EventType":"MMMM","Date":"20230811T000000.000000","orderID":"411111111111","flag":false}',
        '{"Action":"NEW","ID":"60811-195552-952","EventType":"MMMM","Date":"20230811T000000.000000","orderID":"511111111111","flag":false}',
        '{"Action":"NEW","ID":"40811-195552-952","EventType":"MMMM","Date":"20230811T000000.000000","orderID":"611111111111","flag":false}',
        '{"Action":"NEW","ID":"80811-195552-952","EventType":"MMMM","Date":"20230811T000000.000000","orderID":"811111111111","flag":false}',
        '{"Action":"NEW","ID":"70811-195552-952","EventType":"MMMM","Date":"20230811T000000.000000","orderID":"911111111111","flag":false}'
    ],
    "errortype": ["BBBB"]*6 + ["AAAA"]
}
df = pd.DataFrame(data)

# 定义处理JSON的函数
def parse_and_join(json_str):
    try:
        # 解析JSON字符串为字典
        json_obj = json.loads(json_str)
        # 提取所有值并转为字符串，用逗号连接
        return ','.join(map(str, json_obj.values()))
    except json.JSONDecodeError:
        # 解析失败时返回原字符串，也可根据需求返回空值
        return json_str

# 应用函数处理rawrecord列
df['rawrecord'] = df['rawrecord'].apply(parse_and_join)

# 输出结果
print(df)

非标准JSON处理说明 #

原示例中的JSON存在非标准格式（flag:False不符合JSON规范，标准布尔值应为小写false），如果你的原始数据是这种格式，可改用demjson库解析非标准JSON：

import demjson

def parse_and_join(json_str):
    json_obj = demjson.decode(json_str)
    return ','.join(map(str, json_obj.values()))

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