CODEWITHSUNDEEP
rdataframecombinations
Rob
Rob

Reputation: 1490

Find most frequent combination of values in a data.frame

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!

Upvotes: 7

Views: 6490

Answers (4)

Calum You
Calum You

Reputation: 15062

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).

Upvotes: 1

Jilber Urbina
Jilber Urbina

Reputation: 61154

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

Upvotes: 1

A5C1D2H2I1M1N2O1R2T1
A5C1D2H2I1M1N2O1R2T1

Reputation: 193507

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.

Upvotes: 10

Backlin
Backlin

Reputation: 14842

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"

Upvotes: 2

Related Questions