python - 使用 python 在 XML 文件中获取唯一项对

Question

我有一个这样设计的 XML 数据集:

<DataSet>
    <Record><!-- each DataSet can have zero to many Record tags -->
        <Identifier><!-- each Record will definitely have exactly one Identifier tag -->
            <MRN value="MRN"></MRN><!-- Each Identifier will have zero or at the most one MRN tag, with alphanumeric character as the patient's MRN in value attribute -->
        </Identifier>
        <Medication><!-- each Record will definitely have exactly one Medication tag -->
            <Item value="CUI"></Item><!-- Each Medication will have zero to many Item tags, with alphanumeric character as the Medication CUI in the value attribute -->
        </Medication>
    </Record>
</DataSet>

我想将一组唯一的 MRN 值/CUI 值导出到一个 csv 文件中。最终的 CSV 文件看起来像这两列:

如果一个 MRN 有多个 CUI，那么我希望 MRN 值在每个 CUI 的第一列重复。此外，我不想要任何空值，这意味着我不想提取任何没有任何 CUI 的 MRN，反之亦然。

我尝试过使用列表和字典，但问题是我无法让最终输出看起来像我想要的那样，每个 CUI 的 MRN 值都重复。我什至创建了一个数据框来查看哪个 CUI 属于哪个 MRN，但这同样不是我想要的输出。这是我使用的代码:

import pandas as pd
import xml.etree.ElementTree as ET
tree = ET.parse('/med/dataset.xml')
root = tree.getroot()


mrn = []
cui = []
for element in root:
    for item in element[0::2]:
        d=[]
        mrn.append(d)
        for child in item:
            d.append(child.attrib['value'])
    for item in element[1::2]:
        d=[]
        cui.append(d)
        for child in item:
            d.append(child.attrib['value'])
new_list = [a + b for a,b in zip(mrn, cui)]
print(new_list)
df = pd.DataFrame(new_list)
print(df)

我希望仅使用标准 Python 库(pandas、numpy、xml.etree.ElementTree 和 csv)就可以做到这一点。

有什么想法吗？

Answer 1

您可以使用 ElementTree 解析 XML，将 mrn/cui 组合存储在一个集合中，然后使用 csv 创建 csv。

这是一个例子...

XML 输入 (dataset.xml)

<DataSet>
    <Record>
        <Identifier>
            <MRN value="MRN1"></MRN>
        </Identifier>
        <Medication>
            <Item value="CUI1"></Item>
            <Item value="CUI2"></Item>
            <Item value="CUI3"></Item>
        </Medication>
    </Record>
    <Record>
        <Identifier>
            <MRN value="MRN1"></MRN>
        </Identifier>
        <Medication>
            <Item value="CUI1"></Item>
            <Item value="CUI2"></Item>
            <Item value="CUI3"></Item>
        </Medication>
    </Record>
    <Record>
        <Identifier>
            <MRN value="MRN2"></MRN>
        </Identifier>
        <Medication>
            <Item value="CUI1"></Item>
            <Item value="CUI2"></Item>
            <Item value="CUI3"></Item>
        </Medication>
    </Record>
</DataSet>

python

import csv
import xml.etree.ElementTree as ET

tree = ET.parse("dataset.xml")

mrn_cui = set()

for record in tree.findall(".//Record"):
    mrn = record.find("./Identifier/MRN")
    items = record.findall("./Medication/Item")
    if mrn is not None and items:
        for cui in items:
            mrn_cui.add(f"{mrn.attrib['value']}|{cui.attrib['value']}")

with open("test.csv", "w", newline="") as csvfile:
    csvwriter = csv.writer(csvfile, delimiter=",", quoting=csv.QUOTE_MINIMAL)
    csvwriter.writerow(["MRN", "CUI"])
    for entry in sorted(mrn_cui):
        csvwriter.writerow(entry.split('|'))

输出 (test.csv)

MRN,CUI
MRN1,CUI1
MRN1,CUI2
MRN1,CUI3
MRN2,CUI1
MRN2,CUI2
MRN2,CUI3

您还可以通过使用 lxml 而不是 ElementTree 来稍微简化它...

python

import csv
from lxml import etree

tree = etree.parse("dataset.xml")

mrn_cui = set()

for record in tree.xpath(".//Record[Identifier/MRN/@value and Medication/Item/@value]"):
    mrn = record.xpath("./Identifier/MRN/@value")
    for cui in record.xpath("./Medication/Item/@value"):
        mrn_cui.add(f"{mrn[0]}|{cui}")

with open("test.csv", "w", newline="") as csvfile:
    csvwriter = csv.writer(csvfile, delimiter=",", quoting=csv.QUOTE_MINIMAL)
    csvwriter.writerow(["MRN", "CUI"])
    for entry in sorted(mrn_cui):
        csvwriter.writerow(entry.split('|'))