I have df with column 2021-10-01 13:00:00+13:00 and another with 2021-10-01 00:00:00+00:00, how to merge them properly?
more info, below is how I convert column:
df1["tsr"] = pd.to_datetime(df1.tsr, utc=True)
df2["tsr"] = pd.to_datetime(df2.tsr,utc=True).dt.tz_convert("Pacific/Auckland")
Below is the table I got with merge, and utc=True, they are not matched!!
Should I convert them into a string and then merge them???
minimal reproducible example
df1:
{'id': [1, 2, 3, 4, 5],
'tsr': [Timestamp('2021-01-01 00:00:00+0000', tz='UTC'),
Timestamp('2021-01-01 00:30:00+0000', tz='UTC'),
Timestamp('2021-01-01 01:00:00+0000', tz='UTC'),
Timestamp('2021-01-01 01:30:00+0000', tz='UTC'),
Timestamp('2021-01-01 02:00:00+0000', tz='UTC')]}
df2:
{'tsr': [Timestamp('2021-10-01 13:00:00+1300', tz='Pacific/Auckland'),
Timestamp('2021-10-01 13:30:00+1300', tz='Pacific/Auckland'),
Timestamp('2021-10-01 14:00:00+1300', tz='Pacific/Auckland'),
Timestamp('2021-10-01 14:30:00+1300', tz='Pacific/Auckland'),
Timestamp('2021-10-01 15:00:00+1300', tz='Pacific/Auckland')]}
The result I want is just matched
2021-10-01 00:30:00+00:00 2021-10-01 00:30:00+13:00
2021-10-01 01:00:00+00:00 2021-10-01 01:00:00+13:00
2021-10-01 01:30:00+00:00 2021-10-01 01:30:00+13:00
2021-10-01 02:00:00+00:00 2021-10-01 02:00:00+13:00