Tabulating using group_by on multiple columns in R

user1kee 0
Viewed 19
library(dplyr)
mydat <- data.frame(ID = c(123, 123, 111, 111, 111), 
           class = c("A", "A", "A", "A", "B"),
           new_ID = c(999, 872, 999, 1566, 254))
> mydat
   ID class new_ID
1 123     A    999
2 123     A    872
3 111     A    999
4 111     A   1566
5 111     B    254

I'm trying to get a count of how many new_IDs there are for each ID-class pairing:

> mydat %>% group_by(ID, class) %>% mutate(n_new_ID = length(unique(new_ID))) %>% select(ID, class, n_new_ID)
# A tibble: 5 × 3
# Groups:   ID, class [3]
     ID class n_new_ID
  <dbl> <chr>    <int>
1   123 A            2
2   123 A            2
3   111 A            2
4   111 A            2
5   111 B            1

However, I'm not sure why some rows are repeated. My desired output is

# A tibble: 3 × 3
# Groups:   ID, class [3]
     ID class n_new_ID
  <dbl> <chr>    <int>
1   123 A            2
2   111 A            2
3   111 B            1
dplyr r
1 Answers

Use summarise instead of mutate - mutate creates a column in the dataset with the same number of rows where as summarise summarises the output to return a single row (assuming the function returns a single value). Also, n_distinct can be used in place of length(unique

library(dplyr)
mydat %>%
   group_by(ID, class)  %>%
   summarise(n_new_ID = n_distinct(new_ID), .groups = 'drop')

-output

# A tibble: 3 × 3
     ID class n_new_ID
  <dbl> <chr>    <int>
1   111 A            2
2   111 B            1
3   123 A            2

Or use distinct and get the count

mydat %>% 
  distinct %>%
  count(ID, class, name = 'n_new_ID', sort = TRUE)
   ID class n_new_ID
1 111     A        2
2 123     A        2
3 111     B        1
akrun0
Related