Pandas: Unable to merge on two date columns-CodePudding

I have two dataframes that look like:

df1:

    Date       Multiplier
0   1995-01-01  5.248256
1   1995-02-01  5.262376
2   1995-03-01  5.255998
3   1995-04-01  5.215762
4   1995-05-01  5.207806

df2:

    PRICE   Date
0   77500   1995-01-01
1   60000   1995-01-01
2   39250   1995-01-01
3   51250   1995-01-01
4   224950  1995-01-01

Both date columns have been made using the pd.to_datetime() method, and they both supposedly have <M8[ns] data types when using df1.Date.dtype and df2.Date.dtype. However when trying to merge the dataframes with pd.merge(df,hpi,how="left",on="Date") I get the error:
ValueError: You are trying to merge on object and datetime64[ns] columns. If you wish to proceed you should use pd.concat

CodePudding user response：

Try to convert the Date column of df1 to a datetime64

Check dtypes first:

>>> df1.dtypes
Date           object    # <- Not a datetime
Multiplier    float64
dtype: object

>>> df2.dtypes
PRICE             int64
Date     datetime64[ns]  # <- Right dtype
dtype: object

Convert and merge:

df1['Date'] = pd.to_datetime(df1['Date'])

out = pd.merge(df1, df2,how='left',on='Date')