Python 如何将 XML 文件转换为漂亮的 Pandas 数据框？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28259301/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to convert an XML file to nice pandas dataframe?
提问by eoriu
Let's assume that I have an XML like this:
让我们假设我有一个这样的 XML：
<author type="XXX" language="EN" gender="xx" feature="xx" web="foobar.com">
<documents count="N">
<document KEY="e95a9a6c790ecb95e46cf15bee517651" web="www.foo_bar_exmaple.com"><![CDATA[A large text with lots of strings and punctuations symbols [...]
]]>
</document>
<document KEY="bc360cfbafc39970587547215162f0db" web="www.foo_bar_exmaple.com"><![CDATA[A large text with lots of strings and punctuations symbols [...]
]]>
</document>
<document KEY="19e71144c50a8b9160b3f0955e906fce" web="www.foo_bar_exmaple.com"><![CDATA[A large text with lots of strings and punctuations symbols [...]
]]>
</document>
<document KEY="21d4af9021a174f61b884606c74d9e42" web="www.foo_bar_exmaple.com"><![CDATA[A large text with lots of strings and punctuations symbols [...]
]]>
</document>
<document KEY="28a45eb2460899763d709ca00ddbb665" web="www.foo_bar_exmaple.com"><![CDATA[A large text with lots of strings and punctuations symbols [...]
]]>
</document>
<document KEY="a0c0712a6a351f85d9f5757e9fff8946" web="www.foo_bar_exmaple.com"><![CDATA[A large text with lots of strings and punctuations symbols [...]
]]>
</document>
<document KEY="626726ba8d34d15d02b6d043c55fe691" web="www.foo_bar_exmaple.com"><![CDATA[A large text with lots of strings and punctuations symbols [...]
]]>
</document>
<document KEY="2cb473e0f102e2e4a40aa3006e412ae4" web="www.foo_bar_exmaple.com"><![CDATA[A large text with lots of strings and punctuations symbols [...] [...]
]]>
</document>
</documents>
</author>
I would like to read this XML file and convert it to a pandas DataFrame:
我想读取这个 XML 文件并将其转换为 Pandas DataFrame：
key type language feature web data
e95324a9a6c790ecb95e46cf15bE232ee517651 XXX EN xx www.foo_bar_exmaple.com A large text with lots of strings and punctuations symbols [...]
e95324a9a6c790ecb95e46cf15bE232ee517651 XXX EN xx www.foo_bar_exmaple.com A large text with lots of strings and punctuations symbols [...]
19e71144c50a8b9160b3cvdf2324f0955e906fce XXX EN xx www.foo_bar_exmaple.com A large text with lots of strings and punctuations symbols [...]
21d4af9021a174f61b8erf284606c74d9e42 XXX EN xx www.foo_bar_exmaple.com A large text with lots of strings and punctuations symbols [...]
28a45eb2460823499763d70vdf9ca00ddbb665 XXX EN xx www.foo_bar_exmaple.com A large text with lots of strings and punctuations symbols [...]
This is what I already tried, but I am getting some errors and probably there is a more efficient way of doing this task:
这是我已经尝试过的，但是我遇到了一些错误，并且可能有一种更有效的方法来完成这项任务：
from lxml import objectify
import pandas as pd
path = 'file_path'
xml = objectify.parse(open(path))
root = xml.getroot()
root.getchildren()[0].getchildren()
df = pd.DataFrame(columns=('key','type', 'language', 'feature', 'web', 'data'))
for i in range(0,len(xml)):
obj = root.getchildren()[i].getchildren()
row = dict(zip(['key','type', 'language', 'feature', 'web', 'data'], [obj[0].text, obj[1].text]))
row_s = pd.Series(row)
row_s.name = i
df = df.append(row_s)
Could anybody provide me a better aproach for this problem?
有人可以为我提供更好的方法来解决这个问题吗？
采纳答案by JaminSore
You can easily use xml(from the Python standard library) to convert to a pandas.DataFrame. Here's what I would do (when reading from a file replace xml_datawith the name of your file or file object):
您可以轻松使用xml（来自 Python 标准库）转换为pandas.DataFrame. 这是我会做的事情（从文件读取时替换xml_data为您的文件或文件对象的名称）：
import pandas as pd
import xml.etree.ElementTree as ET
import io
def iter_docs(author):
author_attr = author.attrib
for doc in author.iter('document'):
doc_dict = author_attr.copy()
doc_dict.update(doc.attrib)
doc_dict['data'] = doc.text
yield doc_dict
xml_data = io.StringIO(u'''\
<author type="XXX" language="EN" gender="xx" feature="xx" web="foobar.com">
<documents count="N">
<document KEY="e95a9a6c790ecb95e46cf15bee517651" web="www.foo_bar_exmaple.com"><![CDATA[A large text with lots of strings and punctuations symbols [...]
]]>
</document>
<document KEY="bc360cfbafc39970587547215162f0db" web="www.foo_bar_exmaple.com"><![CDATA[A large text with lots of strings and punctuations symbols [...]
]]>
</document>
<document KEY="19e71144c50a8b9160b3f0955e906fce" web="www.foo_bar_exmaple.com"><![CDATA[A large text with lots of strings and punctuations symbols [...]
]]>
</document>
<document KEY="21d4af9021a174f61b884606c74d9e42" web="www.foo_bar_exmaple.com"><![CDATA[A large text with lots of strings and punctuations symbols [...]
]]>
</document>
<document KEY="28a45eb2460899763d709ca00ddbb665" web="www.foo_bar_exmaple.com"><![CDATA[A large text with lots of strings and punctuations symbols [...]
]]>
</document>
<document KEY="a0c0712a6a351f85d9f5757e9fff8946" web="www.foo_bar_exmaple.com"><![CDATA[A large text with lots of strings and punctuations symbols [...]
]]>
</document>
<document KEY="626726ba8d34d15d02b6d043c55fe691" web="www.foo_bar_exmaple.com"><![CDATA[A large text with lots of strings and punctuations symbols [...]
]]>
</document>
<document KEY="2cb473e0f102e2e4a40aa3006e412ae4" web="www.foo_bar_exmaple.com"><![CDATA[A large text with lots of strings and punctuations symbols [...] [...]
]]>
</document>
</documents>
</author>
''')
etree = ET.parse(xml_data) #create an ElementTree object
doc_df = pd.DataFrame(list(iter_docs(etree.getroot())))
If there are multiple authors in your original document or the root of your XML is not an author, then I would add the following generator:
如果您的原始文档中有多个作者，或者您的 XML 的根不是author，那么我会添加以下生成器：
def iter_author(etree):
for author in etree.iter('author'):
for row in iter_docs(author):
yield row
and change doc_df = pd.DataFrame(list(iter_docs(etree.getroot())))to doc_df = pd.DataFrame(list(iter_author(etree)))
并更改doc_df = pd.DataFrame(list(iter_docs(etree.getroot())))为doc_df = pd.DataFrame(list(iter_author(etree)))
Have a look at the ElementTreetutorialprovided in the xmllibrary documentation.
回答by Jai Prakash
Here is another way of converting a xml to pandas data frame. For example i have parsing xml from a string but this logic holds good from reading file as well.
这是将 xml 转换为 Pandas 数据框的另一种方法。例如，我从一个字符串中解析 xml，但是这个逻辑也适用于读取文件。
import pandas as pd
import xml.etree.ElementTree as ET
xml_str = '<?xml version="1.0" encoding="utf-8"?>\n<response>\n <head>\n <code>\n 200\n </code>\n </head>\n <body>\n <data id="0" name="All Categories" t="2018052600" tg="1" type="category"/>\n <data id="13" name="RealEstate.com.au [H]" t="2018052600" tg="1" type="publication"/>\n </body>\n</response>'
etree = ET.fromstring(xml_str)
dfcols = ['id', 'name']
df = pd.DataFrame(columns=dfcols)
for i in etree.iter(tag='data'):
df = df.append(
pd.Series([i.get('id'), i.get('name')], index=dfcols),
ignore_index=True)
df.head()
回答by Naveen Kaushik
You can also convert by creating a dictionary of elements and then directly converting to a data frame:
您还可以通过创建元素字典然后直接转换为数据框来进行转换：
import xml.etree.ElementTree as ET
import pandas as pd
# Contents of test.xml
# <?xml version="1.0" encoding="utf-8"?> <tags> <row Id="1" TagName="bayesian" Count="4699" ExcerptPostId="20258" WikiPostId="20257" /> <row Id="2" TagName="prior" Count="598" ExcerptPostId="62158" WikiPostId="62157" /> <row Id="3" TagName="elicitation" Count="10" /> <row Id="5" TagName="open-source" Count="16" /> </tags>
root = ET.parse('test.xml').getroot()
tags = {"tags":[]}
for elem in root:
tag = {}
tag["Id"] = elem.attrib['Id']
tag["TagName"] = elem.attrib['TagName']
tag["Count"] = elem.attrib['Count']
tags["tags"]. append(tag)
df_users = pd.DataFrame(tags["tags"])
df_users.head()
