How to set a cell to NaN in a pandas dataframe
Most replies here above need to import an external module:
import numpy as np
There is a built-in solution into pandas itself: pd.NA
, to use like this:
df.replace('N/A', pd.NA)
just use replace
:
In [106]:
df.replace('N/A',np.NaN)
Out[106]:
x y
0 10 12
1 50 11
2 18 NaN
3 32 13
4 47 15
5 20 NaN
What you're trying is called chain indexing: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy
You can use loc
to ensure you operate on the original dF:
In [108]:
df.loc[df['y'] == 'N/A','y'] = np.nan
df
Out[108]:
x y
0 10 12
1 50 11
2 18 NaN
3 32 13
4 47 15
5 20 NaN
While using replace
seems to solve the problem, I would like to propose an alternative. Problem with mix of numeric and some string values in the column not to have strings replaced with np.nan, but to make whole column proper. I would bet that original column most likely is of an object type
Name: y, dtype: object
What you really need is to make it a numeric column (it will have proper type and would be quite faster), with all non-numeric values replaced by NaN.
Thus, good conversion code would be
pd.to_numeric(df['y'], errors='coerce')
Specify errors='coerce'
to force strings that can't be parsed to a numeric value to become NaN. Column type would be
Name: y, dtype: float64