Return most frequent string value for each group

Question

a <- c(rep(1:2,3))
b <- c("A","A","B","B","B","B")
df <- data.frame(a,b)

> str(b)
chr [1:6] "A" "A" "B" "B" "B" "B"

  a b
1 1 A
2 2 A
3 1 B
4 2 B
5 1 B
6 2 B

I want to group by variable a and return the most frequent value of b

My desired result would look like

  a b
1 1 B
2 2 B

In dplyr it would be something like

df %>% group_by(a) %>% summarize (b = most.frequent(b))

I mentioned dplyr only to visualize the problem.

OK, I found it myself `df %>% group_by(a) %>% summarize (b =names(which.max(table(b))))` — rmuc8, Apr 28 '15 at 14:24

score 31 · Accepted Answer · answered Apr 28 '15 at 14:25

31

The key is to start grouping by both a and b to compute the frequencies and then take only the most frequent per group of a, for example like this:

df %>% 
  count(a, b) %>%
  slice(which.max(n))

Source: local data frame [2 x 3]
Groups: a

  a b n
1 1 B 2
2 2 B 2

Of course there are other approaches, so this is only one possible "key".

answered Apr 28 '15 at 14:25

talat

62,625
18
110
141

1

Hi, I tried this solution. Not really working. the function will only return the maximum n row, not maximum row per group. In this case, there is a draw --so you can see 2 rows(both n is 2, if group A==1 maximum b==B n is 3 and group A==2, maximum b==B is 2 then you will only have one row) – CloverCeline Oct 31 '19 at 11:29
I think @talat may have forgot a line of code. Pipe the output of count into group_by before piping them into slice – Guy4444 Mar 27 '21 at 21:03

Ferroao · Answer 2 · 2020-10-14T17:25:59.527

5

What works for me or is simpler is:

df %>% group_by(a) %>% count(b) %>% top_n(1) # includes ties

library(data.table)
DT<-as.data.table(df)
DT[ , .N, by=.(a, b)][
  order(-N), 
  .SD[ N == max(N) ]
  ,by=a]                     # includes ties

edited Oct 14 '20 at 17:25

answered Nov 08 '18 at 20:23

Ferroao

1,953
14
36

1

Thank you but you need to be careful with duplicates – DJV Jan 07 '19 at 11:30
This doesn't work for me.. I get the wrong output. Probably because of duplicates – Louise Sørensen Jan 08 '20 at 09:16
See the answer of GGamba or others in https://stackoverflow.com/questions/42698465/obtaining-3-most-common-elements-of-groups-concatenating-ties-and-ignoring-les for ties – Ferroao Jan 08 '20 at 13:25

score 3 · Answer 3 · answered Apr 28 '15 at 14:25

by() each value of a, create a table() of b and extract the names() of the largest entry in that table():

> with(df,by(b,a,function(xx)names(which.max(table(xx)))))
a: 1
[1] "B"
------------------------
a: 2
[1] "B"

You can wrap this in as.table() to get a prettier output, although it still does not exactly match your desired result:

> as.table(with(df,by(b,a,function(xx)names(which.max(table(xx))))))
a
1 2 
B B

Return most frequent string value for each group

3 Answers3

Linked

Related