How to lookup in R

Question

I have two df:

df1 <- data.frame(DTS = c(as.Date("2009-12-12"), as.Date("2012-12-12") , as.Date("2015-3-4"), 
as.Date("2018-7-9")),score= c(10,7,3,8))

df2 <- data.frame(DTS = c(as.Date("2009-12-14"), as.Date("2013-09-12") ,as.Date("2014-09-12"),  
as.Date("2015-3-4"),as.Date("2016-05-05"), as.Date("2019-12-12")))

I need to find the score for the relevant dates in df2 by looking up in df1

This works, but it is far from elegant.. anyone with a better solution?

df2$score <- 0

for (int_x in 1:NROW(df1)) {

   df2$score[ df2$DTS <= df1$DTS[int_x+1]  &  df2$DTS> df1$DTS [int_x] ] <- df1$score[int_x] 

 }

 df2$score[df2$DTS > df1$DTS[nrow(df1)]]   <- df1$score[NROW(df1)]

Related / possible duplicate: [*Merge 2 dataframes by matching dates*](https://stackoverflow.com/q/31876335/2204410) — Jaap, Sep 09 '20 at 13:03
Could you explain in words what that score is supposed to be instead of code that you yourself do not find elegant, please? — Bernhard, Sep 09 '20 at 13:08
the score is correct.. i just dont like these loops.. i was wibderubg fu tgere was a function or anything i R doing this? — Soren Christensen, Sep 09 '20 at 13:10

score 1 · Accepted Answer · answered Sep 09 '20 at 13:09

As DTS in df1 is sorted findInterval can be used.

df2$score <- df1$score[findInterval(df2$DTS, df1$DTS, left.open=TRUE)]
df2
#         DTS score
#1 2009-12-14    10
#2 2013-09-12     7
#3 2014-09-12     7
#4 2015-03-04     7
#5 2016-05-05     3
#6 2019-12-12     8

How to lookup in R

1 Answers1