如何将嵌套字典解析为Pandas DataFrame并解决字段拆分问题

需要将结构复杂的嵌套字典解析为Pandas DataFrame，以下是简化的字典示例：

import datetime
from decimal import *

test_dict = [{'record_id': '43bbdfbf',
  'date': datetime.date(2023, 3, 25),
  'person': {
      'id': '123abc',
      'name': 'Person1'
  },
  'venue': {
      'id': '5bd6c74c',
      'name': 'Place1',
      'city': {
          'id': '3448439',
          'name': 'São Paulo',
          'state': 'São Paulo',
          'state_code': 'SP',
          'coords': {'lat': Decimal('-23.5475'), 'long': Decimal('-46.63611111')},
          'country': {'code': 'BR', 'name': 'Brazil'}
      },
   },
  'thing_lists': {'thing_list': [
      {'song': [
          {'name': 'Thing1','info': None,'dup': None},
          {'name': 'Thing2', 'info': None, 'dup': None},
          {'name': 'Thing3', 'info': None, 'dup': None},
          {'name': 'Thing4', 'info': None, 'dup': None}],
         'extra': None},
     {'song': [
          {'name': 'ExtraThing1','info': None,'dup': None},
          {'name': 'ExtraThing2', 'info': None, 'dup': None}],
         'extra': 1
     }]}}]

编写的提取函数及转DataFrame代码如下：

def extract_values(dictionary):
    record_id = dictionary[0]['record_id'],
    date = dictionary[0]['date'],
    country = dictionary[0]['venue']['city']['country']['name']
    
    return record_id, date, venue, city, lat, long, country

import pandas as pd
df = pd.DataFrame(extract_values(test_dict)).transpose()
df.rename(
    columns={
        df.columns[0]: 'record_id',
        df.columns[1]: 'date',
        df.columns[3]: 'city',
        df.columns[6]: 'country'
    }, 
    inplace=True
)

遇到的问题：字符串字段被拆分为单个字符，每行仅显示一个字符，且无法正确提取深层嵌套字段。期望生成的DataFrame结构如下：

RecordID Date       City      Country ThingName    Dup   Extra
43bbdfbf 2023-03-25 São Paulo Brazil  Thing1       None  None
43bbdfbf 2023-03-25 São Paulo Brazil  Thing2       None  None
43bbdfbf 2023-03-25 São Paulo Brazil  Thing3       None  None 
43bbdfbf 2023-03-25 São Paulo Brazil  Thing4       None  None
43bbdfbf 2023-03-25 São Paulo Brazil  ExtraThing1  None  1
43bbdfbf 2023-03-25 São Paulo Brazil  ExtraThing2  None  1

错误原因分析 #

1. 字符串拆分问题：函数中record_id = dictionary[0]['record_id'],末尾的逗号会将变量转为单元素元组，Pandas处理元组时会将字符串拆分为单个字符。
2. 嵌套列表未处理：原代码未遍历thing_list和song嵌套列表，无法生成多行的目标结构；同时函数中未定义venue、city等变量就返回，会直接报错。

正确实现代码 #

import pandas as pd
import datetime
from decimal import *

# 原测试字典
test_dict = [{'record_id': '43bbdfbf',
  'date': datetime.date(2023, 3, 25),
  'person': {
      'id': '123abc',
      'name': 'Person1'
  },
  'venue': {
      'id': '5bd6c74c',
      'name': 'Place1',
      'city': {
          'id': '3448439',
          'name': 'São Paulo',
          'state': 'São Paulo',
          'state_code': 'SP',
          'coords': {'lat': Decimal('-23.5475'), 'long': Decimal('-46.63611111')},
          'country': {'code': 'BR', 'name': 'Brazil'}
      },
   },
  'thing_lists': {'thing_list': [
      {'song': [
          {'name': 'Thing1','info': None,'dup': None},
          {'name': 'Thing2', 'info': None, 'dup': None},
          {'name': 'Thing3', 'info': None, 'dup': None},
          {'name': 'Thing4', 'info': None, 'dup': None}],
         'extra': None},
     {'song': [
          {'name': 'ExtraThing1','info': None,'dup': None},
          {'name': 'ExtraThing2', 'info': None, 'dup': None}],
         'extra': 1
     }]}}]

def extract_values(dictionary):
    # 提取所有公共字段（所有行共享的信息）
    base_info = {
        'RecordID': dictionary[0]['record_id'],
        'Date': dictionary[0]['date'],
        'City': dictionary[0]['venue']['city']['name'],
        'Country': dictionary[0]['venue']['city']['country']['name']
    }
    result_records = []
    
    # 遍历外层的thing_list分组
    for item_group in dictionary[0]['thing_lists']['thing_list']:
        current_extra = item_group['extra']
        # 遍历分组内的每首song，生成单独记录
        for song in item_group['song']:
            record = base_info.copy()
            record['ThingName'] = song['name']
            record['Dup'] = song['dup']
            record['Extra'] = current_extra
            result_records.append(record)
    
    return result_records

# 生成DataFrame并调整列顺序
final_df = pd.DataFrame(extract_values(test_dict))
final_df = final_df[['RecordID', 'Date', 'City', 'Country', 'ThingName', 'Dup', 'Extra']]

print(final_df)

代码说明 #

1. 先提取所有公共字段（RecordID、Date、City、Country），这些字段在所有结果行中重复出现。
2. 遍历嵌套的thing_list分组，获取每个分组的extra值。
3. 对每个分组下的song列表逐个遍历，为每首歌复制一份公共字段，再添加当前歌的ThingName、Dup以及分组的Extra值，形成一条完整记录。
4. 将所有记录收集为列表后转为DataFrame，最后调整列顺序匹配目标结构。

运行代码后即可得到期望的DataFrame结果。

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