如何在Pandas DataFrame中根据前三列值生成Secure列？

需求说明 #

给定如下Pandas DataFrame：

需要根据前三列（GovKeepSecure、BankKeepSecure、OtherKeepSecure）生成Secure列，规则如下：

• 同一行前三列中有2个及以上No，则Secure为No
• 同一行前三列中有2个及以上Yes，则Secure为Yes
• 不满足上述条件时，默认设为Neutral

原有实现的问题 #

你当前的实现需要枚举所有可能的列值组合，不仅代码繁琐，而且后续规则调整时维护成本极高：

import pandas as pd

def secure(row):
    if row["GovKeepSecure", "BankKeepSecure", OtherKeepSecure] == ["Yes", "Yes", "Yes"]:
             return "Yes"
    if row["GovKeepSecure", "BankKeepSecure", OtherKeepSecure] == ["Yes", "Yes", "No"]:
             return "Yes"
# ... 省略大量枚举代码
df["Secure"] = df.apply(lambda row: secure(row), axis=1)

优化实现方案 #

下面两种方案均采用向量化操作，比逐行apply效率更高，代码简洁易维护：

方法1：统计数量后批量赋值 #

import pandas as pd

# 构造示例数据
data = {
    "GovKeepSecure": ["Yes", "No", "No"],
    "BankKeepSecure": ["Yes", "No", "Neutral"],
    "OtherKeepSecure": ["Yes", "Yes", "Yes"]
}
df = pd.DataFrame(data)

# 指定需要判断的目标列
target_cols = ["GovKeepSecure", "BankKeepSecure", "OtherKeepSecure"]

# 统计每行中"Yes"和"No"的数量
yes_count = df[target_cols].eq("Yes").sum(axis=1)
no_count = df[target_cols].eq("No").sum(axis=1)

# 生成Secure列：先默认设为Neutral，再覆盖满足条件的行
df["Secure"] = "Neutral"
df.loc[yes_count >= 2, "Secure"] = "Yes"
df.loc[no_count >= 2, "Secure"] = "No"

print(df)

方法2：使用numpy.select处理多条件 #

如果后续规则扩展，这种方式的可读性更强：

import pandas as pd
import numpy as np

# 构造示例数据
data = {
    "GovKeepSecure": ["Yes", "No", "No"],
    "BankKeepSecure": ["Yes", "No", "Neutral"],
    "OtherKeepSecure": ["Yes", "Yes", "Yes"]
}
df = pd.DataFrame(data)

target_cols = ["GovKeepSecure", "BankKeepSecure", "OtherKeepSecure"]
yes_count = df[target_cols].eq("Yes").sum(axis=1)
no_count = df[target_cols].eq("No").sum(axis=1)

# 定义条件列表和对应结果
conditions = [
    no_count >= 2,
    yes_count >= 2
]
choices = [
    "No",
    "Yes"
]

# 应用规则，默认返回Neutral
df["Secure"] = np.select(conditions, choices, default="Neutral")

print(df)

方案优势 #

• 无需枚举所有组合，代码量大幅减少
• 向量化操作比apply效率提升明显，尤其适合大数据集
• 规则调整简单（比如修改阈值只需更改数字即可）

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