如何基于动态表头将Python DataFrame转换为嵌套JSON？

问题描述 #

我正在尝试将Python DataFrame转换为JSON，CSV格式如下：

policyID|Name | Description | Contact_Telephone | Contact_Email |
--------|-----| ------------|-------------------|---------------|
1221    |ABC  | New Policy  | 1234567890        | abc@mail.com  |
1223    |DEF  | Old Policy  | 2341243324        | def@gmail.com |

期望的JSON输出（注：原示例存在重复键问题，实际应为对象数组）：

[
    {
        "policyID": 1221,
        "Name": "ABC",
        "Description": "New Policy",
        "Contact": {
            "Telephone": 1234567890,
            "Email": "abc@mail.com"
        }
    },
    {
        "policyID": 1223,
        "Name": "DEF",
        "Description": "Old Policy",
        "Contact": {
            "Telephone": 2341243324,
            "Email": "def@gmail.com"
        }
    }
]

核心需求是识别列名中的下划线，将其作为JSON的嵌套层级。我已通过pandas的to_json(orient='records')实现第一级元素转换，现寻求处理下划线列名生成嵌套JSON的方法。

解决方案 #

可以通过自定义函数处理每一行数据，将下划线分隔的列名拆解为嵌套字典结构，再将结果转为JSON。步骤如下：

1. 读取并预处理CSV数据 #

首先读取CSV，注意分隔符是|，同时清理列名中的空格（原CSV列名如Name 带有尾部空格）：

import pandas as pd
import json

# 读取CSV，指定分隔符为|，跳过最后一行分隔线
df = pd.read_csv('your_file.csv', sep='|', skipfooter=1, engine='python')
# 清理列名的前后空格
df.columns = df.columns.str.strip()

2. 定义嵌套转换函数 #

编写函数将一行数据的键（列名）按下划线分割，生成嵌套字典：

def create_nested_dict(row):
    nested_dict = {}
    for key, value in row.items():
        # 跳过空值
        if pd.isna(value):
            continue
        # 按下划线分割键
        keys = key.split('_')
        current_level = nested_dict
        # 遍历分割后的键，构建嵌套层级
        for k in keys[:-1]:
            if k not in current_level:
                current_level[k] = {}
            current_level = current_level[k]
        # 设置最后一级的键值
        current_level[keys[-1]] = value
    return nested_dict

3. 转换并生成JSON #

对DataFrame的每一行应用函数，生成嵌套结构的列表，再转为JSON：

# 转换每一行数据
nested_data = [create_nested_dict(row) for _, row in df.iterrows()]
# 转为格式化的JSON字符串
json_output = json.dumps(nested_data, indent=4)
print(json_output)

说明 #

• 原期望的JSON示例存在重复键问题（同一个对象里有两个policyID），这不符合JSON规范，因此实际输出为包含两个政策对象的数组，这是更合理的结构。
• 函数支持任意深度的下划线嵌套（比如A_B_C会转为{"A": {"B": {"C": value}}}）。
• 只要CSV读取时pandas自动识别了数据类型，policyID和Contact_Telephone这类数值会保留为数字类型，而非字符串。

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