Rows sequence by group using two columns

Question

Suppose I have the following df

data <- data.frame(ID = c(1,1,1,1,1,1,1,2,2,2,2,3,3,3),
               Value = c(1,1,0,1,0,1,1,1,0,0,1,0,0,0),
               Result = c(1,1,2,3,4,5,5,1,2,2,3,1,1,1))

How can I obtain column Result from the first two columns?

I have tried different approaches using rle, seq, cumsum and cur_group_id but can't get the Result column easily

Can you clarify what you mean by obtaining the result column. What calculation are you trying to do to achieve the `Result` column? — Kent Orr, Nov 11 '20 at 16:34
@KentOrr Have a look at the answers, I was trying to create an identifier for each combination of ID and value. The identifier must be the same for both ID and value, and increase when the value of 'Value' column changes — torakxkz, Nov 11 '20 at 16:41

score 1 · Accepted Answer · answered Nov 11 '20 at 16:32

library(data.table)
library(dplyr)

data %>% 
  group_by(ID) %>% 
  mutate(Result2 = rleid(Value))

This gives us:

     ID Value Result Result2
   <dbl> <dbl>  <dbl>   <int>
 1     1     1      1       1
 2     1     1      1       1
 3     1     0      2       2
 4     1     1      3       3
 5     1     0      4       4
 6     1     1      5       5
 7     1     1      5       5
 8     2     1      1       1
 9     2     0      2       2
10     2     0      2       2
11     2     1      3       3
12     3     0      1       1
13     3     0      1       1
14     3     0      1       1

That was fast, elegant and direct. Thank you – torakxkz Nov 11 '20 at 16:36 — torakxkz, Nov 11 '20 at 16:36

score 1 · Answer 2 · answered Nov 11 '20 at 16:36

Does this work:

library(dplyr)
data %>% group_by(ID) %>% mutate(r = rep(seq_along(rle(ID*Value)$values), rle(ID*Value)$lengths))
# A tibble: 14 x 4
# Groups:   ID [3]
      ID Value Result     r
   <dbl> <dbl>  <dbl> <int>
 1     1     1      1     1
 2     1     1      1     1
 3     1     0      2     2
 4     1     1      3     3
 5     1     0      4     4
 6     1     1      5     5
 7     1     1      5     5
 8     2     1      1     1
 9     2     0      2     2
10     2     0      2     2
11     2     1      3     3
12     3     0      1     1
13     3     0      1     1
14     3     0      1     1

Thank you, for your dplyr only based response. I was trying to do something similar with seq_along and rle but failed — torakxkz, Nov 11 '20 at 16:39

score 1 · Answer 3 · answered Nov 11 '20 at 21:05

1

We could use rle with ave in base R

data$Result2 <- with(data, ave(Value, ID, FUN = 
   function(x) inverse.rle(within.list(rle(x), values <- seq_along(values)))))
data$Result2
#[1] 1 1 2 3 4 5 5 1 2 2 3 1 1 1

answered Nov 11 '20 at 21:05

akrun

874,273
37
540
662

Rows sequence by group using two columns

3 Answers3