计算多个组中多列的最大值 [英] Calculate max value across multiple columns by multiple groups

问题描述

我有一个数据文件，该文件在三列中有数值，并且有两个分组变量（ID和组），我需要从中通过ID和组计算单个最大值：

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

我试图获得的结果是：

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

我正在dplyr中尝试以下操作：

I am trying the following in dplyr:

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

但它会产生此错误：

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

在 dplyr 或 data.table 中是否有简单的方法来实现此目的？

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

推荐答案

使用 data.table 的解决方案。在 3：5 列（得分列）上按 ID 和 Group查找最大值code>。

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

数据：

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

这篇关于计算多个组中多列的最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！