in R, how to keep only the data.frame rows corresponding to the maximal value of one numerical column aggregated by a category column?

Question

I have this 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")

and I want to keep only the rows where numerical column $PSC$ has the maximal value within each group defined by (in this case numerical, but used as a category) column $ID$. If there are ties, I want to keep only the first occurring value. And I'd like to use only basic R functionalities, not 'convenient' packages that however require learning new syntax etc.

After several trials, I found that the following seems to work:

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 being the desired reduced data.frame.

Question: do you see anything wrong (i.e. that may not work in some cases) / inefficient / computationally expensive in the above code, which could be improved or for which shorter/better commands exist?

I am using this method on a data.frame with ~187 K rows, and it's not the fastest, it takes about 5-10 seconds; I'm wondering if my code is suboptimal.

Thank you

Yes thanks, I am aware of the several packages that do this kind of operation in various ways and with various syntax. So my question is different in the sense that I was looking for the most efficient way to do this **only using basic R functionalities**. I specified that in the original question. — user6376297, Nov 22 '18 at 08:14

akrun · Answer 1 · 2018-11-21T20:53:52.363

0

After grouping by 'ID', get the index of the maximum value of 'PSC' with which.max (will return only a single index i.e. the first index if there are ties) to slice the rows

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

Or use top_n

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

If we need an efficient option, use data.table

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

edited Nov 21 '18 at 20:53

answered Nov 21 '18 at 20:48

akrun

874,273
37
540
662

Thank you akrun. data.table is indeed a nice package, and your code gave a huge improvement in system.time. I was hoping that the basic R functionalities could achieve the same result, but as a practical solution yours is currently the best one I know of. I tried to upvote your answer but apparently I can't. – user6376297 Nov 22 '18 at 18:45

in R, how to keep only the data.frame rows corresponding to the maximal value of one numerical column aggregated by a category column?

1 Answers1