In cbailiss/pivottabler: Create Pivot Tables

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:

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:

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:

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.

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 vignette.

Results in Excel

Please see the Excel Export vignette.

Results as FlexTable

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

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

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

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

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.

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:

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:

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:

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:

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:

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:

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:

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:

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:

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.

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()

cbailiss/pivottabler
Create Pivot Tables

In cbailiss/pivottabler: Create Pivot Tables

In This Vignette

Results of Pivot Calculations

Example Pivot Table

Results as Plain Text

Results as HTML

Rendering a htmlwidget

Retrieving HTML

Results as Latex

Results in Excel

Results as FlexTable

Results in Word

Results in PowerPoint

Results as an R Matrix

As a Data Matrix

As a `character` matrix

Results as an R Data Frame

The `asDataFrame()` function

The `asTidyDataFrame()` function

Results as a basictabler Table

Further Reading

R Package Documentation

Browse R Packages

We want your feedback!

cbailiss/pivottabler Create Pivot Tables

In cbailiss/pivottabler: Create Pivot Tables

In This Vignette

Results of Pivot Calculations

Example Pivot Table

Results as Plain Text

Results as HTML

Rendering a htmlwidget

Retrieving HTML

Results as Latex

Results in Excel

Results as FlexTable

Results in Word

Results in PowerPoint

Results as an R Matrix

As a Data Matrix

As a character matrix

Results as an R Data Frame

The asDataFrame() function

The asTidyDataFrame() function

Results as a basictabler Table

Further Reading

R Package Documentation

Browse R Packages

We want your feedback!

cbailiss/pivottabler
Create Pivot Tables

As a `character` matrix

The `asDataFrame()` function

The `asTidyDataFrame()` function