R: How to start a new sub_id each time a new sequence begins

Question

Suppose I have data as follows:

tibble(
    A = c(1, 2, 2, 2, 2, 2, 3, 3, 3, 3, 4, 4, 4, 4, 4, 5),
    B = c(1, 1, 2, 1, 2, 3, 1, 2, 1, 1, 1, 2, 3, 4, 1, 1),
)

i.e.,

# A tibble: 16 x 2
       A     B
   <dbl> <dbl>
 1     1     1
 2     2     1
 3     2     2
 4     2     1
 5     2     2
 6     2     3
 7     3     1
 8     3     2
 9     3     1
10     3     1
11     4     1
12     4     2
13     4     3
14     4     4
15     4     1
16     5     1

How do I create a sub_id each time a new sequence begins within the group defined by variable A, i.e.,

tibble(
    A = c(1, 2, 2, 2, 2, 2, 3, 3, 3, 3, 4, 4, 4, 4, 4, 5),
    B = c(1, 1, 2, 1, 2, 3, 1, 2, 1, 1, 1, 2, 3, 4, 1, 1),
    sub_id = c(1, 1, 1, 2, 2, 2, 1, 1, 2, 3, 1, 1, 1, 1, 2, 1)
)

# A tibble: 16 x 3
       A     B sub_id
   <dbl> <dbl>  <dbl>
 1     1     1      1
 2     2     1      1
 3     2     2      1
 4     2     1      2
 5     2     2      2
 6     2     3      2
 7     3     1      1
 8     3     2      1
 9     3     1      2
10     3     1      3
11     4     1      1
12     4     2      1
13     4     3      1
14     4     4      1
15     4     1      2
16     5     1      1

Hopefully that’s well defined. I suppose I’m after a kind of inverse to row_number

Thanks in advance,

James.

score 3 · Answer 1 · answered May 29 '21 at 12:39

You got the "ingredients" already laid out.

(i) for each group of column A (ii) check if a new sequence starts

The following is based on {dplyr}. For demo purposes, I create an additional column/variable to show the "start condition". You can combine this into one call.

I use the fact that summing over TRUE/FALSE codes TRUE as 1. If this is not evident for you, you can use as.numeric(B == 1)

library(dplyr)
library(tibble)

# load example data
df <- tibble(
    A = c(1, 2, 2, 2, 2, 2, 3, 3, 3, 3, 4, 4, 4, 4, 4, 5),
    B = c(1, 1, 2, 1, 2, 3, 1, 2, 1, 1, 1, 2, 3, 4, 1, 1),
    sub_id = c(1, 1, 1, 2, 2, 2, 1, 1, 2, 3, 1, 1, 1, 1, 2, 1)
)

# perform group-wise operations 
df %>% 
   group_by(A) %>% 

   mutate(
# --------------- highlight start of new sequence --------------
      start = B == 1
# --------------- create cumsum over TRUEs----------------------
    , sub_id2 = cumsum(start)
)

This yields what you looked for:

# A tibble: 16 x 5
# Groups:   A [5]
       A     B sub_id start sub_id2
   <dbl> <dbl>  <dbl> <lgl>   <int>
 1     1     1      1 TRUE        1
 2     2     1      1 TRUE        1
 3     2     2      1 FALSE       1
 4     2     1      2 TRUE        2
 5     2     2      2 FALSE       2
 6     2     3      2 FALSE       2
 7     3     1      1 TRUE        1
 8     3     2      1 FALSE       1
 9     3     1      2 TRUE        2
10     3     1      3 TRUE        3
11     4     1      1 TRUE        1
12     4     2      1 FALSE       1
13     4     3      1 FALSE       1
14     4     4      1 FALSE       1
15     4     1      2 TRUE        2
16     5     1      1 TRUE        1

Brilliant! I didn’t realise you could do that with mutate (after group_by). Thanks! — James Bejon, May 29 '21 at 13:20

score 3 · Accepted Answer · answered May 29 '21 at 19:20

3

Using base R

df$sub_id <- with(df, ave(B ==1, A, FUN = cumsum))

answered May 29 '21 at 19:20

akrun

874,273
37
540
662

1

Very neat. Thank you. Did not realise base was so flexible. – James Bejon May 30 '21 at 13:29

TarJae · Answer 3 · 2021-05-29T12:59:20.257

We could use group_by and cumsum:

library(dplyr)

df %>%
  group_by(A) %>%
  mutate(sub_id = cumsum(B==1)

Output:

# Groups:   A [5]
       A     B sub_id
   <dbl> <dbl>  <int>
 1     1     1      1
 2     2     1      1
 3     2     2      1
 4     2     1      2
 5     2     2      2
 6     2     3      2
 7     3     1      1
 8     3     2      1
 9     3     1      2
10     3     1      3
11     4     1      1
12     4     2      1
13     4     3      1
14     4     4      1
15     4     1      2
16     5     1      1

score 1 · Answer 4 · answered May 29 '21 at 21:21

A data.table option

> setDT(df)[, sub_id := cumsum(B == 1), A][]
    A B sub_id
 1: 1 1      1
 2: 2 1      1
 3: 2 2      1
 4: 2 1      2
 5: 2 2      2
 6: 2 3      2
 7: 3 1      1
 8: 3 2      1
 9: 3 1      2
10: 3 1      3
11: 4 1      1
12: 4 2      1
13: 4 3      1
14: 4 4      1
15: 4 1      2
16: 5 1      1

R: How to start a new sub_id each time a new sequence begins

4 Answers4