How to match two data frame with constraints in R?

Question

I have two kinds of data set.

One is the data that people have on the elevator and the other is that the elevator arrived on the first floor.

this is the data of people who used the elevator

usercode    board time(sec) 
1           165 
2           167 
3           170 
4           340 
5           351 
6           820 
7           830 
8           1200    
9           1201    
10          1400    

and this is the data of elevator schedule

elevator code   arrival time
1               164
2               338
3               813
4               1175
5               1367

what i want to is

usercode    board time(sec) elevator code     elevator arrival time
1           165                 1                   164
2           167                 1                   164
3           170                 1                   164
4           340                 2                   338
5           351                 2                   338
6           820                 3                   813
7           830                 3                   813
8           1200                4                   1175
9           1201                4                   1175
10          1400                5                   1367

Help me!! please

Answer 1

You might want to consider using the rolling joins in data.table as follows:

arrival[people, on="arrivaltime_sec", roll=Inf]

#     elevatorcode arrivaltime arrivaltime_sec usercode
#  1:            1         164             165        1
#  2:            1         164             167        2
#  3:            1         164             170        3
#  4:            2         338             340        4
#  5:            2         338             351        5
#  6:            3         813             820        6
#  7:            3         813             830        7
#  8:            4        1175            1200        8
#  9:            4        1175            1201        9
# 10:            5        1367            1400       10

data:

library(data.table)

people <- fread("usercode,arrivaltime_sec
1,165
2,167
3,170
4,340
5,351
6,820
7,830
8,1200
9,1201
10,1400")

arrival <- fread("elevatorcode,arrivaltime
1,164
2,338
3,813
4,1175
5,1367")

arrival[, arrivaltime_sec := arrivaltime]

Answer 2

taking the first data as dat1 and the second one as dat2 we can do :

  transform(dat1,add=dat2[rowSums(outer(dat1[,2],dat2[,2],">")),],row.names=NULL)

   usercode arrival_time.sec. add.elevator_code add.arrival_time
1         1               165                 1              164
2         2               167                 1              164
3         3               170                 1              164
4         4               340                 2              338
5         5               351                 2              338
6         6               820                 3              813
7         7               830                 3              813
8         8              1200                 4             1175
9         9              1201                 4             1175
10       10              1400                 5             1367