Creating Crosstable with Multiple Variables Summarized by Row Categories

Question

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!

score 1 · Accepted Answer · answered Jan 24 '22 at 23:19

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

PaulS · Answer 2 · 2022-01-25T01:55:56.893

A solution based on purrr::map_dfc and tidyr::pivot_wider:

library(tidyverse)

map_dfc(c("vs", "am", "gear"), ~ mtcars %>% pivot_wider(id_cols = cyl, 
  names_from = .x, values_from = .x, values_fn = length,
  names_prefix = str_c(.x, "_"), names_sort = T, values_fill = 0) %>%
  {if (.x != "vs")  select(.,-cyl) else .}) %>% arrange(cyl)

#> This message is displayed once per session.
#> # A tibble: 3 × 8
#>     cyl  vs_0  vs_1  am_0  am_1 gear_3 gear_4 gear_5
#>   <dbl> <int> <int> <int> <int>  <int>  <int>  <int>
#> 1     4     1    10     3     8      1      8      2
#> 2     6     3     4     4     3      2      4      1
#> 3     8    14     0    12     2     12      0      2

I'm less facile with tidyverse approaches, so this solution is also good to see. Thank you. — wes, Jan 26 '22 at 18:30

Dan Chaltiel · Answer 3 · 2022-06-16T06:48:36.650

This was not really planned, but you can do this using the package crosstable, with the help of a simple left_join() call:

library(tidyverse)
library(crosstable)

ct1 = crosstable(mtcars, cyl, by=vs)
ct2 = crosstable(mtcars, cyl, by=am)

ct = left_join(ct1, ct2, by=c(".id", "label", "variable"), 
               suffix=c("_vs", "_am"))
ct
#> # A tibble: 3 × 7
#>   .id   label variable `0_vs`       `1_vs`      `0_am`      `1_am`    
#>   <chr> <chr> <chr>    <chr>        <chr>       <chr>       <chr>     
#> 1 cyl   cyl   4        1 (9.09%)    10 (90.91%) 3 (27.27%)  8 (72.73%)
#> 2 cyl   cyl   6        3 (42.86%)   4 (57.14%)  4 (57.14%)  3 (42.86%)
#> 3 cyl   cyl   8        14 (100.00%) 0 (0%)      12 (85.71%) 2 (14.29%)

as_flextable(ct)

^{Created on 2022-06-16 by the reprex package (v2.0.1)}

Maybe I will add a cbind() method for crosstables one day, so that the as_flextable() output looks better.

This looks quite promising as a straight-forward and flexible way to address tasks like this. — wes, Jul 24 '22 at 17:47

Creating Crosstable with Multiple Variables Summarized by Row Categories

3 Answers3