v05-outputs.Rmd

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

## In This Vignette

* Results of Pivot Calculations
* Example Pivot Table
* Results as Plain Text
* Results as HTML
* Results as Latex
* Results in Excel
* Results as FlexTable
* Results in Word
* Results in PowerPoint
* Results as an R Matrix
* Results as an R Data Frame
* Results as a basictabler Table
* Further Reading

## Results of Pivot Calculations

A `pivottabler` pivot table object has a fairly complex internal structure - containing two trees of data groups (the row groups and the column groups) plus a set of cells linked to the data groups.

The `pivottabler` package supports outputting a pivot table in a number of different forms:

- A htmlwidget for R-Studio - using `pt$renderPivot()` to render the pivot table into the "Viewer" tab in R-Studio,
- A htmlwidget for Shiny applications - using `pivottabler(pt)` to render the pivot table into the Shiny app,
- As HTML - using either:
    + `pt$getHtml()` to retrieve a character variable containing HTML, or
    + `pt$saveHtml()` to save the HTML to a file.
- As Latex - using `pt$getLatex()` to retrieve a character variable containing Latex.
- As plain text - using `pt` to output to the console or `pt$asCharacter` to retrieve as a character value.
- Into an Excel Worksheet.

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

## Example Pivot Table

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

```{r, message=FALSE, warning=FALSE}
library(pivottabler)
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$addColumnDataGroups("PowerType")
pt$addRowDataGroups("TOC")
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
pt$renderPivot()
```

## Results as Plain Text

A pivot table is outputted to the console as plain text simply by using `pt`:

```{r, message=FALSE, warning=FALSE, comment=""}
library(pivottabler)
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$addColumnDataGroups("PowerType")
pt$addRowDataGroups("TOC")
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
pt$evaluatePivot()
pt
```

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

`pt` and `pt$asString` show the current state of the pivot table.  If the pivot table has not been evaluated (either by using `pt$evaluatePivot()` or `pt$renderPivot()`) then `pt` and `pt$asCharacter` will return the headings only:

```{r, message=FALSE, warning=FALSE, comment=""}
library(pivottabler)
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$addColumnDataGroups("PowerType")
pt$addRowDataGroups("TOC")
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
pt
```

## Results as HTML

### Rendering a htmlwidget

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

For outputting as a htmlwidget in a Shiny application, use `pivottabler(pt)`.

### Retrieving HTML

To retrieve the HTML of a pivot table, use `pt$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 pivot table can be retrieved using `pt$getCss()` - also illustrated below.

```{r, message=FALSE, warning=FALSE, comment=""}
library(pivottabler)
library(htmltools)
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$addColumnDataGroups("PowerType")
pt$addRowDataGroups("TOC")
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
pt$evaluatePivot()
cat(paste(pt$getHtml(), sep="", collapse="\n"))
cat(pt$getCss())
```

## Results as Latex

Please see the [Latex Output](v06-latexoutput.html) vignette.

## Results in Excel

Please see the [Excel Export](v13-excelexport.html) vignette.

## Results as FlexTable

Converting a pivot table to a table from the `flextabler` package is possible:

```{r, message=FALSE, warning=FALSE, eval=TRUE, comment=""}
# construct the table
library(pivottabler)
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$addColumnDataGroups("PowerType")
pt$addRowDataGroups("TOC")
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
pt$evaluatePivot()

# convert to a basictabler table
library(basictabler)
tbl <- pt$asBasicTable()

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

## Results in Word

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

```{r, eval=FALSE}
# construct the table
library(pivottabler)
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$addColumnDataGroups("PowerType")
pt$addRowDataGroups("TOC")
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
pt$evaluatePivot()

# convert to a basictabler table
library(basictabler)
tbl <- pt$asBasicTable()

# 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")
```

## Results in PowerPoint

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

```{r, eval=FALSE}
# construct the table
library(pivottabler)
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$addColumnDataGroups("PowerType")
pt$addRowDataGroups("TOC")
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
pt$evaluatePivot()

# convert to a basictabler table
library(basictabler)
tbl <- pt$asBasicTable()

# 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")
```

## Results as an R Matrix

### As a Data Matrix

Converting a pivot table to a matrix is possible.  The row/column headers become the row/column names in the matrix:

```{r, message=FALSE, warning=FALSE, eval=TRUE, comment=""}
library(pivottabler)
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$addColumnDataGroups("PowerType")
pt$addRowDataGroups("TOC")
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
pt$evaluatePivot()
pt$asDataMatrix()
```

If only the cell values are required, the headings can be removed from the matrix by setting the `includeHeaders` parameter to `FALSE`.

By default, `asDataMatrix()` populates the matrix with the raw cell values.  Setting the `rawValue` parameter to `FALSE` specifies that the matrix should contain the formatted `character` values instead of the raw values.

```{r, message=FALSE, warning=FALSE, eval=TRUE, comment=""}
library(pivottabler)
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$addColumnDataGroups("PowerType")
pt$addRowDataGroups("TOC")
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
pt$evaluatePivot()
pt$asDataMatrix(rawValue=FALSE)
```

When there are multiple levels of headers, headers are concatenated.  A separator can be specified:

```{r, message=FALSE, warning=FALSE, comment=""}
library(dplyr)
library(pivottabler)
data <- filter(bhmtrains, (Status=="A")|(Status=="C"))
pt <- PivotTable$new()
pt$addData(data)
pt$addColumnDataGroups("PowerType", addTotal=FALSE)
pt$addColumnDataGroups("Status", addTotal=FALSE)
pt$addRowDataGroups("TOC")
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
pt$renderPivot()
pt$asDataMatrix(separator="|")
```

### As a `character` matrix

It is also possible to convert a pivot table to a `character` matrix, where the row/column names are within the body of the matrix:

```{r, message=FALSE, warning=FALSE, eval=TRUE, comment=""}
library(pivottabler)
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$addColumnDataGroups("PowerType")
pt$addRowDataGroups("TOC")
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
pt$evaluatePivot()
pt$asMatrix()
```

If only the cell values are required, the headings can be removed from the matrix by setting the `includeHeaders` parameter to `FALSE`.

When there are multiple levels of headers, by default the column headers are not repeated:

```{r, message=FALSE, warning=FALSE, comment=""}
library(dplyr)
library(pivottabler)
data <- filter(bhmtrains, (Status=="A")|(Status=="C"))
pt <- PivotTable$new()
pt$addData(data)
pt$addColumnDataGroups("PowerType", addTotal=FALSE)
pt$addColumnDataGroups("Status", addTotal=FALSE)
pt$addRowDataGroups("TOC")
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
pt$renderPivot()
pt$asMatrix()
```

However, the `repeatHeaders` parameter can be used to specify repeating headings:

```{r, message=FALSE, warning=FALSE, comment=""}
pt$asMatrix(repeatHeaders=TRUE)
```

## Results as an R Data Frame

Two different functions can be used to convert a pivot table to a data frame.  The `asDataFrame()` function returns a data frame with a roughly similar layout to the pivot table, e.g. a pivot table with a body consisting of 10 rows and 2 columns will result in a data frame also containing 10 rows and 2 columns.  The `asTidyDataFrame()` function returns a data frame consisting of one row for every cell in the body of the pivot table, e.g. a pivot table with a body consisting of 10 rows and 2 columns will result in a data frame containing 20 rows.

Examples of both functions are given below.

### The `asDataFrame()` function

The example pivot table converts as follows:

```{r, message=FALSE, warning=FALSE, eval=TRUE, comment=""}
library(pivottabler)
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$addColumnDataGroups("PowerType")
pt$addRowDataGroups("TOC")
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
pt$evaluatePivot()
df <- pt$asDataFrame()
df
str(df)
```

Data frames can have at most one name for each row and column.  Therefore, when there are multiple levels of headers in the pivot table, the captions are concatenated into a single value for each row and column:

```{r, message=FALSE, warning=FALSE, comment=""}
library(pivottabler)
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$addColumnDataGroups("TrainCategory")
pt$addColumnDataGroups("PowerType")
pt$addRowDataGroups("TOC")
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
pt$evaluatePivot()
pt$asDataFrame()
```

The space character is the default character used to combine headers as seen above.  This can easily be changed, e.g. to a pipe character:

```{r, message=FALSE, warning=FALSE, comment=""}
library(pivottabler)
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$addColumnDataGroups("TrainCategory")
pt$addColumnDataGroups("PowerType")
pt$addRowDataGroups("TOC")
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
pt$evaluatePivot()
pt$asDataFrame(separator="|")
```

In addition, the row group headings can be exported as separate columns:

```{r, message=FALSE, warning=FALSE, comment=""}
library(pivottabler)
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$addColumnDataGroups("TrainCategory")
pt$addRowDataGroups("TOC")
pt$addRowDataGroups("PowerType")
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
pt$evaluatePivot()
pt$asDataFrame(rowGroupsAsColumns=TRUE)
```

### The `asTidyDataFrame()` function

The example pivot table converts as follows:

```{r, message=FALSE, warning=FALSE, eval=TRUE, comment=""}
library(pivottabler)
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$addColumnDataGroups("PowerType")
pt$addRowDataGroups("TOC")
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
pt$evaluatePivot()
pt$asDataFrame()
df <- pt$asTidyDataFrame()
str(df)
head(df)
```

By default the generated pivot table contains columns for both the captions of the data groups and the variables/values that the data groups represent.  Each of these sets of columns can be removed from the data frame by setting `includeGroupCaptions=FALSE` or `includeGroupValues=FALSE` respectively.

Where a data group represents multiple values, those values are concatenated and returned in a single column in the data frame.  Again, the separator between the values can be changed, e.g. by specifying `separator="|"`.

## Results as a basictabler Table

The `asBasicTable()` function allows a pivot table to be converted to a basic table - from the `basictabler` package.  

The `basictabler` package allows free-form tables to be constructed, in contrast to `pivottabler` which creates pivot tables with relatively fixed structures.  `pivottabler` contains calculation logic - to calculate the values of cells within the pivot table.  `basictabler` contains no calculation logic - cell values must be provided either from a data frame, row-by-row, column-by-column or cell-by-cell.

Converting a pivot table to a basic table allows the structure of pivot tables to be altered after they have been created, e.g.

```{r, message=FALSE, warning=FALSE, eval=TRUE, comment=""}
library(pivottabler)
library(dplyr)
library(lubridate)
trains <- mutate(bhmtrains, 
                 GbttDate=if_else(is.na(GbttArrival), GbttDeparture, GbttArrival),
                 GbttMonth=make_date(year=year(GbttDate), month=month(GbttDate), day=1))

pt <- PivotTable$new()
pt$addData(trains)
pt$addColumnDataGroups("GbttMonth", dataFormat=list(format="%B %Y"))
pt$addColumnDataGroups("PowerType")
pt$addRowDataGroups("TOC")
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
pt$evaluatePivot()

# convert the pivot table to a basic table, insert a new row, merge cells and highlight
bt <- pt$asBasicTable()
bt$cells$insertRow(5)
bt$cells$setCell(5, 2, rawValue="The values below are significantly higher than expected.", 
                 styleDeclarations=list("text-align"="left", "background-color"="yellow",
                                        "font-weight"="bold", "font-style"="italic"))
bt$mergeCells(rFrom=5, cFrom=2, rSpan=1, cSpan=13)
bt$setStyling(rFrom=6, cFrom=2, rTo=6, cTo=14, 
              declarations=list("text-align"="left", "background-color"="yellow"))
bt$renderTable()
```

## Further Reading

The full set of vignettes is:

1. [Introduction](v01-introduction.html)
2. [Data Groups](v02-datagroups.html)
3. [Calculations](v03-calculations.html)
4. [Regular Layout](v04-regularlayout.html)
5. [Outputs](v05-outputs.html)
6. [Latex Output](v06-latexoutput.html)
7. [Styling](v07-styling.html)
8. [Finding and Formatting](v08-findingandformatting.html)
9. [Cell Context](v09-cellcontext.html)
10. [Navigating a Pivot Table](v10-navigatingapivottable.html)
11. [Irregular Layout](v11-irregularlayout.html)
12. [Performance](v12-performance.html)
13. [Excel Export](v13-excelexport.html)
14. [Shiny](v14-shiny.html)
15. [Appendix: Details](vA1-appendix.html)
16. [Appendix: Calculations](vA2-appendix.html)
17. [Appendix: Class Overview](vA3-appendix.html)