使用Pandas将列名含点分隔层级的CSV转换为嵌套JSON

我明白你遇到的困扰了——用Pandas自带的to_json()方法没法把点分隔的层级列名转成你想要的嵌套JSON结构，默认参数确实做不到这一点。下面给你一个可行的解决方案，分步骤来实现：

步骤1：准备示例数据（模拟你的DataFrame） #

首先把你提供的数据构造成Pandas DataFrame，顺便修正列名里的拼写错误（比如adress改成address，strret1改成street1，不然生成的JSON会有错误的键名）：

import pandas as pd
import json

# 构造你的数据
data = [
    ["account123","id123","riga","latvia","laura","female",1990,"subs123","1990-12-14T00:00:00Z","latvia","street 1","email1@myorg.com|email2@sanoma.com","+371401234567"],
    ["account123","id000","riga","latvia","laura","female",1990,"subs456","1990-12-14T00:00:00Z","latvia","street 1","email1@myorg.com","+371401234567"],
    ["account123","id456","riga","latvia","laura","female",1990,"subs789","1990-12-14T00:00:00Z","latvia","street 1","email1@myorg.com","+371401234567"]
]

cols = [
    "org.iden.account","org.iden.id","adress.city","adress.country","person.name.fullname","person.gender","person.birthYear",
    "subs.id","subs.subs1.birthday","subs.subs1.org.address.country","subs.subs1.org.address.strret1",
    "subs.org.buyer.email.address","subs.org.buyer.phone.number"
]

df = pd.DataFrame(data, columns=cols)

# 修正列名拼写错误
df.columns = df.columns.str.replace('adress', 'address').str.replace('strret1', 'street1')

步骤2：编写转换函数，将单行数据转为嵌套字典 #

我们需要一个自定义函数，把带有多层索引的单行Series转成嵌套字典结构：

def series_to_nested_dict(series):
    nested_dict = {}
    for keys_tuple, value in series.items():
        current_level = nested_dict
        # 遍历层级键，除了最后一个
        for key in keys_tuple[:-1]:
            if key not in current_level:
                current_level[key] = {}
            current_level = current_level[key]
        # 给最后一层键赋值
        current_level[keys_tuple[-1]] = value
    return nested_dict

步骤3：将DataFrame转为嵌套字典列表，再转成JSON #

用apply方法把每行数据都转换成嵌套字典，然后转成列表，最后用json.dumps生成格式化的JSON：

# 逐行转换为嵌套字典
nested_data_list = df.apply(series_to_nested_dict, axis=1).tolist()

# 生成格式化的JSON
formatted_json = json.dumps(nested_data_list, indent=2)

print(formatted_json)

运行结果 #

这样就能得到你期望的嵌套JSON列表了，第一行数据的结构和你给出的示例完全一致，所有行都会被正确转换为嵌套结构。

这个方法的核心是把点分隔的列名拆分成多层索引，然后通过遍历层级键来构建嵌套字典，完美适配你的需求。

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