CODEWITHSUNDEEP
rtimestamptime-series
Tanjil
Tanjil

Reputation: 198

Comparing timestamps within margin in R

I am trying to find common timestamps between two dataframes. There is a small difference between them which can vary by upto 1 min. The final aim is to get the index number of t1 that matches with t2.

One of the approaches I attempted is below:

t1<-c("2019-12-02 12:40:38", "2019-12-02 12:41:42", "2019-12-18 08:50:59", "2019-12-18 10:27:35", "2019-12-18 10:31:37", "2019-12-18 13:11:57")
 
t2<-c("2019-12-02 12:39:57", "2019-12-02 12:40:34", "2019-12-02 13:16:10", "2019-12-02 14:08:23", "2019-12-18 10:26:17", "2019-12-18 10:31:58", "2019-12-18 13:10:45")

t1<-as.POSIXct(strptime(t1, "%Y-%m-%d %H:%M"))
t2<-as.POSIXct(strptime(t2, "%Y-%m-%d %H:%M"))

common_time<-format(anytime(intersect(t1,t2)), "%Y-%m-%d %H:%M")

I have also tried rounding to the nearest minute (using round_date(1 minutes)), which misses some values (e.g. 12:41:42 and 12:40:34). My questions are:

i) From the above how can I get all the index numbers of t1 that matches t2? ii) Is there a better approach to this?

In general T2 will have less elements than T1. Link to full datasets are below:

T1: https://www.dropbox.com/s/7nhxdvhbrhkb6rt/file_modified_times.csv?dl=0

T2: https://www.dropbox.com/s/nm72na37u0dkzil/OS_times.csv?dl=0

Thanks in advance.

Upvotes: 1

Views: 50

Answers (1)

Waldi
Waldi

Reputation: 41220

You could use nearestfrom data.table:

t1<-c("2019-12-02 12:40:38", "2019-12-02 12:41:42", "2019-12-18 08:50:59", "2019-12-18 10:27:35", "2019-12-18 10:31:37", "2019-12-18 13:11:57")

t2<-c("2019-12-02 12:39:57", "2019-12-02 12:40:34", "2019-12-02 13:16:10", "2019-12-02 14:08:23", "2019-12-18 10:26:17", "2019-12-18 10:31:58", "2019-12-18 13:10:45")

T1<-as.POSIXct(t1)
T2<-as.POSIXct(t2)


T1 <- data.table(t1 = T1)
T2 <- data.table(t2 = T2)

T1[T2,roll='nearest',.(x.t1,t2,dt=difftime(t2,x.t1)),on=.(t1==t2),allow.cartesian=T]

                  x.t1                  t2        dt
1: 2019-12-02 12:40:38 2019-12-02 12:39:57  -41 secs
2: 2019-12-02 12:40:38 2019-12-02 12:40:34   -4 secs
3: 2019-12-02 12:41:42 2019-12-02 13:16:10 2068 secs
4: 2019-12-02 12:41:42 2019-12-02 14:08:23 5201 secs
5: 2019-12-18 10:27:35 2019-12-18 10:26:17  -78 secs
6: 2019-12-18 10:31:37 2019-12-18 10:31:58   21 secs
7: 2019-12-18 13:11:57 2019-12-18 13:10:45  -72 secs

Upvotes: 1

Related Questions