r - Keep before and after date of an external list

Question

Having this dataframe:

dframe1 <- structure(list(id = c(1L, 1L, 1L, 2L, 2L), name = c("Google", 
"Yahoo", "Amazon", "Amazon", "Google"), date = c("2008-11-01", 
"2008-11-01", "2008-11-04", "2008-11-01", "2008-11-02")), class = "data.frame", row.names = c(NA, 
-5L))

And this second one:

    dframe2 <- structure(list(id = c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 2L, 2L, 2L, 2L, 2L, 2L), date = c("2008-11-01", "2008-11-01", 
"2008-11-04", "2008-10-31", "2008-10-31", "2008-11-02", "2008-11-02", 
"2008-11-02", "2008-11-05", "2008-11-02", "2008-11-03", "2008-10-31", 
"2008-11-01", "2008-11-01", "2008-11-02", "2008-11-02", "2008-11-03"
), name = c("Google", "Yahoo", "Amazon", "Google", "Yahoo", "Amazon", 
"Google", "Yahoo", "Amazon", "Google", "Yahoo", "Amazon", "Google", 
"Amazon", "Google", "Amazon", "Google"), text_sth = c("test", 
"text_sth", "text here", "another text", "other", "another one", 
"test", "text_sth", "text here", "another text", "other", "etc", 
"test", "text_sth", "text here", "another text", "text here")), class = "data.frame", row.names = c(NA, 
-17L))

Using the results of dframe1 how is it possible to keep from dataframe2 the rows which have the same name for every id as dframe1 but one date before and after the record date of dframe1?

Here what I tried

library(data.table)
library(tidyverse)
library(reshape2)

dframe1 = data.table(dframe1)
dframe1[, date := as.Date(date)]

dframe1_first = dframe1[, .(date = min(date)), .(id, name)] %>% 
    mutate(date_pre = date - 1,
           date_after = date + 1)

req_rows = dframe2 %>%
    merge(dframe1_first %>%
              rename(id = id),
          by = "id") %>%
    filter(date >= date_pre,
           date <= date_after,
           date != date) %>%
    mutate(period = ifelse(date<date, '1-day-pre', '1-day-after'))

Expected output:

 id       date   name     text_sth
1 2008-10-31 Google another text
1 2008-10-31  Yahoo        other
1 2008-11-02 Google         test
1 2008-11-02  Yahoo     text_sth
1 2008-11-05 Amazon    text here
1 2008-11-02 Google another text
2 2008-10-31 Amazon          etc
2 2008-11-01 Google         test
2 2008-11-02 Amazon another text
2 2008-11-03 Google    text here

Answer 1

If I understand correctly, the OP wants to find matching entries on id, name and the day before or the day after. Therefore, a non-equi join will not help as it will include matches on the day itself.

I suggest to perform two inner joins, one for the day before and a second for the day after using lapply(). Subsequently, the results are combined with rbindlist() which also adds a new column matching_day as requested by the OP:

library(data.table)
library(magrittr)
setDT(dframe1)[, date := as.Date(date)]
setDT(dframe2)[, date := as.Date(date)]

lapply(
  c(-1, +1), 
  function(x) dframe2[dframe1[, .(id, name, date = date + x)], on = .(id, name, date), nomatch = 0L]
) %>%
  set_names(c("before", "after")) %>% 
  rbindlist(idcol = "matching_day") %>% 
  .[order(id)]

    matching_day id       date   name     text_sth
 1:       before  1 2008-10-31 Google another text
 2:       before  1 2008-10-31  Yahoo        other
 3:        after  1 2008-11-02 Google         test
 4:        after  1 2008-11-02 Google another text
 5:        after  1 2008-11-02  Yahoo     text_sth
 6:        after  1 2008-11-05 Amazon    text here
 7:       before  2 2008-10-31 Amazon          etc
 8:       before  2 2008-11-01 Google         test
 9:        after  2 2008-11-02 Amazon another text
10:        after  2 2008-11-03 Google    text here