Remove duplicates keeping the most frequent row

Question

I would like to deduplicate my data while keeping a row that has the most frequent occurrences. If there is a line in lines, I don't care what comes back - the first in alphabetical or numerical order - that's good. I would like to do this with the help of the group idand var.

MRE:

df <- data.frame(
  id = rep("a", 8),
  var = c(rep("b", 4), rep("c", 4)),
  val = c("d", "d", "d", "e", "f", "f", "g", "g")
)

> df
  id var val
1  a   b   d
2  a   b   d
3  a   b   d
4  a   b   e
5  a   c   f
6  a   c   f
7  a   c   g
8  a   c   g

Must be:

  id var val
1  a   b   d
2  a   c   f

I work with large datasets and chaining chains tidyverse, so a solution would be preferable dplyr.

Answer 1

Use tableand which.maxto extract mode:

df %>% 
    group_by(id, var) %>% 
    summarise(val = {t <- table(val); names(t)[which.max(t)] })

# A tibble: 2 x 3
# Groups:   id [?]
#  id    var   val  
#  <fct> <fct> <chr>
#1 a     b     d    
#2 a     c     f  

---

Another way to do this in the R database is to create a table of trilateral unexpected events directly, and then find the max column along the third axis:

apply(table(df), c(1, 2), function(v) names(v)[which.max(v)])

#   var
#id  b   c  
#  a "d" "f"

:

as.data.frame.table(
    apply(table(df), c(1, 2), function(v) names(v)[which.max(v)])
)

#  id var Freq
#1  a   b    d
#2  a   c    f

Answer 2

, ,

df %>% 
    group_by(id, var) %>% 
    filter(row_number() == rle(as.character(val))$lengths %>% 
                                {sum(.[1:which.max(.)])})

Answer 3

Using dplyr:

library(dplyr)

df %>% 
  group_by(id, var, val) %>% 
  summarise(n = n()) %>% 
  group_by(id, var) %>% 
  arrange(-n) %>% 
  slice(1) %>% 
  ungroup() %>% 
  select(-n)

# # A tibble: 2 x 3
#   id    var   val  
#   <fct> <fct> <fct>
# 1 a     b     d    
# 2 a     c     f   

Answer 4

One option may be to use tableand maxhow to:

library(dplyr)
df %>% group_by(id, var) %>%
  filter(table(val) == max(table(val))) %>%
  slice(1)
# # A tibble: 2 x 3
# # Groups: id, var [2]
# id     var    val   
# <fctr> <fctr> <fctr>
# 1 a      b      d     
# 2 a      c      g    

NOTE. a c gis a case of binding. For OP, any entry may be returned in case of equality.

Answer 5

Here is my attempt:

library(dplyr)
df %>%
  group_by(id,var,val) %>%
  mutate(n=n()) %>%
  arrange(desc(n)) %>%
  group_by(id,var) %>%
  filter(row_number()==1) %>%
  select(-n)

`

Answer 6

Dplyr solution using count:

library(dplyr)

df %>%
  count(id,var,val,sort = T) %>%
  group_by(id,var) %>%
  summarize_at("val",head,1)

# # A tibble: 2 x 3
#       id    var    val
#   <fctr> <fctr> <fctr>
# 1      a      b      d
# 2      a      c      f

or maybe more idiomatic, but longer:

df %>%
  count(id,var,val,sort = T) %>%
  group_by(id,var) %>%
  slice(1) %>%
  select(-n) %>%
  ungroup

Or with tallyfor the same output with slightly different syntax:

df %>%
  group_by(id,var,val) %>%
  tally(sort = T) %>%
  slice(1) %>%
  select(-n) %>%
  ungroup

and basic solution:

df2 <- aggregate(x ~ .,cbind(df,x=1),sum)
aggregate(val ~ id+var, df2[order(-df2$x),],head,1)
#   id var val
# 1  a   b   d
# 2  a   c   f