0

I have two data frames. My first data frame looks like this :

TIME                TYPE    CATEGORY
2020-10-11 10:09:12 A       GOOD
2020-10-11 10:09:28 B       AVG
2020-10-11 10:10:18 C       WORST
2020-10-12 10:09:12 B       AVG
2020-10-12 10:12:09 A       GOOD
2020-10-13 12:03:23 A       AVG
2020-10-13 13:09:23 B       GOOD
2020-10-13 13:16:07 C       GOOD

The second data frame looks like this(In real data more data points are available) :

timestamp           value1  value2  type
2020-10-11 10:09:10 100     109     A
2020-10-11 10:09:14 120     123     B
2020-10-11 10:09:20 90      134     A
2020-10-11 10:09:25 113     165     B
2020-10-11 10:09:34 100     111     C
2020-10-11 10:10:01 120     123     C
2020-10-11 10:10:07 90      113     A
2020-10-11 10:10:35 113     109     C
2020-10-11 10:11:10 100     109     B
2020-10-11 10:11:34 120     165     A
2020-10-12 10:09:20 90      178     B
2020-10-12 10:12:19 113     145     A
2020-10-12 10:12:21 100     108     B
2020-10-12 12:09:15 120     109     C
2020-10-13 11:59:03 90      134     C
2020-10-13 12:01:23 113     176     B
2020-10-13 12:03:14 100     198     A
2020-10-13 12:03:44 120     109     C
2020-10-13 13:09:20 90      105     B
2020-10-13 10:09:26 113     110     B
2020-10-13 13:16:20 100     102     C
2020-10-13 13:16:40 120     167     A

Expected output is to extract columns timestamp, value1 , value2 based on nearest timestamp occurrences (both the sides) and type :

TIME                   TYPE     CATEGORY        timestamp       value1      value2
2020-10-11 10:09:12     A       GOOD        2020-10-11 10:09:10     100     109
2020-10-11 10:09:28     B       AVG         2020-10-11 10:09:25     113     165
2020-10-11 10:10:18     C       WORST       2020-10-11 10:10:35     113     109
2020-10-12 10:09:12     B       AVG         2020-10-12 10:09:20     90      178
2020-10-12 10:12:09     A       GOOD        2020-10-12 10:12:19     113     145
2020-10-13 12:03:23     A       AVG         2020-10-13 12:03:14     100     198
2020-10-13 13:09:23     B       GOOD        2020-10-13 13:09:20     90      105
2020-10-13 13:16:07     C       GOOD        2020-10-13 13:16:20     100     102

I tried something like :

t.first$nearest_time<- unlist(mapply(x = t.first$TIME, y = t.first$type, FUN = function(x, y) as.character(t.second$timestamp[which.min(abs(x - t.second$timestamp))])))

But I am unable to add conditions

Ronak Shah
  • 377,200
  • 20
  • 156
  • 213
VJ D
  • 177
  • 8
  • 1
    You can try this `data.table` approach https://stackoverflow.com/questions/39282749/r-how-to-join-two-data-frames-by-nearest-time-date – Ronak Shah Nov 02 '20 at 06:00
  • @RonakShah, this is partially working. This works with respect to date alone. How can I include `type` column as a condition. ie, `type == TYPE` value – VJ D Nov 02 '20 at 06:13
  • It worked! Thanks – VJ D Nov 02 '20 at 06:52

0 Answers0