The `PivotTable` class is the primary class for constructing and interacting with a pivot table.
R6Class object.
argumentCheckModeThe level of argument checking to perform. One of "auto", "none", "minimal", "basic", "balanced" (default) or "full".
compatibilityA list containing compatibility options to force legacy behaviours. See the NEWS file for details.
traceEnabledDefault `FALSE`. Specify `TRUE` to generate a trace for debugging purposes.
processingLibraryThe package to use when processing data. Must be one of "auto" (which today is dplyr), "dplyr" or "data.table".
dataA `PivotData` object containing the data frames added to the pivot table.
rowGroupThe hidden root `PivotDataGroup` at the top of the row data groups hierarchy. The children of this group form the first level of visible row data groups.
columnGroupThe hidden root `PivotDataGroup` at the top of the column data groups hierarchy. The children of this group form the first level of visible column data groups.
rowGroupLevelCountThe number of visible levels in the row data group hierarchy.
columnGroupLevelCountThe number of visible levels in the column data group hierarchy.
topColumnGroupsA list containing the first level of column data groups.
leafColumnGroupsA list containing the bottom level of column data groups.
allColumnGroupsA list containing all of the column data groups.
topRowGroupsA list containing the first level of row data groups.
leafRowGroupsA list containing the bottom level of row data groups.
allRowGroupsA list containing all of the row data groups.
rowGrpHeadersA list containing the row group headers.
calculationGroupsA list containing the calculation groups in the pivot table.
calculationsPositionEither "row" or "column" describing which axis the calculations are rendered.
evaluationModeEither "batch" (default) or "sequential" (legacy).
batchInfoDiagnostic information describing the batches used in the last pivot table evaluation.
cellsA `PivotCells` object that contains all of the cells in the pivot table.
allCellsA list of all of the cells in the pivot table, where each element in the list is a 'PivotCell' object.
rowCountThe number of rows in the pivot table, excluding headings.
columnCountThe number of columns in the pivot table, excluding headings.
fixedWidthSizedThe total width of the pivot table in characters if the pivot table were to be rendered as plain text, e.g. to the console.
asCharacterA plain text representation of the pivot table.
themeThe name of the theme used to style the pivot table. If setting this property, either a theme name can be used, or a list can be used (which specifies a simple theme) or a `PivotStyles` object can be used. See the "Styling" vignette for details and examples.
stylesA `PivotStyles` object that contains the styles applied to the pivot table.
allowExternalStylesDefault `FALSE`, which means the `PivotStyles` object checks that style names specified for styling the different parts of the pivot table must exist in the styles collection. If they do not an error will occur. Specify `TRUE` to disable this check, e.g. if the style definitions are not managed by `pivottabler` but instead in an external system.
mergeEmptyRowSpaceA character value describing how empty space is merged. Allowed values: "doNotMerge", "dataGroupsOnly", "cellsOnly", "dataGroupsAndCellsAs1", "dataGroupsAndCellsAs2".
mergeEmptyColumnSpaceA character value describing how empty space is merged. Allowed values: "doNotMerge", "dataGroupsOnly", "cellsOnly", "dataGroupsAndCellsAs1", "dataGroupsAndCellsAs2".
mergeEmptySpaceDirectionA character value describing how empty space is merged. Allowed values: "row" or "column"
allTimingsGet a data frame containing timing details of pivot table operations.
significantTimingsGet a data frame containing timing details of significant pivot table operations (i.e. where elapsed>0.1).
new()Create a new `PivotTable` object.
PivotTable$new(
processingLibrary = "auto",
evaluationMode = "batch",
argumentCheckMode = "auto",
theme = NULL,
replaceExistingStyles = FALSE,
tableStyle = NULL,
headingStyle = NULL,
cellStyle = NULL,
totalStyle = NULL,
compatibility = NULL,
traceEnabled = FALSE,
traceFile = NULL
)processingLibraryThe package to use when processing data. Must be one of "auto" (which today is dplyr), "dplyr" or "data.table".
evaluationModeEither "batch" (default) or "sequential" (legacy).
argumentCheckModeThe level of argument checking to perform. Must be one of "auto", "none", "minimal", "basic", "balanced" (default) or "full".
themeA theme to use to style the pivot table. Either:
(1) The name of a built in theme, or
(2) A list of simple style settings, or
(3) A `PivotStyles` object containing a full set of styles.
See the "Styling" vignette for many examples.
replaceExistingStylesDefault `FALSE` to retain existing styles in the styles collection and add specified styles as new custom styles. Specify `TRUE` to update the definitions of existing styles.
tableStyleStyling to apply to the table. Either:
(1) The name of a built in style, or
(2) A list of CSS style declarations, e.g.
`list("font-weight"="bold", "color"="#0000FF")`, or
(3) A `PivotStyle` object.
headingStyleStyling to apply to the headings. See the `tableStyle` argument for details.
cellStyleStyling to apply to the normal cells. See the `tableStyle` argument for details.
totalStyleStyling to apply to the total cells. See the `tableStyle` argument for details.
compatibilityA list containing compatibility options to force legacy behaviours. See the NEWS file for details.
traceEnabledDefault `FALSE`. Specify `TRUE` to generate a trace for debugging purposes.
traceFileIf tracing is enabled, the location to generate the trace file.
setDefault()Specify default values for some function arguments.
Defaults can be set for the following arguments of
`pt$addRowDataGroups()` and `pt$addColumnDataGroups()`:
`logical` values: `addTotal`, `expandExistingTotals`, `visualTotals`.
`character` values: `totalPosition`, `totalCaption`.
`list` or `logical` values: `outlineBefore`, `outlineAfter`, `outlineTotal`.
Errors are generated for default values that could not be set.
Warnings are generated for attempts to set defaults that aren't supported.
See the "A1. Appendix" vignette for more details.
getDefault1()Get the default value of an argument.
argValueThe name and value of the argument.
useDefaultSpecify `TRUE` to use the default.
getDefault2()Get the default value of an argument.
getNextInstanceId()Get the next unique object instance identifier.
addData()Add a data frame with the specified name to the pivot table.
dataFrameThe data frame to add.
dataNameThe name to be used to refer to the data frame. If no name is specified, the data frame variable name from the calling code is used, retrieved via `deparse(substitute(dataFrame))`.
The name is used to refer to the data frame when generating data groups or defining calculations. The pivot table tracks the first data frame added as the default data frame, so if only a single data frame is used, it is typically not necessary to ever explicitly refer to the name. Pivot tables are typically based on a single data frame, however it is possible to build a pivot table that uses data from multiple data frames.
addTotalData()Add a data frame containing totals data with the specified name and variables to the pivot table.
dataFrameThe data frame to add.
dataNameThe name of the data frame to associate these totals with.
variableNamesA vector specifying how the aggregate data/totals in the data frame are grouped.
When generating pivot tables, the package typically calculates cell values. However, the package can also use provided values (i.e. carry out no calculations). This presents a challenge in that the sub-totals and totals in a pivot table display values at a higher aggregation level than the normal cells in the body of the pivot table. This method allows further data frames to be specified that contain aggregated versions of the data. See the "Calculations" vignette for details and an example.
getColumnGroupsByLevel()Retrieve the data groups at the specified level or levels in the column groups hierarchy.
levelAn integer value or vector specifying one or more level numbers. Level 1 represents the first visible level of data groups.
collapseA logical value specifying whether the return value should be simplified. See details.
getTopColumnGroups()[Deprecated: Use topColumnGroups instead] Retrieve the first level of column data groups.
getLeafColumnGroups()[Deprecated: Use leafColumnGroups instead] Retrieve the bottom level of column data groups.
getLeafColumnGroup()Retrieve the leaf-level data group associated with a specific column or columns.
addColumnGroup()Add a new column data group at the top level of the column group hierarchy. The new group is added as the last child unless an index is specified.
PivotTable$addColumnGroup(
variableName = NULL,
filterType = "ALL",
values = NULL,
doNotExpand = FALSE,
isEmpty = FALSE,
isOutline = FALSE,
styleAsOutline = FALSE,
captionTemplate = "{value}",
caption = NULL,
isTotal = FALSE,
isLevelSubTotal = FALSE,
isLevelTotal = FALSE,
calculationGroupName = NULL,
calculationName = NULL,
baseStyleName = NULL,
styleDeclarations = NULL,
insertAtIndex = NULL,
insertBeforeGroup = NULL,
insertAfterGroup = NULL,
mergeEmptySpace = NULL,
cellBaseStyleName = NULL,
cellStyleDeclarations = NULL,
sortAnchor = NULL,
resetCells = TRUE
)variableNameA character value that specifies the name of the variable in the data frame that the group relates to and will filter.
filterTypeMust be one of "ALL", "VALUES", or "NONE" to specify
the filter type:
ALL means no filtering is applied.
VALUEs is the typical value used to specify that `variableName` is
filtered to only `values`.
NONE means no data will match this data group.
valuesA vector that specifies the filter values applied to `variableName` to select the data to match this row/column in the pivot table.
doNotExpandDefault value `FALSE` - specify `TRUE` to prevent the high-level methods such as `addDataGroups()` from adding child groups.
isEmptyDefault value `FALSE`, specify `TRUE` to mark that this group contains no data (e.g. if it is part of a header or outline row)
isOutlineDefault value `FALSE` - specify `TRUE` to mark that this data group is an outline group.
styleAsOutlineDefault value `FALSE` - specify `TRUE` to style this data group as an outline group. Only applicable when `isOutline` is `TRUE`.
captionTemplateA character value that specifies the template for the data group caption, default "values".
captionEffectively a hard-coded caption that overrides the built-in logic for generating a caption.
isTotalDefault `FALSE` - specify `TRUE` to mark that this data group is a total.
isLevelSubTotalDefault `FALSE` - specify `TRUE` to mark that this data group is a sub-total within a level.
isLevelTotalDefault `FALSE` - specify `TRUE` to mark that this data group is level total.
calculationGroupNameFor calculation groups, this character value specifies the calculation group that `calculationName` belongs to.
calculationNameFor calculation groups, this character value specifies the name of the calculation.
baseStyleNameThe style name for the data group.
styleDeclarationsA list of CSS style declarations to overlay on top of the base style.
insertAtIndexAn integer that specifies the index in the list of child groups where the new group should be inserted.
insertBeforeGroupSpecifies an existing group that the new group should be inserted before.
insertAfterGroupSpecifies an existing group that the new group should be inserted after
mergeEmptySpaceA character value that specifies how empty space should be merged. This is typically only used with outline groups (so applies to row groups only, not column groups). Must be one of "doNotMerge", "dataGroupsOnly", "cellsOnly", "dataGroupsAndCellsAs1" or "dataGroupsAndCellsAs2". See the "Regular Layout" vignette for more information.
cellBaseStyleNameThe style name for cells related to this data group.
cellStyleDeclarationsA list of CSS style declarations to overlay on top of the base style for cells related to this data group
sortAnchorUsed to specify sort behaviour for outline groups, must be one of "fixed", "next" or "previous".
resetCellsDefault `TRUE` to reset any cells that currently exist in the pivot table and trigger a recalculation of the pivot table when it is next rendered.
addColumnDataGroups()Add multiple new data groups to the column group hierarchy based on the distinct values in a data frame column or using explicitly specified data values. See the "Data Groups" vignette for example usage.
PivotTable$addColumnDataGroups(
variableName = NULL,
atLevel = NULL,
fromData = TRUE,
dataName = NULL,
dataSortOrder = "asc",
customSortOrder = NULL,
caption = "{value}",
dataFormat = NULL,
dataFmtFuncArgs = NULL,
onlyCombinationsThatExist = TRUE,
explicitListOfValues = NULL,
calculationGroupName = NULL,
expandExistingTotals = FALSE,
addTotal = TRUE,
visualTotals = FALSE,
totalPosition = "after",
totalCaption = "Total",
onlyAddGroupIf = NULL,
preGroupData = TRUE,
baseStyleName = NULL,
styleDeclarations = NULL
)variableNameThe name of the related column in the data frame(s) of the pivot table.
atLevelThe level number that specifies where to add the new groups. Level 1 = on the first visible level of the hierarchy. `NULL` = create a new level at the bottom of the hierarchy for the new groups.
fromDataDefault `TRUE` to generate the new data groups based on the data values that exist in the `variableName` column in the named data frame. If `FALSE`, then `explicitListOfValues` must be specified.
dataNameThe name of the data frame (as specified in `pt$addData()`) to read the data group values from.
dataSortOrderMust be one of "asc", "desc", "custom" or "none".
customSortOrderA vector values sorted into the desired order.
captionThe template of data group captions to generate, default "value".
dataFormatA character, list or custom function to format the data value.
dataFmtFuncArgsA list that specifies any additional arguments to pass to a custom format function.
onlyCombinationsThatExistDefault `TRUE` to generate only combinations of data groups that exist in the data frame.
explicitListOfValuesA list of explicit values to create data groups from. A data group is created for each element of the list. If a list element is vector of values (with length greater than 1), then a data group is created for multiple values instead of just a single value.
calculationGroupNameThe calculation group that the new data groups are related to.
expandExistingTotalsDefault `FALSE`, which means totals are not broken down in multi-level hierarchies.
addTotalDefault `TRUE`, which means sub-total and total data groups are automatically added.
visualTotalsDefault `FALSE`, which means visual totals are disabled. See the "Data Groups" vignette for more details about visual totals.
totalPositionEither "before" or "after" to specify where total groups are created, default "after".
totalCaptionThe caption to display on total groups, default "Total".
onlyAddGroupIfA filter expression that can be used to more finely control whether data groups are created at different locations in the hierarchy. There must be at least one row that matches this filter and the filters from the ancestor groups in order that the child group is created. E.g. `MaxDisplayLevel>5`.
preGroupDataDefault `TRUE`, which means that the pivot table pre-calculates the distinct combinations of variable values to reduce the CPU time and elapsed time required to generate data groups. Cannot be used in conjunction with the
baseStyleNameThe name of the style applied to this data group (i.e. this row/column heading). The style must exist in the `PivotStyles` object associated with the PivotTable.
styleDeclarationsCSS style declarations that can override the base style, expressed as a list, e.g. `list("font-weight"=bold")`.
There are broadly three different ways to call `addColumnDataGroups()`:
(1) dataName=name, fromData=TRUE, onlyCombinationsThatExist=TRUE - which
considers the ancestors of each existing data group to generate only those
combinations of values that exist in the data frame.
(2) dataName=name, fromData=TRUE, onlyCombinationsThatExist=FALSE - which
ignores the ancestors of each existing data group and simply adds every
distinct value of the specified variable under every existing data group,
which can result in combinations of values in the pivot table that don't
exist in the data frame (i.e. blank rows/columns in the pivot table).
(3) fromData=FALSE, explicitListOfValues=list(...) - simply adds every
value from the specified list under every existing data group.
normaliseColumnGroups()Normalise the column data group hierarchy so that all branches have the same number of levels - accomplished by adding empty child data groups where needed.
sortColumnDataGroups()Sort column data groups either by the data group data value, caption, a custom order or based on calculation result values.
PivotTable$sortColumnDataGroups(
levelNumber = 1,
orderBy = "calculation",
customOrder = NULL,
sortOrder = "desc",
calculationGroupName = "default",
calculationName = NULL,
fromIndex = NULL,
toIndex = NULL,
resetCells = TRUE
)levelNumberThe level number to sort the data groups, e.g. level 1 (default) sorts the data groups at level 1 of the hierarchy (which is the first visible level of data groups).
orderByMust be either "value", "caption", "calculation",
"customByValue" or "customByCaption".
"value" sorts by the raw (i.e. unformatted) group value.
"caption" sorts by the formatted character group caption.
"calculation" sorts using one of the calculations defined in the pivot table.
"customValue" sorts by the raw (i.e. unformatted) group value according to
the specified custom sort order.
"customCaption" sorts by the formatted character group caption according to
the specified custom sort order.
customOrderA vector values sorted into the desired order.
sortOrderMust be either "asc" or "desc".
calculationGroupNameIf sorting using a calculation, the name of the calculation group containing the specified calculation.
calculationNameIf sorting using a calculation, the name of the calculation.
fromIndexA boundary to limit the sort operation.
toIndexA boundary to limit the sort operation.
resetCellsDefault `TRUE` to reset any cells that currently exist in the pivot table and trigger a recalculation of the pivot table when it is next rendered.
getRowGroupsByLevel()Retrieve the data groups at the specified level or levels in the row groups hierarchy.
levelAn integer value or vector specifying one or more level numbers. Level 1 represents the first visible level of data groups.
collapseA logical value specifying whether the return value should be simplified. See details.
getTopRowGroups()[Deprecated: Use topRowGroups instead] Retrieve the first level of row data groups.
getLeafRowGroups()[Deprecated: Use leafRowGroups instead] Retrieve the bottom level of row data groups.
addRowGroup()Add a new column data group at the top level of the row group hierarchy. The new group is added as the last child unless an index is specified.
PivotTable$addRowGroup(
variableName = NULL,
filterType = "ALL",
values = NULL,
doNotExpand = FALSE,
isEmpty = FALSE,
isOutline = FALSE,
styleAsOutline = FALSE,
captionTemplate = "{value}",
caption = NULL,
isTotal = FALSE,
isLevelSubTotal = FALSE,
isLevelTotal = FALSE,
calculationGroupName = NULL,
calculationName = NULL,
baseStyleName = NULL,
styleDeclarations = NULL,
insertAtIndex = NULL,
insertBeforeGroup = NULL,
insertAfterGroup = NULL,
mergeEmptySpace = NULL,
cellBaseStyleName = NULL,
cellStyleDeclarations = NULL,
sortAnchor = NULL,
resetCells = TRUE
)variableNameA character value that specifies the name of the variable in the data frame that the group relates to and will filter.
filterTypeMust be one of "ALL", "VALUES", or "NONE" to specify
the filter type:
ALL means no filtering is applied.
VALUEs is the typical value used to specify that `variableName` is
filtered to only `values`.
NONE means no data will match this data group.
valuesA vector that specifies the filter values applied to `variableName` to select the data to match this row/column in the pivot table.
doNotExpandDefault value `FALSE` - specify `TRUE` to prevent the high-level methods such as `addDataGroups()` from adding child groups.
isEmptyDefault value `FALSE`, specify `TRUE` to mark that this group contains no data (e.g. if it is part of a header or outline row)
isOutlineDefault value `FALSE` - specify `TRUE` to mark that this data group is an outline group.
styleAsOutlineDefault value `FALSE` - specify `TRUE` to style this data group as an outline group. Only applicable when `isOutline` is `TRUE`.
captionTemplateA character value that specifies the template for the data group caption, default "values".
captionEffectively a hard-coded caption that overrides the built-in logic for generating a caption.
isTotalDefault `FALSE` - specify `TRUE` to mark that this data group is a total.
isLevelSubTotalDefault `FALSE` - specify `TRUE` to mark that this data group is a sub-total within a level.
isLevelTotalDefault `FALSE` - specify `TRUE` to mark that this data group is level total.
calculationGroupNameFor calculation groups, this character value specifies the calculation group that `calculationName` belongs to.
calculationNameFor calculation groups, this character value specifies the name of the calculation.
baseStyleNameThe style name for the data group.
styleDeclarationsA list of CSS style declarations to overlay on top of the base style.
insertAtIndexAn integer that specifies the index in the list of child groups where the new group should be inserted.
insertBeforeGroupSpecifies an existing group that the new group should be inserted before.
insertAfterGroupSpecifies an existing group that the new group should be inserted after
mergeEmptySpaceA character value that specifies how empty space should be merged. This is typically only used with outline groups (so applies to row groups only, not column groups). Must be one of "doNotMerge", "dataGroupsOnly", "cellsOnly", "dataGroupsAndCellsAs1" or "dataGroupsAndCellsAs2". See the "Regular Layout" vignette for more information.
cellBaseStyleNameThe style name for cells related to this data group.
cellStyleDeclarationsA list of CSS style declarations to overlay on top of the base style for cells related to this data group
sortAnchorUsed to specify sort behaviour for outline groups, must be one of "fixed", "next" or "previous".
resetCellsDefault `TRUE` to reset any cells that currently exist in the pivot table and trigger a recalculation of the pivot table when it is next rendered.
outlineLinkedGroupIdUsed to link an outline group to the value data group which has the child data groups.
addRowDataGroups()Add multiple new data groups to the row group hierarchy based on the distinct values in a data frame column or using explicitly specified data values. See the "Data Groups" vignette for example usage.
PivotTable$addRowDataGroups(
variableName = NULL,
atLevel = NULL,
fromData = TRUE,
dataName = NULL,
dataSortOrder = "asc",
customSortOrder = NULL,
caption = "{value}",
dataFormat = NULL,
dataFmtFuncArgs = NULL,
onlyCombinationsThatExist = TRUE,
explicitListOfValues = NULL,
calculationGroupName = NULL,
expandExistingTotals = FALSE,
addTotal = TRUE,
visualTotals = FALSE,
totalPosition = "after",
totalCaption = "Total",
onlyAddGroupIf = NULL,
preGroupData = TRUE,
baseStyleName = NULL,
styleDeclarations = NULL,
header = NULL,
outlineBefore = NULL,
outlineAfter = NULL,
outlineTotal = NULL,
onlyAddOutlineChildGroupIf = NULL
)variableNameThe name of the related column in the data frame(s) of the pivot table.
atLevelThe level number that specifies where to add the new groups. Level 1 = on the first visible level of the hierarchy. `NULL` = create a new level at the bottom of the hierarchy for the new groups.
fromDataDefault `TRUE` to generate the new data groups based on the data values that exist in the `variableName` column in the named data frame. If `FALSE`, then `explicitListOfValues` must be specified.
dataNameThe name of the data frame (as specified in `pt$addData()`) to read the data group values from.
dataSortOrderMust be one of "asc", "desc", "custom" or "none".
customSortOrderA vector values sorted into the desired order.
captionThe template of data group captions to generate, default "value".
dataFormatA character, list or custom function to format the data value.
dataFmtFuncArgsA list that specifies any additional arguments to pass to a custom format function.
onlyCombinationsThatExistDefault `TRUE` to generate only combinations of data groups that exist in the data frame.
explicitListOfValuesA list of explicit values to create data groups from. A data group is created for each element of the list. If a list element is vector of values (with length greater than 1), then a data group is created for multiple values instead of just a single value.
calculationGroupNameThe calculation group that the new data groups are related to.
expandExistingTotalsDefault `FALSE`, which means totals are not broken down in multi-level hierarchies.
addTotalDefault `TRUE`, which means sub-total and total data groups are automatically added.
visualTotalsDefault `FALSE`, which means visual totals are disabled. See the "Data Groups" vignette for more details about visual totals.
totalPositionEither "before" or "after" to specify where total groups are created, default "after".
totalCaptionThe caption to display on total groups, default "Total".
onlyAddGroupIfA filter expression that can be used to more finely control whether data groups are created at different locations in the hierarchy. There must be at least one row that matches this filter and the filters from the ancestor groups in order that the child group is created. E.g. `MaxDisplayLevel>5`.
preGroupDataDefault `TRUE`, which means that the pivot table pre-calculates the distinct combinations of variable values to reduce the CPU time and elapsed time required to generate data groups. Cannot be used in conjunction with the
baseStyleNameThe name of the style applied to this data group (i.e. this row/column heading). The style must exist in the `PivotStyles` object associated with the PivotTable.
styleDeclarationsCSS style declarations that can override the base style, expressed as a list, e.g. `list("font-weight"=bold")`.
headerA character value used as the row-group column caption when row group headers are rendered.
outlineBeforeDefault `FALSE` to disable the creation of outline header groups. Specify either `TRUE` or a list of outline group settings to create outline header groups. See the "Regular Layout" vignette for details.
outlineAfterDefault `FALSE` to disable the creation of outline footer groups. Specify either `TRUE` or a list of outline group settings to create outline footer groups. See the "Regular Layout" vignette for details.
outlineTotalDefault `FALSE` to disable the creation of outline totals. Specify either `TRUE` or a list of outline group settings to create outline totals. See the "Regular Layout" vignette for details.
onlyAddOutlineChildGroupIfA filter expression that can be used to more finely control whether outline child groups are created at different locations in the hierarchy. There must be at least one row that matches this filter and the filters from the ancestor groups in order that the outline child group is created. E.g. `MaxDisplayLevel>5`. See the "Regular Layout" vignette for an example.
There are broadly three different ways to call `addRowDataGroups()`:
(1) dataName=name, fromData=TRUE, onlyCombinationsThatExist=TRUE - which
considers the ancestors of each existing data group to generate only those
combinations of values that exist in the data frame.
(2) dataName=name, fromData=TRUE, onlyCombinationsThatExist=FALSE - which
ignores the ancestors of each existing data group and simply adds every
distinct value of the specified variable under every existing data group,
which can result in combinations of values in the pivot table that don't
exist in the data frame (i.e. blank rows/columns in the pivot table).
(3) fromData=FALSE, explicitListOfValues=list(...) - simply adds every
value from the specified list under every existing data group.
normaliseRowGroups()Normalise the row data group hierarchy so that all branches have the same number of levels - accomplished by adding empty child data groups where needed.
sortRowDataGroups()Sort row data groups either by the data group data value, caption, a custom order or based on calculation result values.
PivotTable$sortRowDataGroups(
levelNumber = 1,
orderBy = "calculation",
customOrder = NULL,
sortOrder = "desc",
calculationGroupName = "default",
calculationName = NULL,
fromIndex = NULL,
toIndex = NULL,
resetCells = TRUE
)levelNumberThe level number to sort the data groups, e.g. level 1 (default) sorts the data groups at level 1 of the hierarchy (which is the first visible level of data groups).
orderByMust be either "value", "caption", "calculation",
"customByValue" or "customByCaption".
"value" sorts by the raw (i.e. unformatted) group value.
"caption" sorts by the formatted character group caption.
"calculation" sorts using one of the calculations defined in the pivot table.
"customValue" sorts by the raw (i.e. unformatted) group value according to
the specified custom sort order.
"customCaption" sorts by the formatted character group caption according to
the specified custom sort order.
customOrderA vector values sorted into the desired order.
sortOrderMust be either "asc" or "desc".
calculationGroupNameIf sorting using a calculation, the name of the calculation group containing the specified calculation.
calculationNameIf sorting using a calculation, the name of the calculation.
fromIndexA boundary to limit the sort operation.
toIndexA boundary to limit the sort operation.
resetCellsDefault `TRUE` to reset any cells that currently exist in the pivot table and trigger a recalculation of the pivot table when it is next rendered.
setRowDataGroupHeader()Set the row group header associated with a level of the row data group hierarchy.
levelNumberAn integer specifying the level number.
headerA character value specifying the caption.
addCalculationGroup()Create a new calculation group. This is rarely needed since the default group is sufficient for all regular pivot tables.
defineCalculation()Create a new `PivotCalculation` object.
PivotTable$defineCalculation(
calculationGroupName = "default",
calculationName = NULL,
caption = NULL,
visible = TRUE,
displayOrder = NULL,
filters = NULL,
format = NULL,
fmtFuncArgs = NULL,
dataName = NULL,
type = "summary",
valueName = NULL,
summariseExpression = NULL,
calculationExpression = NULL,
calculationFunction = NULL,
calcFuncArgs = NULL,
basedOn = NULL,
noDataValue = NULL,
noDataCaption = NULL,
headingBaseStyleName = NULL,
headingStyleDeclarations = NULL,
cellBaseStyleName = NULL,
cellStyleDeclarations = NULL,
resetCells = TRUE
)calculationGroupNameThe name of the calculation group this calculation will belong to. The default calculation group will be used if this parameter is not specified (this is sufficient for all regular pivot tables).
calculationNameCalculation unique name.
captionCalculation display name
visible`TRUE` to show the calculation in the pivot table or `FALSE` to hide it. Hidden calculations are typically used as base values for other calculations.
displayOrderThe order the calculations are displayed in the pivot table.
filtersAny additional data filters specific to this calculation. This can be a `PivotFilters` object that further restricts the data for the calculation or a list of individual `PivotFilter` objects that provide more flexibility (and/or/replace). See the Calculations vignette for details.
formatA character, list or custom function to format the calculation result.
fmtFuncArgsA list that specifies any additional arguments to pass to a custom format function.
dataNameSpecifies which data frame in the pivot table is used for this calculation (as specified in `pt$addData()`).
typeThe calculation type: "summary", "calculation", "function" or "value".
valueNameFor type="value", the name of the column containing the value to display in the pivot table.
summariseExpressionFor type="summary", either the dplyr expression to use with dplyr::summarise() or a data.table calculation expression.
calculationExpressionFor type="calculation", an expression to combine aggregate values.
calculationFunctionFor type="function", a reference to a custom R function that will carry out the calculation.
calcFuncArgsFor type="function", a list that specifies additional arguments to pass to calculationFunction.
basedOnA character vector specifying the names of one or more calculations that this calculation depends on.
noDataValueAn integer or numeric value specifying the value to use if no data exists for a particular cell.
noDataCaptionA character value that will be displayed by the pivot table if no data exists for a particular cell.
headingBaseStyleNameThe name of a style defined in the pivot table to use as the base styling for the data group heading.
headingStyleDeclarationsA list of CSS style declarations (e.g. `list("font-weight"="bold")`) to override the base style.
cellBaseStyleNameThe name of a style defined in the pivot table to use as the base styling for the cells related to this calculation.
cellStyleDeclarationsA list of CSS style declarations (e.g. `list("font-weight"="bold")`) to override the base style.
resetCellsDefault `TRUE` to reset any cells that currently exist in the pivot table and trigger a recalculation of the pivot table when it is next rendered.
addColumnCalculationGroups()Set calculations on existing data groups or add multiple new groups to the column data group hierarchy to represent calculations.
calculationGroupNameThe name of the calculation group to add into the data group hierarchy.
atLevelThe level number that specifies where to add the new groups. Level 1 = on the first visible level of the hierarchy. `NULL` = create a new level at the bottom of the hierarchy for the new groups.
If only one calculation is defined in the pivot table, then the calculation is set onto the existing column data groups (and no new groups are generated). If multiple calculations are defined, then a new level of data groups is added, e.g. if two calculations are defined, then two new data groups will be created under each existing leaf-level column data group.
addRowCalculationGroups()Set calculations on existing data groups or add multiple new groups to the row data group hierarchy to represent calculations.
PivotTable$addRowCalculationGroups(
calculationGroupName = "default",
atLevel = NULL,
outlineBefore = NULL,
outlineAfter = NULL
)calculationGroupNameThe name of the calculation group to add into the data group hierarchy.
atLevelThe level number that specifies where to add the new groups. Level 1 = on the first visible level of the hierarchy. `NULL` = create a new level at the bottom of the hierarchy for the new groups.
outlineBeforeDefault `FALSE` to disable the creation of outline header groups. Specify either `TRUE` or a list of outline group settings to create outline header groups. See the "Regular Layout" vignette for details.
outlineAfterDefault `FALSE` to disable the creation of outline footer groups. Specify either `TRUE` or a list of outline group settings to create outline footer groups. See the "Regular Layout" vignette for details.
If only one calculation is defined in the pivot table, then the calculation is set onto the existing row data groups (and no new groups are generated). If multiple calculations are defined, then a new level of data groups is added, e.g. if two calculations are defined, then two new data groups will be created under each existing leaf-level row data group.
addStyle()Add a new named style to the pivot table.
createInlineStyle()Create an inline style that can be used to override a base style. For general use cases, the `setStyling()` method provides a simpler and more direct way of styling specific parts of a pivot table.
baseStyleNameThe name of an existing style to base the new style on.
declarationsCSS style declarations in the form of a list, e.g. `list("font-weight"="bold", "color"="#0000FF")`
setStyling()Apply styling to a set of data groups or cells in a pivot table.
PivotTable$setStyling(
rFrom = NULL,
cFrom = NULL,
rTo = NULL,
cTo = NULL,
rowNumbers = NULL,
columnNumbers = NULL,
groups = NULL,
cells = NULL,
baseStyleName = NULL,
style = NULL,
declarations = NULL,
applyBorderToAdjacentCells = FALSE
)rFromAn integer row number that specifies the start row for the styling changes.
cFromAn integer column number that specifies the start column for the styling changes.
rToAn integer row number that specifies the end row for the styling changes.
cToAn integer column number that specifies the end column for the styling changes.
rowNumbersAn integer vector that specifies the row numbers for the styling changes.
columnNumbersAn integer vector that specifies the column numbers for the styling changes.
groupsA list containing `PivotDataGroup` objects.
cellsA list containing `PivotCell` objects.
baseStyleNameThe name of a style to apply.
styleA `PivotStyle` object to apply.
declarationsCSS style declarations to apply in the form of a list, e.g. `list("font-weight"="bold", "color"="#0000FF")`
applyBorderToAdjacentCellsTRUE to override the border in neighbouring cells, e.g. the left border of the current cell becomes the right border of the cell to the left. Does not apply to row/column groups.
There are five ways to specify the part(s) of a pivot table to apply
styling to:
(1) By specifying a list of data groups using the `groups` argument.
(2) By specifying a list of cells using the `cells` argument.
(3) By specifying a single cell using the `rFrom` and `cFrom` arguments.
(4) By specifying a rectangular cell range using the `rFrom`, `cFrom`,
`rTo` and `cTo` arguments.
(5) By specifying a vector of rowNumbers and/or columnNumbers. If both
rowNumbers and columnNumbers are specified, then the cells at the
intersection of the specified row numbers and column numbers are styled.
If both rFrom/rTo and rowNumbers are specified, then rFrom/rTo constrain
the row numbers specified in rowNumbers.
If both cFrom/cTo and columnNumbers are specified, then cFrom/cTo constrain
the column numbers specified in columnNumbers.
See the "Styling" and "Finding and Formatting" vignettes for more
information and many examples.
mapStyling()Apply styling to pivot table cells based on the value of each cell.
PivotTable$mapStyling(
styleProperty = NULL,
cells = NULL,
valueType = "text",
mapType = "range",
mappings = NULL,
styleLowerValues = FALSE,
styleHigherValues = TRUE
)stylePropertyThe name of the style property to set on the specified cells, e.g. background-color.
cellsA list containing `PivotCell` objects.
valueTypeThe type of style value to be set. Must be one of:
"text", "character", "number", "numeric", "color" or "colour".
"text" and "character" are equivalent. "number" and "numeric" are equivalent.
"color" and "colour" are equivalent.
mapTypeThe type of mapping to be performed. The following mapping
types are supported:
(1) "value" = a 1:1 mapping which maps each specified "from" value to the
corresponding "to" value, e.g. 100 -> "green".
(2) "logic" = each from value is logical criteria. See details.
(3) "range" = values between each pair of "from" values are mapped to the
corresponding "to" value, e.g. values in the range 80-100 -> "green" (more
specifically values greater than or equal to 80 and less than 100).
(4) "continuous" = rescales values between each pair of "from" values into
the range of the corresponding pair of "to" values, e.g. if the "from" range
is 80-100 and the corresponding "to" range is 0.8-1, then 90 -> 0.9.
"continuous" cannot be used with valueType="text"/"character".
mappingsThe mappings to be applied, specified in one of the following
three forms:
(1) a list containing pairs of values, e.g.
`list(0, "red", 0.4, "yellow", 0.8, "green")`.
(2) a list containing "from" and "to" vectors/lists, e.g.
`list(from=c(0, 0.4, 0.8), to=c("red", "yellow", "green"))`.
(3) a custom mapping function that will be invoked once per cell, e.g.
`function(v, cell) if(isTRUE(v>0.8)) return("green") `.
Mappings must be specified in ascending order when valueType="range" or
valueType="continuous".
If a custom mapping function is specified, then the valueType and mapType
parameters are ignored.
styleLowerValuesA logical value, default `FALSE`, that specifies whether values less than the lowest specified "from" value should be styled using the style specified for the lowest "from" value. Only applies when valueType="range" or valueType="continuous".
styleHigherValuesA logical value, default `TRUE`, that specifies whether values greater than the highest specified "from" value should be styled using the style specified for the highest "from" value. Only applies when valueType="range" or valueType="continuous".
`mapStyling()` is typically used to conditionally apply styling to cells
based on the value of each individual cell, e.g. cells with values less
than a specified number could be coloured red.
mapType="logic" maps values matching specified logical criteria to
specific "to" values. The logical criteria can be any of the following
forms (the first matching mapping is used):
(1) a specific value, e.g. 12.
(2) a specific value equality condition, e.g. "v==12", where v
represents the cell value.
(3) a value range expression using the following abbreviated form:
"value1<=v<value2", e.g. "10<=v<15". Only "<" or "<=" can be used
in these value range expressions.
(4) a standard R logical expression, e.g.
"10<=v && v<15".
Basic R functions that test the value can also be
used, e.g. is.na(v).
See the "Styling" and Finding and Formatting" vignettes for more
information and many examples.
generateCellStructure()Generate the cells that will form the body of the pivot table.
resetCells()Clear the cells of the pivot table.
evaluateCells()Calculate the cell values in the body of the pivot table.
evaluatePivot()Calculate the cell values in the body of the pivot table.
This generally only needs to be called explicitly if specific pivot cells
need to be further processed (e.g. formatted) before the pivot table is
rendered.
This method is a wrapper for calling `normaliseColumnGroups()`,
`normaliseRowGroups()`, `generateCellStructure()` and `evaluateCells()`
in sequence.
findRowDataGroups()Find row data groups that match specified criteria.
PivotTable$findRowDataGroups(
matchMode = "simple",
variableNames = NULL,
variableValues = NULL,
totals = "include",
calculationNames = NULL,
atLevels = NULL,
minChildCount = NULL,
maxChildCount = NULL,
emptyGroups = "exclude",
outlineGroups = "exclude",
outlineLinkedGroupExists = NULL,
includeDescendantGroups = FALSE,
rowNumbers = NULL,
cells = NULL
)matchModeEither "simple" (default) or "combinations".
"simple" is used when matching only one variable-value, multiple
variable-value combinations are effectively logical "OR".
"combinations" is used when matching for combinations of variable
values, multiple variable-value combinations are effectively
logical "AND". A child group is viewed as having the variable-value
filters of itself and it's parent/ancestors, e.g.
`list("TrainCategory"="Express Passenger", "PowerType"="DMU")`,
would return the "DMU" data group underneath "Express Passenger".
See the "Finding and Formatting" vignette for graphical examples.
variableNamesA character vector specifying the name/names of the variables to find. This is useful generally only in pivot tables with irregular layouts, since in regular pivot tables every cell is related to every variable.
variableValuesA list specifying the variable names and values to find,
e.g. `variableValues=list("PowerType"=c("DMU", "HST"))`.
Specify "**" as the variable value to match totals for the specified variable.
Specify "!*" as the variable value to match non-totals for the specified variable.
NB: The totals/non-totals criteria above won’t work when visual totals are used.
totalsA word that specifies how totals are matched (overrides the finer settings above) - must be one of "include" (default), "exclude" or "only".
calculationNamesA character vector specifying the name/names of the calculations to find.
atLevelsAn integer vector constraining the levels in the hierarchy to search.
minChildCountMatch only data groups with this minimum number of children.
maxChildCountMatch only data groups with this maximum number of children.
emptyGroupsA word that specifies how empty groups are matched - must be one of "include", "exclude" (default) or "only".
outlineGroupsA word that specifies how outline cells are matched - must be one of "include", "exclude" (default) or "only".
outlineLinkedGroupExists`TRUE` to match only groups where the related outline child group still exists. `FALSE` to match only groups where the related outline child group no longer exists.
includeDescendantGroupsDefault `FALSE`. Specify true to also return all descendants of data groups that match the specified criteria.
rowNumbersAn integer vector specifying row numbers that constrains the data groups to be found.
cellsA `PivotCell` object or a list of `PivotCell` objects to specify one or more cells that must intersect the data groups.
findColumnDataGroups()Find column data groups that match specified criteria.
PivotTable$findColumnDataGroups(
matchMode = "simple",
variableNames = NULL,
variableValues = NULL,
totals = "include",
calculationNames = NULL,
atLevels = NULL,
minChildCount = NULL,
maxChildCount = NULL,
emptyGroups = "exclude",
includeDescendantGroups = FALSE,
columnNumbers = NULL,
cells = NULL
)matchModeEither "simple" (default) or "combinations".
"simple" is used when matching only one variable-value - multiple
variable-value combinations are effectively logical "OR".
"combinations" is used when matching for combinations of variable
values - multiple variable-value combinations are effectively
logical "AND". A child group is viewed as having the variable-value
filters of itself and it's parent/ancestors, e.g.
`list("TrainCategory"="Express Passenger", "PowerType"="DMU")`,
would return the "DMU" data group underneath "Express Passenger".
See the "Finding and Formatting" vignette for graphical examples.
variableNamesA character vector specifying the name/names of the variables to find. This is useful generally only in pivot tables with irregular layouts, since in regular pivot tables every cell is related to every variable.
variableValuesA list specifying the variable names and values to find,
e.g. `variableValues=list("PowerType"=c("DMU", "HST"))`.
Specify "**" as the variable value to match totals for the specified variable.
Specify "!*" as the variable value to match non-totals for the specified variable.
NB: The totals/non-totals criteria above won’t work when visual totals are used.
totalsA word that specifies how totals are matched (overrides the finer settings above) - must be one of "include" (default), "exclude" or "only".
calculationNamesA character vector specifying the name/names of the calculations to find.
atLevelsAn integer vector constraining the levels in the hierarchy to search.
minChildCountMatch only data groups with this minimum number of children.
maxChildCountMatch only data groups with this maximum number of children.
emptyGroupsA word that specifies how empty groups are matched - must be one of "include", "exclude" (default) or "only".
includeDescendantGroupsDefault `FALSE`. Specify true to also return all descendants of data groups that match the specified criteria.
columnNumbersAn integer vector specifying column numbers that constrains the data groups to be found.
cellsA `PivotCell` object or a list of `PivotCell` objects to specify one or more cells that must intersect the data groups.
getEmptyRows()Retrieve row numbers for rows where all cells are empty.
PivotTable$getEmptyRows(
NAasEmpty = TRUE,
zeroAsEmpty = FALSE,
zeroTolerance = 1e-06,
includeOutlineRows = FALSE
)getEmptyColumns()Retrieve column numbers for columns where all cells are empty.
getCell()Get the cell at the specified row and column coordinates in the pivot table.
getCells()Retrieve cells by a combination of row and/or column numbers. See the "Finding and Formatting" vignette for graphical examples.
PivotTable$getCells(
specifyCellsAsList = TRUE,
rowNumbers = NULL,
columnNumbers = NULL,
cellCoordinates = NULL,
excludeEmptyCells = FALSE,
groups = NULL,
rowGroups = NULL,
columnGroups = NULL,
matchMode = "simple"
)specifyCellsAsListSpecify how cells are retrieved. Default `TRUE`. More information is provided in the details section.
rowNumbersA vector of row numbers that specify the rows or cells to retrieve.
columnNumbersA vector of column numbers that specify the columns or cells to retrieve.
cellCoordinatesA list of two-element vectors that specify the coordinates of cells to retrieve. Ignored when `specifyCellsAsList=FALSE`.
excludeEmptyCellsDefault `FALSE`. Specify `TRUE` to exclude empty cells.
groupsA `PivotDataGroup` object or a list of `PivotDataGroup` objects on either the rows or columns axes. The cells to be retrieved must be related to at least one of these groups.
rowGroupsA `PivotDataGroup` object or a list of `PivotDataGroup` objects on the rows axis. The cells to be retrieved must be related to at least one of these row groups. If both `rowGroups` and `columnGroups` are specified, then the cells to be retrieved must be related to at least one of the specified row groups and one of the specified column groups.
columnGroupsA `PivotDataGroup` object or a list of `PivotDataGroup` objects on the columns axis. The cells to be retrieved must be related to at least one of these column groups. If both `rowGroups` and `columnGroups` are specified, then the cells to be retrieved must be related to at least one of the specified row groups and one of the specified column groups.
matchModeEither "simple" (default) or "combinations":
"simple" specifies that row and column arguments are considered separately
(logical OR), e.g. rowNumbers=1 and columnNumbers=2 will match all cells in
row 1 and all cells in column 2.
"combinations" specifies that row and column arguments are considered together
(logical AND), e.g. rowNumbers=1 and columnNumbers=2 will match only the
cell single at location (1, 2).
Arguments `rowNumbers`, `columnNumbers`, `rowGroups` and `columnGroups` are
affected by the match mode. All other arguments are not.
When `specifyCellsAsList=TRUE` (the default):
Get one or more rows by specifying the row numbers as a vector as
the rowNumbers argument and leaving the columnNumbers argument set
to the default value of `NULL`, or
Get one or more columns by specifying the column numbers as a vector
as the columnNumbers argument and leaving the rowNumbers argument
set to the default value of `NULL`, or
Get one or more individual cells by specifying the cellCoordinates
argument as a list of vectors of length 2, where each element in the
list is the row and column number of one cell,
e.g. `list(c(1, 2), c(3, 4))` specifies two cells, the first located
at row 1, column 2 and the second located at row 3, column 4.
When `specifyCellsAsList=FALSE`:
Get one or more rows by specifying the row numbers as a vector as the
rowNumbers argument and leaving the columnNumbers argument set to the
default value of `NULL`, or
Get one or more columns by specifying the column numbers as a vector
as the columnNumbers argument and leaving the rowNumbers argument set
to the default value of `NULL`, or
Get one or more cells by specifying the row and column numbers as vectors
for the rowNumbers and columnNumbers arguments, or
a mixture of the above, where for entire rows/columns the element in the
other vector is set to `NA`, e.g. to retrieve whole rows, specify the row
numbers as the rowNumbers but set the corresponding elements in the
columnNumbers vector to `NA`.
findCells()Find cells matching specified criteria. See the "Finding and Formatting" vignette for graphical examples.
PivotTable$findCells(
variableNames = NULL,
variableValues = NULL,
totals = "include",
calculationNames = NULL,
minValue = NULL,
maxValue = NULL,
exactValues = NULL,
valueRanges = NULL,
includeNull = TRUE,
includeNA = TRUE,
emptyCells = "include",
outlineCells = "exclude",
rowNumbers = NULL,
columnNumbers = NULL,
cellCoordinates = NULL,
groups = NULL,
rowGroups = NULL,
columnGroups = NULL,
rowColumnMatchMode = "simple",
cells = NULL,
lowN = NULL,
highN = NULL
)variableNamesA character vector specifying the name/names of the variables to find. This is useful generally only in pivot tables with irregular layouts, since in regular pivot tables every cell is related to every variable.
variableValuesA list specifying the variable names and values to find,
e.g. `variableValues=list("PowerType"=c("DMU", "HST"))`.
Specify "**" as the variable value to match totals for the specified variable.
Specify "!*" as the variable value to match non-totals for the specified variable.
NB: The totals/non-totals criteria above won’t work when visual totals are used.
totalsA word that specifies how totals are matched (overrides the finer settings above) - must be one of "include" (default), "exclude" or "only".
calculationNamesA character vector specifying the name/names of the calculations to find.
minValueA numerical value specifying a minimum value threshold.
maxValueA numerical value specifying a maximum value threshold.
exactValuesA vector or list specifying a set of allowed values.
valueRangesA vector specifying one or more value range expressions which the cell values must match. If multiple value range expressions are specified, then the cell value must match any of one the specified expressions. See details.
includeNullspecify TRUE to include `NULL` in the matched cells, FALSE to exclude `NULL` values.
includeNAspecify TRUE to include `NA` in the matched cells, FALSE to exclude `NA` values.
emptyCellsA word that specifies how empty cells are matched - must be one of "include" (default), "exclude" or "only".
outlineCellsA word that specifies how outline cells are matched - must be one of "include", "exclude" (default) or "only".
rowNumbersA vector of row numbers that specify the rows or cells to constrain the search.
columnNumbersA vector of column numbers that specify the columns or cells to constrain the search.
cellCoordinatesA list of two-element vectors that specify the coordinates of cells to constrain the search.
groupsA `PivotDataGroup` object or a list of `PivotDataGroup` objects on either the rows or columns axes. The cells to be searched must be related to at least one of these groups.
rowGroupsA `PivotDataGroup` object or a list of `PivotDataGroup` objects on the rows axis. The cells to be searched must be related to at least one of these row groups. If both `rowGroups` and `columnGroups` are specified, then the cells to be searched must be related to at least one of the specified row groups and one of the specified column groups.
columnGroupsA `PivotDataGroup` object or a list of `PivotDataGroup` objects on the columns axis. The cells to be searched must be related to at least one of these column groups. If both `rowGroups` and `columnGroups` are specified, then the cells to be searched must be related to at least one of the specified row groups and one of the specified column groups.
rowColumnMatchModeEither "simple" (default) or "combinations":
"simple" specifies that row and column arguments are considered separately
(logical OR), e.g. rowNumbers=1 and columnNumbers=2 will match all cells in
row 1 and all cells in column 2.
"combinations" specifies that row and column arguments are considered together
(logical AND), e.g. rowNumbers=1 and columnNumbers=2 will match only the
cell single at location (1, 2).
Arguments `rowNumbers`, `columnNumbers`, `rowGroups` and `columnGroups` are
affected by the match mode. All other arguments are not.
cellsA `PivotCell` object or a list of `PivotCell` objects to constrain the scope of the search.
lowNFind the first N cells (ascending order, lowest values first).
highNFind the last N cells (descending order, highest values first).
The valueRanges parameter can be any of the following
forms:
(1) a specific value, e.g. 12.
(2) a specific value equality condition, e.g. "v==12", where v
represents the cell value.
(3) a value range expression using the following abbreviated form:
"value1<=v<value2", e.g. "10<=v<15". Only "<" or "<=" can be used
in these value range expressions.
(4) a standard R logical expression, e.g.
"10<=v && v<15".
Basic R functions that test the value can also be
used, e.g. is.na(v).
findGroupColumnNumbers()Find the column numbers associated with a specific data group or groups.
groupA `PivotDataGroup` in the column data groups (i.e. a column heading) or a list of column data groups.
collapseA logical value specifying whether the return value should be simplified. See details.
findGroupRowNumbers()Find the row numbers associated with a specific data group or groups.
groupA `PivotDataGroup` in the row data groups (i.e. a row heading) or a list of row data groups.
collapseA logical value specifying whether the return value should be simplified. See details.
removeColumn()Remove a column from the pivot table.
removeColumns()Remove multiple column from the pivot table.
removeEmptyColumns()Remove columns where all cells are empty.
removeRow()Remove a row from the pivot table.
removeRows()Remove multiple rows from the pivot table.
removeEmptyRows()Remove rows where all cells are empty.
PivotTable$removeEmptyRows(
NAasEmpty = TRUE,
zeroAsEmpty = FALSE,
zeroTolerance = 1e-06,
includeOutlineRows = FALSE
)NAasEmpty`TRUE` (default) specifies that `NA` is treated as empty.
zeroAsEmpty`TRUE` specifies that zero is treated as empty, default `FALSE`.
zeroToleranceThe tolerance for zero comparisons, default 0.000001.
includeOutlineRows`TRUE` to also remove empty outline rows, default `FALSE`.
print()Outputs a plain text representation of the pivot table to the console or returns a character representation of the pivot table.
asMatrix()Convert the pivot table to a matrix, where the data group headings are included in the body of the matrix. This method tends to produce a character matrix.
PivotTable$asMatrix(
includeHeaders = TRUE,
repeatHeaders = FALSE,
rawValue = FALSE,
showRowGroupHeaders = FALSE
)includeHeaders`TRUE` (default) to include the headings in the body of the matrix. Specifying `FALSE` omits the headings.
repeatHeaders`FALSE` (default) only outputs the first occurrence of each header. Specify `TRUE` to repeat the headings.
rawValue`FALSE` (default) outputs the formatted (character) values. Specify `TRUE` to output the raw cell values.
showRowGroupHeaders`TRUE` to include the row group headers in the matrix, default `FALSE`.
asDataMatrix()Convert the pivot table to a matrix, where the data group headings are included as row/column headings in the matrix. This method tends to produce a numeric matrix.
includeHeaders`TRUE` (default) to include the headings in the matrix. Specifying `FALSE` omits the headings.
rawValue`TRUE` (default) outputs the raw cell values. Specify `FALSE` to output the formatted (character) values.
separatorSpecifies the character value used to concatenate data group captions where multiple levels exist in the data group hierarchy.
asDataFrame()Convert the pivot table to a data frame, combining multiple levels of headings with the specified separator and/or exporting the row groups as columns in the data frame.
PivotTable$asDataFrame(
separator = " ",
stringsAsFactors = NULL,
forceNumeric = FALSE,
rowGroupsAsColumns = FALSE
)separatorSpecifies the character value used to concatenate data group captions where multiple levels exist in the data group hierarchy.
stringsAsFactorsSpecify `TRUE` to convert strings to factors, default is `default.stringsAsFactors()` for R < 4.1.0 and `FALSE` for R >= 4.1.0.
forceNumericSpecify `TRUE` to force the conversion of cell values to a numeric value, default `FALSE`.
rowGroupsAsColumnsSpecify `TRUE` to include the row groups as additional columns in the data frame. Default `FALSE`.
asTidyDataFrame()Convert the pivot table to tidy data frame, where each cell in the body of the pivot table becomes one row in the data frame.
PivotTable$asTidyDataFrame(
includeGroupCaptions = TRUE,
includeGroupValues = TRUE,
separator = " ",
stringsAsFactors = NULL,
excludeEmptyCells = TRUE
)includeGroupCaptions`TRUE` (default) to include the data group captions as columns in the data frame.
includeGroupValues`TRUE` (default) to include the data group values as columns in the data frame.
separatorSpecifies the character value used to concatenate filter values where multiple values exist in a filter.
stringsAsFactorsSpecify `TRUE` to convert strings to factors, default is `default.stringsAsFactors()` for R < 4.1.0 and `FALSE` for R >= 4.1.0.
excludeEmptyCellsSpecify `FALSE` to also include rows for empty cells in the data frame, default `TRUE`.
getMerges()Generate a list of the merged cell information arising from the data group hierarchies. This is an internal method used to support rendering the pivot table.
asBasicTable()Convert the pivot table to a `basictabler` table (from the `basictabler` R package) which allows further custom manipulation of the pivot table.
PivotTable$asBasicTable(
exportOptions = NULL,
compatibility = NULL,
showRowGroupHeaders = FALSE
)exportOptionsA list of additional export options - see the "A1. Appendix" for details.
compatibilityA list containing compatibility options to force legacy behaviours in the resulting `basictabler` table.
showRowGroupHeaders`TRUE` to include the row group headers in the matrix, default `FALSE`.
getCss()Get the CSS declarations for the pivot table.
getHtml()Generate a HTML representation of the pivot table, optionally including additional detail for debugging purposes.
PivotTable$getHtml(
styleNamePrefix = NULL,
includeHeaderValues = FALSE,
includeRCFilters = FALSE,
includeCalculationFilters = FALSE,
includeWorkingData = FALSE,
includeEvaluationFilters = FALSE,
includeCalculationNames = FALSE,
includeRawValue = FALSE,
includeTotalInfo = FALSE,
exportOptions = NULL,
showRowGroupHeaders = FALSE
)styleNamePrefixA character variable specifying a prefix for all named CSS styles, to avoid style name collisions where multiple pivot tables exist.
includeHeaderValuesDefault `FALSE`, specify `TRUE` to render this debug information.
includeRCFiltersDefault `FALSE`, specify `TRUE` to render this debug information.
includeCalculationFiltersDefault `FALSE`, specify `TRUE` to render this debug information.
includeWorkingDataDefault `FALSE`, specify `TRUE` to render this debug information.
includeEvaluationFiltersDefault `FALSE`, specify `TRUE` to render this debug information.
includeCalculationNamesDefault `FALSE`, specify `TRUE` to render this debug information.
includeRawValueDefault `FALSE`, specify `TRUE` to render this debug information.
includeTotalInfoDefault `FALSE`, specify `TRUE` to render this debug information.
exportOptionsA list of additional export options - see the "A1. Appendix" for details.
showRowGroupHeadersDefault `FALSE`, specify `TRUE` to render the row group headings. See the "Data Groups" vignette for details.
saveHtml()Save a HTML representation of the pivot table to file, optionally including additional detail for debugging purposes.
PivotTable$saveHtml(
filePath = NULL,
fullPageHTML = TRUE,
styleNamePrefix = NULL,
includeHeaderValues = FALSE,
includeRCFilters = FALSE,
includeCalculationFilters = FALSE,
includeWorkingData = FALSE,
includeEvaluationFilters = FALSE,
includeCalculationNames = FALSE,
includeRawValue = FALSE,
includeTotalInfo = FALSE,
exportOptions = NULL,
showRowGroupHeaders = FALSE
)filePathThe file to save the HTML to.
fullPageHTML`TRUE` (default) includes basic HTML around the pivot table HTML so that the result file is a valid HTML file.
styleNamePrefixA character variable specifying a prefix for all named CSS styles, to avoid style name collisions where multiple pivot tables exist.
includeHeaderValuesDefault `FALSE`, specify `TRUE` to render this debug information.
includeRCFiltersDefault `FALSE`, specify `TRUE` to render this debug information.
includeCalculationFiltersDefault `FALSE`, specify `TRUE` to render this debug information.
includeWorkingDataDefault `FALSE`, specify `TRUE` to render this debug information.
includeEvaluationFiltersDefault `FALSE`, specify `TRUE` to render this debug information.
includeCalculationNamesDefault `FALSE`, specify `TRUE` to render this debug information.
includeRawValueDefault `FALSE`, specify `TRUE` to render this debug information.
includeTotalInfoDefault `FALSE`, specify `TRUE` to render this debug information.
exportOptionsA list of additional export options - see the "A1. Appendix" for details.
showRowGroupHeadersDefault `FALSE`, specify `TRUE` to render the row group headings. See the "Data Groups" vignette for details.
renderPivot()Render a HTML representation of the pivot table as an HTML widget, optionally including additional detail for debugging purposes.
PivotTable$renderPivot(
width = NULL,
height = NULL,
styleNamePrefix = NULL,
includeHeaderValues = FALSE,
includeRCFilters = FALSE,
includeCalculationFilters = FALSE,
includeWorkingData = FALSE,
includeEvaluationFilters = FALSE,
includeCalculationNames = FALSE,
includeRawValue = FALSE,
includeTotalInfo = FALSE,
exportOptions = NULL,
showRowGroupHeaders = FALSE
)widthThe width of the widget.
heightThe height of the widget.
styleNamePrefixA character variable specifying a prefix for all named CSS styles, to avoid style name collisions where multiple pivot tables exist.
includeHeaderValuesDefault `FALSE`, specify `TRUE` to render this debug information.
includeRCFiltersDefault `FALSE`, specify `TRUE` to render this debug information.
includeCalculationFiltersDefault `FALSE`, specify `TRUE` to render this debug information.
includeWorkingDataDefault `FALSE`, specify `TRUE` to render this debug information.
includeEvaluationFiltersDefault `FALSE`, specify `TRUE` to render this debug information.
includeCalculationNamesDefault `FALSE`, specify `TRUE` to render this debug information.
includeRawValueDefault `FALSE`, specify `TRUE` to render this debug information.
includeTotalInfoDefault `FALSE`, specify `TRUE` to render this debug information.
exportOptionsA list of additional export options - see the "A1. Appendix" for details.
showRowGroupHeadersDefault `FALSE`, specify `TRUE` to render the row group headings. See the "Data Groups" vignette for details.
getLatex()Generate a Latex representation of the pivot table.
PivotTable$getLatex(
caption = NULL,
label = NULL,
fromRow = NULL,
toRow = NULL,
fromColumn = NULL,
toColumn = NULL,
boldHeadings = FALSE,
italicHeadings = FALSE,
exportOptions = NULL
)captionThe caption to appear above the table.
labelThe label to use when referring to the table elsewhere in the document
fromRowThe row number to render from.
toRowThe row number to render to.
fromColumnThe column number to render from.
toColumnThe column number to render to.
boldHeadingsDefault `FALSE`, specify `TRUE` to render headings in bold.
italicHeadingsDefault `FALSE`, specify `TRUE` to render headings in italic.
exportOptionsA list of additional export options - see the "A1. Appendix" for details.
writeToExcelWorksheet()Write the pivot table into the specified workbook and worksheet at the specified row-column location.
PivotTable$writeToExcelWorksheet(
wb = NULL,
wsName = NULL,
topRowNumber = NULL,
leftMostColumnNumber = NULL,
outputHeadingsAs = "formattedValueAsText",
outputValuesAs = "rawValue",
applyStyles = TRUE,
mapStylesFromCSS = TRUE,
exportOptions = NULL,
showRowGroupHeaders = FALSE
)wbA `Workbook` object representing the Excel file being written to.
wsNameA character value specifying the name of the worksheet to write to.
topRowNumberAn integer value specifying the row number in the Excel worksheet to write the pivot table.
leftMostColumnNumberAn integer value specifying the column number in the Excel worksheet to write the pivot table.
outputHeadingsAsMust be one of "rawValue", "formattedValueAsText" (default) or "formattedValueAsNumber" to specify how data groups are written into the Excel sheet.
outputValuesAsMust be one of "rawValue" (default), "formattedValueAsText" or "formattedValueAsNumber" to specify how cell values are written into the Excel sheet.
applyStylesDefault `TRUE` to write styling information to the cell.
mapStylesFromCSSDefault `TRUE` to automatically convert CSS style declarations to their Excel equivalents.
exportOptionsA list of additional export options - see the "A1. Appendix" for details.
showRowGroupHeadersDefault `FALSE`, specify `TRUE` to write row group headers.
trace()Capture a call for tracing purposes. This is an internal method.
# The package vignettes include extensive examples of working with the
# PivotTable class.
library(pivottabler)
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$addColumnDataGroups("TrainCategory")
pt$addRowDataGroups("TOC")
pt$defineCalculation(calculationName="TotalTrains",
summariseExpression="n()")
pt$renderPivot()