使用json_normalize函数解析含嵌套列表的JSON并生成DataFrame

待解析的JSON数据 #

{
  "Serialnumber": 1,
  "JSON": {
    "body": {
      "ver": "1",
      "dt": 169,
      "od": "OBJECT_ID",
      "bin": "Vbin",
      "myname": "VME",
      "msgss": [
        {
          "code": "TLHGH1",
          "Details": {
            "no": 1,
            "rep": 4
          },
          "pngds": [
            {
              "id": "ID1",
              "mpo": 16,
              "mkg": "up"
            }
          ]
        },
        {
          "code": "TLHGH2",
          "Details": {
            "no": 2,
            "rep": 5
          },
          "pngds": [
            {
              "id": "ID2",
              "mpo": 17,
              "mkg": "down"
            }
          ]
        }
      ]
    }
  }
}

解析步骤与代码 #

通过pandas的json_normalize函数分层次展开嵌套结构，最终得到扁平化的DataFrame：

import pandas as pd

# 原始JSON数据
data = {
  "Serialnumber": 1,
  "JSON": {
    "body": {
      "ver": "1",
      "dt": 169,
      "od": "OBJECT_ID",
      "bin": "Vbin",
      "myname": "VME",
      "msgss": [
        {
          "code": "TLHGH1",
          "Details": {"no": 1, "rep": 4},
          "pngds": [{"id": "ID1", "mpo": 16, "mkg": "up"}]
        },
        {
          "code": "TLHGH2",
          "Details": {"no": 2, "rep": 5},
          "pngds": [{"id": "ID2", "mpo": 17, "mkg": "down"}]
        }
      ]
    }
  }
}

# 提取核心嵌套节点body
body_data = data['JSON']['body']

# 第一步：展开msgss列表，同时解析Details子对象
df_msg = pd.json_normalize(
    body_data,
    record_path='msgss',
    meta=['ver', 'dt', 'od', 'bin', 'myname'],
    record_prefix='msgss_',
    meta_prefix='body_'
)

# 第二步：展开pngds列表
df_final = pd.json_normalize(
    df_msg.to_dict('records'),
    record_path='msgss_pngds',
    meta=[col for col in df_msg.columns if col != 'msgss_pngds'],
    record_prefix='pngds_'
)

# 补充Serialnumber字段
df_final['Serialnumber'] = data['Serialnumber']

# 调整列顺序（可选）
df_final = df_final[
    ['Serialnumber', 'body_ver', 'body_dt', 'body_od', 'body_bin', 'body_myname',
     'msgss_code', 'msgss_Details.no', 'msgss_Details.rep',
     'pngds_id', 'pngds_mpo', 'pngds_mkg']
]

print(df_final)

最终输出结果 #

Serialnumber body_ver  body_dt    body_od body_bin body_myname msgss_code  msgss_Details.no  msgss_Details.rep pngds_id  pngds_mpo pngds_mkg
0             1        1      169  OBJECT_ID     Vbin         VME     TLHGH1                 1                  4      ID1         16        up
1             1        1      169  OBJECT_ID     Vbin         VME     TLHGH2                 2                  5      ID2         17      down

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