Enumerate groups within groups in a data.table

Question

This is related to multiple duplicates (1, 2, 3), but a slightly different problem that I'm stuck with. So far, I've seen pandas solution only.

In this data table:

dt = data.table(gr = rep(letters[1:2], each = 6), 
                cl = rep(letters[1:4], each = 3))

    gr cl
 1:  a  a
 2:  a  a
 3:  a  a
 4:  a  b
 5:  a  b
 6:  a  b
 7:  b  c
 8:  b  c
 9:  b  c
10:  b  d
11:  b  d
12:  b  d

I'd like to enumerate unique classes per group to obtain this:

    gr cl id
 1:  a  a  1
 2:  a  a  1
 3:  a  a  1
 4:  a  b  2
 5:  a  b  2
 6:  a  b  2
 7:  b  c  1
 8:  b  c  1
 9:  b  c  1
10:  b  d  2
11:  b  d  2
12:  b  d  2

score 4 · Accepted Answer · answered Jun 21 '19 at 12:18

4

Try

library(data.table)
dt[, id := rleid(cl), by=gr]
dt
#    gr cl id
# 1:  a  a  1
# 2:  a  a  1
# 3:  a  a  1
# 4:  a  b  2
# 5:  a  b  2
# 6:  a  b  2
# 7:  b  c  1
# 8:  b  c  1
# 9:  b  c  1
#10:  b  d  2
#11:  b  d  2
#12:  b  d  2

answered Jun 21 '19 at 12:18

markus

25,843
5
39
58

That's it! Had no idea about the `rleid`... – mattek Jun 21 '19 at 12:21

tmfmnk · Answer 2 · 2019-06-21T12:29:28.160

3

You can do (maybe it will require to sort the data first):

dt[, id := cumsum(!duplicated(cl)), by = gr]

    gr cl id
 1:  a  a  1
 2:  a  a  1
 3:  a  a  1
 4:  a  b  2
 5:  a  b  2
 6:  a  b  2
 7:  b  c  1
 8:  b  c  1
 9:  b  c  1
10:  b  d  2
11:  b  d  2
12:  b  d  2

The same with dplyr:

dt %>%
 group_by(gr) %>%
 mutate(id = cumsum(!duplicated(cl)))

Or a rleid()-like possibility:

dt %>%
 group_by(gr) %>%
 mutate(id = with(rle(cl), rep(seq_along(lengths), lengths)))

edited Jun 21 '19 at 12:29

answered Jun 21 '19 at 12:04

tmfmnk

38,881
4
47
67

1

interesting solution with `cumsum` – mattek Jun 21 '19 at 12:23

score 0 · Answer 3 · answered Jun 21 '19 at 12:15

An alternative solution using factor which will not require ordering first

dt %>%
  group_by(gr) %>%
  mutate(id = as.numeric(factor(cl))) %>%
  ungroup()

# # A tibble: 12 x 3
#   gr    cl       id
#   <chr> <chr> <dbl>
# 1 a     a         1
# 2 a     a         1
# 3 a     a         1
# 4 a     b         2
# 5 a     b         2
# 6 a     b         2
# 7 b     c         1
# 8 b     c         1
# 9 b     c         1
#10 b     d         2
#11 b     d         2
#12 b     d         2

Note that this will automatically assign a number / id based on the alphabetical order of the cl values, within each gr group.

Enumerate groups within groups in a data.table

3 Answers3