Home > Article > Backend Development > How do you replace whitespace values with NaN in a Pandas dataframe?
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.
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.
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
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!