Directly Converting Data Frames into Contingency Tables (R)

Question

I am working with R.

Given the following random data I generated, I was able to make a contingency table with this data:

library(memisc)
library(dplyr)

set.seed(123)

v1 <- c("2010-2011","2011-2012", "2012-2013", "2013-2014", "2014-2015") 
v2 <- c("A", "B", "C", "D", "E")
v3 <- c("Z", "Y", "X", "W" )
v4 <- c("data_1", "data_2", "data_3", "data_4" )


dates <- as.factor(sample(v1, 1000, replace=TRUE, prob=c(0.5, 0.2, 0.1, 0.1, 0.1)))

types <- as.factor(sample(v2,1000, replace=TRUE, prob=c(0.3, 0.2, 0.1, 0.1, 0.1)))

types2 <- as.factor(sample(v3, 1000, replace=TRUE, prob=c(0.3, 0.5, 0.1, 0.1)))

names <- as.factor(sample(v3, 1000, replace=TRUE, prob=c(0.3, 0.5, 0.1, 0.1)))

var = rnorm(1000,10,10)

problem_data = data.frame(var,dates, types, types2, names)


summary <- xtabs(~dates+names+types+types2, problem_data)
t = ftable(summary, row.vars=1, col.vars=2:4)

show_html(t)

Is it possible to make contingency tables similar to the table directly from a data frame?

For example, suppose I wanted to make the above contingency table, but instead of filling this table with "counts", I would like to fill the table with the average of "var". Using the "dplyr" library, I can create a data frame that contains all the values required for this contingency table:

library(dplyr)

contingency_table = data.frame(problem_data %>% group_by(dates,names, types, types2) %>% summarise(mean_value = mean(var)))

head(contingency_table)

      dates names types types2 mean_value
1 2010-2011     W     A      X -10.128687
2 2010-2011     W     A      Y   9.552724
3 2010-2011     W     A      Z   9.686354
4 2010-2011     W     B      W  -4.411400
5 2010-2011     W     B      Y  13.624970
6 2010-2011     W     B      Z   7.008089

Can this above data frame be made into a contingency table, and then converted to a html publishable format?

Using this stackoverflow post here (Is there an (easy) way to convert flat contingency tables (ftable) to flextable), I tried to convert this data frame into a contingency table using the function that was provided - but it is not giving the desired result (i.e. it is not the same as the table above):

ftable_to_flextable <- function( x ){
    
    row.vars = attr( x, "row.vars" )
    col.vars = attr( x, "col.vars" )
    rows <- rev( expand.grid( rev(row.vars), stringsAsFactors = FALSE ) )
    cols <- rev(expand.grid( rev(col.vars), stringsAsFactors = FALSE ))
    
    xmat <- as.matrix(x)
    cols$col_keys = dimnames(xmat)[[2]]
    xdata <- cbind(
        data.frame(rows, stringsAsFactors = FALSE),
        data.frame(xmat, stringsAsFactors = FALSE)
    )
    names(xdata) <- c(names(row.vars), cols$col_keys)
    
    ft <- regulartable(xdata)
    ft <- set_header_df(ft, cols)
    ft <- theme_booktabs(ft)
    ft <- merge_v(ft, j = names(row.vars))
    ft
}

library(flextable)
library(magrittr)

ftable(contingency_table, row.vars = 1:2, col.vars = 3:4) %>% ftable_to_flextable()

Is it possible to make a contingency table where instead of counts, the mean value of the variable "var" for each group is used? Is it better to do this using the xtabs() function , and I have overcomplicated this using the "data frame approach"? Can someone please show me how to do this?

Thanks!

Answer 1

The generic cross-tabulation method is known as tapply in R. You can read in more detail here. For example, you can

tapply(problem_data$var, problem_data[, -1L], mean)

This gives you something analogous to xtabs(~dates + types + types2 + names, data = problem_data), except that all values in the cells are now group means. You can do something like

smy <- tapply(problem_data$var, problem_data[, -1L], mean)
x <- ftable(smy, row.vars = c(4L, 1L), col.vars = 2:3) # var 1 is the first var you see in names(problem_data[, -1L])
y <- ftable(smy, row.vars = 1L, col.vars = c(4L, 2:3))

And memisc::show_html(x) gives

memisc::show_html(y) gives