Return most frequent string by group in base R

Question

I have a data set where I would like to return the most frequent entry in column b, for each unique entry of a, where both columns are character vectors. In the event where two entries in b are equally frequent for a unique entry in a, I would like to return both entries of b, in separate columns (desired output below).

This is similar to a question asked here, however the answers to that question all use tidyverse. I am tidyverse averse, as tidyverse objects break other things in my project. Looking for a base R solution (and looking to avoid a conversation about the merits of tidyverse).

My data look like this:

a <- as.character(c(rep(1:3,4)))
b <- c("A","A","A",
       "B","B","B",
       "A","B","A",
       "A","B","B")
df <- data.frame(a,b)

 a b
 1 A
 2 A
 3 A
 1 B
 2 B
 3 B
 1 A
 2 B
 3 A
 1 A
 2 B
 3 B

desired output:

 group match_1 match_2
     1       A    <NA>
     2       B    <NA>
     3       A       B

Answer 1

Continuing from docendo discimus's answer:

library(dplyr)
# library(tidyr)
df %>%
  count(a, b) %>%
  group_by(a) %>%
  filter(n == max(n)) %>%
  mutate(r = row_number()) %>%
  tidyr::spread(r, b) %>%
  select(-n)
# # A tibble: 3 x 3
# # Groups:   a [3]
#   a     `1`   `2`  
#   <fct> <fct> <fct>
# 1 1     A     <NA> 
# 2 2     B     <NA> 
# 3 3     A     B    

And then you just need to rename the columns.

Base R variant:

reshape(do.call(rbind.data.frame, by(df, df$a, function(x) {
  tb <- table(x$b)
  tb <- tb[ tb == max(tb) ]
  data.frame(a = x$a[1], b = names(tb), r = seq_along(tb))
})), timevar = "r", idvar = "a", direction = "wide")
#     a b.1  b.2
# 1   1   A <NA>
# 2   2   B <NA>
# 3.1 3   A    B

I'll break it down, since not all of it may be intuitive:

The by function returns a list (specially formatted, but still just a list). If we look at a single instance of a, let's explore what happens. I'll skip to a == "3", since that's the one with repeats:

by(df, df$a, function(x) { browser(); 1; })
# Called from: FUN(data[x, , drop = FALSE], ...)
# Browse[1]> 
debug at #1: [1] 1
# Browse[2]> 
Called from: FUN(data[x, , drop = FALSE], ...)
# Browse[1]> 
debug at #1: [1] 1
# Browse[2]> 
Called from: FUN(data[x, , drop = FALSE], ...)
# Browse[1]> 
debug at #1: [1] 1
# Browse[2]> 
x
#    a b
# 3  3 A
# 6  3 B
# 9  3 A
# 12 3 B
# Browse[2]> 
( tb <- table(x$b) )
# A B 
# 2 2 

Alright, so we now have the count per-b. Realize that there might easily have been more here, say:

# A B C
# 2 2 1

so I'm going to reduce this named vector to just those with the highest value:

# Browse[2]> 
( tb <- tb[ tb == max(tb) ] ) # no change here, but had there been a third value in 'b' ...
# A B 
# 2 2 

Lastly, we want by to capture a data.frame (that we can later combine). We're guaranteed that a is one value potentially repeated, so a[1]; we have ensured that names(tb) has all "interesting" values, and the r is a helper for reshape, later:

# Browse[2]> 
data.frame(a = x$a[1], b = names(tb), r = seq_along(tb))
#   a b r
# 1 3 A 1
# 2 3 B 2

Now that we explored internally, let's wrap that up.

by(df, df$a, function(x) {
   tb <- table(x$b)
   tb <- tb[ tb == max(tb) ]
   data.frame(a = x$a[1], b = names(tb), r = seq_along(tb))
})
# df$a: 1
#   a b r
# 1 1 A 1
# ------------------------------------------------------------ 
# df$a: 2
#   a b r
# 1 2 B 1
# ------------------------------------------------------------ 
# df$a: 3
#   a b r
# 1 3 A 1
# 2 3 B 2

This looks awkward, but if you look under the hood (with dput), you'll see it's just a re-classed list. We can now combine them into a single frame with:

do.call(rbind.data.frame, by(df, df$a, function(x) {
  tb <- table(x$b)
  tb <- tb[ tb == max(tb) ]
  data.frame(a = x$a[1], b = names(tb), r = seq_along(tb))
}))
#     a b r
# 1   1 A 1
# 2   2 B 1
# 3.1 3 A 1
# 3.2 3 B 2

BTW: for both data.frame and rbind.data.frame, these are by default giving you factors. If you don't want them, then:

do.call(rbind.data.frame, c(by(df, df$a, function(x) {
  tb <- table(x$b)
  tb <- tb[ tb == max(tb) ]
  data.frame(a = x$a[1], b = names(tb), r = seq_along(tb),
             stringsAsFactors = FALSE)
}), stringsAsFactors=FALSE))
#     a b r
# 1   1 A 1
# 2   2 B 1
# 3.1 3 A 1
# 3.2 3 B 2

And then the reshaping. I admit that this is the most fragile (at least for me) part of it. I'm not a reshape-user, I tend towards tidyr::spread or data.table::dcast, but this is base-R and works for now. The use of reshape is a tutorial in and of itself, so I won't go into it here. There are numerous attempts to provide more-user-friendly reshaping tools out there (reshape2, tidyr, data.table all come to mind up front but are unlikely to be the only ones).

Answer 2

Another base alternative.

Create contingency table and convert to data frame: as.data.frame(table(df)).

Use ave to select rows with max values by group.

Use ave to create a 'time' variable to "differentiate multiple records from the same group" (see ?reshape).

reshape relevant variables to wide.

d <- as.data.frame(table(df))
d2 <- d[d$Freq == ave(d$Freq, d$a, FUN = max), ]
d2$time <- ave(d2$a, d2$a, FUN = seq_along)
reshape(d2[ , c("a", "b", "time")], idvar = "a", direction = "wide")

#   a b.1  b.2
# 1 1   A <NA>
# 3 3   A    B
# 5 2   B <NA>

If desired, order by 'a'.

Answer 3

We can do this in base R

tbl <- table(df)
ifelse(tbl[,1] == tbl[,2], toString(colnames(tbl)), colnames(tbl)[max.col(tbl)])