How to Replace All the "nan" Strings with Empty String in My DataFrame?

Question

I have "None" and "nan" strings scattered in my dataframe. Is there a way to replace all of those with empty string "" or nan so they do not show up when I export the dataframe as excel sheet?

Simplified Example:

Note: nan in col4 are not strings

ID  col1   col2   col3   col4
1   Apple  nan    nan    nan
2   None   orange None   nan
3   None   nan    banana nan

The output should be like this after removing all the "None" and "nan" strings when we replaced them by empty strings "":

ID  col1   col2   col3   col4
1   Apple                nan
2          orange        nan
3                 banana nan

Any idea how to solve this problem?

Thanks,

Why are the nan values in col4 retained in the expected output? — Alexander
– Alexander, Commented Aug 12, 2016 at 17:00

score 8 · Accepted Answer · 2016-08-12 17:05:42Z

8

Use pandas' NaN. Those cells will be empty in Excel (you will be able to use 'select empty cells' command for example. You cannot do that with empty strings).

import numpy as np
df.replace(['None', 'nan'], np.nan, inplace=True)

edited Aug 12, 2016 at 17:05

answered Aug 12, 2016 at 16:56

user2285236

Sign up to request clarification or add additional context in comments.

1 Comment

prem Over a year ago

Replacing with '' consumes a lot of memory so this should be the acceptable solution.

Jon Clements · Accepted Answer · 2016-08-12 17:00:02Z

6

Use a list of strings to replace with blanks strings that won't affect actual nan's...

df.replace(['nan', 'None'], '')

Which'll give you a new dataframe of:

ID   col1    col2    col3  col4
1  Apple                   NaN
2         orange           NaN
3                 banana   NaN

answered Aug 12, 2016 at 17:00

Jon Clements

143k34 gold badges254 silver badges288 bronze badges

Comments

Alexander · Accepted Answer · 2016-08-12 17:05:51Z

4

You can compare each column to the numpy object dtype (which is basically text columns), and then only do replacements for those columns.

for col in df:
    if df[col] == np.dtype('O'):  # Object
        df.col1.replace(['None', 'NaN', np.nan], "", inplace=True)

answered Aug 12, 2016 at 17:05

Alexander

111k32 gold badges212 silver badges208 bronze badges

Comments

Kartik · Accepted Answer · 2016-08-12 19:10:00Z

3

All those loop-de-loop solutions...

replacers = [None, np.nan, "None", "NaN", "nan"] # and everything else that needs replacing.
df.loc[:, df.dtypes == 'object'].replace(replacers, '', inplace=True)

answered Aug 12, 2016 at 19:10

Kartik

8,73345 silver badges78 bronze badges

Collectives™ on Stack Overflow

How to Replace All the "nan" Strings with Empty String in My DataFrame?

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related