如何用Pandas按年月均值高效填充DataFrame的NaN与异常值？

我有一个包含NaN值和异常值的Pandas DataFrame，结构如下：

df[["arrival_day", "ib_units", "month","year"]]

            arrival_day     ib_units    month   year
 37         2020-01-01      262           1     2020
235         2020-01-02      2301          1     2020
290         2020-01-02      145           1     2020
476         2020-01-02      6584          1     2020
551         2020-01-02      30458         1     2020
    ...           ...       ...     ...     ...
1479464     2022-07-19      56424         7     2022
1479490     2022-07-19      130090        7     2022
1479510     2022-07-19      3552          7     2022
1479556     2022-07-19      23779         7     2022
1479756     2022-07-20      2882          7     2022

需求是：

• 用对应年份+月份的均值填充ib_units列的NaN值
• 用同组均值替换该组内的异常值（基于四分位距IQR判断）

现有代码仅按年份处理，冗余且无法实现按月分组，希望得到更高效的实现方式。

可以通过Pandas的groupby分组统计、合并数据以及向量化操作来实现，避免冗余循环：

步骤1：计算年月分组的统计量 #

先按year和month分组，计算每组的均值、四分位距（IQR）及异常值阈值：

import pandas as pd
import numpy as np

# 按年份+月份分组，计算所需统计量
group_stats = df.groupby(['year', 'month'])['ib_units'].agg(
    group_mean='mean',
    q25=lambda x: np.percentile(x, 25),
    q75=lambda x: np.percentile(x, 75)
).reset_index()

# 计算异常值的上下限
group_stats['iqr'] = group_stats['q75'] - group_stats['q25']
group_stats['max_val'] = group_stats['q75'] + 1.5 * group_stats['iqr']
group_stats['min_val'] = group_stats['q25'] - 1.5 * group_stats['iqr']

步骤2：合并统计量到原DataFrame #

将分组统计结果与原DataFrame合并，让每条数据都能匹配到所属年月的统计值：

df_merged = df.merge(group_stats, on=['year', 'month'], how='left')

步骤3：填充NaN值 #

用对应年月的均值填充ib_units的缺失值：

df_merged['ib_units'] = df_merged['ib_units'].fillna(df_merged['group_mean'])

步骤4：替换异常值 #

用同组均值替换超出阈值的异常值：

# 替换低于下限的异常值
df_merged.loc[df_merged['ib_units'] < df_merged['min_val'], 'ib_units'] = df_merged['group_mean']
# 替换高于上限的异常值
df_merged.loc[df_merged['ib_units'] > df_merged['max_val'], 'ib_units'] = df_merged['group_mean']

完整代码 #

import pandas as pd
import numpy as np

# 1. 计算年月分组统计量
group_stats = df.groupby(['year', 'month'])['ib_units'].agg(
    group_mean='mean',
    q25=lambda x: np.percentile(x, 25),
    q75=lambda x: np.percentile(x, 75)
).reset_index()
group_stats['iqr'] = group_stats['q75'] - group_stats['q25']
group_stats['max_val'] = group_stats['q75'] + 1.5 * group_stats['iqr']
group_stats['min_val'] = group_stats['q25'] - 1.5 * group_stats['iqr']

# 2. 合并统计量到原数据
df_merged = df.merge(group_stats, on=['year', 'month'], how='left')

# 3. 填充NaN值
df_merged['ib_units'] = df_merged['ib_units'].fillna(df_merged['group_mean'])

# 4. 替换异常值
df_merged.loc[df_merged['ib_units'] < df_merged['min_val'], 'ib_units'] = df_merged['group_mean']
df_merged.loc[df_merged['ib_units'] > df_merged['max_val'], 'ib_units'] = df_merged['group_mean']

# 可选：删除临时统计列
final_df = df_merged.drop(['group_mean', 'q25', 'q75', 'iqr', 'max_val', 'min_val'], axis=1)

方案优势 #

• 避免了按年份拆分数据的冗余循环，代码更简洁易维护
• 基于groupby和向量化操作，执行效率远高于逐组循环
• 精准实现年份+月份维度的分组处理，满足需求

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