如何用Pandas中DataFrame列的唯一值数组过滤另一个DataFrame？

我想使用X.ColumnA.unique()得到的数组值来过滤另一个DataFrame，之前是手动指定多个值用或运算过滤，现在需要用这个数组的全部值完成过滤，该怎么实现？

原有手动过滤代码 #

Y = ((df['ColumnnA'] == "22.33.44.55")
| (df['ColumnnA'] == "12.12.32.44") 
| (df['ColumnnA'] == "45.142.22.22") 
| (df['ColumnnA'] == "55.197.55.8") 
| (df['ColumnnA'] == "44.44.211.254") 
| (df['ColumnnA'] == "33.44.234.83") 
| (df['ColumnnA'] == "33.33.221.240") 
| (df['ColumnnA'] == "33.33.33.1"))
restdataframe = df[~Y]
Y=df[Y]

X.ColumnA.unique()得到的数组 #

X.ColumnA.unique()
array(['0', '222.33.222.106', '12.12.32.44', '122.122.1.1',
       '122.222.180.150', '142.222.180.142', '222.99.222.78',
       '33.33.221.240', '151.99.222.76', '222.251.222.1',
       '222.250.184.46', '22.33.44.55', ........ ]

目标DataFrame示例 #

df.head(5).to_dict()
{'Column0': {0: 0.00192, 1: 0.0, 2: 0.834324, 3: 8.588816, 4: 2.908711},
 'Column1': {0: '0',
  1: '192.168.1.1',
  2: '22.22.2.15',
  3: '10.22.2.15',
  4: '10.22.22.15'},
 'ColumnA': {0: '0',
  1: '10.0.2.22',
  2: '20.55.22.22',
  3: '22.44.1.1',
  4: '44.33.1.1'},
 'Column2': {0: 'yyy', 1: 'xxx', 2: 'zzz', 3: 'xxx', 4: 'yyy'},
 'Column3': {0: '88', 1: '88', 2: '777', 3: '666', 4: '555'},
 'Column4': {0: '0', 1: '111', 2: '222', 3: '333', 4: '444'},
 'Column5': {0: 0, 1: 1, 2: 17, 3: 8, 4: 4},
 'Column6': {0: 0, 1: 1, 2: 7, 3: 4, 4: 2},
 'Column7': {0: 0, 1: 0, 2: 10, 3: 4, 4: 2},
 'Column8': {0: 0, 1: 110, 2: 5798, 3: 504, 4: 408},
 'Column9': {0: 0, 1: 110, 2: 775, 3: 264, 4: 188},
 'Column10': {0: 0, 1: 0, 2: 5023, 3: 240, 4: 220},
 'Column11': {0: 0, 1: 0, 2: 0, 3: 3, 4: 0},
 'Column12': {0: 'DDD', 1: 'EEE', 2: 'AAA', 3: 'BBB', 4: 'CCC'}}

解决方案 #

直接用Pandas的isin()方法就能实现，代码简洁且能自动适配目标数组的所有值：

# 获取需要匹配的数组
target_values = X.ColumnA.unique()
# 生成过滤条件：ColumnA是否在目标数组中
mask = df['ColumnA'].isin(target_values)
# 分离出匹配和不匹配的DataFrame
Y = df[mask]
restdataframe = df[~mask]

说明 #

• isin()会检查列中每个元素是否存在于传入的数组/列表中，返回布尔Series，完全替代原来手动拼接的多个==或运算。
• 注意原代码中列名写错：ColumnnA应为ColumnA，需和目标DataFrame列名保持一致，否则会报错。
• 这种方式无论目标数组有多少值，都能一次性处理，无需修改代码，扩展性更强。

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