Creating an XML File from a dataframe using Python and LXML

Question

I'm trying to create an XML file that uses a Pandas dataframe to populate the elements and subelements. Here is the code I have written:

import pandas as pd
from lxml import etree as et

df = pd.DataFrame({'id_profile': [439, 444654, 56454, 12222], 'ServiceDate': 
['2017-12-05', '2017-01-25', '2017-12-05', '2017-01-25'], 
'PrimaryServiceCategory': [25, 25, 33, 25]})

root = et.Element('ClientReport')
idnum = et.SubElement(root, 'ID')
prime_serv = et.SubElement(root, 'ServiceCategory')
serv_date = et.SubElement(root, 'ServiceDate')

for row in df.iterrows():
    idnum.text = df['id_profile']
    prime_serv.text = df['PrimaryServiceCategory']
    serv_date.text = df['ServiceDate']

print(et.tostring(root, pretty_print=True))

My expected outcome is:

<ClientReport>
   <ID>439</ID>
   <ServiceCategory>25</ServiceCategory>
   <ServiceDate>2017-12-05</ServiceDate>
</ClientReport>
<ClientReport>
   <ID>444654</ID>
   <ServiceCategory>25</ServiceCategory>
   <ServiceDate>2017-01-25</ServiceDate>
</ClientReport>
<ClientReport>
   <ID>12222</ID>
   <ServiceCategory>25</ServiceCategory>
   <ServiceDate>2017-01-25</ServiceDate>
</ClientReport>

Instead, I get:

TypeError: Argument must be bytes or unicode, got 'Series'

I'm not sure how to get iterated rows, rather than a static value, to populate the XML file's attributes. Is Pandas the right package here? And is LXML the right one as well?

Does this answer your question? How do convert a pandas/dataframe to XML? — End genocide - save Gaza
– End genocide - save Gaza, Commented Mar 25, 2021 at 9:27

Robᵩ · Accepted Answer · 2018-03-22 22:14:08Z

2

One solution is to iterate over the dataframe, populating each XML element individually:

import pandas as pd
from lxml import etree as et

df = pd.DataFrame({'id_profile': [439, 444654, 56454, 12222], 'ServiceDate':
['2017-12-05', '2017-01-25', '2017-12-05', '2017-01-25'],
'PrimaryServiceCategory': [25, 25, 33, 25]})

root = et.Element('root')

for row in df.iterrows():
    report = et.SubElement(root, 'ClientReport')
    idnum = et.SubElement(report, 'ID')
    prime_serv = et.SubElement(report, 'ServiceCategory')
    serv_date = et.SubElement(report, 'ServiceDate')
    idnum.text = str(row[1]['id_profile'])
    prime_serv.text = str(row[1]['PrimaryServiceCategory'])
    serv_date.text = str(row[1]['ServiceDate'])

print(et.tostring(root, pretty_print=True).decode('utf-8'))

answered Mar 22, 2018 at 22:14

Robᵩ

170k20 gold badges251 silver badges323 bronze badges

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

Collectives™ on Stack Overflow

Creating an XML File from a dataframe using Python and LXML

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related