如何将扁平化的pandas DataFrame还原为原始JSON/XML格式

还原为嵌套JSON #

核心思路是按列名的分隔符逆向拆分层级，自动识别数字索引的层级为列表结构，递归构造嵌套字典，最后组装为原始JSON的外层结构即可。
实现代码如下：

import pandas as pd
import json

def unflatten_json(flat_dict, key_sep='.'):
    """
    将扁平化字典还原为嵌套结构，自动适配字典、列表层级
    """
    out = {}
    for composite_key, value in flat_dict.items():
        if pd.isna(value):
            continue
        parts = composite_key.split(key_sep)
        current = out
        skip_next = False
        for i, part in enumerate(parts[:-1]):
            if skip_next:
                skip_next = False
                continue
            next_part = parts[i+1]
            # 下一级为纯数字则判定为列表索引
            if next_part.isdigit():
                if part not in current:
                    current[part] = []
                idx = int(next_part)
                # 补全列表长度到对应索引位
                while len(current[part]) <= idx:
                    current[part].append({})
                current = current[part][idx]
                skip_next = True
            else:
                if part not in current:
                    current[part] = {}
                current = current[part]
        if not skip_next:
            current[parts[-1]] = value
    return out

# 实际使用示例
# 1. 将DataFrame每一行转为嵌套字典
nested_rows = df.apply(lambda x: unflatten_json(x.dropna().to_dict()), axis=1).tolist()
# 2. 组装为原始JSON的外层结构，示例中外层key为features
final_json = {"features": nested_rows}
# 3. 导出为JSON文件
with open("restored_data.json", "w", encoding="utf-8") as f:
    json.dump(final_json, f, indent=2, ensure_ascii=False)

还原为XML结构 #

先复用上面的unflatten_json函数将每行数据转为嵌套字典，再递归生成XML节点即可，实现代码如下：

import xml.etree.ElementTree as et
import pandas as pd

def dict_to_xml(parent_node, nested_dict):
    """
    递归将嵌套字典转为XML节点树
    """
    for key, value in nested_dict.items():
        if isinstance(value, dict):
            child_node = et.SubElement(parent_node, key)
            dict_to_xml(child_node, value)
        elif isinstance(value, list):
            for idx, item in enumerate(value, 1):
                child_node = et.SubElement(parent_node, f"{key}.{idx}")
                dict_to_xml(child_node, item)
        else:
            child_node = et.SubElement(parent_node, key)
            child_node.text = str(value)

# 实际使用示例
# 1. 将DataFrame每一行转为嵌套字典，适配XML扁平化的.分隔符
nested_rows = df.apply(lambda x: unflatten_json(x.dropna().to_dict(), key_sep='.'), axis=1).tolist()
# 2. 创建XML根节点，可替换为原始XML的根节点名称
root = et.Element("root")
# 3. 逐行生成XML子节点，节点名可替换为原始XML的行级节点名称
for row in nested_rows:
    record_node = et.SubElement(root, "record")
    dict_to_xml(record_node, row)
# 4. 导出为XML文件
tree = et.ElementTree(root)
with open("restored_data.xml", "wb") as f:
    tree.write(f, encoding="utf-8", xml_declaration=True)

注意事项 #

• 如果原始XML需要区分节点属性和子节点内容，可在扁平化阶段给属性列加统一前缀（如@attr_xxx），逆向阶段识别前缀写入节点的attrib属性即可。
• 空值会在转换阶段自动跳过，不会生成冗余的嵌套层级。

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