1

How can I increment a column when there is a change within a group?

Example data.table:

library(data.table)
dt = data.table(id = c(1,1,1,1,2,2), loc = c(1,1,1,2,1,1), var = c("N","Y","N","N","N","N"))

   id loc var
1:  1   1   N
2:  1   1   Y
3:  1   1   N
4:  1   2   N
5:  2   1   N
6:  2   1   N

I would like to group by id and loc and whenever the column var changes I would like to add 1 to a new column.

Desired output:

   id loc var V2
1:  1   1   N  1
2:  1   1   Y  2
3:  1   1   N  3
4:  1   2   N  1
5:  2   1   N  1
6:  2   1   N  1
Frank
  • 66,179
  • 8
  • 96
  • 180
Kristofersen
  • 2,736
  • 1
  • 15
  • 31
  • not a duplicate. using .grp will add `v2 = c(1,1,1,2,3)` – Kristofersen Jan 23 '17 at 17:16
  • 1
    @Kristofersen What about http://stackoverflow.com/q/27959280/ ? Hmm, not sure that fits either... – Frank Jan 23 '17 at 17:17
  • 1
    @Frank yeah, that one is the same. Didn't find that when I searched. Should I delete the question? – Kristofersen Jan 23 '17 at 17:18
  • Nah, should keep it and mark as dupe in my opinion. Your title and example are clear and the dupe-closing just makes your post into a signpost for others. – Frank Jan 23 '17 at 17:19
  • @Frank, I'm failing. just added the duplicate to the wrong link. Not sure how to undo. – Kristofersen Jan 23 '17 at 17:20
  • It's ok. We can still change it. I'm not convinced that's the best dupe target, even though the answer fits. Still looking... – Frank Jan 23 '17 at 17:21
  • Ok, closing with a basic rleid dupe. Josliber's notes on rle might also be worth a look: http://stackoverflow.com/documentation/r/1133/run-length-encoding#t=201701231725477543017 – Frank Jan 23 '17 at 17:28
  • 1
    @Frank Thanks, I'll check it out. – Kristofersen Jan 23 '17 at 17:29

1 Answers1

1
dt[ , rleid(var),by = .(id, loc)]
#   id loc V1
#1:  1   1  1
#2:  1   1  2
#3:  1   1  3
#4:  1   2  1
#5:  2   1  1
#6:  2   1  1
joel.wilson
  • 8,243
  • 5
  • 28
  • 48