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 also be converted to a standard R matrix or data frame.

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

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

0.4.0 by Christopher Bailiss, 9 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


See at CRAN