过去21天的事件,每行按姓名 [英] Events in last 21 days for every row by Name
问题描述
这是我的数据帧。两个最右边的列是我想要的列。这两列检查条件是否在过去21天有一个电子邮件活动类型,以及是否在过去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
s。例如,第二行ActivityType =网络研讨会,Email21和网络研讨会21应该说真的。当我定义最近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
使用滚动连接:
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屋!