#

4. Outputs

#### Chris Bailiss

#### 2017-10-02

## In This Vignette

- Results of Pivot Calculations
- Example Pivot Table
- Results as Plain Text
- Results as HTML
- Results as Latex
- Results in Excel
- Results as an R Matrix
- Results as an R Data Frame
- Further Reading

## 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 outputing 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" rowspan="1" colspan="1">&nbsp;</th>
        <th class="ColumnHeader" colspan="1">DMU</th>
        <th class="ColumnHeader" colspan="1">EMU</th>
        <th class="ColumnHeader" colspan="1">HST</th>
        <th class="ColumnHeader" colspan="1">Total</th>
      </tr>
      <tr>
        <th class="RowHeader" rowspan="1">Arriva Trains Wales</th>
        <td class="Cell">3909</td>
        <td class="Cell"></td>
        <td class="Cell"></td>
        <td class="Cell">3909</td>
      </tr>
      <tr>
        <th class="RowHeader" rowspan="1">CrossCountry</th>
        <td class="Cell">22196</td>
        <td class="Cell"></td>
        <td class="Cell">732</td>
        <td class="Cell">22928</td>
      </tr>
      <tr>
        <th class="RowHeader" rowspan="1">London Midland</th>
        <td class="Cell">11229</td>
        <td class="Cell">37050</td>
        <td class="Cell"></td>
        <td class="Cell">48279</td>
      </tr>
      <tr>
        <th class="RowHeader" rowspan="1">Virgin Trains</th>
        <td class="Cell">2137</td>
        <td class="Cell">6457</td>
        <td class="Cell"></td>
        <td class="Cell">8594</td>
      </tr>
      <tr>
        <th class="RowHeader" rowspan="1">Total</th>
        <td class="Cell">39471</td>
        <td class="Cell">43507</td>
        <td class="Cell">732</td>
        <td class="Cell">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; }

## 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      : int  3909 NA NA 3909 22196 NA 732 22928 11229 37050 ...

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

## 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