如何提取嵌套JSON的text字段内容并扁平化转为Pandas DataFrame

你可以通过自定义提取函数，筛选text字段中所有type=bold的元素，按顺序提取对应值生成新列，完整可运行代码如下：

import pandas as pd

data = '''
          [{
    "id": 735221,
    "type": "message",
    "date": "2021-01-01T00:03:58",
    "from": "Lost Relics Overseer",
    "from_id": "channel1462123539",
    "text": [
        "💎 ",
        {
            "type": "bold",
            "text": "wakaiacerse1"
        },
        " located ",
        {
            "type": "bold",
            "text": "Emenire Bamboo (R)"
        },
        " at ",
        {
            "type": "bold",
            "text": "Ash Woods"
        },
        "!"
    ]
}, {
    "id": 735222,
    "type": "message",
    "date": "2021-01-01T00:05:40",
    "from": "Lost Relics Overseer",
    "from_id": "channel1462123539",
    "text": [
        "💎 ",
        {
            "type": "bold",
            "text": "oH3CKZ"
        },
        " stumbled across ",
        {
            "type": "bold",
            "text": "Messantic Crab (R)"
        },
        " at ",
        {
            "type": "bold",
            "text": "Trayl Woods"
        },
        "!"
    ]
}, {
    "id": 735223,
    "type": "message",
    "date": "2021-01-01T00:09:58",
    "from": "Lost Relics Overseer",
    "from_id": "channel1462123539",
    "text": [
        "💎 ",
        {
            "type": "bold",
            "text": "Crypton"
        },
        " stumbled across ",
        {
            "type": "bold",
            "text": "Turquoise Teardrop (R)"
        },
        " at ",
        {
            "type": "bold",
            "text": "Odenspire Sands"
        },
        "!"
    ]
}, {
    "id": 735239,
    "type": "message",
    "date": "2021-01-01T00:37:27",
    "from": "Lost Relics Overseer",
    "from_id": "channel1462123539",
    "text": [
        "💎 ",
        {
            "type": "bold",
            "text": "Devotion"
        },
        " located ",
        {
            "type": "bold",
            "text": "Emenire Bamboo (R)"
        },
        " at ",
        {
            "type": "bold",
            "text": "Ash Woods"
        },
        "!"
    ]
}]
'''
# 替换HTML转义的双引号
data = data.replace('"', '"')
# 读取JSON为DataFrame
df = pd.read_json(data)

# 定义信息提取函数
def extract_fields(text_list):
    # 收集所有bold类型的文本内容
    bold_values = []
    for item in text_list:
        if isinstance(item, dict) and item.get('type') == 'bold':
            bold_values.append(item.get('text', ''))
    # 按顺序返回用户名、物品名、位置，不足则返回空字符串
    return pd.Series([
        bold_values[0] if len(bold_values) >= 1 else '',
        bold_values[1] if len(bold_values) >= 2 else '',
        bold_values[2] if len(bold_values) >= 3 else ''
    ])

# 生成新列
df[['用户名', '物品名', '位置']] = df['text'].apply(extract_fields)

# 可选：删除原始text列
# df = df.drop('text', axis=1)

# 打印结果
print(df)

实现逻辑说明 #

• 首先处理JSON字符串中的HTML转义字符"，替换为标准双引号，保证JSON可以被正常解析
• 自定义提取函数遍历每一行的text列表，筛选所有type为bold的字典元素，按顺序收集对应的文本值
• 用apply方法将提取函数作用于text列，自动生成三个独立列追加到原DataFrame中
• 函数做了容错处理，即使某条数据的bold元素不足3个也不会报错，会自动填充空字符串

内容的提问来源于stack exchange，提问作者Roger Bücker