CODEWITHSUNDEEP
rdata.table
Laurence_jj
Laurence_jj

Reputation: 726

R data tables vlookup nearest date

I am trying to do a vlookup in r using data.tables. I am looking up the value for a specific date, if it is not available I would like the nearest next value. 

table1 <- fread(
          "id | date_created       
           1  |  2018-01-02
           1  |  2018-01-03
           2  |  2018-01-08
           2  |  2018-01-09", 
          sep ="|"
        )
        table2<- fread(
          "otherid       | date  | value
           1             |  2018-01-02 |   1
           2             |  2018-01-04 |   5    
           3             |  2018-01-07 |   3       
           4             |  2018-01-08 |   5         
           5             |  2018-01-11 |   3           
           6             |  2018-01-12 |   2",
          sep = "|"
        )

The result should look like:

table1 <- fread(
          "id | date        | value2
           1  |  2018-01-02 |  1
           1  |  2018-01-03 |  5
           2  |  2018-01-08 |  5
           2  |  2018-01-09 |  3", 
          sep ="|"
        )

Edit I fixed it, this works:

table1[, value2:= table2[table1, value, on = .(date=date_created), roll = -7]]

Upvotes: 0

Views: 81

Answers (1)

Laurence_jj
Laurence_jj

Reputation: 726

table1[, value2:= table2[table1, value, on = .(date=date_created), roll = -7]]

Upvotes: 1

Related Questions