PivotCells: R6 class that contains the cells in a pivot table.

In pivottabler: Create Pivot Tables

R6 class that contains the cells in a pivot table.

Description

The 'PivotCells' class contains all of the 'PivotCell' instances that comprise the body of a pivot table.

Format

R6Class object.

Active bindings

rowCount

The number of rows in the pivot table (excluding column headings).

columnCount

The number of columns in the pivot table (excluding column headings).

rowGroups

A list of the leaf-level data groups on the rows axis.

columnGroups

A list of the leaf-level data groups on the columns axis.

rows

A list of the rows in the pivot table. Each element in this list is a list of 'PivotCell' objects comprising the row.

all

A list of the cells in the pivot table. Each element in this list is a 'PivotCell' object.

Methods

Public methods

• PivotCells$new()

• PivotCells$reset()

• PivotCells$getColumnGroup()

• PivotCells$getRowGroup()

• PivotCells$setGroups()

• PivotCells$getCell()

• PivotCells$setCell()

• PivotCells$getCells()

• PivotCells$findCells()

• PivotCells$findGroupColumnNumbers()

• PivotCells$findGroupRowNumbers()

• PivotCells$getColumnWidths()

• PivotCells$removeColumn()

• PivotCells$removeColumns()

• PivotCells$removeRow()

• PivotCells$removeRows()

• PivotCells$asMatrix()

• PivotCells$asList()

• PivotCells$asJSON()

• PivotCells$clone()

---

Method new()

Create a new 'PivotCells' object.

Usage

PivotCells$new(parentPivot = NULL)

Arguments

parentPivot

The pivot table that this 'PivotCells' instance belongs to.

Returns

A new 'PivotCells' object.

---

Method reset()

Remove all cells from the pivot table and reset row and column counts back to zero.

Usage

PivotCells$reset()

Returns

No return value.

---

Method getColumnGroup()

Get the leaf-level data group that is associated with a specific column or columns in the pivot table.

Usage

PivotCells$getColumnGroup(c = NULL)

Arguments

c

The column number or numbers. The first column is column 1, excluding the column(s) associated with row-headings.

Returns

A 'PivotDataGroup' that is associated with the specified column.

---

Method getRowGroup()

Get the leaf-level data group that is associated with a specific row or rows in the pivot table.

Usage

PivotCells$getRowGroup(r = NULL)

Arguments

r

The row number or numbers. The first row is row 1, excluding the row(s) associated with column-headings.

Returns

A 'PivotDataGroup' that is associated with the specified row

---

Method setGroups()

An internal method used when building the cell structure of the pivot table.

Usage

PivotCells$setGroups(rowGroups = NULL, columnGroups = NULL)

Arguments

rowGroups

A list of 'PivotDataGroup' objects to be set as the leaf-level row groups in the pivot table.

columnGroups

A list of 'PivotDataGroup' objects to be set as the leaf-level column groups in the pivot table.

Returns

No return value.

---

Method getCell()

Get the cell at the specified row and column coordinates in the pivot table.

Usage

PivotCells$getCell(r = NULL, c = NULL)

Arguments

r

Row number of the cell to retrieve.

c

Column number of the cell to retrieve.

Details

The row and column numbers refer only to the cells in the body of the pivot table, i.e. row and column headings are excluded, e.g. row 1 is the first row of cells underneath the column headings.

Returns

A 'PivotCell' object representing the cell.

---

Method setCell()

Set the cell at the specified row and column coordinates in the pivot table.

Usage

PivotCells$setCell(r, c, cell)

Arguments

r

Row number of the cell to retrieve.

c

Column number of the cell to retrieve.

cell

A 'PivotCell' object to set into the pivot table cells.

Details

This method is intended for internal package use only, used when building # the cell structure. The row and column numbers refer only to the cells in the body of the pivot table, i.e. row and column headings are excluded, e.g. row 1 is the first row of cells underneath the column headings.

Returns

No return value.

---

Method getCells()

Retrieve cells by a combination of row and/or column numbers. See the "Finding and Formatting" vignette for graphical examples.

Usage

PivotCells$getCells(
  specifyCellsAsList = TRUE,
  rowNumbers = NULL,
  columnNumbers = NULL,
  cellCoordinates = NULL,
  excludeEmptyCells = FALSE,
  groups = NULL,
  rowGroups = NULL,
  columnGroups = NULL,
  matchMode = "simple"
)

Arguments

specifyCellsAsList

Specify how cells are retrieved. Default 'TRUE'. More information is provided in the details section.

rowNumbers

A vector of row numbers that specify the rows or cells to retrieve.

columnNumbers

A vector of row numbers that specify the columns or cells to retrieve.

cellCoordinates

A list of two-element vectors that specify the coordinates of cells to retrieve. Ignored when 'specifyCellsAsList=FALSE'.

excludeEmptyCells

Default 'FALSE'. Specify 'TRUE' to exclude empty cells.

groups

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

rowGroups

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

columnGroups

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

matchMode

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

Details

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

Returns

A list of 'PivotCell' objects.

---

Method findCells()

Find cells matching specified criteria. See the "Finding and Formatting" vignette for graphical examples.

Usage

PivotCells$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
)

Arguments

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 is related to every variable.

variableValues

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

totals

A word that specifies how totals are matched (overrides the finer settings above) - must be one of "include" (default), "exclude" or "only".

calculationNames

A character vector specifying the name/names of the calculations to find.

minValue

A numerical value specifying a minimum value threshold.

maxValue

A numerical value specifying a maximum value threshold.

exactValues

A vector or list specifying a set of allowed values.

valueRanges

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

includeNull

Specify TRUE to include 'NULL' in the matched cells, FALSE to exclude 'NULL' values.

includeNA

Specify TRUE to include 'NA' in the matched cells, FALSE to exclude 'NA' values.

emptyCells

A word that specifies how empty cells are matched - must be one of "include" (default), "exclude" or "only".

outlineCells

A word that specifies how outline cells are matched - must be one of "include", "exclude" (default) or "only".

rowNumbers

A vector of row numbers that specify the rows or cells to constrain the search.

columnNumbers

A vector of column numbers that specify the columns or cells to constrain the search.

cellCoordinates

A list of two-element vectors that specify the coordinates of cells to constrain the search.

groups

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

rowGroups

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

columnGroups

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

rowColumnMatchMode

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

cells

A 'PivotCell' object or a list of 'PivotCell' objects to constrain the scope of the search.

lowN

Find the first N cells (ascending order, lowest values first).

highN

Find the last N cells (descending order, highest values first).

Returns

A list of 'PivotCell' objects.

---

Method findGroupColumnNumbers()

Find the column numbers associated with a specific data group or groups.

Usage

PivotCells$findGroupColumnNumbers(group = NULL, collapse = FALSE)

Arguments

group

A 'PivotDataGroup' in the column data groups (i.e. a column heading) or a list of column data groups..

collapse

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

Details

If 'group' is a list: If 'collapse' is 'FALSE', then a list of vectors is returned, if 'collapse' is 'TRUE', then a single combined vector is returned.

Returns

Either a vector of column numbers related to the single specified group or a list of vectors containing column numbers related to the specified groups.

---

Method findGroupRowNumbers()

Find the row numbers associated with a specific data group or groups.

Usage

PivotCells$findGroupRowNumbers(group = NULL, collapse = FALSE)

Arguments

group

A 'PivotDataGroup' in the row data groups (i.e. a row heading) or a list of row data groups.

collapse

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

Details

If 'group' is a list: If 'collapse' is 'FALSE', then a list of vectors is returned, if 'collapse' is 'TRUE', then a single combined vector is returned.

Returns

Either a vector of row numbers related to the single specified group or a list of vectors containing row numbers related to the specified groups.

---

Method getColumnWidths()

Retrieve the width (in characters) of the longest value in each column.

Usage

PivotCells$getColumnWidths()

Returns

A vector containing the length of the longest value in each column.

---

Method removeColumn()

Remove a column from the pivot table.

Usage

PivotCells$removeColumn(c = NULL, renumberGroups = TRUE)

Arguments

c

The column number. The first column is column 1, excluding the column(s) associated with row-headings.

renumberGroups

'TRUE' (default) to renumber the 'rowColumnNumber' property of the data groups after removing the row.

Details

This method removes both the related column group and cells.

Returns

No return value.

---

Method removeColumns()

Remove multiple column from the pivot table.

Usage

PivotCells$removeColumns(columnNumbers = NULL, renumberGroups = TRUE)

Arguments

columnNumbers

The column numbers. The first column is column 1, excluding the column(s) associated with row-headings.

renumberGroups

'TRUE' (default) to renumber the 'rowColumnNumber' property of the data groups after removing the row.

Details

This method removes both the related column groups and cells.

Returns

No return value.

---

Method removeRow()

Remove a row from the pivot table.

Usage

PivotCells$removeRow(r = NULL, renumberGroups = TRUE)

Arguments

r

The row number. The first row is row 1, excluding the row(s) associated with column-headings.

renumberGroups

'TRUE' (default) to renumber the 'rowColumnNumber' property of the data groups after removing the row.

Details

This method removes both the related row group and cells.

Returns

No return value.

---

Method removeRows()

Remove multiple rows from the pivot table.

Usage

PivotCells$removeRows(rowNumbers = NULL, renumberGroups = TRUE)

Arguments

rowNumbers

The row numbers. The first row is row 1, excluding the rows(s) associated with column-headings.

renumberGroups

'TRUE' (default) to renumber the 'rowColumnNumber' property of the data groups after removing the row.

Details

This method removes both the related row groups and cells.

Returns

No return value.

---

Method asMatrix()

Get a matrix containing all of the values from the body of the pivot table.

Usage

PivotCells$asMatrix(rawValue = TRUE)

Arguments

rawValue

'TRUE' (default) to populate the matrix with the numerical raw values, 'FALSE' to populate the matrix with the character formatted values.

Returns

A 'matrix' containing the values from the body of the pivot table.

---

Method asList()

Return the contents of this object as a list for debugging.

Usage

PivotCells$asList()

Returns

A list of various object properties.

---

Method asJSON()

Return the contents of this object as JSON for debugging.

Usage

PivotCells$asJSON()

Returns

A JSON representation of various object properties.

---

Method clone()

The objects of this class are cloneable with this method.

Usage

PivotCells$clone(deep = FALSE)

Arguments

deep

Whether to make a deep clone.

Examples

# This class should only be created by the pivot table.
# It is not intended to be created outside of the pivot table.

pivottabler documentation built on Oct. 1, 2023, 5:07 p.m.