Pandas多条件过滤DataFrame报错求助：跨表匹配设备ID组合

需求说明 #

有两个DataFrame（transfers和make_costs），需对transfers执行过滤：当TransferType为'Make'或'MakeTransport'时，移除那些SourceEquipmentID与DestinationEquipmentID的组合在make_costs中不存在的行。

原代码与报错 #

原代码 #

mask = ((~transfers['TransferType'].isin(['Make', 'MakeTransport'])) |  (transfers[['SourceEquipmentID', 'DestinationEquipmentID']] <=  make_costs[['SourceEquipmentID', 'DestinationEquipmentID']].values))

transfers1 = transfers[mask]

报错信息 #

236 mask = ((~transfers['TransferType'].isin(['Make', 'MakeTransport'])) |
--> 237         (transfers[['SourceEquipmentID', 'DestinationEquipmentID']] <=  make_costs[[
238             'SourceEquipmentID', 'DestinationEquipmentID']].values))

File ~/anaconda3/lib/python3.10/site-packages/pandas/core/ops/common.py:81, in _unpack_zerodim_and_defer.<locals>.new_method(self, other)
 77             return NotImplemented
 79 other = item_from_zerodim(other)
---&gt; 81 return method(self, other)

File ~/anaconda3/lib/python3.10/site-packages/pandas/core/arraylike.py:52, in OpsMixin.__le__(self, other)
50 @unpack_zerodim_and_defer("__le__")
51 def __le__(self, other):
---&gt; 52     return self._cmp_method(other, operator.le)
File ~/anaconda3/lib/python3.10/site-packages/pandas/core/frame.py:7442, in  DataFrame._cmp_method(self, other, op)
7439 def _cmp_method(self, other, op):
7440     axis: Literal[1] = 1  # only relevant for Series other case
-&gt; 7442     self, other = ops.align_method_FRAME(self, other, axis, flex=False, level=None)
7444     # See GH#4537 for discussion of scalar op behavior
7445     new_data = self._dispatch_frame_op(other, op, axis=axis)

File ~/anaconda3/lib/python3.10/site-packages/pandas/core/ops/__init__.py:288, in align_method_FRAME(left, right, axis, flex, level)
285         right = to_series(right[0, :])
287     else:
--&gt; 288         raise ValueError(
289             "Unable to coerce to DataFrame, shape "
290             f"must be {left.shape}: given {right.shape}"
291         )
293 elif right.ndim &gt; 2:
294     raise ValueError(
295         "Unable to coerce to Series/DataFrame, "
296         f"dimension must be &lt;= 2: {right.shape}"
297     )

ValueError: Unable to coerce to DataFrame, shape must be (81, 2): given (6, 2)

错误原因 #

直接使用<=比较两个不同形状的DataFrame是错误逻辑——这不是判断设备ID组合是否存在的正确方式，pandas无法对齐不同形状的结构进行逐元素比较，因此抛出形状不匹配的错误。

解决方案 #

以下提供三种可行实现方式，可根据数据集大小选择：

方法1：通过merge标记合法组合（通用推荐） #

先提取make_costs中的合法设备ID组合，再通过左连接标记transfers中每行的组合是否合法：

# 提取make_costs中的合法组合并去重
valid_pairs = make_costs[['SourceEquipmentID', 'DestinationEquipmentID']].drop_duplicates()

# 给transfers添加临时标记列，判断组合是否在合法列表中
transfers['is_valid'] = transfers.merge(
    valid_pairs,
    on=['SourceEquipmentID', 'DestinationEquipmentID'],
    how='left',
    indicator=True
)['_merge'] == 'both'

# 构建过滤条件：非Make/ MakeTransport类型 或 是该类型且组合合法
mask = (~transfers['TransferType'].isin(['Make', 'MakeTransport'])) | transfers['is_valid']
transfers1 = transfers[mask].drop(columns='is_valid')

方法2：利用元组集合快速判断（适合大数据集） #

将设备ID组合转换为元组，借助集合的O(1)查询效率判断是否存在：

# 将make_costs中的组合转成元组集合
valid_pairs_set = set(zip(
    make_costs['SourceEquipmentID'],
    make_costs['DestinationEquipmentID']
))

# 生成transfers每行的设备组合元组
transfers_pairs = list(zip(
    transfers['SourceEquipmentID'],
    transfers['DestinationEquipmentID']
))

# 构建过滤掩码
mask = (~transfers['TransferType'].isin(['Make', 'MakeTransport'])) | [pair in valid_pairs_set for pair in transfers_pairs]
transfers1 = transfers[mask]

方法3：广播比较（适合小数据集） #

如果make_costs数据量很小，可以用广播方式直接比较每行组合：

# 生成每行组合是否合法的布尔数组
is_valid_pair = (
    (transfers['SourceEquipmentID'].values[:, None] == make_costs['SourceEquipmentID'].values) &
    (transfers['DestinationEquipmentID'].values[:, None] == make_costs['DestinationEquipmentID'].values)
).any(axis=1)

# 构建过滤条件
mask = (~transfers['TransferType'].isin(['Make', 'MakeTransport'])) | is_valid_pair
transfers1 = transfers[mask]

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