How do you replace whitespace values with NaN in a Pandas dataframe?

Replacing Blank Values with NaN in Pandas

Problem

Finding whitespace values in a Pandas dataframe and replacing them with NaNs can be a challenge. The goal is to convert a dataframe with empty string values to one with NaN values, potentially improving data handling and analysis.

Solution

The df.replace() method provides an elegant solution, allowing you to replace values based on regular expressions:

<code class="python">df.replace(r'^\s*$', np.nan, regex=True)</code>

In this regex pattern, ^ matches the beginning of the string, s* matches zero or more whitespace characters, and $ matches the end of the string. Therefore, this regex checks for strings consisting entirely of whitespace or an empty string.

Implementation

Applying this solution to the example dataframe:

<code class="python">df = pd.DataFrame([
    [-0.532681, 'foo', 0],
    [1.490752, 'bar', 1],
    [-1.387326, 'foo', 2],
    [0.814772, 'baz', ' '],     
    [-0.222552, '   ', 4],
    [-1.176781,  'qux', '  '],         
], columns='A B C'.split(), index=pd.date_range('2000-01-01','2000-01-06'))

result = df.replace(r'^\s*$', np.nan, regex=True)

print(result)</code>

This will produce the desired output:

                   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

Improvement

As pointed out by Temak, if valid data may contain whitespace, the regex pattern can be modified to r'^s $' to match only strings consisting entirely of whitespace:

<code class="python">df.replace(r'^\s+$', np.nan, regex=True)</code>

The above is the detailed content of How do you replace whitespace values with NaN in a Pandas dataframe?. For more information, please follow other related articles on the PHP Chinese website!