Pandas使用np.select批量更新列时仅最后条件生效问题求助

现有名为Trial.xlsx的Excel文件，包含prod_code列（900+条数据），需要用Pandas新增sub_category列，根据prod_code的字符串值匹配填充分类内容，最终写入新Excel。
使用np.select设置多组条件赋值时，发现仅最后一组条件生效，sub_category列只有对应最后一组条件的数据，其余均为0，代码如下：

import pandas as pd
import numpy as np

database = pd.read_excel("Trial.xlsx")
database_df = pd.DataFrame(database)
database_df['sub-category'] = ""

conditions_vehicle = [
    database_df['prod_code'] == 'CLASSIC_CAR',
    (database_df['prod_code'] == 'CLASSIC_CYCLE') | (database_df['prod_code'] == 'CLASSIC_TAGGED_ONTRACK_ACTIVA6G'),
    (database_df['prod_code'] == 'CLASSIC_ELECTRIC_BIKE') | (database_df['prod_code']== 'CLASSIC_TAGGED_ATHER')]
choices_vehicle = ['4W','2W','EV-2W']
database_df['sub-category'] = np.select(conditions_vehicle, choices_vehicle)

conditions_jewellery = [
    (database_df['prod_code'] == 'CLASSIC_TAGGED_BLUESTONE_DIAMOND') | (database_df['prod_code'] == 'CLASSIC_TAGGED_BLUESTONE_GOLD'),
    database_df['prod_code'] == 'CLASSIC_JEWELLERY',
    database_df['prod_code'] == 'CLASSIC_TAGGED_KALYANJEWELLERY_QC',
    database_df['prod_code'] == 'CLASSIC_TAGGED_PCJDIAMOND_QC',
    (database_df['prod_code'] == 'CLASSIC_AKSHAYA_TRITYIYA_2021') | (database_df['prod_code'] == 'CLASSIC_ANNIVERSARY_JEWELLERY') | (database_df['prod_code'] == 'CLASSIC_WEDDING_JEWELLERY'),
    (database_df['prod_code'] == 'CLASSIC_TAGGED_TANISHQ_QC')]
choices_jewellery = ['Blue stone jewellers', 'General Jewellery', 'Kalyan jewellers', 'PCJ jewellers', 'Special Occasion', 'Tanishq jewellers']
database_df['sub-category'] = np.select(conditions_jewellery, choices_jewellery)
database_df.to_excel("Final_data.xlsx", sheet_name='Test_data',index=False)

问题原因 #

核心问题是两次赋值覆盖了结果：第一次用np.select给sub-category列赋值后，第二次又对同一列重新赋值，直接覆盖了第一次的结果。同时np.select默认default=0，不匹配最后一组条件的行就会被设为0。

解决方案 #

方案1：合并所有条件与选项，单次调用np.select

把车辆、珠宝的所有条件和对应选项合并成统一列表，一次调用np.select，同时指定默认值（比如空字符串）避免出现0。

import pandas as pd
import numpy as np

# 读取数据
database_df = pd.read_excel("Trial.xlsx")

# 合并所有分类条件与对应选项
all_conditions = [
    # 车辆类条件
    database_df['prod_code'] == 'CLASSIC_CAR',
    (database_df['prod_code'] == 'CLASSIC_CYCLE') | (database_df['prod_code'] == 'CLASSIC_TAGGED_ONTRACK_ACTIVA6G'),
    (database_df['prod_code'] == 'CLASSIC_ELECTRIC_BIKE') | (database_df['prod_code']== 'CLASSIC_TAGGED_ATHER'),
    # 珠宝类条件
    (database_df['prod_code'] == 'CLASSIC_TAGGED_BLUESTONE_DIAMOND') | (database_df['prod_code'] == 'CLASSIC_TAGGED_BLUESTONE_GOLD'),
    database_df['prod_code'] == 'CLASSIC_JEWELLERY',
    database_df['prod_code'] == 'CLASSIC_TAGGED_KALYANJEWELLERY_QC',
    database_df['prod_code'] == 'CLASSIC_TAGGED_PCJDIAMOND_QC',
    (database_df['prod_code'] == 'CLASSIC_AKSHAYA_TRITYIYA_2021') | (database_df['prod_code'] == 'CLASSIC_ANNIVERSARY_JEWELLERY') | (database_df['prod_code'] == 'CLASSIC_WEDDING_JEWELLERY'),
    (database_df['prod_code'] == 'CLASSIC_TAGGED_TANISHQ_QC')
]

all_choices = [
    # 车辆类选项
    '4W','2W','EV-2W',
    # 珠宝类选项
    'Blue stone jewellers', 'General Jewellery', 'Kalyan jewellers', 'PCJ jewellers', 'Special Occasion', 'Tanishq jewellers'
]

# 单次调用np.select，指定默认值为空字符串
database_df['sub-category'] = np.select(all_conditions, all_choices, default="")

# 写入结果
database_df.to_excel("Final_data.xlsx", sheet_name='Test_data', index=False)

方案2：使用字典映射（更简洁易维护）

用字典建立prod_code到分类的映射关系，通过map方法批量赋值，不匹配的行用fillna设为默认值。这种方式逻辑更清晰，后续新增或修改分类时只需调整字典即可。

import pandas as pd

# 读取数据
database_df = pd.read_excel("Trial.xlsx")

# 建立prod_code与分类的映射字典
prod_category_map = {
    # 车辆类
    'CLASSIC_CAR': '4W',
    'CLASSIC_CYCLE': '2W',
    'CLASSIC_TAGGED_ONTRACK_ACTIVA6G': '2W',
    'CLASSIC_ELECTRIC_BIKE': 'EV-2W',
    'CLASSIC_TAGGED_ATHER': 'EV-2W',
    # 珠宝类
    'CLASSIC_TAGGED_BLUESTONE_DIAMOND': 'Blue stone jewellers',
    'CLASSIC_TAGGED_BLUESTONE_GOLD': 'Blue stone jewellers',
    'CLASSIC_JEWELLERY': 'General Jewellery',
    'CLASSIC_TAGGED_KALYANJEWELLERY_QC': 'Kalyan jewellers',
    'CLASSIC_TAGGED_PCJDIAMOND_QC': 'PCJ jewellers',
    'CLASSIC_AKSHAYA_TRITYIYA_2021': 'Special Occasion',
    'CLASSIC_ANNIVERSARY_JEWELLERY': 'Special Occasion',
    'CLASSIC_WEDDING_JEWELLERY': 'Special Occasion',
    'CLASSIC_TAGGED_TANISHQ_QC': 'Tanishq jewellers'
}

# 映射赋值，不匹配的行填充空字符串
database_df['sub-category'] = database_df['prod_code'].map(prod_category_map).fillna("")

# 写入结果
database_df.to_excel("Final_data.xlsx", sheet_name='Test_data', index=False)

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