R subset data.table by condition , getting nearest value row if missing-CodePudding

I would like to subset a data.table by condition "Year". Basically I want the data from the dt that matches a given year, per group. However, some groups do not have a complete time line across all years, and therefore I would like to return the nearest year's data for every group, so there are data for every group present for any year chosen (whether that is exactly the right year, or not).

library(data.table)

# make dummy data
dt <- data.table(Group = c(rep("A", 5),rep("B", 3),rep("C", 5),rep("D", 2)), 
x = sample(1:10,15, rep=T), Year = c(2011:2015, 2013:2015, 2011:2015, 2014:2015))

# subset by, e.g., Year == 2015 is fine, but I want a full result for ANY
# year chosen, such as 2012, by using the closest entry in time, per group. 

# Attempt;

y <- 2012

dt[Year == which.min(abs(Year - y)), .SD, by = Group]

Empty data.table (0 rows and 3 cols): Group,x,Year

The result in this example should be;

   Group x Year
1:     A 4 2012
2:     B 7 2013
3:     C 2 2012
4:     D 3 2014

CodePudding user response：

You are close: the use of which.min(abs(Year - y)) is good, but needs to be within the .SD-subsetting in the j portion.

dt[, .SD[which.min(abs(Year - y)),], Group]
#     Group     x  Year
#    <char> <int> <int>
# 1:      A     5  2012
# 2:      B     4  2013
# 3:      C     8  2012
# 4:      D     5  2014

Reproducible data

set.seed(42)
dt <- data.table(Group = c(rep("A", 5),rep("B", 3),rep("C", 5),rep("D", 2)), x = sample(1:10,15, rep=T), Year = c(2011:2015, 2013:2015, 2011:2015, 2014:2015))
dt
#      Group     x  Year
#     <char> <int> <int>
#  1:      A     1  2011
#  2:      A     5  2012
#  3:      A     1  2013
#  4:      A     9  2014
#  5:      A    10  2015
#  6:      B     4  2013
#  7:      B     2  2014
#  8:      B    10  2015
#  9:      C     1  2011
# 10:      C     8  2012
# 11:      C     7  2013
# 12:      C     4  2014
# 13:      C     9  2015
# 14:      D     5  2014
# 15:      D     4  2015
y <- 2012