pandas 用给定分组的平均值替换nan [英] Pandas replace nan with mean value for a given grouping

问题描述

我有一个很大的数据集，格式为:

I have a large dataset of the form:

    period_id  gic_subindustry_id  operating_mgn_fym5  operating_mgn_fym4  317        201509            25101010           13.348150           11.745965   
682        201509            20101010           10.228725           10.473917   
903        201509            20101010           NaN                 17.700966   
1057       201509            50101010           27.858305           28.378040   
1222       201509            25502020           15.598956           11.658813   
2195       201508            25502020           27.688324           22.969760   
2439       201508            45202020           NaN                 27.145216   
2946       201508            45102020           17.956425           18.327724 

实际上，在过去25年中，我每年都有成千上万个值，并且有多(10+)列.

In practice, I have thousands of values for each year going back 25 years, and multiple (10+) columns.

我正在尝试将该时间段内的NaN值替换为gic_industry_id中位数/平均值.

I am trying to replace the NaN values with the gic_industry_id median/mean value for that time period.

我尝试了

df.fillna(df.groupby('period_id'，'gic_subindustry_id').transform('mean'))， 但这似乎太慢了(几分钟后我停了下来).

df.fillna(df.groupby('period_id', 'gic_subindustry_id').transform('mean')), but this seemed to be painfully slow (I stopped it after several minutes).

在我看来，之所以变慢的原因是由于重新计算了遇到的每个NaN的平均值.为了解决这个问题，我认为计算每个period_id的平均值，然后使用此值替换/映射每个NaN可能会更快.

It occurred to me that the reason it might be slow was due to re-calculating the mean for every NaN encountered. To get around this, I thought that calculating the mean at each period_id, and then replacing/mapping each NaN using this might be substantially faster.

means = df.groupby(['period_id', 'gic_subindustry_id']).apply(lambda x:x.mean())

输出:

                             operating_mgn_fym5  operating_mgn_fym4 operating_mgn_fym3 operating_mgn_fym2   
period_id gic_subindustry_id                                             
201509    45202030            1.622685  0.754661   0.755324  321.295665  
          45203010            1.447686  0.226571   0.334280   12.564398  
          45203015            0.733524  0.257581   0.345450   27.659407  
          45203020            1.322349  0.655481   0.468740   19.823722  
          45203030            1.461916  1.181407   1.487330   16.598534  
          45301010            2.074954  0.981030   0.841125   29.423161  
          45301020            2.621158  1.235087   1.550252   82.717147  

确实，这要快得多(30-60秒).

And indeed, this is much faster (30 - 60 seconds).

但是，我正在努力弄清楚如何将NaN映射到这些方法.确实，这是执行此映射的正确"方法吗?速度实际上并不是最重要的，但< 60秒会很好.

However, I am struggling to figure out how to map the NaNs to these means. And, indeed, is this the 'correct' way of performing this mapping? Speed actually isn't of paramount importance, but < 60 seconds would be nice.

推荐答案

如果数据框具有相同的结构(由as_index=False赋予)，则可以使用group-by的结果使用fillna:

You can use fillna using the result of group-by, provided the dataframes have the same structure (given by as_index=False):

df.fillna(df.groupby(['period_id', 'gic_subindustry_id'], as_index=False).mean())

#In [60]: df
#Out[60]: 
#   period_id  gic_subindustry_id  operating_mgn_fym5  operating_mgn_fym4
#0     201508            25502020           27.688324           22.969760
#1     201508            45102020           17.956425           18.327724
#2     201508            45202020                 NaN           27.145216
#3     201509            20101010           10.228725           14.087442
#4     201509            25101010           13.348150           11.745965
#5     201509            25502020           15.598956           11.658813
#6     201509            50101010           27.858305           28.378040
#7     201508            45102020           17.956425           18.327724

这篇关于 pandas 用给定分组的平均值替换nan的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！