我有两个数据框mondays和tdates如下：
T Dates
User.ID tdate
1 11-02-2013
1 04-03-2013
1 16-04-2015
1 03-05-2015
1 05-05-2015
1 11-05-2015
1 29-09-2015
1 26-11-2013
1 28-11-2013
3 01-02-2016
4 22-11-2012
4 25-04-2013
4 29-05-2013
Mondays
ID Monday Closest Date
1 05-09-2016
1 20-04-2015
1 27-07-2015
1 08-06-2015
1 13-10-2014
3 16-09-2013
3 16-02-2015
3 29-08-2016
3 26-05-2014
3 29-02-2016
3 18-07-2016
3 22-02-2016
4 16-11-2015
现在我想从tdates中返回第 3 列中过去最接近或等效的日期，用于mondays中的每个User.ID。 例如 预期输出为
Mondays
ID Monday Closest Date
1 05-09-2016 29-09-2015
1 20-04-2015 16-04-2015
1 27-07-2015 11-05-2015
1 08-06-2015 11-05-2015
1 13-10-2014 28-11-2013
3 16-09-2013 NA
3 16-02-2015 NA
3 29-08-2016 01-02-2016
3 26-05-2014 NA
3 29-02-2016 01-02-2016
3 18-07-2016 01-02-2016
3 22-02-2016 01-02-2016
4 16-11-2015 29-05-2013
ForID = 1&Monday = 05-09-2016
过去最接近tdate是29-09-2015因此它将在Closest Date列中获取此日期
注意：如果没有找到过去或等于星期一日期的交易日期，请填写NAs
对于一个非常大的数据集，这必须完成，任何想法如何做到这一点。 我已经使用自定义功能尝试了此操作，如下所示：
lasttxndate <- function(userid, mydate){
+ return(max(subset(tdates$Date.Asked, tdates$User.ID == userid & tdates$Date.Asked <= as.Date(mydate))))
+ }
但是当将其与lapply' or应用一起使用时，这是行不通的。
# date conversion
mondays$Monday <- as.Date(mondays$Monday, "%d-%m-%Y")
tdates$tdate <- as.Date(tdates$tdate, "%d-%m-%Y")
# convert to data.table
library(data.table)
setDT(mondays)
setDT(tdates)
# you need identical column names for join
tdates[, ID := User.ID, ]
tdates[, Monday := tdate, ]
tdates[mondays, on = c("ID", "Monday"), roll = Inf]
User.ID tdate ID Monday
1: 1 2015-09-29 1 2016-09-05
2: 1 2015-04-16 1 2015-04-20
3: 1 2015-05-11 1 2015-07-27
4: 1 2015-05-11 1 2015-06-08
5: 1 2013-11-28 1 2014-10-13
6: NA <NA> 3 2013-09-16
7: NA <NA> 3 2015-02-16
8: 3 2016-02-01 3 2016-08-29
9: NA <NA> 3 2014-05-26
10: 3 2016-02-01 3 2016-02-29
11: 3 2016-02-01 3 2016-07-18
12: 3 2016-02-01 3 2016-02-22
13: 4 2013-05-29 4 2015-11-16
tdate列为您提供所需的日期
这段代码效果很好：
T.Dates <- data.frame(
User.ID=c("1","1","1","1","1","1","1","1","1","3","4","4","4"),
tdate=as.Date(c("11-02-2013","04-03-2013","16-04-2015","03-05-2015","05-05-2015","11-05-2015","29-09-2015","26-11-2013","28-11-2013","01-02-2016","22-11-2012","25-04-2013","29-05-2013"),format="%d-%m-%Y"))
Mondays <- data.frame(
ID=c("1","1","1","1","1","3","3","3","3","3","3","3","4"),
Monday=as.Date(c("05-09-2016","20-04-2015","27-07-2015","08-06-2015","13-10-2014","16-09-2013","16-02-2015","29-08-2016","26-05-2014","29-02-2016","18-07-2016","22-02-2016","16-11-2015"),format="%d-%m-%Y"))
Mondays$Closest.Date <- NA
Mondays$Closest.Date <- as.Date(Mondays$Closest.Date, format="%d-%m-%Y")
for(i in 1:nrow(Mondays)){
Mondays[i,"Closest.Date"] <- max(T.Dates$tdate[T.Dates$User.ID==Mondays$ID[i] & T.Dates$tdate <= Mondays[i,"Monday"]])
}
输出：
> Mondays
ID Monday Closest.Date
1 1 2016-09-05 2015-09-29
2 1 2015-04-20 2015-04-16
3 1 2015-07-27 2015-05-11
4 1 2015-06-08 2015-05-11
5 1 2014-10-13 2013-11-28
6 3 2013-09-16 <NA>
7 3 2015-02-16 <NA>
8 3 2016-08-29 2016-02-01
9 3 2014-05-26 <NA>
10 3 2016-02-29 2016-02-01
11 3 2016-07-18 2016-02-01
12 3 2016-02-22 2016-02-01
13 4 2015-11-16 2013-05-29