Find the most frequent value by row

Question

My problem is as follows:

I have a data set containing several factor variables, which have the same categories. I need to find the category, which occurs most frequently for each row. In case of ties an arbitrary value can be chosen, although it would be great if I can have more control over it.

My data set contains over a hundred factors. However, the structure is something like that:

df = data.frame(id = 1:3
                var1 = c("red","yellow","green")
                var2 = c("red","yellow","green")
                var3 = c("yellow","orange","green")
                var4 = c("orange","green","yellow"))

df
#   id   var1   var2   var3   var4
# 1  1    red    red yellow orange
# 2  2 yellow yellow orange  green
# 3  3  green  green  green yellow

The solution should be a variable within the data frame, for example var5, which contains the most frequent category for each row. It can be a factor or a numeric vector (in case the data need to be converted first to numeric vectors)

In this case, I would like to have this solution:

df$var5
# [1] "red"    "yellow" "green" 

Any advice will be much appreciated! Thanks in advance!

Answer 1

For an internal package I've made a rowMode-function in which you can choose what to do with ties and missing values:

rowMode <- function(x, ties = NULL, include.na = FALSE) {
  # input checks data
  if ( !(is.matrix(x) | is.data.frame(x)) ) {
    stop("Your data is not a matrix or a data.frame.")
  }
  # input checks ties method
  if ( !is.null(ties) && !(ties %in% c("random", "first", "last")) ) {
    stop("Your ties method is not one of 'random', 'first' or 'last'.")
  }
  # set ties method to 'random' if not specified
  if ( is.null(ties) ) ties <- "random"
  
  # create row frequency table
  rft <- table(c(row(x)), unlist(x), useNA = c("no","ifany")[1L + include.na])
  
  # get the mode for each row
  colnames(rft)[max.col(rft, ties.method = ties)]
}

Several possible outputs (based on the different parameter options):

> rowMode(DF[,-1])
 [1] "B" "E" "B" "E" "B" "C" "B" "E" "A" "E"
> rowMode(DF[,-1], ties = "first")
 [1] "B" "B" "B" "A" "B" "C" "B" "E" "A" "E"
> rowMode(DF[,-1], ties = "first", include.na = TRUE)
 [1] "B" NA  "B" NA  "B" "C" "B" "E" "A" "E"
> rowMode(DF[,-1], ties = "last", include.na = TRUE)
 [1] "B" NA  NA  NA  "B" "C" "B" "E" "D" "E"
> rowMode(DF[,-1], ties = "last")
 [1] "B" "C" "B" "E" "B" "C" "B" "E" "D" "E"

---

Used data:

set.seed(2020)
DF <- data.frame(id = 1:10, matrix(sample(c(LETTERS[1:5], NA_character_), 60, TRUE), ncol = 6))

Answer 2

Here is another base R option:

tab <- table(data.frame(as.vector(row(df[,-1L])), unlist(df[,-1L])))
colnames(tab)[max.col(tab, "first")]

Or another data.table approach:

melt(as.data.table(df), id.vars="id")[
    order(id, value), ri := rowid(rleid(value))][,
        value[which.max(ri)], id]$V1

timing code:

library(data.table)
set.seed(0L)
nr <- 1e5L
nc <- 4L
DF <- data.frame(id=1L:nr, as.data.frame(matrix(sample(letters, nr*nc, TRUE), ncol=nc)))
DT <- as.data.table(DF)

mtd0 <- function(df) apply(df,1,function(x) names(which.max(table(x))))

Mode <- function(x) {
    ux <- unique(x)
    ux[which.max(tabulate(match(x, ux)))]
}

mtd_dt <- function(dt) melt(dt, id.vars="id")[, Mode(value), id]$V1

mtd_dt2 <- function(dt) melt(dt, id.vars="id")[
    order(id, value), ri := rowid(rleid(value))][,
        value[which.max(ri)], id]$V1

mtd2 <- function(df) {
    tab <- table(data.frame(as.vector(row(df[,-1L])), unlist(df[,-1L])))
    colnames(tab)[max.col(tab, "first")]
}

df = data.frame(id = 1:3,
    var1 = c("red","yellow","green"),
    var2 = c("red","yellow","green"),
    var3 = c("yellow","orange","green"),
    var4 = c("orange","green","yellow"))

a0 <- mtd0(df)
identical(a0, mtd_dt(as.data.table(df)))
#[1] TRUE

identical(a0, mtd2(df))
#[1] TRUE

identical(a0, mtd_dt2(as.data.table(df)))
#[1] TRUE

microbenchmark::microbenchmark(times=1L, mtd0(DF), mtd_dt(DT), mtd_dt2(DT), mtd2(DF))

timings:

Unit: milliseconds
        expr        min         lq       mean     median         uq        max neval
    mtd0(DF) 10083.9941 10083.9941 10083.9941 10083.9941 10083.9941 10083.9941     1
  mtd_dt(DT)  1056.2319  1056.2319  1056.2319  1056.2319  1056.2319  1056.2319     1
 mtd_dt2(DT)   168.6183   168.6183   168.6183   168.6183   168.6183   168.6183     1
    mtd2(DF)   519.2030   519.2030   519.2030   519.2030   519.2030   519.2030     1

Answer 3

If your data is quite big you might want to consider using the data.table package.

# Generate the data
nrow <- 10^5
id <- 1:nrow
colors <- c("red","yellow","green")
var1 <- sample(colors, nrow, replace = TRUE)
var2 <- sample(colors, nrow, replace = TRUE)
var3 <- sample(colors, nrow, replace = TRUE)
var4 <- sample(colors, nrow, replace = TRUE)

Mode <- function(x) {
    ux <- unique(x)
    ux[which.max(tabulate(match(x, ux)))]
}

Chargaff's solution is simple and works well in some cases. You can gain a small performance improvement (~20%) using data.table.

df <- data.frame(cbind(id, var1, var2, var3, var4))
system.time(apply(df, 1, Mode))
#   user  system elapsed
#  1.242   0.018   1.264

library(data.table)
dt <- data.table(cbind(id, var1, var2, var3, var4))
system.time(melt(dt, measure = patterns('var'))[, Mode(value1), by = id])
#   user  system elapsed
#  1.020   0.012   1.034

Answer 4

Something like :

apply(df,1,function(x) names(which.max(table(x))))
[1] "red"    "yellow" "green" 

In case there is a tie, which.max takes the first max value. From the which.max help page :

Determines the location, i.e., index of the (first) minimum or maximum of a numeric vector.

Ex :

var4 <- c("yellow","green","yellow")
df <- data.frame(cbind(id, var1, var2, var3, var4))

> df
  id   var1   var2   var3   var4
1  1    red    red yellow yellow
2  2 yellow yellow orange  green
3  3  green  green  green yellow

apply(df,1,function(x) names(which.max(table(x))))
[1] "red"    "yellow" "green"