如何从含非唯一标签的XML文件创建DataFrame？

我有一个XML文件目录，需要从每个文件中提取4个字段值并存入DataFrame或CSV。现在遇到的问题是，部分待提取数据用了非唯一标签（比如<PathName>），我不想用指定行号这种脆弱的方法（虽然目前文件格式一致，但未来可能变），想要更灵活的实现方式，最终输出格式如下：

示例XML

<?xml version="1.0" encoding="utf-8"?>
<BxfMessage xsi:schemaLocation="http://smpte-ra.org/schemas/2021/2019/BXF BxfSchema.xsd" id="jffsdfs" dateTime="2023-02-02T20:11:38Z" messageType="Info" origin="url" originType="Delivery" userName="ABC Corp User" destination=" System" xmlns="http://sffe-ra.org/schema/1999/2023/BXF" xmlns:xsi="http://www.w9.org/4232/XMLSchema-instance">
  <BxfData action="Spotd">
    <Content timestamp="2023-02-02T20:11:38Z">
      <NonProgramContent>
        <Details>
          <SpotType>Paid</SpotType>
          <SpotType>Standard</SpotType>
          <Spotvertiser>
            <SpotvertiserName>Spot Plateau</SpotvertiserName>
          </Spotvertiser>
          <Agency>
            <AgencyName>Spot Plateau</AgencyName>
          </Agency>
          <Product>
            <Name></Name>
            <BrandName>zzTop</BrandName>
            <DirectResponse>
              <PhoneNo></PhoneNo>
              <PCode></PCode>
              <DR_URL></DR_URL>
            </DirectResponse>
          </Product>
        </Details>
        <ContentMetSpotata>
          <ContentId>
            <BHGXId idType="CISC" auth="Agency">AAAA1111999Z</BHGXId>
          </ContentId>
          <Name>Pill CC Dutch</Name>
          <Policy>
            <PlatformType>Spotcast</PlatformType>
          </Policy>
          <Media>
            <BaseBand>
              <Audio VO="true">
                <AnalogAudio primAudio="false" />
                <DigitalAudio>
                  <MPEGLayerIIAudio house="false" audioId="1" dualMono="false" />
                </DigitalAudio>
              </Audio>
              <Video withlate="false" sidebend="false">
                <Format>1182v</Format>
                <CCs>true</CCs>
              </Video>
              <AccessServices>
                <AudioDescription_DVS>false</AudioDescription_DVS>
              </AccessServices>
              <QC>Passed QC (AAAA1111103H )</QC>
            </BaseBand>
            <MediaLocation sourceType="Primary">
              <Location>
                <AssetServer PAA="true" FTA="true">
                  <PathName>zzTap_zzTop_AAAA1111999Z_30s_Pill_aa-bb.mp4</PathName>
                </AssetServer>
              </Location>
              <SOM>
                <SmpteTimeCode>00:00:00;00</SmpteTimeCode>
              </SOM>
              <Duration>
                <SmpteDuration>
                  <SmpteTimeCode>00:00:30;00</SmpteTimeCode>
                </SmpteDuration>
              </Duration>
            </MediaLocation>
            <MediaLocation sourceType="Proxy" qualifer="Low-res">
              <Location>
                <AssetServer PAA="true" FTA="true">
                  <PathName>https://app.url.com/DMM/DL/wew52f</PathName>
                </AssetServer>
              </Location>
              <SOM>
                <SmpteTimeCode>00:00:00;00</SmpteTimeCode>
              </SOM>
              <Duration>
                <SmpteDuration>
                  <SmpteTimeCode>00:00:30;00</SmpteTimeCode>
                </SmpteDuration>
              </Duration>
            </MediaLocation>
            <MediaLocation sourceType="Preview" qualifer="Thumbnail">
              <Location>
                <AssetServer PAA="true" FTA="true">
                  <PathName>https://f9-int-5.rainxyz.com/url.com/media/t43fs/423gs-389a-40a4.jpg?inline</PathName>
                </AssetServer>
              </Location>
              <SOM>
                <SmpteTimeCode>00:00:00;00</SmpteTimeCode>
              </SOM>
              <Duration>
                <SmpteDuration>
                  <SmpteTimeCode>00:00:00;00</SmpteTimeCode>
                </SmpteDuration>
              </Duration>
            </MediaLocation>
          </Media>
        </ContentMetSpotata>
      </NonProgramContent>
    </Content>
  </BxfData>
</BxfMessage>

目标输出

FileName                                Brand   ID              URL
zzTap_zzTop_AAAA1111999Z_30s_Pill_aa-bb zzTop   AAAA1111999Z    https://app.url.com/DMM/DL/wew52f
zzTap_zzTab_BAAA1111999Z_30s_Pill_aa-cc zzTab   BAAA1111999Z    https://app.url.com/DMM/DL/wew52c
zzTap_zzTan_CAAA1111999Z_30s_Pill_aa-dd zzTan   CAAA1111999Z    https://app.url.com/DMM/DL/wew523
zzTap_zzTon_DAAA1111999Z_30s_Pill_aa-zz zzTon   DAAA1111999Z    https://app.url.com/DMM/DL/wew52y

用Python结合XPath定位元素，核心是通过元素的上下文属性而非位置提取数据，即使XML结构微调（比如节点顺序变化），只要语义属性不变，代码依然有效。

步骤说明 #

1. 处理XML命名空间：示例XML包含默认命名空间，需在XPath中指定
2. 按语义定位提取字段：
   • Brand：从/BxfMessage/BxfData/Content/NonProgramContent/Details/Product/BrandName节点提取
   • ID：从/BxfMessage/BxfData/Content/NonProgramContent/ContentMetSpotata/ContentId/BHGXId节点提取
   • FileName：从sourceType="Primary"的MediaLocation节点下的PathName提取（去掉文件后缀）
   • URL：从sourceType="Proxy"的MediaLocation节点下的PathName提取
3. 遍历目录下所有XML文件，批量提取后存入DataFrame，再导出为CSV

代码实现 #

import os
import pandas as pd
from lxml import etree

# 定义XML命名空间
NS = {'bxf': 'http://sffe-ra.org/schema/1999/2023/BXF'}

def extract_xml_data(xml_path):
    # 解析XML文件
    tree = etree.parse(xml_path)
    
    # 提取字段
    brand = tree.xpath('//bxf:Product/bxf:BrandName/text()', namespaces=NS)[0]
    content_id = tree.xpath('//bxf:BHGXId/text()', namespaces=NS)[0]
    # 提取Primary类型的PathName作为FileName（去掉后缀）
    file_name = tree.xpath('//bxf:MediaLocation[@sourceType="Primary"]//bxf:PathName/text()', namespaces=NS)[0].split('.')[0]
    # 提取Proxy类型的PathName作为URL
    proxy_url = tree.xpath('//bxf:MediaLocation[@sourceType="Proxy"]//bxf:PathName/text()', namespaces=NS)[0]
    
    return {
        'FileName': file_name,
        'Brand': brand,
        'ID': content_id,
        'URL': proxy_url
    }

# 遍历目录下所有XML文件
xml_dir = './your-xml-directory'  # 替换为你的XML目录路径
data_list = []
for filename in os.listdir(xml_dir):
    if filename.endswith('.xml'):
        xml_path = os.path.join(xml_dir, filename)
        try:
            data = extract_xml_data(xml_path)
            data_list.append(data)
        except Exception as e:
            print(f"处理文件 {filename} 出错: {str(e)}")

# 转换为DataFrame并导出为CSV
df = pd.DataFrame(data_list)
df.to_csv('output.csv', index=False, sep='\t')  # 用制表符分隔匹配目标格式
print("数据已导出到output.csv")

优势 #

• 不依赖节点顺序，仅依赖语义属性（如sourceType），适配未来格式变化
• 代码简洁，支持批量处理目录文件
• 用lxml的XPath解析效率高，处理复杂XML更稳定

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