如何在Python Pandas中规范化含嵌套数组的JSON对象

问题背景 #

我有一段嵌套的JSON数据，想要将其扁平化为单行结构，但尝试了explode()、pd.json_normalize(data, max_level=3)、flatten_json等方法后都没达到预期效果。处理嵌套元素时遇到了瓶颈，不管用哪种方法，嵌套列始终以列表形式保留在单个列中，我找不到扁平化过程中遗漏的步骤。

我的代码 #

import requests
import pandas as pd
import json
from flatten_json import flatten

response = requests.get(
    ELASTICSEARCH_URL,
    data = QUERY,
    auth = (variables.get('username'), variables.get('password')),
    verify = False,
    headers = {'Content-Type': 'application/json'}
)
extracted_data = response.json()

required_records = extracted_data["hits"]["hits"][0]["_source"]["response"]["data"][CLAIMTYPE]

df = pd.json_normalize(required_records, max_level=2).fillna('')
#df = flatten(extracted_data)
#print(json.dumps(df, indent=4))

# df1 = df.explode('icdDiagnosisCodes')
# df2 = df1.explode('serviceProcedures')
#print("\nNumner of Records Extracted from MONGODB:\n", df.head(10).to_string())

# df2 = df.explode('icdDiagnosisCodes') # this is not working
df2 = pd.json_normalize(df['icdDiagnosisCodes'])
print("\nNumner of Records Extracted from MONGODB:\n", df2.head(10).to_string())

待处理的JSON数据 #

{
  "providerCity": "SOME CITY",
  "providerSpecialtyDescription": "PHYSICAL/OCCUPATIONAL THERAPY",
  "updateDate": "YYYY-MM-DD",
  "serviceDate": "YYYY-MM-DD",
  "providerLastName": "XXXXXXXXXXX",
  "gender": "F",
  "city": "SOME CITY",
  "healthPlanIdentifier": "POS CHOICE PLUS",
  "ndcCodeDescription": "NO NDC",
  "claimType": "Physician",
  "providerName": "XXXX,XXX",
  "ndcCode": "NONE",
  "zip": "00000",
  "providerZip": "00000",
  "providerStateCode": "XX",
  "providerNpi": "XXXXXXXXXXX",
  "icdDiagnosisCodes": [
    {
      "icdDiagnosisCode": "M25551",
      "icdDiagnosisDecimalCode": "M25.551",
      "icdDiagnosisCodeDescription": "PAIN IN RIGHT HIP"
    },
    {
      "icdDiagnosisCode": "M545",
      "icdDiagnosisDecimalCode": "M54.5",
      "icdDiagnosisCodeDescription": "LOW BACK PAIN"
    }
  ],
  "dateOfBirth": "YYYY-MM-DD",
  "claimId": "ASDFGHJKLTUYBNCNDSDWEWRWDEW",
  "memberIdentifier": "999999999",
  "providerSpecialtyCode": "99",
  "serviceProcedures": [
    {
      "typeOfServiceCode": "1",
      "procedureCode": "97110",
      "procedureCodeType": "CPT-4",
      "quantityOfServices": "1",
      "procedureCodeModifiers": [
        {
          "procedureCodeModifier": "GP",
          "procedureCodeModifierDescription": "SERVICES DELIVERED UNDER AN OUTPATIENT PHYSICAL THERAPY PLAN OF CARE"
        }
      ],
      "toDate": "YYYY-MM-DD",
      "placeOfService": "11",
      "typeOfServiceDescription": "Medical/Surgical",
      "fromDate": "YYYY-MM-DD",
      "serviceDiagnoses": [
        {
          "diagnosisCode": "M25551",
          "diagnosisCodeDescription": "PAIN IN RIGHT HIP"
        },
        {
          "diagnosisCode": "M545",
          "diagnosisCodeDescription": "LOW BACK PAIN"
        }
      ],
      "procedureCodeDescription": "THERAPEUTIC EXERCISES",
      "lineNumber": "003",
      "placeOfServiceDescription": "OFFICE"
    },
    {
      "typeOfServiceCode": "1",
      "procedureCode": "97140",
      "procedureCodeType": "CPT-4",
      "quantityOfServices": "1",
      "procedureCodeModifiers": [
        {
          "procedureCodeModifier": "GP",
          "procedureCodeModifierDescription": "SERVICES DELIVERED UNDER AN OUTPATIENT PHYSICAL THERAPY PLAN OF CARE"
        }
      ],
      "toDate": "YYYY-MM-DD",
      "placeOfService": "00",
      "typeOfServiceDescription": "Medical/Surgical",
      "fromDate": "YYYY-MM-DD",
      "serviceDiagnoses": [
        {
          "diagnosisCode": "M25551",
          "diagnosisCodeDescription": "PAIN IN RIGHT HIP"
        },
        {
          "diagnosisCode": "M545",
          "diagnosisCodeDescription": "LOW BACK PAIN"
        }
      ],
      "procedureCodeDescription": "MANUAL THERAPY 1/> REGIONS",
      "lineNumber": "001",
      "placeOfServiceDescription": "OFFICE"
    },
    {
      "typeOfServiceCode": "1",
      "procedureCode": "97110",
      "procedureCodeType": "CPT-4",
      "quantityOfServices": "1",
      "procedureCodeModifiers": [
        {
          "procedureCodeModifier": "GP",
          "procedureCodeModifierDescription": "SERVICES DELIVERED UNDER AN OUTPATIENT PHYSICAL THERAPY PLAN OF CARE"
        }
      ],
      "toDate": "YYYY-MM-DD",
      "placeOfService": "00",
      "typeOfServiceDescription": "Medical/Surgical",
      "fromDate": "YYYY-MM-DD",
      "serviceDiagnoses": [
        {
          "diagnosisCode": "M25551",
          "diagnosisCodeDescription": "PAIN IN RIGHT HIP"
        },
        {
          "diagnosisCode": "M545",
          "diagnosisCodeDescription": "LOW BACK PAIN"
        }
      ],
      "procedureCodeDescription": "THERAPEUTIC EXERCISES",
      "lineNumber": "002",
      "placeOfServiceDescription": "OFFICE"
    }
  ],
  "providerFirstName": "ANONYMOUS",
  "adjudicationFlag": "Y",
  "stateCode": "XX",
  "icdCodeType": "10",
  "claimStatus": "P",
  "providerAddress1": "SOME ADDRESS"
}

解决方案 #

这类多层嵌套的JSON需要逐层拆解，不能只靠一次json_normalize或explode解决。以下是分步处理的代码：

步骤1：拆解第一层嵌套（icdDiagnosisCodes） #

先把icdDiagnosisCodes列表展开，同时保留原数据的其他字段：

# 先将主数据转为DataFrame
main_df = pd.json_normalize(required_records).fillna('')
# 拆解icdDiagnosisCodes，生成多行数据（每个诊断码对应一行主数据）
df_explode_icd = main_df.explode('icdDiagnosisCodes', ignore_index=True)
# 将拆解后的诊断码字段扁平化
df_icd_flat = pd.concat([
    df_explode_icd.drop('icdDiagnosisCodes', axis=1),
    pd.json_normalize(df_explode_icd['icdDiagnosisCodes'])
], axis=1)

步骤2：拆解第二层嵌套（serviceProcedures） #

接着处理serviceProcedures列表，同样先展开再扁平化：

# 拆解serviceProcedures
df_explode_procedures = df_icd_flat.explode('serviceProcedures', ignore_index=True)
# 扁平化serviceProcedures字段
df_procedures_flat = pd.concat([
    df_explode_procedures.drop('serviceProcedures', axis=1),
    pd.json_normalize(df_explode_procedures['serviceProcedures'])
], axis=1)

步骤3：拆解第三层嵌套（procedureCodeModifiers和serviceDiagnoses） #

最后处理procedureCodeModifiers和serviceDiagnoses这两个深层列表：

# 拆解procedureCodeModifiers
df_explode_modifiers = df_procedures_flat.explode('procedureCodeModifiers', ignore_index=True)
df_modifiers_flat = pd.concat([
    df_explode_modifiers.drop('procedureCodeModifiers', axis=1),
    pd.json_normalize(df_explode_modifiers['procedureCodeModifiers'])
], axis=1)

# 拆解serviceDiagnoses
df_final = df_modifiers_flat.explode('serviceDiagnoses', ignore_index=True)
df_final = pd.concat([
    df_final.drop('serviceDiagnoses', axis=1),
    pd.json_normalize(df_final['serviceDiagnoses'])
], axis=1)

最终效果 #

经过以上三步，所有嵌套列表都会被完全拆解成单行结构，每个子项都对应独立的行，同时保留主数据的所有关联字段。你可以通过print(df_final.head())查看结果。

关键说明 #

• explode()的作用是将列表类型的字段展开，每个列表元素生成一行，复制其他字段的内容
• pd.json_normalize()用于将字典类型的字段扁平化为多个列
• 多层嵌套需要逐层处理，从最外层的列表开始，依次向内拆解

内容的提问来源于stack exchange，提问作者Bhavani Kumar Metla