我非常感谢您提供一些有关此问题的帮助，但我在SO上找不到足够接近的示例。
我有两个data.tables，第一个称为customer.table，包含特定于时间戳（AsOfDate）的成员的快照，第二个称为activity.table描述在时发送给该客户的营销活动的表ActivityDate。
我想为客户数据表中的每个记录查找在AsOfDate之前或之日发送给成员的最新ActivityDate（即最大日期）。
我看了几个问题（一个很接近的问题是：使用ID重复处理一个表），但是我不确定如何将条件（ActivityDate <AsOfDate）与Activity日期的最大值结合起来-我我还想保留联接中两个表的所有列，因为我需要计算出ActivityDate和AsOfDate之间的时间。何时使用滚轴我仍然没有头脑...
#libraries
library(lubridate)
library(data.table)
#data
customer.table = structure(list(CustomerID = c(1, 2, 3, 4, 5, 6, 7, 8, 9, 10,
4), AsOfDate = structure(c(1435622400, 1435622400, 1435622400,
1435622400, 1435622400, 1435622400, 1435622400, 1435622400, 1435622400,
1435622400, 1394150400), tzone = "UTC", class = c("POSIXct",
"POSIXt")), distance = c(2.17380476584343, 29.4024827688224,
3.01353310956009, 18.4923143452557, 294.878606580665, 11.8870209430565,
9.54438580030996, 24.2192034858273, 15.0069335290262, 10.4513664447137,
18.4923143452557)), .Names = c("CustomerID", "AsOfDate", "distance"
), row.names = c("1", "5", "8", "10", "18", "28", "33", "37",
"45", "47", "101"), class = "data.frame")
activity.table = structure(list(CustomerID = c(3, 5, 8, 10, 4, 10, 2, 2, 5, 7,
5, 8, 4, 6, 10, 6, 5, 4, 2, 5, 5, 6, 5, 5, 10, 8, 6, 4, 5, 8,
7, 1, 8, 10, 7, 8, 4, 1, 1, 10, 9, 7, 4, 6, 9, 10, 8, 3, 5, 8,
1, 4, 4), ActivityDate = structure(c(1330560000, 1368144000,
1332855900, 1337817600, 1370822400, 1365984000, 1337817600, 1368144000,
1331164800, 1331164800, 1394150400, 1394150400, 1396224000, 1393891200,
1393891200, 1398643200, 1396310400, 1399334400, 1399939200, 1403222400,
1402358400, 1404086400, 1425254400, 1426464000, 1426464000, 1426464000,
1427155200, 1429056000, 1429056000, 1429056000, 1363737600, 1332201600,
1330560000, 1433116800, 1433289600, 1433289600, 1338462000, 1366628400,
1335885300, 1427241600, 1427241600, 1427241600, 1430265600, 1430265600,
1430265600, 1430265600, 1365503400, 1338394200, 1430265600, 1430265600,
1432598400, 1433894400, 1426723200), tzone = "UTC", class = c("POSIXct",
"POSIXt")), row.index = 1:53), .Names = c("CustomerID", "ActivityDate",
"row.index"), row.names = c(NA, -53L), class = "data.frame")
# what does the data look like
> head(activity.table)
CustomerID ActivityDate row.index
1 3 2012-03-01 00:00:00 1
2 5 2013-05-10 00:00:00 2
3 8 2012-03-27 13:45:00 3
4 10 2012-05-24 00:00:00 4
5 4 2013-06-10 00:00:00 5
6 10 2013-04-15 00:00:00 6
> head(customer.table)
CustomerID AsOfDate distance
1 1 2015-06-30 2.173805
5 2 2015-06-30 29.402483
8 3 2015-06-30 3.013533
10 4 2015-06-30 18.492314
18 5 2015-06-30 294.878607
28 6 2015-06-30 11.887021
谢谢你的协助。
似乎您在这里寻找简单的滚动联接。首先，我们将转换为data.table对象（请注意，对于该解决方案，我在CRAN上使用最新版本（V 1.9.6+）
library(data.table) # V 1.9.6+
setDT(customer.table)
setDT(activity.table)
然后，customer.table我们将尝试将每行中的最接近值连接到从activity.table无穷大到无穷大
indx <- activity.table[customer.table,
on = c(CustomerID = "CustomerID",
ActivityDate = "AsOfDate"),
roll = Inf,
which = TRUE]
indx
# [1] 51 19 48 52 49 44 35 36 45 34 5
indx是activity.table中最接近日期中每一行的日期的位置向量customer.table。
现在，剩下的就是重新加入 customer.table
customer.table[, MostRecentDate := activity.table[indx,ActivityDate]]
customer.table
# CustomerID AsOfDate distance MostRecentDate
# 1: 1 2015-06-30 2.173805 2015-05-26 00:00:00
# 2: 2 2015-06-30 29.402483 2014-05-13 00:00:00
# 3: 3 2015-06-30 3.013533 2012-05-30 16:10:00
# 4: 4 2015-06-30 18.492314 2015-06-10 00:00:00
# 5: 5 2015-06-30 294.878607 2015-04-29 00:00:00
# 6: 6 2015-06-30 11.887021 2015-04-29 00:00:00
# 7: 7 2015-06-30 9.544386 2015-06-03 00:00:00
# 8: 8 2015-06-30 24.219203 2015-06-03 00:00:00
# 9: 9 2015-06-30 15.006934 2015-04-29 00:00:00
# 10: 10 2015-06-30 10.451366 2015-06-01 00:00:00
# 11: 4 2014-03-07 18.492314 2013-06-10 00:00:00
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句