Skip to main content
 首页 » 编程设计

python之将 XML 响应转换为 Pandas 数据框

2024年11月24日7cmt

一直在尝试构建从 XML 到可读 Pandas 数据框的 API 响应。我在有关此主题的早期线程中找到了很多灵感,但我的数据框中的值仍然显示为“无”。

XML 响应:

<VehiclePositionResponse xmlns="http://fms-standard.com/rfms/v1.0.0/xsd/position" xmlns:ns2="http://fms-standard.com/rfms/v1.0.0/xsd/common/position"> 
    <VehiclePosition> 
        <VIN>YS2R8X40005440923</VIN> 
        <TriggerType>OTHER</TriggerType> 
        <CreatedDateTime>2019-07-31T16:50:28</CreatedDateTime> 
        <ReceivedDateTime>2019-07-31T16:50:29</ReceivedDateTime> 
        <GNSSPosition> 
            <ns2:Latitude>62.098339</ns2:Latitude> 
            <ns2:Longitude>10.542222</ns2:Longitude> 
            <ns2:Heading>291</ns2:Heading> 
            <ns2:Altitude>655</ns2:Altitude> 
            <ns2:Speed>0</ns2:Speed> 
            <ns2:PositionDateTime>2019-07-31T16:50:28</ns2:PositionDateTime> 
        </GNSSPosition> 
        <WheelBasedSpeed></WheelBasedSpeed> 
    </VehiclePosition> 
    <VehiclePosition> 
        <VIN>YS2R8X40005441367</VIN> 
        <TriggerType>OTHER</TriggerType> 
        <CreatedDateTime>2019-07-31T18:13:24</CreatedDateTime> 
        <ReceivedDateTime>2019-07-31T18:13:25</ReceivedDateTime> 
        <GNSSPosition> 
            <ns2:Latitude>62.127206</ns2:Latitude> 
            <ns2:Longitude>10.608676</ns2:Longitude> 
            <ns2:Heading>3</ns2:Heading> 

等等

代码:

headers={'Authorization':Token,'Content-Type':'application/xml'} 
r=requests.get(url, headers=headers) 
 
 
def getvalueofnode(node): 
    return node.text if node is not None else None 
 
def main(): 
   root = cET.fromstring(r.content) 
   dfcols = ['VIN', 'CreatedDateTime', 'ReceivedDateTime', 'Latitude', 'Longitude', 'Altitude'] 
   df_xml = pd.DataFrame(columns=dfcols) 
 
   for node in root: 
      VIN = node.find('VIN') 
      CreatedDateTime = node.find('CreatedDateTime') 
      ReceivedDateTime = node.find('ReceivedDateTime') 
      Latitude = node.find('Latitude') 
      Longitude = node.find('Longitude') 
      Altitude = node.find('Altitude') 
 
      df_xml = df_xml.append( 
         pd.Series([getvalueofnode(VIN), getvalueofnode(CreatedDateTime), getvalueofnode(ReceivedDateTime), getvalueofnode(Latitude), getvalueofnode(Longitude), getvalueofnode(Altitude)], index=dfcols), 
      ignore_index=True) 
 
   print(df_xml) 
 
main() 

我的回复是这样的: Current Dataframe

请您参考如下方法:

从本质上讲,您没有考虑位于根标记中的 XML 中的 namespace ,这可能是所有 None 结果的原因。考虑使用已定义的命名空间进行解析。因为 one 是默认命名空间,所以给它任何前缀,如 data 并用它解析:

ns = {"doc":"http://fms-standard.com/rfms/v1.0.0/xsd/position", 
      "ns2":"http://fms-standard.com/rfms/v1.0.0/xsd/common/position"} 
 
for node in root: 
   VIN = node.find("doc:VIN", ns) 
   CreatedDateTime = node.find('doc:CreatedDateTime', ns) 
   ReceivedDateTime = node.find('doc:ReceivedDateTime', ns) 
   Latitude = node.find('doc:GNSSPosition/ns2:Latitude', ns) 
   Longitude = node.find('doc:GNSSPosition/ns2:Longitude', ns) 
   Altitude = node.find('doc:GNSSPosition/ns2:Altitude', ns) 

另外,避免 quadratic copy在循环中调用 append。相反,构建一个字典列表以绑定(bind)到 DataFrame() 构造函数中。

def main2(): 
   root = cET.fromstring(r.content) 
 
   ns = {"doc":"http://fms-standard.com/rfms/v1.0.0/xsd/position", 
         "ns2":"http://fms-standard.com/rfms/v1.0.0/xsd/common/position"} 
 
   data_list = [{'VIN': getvalueofnode(node.find("doc:VIN", ns)), 
                 'CreatedDateTime': getvalueofnode(node.find('doc:CreatedDateTime', ns)), 
                 'ReceivedDateTime': getvalueofnode(node.find('doc:ReceivedDateTime', ns)), 
                 'Latitude': getvalueofnode(node.find('doc:GNSSPosition/ns2:Latitude', ns)), 
                 'Longitude': getvalueofnode(node.find('doc:GNSSPosition/ns2:Longitude', ns)), 
                 'Altitude': getvalueofnode(node.find('doc:GNSSPosition/ns2:Altitude', ns))} \ 
                      for node in root] 
 
   df_xml = pd.DataFrame(data_list) 

输出

print(df_xml) 
#   Altitude      CreatedDateTime   Latitude  Longitude     ReceivedDateTime                VIN 
# 0      655  2019-07-31T16:50:28  62.098339  10.542222  2019-07-31T16:50:29  YS2R8X40005440923 
# 1     None  2019-07-31T18:13:24  62.127206  10.608676  2019-07-31T18:13:25  YS2R8X40005441367