NEWS.md
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.
This release includes one improvement and one change in anticipation of R 4.3.0.
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:
lowN=1 will find the cell with the lowest value.highN=1 will find the cell with the highest value.lowN=5 will find the five cells containing the lowest values, sorted into ascending order by cell value.highN=5 will find the five cells containing the highest values, sorted into descending order by cell value.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.
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.
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.
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.
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/
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().
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.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().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.pt$getColumnGroupsByLevel(), pt$getRowGroupsByLevel(), pt$getLeafColumnGroup(), pt$getLeafRowGroup(), pt$findGroupColumnNumbers() and pt$findGroupRowNumbers().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/
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 ), 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.
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.pt$getLeafColumnGroup(), pt$getLeafRowGroup(), pt$getCell(), pt$getColumnGroupsByLevel(), pt$getRowGroupsByLevel(), pt$columnGroupLevelCount and pt$rowGroupLevelCount.pt$setDefault() for the following parameters of pt$addColumnDataGroups() and pt$addRowDataGroups(): addTotal, expandExistingTotals, visualTotals, totalPosition, totalCaption, outlineBefore, outlineAfter and outlineTotal.pt$addColumnDataGroups() and pt$addRowDataGroups()can now be specified using the new caption argument. See the “Data Groups” vignette for details.pt$getEmptyRows() and pt$getEmptyColumns(). See the “Custom Layout Changes” section of the “Irregular Layout” vignette for details.pt$findRowDataGroups() and pt$findColumnDataGroups() functions gain additional parameters: atLevels, minChildCount, maxChildCount and outlineLinkedGroupExists. See the “Finding and Formatting” vignette for details.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.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.pt$asDataFrame(rowGroupsAsColumns=TRUE). Thanks to @ismailmuller for the suggestion (#29).forceNumeric argument. TRUE will convert any values that are not integer/numeric to NA.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="...").calcFuncArgs argument. See the “Calculations” vignette for details. Thanks to @MarcoPortmann for the suggestion (#31 and #32).This release includes two small bug fixes only:
pt$asDataFrame() (issue #30).pt$asMatrix() or pt$asDataMatrix().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 been moved to: http://www.pivottabler.org.uk/articles/
pt$addRowGroup(caption="By Size", isEmpty=TRUE) or pt$addColumnGroup(...),pt$addRowGroup(variableName="Size", values="Small") or pt$addColumnGroup(...),pt$addRowGroup(variableName="Size", isTotal=TRUE) or pt$addColumnGroup(...).showRowGroupHeaders argument can be used with pt$renderPivot(), pt$getHtml(), pt$saveHtml(), pt$writeToExcelWorksheet() and pt$asBasicTable().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.setStyling() provides an alternative method to set style declarations on data group headers and cells. See the “Finding and Formatting” or “Irregular Layout” vignettes for examples.fmtFuncArgs parameter in the “Calculations” vignette for details.fmtFuncArgs parameter in the “Data Groups” vignette for details.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.
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.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).
This release includes one small bug fix only: Calling pt$setStyling(cells=…) on an empty list of cells now succeeds without an error (issue #23).
This release includes one small bug fix only: Calling pt$asDataFrame() on a pivot table containing blank/NA cells now succeeds without an error (issue #20).
Changes to rowspan and colspan attributes in HTML
When generating HTML, previous versions of the package would always generate rowspan and colspan attributes for merged table cells, even if the number of rows or columns being spanned was only one. Starting with v1.2.0, rowspan and colspan attributes are only generated where the number of rows or columns being spanned is greater than one. This should make no 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(explicitHeaderSpansOfOne=TRUE) as an argument when creating the pivot table, either in PivotTable$new() or one of the quick pivot functions such as qpvt().
Small improvements have been made to the conversion of pivot tables to basic tables (in the basictabler package). Starting from this version of pivottabler and v0.3.0 of basictabler the HTML that is generated from the two packages should be more consistent. Previously, basictabler would render row/column header cells using the HTML td element instead of the more correct th element. Thanks to @rickwargo for reporting this difference in HTML output between the two packages.
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().pt or retrieve the plain text as a character value using pt$asCharacter.qpvt(), qhpvt() and qlpvt(). See the Introduction vignette for more details.