0

I am stuck with a data.table query that should be really easy, but I can't figure it out! Your help is much appreciated! Say I have this R data.table: 

dt <- data.table("ID"=c(1,1,2,2,2,3,4,5), "colour" = c("red","blue","red","blue","green","yellow","red","blue"), "timeinteam" = c(1,2,1,2,3,1,4,2))

   ID colour timeinteam
1:  1    red          1
2:  1   blue          2
3:  2    red          1
4:  2   blue          2
5:  2  green          3
6:  3 yellow          1
7:  4    red          4
8:  5   blue          2

If I want to know which ID was in which team (colour) for the longest period of time, how can I do so in data.table? I have tried:

dt[,.(Maxtimeinteam=max(timeinteam)), by=ID]

   ID Maxtimeinteam
1:  1             2
2:  2             3
3:  3             1
4:  4             4
5:  5             2

That will give me the maximum time per ID, but by doing so, I loose the information about the colour which belongs to that maximum time. The outcome I would like to get is: 

ID colour timeinteam
1     blue          2
2    green          3
3   yellow          1
4      red          4
5     blue          2
Frank
  • 66,179
  • 8
  • 96
  • 180
Pel
  • 19
  • 2
  • Please don't wrap your code as if it's html. – Frank Jul 15 '16 at 18:03
  • Copying from the linked answer, it looks like you want `dt[dt[, .I[timeinteam == max(timeinteam)], by=ID]$V1]` – Frank Jul 15 '16 at 18:06
  • That works, thanks! And thanks for correcting my code. Would you mind talking me through the solution? I have not used the .I[ ] before: I understand this selects only those rows for which the code within brackets holds true? Any other useful .[] commands I should know about? Thanks! – Pel Jul 22 '16 at 12:11
  • Yep, your understanding of `.I` is correct. You can find its documentation by typing `?\`special-symbols\``. The `.I[]` result is stored in a V1 column, and is extracted with `$V1`. Then, this vector of row numbers is used to subset the data like `dt[row_numbers]`. The vignettes are a good walk-through of other typical uses of the package https://github.com/Rdatatable/data.table/wiki/Getting-started If you have other quick questions, you could consider asking in the R chat room (once you have 20 rep): http://chat.stackoverflow.com/rooms/25312/r-public – Frank Jul 22 '16 at 12:56

0 Answers0