将没有标记的XML API响应转换为Pandas Dataframe

Question

我对XML解析非常陌生，我需要帮助将XML API响应转换为pandas数据框。

下面是XML响应:

<?xml version="1.0" encoding="UTF-8"?>
<response success="true">
<output><![CDATA[Account Name,Account Code,Level Name,"Item","Customer",Wk1Y23,Wk2Y23
"SVF - Customer Shipping Default","SVF_Shipping_Default.SVF_CSD","Los Fresnos","1.5 G PANSY Matrix Pltr Clear Mix ~ LW85201","Lowes # 90 ~ LW00090",0.0,2.0
"SVF - Customer Shipping Default","SVF_Shipping_Default.SVF_CSD","Los Fresnos","1.5 G PANSY Matrix Pltr Clear Mix ~ LW85201","Lowes # 63 ~ LW00063",0.0,2.0
"SVF - Customer Shipping Default","SVF_Shipping_Default.SVF_CSD","Los Fresnos","1.5 G PANSY Matrix Pltr Clear Mix ~ LW85201","Lowes # 271 ~ LW00271",0.0,4.0]]></output>
</response>

如您所见，这个XML中没有很多标记。我尝试使用xml.etree. elementtree来解析xml到数据框架，但我没有得到任何数据到数据框架:

etree = ET.parse(xml_data)
df = pd.DataFrame(list(iter_docs(etree.getroot())))
print(df)

最好的方法是什么?

Answer 1

考虑解析<output>节点的文本，并使用StringIO将内容传递给read_csv调用:

from io import StringIO
import xml.etree.ElementTree as ET
import pandas as pd
...
output_text = ET.parse(xml_data).find("output").text
with StringIO(output_text) as csv_data:
df = pd.read_csv(csv_data)
print(df)
Account Name                  Account Code   Level Name  
0  SVF - Customer Shipping Default  SVF_Shipping_Default.SVF_CSD  Los Fresnos   
1  SVF - Customer Shipping Default  SVF_Shipping_Default.SVF_CSD  Los Fresnos   
2  SVF - Customer Shipping Default  SVF_Shipping_Default.SVF_CSD  Los Fresnos   
Item               Customer  Wk1Y23  
0  1.5 G PANSY Matrix Pltr Clear Mix ~ LW85201   Lowes # 90 ~ LW00090     0.0   
1  1.5 G PANSY Matrix Pltr Clear Mix ~ LW85201   Lowes # 63 ~ LW00063     0.0   
2  1.5 G PANSY Matrix Pltr Clear Mix ~ LW85201  Lowes # 271 ~ LW00271     0.0   
Wk2Y23  
0     2.0  
1     2.0  
2     4.0