Contigency table with many columns, R

Question

I want a contigency table looking like something like this:

Generic data:

df <-data.frame(Product=c("a","a","a","b","b","c","c"), 
            Content=c("A","B","C","C","E","A","F"),
            Klass=c("pH","pH","Tenc", "Tenc", "Qsalt","pH","Qsalt"),
            Mix=c("Mix1", "Mix1", "Mix1", "Mix1","Mix1", "Mix2", "Mix2"))

I manage to do this

library(tidyverse)
library(flextable)
df2 <- df %>% with(ftable(Product,Content))
df3 <- as.data.frame(df2) %>%   spread(Content, Freq)
flextable(df3)

Which give me this

But if i try

df4 <- df %>% with(ftable(Mix,Product,Content))
df5 <- as.data.frame(df4) %>% spread(Content, Freq)
flextable(df5)

The mix section is wrong. And also, How can I add the top row (Content) from the first picture? Is it even possible?

Answer 1

I prefer to use count and then pivot_wider (with values_fill) as it will not have the combinations that are not in the data.

df %>% count(Mix, Product, Content) %>%
  pivot_wider(names_from = "Content", values_from = "n", values_fill = 0) %>% 
  flextable() %>% 
  add_header(Mix="", Product="", A="pH", B="pH", C="pH", E="Tenc", `F`="Qsalt") %>% 
  merge_h(part="header") %>% 
  align(j=3:7, align = "centre", part="all")

Which gives something like this

  Mix   Product     A     B     C     E     F
  <chr> <chr>   <int> <int> <int> <int> <int>
1 Mix1  a           1     1     1     0     0
2 Mix1  b           0     0     1     1     0
3 Mix2  c           1     0     0     0     1

As the main block of your table and then

Output

with ft then you can customize your flextable to your heart's content.