XML重复标签解析为DataFrame时集合值问题的修复方案咨询

从多个XML文件提取标签值生成Pandas DataFrame时，标签不重复时代码运行正常，但标签重复时，DataFrame列值会呈现为集合形式（例如FName列显示{'Michael','James',' '}）。需要将重复标签拆分为多行，每行对应一个重复项，空值显示为NULL。

原解析代码 #

def parse_XML(list_of_trees, df_cols):

    def get_el(el_list):
        if len(el_list) > 1:
            return [el_text.text for el_text in el_list]
        else:
            return el_list[0].text
    rows = []
    for tree in list_of_trees:
        xroot = tree.getroot()

        res = {}
        for node in xroot:
            for el in df_cols[0:]:
                if node is not None and node.find(f".//{el}") is not None:
                    el_res = get_el(node.findall(f".//{el}"))  # 修正原代码多余的语法点
                    if el not in res:
                        res[el] = el_res
                    elif type(res[el]) == list:
                        res[el].extend(el_res)
                    else:
                        res[el] = [res[el], el_res]
        rows.append(res)

    out_df = pd.DataFrame(rows, columns=df_cols)

    return out_df

XML示例 #

<PD>
  <Clt>
    <PType>xxxx</PType>
    <PNumber>xxxxx</PNumber>
    <UID>xxxx</UID>
    <TEfd>xxxxx</TEfd>
    <TExd>xxxxxx</TExd>
    <DID>xxxxx</DID>
    <CType>xxxxx</CType>
    <FName>Michael</FName>
    <MName></MName>
    <LName>Smith</LName>
    <FName>James</FName>
    <MName> </MName>
    <LName> </LName>
    <MAL>Home</MAL>
    <AddressLine1>xxxx</AddressLine1>
    <AddressLine2>xxxx</AddressLine2>
    <AddressLine3></AddressLine3>
    <City>xxxx</City>
    <State>xx</State>
    <ZipCode>xxxx</ZipCode>
    <Country>xxxx</Country>
    <Pr>
      <PrType>xxxxx</PrType>
      <PrName>xxxxxx</PrName>
      <PrID>xxxxxx</PrID>
    </Pr>
</Clt>
  <CData>
    <InceptionYear>2021</InceptionYear>
    <FName> </FName>
  </CData>
</PD>

当前输出 #

PNumber FName               MName     LName
xxxx    {Michael,James,''} {'',' '}   {Smith,''}

期望输出 #

PNumber      FName          MName     LName
    xxxx    Michael                   Smith
    xxxx    James                          
    xxxx                    NULL      NULL

修复后的代码 #

import pandas as pd

def parse_XML(list_of_trees, df_cols):
    rows = []
    for tree in list_of_trees:
        xroot = tree.getroot()
        # 收集每个目标标签的所有值，统一存为列表
        tag_values = {}
        for el in df_cols:
            elements = xroot.findall(f".//{el}")
            # 处理空文本，统一转为空白字符串
            values = [el.text.strip() if el.text is not None else '' for el in elements]
            tag_values[el] = values
        
        # 确定总行数：取所有标签值列表的最大长度
        max_rows = max(len(vals) for vals in tag_values.values()) if tag_values else 0
        
        # 逐行构建记录
        for i in range(max_rows):
            row = {}
            for el in df_cols:
                vals = tag_values.get(el, [])
                # 按索引取值，超出范围则为空
                val = vals[i] if i < len(vals) else ''
                # 空值替换为NULL
                row[el] = val if val != '' else 'NULL'
            rows.append(row)
    
    out_df = pd.DataFrame(rows, columns=df_cols)
    return out_df

关键修复说明 #

1. 统一收集标签值：将每个目标标签的所有文本值整理为列表，无论是否重复，保证格式一致
2. 动态确定行数：以重复次数最多的标签的数量作为总行数，确保所有重复项都能拆分为独立行
3. 逐行生成记录：非重复标签的列表长度为1，会在所有行重复填充对应值；空值或空白值统一替换为NULL
4. 修正语法错误：移除原代码中多余的语法点，避免运行报错

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