对比两个DataFrame列并剔除不匹配行的技术实现需求

现有两个DataFrame：

import pandas as pd
import numpy as np

camera = pd.DataFrame({'camera_sn':[23,34,465,987], 
                       'sn_sensor':[987,976,543,np.nan], 
                       'colormask':['color', 'grey', 'none', 'grey'],
                       'id':[23,45,np.nan,np.nan]})

sensor = pd.DataFrame({'quality_mask':['A','A',np.nan,'B'],
                       'sensor_sn':[987,976,543,np.nan],
                       'featureMask':['color', 'grey', 'none', 'grey'],
                       'id':[np.nan,np.nan,np.nan,np.nan]})

通过sensor_sn和sn_sensor列进行左连接（无法修改列名）：

join_cs = camera.join(sensor.set_index('sensor_sn'), on='sn_sensor', how='left', 
                      lsuffix='_camera',rsuffix='_sensor')
join_cs.drop_duplicates(inplace=True) # 存在重复行，但未完全解决问题

连接后出现的问题：当sn_sensor为NaN时，生成了多条索引重复且quality_mask/featureMask不匹配的无效行，这类行无法通过drop_duplicates剔除。示例连接后数据如下：

index  camera_sn  sensor_sn  id_camera colormask  featureMask quality_mask id_sensor
0          23        987       23        color      color        A             nan
1          34        976       45        grey       grey         A             nan
2          465       543       nan       none       none         nan           nan
2          465       543       nan       none       color        nan           nan
3          987       nan       nan       grey       grey         B             nan
3          987       nan       nan       grey       none         B             nan
3          987       nan       nan       grey       color        A             nan
3          987       nan       nan       grey       grey         A             nan

需要过滤掉featureMask或quality_mask与sensor中对应列不匹配的行，得到符合要求的结果。

无需遍历DataFrame，利用Pandas的集合匹配即可高效过滤无效行：

方法1：基于匹配组合过滤 #

1. 先提取sensor中所有有效的(sensor_sn, featureMask, quality_mask)组合：

# 获取sensor中的有效匹配组合
valid_combinations = sensor[['sensor_sn', 'featureMask', 'quality_mask']].drop_duplicates()

2. 将连接后的DataFrame与有效组合做内连接，只保留匹配的行：

filtered_df = join_cs.merge(valid_combinations,
                            left_on=['sn_sensor', 'featureMask', 'quality_mask'],
                            right_on=['sensor_sn', 'featureMask', 'quality_mask'],
                            how='inner')
# 移除重复的sensor_sn列（可选，根据需求保留）
filtered_df = filtered_df.drop(columns='sensor_sn_y').rename(columns={'sensor_sn_x': 'sensor_sn'})

方法2：基于元组匹配过滤 #

如果需要更直观的匹配判断，可将有效组合转为元组列表，再用isin过滤：

# 将有效组合转为元组列表
match_tuples = list(valid_combinations.itertuples(index=False, name=None))
# 过滤出符合组合的行
filtered_df = join_cs[join_cs[['sn_sensor', 'featureMask', 'quality_mask']].apply(tuple, axis=1).isin(match_tuples)]

结果验证 #

处理后示例数据将只保留以下有效行：

index  camera_sn  sensor_sn  id_camera colormask  featureMask quality_mask id_sensor
0          23        987       23        color      color        A             nan
1          34        976       45        grey       grey         A             nan
2          465       543       nan       none       none         nan           nan
3          987       nan       nan       grey       grey         B             nan

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