Create regular pivot tables with just a few lines of R. More complex pivot tables can also be created, e.g. pivot tables with irregular layouts, multiple calculations and/or derived calculations based on multiple data frames. Pivot tables are constructed using R only and can be written to a range of output formats (plain text, 'HTML', 'Latex' and 'Excel'), including with styling/formatting.
The pivottabler
package enables pivot tables to be created with just a few lines of R.
The pivottabler
package aims to:
All calculations for the pivot tables take place inside R, enabling the use of a wide-range of R functions in the calculation logic.
Pivot tables are rendered as htmlwidgets, Latex or plain text. The HTML/Latex/text can be exported for use outside of R.
Pivot tables can be converted to a standard R matrix or data frame. Pivot tables can be exported to Excel. Pivot tables can also be converted to a basictabler
table for further manipulation.
pivottabler
is a companion package to the basictabler
package. pivottabler
is focussed on generating pivot tables and can aggregate data. basictabler
does not aggregate data but offers more control of table structure.
You can install:
install.packages("pivottabler")
devtools::install_github("cbailiss/pivottabler", build_vignettes = TRUE)
pivottabler
has many styling and formatting capabilities when rendering pivot tables in HTML / as htmlwidgets using pt$renderPivot()
, however the most basic output is simply as plain text.
A simple example of creating a pivot table - summarising the types of trains run by different train companies:
library(pivottabler)# arguments: qpvt(dataFrame, rows, columns, calculations, ...)qpvt(bhmtrains, "TOC", "TrainCategory", "n()") # TOC = Train Operating Company
Express Passenger Ordinary Passenger Total
Arriva Trains Wales 3079 830 3909
CrossCountry 22865 63 22928
London Midland 14487 33792 48279
Virgin Trains 8594 8594
Total 49025 34685 83710
pivottabler
also offers a more verbose syntax that is more self-describing and offers additional options that aren't available with the quick-pivot functions. The equivalent verbose commands to output the same pivot table as above are:
library(pivottabler)pt <- PivotTable$new()pt$addData(bhmtrains) # bhmtrains is a data frame with columns TrainCategory, TOC, etc.pt$addColumnDataGroups("TrainCategory") # e.g. Express Passengerpt$addRowDataGroups("TOC") # TOC = Train Operating Company e.g. Arriva Trains Walespt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")pt$evaluatePivot()pt
Multiple levels can be added to the pivot table row or column headings, e.g. looking at combinations of TOC and PowerType:
library(pivottabler)qpvt(bhmtrains, c("TOC", "PowerType"), "TrainCategory", "n()")
library(pivottabler)pt <- PivotTable$new()pt$addData(bhmtrains)pt$addColumnDataGroups("TrainCategory")pt$addRowDataGroups("TOC")pt$addRowDataGroups("PowerType") # D/EMU = Diesel/Electric Multiple Unit, HST=High Speed Trainpt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")pt$evaluatePivot()pt
Express Passenger Ordinary Passenger Total
Arriva Trains Wales DMU 3079 830 3909
Total 3079 830 3909
CrossCountry DMU 22133 63 22196
HST 732 732
Total 22865 63 22928
London Midland DMU 5638 5591 11229
EMU 8849 28201 37050
Total 14487 33792 48279
Virgin Trains DMU 2137 2137
EMU 6457 6457
Total 8594 8594
Total 49025 34685 83710
The HTML rendering of the same two pivot tables shown above (each constructed using both a quick-pivot function and verbose syntax) is:
library(pivottabler)qhpvt(bhmtrains, "TOC", "TrainCategory", "n()")
library(pivottabler)pt <- PivotTable$new()pt$addData(bhmtrains)pt$addColumnDataGroups("TrainCategory")pt$addRowDataGroups("TOC")pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")pt$renderPivot()
library(pivottabler)qhpvt(bhmtrains, c("TOC", "PowerType"), "TrainCategory", "n()")
library(pivottabler)pt <- PivotTable$new()pt$addData(bhmtrains) # bhmtrains is a data frame with columns TrainCategory, TOC, etc.pt$addColumnDataGroups("TrainCategory") # e.g. Express Passengerpt$addRowDataGroups("TOC") # TOC = Train Operating Company e.g. Arriva Trains Walespt$addRowDataGroups("PowerType") # D/EMU = Diesel/Electric Multiple Unit, HST=High Speed Trainpt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")pt$renderPivot()
Multiple calculations are supported. Calculations can be based on other calculations in the pivot table. Calculations can be hidden - e.g. to hide calculations that only exist to provide values to other calculations.
For example, looking at the total number of trains and the percentage of trains that arrive more than five minutes late for combinations of train operating company (TOC) and train category:
library(pivottabler)library(dplyr)library(lubridate)# derive train delay datatrains <- mutate(bhmtrains,ArrivalDelta=difftime(ActualArrival, GbttArrival, units="mins"),ArrivalDelay=ifelse(ArrivalDelta<0, 0, ArrivalDelta),DelayedByMoreThan5Minutes=ifelse(ArrivalDelay>=5,1,0))# create the pivot tablept <- PivotTable$new()pt$addData(trains)pt$addRowDataGroups("TOC", totalCaption="All TOCs")pt$addColumnDataGroups("TrainCategory", totalCaption="All Trains")pt$defineCalculation(calculationName="TotalTrains", caption="Train Count",summariseExpression="n()")pt$defineCalculation(calculationName="DelayedTrains", caption="Trains Arr. 5+ Mins Late",summariseExpression="sum(DelayedByMoreThan5Minutes, na.rm=TRUE)",visible=FALSE)pt$defineCalculation(calculationName="DelayedPercent", caption="% Late Trains",type="calculation", basedOn=c("DelayedTrains", "TotalTrains"),format="%.1f %%",calculationExpression="values$DelayedTrains/values$TotalTrains*100")pt$renderPivot()
It is also possible to change the axis (rows or columns) and level in which the calculations appear. See the "Calculations" vignette for details.
More advanced calculations such as % of row total, cumulative sums, etc are possible. See the "A2. Appendix: Calculations" vignette for details.
Styling can be specified when creating the pivot table. The example below shows specifying styling using a quick-pivot function and using the more verbose syntax.
library(pivottabler)qhpvt(bhmtrains, "TOC", "TrainCategory", "n()",tableStyle=list("border-color"="maroon"),headingStyle=list("color"="cornsilk", "background-color"="maroon","font-style"="italic", "border-color"="maroon"),cellStyle=list("color"="maroon", "background-color"="cornsilk","border-color"="maroon"),totalStyle=list("color"="maroon", "background-color"="cornsilk","border-color"="maroon", "font-weight"="bold"))
library(pivottabler)pt <- PivotTable$new(tableStyle=list("border-color"="maroon"),headingStyle=list("color"="cornsilk", "background-color"="maroon","font-style"="italic", "border-color"="maroon"),cellStyle=list("color"="maroon", "background-color"="cornsilk","border-color"="maroon"),totalStyle=list("color"="maroon", "background-color"="cornsilk","border-color"="maroon", "font-weight"="bold"))pt$addData(bhmtrains)pt$addColumnDataGroups("TrainCategory")pt$addRowDataGroups("TOC")pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")pt$renderPivot()
It is also possible to change the styling of single cells and ranges of cells after the pivot table has been created. See the "Styling" and "Finding and Formatting" vignettes for more details.
The same styling/formatting used for the HTML output is also used when outputting to Excel - greatly reducing the amount of script that needs to be written to create Excel output.
library(pivottabler)pt <- PivotTable$new()pt$addData(bhmtrains) # bhmtrains is a data frame with columns TrainCategory, TOC, etc.pt$addColumnDataGroups("TrainCategory") # e.g. Express Passengerpt$addRowDataGroups("TOC") # TOC = Train Operating Company e.g. Arriva Trains Walespt$addRowDataGroups("PowerType") # D/EMU = Diesel/Electric Multiple Unit, HST=High Speed Trainpt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")pt$evaluatePivot()library(openxlsx)wb <- createWorkbook(creator = Sys.getenv("USERNAME"))addWorksheet(wb, "Data")pt$writeToExcelWorksheet(wb=wb, wsName="Data",topRowNumber=2, leftMostColumnNumber=2, applyStyles=TRUE)saveWorkbook(wb, file="C:\\test.xlsx", overwrite = TRUE)
In the screenshot above, Gridlines have been made invisible to make the styling easier to see (by clearing the checkbox on the 'View' ribbon). Columns were also auto-sized - though the widths of columns could also be manually specified from R. See the Excel Export vignette for more details.
More complex pivot tables can also be created, e.g. with irregular layouts, using multiple data frames, using multiple calculations and/or custom R calculation functions. See the package vignettes for more details:
# to see a list of available package vignettes:vignette(package="pivottabler")# to open a specific vignettevignette(topic="v01-introduction", package="pivottabler")
The vignettes can also be read on CRAN at: https://cran.r-project.org/package=pivottabler
The following are a few of the example pivot tables constructed in the package vignettes (click to open full sized picture):
This release includes:
This version of pivottabler
generates slightly different CSS/HTML for the built-in themes/styling compared to previous versions. The visual appearance is unchanged. This may be a breaking change for users who require the generated CSS/HTML code to be identical to previous versions.
More details:
In version 1.0.0 and earlier versions of pivottabler
, the built-in themes used a shared set of style declarations for both calculation value cells and total cells. From pivottabler
version 1.1.0 onwards, total cells use a separate set of style declarations. The visual appearance of pivot tables using the built-in themes has not changed, only the HTML/CSS that is generated is slightly different - so the great majority of users will not be affected.
This change reduces the risk of styling changes to totals accidentally affecting all calculation value cells and vice-versa.
The output of earlier versions, where total cells and calculation value cells use a shared set of style declarations, can be generated by specifying compatibility=list(totalStyleIsCellStyle=TRUE)
as an argument when creating the pivot table, either in PivotTable$new()
or one of the quick pivot functions such as qpvt()
.
basictabler
table - enabling flexible/arbitrary changes to be made to pivot tables after they have been created, e.g. inserting or deleting rows/columns/cells. See the "Outputs" vignette for more details.pt$setStyling()
simplifies the setting of formatting and styling on data groups and table cells. See the "Styling" vignette for details.qpvt()
and qhpvt()
functions is now possible. See the "Introduction" vignette for a list of parameters for these functions. See the "Styling" vignette for more examples.specifyCellsAsList
argument in the pt$getCells()
function has been changed to TRUE
.
The previous usage of the pt$getCells()
function is still supported (now you must explicitly specify specifyCellsAsList=FALSE
). This change has been planned since v0.3.0 (June 2017) and a warning message has been displayed since then. See the Finding and Formatting vignette for more details on the specifyCellsAsList
argument.pt$addColumnCalculationGroups()
or pt$addRowCalculationGroups()
(previously this would silently fail).PivotFiltersOverrides
class provides many new options for overriding the data used to calculate cell values. It is now possible to add to, remove from or entirely replace filter criteria as part of calculation definitions. This makes calculations such as "% of row/column/grand total", ratios/multiples, rolling averages and cumulative sums easier. See the Calculations Appendix (A2) vignette for examples.baseStyleName
and styleDeclarations
arguments in pt$addColumnDataGroups(...)
and pt$addRowDataGroups(...)
. See the Styling vignette for an example.headingBaseStyleName
and headingStyleDeclarations
arguments in pt$defineCalculation(...)
. See the Styling vignette for an example.cellBaseStyleName
and cellStyleDeclarations
arguments in pt$defineCalculation(...)
. See the Styling vignette for an example.exportOptions
parameter when exporting to HTML, Latex and Excel for controlling how NA, NaN, -Inf and Inf are exported. See the Details Appendix (A1) for more information.outputHeadingsAs
in pt$writeToExcelWorksheet(...)
to control how row/column headings are formatted when exporting to Excel. See the Excel Export vignette for more details.pt$asDataFrame(...)
and pt$asTidyDataFrame(...)
now support additional parameter stringsAsFactors
with default value default.stringsAsFactors()
.No breaking changes currently planned.
pt
or retrieve the plain text as a character value using pt$asCharacter
.qpvt()
, qhpvt()
and qlpvt()
. See the Introduction vignette for more details.(none)
Initial version.
No versions prior to 0.1.0 were released.