Pandas如何按values列选前11行且保证11个0-1列无全零

实现思路 #

这个需求本质是带权重的集合覆盖问题变种：我们需要选11行，硬约束是覆盖全部11个0-1列（每列至少有一个1），软约束是选中行的values总和尽可能高（等价于优先选values更高的行）。可以用贪心算法实现，效率高且能满足通用需求：

• 第一步：先把全表按values列降序排序，保证我们优先判断数值更高的行
• 第二步：贪心迭代选行：每次优先选「能覆盖最多未覆盖的0-1列」的行，覆盖数相同时选values更高的行，直到所有0-1列都被覆盖
• 第三步：如果选完覆盖所有列的行之后不足11行，从剩余未选中的行里按values降序补够11行即可

代码实现 #

import pandas as pd

# 1. 读取/构造你的DataFrame（这里用你提供的示例数据）
df = pd.DataFrame([
    [0.0,0.0,0.0,1.0,0.0,0.0,0.0,1.0,0.0,0.0,0.0,1.193744,1],
    [0.0,0.0,0.0,1.0,0.0,0.0,0.0,0.0,1.0,0.0,0.0,1.193744,2],
    [0.0,0.0,0.0,1.0,0.0,0.0,0.0,0.0,0.0,0.0,1.0,1.193744,3],
    [0.0,0.0,0.0,1.0,0.0,0.0,0.0,0.0,0.0,1.0,0.0,1.193744,4],
    [0.0,0.0,0.0,1.0,0.0,0.0,1.0,0.0,0.0,0.0,0.0,1.193744,5],
    [0.0,0.0,0.0,1.0,0.0,1.0,0.0,0.0,0.0,0.0,0.0,1.193744,6],
    [0.0,0.0,0.0,1.0,1.0,0.0,0.0,0.0,0.0,0.0,0.0,1.193744,7],
    [0.0,0.0,1.0,0.0,0.0,0.0,0.0,0.0,1.0,0.0,0.0,0.633150,8],
    [0.0,0.0,1.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,1.0,0.633150,9],
    [0.0,0.0,1.0,0.0,0.0,0.0,0.0,0.0,0.0,1.0,0.0,0.633150,10],
    [0.0,0.0,1.0,0.0,0.0,0.0,1.0,0.0,0.0,0.0,0.0,0.633150,11],
    [0.0,0.0,1.0,0.0,0.0,0.0,0.0,1.0,0.0,0.0,0.0,0.633150,12],
    [0.0,0.0,1.0,0.0,1.0,0.0,0.0,0.0,0.0,0.0,0.0,0.033640,13],
    [0.0,0.0,1.0,0.0,0.0,1.0,0.0,0.0,0.0,0.0,0.0,0.033640,14],
    [0.0,1.0,0.0,0.0,0.0,0.0,0.0,1.0,0.0,0.0,0.0,0.033640,15],
    [1.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,1.0,0.0,0.0,0.033640,16],
    [0.0,1.0,0.0,0.0,0.0,0.0,1.0,0.0,0.0,0.0,0.0,0.033640,17],
    [1.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,1.0,0.0,0.033640,18],
    [0.0,1.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,1.0,0.033640,19],
    [1.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,1.0,0.033640,20],
    [0.0,1.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,1.0,0.0,0.033640,21],
    [0.0,1.0,0.0,0.0,0.0,0.0,0.0,0.0,1.0,0.0,0.0,0.033640,22],
    [0.0,1.0,0.0,0.0,1.0,0.0,0.0,0.0,0.0,0.0,0.0,0.033640,23],
    [0.0,1.0,0.0,0.0,0.0,1.0,0.0,0.0,0.0,0.0,0.0,0.033640,24],
    [1.0,0.0,0.0,0.0,0.0,0.0,1.0,0.0,0.0,0.0,0.0,0.033640,25],
    [1.0,0.0,0.0,0.0,0.0,0.0,1.0,0.0,0.0,0.0,0.0,-0.279495,26],
    [1.0,0.0,0.0,0.0,0.0,1.0,0.0,0.0,0.0,0.0,0.0,-3.013531,27],
    [1.0,0.0,0.0,0.0,1.0,0.0,0.0,0.0,0.0,0.0,0.0,-3.013531,28]
], columns=['a','b','c','d','e','f','g','h','i','j','k','values','ID'])

# 2. 配置参数：指定0-1列、需要选的行数
cols_01 = ['a','b','c','d','e','f','g','h','i','j','k']
n_select = 11

# 3. 按values降序排序
df_sorted = df.sort_values('values', ascending=False).reset_index(drop=True)

# 4. 贪心选行满足覆盖约束
covered = set()
selected_idx = []

while len(covered) < len(cols_01) and len(selected_idx) < n_select:
    best_score = -1
    best_idx = None
    # 遍历所有未选中的行
    for idx, row in df_sorted.iterrows():
        if idx in selected_idx:
            continue
        # 计算当前行能覆盖的未覆盖列数量
        cover_cnt = sum([row[col] == 1 and col not in covered for col in cols_01])
        # 优先选覆盖数多的，覆盖数相同的话因为已经按values排序，先出现的就是数值更高的
        if cover_cnt > best_score:
            best_score = cover_cnt
            best_idx = idx
    if best_idx is None:
        break # 没有更多行可以选，说明数据本身无法满足覆盖约束
    selected_idx.append(best_idx)
    # 更新已覆盖的列
    for col in cols_01:
        if df_sorted.loc[best_idx, col] == 1:
            covered.add(col)

# 5. 不够11行的话补剩下values最高的行
if len(selected_idx) < n_select:
    remain_idx = [idx for idx in df_sorted.index if idx not in selected_idx]
    selected_idx += remain_idx[:n_select - len(selected_idx)]

# 6. 得到最终结果
result = df_sorted.loc[selected_idx].reset_index(drop=True)

# 验证硬约束：所有0-1列的和都>=1
assert (result[cols_01].sum() >= 1).all(), "数据无法满足硬约束：存在0-1列全为0"
print(result)

适用说明 #

上述代码是通用实现：如果后续values列动态变化、或者0-1列数量/选中行数有调整，只需要修改cols_01和n_select两个配置参数，重新运行代码即可得到符合要求的结果。

内容的提问来源于stack exchange，提问作者Waqar ul islam