XML to dictionary to DataFrame in Python

Question

I want to print data from an XML file. To do that, I created a dictionary to parse the file. Finally, I used a for loop to print the data in a new DataFrame.

<BREVIER>
  <BRV>
    <MONO>stuff</MONO>
    <TITD>stuff</TITD>
    <TITF>Blabla</TITF>
    <CMPD>stuff</CMPD>
    <CMPF>stuff</CMPF>
    <INDD>stuff</INDD>
    <INDF>Blablo</INDF>
    <CINDD>stuff</CINDD>
    <CINDF>stuff</CINDF>
    <POSD>stuff</POSD>
    <POSF>stuff</POSF>
    <DEL>true</DEL>
  </BRV>

and so on with many, many BRV categories.

The output I expect to have:

Nom_du_medicament    Indication
Blabla               Blablo

I tried this code:

# encoding: utf-8

import xmltodict
import pprint
import json
import pandas as pd

with open('Brevier.xml',encoding='UTF-8','rb') as fd:
    my_dict = xmltodict.parse(fd.read(),encoding='UTF-8')
    tableau_indic=pd.DataFrame()
    for section in my_dict ['BREVIER']['BRV']:
        drugname = section.get('TITF')
        print(drugname in tableau_indic.loc(["Nom_du_medicament"]))
        drugindication = section.get('INDF')
        print(drugindication in tableau_indic.loc(["Indication"]))
print(tableau_indic)
fd.close()

I am getting a type error TypeError: unhashable type: 'list'

Since it did not work, here is the second method I tried using .loc:

# encoding: utf-8
import xmltodict
import pprint
import json
import pandas as pd

with open('Brevier.xml',encoding='UTF-8') as fd:
    my_dict = xmltodict.parse(fd.read(),encoding='UTF-8')
    tableau_indic=pd.DataFrame
    for section in my_dict ['BREVIER']['BRV']:
        drugname = section.get('TITF')
        print(tableau_indic.loc["Nom_du_medicament"])
        drugindication = section.get('INDF')
        print(tableau_indic.loc["Indication"])
print(tableau_indic)
fd.close()

This time I had the KeyError: 'Nom_du_medicament' error.

Is there a way to avoid these errors?

Why bother with all these conversions instead of printing directly from the xml file? — Jack Fleeting
– Jack Fleeting, Commented Oct 1, 2020 at 16:36
I am a beginner and that was the most logical way that I've imagined. — marou95thebest
– marou95thebest, Commented Oct 1, 2020 at 16:41
I would suggest you edit your question with a representative sample of the xml and the expected output from that sample and we can see what can be done with it. — Jack Fleeting
– Jack Fleeting, Commented Oct 1, 2020 at 16:43
Ok, I have added a representative sample of the xml and the expected output from that sample — marou95thebest
– marou95thebest, Commented Oct 1, 2020 at 16:52

Jack Fleeting · Accepted Answer · 2020-10-01 17:08:47Z

2

There are several ways to approach it, but basically, since you are dealing with an xml file, might as well use xml tools like xpath.

Let's say your xml looks like this:

meds = """<BREVIER>
  <BRV>
    <MONO>stuff</MONO>
    <TITF>Blabla</TITF>
    <CMPD>stuff</CMPD>
    <INDF>Blablo</INDF>
    <CINDD>stuff</CINDD>
    <DEL>true</DEL>
  </BRV>
  <BRV>
    <MONO>stuff</MONO>
    <TITF>Blabla 2</TITF>
    <CMPD>stuff</CMPD>
    <INDF>Blablo 2</INDF>
    <CINDD>stuff</CINDD>
    <DEL>true</DEL>
  </BRV>
</BREVIER>"""

You can use lxml to process it:

from lxml import etree
doc = etree.XML(meds)
print('Nom_du_medicament Indication')
for m in doc.xpath('//BRV'):
      print(m.xpath('./TITF/text()')[0], m.xpath('./INDF/text()')[0])

Output:

Nom_du_medicament Indication
Blabla Blablo
Blabla 2 Blablo 2

From here, you can format the output, load it into a dataframe or whatever.

answered Oct 1, 2020 at 17:08

Jack Fleeting

25k6 gold badges27 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

marou95thebest Over a year ago

Your code seems to run fine but I still have one last error related to the XML file: lxml.etree.XMLSyntaxError: Start tag expected, '<' not found, line 1, column 1 This is confusing because I checked my file and the first line is <Brevier> so normally there shouldn't be any errors.

Jack Fleeting Over a year ago

@marou95thebest Your actual xml is probably different from the sample in my question, so the error indicates some problem with the formatting of the xml. Ocassionally, it can be solved by changing doc = etree.XML(meds) to doc = etree.fromstring(meds). If not, you have to make sure your actual xml is valid.

Collectives™ on Stack Overflow

XML to dictionary to DataFrame in Python

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related