counting the rows using group_by of two other columns in r

user_3659286 0
Viewed 32

I have data as below. I would like to add a new column that counts whenever column code changes and when ID changes it resets and counter to 1 and start counting.

ID  code   
1    10     
1    10     
1    11     
1    11     
1    21     
1    21     
2    10     
2    10     
2    11     
2    11     
2    11     
2    14     
2    15

result:

ID  code   counter
1    10     1
1    10     1
1    11     2
1    11     2
1    21     3
1    21     3
2    10     1
2    10     1
2    11     2
2    11     2
2    11     2
2    14     3
2    15     4
r group-by
2 Answers

We may use cumsum along with duplicated as in

df %>% group_by(ID) %>% mutate(counter = cumsum(!duplicated(code)))
# A tibble: 13 x 3
# Groups:   ID [2]
#       ID  code counter
#    <int> <int>   <int>
#  1     1    10       1
#  2     1    10       1
#  3     1    11       2
#  4     1    11       2
#  5     1    21       3
#  6     1    21       3
#  7     2    10       1
#  8     2    10       1
#  9     2    11       2
# 10     2    11       2
# 11     2    11       2
# 12     2    14       3
# 13     2    15       4

If code reverted back, say, from 11 to 10, then counter wouldn't increase. But I guess either that's not possible in your case or that would even be the desired effect.

Here's how duplicated works in this case:

cbind(df[df$ID == 1, "code"], !duplicated(df[df$ID == 1, "code"]))
#      [,1] [,2]
# [1,]   10    1
# [2,]   10    0
# [3,]   11    1
# [4,]   11    0
# [5,]   21    1
# [6,]   21    0

Whenever a new value in code appears, it gives a one, and then cumsum finishes the job.

user_13205350

You can do this with dplyr, using lag to find rows where code changes:

library(dplyr)

df %>% 
    group_by(ID) %>% 
    mutate(counter = cumsum(c(1, tail(code != lag(code), -1))))

Result:

      ID  code counter
   <int> <int>   <dbl>
 1     1    10       1
 2     1    10       1
 3     1    11       2
 4     1    11       2
 5     1    21       3
 6     1    21       3
 7     2    10       1
 8     2    10       1
 9     2    11       2
10     2    11       2
11     2    11       2
12     2    14       3
13     2    15       4
user_12225780
Related