如何将Pandas DataFrame转换为指定结构的嵌套JSON？

步骤1：编写单行数据转换函数 #

针对DataFrame的每一行，将地址类列表字段按索引配对组装成嵌套对象数组，同时处理addition_details生成criteria数组：

import pandas as pd
import json

def row_to_nested_json(row):
    # 组装address数组：按索引配对各地址字段的元素
    addresses = []
    addr_count = len(row['address_id'])
    for idx in range(addr_count):
        addresses.append({
            'address_id': row['address_id'][idx],
            'address_1': row['address_1'][idx],
            'address_2': row['address_2'][idx],
            'city': row['city'][idx],
            'state': row['state'][idx]
        })
    
    # 组装criteria数组
    criteria = [{'addition_details': detail} for detail in row['addition_details']]
    
    # 生成最终嵌套结构
    return {
        'id': row['id'],
        'col1': row['col1'],
        'address': addresses,
        'criteria': criteria
    }

步骤2：应用函数并输出JSON #

遍历DataFrame每行调用转换函数，再输出格式化后的JSON：

# 构建匹配你提供结构的示例DataFrame
df = pd.DataFrame({
    'id': ['A'],
    'col1': ['B'],
    'address_id': [['123','ABC']],
    'address_1': [['Street 123','Street ABC']],
    'address_2': [['Road 123','Road ABC']],
    'city': [['Dallas','Houston']],
    'state': [['Texas','Texas']],
    'addition_details': [['XYZ','LMP']]
})

# 处理所有行（示例仅一行）
nested_json_list = [row_to_nested_json(row) for _, row in df.iterrows()]

# 打印格式化后的JSON结果
print(json.dumps(nested_json_list[0], indent=2))

最终输出 #

{
  "id": "A",
  "col1": "B",
  "address": [
    {
      "address_id": "123",
      "address_1": "Street 123",
      "address_2": "Road 123",
      "city": "Dallas",
      "state": "Texas"
    },
    {
      "address_id": "ABC",
      "address_1": "Street ABC",
      "address_2": "Road ABC",
      "city": "Houston",
      "state": "Texas"
    }
  ],
  "criteria": [
    {
      "addition_details": "XYZ"
    },
    {
      "addition_details": "LMP"
    }
  ]
}

原代码问题说明 #

你之前用groupby+to_dict('list')的方式，会把每个地址字段的列表再嵌套一层（分组后的DataFrame每个单元格本身已是列表，to_dict('list')会将这些列表再收集到新列表中），导致结构不符合预期。直接遍历每行并按索引配对列表元素，能精准生成所需的嵌套结构。

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