<pre><code>df=pd.DataFrame({
'CompanyID':[602,602,202],
'RegistrationNo':['veh1','veh1','veh2'],
'slotStartTime':['2020-07-27 21:12:00','2020-07-27 21:30:00',
'2020-07-28 22:16:00'],
'slotEndTime':['2020-07-27 22:12:00','2020-07-27 22:30:00',
'2020-07-28 23:16:00']
})
df2=pd.DataFrame({
'RegistrationNo':['veh1','veh1','veh1','veh1','veh1','veh1','veh1',
'veh2','veh2','veh2','veh2','veh2'],
'GPSTime':['2020-07-27 21:12:00','2020-07-27 21:15:00',
'2020-07-27 21:20:00','2020-07-27 21:30:00',
'2020-07-27 21:45:00','2020-07-27 22:15:00','2020-07-27 22:29:00',
'2020-07-28 21:45:00','2020-07-28 22:15:00','2020-07-28 22:29:00',
'2020-07-28 22:50:00','2020-07-28 23:16:00'],
'location':['loc1','loc2','loc3','loc4','loc5','loc6','loc7','loc8',
'loc9','loc10','loc11','loc12',]
})
df['slotStartTime']=pd.to_datetime(df['slotStartTime'])
df['slotEndTime']=pd.to_datetime(df['slotEndTime'])
df2['GPSTime']=pd.to_datetime(df2['GPSTime'])
#for each for in df merge df2 to get you time range start-end
#then take first and last row to get start loc and end loc
result=pd.DataFrame()
for index,row in df.iterrows():
row= pd.DataFrame(row).T
df_main = pd.merge(row,df2,on='RegistrationNo')
# print(df_main)
df_main = df_main[
(df_main.slotEndTime>df_main.slotStartTime) &
(df_main.slotStartTime<=df_main.GPSTime)&
(df_main.GPSTime<=df_main.slotEndTime)
].sort_values(by=['slotStartTime','RegistrationNo'])
df_main['start_loc'] = df_main.iloc[0]['location']
df_main['end_loc'] = df_main.iloc[-1]['location']
result = result.append(df_main)
#here you have 'result' DF with locations, now you need to assign them to original DF
df=df.merge(result,on=['slotStartTime','slotEndTime','RegistrationNo','CompanyID'],how='inner').drop_duplicates(
keep='last',subset=['slotStartTime','slotEndTime','RegistrationNo']
)
del df['location']
print(df)
CompanyID RegistrationNo slotStartTime slotEndTime GPSTime start_loc end_loc
4 602 veh1 2020-07-27 21:12:00 2020-07-27 22:12:00 2020-07-27 21:45:00 loc1 loc5
8 602 veh1 2020-07-27 21:30:00 2020-07-27 22:30:00 2020-07-27 22:29:00 loc4 loc7
11 202 veh2 2020-07-28 22:16:00 2020-07-28 23:16:00 2020-07-28 23:16:00 loc10 loc12
</code></pre>