BUG: convert_objects(convert_numeric=True) fails with all strings

[bashtage]

s=pd.Series(['a','a','a'])

s.convert_objects(convert_numeric=True)
Out[78]: 
0    a
1    a
2    a
dtype: object

s[0]=1.0

s
Out[80]: 
0    1
1    a
2    a
dtype: object

s.convert_objects(convert_numeric=True)
Out[81]: 
0     1
1   NaN
2   NaN
dtype: float64

Having a single number changes behavior. Makes convert_objects unreliable when the data type must be numeric.

[jreback]

this was on pupose to avoid indiscrimate converting, hence this is somewhat of a 'soft' option. could add a 'force' which just converts to numeric no matter what.

[bashtage]

coerce might be the most consistent keyword with other functions that do conversions.

[jreback]

sorry, that's what I meant. sounds reasonable to me.

[bashtage]

I would argue that the default behavior feels a bit like an attempt at "magic" -- that is, guessing what I want rather than doing what I say.

[jreback]

@bashtage you can certainly try to change it. I am ok with that. I think the problem was that it was overly aggressive and broke bunch of stuff, but if you want to make it work fine too.

[detroitcoder]

What about using convert_object on a field of strings where only one of the fields can be converted to a numeric?

eg.

converted_df = DataFrame(list("abc1xyz")).convert_objects(convert_numeric=True)
converted_df.info()
>> class 'pandas.core.frame.DataFrame'>
Int64Index: 7 entries, 0 to 6
Data columns (total 1 columns):
0    1 non-null float64
dtypes: float64(1)

Only one numeric value will force this field to float64, set all strings to null, and convert the rest to floats. This seems a bit more like black magic.

[jreback]

@detroitcoder this is exactly the purpose of using convert_objects(convert_numeric=True) to force convert things to numeric, or set them to nan

[detroitcoder]

That seems a bit confusing and dangerous. Say I have a dataframe with three columns, all containing string objects. The first contains no fields that can be converted to int, the second only contains one, and the third can all be converted to ints. If someone uses convert_objects(convert_numeric=True) on this dataframe, the third field is set to int32 and the second is float64.

df = DataFrame(zip(list("abc"), list("xy1"), list("123")))
converted_df = df.convert_objects(convert_numeric=True)
converted_df.info()

>> class 'pandas.core.frame.DataFrame'>
Int64Index: 3 entries, 0 to 2
Data columns (total 3 columns):
0    3 non-null object
1    1 non-null float64
2    3 non-null int64
dtypes: float64(1), int64(1), object(1)
memory usage: 96.0+ bytes

If a user has to know the datatype of the columns before using convert_objects they could just use DataFrame.astype and DataFrame.replace with a regex to convert strings to nulls. I was under the impression that convert_objects could be used to convert objects without having to know what the underlying data consists of.

[jreback]

the point is to force convert something that astype won't work on

what are you trying to do? you rarely actually need to use convert_objects; only use case is what I mentioned above

[detroitcoder]

I want to convert objects from various sources to ndtypes without metadata or knowing what the data consists of. If I could use convert_numeric=True for a soft convert, and convert_numeric="coerce" for a forced convert, similar to convert_dates, that would be perfect. However, convert_numeric=True actually means 'coerce' which means I can't use convert_objects all together.

[jreback]

@detroitcoder well, where is your data coming from? read_csv/read_sql and most of the input converters automatically coerce things for you. You have to work pretty hard to construct a mixed-type object array. I mean you may have it and that's when convert_objects() comes in handy. The default settings do a 'soft' conversion, that is if all of the objects are convertible it will coerce it appropriately. convert_numeric is to perform 'forced' conversions that for some reason you don't/can't do using say read_csv.

[detroitcoder]

@jreback the data can come from many different sources, but most often as JSON or XML over HTTP/S. The problem that i have is that the data is often stored as strings even though it should be treated as int/float/datetime.

[jreback]

@detroitcoder ok that makes sense then. you will have to do some type conversions (not that read_json might work for you in some cases). try .convert_objects() w/o the modifiers first and see what it can do for you.

[detroitcoder]

@jreback, if .convert_objects() is called using default params, it has no affect on ints/floats:

df = DataFrame(zip(list("abc"), list("xy1"), list("123")))
converted_df = df.convert_objects()
converted_df.info()

<class 'pandas.core.frame.DataFrame'>
Int64Index: 3 entries, 0 to 2
Data columns (total 3 columns):
0    3 non-null object
1    3 non-null object
2    3 non-null object
dtypes: object(3)
memory usage: 96.0+ bytes

[jreback]

so pass the options. Not really sure what you don't like about that. You can also astype. or even do this
Series(numpy_array.tolist()) which will automatically infer the types for you.

[detroitcoder]

I wanted to use .convert_objects on an entire dataframe to convert objects into int32/float64 only for columns where every value conforms to a int/float. Right now I can't do that because all it takes is one value in a column to appear as a float and the entire column will be converted, setting everything else to nan.

A user cannot safely call .convert_objects(convert_numeric=True) unless they are certain that every column that should remain as a string does not contain a single integer-like value. However when dealing with string-data from multiple sources, often times uncleaned, you are rarely certain about this.

[jreback]

you would have to show an example

[detroitcoder]

This is what all of my examples have been of.

[detroitcoder]

This is practically the same example from my previous comment.

Here we create a dataframe that has 5000 rows and 3 columns. If a user calls df.convert_objects(convert_numeric=True), column B will be converted to a float64 just because there is one value that happens to be integer-like.

col_a = "A" * 5000
col_b = "B" * 4999 + "1"
col_c = "1" * 5000

df = DataFrame(zip(col_a, col_b, col_c), columns=["A", "B", "C"])
converted_df = df.convert_objects(convert_numeric=True)
converted_df.info()

<class 'pandas.core.frame.DataFrame'>
Int64Index: 5000 entries, 0 to 4999
Data columns (total 3 columns):
A    5000 non-null object
B    1 non-null float64
C    5000 non-null int64
dtypes: float64(1), int64(1), object(1)
memory usage: 156.2+ KB

Unless a user is certain that every column that should remain as a string does not contain a single integer-like value, they cannot safely call convert_objects(convert_numeric=True).

[jreback]

so just do something like this

In [11]: def convert(c):
   ....:     try:
   ....:         return c.astype(np.number)
   ....:     except:
   ....:         return c
   ....:     

In [12]: df.apply(convert).dtypes
Out[12]: 
A     object
B     object
C    float64
dtype: object

If you have completely unknown data, then pandas cannot reasonably guess what you actually want. Clearly you want a 'safe' version of astype.

[detroitcoder]

Thank you. I am doing something similar but I would ask why convert_objects does not do this or at least allow for it? I imagine that based on naming, convert_objects() is the first method that users turn to when converting datatypes in dataframes. Someone could be using this for months without realizing that all it takes is one record that is integer-like and an entire field will be converted incorrectly. Just seems like a very big gotcha.

[bashtage]

It all depends on what you consider 'correct'. This is a general problem with this function - it operates with a bit of magic (e.g. my point above about the vast difference between 1 number of 0 numbers).

Maybe the docstring be improved to describe the edge cases?

[jreback]

well pull-requests are welcome. I think you could even have a thresh option if you want to conditionally convert.

[detroitcoder]

@bashtage I would agree that improved docstring would help, but I would argue that making convert_numeric=True different from convert_numeric="coerce" would fix this specific problem. This is similar to how the convert_dates argument works.

With convert_numeric=True, conversion would only occur if every value in a field conforms to an integer-like value. convert_numeric="coerce" would be the same as convert_numeric=True is currently.

@jreback sounds like a fun thing to work on this weekend!

[bashtage]

coerce should always return numeric values IMO, which is different from
the current behavior.

On Wed, May 6, 2015 at 2:20 PM detroitcoder notifications@github.com
wrote:

@bashtage https://github.com/bashtage I would agree that improved
docstring would help, but I would argue that making convert_numeric=True
different from convert_numeric="coerce" would fix this specific problem.
This is similar to how the convert_dates argument works.

With convert_numeric=True, conversion would only occur if every value in
a field conforms to an integer-like value. convert_numeric="coerce"
would be the same as convert_numeric=True is currently.

@jreback https://github.com/jreback sounds like a fun thing to work on
this weekend!

—
Reply to this email directly or view it on GitHub
#9589 (comment).

[jreback]

yeh, you could simply add coerce option for convert_numeric which would always coerce, while True could do more of an astype like conversion (and I think would be reasonable back-compat)

[bashtage]

Having looked at the main code that does the conversion (common.py#1859) I'm not sure it is possible to do this.

The issue is that is sequentially tries the three converters, dates, timestamp and then numeric so that if you use coerce you could only use 1 coercion - I suppose this would be correct behavior, and in fact using 'coerce' would mean that other type converters would have to be False.

[jreback]

@bashtage generally only the soft coerce will all happen. A 'hard' coerceion (e.g. True/coerce) is only user initiated, and will be generally only try 1 thing (e.g. say for numeric). The point of this is to basically do an .astype but instead of having to try except everything if coerce it will simply mark those as NaN.

The 'bug' is that if it doesn't find anything (e.g. say you give it all strings), it will return the original. (which FYI is the same behavior as to_datetime), which I think we are going to change in 0.17.0, so coerce will make all things that don't fit (e.g. in this case non-numeric) to NaN regardless of the number of matches.