Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Pandas: Unable to merge on two date columns

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:

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

    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

>Solution :

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')
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading