Better way to convert pandas dataframe columns to numeric

Question

I have a dataframe with some columns containing data of type object because of some funky data entries (aka a . or whatnot).

I have been able to correct this by identifying the object columns and then doing this:

obj_cols = df.loc[:, df.dtypes == object]
conv_cols = obj_cols.convert_objects(convert_numeric='force')

This works fine and allows me to run the regression I need, but generates this error:

FutureWarning: convert_objects is deprecated.

Is there a better way to do this so as to avoid the error? I also tried constructing a lambda function but that didn't work.

You can use astype(int) or pd.to_numeric

Vaishali
– Vaishali

2017-04-16 21:29:44 +00:00
Commented Apr 16, 2017 at 21:29 — Vaishali
– Vaishali, Commented Apr 16, 2017 at 21:29

Vaishali · Accepted Answer · 2017-04-16 22:20:34Z

14

Convert_objects is deprecated. Use this instead. You can add parameter errors='coerce' to convert bad non numeric values to NaN.

conv_cols = obj_cols.apply(pd.to_numeric, errors = 'coerce')

The function will be applied to the whole DataFrame. Columns that can be converted to a numeric type will be converted, while columns that cannot (e.g. they contain non-digit strings or dates) will be left alone.

edited Apr 16, 2017 at 22:20

answered Apr 16, 2017 at 21:34

Vaishali

38.5k5 gold badges62 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Sergei Over a year ago

This gave me: Value Error: ('Unable to parse string "." at position...)

Sergei Over a year ago

I tried that earlier. I get TypeError: arg must be a list, tuple, 1-d array, or Series

Sergei Over a year ago

Because obj_cols is a dataframe

Vaishali Over a year ago

The answer with apply should work along with the argument errors = 'coerce'

gregV Over a year ago

i believe errors = 'coerce' converts all non-digit strings to NaN , so it rather should be errors='ignore'

|

MissBleu · Accepted Answer · 2018-01-17 19:44:36Z

3

If you have a sample data frame:

sales = [{'account': 'Jones LLC', 'Jan': 150, 'Feb': 'f', 'Mar': 140},
     {'account': 'Alpha Co',  'Jan': 'e', 'Feb': 210, 'Mar': 215},
     {'account': 'Blue Inc',  'Jan': 50,  'Feb': 90,  'Mar': 'g' }]
df = pd.DataFrame(sales)

and you want to get rid of the strings in the columns that should be numeric, you can do this with pd.to_numeric

cols = ['Jan', 'Feb', 'Mar']
df[cols] = df[cols].apply(pd.to_numeric, errors='coerce', axis=1)

your new data frame will have NaN in place of the 'wacky' data

answered Jan 17, 2018 at 19:44

MissBleu

1753 silver badges15 bronze badges

Collectives™ on Stack Overflow

Better way to convert pandas dataframe columns to numeric

2 Answers 2

9 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related