CODEWITHSUNDEEP
r
Marc Moroccoholic
Marc Moroccoholic

Reputation: 55

Creating a Session/Visit ID in R

Assume I have a dataset that looks like the following:

                 id                  date.time          date.time1     diff      n.diff  
___984A39Pok8OrVfPhn2Q 2013-09-14 16:21:09 2013-09-14 16:21:51     -42 secs     42   
___984A39Pok8OrVfPhn2Q 2013-09-14 16:21:51 2013-09-14 16:22:22     -31 secs     31   
___984A39Pok8OrVfPhn2Q 2013-09-14 16:22:22 2013-09-15 11:42:24  -69602 secs  69602   
___984A39Pok8OrVfPhn2Q 2013-09-15 11:42:24 2013-09-15 11:42:39     -15 secs     15   
___984A39Pok8OrVfPhn2Q 2013-09-15 11:42:39 2013-09-15 11:43:02     -23 secs     23   
___984A39Pok8OrVfPhn2Q 2013-09-15 11:43:02 2013-09-15 11:43:55     -53 secs     53   
___984A39Pok8OrVfPhn2Q 2013-09-15 11:43:55 2013-09-15 11:44:28     -33 secs     33   
___984A39Pok8OrVfPhn2Q 2013-09-15 11:44:28 2013-09-15 11:45:03     -35 secs     35   
___984A39Pok8OrVfPhn2Q 2013-09-15 11:45:03                <NA>      NA secs     NA   
___WYOJCtZPSqDqtBYaiVA 2013-09-09 23:04:32 2013-09-09 23:05:05     -33 secs     33   
___WYOJCtZPSqDqtBYaiVA 2013-09-09 23:05:05                <NA>      NA secs     NA   
__-C0RFVOSmYSHyoEn-IYw 2013-09-11 23:18:11 2013-09-11 23:18:26     -15 secs     15   
__-C0RFVOSmYSHyoEn-IYw 2013-09-11 23:18:26 2013-09-11 23:18:31      -5 secs      5   
__-C0RFVOSmYSHyoEn-IYw 2013-09-11 23:18:31 2013-09-11 23:19:11     -40 secs     40   
__-C0RFVOSmYSHyoEn-IYw 2013-09-11 23:19:11 2013-09-11 23:19:31     -20 secs     20   
__-C0RFVOSmYSHyoEn-IYw 2013-09-11 23:19:31 2013-09-12 17:07:54  -64103 secs  64103   
__-C0RFVOSmYSHyoEn-IYw 2013-09-12 17:07:54 2013-09-12 17:08:02      -8 secs      8   
__-C0RFVOSmYSHyoEn-IYw 2013-09-12 17:08:02 2013-09-12 17:08:15     -13 secs     13   
__-C0RFVOSmYSHyoEn-IYw 2013-09-12 17:08:15 2013-09-12 17:08:22      -7 secs      7

I want to create a session id by user that consists of all instances where the maximum time out between two sessions is no longer than 30 minutes or 1800 seconds. I should end up with something like this:

            id                  date.time          date.time1     diff      n.diff   session
___984A39Pok8OrVfPhn2Q 2013-09-14 16:21:09 2013-09-14 16:21:51     -42 secs     42      1
___984A39Pok8OrVfPhn2Q 2013-09-14 16:21:51 2013-09-14 16:22:22     -31 secs     31      1
___984A39Pok8OrVfPhn2Q 2013-09-14 16:22:22 2013-09-15 11:42:24  -69602 secs  69602      1
___984A39Pok8OrVfPhn2Q 2013-09-15 11:42:24 2013-09-15 11:42:39     -15 secs     15      2
___984A39Pok8OrVfPhn2Q 2013-09-15 11:42:39 2013-09-15 11:43:02     -23 secs     23      2
___984A39Pok8OrVfPhn2Q 2013-09-15 11:43:02 2013-09-15 11:43:55     -53 secs     53      2
___984A39Pok8OrVfPhn2Q 2013-09-15 11:43:55 2013-09-15 11:44:28     -33 secs     33      2
___984A39Pok8OrVfPhn2Q 2013-09-15 11:44:28 2013-09-15 11:45:03     -35 secs     35      2
___984A39Pok8OrVfPhn2Q 2013-09-15 11:45:03                <NA>      NA secs     NA      2
___WYOJCtZPSqDqtBYaiVA 2013-09-09 23:04:32 2013-09-09 23:05:05     -33 secs     33      1
___WYOJCtZPSqDqtBYaiVA 2013-09-09 23:05:05                <NA>      NA secs     NA      1
__-C0RFVOSmYSHyoEn-IYw 2013-09-11 23:18:11 2013-09-11 23:18:26     -15 secs     15      1
__-C0RFVOSmYSHyoEn-IYw 2013-09-11 23:18:26 2013-09-11 23:18:31      -5 secs      5      1
__-C0RFVOSmYSHyoEn-IYw 2013-09-11 23:18:31 2013-09-11 23:19:11     -40 secs     40      1
__-C0RFVOSmYSHyoEn-IYw 2013-09-11 23:19:11 2013-09-11 23:19:31     -20 secs     20      1
__-C0RFVOSmYSHyoEn-IYw 2013-09-11 23:19:31 2013-09-12 17:07:54  -64103 secs  64103      1
__-C0RFVOSmYSHyoEn-IYw 2013-09-12 17:07:54 2013-09-12 17:08:02      -8 secs      8      2
__-C0RFVOSmYSHyoEn-IYw 2013-09-12 17:08:02 2013-09-12 17:08:15     -13 secs     13      2
__-C0RFVOSmYSHyoEn-IYw 2013-09-12 17:08:15 2013-09-12 17:08:22      -7 secs      7      2

Any assistance is appreciated.

Upvotes: 0

Views: 76

Answers (1)

holzben
holzben

Reputation: 1471

First make sure that you are using Date and Time: 

# mat =  your posted data
mat[ , 2] <- as.POSIXct(mat[ , 2])
mat[ , 3] <- as.POSIXct(mat[ , 3])

than I calculated the differences between

date.time[i] and date.time[i - 1] using diff(). Since I compare the differences and use cumsum() to generate the IDs, the first element of the resulting logical vector must be TRUE.

The function for doing that looks as following:

 fun <- function(x, ses_TH) cumsum(c(TRUE, diff(x$date.time) > ses_TH))

where ses_TH is the session threshold, in your example 1800.

Now I use this function over all unique id´s:

get_session_id <- function(mat, ses_TH = 1800){
  fun <- function(x, ses_TH) cumsum(c(TRUE, diff(x$date.time) > ses_TH))
  unlist(sapply(split(mat, f = mat$ID), fun, ses_TH = ses_TH))
}

The result of get_session_id(mat) is than:

___984A39Pok8OrVfPhn2Q1 ___984A39Pok8OrVfPhn2Q2 ___984A39Pok8OrVfPhn2Q3 
                      1                       1                       1 
___984A39Pok8OrVfPhn2Q4 ___984A39Pok8OrVfPhn2Q5 ___984A39Pok8OrVfPhn2Q6 
                      2                       2                       2 
___984A39Pok8OrVfPhn2Q7 ___984A39Pok8OrVfPhn2Q8 ___984A39Pok8OrVfPhn2Q9 
                      2                       2                       2 
___WYOJCtZPSqDqtBYaiVA1 ___WYOJCtZPSqDqtBYaiVA2 __-C0RFVOSmYSHyoEn-IYw1 
                      1                       1                       1 
__-C0RFVOSmYSHyoEn-IYw2 __-C0RFVOSmYSHyoEn-IYw3 __-C0RFVOSmYSHyoEn-IYw4 
                      1                       1                       1 
__-C0RFVOSmYSHyoEn-IYw5 __-C0RFVOSmYSHyoEn-IYw6 __-C0RFVOSmYSHyoEn-IYw7 
                      1                       2                       2 
__-C0RFVOSmYSHyoEn-IYw8 
                      2

Upvotes: 1

Related Questions