Boudewijn Aasman
Boudewijn Aasman

Reputation: 1256

Increase counter by 1 for each unique group of values

I want to create a continually increasing counter for each group, where each group is a unique combination of person and day.

This is what the data looks like:

> df
  person      date
1      0    monday
2      0   tuesday
3      1    monday
4      1    monday
5      1   tuesday
6      2    monday
7      2    monday
8      2   tuesday
9      2 wednesday

Thus, I want to add a new variable starts at 1, and adds for for each new combination of person and day.

> df
  person      date counter
1      0    monday       1
2      0   tuesday       2
3      1    monday       3
4      1    monday       3
5      1   tuesday       4
6      2    monday       5
7      2    monday       5
8      2   tuesday       6
9      2 wednesday       7

I hope that the data is clear enough. The counter continues until it reaches the end of the data set.

Upvotes: 6

Views: 271

Answers (2)

mpalanco
mpalanco

Reputation: 13580

Base package:

df1 <- data.frame(unique(df), counter= 1:nrow(unique(df)))
merge(df, df1)

Output:

  person      date counter
1      0    monday       1
2      0   tuesday       2
3      1    monday       3
4      1    monday       3
5      1   tuesday       4
6      2    monday       5
7      2    monday       5
8      2   tuesday       6
9      2 wednesday       7

Upvotes: 2

akrun
akrun

Reputation: 887991

You can use rleid from the devel version of data.table. Instructions to install the devel version are here

 library(data.table)#v.9.5+
 setDT(df)[, counter:= rleid(date)][]
 #    person      date counter
 # 1:      0    monday       1
 # 2:      0   tuesday       2
 # 3:      1    monday       3
 # 4:      1    monday       3
 # 5:      1   tuesday       4
 # 6:      2    monday       5
 # 7:      2    monday       5
 # 8:      2   tuesday       6
 # 9:      2 wednesday       7

Or

library(dplyr)
df %>%  
   mutate(counter= cumsum(date!=lag(date, default=FALSE)))

Upvotes: 5

Related Questions