Reshaping more data

We have 6 columns of data as follows:

LC1, Perc1, LC2, Perc2, LC3, Perc3

LCx has values from 1 to 10 and Percx contains values from 1 to 100.

We need a new matrix with two columns: LC, Perc that contains the LC with the maximum Perc across the three sets.

Let us simulate the data

set.seed(1234)
rawData <- data.frame(LC1 = sample(1:10, 10), Perc1 = sample(1:100, 10), LC2 = sample(1:10, 
    10), Perc2 = sample(1:100, 10), LC3 = sample(1:10, 10), Perc3 = sample(1:100, 
    10))
rawData

##    LC1 Perc1 LC2 Perc2 LC3 Perc3
## 1    2    70   4    46   6     8
## 2    6    54   3    27  10    31
## 3    5    28   2    30   3    71
## 4    8    90   1    50   5    49
## 5    9    29   8    18   2    15
## 6    4    80   5    73   8    48
## 7    1    27   9    19   7    47
## 8    7    25  10    25   9    70
## 9   10    18   6    92   1    17
## 10   3    22   7    74   4    78

A vectorised loop over the sequence 1:10 do the task:

t(sapply(1:10, function(x) {
    LCrank <- c(rawData$Perc1[rawData$LC1 == x], rawData$Perc2[rawData$LC2 == 
        x], rawData$Perc3[rawData$LC3 == x])
    winner <- which(LCrank == max(LCrank))
    cbind(x, winner)
}))

##       [,1] [,2]
##  [1,]    1    2
##  [2,]    2    1
##  [3,]    3    3
##  [4,]    4    1
##  [5,]    5    2
##  [6,]    6    2
##  [7,]    7    2
##  [8,]    8    1
##  [9,]    9    3
## [10,]   10    3

The code can be easily generalised for n groups. Maybe there are more elegant ways to do that combining xapply functions, but usually sapply with the appropriate code within the function works fine.