如何将嵌套XML数据解析为Pandas DataFrame？

问题描述 #

需要将包含多层嵌套（如DOB节点）和重复节点（如多个SCORE节点）的XML数据，转换为适合数据分析的Pandas DataFrame，常规浅层级解析方法无法处理这类复杂结构。

源XML数据 #

<?xml version='1.0' encoding='UTF-8'?>
<CONSOLIDATED_LIST>
    <INDIVIDUALS>
        <INDIVIDUAL>
            <STUDENT_ID>1</STUDENT_ID>
            <FIRST_NAME>JAMES</FIRST_NAME>
            <SECOND_NAME>PETER</SECOND_NAME>
            <THIRD_NAME>SOMMER</THIRD_NAME>
            <DOB>
                <YEAR>2000</YEAR>
                <MONTH>JUNE</MONTH>
                <DATE>30</DATE>
            </DOB>
            <SCORE>
                <MATH>100</MATH>
                <PHYSICS>90</PHYSICS>
            </SCORE>
            <SCORE>
                <BIOLOGY>100</BIOLOGY>
            </SCORE>
            <SCORE>
                <Economic>100</Economic>
            </SCORE>
        </INDIVIDUAL>
        <INDIVIDUAL>
            <STUDENT_ID>2</STUDENT_ID>
            <FIRST_NAME>RICKY</FIRST_NAME>
            <SECOND_NAME>HUTTON</SECOND_NAME>
            <THIRD_NAME>LEE</THIRD_NAME>
            <DOB>
                <YEAR>2001</YEAR>
                <MONTH>MARCH</MONTH>
                <DATE>29</DATE>
            </DOB>
            <SCORE>
                <MATH>90</MATH>
                <PHYSICS>70</PHYSICS>
                <ENGLISH>70</ENGLISH>
            </SCORE>
            <SCORE>
                <Economic>100</Economic>
            </SCORE>
        </INDIVIDUAL>
    </INDIVIDUALS>
    <GROUPS>
        <GROUP>
            <GROUP_ID>1</GROUP_ID>
            <TEAM_LEADER>JAMES</TEAM_LEADER>
            <TEAM_MEMBER>PETER</TEAM_MEMBER>
        </GROUP>
    </GROUPS>
</CONSOLIDATED_LIST>

解决方案 #

使用Python的xml.etree.ElementTree遍历XML结构，针对嵌套节点和重复节点做针对性处理，最终转换为DataFrame：

代码实现

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

# 加载并解析XML（若为字符串则用ET.fromstring(xml_str)）
tree = ET.parse('student_data.xml')  # 替换为你的XML文件路径
root = tree.getroot()

student_records = []

# 遍历所有学生节点
for individual in root.findall('./INDIVIDUALS/INDIVIDUAL'):
    record = {}
    
    # 提取基础字段
    record['STUDENT_ID'] = individual.find('STUDENT_ID').text
    record['FIRST_NAME'] = individual.find('FIRST_NAME').text
    record['SECOND_NAME'] = individual.find('SECOND_NAME').text
    record['THIRD_NAME'] = individual.find('THIRD_NAME').text
    
    # 处理DOB嵌套节点，拆分为独立列
    dob_node = individual.find('DOB')
    record['DOB_YEAR'] = dob_node.find('YEAR').text
    record['DOB_MONTH'] = dob_node.find('MONTH').text
    record['DOB_DATE'] = dob_node.find('DATE').text
    
    # 合并多个SCORE节点的科目分数
    score_dict = {}
    for score_node in individual.findall('SCORE'):
        for subject in score_node:
            score_dict[subject.tag] = subject.text
    record.update(score_dict)
    
    student_records.append(record)

# 转换为DataFrame并调整列顺序
df = pd.DataFrame(student_records)
df = df[['STUDENT_ID', 'FIRST_NAME', 'SECOND_NAME', 'THIRD_NAME', 
         'DOB_YEAR', 'DOB_MONTH', 'DOB_DATE', 
         'MATH', 'PHYSICS', 'BIOLOGY', 'Economic', 'ENGLISH']]

print(df)

输出结果

STUDENT_ID FIRST_NAME SECOND_NAME THIRD_NAME DOB_YEAR DOB_MONTH DOB_DATE MATH PHYSICS BIOLOGY Economic ENGLISH
0          1       JAMES        PETER     SOMMER     2000       JUNE       30  100      90     100      100     NaN
1          2       RICKY       HUTTON        LEE     2001      MARCH       29   90      70      NaN      100      70

说明 #

• DOB节点：将子节点拆分为独立列，便于后续日期格式转换或分析
• 多SCORE节点：遍历所有SCORE节点，合并所有科目-分数对，缺失科目自动填充NaN
• 若需处理GROUPS部分，可采用相同逻辑单独解析，再根据需求合并为一个DataFrame或作为独立数据集使用

内容的提问来源于stack exchange，提问作者justnewbie89