totaling grouped categories with multiple rows with same id

Question

I have a large data frame with first 2 columns as follows. I managed to group_count but unable to get a column with total count. i.e 14

id  company group_count
1   aaa 1
1   bbb 2
1   bbb 2
1   ccc 1
1   ddd 1
1   eee 2
1   eee 2
1   fff 3
1   fff 3
1   fff 3
1   ggg 1
1   hhh 1
1   iii 1
1   jjj 1

I am expecting the following

id  company count   total count
1   aaa 1   10
1   bbb 2   10
1   bbb 2   10
1   ccc 1   10
1   ddd 1   10
1   eee 2   10
1   eee 2   10
1   fff 3   10
1   fff 3   10
1   fff 3   10
1   ggg 1   10
1   hhh 1   10
1   iii 1   10
1   jjj 1   10

Why do you have total count as 10 when you have 14 rows with `id = 1`. — Ronak Shah, Aug 19 '19 at 02:41
in `dplyr`, you could use `n_distinct` to get unique values. `df %>% group_by(id) %>% mutate(total = n_distinct(company)) ` — Ronak Shah, Aug 19 '19 at 02:51
@RonakShah this is an intuitive approach, and easy to follow. — Zhiqiang Wang, Aug 19 '19 at 03:01

score 1 · Answer 1 · answered Aug 19 '19 at 02:47

Use ave to group company by id and calculate the length of unique elements. We first convert company to factor and then numeric so that the output is also numeric

ave(as.numeric(as.factor(df$company)), df$id, FUN = function(x) length(unique(x)))
# [1] 10 10 10 10 10 10 10 10 10 10 10 10 10 10

totaling grouped categories with multiple rows with same id

1 Answers1