Pandas DataFrame按键查找值及XML提取关联ID实现问题求助

我认为dataframe的基础用法之一是通过类似'key'的索引返回对应'value'，但我多日搜索尝试均未成功，希望能获得帮助。
我曾尝试使用.to_dict()方法，但无法将值调整为可查询的结构，且将从XML生成的dataframe再转为字典效率过低，因此我回到使用.loc[]方法进行尝试。

测试Python代码 #

# -*- coding: utf-8 -*-
import pandas as pd

#Define thing lookup dataframe columns and rows
thing_cols = ["Thing Name", "Thing ID"]
thing_rows = []

# Append rows, create and index the dataframe
thing_rows.append({"Thing Name": "thing 1 name",
                   "Thing ID": "thing_1_id"})
thing_rows.append({"Thing Name": "thing 2 name",
                   "Thing ID": "thing_2_id"})
thing_df = pd.DataFrame(thing_rows, columns=thing_cols)
thing_df = thing_df.set_index(list(thing_df.keys())[0])
        
print(thing_df.loc["thing 1 name"])

当前输出 #

Thing ID    thing_1_id
Name: thing 1 name, dtype: object

期望输出 #

thing_1_id

我需要从XML文件中提取COLLECTION-ITEM关联的所有THING对应的ID，最终导出为指定格式的CSV。

期望输出CSV格式 #

,Collection item,RELATED-THING-IDs
0,name of Item 1,"thing_1_id, thing_2_id"

现有完整实现代码 #

# -*- coding: utf-8 -*-
import lxml.etree as Xet
import pandas as pd

#Define main collection columns and rows for dataframe
coll_cols = ["Collection item", "RELATED-THING-IDs"]
coll_rows = []
#Define thing lookup dataframe columns and rows
thing_cols = ["Thing Name", "Thing ID"]
thing_rows = []

# Parsing the XML file
xmlparse = Xet.parse('sample.xml')
root = xmlparse.getroot()
for row in root:
    # Create thing lookup dataframe
    if (row.findtext('type') == "THING"):
        thing_id = row.findtext("THING-ID")
        thing_name = row.findtext("name")
        thing_rows.append({"Thing Name": thing_name,
                           "Thing ID": thing_id})
        thing_df = pd.DataFrame(thing_rows, columns=thing_cols)
        thing_df = thing_df.set_index(list(thing_df.keys())[0])
    # Find only collection items
    if row.findtext('type') != "COLLECTION-ITEM":
        continue
    # Define values for collection item dataframe
    name = row.findtext("name", "Missing name")
    relat_thing_items = thing_df.loc[[row.xpath(
        "./RELATED-THING/result/row/name/text()")],["THING-ID"]]
    if len(relat_thing_items) > 0:
        relat_thing_id = ', '.join(relat_thing_items)
    else:
        relat_thing_id = ""

    coll_rows.append({"Collection item": name,
                 "RELATED-THING-IDs": relat_thing_id
})

coll_df = pd.DataFrame(coll_rows, columns=coll_cols)

# Writing dataframe to csv
coll_df.to_csv('output.csv')

示例XML文件内容 #

<?xml version="1.0" encoding="UTF-8"?>
<result size="4321">
  <row>
    <type>CONTEXT</type>
    <name>collections</name>
  </row>
  <row>
    <type>COLLECTION-ITEM</type>
    <name>name of Item 1</name>
    <ITEM-ID>item_000001</ITEM-ID>
    <RELATED-THING>
      <result size="2">
        <row>
          <type>THING</type>
          <name>thing name 1</name>
          <no>1</no>
        </row>
        <row>
          <type>THING</type>
          <name>thing name 2</name>
          <no>1</no>
        </row>
      </result>
    </RELATED-THING>
  </row>
  <row>
    <type>THING</type>
    <name>thing name 1</name>
    <THING-ID>thing_000783</THING-ID>
  </row>
  <row>
    <type>THING</type>
    <name>thing name 2</name>
    <THING-ID>thing_000803</THING-ID>
  </row>
</result>

单条.loc查询问题解决 #

你测试代码里只要把打印语句修改为指定列名，就能直接拿到纯ID值：

print(thing_df.loc["thing 1 name", "Thing ID"])

完整业务代码修正 #

原有代码核心问题：

1. 遍历顺序错误，先处理COLLECTION-ITEM时THING映射还没生成，查询为空
2. xpath返回的列表多套了一层括号，导致.loc索引失败
3. 拼接ID时直接操作Series对象，没有取实际值

优化版代码（用原生字典做映射查询效率远高于dataframe，符合你对性能的要求）：

# -*- coding: utf-8 -*-
import lxml.etree as Xet
import pandas as pd

xmlparse = Xet.parse('sample.xml')
root = xmlparse.getroot()

# 第一步优先生成所有THING的名称-ID映射
thing_map = {}
for row in root:
    if row.findtext('type') == "THING":
        thing_name = row.findtext("name").strip()
        thing_id = row.findtext("THING-ID").strip()
        thing_map[thing_name] = thing_id

# 第二步处理COLLECTION-ITEM
coll_cols = ["Collection item", "RELATED-THING-IDs"]
coll_rows = []
for row in root:
    if row.findtext('type') != "COLLECTION-ITEM":
        continue
    name = row.findtext("name", "Missing name").strip()
    # 取所有关联的THING名称
    relat_thing_names = row.xpath("./RELATED-THING/result/row/name/text()")
    # 匹配ID后拼接
    relat_thing_ids = [thing_map[name.strip()] for name in relat_thing_names if name.strip() in thing_map]
    coll_rows.append({
        "Collection item": name,
        "RELATED-THING-IDs": ', '.join(relat_thing_ids)
    })

coll_df = pd.DataFrame(coll_rows, columns=coll_cols)
coll_df.to_csv('output.csv', encoding='utf-8')

运行后输出的CSV完全符合要求。

内容的提问来源于stack exchange，提问作者Cathi G