CODEWITHSUNDEEP
rdata.table
daRknight
daRknight

Reputation: 243

Create a Session ID from User ID and time differences

I have a similar question to this (Create a "sessionID" based on "userID" and differences in "timeStamp") on creating a 'Session ID'; though my specifications are slightly different. Perhaps the solution is still apparent in this post but I could not apply it to my needs -- pointing out how the original solution satisfies my question would be equivalent.

My data.table looks like this (dput available below):

unique_visitor_id        datetime            
100                 2016-07-25 15:43:02      
100                 2016-08-15 15:35:16      
101                 2016-08-01 21:24:46      
101                 2016-08-13 05:32:27      
101                 2016-08-13 05:33:01      
101                 2016-08-13 05:33:37      
101                 2016-08-13 05:34:04      
101                 2016-08-13 05:37:42      
101                 2016-08-13 05:38:20      
102                 2016-09-15 17:28:00      
102                 2016-09-15 17:31:04      
103                 2016-07-18 21:19:07

NB: datetime was converted to a date object in lubridate via ymd_hms(datetime)

What I'd like is a new variable identifying the session, which is a simple integer sequence (does not need to incorporate the visitorID, like the original question) -- a session is defined by visitor, as long as records are <= 30m AND within the same day. So for example, the first two rows would be two different sessions: though it's the same visitor, the difference in time is >30m.

The desired output from the above data would be:

unique_visitor_id        datetime            session_id
100                 2016-07-25 15:43:02           1
100                 2016-08-15 15:35:16           2
101                 2016-08-01 21:24:46           3
101                 2016-08-13 05:32:27           4
101                 2016-08-13 05:33:01           4
101                 2016-08-13 05:33:37           4
101                 2016-08-13 05:34:04           4
101                 2016-08-13 05:37:42           4
101                 2016-08-13 05:38:20           4
102                 2016-09-15 17:28:00           5
102                 2016-09-15 17:31:04           5
103                 2016-07-18 21:19:07           6

If this can be done in a data.table way, that would be desirable. Again, apologies if I am missing something from the original question's solution!

Here is the dput sample data table:

myDT <- structure(list(unique_visitor_id = c(100L, 100L, 101L, 
                                 101L, 101L, 101L, 101L, 101L, 101L, 102L, 102L, 103L), 
           datetime = structure(c(1469475782, 1471289716, 1470101086, 1471080747, 1471080781, 
                                            1471080817, 1471080844, 1471081062, 1471081100, 1473974880, 
                                            1473975064, 1468891147), 
                                          tzone = "EST5EDT", class = c("POSIXct", "POSIXt"))), 
      .Names = c("unique_visitor_id", "datetime"), 
      sorted = c("unique_visitor_id", "datetime"), 
      class = c("data.table", "data.frame"), 
      row.names = c(NA, -12L))

Upvotes: 4

Views: 1624

Answers (2)

DaveH
DaveH

Reputation: 191

Same idea with dplyr. 

library(dplyr)
library(lubridate)
myDT %>% 
    mutate(new_session = c(0, diff(datetime)) > 30*60 |
                         c(0, diff(unique_visitor_id)) != 0 ) %>%
    mutate(session_id = cumsum(new_session)) %>% print()

BTW, you need to add a test case for new user same time (both these answers should cover that). Of course you can eliminate the new_session column, I just found it helpful.

Upvotes: 3

akuiper
akuiper

Reputation: 214957

Assuming your data frame is originally sorted by visitor id and datetime, you can use cumsum() on the condition vector which is TRUE where a new session_id should appear:

myDT[, session_id := cumsum(c(T, diff(unique_visitor_id) != 0 | diff(datetime)/60 > 30))][]

#    unique_visitor_id            datetime session_id
# 1:               100 2016-07-25 15:43:02          1
# 2:               100 2016-08-15 15:35:16          2
# 3:               101 2016-08-01 21:24:46          3
# 4:               101 2016-08-13 05:32:27          4
# 5:               101 2016-08-13 05:33:01          4
# 6:               101 2016-08-13 05:33:37          4
# 7:               101 2016-08-13 05:34:04          4
# 8:               101 2016-08-13 05:37:42          4
# 9:               101 2016-08-13 05:38:20          4
#10:               102 2016-09-15 17:28:00          5
#11:               102 2016-09-15 17:31:04          5
#12:               103 2016-07-18 21:19:07          6

Upvotes: 8

Related Questions