03. Outputs

Chris Bailiss

2021-06-27

Source: vignettes/v03-outputs.Rmd

In This Vignette

  • Overview
  • Example Table
  • Output as Plain Text
  • Output as HTML
  • Output in Excel
  • Output as FlexTable
  • Output in Word
  • Output in PowerPoint
  • Output as an R Matrix
  • Output as an R Data Frame
  • Further Reading

Overview

The basictabler package supports outputting a table in a number of different forms:

  • A htmlwidget for R-Studio - using tbl$renderTable() to render the table into the “Viewer” tab in R-Studio,
  • A htmlwidget for Shiny applications - using basictabler(tbl) to render the table into the Shiny app,
  • As HTML - using either:
    • tbl$getHtml() to retrieve a character variable containing HTML, or
    • tbl$saveHtml() to save the HTML to a file.
  • As plain text - using tbl to output to the console or tbl$asCharacter to retrieve as a character value.
  • Into an Excel Worksheet.

Sometimes it is desirable to retrieve the table as a more standard data type that is easier to work with in R code. A table can be converted to either a matrix or a data frame. Often neither data type is a perfect representation of your table - which option is better will depend upon your use case.

Example Table

The following table is used as the basis of the examples in the rest of this vignette:

# data for the table
saleIds <- c(5334, 5336, 5338)
items <- c("Apple", "Orange", "Banana")
quantities <- c(5, 8, 6)
prices <- c(0.34452354, 0.4732543, 1.3443243)

# construct the table
library(basictabler)
tbl <- BasicTable$new()
tbl$addData(data.frame(saleIds, items, quantities, prices), 
            firstColumnAsRowHeaders=TRUE,
            explicitColumnHeaders=c("Sale ID", "Item", "Quantity", "Price"),
            columnFormats=list(NULL, NULL, NULL, "%.2f"))
tbl$renderTable()

Output as Plain Text

A table is outputted to the console as plain text simply by using tbl:

# data for the table
saleIds <- c(5334, 5336, 5338)
items <- c("Apple", "Orange", "Banana")
quantities <- c(5, 8, 6)
prices <- c(0.34452354, 0.4732543, 1.3443243)

# construct the table
library(basictabler)
tbl <- BasicTable$new()
tbl$addData(data.frame(saleIds, items, quantities, prices), 
            firstColumnAsRowHeaders=TRUE,
            explicitColumnHeaders=c("Sale ID", "Item", "Quantity", "Price"),
            columnFormats=list(NULL, NULL, NULL, "%.2f"))

# output table
tbl
Sale ID    Item  Quantity  Price  
   5334   Apple         5   0.34  
   5336  Orange         8   0.47  
   5338  Banana         6   1.34

Alternatively, the plain text representation of the table can be retrieved as a character value using tbl$asCharacter.

Output as HTML

Rendering a htmlwidget

A table is outputted as a htmlwidget simply by calling tbl$renderTable(). There are numerous examples throughout these vignettes, including the example directly above.

For outputting as a htmlwidget in a Shiny application, use basictabler(tbl).

Retrieving HTML

To retrieve the HTML of a table, use tbl$getHtml(). This returns a list of html tag objects built using the htmltools package. This object can be converted to a simple character variable using as.character() or as illustrated below. The CSS declarations for a table can be retrieved using tbl$getCss() - also illustrated below.

# data for the table
saleIds <- c(5334, 5336, 5338)
items <- c("Apple", "Orange", "Banana")
quantities <- c(5, 8, 6)
prices <- c(0.34452354, 0.4732543, 1.3443243)

# construct the table
library(basictabler)
tbl <- BasicTable$new()
tbl$addData(data.frame(saleIds, items, quantities, prices), 
            firstColumnAsRowHeaders=TRUE,
            explicitColumnHeaders=c("Sale ID", "Item", "Quantity", "Price"),
            columnFormats=list(NULL, NULL, NULL, "%.2f"))

#out the HTML and CSS
cat(paste(tbl$getHtml(), sep="", collapse="\n"))
<table class="Table">
  <tr>
    <th class="ColumnHeader">Sale ID</th>
    <th class="ColumnHeader">Item</th>
    <th class="ColumnHeader">Quantity</th>
    <th class="ColumnHeader">Price</th>
  </tr>
  <tr>
    <th class="RowHeader">5334</th>
    <td class="Cell">Apple</td>
    <td class="Cell">5</td>
    <td class="Cell">0.34</td>
  </tr>
  <tr>
    <th class="RowHeader">5336</th>
    <td class="Cell">Orange</td>
    <td class="Cell">8</td>
    <td class="Cell">0.47</td>
  </tr>
  <tr>
    <th class="RowHeader">5338</th>
    <td class="Cell">Banana</td>
    <td class="Cell">6</td>
    <td class="Cell">1.34</td>
  </tr>
</table>
cat(tbl$getCss())
.Table {display: table; border-collapse: collapse; }
.ColumnHeader {font-family: Arial; font-size: 0.75em; padding: 2px; border: 1px solid lightgray; vertical-align: middle; text-align: center; font-weight: bold; background-color: #F2F2F2; }
.RowHeader {font-family: Arial; font-size: 0.75em; padding: 2px 8px 2px 2px; border: 1px solid lightgray; vertical-align: middle; text-align: left; font-weight: bold; background-color: #F2F2F2; }
.Cell {font-family: Arial; font-size: 0.75em; padding: 2px 2px 2px 8px; border: 1px solid lightgray; vertical-align: middle; text-align: right; }
.Total {font-family: Arial; font-size: 0.75em; padding: 2px 2px 2px 8px; border: 1px solid lightgray; vertical-align: middle; text-align: right; font-weight: bold; }

Output in Excel

Please see the Excel Export vignette.

Output as FlexTable

Converting a table from the basictabler package to a table from the flextabler package is possible:

# data for the table
saleIds <- c(5334, 5336, 5338)
items <- c("Apple", "Orange", "Banana")
quantities <- c(5, 8, 6)
prices <- c(0.34452354, 0.4732543, 1.3443243)

# construct the table
library(basictabler)
tbl <- BasicTable$new()
tbl$addData(data.frame(saleIds, items, quantities, prices), 
            firstColumnAsRowHeaders=TRUE,
            explicitColumnHeaders=c("Sale ID", "Item", "Quantity", "Price"),
            columnFormats=list(NULL, NULL, NULL, "%.2f"))

# convert to flextable
library(flextable)
ft <- tbl$asFlexTable()
ft

When converting to a flextable, the styles used in the basictabler table are converted to their flextable equivalents where possible. If you specify both a CSS and ft value, then the CSS value will be used when rendering to HTML and the ft value will be used when converting the table to a flex table. The table below shows how the CSS style values are mapped to the equivalent style settings in the flextable package.

CSS Property FT Property FT Example Notes
font-family ft-font-name Arial Only the first CSS font is used in Excel.
font-size ft-font-size 12 In Points (4-72). See below for CSS units.
font-weight ft-bold normal or bold XL bold is CSS font-weight >= 600.
font-style ft-italic normal or italic italic and oblique map to italic.
background-color ft-bg-color #FF0000 See below for supported CSS colours.
color ft-text-color #00FF00 See below for supported CSS colours.
text-align ft-h-align left or center or right
vertical-align ft-v-align top or middle or bottom
ft-text-rotation lrtb tblr btlr number One of the constant values or 0 to 359.
padding ft-padding number (in px, pt, etc) See below for supported padding values.
padding-left ft-padding-left number (in px, pt, etc) See below for supported padding values.
padding-right ft-padding-right number (in px, pt, etc) See below for supported padding values.
padding-top ft-padding-top number (in px, pt, etc) See below for supported padding values.
padding-bottom ft-padding-bottom number (in px, pt, etc) See below for supported padding values.
border ft-border thin black See below for supported CSS border values.
border-left ft-border-left thin black See below for supported CSS border values.
border-right ft-border-right thin black See below for supported CSS border values.
border-top ft-border-top thin black See below for supported CSS border values.
border-bottom ft-border-bottom thin black See below for supported CSS border values.

Notes:

  • For CSS font-size, only the following units are supported: in, cm, mm, pt, pc, px, em, %.
  • For CSS background-color, CSS color and border colours, only hex colours, named colours, RGB() and RGBA() values are supported. HLS() and HLSA() values are not supported. Examples of supported CSS values: #0080FF, rgb(0, 128, 255), rgba(0, 128, 255, 0.5), red, black, darkolivegreen, etc.
  • For padding, either use CSS padding / ft-padding to specify the same padding on all four sides of each cell. Or use the side-specific versions, e.g. CSS padding-left / ft-padding-left.
  • For borders, either use CSS border / xl-border to specify the same border on all four sides of each cell. Or use the side-specific versions, e.g. CSS border-left / xl-border-left.

Examples of valid ft border values:

  • thin solid #FF00BB
  • 2px dotted red
  • medium dashed rgb(0, 255, 0)

Note that the following CSS properties are NOT supported for automatic mapping to flextable values:

  • font - use font-family, font-size, etc. instead
  • Border side specific properties, e.g. border-left-style, border-top-color, etc.

Output in Word

Converting a table to a Word document is possible using the flextabler package:

# data for the table
saleIds <- c(5334, 5336, 5338)
items <- c("Apple", "Orange", "Banana")
quantities <- c(5, 8, 6)
prices <- c(0.34452354, 0.4732543, 1.3443243)

# construct the table
library(basictabler)
tbl <- BasicTable$new()
tbl$addData(data.frame(saleIds, items, quantities, prices), 
            firstColumnAsRowHeaders=TRUE,
            explicitColumnHeaders=c("Sale ID", "Item", "Quantity", "Price"),
            columnFormats=list(NULL, NULL, NULL, "%.2f"))

# convert to flextable
library(flextable)
ft <- tbl$asFlexTable()

# save word document
library(officer)
docx <- read_docx()
docx <- body_add_par(docx, "Example Table")
docx <- body_add_flextable(docx, value = ft)
print(docx, target = "example_table_word.docx")

Output in PowerPoint

Converting a table to a PowerPoint document is possible using the flextabler package:

# data for the table
saleIds <- c(5334, 5336, 5338)
items <- c("Apple", "Orange", "Banana")
quantities <- c(5, 8, 6)
prices <- c(0.34452354, 0.4732543, 1.3443243)

# construct the table
library(basictabler)
tbl <- BasicTable$new()
tbl$addData(data.frame(saleIds, items, quantities, prices), 
            firstColumnAsRowHeaders=TRUE,
            explicitColumnHeaders=c("Sale ID", "Item", "Quantity", "Price"),
            columnFormats=list(NULL, NULL, NULL, "%.2f"))

# convert to flextable
library(flextable)
ft <- tbl$asFlexTable()

# save PowerPoint document
library(officer)
ppt <- read_pptx()
ppt <- add_slide(ppt, layout = "Title and Content", master = "Office Theme")
ppt <- ph_with(ppt, value = ft, location = ph_location_left()) 
print(ppt, target = "example_table_powerpoint.pptx")

Output as an R Matrix

Converting a table to a matrix can be accomplished as follows:

# data for the table
saleIds <- c(5334, 5336, 5338)
items <- c("Apple", "Orange", "Banana")
quantities <- c(5, 8, 6)
prices <- c(0.34452354, 0.4732543, 1.3443243)

# construct the table
library(basictabler)
tbl <- BasicTable$new()
tbl$addData(data.frame(saleIds, items, quantities, prices), 
            firstColumnAsRowHeaders=TRUE,
            explicitColumnHeaders=c("Sale ID", "Item", "Quantity", "Price"),
            columnFormats=list(NULL, NULL, NULL, "%.2f"))

# output as matrix
tbl$asMatrix()
     [,1]      [,2]     [,3]       [,4]   
[1,] "Sale ID" "Item"   "Quantity" "Price"
[2,] "5334"    "Apple"  "5"        "0.34" 
[3,] "5336"    "Orange" "8"        "0.47" 
[4,] "5338"    "Banana" "6"        "1.34"

The firstRowAsColumnNames and firstColumnAsRowNames parameters control how the names in the matrix are set. The rawValue parameter specifies whether the matrix should contain the raw values or the formatted values.

# data for the table
saleIds <- c(5334, 5336, 5338)
items <- c("Apple", "Orange", "Banana")
quantities <- c(5, 8, 6)
prices <- c(0.34452354, 0.4732543, 1.3443243)

# construct the table
library(basictabler)
tbl <- BasicTable$new()
tbl$addData(data.frame(saleIds, items, quantities, prices), 
            firstColumnAsRowHeaders=TRUE,
            explicitColumnHeaders=c("Sale ID", "Item", "Quantity", "Price"),
            columnFormats=list(NULL, NULL, NULL, "%.2f"))

# output as matrix
tbl$asMatrix(firstRowAsColumnNames=TRUE, firstColumnAsRowNames=TRUE, rawValue=TRUE)
     Item     Quantity Price       
5334 "Apple"  "5"      "0.34452354"
5336 "Orange" "8"      "0.4732543" 
5338 "Banana" "6"      "1.3443243"

Output as an R Data Frame

The asDataFrame() function returns a data frame with the same layout as the table, e.g. a table with a body consisting of 10 rows and 2 columns will result in a data frame also containing 10 rows and 2 columns.

Again, the firstRowAsColumnNames and firstColumnAsRowNames parameters control how the names in the data frame are set and the rawValue parameter specifies whether the matrix should contain the raw values or the formatted values.

# data for the table
saleIds <- c(5334, 5336, 5338)
items <- c("Apple", "Orange", "Banana")
quantities <- c(5, 8, 6)
prices <- c(0.34452354, 0.4732543, 1.3443243)

# construct the table
library(basictabler)
tbl <- BasicTable$new()
tbl$addData(data.frame(saleIds, items, quantities, prices), 
            firstColumnAsRowHeaders=TRUE,
            explicitColumnHeaders=c("Sale ID", "Item", "Quantity", "Price"),
            columnFormats=list(NULL, NULL, NULL, "%.2f"))

# output as data frame
df <- tbl$asDataFrame(firstRowAsColumnNames=TRUE, rawValue=TRUE)
df
  Sale.ID   Item Quantity     Price
1    5334  Apple        5 0.3445235
2    5336 Orange        8 0.4732543
3    5338 Banana        6 1.3443243
str(df)
'data.frame':   3 obs. of  4 variables:
 $ Sale.ID : num  5334 5336 5338
 $ Item    : chr  "Apple" "Orange" "Banana"
 $ Quantity: num  5 8 6
 $ Price   : num  0.345 0.473 1.344