data.table "key indices" or "group counter"

Question

After creating a key on a data.table:

set.seed(12345)
DT <- data.table(x = sample(LETTERS[1:3], 10, replace = TRUE),
                 y = sample(LETTERS[1:3], 10, replace = TRUE))
setkey(DT, x, y)
DT
#       x y
#  [1,] A B
#  [2,] A B
#  [3,] B B
#  [4,] B B
#  [5,] C A
#  [6,] C A
#  [7,] C A
#  [8,] C A
#  [9,] C C
# [10,] C C

I would like to get an integer vector giving for each row the corresponding "key index". I hope the expected output (column i) below will help clarify what I mean:

#       x y i
#  [1,] A B 1
#  [2,] A B 1
#  [3,] B B 2
#  [4,] B B 2
#  [5,] C A 3
#  [6,] C A 3
#  [7,] C A 3
#  [8,] C A 3
#  [9,] C C 4
# [10,] C C 4

I thought about using something like cumsum(!duplicated(DT[, key(DT), with = FALSE])) but am hoping there is a better solution. I feel this vector could be part of the table's internal representation, and maybe there is a way to access it? Even if it is not the case, what would you suggest?

score 69 · Accepted Answer · edited Mar 01 '16 at 22:08

69

Update: From v1.8.3, you can simply use the inbuilt special .GRP:

DT[ , i := .GRP, by = key(DT)]

See history for older answers.

edited Mar 01 '16 at 22:08

Arun

116,683
26
284
387

answered Oct 22 '12 at 20:44

Matt Dowle

58,872
22
166
224

5

I think `.GRP` is a very good idea, and have wished for it in the past. Nice and seemingly optimal solution, BTW. – Josh O'Brien Oct 22 '12 at 20:53
3

@JoshO'Brien Ok, `.GRP` now in 1.8.3. – Matt Dowle Oct 23 '12 at 08:41
6

How does one find the documentation on these and other variables like `.SDcols`? ??`.GRP` etc. – Brandon Bertelsen Sep 01 '16 at 06:46

score 13 · Answer 2 · edited Mar 01 '16 at 22:00

I'd probably just do this, since I'm fairly confident that no index counter is available from within the call to [.data.table():

ii <- unique(DT)
ii[ , i := seq_len(nrow(ii))]
DT[ii]
#     x y i
#  1: A B 1
#  2: A B 1
#  3: B B 2
#  4: B B 2
#  5: C A 3
#  6: C A 3
#  7: C A 3
#  8: C A 3
#  9: C C 4
# 10: C C 4

You could make this a one-liner, at the expense of an additional call to unique.data.table():

DT[unique(DT)[ , i := seq_len(nrow(unique(DT)))]]

data.table "key indices" or "group counter"

2 Answers2

Linked

Related