Pandas：基于设备名称匹配，从另一数据集导入MAC地址

数据集说明 #

df1（仅含deviceNames列） #

deviceNames
0     12132182
1     12134086
2     12203676
3     12131211
4     12129534

df2（含deviceNames和macAddress等列） #

deviceNames        macAddress
0         12080084  001350050039517e
1         12080085  001350050039448c
2         12080086  00135005003954c9
3         12080087  00135005003943bc
4         12080088  0013500500394ff5
...            ...               ...
107549    C0524751  0013500500EA4DEB
107550         NaN               NaN
107551         NaN               NaN
107552         NaN               NaN
107553    C0591266  00135005010FB39D

期望输出 #

deviceNames        macAddress
0         12132182  0013500124039517e
1         12134086  0013501340039448c
2         12203676  001350440031954c9
3         12131211  0013503300w3943bc
4         12129534  00135032500394ff5

尝试方案的问题分析 #

方案1 #

df2[df2['deviceNames'].isin(df1['deviceNames'])]

仅能从df2筛选出存在于df1的设备记录，但无法匹配到df1的原有行结构，输出行顺序与df1不一致，且会丢失df2中无对应记录的df1设备。

方案2 #

s = (df2.loc[df2.deviceNames.isin(df1.deviceNames.values.tolist())]
     .drop_duplicates('df1')
     .set_index('df1')['macAddress'])

df1['newcolumn'] = df1['newcolumn'].map(s)

存在两处错误：

1. drop_duplicates('df1')和set_index('df1')中误用数据集名称df1作为列名，实际应使用列名deviceNames
2. 赋值时df1['newcolumn']尚未创建，直接调用会报错

正确解决方案 #

方法1：使用merge左连接（推荐） #

通过左连接保留df1的所有行，精准匹配对应MAC地址：

import pandas as pd

# 读取CSV文件
df1 = pd.read_csv('df1.csv')
df2 = pd.read_csv('df2.csv')

# 左连接匹配，仅保留需要的列
result = pd.merge(df1, df2[['deviceNames', 'macAddress']], on='deviceNames', how='left')

# 去重，确保每个设备仅保留一条MAC记录
result = result.drop_duplicates('deviceNames', keep='first')

print(result)

• how='left'：保证df1的所有设备都被保留，无对应MAC时填充NaN
• 仅选取df2的目标列合并，避免引入无关数据

方法2：使用map映射 #

先构建设备名称到MAC地址的字典，再批量匹配：

import pandas as pd

df1 = pd.read_csv('df1.csv')
df2 = pd.read_csv('df2.csv')

# 创建去重后的设备-MAC映射字典
mac_map = df2.drop_duplicates('deviceNames').set_index('deviceNames')['macAddress'].to_dict()

# 给df1添加MAC地址列
df1['macAddress'] = df1['deviceNames'].map(mac_map)

print(df1)

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