16

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?

alistaire
  • 42,459
  • 4
  • 77
  • 117
Brad
  • 813
  • 1
  • 10
  • 20

2 Answers2

23

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]]
mnel
  • 113,303
  • 27
  • 265
  • 254
  • (+1) That's much better than my answer; to directly obtain unique entries from the data.table thereby removing the necessity for `nomatch=0`. – Arun Apr 02 '13 at 23:34
  • +1 very cool, looks like it matches the "unique_together" index workings of MySQL. – Brandon Bertelsen Apr 02 '13 at 23:40
  • It seems to me that the self-join is still faster on my large data.table (i.e. 40mm+ rows). Thanks again – Brad Apr 03 '13 at 00:08
  • 2
    +1 too. I have a slight preference for `.I` for tasks like this, but the syntax bites because the pesky group columns get included in the result (when `drop` is added that'll be nicer). There is also a FR to make the natural `DT[,.SD[1],by="store,year,month"]` work quickly without any penalty of using `.SD` ([FR#2330](https://r-forge.r-project.org/tracker/index.php?func=detail&aid=2330&group_id=240&atid=978), so in future that'll be the idiomic way to `select top n * from` or `select 2nd from ...` etc – Matt Dowle Apr 03 '13 at 09:16
  • Has this functionality of `data.table` changed since this answer was posted? When I tried "Option 1" `unique(DT)` is just giving me back `DT` again? – Adam Hoelscher Jul 10 '17 at 15:25
  • @Adam it appears that Matt's advice above is now functional: (`DT[,.SD[1],by="store,year,month"]`. I just used this syntax to select the first of two records by person and date. – Mark Danese Sep 10 '17 at 22:42
3

What about:

solution2 <- data.table(data)[ , sales[1], by="store,year,month"]
> solution2
   store year month V1
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

I suppose you could rename that column:

data.table(data)[,fsales := sales[1],by="store,year,month"]
IRTFM
  • 258,963
  • 21
  • 364
  • 487
  • 1
    If you want to rename the column (and not add a column by reference) then `data.table(data)[,list(fsales = sales[1]),by="store,year,month"]` would be the way to go. – mnel Apr 02 '13 at 23:43