In R data.table, how do I keep information from columns that are not used?
<p>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: </p>

<pre><code>dt &lt;- 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
</code></pre>

<p>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:</p>

<pre><code>dt[,.(Maxtimeinteam=max(timeinteam)), by=ID]

   ID Maxtimeinteam
1:  1             2
2:  2             3
3:  3             1
4:  4             4
5:  5             2
</code></pre>

<p>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: </p>

<pre><code>ID colour timeinteam
1     blue          2
2    green          3
3   yellow          1
4      red          4
5     blue          2
</code></pre>