Python解析XML多属性值并生成带属性标识的DataFrame列名

我正在用Python解析XML文件，目标是提取字段的多个属性值，给每个字段标记是否包含属性，并将属性值与列名结合，生成带属性标识的表头（并非所有列都有属性）。最终要把这些表头导入主Excel文档，用来构建企业报表的定义/数据字典，XML里的数据部分不重要，只需要提取表头。

导入的库 #

#---import libraries
import xlwings as xw #---lib to do most of the excel steps
import pandas as pd #---lib to read csv file
import openpyxl as xl #---lib to convert csv to xls
import os #---lib to extract filename for input and folder movement
from datetime import datetime
import xml.etree.ElementTree as Etree

现有代码及问题 #

目前用这段代码可以识别带属性的字段，并在表头添加"Custom"文本：

for elem in tree.iter():
    if bool(elem.attrib):
        ea = str(elem.attrib)
        B.update({elem.tag + " (Custom)": ea})
        A.append(B) # appending B to list

    else:
        B.update({elem.tag: elem.attrib})
        A.append(B) # appending B to list

输出结果如下：

company department employee name job      salary (Custom)
0      {}         {}       {}   {}  {}  {'datatype': 'int'}

但这段代码只能处理单个属性的场景，我需要适配多个属性的情况（比如XML里的custom和datatype属性），要同时提取这两个属性值并整合到最终的DataFrame/列表列名中。

理想的列名格式 #

希望把custom属性的值用圆括号()包裹，datatype属性的值用方括号[]包裹，示例如下：

XML文件内容 #

<?xml version="1.0" encoding="UTF-8"?>
<company>
    <department>
        <employee>
            <name>
            </name>
            <job>
            </job>
            <salary  custom = 'Yes'>
            </salary>
            <salary  datatype = 'int'>
            </salary>
        </employee>
        <employee>
            <name>
            </name>
            <job>
            </job>
            <salary  custom = 'Yes'>
            </salary>
            <salary  datatype = 'int'>
            </salary>
        </employee>
        <employee>
            <name>
            </name>
            <job>
            </job>
            <salary  custom = 'Yes'>
            </salary>
            <salary  datatype = 'int'>
            </salary>
        </employee>
        <employee>
            <name>
            </name>
            <job>
            </job>
            <salary  custom = 'Yes'>
            </salary>
            <salary  datatype = 'int'>
            </salary>
        </employee>
    </department>
    <department>
        <employee>
            <name>
            </name>
            <job>
            </job>
            <salary  custom = 'Yes'>
            </salary>
            <salary  datatype = 'int'>
            </salary>
        </employee>
        <employee>
            <name>
            </name>
            <job>
            </job>
            <salary  custom = 'Yes'>
            </salary>
            <salary  datatype = 'int'>
            </salary>
        </employee>
        <employee>
            <name>
            </name>
            <job>
            </job>
            <salary  custom = 'Yes'>
            </salary>
            <salary  datatype = 'int'>
            </salary>
        </employee>
    </department>
</company>

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