Python Pandas replace multiple columns zero to Nan

Question

List with attributes of persons loaded into pandas dataframe df2. For cleanup I want to replace value zero (0 or '0') by np.nan.

df2.dtypes

ID                   object
Name                 object
Weight              float64
Height              float64
BootSize             object
SuitSize             object
Type                 object
dtype: object

Working code to set value zero to np.nan:

df2.loc[df2['Weight'] == 0,'Weight'] = np.nan
df2.loc[df2['Height'] == 0,'Height'] = np.nan
df2.loc[df2['BootSize'] == '0','BootSize'] = np.nan
df2.loc[df2['SuitSize'] == '0','SuitSize'] = np.nan

Believe this can be done in a similar/shorter way:

df2[["Weight","Height","BootSize","SuitSize"]].astype(str).replace('0',np.nan)

However the above does not work. The zero's remain in df2. How to tackle this?

score 117 · Accepted Answer · answered Jul 31 '17 at 13:04

117

I think you need replace by dict:

cols = ["Weight","Height","BootSize","SuitSize","Type"]
df2[cols] = df2[cols].replace({'0':np.nan, 0:np.nan})

answered Jul 31 '17 at 13:04

jezrael

822,522
95
1,334
1,252

1

I wonder why this solution works, while ```df2[cols].replace({'0':np.nan, 0:np.nan}, inplace=True)``` gives an error `A value is trying to be set on a copy of a slice from a DataFrame`? – Alexandr Kapshuk Sep 27 '19 at 16:38
It's not an error. It's just a warning. Basically, there could be memory issues there. – Bob Feb 07 '20 at 05:45
@M.Mariscal - Use `.replace({'.':'')` – jezrael Feb 10 '20 at 10:32
Doesnt work, my code is: cols = ['Total', 'uno', 'dos'] df[cols] = df[cols].replace({'.':''}) The problem is the to_csv i can see the point but because its thousands, but there is no point... the csv is a mess and i need to sort it ascend but cannot find the correct way – M. Mariscal Feb 10 '20 at 10:37

christk · Answer 2 · 2020-03-19T09:43:26.207

10

You could use the 'replace' method and pass the values that you want to replace in a list as the first parameter along with the desired one as the second parameter:

cols = ["Weight","Height","BootSize","SuitSize","Type"]
df2[cols] = df2[cols].replace(['0', 0], np.nan)

edited Mar 19 '20 at 09:43

answered Mar 17 '20 at 21:20

christk

834
11
23

score 5 · Answer 3 · answered Nov 09 '20 at 21:04

5

Try:

df2.replace(to_replace={
             'Weight':{0:np.nan}, 
             'Height':{0:np.nan},
             'BootSize':{'0':np.nan},
             'SuitSize':{'0':np.nan},
                 })

answered Nov 09 '20 at 21:04

Myccha

961
1
11
20

1

this is the cleanest solution IMO. You don't need to pass it in as a kwarg either. Just the dict is fine. For reference -> https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.replace.html , the **dict-like `to_replace`** section – Nick Brady Dec 14 '20 at 22:11

score 3 · Answer 4 · answered Aug 05 '19 at 11:37

3

data['amount']=data['amount'].replace(0, np.nan)
data['duration']=data['duration'].replace(0, np.nan)

answered Aug 05 '19 at 11:37

Ayyasamy

149
1
13

score 3 · Answer 5 · answered Jul 21 '21 at 09:28

in column "age", replace zero with blanks

df['age'].replace(['0', 0'], '', inplace=True)

Replace zero with nan for single column

df['age'] = df['age'].replace(0, np.nan)

Replace zero with nan for multiple columns

cols = ["Glucose", "BloodPressure", "SkinThickness", "Insulin", "BMI"]

df[cols] = df[cols].replace(['0', 0], np.nan)

Replace zero with nan for dataframe

df.replace(0, np.nan, inplace=True)

score 1 · Answer 6 · answered Oct 13 '21 at 19:59

1

If you just want to o replace the zeros in whole dataframe, you can directly replace them without specifying any columns:

df = df.replace({0:pd.NA})

answered Oct 13 '21 at 19:59

Hamza

5,373
3
28
43

This is the fastest way – Paul Jul 03 '22 at 06:58

score 0 · Answer 7 · answered Feb 02 '21 at 09:40

0

Another alternative way:

cols = ["Weight","Height","BootSize","SuitSize","Type"]
df2[cols] = df2[cols].mask(df2[cols].eq(0) | df2[cols].eq('0'))

answered Feb 02 '21 at 09:40

Zhongbo Chen

493
4
12

Python Pandas replace multiple columns zero to Nan

7 Answers7

Linked