7

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!

Rob
  • 1,460
  • 2
  • 16
  • 23

4 Answers4

10

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.

A5C1D2H2I1M1N2O1R2T1
  • 190,393
  • 28
  • 405
  • 485
  • Thanks for the explanation! The data.table approach works perfectly and is very fast (0.31 sec on my own data of 30000*20), however, the base R-approach doesn't work on my own data since the number of combinations is indeed too large for table(). – Rob Sep 02 '13 at 10:45
  • Just tried the `dt[, .N, by = key(dt)][N == which max(N)]` you suggested to get only one row, but that gives me the same result als `dt[, .N, by = key(dt)][N == max(N)]`... Any suggestions on how to get only one row with the most common combination of values? – Rob Sep 02 '13 at 11:00
  • @Rob, It should be `dt[, .N, by = key(dt)][which.max(N)]`. – A5C1D2H2I1M1N2O1R2T1 Sep 02 '13 at 11:04
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"
Backlin
  • 14,612
  • 2
  • 49
  • 81
  • `table(do.call(paste, dat))` also works. Not sure how it compares speed-wise with `apply(...)` – A5C1D2H2I1M1N2O1R2T1 Sep 02 '13 at 09:54
  • Thanks, works perfectly! do.call seems to be a bit faster than the apply method (2.00 sec vs 2.58 sec). – Rob Sep 02 '13 at 10:46
  • That is probably due to that in my `apply` solution, `paste` is called on every row, but in the `do.call` solution it is only called once. I'm glad you settled on @AnandaMahto's answer though, it is definitely the way to go. – Backlin Sep 02 '13 at 11:38
1

Something like this??

> dat[duplicated(dat), ]
  age sex bmi
5  55   1  25

using while (maybe time consuming)

Here's another data.frame with more than 1 case duplicated

> dat <- data.frame(age=c(50,55,60,50,55, 55, 60),
                   sex=c(1,1,1,0,1, 1,1),
                   bmi=c(20,25,30,20,25, 25,30))
> dat[duplicated(dat), ] # see data.frame
      age sex bmi
    5  55   1  25
    6  55   1  25
    7  60   1  30


# finding the most repeated item
> while(any(duplicated(dat))){
   dat <- dat[duplicated(dat), ]
   #print(dat)
 }

> print(dat)
  age sex bmi
6  55   1  25
Jilber Urbina
  • 58,147
  • 10
  • 114
  • 138
1

Here's a tidyverse solution. Grouping by all variables and getting the count per group has the benefit that you can see the counts of all other groups, not just the max.

library(tidyverse)
dat <- data.frame(age=c(50,55,60,50,55),sex=c(1,1,1,0,1),bmi=c(20,25,30,20,25))
dat %>%
  group_by_all() %>%
  summarise(count = n()) %>%
  arrange(desc(count))
#> # A tibble: 4 x 4
#> # Groups:   age, sex [4]
#>     age   sex   bmi count
#>   <dbl> <dbl> <dbl> <int>
#> 1    55     1    25     2
#> 2    50     0    20     1
#> 3    50     1    20     1
#> 4    60     1    30     1

Created on 2018-10-17 by the reprex package (v0.2.0).

Calum You
  • 14,687
  • 4
  • 23
  • 42