Python / Pandas Dataframe replace 0 with median value

Question

I have a python pandas dataframe with multiple columns, and one column has a value of 0 . I want to replace the values 0 with median or mean this column.

data is my dataframe
artist_hotness - column

 mean_artist_hotness = data['artist_hotness'].dropna().mean() if len(data.artist_hotness[ data.artist_hotness.isnull() ]) > 0: data.artist_hotness.loc[ (data.artist_hotness.isnull()), 'artist_hotness'] = mean_artist_hotness 

I tried this, but it does not work.

Answer 1

I think you can use mask and add the skipna=True parameter to mean instead of dropna . It is also necessary to change the condition to data.artist_hotness == 0 , if necessary, replace the values 0 or data.artist_hotness.isnull() if necessary, replace the values NaN :

 import pandas as pd import numpy as np data = pd.DataFrame({'artist_hotness': [0,1,5,np.nan]}) print (data) artist_hotness 0 0.0 1 1.0 2 5.0 3 NaN mean_artist_hotness = data['artist_hotness'].mean(skipna=True) print (mean_artist_hotness) 2.0 data['artist_hotness']=data.artist_hotness.mask(data.artist_hotness == 0,mean_artist_hotness) print (data) artist_hotness 0 2.0 1 1.0 2 5.0 3 NaN 

---

Alternatively use loc , but leave the column name:

 data.loc[data.artist_hotness == 0, 'artist_hotness'] = mean_artist_hotness print (data) artist_hotness 0 2.0 1 1.0 2 5.0 3 NaN data.artist_hotness.loc[data.artist_hotness == 0, 'artist_hotness'] = mean_artist_hotness print (data) 

IndexingError: (0 True 1 False 2 False 3 False Name: artist_hotness, dtype: bool, 'artist_hotness')

Another solution to DataFrame.replace with columns:

 data=data.replace({'artist_hotness': {0: mean_artist_hotness}}) print (data) aa artist_hotness 0 0.0 2.0 1 1.0 1.0 2 5.0 5.0 3 NaN NaN 

Or, if you want to replace all 0 values ​​in all columns:

 import pandas as pd import numpy as np data = pd.DataFrame({'artist_hotness': [0,1,5,np.nan], 'aa': [0,1,5,np.nan]}) print (data) aa artist_hotness 0 0.0 0.0 1 1.0 1.0 2 5.0 5.0 3 NaN NaN mean_artist_hotness = data['artist_hotness'].mean(skipna=True) print (mean_artist_hotness) 2.0 data=data.replace(0,mean_artist_hotness) print (data) aa artist_hotness 0 2.0 2.0 1 1.0 1.0 2 5.0 5.0 3 NaN NaN 

If you want to replace NaN in all columns, use DataFrame.fillna :

 data=data.fillna(mean_artist_hotness) print (data) aa artist_hotness 0 0.0 0.0 1 1.0 1.0 2 5.0 5.0 3 2.0 2.0 

But if only in some columns use Series.fillna :

 data['artist_hotness'] = data.artist_hotness.fillna(mean_artist_hotness) print (data) aa artist_hotness 0 0.0 0.0 1 1.0 1.0 2 5.0 5.0 3 NaN 2.0 

Answer 2

use the pandas replace method:

 df = pd.DataFrame({'a': [1,2,3,4,0,0,0,0], 'b': [2,3,4,6,0,5,3,8]}) df ab 0 1 2 1 2 3 2 3 4 3 4 6 4 0 0 5 0 5 6 0 3 7 0 8 df['a']=df['a'].replace(0,df['a'].mean()) df ab 0 1 2 1 2 3 2 3 4 3 4 6 4 1 0 5 1 5 6 1 3 7 1 8 

Answer 3

 data['artist_hotness'] = data['artist_hotness'].map( lambda x : data.artist_hotness.mean() if x == 0 else x)