Getting Around 7 days ago

Question

This is what my framework looks like. The rightmost (fourth) column is my desired column. For this name, I have been trying to get the rating of this person from 7 days ago. If the date does not exist exactly 7 days ago, I want the rating to be associated with any nearest date (row date is 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 tried to dt[dt,roll=7+nearest]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

It 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

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