17

I have a pandas dataframe (that was created by importing a csv file). I want to replace blank values with NaN. Some of these blank values are empty and some contain a (variable number) of spaces '', ' ', ' ', etc.

Using the suggestion from this thread I have 

df.replace(r'\s+', np.nan, regex=True, inplace = True)

which does replace all the strings that only contain spaces, but also replaces every string that has a space in it, which is not what I want.

How do I replace only strings with just spaces and empty strings?

Improve this question
2
  • I believe this is a duplicate question: stackoverflow.com/questions/13445241/… Commented Feb 17, 2018 at 23:27
  • @NickK9 I linked to that thread in my question. The question and accepted answer replace cells containing any white space. My question is about replacing cells containing only white space. So, I disagree that it is a duplicate. Commented Feb 18, 2018 at 23:51

2 Answers 2

Reset to default
20

Indicate it has to start with blank and end with blanks with ^ and $ :

df.replace(r'^\s*$', np.nan, regex=True, inplace = True)
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

This would also trap strings embedded in spaces such as ' thisstring ' but it will work for my dataset.
@Boud shouldn't the pattern to replace really be r'^\s*$' instead. the original answer doesn't match empty string ''.
@Boud stumbled upon this answer when i was stuck at not able replace few empty cells with replace().. this worked perfectly. thanks
6

If you are reading a csv file and want to convert all empty strings to nan while reading the file itself then you can use the option

skipinitialspace=True

Example code

pd.read_csv('Sample.csv', skipinitialspace=True)

This will remove any white spaces that appear after the delimiters, Thus making all the empty strings as nan

From the documentation http://pandas.pydata.org/pandas-docs/stable/io.html

enter image description here

Note: This option will remove preceding white spaces even from valid data, if for any reason you want to retain the preceding white space then this option is not a good choice.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.