Python Pandas嵌套JSON转DataFrame遇KeyError问题求助

错误原因 #

你的data是单个字典对象，而参考示例中的data是字典列表。直接传入单个字典时，pd.json_normalize对meta参数中嵌套字段的解析逻辑会出现偏差，导致无法定位到顶层的consumer.phoneNumber字段。

修复方案 #

将单个字典包装成列表[data]传入pd.json_normalize，这样就能正确识别meta参数中的嵌套字段路径。

修改后的完整代码 #

import pandas as pd

data ={
  "consumer": {
    "phoneNumber": "3156578877",
    "channelId": "83",
    "appId": "APP_DAVIPLATA",
    "moduleId": "MA_PSE_VNZ",
    "sessionId": "3fa85f64-5717-4562-b3fc-2c963f66afa6",
    "appVersion": "16.1.1",
    "soVersion": "Android 11",
    "agentInfo": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.77 Safari/537.36",
    "ipDevice": "163.111.221.230"
  },
  "transactionHeader": {
    "transactionType": "LOG",
    "transactionId": "3fa85f64-5717-4562-b3fc-2c963f66afa6",
    "transactionDate": "2018-07-03T17:54:36.762-05:00"
  },
  "transactionDetail": {
    "logType": "ANALITICA_OPERACIONAL",
    "MediaTarjetId": "PSE_VNZ_1",
    "Consumer": {
      "phoneNumber": "3156578877",
      "channelId": "83",
      "appId": "APP_DAVIPLATA",
      "moduleId": "MA_PSE_VNZ",
      "sessionId": "3fa85f64-5717-4562-b3fc-2c963f66afa6",
      "appVersion": "16.1.1",
      "soVersion": "Android 11",
      "agentInfo": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.77 Safari/537.36",
      "ipDevice": "163.111.221.230"
    },
    "Transaction": {
      "transactionType": "LOG",
      "transactionId": "3fa85f64-5717-4562-b3fc-2c963f66afa6",
      "transactionDate": "2018-07-03T17:54:36.762-05:00"
    },
    "Client": {
      "identificationType": "CC",
      "identificationNumber": "1027868487",
      "documentExpeditionDate": "2009-05-10",
      "documentExpeditionPlace": "Bogota"
    },
    "Product": {
      "productCode": "DVP_CO",
      "productNumber": "3158765639"
    },
    "Messages": {
      "OperationalAnalytics": [
        {
          "nameField": "fecha_transaccion",
          "valueField": "2022071518:35:50",
          "valueFormat": "YYYYMMDDHH:MM:SS"
        },
        {
          "nameField": "nombre_transaccion",
          "valueField": "DEBITO PAGO",
          "valueFormat": "String"
        },
        {
          "nameField": "valor",
          "valueField": "5300",
          "valueFormat": "Number"
        },
        {
          "nameField": "referencia_destino",
          "valueField": "3156547865",
          "valueFormat": "String"
        }
      ]
    }
  }
}

# 关键修改：将data包装成列表[data]
df = pd.json_normalize(
    [data],
    record_path=['transactionDetail','Messages','OperationalAnalytics'],
    meta=[['consumer','phoneNumber'], 'transactionHeader']
)

print(df)

额外优化（可选） #

如果希望把transactionHeader中的字段直接展开为DataFrame的列，而不是嵌套字典，可以将meta参数改为：

meta=[
    ['consumer','phoneNumber'],
    ['transactionHeader','transactionType'],
    ['transactionHeader','transactionId'],
    ['transactionHeader','transactionDate']
]

这样生成的DataFrame会包含扁平化的字段，结构更清晰。

内容的提问来源于stack exchange，提问作者Richard Alfonso Santana Benavi