0

I'm trying to merge a column from table2 onto table1 using two keys. Where I'm having issues is that one of these keys is a date, and I don't always have an exact date match. So I'd like to match on the date from table1 using the date from table2 that is closest to that date but not "greater" than it. So if I have:

table1 <- data.frame(id1=c(30380,30380,30455,51949), date=as.POSIXct(c("2012-05-13 00:00:00","2012-09-23 00:00:00","2011-04-09 00:00:00","2014-11-08 00:00:00"))) 

table2 <- data.frame(id2=c(30380,30380,30380,30380,
                       30455,30455,
                       51949,51949,51949), 
                 date=as.POSIXct(c("2012-10-01 00:00:00","2012-08-31 00:00:00","2012-02-22 00:00:00","2011-08-30 00:00:00",
                                   "2011-08-30 00:00:00","2011-02-22 00:00:00",
                                   "2015-01-08 00:00:00","2014-08-24 00:00:00","2014-11-08 00:00:00")),
                 rating=c(77,79,82,80,80,81,78,79,77))

Then I would expect output:

output <- data.frame(id1=c(30380,30380,30455,51949), 
                 date=as.POSIXct(c("2012-05-13 00:00:00","2012-09-23 00:00:00","2011-04-09 00:00:00","2014-11-08 00:00:00")),
                 rating=c(82,79,81,77))

I have tried using data.table's setDT() roll option with no success. If it matters, I also plan on doing this over several columns, meaning I would create several rating columns since I actually have several id1 columns. I thought figuring this out first would be a better start than attacking all of it. I'm really stuck, thanks for any help.

nsalas
  • 31
  • 5
  • Please, read about roll-joins and join-on-intervals. – zx8754 Jul 17 '17 at 12:32
  • My understanding of roll-joins is definitely lacking. I think the question marked as duplicate is not similar to my question though as there are two keys. The answer to my question is best found here https://stackoverflow.com/questions/28072542/merge-nearest-date-and-related-variables-from-a-another-dataframe-by-group – nsalas Jul 17 '17 at 12:43
  • OK, added the link, if you still think this is not a duplicate, please vote to re-open. – zx8754 Jul 17 '17 at 12:58

0 Answers0