Convert string based NaN's to numpy NaN's

Question

I have a dataframe with a part of it shown as below:

2016-12-27              NaN
2016-12-28              NaN
2016-12-29              NaN
2016-12-30              NaN
2016-12-31              NaN
Name: var_name, dtype: object

The column contains NaN as strings/objects. How can I convert it to a numpy nan instead. Best would be able to do so when I read in the csv file.

Answer 1

df[var_name_replace] = df[var_name].replace('NaN', pd.NA)

This simply replaces the 'NaN' string object with pd.NA. This uses the top-level .replace(), not the string replace (that is, NOT .str.replace()).

You could use np.nan in the place of pd.NA and it would work nearly the same way.

When you first load a file, Pandas will usually do these sorts of conversions by default. But if you know just a specific form of the string is in your object at some later point in the program, this is a good way to accomplish the conversion.

Answer 2

I'd use the converters option in read_csv. In this case, we are aiming to convert the column in question to numeric values and treat everything else as numpy.nan which includes string version of 'NaN'

converter = lambda x: pd.to_numeric(x, 'coerce')
df = pd.read_csv(StringIO(txt), delim_whitespace=True, converters={1: converter}, header=None)
df

df.dtypes

0     object
1    float64
dtype: object