将XML解析为PandasDataFrame到CSV

Question

我尝试了多次代码迭代来完成这项工作，但似乎找不到一个好的解决方案。已经干了好几天了。在

以下是我从API返回的XML数据：

 <response status="success"><result>
  <job>
    <tenq>14:00:42</tenq>
    <tdeq>14:00:42</tdeq>
    <tlast>16:00:00</tlast>
    <status>FIN</status>
    <id>123</id>
    <cached-logs>123</cached-logs>
  </job>
  <log>
    <logs count="20" progress="100">
      <entry logid="123">
        <domain>1</domain>
        <receive_time>2017/11/25 12:12:02</receive_time>
        <serial>123</serial>
        <seqno>123</seqno>
        <actionflags>123</actionflags>
        <type>Example</type>
        <subtype>Example</subtype>
        <config_ver>0</config_ver>
        <time_generated>2017/11/25 12:12:00</time_generated>
        <src>192.168.1.1</src>
        <dst>192.168.1.1</dst>
        <natsrc>192.168.1.1</natsrc>
        <natdst>192.168.1.1</natdst>
        <rule>Example</rule>
        <srcloc code="10.0.0.0-10.255.255.255" cc="10.0.0.0-10.255.255.255">10.0.0.0-10.255.255.255</srcloc>
        <dstloc code="United States" cc="US">United States</dstloc>
        <app>Example</app>
        <vsys>Example</vsys>
        <from>Example</from>
        <to>Example</to>
        <inbound_if>Example</inbound_if>
        <outbound_if>Example</outbound_if>
        <logset>Example</logset>
        <time_received>Example</time_received>
        <sessionid>Example</sessionid>
        <repeatcnt>1</repeatcnt>
        <sport>123</sport>
        <dport>80</dport>
        <natsport>123</natsport>
        <natdport>80</natdport>
        <flags>123</flags>
        <flag-pcap>no</flag-pcap>
        <flag-flagged>no</flag-flagged>
        <flag-proxy>no</flag-proxy>
        <flag-url-denied>no</flag-url-denied>
        <flag-nat>yes</flag-nat>
        <captive-portal>no</captive-portal>
        <non-std-dport>no</non-std-dport>
        <transaction>no</transaction>
        <pbf-c2s>no</pbf-c2s>
        <pbf-s2c>no</pbf-s2c>
        <temporary-match>no</temporary-match>
        <sym-return>no</sym-return>
        <decrypt-mirror>no</decrypt-mirror>
        <proto>tcp</proto>
        <action>Example</action>
        <cpadding>0</cpadding>
        <dg_hier_level_1>0</dg_hier_level_1>
        <dg_hier_level_2>0</dg_hier_level_2>
        <dg_hier_level_3>0</dg_hier_level_3>
        <dg_hier_level_4>0</dg_hier_level_4>
        <vsys_name>Legacy</vsys_name>
        <device_name>Example</device_name>
        <vsys_id>123</vsys_id>
        <bytes>463</bytes>
        <bytes_sent>393</bytes_sent>
        <bytes_received>70</bytes_received>
        <packets>4</packets>
        <start>Example</start>
        <elapsed>0</elapsed>
        <category>Example</category>
        <padding>0</padding>
        <pkts_sent>3</pkts_sent>
        <pkts_received>1</pkts_received>
        <session_end_reason>Example</session_end_reason>
        <action_source>Example</action_source>
      </entry>
      <entry logid="456">
        <domain>1</domain>
        <receive_time>2017/11/25 12:12:02</receive_time>
        <serial>Example</serial>
        <seqno>Example</seqno>
        <actionflags>Example</actionflags>
        <type>Example</type>
        <subtype>Example</subtype>
        <config_ver>0</config_ver>
        <time_generated>2017/11/25 12:12:00</time_generated>
        <src>192.168.1.1</src>
        <dst>192.168.1.2</dst>
        <rule>Example</rule>
        <dstuser>Example</dstuser>
        <srcloc code="10.0.0.0-10.255.255.255" cc="10.0.0.0-10.255.255.255">10.0.0.0-10.255.255.255</srcloc>
        <dstloc code="10.0.0.0-10.255.255.255" cc="10.0.0.0-10.255.255.255">10.0.0.0-10.255.255.255</dstloc>
        <app>Example</app>
        <vsys>Example</vsys>
        <from>Example</from>
        <to>Example</to>
        <inbound_if>Example</inbound_if>
        <logset>Example</logset>
        <time_received>Example</time_received>
        <sessionid>0</sessionid>
        <repeatcnt>1</repeatcnt>
        <sport>123</sport>
        <dport>123</dport>
        <natsport>0</natsport>
        <natdport>0</natdport>
        <flags>123</flags>
        <flag-pcap>no</flag-pcap>
        <flag-flagged>no</flag-flagged>
        <flag-proxy>no</flag-proxy>
        <flag-url-denied>no</flag-url-denied>
        <flag-nat>no</flag-nat>
        <captive-portal>no</captive-portal>
        <non-std-dport>no</non-std-dport>
        <transaction>no</transaction>
        <pbf-c2s>no</pbf-c2s>
        <pbf-s2c>no</pbf-s2c>
        <temporary-match>no</temporary-match>
        <sym-return>no</sym-return>
        <decrypt-mirror>no</decrypt-mirror>
        <proto>tcp</proto>
        <action>Example</action>
        <cpadding>0</cpadding>
        <dg_hier_level_1>0</dg_hier_level_1>
        <dg_hier_level_2>0</dg_hier_level_2>
        <dg_hier_level_3>0</dg_hier_level_3>
        <dg_hier_level_4>0</dg_hier_level_4>
        <vsys_name>Example</vsys_name>
        <device_name>Example</device_name>
        <vsys_id>0</vsys_id>
        <bytes>70</bytes>
        <bytes_sent>70</bytes_sent>
        <bytes_received>0</bytes_received>
        <packets>1</packets>
        <start>Example</start>
        <elapsed>0</elapsed>
        <category>Example</category>
        <padding>0</padding>
        <pkts_sent>1</pkts_sent>
        <pkts_received>0</pkts_received>
        <session_end_reason>Example</session_end_reason>
        <action_source>Example</action_source>
      </entry>
      <entry logid="789">
        <domain>1</domain>
        <receive_time>2017/11/25 12:12:02</receive_time>
        <serial>Example</serial>
        <seqno>Example</seqno>
        <actionflags>Example</actionflags>
        <type>Example</type>
        <subtype>Example</subtype>
        <config_ver>0</config_ver>
        <time_generated>2017/11/25 12:12:00</time_generated>
        <src>192.168.1.1</src>
        <dst>192.168.1.2</dst>
        <rule>Example</rule>
        <srcuser>Example</srcuser>
        <dstuser>Example</dstuser>
        <srcloc code="10.0.0.0-10.255.255.255" cc="10.0.0.0-10.255.255.255">10.0.0.0-10.255.255.255</srcloc>
        <dstloc code="10.0.0.0-10.255.255.255" cc="10.0.0.0-10.255.255.255">10.0.0.0-10.255.255.255</dstloc>
        <app>Example</app>
        <vsys>Example</vsys>
        <from>Example</from>
        <to>Example</to>
        <inbound_if>Example</inbound_if>
        <outbound_if>Example</outbound_if>
        <logset>Example</logset>
        <time_received>Example</time_received>
        <sessionid>Example</sessionid>
        <repeatcnt>1</repeatcnt>
        <sport>123</sport>
        <dport>123</dport>
        <natsport>0</natsport>
        <natdport>0</natdport>
        <flags>Example</flags>
        <flag-pcap>no</flag-pcap>
        <flag-flagged>no</flag-flagged>
        <flag-proxy>no</flag-proxy>
        <flag-url-denied>no</flag-url-denied>
        <flag-nat>no</flag-nat>
        <captive-portal>no</captive-portal>
        <non-std-dport>no</non-std-dport>
        <transaction>no</transaction>
        <pbf-c2s>no</pbf-c2s>
        <pbf-s2c>no</pbf-s2c>
        <temporary-match>no</temporary-match>
        <sym-return>no</sym-return>
        <decrypt-mirror>no</decrypt-mirror>
        <proto>no</proto>
        <action>no</action>
        <cpadding>0</cpadding>
        <dg_hier_level_1>0</dg_hier_level_1>
        <dg_hier_level_2>0</dg_hier_level_2>
        <dg_hier_level_3>0</dg_hier_level_3>
        <dg_hier_level_4>0</dg_hier_level_4>
        <vsys_name>Example</vsys_name>
        <device_name>Example</device_name>
        <vsys_id>0</vsys_id>
        <bytes>299</bytes>
        <bytes_sent>104</bytes_sent>
        <bytes_received>195</bytes_received>
        <packets>2</packets>
        <start>Example</start>
        <elapsed>0</elapsed>
        <category>Example</category>
        <padding>0</padding>
        <pkts_sent>1</pkts_sent>
        <pkts_received>1</pkts_received>
        <session_end_reason>Example</session_end_reason>
        <action_source>Example</action_source>
      </entry>
    </logs>
  </log>
</result></response>

我想把数据放入一个数据帧中，这样我就可以用以下标题将其写入csv：

^{pr2}$

下面是表的外观图（显然需要插入所有数据）：
Table

我可以用for循环部分地使用它，但是我需要额外的29个循环和29个df转换来合并其余的头/数据：

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

tree = ET.parse("My_Data.xml")


a = []
b = []


for src in tree.findall('.//src'):
    a.append({'Source': src.text})


for domain in tree.findall('.//domain'):
    b.append({'Domain': domain.text})


df = pd.DataFrame(a)

df1 = pd.DataFrame(b)


result = pd.concat([df,df1], axis=1)

这是我的第一篇文章。我浏览这个网站很多年了，它帮了我很多忙。感谢大家的帮助和辛勤工作。如果您需要更多信息，请告诉我。在

更新

我也有一个问题，不是每个响应中都存在标记。例如，如果第一个响应中不存在“type”，则不会将其添加到头中。如果第二个xml响应包含此标记，它将抛出一个错误：ValueError:dict包含不在fieldnames中的字段：“type”

更新代码

这段代码发布在其中一个回应工作得很好，只需要修改它，以配合上面的更新。在

最终更新

下面的代码100%适用于我的用例。您可以在“评论”部分的所选答案中看到完整的解释。在

import csv
import xml.etree.ElementTree as et
from collections import OrderedDict

doc = et.parse('LogEntryCSV.xml')

csv_data = []

fields =  ['logid', 'receive_time', 'type', 'src', 'dst', 'rule',
           'srcuser', 'srcloc', 'dstloc', 'app', 'from', 'to', 'repeatcnt', 
           'sport', 'dport', 'proto', 'action', 'bytes', 'bytes_sent', 
           'bytes_received', 'packets', 'start', 'elapsed', 'category', 
           'pkts_sent', 'pkts_received', 'session_end_reason', 'action_source']

for elem in doc.findall('.//entry'):    
    inner_dict = OrderedDict({k:None for k in fields})   # PRE-POPULATES TEMP DICT

    inner_dict['logid'] = elem.attrib['logid']

    for item in elem.findall('.//*'):
        if item.tag in fields:
            if item.tag=='srcloc':
                inner_dict['scrloc code'] = item.attrib['code']
                inner_dict['scr_cc'] = item.attrib['cc']

            elif item.tag=='dstloc':
                inner_dict['dstloc code'] = item.attrib['code']
                inner_dict['dst_cc'] = item.attrib['cc']

            else:
                inner_dict[item.tag] = item.text

    csv_data.append(inner_dict)

with open('Output.csv', 'w', newline='') as f:
    w = csv.DictWriter(f, csv_data[0].keys())
    w.writeheader()
    w.writerows(csv_data)

Answer 1

考虑使用像字典列表这样的容器，这些容器在for循环迭代中分配给entry的每个子级。对于属性，if条件语句用于分析属性而不是文本值。OrderedDict用于在填充密钥时保持密钥的完整性。不需要熊猫，因为你应该离开图书馆进行实际的数据分析，而不是数据争论。在

import csv
import xml.etree.ElementTree as et
from collections import OrderedDict

doc = et.parse('LogEntryCSV.xml')

csv_data = []

fields =  ['logid', 'receive_time', 'type', 'src', 'dst', 'rule',
           'srcuser', 'srcloc', 'dstloc', 'app', 'from', 'to', 'repeatcnt', 
           'sport', 'dport', 'proto', 'action', 'bytes', 'bytes_sent', 
           'bytes_received', 'packets', 'start', 'elapsed', 'category', 
           'pkts_sent', 'pkts_received', 'session_end_reason', 'action_source']

for elem in doc.findall('.//entry'):    
    inner_dict = OrderedDict({k:None for k in fields})   # PRE-POPULATES TEMP DICT

    inner_dict['logid'] = elem.attrib['logid']

    for item in elem.findall('.//*'):
        if item.tag in fields:
            if item.tag=='srcloc':
                inner_dict['scrloc code'] = item.attrib['code']
                inner_dict['scr_cc'] = item.attrib['cc']

            elif item.tag=='dstloc':
                inner_dict['dstloc code'] = item.attrib['code']
                inner_dict['dst_cc'] = item.attrib['cc']

            else:
                inner_dict[item.tag] = item.text

    csv_data.append(inner_dict)

with open('Output.csv', 'w', newline='') as f:
    w = csv.DictWriter(f, csv_data[0].keys())
    w.writeheader()
    w.writerows(csv_data)