python如何创建xml文件_如何在Python中打开这个XML文件来创建dataframe？

XML是一个树状结构&＃xff0c;而Pandas数据帧是一个二维表状结构。所以这两者之间没有自动转换的方法。您必须了解XML结构并知道如何将其数据映射到二维表。

因此&＃xff0c;每个XML到数据帧的问题都是不同的。

XML有两个数据集&＃xff0c;每个数据集包含一系列数据。每个序列都包含许多Obs元素。

每个序列都有一个NAME属性&＃xff0c;每个Obs都有Obs_STATUS、TIME_PERIOD和Obs_VALUE属性。因此&＃xff0c;创建一个包含NAME、OBS_STATUS、TIME_PERIOD和OBS_VALUE列的表可能是合理的。

我发现从XML中提取所需的数据有点复杂&＃xff0c;这使我怀疑是否找到了最好的方法。但这里有一种方法(PS.Thomas Maloney关于从二维表(如XLS)数据开始的想法应该简单得多)&＃xff1a;import lxml.etree as ET

import pandas as pd

path &＃61; &＃39;feds200628.xml&＃39;

def fast_iter(context, func, *args, **kwargs):

"""

http://lxml.de/parsing.html#modifying-the-tree

Based on Liza Daly&＃39;s fast_iter

http://www.ibm.com/developerworks/xml/library/x-hiperfparse/

See also http://effbot.org/zone/element-iterparse.htm

http://stackoverflow.com/a/7171543/190597 (unutbu)

"""

for event, elem in context:

func(elem, *args, **kwargs)

# It&＃39;s safe to call clear() here because no descendants will be

# accessed

elem.clear()

# Also eliminate now-empty references from the root node to elem

for ancestor in elem.xpath(&＃39;ancestor-or-self::*&＃39;):

while ancestor.getprevious() is not None:

del ancestor.getparent()[0]

del context

data &＃61; list()

obs_keys &＃61; [&＃39;OBS_STATUS&＃39;, &＃39;TIME_PERIOD&＃39;, &＃39;OBS_VALUE&＃39;]

columns &＃61; [&＃39;NAME&＃39;] &＃43; obs_keys

def process_obs(elem, name):

dct &＃61; elem.attrib

# print(dct)

data.append([name] &＃43; [dct[key] for key in obs_keys])

def process_series(elem):

dct &＃61; elem.attrib

# print(dct)

context &＃61; ET.iterwalk(

elem, events&＃61;(&＃39;end&＃39;, ),

tag&＃61;&＃39;{http://www.federalreserve.gov/structure/compact/common}Obs&＃39;

)

fast_iter(context, process_obs, dct[&＃39;SERIES_NAME&＃39;])

def process_dataset(elem):

nsmap &＃61; elem.nsmap

# print(nsmap)

context &＃61; ET.iterwalk(

elem, events&＃61;(&＃39;end&＃39;, ),

tag&＃61;&＃39;{{{prefix}}}Series&＃39;.format(prefix&＃61;elem.nsmap[&＃39;kf&＃39;])

)

fast_iter(context, process_series)

with open(path, &＃39;rb&＃39;) as f:

context &＃61; ET.iterparse(

f, events&＃61;(&＃39;end&＃39;, ),

tag&＃61;&＃39;{http://www.federalreserve.gov/structure/compact/common}DataSet&＃39;

)

fast_iter(context, process_dataset)

df &＃61; pd.DataFrame(data, columns&＃61;columns)

收益率NAME OBS_STATUS TIME_PERIOD OBS_VALUE

0 SVENY01 A 1961-06-14 2.9825

1 SVENY01 A 1961-06-15 2.9941

2 SVENY01 A 1961-06-16 3.0012

3 SVENY01 A 1961-06-19 2.9949

4 SVENY01 A 1961-06-20 2.9833

5 SVENY01 A 1961-06-21 2.9993

6 SVENY01 A 1961-06-22 2.9837

...

1029410 TAU2 A 2014-09-19 3.72896779

1029411 TAU2 A 2014-09-22 3.12836171

1029412 TAU2 A 2014-09-23 3.20146575

1029413 TAU2 A 2014-09-24 3.29972110