This vignette explains how to find parts of a table, i.e. one or more cells in the body of the table.
This is often useful to retrieve either a specific value/values, or to change the appearance of specific headings/cells - similar to the conditional formatting capabilities of many off-the-shelf tools.
The following table is used as the basis of the examples in the rest of this vignette:
# 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)
# formatting values (explained in the introduction vignette)
columnFormats=list(NULL, list(big.mark=","), list(big.mark=","), list(big.mark=","), "%.1f", "%.1f")
# create the table and render
tbl <- BasicTable$new()
tbl$addData(tocsummary, firstColumnAsRowHeaders=TRUE,
explicitColumnHeaders=c("TOC", "On-Time Arrivals", "On-Time Departures",
"Total Trains", "On-Time Arrival %", "On-Time Departure %"),
columnFormats=columnFormats)
tbl$renderTable()The getCells() function can be used to retrieve one or more cells by row/column number in the body of the table. This function has the following parameters:
specifyCellsAsList - a logical value, usage and default value described below.rowNumbers - an integer vector specifying row numbers.columnNumbers - an integer vector specifying column numbers.cellCoordinates - a list of two-element vectors that specify the coordinates of cells to retrieve. Ignored when specifyCellsAsList=FALSE.excludeEmptyCells - a logical value that specifies whether cells with no value should be excluded, default TRUE.matchMode - either “simple” (default) or “combinations”.
rowNumbers=1 and columnNumbers=2 will match all cells in row 1 and all cells in column 2.rowNumbers=1 and columnNumbers=2 will match only the cell single at location (1, 2).rowNumbers and columnNumbers are affected by the match mode. All other arguments are not.The arguments can be specified in two different ways depending on the value of the specifyCellsAsList argument. The specifyCellsAsList can have the values TRUE or FALSE, TRUE is the default value.
The getCells() function returns a list of cell objects.
specifyCellsAsList=TRUETo get cells when specifyCellsAsList=TRUE:
rowNumbers argument and leaving the columnNumbers argument set to the default value of NULL, orcolumnNumbers argument and leaving the rowNumbers argument set to the default value of NULL, orcellCoordinates argument as a list of vectors of length 2, where each element in the list is the row and column number of one cell, e.g. list(c(1, 2), c(3, 4)) specifies two cells, the first located at row 1, column 2 and the second located at row 3, column 4.Examples of the above are given below. The retrieved cells are highlighted in orange by specifying a different style.
specifyCellsAsList=TRUERetrieving the first and third rows:
# 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)
# formatting values (explained in the introduction vignette)
columnFormats=list(NULL, list(big.mark=","), list(big.mark=","), list(big.mark=","), "%.1f", "%.1f")
# create the table
tbl <- BasicTable$new()
tbl$addData(tocsummary, firstColumnAsRowHeaders=TRUE,
explicitColumnHeaders=c("TOC", "On-Time Arrivals", "On-Time Departures",
"Total Trains", "On-Time Arrival %", "On-Time Departure %"),
columnFormats=columnFormats)
# get the cells and apply styling
cells <- tbl$getCells(rowNumbers=c(1, 3))
tbl$setStyling(cells=cells, declarations=list("background-color"="#FFCC66"))
tbl$renderTable()specifyCellsAsList=TRUERetrieving the second column:
# 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)
# formatting values (explained in the introduction vignette)
columnFormats=list(NULL, list(big.mark=","), list(big.mark=","), list(big.mark=","), "%.1f", "%.1f")
# create the table
tbl <- BasicTable$new()
tbl$addData(tocsummary, firstColumnAsRowHeaders=TRUE,
explicitColumnHeaders=c("TOC", "On-Time Arrivals", "On-Time Departures",
"Total Trains", "On-Time Arrival %", "On-Time Departure %"),
columnFormats=columnFormats)
# get the cells and apply styling
cells <- tbl$getCells(columnNumbers=2)
tbl$setStyling(cells=cells, declarations=list("background-color"="#FFCC66"))
tbl$renderTable()specifyCellsAsList=TRUERetrieving the raw/formatted values of the cell in the third column on the second 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)
# formatting values (explained in the introduction vignette)
columnFormats=list(NULL, list(big.mark=","), list(big.mark=","), list(big.mark=","), "%.1f", "%.1f")
# create the table
tbl <- BasicTable$new()
tbl$addData(tocsummary, firstColumnAsRowHeaders=TRUE,
explicitColumnHeaders=c("TOC", "On-Time Arrivals", "On-Time Departures",
"Total Trains", "On-Time Arrival %", "On-Time Departure %"),
columnFormats=columnFormats)
# get the cells and apply styling
cells <- tbl$getCells(cellCoordinates=list(c(2, 3)))
tbl$setStyling(cells=cells, declarations=list("background-color"="#FFCC66"))
cat("The raw value of the cell is", cells[[1]]$rawValue, "and the formatted value is", cells[[1]]$formattedValue, ".")## The raw value of the cell is 2348 and the formatted value is 2,348 .tbl$renderTable()Retrieving multiple cells (2nd row-3rd column, 3rd row-4th column and 5th row-6th column):
# 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)
# formatting values (explained in the introduction vignette)
columnFormats=list(NULL, list(big.mark=","), list(big.mark=","), list(big.mark=","), "%.1f", "%.1f")
# create the table
tbl <- BasicTable$new()
tbl$addData(tocsummary, firstColumnAsRowHeaders=TRUE,
explicitColumnHeaders=c("TOC", "On-Time Arrivals", "On-Time Departures",
"Total Trains", "On-Time Arrival %", "On-Time Departure %"),
columnFormats=columnFormats)
# get the cells and apply styling
cells <- tbl$getCells(cellCoordinates=list(c(2, 3), c(3, 4), c(5, 6)))
tbl$setStyling(cells=cells, declarations=list("background-color"="#FFCC66"))
tbl$renderTable()specifyCellsAsList=TRUERetrieving the 2nd row, 4th column and 5th row-6th column cell:
# 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)
# formatting values (explained in the introduction vignette)
columnFormats=list(NULL, list(big.mark=","), list(big.mark=","), list(big.mark=","), "%.1f", "%.1f")
# create the table
tbl <- BasicTable$new()
tbl$addData(tocsummary, firstColumnAsRowHeaders=TRUE,
explicitColumnHeaders=c("TOC", "On-Time Arrivals", "On-Time Departures",
"Total Trains", "On-Time Arrival %", "On-Time Departure %"),
columnFormats=columnFormats)
# get the cells and apply styling
cells <- tbl$getCells(rowNumbers=2, columnNumbers=4, cellCoordinates=list(c(5, 6)))
tbl$setStyling(cells=cells, declarations=list("background-color"="#FFCC66"))
tbl$renderTable()specifyCellsAsList=FALSETo get cells when specifyCellsAsList=FALSE:
rowNumbers argument and leaving the columnNumbers argument set to the default value of NULL, orcolumnNumbers argument and leaving the rowNumbers argument set to the default value of NULL, orrowNumbers and columnNumbers arguments, orNA, e.g. to retrieve whole rows, specify the row numbers as the rowNumbers but set the corresponding elements in the columnNumbers vector to NA.Examples of the above are given below. The retrieved cells are highlighted in green by specifying a different style.
specifyCellsAsList=FALSEWhen retrieving just rows, the rowNumbers argument is specified the same irrespective of whether specifyCellsAsList is TRUE or FALSE.
Retrieving the first and third rows:
# 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)
# formatting values (explained in the introduction vignette)
columnFormats=list(NULL, list(big.mark=","), list(big.mark=","), list(big.mark=","), "%.1f", "%.1f")
# create the table
tbl <- BasicTable$new()
tbl$addData(tocsummary, firstColumnAsRowHeaders=TRUE,
explicitColumnHeaders=c("TOC", "On-Time Arrivals", "On-Time Departures",
"Total Trains", "On-Time Arrival %", "On-Time Departure %"),
columnFormats=columnFormats)
# get the cells and apply styling
cells <- tbl$getCells(specifyCellsAsList=FALSE, rowNumbers=c(1, 3))
tbl$setStyling(cells=cells, declarations=list("background-color"="#00FF00"))
tbl$renderTable()specifyCellsAsList=FALSEWhen retrieving just columns, the columnNumbers argument is specified the same irrespective of whether specifyCellsAsList is TRUE or FALSE.
Retrieving the second column:
# 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)
# formatting values (explained in the introduction vignette)
columnFormats=list(NULL, list(big.mark=","), list(big.mark=","), list(big.mark=","), "%.1f", "%.1f")
# create the table
tbl <- BasicTable$new()
tbl$addData(tocsummary, firstColumnAsRowHeaders=TRUE,
explicitColumnHeaders=c("TOC", "On-Time Arrivals", "On-Time Departures",
"Total Trains", "On-Time Arrival %", "On-Time Departure %"),
columnFormats=columnFormats)
# get the cells and apply styling
cells <- tbl$getCells(specifyCellsAsList=FALSE, columnNumbers=2)
tbl$setStyling(cells=cells, declarations=list("background-color"="#00FF00"))
tbl$renderTable()specifyCellsAsList=FALSEWhen retrieving cells, the rowNumbers and columnNumbers arguments are specified differently depending on whether specifyCellsAsList is TRUE or FALSE.
Retrieving the raw/formatted values of the cell in the third column on the second 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)
# formatting values (explained in the introduction vignette)
columnFormats=list(NULL, list(big.mark=","), list(big.mark=","), list(big.mark=","), "%.1f", "%.1f")
# create the table
tbl <- BasicTable$new()
tbl$addData(tocsummary, firstColumnAsRowHeaders=TRUE,
explicitColumnHeaders=c("TOC", "On-Time Arrivals", "On-Time Departures",
"Total Trains", "On-Time Arrival %", "On-Time Departure %"),
columnFormats=columnFormats)
# get the cells and apply styling
cells <- tbl$getCells(specifyCellsAsList=FALSE, rowNumbers=2, columnNumbers=3)
tbl$setStyling(cells=cells, declarations=list("background-color"="#00FF00"))
cat("The raw value of the cell is", cells[[1]]$rawValue, "and the formatted value is", cells[[1]]$formattedValue, ".")## The raw value of the cell is 2348 and the formatted value is 2,348 .tbl$renderTable()# 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)
# formatting values (explained in the introduction vignette)
columnFormats=list(NULL, list(big.mark=","), list(big.mark=","), list(big.mark=","), "%.1f", "%.1f")
# create the table
tbl <- BasicTable$new()
tbl$addData(tocsummary, firstColumnAsRowHeaders=TRUE,
explicitColumnHeaders=c("TOC", "On-Time Arrivals", "On-Time Departures",
"Total Trains", "On-Time Arrival %", "On-Time Departure %"),
columnFormats=columnFormats)
# get the cells and apply styling
cells <- tbl$getCells(specifyCellsAsList=FALSE, rowNumbers=c(2, 3, 5), columnNumbers=c(3, 4, 6))
tbl$setStyling(cells=cells, declarations=list("background-color"="#00FF00"))
tbl$renderTable()specifyCellsAsList=FALSEWhen retrieving cells, the rowNumbers and columnNumbers arguments are specified differently depending on whether specifyCellsAsList is TRUE or FALSE.
Retrieving the 2nd row, 4th column and 5th row-6th column cell:
# 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)
# formatting values (explained in the introduction vignette)
columnFormats=list(NULL, list(big.mark=","), list(big.mark=","), list(big.mark=","), "%.1f", "%.1f")
# create the table
tbl <- BasicTable$new()
tbl$addData(tocsummary, firstColumnAsRowHeaders=TRUE,
explicitColumnHeaders=c("TOC", "On-Time Arrivals", "On-Time Departures",
"Total Trains", "On-Time Arrival %", "On-Time Departure %"),
columnFormats=columnFormats)
# get the cells and apply styling
cells <- tbl$getCells(specifyCellsAsList=FALSE, rowNumbers=c(2, NA, 5), columnNumbers=c(NA, 4, 6))
tbl$setStyling(cells=cells, declarations=list("background-color"="#00FF00"))
tbl$renderTable()The findCells() function is used to search for cells within the body of the table matching one or more criteria. The function returns a list of cell objects. This function has the following parameters:
minValue - a numerical value specifying a minimum value threshold when matching cells.maxValue - a numerical value specifying a maximum value threshold when matching cells.exactValues - vector specifying a set of allowed values when matching cells.valueRanges - a vector specifying one or more value range expressions to use when matching cells. The expression(s) can be any of the following forms:
"v==12", where v represents the cell value."10<=v<15".
"10<=v && v<15".
"is.na(v)".includeNA - specify TRUE to include NA in the matched cells, FALSE to exclude NA values.includeNull - specify TRUE to include cells with no value, FALSE to exclude cells with no value.emptyCells - a word that specifies how empty cells are matched - must be one of:
include to match empty and non-empty cells.exclude to match only non-empty cells.only to match only empty cells.In addition, the following parameters can also be used with pt$findCells():
rowNumbers - an integer vector specifying row numbers.columnNumbers - an integer vector specifying column numbers.cellCoordinates - a list of two-element vectors that specify the coordinates of cells to retrieve. Ignored when specifyCellsAsList=FALSE.cells - a PivotCell object or a list of PivotCell objects to restrict the cells that are matched.rowColumnMatchMode - either “simple” (default) or “combinations”.
rowNumbers=1 and columnNumbers=2 will match all cells in row 1 and all cells in column 2.rowNumbers=1 and columnNumbers=2 will match only the cell single at location (1, 2).rowNumbers, columnNumbers, rowGroups and columnGroups are affected by the match mode. All other arguments are not.findCells() is typically used for conditional formatting, as illustrated in the next section.
The findCells() and getCells() functions can be used to help conditionally format a table.
For example, to highlight in red those cells in the basic example table that have a percentage value between 0% and 40%:
# 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)
# formatting values (explained in the introduction vignette)
columnFormats=list(NULL, list(big.mark=","), list(big.mark=","), list(big.mark=","), "%.1f", "%.1f")
# create the table
tbl <- BasicTable$new()
tbl$addData(tocsummary, firstColumnAsRowHeaders=TRUE,
explicitColumnHeaders=c("TOC", "On-Time Arrivals", "On-Time Departures",
"Total Trains", "On-Time Arrival %", "On-Time Departure %"),
columnFormats=columnFormats)
# apply the formatting
cells <- tbl$findCells(columnNumbers=5:6, minValue=0, maxValue=40, includeNull=FALSE, includeNA=FALSE)
tbl$setStyling(cells=cells, declarations=list("background-color"="#FFC7CE", "color"="#9C0006"))
tbl$renderTable()Extending the above example so that on-time arrival/departure percentages are coloured as follows:
# 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)
# formatting values (explained in the introduction vignette)
columnFormats=list(NULL, list(big.mark=","), list(big.mark=","), list(big.mark=","), "%.1f", "%.1f")
# create the table
tbl <- BasicTable$new()
tbl$addData(tocsummary, firstColumnAsRowHeaders=TRUE,
explicitColumnHeaders=c("TOC", "On-Time Arrivals", "On-Time Departures",
"Total Trains", "On-Time Arrival %", "On-Time Departure %"),
columnFormats=columnFormats)
# apply the red formatting
cells <- tbl$findCells(columnNumbers=5:6, minValue=0, maxValue=40, includeNull=FALSE, includeNA=FALSE)
tbl$setStyling(cells=cells, declarations=list("background-color"="#FFC7CE", "color"="#9C0006"))
# apply the yellow formatting
cells <- tbl$findCells(columnNumbers=5:6, minValue=40, maxValue=60, includeNull=FALSE, includeNA=FALSE)
tbl$setStyling(cells=cells, declarations=list("background-color"="#FFEB9C", "color"="#9C5700"))
# apply the green formatting
cells <- tbl$findCells(columnNumbers=5:6, minValue=60, maxValue=100, includeNull=FALSE, includeNA=FALSE)
tbl$setStyling(cells=cells, declarations=list("background-color"="#C6EFCE", "color"="#006100"))
tbl$renderTable()The mapStyling() function can be used to map the colours using a continuous colour range instead of fixed colours:
# 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)
# formatting values (explained in the introduction vignette)
columnFormats=list(NULL, list(big.mark=","), list(big.mark=","), list(big.mark=","), "%.1f", "%.1f")
# create the table
tbl <- BasicTable$new()
tbl$addData(tocsummary, firstColumnAsRowHeaders=TRUE,
explicitColumnHeaders=c("TOC", "On-Time Arrivals", "On-Time Departures",
"Total Trains", "On-Time Arrival %", "On-Time Departure %"),
columnFormats=columnFormats)
# apply the conditional formatting
cells <- tbl$getCells(rowNumbers=2:5, columnNumbers=5:6, matchMode="combinations")
tbl$mapStyling(cells=cells, styleProperty="background-color", valueType="color", mapType="continuous",
mappings=list(25, "#FFC7CE", 40, "#FFEB9C", 60, "#C6EFCE", 100))
tbl$mapStyling(cells=cells, styleProperty="color", valueType="color", mapType="continuous",
mappings=list(25, "#9C0006", 40, "#9C5700", 60, "#006100", 100))
# render
tbl$renderTable()The full set of vignettes is: