pd.merge() doesn't merge int and str column dtypes but no warning or error

[inodb]

When merging an int dtype with a str dtype the join does not work:

>>> import pandas as pd
>>> df1 = pd.DataFrame({"A":[0]})
>>> df2 = pd.DataFrame({"A":["0"]})
>>> pd.merge(df1, df2, on=["A"])
Empty DataFrame
Columns: [A]
Index: []

I think it would be better to get a warning that the join is performed on incompatible column dtypes.

This is my pandas version:

>>> pd.show_versions()                                                                                                                                    

INSTALLED VERSIONS
------------------
commit: None
python: 2.7.9.final.0
python-bits: 64
OS: Darwin
OS-release: 13.4.0
machine: x86_64
processor: i386
byteorder: little
LC_ALL: None
LANG: en_US.UTF-8

pandas: 0.15.2
nose: 1.3.4
Cython: 0.21
numpy: 1.9.2
scipy: 0.15.1                                                                                                                                                          
statsmodels: 0.6.1
IPython: 2.2.0
sphinx: 1.2.3
patsy: 0.3.0
dateutil: 2.4.1
pytz: 2014.9
bottleneck: None
tables: 3.1.1
numexpr: 2.3.1
matplotlib: 1.4.3
openpyxl: 1.8.5
xlrd: 0.9.3
xlwt: 0.7.5
xlsxwriter: 0.5.7
lxml: 3.4.0
bs4: 4.3.2
html5lib: None
httplib2: None
apiclient: None
rpy2: 2.5.6
sqlalchemy: 0.9.7
pymysql: None
psycopg2: None

Thanks for all your work on pandas!

[jreback]

You are doing an inner merge, which doesn't match. Not sure if we could reliably detect this, as it involves a computation to figure out that you have strings that looks like numbers.

In [8]: >>> pd.merge(df1, df2, on=["A"],how='outer')
Out[8]: 
   A
0  0
1  0

In [9]: >>> pd.merge(df1, df2, on=["A"],how='outer').dtypes
Out[9]: 
A    float64
dtype: object

you can also do

df.convert_objects(convert_numeric=True) to force the objects to become numbers

[inodb]

Thanks for your reply and the quick fix. I actually don't think that one should check whether the string represents numbers or not. I think it is more about whether the dtypes of the columns with the same name match. If you do an inner merge on DataFrames with no matching columns you get a MergeError. I think it would make sense to also throw one if there are matching columns but their dtypes can't be silently cast to match. Especially if one specifically sets the option on=["A"].

[sudk1896]

@inodb @jreback: I would like to take a stab at this. From what I can understand, I need to check the datatypes of the columns that are to be merged, and if they're unequal then throw an error. Could you tell me what type of error ? Thanks.

[jreback]

this should raise a ValueError if the dtypes for the on columns don't match

[jorisvandenbossche]

Suppose the case of an int and float column. I don't think it should raise if you want to merge on those columns? (can eg already typically occur when having NaNs in one of both)

[jreback]

right we care about 'obvious' mismatches here that by-definition cannot match. so object vs int/float (or datetimelike vs int/float/object). We will need to lib.infer_dtype to make this more specific (e.g. string/unicode cannot match int)

[jorisvandenbossche]

Personally, I would leave this as a responsibility of the user.
Although I understand that the example above can be confusing at first, trying to guess when the user passed wrong dtypes by accident instead of on purpose seems like adding complexity that is not really worth it (but it's a trade-off of course)

Something else, currently, as @jreback shows above (#9780 (comment)), the strings of df2 get coerced to numeric values on merging:

In [57]: pd.merge(df1, df2, on=["A"],how='outer').dtypes
Out[57]: 
A    int64
dtype: object

I suppose this happens on purpose? IMO it should return object array and keep the original values (like pd.concat does)

[jreback]

@jorisvandenbossche yes, we should certainly not coerce on mixed types in merging, unless they are losslessly convertible (e.g. int & float), so maybe make a separate issue.

But I think we should raise on str/numeric, and datetimelike/(str or numeric), it is simply not possible (and if the user really wants that, then its just a concat).

[jorisvandenbossche]

and if the user really wants that, then its just a concat

That's a good point :-)

[jorisvandenbossche]

yes, we should certainly not coerce on mixed types in merging, unless they are losslessly convertible (e.g. int & float), so maybe make a separate issue.

On a second thought, if we disallow merging on str/numeric (the case of the initial example), I don't think are cases left that we would allow but where no coercing should happen? (for which I wanted to open an new issue)