I'm trying to merge a dataframe which contains number of vehicle supplies over regions to another data, which is a record of movement between regions.
The first data looks like this where start is the region id, supply_cnt is the number of supply for each region at the beginning of the day.
df1
start supply_cnt
A 7.0
B 4.0
C 21.0
D 5.0
E 5.0
... ... ... ... ...
The second data looks like this where start_dt and end_dt indicates departure time and arrival time of the movement.
df2
start end start_dt end_dt
B A 00:00:42 00:11:27
A D 00:12:58 00:15:52
F G 00:21:07 00:29:28
G K 00:34:08 00:39:49
K K 00:34:10 00:45:59
I want to merge supply_cnt from df1 to df2's start region, but before merging I would like to update the supply_cnt in df1 with df2.
For example, the first row of df2 is merged from df1 as it is, and if I update the supply of df1 with first row of df2, the supply of A and B will each increase and decrease by 1. So the merged value to the second row of df2 will be updated supply_cnt of A which is 8.
df2
start end start_dt end_dt supply_cnt
B A 00:00:42 00:11:27 4
A D 00:12:58 00:15:52 _
F G 00:21:07 00:29:28 _
G K 00:34:08 00:39:49 _
K K 00:34:10 00:45:59 _
df1
start supply_cnt new supply_cnt
A 7.0 -> 8
B 4.0 -> 3
C 21.0 -> 21
D 5.0 -> 5
E 5.0 -> 5
... ... ... ... ...
The most challenging part of the problem is that I want to consider the starting and ending time (start_dt, end_dt) when merging the supply count data.
For example, assume the start_dt of second row in df2 is 00:10:58 instead of 00:12:58. Since 00:10:58 is faster than end_dt of row1 (00:11:27), I want to update 7 instead of 8.
What would be the best way to do this? I would sincerely appreciate some help.