---
title: "04. Outputs"
author: "Chris Bailiss"
date: "`r Sys.Date()`"
output: rmarkdown::html_vignette
vignette: >
  %\VignetteIndexEntry{04. 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 an R Matrix
* Results as an R Data Frame
* 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 outputing 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](v05-latexoutput.html) vignette.

## Results in Excel

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

## Results as an R Matrix

Converting a pivot table to a matrix can be accomplished 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$asMatrix()
```

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

The `rawValue` parameter specifies that the matrix should contain the numerical result values, not the formatted 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$asMatrix(includeHeaders=FALSE, rawValue=TRUE)
```

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

```{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$renderPivot()
pt$asMatrix()
```

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

```{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$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="|")
```

### 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="|"`. 

## 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. [Outputs](v04-outputs.html)
5. [Latex Output](v05-latexoutput.html)
6. [Styling](v06-styling.html)
7. [Finding and Formatting](v07-findingandformatting.html)
8. [Cell Context](v08-cellcontext.html)
9. [Irregular Layout](v09-irregularlayout.html)
10. [Performance](v10-performance.html)
11. [Shiny](v11-shiny.html)
12. [Excel Export](v12-excelexport.html)
