文章/答案/技术大牛

发布

社区首页 >问答首页 >将XML解析为Pandas DataFrame到CSV

问将XML解析为Pandas DataFrame到CSV
EN

Stack Overflow用户

提问于 2017-12-05 06:21:40

回答 1查看 3.8K关注 0票数 1

我尝试过多次迭代代码来完成这个任务，但似乎找不到一个好的解决方案。干了好几天了。

下面是从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>

我希望数据适合于一个dataframe，这样我就可以用以下头将其写入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

下面是表的外观(显然需要插入的所有数据)的图像：

表格

我能够部分地使用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包含字段而不是字段名：'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)

pandas

dataframe

python

xml

api

回答 1

Stack Overflow用户

回答已采纳

发布于 2017-12-05 18:42:27

考虑使用容器，比如通过每个条目的子条目在for循环迭代中分配的字典列表。对于属性，使用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)

票数 2

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/47647282

复制

相似问题

问将XML解析为Pandas DataFrame到CSV
EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问将XML解析为Pandas DataFrame到CSVEN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问将XML解析为Pandas DataFrame到CSV
EN