Python XML转CSV多值合并及第二DataFrame关联取值问题咨询

核心调整说明 #

• 提前将Thing对象的「名称- ID」映射处理为字典，相比DataFrame合并操作效率更高，同时适配单值、多值的匹配场景
• 列表转逗号分隔字符串直接使用Python内置str.join()方法实现，无需额外分组操作；即使有120列，也仅需要对返回结果为列表的字段单独处理即可，无需全列配置分组规则
• 移除原代码中循环内重复生成thing_df的冗余逻辑，优先遍历全量XML节点拿到完整的映射关系后，再处理集合类条目

完整修改后代码 #

# -*- coding: utf-8 -*-
import lxml.etree as Xet
import pandas as pd

# 定义主集合数据框的列名
coll_cols = ["Collection item", "ITEM-ID", "ATTRIB-1", "PERSON-TYPE-1-NAME" ,
        "ATTRIB-2", "PERSON-TYPE-2-NAME", "RELATED-THING-1 id",
        "RELATED-THING-2 IDs", "Years"]
coll_rows = []
# 定义Thing映射存储列表
thing_rows = []

# 解析XML文件
xmlparse = Xet.parse('sample.xml')
root = xmlparse.getroot()

# 第一遍遍历：先收集所有Thing的名称-ID映射
for row in root:
    row_type = row.findtext('type')
    if row_type in ("THING-TYPE-1", "THING-TYPE-2"):
        thing_id = row.findtext("THING-ID", "")
        thing_name = row.findtext("name", "")
        if thing_name:
            thing_rows.append({"Thing Name": thing_name, "Thing ID": thing_id})
# 转换为查询字典，后续匹配直接查字典即可
thing_map = {item["Thing Name"]: item["Thing ID"] for item in thing_rows}

# 第二遍遍历：处理集合类条目
for row in root:
    if row.findtext('type') != "COLLECTION-ITEM":
        continue
    # 提取基础属性
    name = row.findtext("name", "Missing name")
    item_id = row.findtext("ITEM-ID", "Missing item ID")
    attrib_1 = row.findtext("ATTRIB-1", "Missing attribute 1")
    p1_name = row.findtext("./PERSON-TYPE-1-NAME/result/row/name", "")
    attrib_2 = row.findtext("ATTRIB-2", "Missing attribute 2")
    p2_name = row.findtext("./PERSON-TYPE-2-NAME/result/row/name", "")
    
    # 处理关联对象1匹配：单值匹配
    relat_thing1 = row.xpath("./RELATED-THING-1/result/row/name/text()")
    relat_thing1_id = thing_map.get(relat_thing1[0], "") if relat_thing1 else ""
    
    # 处理关联对象2匹配：多值匹配后拼接为逗号分隔字符串
    relat_thing2 = row.xpath("./RELATED-THING-2/result/row/name/text()")
    relat_thing2_ids = ', '.join([thing_map.get(name, "") for name in relat_thing2])
    
    # 处理年份列表拼接
    years = row.xpath("./RPTD-HIST-CODE/result/row/name/text()")
    years_str = ', '.join(years)

    coll_rows.append({
        "Collection item": name,
        "ITEM-ID": item_id,
        "ATTRIB-1": attrib_1,
        "PERSON-TYPE-1-NAME": p1_name,
        "ATTRIB-2": attrib_2,
        "PERSON-TYPE-2-NAME": p2_name,
        "RELATED-THING-1 id": relat_thing1_id,
        "RELATED-THING-2 IDs": relat_thing2_ids,
        "Years": years_str
    })

coll_df = pd.DataFrame(coll_rows, columns=coll_cols)
# 输出到CSV，添加utf-8-sig编码兼容中文Excel打开，不需要可移除
coll_df.to_csv('output.csv', encoding='utf-8-sig', index=False)

补充说明 #

如果需要兼容空值、异常值场景，可以自行在join操作前增加列表非空判断逻辑，输出格式和给出的预期结果完全一致。

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