如何将Pandas DataFrame按父子列关系转换为指定嵌套JSON格式？

给定如下Pandas DataFrame：

dict_ = {'ID': 'ABC', 'CountryList': None, 'CountryList_City': None, 'City_Name': 'aaaa', 'City_Area': 22222, 'CountryList_Coordinates': None, 
         'Coordinates_X': 111, 'Coordinates_Y': 222, 'CountryList_Restaurants': None, 'Restaurants_Name': 'aaa', 'Restaurants_MenuList': None,'MenuList_Weekdays': 'bbbb', 'MenuList_Weekends': 'cccc' }

data = pd.DataFrame([dict_])

需要根据列名下划线的父子关系（如CountryList_Coordinates是Coordinates_X、Coordinates_Y的父级），将其转换为指定嵌套JSON格式：

{
  "ID": "ABC",
  "CountryList": [
    {
      "City": {
        "Name": "aaaa",
        "Area": 22222
      },
      "Coordinates": {
        "X": 111,
        "Y": 222
      },
      "Restaurants": {
        "Name": "aaa",
        "MenuList": [
          {
            "Weekdays": "bbbb",
            "Weekends": "cccc"
          }
        ]
      }
    }
  ]
}

规则：无下划线且值为None的列（如CountryList）为父级，需开启嵌套。

原尝试代码仅支持两层嵌套，缺失MenuList部分，输出结果如下：

{
  "ID": "ABC",
  "CountryList": [
    {"City": {"Name": "aaaa", "Area": 22222}},
    {"Coordinates": {"X": 111, "Y": 222}},
    {"Restaurants": {"Name": "aaa"}}
  ]
}

以下是支持任意深度嵌套的实现代码：

import pandas as pd
import json

dict_ = {'ID': 'ABC', 'CountryList': None, 'CountryList_City': None, 'City_Name': 'aaaa', 'City_Area': 22222, 'CountryList_Coordinates': None, 
         'Coordinates_X': 111, 'Coordinates_Y': 222, 'CountryList_Restaurants': None, 'Restaurants_Name': 'aaa', 'Restaurants_MenuList': None,'MenuList_Weekdays': 'bbbb', 'MenuList_Weekends': 'cccc' }

data = pd.DataFrame([dict_])
data_json = data.to_dict(orient='records')[0]

def build_nested_structure(data):
    result = {}
    # 处理顶层非父级字段（无下划线且值不为None）
    top_fields = [k for k, v in data.items() if '_' not in k and v is not None]
    for field in top_fields:
        result[field] = data[field]
    
    # 处理顶层父级（无下划线且值为None的列）
    top_parents = [k for k, v in data.items() if '_' not in k and v is None]
    for parent in top_parents:
        result[parent] = []
        child_container = {}
        # 提取该父级下的直接子节点
        child_keys = {k.split('_')[1] for k in data.keys() if k.startswith(f"{parent}_")}
        
        for child in child_keys:
            # 收集子节点的所有关联字段
            child_related = {k.split('_', 1)[1]: data[k] 
                            for k in data.keys() if k.startswith(f"{child}_")}
            # 检查子节点本身是否是需要转列表的父级
            if child in data and data[child] is None:
                # 递归构建子节点的嵌套结构并转为列表
                child_container[child] = [build_nested_structure(child_related)]
            else:
                # 递归构建子节点的嵌套结构
                child_content = build_nested_structure(child_related)
                # 如果子节点有直接值，补充进去
                if child in data and data[child] is not None:
                    child_content = {**{child.split('_')[-1]: data[child]}, **child_content}
                child_container[child] = child_content
        
        result[parent].append(child_container)
    return result

# 生成嵌套结构并格式化输出
nested_result = build_nested_structure(data_json)
print(json.dumps(nested_result, indent=2))

1. 递归处理多层嵌套：通过递归函数build_nested_structure识别每一层的父子关系，支持任意深度的嵌套结构，解决了原代码只能处理两层的问题。
2. 分层识别父级：先处理顶层非父级字段，再识别顶层父级，逐层向下提取子节点并构建嵌套内容。
3. 列表转换逻辑：当子节点本身是无下划线且值为None的父级时（如MenuList），自动将其内容转为列表格式，符合需求中的嵌套规则。

运行代码后将得到包含完整MenuList部分的目标JSON结构。

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