Given two dataframes:
print(df1)
id status started ended
0 001 green 2014-09-04 01:00:00 2014-09-05 01:49:49
1 001 green 2014-09-05 01:49:50 2014-09-08 03:49:49
2 001 orange 2014-09-08 01:49:50 2014-09-18 23:49:49
3 002 green 2014-09-03 07:00:00 2014-09-09 02:59:49
4 002 red 2014-09-09 02:59:50 2014-09-11 02:39:29
5 002 red 2014-09-11 02:39:30 2014-09-15 01:00:00
print(df2)
id action action_datetime
0 001 process1 2014-09-07 01:49:49
1 001 process2 2014-09-08 01:49:52
2 001 process3 2014-09-09 10:00:00
3 002 process1 2014-09-08 11:01:29
4 002 process2 2014-09-09 06:59:49
In df2, I want to lookup corresponding status from df1 using df2's id and action_datetime. i.e. status corresponding
to each df2 row where df1.id = df2.id and action_datetime between the df1's started and ended:
id action action_datetime status
0 001 process1 2014-09-07 01:49:49 green
1 001 process2 2014-09-08 01:49:52 orange
2 001 process3 2014-09-09 10:00:00 orange
3 002 process1 2014-09-08 11:01:29 green
4 002 process2 2014-09-09 06:59:49 red
