在R中计算上次事件以来的天数

Question

我的问题是如何在R中计算自上次事件以来的天数。以下是数据的最小示例：

df <- data.frame(date=as.Date(c("06/07/2000","15/09/2000","15/10/2000","03/01/2001","17/03/2001","23/05/2001","26/08/2001"), "%d/%m/%Y"), 
event=c(0,0,1,0,1,1,0))
        date event
1 2000-07-06     0
2 2000-09-15     0
3 2000-10-15     1
4 2001-01-03     0
5 2001-03-17     1
6 2001-05-23     1
7 2001-08-26     0

一个二元变量（事件）的值为1表示事件发生，值为0表示未发生。在不同时间（date）进行重复观察。预期输出如下，包括自上次事件以来的天数（tae）：

 date        event       tae
1 2000-07-06     0        NA
2 2000-09-15     0        NA
3 2000-10-15     1         0
4 2001-01-03     0        80
5 2001-03-17     1       153
6 2001-05-23     1        67
7 2001-08-26     0        95

我曾经寻找过类似问题的答案，但它们没有解决我的具体问题。我尝试实现了来自一个相似帖子 (Calculate elapsed time since last event) 的想法，下面是我接近解决方案的代码：

library(dplyr)
df %>%
  mutate(tmp_a = c(0, diff(date)) * !event,
         tae = cumsum(tmp_a))

这将产生下面所示的输出，与预期的输出略有不同：

        date event tmp_a tae
1 2000-07-06     0     0   0
2 2000-09-15     0    71  71
3 2000-10-15     1     0  71
4 2001-01-03     0    80 151
5 2001-03-17     1     0 151
6 2001-05-23     1     0 151
7 2001-08-26     0    95 246

任何关于如何微调这个或其他方法的帮助都将不胜感激。

- amo

---

@Pascal 如果这样做可以更容易些，那么将前三个条目的“tae”设置为“0”而不是“NA”也可以。 - amo

@Pascal as.Date('2001-01-03')-as.Date('2000-10-15') 时间差为80天。这是自2000年10月15日发生的上一个事件以来的天数。明白了吗？ - amo

Answer 1

你可以尝试像这样：

# make an index of the latest events
last_event_index <- cumsum(df$event) + 1

# shift it by one to the right
last_event_index <- c(1, last_event_index[1:length(last_event_index) - 1])

# get the dates of the events and index the vector with the last_event_index, 
# added an NA as the first date because there was no event
last_event_date <- c(as.Date(NA), df[which(df$event==1), "date"])[last_event_index]

# substract the event's date with the date of the last event
df$tae <- df$date - last_event_date
df

#        date event      tae
#1 2000-07-06     0  NA days
#2 2000-09-15     0  NA days
#3 2000-10-15     1  NA days
#4 2001-01-03     0  80 days
#5 2001-03-17     1 153 days
#6 2001-05-23     1  67 days
#7 2001-08-26     0  95 days

- NicE

---

刚刚发现这个问题，最后一个事件日期部分出现了错误： Error in as.Date.default(e) : do not know how to convert 'e' to class “Date”有什么解决方法吗？我已经尝试了不同的方法，但似乎没有得到正确的结果。在 R 3.6.x 中可以工作，但在 4.x.x 中不行。 - MCP_infiltrator

Answer 2

虽然这样做会很痛苦且会降低性能，但您可以使用 for 循环来实现：

datas <- read.table(text = "date event
2000-07-06     0
2000-09-15     0
2000-10-15     1
2001-01-03     0
2001-03-17     1
2001-05-23     1
2001-08-26     0", header = TRUE, stringsAsFactors = FALSE)


datas <- transform(datas, date = as.Date(date))

lastEvent <- NA
tae <- rep(NA, length(datas$event))
for (i in 2:length(datas$event)) {
  if (datas$event[i-1] == 1) {
    lastEvent <- datas$date[i-1]
  }
  tae[i] <- datas$date[i] - lastEvent

  # To set the first occuring event as 0 and not NA
  if (datas$event[i] == 1 && sum(datas$event[1:i-1] == 1) == 0) {
    tae[i] <- 0
  }
}

cbind(datas, tae)

date event tae
1 2000-07-06     0  NA
2 2000-09-15     0  NA
3 2000-10-15     1   0
4 2001-01-03     0  80
5 2001-03-17     1 153
6 2001-05-23     1  67
7 2001-08-26     0  95

- Julien Navarre

Answer 3

这是一个老问题，但我正在尝试滚动连接并发现了这个有趣的现象。

library(data.table)
setDT(df)
setkey(df, date)

# rolling self-join to attach last event time
df = df[event == 1, .(lastevent = date), key = date][df, roll = TRUE]

# find difference between record and previous event == 1 record
df[, tae := difftime(lastevent, shift(lastevent, 1L, "lag"), unit = "days")]

# difftime for simple case between date and joint on previous event
df[event == 0, tae:= difftime(date, lastevent, unit = "days")]

> df
         date  lastevent event      tae
1: 2000-07-06       <NA>     0  NA days
2: 2000-09-15       <NA>     0  NA days
3: 2000-10-15 2000-10-15     1  NA days
4: 2001-01-03 2000-10-15     0  80 days
5: 2001-03-17 2001-03-17     1 153 days
6: 2001-05-23 2001-05-23     1  67 days
7: 2001-08-26 2001-05-23     0  95 days

- Akhil Nair

Answer 4

我很晚才参加这个聚会，但我使用了tidyr::fill使这更容易。您可以将非事件转换为缺失值，然后使用fill将NA填充为最后一个事件，然后从当前日期减去最后一个事件。

我已经测试过这个方法，对于整数日期列可能需要进行一些调整，对于Date类型的日期列（特别是使用NA_integer_时）可能需要进行一些调整。我不确定Date对象的底层类型是什么；我猜测是NA_real_。

df %>%
  mutate(
    event = as.logical(event),
    last_event = if_else(event, true = date, false = NA_integer_)) %>%
  fill(last_event) %>%
  mutate(event_age = date - last_event)

- jimjamslam

Answer 5

我曾经遇到过类似的问题，并且通过结合上述一些想法来解决了它。 我与我的主要区别在于，我的客户a-nth将拥有不同的事件（对我来说是购买）。 我想知道所有这些购买的累计总额以及最后活动的日期。 我解决这个问题的主要方法是创建一个索引数据框，以与主数据框连接。 与上面评分最高的问题类似。 请参见下面的可重复代码。

library(tidyverse)
rm(list=ls())

#generate repeatable code sample dataframe
df <- as.data.frame(sample(rep(sample(seq(as.Date('1999/01/01'), as.Date('2000/01/01'), by="day"), 12), each = 4),36))
df$subtotal <- sample(1:100, 36)
df$cust <- sample(rep(c("a", "b", "c", "d", "e", "f"), each=12), 36)

colnames(df) <- c("dates", "subtotal", "cust")

#add a "key" based on date and event
df$datekey <- paste0(df$dates, df$cust)

#The following 2 lines are specific to my own analysis but added to show depth
df_total_visits <- df %>% select(dates, cust) %>% distinct() %>% group_by(cust) %>% tally(n= "total_visits") %>% mutate(variable = 1)
df_order_bydate <-   df %>% select(dates, cust) %>% group_by(dates, cust) %>% tally(n= "day_orders") 


df <- left_join(df, df_total_visits)
df <- left_join(df, df_order_bydate) %>% arrange(dates)

# Now we will add the index, the arrange from the previous line is super important if your data is not already ordered by date
cummulative_groupping <- df %>% select(datekey, cust, variable, subtotal) %>% group_by(datekey) %>% mutate(spending = sum(subtotal)) %>% distinct(datekey, .keep_all = T) %>% select(-subtotal)
cummulative_groupping <- cummulative_groupping %>% group_by(cust) %>% mutate(cumulative_visits = cumsum(variable),
                                                                                    cumulative_spend = cumsum(spending))

df <- left_join(df, cummulative_groupping) %>% select(-variable)

#using the cumulative visits as the index, if we add one to this number we can then join it again on our dataframe
last_date_index <- df %>% select(dates, cust, cumulative_visits)
last_date_index$cumulative_visits <- last_date_index$cumulative_visits + 1 
colnames(last_date_index) <- c("last_visit_date", "cust", "cumulative_visits")
df <- left_join(df, last_date_index, by = c("cust", "cumulative_visits"))


#the difference between the date and last visit answers the original posters question.  NAs will return as NA
df$toa <- df$dates - df$last_visit_date

这个答案适用于同一天发生相同事件的情况（可能是数据不规范或者多个供应商/客户参加了该事件）。感谢您查看我的答案。这实际上是我在Stack上的第一篇帖子。

- Bryan Wammack