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.