How do I sum recurring values according to a level in a column and output a table of counts?

Question

I'm new to R and I have data that looks something like this:

categories <- c("A","B","C","A","A","B","C","A","B","C","A","B","B","C","C")
animals <- c("cat","cat","cat","dog","mouse","mouse","rabbit","rat","shark","shark","tiger","tiger","whale","whale","worm")
dat <- cbind(categories,animals)

Some animals repeat according to the category. For example, "cat" appears in all three categories A, B, and C.

I like my new dataframe output to look something like this:

A   B   C   count
1   1   1   1
1   1   0   2
1   0   1   0
0   1   1   2
1   0   0   2
0   1   0   0
0   0   1   2
0   0   0   0

The number 1 under A, B, and C means that the animal appears in that category, 0 means the animal does not appear in that category. For example, the first line has 1s in all three categories. The count is 1 for the first line because "cat" is the only animal that repeats itself in each category.

Is there a function in R that will help me achieve this? Thank you in advance.

Answer 1

We can use table to create a cross-tabulation of categories and animals, transpose, convert to data.frame, group_by all categories and count the frequency per combination:

library(dplyr)
library(tidyr)

as.data.frame.matrix(t(table(dat))) %>%
  group_by_all() %>%
  summarize(Count = n())

Result:

# A tibble: 5 x 4
# Groups:   A, B [?]
      A     B     C Count
  <int> <int> <int> <int>
1     0     0     1     2
2     0     1     1     2
3     1     0     0     2
4     1     1     0     2
5     1     1     1     1

Edit (thanks to @C. Braun). Here is how to also include the zero A, B, C combinations:

as.data.frame.matrix(t(table(dat))) %>%
  bind_rows(expand.grid(A = c(0,1), B = c(0,1), C = c(0,1))) %>%
  group_by_all() %>%
  summarize(Count = n()-1) 

or with complete, as suggested by @Ryan:

as.data.frame.matrix(t(table(dat))) %>%
  mutate(non_missing = 1) %>%
  complete(A, B, C) %>%
  group_by(A, B, C) %>%
  summarize(Count = sum(ifelse(is.na(non_missing), 0, 1))) 

Result:

# A tibble: 8 x 4
# Groups:   A, B [?]
      A     B     C Count
  <dbl> <dbl> <dbl> <dbl>
1     0     0     0     0
2     0     0     1     2
3     0     1     0     0
4     0     1     1     2
5     1     0     0     2
6     1     0     1     0
7     1     1     0     2
8     1     1     1     1

Answer 2

We have

xxtabs <- function(df, formula) {
    xt <- xtabs(formula, df)
    xxt <- xtabs( ~ . , as.data.frame.matrix(xt))
    as.data.frame(xxt)
}

and

> xxtabs(dat, ~ animals + categories)
  A B C Freq
1 0 0 0    0
2 1 0 0    2
3 0 1 0    0
4 1 1 0    2
5 0 0 1    2
6 1 0 1    0
7 0 1 1    2
8 1 1 1    1

(dat should really be constructed as data.frame(animals, categories)). This base approach uses xtabs() to form the first cross-tabulation

xt <- xtabs(~ animals + categories, dat)

then coerces using as.data.frame.matrix() to a second data.frame, and uses a second cross-tabulation of all columns of the computed data.frame

xxt <- xtabs(~ ., as.data.frame.matrix(xt))

coerced to the desired form

as.data.frame(xxt)

I originally said this approach was 'arcane', because it relies on knowledge of the difference between as.data.frame() and as.data.frame.matrix(); I think of xtabs() as a tool that users of base R should know. I see though that the other solutions also require this arcane knowledge, as well as knowledge of more obscure (e.g., complete(), group_by_all(), funs()) parts of the tidyverse. Also, the other answers are not (or at least not written in a way that allows) easily generalizable; xxtabs() does not actually know anything about the structure of the incoming data.frame, whereas implicit knowledge of the incoming data are present throughout the other answers.

One 'lesson learned' from the tidy approach is to place the data argument first, allowing piping

dat %>% xxtabs(~ animals + categories)

Answer 3

If I understood you correctly, this should do the trick.

require(tidyverse)

 dat %>% 
  mutate(value = 1) %>%
  spread(categories, value) %>%
  mutate_if(is.numeric, funs(replace(., is.na(.), 0))) %>% 
  mutate(count = rowSums(data.frame(A, B, C), na.rm = TRUE)) %>%
  group_by(A, B, C) %>%
  summarize(Count = n()) 

# A tibble: 5 x 4
# Groups:   A, B [?]
      A     B     C Count
  <dbl> <dbl> <dbl> <int>
1    0.    0.    1.     2
2    0.    1.    1.     2
3    1.    0.    0.     2
4    1.    1.    0.     2
5    1.    1.    1.     1

Answer 4

Adding a data.table solution. First, pivot animals against categories using dat. Then, create the combinations of A, B, C using CJ. Join that combinations with dat and count the number of occurrences for each combi.

dcast(as.data.table(dat), animals ~ categories, length)[
    CJ(A=0:1, B=0:1, C=0:1), .(count=.N), on=c("A","B","C"), by=.EACHI]