First observation by group using self-join

data.table, r, self-join

Solution

option 1 (using keys)

Set the key to be `store, year, month`

DT <- data.table(data, key = c('store','year','month'))

Then you can use `unique` to create a data.table containing the unique values of the key columns. By default this will take the first entry

unique(DT)
   store year month sales
1:     1 2000    12     1
2:     1 2001    12     3
3:     2 2000    12     5
4:     2 2001    12     7
5:     3 2000    12     9
6:     3 2001    12    11

But, to be sure, you could use a self-join with `mult='first'`. (other options are `'all'` or `'last'`)

# the key(DT) subsets the key columns only, so you don't end up with two 
# sales columns
DT[unique(DT[,key(DT), with = FALSE]), mult = 'first']

Option 2 (No keys)

Without setting the key, it would be faster to use `.I` not `.SD`

DTb <- data.table(data)
DTb[DTb[,list(row1 = .I[1]), by = list(store, year, month)][,row1]]

Problem

I'm trying to get the top row by a group of three variables using a data.table. I have a working solution: ``` col1 <- c(1,1,1,1,2,2,2,2,3,3,3,3) col2 <- c(2000,2000,2001,2001,2000,2000,2001,2001,2000,2000,2001,2001) col4 <- c(1,2,3,4,5,6,7,8,9,10,11,12) data <- data.frame(store=col1,year=col2,month=12,sales=col4) solution1 <- data.table(data)[,.SD[1,],by="store,year,month"] ``` I used the slower approach suggested by Matthew Dowle in the following link: https://stats.stackexchange.com/questions/7884/fast-ways-in-r-to-get-the-first-row-of-a-data-frame-grouped-by-an-identifier I'm trying to implement the faster self join but cannot get it to work. Does anyone have any suggestions?

Original source