如何用Pandas Merge/Join关联新旧数据并统一NE_NAME？

需求说明 #

现有两个数据文件：

1. 路由数据文件：包含新旧路由记录，同一NUMBER对应新旧两条路由，NEXT_HOP_IP不同
2. 旧设备数据文件：NE_NAME重复但NEXT_HOP_IP唯一，需通过旧路由的NEXT_HOP_IP匹配到NE_NAME，并将该NE_NAME同步至同NUMBER的新路由条目

示例数据 #

路由数据文件

NUMBER  route_id        route_type  NEXT_HOP_IP          
14738   14738_site_1    new         10.43.148.221    
14738   14738_site_1    old         10.43.148.217    
79470   79470_site_6    new         10.43.148.101    
79470   79470_site_6    old         10.43.148.241

旧设备数据文件

NE_NAME      NEXT_HOP_IP  
ABC14738_A   10.43.148.217
ABC14738_A   ...
ABC79470_B   10.43.148.241
ABC79470_B   ...
ABC14738_B   xx.xx.xx.xx  
ABC79470_C   xx.xx.xx.xx  

期望结果

NUMBER  route_id        route_type  NEXTHOP         Name          
14738   14738_site_1    new         10.43.148.221   ABC14738_A 
14738   14738_site_1    old         10.43.148.217   ABC14738_A 
79470   79470_site_6    new         10.43.148.101   ABC79470_B
79470   79470_site_6    old         10.43.148.241   ABC79470_B

解决方案：用Pandas的merge+分组填充实现 #

完全可以用Pandas的merge结合分组填充来实现，步骤如下：

1. 导入依赖并读取数据 #

import pandas as pd

# 读取路由数据（分隔符根据实际文件调整，示例为空格分隔）
route_df = pd.read_csv('路由数据文件路径', sep='\s+')
# 读取旧设备数据
device_df = pd.read_csv('旧设备数据文件路径', sep='\s+')

2. 预处理旧设备数据：提取唯一IP与NE_NAME映射 #

因为旧设备数据中NEXT_HOP_IP唯一，先去重得到每个IP对应的NE_NAME：

# 保留每个NEXT_HOP_IP对应的第一个NE_NAME（IP唯一，任意一条有效记录均可）
device_unique = device_df.drop_duplicates(subset=['NEXT_HOP_IP'], keep='first')

3. 匹配旧路由的NE_NAME #

将路由数据与去重后的设备数据按NEXT_HOP_IP合并，此时旧路由会匹配到NE_NAME，新路由因IP不在设备数据中会显示空值：

merged_df = route_df.merge(device_unique, on='NEXT_HOP_IP', how='left')

4. 按NUMBER同步NE_NAME到同组新路由 #

通过分组填充，将同一NUMBER下旧路由的NE_NAME填充到新路由的空值位置：

# 按NUMBER分组，用前后填充法补全空值，确保同组所有条目共享同一NE_NAME
merged_df['NE_NAME'] = merged_df.groupby('NUMBER')['NE_NAME'].transform(lambda x: x.ffill().bfill())

5. 调整列名与格式（可选） #

根据期望结果重命名列并调整顺序：

# 重命名列
merged_df.rename(columns={'NE_NAME': 'Name', 'NEXT_HOP_IP': 'NEXTHOP'}, inplace=True)
# 调整列顺序
final_df = merged_df[['NUMBER', 'route_id', 'route_type', 'NEXTHOP', 'Name']]

最终效果 #

运行上述代码后，final_df即为期望的结果格式。

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