Replace data in Pandas dataframe based on condition by locating index and replacing by the column's mode

Question

Hopefully, you don't mind me posting a question here instead of the regular stack exchange forum. I'm learning ML basics and practising pandas.

assuming the data frame is called df, column name = column name

Suppose I want to replace some 'dirty' values in the column 'column name'. There are "not known" values in this column that mean nothing so i would like to replace them with the mode.

df['columnname'].mode()

returns

0   dog
dtype: object

this code below replaces the "not known" values as NaN rather than the mode.

df.loc[df.index[df['columnname'] == "not known"].tolist(),'columnname'] = df['columnname'].mode()

what am i missing? or how should i fix the code?

Answer 1

Your entire code is correct except at the last point where you are equating with df['columnname'].mode(). The value here should have a dtype int or string but this has a dtype object. Just replace it with df['columnname'].mode().values and you are good to go.

Also, I see a lot of stuff that is not required here. Since you are using pd.loc, you can simply write:

df.loc[df['columnname'] == "not known",'columnname'] = df['columnname'].mode().values

Answer 2

Try this

df['columnname'].replace({'not known' : df['columnname'].mode().values} , inplace=True)

This is the most direct and intuitive way of replacing values given you already know what the "dirty" value is.