过去21天中的每个行的事件按名称 [英] Events in last 21 days for every row by Name
问题描述
这是我的数据框看起来像。两个最右边的列是我所需的列。这两列检查条件是否在最近21天有一个电子邮件ActivityType,以及在最近21天是否有一个网络研讨会ActivityType。
This is what my dataframe looks like. The two rightmost columns are my desired columns.These two columns check the condition whether in the last 21 days there is an "Email" ActivityType and whether in the last 21 days there is a "Webinar" ActivityType.
Name ActivityType ActivityDate Email(last21days) Webinar(last21day)**
John Email 1/1/2014 TRUE NA
John Webinar 1/5/2014 TRUE TRUE
John Sale 1/20/2014 TRUE TRUE
John Webinar 3/25/2014 NA TRUE
John Sale 4/1/2014 NA TRUE
John Sale 7/1/2014 NA NA
Tom Email 1/1/2015 TRUE NA
Tom Webinar 1/5/2015 TRUE TRUE
Tom Sale 1/20/2015 TRUE TRUE
Tom Webinar 3/25/2015 NA TRUE
Tom Sale 4/1/2015 NA TRUE
Tom Sale 7/1/2015 NA NA
根据帮助:
从过去21天窗口提取事件类型
我试过:
Based on the help here: Extracting event types from last 21 day window I tried:
df$ActivityDate <- as.Date(df$ActivityDate)
library(data.table)
setDT(df)
setkey(df, Name,ActivityDate)
Elsetemp <- df[, .(Name, ActivityDate, ActivityType)]
df[Elsetemp, `:=`(Email21 = as.logical(which(i.ActivityType == "Email")),
Webinar21 = as.logical(which(i.ActivityType == "Webinar"))),
roll = -21, by = .EACHI]
无效,因为对于Sale的行,我只能获得 TRUE
。例如,第二行,其中ActivityType = Webinar,Email21& Webinar21应该是TRUE。当我定义最后21天,我试图包括那一天,当事件发生了。
to no avail as I only get TRUE
s for rows with "Sale". For example, second row where ActivityType = Webinar, both Email21 & Webinar21 should say TRUE. When I am defining last 21 days, I am trying to include that very day when the event happened too.
推荐答案
这个怎么样?
使用 data.table
中的滚动连接:
Using rolling joins from data.table
:
require(data.table)
dt[, ActivityDate := as.Date(ActivityDate, format="%m/%d/%Y")]
setkey(dt, Name, ActivityDate)
roll_index <- function(x, types, roll=21) {
lapply(types, function(type) {
idx = x[ActivityType == type][x, roll=roll, which=TRUE]
as.logical(idx)
})
}
dt[, c("Email_21", "Webinar_21") := roll_index(dt, c("Email", "Webinar"))]
# Name ActivityType ActivityDate Email_21 Webinar_21
# 1: John Email 2014-01-01 TRUE NA
# 2: John Webinar 2014-01-05 TRUE TRUE
# 3: John Sale 2014-01-20 TRUE TRUE
# 4: John Webinar 2014-03-25 NA TRUE
# 5: John Sale 2014-04-01 NA TRUE
# 6: John Sale 2014-07-01 NA NA
# 7: Tom Email 2015-01-01 TRUE NA
# 8: Tom Webinar 2015-01-05 TRUE TRUE
# 9: Tom Sale 2015-01-20 TRUE TRUE
# 10: Tom Webinar 2015-03-25 NA TRUE
# 11: Tom Sale 2015-04-01 NA TRUE
# 12: Tom Sale 2015-07-01 NA NA
这篇关于过去21天中的每个行的事件按名称的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!