R- selecting a row based on characteristics of another column in that row

Question

So I have the following data.frame

    num      id

1   44982   44979
2   44981   44979
3   43554   43551
4   43552   43551
5   42510   42507
6   42509   42507
7   41997   41994
8   41996   41994
9   40519   40517
10  40519   40517
11  40039   40036
12  40038   40036
13  31337   31335
14  31336   31335
15  31247   31245
16  31246   31245
17  43984   28769
18  28770   28769
19  27620   27618
20  27619   27618

As can be seen, there are duplicates in the "id" column. What I would like to do is select which duplicate to keep based on the largest value in the "num" column. Thus when ecountering duplicates in the id column, the code looks at the num column, finds the highest value row and removes the lowest value. The output should look something like this.

        num      id

1       44982   44979
2       43554   43551
3       42510   42507
4       41997   41994
5       40519   40517
6       40039   40036
7       31337   31335
8       31247   31245
9       43984   28769
10      27620   27618
11      27497   27495
12      44317   27374
13      10892   10697
14      10612   10606
15      10445   10443
16      10361   10359
17      10063   10061
18      9673    9671
19      9601    9599
20      8148    8146

Thanks for any help in advance

Answer 1

A standard R solution would be:

aggregate(num~id, dat, max)

Result:

      id   num
1  27618 27620
2  28769 43984
3  31245 31247
4  31335 31337
5  40036 40039
6  40517 40519
7  41994 41997
8  42507 42510
9  43551 43554
10 44979 44982

You can also use data.table to do this as follows:

require(data.table)
setDT(dat)[,.(num = max(num)),by=id]
setDF(dat) #making dat a normal data.frame again

This will return the highest value per id as num.

Answer 2

A possibly faster approach using the data.table package would be to order by num in decreasing order first and then select the unique ids

library(data.table)
unique(setorder(setDT(df), -num), by = "id")
#       num    id
#  1: 44982 44979
#  2: 43984 28769
#  3: 43554 43551
#  4: 42510 42507
#  5: 41997 41994
#  6: 40519 40517
#  7: 40039 40036
#  8: 31337 31335
#  9: 31247 31245
# 10: 27620 27618

Answer 3

You can aggregate on max

maxids <- aggregate(df$num, by=list(df$id), max) # get highest num for each id
names(maxids) <- c("id", "num") # rename result columns

Or as shown above (in one step):

maxids <- aggregate(num~id, df, max)

Answer 4

And, just for the record, here's a dplyr answer:

library(dplyr)
DF %>% group_by(id) %>% slice(which.max(num))
#Source: local data frame [10 x 2]
#Groups: id
#
#     num    id
#1  27620 27618
#2  43984 28769
#3  31247 31245
#4  31337 31335
#5  40039 40036
#6  40519 40517
#7  41997 41994
#8  42510 42507
#9  43554 43551
#10 44982 44979