如何使用pandas展平嵌套JSON为适配RDBMS导入的指定列DataFrame

核心逻辑 #

• 复用你已经导出的仅含id、events两列的DataFrame，无需回读原始全量JSON
• 通过explode操作将每个id对应的事件列表拆分为独立行，每行对应一个事件，同时保留关联的顶层id
• 借助json_normalize自动展平事件内的嵌套字典（origin、destination），空嵌套字典/缺失嵌套键会自动生成空值，无需额外编写判断逻辑
• 最终调整列名、列顺序至要求的目标格式，可直接导入关系型数据库

可执行代码 #

1. （可选）复现样例输入 #

如果你还没生成对应结构的输入df，可直接运行以下代码加载你提供的样例数据：

import pandas as pd
import json

# 你的样例JSON
raw_json = '''[
   {
      "id":"abcd",
      "events":[
         {
            "description":"Liquidated",
            "time":"2020-12-18T12:00:00Z",
            "origin":{},
            "destination":{"name":" ","code":"SAV"},
            "remarks":"L 99",
            "invoice":"pqr",
            "code":"LIQ"
         },
         {
            "description":"Customs Monthly Stmt Paid",
            "time":"2020-02-26T12:00:00Z",
            "origin":{"name":"abc, GA","code":" "},
            "destination":{"name":"SAVANNAH, GA","code":"SAV"},
            "remarks":"PM Stmt No 1720P0207Q",
            "invoice":"pqr",
            "code":"MSP"
         }
      ]
  },
  {
      "id":"xyz",
      "events":[
         {
            "description":"Liquidated",
            "time":"2020-12-18T12:00:00Z",
            "origin":{"name":"abc, GA","code":" "},
            "destination":{},
            "remarks":"L 99",
            "invoice":" ",
            "code":"LIQ"
         },
         {
            "description":"Customs Monthly Stmt Paid",
            "time":"2020-02-26T12:00:00Z",
            "origin":{},
            "destination":{"name":"SAVANNAH, GA","code":"SAV"},
            "remarks":"PM Stmt No 1720P0207Q",
            "invoice":" ",
            "code":"MSP"
         }
      ]
}
]'''

# 生成仅含id、events的输入df，和你当前的输入状态完全一致
input_df = pd.DataFrame(json.loads(raw_json))

2. 核心展平处理 #

# 炸开events列，每个事件单独成行，保留对应id
exploded_df = input_df.explode('events', ignore_index=True)

# 展平事件内的嵌套字段
event_fields = pd.json_normalize(exploded_df['events'])
# 关联顶层id
event_fields['id'] = exploded_df['id']

# 调整列顺序、重命名为目标列名
target_column_map = {
    'origin.name': 'origin_name',
    'origin.code': 'origin_code',
    'destination.name': 'destination_name',
    'destination.code': 'destination_code'
}
target_columns = ['id', 'description', 'time', 'origin.name', 'origin.code', 'remarks', 'destination.name', 'destination.code', 'invoice', 'code']

final_df = event_fields[target_columns].rename(columns=target_column_map)

可选优化 #

• 如需将所有空值统一替换为空字符串（适配样例中空格的存储风格），可执行：final_df = final_df.fillna('')
• 若部分id下无有效事件，可在explode后添加一行过滤空事件：exploded_df = exploded_df.dropna(subset=['events'])

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