Find second largest column name

Question

I am trying to find the largest column value and the second largest column value and the names of both columns. I'm struggling to get the second largest column name however.

I tried to write an lapply function that removed the value of the first max from consideration, but it threw off the column name count. Any suggestions?

temp<-data.frame(c(1,2,3,4),c(1,2,3,1),c(4,5,1,2),c(1,6,5,4),c(2,2,2,2))
colnames(temp)<-c("c1","c2","c3","c4","c5")

temp$MaxOrders<-as.numeric(apply(temp[,c(-1)],1,function(x){x[which.max(x)]}))
temp$secondMaxOrders<-as.numeric(apply(temp[,c(2,3,4,5)],1,function(x){x[order(x)[2]]}))

temp$MaxColName<-colnames(temp)[c(-1)][max.col(temp[,c(-1)],ties.method="first")]

temp

  c1 c2 c3 c4 c5 MaxOrders secondMaxOrders MaxColName
1  1  1  4  1  2         4               1         c3
2  2  2  5  6  2         6               5         c4
3  3  3  1  5  2         5               3         c4
4  4  1  2  4  2         4               2         c4

GOAL: find the second highest by column name

  c1 c2 c3 c4 c5 MaxOrders secondMaxOrders MaxColName secondMaxColumnName
1  1  1  4  1  2         4               2         c3 c5
2  2  2  5  6  2         6               5         c4 c3
3  3  3  1  5  2         5               3         c4 c2
4  4  1  2  4  2         4               2         c4 c3

Answer 1

We can do this in one apply call by finding out 2 maximum value in each row and returning their column name.

temp[c("MaxOrders", "secondMaxOrders", "MaxColName", "secondMaxColumnName")] <-
    t(apply(temp, 1, function(x) {
         inds <- order(x, decreasing = TRUE)[1:2]
         c(x[inds], names(temp)[inds])
}))

temp
#  c1 c2 c3 c4 c5 MaxOrders secondMaxOrders MaxColName secondMaxColumnName
#1  1  1  4  1  2         4               2         c3                  c5
#2  2  2  5  6  2         6               5         c4                  c3
#3  3  3  1  5  2         5               3         c4                  c1
#4  4  1  2  4  2         4               4         c1                  c4

OR if you want to completely remove the maximum value and consider only the remaining ones for second max

t(apply(temp, 1, function(x) {
    inds <- match(unique(sort(x, decreasing=TRUE))[1:2], x)
    c(x[inds], names(temp)[inds])
}))

#     [,1] [,2] [,3] [,4]
#[1,] "4"  "2"  "c3" "c5"
#[2,] "6"  "5"  "c4" "c3"
#[3,] "5"  "3"  "c4" "c1"
#[4,] "4"  "2"  "c1" "c3"

Answer 2

temp<-data.frame(c(1,2,3,1),c(4,5,1,2),c(1,6,5,4),c(2,2,2,2))
colnames(temp)<-c("c2","c3","c4","c5")

m1 = max.col(temp)
m2 = max.col(t(sapply(seq_along(m1), function(i)
    replace(temp[i,], temp[i,] == temp[i, m1[i]], -Inf))))

max1 = temp[cbind(1:NROW(temp), m1)]
max2 = temp[cbind(1:NROW(temp), m2)]

data.frame(m1 = colnames(temp)[m1],
           m2 = colnames(temp)[m2],
           max1,
           max2)
#  m1 m2 max1 max2
#1 c3 c5    4    2
#2 c4 c3    6    5
#3 c4 c2    5    3
#4 c4 c5    4    2

Answer 3

You could work with a key vector which will give you both *Orders and *ColName that you can concatenate:

key <- setNames(names(temp[1:5]), 1:5)
nms <- c("MaxOrders", "secondMaxOrders", "MaxColName", "secondMaxColumnName")

d <- t(sapply(seq(nrow(temp)), function(x) {
  o <- order(-temp[x, 2:5])[1:2]
  return(setNames(c(temp[x, o + 1], key[o + 1]), nms))
}))

This should give you the desired result:

cbind(temp, d)
#   c1 c2 c3 c4 c5 MaxOrders secondMaxOrders MaxColName secondMaxColumnName
# 1  1  1  4  1  2         4               2         c3                  c5
# 2  2  2  5  6  2         6               5         c4                  c3
# 3  3  3  1  5  2         5               3         c4                  c2
# 4  4  1  2  4  2         4               2         c4                  c3

---

Data

temp <- structure(list(c1 = c(1, 2, 3, 4), c2 = c(1, 2, 3, 1), c3 = c(4, 5, 1, 2), 
                       c4 = c(1, 6, 5, 4), c5 = c(2, 2, 2, 2)), class = "data.frame", 
                  row.names = c(NA, -4L))