将XML响应转换为datafram

Question

我们一直在尝试构造一个从XML到可读数据帧的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

Answer 1

从本质上讲，您没有考虑位于根标记中的XML中的名称空间，很可能是所有None结果的原因。考虑使用已定义的名称空间进行解析。因为一个是默认名称空间，所以给它任何前缀，比如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)

另外，避免在循环中调用append的quadratic copy。相反，构建一个字典列表以绑定到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