I'd like to a left join an information by the nearest and anterior date of specific observations. I'll take as an example this dataset :
| ID | Date1 |
|---|---|
| A | 2010-09-12 |
| B | 2009-05-28 |
| C | 2008-04-16 |
The table to join informations is as follows:
| ID | Date1 | Rating |
|---|---|---|
| A | 2010-06-13 | 1 |
| A | 2010-10-03 | 7 |
| A | 2010-11-02 | 8 |
| B | 2008-12-28 | 3 |
| B | 2009-06-28 | 5 |
| B | 2009-07-28 | 4 |
| C | 2008-01-02 | 12 |
| C | 2008-05-23 | 6 |
| C | 2008-06-02 | 9 |
The final Database I'd like to have will be :
| ID | Date1 | Rating |
|---|---|---|
| A | 2010-09-12 | 1 |
| B | 2009-05-28 | 8 |
| C | 2008-04-16 | 12 |
As displayed above, it is important to notice that I want the anterior date, even if the ulterior date is nearer than the anterior one.
I've tried to data.table rolling join but not sure it will take the anterior rating by date:
test=df1[df2, roll = T]
Thanks a lot for your help!