Compute mean difference between dates based on a condition in R

user_8831251 0
Viewed 109

I have this dataset:

Date New_Renew
 2019-01-10 22:11:16  Renewing
 2019-02-23 00:21:48  Renewing
 2019-03-05 05:26:17  Renewing
 2019-04-18 15:05:10       NEW
 2019-04-18 15:07:52       NEW
 2019-04-26 11:32:25  Renewing
 2019-05-03 14:15:25  Renewing
 2019-05-08 21:10:08       NEW
 2019-05-16 13:35:57  Renewing
 2019-05-24 13:18:23  Renewing
 2019-06-01 12:42:21  Renewing
 2019-06-17 18:08:09  Renewing
 2019-06-26 13:40:29  Renewing
 2019-12-13 17:57:43  Renewing
 2020-01-03 11:49:14  Renewing
 2020-01-11 11:46:51  Renewing
 2020-01-14 21:08:08       NEW
 2020-01-18 21:14:30       NEW
 2020-01-21 16:08:37       NEW
 2020-01-28 11:41:44  Renewing
 2020-01-30 13:34:21  Renewing
 2020-02-03 13:29:37  Renewing
 2020-02-18 17:15:52  Renewing
 2020-02-20 13:37:52  Renewing
 2020-02-24 12:55:25  Renewing
 2020-02-26 21:13:38       NEW
 2020-03-04 13:23:41  Renewing
 2020-03-09 16:48:36  Renewing

What I want is, when the New_Renew variable is equal to NEW, to compute the mean of the difference between the dates associated to NEW. In simple words, how frequently is a user doing a NEW transaction.

r datetime
4 Answers

Using aggregate and diff. The 60*24 converts the yielding seconds to days.

aggregate(Date ~ New_Renew, dat, function(x) mean(diff(x))/(60*24))
#  New_Renew         Date
# 1       NEW 52.38292438 
# 2  Renewing  0.01471444

Or with tapply.

with(dat, tapply(Date, New_Renew, function(x) mean(diff(x))/(60*24)))
#         NEW    Renewing 
# 52.38292438  0.01471444

Data

dat <- structure(list(Date = structure(c(1547154676, 1550877708, 1551759977, 
1555592710, 1555592872, 1556271145, 1556885725, 1557342608, 1558006557, 
1558696703, 1559385741, 1560787689, 1561549229, 1576256263, 1578048554, 
1578739611, 1579032488, 1579378470, 1579619317, 1580208104, 1580387661, 
1580732977, 1582042552, 1582202272, 1582545325, 1582748018, 1583324621, 
1583768916), class = c("POSIXct", "POSIXt"), tzone = ""), New_Renew = c("Renewing", 
"Renewing", "Renewing", "NEW", "NEW", "Renewing", "Renewing", 
"NEW", "Renewing", "Renewing", "Renewing", "Renewing", "Renewing", 
"Renewing", "Renewing", "Renewing", "NEW", "NEW", "NEW", "Renewing", 
"Renewing", "Renewing", "Renewing", "Renewing", "Renewing", "NEW", 
"Renewing", "Renewing")), row.names = c(NA, -28L), class = "data.frame")
user_65740380

Using dplyr

library(dplyr)
df %>%
  filter(New_Renew=="NEW") %>%
  mutate(Date_next=lead(Date), duration=Date_next-Date) %>%
  summarise(mean=mean(duration, na.rm=TRUE))

# A tibble: 1 x 1
  mean         
  <drtn>       
1 75421.41 mins # ~ 52 days

Before overall summary:

# A tibble: 7 x 4
  Date                New_Renew Date_next           duration       
  <dttm>              <chr>     <dttm>              <drtn>         
1 2019-04-18 15:05:10 NEW       2019-04-18 15:07:52      2.700 mins
2 2019-04-18 15:07:52 NEW       2019-05-08 21:10:08  29162.267 mins
3 2019-05-08 21:10:08 NEW       2020-01-14 21:08:08 361438.000 mins
4 2020-01-14 21:08:08 NEW       2020-01-18 21:14:30   5766.367 mins
5 2020-01-18 21:14:30 NEW       2020-01-21 16:08:37   4014.117 mins
6 2020-01-21 16:08:37 NEW       2020-02-26 21:13:38  52145.017 mins
7 2020-02-26 21:13:38 NEW       NA                          NA mins
user_75145270

Create minimal reproducible example first

DT <- read.table(text = 'Date, New_Renew
 2019-01-10 22:11:16,Renewing
 2019-02-23 00:21:48,Renewing
 2019-03-05 05:26:17,Renewing
 2019-04-18 15:05:10,NEW
 2019-04-18 15:07:52,NEW
 2019-04-26 11:32:25,Renewing
 2019-05-03 14:15:25,Renewing
 2019-05-08 21:10:08,NEW
 2019-05-16 13:35:57,Renewing
 2019-05-24 13:18:23,Renewing
 2019-06-01 12:42:21,Renewing
 2019-06-17 18:08:09,Renewing
 2019-06-26 13:40:29,Renewing
 2019-12-13 17:57:43,Renewing
 2020-01-03 11:49:14,Renewing
 2020-01-11 11:46:51,Renewing
 2020-01-14 21:08:08,NEW
 2020-01-18 21:14:30,NEW
 2020-01-21 16:08:37,NEW
 2020-01-28 11:41:44,Renewing
 2020-01-30 13:34:21,Renewing
 2020-02-03 13:29:37,Renewing
 2020-02-18 17:15:52,Renewing
 2020-02-20 13:37:52,Renewing
 2020-02-24 12:55:25,Renewing
 2020-02-26 21:13:38,NEW
 2020-03-04 13:23:41,Renewing
 2020-03-09 16:48:36,Renewing', 
                 sep = ',', 
                 header = T)

Then we'll use xts to make convenient calculations on dates. The for loop is straight forward, stores the first renewal and subtracts the first new from 

library(data.table)
library(xts)
library(lubridate)
library(tbl2xts)

df <- xts(DT, order.by = ymd_hms(DT$Date))

new_items <- which(DT$New_Renew=="NEW")

dif <- DT

dif$difference <- NA

renewal <- 0

for (i in 1:nrow(df)){

  if (df[i,2]=='Renewing' & renewal == 0){
    renewal <- i
  } else if (df[i,2]=='Renewing' & renewal != 0){
    next
  } else if (df[i, 2]=='NEW' & renewal != 0) {
    dif[i, 'difference'] <- index(df[i, 2]) - index(df[renewal, 2])
    renewal <- i
  } else {
    dif[i, 'difference'] <- index(df[i, 2]) - index(df[renewal, 2])
    renewal <- 0
  }

}

mean_diff <- mean(dif$difference, na.rm = T)
user_97789460

An option using data.table:

DT[, Date := as.POSIXct(Date, format="%Y-%m-%d %T")][
    New_Renew=="NEW", mean(diff(Date))]

data:

library(data.table)
DT <- fread("Date,New_Renew
2019-01-10 22:11:16,Renewing
2019-02-23 00:21:48,Renewing
2019-03-05 05:26:17,Renewing
2019-04-18 15:05:10,     NEW
2019-04-18 15:07:52,     NEW
2019-04-26 11:32:25,Renewing
2019-05-03 14:15:25,Renewing
2019-05-08 21:10:08,     NEW
2019-05-16 13:35:57,Renewing
2019-05-24 13:18:23,Renewing
2019-06-01 12:42:21,Renewing
2019-06-17 18:08:09,Renewing
2019-06-26 13:40:29,Renewing
2019-12-13 17:57:43,Renewing
2020-01-03 11:49:14,Renewing
2020-01-11 11:46:51,Renewing
2020-01-14 21:08:08,     NEW
2020-01-18 21:14:30,     NEW
2020-01-21 16:08:37,     NEW
2020-01-28 11:41:44,Renewing
2020-01-30 13:34:21,Renewing
2020-02-03 13:29:37,Renewing
2020-02-18 17:15:52,Renewing
2020-02-20 13:37:52,Renewing
2020-02-24 12:55:25,Renewing
2020-02-26 21:13:38,     NEW
2020-03-04 13:23:41,Renewing
2020-03-09 16:48:36,Renewing")
user_19894800
Related