I am trying to remove duplicate rows from a data frame based on the max value on a different column
So, for the data frame:
df<-data.frame (rbind(c("a",2,3),c("a",3,4),c("a",3,5),c("b",1,3),c("b",2,6),c("r",4,5)) colnames(df)<-c("id","val1","val2")
id val1 val2 a 2 3 a 3 4 a 3 5 b 1 3 b 2 6 r 4 5I would like to keep remove all duplicates by id with the condition that for the corresponding rows they do not have the maximum value for val2.
Thus the data frame should become:
a 3 5 b 2 6 r 4 5-> remove all a duplicates but keep row with the max value for df$val2 for subset(df, df$id=="a")
4 Answers
Using base R. Here, the columns are factors. Make sure to convert it to numeric
df$val2 <- as.numeric(as.character(df$val2)) df[with(df, ave(val2, id, FUN=max)==val2),] # id val1 val2 #3 a 3 5 #5 b 2 6 #6 r 4 5Or using dplyr
library(dplyr) df %>% group_by(id) %>% filter(val2==max(val2)) # id val1 val2 #1 a 3 5 #2 b 2 6 #3 r 4 5One possible way is to use data.table
library(data.table)
setDT(df)[, .SD[which.max(val2)], by = id]
## id val1 val2
## 1: a 3 5
## 2: b 2 6
## 3: r 4 5Here's how I hope your data is really set up
df <- data.frame (id = c(rep("a", 3), rep("b", 2), "r"), val1 = c(2, 3, 3, 1, 2, 4), val2 = c(3, 4, 5, 3, 6, 5))You could do a split-unsplit
> unsplit(lapply(split(df, df$id), function(x) { if(nrow(x) > 1) { x[duplicated(x$id) & x$val2 == max(x$val2),] } else { x } }), levels(df$id))
# id val1 val2
# 3 a 3 5
# 5 b 2 6
# 6 r 4 5You can also use Reduce(rbind, ...) or do.call(rbind, ...) in place of unsplit
Another one
df %>% group_by(id) %>% slice(which.max(val2)) id val1 val2 a 3 5 b 2 6 r 4 5
