Clever Geek Handbook

How can I remove duplicate rows based on a different column state?

How to select the duplicate row (based on the first column only) based on the max in the second column:

data<-data.frame(a=c(1,3,3,3),b=c(1,4,6,3),d=c(1,5,7,1))

a b d
1 1 1
3 4 5
3 6 7
3 3 1


a b d
1 1 1
3 6 7

In the second column 6 max between 4,6,3

+3


source to share


2 answers


You can try something like the following using "dplyr":

library(dplyr)

data %>%                  ## Your data
  group_by(a) %>%         ##   grouped by "a"
  filter(b == max(b))     ##   filtered to only include the rows where b == max(b)
# Source: local data frame [2 x 3]
# Groups: a
# 
#   a b d
# 1 1 1 1
# 2 3 6 7


Note, however, that if there are more rows that match b == max(b)

, they will also be returned. So an alternative could be:

data %>%                  ## Your data
  group_by(a) %>%         ##   grouped by "a"
  arrange(desc(b)) %>%    ##   sorted by descending values of "b"
  slice(1)                ##   with just the first row extracted
+6


source


Options using data.table

:

library(data.table)
setDT(data)[, .SD[which.max(b)], a]
#   a b d
#1: 1 1 1
#2: 3 6 7

Or using .I

to get the index of the row (which will be slightly faster)

 setDT(data)[data[, .I[which.max(b)], a]$V1]
 #   a b d
 #1: 1 1 1
 #2: 3 6 7


or

setkey(setDT(data), a,b)[,.SD[.N], a]
#   a b d
#1: 1 1 1
#2: 3 6 7

If there are relationships for maximum values

setDT(data)[, .SD[max(b)==b], a]
#   a b d
#1: 1 1 1
#2: 3 6 7
+3


source






More articles:


All Articles