BUG: `convert_dtypes` and `infer_objects` do not convert numeric-like strings

[abokey1]

Pandas version checks

• I have checked that this issue has not already been reported.

• I have confirmed this bug exists on the latest version of pandas.

• I have confirmed this bug exists on the main branch of pandas.

Reproducible Example

import pandas as pd

df = pd.DataFrame({"col1": ["123", "456"], "col2": ["1.23", "45.6"]})

>>> df.dtypes
# col1    object
# col2    object
# dtype: object

>>> df.convert_dtypes().dtypes
# col1    string[python]
# col2    string[python]
# dtype: object

>>> df.infer_objects().dtypes
# col1    object
# col2    object
# dtype: object

Issue Description

As stated in the title, neither of the two methods succeeds in correctly inferring the best possible dtypes.

Expected Behavior

A possible workaround would be :

>>> df.apply(pd.to_numeric).dtypes
# col1      int64
# col2    float64
# dtype: object

Installed Versions

Replace this line with the output of pd.show_versions()

[charizard-knows]

I don't believe that the functions are supposed to work in the way you are suggesting.
df.convert_dtypes attempt to use pd.Na so that columns can have na values without it being casted to float or object.
Please see the examples in the documentation. None of the examples suggest that they will try to coerce strings into ints or floats.

Same with df.infer_objects, it is used to let df rethink the dtype after you've done some operation on dirty columns. For eg, in a column with ["one", 2, 3, 4, 5, 6], df will assign it object but after you drop the "one", the column now holds only int values, so now you can use df.infer_objects, so that pandas will see and know that it now holds only int, so now the dtype will become int

So, this is not a bug, you will have to continue to use pd.to_numeric to convert it into int

[abokey1]

I don't believe that the functions are supposed to work in the way you are suggesting.

Hi @charizard-knows, It's not a belief thing and I'm not suggesting anything.

So, this is not a bug, you will have to continue to use pd.to_numeric to convert it into int

No offense but you can't be so definitive like that.

I'm simply seeking clarification from core developers on a behavior that I (as a regular pandas user) found a bit counter-intuitive.

[rhshadrach]

As stated in the title, neither of the two methods succeeds in correctly inferring the best possible dtypes.

I think whether numeric or string is the "best" is dependent on the context of the situation and so pandas should take no action here.

[abokey1]

Hey @rhshadrach, appreciate it! Feel free to close but before, could you outline all potential contexts for the given df ? Currently, I only identify one: "No ambiguity, just full numeric-like string values that should be treated and cast to int/float."

Curious about what's holding convert_dtypes and infer_objects back from making a decision in such configurations and encouraging the user to handle this by their own.

[mroeschke]

I agree with @rhshadrach. to_numeric exists for this purpose and recent pandas development has tried to reduce redundant ways to accomplish the same task to avoid diverging behaviors.

could you outline all potential contexts for the given df ?

For example, a column of US 5 digit zip codes could be treated as ints but in practice are labels (strings) and numeric operations (adding zip codes) makes no sense. Due to context it's best that inference like this is not performed implicitly

[rhshadrach]

For example, a column of US 5 digit zip codes

More generally, numeric codes are often used as ids, e.g. UPCs. Many systems I have worked with in industry store such codes as strings.