Create table with nested header from pre-summarized data

Question

How do I create a nested table from a data.frame, which have already been summarized? By nested I mean that the table has headers and subheaders.

My input data looks like this:

library(ggplot2)
library(reshape2)
df <- ggplot2::diamonds
count(df, cut,color) %>% mutate( 
  n = n,
  pct = round(n / sum(n),2) ) %>% reshape2::melt() -> df2
head(df2 ) 

> head(df2 )
   cut color variable value
1 Fair     D        n   163
2 Fair     E        n   224
3 Fair     F        n   312
4 Fair     G        n   314
5 Fair     H        n   303
6 Fair     I        n   175

I would like to have something this:

             Color
               D          E          F          G          H          I         J
        cut    n   pct    n   pct    n   pct    n   pct    n   pct    n   pct   n   pct
       Fair  163  0.10  224  0.14  312  0.19  314  0.20  303  0.19  175  0.11 119  0.07
       Good  662  0.13  933  0.19  909  0.19  871  0.18  702  0.14  522  0.11 307  0.06
  Very Good 1513  0.13 2400  0.20 2164  0.18 2299  0.19 1824  0.15 1204  0.10 678  0.06
    Premium 1603  0.12 2337  0.17 2331  0.17 2924  0.21 2360  0.17 1428  0.10 808  0.06
      Ideal 2834  0.13 3903  0.18 3826  0.18 4884  0.23 3115  0.14 2093  0.10 896  0.04

Below is an example of the closest I can get. The problem with this table below is that there is only one header. I would like 3 rows/headers: One which says the name of the variable: Color, one which lists the individual categories inside color, and one which lists type of summary (coming from df2$variable):

reshape2::dcast(df2, cut  ~ color + variable , value.var = c("value")  ) 
        cut  D_n D_pct  E_n E_pct  F_n F_pct  G_n G_pct  H_n H_pct  I_n I_pct J_n J_pct
1      Fair  163  0.10  224  0.14  312  0.19  314  0.20  303  0.19  175  0.11 119  0.07
2      Good  662  0.13  933  0.19  909  0.19  871  0.18  702  0.14  522  0.11 307  0.06
3 Very Good 1513  0.13 2400  0.20 2164  0.18 2299  0.19 1824  0.15 1204  0.10 678  0.06
4   Premium 1603  0.12 2337  0.17 2331  0.17 2924  0.21 2360  0.17 1428  0.10 808  0.06
5     Ideal 2834  0.13 3903  0.18 3826  0.18 4884  0.23 3115  0.14 2093  0.10 896  0.04

I hope there is some function/package which can do this. I think it should be possible because the packages etable and tables, and the function ftable, can create the output I want, but not for pre-summarized data.

This link does what I need (I think), but I only have access to CRAN-packages on the server I use.

https://www.r-statistics.com/2012/01/printing-nested-tables-in-r-bridging-between-the-reshape-and-tables-packages/

Where does this need to be displayed? In the console? In an RMarkdown? HTML? Exported to text editor/spreadsheet? — GGamba, Mar 23 '17 at 15:05
For pre-calculated data, you may use `identity` in `tables::tabular` as describes e.g. here: [Create a table in R with header expanding on two columns using xtable or any package](http://stackoverflow.com/questions/17560683/create-a-table-in-r-with-header-expanding-on-two-columns-using-xtable-or-any-pac). — Henrik, Mar 23 '17 at 15:12

score 0 · Answer 1 · answered Mar 24 '17 at 08:11

Solution based on comments. Thanks!

# data
    library(tidyr)
    library(dplyr)
    library(ggplot2)
    library(reshape2)
    df <- ggplot2::diamonds
    count(df, cut,color) %>% mutate( 
      n = n,
      pct = round(n / sum(n),2) ) %>% reshape2::melt() -> df2
    head(df2 ) 

# Solution
    spread( data = df2, key = variable, value = value  )  -> df2_spread

    tabular( Heading() * cut ~ color * (n + pct) * Heading() * (identity), data =df2_spread )

Create table with nested header from pre-summarized data

1 Answers1