DF with values for Time Intervals

Question

I am trying to make a manual dataframe.. I would like to have a time stamp with a time interval, for example:

df1:

Time Interval	Price
10:00 - 11:00	$15
11:00 - 12:00	$10
15:00 - 16:00	$18

So when I do a left join with df2:

Van	Route	Departure Time
223	NY-RO	10:35
655	LA-SD	15:15
987	LA-SF	15:50
278	LA-SD	11:45

The result will be:

Van	Route	Departure Time	Price
223	NY-RO	10:35	$15
655	LA-SD	15:15	$18
987	LA-SF	15:50	$18
278	LA-SD	11:45	$10

Answer 1

I think you can use merge_asof on the Departure Times. Note that it expects the columns to be merged on to be sorted, so we sort by time first.

df1['Departure Time'] = pd.to_datetime(df1['Time Interval'].str.split(' - ').str[0])
df2['Departure Time'] = pd.to_datetime(df2['Departure Time'])
out = (pd.merge_asof(df2.sort_values(by='Departure Time'), 
                     df1.sort_values(by='Departure Time'), on='Departure Time')
       .drop(columns=['Time Interval']))
out['Departure Time'] = out['Departure Time'].dt.strftime('%H:%M')

Output:

   Van  Route Departure Time Price
0  223  NY-RO          10:35   $15
1  278  LA-SD          11:45   $10
2  655  LA-SD          15:15   $18
3  987  LA-SF          15:50   $18