CODEWITHSUNDEEP
r
Btibert3
Btibert3

Reputation: 40146

Create Web Sessions

I know that Randy has a great post on Sessonizing Log Data, but I am struggling with tweaking the idea to generate session ids based on 30-minute inactivity windows.

Here is what I am hoping to generate in R, and preferably, with dplyr. I am looking to calculate the session_id variable shown below.

   dim_user_id       activity_date session_id
1      2665871 2014-12-31 19:00:08         1
2      2665871 2014-12-31 19:00:45         1
3      2665871 2014-12-31 19:01:01         1
4      2665877 2014-12-31 19:00:08         2
5      2665877 2014-12-31 19:00:33         2
6      2666612 2014-12-31 19:08:19         3
7      2666612 2014-12-31 19:08:32         3
8      2666612 2014-12-31 19:09:04         3
9      2666626 2014-12-31 19:00:25         4
10     2666627 2014-12-31 19:04:39         5

The code that I was trying to use was:

user_activity$sid = 1:nrow(user_activity)
user_activity$session_id = NA
# startTime = Sys.time()
user_activity = user_activity %>% 
  group_by(dim_user_id) %>% 
  arrange(activity_date) %>% 
  transform(lag_seconds = ifelse(lag(dim_user_id) == dim_user_id, 
                                 as.numeric(activity_date - lag(activity_date)), 
                                 9999)) %>% 
  mutate(session_id = ifelse(is.na(lag_seconds) | lag_seconds >= 1801, sid, lag(session_id)))

but the issue that I am running into is that I do not believe the value is being set row-wise. I did explore the rowwwise function in dplyr, but I am stuck.

Thanks in advance.

Upvotes: 2

Views: 56

Answers (1)

Rentrop
Rentrop

Reputation: 21497

If i understand you correctly you are looking for group_indices which you can use as follows:

df %>% mutate(session_id = group_indices_(df, .dots="dim_user_id"))

EDIT: As your sample data does not provide a case where one user has multiple sessions with 30+ time diff i used this altered data set:

df <- read.table(header=TRUE, text="dim_user_id date  time
2665871 2014-12-31 19:00:08
2665871 2014-12-31 19:00:45
2665871 2014-12-31 19:01:01
2665877 2014-12-31 19:00:08
2665877 2014-12-31 19:00:33
2666612 2014-12-31 19:08:19
2666612 2014-12-31 19:38:32
2666612 2014-12-31 19:39:04
2666626 2014-12-31 19:00:25
2666627 2014-12-31 19:04:39")

df$activity_date <- as.POSIXct(paste(df$date, df$time))
df$date <- NULL
df$time <- NULL

So user #2666612 has a 30+ min lag. The following code calculates your session_id's step by step. I am sure it could be shortened but this is for clarification.

require(dplyr)
cuttoff <- 30*60 # 30 min times 60 seconds.
df %>% 
  # group by user_id
  group_by(dim_user_id) %>% 
  # Difference in seconds within a given user
  mutate(time_diff = c(0, diff(activity_date))) %>%
  # If the difference is >cutoff start new session
  mutate(session_num = cumsum(time_diff>cuttoff)) %>% 
  # ungroup to set group_indices data-wide instead of groupwide
  ungroup() %>% 
  # calculate group_indices based in user_id and session_num
  mutate(session_id = group_indices_(., .dots=c("dim_user_id", "session_num")))

Which results in:

Source: local data frame [10 x 5]

   dim_user_id       activity_date time_diff session_num session_id
         (int)              (time)     (dbl)       (int)      (int)
1      2665871 2014-12-31 19:00:08         0           0          1
2      2665871 2014-12-31 19:00:45        37           0          1
3      2665871 2014-12-31 19:01:01        16           0          1
4      2665877 2014-12-31 19:00:08         0           0          2
5      2665877 2014-12-31 19:00:33        25           0          2
6      2666612 2014-12-31 19:08:19         0           0          3
7      2666612 2014-12-31 19:38:32      1813           1          4
8      2666612 2014-12-31 19:39:04        32           1          4
9      2666626 2014-12-31 19:00:25         0           0          5
10     2666627 2014-12-31 19:04:39         0           0          6

Upvotes: 2

Related Questions