Pandas: Converting to numeric, creating NaNs when necessary

Question

Say I have a column in a dataframe that has some numbers and some non-numbers

>> df['foo']
0       0.0
1     103.8
2     751.1
3       0.0
4       0.0
5         -
6         -
7       0.0
8         -
9       0.0
Name: foo, Length: 9, dtype: object

How can I convert this column to np.float, and have everything else that is not float convert it to NaN?

When I try:

>> df['foo'].astype(np.float)

or

>> df['foo'].apply(np.float)

I get ValueError: could not convert string to float: -

For a more comprehensive explanation of `pd.to_numeric` along with its applications, please take a look at [this answer](https://stackoverflow.com/a/47942854/4909087). — cs95, Jan 16 '19 at 18:12

score 77 · Accepted Answer · edited Dec 21 '18 at 03:50

77

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)

edited Dec 21 '18 at 03:50

Jack Moody

1,590
3
21
38

answered Nov 19 '15 at 05:25

Anton Protopopov

30,354
12
88
93

3

Fingers crossed this comes back, it was a great silver bullet. – Andy Hayden Nov 20 '15 at 06:44
'module' object has no attribute 'to_numeric' ? – bgenchel Nov 30 '15 at 07:27
show edited version, it's only available from `0.17.0` pandas version – Anton Protopopov Nov 30 '15 at 07:44

score 35 · Answer 2 · answered Aug 25 '13 at 22:33

35

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.

answered Aug 25 '13 at 22:33

Andy Hayden

359,921
101
625
535

1

"Attempt to infer better dtype for object columns" is basically a magic bullet... (and it does dates too.) – Andy Hayden Aug 25 '13 at 22:41
@delgadom suprisingly there isn't a "cleaning" section in the [10 minute tutorial](http://pandas.pydata.org/pandas-docs/stable/10min.html). I need to finish up my book :) – Andy Hayden Oct 09 '15 at 22:17
It's depreciated unfortunately :/ – Newskooler Jan 31 '18 at 14:43

score 29 · Answer 3 · answered Nov 28 '17 at 23:13

29

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

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

answered Nov 28 '17 at 23:13

Amir Imani

3,118
2
22
24

score 11 · Answer 4 · answered Aug 25 '13 at 22:08

11

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)

answered Aug 25 '13 at 22:08

Viktor Kerkez

45,070
12
104
85

Thanks! Good and simple. – Amelio Vazquez-Reina Aug 25 '13 at 22:24
Simple and works much better than the previous suggestions. – Gunay Anach Aug 09 '17 at 13:04

Pandas: Converting to numeric, creating NaNs when necessary

4 Answers4

Linked

Related