python - 解析 XML，搜索目标起始 标记，并忽略其上方的所有 标记

Question

我正在将 XML 文件解析为 pandas 数据帧。使用下面的代码我可以成功获取所有内容，但是这使用了完整 XML 的编辑版本。完整的 XML 在主数据表之上有一堆摘要数据，请参阅完整的 XML here 。我需要开始提取的行位于 XML 中的第 641 行。

XML 示例:

<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<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">
<Styles> ### approx. 300 lines of styling ### </Styles>
<Worksheet ss:Name="MetasoftStudio">
  <Table>
    <Row/>
    <Row>
      <Cell ss:StyleID="HeadTableTitle" ss:MergeAcross="1"><Data ss:Type="String">CPET Results</Data></Cell>
    </Row>
    <Row/>
    <Row>
      <Cell ss:StyleID="HeadTableParameterName" ss:MergeAcross="1"><Data ss:Type="String">Operator</Data></Cell>
      <Cell ss:StyleID="HeadTableParameterValue" ss:MergeAcross="7"><Data ss:Type="String"></Data></Cell>
    </Row>
    <Row/>
    <Row/>
    <Row>
      <Cell ss:StyleID="HeadTableTitle" ss:MergeAcross="1"><Data ss:Type="String">Patient data</Data></Cell>
    </Row>
    <Row/>
    <Row>
      <Cell ss:StyleID="HeadTableTitle" ss:MergeAcross="1"><Data ss:Type="String">Administrative Data</Data></Cell>
    </Row>
    <Row>
      <Cell ss:StyleID="HeadTableParameterName" ss:MergeAcross="1"><Data ss:Type="String">ID</Data></Cell>
      <Cell ss:StyleID="HeadTableParameterValue" ss:MergeAcross="7"><Data ss:Type="String">B013</Data></Cell>
    </Row>
    <Row>
      <Cell ss:StyleID="HeadTableParameterName" ss:MergeAcross="1"><Data ss:Type="String">Title</Data></Cell>
      <Cell ss:StyleID="HeadTableParameterValue" ss:MergeAcross="7"><Data ss:Type="String"></Data></Cell>
    </Row>
    <Row>
      <Cell ss:StyleID="HeadTableParameterName" ss:MergeAcross="1"><Data ss:Type="String">Last Name</Data></Cell>
      <Cell ss:StyleID="HeadTableParameterValue" ss:MergeAcross="7"><Data ss:Type="String">Data</Data></Cell>
    </Row>

### Skipping few hundred lines ###

    <Row>
      <Cell ss:StyleID="SummaryTableHead"><Data ss:Type="String">Variable</Data></Cell>
      <Cell ss:StyleID="SummaryTableHead"><Data ss:Type="String">Unit</Data></Cell>
      <Cell ss:StyleID="SummaryTableHead"><Data ss:Type="String">Rest</Data></Cell>
      <Cell ss:StyleID="SummaryTableHead"><Data ss:Type="String">Warm Up</Data></Cell>
      <Cell ss:StyleID="SummaryTableHead"><Data ss:Type="String">AT</Data></Cell>
      <Cell ss:StyleID="SummaryTableHead"><Data ss:Type="String">AT % Max</Data></Cell>
      <Cell ss:StyleID="SummaryTableHead"><Data ss:Type="String">RCP</Data></Cell>
      <Cell ss:StyleID="SummaryTableHead"><Data ss:Type="String">RCP % Max</Data></Cell>
      <Cell ss:StyleID="SummaryTableHead"><Data ss:Type="String">V'O2peak</Data></Cell>
      <Cell ss:StyleID="SummaryTableHead"><Data ss:Type="String">Absolute Maximum Values</Data></Cell>
    </Row>
    <Row>
      <Cell ss:StyleID="SummaryTableParameters"><Data ss:Type="String">V'O2</Data></Cell>
      <Cell ss:StyleID="SummaryTableUnits"><Data ss:Type="String">L/min</Data></Cell>
      <Cell ss:StyleID="SummaryTableValues"><Data ss:Type="String">0.34</Data></Cell>
      <Cell ss:StyleID="SummaryTableValues"><Data ss:Type="String">-</Data></Cell>
      <Cell ss:StyleID="SummaryTableValues"><Data ss:Type="String">1.83</Data></Cell>
      <Cell ss:StyleID="SummaryTableValues"><Data ss:Type="String">76</Data></Cell>
      <Cell ss:StyleID="SummaryTableValues"><Data ss:Type="String">2.28</Data></Cell>
      <Cell ss:StyleID="SummaryTableValues"><Data ss:Type="String">94</Data></Cell>
      <Cell ss:StyleID="SummaryTableValues"><Data ss:Type="String">2.42</Data></Cell>
      <Cell ss:StyleID="SummaryTableValues"><Data ss:Type="String">2.59</Data></Cell>
    </Row>
### Skipping some more lines ###
    <Row>
      <Cell ss:StyleID="SummaryTableParameters"><Data ss:Type="String">Borg</Data></Cell>
      <Cell ss:StyleID="SummaryTableUnits"><Data ss:Type="String"></Data></Cell>
      <Cell ss:StyleID="SummaryTableValues"><Data ss:Type="String">-</Data></Cell>
      <Cell ss:StyleID="SummaryTableValues"><Data ss:Type="String">-</Data></Cell>
      <Cell ss:StyleID="SummaryTableValues"><Data ss:Type="String">-</Data></Cell>
      <Cell ss:StyleID="SummaryTableValues"><Data ss:Type="String">-</Data></Cell>
      <Cell ss:StyleID="SummaryTableValues"><Data ss:Type="String">-</Data></Cell>
      <Cell ss:StyleID="SummaryTableValues"><Data ss:Type="String">-</Data></Cell>
      <Cell ss:StyleID="SummaryTableValues"><Data ss:Type="String">-</Data></Cell>
      <Cell ss:StyleID="SummaryTableValues"><Data ss:Type="String">-</Data></Cell>
    </Row>
    <Row/>
    <Row/>
###### NEED TO START EXTRACTING FROM THIS ROW ######
    <Row>
      <Cell ss:StyleID="MeasurementDataTableHead"><Data ss:Type="String">t</Data></Cell>
      <Cell ss:StyleID="MeasurementDataTableHead"><Data ss:Type="String">Phase</Data></Cell>
      <Cell ss:StyleID="MeasurementDataTableHead"><Data ss:Type="String">Marker</Data></Cell>
      <Cell ss:StyleID="MeasurementDataTableHead"><Data ss:Type="String">V'O2</Data></Cell>
      <Cell ss:StyleID="MeasurementDataTableHead"><Data ss:Type="String">V'CO2</Data></Cell>
      <Cell ss:StyleID="MeasurementDataTableHead"><Data ss:Type="String">V'E</Data></Cell>
      <Cell ss:StyleID="MeasurementDataTableHead"><Data ss:Type="String">V'E/V'O2</Data></Cell>
      <Cell ss:StyleID="MeasurementDataTableHead"><Data ss:Type="String">V'E/V'CO2</Data></Cell>
      <Cell ss:StyleID="MeasurementDataTableHead"><Data ss:Type="String">HR</Data></Cell>
      <Cell ss:StyleID="MeasurementDataTableHead"><Data ss:Type="String">RER</Data></Cell>
      <Cell ss:StyleID="MeasurementDataTableHead"><Data ss:Type="String">V'O2/kg</Data></Cell>
      <Cell ss:StyleID="MeasurementDataTableHead"><Data ss:Type="String">PetO2</Data></Cell>
      <Cell ss:StyleID="MeasurementDataTableHead"><Data ss:Type="String">PetCO2</Data></Cell>
      <Cell ss:StyleID="MeasurementDataTableHead"><Data ss:Type="String">ExCO2</Data></Cell>
      <Cell ss:StyleID="MeasurementDataTableHead"><Data ss:Type="String">BF</Data></Cell>
      <Cell ss:StyleID="MeasurementDataTableHead"><Data ss:Type="String">WR</Data></Cell>
      <Cell ss:StyleID="MeasurementDataTableHead"><Data ss:Type="String">Borg</Data></Cell>
    </Row>
### XML File continues the same from here with the same structure ###

我当前的代码:

from lxml import etree
import pandas as pd

with open('cortex.xml', 'r') as infile:
    root = etree.parse(infile)

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

data = []

ws = root.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:
            temp = []
            cells = row.xpath('./ss:Cell/ss:Data', namespaces=namespaces)
            for cell in cells:
                temp.append(cell.text)
            data.append(temp)

df = pd.DataFrame(data[2:], columns=data[0])

我需要如何编辑当前代码才能仅提取与 <Row> 相对应的数据主数据表中的标签？顶部的摘要数据量可能会有所不同，因此不能选择对起点进行硬编码。

<Cell>在第一<Row>主要数据的属性有 StyleID="MeasurementDataTableHead" ，是否可以基于此进行搜索并从那里开始提取？我完全被这个问题难住了。

Answer 1

如果解析表始终是最后一个，您可以创建辅助 bool 值，如果值 t (解析表的第一个值)设置为 True 并附加它:

from lxml import etree
import pandas as pd

with open('cortex_full.xml', 'r') as infile:
    root = etree.parse(infile)

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

data = []
parse = False
ws = root.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:
            temp = []
            cells = row.xpath('./ss:Cell/ss:Data', namespaces=namespaces)
            for cell in cells:
                if cell.text == 't':
                    parse = True
                if parse:    
                    temp.append(cell.text)
            if parse:  
                data.append(temp)

<小时/>

df = pd.DataFrame(data[2:], columns=data[0])
print (df.head())

         t Phase Marker               V'O2              V'CO2  \
0  0:00:10  Rest   None  0.251524741119069  0.236014510176694   
1  0:00:20  Rest   None  0.434384403499744  0.411231246288168   
2  0:00:30  Rest   None  0.343945761508378  0.341020990667946   
3  0:00:40  Rest   None  0.220729503668137  0.196421701276079   
4  0:00:50  Rest   None  0.257787497144625  0.217026849651075   

                V'E          V'E/V'O2         V'E/V'CO2 HR                RER  \
0          11.89836  36.3356700392092  38.7235513323219  0  0.938335167851211   
1          15.59965  31.7517155056147  33.5394017952007  0  0.946698921450597   
2  14.8086111111111  36.8447951082032  37.1607949595411  0  0.991496418424796   
3  9.04168333333334  29.5090743425324  33.1609149651866  0  0.889875154938032   
4  9.99203333333333  28.3149237809555  33.6328585383268  0  0.841882760238438   

            V'O2/kg             PetO2            PetCO2                 ExCO2  \
0  4.42046996694321  115.479326671601  28.7635623488774   -0.0145537951547244   
1  7.63417229349286  111.689054293116  33.3331430668641   -0.0219190689603747   
2  6.04474097554267  117.675559264742  29.9207171560161  -0.00289989981300173   
3  3.87925314003756  115.526557480056  28.6691007319681   -0.0216309094198363   
4  4.53053597793717  109.339321572498  32.0697189407024   -0.0343156864209755   

                 BF WR  Borg  
0             19.16  0  None  
1             12.55  0  None  
2  14.8333333333333  0  None  
3  17.5566666666667  0  None  
4              18.7  0  None