我有以下数据结构(原始是 2.5gb,因此必须解析):
<households xmlns="http://www.matsim.org/files/dtd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.matsim.org/files/dtd http://www.matsim.org/files/dtd/households_v1.0.xsd">
<household id="1473">
<members>
<personId refId="2714"/>
<personId refId="2715"/>
<personId refId="2716"/>
<personId refId="2717"/>
<personId refId="2718"/>
<personId refId="2719"/>
</members>
<income currency="CHF" period="month">
3094.87101
</income>
<attributes>
<attribute name="bikeAvailability" class="java.lang.String" >some</attribute>
<attribute name="carAvailability" class="java.lang.String" >some</attribute>
<attribute name="consumptionUnits" class="java.lang.Double" >3.3</attribute>
<attribute name="householdIncomePerConsumptionUnit" class="java.lang.Double" >3094.8710104279835</attribute>
<attribute name="numberOfCars" class="java.lang.Integer" >1</attribute>
<attribute name="residenceZoneCategory" class="java.lang.Integer" >1</attribute>
<attribute name="totalHouseholdIncome" class="java.lang.Double" >10213.074334412346</attribute>
</attributes>
</household>
<household id="2474">
<members>
<personId refId="4647"/>
<personId refId="4648"/>
<personId refId="4649"/>
<personId refId="4650"/>
<personId refId="4651"/>
<personId refId="4652"/>
<personId refId="4653"/>
<personId refId="4654"/>
<personId refId="4655"/>
</members>
<income currency="CHF" period="month">
1602.562822
</income>
<attributes>
<attribute name="bikeAvailability" class="java.lang.String" >none</attribute>
<attribute name="carAvailability" class="java.lang.String" >all</attribute>
<attribute name="consumptionUnits" class="java.lang.Double" >3.6999999999999997</attribute>
<attribute name="householdIncomePerConsumptionUnit" class="java.lang.Double" >1602.5628215679633</attribute>
<attribute name="numberOfCars" class="java.lang.Integer" >1</attribute>
<attribute name="residenceZoneCategory" class="java.lang.Integer" >1</attribute>
<attribute name="totalHouseholdIncome" class="java.lang.Double" >5929.482439801463</attribute>
</attributes>
</household>
<household id="4024">
<members>
<personId refId="7685"/>
</members>
<income currency="CHF" period="month">
61610.096619
</income>
<attributes>
<attribute name="bikeAvailability" class="java.lang.String" >none</attribute>
<attribute name="carAvailability" class="java.lang.String" >none</attribute>
<attribute name="consumptionUnits" class="java.lang.Double" >1.0</attribute>
<attribute name="householdIncomePerConsumptionUnit" class="java.lang.Double" >61610.096618936936</attribute>
<attribute name="numberOfCars" class="java.lang.Integer" >0</attribute>
<attribute name="residenceZoneCategory" class="java.lang.Integer" >1</attribute>
<attribute name="totalHouseholdIncome" class="java.lang.Double" >61610.096618936936</attribute>
</attributes>
</household>
</households>
我想提取所有person ID refId
值及其对应的income
值。最终,我计划有一个 df,其中一列是 personId,一列是收入(收入将是重复的)。所以棘手的部分不仅是命名空间,还有如何在不同节点级别访问 XML。
到目前为止,我的方法未能做到这一点。
import gzip
import xml.etree.ElementTree as ET
from collections import defaultdict
import pandas as pd
import numpy as np
tree = ET.parse(gzip.open('V0_1pm/output_households.xml.gz', 'r'))
root = tree.getroot()
rows = []
for it in root.iter('household'):
hh = it.attrib['id']
inc = it.find('income').text
rows.append([hh,inc])
hh_inc = pd.DataFrame(rows, columns=['id', 'PTSubscription'])
hh_inc
任何帮助都受到高度赞赏。
您的代码失败的原因是您的输入元素具有非空命名空间。
处理命名空间 XML 的方法之一是:
还要注意的是找到(...)。文字的回报完全文本,以换行字符和空格。为了解决这个问题,你可能应该:
因此,将您的代码更改为:
# Namespace dictionary
ns = {'dtd': 'http://www.matsim.org/files/dtd'}
rows = []
for it in root.findall('dtd:household', ns):
hh = it.attrib['id']
inc = it.find('dtd:income', ns).text
inc = float(inc.strip())
rows.append([hh, inc])
hh_inc = pd.DataFrame(rows, columns=['id', 'PTSubscription'])
hh_inc
对于您的示例输入,我得到了:
id PTSubscription
0 1473 3094.871010
1 2474 1602.562822
2 4024 61610.096619
我假设 DataFrame 应该为每个refId包含单独的行,具有相关的id和PTSubscription。
要包含refId,请将循环更改为:
for it in root.findall('dtd:household', ns):
hh = it.attrib['id']
inc = it.find('dtd:income', ns).text
inc = float(inc.strip())
pids = it.findall('.//dtd:personId', ns)
for pId in pids:
refId = pId.attrib['refId']
rows.append([hh, inc, int(refId)])
if not pids:
rows.append([hh, inc, -1])
我添加了最后 2 条说明,以免“丢失”任何不包含refId 的家庭。
创建 DataFrame 时,传递附加列名称:
hh_inc = pd.DataFrame(rows, columns=['id', 'PTSubscription', 'refId'])
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句