Create bins based on values for each group in R

user_13855675 0
Viewed 210

I have the following data:

test1<- data.frame("ID"= c("1","1","1", "2","2","2","3"), "TimeElapsed"=c(0,10,40,0,40,12,0))

Looks like:

ID  TimeElapsed
1    0
1    10
1    40
2    0
2    40
2    12
3    0

I want to create bins/sessions such that whenever time elapsed since last entry of an ID is more than 30, I want to start a new session for that ID. I use the following code from dplyr.

test1<-test1 %>%
  group_by(ID) %>%
mutate(cut_session = (TimeElapsed > 30) + 1)

I get this result:

ID  TimeElapsed Session
1    0            1
1    10           1 
1    40           2 
2    0            1  
2    40           2
2    12           1     <- Doesn't work for this case         
3    0            1

I would like a solution that gives me session= 2 in the second last row since time elapsed is <30, it should be the same session. Many thanks in advance.

dplyr r
2 Answers

Perhaps, we can use cumsum to add 1 whenever there the logical value is TRUE (TRUE/FALSE is 1/0). After grouping by 'ID', get the cumulative sum of logical vector

library(dplyr)
test1 %>%
  group_by(ID) %>%
  mutate(cut_session = cumsum(TimeElapsed > 30))

How it works with cumsum? Consider a logical vector

v1 <- c(TRUE, FALSE, TRUE, FALSE, FALSE)
cumsum(v1)
#[1] 1 1 2 2 2

Whenever it hits a TRUE, 1 is added to the previous until it reaches the next TRUE values

If we need to add 1, then use

 cumsum(v1) + 1

Here, + is a symbol used explicitly for adding i.e. getting the sum of two objects on the lhs and rhs of it. But, it would behave differently when there is an NA i.e.

NA + 1

returns NA

user_37322710

Use of cumsum is ideal here. I've mutated over two rows just to show you what's going on.

  1. is TimeElapsed over 30?
  2. Cumulatively count over 30s (adding 1 to make 1 the starting number)
library(dplyr)

test1<- data.frame("ID"= c("1","1","1", "2","2","2","3"), "TimeElapsed"=c(0,10,40,0,40,12,0))

test1 %>% 
  group_by(ID) %>% 
  mutate(over30 = TimeElapsed > 30,
         cut_session = cumsum(over30) + 1)
#> # A tibble: 7 x 4
#> # Groups:   ID [3]
#>   ID    TimeElapsed over30 cut_session
#>   <chr>       <dbl> <lgl>        <dbl>
#> 1 1               0 FALSE            1
#> 2 1              10 FALSE            1
#> 3 1              40 TRUE             2
#> 4 2               0 FALSE            1
#> 5 2              40 TRUE             2
#> 6 2              12 FALSE            2
#> 7 3               0 FALSE            1

Created on 2020-07-02 by the reprex package (v0.3.0)

user_96632070
Related