Performance issue creating pivot tables using pivottabler

Question

I'm using a package called pivottabler in R to generate pivot tables in a Shiny dashboard. The report is awesome but it takes a long time to render (10 - 15 min) which is not a good user experience. I'm aware of the different factors that influence the performance of the pivottabler package listed here: http://www.pivottabler.org.uk/articles/v08-findingandformatting.html.

After trying different approaches listed in the document I was not able to improve performance.

Below is a minimal example that uses a data frame of about 300 rows and 4 columns that takes ~25 seconds with no formatting.

library(pivottabler)
library(tidyverse)

diamonds.mod <- diamonds %>% 
                  group_by(cut, color, clarity) %>% 
                  summarise(avg.price = mean(price)) %>% ungroup()

startTime <- Sys.time()
  pt <- PivotTable$new()
  pt$addData(diamonds.mod)
  pt$addColumnDataGroups("cut", addTotal=FALSE)
  pt$addRowDataGroups("color", addTotal=FALSE)
  pt$addRowDataGroups("clarity", addTotal=FALSE)
  pt$defineCalculation(calculationName="avg.price", summariseExpression="sum(avg.price, na.rm=TRUE)")
  pt$evaluatePivot()
  
  pt$renderPivot()
endTime <- Sys.time()

endTime - startTime

My question: Does anyone know how to create such a table (above) in less time either with pivottabler or a different package? I can't just present the data in a table in tubular format as the data has many grouping levels making it hard to read in a flat table.

Thanks

TarJae · Answer 1 · 2023-02-11T07:47:12.323

A similar table could be achieved using pivot_wider from tidyr package combined with flextable() and officer:

Time difference of 0.2875979 secs

library(officer)
library(flextable)
library(dplyr)
library(tidyr)

diamonds.mod %>% 
  pivot_wider(
    names_from = cut,
    values_from = avg.price
    ) %>% 
  flextable() %>% 
  bold(j = c(1,2)) %>% 
  merge_v(., j = ~ color) %>% 
  hline(., part="all", border = fp_border(color="gray"))

Performance issue creating pivot tables using pivottabler

1 Answers1