CODEWITHSUNDEEP
rlubridate
carlite71
carlite71

Reputation: 413

How to match a timestamp to a time period in R

I have 2 tables as shown below. In table 1, Start and End define a period of time for each Key.

The task is to match Timestamp from the table 2 with its corresponding period of the first table, retrieve the associated Key and assign it to the Timestamp.

So far I have found between()::lubridate is useful to test whether the timestamp falls within the period. The output of between() is a logical vector. I think the code works if the value is TRUE, but fails when the timestamp doesn't match any period (i.e. value is FALSE).

Anyone knows how to fix it?

# generate tables

Keys = c("F11-47" , "F11-49" , "F11-66" )
Start = c("2018-01-15 11:35:00" ,"2018-01-23 12:05:00" , "2018-10-09 11:44:00" )
End = c("2018-01-23 04:05:00", "2018-05-15 13:32:03", "2018-12-10 05:06:00")

table1 = as.data.frame(cbind(Keys, Start, End))
table1$Start = ymd_hms(table1$Start) # parse to POSIX
table1$End = ymd_hms(table1$End) # parse to POSIX

timestamps = c("2018-01-16 11:37:00", "2019-04-26 16:13:05" , "2018-01-19 15:35:00", "2018-01-23 12:05:00", "2018-01-24 12:05:00" ,"2018-02-24 12:05:00" , 
               "2018-03-23 12:15:00", "2017-10-03 14:11:01" , "2018-04-07 14:15:00", "2018-10-17 14:15:00" , "2018-11-01 5:33:16", "2019-03-26 16:18:27"  )

table2 = as.data.frame(timestamps)
table2$Keys = ""
table2$timestamps = ymd_hms(table2$timestamps) # parse to POSIX

# what I've done so far

for (i in 1:length(table2$timestamps)) {

  timestamp = table2$timestamps[i]

   for (j in 1:length(table1$Keys)) {

    if (between(timestamp, table1$Start[j], table1$End[j])) {  # test if timestamp is between a time period

      expkey = table1$Exp_Keys[j]  # retrieve Key from that time period
    }

  }

  table2$Keys[i] = expkey  # assign key to timestamp

}

Upvotes: 0

Views: 548

Answers (1)

G. Grothendieck
G. Grothendieck

Reputation: 269654

Perform a left join on the indicated condition:

library(sqldf)

sqldf("select t2.timestamps, t1.Keys
  from table2 t2 
  left join table1 t1 on t2.timestamps between t1.Start and t1.End")

giving:

            timestamps   Keys
1  2018-01-16 06:37:00 F11-47
2  2019-04-26 12:13:05   <NA>
3  2018-01-19 10:35:00 F11-47
4  2018-01-23 07:05:00 F11-49
5  2018-01-24 07:05:00 F11-49
6  2018-02-24 07:05:00 F11-49
7  2018-03-23 08:15:00 F11-49
8  2017-10-03 10:11:01   <NA>
9  2018-04-07 10:15:00 F11-49
10 2018-10-17 10:15:00 F11-66
11 2018-11-01 01:33:16 F11-66
12 2019-03-26 12:18:27   <NA>

Upvotes: 1

Related Questions