v1.5.5: Minor maintenance release

pivottabler 1.5.5

A minor maintenance release in anticipation of R 4.4.0:

R 4.4.0 changes the behaviour of the base function is.atomic() so that is.atomic(NULL) now returns FALSE. A couple of minor code changes were needed in the pivottabler package source code as a result of this change. Users of the pivottabler package should not need to make any changes to their code/scripts.

v1.5.4: Minor Changes

This release includes one improvement and one change in anticipation of R 4.3.0.

Improvements

pt$findCells() gains additional arguments lowN and highN. These arguments allow the cell with the min/max value or the N cells with the lowest/highest values to be easily found.

When either of these arguments is specified, the list of cells returned from pt$findCells() is sorted into the corresponding order by cell value.

Examples:

• Specifying lowN=1 will find the cell with the lowest value.
• Specifying highN=1 will find the cell with the highest value.
• Specifying lowN=5 will find the five cells containing the lowest values, sorted into ascending order by cell value.
• Specifying highN=5 will find the five cells containing the highest values, sorted into descending order by cell value.

R 4.3.0 Change

The change described below will not affect most pivottabler package users.

R 4.3.0 includes a fix/breaking change that affects how the base R match() function works and how the %in% operator works. Specifically Date, POSIXct and POSIXlt values are matched as character values from R 4.3.0 rather than their underlying internal value. This breaks one of the pivottabler package automated tests. Whilst fixing this test, the opportunity was also taken to to improve how pivottabler filters work with Date, POSIXct and POSIXlt values when filters specify more than one value of these types (e.g. when visual totals are in use) by replacing uses of the base::intersect(), base::union() and base::unlist() functions with versions that better handle Date, POSIXct and POSIXlt values.

v1.5.3: New export options

Improvements

Tables can now be exported to a wider variety of file formats using the basictabler and flextable packages. In addition to HTML, Latex and Excel (which can be generated directly by the pivottabler package), additional formats now supported using basictabler+flextable include include Microsoft Word, Microsoft PowerPoint and PDF. See the Outputs vignette for more details.

It is now easier to format the borders for specific cells. See the "Formatting cell borders for specific cells" section of the Styling vignette for more details.

v1.5.2: Bug fix for export to Excel via basictabler table

This release includes one small bug fix that sometimes affected pivot tables that were converted to basic tables (from the basictabler package) and which then were exported to an Excel file. The issue caused row/column headings for totals or calculations (when multiple calculations are defined) to appear blank when the basictabler table was exported to Excel. This issue has been resolved in two ways:

1. Headings (such as totals and calculation names) now have a raw value specified in the converted basictabler table.
2. The basictabler package now (by default) will write the formatted value to the Excel file if no raw value exists.

v1.5.1: Maintenance Release

This is a minor maintenance release.

Breaking Changes

stringsAsFactors in R 4.0.x and 4.1.x

From R 4.1.0, the default value of the stringsAsFactors argument in tbl$asDataFrame() changes to FALSE due to the deprecation of default.stringsAsFactors(). When the package is used on versions of R < 4.1.0, the package behaviour is unchanged. When the package used on R 4.0.x versions, a warning message is displayed about the change in future behaviour. The logic for this change was actually implemented in version 1.5.0 of the package but the impact will be felt with the release of R 4.1.0.

Bug Fixes

• Resolved error "getSimpleColoredTheme(): colors must be specified." that was sometimes encountered when using a simple theme as a list that is assigned to the pivot table pt$theme field.

v1.5.0: Numerous small enhancements

Overview

This release includes one potentially breaking change and many small enhancements across various parts of the package.

Reminder: The package now only contains one introductory vignette (due to the constraints on CRAN).
The full set of 15+ vignettes can be found at: http://www.pivottabler.org.uk/articles/

Breaking Changes

Stricter definition of empty cells

When retrieving cells using pt$getCells() with the excludeEmptyCells parameter, or when searching cells using pt$findCells() with the emptyCells parameter, previous versions of the package would only treat cells as empty if they were related to a data group marked as empty. Cells with NULL values were not considered as empty.

Starting with this version, cells with NULL values (i.e. where is.null(cell$rawValue)==TRUE) are also regarded as empty. This may result in more cells being regarded as empty. The previous behaviour is still available by specifying compatibility=list(legacyEmptyCellMatching=TRUE) as an argument when creating the pivot table, either in PivotTable$new() or one of the quick pivot functions such as qpvt().

Improvements

• pt$findRowDataGroups() and pt$findColumnDataGroups() gain new arguments rowNumbers, columnNumbers and cells to restrict the data group search based on combinations of row/column numbers and/or cells. See the "Finding and Formatting" vignette for details.
• New property pt$allCells provides a simple way to retrieve a list of all cells in the pivot table.
• pt$getCells() gains new arguments groups, rowGroups and columnGroups making it easier to retrieve cells related to specific data groups. See the "Finding and Formatting" vignette for details.
• pt$getCells() also gains a new matchMode argument making it easier to retrieve cells based on combinations of row and column criteria. See the "Finding and Formatting" vignette for details.
• pt$findCells() gain new arguments rowNumbers, columnNumbers, cellCoordinates, groups, rowGroups, columnGroups, cells and rowColumnMatchMode to restrict the cell search based on combinations of row, column and cell criteria. See the "Finding and Formatting" vignette for details.
• pt$findCells() also gains new argument valueRanges to enable more granular logic to be specified when matching cell values. See the "Finding and Formatting" vignette for details.
• pt$setStyling() gains new integer/numeric vector arguments rowNumbers and/or columnNumbers. It is now also possible to specify only a set of row numbers and then all cells in those rows will be styled (and similarly for column numbers). Previously, if only row numbers or only column numbers were specified, then no cells would be styled. Users who require the old logic can specify the argument compatibility=list(legacySetStylingRowColumnNumbers=TRUE) when calling PivotTable$new().
• New method pt$mapStyling() simplifies applying styling to cells based on cell value, e.g. banding by value into different colours or colour gradients. See the "Styling" vignette for details plus the "Finding and Formatting" vignette for a couple more examples.
• When using a simple theme (specified as a list) to style a pivot table, it is now possible to specify a font size. Thanks to @msgoussi for the suggestion (#48). See the "Styling" vignette for details.
• The following now accept vector/list arguments: pt$getColumnGroupsByLevel(), pt$getRowGroupsByLevel(), pt$getLeafColumnGroup(), pt$getLeafRowGroup(), pt$findGroupColumnNumbers() and pt$findGroupRowNumbers().
• Several new outline parameters are now supported to simplify styling when generating row data groups from a variable depth hierarchy. See "Variable Depth Hierarchy Example" in the "Regular Layout" vignette for details.

Bug Fixes

• Cell styles are now inherited correctly from calculations and data groups (#46). Thanks to @msgoussi for the bug report. An explanation of the style inheritance rules has also been added to the "Styling" vignette.

v1.4.0: Numerous small enhancements

Overview

This release includes one potential breaking change and many small enhancements across various parts of the package.

Reminder: The package now only contains one introductory vignette (due to the constraints on CRAN).
The full set of 15+ vignettes can be found at: http://www.pivottabler.org.uk/articles/

Breaking Changes

Row group and column group captions for blank values

When generating HTML, previous versions of the package would not output any caption for data groups with a blank null (e.g. NULL). This could lead to rows collapsing to a few pixels in height (if all of the cells on the row also had no value). Starting with v1.4.0, a non-breaking space character is emitted instead (HTML &nbsp;), in the same way that other parts of the pivot table sometimes also emit a non-breaking space character. This should make minimal difference to the visual appearance of the table, however it may cause issues for users who require the previous behaviour. The previous behaviour is still available by specifying compatibility=list(noDataGroupNBSP=TRUE) as an argument when creating the pivot table, either in PivotTable$new() or one of the quick pivot functions such as qpvt().

A future version of the package will likely include an option to prevent all non-breaking space characters from being emitted and more correctly use CSS style rules instead to control minimum data group heights/widths.

Documentation Changes

• The code documentation for pivottabler has been re-written to use the new R6 documentation capabilities in roxygen2. As a result the documentation is now more detailed than in previous versions, e.g. arguments in object method calls are now properly documented. Nonetheless, the easiest way to learn the package is using the vignettes.
• The new "Navigating a Pivot Table" vignette provides more detailed information about low-level methods for navigating in code over the different parts of a pivot table. Several new methods have also been added that are described in this vignette: pt$getLeafColumnGroup(), pt$getLeafRowGroup(), pt$getCell(), pt$getColumnGroupsByLevel(), pt$getRowGroupsByLevel(), pt$columnGroupLevelCount and pt$rowGroupLevelCount.
• The "Irregular Layout" vignette has been expanded to provide additional information to help with building custom pivot tables.

Improvements

• Defaults can now be set using pt$setDefault() for the following parameters of pt$addColumnDataGroups() and pt$addRowDataGroups(): addTotal, expandExistingTotals, visualTotals, totalPosition, totalCaption, outlineBefore, outlineAfter and outlineTotal.
• The captions of data groups added to pivot tables using pt$addColumnDataGroups() and pt$addRowDataGroups()can now be specified using the new caption argument. See the "Data Groups" vignette for details.
• Custom sort orders can be specified for data groups. See the "Data Groups" vignette for details.
• Empty rows/columns can be found using the new functions pt$getEmptyRows() and pt$getEmptyColumns(). See the "Custom Layout Changes" section of the "Irregular Layout" vignette for details.
• The pt$findRowDataGroups() and pt$findColumnDataGroups() functions gain additional parameters: atLevels, minChildCount, maxChildCount and outlineLinkedGroupExists. See the "Finding and Formatting" vignette for details.
• When deleting data groups using group$removeGroup(), it is now possible to also remove the related groups such as the outline group header row (aka. outline before) and outline group footer row (aka. outline after) using the new argument removedRelatedOutlineGroups=TRUE.
• Specific rows and/or columns can be removed from pivot tables using new functions such as pt$removeRow(3) and pt$removeRows(c(2, 4)). See the "Custom Layout Changes" section of the "Irregular Layout" vignette for details.
• pt$addRowDataGroups() gains two new arguments onlyAddGroupIf and onlyAddOutlineChildGroupIf which enable hierarchies with a variable number of levels to be used on rows in a pivot table in outline layout. See the "Regular Layout" vignette for details. Thanks to @MarcoPortmann for the usage scenario.
• When exporting to a data frame, it is now possible to also export the row groups as columns (instead of only row names) using pt$asDataFrame(rowGroupsAsColumns=TRUE). Thanks to @ismailmuller for the suggestion (#29).
• When exporting to a data frame, the handling of cell values that are not integer/numeric can be specified using the forceNumeric argument. TRUE will convert any values that are not integer/numeric to NA.
• Row group headers, visible when specifying pt$renderPivot(showRowGroupHeaders=TRUE), are now set automatically to the variable name when calling pt$addRowDataGroups(...). The name can be overridden using pt$addRowDataGroups(..., header="...").
• Additional arguments can now be passed to custom calculation functions using the calcFuncArgs argument. See the "Calculations" vignette for details. Thanks to @MarcoPortmann for the suggestion (#31 and #32).
• It is now possible to pre-calculate totals/aggregate data cells when using calculation method 4 ("Showing a value"). See the "Calculations" vignette for details. This closes a long-standing gap with this calculation method. (#2).

Deprecated

The following can still be used but now emits a deprecation warning:

• The getLevelNumber() method on data groups has been replaced with the levelNumber property.

v1.3.1: Two small bug fixes

This release includes two small bug fixes only:

• Pivot tables with nothing on the rows axis fail to convert to a data frame using pt$asDataFrame() (issue #30).
• Pivot tables with nothing on rows and/or columns sometimes fail to convert to a matrix using pt$asMatrix() or pt$asDataMatrix().

v1.3.0: Outline Layout

Overview

This release introduces a new layout type - outline layout - that can make larger pivot tables with multiple levels of row groups more readable and more visually appealing.

Several small improvements mean that irregular pivot tables (e.g. two pivot tables in one) are now easier to construct.

The package vignettes have grown too large be hosted on CRAN. They have moved to: http://www.pivottabler.org.uk/articles/

Improvements

• A new "outlined" layout is now supported (in "beta" in this release). This generates additional rows for row data groups which appear as headings within the pivot table. See the new "Regular Layout" vignette for details.
• Several small improvements make building irregular layouts much easier, including:
  • adding empty rows and columns, e.g. using pt$addRowGroup(caption="By Size", isEmpty=TRUE) or pt$addColumnGroup(...),
  • adding individual data groups, e.g. using pt$addRowGroup(variableName="Size", values="Small") or pt$addColumnGroup(...),
  • adding total data groups, e.g. using pt$addRowGroup(variableName="Size", isTotal=TRUE) or pt$addColumnGroup(...).
  • Several other options that are described in the "Irregular Layout" vignette.
• Headings for the row data groups (i.e. headings for the first column / first few columns) in a pivot table can now be specified. See the "Pivot tables as standard tables (row group headings)" section in the "Data Groups" vignette for details. The new showRowGroupHeaders argument can be used with pt$renderPivot(), pt$getHtml(), pt$saveHtml(), pt$writeToExcelWorksheet() and pt$asBasicTable().
• New function pt$asDataMatrix() provides a cleaner way to convert a pivot table to a matrix, where the row/column headings in the pivot table become the row/column headings in the matrix. See the "Outputs" vignette for details.
• New function setStyling() provides an alternative method to set style declarations on data group headers and cells. See the "Irregular Layout" vignette for an example.
• Additional arguments can now be passed to custom functions used to format calculation values. See the fmtFuncArgs parameter in the "Calculations" vignette for details.
• Additional arguments can now be passed to custom functions used to format data group values. See the fmtFuncArgs parameter in the "Data Groups" vignette for details.

Bug Fixes

The atLevel argument of pt$addColumnDataGroups() and pt$addRowDataGroups() now behaves correctly / more intuitively. Previously it would often add the data groups at the level below the level expected.

Deprecated

The following can still be used but now emit a deprecation warning:

• pt$getTopColumnGroups() has been deprecated and replaced with pt$topColumnGroups.
• pt$getLeafColumnGroups() has been deprecated and replaced with pt$leafColumnGroups.
• pt$getTopRowGroups() has been deprecated and replaced with pt$topRowGroups.
• pt$getLeafRowGroups() has been deprecated and replaced with pt$leafRowGroups.

v1.2.3: Calculation display order fix when using 10 or more calculations

This release includes one small bug fix only: Adding more than nine calculations causes the calculation columns to appear in the wrong order (issue #25).