How to Customize a Table with Multiple Statistics using CreateCustomTable in R

This article describes how to create a CreateCustomTable R table with multiple statistics so that you can take advantage of all the customizations in the CreateCustomTable function.

The article explains how to go from a table with multiple statistics like this:

To a table that enables you to add in more font, color, and formatting customizations (examples under See Also below):

Requirements

• A Displayr table showing multiple statistics, or a combined table created with R (see: How to Combine Tables with Multiple Statistics). 

Method

The example below reformats a multiple statistic table in R for the CreateCustomTable function. The code then automatically creates a list of spans to span both the Row % and Count rows of each brand. To modify for your own document, please read through the comments (denoted with #) and edit where appropriate.

The code below is on the more complex side so a familiarity with How to Work with Data in R, How to Perform Mathematical Calculations Using R, and How to Work with Conditional R Formulas is very useful.

You can see other options for further customizing the final table at CreateCustomTable. Click here for a similar example (and more) in your Displayr account.

1. From the toolbar, click the Calculation button and draw a box for your table on the page.
2. Paste in the following R code in the R CODE box in the object inspector and then click Calculate:
   
   

   ###Identify table to use
   mytable = table.Brand.attitude.5
   
   ###Convert the table (which is an array because there are multiple stats) 
   ###to a 2 dimensional table
   #load the reshape2 functions needed
   library(reshape2)
   #use melt() to make the table "long" with a row for each stat/row name pair
   mytable=melt(mytable)
   #use dcast() to make the table "wide" keeping two columns for brand (Var1) and statistic (Var3) and creating columns for each rating categories
   mytable=dcast(mytable, Var1 + Var3 ~ Var2)
   
   ###Add in custom formatting for row% and counts
   #round the data (in columns 3-5) to whole numbers
   mytable[3:5]=apply(mytable[,3:5],2,round,0)
   #add % to the row % rows (in columns 3-5)
   mytable[mytable$Var3 == "Row %",3:5]=apply(mytable[mytable$Var3 == "Row %",3:5],2,paste0,"%")
   
   ###automatically create list of spans and format for final table
   ###(each brand span will span over the Row % and Count rows)
   #create the list of lists automatically that create spans for final table using Var1
   spans=lapply(unique(mytable$Var1),function(x) list(height=NROW(mytable)/length(unique(mytable$Var1)), label=x))
   #create placeholder rownames which will be hidden later
   rownames(mytable)=paste(mytable$Var1, mytable$Var3)
   
   ###Create the custom table
   #row.header.labels are overridden as blank because we want to use spans to label
   #col.widths sets the second column (the blank row labels) to 0 to hide it
   flipFormat::CreateCustomTable(mytable[,!colnames(mytable) %in% c("Var1","Var3")],
            row.header.labels=rep(" ",NROW(mytable)),
            col.widths=c("25%","0%","25%","25%","25%"),
            row.span.align.horizontal="right",
            row.spans=spans,
            row.span.pad=5,
            border.width=1,
            border.color="black")

Given the data manipulation above, it can be helpful to see a preview of what the table looks like after melting and dcasting. 

After melting:

After dcasting using the formula Var1 + Var3 ~ Var2:

See Also

CreateCustomTable

How to Work with Data in R

How to Use Paste Functions to Create Dynamic Text Using R

How to Customize Fonts in a CreateCustomTable R Table

How to Add Row Spans to a CreateCustomTable R Table

How to Customize Colors on a CreateCustomTable R Table

How to Add Statistical Significance to CreateCustomTable R Tables