[Python] Combine dataframes fuzzy matching on time

I have two dataframes, dfA and dfB, whose primary key is an ID and a datetime object each stored in a column.

I wish to merge these dataframes on the ID & time if the ID is identical AND if the datetimes are within 24 hours.

I have written the following dummy example:

import numpy as np
import pandas as pd
import datetime

dfA = pd.DataFrame({
'id': [1,2,3],
'date': [datetime.datetime(2000, 1, 1, 22, 52), datetime.datetime(2000, 1, 2, 22, 53), datetime.datetime(2000, 1, 3, 22, 53)],
'value1': ['N','N','Y']
})

dfB = pd.DataFrame({
'id': [1,2,3],
'date': [datetime.datetime(2000, 1, 1, 22, 52), datetime.datetime(2000, 1, 2, 22, 53), datetime.datetime(2000, 1, 4, 10, 11)],
'value2': ['Y','Y','N']
})

print(dfA)
print(dfB)
print(pd.merge(dfA, dfB, on=['id','date']))


This works for the first two entries, but incorrectly excludes the third from the merge as the datetime is not identical. How can I adjust this to merge exactly on ID AND time within 24 hours?

UPDATE: For possible inspiration, here is my solution using for-loops. 86401 = 24 hours in seconds + 1. The problem is that it's ugly; there must be a more elegant way of doing it.

dfA = dfA.add_suffix('_A')
dfB = dfB.add_suffix('_B')
newData = []
for indexA, rowA in dfA.iterrows():
for indexB, rowB in dfB.iterrows():
if rowA['id_A'] == rowB['id_B']:
if 86401> abs((rowB['date_B']-rowA['date_A']).total_seconds()):
newData.append({**rowA.to_dict(), **rowB.to_dict()})
mergedDf = pd.DataFrame(newData)

Continue reading...