How many of each distinct item in a column is assigned to each distinct item of another column?

user_16354132 0
Viewed 52

I am trying with dplyr functions group_by() and summarize(), count() but I can not figure out how to do :

How many of each color is assigned to each id? ie : id = 2 has 1 b and 1 r. id 3 has only 1 r ,...

df = data.frame(color = c("b","r","r","g","y","y","r"), id = c(2,3,2,6,4,4,7))


df %>%
  group_by(id) %>%
  summarize(count = n_distinct(color))

Is there any explicit way to do this? I would think of maybe creating dummies but my real dataset is very large and then I was wondering is there is a neat, pretty style of coding for this. Thanks for any suggestion.

output:

  color id_2 id_3 id_4 id_6 id_7
1     b    1    0    0    0    0
2     g    0    0    0    1    0
3     r    1    1    0    0    1
4     y    0    0    2    0    0
dplyr tidyverse dataframe r
2 Answers

Are you simply looking for:

df %>%
  group_by(id) %>%
  count(color)

?

# A tibble: 6 x 3
# Groups:   id [5]
     id color     n
  <dbl> <chr> <int>
1     2 b         1
2     2 r         1
3     3 r         1
4     4 y         2
5     6 g         1
6     7 r         1

Or, alternatively, if you want to have the ids as columns:

df %>%
  group_by(id) %>%
  count(color) %>%
  pivot_wider(names_from = id,
              names_prefix = 'id_',
              values_from = n,
              values_fill = 0) %>%
  arrange(color)

# A tibble: 4 x 6
  color  id_2  id_3  id_4  id_6  id_7
  <chr> <int> <int> <int> <int> <int>
1 b         1     0     0     0     0
2 g         0     0     0     1     0
3 r         1     1     0     0     1
4 y         0     0     2     0     0
user_27257730

Thanks for adding your expected output; here are two potential solutions:

library(tidyverse)

df = data.frame(color = c("b","r","r","g","y","y","r"),
                id = c(2,3,2,6,4,4,7))

df %>%
  group_by(id) %>%
  count(color) %>%
  pivot_wider(values_from = n,
              names_from = id,
              names_prefix = "id_",
              values_fill = 0)
#> # A tibble: 4 × 6
#>   color  id_2  id_3  id_4  id_6  id_7
#>   <chr> <int> <int> <int> <int> <int>
#> 1 b         1     0     0     0     0
#> 2 r         1     1     0     0     1
#> 3 y         0     0     2     0     0
#> 4 g         0     0     0     1     0

table(df$color, df$id)
#>    
#>     2 3 4 6 7
#>   b 1 0 0 0 0
#>   g 0 0 0 1 0
#>   r 1 1 0 0 1
#>   y 0 0 2 0 0

Another potential approach is to use tabyl() from the janitor package, i.e.

library(janitor)
#> 
#> Attaching package: 'janitor'
#> The following objects are masked from 'package:stats':
#> 
#>     chisq.test, fisher.test
df %>%
  tabyl(color, id)
#>  color 2 3 4 6 7
#>      b 1 0 0 0 0
#>      g 0 0 0 1 0
#>      r 1 1 0 0 1
#>      y 0 0 2 0 0

This package provides further functionality over the other approaches shown above, e.g. converting counts to percentages:

df %>%
  tabyl(color, id) %>%
  adorn_percentages() %>%
  adorn_pct_formatting(digits = 0)
#>  color    2   3    4    6   7
#>      b 100%  0%   0%   0%  0%
#>      g   0%  0%   0% 100%  0%
#>      r  33% 33%   0%   0% 33%
#>      y   0%  0% 100%   0%  0%

Or adding the total count in brackets after the percentage:

df %>%
  tabyl(color, id) %>%
  adorn_percentages() %>%
  adorn_pct_formatting(digits = 0) %>%
  adorn_ns()
#>  color        2       3        4        6       7
#>      b 100% (1)  0% (0)   0% (0)   0% (0)  0% (0)
#>      g   0% (0)  0% (0)   0% (0) 100% (1)  0% (0)
#>      r  33% (1) 33% (1)   0% (0)   0% (0) 33% (1)
#>      y   0% (0)  0% (0) 100% (2)   0% (0)  0% (0)

Created on 2022-03-25 by the reprex package (v2.0.1)

user_129573400
Related