Find most frequent combination of values in a data.frame

Question

I would like to find the most frequent combination of values in a data.frame.

Here's some example data:

dat <- data.frame(age=c(50,55,60,50,55),sex=c(1,1,1,0,1),bmi=c(20,25,30,20,25))

In this example the result I am looking for is the combination of age=55, sex=1 and bmi=25, since that is the most frequent combination of column values.

My real data has about 30000 rows and 20 columns. What would be an efficient way to find the most common combination of these 20 values among the 30000 observations?

Many thanks!

Answer 1

Here's an approach with data.table:

dt <- data.table(dat)
setkeyv(dt, names(dt))
dt[, .N, by = key(dt)]
dt[, .N, by = key(dt)][N == max(N)]
#    age sex bmi N
# 1:  55   1  25 2

And an approach with base R:

x <- data.frame(table(dat))
x[x$Freq == max(x$Freq), ]
#    age sex bmi Freq
# 11  55   1  25    2

I don't know how well either of these scale though, particularly if the number of combinations is going to be large. So, test back and report!

---

Replace x$Freq == max(x$Freq) with which.max(x$Freq) and N == max(N) with which.max(N) if you are really just interested in one row of results.

Answer 2

The quick and dirty solution. I am sure there is a fancier way to it though, with the plyr package or similar.

> (tab <- table(apply(dat, 1, paste, collapse=", ")))
50, 0, 20 50, 1, 20 55, 1, 25 60, 1, 30 
        1         1         2         1 

> names(which.max(tab))
[1] "55, 1, 25"