Results of Pivot Calculations

A pivottabler pivot table object has a fairly complex internal structure - containing two trees of data groups (the row groups and the column groups) plus a set of cells linked to the data groups.

The pivottabler package supports outputting a pivot table in a number of different forms:

A htmlwidget for R-Studio - using pt$renderPivot() to render the pivot table into the “Viewer” tab in R-Studio,
A htmlwidget for Shiny applications - using pivottabler(pt) to render the pivot table into the Shiny app,
As HTML - using either:
- pt$getHtml() to retrieve a character variable containing HTML, or
- pt$saveHtml() to save the HTML to a file.
As Latex - using pt$getLatex() to retrieve a character variable containing Latex.
As plain text - using pt to output to the console or pt$asCharacter to retrieve as a character value.
Into an Excel Worksheet.

Sometimes it is desirable to retrieve the pivot table results as a more standard data type that is easier to work with in R code. A pivot table can be converted to either a matrix or a data frame. Neither data type is a perfect representation of a pivot table - which option is better will depend upon your use case.

Example Pivot Table

The following pivot table is used as the basis of the examples in the rest of this vignette:

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

Results as Plain Text

A pivot table is outputted to the console as plain text simply by using pt:

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

                     DMU    EMU    HST  Total  
Arriva Trains Wales   3909               3909  
CrossCountry         22196         732  22928  
London Midland       11229  37050       48279  
Virgin Trains         2137   6457        8594  
Total                39471  43507  732  83710

Alternatively, the plain text representation of the pivot table can be retrieved as a character value using pt$asCharacter.

pt and pt$asString show the current state of the pivot table. If the pivot table has not been evaluated (either by using pt$evaluatePivot() or pt$renderPivot()) then pt and pt$asCharacter will return the headings only:

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

                     DMU  EMU  HST  Total  
Arriva Trains Wales  
CrossCountry         
London Midland       
Virgin Trains        
Total

Results as HTML

Rendering a htmlwidget

A pivot table is outputted as a htmlwidget simply by calling pt$renderPivot(). There are numerous examples throughout these vignettes, including the example directly above.

For outputting as a htmlwidget in a Shiny application, use pivottabler(pt).

Retrieving HTML

To retrieve the HTML of a pivot table, use pt$getHtml(). This returns a list of html tag objects built using the htmltools package. This object can be converted to a simple character variable using as.character() or as illustrated below. The CSS declarations for a pivot table can be retrieved using pt$getCss() - also illustrated below.

library(pivottabler)
library(htmltools)
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$addColumnDataGroups("PowerType")
pt$addRowDataGroups("TOC")
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
pt$evaluatePivot()
cat(paste(pt$getHtml(), sep="", collapse="\n"))

<table class="Table">
  <tr>
    <th class="RowHeader">&nbsp;</th>
    <th class="ColumnHeader">DMU</th>
    <th class="ColumnHeader">EMU</th>
    <th class="ColumnHeader">HST</th>
    <th class="ColumnHeader">Total</th>
  </tr>
  <tr>
    <th class="RowHeader">Arriva Trains Wales</th>
    <td class="Cell">3909</td>
    <td class="Cell"></td>
    <td class="Cell"></td>
    <td class="Total">3909</td>
  </tr>
  <tr>
    <th class="RowHeader">CrossCountry</th>
    <td class="Cell">22196</td>
    <td class="Cell"></td>
    <td class="Cell">732</td>
    <td class="Total">22928</td>
  </tr>
  <tr>
    <th class="RowHeader">London Midland</th>
    <td class="Cell">11229</td>
    <td class="Cell">37050</td>
    <td class="Cell"></td>
    <td class="Total">48279</td>
  </tr>
  <tr>
    <th class="RowHeader">Virgin Trains</th>
    <td class="Cell">2137</td>
    <td class="Cell">6457</td>
    <td class="Cell"></td>
    <td class="Total">8594</td>
  </tr>
  <tr>
    <th class="RowHeader">Total</th>
    <td class="Total">39471</td>
    <td class="Total">43507</td>
    <td class="Total">732</td>
    <td class="Total">83710</td>
  </tr>
</table>

cat(pt$getCss())

.Table {border-collapse: collapse; }
.ColumnHeader {font-family: Arial; font-size: 0.75em; padding: 2px; border: 1px solid lightgray; vertical-align: middle; text-align: center; font-weight: bold; background-color: #F2F2F2; }
.RowHeader {font-family: Arial; font-size: 0.75em; padding: 2px 8px 2px 2px; border: 1px solid lightgray; vertical-align: middle; text-align: left; font-weight: bold; background-color: #F2F2F2; }
.Cell {font-family: Arial; font-size: 0.75em; padding: 2px 2px 2px 8px; border: 1px solid lightgray; vertical-align: middle; text-align: right; }
.Total {font-family: Arial; font-size: 0.75em; padding: 2px 2px 2px 8px; border: 1px solid lightgray; vertical-align: middle; text-align: right; }

Results as Latex

Please see the Latex Output vignette.

Results in Excel

Please see the Excel Export vignette.

Results as an R Matrix

Converting a pivot table to a matrix can be accomplished as follows:

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

     [,1]                  [,2]    [,3]    [,4]  [,5]   
[1,] ""                    "DMU"   "EMU"   "HST" "Total"
[2,] "Arriva Trains Wales" "3909"  ""      ""    "3909" 
[3,] "CrossCountry"        "22196" ""      "732" "22928"
[4,] "London Midland"      "11229" "37050" ""    "48279"
[5,] "Virgin Trains"       "2137"  "6457"  ""    "8594" 
[6,] "Total"               "39471" "43507" "732" "83710"

If only the cell values are required, the headings can be removed from the matrix by setting the includeHeaders parameter to FALSE.

The rawValue parameter specifies that the matrix should contain the numerical result values, not the formatted values.

library(pivottabler)
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$addColumnDataGroups("PowerType")
pt$addRowDataGroups("TOC")
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
pt$evaluatePivot()
pt$asMatrix(includeHeaders=FALSE, rawValue=TRUE)

      [,1]  [,2] [,3]  [,4]
[1,]  3909    NA   NA  3909
[2,] 22196    NA  732 22928
[3,] 11229 37050   NA 48279
[4,]  2137  6457   NA  8594
[5,] 39471 43507  732 83710

When there are multiple levels of headers, by default the column headers are not repeated:

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()

pt$asMatrix()

     [,1]                  [,2]                [,3]    [,4]  [,5]   
[1,] ""                    "Express Passenger" ""      ""    ""     
[2,] ""                    "DMU"               "EMU"   "HST" "Total"
[3,] "Arriva Trains Wales" "3079"              ""      ""    "3079" 
[4,] "CrossCountry"        "22133"             ""      "732" "22865"
[5,] "London Midland"      "5638"              "8849"  ""    "14487"
[6,] "Virgin Trains"       "2137"              "6457"  ""    "8594" 
[7,] "Total"               "32987"             "15306" "732" "49025"
     [,6]                 [,7]    [,8]    [,9]   
[1,] "Ordinary Passenger" ""      ""      "Total"
[2,] "DMU"                "EMU"   "Total" ""     
[3,] "830"                ""      "830"   "3909" 
[4,] "63"                 ""      "63"    "22928"
[5,] "5591"               "28201" "33792" "48279"
[6,] ""                   ""      ""      "8594" 
[7,] "6484"               "28201" "34685" "83710"

However, the repeatHeaders parameter can be used to specify repeating headings:

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

     [,1]                  [,2]                [,3]               
[1,] ""                    "Express Passenger" "Express Passenger"
[2,] ""                    "DMU"               "EMU"              
[3,] "Arriva Trains Wales" "3079"              ""                 
[4,] "CrossCountry"        "22133"             ""                 
[5,] "London Midland"      "5638"              "8849"             
[6,] "Virgin Trains"       "2137"              "6457"             
[7,] "Total"               "32987"             "15306"            
     [,4]                [,5]                [,6]                
[1,] "Express Passenger" "Express Passenger" "Ordinary Passenger"
[2,] "HST"               "Total"             "DMU"               
[3,] ""                  "3079"              "830"               
[4,] "732"               "22865"             "63"                
[5,] ""                  "14487"             "5591"              
[6,] ""                  "8594"              ""                  
[7,] "732"               "49025"             "6484"              
     [,7]                 [,8]                 [,9]   
[1,] "Ordinary Passenger" "Ordinary Passenger" "Total"
[2,] "EMU"                "Total"              ""     
[3,] ""                   "830"                "3909" 
[4,] ""                   "63"                 "22928"
[5,] "28201"              "33792"              "48279"
[6,] ""                   ""                   "8594" 
[7,] "28201"              "34685"              "83710"

Results as an R Data Frame

Two different functions can be used to convert a pivot table to a data frame. The asDataFrame() function returns a data frame with a roughly similar layout to the pivot table, e.g. a pivot table with a body consisting of 10 rows and 2 columns will result in a data frame also containing 10 rows and 2 columns. The asTidyDataFrame() function returns a data frame consisting of one row for every cell in the body of the pivot table, e.g. a pivot table with a body consisting of 10 rows and 2 columns will result in a data frame containing 20 rows.

Examples of both functions are given below.

The `asDataFrame()` function

The example pivot table converts as follows:

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

                      DMU   EMU HST Total
Arriva Trains Wales  3909    NA  NA  3909
CrossCountry        22196    NA 732 22928
London Midland      11229 37050  NA 48279
Virgin Trains        2137  6457  NA  8594
Total               39471 43507 732 83710

str(df)

'data.frame':   5 obs. of  4 variables:
 $ DMU  : int  3909 22196 11229 2137 39471
 $ EMU  : int  NA NA 37050 6457 43507
 $ HST  : int  NA 732 NA NA 732
 $ Total: int  3909 22928 48279 8594 83710

Data frames can have at most one name for each row and column. Therefore, when there are multiple levels of headers in the pivot table, the captions are concatenated into a single value for each row and column:

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

                    Express Passenger DMU Express Passenger EMU
Arriva Trains Wales                  3079                    NA
CrossCountry                        22133                    NA
London Midland                       5638                  8849
Virgin Trains                        2137                  6457
Total                               32987                 15306
                    Express Passenger HST Express Passenger Total
Arriva Trains Wales                    NA                    3079
CrossCountry                          732                   22865
London Midland                         NA                   14487
Virgin Trains                          NA                    8594
Total                                 732                   49025
                    Ordinary Passenger DMU Ordinary Passenger EMU
Arriva Trains Wales                    830                     NA
CrossCountry                            63                     NA
London Midland                        5591                  28201
Virgin Trains                           NA                     NA
Total                                 6484                  28201
                    Ordinary Passenger Total Total 
Arriva Trains Wales                      830   3909
CrossCountry                              63  22928
London Midland                         33792  48279
Virgin Trains                             NA   8594
Total                                  34685  83710

The space character is the default character used to combine headers as seen above. This can easily be changed, e.g. to a pipe character:

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

                    Express Passenger|DMU Express Passenger|EMU
Arriva Trains Wales                  3079                    NA
CrossCountry                        22133                    NA
London Midland                       5638                  8849
Virgin Trains                        2137                  6457
Total                               32987                 15306
                    Express Passenger|HST Express Passenger|Total
Arriva Trains Wales                    NA                    3079
CrossCountry                          732                   22865
London Midland                         NA                   14487
Virgin Trains                          NA                    8594
Total                                 732                   49025
                    Ordinary Passenger|DMU Ordinary Passenger|EMU
Arriva Trains Wales                    830                     NA
CrossCountry                            63                     NA
London Midland                        5591                  28201
Virgin Trains                           NA                     NA
Total                                 6484                  28201
                    Ordinary Passenger|Total Total|
Arriva Trains Wales                      830   3909
CrossCountry                              63  22928
London Midland                         33792  48279
Virgin Trains                             NA   8594
Total                                  34685  83710

The `asTidyDataFrame()` function

The example pivot table converts as follows:

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

                      DMU   EMU HST Total
Arriva Trains Wales  3909    NA  NA  3909
CrossCountry        22196    NA 732 22928
London Midland      11229 37050  NA 48279
Virgin Trains        2137  6457  NA  8594
Total               39471 43507 732 83710

df <- pt$asTidyDataFrame()
str(df)

'data.frame':   20 obs. of  11 variables:
 $ rowNumber           : int  1 1 1 1 2 2 2 2 3 3 ...
 $ columnNumber        : int  1 2 3 4 1 2 3 4 1 2 ...
 $ isTotal             : logi  FALSE FALSE FALSE TRUE FALSE FALSE ...
 $ RowLevel01          : Factor w/ 5 levels "Arriva Trains Wales",..: 1 1 1 1 2 2 2 2 3 3 ...
 $ ColumnLevel01       : Factor w/ 4 levels "DMU","EMU","HST",..: 1 2 3 4 1 2 3 4 1 2 ...
 $ TOC                 : Factor w/ 5 levels "Arriva Trains Wales",..: 1 1 1 1 2 2 2 2 3 3 ...
 $ PowerType           : Factor w/ 4 levels "DMU","EMU","HST",..: 1 2 3 4 1 2 3 4 1 2 ...
 $ calculationName     : Factor w/ 1 level "TotalTrains": 1 1 1 1 1 1 1 1 1 1 ...
 $ calculationGroupName: Factor w/ 1 level "default": 1 1 1 1 1 1 1 1 1 1 ...
 $ rawValue            : int  3909 NA NA 3909 22196 NA 732 22928 11229 37050 ...
 $ formattedValue      : Factor w/ 13 levels "11229","2137",..: 6 NA NA 6 3 NA 11 4 1 5 ...

head(df)

  rowNumber columnNumber isTotal          RowLevel01 ColumnLevel01
1         1            1   FALSE Arriva Trains Wales           DMU
2         1            2   FALSE Arriva Trains Wales           EMU
3         1            3   FALSE Arriva Trains Wales           HST
4         1            4    TRUE Arriva Trains Wales         Total
5         2            1   FALSE        CrossCountry           DMU
6         2            2   FALSE        CrossCountry           EMU
                  TOC PowerType calculationName calculationGroupName
1 Arriva Trains Wales       DMU     TotalTrains              default
2 Arriva Trains Wales       EMU     TotalTrains              default
3 Arriva Trains Wales       HST     TotalTrains              default
4 Arriva Trains Wales        NA     TotalTrains              default
5        CrossCountry       DMU     TotalTrains              default
6        CrossCountry       EMU     TotalTrains              default
  rawValue formattedValue
1     3909           3909
2       NA           <NA>
3       NA           <NA>
4     3909           3909
5    22196          22196
6       NA           <NA>

By default the generated pivot table contains columns for both the captions of the data groups and the variables/values that the data groups represent. Each of these sets of columns can be removed from the data frame by setting includeGroupCaptions=FALSE or includeGroupValues=FALSE respectively.

Where a data group represents multiple values, those values are concatenated and returned in a single column in the data frame. Again, the separator between the values can be changed, e.g. by specifying separator="|".

Results as a basictabler Table

The asBasicTable() function allows a pivot table to be converted to a basic table - from the basictabler package.

The basictabler package allows free-form tables to be constructed, in contrast to pivottabler which creates pivot tables with relatively fixed structures. pivottabler contains calculation logic - to calculate the values of cells within the pivot table. basictabler contains no calculation logic - cell values must be provided either from a data frame, row-by-row, column-by-column or cell-by-cell.

Converting a pivot table to a basic table allows the structure of pivot tables to be altered after they have been created, e.g.

library(pivottabler)
library(dplyr)
library(lubridate)
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$evaluatePivot()

# convert the pivot table to a basic table, insert a new row, merge cells and highlight
bt <- pt$asBasicTable()
bt$cells$insertRow(5)
bt$cells$setCell(5, 2, rawValue="The values below are significantly higher than expected.", 
                 styleDeclarations=list("text-align"="left", "background-color"="yellow",
                                        "font-weight"="bold", "font-style"="italic"))
bt$mergeCells(rFrom=5, cFrom=2, rSpan=1, cSpan=13)
bt$setStyling(rFrom=6, cFrom=2, rTo=6, cTo=14, 
              declarations=list("text-align"="left", "background-color"="yellow"))
bt$renderTable()

Outputs

Chris Bailiss

2019-03-20

In This Vignette

Results of Pivot Calculations

Example Pivot Table

Results as Plain Text

Results as HTML

Rendering a htmlwidget

Retrieving HTML

Results as Latex

Results in Excel

Results as an R Matrix

Results as an R Data Frame

The `asDataFrame()` function

The `asTidyDataFrame()` function

Results as a basictabler Table

Further Reading

Outputs

Chris Bailiss

2019-03-20

In This Vignette

Results of Pivot Calculations

Example Pivot Table

Results as Plain Text

Results as HTML

Rendering a htmlwidget

Retrieving HTML

Results as Latex

Results in Excel

Results as an R Matrix

Results as an R Data Frame

The asDataFrame() function

The asTidyDataFrame() function

Results as a basictabler Table

Further Reading

The `asDataFrame()` function

The `asTidyDataFrame()` function