01. Introduction

Chris Bailiss

2021-06-26

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)
Status A A A A
TOC Arriva Trains Wales Arriva Trains Wales Arriva Trains Wales Arriva Trains Wales
TrainCategory Express Passenger Express Passenger Express Passenger Express Passenger
PowerType DMU DMU DMU DMU
SchedSpeedMPH 75 75 75 75
GbttWeekDate 2016-11-27 2016-12-04 2016-12-11 2016-12-18
GbttMonth 2016-12-01 2016-12-01 2016-12-01 2016-12-01
Origin CRE CRE CRE CRE
Destination BHI BHI BHI BHI
TrainCount 2 5 4 5
OnTimeArrivals 0 0 0 1
OnTimeDepartures 0 2 0 1
TotalArrivalDelayMinutes 8 mins 50 mins 27 mins 12 mins
TotalDepartureDelayMinutes 3 mins 37 mins 15 mins 7 mins

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 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 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()
a b
1 3
2 4

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

library(basictabler)
qhtbl(data.frame(a=1:2, b=3:4))
a b
1 3
2 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)
TOC On-Time Arrivals On-Time Departures Total Trains On-Time Arrival % On-Time Departure %
Arriva Trains Wales 1,404 2,348 3,909 35.9 60.1
CrossCountry 5,799 10,246 22,928 25.3 44.7
London Midland 13,036 17,184 48,279 27.0 35.6
Virgin Trains 3,289 3,864 8,594 38.3 45.0

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()
TOC On-Time Arrivals On-Time Departures Total Trains On-Time Arrival % On-Time Departure %
Arriva Trains Wales 1,404 2,348 3,909 35.9 60.1
CrossCountry 5,799 10,246 22,928 25.3 44.7
London Midland 13,036 17,184 48,279 27.0 35.6
Virgin Trains 3,289 3,864 8,594 38.3 45.0
All TOC 23,528 33,642 83,710 28.1 40.2

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

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