XML转Pandas DataFrame遇问题：无法提取文本且行数据异常

示例XML #

<Fruits>
    <Fruit ReferenceDate="2022-09-22"
                FruitName="Apple">
        <Identifier FruitIdentifier="111"
                    FruitBrand="GoldenApple"/>
        <FruitInformation Country="Turkey"
                          Colour="Green"/>
        <CompanyInformation CompanyName="GlobalFruits"
                            Location="USA"/>
        <Languages>
            <LanguageDependent CountryId="GB"
                               LanguageId="EN">
                <FreeText1>Sample sentence 1.</FreeText1>
                <FreeText2>Sample sentence 2.</FreeText2>
            </LanguageDependent>
        </Languages>
    </Fruit>
    <Fruit ReferenceDate="2022-09-22"
                FruitName="Orange">
        <Identifier FruitIdentifier="222"
                    FruitBrand="BestOrange"/>
        <FruitInformation Country="Egypt"
                          Colour="Orange"/>
        <CompanyInformation CompanyName="FreshFood"
                            Location="UK"/>
        <Languages>
            <LanguageDependent CountryId="GB"
                               LanguageId="EN">
                <FreeText1>Sample sentence 3.</FreeText1>
                <FreeText2>Sample sentence 4.</FreeText2>
            </LanguageDependent>
        </Languages>
    </Fruit>
</Fruits>

期望输出 #

最终DataFrame需包含以下列：ReferenceDate、FruitName、FruitIdentifier、FruitBrand、Country、Colour、CompanyName、Location、CountryId、LanguageId、FreeText1、FreeText2，每个<Fruit>节点对应一行完整数据，示例如下：

当前代码的问题 #

• 无法提取FreeText1和FreeText2的文本内容
• 子节点的每组属性各自生成一行数据，导致DataFrame存在大量空值且数据拆分错误

修正后的代码 #

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

xtree = et.parse("fruits.xml")
xroot = xtree.getroot() 

# 新增FreeText1和FreeText2列
df_cols = ["ReferenceDate", "FruitName", "FruitIdentifier",
           "FruitBrand", "Country", "Colour", "CompanyName",
           "Location", "CountryId", "LanguageId", "FreeText1", "FreeText2"]
rows = []

# 仅遍历每个<Fruit>节点，而非所有节点
for fruit_node in xroot.findall(".//Fruit"):
    # 提取Fruit节点自身属性
    row_data = {
        "ReferenceDate": fruit_node.attrib.get("ReferenceDate"),
        "FruitName": fruit_node.attrib.get("FruitName")
    }
    
    # 提取Identifier子节点属性
    identifier_node = fruit_node.find("Identifier")
    if identifier_node is not None:
        row_data.update({
            "FruitIdentifier": identifier_node.attrib.get("FruitIdentifier"),
            "FruitBrand": identifier_node.attrib.get("FruitBrand")
        })
    
    # 提取FruitInformation子节点属性
    fruit_info_node = fruit_node.find("FruitInformation")
    if fruit_info_node is not None:
        row_data.update({
            "Country": fruit_info_node.attrib.get("Country"),
            "Colour": fruit_info_node.attrib.get("Colour")
        })
    
    # 提取CompanyInformation子节点属性
    company_info_node = fruit_node.find("CompanyInformation")
    if company_info_node is not None:
        row_data.update({
            "CompanyName": company_info_node.attrib.get("CompanyName"),
            "Location": company_info_node.attrib.get("Location")
        })
    
    # 提取LanguageDependent子节点的属性与文本内容
    lang_dep_node = fruit_node.find(".//LanguageDependent")
    if lang_dep_node is not None:
        row_data.update({
            "CountryId": lang_dep_node.attrib.get("CountryId"),
            "LanguageId": lang_dep_node.attrib.get("LanguageId")
        })
        # 获取FreeText1和FreeText2的文本
        free_text1 = lang_dep_node.find("FreeText1")
        if free_text1 is not None:
            row_data["FreeText1"] = free_text1.text
        free_text2 = lang_dep_node.find("FreeText2")
        if free_text2 is not None:
            row_data["FreeText2"] = free_text2.text
    
    rows.append(row_data)

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

问题解决说明 #

1. 提取文本内容：定位到<LanguageDependent>节点下的<FreeText1>和<FreeText2>子节点，通过.text属性获取内部文本内容。
2. 避免多行拆分：不再遍历所有节点，改为仅遍历每个<Fruit>节点，在节点内部逐一提取所有子节点的属性和文本，将数据整合到同一行字典中，确保每个<Fruit>对应DataFrame的一行完整数据。

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