Column that tracks when the value in another column changes

Question

Subset of my data:

library(tidyverse)

df <- structure(list(Score = c(18, 18, 20, 20, 19, 19, 19, 19, 19, 
19, 19, 19, 19, 17, 17, 17, 17, 17, 19, 19)), row.names = c(NA, 
-20L), class = c("tbl_df", "tbl", "data.frame"))

I want to create a column that will track the Score when it changes from being < 19 to >= 19 or vice versa.

I'm currently trying this:

df %>%
  mutate(temp = as.numeric(Score < 19))

Score temp
18  1           
18  1           
20  0           
20  0           
19  0           
19  0           
19  0           
19  0           
19  0           
19  0
19  0           
19  0           
19  0           
17  1           
17  1           
17  1           
17  1           
17  1           
19  0           
19  0

However, I want something that looks like this:

Any help is greatly appreciated.

Edit: ended up using this post.

As @Henrik noted: `data.table::rleid(df$Score >= 19)` – bouncyball Mar 30 '21 at 20:09 — bouncyball, Mar 30 '21 at 20:09

score 2 · Accepted Answer · answered Mar 30 '21 at 20:10

2

using rle

with(rle(df$Score >= 19), rep(seq_along(values), lengths))
#[1] 1 1 2 2 2 2 2 2 2 2 2 2 2 3 3 3 3 3 4 4

answered Mar 30 '21 at 20:10

akrun

874,273
37
540
662

score 2 · Answer 2 · answered Mar 30 '21 at 20:46

Another base R option using cumsum + diff

transform(
  df,
  temp = cumsum(c(1, diff(Score >= 19) != 0))
)

gives

   Score temp
1     18    1
2     18    1
3     20    2
4     20    2
5     19    2
6     19    2
7     19    2
8     19    2
9     19    2
10    19    2
11    19    2
12    19    2
13    19    2
14    17    3
15    17    3
16    17    3
17    17    3
18    17    3
19    19    4
20    19    4

Column that tracks when the value in another column changes

2 Answers2