Learn R
pivottabler (version 1.5.0)

PivotTable: R6 class that represents a pivot table.

Description

The `PivotTable` class is the primary class for constructing and interacting with a pivot table.

Arguments

Format

R6Class object.

Active bindings

argumentCheckMode

The level of argument checking to perform. One of "auto", "none", "minimal", "basic", "balanced" (default) or "full".

compatibility

A list containing compatibility options to force legacy behaviours. See the NEWS file for details.

traceEnabled

Default `FALSE`. Specify `TRUE` to generate a trace for debugging purposes.

processingLibrary

The package to use when processing data. Must be one of "auto" (which today is dplyr), "dplyr" or "data.table".

data

A `PivotData` object containing the data frames added to the pivot table.

rowGroup

The 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.

columnGroup

The 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.

rowGroupLevelCount

The number of visible levels in the row data group hierarchy.

columnGroupLevelCount

The number of visible levels in the column data group hierarchy.

topColumnGroups

A list containing the first level of column data groups.

leafColumnGroups

A list containing the bottom level of column data groups.

allColumnGroups

A list containing all of the column data groups.

topRowGroups

A list containing the first level of row data groups.

leafRowGroups

A list containing the bottom level of row data groups.

allRowGroups

A list containing all of the row data groups.

rowGrpHeaders

A list containing the row group headers.

calculationGroups

A list containing the calculation groups in the pivot table.

calculationsPosition

Either "row" or "column" describing which axis the calculations are rendered.

evaluationMode

Either "batch" (default) or "sequential" (legacy).

batchInfo

Diagnostic information describing the batches used in the last pivot table evaluation.

cells

A `PivotCells` object that contains all of the cells in the pivot table.

allCells

A list of all of the cells in the pivot table, where each element in the list is a 'PivotCell' object.

rowCount

The number of rows in the pivot table, excluding headings.

columnCount

The number of columns in the pivot table, excluding headings.

fixedWidthSized

The total width of the pivot table in characters if the pivot table were to be rendered as plain text, e.g. to the console.

asCharacter

A plain test representation of the pivot table.

theme

The 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.

styles

A `PivotStyles` object that contains the styles applied to the pivot table.

allowExternalStyles

Default `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.

mergeEmptyRowSpace

A character value describing how empty space is merged. Allowed values: "doNotMerge", "dataGroupsOnly", "cellsOnly", "dataGroupsAndCellsAs1", "dataGroupsAndCellsAs2".

mergeEmptyColumnSpace

A character value describing how empty space is merged. Allowed values: "doNotMerge", "dataGroupsOnly", "cellsOnly", "dataGroupsAndCellsAs1", "dataGroupsAndCellsAs2".

mergeEmptySpaceDirection

A character value describing how empty space is merged. Allowed values: "row" or "column"

allTimings

Get a data frame containing timing details of pivot table operations.

significantTimings

Get a data frame containing timing details of significant pivot table operations (i.e. where elapsed>0.1).

Methods

Public methods

Method new()

Create a new `PivotTable` object.

Usage

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
)

Arguments

processingLibrary

The package to use when processing data. Must be one of "auto" (which today is dplyr), "dplyr" or "data.table".

evaluationMode

Either "batch" (default) or "sequential" (legacy).

argumentCheckMode

The level of argument checking to perform. Must be one of "auto", "none", "minimal", "basic", "balanced" (default) or "full".

theme

A 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.

replaceExistingStyles

Default `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.

tableStyle

Styling 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.

headingStyle

Styling to apply to the headings. See the `tableStyle` argument for details.

cellStyle

Styling to apply to the normal cells. See the `tableStyle` argument for details.

totalStyle

Styling to apply to the total cells. See the `tableStyle` argument for details.

compatibility

A list containing compatibility options to force legacy behaviours. See the NEWS file for details.

traceEnabled

Default `FALSE`. Specify `TRUE` to generate a trace for debugging purposes.

traceFile

If tracing is enabled, the location to generate the trace file.

Returns

A new `PivotTable` object.

Method setDefault()

Specify default values for some function arguments.

Usage

PivotTable$setDefault(...)

Arguments

...

Default values to specify. See details.

Details

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.

Returns

No return value.

Method getDefault1()

Get the default value of an argument.

Usage

PivotTable$getDefault1(argValue = NULL, useDefault = NULL)

Arguments

argValue

The name and value of the argument.

useDefault

Specify `TRUE` to use the default.

Details

Both the argument name and argument value are taken from the `argValue` argument. The name is obtained from `as.character(substitute(argValue))`. This function is designed to easily slot into existing code, e.g. `getDefault1(addTotal, missing(addTotal))`.

Returns

The current value of the argument or the default value.

Method getDefault2()

Get the default value of an argument.

Usage

PivotTable$getDefault2(argName = NULL, argValue = NULL, useDefault = NULL)

Arguments

argName

The name of the argument.

argValue

The current value of the argument.

useDefault

Specify `TRUE` to use the default.

Returns

The current value of the argument or the default value.

Method getDefault3()

Get the default value of an argument.

Usage

PivotTable$getDefault3(argName)

Arguments

argName

The name of the argument.

Returns

The default value.

Method getNextInstanceId()

Get the next unique object instance identifier.

Usage

PivotTable$getNextInstanceId()

Details

R6 classes cannot be easily compared to check if two variables are both referring to the same object instance. Instance ids are a mechanism to work around this problem. Each data group and cell is assigned an instance id during object creation, which enables reliable reference comparisons.

Returns

An integer instance id.

Method addData()

Add a data frame with the specified name to the pivot table.

Usage

PivotTable$addData(dataFrame = NULL, dataName = NULL)

Arguments

dataFrame

The data frame to add.

dataName

The 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))`.

Details

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.

Returns

The `PivotData` object managing the data frames for the pivot table.

Method addTotalData()

Add a data frame containing totals data with the specified name and variables to the pivot table.

Usage

PivotTable$addTotalData(
  dataFrame = NULL,
  dataName = NULL,
  variableNames = NULL
)

Arguments

dataFrame

The data frame to add.

dataName

The name of the data frame to associate these totals with.

variableNames

A vector specifying how the aggregate data/totals in the data frame are grouped.

Details

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.

Returns

No return value.

Method getColumnGroupsByLevel()

Retrieve the data groups at the specified level or levels in the column groups hierarchy.

Usage

PivotTable$getColumnGroupsByLevel(level = NULL, collapse = FALSE)

Arguments

level

An integer value or vector specifying one or more level numbers. Level 1 represents the first visible level of data groups.

collapse

A logical value specifying whether the return value should be simplified. See details.

Details

If `level` is a vector: If `collapse` is `FALSE`, then a list of lists is returned, if `collapse` is `TRUE`, then a single combined list is returned.

Returns

A list containing `PivotDataGroup` objects.

Method getTopColumnGroups()

[Deprecated: Use topColumnGroups instead] Retrieve the first level of column data groups.

Usage

PivotTable$getTopColumnGroups()

Returns

A list containing `PivotDataGroup` objects.

Method getLeafColumnGroups()

[Deprecated: Use leafColumnGroups instead] Retrieve the bottom level of column data groups.

Usage

PivotTable$getLeafColumnGroups()

Returns

A list containing `PivotDataGroup` objects.

Method getLeafColumnGroup()

Retrieve the leaf-level data group associated with a specific column or columns.

Usage

PivotTable$getLeafColumnGroup(c = NULL)

Arguments

c

An integer column number or an integer vector of column numbers.

Returns

A `PivotDataGroup` object or a list of `PivotDataGroup` objects.

Method 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.

Usage

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
)

Arguments

variableName

A character value that specifies the name of the variable in the data frame that the group relates to and will filter.

filterType

Must 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.

values

A vector that specifies the filter values applied to `variableName` to select the data to match this row/column in the pivot table.

doNotExpand

Default value `FALSE` - specify `TRUE` to prevent the high-level methods such as `addDataGroups()` from adding child groups.

isEmpty

Default value `FALSE`, specify `TRUE` to mark that this group contains no data (e.g. if it is part of a header or outline row)

isOutline

Default value `FALSE` - specify `TRUE` to mark that this data group is an outline group.

styleAsOutline

Default value `FALSE` - specify `TRUE` to style this data group as an outline group. Only applicable when `isOutline` is `TRUE`.

captionTemplate

A character value that specifies the template for the data group caption, default "values".

caption

Effectively a hard-coded caption that overrides the built-in logic for generating a caption.

isTotal

Default `FALSE` - specify `TRUE` to mark that this data group is a total.

isLevelSubTotal

Default `FALSE` - specify `TRUE` to mark that this data group is a sub-total within a level.

isLevelTotal

Default `FALSE` - specify `TRUE` to mark that this data group is level total.

calculationGroupName

For calculation groups, this character value specifies the calculation group that `calculationName` belongs to.

calculationName

For calculation groups, this character value specifies the name of the calculation.

baseStyleName

The style name for the data group.

styleDeclarations

A list of CSS style declarations to overlay on top of the base style.

insertAtIndex

An integer that specifies the index in the list of child groups where the new group should be inserted.

insertBeforeGroup

Specifies an existing group that the new group should be inserted before.

insertAfterGroup

Specifies an existing group that the new group should be inserted after

mergeEmptySpace

A 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.

cellBaseStyleName

The style name for cells related to this data group.

cellStyleDeclarations

A list of CSS style declarations to overlay on top of the base style for cells related to this data group

sortAnchor

Used to specify sort behaviour for outline groups, must be one of "fixed", "next" or "previous".

resetCells

Default `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.

Details

See the "Irregular Layout" vignette for details and examples.

Returns

The new `PivotDataGroup` object.

Method 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.

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
)

Arguments

variableName

The name of the related column in the data frame(s) of the pivot table.

atLevel

The 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.

fromData

Default `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.

dataName

The name of the data frame (as specified in `pt$addData()`) to read the data group values from.

dataSortOrder

Must be one of "asc", "desc", "custom" or "none".

customSortOrder

A vector values sorted into the desired order.

caption

The template of data group captions to generate, default "value".

dataFormat

A character, list or custom function to format the data value.

dataFmtFuncArgs

A list that specifies any additional arguments to pass to a custom format function.

onlyCombinationsThatExist

Default `TRUE` to generate only combinations of data groups that exist in the data frame.

explicitListOfValues

A 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.

calculationGroupName

The calculation group that the new data groups are related to.

expandExistingTotals

Default `FALSE`, which means totals are not broken down in multi-level hierarchies.

addTotal

Default `TRUE`, which means sub-total and total data groups are automatically added.

visualTotals

Default `FALSE`, which means visual totals are disabled. See the "Data Groups" vignette for more details about visual totals.

totalPosition

Either "before" or "after" to specify where total groups are created, default "after".

totalCaption

The caption to display on total groups, default "Total".

onlyAddGroupIf

A 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`.

preGroupData

Default `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

baseStyleName

The 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.

styleDeclarations

CSS style declarations that can override the base style, expressed as a list, e.g. `list("font-weight"=bold")`.

Details

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.

Returns

A list of new `PivotDataGroup` objects that have been added.

Method 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.

Usage

PivotTable$normaliseColumnGroups(resetCells = TRUE)

Arguments

resetCells

Default `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.

Returns

A list of new `PivotDataGroup` objects that have been added.

Method sortColumnDataGroups()

Sort column data groups either by the data group data value, caption, a custom order or based on calculation result values.

Usage

PivotTable$sortColumnDataGroups(
  levelNumber = 1,
  orderBy = "calculation",
  customOrder = NULL,
  sortOrder = "desc",
  calculationGroupName = "default",
  calculationName = NULL,
  fromIndex = NULL,
  toIndex = NULL,
  resetCells = TRUE
)

Arguments

levelNumber

The 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).

orderBy

Must 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.

customOrder

A vector values sorted into the desired order.

sortOrder

Must be either "asc" or "desc".

calculationGroupName

If sorting using a calculation, the name of the calculation group containing the specified calculation.

calculationName

If sorting using a calculation, the name of the calculation.

fromIndex

A boundary to limit the sort operation.

toIndex

A boundary to limit the sort operation.

resetCells

Default `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.

Returns

No return value.

Method getRowGroupsByLevel()

Retrieve the data groups at the specified level or levels in the row groups hierarchy.

Usage

PivotTable$getRowGroupsByLevel(level = NULL, collapse = FALSE)

Arguments

level

An integer value or vector specifying one or more level numbers. Level 1 represents the first visible level of data groups.

collapse

A logical value specifying whether the return value should be simplified. See details.

Details

If `level` is a vector: If `collapse` is `FALSE`, then a list of lists is returned, if `collapse` is `TRUE`, then a single combined list is returned.

Returns

A list containing `PivotDataGroup` objects.

Method getTopRowGroups()

[Deprecated: Use topRowGroups instead] Retrieve the first level of row data groups.

Usage

PivotTable$getTopRowGroups()

Returns

A list containing `PivotDataGroup` objects.

Method getLeafRowGroups()

[Deprecated: Use leafRowGroups instead] Retrieve the bottom level of row data groups.

Usage

PivotTable$getLeafRowGroups()

Returns

A list containing `PivotDataGroup` objects.

Method getLeafRowGroup()

Retrieve the leaf-level data group associated with a specific row or rows.

Usage

PivotTable$getLeafRowGroup(r = NULL)

Arguments

r

An integer row number or an integer vector of row numbers.

Returns

A `PivotDataGroup` object or a list of `PivotDataGroup` objects.

Method 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.

Usage

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
)

Arguments

variableName

A character value that specifies the name of the variable in the data frame that the group relates to and will filter.

filterType

Must 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.

values

A vector that specifies the filter values applied to `variableName` to select the data to match this row/column in the pivot table.

doNotExpand

Default value `FALSE` - specify `TRUE` to prevent the high-level methods such as `addDataGroups()` from adding child groups.

isEmpty

Default value `FALSE`, specify `TRUE` to mark that this group contains no data (e.g. if it is part of a header or outline row)

isOutline

Default value `FALSE` - specify `TRUE` to mark that this data group is an outline group.

styleAsOutline

Default value `FALSE` - specify `TRUE` to style this data group as an outline group. Only applicable when `isOutline` is `TRUE`.

captionTemplate

A character value that specifies the template for the data group caption, default "values".

caption

Effectively a hard-coded caption that overrides the built-in logic for generating a caption.

isTotal

Default `FALSE` - specify `TRUE` to mark that this data group is a total.

isLevelSubTotal

Default `FALSE` - specify `TRUE` to mark that this data group is a sub-total within a level.

isLevelTotal

Default `FALSE` - specify `TRUE` to mark that this data group is level total.

calculationGroupName

For calculation groups, this character value specifies the calculation group that `calculationName` belongs to.

calculationName

For calculation groups, this character value specifies the name of the calculation.

baseStyleName

The style name for the data group.

styleDeclarations

A list of CSS style declarations to overlay on top of the base style.

insertAtIndex

An integer that specifies the index in the list of child groups where the new group should be inserted.

insertBeforeGroup

Specifies an existing group that the new group should be inserted before.

insertAfterGroup

Specifies an existing group that the new group should be inserted after

mergeEmptySpace

A 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.

cellBaseStyleName

The style name for cells related to this data group.

cellStyleDeclarations

A list of CSS style declarations to overlay on top of the base style for cells related to this data group

sortAnchor

Used to specify sort behaviour for outline groups, must be one of "fixed", "next" or "previous".

resetCells

Default `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.

outlineLinkedGroupId

Used to link an outline group to the value data group which has the child data groups.

Details

See the "Irregular Layout" vignette for details and examples.

Returns

The new `PivotDataGroup` object.

Method 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.

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
)

Arguments

variableName

The name of the related column in the data frame(s) of the pivot table.

atLevel

The 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.

fromData

Default `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.

dataName

The name of the data frame (as specified in `pt$addData()`) to read the data group values from.

dataSortOrder

Must be one of "asc", "desc", "custom" or "none".

customSortOrder

A vector values sorted into the desired order.

caption

The template of data group captions to generate, default "value".

dataFormat

A character, list or custom function to format the data value.

dataFmtFuncArgs

A list that specifies any additional arguments to pass to a custom format function.

onlyCombinationsThatExist

Default `TRUE` to generate only combinations of data groups that exist in the data frame.

explicitListOfValues

A 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.

calculationGroupName

The calculation group that the new data groups are related to.

expandExistingTotals

Default `FALSE`, which means totals are not broken down in multi-level hierarchies.

addTotal

Default `TRUE`, which means sub-total and total data groups are automatically added.

visualTotals

Default `FALSE`, which means visual totals are disabled. See the "Data Groups" vignette for more details about visual totals.

totalPosition

Either "before" or "after" to specify where total groups are created, default "after".

totalCaption

The caption to display on total groups, default "Total".

onlyAddGroupIf

A 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`.

preGroupData

Default `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

baseStyleName

The 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.

styleDeclarations

CSS style declarations that can override the base style, expressed as a list, e.g. `list("font-weight"=bold")`.

header

A character value used as the row-group column caption when row group headers are rendered.

outlineBefore

Default `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.

outlineAfter

Default `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.

outlineTotal

Default `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.

onlyAddOutlineChildGroupIf

A 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.

Details

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.

Returns

A list of new `PivotDataGroup` objects that have been added.

Method 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.

Usage

PivotTable$normaliseRowGroups(resetCells = TRUE)

Arguments

resetCells

Default `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.

Returns

A list of new `PivotDataGroup` objects that have been added.

Method sortRowDataGroups()

Sort row data groups either by the data group data value, caption, a custom order or based on calculation result values.

Usage

PivotTable$sortRowDataGroups(
  levelNumber = 1,
  orderBy = "calculation",
  customOrder = NULL,
  sortOrder = "desc",
  calculationGroupName = "default",
  calculationName = NULL,
  fromIndex = NULL,
  toIndex = NULL,
  resetCells = TRUE
)

Arguments

levelNumber

The 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).

orderBy

Must 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.

customOrder

A vector values sorted into the desired order.

sortOrder

Must be either "asc" or "desc".

calculationGroupName

If sorting using a calculation, the name of the calculation group containing the specified calculation.

calculationName

If sorting using a calculation, the name of the calculation.

fromIndex

A boundary to limit the sort operation.

toIndex

A boundary to limit the sort operation.

resetCells

Default `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.

Returns

No return value.

Method setRowDataGroupHeader()

Set the row group header associated with a level of the row data group hierarchy.

Usage

PivotTable$setRowDataGroupHeader(levelNumber = NULL, header = NULL)

Arguments

levelNumber

An integer specifying the level number.

header

A character value specifying the caption.

Details

By default, the row data groups (i.e. row headings) at the left of the pivot table have no column headings. This method can specify the headings, which can be rendered by specifying the `showRowGroupHeaders=TRUE` in the render methods.

Returns

No return value.

Method addCalculationGroup()

Create a new calculation group. This is rarely needed since the default group is sufficient for all regular pivot tables.

Usage

PivotTable$addCalculationGroup(calculationGroupName = NULL)

Arguments

calculationGroupName

The name of the new calculation group to create.

Returns

A `PivotCalculationGroup` object.

Method defineCalculation()

Create a new `PivotCalculation` object.

Usage

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
)

Arguments

calculationGroupName

The 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).

calculationName

Calculation unique name.

caption

Calculation 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.

displayOrder

The order the calculations are displayed in the pivot table.

filters

Any 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.

format

A character, list or custom function to format the calculation result.

fmtFuncArgs

A list that specifies any additional arguments to pass to a custom format function.

dataName

Specifies which data frame in the pivot table is used for this calculation (as specified in `pt$addData()`).

type

The calculation type: "summary", "calculation", "function" or "value".

valueName

For type="value", the name of the column containing the value to display in the pivot table.

summariseExpression

For type="summary", either the dplyr expression to use with dplyr::summarise() or a data.table calculation expression.

calculationExpression

For type="calculation", an expression to combine aggregate values.

calculationFunction

For type="function", a reference to a custom R function that will carry out the calculation.

calcFuncArgs

For type="function", a list that specifies additional arguments to pass to calculationFunction.

basedOn

A character vector specifying the names of one or more calculations that this calculation depends on.

noDataValue

An integer or numeric value specifying the value to use if no data exists for a particular cell.

noDataCaption

A character value that will be displayed by the pivot table if no data exists for a particular cell.

headingBaseStyleName

The name of a style defined in the pivot table to use as the base styling for the data group heading.

headingStyleDeclarations

A list of CSS style declarations (e.g. `list("font-weight"="bold")`) to override the base style.

cellBaseStyleName

The name of a style defined in the pivot table to use as the base styling for the cells related to this calculation.

cellStyleDeclarations

A list of CSS style declarations (e.g. `list("font-weight"="bold")`) to override the base style.

resetCells

Default `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.

Returns

A new `PivotCalculation` object.

Method addColumnCalculationGroups()

Set calculations on existing data groups or add multiple new groups to the column data group hierarchy to represent calculations.

Usage

PivotTable$addColumnCalculationGroups(
  calculationGroupName = "default",
  atLevel = NULL
)

Arguments

calculationGroupName

The name of the calculation group to add into the data group hierarchy.

atLevel

The 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.

Details

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.

Returns

A list of new `PivotDataGroup` objects that have been added.

Method addRowCalculationGroups()

Set calculations on existing data groups or add multiple new groups to the row data group hierarchy to represent calculations.

Usage

PivotTable$addRowCalculationGroups(
  calculationGroupName = "default",
  atLevel = NULL,
  outlineBefore = NULL,
  outlineAfter = NULL
)

Arguments

calculationGroupName

The name of the calculation group to add into the data group hierarchy.

atLevel

The 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.

outlineBefore

Default `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.

outlineAfter

Default `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.

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.

Returns

A list of new `PivotDataGroup` objects that have been added.

Method addStyle()

Add a new named style to the pivot table.

Usage

PivotTable$addStyle(styleName = NULL, declarations = NULL)

Arguments

styleName

The name of the new style.

declarations

CSS style declarations in the form of a list, e.g. `list("font-weight"="bold", "color"="#0000FF")`

Returns

The newly created `PivotStyle` object.

Method 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.

Usage

PivotTable$createInlineStyle(baseStyleName = NULL, declarations = NULL)

Arguments

baseStyleName

The name of an existing style to base the new style on.

declarations

CSS style declarations in the form of a list, e.g. `list("font-weight"="bold", "color"="#0000FF")`

Details

Inline styles are typically used to override the style of some specific cells in a pivot table. Inline styles have no name. In HTML, they are rendered as 'style' attributes on specific table cells, where as named styles are linked to cells using the 'class' attribute.

Returns

The newly created `PivotStyle` object.

Method setStyling()

Apply styling to a set of data groups or cells in a pivot table.

Usage

PivotTable$setStyling(
  rFrom = NULL,
  cFrom = NULL,
  rTo = NULL,
  cTo = NULL,
  rowNumbers = NULL,
  columnNumbers = NULL,
  groups = NULL,
  cells = NULL,
  baseStyleName = NULL,
  style = NULL,
  declarations = NULL
)

Arguments

rFrom

An integer row number that specifies the start row for the styling changes.

cFrom

An integer column number that specifies the start column for the styling changes.

rTo

An integer row number that specifies the end row for the styling changes.

cTo

An integer column number that specifies the end column for the styling changes.

rowNumbers

An integer vector that specifies the row numbers for the styling changes.

columnNumbers

An integer vector that specifies the column numbers for the styling changes.

groups

A list containing `PivotDataGroup` objects.

cells

A list containing `PivotCell` objects.

baseStyleName

The name of a style to apply.

style

A `PivotStyle` object to apply.

declarations

CSS style declarations to apply in the form of a list, e.g. `list("font-weight"="bold", "color"="#0000FF")`

Details

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.

Returns

No return value.

Method mapStyling()

Apply styling to pivot table cells based on the value of each cell.

Usage

PivotTable$mapStyling(
  styleProperty = NULL,
  cells = NULL,
  valueType = "text",
  mapType = "range",
  mappings = NULL,
  styleLowerValues = FALSE,
  styleHigherValues = TRUE
)

Arguments

styleProperty

The name of the style property to set on the specified cells, e.g. background-color.

cells

A list containing `PivotCell` objects.

valueType

The 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.

mapType

The 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".

mappings

The 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.

styleLowerValues

A 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".

styleHigherValues

A 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".

Details

`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.

Returns

No return value.

Method generateCellStructure()

Generate the cells that will form the body of the pivot table.

Usage

PivotTable$generateCellStructure()

Details

This method rarely needs to be called explicitly, since other methods will invoke it if needed.

Returns

No return value.

Method resetCells()

Clear the cells of the pivot table.

Usage

PivotTable$resetCells()

Details

The cells are reset automatically when structural changes are made to the pivot table, so this method rarely needs to be called explicitly.

Returns

No return value.

Method evaluateCells()

Calculate the cell values in the body of the pivot table.

Usage

PivotTable$evaluateCells()

Details

This method rarely needs to be called explicitly, since other methods will invoke it if needed.

Returns

No return value.

Method evaluatePivot()

Calculate the cell values in the body of the pivot table.

Usage

PivotTable$evaluatePivot()

Details

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.

Returns

No return value.

Method findRowDataGroups()

Find row data groups that match specified criteria.

Usage

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
)

Arguments

matchMode

Either "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.

variableNames

A 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

Examples

Run this code
# NOT RUN {
# 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()
# }

Run the code above in your browser using DataLab