How to convert pandas / dataframe to XML?

Question

There is an easy way to take the pandas / df table:

field_1 field_2 field_3 field_4 cat 15,263 2.52 00:03:00 dog 1,652 3.71 00:03:47 test 312 3.27 00:03:41 book 300 3.46 00:02:40 

And convert it to XML line by line:

 <item> <field name="field_1">cat</field> <field name="field_2">15263</field> <field name="filed_3">2.52</field> ... <item> <field name="field_1">dog</field> and so on... 

Thanks in advance for your help.

Answer 1

You can create a function that creates an item node from a row in your DataFrame:

 def func(row): xml = ['<item>'] for field in row.index: xml.append(' <field name="{0}">{1}</field>'.format(field, row[field])) xml.append('</item>') return '\n'.join(xml) 

And then apply the function along axis=1 .

 >>> print '\n'.join(df.apply(func, axis=1)) <item> <field name="field_1">cat</field> <field name="field_2">15,263</field> <field name="field_3">2.52</field> <field name="field_4">00:03:00</field> </item> <item> <field name="field_1">dog</field> <field name="field_2">1,652</field> <field name="field_3">3.71</field> <field name="field_4">00:03:47</field> </item> ... 

Answer 2

To deploy an excellent answer on Victor (and slightly tweak it to work with duplicate columns), you can set it as the to_xml DataFrame method:

 def to_xml(df, filename=None, mode='w'): def row_to_xml(row): xml = ['<item>'] for i, col_name in enumerate(row.index): xml.append(' <field name="{0}">{1}</field>'.format(col_name, row.iloc[i])) xml.append('</item>') return '\n'.join(xml) res = '\n'.join(df.apply(row_to_xml, axis=1)) if filename is None: return res with open(filename, mode) as f: f.write(res) pd.DataFrame.to_xml = to_xml 

Then you can print xml:

 In [21]: print df.to_xml() <item> <field name="field_1">cat</field> <field name="field_2">15,263</field> <field name="field_3">2.52</field> <field name="field_4">00:03:00</field> </item> <item> ... 

or save it in a file:

 In [22]: df.to_xml('foo.xml') 

Obviously, this example needs to be modified in accordance with the xml standard.

Answer 3

You can use the xml.etree.ElementTree package to create a readable format in very few lines of code.

 root = etree.Element('data'); for i,row in dframe.iterrows(): item = etree.SubElement(root, 'item', attrib=row.to_dict()); etree.dump(root); 

This will create an XML tree (as root), where each row will be of type item and have attributes for all columns. You can create a more nested tree using columns by creating a subitem for each field.

Then you can also read the xml file back into Python using the ElementTree package:

 xml.etree.ElementTree.parse('xml_file.xml');