如何将指定XML响应解析为Pandas DataFrame与元数据字典

问题描述 #

我获取到一个结构规范的XML文件，内容如下：

<?xml version="1.0" encoding="UTF-8"?>
<Response>
  <Record key="XXXXX" req_sym="AAPL-US">
    <Fields>
      <Field id="7000" name="HEADLINE" value="THIS IS THE FIRST STORY" />
      <Field id="7001" name="SOURCE" value="EDG" />
      <Field id="7004" name="STORY_DATE" value="20231010" />
    </Fields>
  </Record>
  <Record key="YYYYY" req_sym="AI-US">
    <Fields>
      <Field id="7000" name="HEADLINE" value="THIS IS THE SECOND STORY" />
      <Field id="7001" name="SOURCE" value="EDG" />
      <Field id="7004" name="STORY_DATE" value="20231010" />
    </Fields>
  </Record>
  <Record key="ZZZZZ" req_sym="MSFT-US">
    <Fields>
      <Field id="7000" name="HEADLINE" value="THIS IS THE THIRD STORY" />
      <Field id="7001" name="SOURCE" value="EDG" />
      <Field id="7004" name="STORY_DATE" value="20231010" />
    </Fields>
  </Record>
  <Request>
      <RequestedReports>'search'</RequestedReports>
      <SearchGUID>'D207EF2FB41023D5'</SearchGUID>
      <RequestedStartDate>'20231010'</RequestedStartDate>
      <RequestedEndDate>'20231016'</RequestedEndDate>
  </Request>
  <Error description="" code="200"/>
</Response>

需要将该XML解析为两种格式：

1. Pandas DataFrame：每个<Record>对应一行，列名为HEADLINE、SOURCE、STORY_DATE，以Record的key为索引；
2. 字典：存储<Request>标签内的元数据。

解决方案 #

1. 解析为Pandas DataFrame #

使用Python标准库xml.etree.ElementTree解析XML，再将数据整理为DataFrame：

import xml.etree.ElementTree as ET
import pandas as pd

# 解析XML（如果是字符串内容，替换为ET.fromstring(xml_content)）
tree = ET.parse('response.xml')
root = tree.getroot()

# 提取所有Record的有效数据
records_list = []
for record in root.findall('Record'):
    record_dict = {
        'key': record.get('key'),
        'req_sym': record.get('req_sym')
    }
    # 遍历当前Record下的所有Field，只保留指定列
    for field in record.find('Fields').findall('Field'):
        field_name = field.get('name')
        if field_name in ['HEADLINE', 'SOURCE', 'STORY_DATE']:
            record_dict[field_name] = field.get('value')
    records_list.append(record_dict)

# 生成DataFrame并设置key为索引
df = pd.DataFrame(records_list).set_index('key')
print(df)

执行后得到的DataFrame结构示例：

req_sym                  HEADLINE SOURCE STORY_DATE
key                                                          
XXXXX  AAPL-US  THIS IS THE FIRST STORY    EDG    20231010
YYYYY    AI-US THIS IS THE SECOND STORY    EDG    20231010
ZZZZZ  MSFT-US  THIS IS THE THIRD STORY    EDG    20231010

2. 解析Request为字典 #

提取<Request>节点内容并整理为字典，同时去除文本首尾的单引号：

# 定位Request节点
request_node = root.find('Request')
# 生成元数字典
request_metadata = {child.tag: child.text.strip("'") for child in request_node}
print(request_metadata)

执行后得到的字典：

{
    'RequestedReports': 'search',
    'SearchGUID': 'D207EF2FB41023D5',
    'RequestedStartDate': '20231010',
    'RequestedEndDate': '20231016'
}

内容的提问来源于stack exchange，提问作者data-monkey