R: Assign incremental ids based on the groups

Question

I have the following sample data frame:

> test = data.frame(UserId = sample(1:5, 10, replace = T)) %>% arrange(UserId)
> test
   UserId
1       1
2       1
3       1
4       1
5       1
6       3
7       4
8       4
9       4
10      5

I now want another column called loginCount for that user, which is something like assigning incremental ids within each group, something like below. Using the mutate like below creates id within each group, but how do I get the incremental ids within each group independent of each other ?

> test %>% mutate(loginCount = group_indices_(test, .dots = "UserId"))
   UserId loginCount
1       1          1
2       1          1
3       1          1
4       1          1
5       1          1
6       3          2
7       4          3
8       4          3
9       4          3
10      5          4

I want something like shown below:

UserId     loginCount
1          1
1          2
1          3
1          4
1          5
3          1
4          1
4          2
4          3
5          1

score 2 · Accepted Answer · answered Apr 08 '19 at 22:50

You could group and use row_number:

test %>% 
  arrange(UserId) %>%
  group_by(UserId) %>% 
  mutate(loginCount = row_number()) %>%
  ungroup()

# A tibble: 10 x 2
# Groups:   UserId [4]
   UserId loginCount
    <int>      <int>
 1      1          1
 2      1          2
 3      1          3
 4      1          4
 5      1          5
 6      3          1
 7      4          1
 8      4          2
 9      4          3
10      5          1

Thank you @neilfws for your help :) – Vipin Verma Apr 08 '19 at 23:00 — Vipin Verma, Apr 08 '19 at 23:00

score 1 · Answer 2 · answered Apr 08 '19 at 23:01

One solution using base R tapply()

test$loginCount <- unlist(tapply(rep(1, nrow(test)), test$UserId, cumsum))

> test
   UserId loginCount
1       1          1
2       1          2
3       1          3
4       1          4
5       1          5
6       3          1
7       4          1
8       4          2
9       4          3
10      5          1

R: Assign incremental ids based on the groups

2 Answers2