我的数据集(784,932行/项目,27,492个唯一ID)。对于每个 item 在每个 id 中,如果日期之间的差异小于60秒,我正在尝试创建一个等于1的虚拟变量。
。风格化数据和代码:
ID <- c(1,1,1,1,1,1,3,3,3,3,3,3)
Item <- c(10,10,10,20,20,20,10,20,10,10,10,20)
Date <- c("19/11/13 18:58:00","19/11/13 18:58:21","19/11/13 20:58:00","19/11/13 18:58:00","19/11/13 18:58:00","19/11/13 18:58:00","19/11/13 18:58:00","19/11/13 18:58:00","19/11/13 18:58:00","19/11/13 18:58:00","19/11/13 18:58:00","19/11/13 19:58:00")
df <- data.frame(ID, Item, Date)
df <- df[order(ID, Date), ]
df[, "Date"] = lapply(df["Date"],function(x){strptime(x, "%d/%m/%y %H:%M:%S")})
# less than 60 sec difference = 1 (first item set to 999 -> 0)
fnDummy <- function(date) { ifelse(c(999, diff(date))<60, 1, 0) }
library(plyr)
ddply(df, .(ID, Item), transform, Dummy=fnDummy(Date) )
输出:
ID Item Date Dummy
1 1 10 2013-11-19 18:58:00 0
2 1 10 2013-11-19 18:58:21 1
3 1 10 2013-11-19 20:58:00 0
4 1 20 2013-11-19 18:58:00 0
5 1 20 2013-11-19 18:58:00 1
6 1 20 2013-11-19 18:58:00 1
7 3 10 2013-11-19 18:58:00 0
8 3 10 2013-11-19 18:58:00 1
9 3 10 2013-11-19 18:58:00 1
10 3 10 2013-11-19 18:58:00 1
11 3 20 2013-11-19 18:58:00 0
12 3 20 2013-11-19 19:58:00 1
从输出中,您可以看到第一行和第二行具有共同的ID和项目,日期的差异仅为21秒,因此假人为1。第二行和第三行也有共同的ID和项目,但在这里日期的差异大于60秒,因此假人为0。
我设法获得了想要的输出,但是操作很慢。对于1000行,大约需要40秒(请参见下面的system.time
结果)。这对应于大约。整个数据集为180分钟(我的计算机在内存中耗尽并在此之前崩溃)。
user system elapsed
36.485 3.328 39.800
如何使此操作更快?我可以使用data.table
完成相同的输出,并且它会更快吗?
您可以按建议使用data.table
。但是,您必须将POSIXlt
转换为POSIXct
:
library(data.table)
df$Date <- as.POSIXct(df$Date)
DT <- as.data.table(df)
DT[, dummy_date := fnDummy(Date), by=c('ID', 'Item')]
但是,速度的很大一部分可能在ifelse
函数中,并且您并不需要它,因为您正在创建布尔值:
as.integer(c(FALSE, diff(date) < 60))
您也可以使用dplyr解决此问题:
ID <- c(1,1,1,1,1,1,3,3,3,3,3,3)
Item <- c(10,10,10,20,20,20,10,20,10,10,10,20)
Date <- c("19/11/13 18:58:00","19/11/13 18:58:21","19/11/13 20:58:00","19/11/13 18:58:00","19/11/13 18:58:00","19/11/13 18:58:00","19/11/13 18:58:00","19/11/13 18:58:00","19/11/13 18:58:00","19/11/13 18:58:00","19/11/13 18:58:00","19/11/13 19:58:00")
df <- data.frame(ID, Item, Date = as.POSIXct(Date))
library(dplyr)
df %.%
group_by(ID, Item) %.%
mutate(
lagged = lag(Date, order_by = Date, default = 999),
dummy = Date - lagged < 60
)
lag()
有效地概括了diff()
,允许您指定第一个元素的订购值和默认值。(目前,它还失去了向量的属性,但它将在将来的版本中修复。在https://github.com/hadley/dplyr/issues/166上跟踪进度)