Find most frequent combination of values in a data

问题:

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!

回答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.

回答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"

回答3:

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

回答4:

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