Merge two dataframe based on a column with different types-CodePudding

I want to merge two dataframes on one column which is date. But, the problem is that in one dataframe the date column type is string, and in another one, the type of the date is datetime.date.

The date in df1 is an integer, then I have use the following code to change it to YYYY-DD-MM

df1['date'] = df1['date'].apply(datetime.fromtimestamp) #change to YYYY-DD-MM HH-MM-SS

df1['date'] = pd.to_datetime(df['date']).dt.date #change to datetime.date

df1['date'] = df1['date'].strftime('%Y-%d-%m') #change to string which does not work

Then, when I want to merge, the results for me is an empty dataframe. Here is a simple example. **[![Here the two dates are string. I could not assign the datetime.date to a date in this example.

import pandas as pd
import numpy as np
df1 = pd.DataFrame()
df2 = pd.DataFrame()

df1['a'] = [ '2012-01-02',  '2013-03-02' ]
df1['b'] = [ 2, 9]

df2['a'] = [ '2012-02-05',  '2013-03-02' ]
df2['b'] = [ 4, 7]

Here is the dataframe which I want.

CodePudding user response：

You could directly convert your date column to string: df1['a'] = df1['a'].astype(str) From what I tested it gives the same format but you don't control it.

To control it you can apply the transformation: df1['a'] = df1['a'].apply(lambda x: x.strftime('%Y-%d-%m'))

Or you could change the other df to a date if it is a string in the format YYYY-MM- DD as you have in your small example: df2['a'] = df2['a'].apply(datetime.fromisoformat)