多索引透视表中按指定索引列均值填充in_dist列的NaN值

我有如下结构的DataFrame：

id = ['A','A','A','A','A','A','A','A','A','A','A','A','B','B','B','B','B','B','C','C','C','C','C','C',]
time = ['2021-05-02 01:00:00','2021-05-02 02:00:00','2021-05-02 03:00:00','2021-05-02 04:00:00',
       '2021-05-02 05:00:00','2021-05-02 06:00:00','2021-05-02 07:00:00','2021-05-02 08:00:00',
       '2021-05-02 09:00:00','2021-05-02 10:00:00','2021-05-02 11:00:00','2021-05-02 12:00:00',
       '2021-05-02 01:00:00','2021-05-02 02:00:00','2021-05-02 04:00:00','2021-05-02 05:00:00',
       '2021-05-02 08:00:00','2021-05-02 09:00:00','2021-05-02 01:00:00','2021-05-02 02:00:00',
        '2021-05-02 04:00:00','2021-05-02 05:00:00',
       '2021-05-02 08:00:00','2021-05-02 10:00:00']
in_count = [1,1,1,2,1,1,2,5,1,2,1,1,1,2,2,3,1,1,2,1,1,1,2,1]
out_count =[1,1,1,1,1,2,1,1,1,3,1,1,2,2,1,1,2,1,1,2,1,1,2,1]
in_distance = [12,12,14,12,10,8,12,10,12,12,13,12,12,12,11,18,13,12,20,21,15,12,12,21]
out_distance = [10,10,10,11,11,21,12,14,12,13,13,13,22,21,13,12,21,11,11,21,21,11,11,21]

d = {'id': id, 'time': time, 'in_count':in_count,'out_count':out_count,'in_dist':in_distance,'out_dist':out_distance}
df = pd.DataFrame(d)
df['time'] = pd.to_datetime(df['time'], format = '%Y-%m-%d %H:%M:%S')

df = df.pivot(index='id', columns='time', values=['in_count', 'out_count','out_dist','in_dist'])

执行pivot操作后得到多索引透视表，需要将表中in_dist列的NaN值，替换为对应ID下其他in_dist列有效值的均值。例如ID B的in_dist列NaN值，需用11、18、13、12的均值填充，且忽略该ID下的in_count、out_dist等其他列数据。

可以通过以下代码直接实现需求：

# 计算每个ID对应的in_dist有效值均值
in_dist_means = df['in_dist'].mean(axis=1)

# 用对应ID的均值填充in_dist列的NaN值
df['in_dist'] = df['in_dist'].apply(lambda row: row.fillna(in_dist_means[row.name]), axis=1)

代码说明 #

• df['in_dist'].mean(axis=1)：按行（每个ID）计算in_dist列的有效值均值，计算时自动忽略NaN值
• apply(lambda row: row.fillna(...), axis=1)：遍历每个ID对应的行，用预先计算好的均值填充该行in_dist中的所有NaN
• 整个操作仅针对in_dist层级的列，不会修改in_count、out_count、out_dist等其他列的数据

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