如何高效展平DataFrame列中多层嵌套字典列表数据

我需要处理DataFrame列中存储的多层嵌套字典列表数据，样本数据如下：

[
    {
        "id": "0001",
        "sport_key": "americanfootball_nfl",
        "sport_title": "NFL",
        "commence_time": "2022-10-28T00:15:00Z",
        "home_team": "Tampa Bay Buccaneers",
        "away_team": "Baltimore Ravens",
        "bookmakers": [
            {
                "key": "betonlineag",
                "title": "BetOnline.ag",
                "last_update": "2022-10-26T00:34:17Z",
                "markets": [
                    {
                        "key": "h2h",
                        "outcomes": [
                            {
                                "name": "Baltimore Ravens",
                                "price": 1.8
                            },
                            {
                                "name": "Tampa Bay Buccaneers",
                                "price": 2.04
                            }
                        ]
                    }
                ]
            },
            {
                "key": "fanduel",
                "title": "FanDuel",
                "last_update": "2022-10-26T00:34:30Z",
                "markets": [
                    {
                        "key": "h2h",
                        "outcomes": [
                            {
                                "name": "Baltimore Ravens",
                                "price": 1.85
                            },
                            {
                                "name": "Tampa Bay Buccaneers",
                                "price": 2.0
                            }
                        ]
                    }
                ]
            }
        ]
    },
    {
        "id": "0002",
        "sport_key": "americanfootball_nfl",
        "sport_title": "NFL",
        "commence_time": "2022-10-30T13:30:00Z",
        "home_team": "Jacksonville Jaguars",
        "away_team": "Denver Broncos",
        "bookmakers": [
            {
                "key": "betonlineag",
                "title": "BetOnline.ag",
                "last_update": "2022-10-26T00:34:17Z",
                "markets": [
                    {
                        "key": "h2h",
                        "outcomes": [
                            {
                                "name": "Denver Broncos",
                                "price": 2.2
                            },
                            {
                                "name": "Jacksonville Jaguars",
                                "price": 1.71
                            }
                        ]
                    }
                ]
            },
            {
                "key": "betrivers",
                "title": "BetRivers",
                "last_update": "2022-10-26T00:34:31Z",
                "markets": [
                    {
                        "key": "h2h",
                        "outcomes": [
                            {
                                "name": "Denver Broncos",
                                "price": 2.26
                            },
                            {
                                "name": "Jacksonville Jaguars",
                                "price": 1.7
                            }
                        ]
                    }
                ]
            }
        ]
    }
]

期望转换后的结构化表格形式如下：

我现在的问题是不知道怎么高效解包这些嵌套字典列表，把需要的数据放到同一行里。

可以用Pandas结合分步展开或自定义遍历的方式处理，以下是两种实用方案：

方案1：用Pandas json_normalize分步展开 #

适合熟悉Pandas内置函数的场景，步骤清晰：

import pandas as pd

# 1. 加载原始数据到DataFrame
data = [
    # 这里放入你的样本数据
]
df = pd.DataFrame(data)

# 2. 第一层展开：拆分bookmakers列表，保留上层核心字段
df_bookmakers = pd.json_normalize(
    data,
    record_path='bookmakers',
    meta=['id', 'sport_title', 'home_team', 'away_team']
)

# 3. 第二层展开：拆分markets列表，保留已有的字段
df_markets = pd.json_normalize(
    df_bookmakers.to_dict('records'),
    record_path='markets',
    meta=['id', 'sport_title', 'home_team', 'away_team', 'key']
).rename(columns={'key': 'bookmaker_name'})

# 4. 提取主客队赔率：从outcomes列表匹配对应队伍的价格
def get_odds(row):
    home = row['home_team']
    away = row['away_team']
    outcomes = row['outcomes']
    home_odds = next(o['price'] for o in outcomes if o['name'] == home)
    away_odds = next(o['price'] for o in outcomes if o['name'] == away)
    return pd.Series([home_odds, away_odds], index=['home_team_odds', 'away_team_odds'])

df_final = df_markets.join(df_markets.apply(get_odds, axis=1))

# 5. 整理列顺序和名称
df_final = df_final[['id', 'sport_title', 'home_team', 'away_team', 'bookmaker_name', 'key', 'home_team_odds', 'away_team_odds']].rename(columns={'key': 'market_type'})

方案2：自定义遍历生成行数据 #

更直观易懂，适合快速理解嵌套结构的场景：

import pandas as pd

data = [
    # 这里放入你的样本数据
]

def parse_match(match):
    rows = []
    # 遍历每个博彩商
    for bookmaker in match['bookmakers']:
        # 遍历每个市场类型
        for market in bookmaker['markets']:
            # 匹配主客队赔率
            home_odds = next(o['price'] for o in market['outcomes'] if o['name'] == match['home_team'])
            away_odds = next(o['price'] for o in market['outcomes'] if o['name'] == match['away_team'])
            # 组装目标行数据
            rows.append({
                'id': match['id'],
                'sport_title': match['sport_title'],
                'home_team': match['home_team'],
                'away_team': match['away_team'],
                'bookmaker_name': bookmaker['key'],
                'market_type': market['key'],
                'home_team_odds': home_odds,
                'away_team_odds': away_odds
            })
    return rows

# 生成所有行数据并转换为DataFrame
all_rows = []
for match in data:
    all_rows.extend(parse_match(match))
df_final = pd.DataFrame(all_rows)

运行任意一种方案，都能得到你需要的结构化表格。

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