Create function which returns the row name and the maximum value

Question

I have two different data frames

    df <- data.frame(A=c(3,2,1,4,5),B=c(1,6,3,8,4),C=c(2,1,4,8,9), D=c(4,1,2,4,6),
                     Type=c("M","M","N","J","M"))
    row.names(df)<-c("R1","R2","R3","R4","R5")


       A B C D Type
    R1 3 1 2 4  M
    R2 2 6 1 1  M
    R3 1 3 4 2  N
    R4 4 8 8 4  J
    R5 5 4 9 6  M


    df2 <- data.frame(E=c(2,5,6,1,4),F=c(2,4,2,5,1),G=c(5,6,2,7,3),H=c(8,2,7,4,1),
                      Category=c("P","M","T","N","J"))
    row.names(df2)<-c("R6","R7","R8","R9","R10")


        E F G H Category
    R6  2 2 5 8    P
    R7  5 4 6 2    M
    R8  6 2 2 7    T
    R9  1 5 7 4    N
    R10 4 1 3 1    J

and I am trying to create a function which takes in a data frame, column, and category OR type as arguments and returns the highest value on the column which belongs to the category/type and the row name.

Let the function be lookup. Here is an example of an outcome I would like to have:

lookup(df, "B", "M")

6, R2

where 6 is the highest value in the column B of type M.

Here is what I have gotten so far:

    lookup<-function(data,col,row) {
      maxrow<-which.max(data[,col])
      print(maxrow)
    }

    lookup(df, "B", "M")

    4

However, this function only prints out the row number (not even row name) of highest value on the entire column and I don't know how to make the function to take the types and categories into account.

Answer 1

You can find the column of Type or Category with grep and colnames. And then you simply need tu subset. In case you want to get also the colname remove unname from the function.

lookup <- function(data,col,row) {
  i <- which(data[,grep("Type|Category", colnames(data))]==row)
  j <- which.max(data[i, col])
  unname(data[i[j], col, drop = FALSE])
}
lookup(df, "B", "M")
#    
#R2 6

Answer 2

some small changes to your function. This requires that the Type or Category etc is always the last column.

lookup<-function(data,col,row) {
      maxrow<-which.max(data[,col][data[,ncol(data)]==row])
      a <- data[,col][maxrow]
      b <- row.names(data)[maxrow])
      c(a,b)    
}

output:

> lookup(df, "B", "M")
[1] "6"  "R2"