如何将给定类表格XML代码转换为Python pandas DataFrame
问题说明
需要提取类SpreadsheetML格式的XML片段中的单元格内容,生成可直接用于数据分析的结构化pandas DataFrame对象,输入的原始XML片段为转义后的截断内容,示例结构如下:
输入原始XML片段:
/Data></Cell></ss:Row><ss:Row><Cell ss:Index="1" ss:MergeDown="0" ss:StyleID="FilaParCentrada"><Data ss:Type="String">26-08-2020</Data></Cell><Cell ss:Index="2" ss:MergeDown="0" ss:StyleID="FilaParCentrada"><Data ss:Type="String">11-12</Data></Cell><Cell ss:Index="3" ss:StyleID="FilaPar0Decimal"><Data ss:Type="String">-</Data></Cell><Cell ss:Index="4" ss:StyleID="FilaPar1Decimal"><Data ss:Type="Number">60.4</Data></Cell><Cell ss:Index="5" ss:StyleID="FilaPar0Decimal"><Data ss:Type="String">-</Data></Cell><Cell ss:Index="6" ss:StyleID="FilaPar1Decimal"><Data ss:Type="Number">650.3</Data></Cell>
实现逻辑
- 先对转义的XML实体做解码,补全缺失的命名空间与根节点,让XML成为可解析的合法结构
- 遍历所有行节点(
ss:Row),逐行读取单元格的列索引(ss:Index属性)、单元格内的文本值(ss:Data节点的文本内容),避免列位置错位 - 按列顺序把单元格值填充为二维列表,缺失列位补空值
- 将二维列表传入pandas生成DataFrame,可根据
ss:Data节点的Type属性自动转换对应字段类型(数值类型转float/int,文本类型保留字符串)
可运行代码
import pandas as pd import xml.etree.ElementTree as ET from html import unescape # 原始转义XML文本 raw_xml = "/Data></Cell></ss:Row><ss:Row><Cell ss:Index="1" ss:MergeDown="0" ss:StyleID="FilaParCentrada"><Data ss:Type="String">26-08-2020</Data></Cell><Cell ss:Index="2" ss:MergeDown="0" ss:StyleID="FilaParCentrada"><Data ss:Type="String">11-12</Data></Cell><Cell ss:Index="3" ss:StyleID="FilaPar0Decimal"><Data ss:Type="String">-</Data></Cell><Cell ss:Index="4" ss:StyleID="FilaPar1Decimal"><Data ss:Type="Number">60.4</Data></Cell><Cell ss:Index="5" ss:StyleID="FilaPar0Decimal"><Data ss:Type="String">-</Data></Cell><Cell ss:Index="6" ss:StyleID="FilaPar1Decimal"><Data ss:Type="Number">650.3</Data></Cell>" # 解码转义字符,补全带命名空间的XML根节点 decoded_xml = unescape(raw_xml) wrapped_xml = f'<ss:Table xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">{decoded_xml}</ss:Table>' root = ET.fromstring(wrapped_xml) ns = {"ss": "urn:schemas-microsoft-com:office:spreadsheet"} all_rows = [] # 逐行解析单元格 for row_node in root.findall("ss:Row", ns): row_cells = {} max_col_pos = 0 for cell_node in row_node.findall("ss:Cell", ns): # 读取列位置属性,无属性时默认从第1列开始 col_pos = int(cell_node.get(f"{{{ns['ss']}}}Index", 1)) data_node = cell_node.find("ss:Data", ns) cell_val = data_node.text if data_node is not None else "" row_cells[col_pos] = cell_val max_col_pos = max(max_col_pos, col_pos) # 按列顺序整理整行数据 sorted_row = [row_cells.get(col_idx, "") for col_idx in range(1, max_col_pos + 1)] all_rows.append(sorted_row) # 生成结构化DataFrame df = pd.DataFrame(all_rows)
注意:如果XML中存在合并单元格,可额外读取Cell节点的MergeDown、MergeAcross属性,实现合并值向下/向右填充;解析时必须指定SS SpreadsheetML对应的命名空间,否则会出现节点查找为空的问题
内容的提问来源于stack exchange,提问作者mxs
相关产品推荐
相关产品推荐

