CODEWITHSUNDEEP
rdplyrdata.table
user3594490
user3594490

Reputation: 1999

Calculate max value across multiple columns by multiple groups

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?

Upvotes: 3

Views: 5870

Answers (5)

Benjamin Christoffersen
Benjamin Christoffersen

Reputation: 4841

Here is a base R solution

# gives 2x2 table
x <- by(df[, !names(df) %in% c("ID", "Group")], list(df$ID, df$Group), max)

# get requested format
tmp <- expand.grid(ID = rownames(x), Group = colnames(x))
tmp$Max <- as.vector(x)
tmp[complete.cases(tmp), ]
#R   ID Group Max
#R 1 a1   abc  11
#R 3 a1   def   5
#R 4 a2   def  11

with 

df <- 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))

Upvotes: 0

moodymudskipper
moodymudskipper

Reputation: 47310

Here is a tidyverse solution using nest :

library(tidyverse)
df %>%
  nest(-(1:2),.key="Max") %>%
  mutate_at("Max",map_dbl, max)
#   ID Group Max
# 1 a1   abc  11
# 2 a1   def   5
# 3 a2   def  11

In base R:

res <- aggregate(. ~ ID + Group,df,max)
res <- cbind(res[1:2], Max = do.call(pmax,res[-(1:2)]))
res
#   ID Group Max
# 1 a1   abc  11
# 2 a1   def   5
# 3 a2   def  11

Upvotes: 0

akrun
akrun

Reputation: 887118

Here are couple of other options with tidyverse

library(tidyverse)
df1 %>%
     group_by(ID, Group) %>% 
     nest %>% 
     mutate(Max = map_dbl(data, ~ max(unlist(.x)))) %>% 
     select(-data)

Or using pmax

df1 %>% 
    mutate(Max = pmax(!!! rlang::syms(names(.)[3:5]))) %>% 
    group_by(ID, Group) %>% 
    summarise(Max = max(Max))
# A tibble: 3 x 3
# Groups:   ID [?]
#  ID    Group   Max
#  <fct> <fct> <dbl>
#1 a1    abc      11
#2 a1    def       5
#3 a2    def      11

Or using base R

aggregate(cbind(Max = do.call(pmax, df1[3:5])) ~ ID + Group, df1, max)

Upvotes: 2

www
www

Reputation: 39154

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

Upvotes: 4

pogibas
pogibas

Reputation: 28339

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))

Upvotes: 5

Related Questions