Calculate max value across multiple columns by multiple groups

Question

I have a data file with numeric values in three columns and two grouping variables (ID and Group) from which I need to calculate a single max value by ID and Group:

structure(list(ID = structure(c(1L, 1L, 1L, 2L), .Label = c("a1", 
"a2"), class = "factor"), Group = structure(c(1L, 1L, 2L, 2L), .Label = 
c("abc", 
"def"), class = "factor"), Score1 = c(10L, 0L, 0L, 5L), Score2 = c(0L, 
0L, 5L, 10L), Score3 = c(0L, 11L, 2L, 11L)), class = "data.frame", row.names = 
c(NA, 
-4L))

The result I am trying to obtain is:

structure(list(ID = structure(c(1L, 1L, 2L), .Label = c("a1", 
"a2"), class = "factor"), Group = structure(c(1L, 2L, 2L), .Label = c("abc", 
"def"), class = "factor"), Max = c(11L, 5L, 11L)), class = "data.frame", 
row.names = c(NA, 
-3L))

I am trying the following in dplyr:

SampTable<-SampDF %>% group_by(ID,Group) %>% 
summarize(max = pmax(SampDF$Score1, SampDF$Score2,SampDF$Score3))

But it generates this error:

Error in summarise_impl(.data, dots) : 
Column `max` must be length 1 (a summary value), not 4

Is there an easy way to achieve this in dplyr or data.table?

Answer 1

Solution using data.table. Find max value on 3:5 columns (Score columns) by ID and Group.

library(data.table)
setDT(d)
d[, .(Max = do.call(max, .SD)), .SDcols = 3:5, .(ID, Group)]

   ID Group Max
1: a1   abc  11
2: a1   def   5
3: a2   def  11

Data:

d <- structure(list(ID = structure(c(1L, 1L, 1L, 2L), .Label = c("a1", 
"a2"), class = "factor"), Group = structure(c(1L, 1L, 2L, 2L), .Label = 
c("abc", 
"def"), class = "factor"), Score1 = c(10L, 0L, 0L, 5L), Score2 = c(0L, 
0L, 5L, 10L), Score3 = c(0L, 11L, 2L, 11L)), class = "data.frame", row.names = 
c(NA, 
-4L))

Answer 2

A solution using tidyverse.

library(tidyverse)

dat2 <- dat1 %>%
  gather(Column, Value, starts_with("Score")) %>%
  group_by(ID, Group) %>%
  summarise(Max = max(Value)) %>%
  ungroup()
dat2
# # A tibble: 3 x 3
#   ID    Group   Max
#   <fct> <fct> <dbl>
# 1 a1    abc      11
# 2 a1    def       5
# 3 a2    def      11