使用pandas.json_normalize提取JSON数据时遇TypeError问题求助

错误原因 #

1. 路径层级错误：pd.json_normalize的record_path参数已经指定从['data', 'events']开始解析（遍历每个event对象），但你在meta里写的['data', 'events', 'name']是从根节点出发的完整路径，导致解析时试图用字符串索引列表，触发TypeError: list indices must be integers or slices, not str。
2. 嵌套数组未处理：markets.outcomes是数组结构，直接在meta中引用会得到嵌套列表，后续访问也会存在数据结构问题。

修正代码 #

场景1：展开到outcome级别（保留所有投注选项数据） #

import pandas as pd
import requests

api = 'https://content.toto.nl/content-service/api/v1/q/event-list?startTimeFrom=2024-04-05T22%3A00%3A00Z&started=false&maxMarkets=10&orderMarketsBy=displayOrder&marketSortsIncluded=--%2CCS%2CDC%2CDN%2CHH%2CHL%2CMH%2CMR%2CWH&marketGroupTypesIncluded=CUSTOM_GROUP%2CDOUBLE_CHANCE%2CDRAW_NO_BET%2CMATCH_RESULT%2CMATCH_WINNER%2CMONEYLINE%2CROLLING_SPREAD%2CROLLING_TOTAL%2CSTATIC_SPREAD%2CSTATIC_TOTAL&eventSortsIncluded=MTCH&includeChildMarkets=true&prioritisePrimaryMarkets=true&includeCommentary=true&includeMedia=true&drilldownTagIds=691&excludeDrilldownTagIds=7291%2C7294%2C7300%2C7303%2C7306'
response = requests.get(api)
data = response.json()

# 扁平化嵌套结构：遍历每个event下的market下的outcome
df = pd.json_normalize(
    data['data']['events'],
    record_path=['markets', 'outcomes'],
    meta=[
        'name',  # 直接引用当前event的name字段（相对路径）
        ['markets', 'name'],
        ['markets', 'type']
    ]
)

# 将event的name设为DataFrame索引
df.set_index('name', inplace=True)
print(df.head())

场景2：仅保留event级别数据 #

如果不需要投注选项的细节，直接提取event级别的数据即可：

import pandas as pd
import requests

api = 'https://content.toto.nl/content-service/api/v1/q/event-list?startTimeFrom=2024-04-05T22%3A00%3A00Z&started=false&maxMarkets=10&orderMarketsBy=displayOrder&marketSortsIncluded=--%2CCS%2CDC%2CDN%2CHH%2CHL%2CMH%2CMR%2CWH&marketGroupTypesIncluded=CUSTOM_GROUP%2CDOUBLE_CHANCE%2CDRAW_NO_BET%2CMATCH_RESULT%2CMATCH_WINNER%2CMONEYLINE%2CROLLING_SPREAD%2CROLLING_TOTAL%2CSTATIC_SPREAD%2CSTATIC_TOTAL&eventSortsIncluded=MTCH&includeChildMarkets=true&prioritisePrimaryMarkets=true&includeCommentary=true&includeMedia=true&drilldownTagIds=691&excludeDrilldownTagIds=7291%2C7294%2C7300%2C7303%2C7306'
response = requests.get(api)
data = response.json()

# 直接解析events数组为DataFrame
df_events = pd.json_normalize(data['data']['events'])
# 设置name为索引
df_events.set_index('name', inplace=True)
print(df_events.head())

关键说明 #

• pd.json_normalize的meta参数路径是相对于record_path指定的层级的，当record_path指向events数组时，每个解析上下文是单个event对象，直接写字段名（如'name'）即可，无需从根节点开始。
• 对于嵌套数组（如markets、outcomes），需要通过record_path逐层深入，才能得到扁平化的表格结构，避免嵌套列表导致的类型错误。

内容的提问来源于stack exchange，提问作者nijhof.ngsvn