rdplyrcrosstable

Creating Crosstable with Multiple Variables Summarized by Row Categories


I am interested in summarizing several outcomes by sample categories and presenting it all in one table. Something with output that resembles:

vs am
cyl 0 1 0 1
4 1 10 3 8
6 3 4 4 3
8 14 0 12 2

were I able to combine ("cbind") the tables generated by:

ftable(mtcars$cyl, mtcars$vs)

and by:

ftable(mtcars$cyl, mtcars$am)

The crosstable() and CrossTable() packages showed promise but I couldn't see how to expand it out to multiple groups of columns without nesting them.

As demonstrated here, ftable can get close with:

ftable(vs + am ~ cyl, mtcars)

except for also nesting am within vs.

Similarly, dplyr gets close via, e.g.,

library(dplyr)
mtcars %>%
  group_by(cyl, vs, am) %>%
  summarize(count = n())

or something more complex like this

but I have several variables to present and this nesting defeats the ability to summarize in my case.

Perhaps aggregate could work in the hands of a cleverer person than I?

TYIA!


Solution

  • foo = function(df, grp, vars) {
        lapply(vars, function(nm) {
            tmp = as.data.frame(as.matrix(ftable(reformulate(grp, nm), df)))
            names(tmp) = paste0(nm, "_", names(tmp))
            tmp
        })
    }
    
    do.call(cbind, foo(mtcars, "cyl", c("vs", "am", "gear")))
    #   vs_0 vs_1 am_0 am_1 gear_3 gear_4 gear_5
    # 4    1   10    3    8      1      8      2
    # 6    3    4    4    3      2      4      1
    # 8   14    0   12    2     12      0      2