I want to compare the timestamps from dataframes and assign the value to the corresponding rows in between a specific time range:
Dataframe A:
| timestamp | value |
|---|---|
| 2020-01-01 14:00:00 | 10 |
| 2020-01-01 19:00:00 | 15 |
| 2020-01-02 14:00:00 | 20 |
| 2020-01-02 19:00:00 | 25 |
Dataframe B:
| timestamp | id |
|---|---|
| 2020-01-01 12:05:00 | 1 |
| 2020-01-01 12:10:00 | 2 |
| 2020-01-01 18:00:00 | 3 |
| 2020-01-01 18:19:00 | 4 |
| 2020-01-02 12:05:00 | 5 |
| 2020-01-02 12:10:00 | 6 |
| 2020-01-02 18:00:00 | 7 |
| 2020-01-02 18:19:00 | 8 |
| 2020-01-02 23:19:00 | 9 |
If an entry (row) from Dataframe B is the time range to a corresponding timestemp in Dataframe A (timestamp in range of the timestemp itself and a delta of -4h it shout be assigned with the value from Dataframe A. E. g. all entries (rows) in Dataframe B that have a timetsamp in between 2020-01-01 14:00:00 and delta -4h = 2020-01-01 10:00:00) should get the value 10.
If an entrie is out of range (that means it is not in the 4h timerange) it should get value 0.
The result should look like this:
Dataframe C:
| timestamp | id | value |
|---|---|---|
| 2020-01-01 12:05:00 | 1 | 10 |
| 2020-01-01 12:10:00 | 2 | 10 |
| 2020-01-01 18:00:00 | 3 | 15 |
| 2020-01-01 18:19:00 | 4 | 15 |
| 2020-01-02 12:05:00 | 5 | 20 |
| 2020-01-02 12:10:00 | 6 | 20 |
| 2020-01-02 18:00:00 | 7 | 25 |
| 2020-01-02 18:19:00 | 8 | 25 |
| 2020-01-02 23:19:00 | 9 | 0 |
