CODEWITHSUNDEEP
rdata.table
mattek
mattek

Reputation: 942

data.table: subset by index

In the following data table I'd like to select rows with unique id and the smallest value from the column pos2:

dt = data.table(id = c(1,2,2,3,3,3),
            pos1 = c(0.1, 0.2, 0.2, 0.3, 0.3, 0.3),
            pos2 = c(0.1, 0.25, 0.21, 0.34, 0.31, 0.32))

   id pos1 pos2
1:  1  0.1 0.10
2:  2  0.2 0.25
3:  2  0.2 0.21
4:  3  0.3 0.34
5:  3  0.3 0.31
6:  3  0.3 0.32

The way I'm doing it now is by creating an intermediate table:

dt.red = dt[, .(pos2 = first(sort(pos2))), by = id]

   id pos2
1:  1 0.10
2:  2 0.21
3:  3 0.31

Then I merge to obtain the desired end result:

merge(dt, dt.red)

   id pos2 pos1
1:  1 0.10  0.1
2:  2 0.21  0.2
3:  3 0.31  0.3

Is there a cleaner way of achieving that with data.table?

Upvotes: 4

Views: 1047

Answers (2)

IceCreamToucan
IceCreamToucan

Reputation: 28705

It's also possible to do this without .I, but it'll be slower*

dt[order(pos2), head(.SD, 1), id]
#    id pos1 pos2
# 1:  1  0.1 0.10
# 2:  2  0.2 0.21
# 3:  3  0.3 0.31

*Or maybe not, see comments below

Upvotes: 6

akrun
akrun

Reputation: 887971

We can use .I to get the row index and use that in i for subsetting the rows

dt[dt[order(pos2), .I[1], by = id]$V1]
#   id pos1 pos2
#1:  1  0.1 0.10
#2:  2  0.2 0.21
#3:  3  0.3 0.31

Or with setorder 

setorder(dt, id, pos2)[, .SD[1L], id]

Upvotes: 5

Related Questions