1. Data Groups

Chris Bailiss

2019-08-05

In This Vignette

Data Group Terminology

Data groups form the column headers and row headers in a pivot table. Typically, the distinct values of a variable form a set of headings (i.e. a set of data groups). Consider the following pivot table:

library(pivottabler)
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$addColumnDataGroups("TrainCategory")
pt$addColumnDataGroups("PowerType")
pt$addRowDataGroups("TOC")
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
pt$renderPivot()

In this pivot table, there are three sets of data groups:

The data groups form a hierarchy. A data group can have child data groups. In the pivot table above, there are three column data groups at the first level, “Express Passenger”, “Ordinary Passenger” and “Total”. The “Express Passenger” group has four child data groups for the power types “DMU”, “EMU”, “HST” and “Total”. The “Ordinary Passenger” data group has three child groups and the “Total” data group has one child group (a blank group). The second level of the column data groups is thus formed of (4+3+1=) eight data groups.

Adding data groups to a pivot table

There are a few different ways to add data groups to a pivot table.

Adding data groups based on the data

The easiest way to add data groups to a pivot table is to simply generate data groups based on the distinct values that exist for a particular variable (i.e. in a particular column in a data frame). This is the method used in the pivot table above.

Considering parent data groups

By default, when adding additional levels to a pivot table after the first level (i.e. for level 2, 3, etc.), the filter criteria coming from parent data groups is considered. For example, in the pivot table above, there are “Express Passenger” trains with a power type of “HST”, but no “Ordinary Passenger” trains with a power type of “HST”. So while a “HST” data group exists under “Express Passenger”, no “HST” data group exists underneath “Ordinary Passenger”.

Ignoring parent data groups

Sometimes, a more uniform layout is preferred, i.e. in the above example, we would like “HST” to be present underneath “Ordinary Passenger” as well. This can be accomplished by setting the argument onlyCombinationsThatExist to FALSE.

library(pivottabler)
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$addColumnDataGroups("TrainCategory")
pt$addColumnDataGroups("PowerType", onlyCombinationsThatExist=FALSE)
pt$addRowDataGroups("TOC")
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
pt$renderPivot()

Adding data groups explicitly

It is also possible to add data values explicitly, by passing a list of values as the explicitListOfValues argument. When an explicit list of values is specified, the pivot table does not sort the values. For example, if we wished to ignore the “HST” power type completely:

library(pivottabler)
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$addColumnDataGroups("TrainCategory")
pt$addColumnDataGroups("PowerType", fromData=FALSE, explicitListOfValues=list("DMU", "EMU"))
pt$addRowDataGroups("TOC")
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
pt$renderPivot()

Visual Totals

In the above example, some of the totals now do not appear to reconcile, e.g. “CrossCountry DMU” (22133) + “CrossCountry EMU” (0) does not equal “CrossCountry Total” (22865). The difference is the “CrossCountry HST” (732) data group that has been omitted. This may or may not be desirable, depending on how the pivot table is introduced, the title that is used, etc.

If it is desirable for the totals to reconcile, then visual totals can be enabled using the visualTotals argument:

library(pivottabler)
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$addColumnDataGroups("TrainCategory")
pt$addColumnDataGroups("PowerType", fromData=FALSE, explicitListOfValues=list("DMU", "EMU"), visualTotals=TRUE)
pt$addRowDataGroups("TOC")
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
pt$renderPivot()

Note that finding total/non-total data groups/cells with the findRowDataGroups(), findColumnDataGroups() and findCells() functions won’t work when visual totals are enabled. See the Finding and Formatting vignette for more details on these functions.

Adding data groups that combine values

By specifying values explicitly, it is also possible to combine multiple values into one data group. For example, if the primary focus of an analysis is the two train operating companies operating the most trains (“London Midland” and “CrossCountry”), then “Arrival Trains Wales” and “Virgin” can be combined:

library(pivottabler)
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$addColumnDataGroups("TrainCategory")
pt$addColumnDataGroups("PowerType")
pt$addRowDataGroups("TOC", fromData=FALSE, explicitListOfValues=list(
    "London Midland", "CrossCountry", c("Arriva Trains Wales", "Virgin Trains")))
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
pt$renderPivot()

Should an alternative caption be preferred for the combined group, it can be specified by setting the name of the list element. Following the example above, they could simply be labelled as “Other”:

library(pivottabler)
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$addColumnDataGroups("TrainCategory")
pt$addColumnDataGroups("PowerType")
pt$addRowDataGroups("TOC", fromData=FALSE, explicitListOfValues=list(
  "London Midland", "CrossCountry", "Other"=c("Arriva Trains Wales", "Virgin Trains")))
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
pt$renderPivot()

More complex layouts of data groups

Adding data groups one-at-a-time for fine-grained control to create a pivot table with an irregular layout is also possible. See the Irregular Layout vignette for more details.

Formatting data groups

Each data group has two values: A sortValue that is used to order the group within the set of groups. The sortValue is the same data type as the variable the data group is based on. The caption is the value that is displayed in the pivot table. The data type of the caption is typically character.

In all of the examples above, the data groups are text values (i.e. are based on data frame columns of data type character). When data groups are numerical or date/time values, then typically some form of formatting is required. Formatting generates a caption for each data group, i.e. a more readable version of the underlying data group value.

For example, plotting the total number of trains per month for each train operation company:

# derive the date of each train (from the arrival/dep times),
# then the month of each train from the date of each train
library(dplyr)
library(lubridate)
library(pivottabler)
trains <- mutate(bhmtrains, 
   GbttDate=if_else(is.na(GbttArrival), GbttDeparture, GbttArrival),
   GbttMonth=make_date(year=year(GbttDate), month=month(GbttDate), day=1))

pt <- PivotTable$new()
pt$addData(trains)
pt$addColumnDataGroups("GbttMonth")
pt$addColumnDataGroups("PowerType")
pt$addRowDataGroups("TOC")
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
pt$renderPivot()

In the example above, the month headings are not explicitly formatted. The example below shows how to format these headings using the dataFormat argument of addColumnDataGroups():

# derive the date of each train (from the arrival/dep times),
# then the month of each train from the date of each train
library(dplyr)
library(lubridate)
library(pivottabler)
trains <- mutate(bhmtrains, 
   GbttDate=if_else(is.na(GbttArrival), GbttDeparture, GbttArrival),
   GbttMonth=make_date(year=year(GbttDate), month=month(GbttDate), day=1))

pt <- PivotTable$new()
pt$addData(trains)
pt$addColumnDataGroups("GbttMonth", dataFormat=list(format="%B %Y"))
pt$addColumnDataGroups("PowerType")
pt$addRowDataGroups("TOC")
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
pt$renderPivot()

A number of different approaches to formatting are supported:

Some formatting behaviour depends on the data type of the value being formatted. For details see the Appendix: Details vignette.

An example of using a custom R function to accomplish the same formatting is:

# derive the date of each train (from the arrival/dep times), then the month of each train from the date of each train
library(dplyr)
library(lubridate)
library(pivottabler)
trains <- mutate(bhmtrains, 
   GbttDate=if_else(is.na(GbttArrival), GbttDeparture, GbttArrival),
   GbttMonth=make_date(year=year(GbttDate), month=month(GbttDate), day=1))

# define a custom formatting function
formatDate <- function(x) {
  base::format(x, format="%B %Y")
}

pt <- PivotTable$new()
pt$addData(trains)
pt$addColumnDataGroups("GbttMonth", dataFormat=formatDate)
pt$addColumnDataGroups("PowerType")
pt$addRowDataGroups("TOC")
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
pt$renderPivot()

Sorting data groups

By default, data groups are sorted into ascending order based on the data group value, e.g.:

library(pivottabler)
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$addColumnDataGroups("TrainCategory")
pt$addColumnDataGroups("PowerType")
pt$addRowDataGroups("TOC")
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
pt$renderPivot()

In the above example the train operating companies are sorted into ascending order alphabetically (Arriva, CrossCountry, London, Virgin). Similarly for the train category (Express, Ordinary) and power type (DMU, EMU HST). The totals appear in a fixed position, irrespective of the data group sorting.

The data groups can be sorted into descending order using dataSortOrder argument, e.g. to reverse the order of the train operating companies:

library(pivottabler)
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$addColumnDataGroups("TrainCategory")
pt$addColumnDataGroups("PowerType")
pt$addRowDataGroups("TOC", dataSortOrder="desc")
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
pt$renderPivot()

The data groups can also be sorted using the sortColumnDataGroups or sortRowDataGroups methods. Using these methods, data groups can be sorted in three different ways:

For example, to sort the train operating companies into descending order by the total number of trains:

library(pivottabler)
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$addColumnDataGroups("TrainCategory")
pt$addColumnDataGroups("PowerType")
pt$addRowDataGroups("TOC", dataSortOrder="desc")
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
pt$sortRowDataGroups(levelNumber=1, orderBy="calculation", sortOrder="desc")
pt$renderPivot()

The pivot is sorted using the totals for each data group. In the pivot table above, the London Midland train operating company is in first place with 48,279 trains. CrossCountry is in second place with 22,928 trains, etc.

Since the pivot table above only contains one calculation, it was not necessary to specify the name of the calculation when sorting the row groups. If a pivot table contains more than one calculation, and there is a wish to sort by a calculation other than the first calculation that was defined, then the calculation can be specified by using the calculationName argument when calling either sortColumnDataGroups or sortRowDataGroups.

When sorting the headings at the second, third, etc. level, the sort does not break the structure coming from the parent data groups, i.e. a separate sort is performed within each parent group, e.g.

library(pivottabler)
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$addColumnDataGroups("TrainCategory")
pt$addColumnDataGroups("PowerType")
pt$addRowDataGroups("TOC")
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
pt$sortColumnDataGroups(levelNumber=2, orderBy="calculation", sortOrder="desc")
pt$renderPivot()

In the example above, one sort was carried out for the power type groups under “Express Passenger” (“DMU” in first place with 32,987 trains, “EMU” second with 15,306 trains, etc.) and a separate sort was carried out for the power type groups under “Ordinary Passenger” (“EMU” first with 28,201 trains, “DMU” second with 6,484 trains, etc.).

The sortColumnDataGroups or sortRowDataGroups can also be used to reset the sort order back to an alphabetic order based on the value of each of the groups, e.g.

library(pivottabler)
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$addColumnDataGroups("TrainCategory")
pt$addColumnDataGroups("PowerType")
pt$addRowDataGroups("TOC")
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
# the follow line sorts the data based on the totals
pt$sortColumnDataGroups(levelNumber=2, orderBy="calculation", sortOrder="desc")
# the following line resets the sort order back, i.e. removes the sort applied in the line above
pt$sortColumnDataGroups(levelNumber=2, orderBy="value", sortOrder="asc")
pt$renderPivot()

This can be useful when building a pivot table interactively, to quickly reset the sort order without needing to rebuild the entire pivot table.

Further Reading

The full set of vignettes is:

  1. Introduction
  2. Data Groups
  3. Calculations
  4. Outputs
  5. Latex Output
  6. Styling
  7. Finding and Formatting
  8. Cell Context
  9. Irregular Layout
  10. Performance
  11. Shiny
  12. Excel Export
  13. Appendix: Details
  14. Appendix: Calculations
  15. Appendix: Class Overview