python解析来自欧盟统计局sdmx的xml文件

Question

我试图解析来自欧盟统计局的数据

原始文件示例：http://ec.europa.eu/eurostat/SDMX/diss-web/rest/data/nrg_bal_c/A.PPRD+IMP.TOTAL.KTOE.FR/?startPeriod=2016

<?xml version='1.0' encoding='UTF-8'?><message:GenericData xmlns:footer="http://www.sdmx.org/resources/sdmxml/schemas/v2_1/message/footer" xmlns:generic="http://www.sdmx.org/resources/sdmxml/schemas/v2_1/data/generic" xmlns:common="http://www.sdmx.org/resources/sdmxml/schemas/v2_1/common" xmlns:message="http://www.sdmx.org/resources/sdmxml/schemas/v2_1/message" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xml="http://www.w3.org/XML/1998/namespace"><message:Header><message:ID>c79d0c159af023563e604b59c443b622</message:ID><message:Test>false</message:Test><message:Prepared>2020-02-05T08:16:58</message:Prepared><message:Sender id="ESTAT"><common:Name xml:lang="en">Eurostat</common:Name><message:Timezone>+01:00</message:Timezone></message:Sender><message:Receiver id="RECEIVER"/><message:Structure structureID="ESTAT_DSD_nrg_bal_c_1_0" dimensionAtObservation="TIME_PERIOD"><common:Structure><Ref agencyID="ESTAT" id="DSD_nrg_bal_c" version="1.0"/></common:Structure></message:Structure><message:DataSetAction>Append</message:DataSetAction><message:DataSetID>nrg_bal_c</message:DataSetID></message:Header><message:DataSet structureRef="ESTAT_DSD_nrg_bal_c_1_0"><generic:Series><generic:SeriesKey><generic:Value id="UNIT" value="KTOE"/><generic:Value id="SIEC" value="TOTAL"/><generic:Value id="NRG_BAL" value="IMP"/><generic:Value id="GEO" value="FR"/><generic:Value id="FREQ" value="A"/></generic:SeriesKey><generic:Obs><generic:ObsDimension value="2018"/><generic:ObsValue value="152791.215"/></generic:Obs><generic:Obs><generic:ObsDimension value="2017"/><generic:ObsValue value="157271.627"/></generic:Obs><generic:Obs><generic:ObsDimension value="2016"/><generic:ObsValue value="151967.722"/></generic:Obs></generic:Series><generic:Series><generic:SeriesKey><generic:Value id="UNIT" value="KTOE"/><generic:Value id="SIEC" value="TOTAL"/><generic:Value id="NRG_BAL" value="PPRD"/><generic:Value id="GEO" value="FR"/><generic:Value id="FREQ" value="A"/></generic:SeriesKey><generic:Obs><generic:ObsDimension value="2018"/><generic:ObsValue value="137928.128"/></generic:Obs><generic:Obs><generic:ObsDimension value="2017"/><generic:ObsValue value="131845.752"/></generic:Obs><generic:Obs><generic:ObsDimension value="2016"/><generic:ObsValue value="133737.071"/></generic:Obs></generic:Series></message:DataSet></message:GenericData>

我使用python+requests+lxml+pandas库（只有这个，我的公司不允许使用第三方库，比如pandasdmx）。我可能会使用xmlschema

root = etree.fromstring(response.content)
dataset = root.getchildren()[1]
result = [elem.attrib for elem in dataset.iter()]

结果如下所示：

[{'structureRef': 'ESTAT_DSD_nrg_bal_c_1_0'},
 {},
 {},
 {'id': 'UNIT', 'value': 'KTOE'},
 {'id': 'SIEC', 'value': 'TOTAL'},
 {'id': 'NRG_BAL', 'value': 'IMP'},
 {'id': 'GEO', 'value': 'FR'},
 {'id': 'FREQ', 'value': 'A'},
 {},
 {'value': '2018'},
 {'value': '152791.215'},
 {},
 {'value': '2017'},
 {'value': '157271.627'},
 {},
 {'value': '2016'},
 {'value': '151967.722'},
 {},
 {},
 {'id': 'UNIT', 'value': 'KTOE'},
 {'id': 'SIEC', 'value': 'TOTAL'},
 {'id': 'NRG_BAL', 'value': 'PPRD'},
 {'id': 'GEO', 'value': 'FR'},
 {'id': 'FREQ', 'value': 'A'},
 {},
 {'value': '2018'},
 {'value': '137928.128'},
 {},
 {'value': '2017'},
 {'value': '131845.752'},
 {},
 {'value': '2016'},
 {'value': '133737.071'}]

我试图将结果对象放在一个格式良好的数据帧中，但我一直坚持这样做

有什么线索吗

另外一个问题：如果我能得到与xml文件相关的xsd文件，我认为xmlschema库可以帮助我解析xml。我是否误读了这个库的功能

Answer 1

我找到了实现目标的方法，但这个解决方案需要改进

nbSerie = int(root.xpath('count(.//generic:Series)', namespaces=ns))

dim = []
obs = []

for i in range(nbSerie):
     serieskey = root.getchildren()[1][i].xpath(".//generic:Value/@id|.//generic:Value/@value", namespaces=ns)
     values = root.getchildren()[1][i].xpath(".//generic:ObsDimension/@value|.//generic:ObsValue/@value", namespaces=ns)
     dim.append(serieskey)
     obs.append(values)

from itertools import chain

a=list(chain(*list(chain(*list(zip(dim, obs))))))
b=list(zip(a[::2], a[1::2]))

from collections import defaultdict

data_dic = defaultdict(list)
for k, v in b:
     data_dic[k].append(v)

df = pd.DataFrame(data_dic)
df

Answer 2

虽然有点晚了，但是已经有一个python包可以帮助您从欧盟统计局SDMX服务获取数据：https://pypi.org/project/eurostat/