Pandas中多字符串匹配列后列值乘常量失效问题排查

问题重现 #

你尝试根据某列的字符串匹配结果，给另一列乘以对应常量，但运行后匹配行出现NaN，最终被填充为默认值。示例代码如下：

import pandas as pd

data = [{'Month': '2020-01-01', 'Expense':1000, 'Revenue':-50000, 'Building':'03 Tree'}, 
       {'Month': '2020-02-01', 'Expense':3000, 'Revenue':40000, 'Building':'17 Tree'},
       {'Month': '2020-03-01', 'Expense':7000, 'Revenue':50000, 'Building':'Tree fails'}, 
       {'Month': '2020-04-01', 'Expense':3000, 'Revenue':40000, 'Building':'overgrown primary'},
       {'Month': '2020-01-01', 'Expense':5000, 'Revenue':-6000, 'Building':'Tree fails'}, 
       {'Month': '2020-02-01', 'Expense':5000, 'Revenue':4000, 'Building':'26 Vines'},
       {'Month': '2020-03-01', 'Expense':5000, 'Revenue':9000, 'Building':'26 Vines'},
       {'Month': '2020-04-01', 'Expense':6000, 'Revenue':10000, 'Building':'Tree fails'}]
df = pd.DataFrame(data)

df['MaintCost'] = df.loc[df['Building'].str.contains('03 Tree','17 Tree'), 'Expense'] * 15
df['MaintCost'] = df.loc[df['Building'].str.contains('26 Vines'), 'Expense'] * 5
df['MaintCost'] = df.loc[df['Building'].str.contains('overgrown primary', 'Tree fails'), 'Expense'] * 12
df['MaintCost'] = df.loc[df['Building'].str.contains('Tree fails'), 'Expense'] * 10

df['MaintCost'] = df['MaintCost'].fillna(100)
print(df)

运行后第0行本该得到15000，却被填充为100。

问题原因 #

1. str.contains参数使用错误 #

str.contains的第二个参数不是第二个匹配字符串，而是用于设置匹配规则的参数（比如case控制大小写、flags设置正则标志）。你想匹配多个字符串时，应该用正则表达式的|分隔多个匹配项，比如'03 Tree|17 Tree'，同时建议加上na=False避免空值导致的匹配失败。

原代码中df['Building'].str.contains('03 Tree','17 Tree')实际上是把'17 Tree'当作case参数传入（case默认是True），这根本不会匹配到'17 Tree'这个字符串，导致第一行的匹配逻辑就失效了。

2. 整列赋值覆盖了之前的结果 #

每次执行df['MaintCost'] = ...时，都会覆盖整列的值：

• 第一行代码仅给匹配'03 Tree'的行赋值，其他行都是NaN；
• 第二行代码仅给匹配'26 Vines'的行赋值，其他行（包括之前已经赋值的'03 Tree'行）被重置为NaN；
• 后续代码同理，最终只有最后一次匹配的行有值，其他全是NaN，最后被fillna(100)替换成默认值。

解决方案 #

方案1：用np.select批量处理多条件 #

这种方式更清晰，适合多条件场景：

import pandas as pd
import numpy as np

data = [{'Month': '2020-01-01', 'Expense':1000, 'Revenue':-50000, 'Building':'03 Tree'}, 
       {'Month': '2020-02-01', 'Expense':3000, 'Revenue':40000, 'Building':'17 Tree'},
       {'Month': '2020-03-01', 'Expense':7000, 'Revenue':50000, 'Building':'Tree fails'}, 
       {'Month': '2020-04-01', 'Expense':3000, 'Revenue':40000, 'Building':'overgrown primary'},
       {'Month': '2020-01-01', 'Expense':5000, 'Revenue':-6000, 'Building':'Tree fails'}, 
       {'Month': '2020-02-01', 'Expense':5000, 'Revenue':4000, 'Building':'26 Vines'},
       {'Month': '2020-03-01', 'Expense':5000, 'Revenue':9000, 'Building':'26 Vines'},
       {'Month': '2020-04-01', 'Expense':6000, 'Revenue':10000, 'Building':'Tree fails'}]
df = pd.DataFrame(data)

# 定义匹配条件和对应计算值
conditions = [
    df['Building'].str.contains('03 Tree|17 Tree', na=False),
    df['Building'].str.contains('26 Vines', na=False),
    df['Building'].str.contains('overgrown primary', na=False),
    df['Building'].str.contains('Tree fails', na=False)
]
values = [
    df['Expense'] * 15,
    df['Expense'] * 5,
    df['Expense'] * 12,
    df['Expense'] * 10
]

# 赋值，默认值设为100
df['MaintCost'] = np.select(conditions, values, default=100)

print(df)

方案2：先设默认值，再逐个更新行 #

这种方式更直观，适合逐步调试：

import pandas as pd

data = [{'Month': '2020-01-01', 'Expense':1000, 'Revenue':-50000, 'Building':'03 Tree'}, 
       {'Month': '2020-02-01', 'Expense':3000, 'Revenue':40000, 'Building':'17 Tree'},
       {'Month': '2020-03-01', 'Expense':7000, 'Revenue':50000, 'Building':'Tree fails'}, 
       {'Month': '2020-04-01', 'Expense':3000, 'Revenue':40000, 'Building':'overgrown primary'},
       {'Month': '2020-01-01', 'Expense':5000, 'Revenue':-6000, 'Building':'Tree fails'}, 
       {'Month': '2020-02-01', 'Expense':5000, 'Revenue':4000, 'Building':'26 Vines'},
       {'Month': '2020-03-01', 'Expense':5000, 'Revenue':9000, 'Building':'26 Vines'},
       {'Month': '2020-04-01', 'Expense':6000, 'Revenue':10000, 'Building':'Tree fails'}]
df = pd.DataFrame(data)

# 先设置默认值
df['MaintCost'] = 100

# 逐个匹配更新，不会覆盖其他行
df.loc[df['Building'].str.contains('03 Tree|17 Tree', na=False), 'MaintCost'] = df['Expense'] * 15
df.loc[df['Building'].str.contains('26 Vines', na=False), 'MaintCost'] = df['Expense'] * 5
df.loc[df['Building'].str.contains('overgrown primary', na=False), 'MaintCost'] = df['Expense'] * 12
df.loc[df['Building'].str.contains('Tree fails', na=False), 'MaintCost'] = df['Expense'] * 10

print(df)

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