In This Vignette

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


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:

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.

pt <- PivotTable$new()
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()")

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

pt <- PivotTable$new()
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")

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

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))
pt <- PivotTable$new()
pt$defineCalculation(calculationName="Total", summariseExpression="sum(SomeNumber)")
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.