如何通过FOR LOOP将关联的Pandas DataFrame转换为指定结构XML？

需求说明 #

现有三个通过adNo关联的DataFrame：admission、diagnosis、dailyIntervention，需要为每个adNo生成包含其所有关联记录的XML文档，结构需匹配指定格式，同时要将日期字段转换为YYYY-MM-DDTHH:MM:SS格式。

实现步骤与代码 #

1. 数据预处理（日期格式转换）

先将CSV读取的日期字符串转换为标准ISO格式，确保XML中的日期符合要求：

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

# 读取CSV文件（替换为实际路径）
admission = pd.read_csv(r'your_path\admission.csv')
diagnosis = pd.read_csv(r'your_path\diagnosis.csv')
dailyIntervention = pd.read_csv(r'your_path\dailyIntervention.csv')

# 转换日期格式：DD/MM/YYYY → YYYY-MM-DDTHH:MM:SS
def convert_date(date_str):
    try:
        dt = datetime.strptime(date_str, '%d/%m/%Y')
        return dt.strftime('%Y-%m-%dT00:00:00')
    except ValueError:
        return date_str  # 处理异常日期

admission['D.O.B'] = admission['D.O.B'].apply(convert_date)
dailyIntervention['activitydate'] = dailyIntervention['activitydate'].apply(convert_date)

2. 构建XML文档

遍历每个adNo，收集对应记录并生成XML结构：

# 创建根节点
root = ET.Element('dataroot', xmlns:od="urn:schemas-microsoft-com:officedata", generated=datetime.now().strftime('%Y-%m-%dT%H:%M:%S'))

# 遍历每个admission记录
for _, adm_row in admission.iterrows():
    ad_no = adm_row['adNo']
    
    # 创建EpisodeDetails节点
    episode = ET.SubElement(root, 'EpisodeDetails')
    
    # 添加Admission子节点
    adm_elem = ET.SubElement(episode, 'Admission')
    for col in admission.columns:
        child = ET.SubElement(adm_elem, col)
        child.text = str(adm_row[col])
    
    # 添加对应diagnosis记录
    diag_records = diagnosis[diagnosis['adNo'] == ad_no]
    for _, diag_row in diag_records.iterrows():
        diag_elem = ET.SubElement(episode, 'diagnosis')
        # 匹配期望XML中的dascID标签（对应原表descID）
        diag_elem.append(ET.Element('dascID'))
        diag_elem.find('dascID').text = str(diag_row['descID'])
        # 添加其他字段
        for col in ['description', 'readCode', 'adNo']:
            child = ET.SubElement(diag_elem, col)
            child.text = str(diag_row[col])
    
    # 添加对应dailyIntervention记录
    int_records = dailyIntervention[dailyIntervention['adNo'] == ad_no]
    for _, int_row in int_records.iterrows():
        int_elem = ET.SubElement(episode, 'dailyIntervention')
        for col in dailyIntervention.columns:
            child = ET.SubElement(int_elem, col)
            child.text = str(int_row[col])

# 美化XML输出（可选，提升可读性）
import xml.dom.minidom as minidom
rough_string = ET.tostring(root, 'utf-8')
reparsed = minidom.parseString(rough_string)
pretty_xml = reparsed.toprettyxml(indent="    ")

# 写入XML文件
with open('output.xml', 'w', encoding='utf-8') as f:
    f.write(pretty_xml)

代码说明 #

• 日期转换函数convert_date处理DD/MM/YYYY格式的字符串，转换为XML要求的ISO日期格式；
• 遍历每个admission记录，为每个adNo创建<EpisodeDetails>节点；
• 依次添加<Admission>、<diagnosis>、<dailyIntervention>子节点，注意原表descID对应XML中的dascID标签；
• 使用xml.dom.minidom美化XML输出，确保结构清晰；
• 最终将生成的XML写入output.xml文件。

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