Pandas: Converting to numeric, creating NaNs when necessary

You can simply use pd.to_numeric and setting error to coerce without using apply

df['foo'] = pd.to_numeric(df['foo'], errors='coerce')

First replace all the string values with None, to mark them as missing values and then convert it to float.

df['foo'][df['foo'] == '-'] = None
df['foo'] = df['foo'].astype(float)

In pandas 0.17.0 convert_objects raises a warning:

FutureWarning: convert_objects is deprecated. Use the data-type specific converters pd.to_datetime, pd.to_timedelta and pd.to_numeric.

You could use pd.to_numeric method and apply it for the dataframe with arg coerce.

df1 = df.apply(pd.to_numeric, args=('coerce',))

or maybe more appropriately:

df1 = df.apply(pd.to_numeric, errors='coerce')

EDIT

The above method is only valid for pandas version >= 0.17.0, from docs what's new in pandas 0.17.0:

pd.to_numeric is a new function to coerce strings to numbers (possibly with coercion) (GH11133)


Use the convert_objects Series method (and convert_numeric):

In [11]: s
Out[11]: 
0    103.8
1    751.1
2      0.0
3      0.0
4        -
5        -
6      0.0
7        -
8      0.0
dtype: object

In [12]: s.convert_objects(convert_numeric=True)
Out[12]: 
0    103.8
1    751.1
2      0.0
3      0.0
4      NaN
5      NaN
6      0.0
7      NaN
8      0.0
dtype: float64

Note: this is also available as a DataFrame method.

Tags:

Python

Pandas