How would I fill NA values in Pandas based on a lookup?

user_10901843 0
Viewed 89

I have some NA values, but I want to replace them by specific look ups. Let me show you an example.

>>> runners_df = pd.DataFrame(runners) 
>>> runners_df
   year  name  miles
0  2010  Paul    6.0
1  2010  Paul    4.0
2  2010  Paul    NaN
3  2011  Paul    8.0
4  2011  Paul    8.0
5  2012  Paul    9.0
6  2012  Paul   12.0
>>> average_miles_per_year = runners_df.groupby(['year','name'])['miles'].mean().reset_index()
>>> average_miles_per_year
   year  name  miles
0  2010  Paul    5.0
1  2011  Paul    8.0
2  2012  Paul   10.5
>>>

In this case, I would like Pauls NaN value to be filled with 5.0 because in 2010 his average miles was 5.

Thanks so much.

data-science pandas python
1 Answers

Try using fillna with groupby and transform:

runners['miles'] = runners['miles'].fillna(runners.groupby(['year','name'])['miles']
                                                  .transform('mean'))

Output:

   year  name  miles
0  2010  Paul    6.0
1  2010  Paul    4.0
2  2010  Paul    5.0
3  2011  Paul    8.0
4  2011  Paul    8.0
5  2012  Paul    9.0
6  2012  Paul   12.0
user_63615310
Related