Merge Dataframe Object And Timedelta64

I have a dataframe of dtype datetime64 df: time timestamp 18053.401736 2019-06-06 09:38:30+00:00 18053.418252 2019-06-06 10:02:17+00:00 18053.424514 2019-06-06 10:1

Solution 1:

Convert the timestamp to timedelta by subtracting the date part and then merge:

df1 = pd.DataFrame([pd.Timestamp('2019-06-06 09:38:30+00:00'),pd.Timestamp('2019-06-06 10:02:17+00:00')], columns=['timestamp'])
df2 = pd.DataFrame([pd.Timedelta('09:38:30')], columns=['ref_time'])

timestamp02019-06-0609:38:30+00:0012019-06-0610:02:17+00:00timestampdatetime64[ns, UTC]dtype: objectref_time009:38:30ref_timetimedelta64[ns]dtype: object

df1['merge_key'] = df1['timestamp'].dt.tz_localize(None) - pd.to_datetime(df1['timestamp'].dt.date)
df_merged = df1.merge(df2, left_on = 'merge_key', right_on = 'ref_time')

Gives:

    timestamp                   merge_key   ref_time
0   2019-06-06 09:38:30+00:00   09:38:30    09:38:30

Solution 2:

The main challenge here is to get everything into compatible date types. Using your, slightly modified, examples as inputs

from io import StringIO
df = pd.read_csv(StringIO(
"""
time,timestamp
18053.401736,2019-06-06 09:38:30+00:00
18053.418252,2019-06-06 10:02:17+00:00
18053.424514,2019-06-06 10:11:18+00:00
18053.454132,2019-06-06 10:53:57+00:00
"""))
df['timestamp'] = pd.to_datetime(df['timestamp'])

from datetime import timedelta
sdf = pd.read_csv(StringIO(
"""
ref_time
0 days 09:00:00
0 days 09:00:01
0 days 09:00:02
0 days 09:00:03
0 days 09:00:04
0 days 09:38:30
0 days 10:02:17
0 days 14:59:56
0 days 14:59:57
0 days 14:59:58
0 days 14:59:59
0 days 15:00:00
"""))
sdf['ref_time'] = pd.to_timedelta(sdf['ref_time'])

The dtypes here are as in your question which is important

First we figure out the base_date as we need to convert timedeltas into datetimes etc. Note we set it to midnight of the relevant date via round('1d')

base_date = df['timestamp'].iloc[0].round('1d').to_pydatetime()
base_date

output

datetime.datetime(2019, 6, 6, 0, 0, tzinfo=<UTC>)

Next we add timedeltas from sdf to the base_date:

sdf['ref_dt'] = sdf['ref_time'] + base_date

Now sdf['ref_dt'] and df['timestamp'] are in the same 'units' and of the same type, so we can merge

sdf.merge(df, left_on = 'ref_dt', right_on = 'timestamp', how = 'left')

output

ref_timeref_dttimetimestamp--------------------------------------------------------------------------00days09:00:002019-06-06 09:00:00+00:00nanNaT10days09:00:012019-06-06 09:00:01+00:00nanNaT20days09:00:022019-06-06 09:00:02+00:00nanNaT30days09:00:032019-06-06 09:00:03+00:00nanNaT40days09:00:042019-06-06 09:00:04+00:00nanNaT50days09:38:302019-06-06 09:38:30+00:0018053.42019-06-06 09:38:30+00:0060days10:02:172019-06-06 10:02:17+00:0018053.42019-06-06 10:02:17+00:0070days14:59:562019-06-06 14:59:56+00:00nanNaT80days14:59:572019-06-06 14:59:57+00:00nanNaT90days14:59:582019-06-06 14:59:58+00:00nanNaT100days14:59:592019-06-06 14:59:59+00:00nanNaT110days15:00:002019-06-06 15:00:00+00:00nanNaT

and we see the merge happening where needed