python - 将 XML 解析为 Pandas DataFrame 为 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:

logid   
receive_time    
type    
src dst rule    
srcuser 
srcloc code 
src_cc  *NEW HEADER*
dstloc code 
dst_cc  *NEW HEADER*
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

这是表格外观的图像(显然需要插入所有数据):
Table

我能够使用 for 循环部分实现此目的，但随后我需要额外的 29 个循环和 29 个 df 转换来连接剩余的 header /数据:

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”，则不会将其添加到 header 中。如果第二个 xml 响应包含此标记，则会抛出错误: ValueError: dict contains fields not in 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 用于在填充键时保持键的完整性。不需要 pandas，因为您应该离开库进行实际数据分析而不是数据整理。

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)