Ytukyg

I have an XML dataset that is designed like this:

<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>

And I want to export a list of unique pairs of MRN values/CUI values into a csv file. The final CSV file would look something like these two columns:

If an MRN has more than one CUI, then I want the MRN value to repeat on the first column for each CUI. Also, I do not want any empty values, meaning I don't want to extract any MRNs that don't have any CUIs or viceversa.

I have tried working with lists and dictionaries, but the problem is I can't get the final output to look like I want, with the MRN value repeating for each CUI. I have even created a dataframe to see which CUI belongs to which MRN, but again that is not the output I want. Here is the code I have used:

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)

I want to be able to do this using only standard Python libraries (pandas, numpy, xml.etree.ElementTree, and csv).

Any ideas?

You could use ElementTree to parse the XML, store the mrn/cui combos in a set, and then create the csv with csv.

Here's an example...

XML Input (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('|'))

Output (test.csv)

MRN,CUI

MRN1,CUI1

MRN1,CUI2

MRN1,CUI3

MRN2,CUI1

MRN2,CUI2

MRN2,CUI3

You could also simplify it a little by using lxml instead of 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('|'))

You can just loop over your medications inside your loop over MRNs. Try something like this.

mrn_li = 

cui_li = 

for record in root:

    for mrn in record[0]:

        for med in record[1]:

            mrn_li.append(mrn.attrib['value'])

            cui_li.append(med.attrib['value'])



new_list = [[i, j] for i, j in zip(mrn_li,cui_li)]

print new_list