python pandas dataframe how to use stylesheet in to_xml function

Question

I have a dataframe like this:

col1	col2	col3	col4	col5	col6	col7	col8	col9	col10
...	...	...	...	...	...	...	...	...	...
...	...	...	...	...	...	...	...	...	...

and i want to create an xml like this:

<?xml version='1.0' encoding='utf-8'?>
<root xmlns:xsi="http://www.example.com" xmlns="http://www.example.com">
  <all>
    <col>
      <col1>...</col1>
      <col2>...</col2>
      <col3>...</col3>
      <col4>...</col4>
      <col5>...</col5>
      <col6>...</col6>
      <group1>
        <col7>...</col7>
        <col8>...</col8>
      </group1>
      <group2>
        <col9>...</col9>
        <col10>...</col10>
      </group2>
    </col>
    <col>
      <col1>...</col1>
      <col2>...</col2>
      <col3>...</col3>
      <col4>...</col4>
      <col5>...</col5>
      <col6>...</col6>
      <group1>
        <col7>...</col7>
        <col8>...</col8>
      </group1>
      <group2>
        <col9>...</col9>
        <col10>...</col10>
      </group2>
    </col>
  </all>
</root>

my solution is to use stylesheet in to_xml function like this:

df.to_xml("example.xml", root_name='all', row_name='col', encoding='utf-8', xml_declaration=True, pretty_print=True, index=False, stylesheet='example.xslt')

but i have no idea how to write example.xslt file and how to set to_xml function to get desired xml. I am looking for suggestions and examples of xslt that might work

Redshank

Answer 1

Your setup for the to_xml function seems to be ok. In the code below I'm generating a DataFrame with 20 rows and 10 columns to emulate your example. You will find below an example of a xslt file that might work for the sample you have given and the XML output from that.

import pandas as pd
import numpy as np

np.random.seed(42)
df = pd.DataFrame(np.random.randint(0,100,size=(20, 10)), columns=[f'col{i+1}' for i in range(10)])

df.to_xml("example.xml", root_name='all', row_name='col', encoding='utf-8', xml_declaration=True, pretty_print=True, index=False, stylesheet='example.xslt')

File example.xslt

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
<root>
  <all>
      <xsl:for-each select="all/col">
      <col>
            <col1><xsl:value-of select="col1"/></col1>
            <col2><xsl:value-of select="col2"/></col2>
            <col3><xsl:value-of select="col3"/></col3>
            <col4><xsl:value-of select="col4"/></col4>
            <col5><xsl:value-of select="col5"/></col5>
            <col6><xsl:value-of select="col6"/></col6>
        <group1>
            <col7><xsl:value-of select="col7"/></col7>
            <col8><xsl:value-of select="col8"/></col8>
        </group1>
        <group2>
            <col9><xsl:value-of select="col9"/></col9>
            <col10><xsl:value-of select="col10"/></col10>
        </group2>
      </col>
      </xsl:for-each>
  </all>
</root>
</xsl:template>
</xsl:stylesheet>

Output File example.xml

<?xml version="1.0"?>
<root>
    <all>
        <col>
            <col1>51</col1>
            <col2>92</col2>
            <col3>14</col3>
            <col4>71</col4>
            <col5>60</col5>
            <col6>20</col6>
            <group1>
                <col7>82</col7>
                <col8>86</col8>
            </group1>
            <group2>
                <col9>74</col9>
                <col10>74</col10>
            </group2>
        </col>
        <col>
            <col1>87</col1>
            <col2>99</col2>
            <col3>23</col3>
            <col4>2</col4>
        ...
    ...
...

Improvisator

Had to use <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:output method="xml" version="1.0" encoding="UTF-8" standalone="yes" indent="yes"/> to get the output to have the proper headers and pretty printed. But the answer is spot on otherwise. Thanks. – Cravens