1

Please find below my sample data. I would like to identify the most frequent number(s) from the column named 'Most frequent'.

structure(list(name = c("act1_25", "act1_26", "act1_27", "act1_28", 
    "act1_29", "act1_30", "act1_31", "act1_32", "act1_33", "act1_34", 
    "act1_35", "act1_36", "act1_37", "act1_38", "act1_39", "act1_40", 
    "act1_41", "act1_42", "act1_43", "act1_44", "act1_45", "act1_46", 
    "act1_47", "act1_48", "serial"), `Most frequent` = c("111,110,9120,310,3110,210,1110", 
    "210,3110,110,310,9120,9210,8210,3819,1110,111", "210,110,310,8210,3110,1110", 
    "210,9120,110,310,8210,1110,3819", "210,9120,310,110,1110,111", 
    "9120,110,1110,3830", "110,1110,3210,310", "210,110,1110,8210,310", 
    "1110,310,8210,110", "210,310,1110,8210,110", "310,1110,8210,210,110", 
    "3210,9120,1110,8210,110", "1110,210,310,110,3830", "1110,210,310,110,3210,3830", 
    "1110,8210,110,3830,3210", "1110,310,110,3210,5190", "1110,110,5190", 
    "1110,3210,3830,310,8210,110,5190", "1110,8210,310,210,1120,110", 
    "1110,8210,310", "1110,8210,310", "1110,8210,310,110", "1110,8210,310,110", 
    "210,1110,8210,5190,110", "27080618")), class = c("tbl_df", "tbl", 
    "data.frame"), row.names = c(NA, -25L))

Example of Output

Most frequent: 110
s_baldur
  • 29,441
  • 4
  • 36
  • 69
Rfanatic
  • 2,224
  • 1
  • 5
  • 21

3 Answers3

4

You can do

names(which.max(table(unlist(strsplit(df$`Most frequent`, ",")))))
#> [1] "1110"

Or if you want the value with its actual count you could do:

rev(sort(table(unlist(strsplit(df$`Most frequent`, ",")))))[1]
#> 1110 
#>   24

You can see that 1110 actually had 24 entries compared to 22 entries for 110:

rev(sort(table(unlist(strsplit(df$`Most frequent`, ",")))))
#>    1110      110      310     8210      210     9120     3210     3830 
#>      24       22       19       16       12        6        6        5 
#>    5190     3110      111     3819     9210 27080618     1120 
#>       4        3        3        2        1        1        1
Allan Cameron
  • 147,086
  • 7
  • 49
  • 87
2

I would suggest a tidyverse approach:

library(tidyr)
library(dplyr)
#Separate rows
df %>% separate_rows(`Most frequent`,2,sep = ',') %>%
  group_by(`Most frequent`) %>%
  summarise(N=n()) %>% arrange(desc(N))

Output:

   Most frequent  N
1           1110 24
2            110 22
3            310 19
4           8210 16
5            210 12
6           3210  6
7           9120  6
8           3830  5
9           5190  4
10           111  3
11          3110  3
12          3819  2
13          1120  1
14      27080618  1
15          9210  1

Which is similar to @AllanCameron results.

Duck
  • 39,058
  • 13
  • 42
  • 84
1
df <- structure(list(name = c("act1_25", "act1_26", "act1_27", "act1_28", 
    "act1_29", "act1_30", "act1_31", "act1_32", "act1_33", "act1_34", 
    "act1_35", "act1_36", "act1_37", "act1_38", "act1_39", "act1_40", 
    "act1_41", "act1_42", "act1_43", "act1_44", "act1_45", "act1_46", 
    "act1_47", "act1_48", "serial"), `Most frequent` = c("111,110,9120,310,3110,210,1110", 
    "210,3110,110,310,9120,9210,8210,3819,1110,111", "210,110,310,8210,3110,1110", 
    "210,9120,110,310,8210,1110,3819", "210,9120,310,110,1110,111", 
    "9120,110,1110,3830", "110,1110,3210,310", "210,110,1110,8210,310", 
    "1110,310,8210,110", "210,310,1110,8210,110", "310,1110,8210,210,110", 
    "3210,9120,1110,8210,110", "1110,210,310,110,3830", "1110,210,310,110,3210,3830", 
    "1110,8210,110,3830,3210", "1110,310,110,3210,5190", "1110,110,5190", 
    "1110,3210,3830,310,8210,110,5190", "1110,8210,310,210,1120,110", 
    "1110,8210,310", "1110,8210,310", "1110,8210,310,110", "1110,8210,310,110", 
    "210,1110,8210,5190,110", "27080618")), class = c("tbl_df", "tbl", 
    "data.frame"), row.names = c(NA, -25L))

library(dplyr)
library(tidyr)

df %>% 
  #//separate Most frequent using , as the separator 
  separate_rows(`Most frequent`, sep = ",") %>%
  #//count the unique values of the Most Frequent variable with results in new column named frequency
  count(`Most frequent`, name = "frequency") %>% 
  #//arrange frequency column in descending order
  arrange(desc(frequency))

#> # A tibble: 15 x 2
#>    `Most frequent` frequency
#>    <chr>               <int>
#>  1 1110                   24
#>  2 110                    22
#>  3 310                    19
#>  4 8210                   16
#>  5 210                    12
#>  6 3210                    6
#>  7 9120                    6
#>  8 3830                    5
#>  9 5190                    4
#> 10 111                     3
#> 11 3110                    3
#> 12 3819                    2
#> 13 1120                    1
#> 14 27080618                1
#> 15 9210                    1
Eric
  • 2,699
  • 5
  • 17