---
title: "05. Finding and Formatting"
author: "Chris Bailiss"
date: "`r Sys.Date()`"
output: rmarkdown::html_vignette
vignette: >
  %\VignetteIndexEntry{05. Finding and Formatting}
  %\VignetteEngine{knitr::rmarkdown}
  %\VignetteEncoding{UTF-8}
---

## In This Vignette

* Finding and Formatting
* Example Table
* Getting Cells by Row and/or Column Numbers 
* Finding Cells
* Conditional Formatting
* Further Reading

## Finding and Formatting

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.

## Example Table

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

```{r, message=FALSE, warning=FALSE}
# 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()
```

## Getting Cells By Row and/or Column Numbers

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".
    + "simple" specifies that row and column arguments are considered separately (logical OR), e.g. `rowNumbers=1` and `columnNumbers=2` will match all cells in row 1 and all cells in column 2.
    + "combinations" specifies that row and column arguments are considered together (logical AND), e.g. `rowNumbers=1` and `columnNumbers=2` will match only the cell single at location (1, 2).
    + Arguments `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.

### Getting cells when `specifyCellsAsList=TRUE`

To get cells when `specifyCellsAsList=TRUE`:

- Get one or more rows by specifying the row numbers as a vector as the `rowNumbers` argument and leaving the `columnNumbers` argument set to the default value of NULL, or
- Get one or more columns by specifying the column numbers as a vector as the `columnNumbers` argument and leaving the `rowNumbers` argument set to the default value of NULL, or
- Get one or more individual cells by specifying the `cellCoordinates` 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.

#### Retrieving whole rows of cells when `specifyCellsAsList=TRUE`

Retrieving the first and third rows:

```{r, message=FALSE, warning=FALSE}
# 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()
```

#### Retrieving whole columns of cells when `specifyCellsAsList=TRUE`

Retrieving the second column:

```{r, message=FALSE, warning=FALSE}
# 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()
```

#### Retrieving specific cells when `specifyCellsAsList=TRUE`

Retrieving the raw/formatted values of the cell in the third column on the second row:

```{r, message=FALSE, warning=FALSE}
# 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, ".")
tbl$renderTable()
```

Retrieving multiple cells (2nd row-3rd column, 3rd row-4th column and 5th row-6th column):

```{r, message=FALSE, warning=FALSE}
# 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()
```

#### Retrieving a mixture of rows, columns and cells when `specifyCellsAsList=TRUE`

Retrieving the 2nd row, 4th column and 5th row-6th column cell:

```{r, message=FALSE, warning=FALSE}
# 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()
```

### Getting cells when `specifyCellsAsList=FALSE`

To get cells when `specifyCellsAsList=FALSE`:

- Get one or more rows by specifying the row numbers as a vector as the `rowNumbers` argument and leaving the `columnNumbers` argument set to the default value of NULL, or
- Get one or more columns by specifying the column numbers as a vector as the `columnNumbers` argument and leaving the `rowNumbers` argument set to the default value of NULL, or
- Get one or more cells by specifying the row and column numbers as vectors for the `rowNumbers` and `columnNumbers` arguments, or
- a mixture of the above, where for entire rows/columns the element in the other vector is set to `NA`, 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.

#### Retrieving whole rows of cells when `specifyCellsAsList=FALSE`

When retrieving just rows, the rowNumbers argument is specified the same irrespective of whether `specifyCellsAsList` is `TRUE` or `FALSE`.

Retrieving the first and third rows:

```{r, message=FALSE, warning=FALSE}
# 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()
```

#### Retrieving whole columns of cells when `specifyCellsAsList=FALSE`

When retrieving just columns, the columnNumbers argument is specified the same irrespective of whether `specifyCellsAsList` is `TRUE` or `FALSE`.

Retrieving the second column:

```{r, message=FALSE, warning=FALSE}
# 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()
```

#### Retrieving specific cells when `specifyCellsAsList=FALSE`

When 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:

```{r, message=FALSE, warning=FALSE}
# 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, ".")
tbl$renderTable()
```

#### Retrieving multiple cells (2nd row-3rd column, 3rd row-4th column and 5th row-6th column):

```{r, message=FALSE, warning=FALSE}
# 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()
```

#### Retrieving a mixture of rows, columns and cells when `specifyCellsAsList=FALSE`

When 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:

```{r, message=FALSE, warning=FALSE}
# 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()
```

## Finding Cells

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:
    + a specific value, e.g. 12.
    + a specific value equality condition, e.g. `"v==12"`, where v represents the cell value.
    + a value range expression using the following abbreviated form:  "value1<=v<value2", e.g. `"10<=v<15"`.  
        - Only "<" or "<=" can be used in these abbreviated value range expressions.
    +  a standard R logical expression, e.g. `"10<=v && v<15"`.
        - Basic R functions that test the value can also be used, e.g. `"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".
    + "simple" specifies that row and column arguments are considered separately (logical OR), e.g. `rowNumbers=1` and `columnNumbers=2` will match all cells in row 1 and all cells in column 2.
    + "combinations" specifies that row and column arguments are considered together (logical AND), e.g. `rowNumbers=1` and `columnNumbers=2` will match only the cell single at location (1, 2).
    + Arguments `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.

## Conditional Formatting

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%:

```{r, message=FALSE, warning=FALSE}
# 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:

- 0% to 40% - red
- 40% to 60% - yellow
- 60% to 100% - green

```{r, message=FALSE, warning=FALSE}
# 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:

```{r, message=FALSE, warning=FALSE}
# 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()
```

## Further Reading

The full set of vignettes is:

1. [Introduction](v01-introduction.html)
2. [Working with Cells](v02-workingwithcells.html)
3. [Outputs](v03-outputs.html)
4. [Styling](v04-styling.html)
5. [Finding and Formatting](v05-findingandformatting.html)
6. [Shiny](v06-shiny.html)
7. [Excel Export](v07-excelexport.html)