R Identify Max and Col Source

Question

HAVE = data.frame("STUDENT"=c(1, 1, 1, 2, 2, 2, 3, 3, 3),
"CLASS"=c('A','A','A','B','B','B','C','C','C'),
"SEMESTER"=c(1, 2, 3, 1, 2, 3, 1, 2, 3),
"SCORE"=c(50, 74, 78, 79, 100, 65, 61, 70, 87),
"TEST"=c(80, 59, 63, 96, 57, 53, 93, 89, 92))


WANT = HAVE %>%
  rowwise() %>%
 mutate(MAX = max(c(SCORE, TEST)))
WANT$WHICHCOL = c("TEST", "SCORE", "SCORE", "TEST", "SCORE", "SCORE", "TEST", "TEST", "TEST")

I am able to identify the way to get the max value between SCORE and TEST but I wish to also make the column WHICHCOL which equals to 'TEST' if TEST> SCORE or 'SCORE' if SCORE > TEST

score 1 · Answer 1 · answered Jul 28 '22 at 19:43

pmax is a built-in function that will be much more efficient than a rowwise max:

HAVE %>%
  mutate(
   MAX = pmax(SCORE, TEST),
   WHICHCOL = ifelse(SCORE > TEST, "SCORE", "TEST")
  )
#   STUDENT CLASS SEMESTER SCORE TEST MAX WHICHCOL
# 1       1     A        1    50   80  80     TEST
# 2       1     A        2    74   59  74    SCORE
# 3       1     A        3    78   63  78    SCORE
# 4       2     B        1    79   96  96     TEST
# 5       2     B        2   100   57 100    SCORE
# 6       2     B        3    65   53  65    SCORE
# 7       3     C        1    61   93  93     TEST
# 8       3     C        2    70   89  89     TEST
# 9       3     C        3    87   92  92     TEST

Note that, since I use > not >=, TEST will win ties.

score 1 · Answer 2 · answered Jul 28 '22 at 20:28

A base R solution:

df1 <- HAVE[c("SCORE", "TEST")]
x <- max.col(df1, "first")
MAX <- df1[cbind(1:nrow(df1), x)]
WHICHCOL <- names(df1)[x]
HAVE <- cbind(HAVE, MAX, WHICHCOL)
HAVE

#>   STUDENT CLASS SEMESTER SCORE TEST MAX WHICHCOL
#> 1       1     A        1    50   80  80     TEST
#> 2       1     A        2    74   59  74    SCORE
#> 3       1     A        3    78   63  78    SCORE
#> 4       2     B        1    79   96  96     TEST
#> 5       2     B        2   100   57 100    SCORE
#> 6       2     B        3    65   53  65    SCORE
#> 7       3     C        1    61   93  93     TEST
#> 8       3     C        2    70   89  89     TEST
#> 9       3     C        3    87   92  92     TEST

R Identify Max and Col Source

2 Answers2