Pandas如何解析DataFrame的XML列并将嵌套值拆分为独立行
2026-9-28
问题背景
我有一个如下结构的DataFrame（以下为1行示例数据）：
Title Date Supervisor Nightshift XML NaN 27/10/2021 Michael Myres No <?xml version="1.0" encoding="utf-8"?><Repeate...
该DataFrame有一列存储了嵌套XML结构，具体内容如下：
<?xml version="1.0" encoding="utf-8"?> <RepeaterData> <Version /> <Items> <Item> <TestReference type="System.String">**1203**</TestReference> <Discipline type="System.String">**Bodyshop**</Discipline> <WON type="System.String">**1234**</WON> <PurchaseOrder type="System.String">**1234**</PurchaseOrder> <LabourType type="System.String">**On Tools**</LabourType> <WorkType type="System.String">**Turnaround**</WorkType> <TestEngineer type="System.String">**Me**</TestEngineer> <_x0034_4508821-eb2d-45be-b408-5db4df886890 type="System.String">&lt;?xml version=&quot;1.0&quot; encoding=&quot;utf-8&quot;?&gt;&lt;RepeaterData&gt;&lt;Version /&gt;&lt;Items&gt;&lt;Item&gt;&lt;control_EmployeeName type=&quot;System.String&quot;&gt;**Elliot**&lt;/control_EmployeeName&gt;&lt;control_NHours type=&quot;System.Int32&quot;&gt;**1**&lt;/control_NHours&gt;&lt;control_PHours type=&quot;System.Int32&quot;&gt;**2**&lt;/control_PHours&gt;&lt;control_DHours type=&quot;System.Int32&quot;&gt;**3**&lt;/control_DHours&gt;&lt;/Item&gt;&lt;Item&gt;&lt;control_EmployeeName type=&quot;System.String&quot;&gt;**Ryan** &lt;/control_EmployeeName&gt;&lt;control_NHours type=&quot;System.Int32&quot;&gt;**1**&lt;/control_NHours&gt;&lt;control_PHours type=&quot;System.Int32&quot;&gt;**2**&lt;/control_PHours&gt;&lt;control_DHours type=&quot;System.Int32&quot;&gt;**4**&lt;/control_DHours&gt;&lt;/Item&gt;&lt;Item&gt;&lt;control_EmployeeName type=&quot;System.String&quot;&gt;**Elliot**&lt;/control_EmployeeName&gt;&lt;control_NHours type=&quot;System.Int32&quot;&gt;**1**&lt;/control_NHours&gt;&lt;control_PHours type=&quot;System.Int32&quot;&gt;**1**&lt;/control_PHours&gt;&lt;control_DHours type=&quot;System.Int32&quot;&gt;1&lt;/control_DHours&gt;&lt;/Item&gt;&lt;/Items&gt;&lt;/RepeaterData&gt;</_x0034_4508821-eb2d-45be-b408-5db4df886890> <f91faab4-2d9d-4c1f-a40c-63c6a3d71572 type="System.String">&lt;?xml version=&quot;1.0&quot; encoding=&quot;utf-8&quot;?&gt;&lt;RepeaterData&gt;&lt;Version /&gt;&lt;Items&gt;&lt;Item&gt;&lt;NonProductiveTime type=&quot;System.String&quot;&gt;Additional duties&lt;/NonProductiveTime&gt;&lt;NonProductiveTimeMinutes type=&quot;System.Double&quot;&gt;**15**&lt;/NonProductiveTimeMinutes&gt;&lt;/Item&gt;&lt;/Items&gt;&lt;/RepeaterData&gt;</f91faab4-2d9d-4c1f-a40c-63c6a3d71572> </Item> </Items> </RepeaterData>
我希望解析该XML列，提取其中带**标记的数值，拆分生成多条独立行，预期输出结果如下：
Date Supervisor Nightshift TestReference Discipline WON PurchaseOrder LabourType WorkType TestEngineer EmployeeName Nhours Phours Dhours 0 2021-10-27 Michael Myres No 1203 Bodyshop 1234 1234 On Tools Turnaround Me Elliot 1 2 3 1 2021-10-27 Michael Myres No 1203 Bodyshop 1234 1234 On Tools Turnaround Me Ryan 1 2 4 2 2021-10-27 Michael Myres No 1203 Bodyshop 1234 1234 On Tools Turnaround Me John 1 1 1
我尝试了如下代码，但最终输出的全是Version、Items列且值均为None，没有得到预期结果：
import pandas as pd import xml.etree.ElementTree as ET df = pd.read_csv('SharePointDataPull.csv') xml_data = df['XML'][0] # test row root = ET.XML(xml_data) # Parse XML for i, child in enumerate(root): data.append([subchild.text for subchild in child]) cols.append(child.tag) df1 = pd.DataFrame(data).T # Write in DF and transpose it df1.columns = cols # Update column names df1
解决方案
原有代码的问题在于没有处理XML的嵌套结构、内层XML的转义字符，也没有针对性提取需要的字段。调整后的代码如下：
import pandas as pd import xml.etree.ElementTree as ET import html # 辅助函数：提取节点文本，去掉前后的**和空格 def clean_text(text): if not text: return "" return text.strip().strip("*").strip() # 读取原始数据 df = pd.read_csv('SharePointDataPull.csv') result = [] # 遍历每一行原始数据 for _, row in df.iterrows(): # 提取基础字段 base_info = { "Date": pd.to_datetime(row["Date"], dayfirst=True).strftime("%Y-%m-%d"), "Supervisor": row["Supervisor"], "Nightshift": row["Nightshift"] } # 解析外层XML root = ET.XML(row["XML"]) outer_item = root.find("./Items/Item") # 提取外层XML字段 outer_info = { "TestReference": clean_text(outer_item.find("TestReference").text), "Discipline": clean_text(outer_item.find("Discipline").text), "WON": clean_text(outer_item.find("WON").text), "PurchaseOrder": clean_text(outer_item.find("PurchaseOrder").text), "LabourType": clean_text(outer_item.find("LabourType").text), "WorkType": clean_text(outer_item.find("WorkType").text), "TestEngineer": clean_text(outer_item.find("TestEngineer").text) } # 提取存储员工工时的内层XML（节点tag为示例中的固定guid，若有变动可调整匹配规则） inner_xml_node = outer_item.find("_x0034_4508821-eb2d-45be-b408-5db4df886890") # 解转义内层XML inner_xml = html.unescape(inner_xml_node.text) inner_root = ET.XML(inner_xml) # 遍历内层每个员工的工时记录 for inner_item in inner_root.findall("./Items/Item"): emp_info = { "EmployeeName": clean_text(inner_item.find("control_EmployeeName").text), "Nhours": clean_text(inner_item.find("control_NHours").text), "Phours": clean_text(inner_item.find("control_PHours").text), "Dhours": clean_text(inner_item.find("control_DHours").text) } # 拼接所有字段，加入结果列表 result.append({**base_info, **outer_info, **emp_info}) # 转成DataFrame result_df = pd.DataFrame(result) print(result_df)
如果内层存储工时的XML节点tag不固定，可以替换inner_xml_node的查找逻辑，比如遍历outer_item的所有子节点，判断text是否包含control_EmployeeName关键字来定位对应的节点。
内容的提问来源于stack exchange，提问作者DECROMAX
相关产品推荐
相关产品推荐
