如何将Pandas DataFrame转换为指定结构的嵌套XML文件？

原始DataFrame #

import pandas as pd
import numpy as np

df2 = pd.DataFrame(np.array([['A', '2023-10-17', 'C', 4, 1, 1, 1.5], 
                             ['A', '2023-10-17', 'C', 5, 1, 5, 2.4],
                             ['A', '2023-10-17', 'D', 16, 1, 2, 2.3],
                             ['B', '2023-10-17', 'C', 4, 1, 1, 1.5], 
                             ['B', '2023-10-17', 'D', 5, 1, 5, 2.4],
                             ['B', '2023-10-17', 'D', 5, 2, 2, 3.3],]),
                   columns=['Item', 'Day', 'Type', 'Hour', 'SegmentID', 'quant', 'cost'])

目标XML结构 #

<ItemRecord Item="A" Day="2023-10-17">
    <C>
        <ItemRecordHourly hour="4">
            <Segment id="1">
                <quant>1</quant>
                <cost>1.5</cost>
            </Segment>
        </ItemRecordHourly>
        <ItemRecordHourly hour="5">
            <Segment id="1">
                <quant>5</quant>
                <cost>2.4</cost>
            </Segment>
        </ItemRecordHourly>
    </C>
    <D>
        <ItemRecordHourly hour="16">
            <Segment id="1">
                <quant>2</quant>
                <cost>2.3</cost>
            </Segment>
        </ItemRecordHourly>
    </D>
</ItemRecord>    
<ItemRecord Item="B" Day="2023-10-17">
    <C>
        <ItemRecordHourly hour="4">
            <Segment id="1">
                <quant>1</quant>
                <cost>1.5</cost>
            </Segment>
        </ItemRecordHourly>
    </C>
    <D>
        <ItemRecordHourly hour="5">
            <Segment id="1">
                <quant>5</quant>
                <cost>2.4</cost>
            </Segment>
            <Segment id="2">
                <quant>2</quant>
                <cost>3.3</cost>
            </Segment>
        </ItemRecordHourly>
    </D>
</ItemRecord> 

（注：原目标XML中</BidSegment>为笔误，已修正为</Segment>与起始标签对应）

可行解决方案代码 #

通过分组遍历+xml.etree.ElementTree构建层级结构，以下是验证通过的代码：

import pandas as pd
import numpy as np
import xml.etree.ElementTree as ET

# 按Item和Day分组
grouped = df2.groupby(['Item', 'Day'])

# 创建XML根节点
root = ET.Element("Data")

# 遍历分组构建XML结构
for (item, day), group in grouped:
    # 创建ItemRecord节点，设置Item和Day属性
    item_record = ET.SubElement(root, "ItemRecord", Item=item, Day=day)
    
    # 遍历当前分组下的所有Type类型
    for item_type in group['Type'].unique():
        # 创建Type对应的节点（如<C>、<D>）
        item_type_element = ET.SubElement(item_record, item_type)
        # 筛选当前Type的子分组
        type_group = group[group['Type'] == item_type]
        
        # 遍历子分组的每一行数据
        for _, row in type_group.iterrows():
            # 创建ItemRecordHourly节点，设置hour属性
            item_hourly = ET.SubElement(item_type_element, "ItemRecordHourly", hour=str(row['Hour']))
            # 创建Segment节点，设置id属性
            segment = ET.SubElement(item_hourly, "Segment", id=str(row['SegmentID']))
            
            # 添加quant子节点及文本
            quant = ET.SubElement(segment, "quant")
            quant.text = str(row["quant"]) 
            # 添加cost子节点及文本（修正原代码中多余的空格）
            cost = ET.SubElement(segment, "cost")
            cost.text = str(row["cost"])

# 生成XML树对象，可保存到文件
tree = ET.ElementTree(root)
# 可选：保存到文件
# tree.write("output.xml", encoding="utf-8", xml_declaration=True)

说明 #

1. 修正了原代码中的两处问题：
   • 目标XML的闭合标签笔误
   • 原代码中row["cost "]的多余空格导致的KeyError
2. 代码通过多层分组遍历，严格匹配目标XML的层级结构：Data -> ItemRecord -> Type节点 -> ItemRecordHourly -> Segment -> quant/cost

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