Home Create column with ID starting at 1 and increment when value in another column changes in R

Questions

Create column with ID starting at 1 and increment when value in another column changes in R

November 29, 2022

I have a data frame like so:

ID <- c('A','A','A','A','A','A','A','A','A','A','A','B','B','B','B','B')
val1 <- c(0,1,2,3,4,5,6,7,8,9,10,11,0,1,2,3)
val2 <- c(0,1,2,3,4,5,0,1,0,1,2,0,1,0,1,2)

df <- data.frame(ID, val1, val2)

Output:

   ID val1 val2
1   A    0    0
2   A    1    1
3   A    2    2
4   A    3    3
5   A    4    4
6   A    5    5
7   A    6    0
8   A    7    1
9   A    8    0
10  A    9    1
11  A   10    2
12  B   11    0
13  B    0    1
14  B    1    0
15  B    2    1
16  B    3    2

I am trying to create a third column (val 3) which is like an index. When val1 = 0 and val 2 = 0 it should be 1 (this is also grouped by ID). It should stay as one and then increment by 1 until val2 = 0 again, like so showing desired output:

   ID val1 val2 val3
1   A    0    0    1
2   A    1    1    1
3   A    2    2    1
4   A    3    3    1
5   A    4    4    1
6   A    5    5    1
7   A    6    0    2
8   A    7    1    2
9   A    8    0    3
10  A    9    1    3
11  A   10    2    3
12  B   11    0    1
13  B    0    1    1
14  B    1    0    2
15  B    2    1    2
16  B    3    2    2

How can this be achieved? I tried:

df <- df %>%
  group_by(ID, val2) %>%
  mutate(val3 = row_number())

And:

df$val3 <- cumsum(c(1,diff(df$val2)==0))

But neither provide the desired outcome.

>Solution :

Inside cumsum use the logical comparison val2==0

  df %>% 
    group_by(ID) %>% 
    mutate(val3 = cumsum(val2==0))
# A tibble: 16 × 4
# Groups:   ID [2]
   ID     val1  val2  val3
   <chr> <dbl> <dbl> <int>
 1 A         0     0     1
 2 A         1     1     1
 3 A         2     2     1
 4 A         3     3     1
 5 A         4     4     1
 6 A         5     5     1
 7 A         6     0     2
 8 A         7     1     2
 9 A         8     0     3
10 A         9     1     3
11 A        10     2     3
12 B        11     0     1
13 B         0     1     1
14 B         1     0     2
15 B         2     1     2
16 B         3     2     2