Python将Excel交易数据转换为指定格式XML的代码优化咨询

你当前代码的核心问题是：遍历Excel每一行数据时都会新建一个<Portfolio>节点，导致同一个投资组合的多笔持仓被拆分到了多个重复的<Portfolio>节点下，和目标输出结构不符。

先对Excel数据按投资组合名称（Name字段）分组，同一个投资组合的所有持仓归为一组，每组仅生成一个<Portfolio>节点，再遍历组内的每一笔持仓生成对应的<PortfolioPosition>节点即可。

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

# 读取Excel数据
df = pd.read_excel("C:/Users/junag/Documents/XML/Portfolio2.xlsx", sheet_name="Sheet1", dtype=object)

# 构建XML根节点
root = ET.Element('trading-data')
root.set('xmlns:xsi', 'http://www.w3.org/2001/XMLSchema-instance')
tree = ET.ElementTree(root)
Portfolios = ET.SubElement(root, "Portfolios")
Defaults = ET.SubElement(Portfolios, "Defaults", BaseCurrency="USD")

# 按投资组合名称分组
for portfolio_name, group in df.groupby("Name", sort=False):
    # 取分组第一行的投资组合公共属性
    first_row = group.iloc[0]
    Portfolio = ET.SubElement(Portfolios, "Portfolio", 
                              Name=portfolio_name, 
                              BaseCurrency=first_row.BaseCurrency2, 
                              TradingPower=str(first_row.TradingPower),
                              ValidationProfile=first_row.ValidationProfile, 
                              CommissionProfile=first_row.CommissionProfile)
    PortfolioPositions = ET.SubElement(Portfolio, "PortfolioPositions")
    # 遍历分组内每一行生成持仓节点
    for _, row in group.iterrows():
        if row.Type == "Cash":
            PortfolioPosition = ET.SubElement(PortfolioPositions, "PortfolioPosition", 
                                              Type=row.Type, 
                                              Volume=str(row.Volume))
            Cash = ET.SubElement(PortfolioPosition, 'Cash', Currency=str(row.Currency))
        else:
            PortfolioPosition = ET.SubElement(PortfolioPositions, "PortfolioPosition", 
                                              Type=row.Type, 
                                              Volume=str(row.Volume),
                                              Invested=str(row.Invested), 
                                              BaseInvested=str(row.BaseInvested))
            Instrument = ET.SubElement(PortfolioPosition, 'Instrument', 
                                       Ticker=str(row.Ticker), 
                                       ISIN=str(row.ISIN), 
                                       Market=str(row.Market),
                                       Currency=str(row.Currency2), 
                                       CFI=str(row.CFI))

# 格式化缩进并输出XML
ET.indent(tree, space="\t", level=0)
tree.write("Portfolios_converted2.xml", encoding="utf-8", xml_declaration=True)

• 该代码默认同一个投资组合的Name、BaseCurrency2、TradingPower等公共字段取值一致，匹配你提供的Excel数据源结构
• 新增xml_declaration=True参数会在XML头部添加编码声明，符合常规XML规范要求

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