CODEWITHSUNDEEP
r
Soren Christensen
Soren Christensen

Reputation: 376

How to lookup in R

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)]

Upvotes: 1

Views: 46

Answers (1)

GKi
GKi

Reputation: 39697

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

Upvotes: 1

Related Questions