如何在Python中基于多条件创建新列及批量优化方法

原始DataFrame：

year    type1   type2   price
    2015    apple   natural 40
    2015    apple   organic 35
    2016    apple   natural 44
    2016    apple   organic 40
    2015    banana  natural 20
    2015    banana  organic 15
    2016    banana  natural 20
    2016    banana  organic 18

需求：基于year、type1、type2的条件创建新列price_new：当匹配对应year、type1、type2组合时填充指定新值，否则保留原price值。

用户尝试的代码：

df["price_new"] = np.where(((df["year"] == 2015) & (
                    df["type1"] == "apple") & (df["type2"].isin(['natural']))),
                                                    25, df["price"])
df["price_new"] = np.where(((df["year"] == 2016) & (
                    df["type1"] == "apple") & (df["type2"].isin(['natural']))),
                                                    26, df["price"])

df["price_new"] = np.where(((df["year"] == 2015) & (
                    df["type1"] == "apple") & (~df["type2"].isin(['natural']))),
                                                    20, df["price"])

df["price_new"] = np.where(((df["year"] == 2016) & (
                    df["type1"] == "apple") & (~df["type2"].isin(['natural']))),
                                                    22, df["price"])

期望输出：

year    type1   type2   price   price_new
2015    apple   natural 40     25
2015    apple   organic 35     20
2016    apple   natural 44     26
2016    apple   organic 40     22
2015    banana  natural 20
2015    banana  organic 15
2016    banana  natural 20
2016    banana  organic 18

实际输出：

year   type1   type2   price   price_new
    2015    apple   natural 40     40
    2015    apple   organic 35     35
    2016    apple   natural 44     44
    2016    apple   organic 40     22
    2015    banana  natural 20
    2015    banana  organic 15
    2016    banana  natural 20
    2016    banana  organic 18

需要解决两个问题：

1. 如何让price_new列应用所有条件的新值？
2. 当type1列有10+种取值时，如何高效实现而非逐个编写条件？

问题1：让所有条件生效 #

你当前的写法每次调用np.where都直接用原price列覆盖price_new，导致前面的条件被后续操作覆盖，仅最后一条规则生效。要让所有规则生效，需基于上一次更新后的price_new值继续判断。

方法1：分步更新（可读性高） #

先初始化price_new为原price，再依次应用每个规则：

import numpy as np
import pandas as pd

# 初始化price_new为原price值
df["price_new"] = df["price"]

# 依次应用规则，每次基于已更新的price_new值操作
df["price_new"] = np.where(
    (df["year"] == 2015) & (df["type1"] == "apple") & (df["type2"] == "natural"),
    25, df["price_new"]
)
df["price_new"] = np.where(
    (df["year"] == 2016) & (df["type1"] == "apple") & (df["type2"] == "natural"),
    26, df["price_new"]
)
df["price_new"] = np.where(
    (df["year"] == 2015) & (df["type1"] == "apple") & (df["type2"] == "organic"),
    20, df["price_new"]
)
df["price_new"] = np.where(
    (df["year"] == 2016) & (df["type1"] == "apple") & (df["type2"] == "organic"),
    22, df["price_new"]
)

方法2：嵌套np.where（一次性完成） #

把所有规则嵌套在一个np.where中，避免多次赋值：

df["price_new"] = np.where(
    (df["year"] == 2015) & (df["type1"] == "apple") & (df["type2"] == "natural"),
    25,
    np.where(
        (df["year"] == 2016) & (df["type1"] == "apple") & (df["type2"] == "natural"),
        26,
        np.where(
            (df["year"] == 2015) & (df["type1"] == "apple") & (df["type2"] == "organic"),
            20,
            np.where(
                (df["year"] == 2016) & (df["type1"] == "apple") & (df["type2"] == "organic"),
                22,
                df["price"]
            )
        )
    )
)

问题2：高效处理大量type1取值 #

当type1取值较多时，逐个写条件效率极低，推荐两种批量处理方式：

方法1：映射字典+loc批量更新 #

把所有规则整理成字典，键为(year, type1, type2)元组，值为对应新价格，遍历字典批量更新：

# 定义规则字典，可随时新增/修改规则
price_map = {
    (2015, "apple", "natural"): 25,
    (2016, "apple", "natural"): 26,
    (2015, "apple", "organic"): 20,
    (2016, "apple", "organic"): 22,
    # 新增其他type1的规则示例
    # (2015, "banana", "natural"): 18,
    # (2016, "banana", "organic"): 16
}

# 初始化price_new
df["price_new"] = df["price"]

# 遍历字典批量更新
for (year, t1, t2), new_price in price_map.items():
    df.loc[(df["year"] == year) & (df["type1"] == t1) & (df["type2"] == t2), "price_new"] = new_price

方法2：规则DataFrame合并更新 #

如果规则数量极大，可将规则整理为单独的DataFrame，通过merge关联更新：

# 创建规则DataFrame，可从CSV/Excel导入
rule_df = pd.DataFrame({
    "year": [2015, 2016, 2015, 2016],
    "type1": ["apple", "apple", "apple", "apple"],
    "type2": ["natural", "natural", "organic", "organic"],
    "price_new": [25, 26, 20, 22]
})

# 左连接保留原始所有数据，匹配规则填充新值
df = df.merge(rule_df, on=["year", "type1", "type2"], how="left")
# 未匹配到规则的行，用原price填充
df["price_new"] = df["price_new"].fillna(df["price"])

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