Wednesday 4 November 2020

Merge dataframe on datetime and range

Merge 2 dataframes.

I have dataframe A that has a value in a non constant timestamp:

creation_time   value_A
2020-09-22 00:00:35 83.0
2020-09-22 00:00:43 83.0
2020-09-22 00:02:20 82.0
2020-09-22 00:03:09 77.0
2020-09-22 00:03:04 77.0
2020-09-22 00:03:44 77.0
2020-09-22 00:07:10 71.0
...
2020-10-23 11:42:31 136.0
2020-10-23 11:42:32 136.0
2020-10-23 11:42:35 136.0
2020-10-29 11:42:31 136.0
2020-10-29 11:42:32 136.0
2020-10-29 11:42:35 136.0

and dataframe B. The first column is a range. For example, from 0 to 75 on 2020-09-22 the value_B should be 60.

value_A_range  creation_time value_B
0   2020-09-22 00:00:00.000000  60
75  2020-09-22 00:00:00.000000  65
124 2020-09-22 00:00:00.000000  300
143 2020-09-22 00:00:00.000000  360
0   2020-10-16 12:23:25.000000  60
75  2020-10-16 12:23:25.000000  400
124 2020-10-16 12:23:25.000000  400
143 2020-10-16 12:23:25.000000  450
0   2020-10-28 15:53:31.000000  10
82  2020-10-28 15:53:31.000000  30
114 2020-10-28 15:53:31.000000  40
129 2020-10-28 15:53:31.000000  60
139 2020-10-28 15:53:31.000000  110

Expected result: Get value_B for each timestamp in dataframe A. Match on range for value_A on the available timestamp.

creation_time   value_A  value_B
2020-09-22 00:00:35 83.0 65   
2020-09-22 00:00:43 83.0 65
2020-09-22 00:02:20 82.0 65
2020-09-22 00:03:09 60.0 60
2020-09-22 00:03:04 60.0 60
2020-09-22 00:03:44 60.0 60
2020-09-22 00:07:10 129.0 300
...
2020-10-23 11:42:31 136.0 400
2020-10-23 11:42:32 156.0 450
2020-10-23 11:42:35 136.0 400
2020-10-29 11:42:31 85.0 30
2020-10-29 11:42:32 120.0 40
2020-10-29 11:42:35 160.0 110

Resources I'm trying: range and merge_asof



from Merge dataframe on datetime and range

No comments:

Post a Comment