A1. Appendix: Details

In This Vignette

Introduction
Data Type Support
Formatting Options
Setting Defaults
Handling Illegal Variable Names
Output of NA, NaN, -Inf and Inf
Styling Reference

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:

integer
numeric
logical
Date
POSIXct
character

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

If format is a character value, then pivottabler invokes base::sprintf() with the specified format.
If format is a list, then pivottabler invokes base::format(), where the elements in the list become arguments in the function call.
If format is an R function, then this is invoked for each value.
If format is not specified, then base::as.character() is invoked to provide a default formatted value.

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:

If format is a character vector of length 1, then sprintf() is invoked to format the value.
If format is a character vector of length 2, then format[1] is used as the formatted value for FALSE, format[2] is used as the formatted value for TRUE and the character value "NA" is used as the formatted value for NA.
If format is a character vector of length 3, then format[1] is used as the formatted value for FALSE, format[2] is used as the formatted value for TRUE and format[3] is used as the formatted value for NA.

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

If format is one of %d, %i, %o, %x or %X then base::sprintf() is used to format the value.
Otherwise base::format() is used.

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

Setting Defaults

Sometimes the same parameter value is specified multiple times when adding data groups, e.g. addTotal=FALSE.

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

To make the script more succinct, it is possible to specify this as a default:

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

This produces the same output as above.

Defaults can be set for the following functions and parameters:

`pt$addColumnDataGroups()`	`pt$addRowDataGroups()`
`addTotal`	`addTotal`
`expandExistingTotals`	`expandExistingTotals`
`visualTotals`	`visualTotals`
`totalPosition`	`totalPosition`
`totalCaption`	`totalCaption`
	`outlineBefore`
	`outlineAfter`
	`outlineTotal`

Once set using pt$setDefault(), the defaults will be automatically used for all arguments listed above except outlineBefore, outlineAfter and outlineTotal. For these three, they must be activated by specifying an argument value of TRUE when calling pt$addRowDataGroups(), e.g. outlineBefore=TRUE.

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:

For CSS font-size, only the following units are supported: in, cm, mm, pt, pc, px, em, %.
For CSS background-color, CSS color and border colours, only hex colours, named colours, RGB() and RGBA() values are supported. HLS() and HLSA() values are not supported. Examples of supported CSS values: #0080FF, rgb(0, 128, 255), rgba(0, 128, 255, 0.5), red, black, darkolivegreen, etc.
For borders, either use CSS border / xl-border to specify the same border on all four sides of each cell. Or use the side-specific versions, e.g. CSS border-left / xl-border-left.
For XL border styles, the allowed range of values is: none thin, medium, dashed, dotted, thick, double, hair, mediumDashed, dashDot, mediumDashDot, dashDotDot, mediumDashDotDot, slantDashDot
For xl-value-format, the following constants are supported: GENERAL, NUMBER, CURRENCY, ACCOUNTING, DATE, LONGDATE, TIME, PERCENTAGE, FRACTION, SCIENTIFIC, TEXT, COMMA. Alternatively, custom format strings can be specified. For date/datetime, specify a combination of d, m, y, e.g. dd/mm/yyyy. For numeric rounding use 0.00 etc.

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

font - use font-family, font-size, etc. instead
Border side specific properties, e.g. border-left-style, border-top-color, etc.

Chris Bailiss

2023-10-01