How to find the maximum using aggregate and get the output with all the columns in R?

When we use aggregate function to find maximum or any other value, the output of the aggregation does not provide all the columns that corresponds to the maximum value. Therefore, we need to merge the data frame obtained by using aggregate with the original data frame. In this way, we will get only those rows that are common between the new data frame and the original one.

Example

Consider the below data frame −

 Live Demo

set.seed(99)
x1<-rep(c(1,2,3,4,5),times=4)
x2<-sample(1:100,20)
x3<-rep(1:10,2)
x4<-rep(c("C1","C2","C3","C4"),each=5)
x5<-sample(1:10,20,replace=TRUE)
x6<-sample(1:1000,20,replace=TRUE)
df<-data.frame(x1,x2,x3,x4,x5,x6)
df

Output

x1 x2 x3 x4 x5 x6
1 1 48 1 C1 1 123
2 2 33 2 C1 7 285
3 3 44 3 C1 2 301
4 4 22 4 C1 7 268
5 5 62 5 C1 6 713
6 1 32 6 C2 5 867
7 2 13 7 C2 8 509
8 3 20 8 C2 7 616
9 4 31 9 C2 10 308
10 5 68 10 C2 6 755
11 1  9  1 C3 5 806
12 2 82 2 C3 10 315
13 3 88 3 C3 1 552
14 4 30 4 C3 6 103
15 5 86 5 C3 6 419
16 1 84 6 C4 7 395
17 2 95 7 C4 4 422
18 3 14 8 C4 5 818
19 4 4 9 C4 7 68
20 5 78 10 C4 1 879

Aggregation examples for maximum value and getting the all column names −

merge(aggregate(x2~x4,df,FUN=max),df)
x4 x2 x1 x3 x5 x6
1 C1 62 5 5 6 713
2 C2 68 5 10 6 755
3 C3 88 3 3 1 552
4 C4 95 2 7 4 422
merge(aggregate(x2~x1,df,FUN=max),df)
x1 x2 x3 x4 x5 x6
1 1 84 6 C4 7 395
2 2 95 7 C4 4 422
3 3 88 3 C3 1 552
4 4 31 9 C2 10 308
5 5 86 5 C3 6 419
merge(aggregate(x2~x3,df,FUN=max),df)
x3 x2 x1 x4 x5 x6
1 1 48 1 C1 1 123
2 10 78 5 C4 1 879
3 2 82 2 C3 10 315
4 3 88 3 C3 1 552
5 4 30 4 C3 6 103
6 5 86 5 C3 6 419
7 6 84 1 C4 7 395
8 7 95 2 C4 4 422
9 8 20 3 C2 7 616
10 9 31 4 C2 10 308
merge(aggregate(x5~x4,df,FUN=max),df)
x4 x5 x1 x2 x3 x6
1 C1 7 4 22 4 268
2 C1 7 2 33 2 285
3 C2 10 4 31 9 308
4 C3 10 2 82 2 315
5 C4 7 4 4 9 68
6 C4 7 1 84 6 395
merge(aggregate(x5~x1,df,FUN=max),df)
x1 x5 x2 x3 x4 x6
1 1 7 84 6 C4 395
2 2 10 82 2 C3 315
3 3 7 20 8 C2 616
4 4 10 31 9 C2 308
5 5 6 62 5 C1 713
6 5 6 68 10 C2 755
7 5 6 86 5 C3 419
merge(aggregate(x6~x4,df,FUN=max),df)
x4 x6 x1 x2 x3 x5
1 C1 713 5 62 5 6
2 C2 867 1 32 6 5
3 C3 806 1 9 1 5
4 C4 879 5 78 10 1

Nizamuddin Siddiqui

Updated on: 21-Aug-2020

381 Views

Kickstart Your Career

Get certified by completing the course

Get Started