Solved : You are trying to merge on object and datetime64[ns] columns

Python Pandas

Trying to merge two Python Pandas dataframes resulted in an error stating that the merg on an object column and a datetime column was not allowed. Which is all honesty is making sense.

The error shown when trying to run the code is shown below;

ValueError: You are trying to merge on object and datetime64[ns] columns. If you wish to proceed you should use pd.concat

To find out the Dtype of the fields I tried to merge on you can use the .info option in the form of df.info() .For the dataframe data the result of data.info() was the below where you can see the Dtype of ds is object

# Column Non-Null Count Dtype 
 — — — — — — — — — — — — — — — 
 0 ds 1429 non-null object 
 1 y 1429 non-null float64

If we do the same for the prediction dataframe with a prediction.info() we get the following and see that the Dtype for ds is datetime64[ns]

# Column Non-Null Count Dtype 
 — — — — — — — — — — — — — — — 
 0 ds 1457 non-null datetime64[ns]
 1 trend 1457 non-null float64 
 2 yhat_lower 1457 non-null float64 
 3 yhat_upper 1457 non-null float64 
 4 trend_lower 1457 non-null float64 
 5 trend_upper 1457 non-null float64 
 6 additive_terms 1457 non-null float64 
 7 additive_terms_lower 1457 non-null float64 
 8 additive_terms_upper 1457 non-null float64 
 9 daily 1457 non-null float64 
 10 daily_lower 1457 non-null float64 
 11 daily_upper 1457 non-null float64 
 12 weekly 1457 non-null float64 
 13 weekly_lower 1457 non-null float64 
 14 weekly_upper 1457 non-null float64 
 15 yearly 1457 non-null float64 
 16 yearly_lower 1457 non-null float64 
 17 yearly_upper 1457 non-null float64 
 18 multiplicative_terms 1457 non-null float64 
 19 multiplicative_terms_lower 1457 non-null float64 
 20 multiplicative_terms_upper 1457 non-null float64 
 21 yhat 1457 non-null float64

This holds that when we do a merge (as shown below) we will get a mismatch in Dtypes which will hinder the merge and which will result in the error which we shown before;

df2 = pd.merge(data, prediction, on=['ds'])

The solution to the issue is obvious, we need to ensure we do change the Dtype to ensure we have the same Dtype on both objects we like to merge. In our case we will change the object type of the data dataFrame to a datetime64 Dtype using the below command:

data['ds']=data['ds'].astype('datetime64')

When the Dtypes on both Pandas Dataframes are in sync and both are datetime64 the merge will happen without any issue and the error is resolved.