使用json_normalize展平DataFrame嵌套字典时遇TypeError错误求助

问题背景 #

作为编程新手，在开发大学橄榄球比赛赔率数据表项目时，调用API得到的数据中bookmakers列是嵌套字典列表结构，尝试用pd.json_normalize展平时遇到TypeError: string indices must be integers错误，目标是将数据整理为包含比赛信息、博彩机构、更新时间、赔率类型和主队让分的表格。

错误原因 #

出现该错误的核心原因是odds_df['bookmakers']的内容并非预期的字典列表，而是字符串类型。这通常是因为使用pd.read_json(odds.text)解析时，原始JSON的嵌套结构被错误解析为字符串，导致json_normalize无法识别为可遍历的字典结构。

解决方案 #

改用response.json()直接解析API返回的JSON数据，保留正确的嵌套结构，再分三层逐步展平嵌套字段，最后筛选并重命名列到目标格式：

完整代码 #

import requests
import pandas as pd

# 替换为你的API密钥
API_KEY = "YOUR_API_KEY"
api_url = f"https://api.the-odds-api.com/v4/sports/americanfootball_ncaaf/odds/?apiKey={API_KEY}&regions=us&markets=spreads&oddsFormat=american"

# 调用API并直接解析为结构化数据
response = requests.get(api_url)
data = response.json()

# 第一层展平：博彩机构与比赛基本信息关联
df_step1 = pd.json_normalize(
    data,
    record_path="bookmakers",
    meta=["id", "home_team", "away_team"],
    meta_prefix="match_"
)

# 第二层展平：博彩机构的市场信息关联
df_step2 = pd.json_normalize(
    df_step1.to_dict("records"),
    record_path="markets",
    meta=["match_id", "match_home_team", "match_away_team", "key", "last_update"],
    meta_prefix="bookmaker_"
)

# 第三层展平：市场的结果信息关联
df_step3 = pd.json_normalize(
    df_step2.to_dict("records"),
    record_path="outcomes",
    meta=["match_id", "match_home_team", "match_away_team", "bookmaker_key", "bookmaker_last_update", "key"],
    meta_prefix="market_"
)

# 筛选主队数据并重命名列到目标格式
df_final = df_step3[df_step3["name"] == df_step3["match_home_team"]].rename(
    columns={
        "match_id": "id",
        "match_home_team": "home_team",
        "match_away_team": "away_team",
        "bookmaker_key": "sportsbook",
        "bookmaker_last_update": "last_update",
        "market_key": "odds_type",
        "point": "home_point"
    }
)[["id", "home_team", "away_team", "sportsbook", "last_update", "odds_type", "home_point"]].reset_index(drop=True)

print(df_final)

输出示例 #

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