I have a python pandas dataframe with several columns and one column has 0
values. I want to replace the 0
values with the median
or mean
of this column.
data
is my dataframeartist_hotness
is the 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 is not working.
Change zero values to the previous column value By using the method parameter, we can specify a different replacement strategy for our cell values. In our case we will method='ffill' . Conversely, we can use method='bfill' to assign the next value to our cell.
You can use the fillna() function to replace NaN values in a pandas DataFrame.
You can replace values of all or selected columns based on the condition of pandas DataFrame by using DataFrame. loc[ ] property. The loc[] is used to access a group of rows and columns by label(s) or a boolean array. It can access and can also manipulate the values of pandas DataFrame.
use 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
a b
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
a b
0 1 2
1 2 3
2 3 4
3 4 6
4 1 0
5 1 5
6 1 3
7 1 8
I think you can use mask
and add parameter skipna=True
to mean
instead dropna
. Also need change condition to data.artist_hotness == 0
if need replace 0
values or data.artist_hotness.isnull()
if need replace NaN
values:
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 omit 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 is DataFrame.replace
with specifying 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 need 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 need 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
Found these very useful, although mask
is really slow (not sure why).
I did this:
df.loc[ df['artist_hotness'] == 0 | np.isnan(df['artist_hotness']), 'artist_hotness' ] = df['artist_hotness'].median()
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With