如何基于另一个DataFrame的阈值过滤Pandas目标DataFrame

你可以通过两种简洁的方式实现需求：

方法一：利用映射（Map）直接生成过滤掩码 #

这种方法无需合并DataFrame，直接通过字典映射匹配对应Type的阈值，执行效率更高：

import pandas as pd

df1 = pd.DataFrame({'Type': ['car', 'bike','car', 'motorbike','bike','bike'], 'Amount': [40,3,50, 699,60,90], 'Count': [10,1,2,4,3,2],'Item': ['a','b','t','u','i','e']})
df2 = pd.DataFrame({'Parameters': ['Amount', 'Count'], 'car': [30, 3], 'bike': [40,4], 'motorbike': [1000,7]}) 

# 从df2提取各Type的阈值字典
amount_thresholds = df2.set_index('Parameters').T['Amount'].to_dict()
count_thresholds = df2.set_index('Parameters').T['Count'].to_dict()

# 生成过滤条件掩码
mask = (df1['Amount'] > df1['Type'].map(amount_thresholds)) & (df1['Count'] < df1['Type'].map(count_thresholds))

# 应用掩码得到结果
dfTotal = df1[mask]
print(dfTotal)

方法二：合并DataFrame后过滤 #

这种方法逻辑更直观，适合需要查看每行对应阈值的场景：

import pandas as pd

df1 = pd.DataFrame({'Type': ['car', 'bike','car', 'motorbike','bike','bike'], 'Amount': [40,3,50, 699,60,90], 'Count': [10,1,2,4,3,2],'Item': ['a','b','t','u','i','e']})
df2 = pd.DataFrame({'Parameters': ['Amount', 'Count'], 'car': [30, 3], 'bike': [40,4], 'motorbike': [1000,7]}) 

# 转换df2结构，让Type作为匹配列，阈值列命名更清晰
threshold_df = df2.set_index('Parameters').T.rename_axis('Type').reset_index()
threshold_df.columns = ['Type', 'Amount_threshold', 'Count_threshold']

# 合并df1和阈值表
merged_df = df1.merge(threshold_df, on='Type')

# 应用过滤条件并移除阈值列
dfTotal = merged_df[(merged_df['Amount'] > merged_df['Amount_threshold']) & (merged_df['Count'] < merged_df['Count_threshold'])].drop(columns=['Amount_threshold', 'Count_threshold'])
print(dfTotal)

两种方法最终输出都与你期望的结果一致：

Type  Amount  Count Item
2     car      50      2    t
4    bike      60      3    i
5    bike      90      2    e

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