如何利用XML信息关联现有DataFrame生成新DataFrame？

现有DataFrame df1 结构如下：

txnID   date        repID
1   1001    8/5/2020    203
2   1002    6/28/2020   887
3   1003    6/28/2020   887
4   1004    4/16/2020   887
5   1005    6/14/2020   887
6   1006    5/19/2020   332
7   1019    9/23/2020   100
8   1027    12/4/2020   221

另有一份存储销售代表信息的XML文件，内容如下：

<?xml version="1.0" encoding="UTF-8"?>
<salesteam>
  <rep rID="r100">
    <firstName>Helmut</firstName>
    <lastName>Schwab</lastName>
    <territory>EMEA</territory>
  </rep>
  <rep rID="r887">
    <firstName>Walison</firstName>
    <lastName>da Silva</lastName>
    <territory>South America</territory>
  </rep>
  <rep rID="r332">
    <firstName>Lynette</firstName>
    <lastName>McRowe</lastName>
    <territory>East</territory>
  </rep>
  <rep rID="r203">
    <firstName>Aneeta</firstName>
    <lastName>Kappoorthy</lastName>
    <territory>West</territory>
  </rep>
  <rep rID="r221">
    <firstName>Veronika</firstName>
    <lastName>Sixt</lastName>
    <territory>EMEA</territory>
  </rep>
</salesteam>

需要根据XML中repID与lastName的对应关系，将lastName字段匹配至df1，生成包含原交易数据的新DataFrame df3，目标结构如下：

head(df3)
    txnID   date        repID   lastName
1   1001    8/5/2020    203     Kappoorthy
2   1002    6/28/2020   887     da Silva
3   1003    6/28/2020   887     da Silva
4   1004    4/16/2020   887     da Silva
5   1005    6/14/2020   887     da Silva
6   1006    5/19/2020   332     McRowe
7   1019    9/23/2020   100     Schwab
8   1027    12/4/2020   221     Sixt

步骤1：解析XML文件，构建repID到lastName的映射字典 #

用Python标准库xml.etree.ElementTree解析XML，提取每个销售代表的ID（去掉前缀r）和对应的姓氏：

import xml.etree.ElementTree as ET
import pandas as pd

# 解析XML文件，替换为你的实际文件路径
tree = ET.parse('sales_reps.xml')
root = tree.getroot()

# 构建映射字典
rep_lastname_map = {}
for rep in root.findall('rep'):
    # 去掉rID属性的前缀'r'，和df1的repID格式统一
    rep_id = rep.attrib['rID'].lstrip('r')
    last_name = rep.find('lastName').text
    rep_lastname_map[rep_id] = last_name

步骤2：为df1添加lastName列 #

将df1的repID转为字符串后，用map方法匹配姓氏，生成目标df3：

# 确保repID是字符串类型，和映射字典的键格式一致
df1['repID'] = df1['repID'].astype(str)
# 添加lastName列
df1['lastName'] = df1['repID'].map(rep_lastname_map)
# 生成df3
df3 = df1.copy()

# 查看结果
print(df3)

运行后得到的df3就会完全符合目标结构。

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