r - Replace missing value with character from 'pivottabler' package

Question

I am using 'pivottabler' package to tabulate a crosstable. However there are some missing values existed in the table.

I would like to know if there is an option to replace the missing values by something like '-'.

question from:https://stackoverflow.com/questions/65862697/replace-missing-value-with-character-from-pivottabler-package

Answer 1

Summary

There are two different options - depending on the scenario you have:

1. If the data group combination has no matching rows in the data frame, use the noDataCaption argument.
2. If rows do exist for the data group combination in the data frame but the values are NA, use the exportOptions argument.

Option 1 - noDataCaption

Use the noDataCaption argument of the defineCalculation() function.

More information: http://pivottabler.org.uk/articles/v03-calculations.html#empty-cells-1

In the example below, there are no "Ordinary Passenger" trains for "Virgin Trains" in the bhmtrains data frame. In example 2, the empty cell is changed to display a dash.

library(pivottabler)

# example 1 - normal output
library(pivottabler)
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$addColumnDataGroups("TrainCategory")
pt$addRowDataGroups("TOC")
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
pt$renderPivot()

# example 2 - display dash where no data exists
library(pivottabler)
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$addColumnDataGroups("TrainCategory")
pt$addRowDataGroups("TOC")
pt$defineCalculation(calculationName="TotalTrains", 
                     summariseExpression="n()", noDataCaption="-")
pt$renderPivot()

Option 2 - exportOptions

Use the exportOptions argument of pt$renderPivot() function.

In the code below, example 1 is the normal output and example 2 changes the NAs to dash.

More information: http://pivottabler.org.uk/articles/vA1-appendix.html#output-of-na-nan-inf-and-inf

In the example below, there is a row for the colour "Green" in the data frame, but it has value NA. In example 2, a dash is output instead of NA for the colour "Green".

library(pivottabler)

someData <- data.frame(Colour=c("Red", "Yellow", "Green", "Blue", "White", "Black"),
                       SomeNumber=c(1, 2, NA, NaN, -Inf, Inf))

# example 1 - normal output
pt <- PivotTable$new()
pt$addData(someData)
pt$addRowDataGroups("Colour")
pt$defineCalculation(calculationName="Total", summariseExpression="sum(SomeNumber)")
pt$evaluatePivot()
pt$renderPivot()

# example 2 - change NA to dash
pt <- PivotTable$new()
pt$addData(someData)
pt$addRowDataGroups("Colour")
pt$defineCalculation(calculationName="Total", summariseExpression="sum(SomeNumber)")
pt$evaluatePivot()
pt$renderPivot(exportOptions=list(exportNAAs="-"))