StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POReplacing blank values (white space) with NaN in pandas
text
Body
copied!<p>I want to find all values in a Pandas dataframe that contain whitespace (any arbitrary amount) and replace those values with NaNs.</p> <p>Any ideas how this can be improved?</p> <p>Basically I want to turn this:</p> <pre><code> A B C 2000-01-01 -0.532681 foo 0 2000-01-02 1.490752 bar 1 2000-01-03 -1.387326 foo 2 2000-01-04 0.814772 baz 2000-01-05 -0.222552 4 2000-01-06 -1.176781 qux </code></pre> <p>Into this:</p> <pre><code> A B C 2000-01-01 -0.532681 foo 0 2000-01-02 1.490752 bar 1 2000-01-03 -1.387326 foo 2 2000-01-04 0.814772 baz NaN 2000-01-05 -0.222552 NaN 4 2000-01-06 -1.176781 qux NaN </code></pre> <p>I've managed to do it with the code below, but man is it ugly. It's not Pythonic and I'm sure it's not the most efficient use of pandas either. I loop through each column and do boolean replacement against a column mask generated by applying a function that does a regex search of each value, matching on whitespace.</p> <pre><code>for i in df.columns: df[i][df[i].apply(lambda i: True if re.search('^\s*$', str(i)) else False)]=None </code></pre> <p>It could be optimized a bit by only iterating through fields that could contain empty strings:</p> <pre><code>if df[i].dtype == np.dtype('object') </code></pre> <p>But that's not much of an improvement</p> <p>And finally, this code sets the target strings to None, which works with Pandas' functions like fillna(), but it would be nice for completeness if I could actually insert a NaN directly instead of None.</p> <p>Help!</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload