1. Introduction

Chris Bailiss

2017-09-29

In This Vignette

Introducing basictabler

The basictabler package enables rich tables to be created and rendered/exported with just a few lines of R.

The basictabler package:

The tables are rendered as htmlwidgets or plain text. The HTML/text can be exported for use outside of R.

The tables can also be exported to Excel, including the styling/formatting. The formatting/styling is specified once and can then be used when rendering to both HTML and Excel - i.e. it is not necessary to specify the formatting/styling separately for each output format.

basictabler is a companion package to the pivottabler package. pivottabler is focussed on generating pivot tables and can aggregate data. basictabler does not aggregate data but offers more control of table structure.

The latest version of the basictabler package can be obtained directly from the package repository. Please log any questions not answered by the vignettes or any bug reports here.

Sample Data: Trains in Birmingham

To build some example tables, we will use the bhmsummary data frame. This summarises the 83,710 trains that arrived into and/or departed from Birmingham New Street railway station between 1st December 2016 and 28th February 2017. As an example, the following are the first four rows from this sample data - note the data has been transposed (otherwise the table would be very wide).

# the qhtbl() function is explained later in this vignette
library(basictabler)
qhtbl(t(bhmsummary[1:4,]), rowNamesAsRowHeaders=TRUE)

Each row in this sample data summarises different types of trains running through Birmingham.

The first row from the sample data (column 1 above) represents:

Quick-Table Functions

To construct basic tables quickly, two functions are provided that can construct tables with one line of R:

The arguments to both functions are the same:

A basic example of quickly printing a pivot table to the console using the qtbl() function:

library(basictabler)
qtbl(data.frame(a=1:2, b=3:4))
a  b  
1  3  
2  4  

The qtbl() function is nothing more than a concise version of a more verbose syntax, i.e.

library(basictabler)
tbl <- qtbl(data.frame(a=1:2, b=3:4))

… is equivalent to …

library(basictabler)
tbl <- BasicTable$new()
tbl$addData(data.frame(a=1:2, b=3:4))

Other operations can be carried out on the table object, e.g. rendering it as a HTML widget:

library(basictabler)
tbl <- BasicTable$new()
tbl$addData(data.frame(a=1:2, b=3:4))
tbl$renderTable()

The qhtbl() function renders the table immediately as html widget:

library(basictabler)
qhtbl(data.frame(a=1:2, b=3:4))

When creating tables from data frames or matrices, it is possible to specify how values should be formatted for display in the table. The following example makes use of the sample data and illustrates how to specify formatting:

# aggregate the sample data to make a small data frame
library(basictabler)
library(dplyr)
tocsummary <- bhmsummary %>%
  group_by(TOC) %>%
  summarise(OnTimeArrivals=sum(OnTimeArrivals),
            OnTimeDepartures=sum(OnTimeDepartures),
            TotalTrains=sum(TrainCount)) %>%
  ungroup() %>%
  mutate(OnTimeArrivalPercent=OnTimeArrivals/TotalTrains*100,
         OnTimeDeparturePercent=OnTimeDepartures/TotalTrains*100) %>%
  arrange(TOC)

# To specify formatting, a list is created which contains one element for each column in 
# the data frame, i.e. tocsummary contains six columns so the columnFormats list has six elements.
# The values in the first column in the data frame won't be formatted since NULL has been specified.
# The values in the 2nd, 3rd and 4th columns will be formatted using format(value, big.mark=",")
# The values in the 5th and 6th columns will be formatted using sprintf(value, "%.1f")
columnFormats=list(NULL, list(big.mark=","), list(big.mark=","), list(big.mark=","), "%.1f", "%.1f")

# render the table directly as a html widget
qhtbl(tocsummary, firstColumnAsRowHeaders=TRUE,
            explicitColumnHeaders=c("TOC", "On-Time Arrivals", "On-Time Departures",
                                    "Total Trains", "On-Time Arrival %", "On-Time Departure %"),
            columnFormats=columnFormats)

Cell-by-Cell Construction

The examples in this vignette illustrate constructing tables from data frames. This populates a table quickly with just one line of R.

Tables can also be constructed row-by-row, column-by-column and/or cell-by-cell. For more details, please see the Working with Cells vignette.

Further Manipulation

Further operations on the basic table object tbl can be carried out to modify the table. For example, to add a total row:

# aggregate the sample data to make a small data frame
library(basictabler)
library(dplyr)
tocsummary <- bhmsummary %>%
  group_by(TOC) %>%
  summarise(OnTimeArrivals=sum(OnTimeArrivals),
            OnTimeDepartures=sum(OnTimeDepartures),
            TotalTrains=sum(TrainCount)) %>%
  ungroup() %>%
  mutate(OnTimeArrivalPercent=OnTimeArrivals/TotalTrains*100,
         OnTimeDeparturePercent=OnTimeDepartures/TotalTrains*100) %>%
  arrange(TOC)

# calculate the data for the total row
totalsummary <- bhmsummary %>%
  summarise(OnTimeArrivals=sum(OnTimeArrivals),
            OnTimeDepartures=sum(OnTimeDepartures),
            TotalTrains=sum(TrainCount)) %>%
  mutate(OnTimeArrivalPercent=OnTimeArrivals/TotalTrains*100,
         OnTimeDeparturePercent=OnTimeDepartures/TotalTrains*100)

# specify formatting
columnFormats=list(NULL, list(big.mark=","), list(big.mark=","), list(big.mark=","), "%.1f", "%.1f")

# generate the table
tbl <- qtbl(tocsummary, firstColumnAsRowHeaders=TRUE,
            explicitColumnHeaders=c("TOC", "On-Time Arrivals", "On-Time Departures",
                                    "Total Trains", "On-Time Arrival %", "On-Time Departure %"),
            columnFormats=columnFormats)

# get the values for the totals row
values <- list("All TOC", totalsummary[1, ]$OnTimeArrivals, totalsummary[1, ]$OnTimeDepartures,
               totalsummary[1, ]$TotalTrains, totalsummary[1, ]$OnTimeArrivalPercent,
               totalsummary[1, ]$OnTimeDeparturePercent)

# add the totals row
tbl$cells$setRow(6, cellTypes=c("rowHeader", "total", "total", "total", "total", "total"),
                 rawValues=values, formats=columnFormats)

# render the table
tbl$renderTable()

For more information and examples regarding manipulating the structure and content of tables see the Working with Cells vignette.

Outputs

The

Further Reading

The full set of vignettes is:

  1. Introduction
  2. Working with Cells
  3. Outputs
  4. Styling
  5. Finding and Formatting
  6. Shiny
  7. Excel Export