r - 在 R 中，如何只保留与类别列聚合的一个数值列的最大值相对应的 data.frame 行？

Question

我有这个data.frame：

d <- structure(list(ID = c(1L, 1L, 2L, 2L, 2L, 2L, 3L, 3L, 3L, 4L, 
4L, 4L, 4L, 5L, 5L, 6L, 6L, 6L, 6L, 6L, 6L, 7L, 7L, 8L, 8L, 8L, 
9L, 9L, 10L, 10L, 10L, 10L, 11L, 11L, 12L, 12L, 13L, 13L, 14L, 
14L, 14L, 14L, 14L, 14L, 15L, 15L, 15L, 15L, 15L, 16L, 16L, 17L, 
17L, 18L, 18L, 19L, 19L, 19L, 19L, 19L, 19L, 20L, 20L, 21L, 21L, 
21L, 21L, 21L, 22L, 22L, 22L, 22L, 23L, 23L, 23L, 24L, 24L, 24L, 
24L, 24L, 24L, 25L, 25L, 25L, 25L, 26L, 26L, 26L, 26L, 26L, 27L, 
27L, 27L, 27L, 27L, 27L, 27L, 28L, 28L, 29L, 29L, 29L, 29L, 30L, 
30L, 30L, 30L, 31L, 31L, 31L, 31L, 31L, 32L, 32L, 32L, 32L, 33L, 
33L, 33L, 33L, 34L, 34L, 34L, 34L, 34L, 34L, 34L, 35L, 35L, 35L, 
35L, 35L, 35L, 35L, 36L, 36L, 36L, 36L, 36L, 36L, 36L, 37L, 37L, 
37L, 37L, 38L, 38L, 39L, 39L, 39L, 39L, 40L, 40L, 40L, 40L, 40L, 
41L, 41L, 41L, 42L, 42L, 42L, 42L, 43L, 43L, 44L, 44L, 45L, 45L, 
46L, 46L, 46L, 46L, 46L, 46L, 46L, 47L, 47L, 47L, 47L, 47L, 47L, 
48L, 48L, 48L, 48L, 48L, 49L, 49L, 49L, 50L, 50L, 51L, 51L, 51L, 
52L, 52L, 53L, 53L, 53L, 54L, 54L, 54L, 54L, 54L, 54L, 55L, 55L, 
55L, 56L, 56L, 57L, 57L, 57L, 58L, 58L, 59L, 59L, 59L, 59L, 60L, 
60L, 60L, 60L, 60L, 61L, 61L, 61L, 61L, 61L, 61L, 61L, 62L, 62L, 
62L, 62L, 62L, 63L, 63L, 64L, 64L, 65L, 65L, 65L, 65L, 65L, 66L, 
66L, 67L, 67L, 67L, 67L, 67L, 67L, 68L, 68L, 68L, 68L, 69L, 69L, 
69L, 69L, 69L, 70L, 70L, 70L, 70L, 71L, 71L, 71L, 71L, 72L, 72L, 
72L, 72L, 72L, 72L, 72L, 73L, 73L, 74L, 74L, 74L, 74L, 75L, 75L, 
76L, 76L, 76L, 77L, 77L, 77L, 78L, 78L, 78L, 78L, 78L, 78L, 79L, 
79L, 80L, 80L, 81L, 81L, 82L, 82L, 83L, 83L, 83L, 83L, 83L, 83L, 
83L, 84L, 84L, 84L, 84L, 84L, 84L, 84L, 85L, 85L, 85L, 85L, 85L, 
86L, 86L, 87L, 87L, 87L, 88L, 88L, 88L, 89L, 89L, 89L, 89L, 89L, 
89L, 90L, 90L, 90L, 90L, 90L, 90L, 91L, 91L, 91L, 92L, 92L, 92L, 
92L, 92L, 92L, 92L, 93L, 93L, 93L, 93L, 94L, 94L, 94L, 94L, 94L, 
94L, 94L, 95L, 95L, 95L, 95L, 95L, 95L, 96L, 96L, 97L, 97L, 97L, 
97L, 97L, 98L, 98L, 98L, 98L, 98L, 99L, 99L, 99L, 99L, 99L, 99L, 
100L, 100L, 100L, 100L), CoreId = c("Core_20", "Core_18", "Core_17", 
"Core_10", "Core_16", "Core_2", "Core_1", "Core_3", "Core_8", 
"Core_5", "Core_13", "Core_9", "Core_17", "Core_20", "Core_9", 
"Core_10", "Core_5", "Core_7", "Core_1", "Core_15", "Core_4", 
"Core_1", "Core_2", "Core_3", "Core_9", "Core_7", "Core_16", 
"Core_4", "Core_17", "Core_11", "Core_16", "Core_1", "Core_3", 
"Core_6", "Core_6", "Core_11", "Core_3", "Core_1", "Core_19", 
"Core_15", "Core_14", "Core_16", "Core_7", "Core_3", "Core_2", 
"Core_17", "Core_3", "Core_13", "Core_5", "Core_18", "Core_15", 
"Core_6", "Core_10", "Core_1", "Core_16", "Core_15", "Core_1", 
"Core_7", "Core_20", "Core_12", "Core_18", "Core_18", "Core_3", 
"Core_6", "Core_15", "Core_5", "Core_12", "Core_7", "Core_9", 
"Core_3", "Core_10", "Core_5", "Core_14", "Core_16", "Core_12", 
"Core_2", "Core_9", "Core_11", "Core_12", "Core_17", "Core_6", 
"Core_11", "Core_1", "Core_4", "Core_12", "Core_17", "Core_18", 
"Core_6", "Core_7", "Core_3", "Core_19", "Core_16", "Core_11", 
"Core_17", "Core_18", "Core_14", "Core_3", "Core_16", "Core_2", 
"Core_13", "Core_11", "Core_20", "Core_2", "Core_5", "Core_19", 
"Core_16", "Core_8", "Core_18", "Core_16", "Core_3", "Core_10", 
"Core_9", "Core_14", "Core_1", "Core_9", "Core_15", "Core_13", 
"Core_7", "Core_14", "Core_2", "Core_17", "Core_1", "Core_7", 
"Core_10", "Core_20", "Core_6", "Core_9", "Core_7", "Core_6", 
"Core_5", "Core_10", "Core_20", "Core_13", "Core_3", "Core_15", 
"Core_1", "Core_5", "Core_7", "Core_9", "Core_8", "Core_19", 
"Core_6", "Core_20", "Core_19", "Core_1", "Core_10", "Core_19", 
"Core_10", "Core_6", "Core_8", "Core_17", "Core_11", "Core_15", 
"Core_12", "Core_10", "Core_18", "Core_18", "Core_3", "Core_8", 
"Core_7", "Core_3", "Core_15", "Core_1", "Core_9", "Core_11", 
"Core_20", "Core_2", "Core_11", "Core_8", "Core_15", "Core_10", 
"Core_18", "Core_7", "Core_2", "Core_20", "Core_5", "Core_12", 
"Core_18", "Core_1", "Core_13", "Core_6", "Core_8", "Core_17", 
"Core_9", "Core_5", "Core_20", "Core_18", "Core_2", "Core_17", 
"Core_14", "Core_8", "Core_14", "Core_1", "Core_17", "Core_4", 
"Core_4", "Core_8", "Core_10", "Core_14", "Core_16", "Core_17", 
"Core_8", "Core_16", "Core_15", "Core_19", "Core_12", "Core_4", 
"Core_19", "Core_13", "Core_1", "Core_2", "Core_5", "Core_8", 
"Core_2", "Core_13", "Core_4", "Core_16", "Core_13", "Core_19", 
"Core_11", "Core_5", "Core_20", "Core_10", "Core_2", "Core_6", 
"Core_2", "Core_11", "Core_4", "Core_1", "Core_10", "Core_6", 
"Core_20", "Core_2", "Core_14", "Core_19", "Core_5", "Core_8", 
"Core_16", "Core_13", "Core_16", "Core_19", "Core_12", "Core_9", 
"Core_4", "Core_6", "Core_3", "Core_5", "Core_7", "Core_10", 
"Core_6", "Core_15", "Core_9", "Core_3", "Core_17", "Core_10", 
"Core_9", "Core_17", "Core_4", "Core_1", "Core_19", "Core_6", 
"Core_10", "Core_14", "Core_6", "Core_4", "Core_13", "Core_3", 
"Core_14", "Core_4", "Core_15", "Core_20", "Core_4", "Core_2", 
"Core_9", "Core_16", "Core_14", "Core_10", "Core_5", "Core_10", 
"Core_8", "Core_13", "Core_1", "Core_4", "Core_3", "Core_4", 
"Core_6", "Core_4", "Core_1", "Core_17", "Core_13", "Core_2", 
"Core_12", "Core_14", "Core_19", "Core_13", "Core_2", "Core_7", 
"Core_6", "Core_7", "Core_17", "Core_15", "Core_6", "Core_10", 
"Core_18", "Core_3", "Core_15", "Core_9", "Core_4", "Core_8", 
"Core_20", "Core_12", "Core_13", "Core_7", "Core_11", "Core_13", 
"Core_9", "Core_5", "Core_3", "Core_8", "Core_15", "Core_16", 
"Core_3", "Core_4", "Core_12", "Core_19", "Core_5", "Core_2", 
"Core_16", "Core_10", "Core_11", "Core_14", "Core_3", "Core_20", 
"Core_8", "Core_11", "Core_18", "Core_20", "Core_7", "Core_2", 
"Core_2", "Core_4", "Core_12", "Core_14", "Core_13", "Core_8", 
"Core_5", "Core_1", "Core_3", "Core_16", "Core_7", "Core_9", 
"Core_20", "Core_14", "Core_8", "Core_15", "Core_17", "Core_20", 
"Core_4", "Core_3", "Core_13", "Core_11", "Core_19", "Core_10", 
"Core_12", "Core_7", "Core_2", "Core_10", "Core_3", "Core_4", 
"Core_13", "Core_1", "Core_18", "Core_3", "Core_18", "Core_16", 
"Core_9", "Core_8", "Core_19", "Core_4", "Core_5", "Core_2", 
"Core_9", "Core_13", "Core_20", "Core_5", "Core_8", "Core_4", 
"Core_11", "Core_13", "Core_2", "Core_17", "Core_20", "Core_15", 
"Core_12"), ES = c("sca11", "sca16", "sca3", "sca10", "sca20", 
"sca1", "sca7", "sca14", "sca12", "sca10", "sca3", "sca15", "sca8", 
"sca10", "sca20", "sca15", "sca3", "sca10", "sca4", "sca2", "sca20", 
"sca12", "sca10", "sca9", "sca4", "sca12", "sca13", "sca9", "sca3", 
"sca19", "sca16", "sca12", "sca13", "sca7", "sca4", "sca10", 
"sca13", "sca9", "sca20", "sca10", "sca8", "sca6", "sca9", "sca11", 
"sca20", "sca19", "sca8", "sca14", "sca12", "sca8", "sca1", "sca2", 
"sca15", "sca19", "sca11", "sca4", "sca11", "sca12", "sca7", 
"sca16", "sca2", "sca2", "sca7", "sca6", "sca4", "sca13", "sca16", 
"sca2", "sca15", "sca10", "sca4", "sca2", "sca20", "sca1", "sca5", 
"sca11", "sca14", "sca12", "sca1", "sca19", "sca10", "sca11", 
"sca1", "sca9", "sca18", "sca10", "sca7", "sca8", "sca15", "sca12", 
"sca16", "sca17", "sca10", "sca11", "sca5", "sca2", "sca6", "sca15", 
"sca9", "sca17", "sca3", "sca9", "sca16", "sca8", "sca4", "sca19", 
"sca17", "sca11", "sca5", "sca3", "sca19", "sca7", "sca1", "sca19", 
"sca20", "sca9", "sca18", "sca20", "sca13", "sca11", "sca9", 
"sca16", "sca6", "sca3", "sca7", "sca12", "sca14", "sca20", "sca4", 
"sca15", "sca16", "sca1", "sca9", "sca10", "sca20", "sca7", "sca1", 
"sca18", "sca9", "sca8", "sca13", "sca8", "sca4", "sca3", "sca20", 
"sca6", "sca1", "sca17", "sca9", "sca1", "sca2", "sca3", "sca15", 
"sca4", "sca20", "sca1", "sca18", "sca13", "sca7", "sca7", "sca10", 
"sca13", "sca12", "sca18", "sca6", "sca9", "sca14", "sca20", 
"sca2", "sca9", "sca10", "sca6", "sca1", "sca15", "sca20", "sca13", 
"sca6", "sca18", "sca13", "sca2", "sca1", "sca17", "sca17", "sca10", 
"sca9", "sca1", "sca6", "sca3", "sca13", "sca11", "sca1", "sca19", 
"sca20", "sca3", "sca10", "sca20", "sca4", "sca16", "sca7", "sca1", 
"sca19", "sca7", "sca11", "sca1", "sca15", "sca10", "sca13", 
"sca3", "sca9", "sca17", "sca4", "sca4", "sca8", "sca16", "sca12", 
"sca7", "sca7", "sca6", "sca8", "sca2", "sca4", "sca14", "sca9", 
"sca17", "sca19", "sca10", "sca13", "sca18", "sca2", "sca12", 
"sca20", "sca6", "sca11", "sca4", "sca12", "sca19", "sca10", 
"sca14", "sca20", "sca7", "sca10", "sca14", "sca15", "sca9", 
"sca8", "sca7", "sca9", "sca20", "sca8", "sca9", "sca5", "sca11", 
"sca4", "sca18", "sca9", "sca12", "sca15", "sca6", "sca14", "sca10", 
"sca9", "sca7", "sca16", "sca10", "sca6", "sca9", "sca1", "sca3", 
"sca18", "sca14", "sca15", "sca9", "sca7", "sca2", "sca11", "sca5", 
"sca1", "sca12", "sca15", "sca9", "sca1", "sca14", "sca12", "sca16", 
"sca7", "sca19", "sca12", "sca15", "sca4", "sca14", "sca15", 
"sca20", "sca20", "sca18", "sca14", "sca17", "sca5", "sca8", 
"sca6", "sca19", "sca19", "sca2", "sca16", "sca1", "sca2", "sca5", 
"sca2", "sca19", "sca7", "sca12", "sca5", "sca16", "sca4", "sca9", 
"sca10", "sca8", "sca16", "sca18", "sca3", "sca4", "sca19", "sca14", 
"sca1", "sca16", "sca18", "sca13", "sca12", "sca6", "sca3", "sca18", 
"sca8", "sca17", "sca7", "sca1", "sca17", "sca13", "sca11", "sca16", 
"sca5", "sca1", "sca12", "sca18", "sca9", "sca10", "sca2", "sca2", 
"sca13", "sca9", "sca4", "sca1", "sca16", "sca17", "sca10", "sca20", 
"sca13", "sca13", "sca12", "sca18", "sca10", "sca13", "sca1", 
"sca6", "sca16", "sca18", "sca8", "sca5", "sca15", "sca12", "sca11", 
"sca3", "sca5", "sca6", "sca7", "sca15", "sca14", "sca17", "sca3", 
"sca4", "sca8", "sca5", "sca6", "sca15", "sca7", "sca16", "sca7", 
"sca1", "sca8", "sca5", "sca13", "sca16", "sca5", "sca4", "sca10", 
"sca1"), PSC = c(0.21, 0.37, 0.64, 0.02, 0.86, 0.55, 0.05, 0.83, 
0.61, 0.71, 0.42, 0.92, 0.08, 0.49, 0.51, 0.03, 0.6, 0.56, 0.07, 
0.66, 0.58, 0.97, 0.81, 0.04, 0.02, 0.04, 0.34, 0.32, 0.05, 0.6, 
0.43, 0.86, 0.37, 0.14, 0.61, 0.34, 0.86, 0.54, 0.63, 0.84, 0.4, 
0.86, 1, 0.05, 0.81, 0.98, 0.96, 0.18, 0, 0.25, 0.19, 0.11, 0.39, 
0.16, 0.51, 0.42, 0.37, 0.5, 0.02, 0.54, 0.33, 0.02, 0.17, 0.8, 
0.39, 0.68, 0.62, 1, 0.86, 0.37, 0.22, 0.17, 0.75, 0.2, 0.05, 
0.11, 1, 0.21, 0.47, 0.24, 0.48, 0.68, 0.38, 0.99, 0.56, 0.11, 
0.83, 0.34, 0.55, 0.98, 0, 0.83, 0.19, 0.99, 0.6, 0.46, 0.4, 
0.11, 0.12, 0.75, 0.77, 0.04, 0.86, 0.95, 0.05, 0.17, 0.49, 0.71, 
0.35, 0.98, 0.16, 0.27, 0.74, 0.05, 0.56, 0.62, 0.35, 0.48, 0.26, 
0.4, 0.43, 0.49, 0.85, 0.69, 0.19, 0.67, 0.54, 0.67, 0.37, 0.25, 
0.95, 0.62, 0.93, 0.56, 0.27, 0.17, 0.71, 0.65, 0.02, 0.45, 0.09, 
0.42, 0.05, 0.26, 0.95, 0.88, 0.4, 0.48, 0.24, 0.15, 0.97, 0.61, 
0.26, 0.18, 0.15, 0.89, 0.84, 0.36, 0.26, 0.82, 0.24, 0.78, 0.24, 
0.33, 0.85, 0.47, 0.03, 0.68, 0.73, 0.57, 0.07, 0.8, 0.06, 0.91, 
0.11, 0.81, 0.58, 0.97, 0.42, 0.25, 0.26, 0.62, 0.25, 0.76, 0.84, 
0.59, 0.98, 0.67, 0.04, 0.08, 0.38, 0.49, 0.78, 0.27, 0.49, 0.8, 
0.18, 0.15, 0.17, 0.72, 0.74, 0.84, 0.36, 0.59, 0.5, 0.89, 0.38, 
0.08, 0.59, 0.61, 0.35, 0.64, 0.59, 0.86, 0.36, 0.91, 0.86, 0.06, 
0.22, 0.31, 0.16, 0.47, 0.92, 0.25, 0.42, 0.33, 0.14, 0.65, 0.46, 
0.74, 0.3, 0.92, 0.77, 0.7, 0.72, 0.79, 0.66, 0.68, 0.61, 0.76, 
0.06, 0.56, 0.43, 0.14, 0.91, 0.75, 0.61, 0.76, 0.54, 0.71, 0.23, 
0.91, 0.32, 0.17, 1, 0.44, 0.46, 0.64, 0.19, 0, 0.08, 0.2, 0.17, 
0.73, 0.19, 0.87, 0.7, 0.91, 0.24, 0.05, 0.32, 0.87, 0.9, 0.33, 
0.91, 0.72, 0.49, 0.62, 0.25, 0.92, 0.11, 0.82, 0.6, 0.7, 0.97, 
0.62, 0.86, 0.68, 0.44, 0.38, 0.9, 0.57, 0.17, 0.31, 0.84, 0.83, 
0.06, 0.87, 0.5, 0.96, 0.4, 0.64, 0.35, 0.7, 0.75, 0.09, 0, 0.48, 
0.29, 0.61, 0.93, 0.81, 0.67, 0.45, 0.29, 0.05, 0.69, 0.27, 0.03, 
0.83, 0.75, 0.34, 0.12, 0.64, 0.51, 0.54, 0.24, 0, 0.68, 0.46, 
0.98, 0.53, 0.03, 0.63, 0.75, 0.84, 0.56, 0.5, 0.33, 0.97, 0.45, 
0.43, 0.61, 0.26, 0.87, 0.94, 0.68, 0.89, 0.98, 0.42, 0.3, 0.1, 
0.62, 0.11, 0.73, 0.44, 0.85, 0.03, 1, 0.45, 0.95, 0.41, 0.02, 
0.45, 0.87, 0.62, 0.94, 0.92, 0.94, 0.92, 0.31, 0.26, 0.95, 0.73, 
0.36, 0.61, 0.78, 0.35, 0.04, 0.89, 0.68, 0.81, 0.3, 0.81, 0.07, 
0.56, 0.17, 0.48, 0.81, 0.49, 0.78, 0.88, 0.08, 0.63)), .Names = c("ID", 
"CoreId", "ES", "PSC"), row.names = c(NA, -394L), class = "data.frame")

并且我只想保留数字列 $PSC$ 在由（在本例中为数字，但用作类别）列 $ID$ 定义的每个组中具有最大值的行。如果有关系，我只想保留第一个出现的值。而且我只想使用基本的 R 功能，而不是需要学习新语法等的“方便”包。

经过几次试验，我发现以下似乎可行：

d <- d[order(d$ID,d$CoreId),]

d2 <- with(d,data.frame(ID=ID,PSC=-PSC))

dr <- aggregate(PSC~ID,data=d2,FUN=rank,ties.method="first")

PSC_ranks <- unlist(dr$PSC)

dred <- d[PSC_ranks==1,]

dred是所需的缩减数据帧。

问题：您在上面的代码中是否看到任何错误（即在某些情况下可能不起作用）/效率低/计算成本高，可以改进或存在哪些更短/更好的命令？

我在大约 187 K 行的 data.frame 上使用这种方法，它不是最快的，大约需要 5-10 秒；我想知道我的代码是否次优。

谢谢

标签： rsubset

Answer 1

按“ID”分组后，获取“PSC”最大值的索引which.max（将仅返回单个索引，即如果有关联，则返回第一个索引）到slice行

library(dplyr)
d %>%
   group_by(ID) %>%
   slice(which.max(PSC))

或使用top_n

d %>%
   group_by(ID) %>% 
   top_n(1, PSC)

---

如果我们需要一个有效的选项，请使用data.table

library(data.table)
setDT(d)[d[, .I[which.max(PSC)], ID]$V1]