Cumulative sequence of values that repeat in R?

Question

My question is similar to this, however, it doesn't quite solve my issue.

I'm trying to create a cumulative sequence of numbers that repeats at a specific interval. For example, if I have a data frame that looks like this:

dfTest <- data.frame(
  ID = c("P", "L", "R",
         "P", "L", "R",
         "P", "L", "R",
         "P", "L", "R",
         "P", "L", "R",
         "P", "L", "R",
         "P", "L", "R",
         "P", "L", "R", "LL", "LR"), 
  number = c(1, 1, 1,
             2, 2, 2,
             3, 3, 3,
             4, 4, 4,
             5, 5, 5,
             6, 6, 6,
             7, 7, 7,
             8, 8, 8, 8, 8),
  iteration = c(1, 1, 1, 1, 1, 1,
                1, 1, 1, 1, 1, 1,
                2, 2, 2, 2, 2, 2, 
                2, 2, 2, 2, 2, 2, 2, 2)
)
> dfTest
   ID number iteration
1   P      1         1
2   L      1         1
3   R      1         1
4   P      2         1
5   L      2         1
6   R      2         1
7   P      3         1
8   L      3         1
9   R      3         1
10  P      4         1
11  L      4         1
12  R      4         1
13  P      5         2
14  L      5         2
15  R      5         2
16  P      6         2
17  L      6         2
18  R      6         2
19  P      7         2
20  L      7         2
21  R      7         2
22  P      8         2
23  L      8         2
24  R      8         2
25 LL      8         2
26 LR      8         2

We can see that the dfTest$number column is just cumsum(dfTest$ID=="P"). But what I want to do is create another column that basically contains the values from dfTest$number... but restarts every time there's a change in the dfTest$iteration column. For example, my desired output would be:

> dfTest
   ID number iteration Counter
1   P      1         1       1
2   L      1         1       1
3   R      1         1       1
4   P      2         1       2
5   L      2         1       2
6   R      2         1       2
7   P      3         1       3
8   L      3         1       3
9   R      3         1       3
10  P      4         1       4
11  L      4         1       4
12  R      4         1       4
13  P      5         2       1
14  L      5         2       1
15  R      5         2       1
16  P      6         2       2
17  L      6         2       2
18  R      6         2       2
19  P      7         2       3
20  L      7         2       3
21  R      7         2       3
22  P      8         2       4
23  L      8         2       4
24  R      8         2       4
25 LL      8         2       4
26 LR      8         2       4

Here, we can see that dfTest$Counter increases, just like dfTest$number... but once the value of dfTest$iteration changes, dfTest$Counter starts over again.

library(dplyr); df %>% group_by(iteration) %>% mutate(Counter = cumsum(ID == "P")` — Jon Spring, Nov 01 '21 at 03:34
@JonSpring this wont work in the case a `P` is missing or even in the case we have a non-arranged table. — Onyambu, Nov 01 '21 at 04:36

score 0 · Accepted Answer · answered Nov 01 '21 at 04:31

dfTest %>% 
 group_by(ID)%>% 
 mutate(Counter = row_number())
# A tibble: 26 x 4
# Groups:   ID [5]
   ID    number iteration Counter
   <chr>  <dbl>     <dbl>   <int>
 1 P          1         1       1
 2 L          1         1       1
 3 R          1         1       1
 4 P          2         1       2
 5 L          2         1       2
 6 R          2         1       2
 7 P          3         1       3
 8 L          3         1       3
 9 R          3         1       3
10 P          4         1       4

Cumulative sequence of values that repeat in R?

1 Answers1