Fetching a score associated with a date 'Around' 7 days ago

Question

This is what my dataframe looks like. The rightmost(4th) column is my desired column. For a given name,I am trying to derive that person' score from 7 days ago. If no date exists exactly 7 days ago, then I want the score associated with whatever date is closest to (a row's date- 7 days).

library(data.table)
 dt <- fread('
    Name      Score     Date         ScoreAround7DaysAgo
    John       9       2016-01-01       NA
    John       6       2016-01-10       9
    John       3       2016-01-17       6
    John       5       2016-01-18       6
    Tom        9       2016-01-01       NA
    Tom        6       2016-01-10       9
    Tom        3       2016-01-17       6
    Tom        5       2016-01-18       6
                                ')
dt[, Date := as.IDate(Date)]

I have tried dt[dt,roll=7+nearest] to no avail. Thank you for your help.

Answer 1

dt[, val := .SD[.(Name = Name, Date = Date - 7), on = c('Name', 'Date'), roll = 'nearest',
                c(NA, tail(Score, -1)), by = Name]$V1]
dt
#   Name Score       Date ScoreAround7DaysAgo val
#1: John     9 2016-01-01                  NA  NA
#2: John     6 2016-01-10                   9   9
#3: John     3 2016-01-17                   6   6
#4: John     5 2016-01-18                   6   6
#5:  Tom     9 2016-01-01                  NA  NA
#6:  Tom     6 2016-01-10                   9   9
#7:  Tom     3 2016-01-17                   6   6
#8:  Tom     5 2016-01-18                   6   6

Answer 2

This works:

dt[, DateLag := Date - 7L ]
w = dt[dt, which = TRUE, on = c("Name", Date = "DateLag"), roll = "nearest"]

dt[ , `:=`(ScoreLag = Score[replace(w, w == .I, NA_integer_)], DateLag = NULL)]


   Name Score       Date ScoreAround7DaysAgo ScoreLag
1: John     9 2016-01-01                  NA       NA
2: John     6 2016-01-10                   9        9
3: John     3 2016-01-17                   6        6
4: John     5 2016-01-18                   6        6
5:  Tom     9 2016-01-01                  NA       NA
6:  Tom     6 2016-01-10                   9        9
7:  Tom     3 2016-01-17                   6        6
8:  Tom     5 2016-01-18                   6        6

It finds the nearest date to Date-7, but discards it if it is the same Date again.