python pandas column dtype=object causing merge to fail with: DtypeWarning: Columns have mixed types

Question

I am trying to merge two dataframes df1, df2 on Customer_ID column. It seems that Customer_ID has the same data type (object) in both.

df1:

Customer_ID |  Flag
12345           A

df2:

Customer_ID | Transaction_Value
12345           258478

When I merge the two tables:

new_df = df2.merge(df1, on='Customer_ID', how='left')

For some Customer_IDs it worked and for others it didn't. FOr this example, I would get this result:

Customer_ID | Transaction_Value | Flag
    12345           258478         NaN

I checked the data types and they are the same:

df1.info()
<class 'pandas.core.frame.DataFrame'>
Int64Index: 873353 entries, 0 to 873352
Data columns (total 2 columns):
Customer_ID    873353 non-null object
Flag      873353 non-null object
dtypes: object(2)
memory usage: 20.0+ MB

df2.info()
<class 'pandas.core.frame.DataFrame'>
Int64Index: 873353 entries, 0 to 873352
Data columns (total 2 columns):
Customer_ID    873353 non-null object
Transaction_Value      873353 int64
dtypes: object(2)
memory usage: 20.0+ MB

When I uploaded df1, I did get this message:

C:\Users\xxx\AppData\Local\Continuum\Anaconda2\lib\site-packages\IPython\core\interactiveshell.py:2717: DtypeWarning: Columns (1) have mixed types. Specify dtype option on import or set low_memory=False.
  interactivity=interactivity, compiler=compiler, result=result)

When I wanted to check, if a customer ID exists, I realized that I have to specify it differently in the two dataframes.

df1.loc[df1['Customer_ID'] == 12345]

df2.loc[df2['Customer_ID'] == '12345']

Answer 1

Customer_ID is of dtype==object in both cases... But that doesn't mean that the individual elements are the same type. You need to make both str or int

---

Using int

dtype = dict(Customer_ID=int)

df1.astype(dtype).merge(df2.astype(dtype), 'left')

   Customer_ID Flag  Transaction_Value
0        12345    A             258478

---

Using str

dtype = dict(Customer_ID=str)

df1.astype(dtype).merge(df2.astype(dtype), 'left')

   Customer_ID Flag  Transaction_Value
0        12345    A             258478

Answer 2

I think i have found the easiest way to merge between two data frame without changing the dtypes.

    final = pd.concat([df1, df2], axis=1, sort=False)

Hope it helps :)