A1. Appendix: Details

Chris Bailiss

2019-11-01

In This Vignette

Introduction

This appendix contains information that doesn’t fit in the other vignettes - typically miscellaneous topics or more detail on existing topics that would clutter the other vignettes.

Data Type Support

pivottabler supports any of the following data types for use either on row/column headings or as part of calculations in the cells of the pivot table:

The most common calculations based on logical, character, Date and POSIXct data types are min/max.

Formatting Options

A general introduction to formatting row/column headings can be found in the Data Groups vignette. A general introduction to formatting the results of calculations can be found in the Calculations vignette.

Four general methods are supported for formatting, depending on what is specified for the format parameter (for calculations) or dataFormat parameter (for data groups):

There are some small variations to the above, depending on the data type of the value that is being formatted:

Value Type character format list format
integer sprintf() format()
numerical sprintf() format()
logical sprintf() or custom - see note below. N/A
Date sprintf() or format() - see note below. format()
POSIXct sprintf() or format() - see note below. format()
character N/A N/A

Formatting logical values when format is a character value:

Formatting Date or POSIXct values when format is a character value:

Some examples of specifying formatting:

Value Type character format list format
integer "%i" e.g. 12 list(digits=4, nsmall=2) e.g. 123.00
numerical "%.1f" e.g. 12.3 list(digits=4, nsmall=2) e.g. 12.35
logical c("No", "Yes", "N/A") e.g. Yes N/A
Date "%d %b %Y" e.g. 04 Mar 2012 list("%d %b %Y") e.g. 04 Mar 2012
POSIXct "%d %b %Y %H:%M" e.g. 04 Mar 2012 17:15 list("%d %b %Y %H:%M") e.g. 04 Mar 2012 17:15
character N/A N/A

Handling Illegal Variable Names

pivottabler supports working with illegal data frame column names and illegal calculation names (e.g. including spaces or symbols such as dash, plus, dollar, etc).

Illegal names must be wrapped in back-ticks in summarise expressions and calculation expressions.

...
pt$addColumnDataGroups("Sale Item")
pt$defineCalculation(calculationName="Total Sales",
                     summariseExpression="sum(`Sale Amount`)")
pt$defineCalculation(type="calculation", basedOn=c("Total Sales", "Sale Count"),  
                     format="%.1f", calculationName="Avg Sale Amount", 
                     calculationExpression="values$`Total Sales`/values$`Sale Count`")
...

Output of NA, NaN, -Inf and Inf

The pt$getHtml(...), pt$saveHtml(...), pt$renderPivot(...), pt$getLatex(...) and pt$writeToExcelWorksheet(...) functions all support an exportOptions list parameter that provides control over how NA, NaN, -Inf and Inf values in R are output.

skipNegInf=TRUE, skipPosInf=TRUE, skipNA=TRUE, skipNaN=TRUE specify that these values are exported as blanks.

exportNegInfAs="-Infinity",exportPosInfAs="Infinity",exportNAAs="No Data",exportNaNAs="Not a Number" specify alternative values to output.

Example of exporting a pivot table using the default values and replaced values:

someData <- data.frame(Colour=c("Red", "Yellow", "Green", "Blue", "White", "Black"),
                       SomeNumber=c(1, 2, NA, NaN, -Inf, Inf))
library(pivottabler)
pt <- PivotTable$new()
pt$addData(someData)
pt$addRowDataGroups("Colour")
pt$defineCalculation(calculationName="Total", summariseExpression="sum(SomeNumber)")
pt$evaluatePivot()
pt$renderPivot()
pt$renderPivot(exportOptions=list(skipNegInf=TRUE, skipPosInf=TRUE, skipNA=TRUE, skipNaN=TRUE))
pt$renderPivot(exportOptions=list(exportNegInfAs="-Infinity", exportPosInfAs="Infinity",
                                  exportNAAs="Nothing", exportNaNAs="Not a Number"))

Styling Reference

For an overview of styling pivot tables see the Styling vignette.

The table below details the common styling properties that are supported.

When outputting to HTML, any valid CSS styling can be used, even if not listed below.

When outputting to Excel, only the styling properties listed below are supported - either the CSS or XL properties can be used - see the Excel Export vignette for more information.

CSS Property XL Property XL Example Notes
font-family xl-font-name Arial Only the first CSS font is used in Excel.
font-size xl-font-size 12 In Points (4-72). See below for CSS units.
font-weight xl-bold normal or bold XL bold is CSS font-weight >= 600.
font-style xl-italic normal or italic italic and oblique map to italic.
text-decoration xl-underline normal or underline
text-decoration xl-strikethrough normal or strikethrough
background-color xl-fill-color #FF0000 See below for supported CSS colours.
color xl-text-color #00FF00 See below for supported CSS colours.
text-align xl-h-align left or center or right
vertical-align xl-v-align top or middle or bottom
white-space xl-wrap-text normal or wrap
xl-text-rotation 90 0 to 359, or 255 for vertical text.
xl-indent 20 0 to 250.
border xl-border thin black See below for supported CSS border values.
border-left xl-border-left thin black See below for supported CSS border values.
border-right xl-border-right thin black See below for supported CSS border values.
border-top xl-border-top thin black See below for supported CSS border values.
border-bottom xl-border-bottom thin black See below for supported CSS border values.
xl-min-column-width 50 0 to 255.
xl-min-row-height 45 0 to 400.
xl-value-format #,###.00 See notes below for full details.

Excel Output Restrictions:

Note that the following CSS properties are NOT supported when outputting to Excel:

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