How To Fill Missing Values In A Dataframe Based On Group Value Counts?

I have a pandas DataFrame with 2 columns: Year(int) and Condition(string). In column Condition I have a nan value and I want to replace it based on information from groupby operati

Solution 1:

I did a little extra transformation to get stat as a dictionary mapping the year to its highest frequency name (credit to this answer):

In[0]:
fill_dict = stat.unstack().idxmax(axis=1).to_dict()
fill_dict

Out[0]:
{2015: 'good', 2016: 'good', 2017: 'excellent'}

Then use fillna with map based on this dictionary (credit to this answer):

In[0]:X['condition']=X['condition'].fillna(X['year'].map(fill_dict))XOut[0]:yearcondition02015       good12016       good22017  excellent32016       good42016  excellent52017  excellent62015       good72016       good82015  excellent92015       good