CODEWITHSUNDEEP
rcountsequence
Tim
Tim

Reputation: 75

R: count consecutive occurrences of values in a single column and by group

I am trying to create a sequential number of equal values, a count of occurrences. However, I want the count to reset once a new ID is introduced even if the the row remains sequential.

Example of what my data looks like:

dataset <- data.frame(ID = 
c("a","a","a","a","a","a","a","b","b","b","b","b","b","b")
dataset$YesNO <- c(1,1,0,0,0,1,1,1,1,1,0,0,0,0)

So I want to create a new column with the results in:

c(1,2,1,2,3,1,2,1,2,3,1,2,3,4)

I've used this code that I've found on this forum:

dataset$Counter <- sequence(rle(as.character(dataset$YesNo))$lengths)

However, this doesn't reset the count for the new ID number. Instead the sequential count continues and the resulting output is:

c(1,2,1,2,3,1,2,3,4,5,1,2,3,4)

What step am I missing to have it reset based on the ID.

Thank you!

Upvotes: 7

Views: 7348

Answers (3)

tmfmnk
tmfmnk

Reputation: 40171

Also one dplyr possibility:

dataset %>%
 group_by(ID, grp = with(rle(YesNO), rep(seq_along(lengths), lengths))) %>%
 mutate(Counter = seq_along(grp)) %>%
 ungroup() %>%
 select(-grp)

   ID    YesNO Counter
   <fct> <dbl>   <int>
 1 a        1.       1
 2 a        1.       2
 3 a        0.       1
 4 a        0.       2
 5 a        0.       3
 6 a        1.       1
 7 a        1.       2
 8 b        1.       1
 9 b        1.       2
10 b        1.       3
11 b        0.       1
12 b        0.       2
13 b        0.       3
14 b        0.       4

Or:

dataset %>%
 group_by(ID, grp = with(rle(YesNO), rep(seq_along(lengths), lengths))) %>%
 mutate(Counter = 1:n()) %>%
 ungroup() %>%
 select(-grp)

Upvotes: 8

G. Grothendieck
G. Grothendieck

Reputation: 270248

Use rleid (from the data.table package) to get a grouping variable and then use ave to apply seq_along within common values of that grouping:

library(data.table)
transform(dataset, Counter = ave(YesNO, rleid(ID, YesNO), FUN = seq_along))

giving:

   ID YesNO Counter
1   a     1       1
2   a     1       2
3   a     0       1
4   a     0       2
5   a     0       3
6   a     1       1
7   a     1       2
8   b     1       1
9   b     1       2
10  b     1       3
11  b     0       1
12  b     0       2
13  b     0       3
14  b     0       4

Upvotes: 6

arg0naut91
arg0naut91

Reputation: 14774

You could do:

dataset$Counter <- with(dataset,
                        ave(YesNO, ID, FUN = function(x) sequence(rle(as.character(x))$lengths)))

Output:

   ID YesNO Counter
1   a     1       1
2   a     1       2
3   a     0       1
4   a     0       2
5   a     0       3
6   a     1       1
7   a     1       2
8   b     1       1
9   b     1       2
10  b     1       3
11  b     0       1
12  b     0       2
13  b     0       3
14  b     0       4

Upvotes: 0

Related Questions