将含多类的ElementTree XML转换为带属性提取的多Sheet DataFrame

输入XML内容 #

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE raml SYSTEM 'raml20.dtd'>
<raml version="2.0" xmlns="raml20.xsd">
  <cmData type="actual">
    <header>
      <log dateTime="2023-12-20T16:11:15.000+05:30" action="created" appInfo="ActualExporter">InternalValues are used</log>
    </header>
    <managedObject class="NRREL" version="NRBTSCL23R4_2315_120" distName="PLMN-PLMN/MRBTS-277215/NRBTS-277215/NRCELL-0/NRREL-1" id="14823863">
      <p name="cellIndividualSsbRsrpOffset">24</p>
      <p name="cellIndividualSsbRsrqOffset">24</p>
      <p name="gNbId">277215</p>
      <p name="gNbIdLength">22</p>
      <list name="gnbPlmn">
        <item>
          <p name="mcc">405</p>
          <p name="mnc">854</p>
          <p name="mncLength">3</p>
        </item>
      </list>
      <p name="handoverAllowedSA">1</p>
      <p name="handoverAllowedSARedCap">0</p>
      <p name="isRplCellForPSCell">0</p>
      <p name="lcrId">16</p>
      <p name="nrDcAllowed">0</p>
      <p name="removeNotAllowed">1</p>
    </managedObject>
    <managedObject class="NRREL" version="NRBTSCL23R4_2315_120" distName="PLMN-PLMN/MRBTS-277215/NRBTS-277215/NRCELL-0/NRREL-131" id="14823714">
      <p name="cellIndividualSsbRsrpOffset">24</p>
      <p name="cellIndividualSsbRsrqOffset">24</p>
      <p name="gNbId">277240</p>
      <p name="gNbIdLength">22</p>
      <list name="gnbPlmn">
        <item>
          <p name="mcc">405</p>
          <p name="mnc">854</p>
          <p name="mncLength">3</p>
        </item>
      </list>
      <p name="handoverAllowedSA">1</p>
      <p name="handoverAllowedSARedCap">0</p>
      <p name="isRplCellForPSCell">0</p>
      <p name="lcrId">18</p>
      <p name="nrDcAllowed">0</p>
      <p name="removeNotAllowed">0</p>
    </managedObject>
    <managedObject class="NRREL" version="NRBTSCL23R4_2315_120" distName="PLMN-PLMN/MRBTS-277215/NRBTS-277215/NRCELL-0/NRREL-132" id="14823717">
      <p name="cellIndividualSsbRsrpOffset">24</p>
      <p name="cellIndividualSsbRsrqOffset">24</p>
      <p name="gNbId">277227</p>
      <p name="gNbIdLength">22</p>
      <list name="gnbPlmn">
        <item>
          <p name="mcc">405</p>
          <p name="mnc">854</p>
          <p name="mncLength">3</p>
        </item>
      </list>
      <p name="handoverAllowedSA">1</p>
      <p name="handoverAllowedSARedCap">0</p>
      <p name="isRplCellForPSCell">0</p>
      <p name="lcrId">18</p>
      <p name="nrDcAllowed">0</p>
      <p name="removeNotAllowed">0</p>
    </managedObject>
    <managedObject class="NRRELE" version="NRBTSCL23R4_2315_120" distName="PLMN-PLMN/MRBTS-277215/NRBTS-277215/NRCELL-2/NRRELE-95" id="14823887">
      <p name="cellIndividualOffset">24</p>
      <p name="eNodeBId">7948</p>
      <list name="ecgiPlmn">
        <item>
          <p name="mcc">405</p>
          <p name="mnc">854</p>
          <p name="mncLength">3</p>
        </item>
      </list>
      <p name="eutranCellBlacklisted">0</p>
      <p name="lcrId">16</p>
      <p name="removeNotAllowed">1</p>
    </managedObject>
    <managedObject class="NRRELE" version="NRBTSCL23R4_2315_120" distName="PLMN-PLMN/MRBTS-277215/NRBTS-277215/NRCELL-2/NRRELE-96" id="14823888">
      <p name="cellIndividualOffset">24</p>
      <p name="eNodeBId">32</p>
      <list name="ecgiPlmn">
        <item>
          <p name="mcc">405</p>
          <p name="mnc">854</p>
          <p name="mncLength">3</p>
        </item>
      </list>
      <p name="eutranCellBlacklisted">0</p>
      <p name="lcrId">50</p>
      <p name="removeNotAllowed">1</p>
    </managedObject>
  </cmData>
</raml>

实现代码 #

使用Python的xml.etree.ElementTree解析XML，结合pandas生成分类DataFrame并导出到Excel：

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

# 解析XML文件（替换为你的XML文件路径）
tree = ET.parse('raml_data.xml')
root = tree.getroot()

# 按类别存储数据
class_data_dict = {}

# 遍历所有managedObject节点
for mo_node in root.findall('.//managedObject'):
    mo_class = mo_node.attrib['class']
    # 初始化类别数据列表
    if mo_class not in class_data_dict:
        class_data_dict[mo_class] = []
    
    # 初始化当前行数据
    current_row = {
        'id': mo_node.attrib['id']
    }
    
    # 拆分distName字段为独立列
    dist_segments = mo_node.attrib['distName'].split('/')[1:]  # 跳过开头的PLMN-PLMN
    for segment in dist_segments:
        key, value = segment.split('-', 1)
        current_row[key] = value
    
    # 提取所有<p>节点的键值对
    for p_node in mo_node.findall('.//p'):
        current_row[p_node.attrib['name']] = p_node.text
    
    # 提取list节点下的嵌套属性
    for list_node in mo_node.findall('.//list'):
        list_name = list_node.attrib['name']
        current_row[list_name] = 'List'  # 标记列表存在
        # 遍历item下的子属性
        for item_p in list_node.findall('.//p'):
            col_name = f'Item-{list_name}-{item_p.attrib["name"]}'
            current_row[col_name] = item_p.text
    
    # 将当前行加入对应类别
    class_data_dict[mo_class].append(current_row)

# 导出到Excel，每个类别对应一个工作表
with pd.ExcelWriter('raml_export.xlsx') as writer:
    for cls_name, data_list in class_data_dict.items():
        df = pd.DataFrame(data_list)
        # 调整列顺序：dist拆分字段 -> id -> 其他字段
        dist_cols = [col for col in df.columns if col in ['MRBTS', 'NRBTS', 'NRCELL', cls_name]]
        remaining_cols = [col for col in df.columns if col not in dist_cols + ['id']]
        df = df[dist_cols + ['id'] + remaining_cols]
        
        # 导出并打印预览
        df.to_excel(writer, sheet_name=cls_name, index=False)
        print(f"=== {cls_name} 数据预览 ===")
        print(df.to_string(index=False))
        print("\n")

输出效果 #

NRREL DataFrame #

MRBTS NRBTS NRCELL NRREL        id cellIndividualSsbRsrpOffset cellIndividualSsbRsrqOffset  gNbId gNbIdLength gnbPlmn Item-gnbPlmn-mcc Item-gnbPlmn-mnc Item-gnbPlmn-mncLength handoverAllowedSA handoverAllowedSARedCap isRplCellForPSCell lcrId nrDcAllowed removeNotAllowed
277215 277215      0     1  14823863                          24                          24 277215          22     List              405              854                      3                  1                        0                  0    16           0                1
277215 277215      0   131  14823714                          24                          24 277240          22     List              405              854                      3                  1                        0                  0    18           0                0
277215 277215      0   132  14823717                          24                          24 277227          22     List              405              854                      3                  1                        0                  0    18           0                0

NRRELE DataFrame #

MRBTS NRBTS NRCELL NRRELE        id cellIndividualOffset eNodeBId ecgiPlmn Item-ecgiPlmn-mcc Item-ecgiPlmn-mnc Item-ecgiPlmn-mncLength eutranCellBlacklisted lcrId removeNotAllowed
277215 277215      2     95  14823887                    24     7948     List              405              854                      3                     0    16                1
277215 277215      2     96  14823888                    24       32     List              405              854                      3                     0    50                1

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