Merge by IDs and Subset by Time, R

Language: Layout:

options(width = 200)

txt <- 'No.     RUID_KEY    sess_start  sess_end
1   101 "2018-12-01 22:48:18.827" "2018-12-01 22:55:18.900"
2   201 "2018-12-01 13:10:20.100" "2018-12-01 13:50:10.000"
3   201 "2018-12-12 11:10:10.100" "2018-12-12 11:20:00.100"'

ip <- read.table(text=txt, header=TRUE, colClasses = c("integer", "integer", "POSIXct", "POSIXct"))

txt <- 'No. RUID_KEY    TASK_START  TASK_END    TASK_NAME
1   101 "2018-12-01 22:50:18.827" "2018-12-01 22:50:18.827" ERROR1
2   101 "2018-12-01 15:10:20.100" "2018-12-01 15:10:20.100" ERROR2
3   201 "2018-12-01 13:40:10.100" "2018-12-01 13:40:10.100" ERROR1'

ip_error <- read.table(text=txt, header=TRUE, colClasses = c("integer", "integer", "POSIXct", "POSIXct", "character"))

### FOR LOOP
cat(" --------------------------------------------- LOOP APPROACH --------------------------------------------- \n")
ev_ip_error <- data.frame(matrix(ncol=5,nrow=0))
x <- c("RUID_KEY", "sess_start", "sess_end")
colnames(ev_ip_error) <- x

for(j in 1:length(ip$RUID_KEY)){
   for(i in 1:length(ip_error$RUID_KEY)){
     if(isTRUE(ip_error$RUID_KEY[i]==ip$RUID_KEY[j]&&ip_error$TASK_START[i]>=ip$sess_start[j]&&ip_error$TASK_END[i]<ip$sess_end[j])){
       ev_ip_error <- rbind(ev_ip_error,ip[j,])
     }
   }
}

ev_ip_error

### MAPPLY 
cat("\n --------------------------------------------- MAPPLY APPROACH --------------------------------------------- \n")
prms <- expand.grid(al = 1:length(ip$RUID_KEY),
                    bl = 1:length(ip_error$RUID_KEY))

f <- function(i,j){
  if(isTRUE(ip_error$RUID_KEY[i]==ip$RUID_KEY[j]&&ip_error$TASK_START[i]>=ip$sess_start[j]&&ip_error$TASK_END[i]<ip$sess_end[j])){
     return(ip[j,])
  }
}

df_list <- mapply(f, prms$al, prms$bl, SIMPLIFY = FALSE)
#df_list <- Map(f, prms$al, prms$bl)   
ev_ip_error_map <- do.call(rbind, df_list)

ev_ip_error_map

### CHECK
cat("\nCHECK DATA FRAMES\n")
identical(ev_ip_error, ev_ip_error_map)

### MERGE
cat("\n --------------------------------------------- MERGE APPROACH  --------------------------------------------- \n")
ev_ip_error_merge <- subset(merge(ip, ip_error, by="RUID_KEY", suffixes=c("", "_")),
                       TASK_START >= sess_start & TASK_END < sess_end)[names(ip)]

# RE-SETTING ROW NAMES
ev_ip_error_merge <- `row.names<-`(ev_ip_error_merge, NULL)

ev_ip_error_merge

### CHECK
cat("\nCHECK DATA FRAMES\n")
identical(ev_ip_error, ev_ip_error_merge)

[ + ] Show input
Absolute running time: 0.42 sec, cpu time: 0.57 sec, memory peak: 31 Mb, absolute service time: 0,42 sec