如何使用Python将CSV数据转换为指定双层嵌套结构的JSON文件

核心需求 #

将CSV数据转换为Web场景适用的双层嵌套JSON格式，满足两个去重规则：

• 最终输出中每个member_id全局唯一
• 单个member_id下每个tag_name唯一

样例输入（detail.csv） #

member_id, tag_name, detail_name, detail_value
-------------------------------------------------------
abc123, m1, Service_A, 20
abc123, m1, Service_B, 20
abc123, m2, Service_C, 10
xyz456, m3, Service A, 5
xyz456, m3, Service A, 10

期望输出格式 #

[
  {
    "member_id": "abc123",
    "tag": [
      {
        "tag_name": "m1",
        "detail": [
          {
            "detail_name": "Service_A",
            "detail_value": "20"
          },
          {
            "detail_name": "Service_B",
            "detail_value": "20"
          }
        ]
      },
      {
        "tag_name": "m2",
        "detail": [
          {
            "detail_name": "Service_C",
            "detail_value": "10"
          }
        ]
      }
    ]
  },
  {
    "member_id": "xyz456",
    "tag": [
      {
        "tag_name": "m3",
        "detail": [
          {
            "detail_name": "Service A",
            "detail_value": "5"
          },
          {
            "detail_name": "Service A",
            "detail_value": "10"
          }
        ]
      }
    ]
  }
]

可行实现代码 #

import json
import pandas as pd

# 读取CSV，预处理去除列名前后空格，避免列名匹配错误
df = pd.read_csv(
    '../detail.csv', 
    sep=',', 
    header=0,
    index_col=False,
    dtype = {'member_id':str,'tag_name':str,'detail_name':str,'detail_value':str}
)
df.columns = df.columns.str.strip()

final_list = []
# 第一层分组：按member_id分组，从根源避免member_id重复
for member_id, member_df in df.groupby('member_id'):
    member_item = {"member_id": member_id, "tag": []}
    # 第二层分组：在单个member的子集内按tag_name分组，保证同用户下标签唯一
    for tag_name, tag_df in member_df.groupby('tag_name'):
        # 直接生成detail列表，简化循环逻辑
        detail_list = tag_df[["detail_name", "detail_value"]].to_dict("records")
        tag_item = {"tag_name": tag_name, "detail": detail_list}
        member_item["tag"].append(tag_item)
    final_list.append(member_item)

# 输出格式化JSON
result = json.dumps(final_list, ensure_ascii=False, indent=2)
print(result)

实现逻辑说明 #

• 采用双层groupby结构，分层控制两层维度的唯一性，避免单层分组带来的重复问题
• 使用pandas内置to_dict("records")方法直接生成明细列表，减少手动循环代码，性能更稳定
• 增加列名空格预处理逻辑，兼容CSV导出时可能存在的列名前后空格问题

内容的提问来源于stack exchange，提问作者Aaron.K