I have two data set (df1 and df2) and both are composed by time-formatted values. I want to make like "objective out". While merging two data by c("id1","id2"), I want to leave "NA" in non-overlapped time.
df1
id1 id2 click_timing
1 11 2015-02-03 01:00:00
1 11 2015-02-03 02:00:00
1 12 2015-02-03 03:00:00
1 12 2015-02-03 04:00:00
1 13 2015-02-03 05:10:00
2 34 2015-02-03 03:00:00
2 34 2015-02-03 04:00:00
2 36 2015-02-03 01:00:00
...
df2
id1 id2 start end
1 11 2015-02-03 00:20:00 2015-02-03 00:40:00
1 11 2015-02-03 00:50:00 2015-02-03 01:20:00
1 13 2015-02-03 01:10:00 2015-02-03 01:40:00
1 13 2015-02-03 04:50:00 2015-02-03 05:30:00
2 34 2015-02-03 03:50:00 2015-02-03 04:10:00
...
objective output
id1 id2 click_timing start end
1 11 NA 2015-02-03 00:20:00 2015-02-03 00:40:00
1 11 2015-02-03 01:00:00 2015-02-03 00:50:00 2015-02-03 01:20:00
1 11 2015-02-03 02:00:00 NA NA
1 12 2015-02-03 03:00:00 NA NA
1 12 2015-02-03 04:00:00 NA NA
1 13 NA 2015-02-03 01:10:00 2015-02-03 01:40:00
1 13 2015-02-03 05:10:00 2015-02-03 04:50:00 2015-02-03 05:30:00
2 34 2015-02-03 03:00:00 NA NA
2 34 2015-02-03 04:00:00 2015-02-03 03:50:00 2015-02-03 04:10:00
2 36 2015-02-03 01:00:00 NA NA
...
Tough problem! I think you have to compute the intersection between each individual click_timing
value and every time period (start
and end
) by manually looping through all click_timing
values, and then use the resulting index matches as an additional join field:
df1 <- data.frame(id1=c(1,1,1,1,1,2,2,2), id2=c(11,11,12,12,13,34,34,36), click_timing=as.POSIXct(c('2015-02-03 01:00:00','2015-02-03 02:00:00','2015-02-03 03:00:00','2015-02-03 04:00:00','2015-02-03 05:10:00','2015-02-03 03:00:00','2015-02-03 04:00:00','2015-02-03 01:00:00')) );
df2 <- data.frame(id1=c(1,1,1,1,2), id2=c(11,11,13,13,34), start=as.POSIXct(c('2015-02-03 00:20:00','2015-02-03 00:50:00','2015-02-03 01:10:00','2015-02-03 04:50:00','2015-02-03 03:50:00')), end=as.POSIXct(c('2015-02-03 00:40:00','2015-02-03 01:20:00','2015-02-03 01:40:00','2015-02-03 05:30:00','2015-02-03 04:10:00')) );
m <- sapply(1:nrow(df1), function(i) which(df1$id1[i]==df2$id1 & df1$id2[i] == df2$id2 & df1$click_timing[i]>=df2$start & df1$click_timing[i]<=df2$end)[1] );
merge(cbind(df1,m=m),cbind(df2,m=1:nrow(df2)),by=c('id1','id2','m'),all=T)[-3];
## id1 id2 click_timing start end
## 1 1 11 <NA> 2015-02-03 00:20:00 2015-02-03 00:40:00
## 2 1 11 2015-02-03 01:00:00 2015-02-03 00:50:00 2015-02-03 01:20:00
## 3 1 11 2015-02-03 02:00:00 <NA> <NA>
## 4 1 12 2015-02-03 04:00:00 <NA> <NA>
## 5 1 12 2015-02-03 03:00:00 <NA> <NA>
## 6 1 13 <NA> 2015-02-03 01:10:00 2015-02-03 01:40:00
## 7 1 13 2015-02-03 05:10:00 2015-02-03 04:50:00 2015-02-03 05:30:00
## 8 2 34 2015-02-03 04:00:00 2015-02-03 03:50:00 2015-02-03 04:10:00
## 9 2 34 2015-02-03 03:00:00 <NA> <NA>
## 10 2 36 2015-02-03 01:00:00 <NA> <NA>
If there will ever be a case where a single click_timing
value intersects with multiple start
and end
pairs, then this solution will select the one that occurs earlier (i.e. has a lower row index in df2
) than the other matches.
Recreating initial data frame and making some minor preparations:
library(data.table)
library(lubridate)
df1<- fread("id1,id2,click_timing
1,11,2015-02-03 01:00:00
1,11,2015-02-03 02:00:00
1,12,2015-02-03 03:00:00
1,12,2015-02-03 04:00:00
1,13,2015-02-03 05:10:00
2,34,2015-02-03 03:00:00
2,34,2015-02-03 04:00:00
2,36,2015-02-03 01:00:00")
# adding a redundant click_timing2 column to use as the end range for further foverlaps() function
df1[, click_timing2:= click_timing]
df1[,c("click_timing", "click_timing2"):= list(parse_date_time(click_timing, "%Y-%m-%d %T"), parse_date_time(click_timing2, "%Y-%m-%d %T"))]
df2<- fread("id1,id2,start,end
1,11,2015-02-03 00:20:00,2015-02-03 00:40:00
1,11,2015-02-03 00:50:00,2015-02-03 01:20:00
1,13,2015-02-03 01:10:00,2015-02-03 01:40:00
1,13,2015-02-03 04:50:00,2015-02-03 05:30:00
2,34,2015-02-03 03:50:00,2015-02-03 04:10:00")
df2[,c("start","end") := list(parse_date_time(start, "%Y-%m-%d %T"), parse_date_time(end, "%Y-%m-%d %T"))]
setkey(df2, id1, id2, start, end)
Solution:
df3<- foverlaps(df1, df2, by.x=c("id1", "id2", "click_timing", "click_timing2"),
by.y = c("id1", "id2", "start", "end"), type="within")
objective_output<- merge(df3, df2, by = c("id1", "id2", "start", "end"), all = T)
# deleting redundant click_timing2 column
objective_output[,click_timing2:= NULL]
# reordering columns
setcolorder(objective_output, c(1,2,5,3,4))
#setting key using all columns and thus reordering all rows
setkey(objective_output)
objective_output
#id1 id2 click_timing start end
# 1: 1 11 2015-02-03 02:00:00 <NA> <NA>
# 2: 1 11 <NA> 2015-02-03 00:20:00 2015-02-03 00:40:00
# 3: 1 11 2015-02-03 01:00:00 2015-02-03 00:50:00 2015-02-03 01:20:00
# 4: 1 12 2015-02-03 03:00:00 <NA> <NA>
# 5: 1 12 2015-02-03 04:00:00 <NA> <NA>
# 6: 1 13 <NA> 2015-02-03 01:10:00 2015-02-03 01:40:00
# 7: 1 13 2015-02-03 05:10:00 2015-02-03 04:50:00 2015-02-03 05:30:00
# 8: 2 34 2015-02-03 03:00:00 <NA> <NA>
# 9: 2 34 2015-02-03 04:00:00 2015-02-03 03:50:00 2015-02-03 04:10:00
#10: 2 36 2015-02-03 01:00:00 <NA> <NA>
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With