Convert a Pandas Dataframe to XML

There is no Pandas function to convert a dataframe to XML but we will show you how to do it with a simple custom function. This is very useful especialy when working with flask and you want your API to have as output an XML file.

#lets create a dataframe
df=pd.DataFrame({'A':[1,2,3,4,5],'B':['a','b','c','d','e']})

   A  B
0  1  a
1  2  b
2  3  c
3  4  d
4  5  e

to_xml Function

def to_xml(df):
    def row_xml(row):
        xml = ['<item>']
        for i, col_name in enumerate(row.index):
            xml.append('  <{0}>{1}</{0}>'.format(col_name, row.iloc[i]))
        xml.append('</item>')
        return '\n'.join(xml)
    res = '\n'.join(df.apply(row_xml, axis=1))
    return(res)

to_xml(df)

'<item>\n  <A>1</A>\n  <B>a</B>\n</item>\n<item>\n  <A>2</A>\n  <B>b</B>\n</item>\n<item>\n  <A>3</A>\n  <B>c</B>\n</item>\n<item>\n  <A>4</A>\n  <B>d</B>\n</item>\n<item>\n  <A>5</A>\n  <B>e</B>\n</item>'

Above you can see that you will get a string and that’s ok because when it’s rendered it will be in a nice XML format like below.

<item>
    <A>1</A>
    <B>a</B
</item>
<item>
    <A>2</A>
    <B>b</B>
</item>
<item>
    <A>3</A>
    <B>c</B>
</item>
<item>
    <A>4</A>
    <B>d</B>
</item>
<item>
    <A>5</A>
    <B>e</B>
</item>

Save XML file

with open('df.XML', 'w') as f:
    f.write(to_xml(df))

Bonus: How to return XML files in Flask

#you need to import Response
from flask import Response


@app.route('/')
def test():
    xml = to_xml(df)
    return Response(xml, mimetype='text/xml')