Pandas如何判断DataFrame单元格值是否落在另一DataFrame指定区间内

完整实现代码 #

import pandas as pd

# 示例数据，实际使用时替换为你自己的df1、df2即可
df1 = pd.DataFrame({
    'Source': ['dog', 'cat'],
    'Target': ['cat', 'rat'],
    'volume': [3, 2]
})
df2 = pd.DataFrame({
    'name': ['dog', 'cat'],
    'min': [1, 1],
    'max': [2, 3]
})

# 构建名称到min、max的映射字典，查询效率更高
name_to_min = df2.set_index('name')['min'].to_dict()
name_to_max = df2.set_index('name')['max'].to_dict()

# 处理Source对应字段
df1['S. min'] = df1['Source'].map(name_to_min).fillna('-')
df1['S. max'] = df1['Source'].map(name_to_max).fillna('-')
df1['S. in range'] = df1.apply(
    lambda row: row['volume'] >= row['S. min'] and row['volume'] <= row['S. max']
    if row['S. min'] != '-' else '-',
    axis=1
)

# 处理Target对应字段，逻辑和Source一致
df1['T. min'] = df1['Target'].map(name_to_min).fillna('-')
df1['T. max'] = df1['Target'].map(name_to_max).fillna('-')
df1['T. in range'] = df1.apply(
    lambda row: row['volume'] >= row['T. min'] and row['volume'] <= row['T. max']
    if row['T. min'] != '-' else '-',
    axis=1
)

# 调整为要求的输出列顺序
result = df1[['Source', 'Target', 'volume', 'S. in range', 'S. min', 'S. max', 'T. in range', 'T. min', 'T. max']]
print(result)

示例运行输出 #

Source Target  volume S. in range S. min S. max T. in range T. min T. max
0    dog    cat       3       False      1      2        True      1      3
1    cat    rat       2        True      1      3           -      -      -

大数据量优化方案 #

如果数据量超过10万行，建议替换apply为向量化操作，避免逐行计算的性能损耗：

# Source侧向量化判断
s_exist_mask = df1['S. min'] != '-'
df1.loc[s_exist_mask, 'S. in range'] = (df1.loc[s_exist_mask, 'volume'] >= df1.loc[s_exist_mask, 'S. min']) & (df1.loc[s_exist_mask, 'volume'] <= df1.loc[s_exist_mask, 'S. max'])
df1.loc[~s_exist_mask, 'S. in range'] = '-'

# Target侧向量化判断
t_exist_mask = df1['T. min'] != '-'
df1.loc[t_exist_mask, 'T. in range'] = (df1.loc[t_exist_mask, 'volume'] >= df1.loc[t_exist_mask, 'T. min']) & (df1.loc[t_exist_mask, 'volume'] <= df1.loc[t_exist_mask, 'T. max'])
df1.loc[~t_exist_mask, 'T. in range'] = '-'

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