嵌套字典列表转Pandas DataFrame重复行问题及解决问询

问题 #

我有一个嵌套字典结构，包含列表和字典，想要遍历后转换成Pandas DataFrame。示例字典对应5条数据，预期生成5行的DataFrame，但实际运行后得到10行重复数据。需求是将外层的game_id、bookie_key、market_key等值加入最内层的outcomes列表中。

附上出错的代码：

import pandas as pd

sample_dic = {'game_id': 'beeec03419b1aa196028a89f177f4324',
 'sportsbooks': [{'bookie_key': 'fanduel',
   'market': {'market_key': 'player_points_over_under',
    'outcomes': [{'timestamp': '2024-02-09T15:40:01',
      'handicap': 20.5,
      'odds': -122,
      'participant': 16142,
      'participant_name': 'Dejounte Murray',
      'name': 'Dejounte Murray Over',
      'description': 'Dejounte Murray - Points'},
     {'timestamp': '2024-02-09T15:57:49',
      'handicap': 21.5,
      'odds': -111,
      'participant': 16142,
      'participant_name': 'Dejounte Murray',
      'name': 'Dejounte Murray Over',
      'description': 'Dejounte Murray - Points'},
     {'timestamp': '2024-02-09T15:57:49',
      'handicap': 15.5,
      'odds': -125,
      'participant': 17163,
      'participant_name': 'Jalen Johnson',
      'name': 'Jalen Johnson Over',
      'description': 'Jalen Johnson - Points'}]}},
  {'bookie_key': 'draftkings',
   'market': {'market_key': 'player_points_over_under',
    'outcomes': [{'timestamp': '2024-02-09T13:09:03',
      'handicap': 18.5,
      'odds': -200,
      'participant': None,
      'participant_name': None,
      'name': 'Over - Dejounte Murray',
      'description': ' Alt Points O/U'},
     {'timestamp': '2024-02-09T15:21:11',
      'handicap': 15.5,
      'odds': -225,
      'participant': None,
      'participant_name': None,
      'name': 'Over - Kelly Oubre Jr.',
      'description': ' Alt Points O/U'}]}}}]

outcomes_list = []
v_game_id = sample_dic['game_id']
v_prop = sample_dic['sportsbooks'][0]['market']['market_key']
for i in range(0, len(sample_dic['sportsbooks'])):
  book = sample_dic['sportsbooks'][i]['bookie_key']
  for s in sample_dic['sportsbooks']:
    for k, v in s.items():
      if k == 'market':
        for k2, v2 in v.items():
          if k2 == 'outcomes':
            for o in v2:
              # outcomes_list.append(o) # This works for the inner 'outcomes' list only
              # Adding game_id, sportsbook, and prop k/v pairs back in
              new_dic = {'game_id': v_game_id, 'sportsbook': book, 'prop': v_prop}
              new_dic.update(o)
              outcomes_list.append(new_dic)
              
# Then to df
# This has doubled-up rows
outcomes_df = pd.DataFrame(outcomes_list)
outcomes_df

预期的正确DataFrame可通过手动构造列表生成：

wanted_outcomes_list = [['beeec03419b1aa196028a89f177f4324',
  'fanduel',
  'player_points_over_under',
  '2024-02-09T15:40:01',
  20.5,
  -122,
  16142,
  'Dejounte Murray',
  'Dejounte Murray Over',
  'Dejounte Murray - Points'],
 ['beeec03419b1aa196028a89f177f4324',
  'fanduel',
  'player_points_over_under',
  '2024-02-09T15:57:49',
  21.5,
  -111,
  16142,
  'Dejounte Murray',
  'Dejounte Murray Over',
  'Dejounte Murray - Points'],
 ['beeec03419b1aa196028a89f177f4324',
  'fanduel',
  'player_points_over_under',
  '2024-02-09T15:57:49',
  15.5,
  -125,
  17163,
  'Jalen Johnson',
  'Jalen Johnson Over',
  'Jalen Johnson - Points'],
 ['beeec03419b1aa196028a89f177f4324',
  'draftkings',
  'player_points_over_under',
  '2024-02-09T13:09:03',
  18.5,
  -200,
  None,
  None,
  'Over - Dejounte Murray',
  ' Alt Points O/U'],
 ['beeec03419b1aa196028a89f177f4324',
  'draftkings',
  'player_points_over_under',
  '2024-02-09T15:21:11',
  15.5,
  -225,
  None,
  None,
  'Over - Kelly Oubre Jr.',
  ' Alt Points O/U']]

wanted_outcomes_df = pd.DataFrame(wanted_outcomes_list, columns=['game_id', 'sportsbook', 'prop', 'timestamp', 'handicap', 'odds', 'participant', 'participant_name', 'name', 'description'])
wanted_outcomes_df

错误排查 #

你的循环逻辑存在重复遍历的核心问题：

1. 外层for i in range(0, len(sample_dic['sportsbooks']))已经在遍历每个体育博彩商（fanduel、draftkings）
2. 但你又嵌套了一个for s in sample_dic['sportsbooks']，导致每个博彩商的循环里，又重新遍历了所有博彩商的outcomes列表
3. 最终每个博彩商的3+2=5条数据，都被重复了2次（因为有2个博彩商），所以得到10行重复数据
   另外，v_prop直接取第一个博彩商的market_key，如果后续博彩商的market_key不同，会导致数据错误，应该从当前遍历的博彩商里取对应的market_key。

优化方案 #

修正后的循环代码 #

直接遍历每个博彩商，取出当前博彩商的bookie_key、market_key，再遍历其outcomes，把外层字段加入每个结果字典：

import pandas as pd

sample_dic = {'game_id': 'beeec03419b1aa196028a89f177f4324',
 'sportsbooks': [{'bookie_key': 'fanduel',
   'market': {'market_key': 'player_points_over_under',
    'outcomes': [{'timestamp': '2024-02-09T15:40:01',
      'handicap': 20.5,
      'odds': -122,
      'participant': 16142,
      'participant_name': 'Dejounte Murray',
      'name': 'Dejounte Murray Over',
      'description': 'Dejounte Murray - Points'},
     {'timestamp': '2024-02-09T15:57:49',
      'handicap': 21.5,
      'odds': -111,
      'participant': 16142,
      'participant_name': 'Dejounte Murray',
      'name': 'Dejounte Murray Over',
      'description': 'Dejounte Murray - Points'},
     {'timestamp': '2024-02-09T15:57:49',
      'handicap': 15.5,
      'odds': -125,
      'participant': 17163,
      'participant_name': 'Jalen Johnson',
      'name': 'Jalen Johnson Over',
      'description': 'Jalen Johnson - Points'}]}},
  {'bookie_key': 'draftkings',
   'market': {'market_key': 'player_points_over_under',
    'outcomes': [{'timestamp': '2024-02-09T13:09:03',
      'handicap': 18.5,
      'odds': -200,
      'participant': None,
      'participant_name': None,
      'name': 'Over - Dejounte Murray',
      'description': ' Alt Points O/U'},
     {'timestamp': '2024-02-09T15:21:11',
      'handicap': 15.5,
      'odds': -225,
      'participant': None,
      'participant_name': None,
      'name': 'Over - Kelly Oubre Jr.',
      'description': ' Alt Points O/U'}]}}}]

outcomes_list = []
game_id = sample_dic['game_id']

# 直接遍历每个sportsbook条目
for bookie in sample_dic['sportsbooks']:
    bookie_key = bookie['bookie_key']
    market = bookie['market']
    market_key = market['market_key']
    # 遍历当前bookie的outcomes
    for outcome in market['outcomes']:
        new_dic = {
            'game_id': game_id,
            'sportsbook': bookie_key,
            'prop': market_key
        }
        new_dic.update(outcome)
        outcomes_list.append(new_dic)

outcomes_df = pd.DataFrame(outcomes_list)
print(outcomes_df)

更简洁的写法：使用列表推导式 #

用列表推导式简化代码，逻辑和上面一致：

game_id = sample_dic['game_id']
outcomes_list = [
    {
        'game_id': game_id,
        'sportsbook': bookie['bookie_key'],
        'prop': bookie['market']['market_key'],
        **outcome
    }
    for bookie in sample_dic['sportsbooks']
    for outcome in bookie['market']['outcomes']
]

outcomes_df = pd.DataFrame(outcomes_list)

进阶：使用Pandas的json_normalize #

如果数据结构固定，也可以直接用pd.json_normalize扁平化嵌套结构，避免手动循环：

df = pd.json_normalize(
    sample_dic['sportsbooks'],
    record_path=['market', 'outcomes'],
    meta=['bookie_key', ['market', 'market_key']],
    meta_prefix=''
)
# 重命名列并添加game_id
df = df.rename(columns={
    'bookie_key': 'sportsbook',
    'market.market_key': 'prop'
})
df['game_id'] = sample_dic['game_id']
# 调整列顺序和预期一致
df = df[['game_id', 'sportsbook', 'prop', 'timestamp', 'handicap', 'odds', 'participant', 'participant_name', 'name', 'description']]
print(df)

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