Trouble parsing XML and getting data into pandas dataframe

Question

I am trying to import data from a XML file that contains breath-by-breath data from an exercise test. the XML structure is as follows (simplified to show the general structure):

<?xml version="1.0"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
   xmlns:o="urn:schemas-microsoft-com:office:office"
   xmlns:x="urn:schemas-microsoft-com:office:excel"
   xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
   xmlns:html="http://www.w3.org/TR/REC-html40">
    <Worksheet ss:Name="MetasoftStudio">
        <Table ss:ExpandedColumnCount="21" ss:ExpandedRowCount="458" x:FullColumns="1"    x:FullRows="1" ss:StyleID="s62" ss:DefaultColumnWidth="53">
            <Column ss:StyleID="s62" ss:AutoFitWidth="0" ss:Width="137"/>
            <Column ss:StyleID="s62" ss:AutoFitWidth="0" ss:Width="97"/>
            <Column ss:StyleID="s62" ss:AutoFitWidth="0" ss:Width="137"/>
            <Row ss:AutoFitHeight="0" ss:Height="26">
                <Cell ss:StyleID="Default"><Data ss:Type="String">t</Data></Cell>
                <Cell ss:StyleID="Default"><Data ss:Type="String">Phase</Data></Cell>
                <Cell ss:StyleID="Default"><Data ss:Type="String">Marker</Data></Cell>
                <Cell ss:StyleID="Default"><Data ss:Type="String">V'O2</Data></Cell>
                <Cell ss:StyleID="Default"><Data ss:Type="String">V'O2/kg</Data></Cell>
                <Cell ss:StyleID="Default"><Data ss:Type="String">V'O2/HR</Data></Cell>
                <Cell ss:StyleID="Default"><Data ss:Type="String">HR</Data></Cell>
                <Cell ss:StyleID="Default"><Data ss:Type="String">WR</Data></Cell>
                <Cell ss:StyleID="Default"><Data ss:Type="String">V'E/V'O2</Data></Cell>
                <Cell ss:StyleID="Default"><Data ss:Type="String">V'E/V'CO2</Data></Cell>
                <Cell ss:StyleID="Default"><Data ss:Type="String">RER</Data></Cell>
                <Cell ss:StyleID="Default"><Data ss:Type="String">V'E</Data></Cell>
                <Cell ss:StyleID="Default"><Data ss:Type="String">BF</Data></Cell>
            </Row>
            <Row ss:Height="15">
                <Cell ss:StyleID="Default"><Data ss:Type="String">h:mm:ss</Data></Cell>
                <Cell ss:StyleID="Default"><Data ss:Type="String"></Data></Cell>
                <Cell ss:StyleID="Default"><Data ss:Type="String"></Data></Cell>
                <Cell ss:StyleID="Default"><Data ss:Type="String">L/min</Data></Cell>
                <Cell ss:StyleID="Default"><Data ss:Type="String">ml/min/kg</Data></Cell>
                <Cell ss:StyleID="Default"><Data ss:Type="String">ml</Data></Cell>
                <Cell ss:StyleID="Default"><Data ss:Type="String">/min</Data></Cell>
                <Cell ss:StyleID="Default"><Data ss:Type="String">W</Data></Cell>
                <Cell ss:StyleID="Default"><Data ss:Type="String"></Data></Cell>
                <Cell ss:StyleID="Default"><Data ss:Type="String"></Data></Cell>
                <Cell ss:StyleID="Default"><Data ss:Type="String"></Data></Cell>
                <Cell ss:StyleID="Default"><Data ss:Type="String">L/min</Data></Cell>
                <Cell ss:StyleID="Default"><Data ss:Type="String">/min</Data></Cell>
            </Row>
            <Row ss:Height="15">
                <Cell ss:StyleID="Default"><Data ss:Type="String">0:00:06</Data></Cell>
                <Cell ss:StyleID="Default"><Data ss:Type="String">Rest</Data></Cell>
                <Cell ss:StyleID="Default"><Data ss:Type="String"></Data></Cell>
                <Cell ss:StyleID="Default"><Data ss:Type="Number">0.27972413565454501</Data></Cell>
                <Cell ss:StyleID="Default"><Data ss:Type="Number">4.3706896196022598</Data></Cell>
                <Cell ss:StyleID="Default"><Data ss:Type="Number">4.5856415681072953</Data></Cell>
                <Cell ss:StyleID="Default"><Data ss:Type="Number">61</Data></Cell>
                <Cell ss:StyleID="Default"><Data ss:Type="Number">0</Data></Cell>
                <Cell ss:StyleID="Default"><Data ss:Type="Number">27.002532271037801</Data></Cell>
                <Cell ss:StyleID="Default"><Data ss:Type="Number">26.4113108545688</Data></Cell>
                <Cell ss:StyleID="Default"><Data ss:Type="Number">1.0223851598932201</Data></Cell>
                <Cell ss:StyleID="Default"><Data ss:Type="Number">10.155340000000001</Data></Cell>
                <Cell ss:StyleID="Default"><Data ss:Type="Number">18.07</Data></Cell>
            </Row>
        </Table>
    </Worksheet>
</Workbook>

I have used lxml to parse and iterate over the XML file then extracted the 'data' in each 'cell' appending it to a list, and then appending that list to a parent list (giving me a nested list of each row) using the code:

from lxml import etree, objectify
import pandas as pd

with open('Python/cortex.xml') as infile:
    xml_file = infile.read()

    root = objectify.fromstring(xml_file)

    header = []
    data = []

    for row in root.Worksheet.Table.getchildren():
        temp_row = []
        if not row.tag == '{urn:schemas-microsoft-com:office:spreadsheet}Column':
            for cell in row.getchildren():
                temp_row.append(cell.Data)
            data.append(temp_row)
    header = data.pop(0) #remove the first 'row' and store in header list
    del data[0] #remove 2nd line of superfluous data

The first row gives the headers, hence I pop that into its own list, and row 2 contains the units for each variable, so I just get rid of that. All working well so far (or so it seemed)...

Now I need to get it into a pd dataframe to start working with it. If I go df = pd.DataFrame(data, columns=header) and I print(df) i get: ValueError: Buffer has wrong number of dimensions (expected 1, got 32)

Ok not sure what happened there... If I make the df without assigning the header and print that I get:

              0           1       2                        3   \
0  [[[0:00:06]]]  [[[Rest]]]  [[[]]]  [[[0.279724135654545]]]   
1  [[[0:00:09]]]  [[[Rest]]]  [[[]]]  [[[0.465136232899829]]]   
2  [[[0:00:13]]]  [[[Rest]]]  [[[]]]  [[[0.357975433456662]]]   
3  [[[0:00:19]]]  [[[Rest]]]  [[[]]]  [[[0.543332419057909]]]   
4  [[[0:00:24]]]  [[[Rest]]]  [[[]]]  [[[0.374604578743889]]]

That doesn't look right! Where did all these lists in lists in lists come from! If I iterate over and print the nested list data, it prints perfectly, but once I try to convert it to a df something goes wrong.

Can anyone enlighten me as to what has happened and how I can get the data into the pd df? If there is a better method than how I've done it, then I am happy to give it a go.

jezrael · Accepted Answer · 2017-09-24 07:18:40Z

4

You can create list of lists and then DataFrame by constructor. For parsing is used this solution:

from lxml import etree

with (open('test.xml','r')) as f:
    doc = etree.parse(f)

namespaces={'o':'urn:schemas-microsoft-com:office:office',
            'x':'urn:schemas-microsoft-com:office:excel',
            'ss':'urn:schemas-microsoft-com:office:spreadsheet'}

L = []
ws = doc.xpath('/ss:Workbook/ss:Worksheet', namespaces=namespaces)
if len(ws) > 0: 
    tables = ws[0].xpath('./ss:Table', namespaces=namespaces)
    if len(tables) > 0: 
        rows = tables[0].xpath('./ss:Row', namespaces=namespaces)
        for row in rows:
            tmp = []
            cells = row.xpath('./ss:Cell/ss:Data', namespaces=namespaces)
            for cell in cells:
#                print(cell.text);
                tmp.append(cell.text)
            L.append(tmp)
print (L)

[['t', 'Phase', 'Marker', "V'O2", "V'O2/kg", "V'O2/HR", 'HR', 'WR', 
  "V'E/V'O2", "V'E/V'CO2", 'RER', "V'E", 'BF'], 
 ['h:mm:ss', None, None, 'L/min', 'ml/min/kg', 'ml', 
 '/min', 'W', None, None, None, 'L/min', '/min'], 
 ['0:00:06', 'Rest', None, '0.27972413565454501', '4.3706896196022598',
  '4.5856415681072953', '61', '0', '27.002532271037801', '26.4113108545688', 
  '1.0223851598932201', '10.155340000000001', '18.07']]

df = pd.DataFrame(L[2:], columns=L[0])
print (df)
         t Phase Marker                 V'O2             V'O2/kg  \
0  0:00:06  Rest   None  0.27972413565454501  4.3706896196022598   

              V'O2/HR  HR WR            V'E/V'O2         V'E/V'CO2  \
0  4.5856415681072953  61  0  27.002532271037801  26.4113108545688   

                  RER                 V'E     BF  
0  1.0223851598932201  10.155340000000001  18.07

answered Sep 24, 2017 at 7:18

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

4 Comments

Braden Over a year ago

Works perfectly, thanks! I assume if there was more than 1 <Table> in the XML file that I would need to amend rows = tables[0].xpath to correspond to the index of the <Table> I want?

jezrael Over a year ago

Give me a some time. But for is necessary.

Braden Over a year ago

Sorry - Just realised the larger file doesn't have multiple <Table> tags, but extra <Row> tags after table BUT before the <Row> tag where the data table starts. How would I get it to ignore the rows before the <Row> I am after? If it help the full XML is here link The row I want to start from is row 641 in the XML

jezrael Over a year ago

Sorry, I have to go away now. So better is post another question with your another problem - parsing xml is not so easy and better is for each problem separate answer for future reference. thank you.

Collectives™ on Stack Overflow

Trouble parsing XML and getting data into pandas dataframe

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related