如何用Pythonic方式基于条件DataFrame过滤Pandas DataFrame行？

现有如下Pandas DataFrame：

id  f1     f2     f3     f4
1   8.327  9.905  8.133  0.785
2   3.549  0.452  7.798  5.797
3   0.011  0.238  1.291  7.593
4   0.325  0.792  4.643  4.3
5   7.093  7.312  3.641  9.88
6   2.88   7.834  5.727  6.984
7   5.554  1.649  4.018  0.623
8   2.501  2.941  9.323  0.565
9   1.032  6.961  3.905  8.116
10  9.68   7.922  7.015  7.542
11  8.096  4.344  1.153  5.244

需要通过另一个条件DataFrame过滤数据，找出满足所有条件的记录：

variable    interval
1   f1          (0,4)
2   f2          [1,3]
3   f3          (5,+np.inf)
4   f4          [0,10]

已知可以通过硬编码的方式实现：

df.query('f1>0 and f1<4 and f2>=1 and f2<=3 and f3>5 and f4>=0 and f4<=10')
# 或者
df.loc[df.f1.between(0,4,inclusive='neither')&df.f2.between(1,3)&df.f3.between(5,np.inf)&df.f4.between(0,10)]

但这种方式的缺点是当条件变更时需要修改代码，有没有更Pythonic的灵活处理方法？

方法1：动态生成query语句 #

遍历条件DataFrame自动拼接查询字符串，实现条件与代码解耦，后续只需修改条件DataFrame即可：

import pandas as pd
import numpy as np

# 原始数据
df = pd.DataFrame({
    'id': range(1,12),
    'f1': [8.327,3.549,0.011,0.325,7.093,2.88,5.554,2.501,1.032,9.68,8.096],
    'f2': [9.905,0.452,0.238,0.792,7.312,7.834,1.649,2.941,6.961,7.922,4.344],
    'f3': [8.133,7.798,1.291,4.643,3.641,5.727,4.018,9.323,3.905,7.015,1.153],
    'f4': [0.785,5.797,7.593,4.3,9.88,6.984,0.623,0.565,8.116,7.542,5.244]
})

# 条件DataFrame
cond_df = pd.DataFrame({
    'variable': ['f1','f2','f3','f4'],
    'interval': ['(0,4)','[1,3]','(5,+np.inf)','[0,10]']
})

# 生成查询条件片段
query_parts = []
for _, row in cond_df.iterrows():
    var = row['variable']
    interval = row['interval']
    left_sym = interval[0]
    right_sym = interval[-1]
    nums = interval[1:-1].split(',')
    left_val = nums[0].strip()
    right_val = nums[1].strip()
    
    # 根据区间符号拼接比较条件
    query_parts.append(f"{var}>{left_val}" if left_sym == '(' else f"{var}>={left_val}")
    query_parts.append(f"{var}<{right_val}" if right_sym == ')' else f"{var}<={right_val}")

# 合并所有条件并执行查询
query_str = ' and '.join(query_parts)
result = df.query(query_str)
print(result)

方法2：动态生成between布尔掩码 #

利用Pandas向量化操作，循环生成每个变量的过滤掩码后合并，性能更优：

import pandas as pd
import numpy as np

# 初始化全True掩码
mask = pd.Series([True]*len(df), index=df.index)

for _, row in cond_df.iterrows():
    var = row['variable']
    interval = row['interval']
    left_sym = interval[0]
    right_sym = interval[-1]
    nums = interval[1:-1].split(',')
    
    # 处理无穷大值
    left_val = float(nums[0].strip()) if nums[0].strip() != '+np.inf' else -np.inf
    right_val = float(nums[1].strip()) if nums[1].strip() != '+np.inf' else np.inf
    
    # 确定区间包含方式
    if left_sym == '[' and right_sym == ']':
        inclusive = 'both'
    elif left_sym == '(' and right_sym == ')':
        inclusive = 'neither'
    elif left_sym == '[' and right_sym == ')':
        inclusive = 'left'
    else:
        inclusive = 'right'
    
    # 更新掩码
    mask &= df[var].between(left_val, right_val, inclusive=inclusive)

result = df[mask]
print(result)

方法3：使用pd.Interval对象直接判断 #

将条件转换为Pandas区间对象，通过成员判断生成过滤掩码：

import pandas as pd
import numpy as np

# 转换条件为区间字典
interval_dict = {}
for _, row in cond_df.iterrows():
    var = row['variable']
    interval_str = row['interval']
    left_sym = interval_str[0]
    right_sym = interval_str[-1]
    nums = interval_str[1:-1].split(',')
    
    left = float(nums[0].strip()) if nums[0].strip() != '+np.inf' else -np.inf
    right = float(nums[1].strip()) if nums[1].strip() != '+np.inf' else np.inf
    
    # 确定区间闭合方式
    if left_sym == '[' and right_sym == ']':
        closed = 'both'
    elif left_sym == '(' and right_sym == ')':
        closed = 'neither'
    elif left_sym == '[' and right_sym == ')':
        closed = 'left'
    else:
        closed = 'right'
    
    interval_dict[var] = pd.Interval(left, right, closed=closed)

# 生成掩码：所有变量都满足区间条件
mask = pd.concat(
    [df[var].apply(lambda x: x in interval_dict[var]) for var in interval_dict],
    axis=1
).all(axis=1)

result = df[mask]
print(result)

以上三种方法均实现了条件与过滤逻辑的解耦，后续修改条件只需更新cond_df，无需改动核心代码，符合Pythonic的灵活扩展原则。

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