45

pivottabler

Create Pivot Tables in R

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.

pivottabler

Build Status CRAN_Status_Badge

The pivottabler package enables pivot tables to be created with just a few lines of R.

The pivottabler package aims to:

  • Provide an easy way of creating pivot tables, without requiring the user to specify low-level layout logic.
  • Provide multiple ways of specifying calculation logic to cover both simple and more sophisticated requirements.
  • Provide styling options so the pivot tables can be themed/branded as needed.

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 also be exported to Excel.

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:

  • the latest released version from CRAN with
install.packages("pivottabler")
  • the latest development version from github with
devtools::install_github("cbailiss/pivottabler", build_vignettes = TRUE)

Example

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.

Plain Text Output

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 Passenger
pt$addRowDataGroups("TOC") # TOC = Train Operating Company e.g. Arriva Trains Wales
pt$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 Train
pt$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

HTML Output

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

http://cbailiss.me.uk/pivottablerreadmeimgs/example1.png

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 Passenger
pt$addRowDataGroups("TOC") # TOC = Train Operating Company e.g. Arriva Trains Wales
pt$addRowDataGroups("PowerType") # D/EMU = Diesel/Electric Multiple Unit, HST=High Speed Train
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
pt$renderPivot()

http://cbailiss.me.uk/pivottablerreadmeimgs/example2.png

Excel Output

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 Passenger
pt$addRowDataGroups("TOC") # TOC = Train Operating Company e.g. Arriva Trains Wales
pt$addRowDataGroups("PowerType") # D/EMU = Diesel/Electric Multiple Unit, HST=High Speed Train
pt$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)

http://cbailiss.me.uk/pivottablerreadmeimgs/example4.png

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 Information

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 vignette
vignette(topic="v01-introduction", package="pivottabler")

The vignettes can also be read on CRAN at: https://cran.r-project.org/package=pivottabler

More Examples

The following are a few of the example pivot tables constructed in the package vignettes (click to open full sized picture):

http://cbailiss.me.uk/pivottablerreadmeimgs/example3.png

News

pivottabler 1.0.0

Breaking Changes

  • The default value of the 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.
  • Additional checks are now made to prevent calculations being moved/added after the calculation row/column groups have been generated. There is a small chance this will cause errors in existing user code - though this will only occur where user code is trying to move/add calculations after the calculations have already been set in the pivot table by a call to pt$addColumnCalculationGroups() or pt$addRowCalculationGroups() (previously this would silently fail).

Improvements

  • pivottabler now supports using any of the following data types in both row/column headings and cell values: integer, numeric, character, logical, Date, and POSIXct.
  • Improved support for illegal data frame column names and illegal calculation names (e.g. including spaces or symbols such as dash, plus, dollar, etc). See the Details Appendix (A1) vignette for details.
  • The new 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.
  • Row/column heading style settings for data groups can now be declared up-front using the baseStyleName and styleDeclarations arguments in pt$addColumnDataGroups(...) and pt$addRowDataGroups(...). See the Styling vignette for an example.
  • Row/column heading style settings for calculations can now be declared up-front using the headingBaseStyleName and headingStyleDeclarations arguments in pt$defineCalculation(...). See the Styling vignette for an example.
  • Cell style settings for calculations can now be declared up-front using the cellBaseStyleName and cellStyleDeclarations arguments in pt$defineCalculation(...). See the Styling vignette for an example.
  • Additional 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.
  • Additional parameter 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().

Bug Fixes

  • Bug fixed that would cause corrupt Excel files to be generated when exporting pivottables with no row/column groups to Excel.

Upcoming Changes

No breaking changes currently planned.

pivottabler 0.4.0

Breaking Changes

  • Removed support for R 3.2.x. Minimum supported version of base R now R 3.3.0.

Improvements

  • It is now possible to output a pivot table to an Excel file with one line of R, including with styling that closely matches the HTML output. See the Excel Export vignette for more details.
  • Quick-pivot functions now support showing/hiding totals and renaming the captions of totals, which was previously only possible using the verbose syntax. See the Introduction vignette for more details.

Bug Fixes

  • Corrections to ordering of code in Styling vignette.
  • A couple of other small bug fixes.

Upcoming Changes

  • The previous usage of the arguments for the getCells() function is still supported (and is still the default) however the new argument usage will be made the default in a future version. For now, a message is displayed noting the upcoming change. See the Finding and Formatting vignette for more details.

pivottabler 0.3.0: Performance Improvements and Quick-Pivot Functions

Breaking Changes

  • Pivot table initialiser parameters renamed from messages and messageFile to traceEnabled and traceFile respectively.

Improvements

  • pivottabler now calculates cell values in batches in order to reduce the calculation time required for larger data frames. For large pivot tables based on large data frames this typically results in a big performance improvement, e.g. for a pivot table of 1000 cells based on a data frame with 10 million rows the rendering time is around 7 seconds in version 0.3.0 compared to over 480 seconds in version 0.2.0. See the new Performance vignette for more details.
  • pivottabler now also supports the data.table package for performing pivot table summary/aggregation calculations. dplyr remains the default however data.table offers a moderate performance improvement for large data frames (10 million rows and above). See the Calculations vignette for more details.
  • addRowDataGroups and addColumnDataGroups functions pre-group the data to reduce the time required for larger data frames.
  • New argumentCheckMode parameter added to pivot table initialiser to provide an additional option to reduce the time required to create larger pivot tables.
  • print() method added to PivotTable class. Can now print a simple plain text view of the pivot table to the console using just pt or retrieve the plain text as a character value using pt$asCharacter.
  • Quick-pivot functions added that construct a basic pivot table with one line of R: qpvt(), qhpvt() and qlpvt(). See the Introduction vignette for more details.
  • Internal pivot filters class differentiates more clearly between 'all', 'some' and 'none' match cases for more robust filtering and early elimination of some cell calculations.
  • The getCells() function has been made more intuitive to use when getting specific cells by using a new cellCoordinates argument. See the Finding and Formatting vignette for details.
  • Stricter name checking for calculation names to avoid later unclear dplyr/data.table errors caused by syntax errors arising from illegal names.

Bug Fixes

  • Various small bug fixes.

Upcoming Changes

  • The previous usage of the arguments for the getCells() function is still supported (and is still the default) however the new argument usage will be made the default in a future version. For now, a message is displayed noting the upcoming change. See the Finding and Formatting vignette for more details.

pivottabler 0.2.0: New Output/Conversion Options, New Find Options

Breaking Changes

(none)

Improvements

  • Added the ability to output a pivot table in Latex.
  • Added the asMatrix() function to allow the pivot table contents to be retrieved as a matrix.
  • Added the asDataFrame() and asTidyDataFrame() functions to allow the pivot table contents to be retrieved as a data frame.
  • Added findRowDataGroups() and findColumnDataGroups() functions to find data groups (i.e. headings) that match specified criteria to simplify scenarios such as changing the styling of specific headings.
  • Added the getCells() function to retrieve cells by row number and/or column number.
  • Added the findCells() function to find cells in the body of a pivot table that match specified criteria to simplify scenarios such as conditional formatting.
  • Five new vignettes added. Many changes to the existing vignettes.
  • Modified the sample data by specifying a time zone (UTC) for all POSIXct data to remove inconsistencies when using the data in different time zones.
  • Modified the automated tests to no longer use the digest package.
  • Updated object documentation to wrap lines longer than 80 characters.

Bug Fixes

  • Various small bug fixes.
  • Shiny vignette examples now working.

pivottabler 0.1.0

Initial version.

Earlier versions

No versions prior to 0.1.0 were released.

Reference manual

It appears you don't have a PDF plugin for this browser. You can click here to download the reference manual.
install.packages("pivottabler")

1.0.0 by Christopher Bailiss, 3 months ago

https://github.com/cbailiss/pivottabler

Report a bug at https://github.com/cbailiss/pivottabler/issues

Browse source code at https://github.com/cran/pivottabler

Authors: Christopher Bailiss [aut, cre]

Documentation:   PDF Manual  

GPL-3 license

Imports R6, dplyr, data.table, jsonlite, htmltools, htmlwidgets

Suggests ggplot2, lubridate, listviewer, openxlsx, shiny, knitr, rmarkdown, testthat

Suggested by vaersvax.

See at CRAN

R links

R homepage

Download R

Mailing lists

R documentation

R manuals

R FAQs

The R Journal

CRAN links

CRAN homepage

CRAN repository policy

Submit a package

METACRAN stuff

About METACRAN

At github

Report a bug