如何基于分组列用3倍标准差替换所有列的异常值？

有一个DataFrame，需要按分组列对指定列的异常值进行替换/截断，用对应分组的3倍标准差边界值处理。示例数据及初始尝试代码如下：

import pandas as pd

df = pd.DataFrame({"A":["A", "A", "A", "A", "B","B","B","B","B","B","B","B","B","B","B","B"], 
                   "B":[7, 2, 54, 3, 5,23,5,7,7,7,7,7,7,7,6,7], 
                   "C":[20, 16, 11, 3, 8,5,5,20,6,6,6,6,6,5,6,6], 
                   "D":[14, 3, 32, 2, 6,5,6,20,4,5,4,5,4,5,5,5],
                }) 

feature=['B','C','D']

mean = df.groupby('A')[feature].mean()
std = df.groupby('A')[feature].std()

初始尝试用嵌套循环处理，但列数较多时循环耗时，希望找到更优方法或仅用单循环实现：

for col in feature:
 for each in df['A'].unique():
  m=mean.loc[each,col]
  s=std.loc[each,col]
  df.loc[each,df[col]< m-3*s,]=m-3*s

可以利用pandas的groupby.transform方法直接生成每个分组的均值和标准差的广播列，再用clip函数一次性完成截断操作，完全避免循环，效率大幅提升：

import pandas as pd

df = pd.DataFrame({"A":["A", "A", "A", "A", "B","B","B","B","B","B","B","B","B","B","B","B"], 
                   "B":[7, 2, 54, 3, 5,23,5,7,7,7,7,7,7,7,6,7], 
                   "C":[20, 16, 11, 3, 8,5,5,20,6,6,6,6,6,5,6,6], 
                   "D":[14, 3, 32, 2, 6,5,6,20,4,5,4,5,4,5,5,5],
                }) 

feature = ['B','C','D']

# 生成每个分组的均值和标准差，通过transform广播到原DataFrame的每一行
group_mean = df.groupby('A')[feature].transform('mean')
group_std = df.groupby('A')[feature].transform('std')

# 计算上下边界
lower_bound = group_mean - 3 * group_std
upper_bound = group_mean + 3 * group_std

# 对指定列进行截断，自动替换异常值
df[feature] = df[feature].clip(lower=lower_bound, upper=upper_bound)

print(df)

代码说明 #

• groupby.transform('mean')和groupby.transform('std')会将分组统计结果扩展为与原DataFrame同维度的结构，每行对应其所在分组的统计值，无需手动匹配分组。
• clip函数可直接按上下边界截断数据，自动把小于下界的值替换为下界、大于上界的值替换为上界，一行代码完成所有指定列的异常值处理，效率远高于嵌套循环。

如果仅考虑单循环优化（虽不如上述方法高效），可以按分组循环，一次性处理该分组下的所有指定列：

for group in df['A'].unique():
    mask = df['A'] == group
    group_data = df.loc[mask, feature]
    m = group_data.mean()
    s = group_data.std()
    df.loc[mask, feature] = group_data.clip(lower=m-3*s, upper=m+3*s)

这种方式将循环次数从列数×分组数减少到分组数，也能一定程度提升效率。

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