Mutate count by group and condition

[budugulo]

code	name
123	A
123	B
666	X
999	M
999	M
555	P
555	Q
555	R

I want to mutate a variable wanted, which will count the number of different names for each code. For example, for code 123 the new variable will take the value 2 but for code 999 the value will be 1 since names are the same.

Here is the desired output:

code	name	wanted
123	A	2
123	B	2
666	X	1
999	M	1
999	M	1
555	P	3
555	Q	3
555	R	3

library(dplyr)
# toy data
df <- tibble(
  code = c(123, 123, 666, 999, 999, 555, 555, 555),
  name = c("A", "B", "X", "M", "M", "P", "Q", "R"),
  wanted = c(2, 2, 1, 1, 1, 3, 3, 3)
)

[FJCC]

I think this is what you want, though the count of 999 is 2 not 1.

library(dplyr)
# toy data
df <- tibble(
   code = c(123, 123, 666, 999, 999, 555, 555, 555),
   name = c("A", "B", "X", "M", "M", "P", "Q", "R")#,
   #wanted = c(2, 2, 1, 1, 1, 3, 3, 3)
 )
df
# A tibble: 8 x 2
   code name 
  <dbl> <chr>
1   123 A    
2   123 B    
3   666 X    
4   999 M    
5   999 M    
6   555 P    
7   555 Q    
8   555 R    
df <- df |> group_by(code) |>  mutate( wanted = n())
df
# A tibble: 8 x 3
# Groups:   code [4]
   code name  wanted
  <dbl> <chr>  <int>
1   123 A          2
2   123 B          2
3   666 X          1
4   999 M          2
5   999 M          2
6   555 P          3
7   555 Q          3
8   555 R          3

[budugulo]

Thanks for the reply. Unfortunately, I want the count of 999 to be 1 because it has only one distinct name - M.

[mikecrobp]

Until googling just now I would have gone through 2 group_by/summarise layers. (please excuse the change of pipes)

df <- df %>% group_by(code, name) %>% summarise() # get distinct rows
df <- df %>% group_by(code) %>% summarise(wanted = n()) # get countss

But it turns out you can have n_distinct in summarise:

df <- df %>% group_by(code) %>% summarise(wanted = n_distinct()) 
df <- df %>% group_by(code) %>% summarise() # get distinct rows

[budugulo]

Thanks for the tip on ´n_distinct()´.

The following code solves the problem

library(dplyr)
# toy data
df <- tibble(
  code = c(123, 123, 666, 999, 999, 555, 555, 555),
  name = c("A", "B", "X", "M", "M", "P", "Q", "R")#,
  #wanted = c(2, 2, 1, 1, 1, 3, 3, 3)
)
df %>% group_by(code) %>%
  mutate(wanted = n_distinct(name)) 
#> # A tibble: 8 x 3
#> # Groups:   code [4]
#>    code name  wanted
#>   <dbl> <chr>  <int>
#> 1   123 A          2
#> 2   123 B          2
#> 3   666 X          1
#> 4   999 M          1
#> 5   999 M          1
#> 6   555 P          3
#> 7   555 Q          3
#> 8   555 R          3

^{Created on 2021-11-17 by the reprex package (v2.0.1)}