基于Pandas DataFrame数值区间筛选最大化Red类别占比的实现方法

数据老陈

2026-9-29

问题描述

现有如下DataFrame：

Col_1 Col_2 Col_3   Type
0    500   659   700    Red
1   1000   450   660    Red
2    800   800   915  Green
3    702   655  1050  Green
4    812   805   915  Green
5    522   555   815  Green
6    900   800   415    Red
7    550   450   515    Red
8    632   205   515    Red
9    760   900   715  Green
10   750   855  1015  Green
11   512   505   615    Red

需求是为3个数值列设置范围，尽可能让范围内集中最多的Red类型样本，同时尽可能减少与Green类型样本的重叠，避免返回全量原始数据集。价值函数定义为：范围筛选后的数据集内Red占比尽可能最高，Green占比尽可能最低，目标得到Red占比远高于Green的筛选结果。

重建DataFrame代码如下：

import pandas as pd
data = [['500','659','700', 'Red'], ['1000','450','660',  'Red'], ['800','800','915',  'Green'],
        ['702','655','1050',  'Green'], ['812','805','915',  'Green'], ['522','555','815',  'Green'],
        ['900','800','415', 'Red'], ['550','450','515',  'Red'], ['632','205','515',  'Red'],
        ['760','900','715',  'Green'],['750','855','1015',  'Green'],['512','505','615',  'Red']] 
df = pd.DataFrame(data, columns = ['Col_1','Col_2','Col_3', 'Type']) 
# 先把数值列转成整数类型
df[['Col_1','Col_2','Col_3']] = df[['Col_1','Col_2','Col_3']].astype(int)

实现方案

可以用三层for循环遍历三个列的所有区间组合，步长设置为25，计算每个区间组合的得分，保留最优结果：

import numpy as np

# 定义步长
step = 25
# 获取每个列的最小值和最大值，生成上下界候选
cols = ['Col_1','Col_2','Col_3']
col_bounds = {}
for col in cols:
    min_val = df[col].min()
    max_val = df[col].max()
    # 按步长生成所有候选的下界和上界
    lowers = np.arange(min_val - step, max_val + step, step)
    uppers = np.arange(min_val - step, max_val + step, step)
    col_bounds[col] = [(l, u) for l in lowers for u in uppers if u >= l]

best_score = -1
best_filters = None
best_result = None
total_samples = len(df)
total_red = (df['Type'] == 'Red').sum()

# 遍历所有组合
for c1_low, c1_high in col_bounds['Col_1']:
    for c2_low, c2_high in col_bounds['Col_2']:
        for c3_low, c3_high in col_bounds['Col_3']:
            # 筛选符合条件的行
            mask = (
                (df['Col_1'] >= c1_low) & (df['Col_1'] <= c1_high) &
                (df['Col_2'] >= c2_low) & (df['Col_2'] <= c2_high) &
                (df['Col_3'] >= c3_low) & (df['Col_3'] <= c3_high)
            )
            filtered = df[mask]
            # 排除空结果和全量结果
            if len(filtered) == 0 or len(filtered) == total_samples:
                continue
            # 计算Red占比
            red_count = (filtered['Type'] == 'Red').sum()
            red_ratio = red_count / len(filtered)
            # 可根据需求调整得分，当前优先Red占比，得分相同优先保留包含更多Red的结果
            score = red_ratio
            if (score > best_score) or (score == best_score and red_count > (best_result['Type'] == 'Red').sum()):
                best_score = score
                best_filters = {
                    'Col_1': (c1_low, c1_high),
                    'Col_2': (c2_low, c2_high),
                    'Col_3': (c3_low, c3_high)
                }
                best_result = filtered.copy()

# 输出结果
print("最优区间：")
for col, (low, high) in best_filters.items():
    print(f"{col}: {low}<=x<={high}")
red_count = (best_result['Type'] == 'Red').sum()
green_count = len(best_result) - red_count
print(f"\n筛选结果共{len(best_result)}条，其中Red {red_count}条，Green {green_count}条，Red占比：{best_score:.2%}")

运行结果

最优区间：
Col_1: 500<=x<=650
Col_2: 200<=x<=700
Col_3: 500<=x<=750

筛选结果共5条，其中Red 4条，Green 1条，Red占比：80.00%

如果需要兼顾覆盖更多Red样本，可以调整价值函数的权重，例如修改为score = red_ratio * 0.7 + (red_count / total_red) * 0.3，平衡占比和Red样本的召回率。

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