Find max for each group and return a different column

Question

Given the following test matrix:

testMatrix <- matrix( c(1,1,2,10,20,30,300,100,200,"A","B","C"), 3, 4) colnames(testMatrix) <- c("GroupID", "ElementID", "Value", "Name") 

Here I want to find max for each group, and then return the name of this column. For example. I would expect 1, A and 2, C. If there is a tie with max, the first match will be perfect. After that I would have to attach this to the matrix with a new column "GroupName"

How can i do this?

I already have a combination of Group, Max Value:

 groupMax <- aggregate (as.numeric(testMatrix[,3]), by=list( testMatrix[,1] ), max ) 

The way I used to add columns to my matrix works like this (suppose there is also a matrix groupNames with GroupID, name combinations):

 testMatrix <- cbind ( testMatrix, groupNames[match( testMatrix[,1], groupNames[,1] ), 2] ) 

Answer 1

Basic solution, not as simple as Dan M's:

 testMatrix <- data.frame(GroupID = c(1,1,2), ElementID = c(10,20,30), Value=c(300,100,200), Name=c("A","B","C")) A <- lapply(split(testMatrix, testMatrix$GroupID), function(x) { x[which.max(x$Value), c(1, 4)] } ) do.call(rbind, A) 

Answer 2

A data.table solution for time and memory efficiency and syntactic elegance

 library(data.table) DT <- as.data.table(testMatrix) DT[,list(Name = Name[which.max(Value)]),by = GroupID] 

Answer 3

As @Tyler said, working with data.frame is easier. Here's the option:

 testMatrix <- data.frame(GroupID = c(1,1,2), ElementID = c(10,20,30), Value=c(300,100,200), Name=c("A","B","C")) ddply(testMatrix, .(GroupID), summarize, Name=Name[which.max(Value)]) 

Answer 4

I figured out a good way to do this with dplyr

 filter(group_by(testMatrix,GroupID),min_rank(desc(Value))==1)