download rpivotTable output in shiny

Question

I've found an interesting package rpivotTable. I'd like to create shiny app which includes rpivotTable with the possibility to download generated data using downloadHandler.

However, I am unable to find the solution, how to create data.frame or something else which I'd be able to pass to the downloadHandler function.

rpivotTable creates an object of class:

class(pivot)
[1] "rpivotTable" "htmlwidget" 

Is threne any possibilities to download the output of the this function?

Also, I enclose the example, how the pivot is created in shiny and the example of download function which I'd like to use.

Maybe are the any other ideas or suggestions?

set.seed(1992)
n=99
Year <- sample(2013:2015, n, replace = TRUE, prob = NULL)
Month <- sample(1:12, n, replace = TRUE, prob = NULL)
Category <- sample(c("Car", "Bus", "Bike"), n, replace = TRUE, prob = NULL)
Brand <- sample("Brand", n, replace = TRUE, prob = NULL)
Brand <- paste0(Brand, sample(1:14, n, replace = TRUE, prob = NULL))
USD <- abs(rnorm(n))*100

df <- data.frame(Year, Month, Category, Brand, USD)



output$Pivot <- rpivotTable::renderRpivotTable({
 rpivotTable(data = df, rows = "Brand", col = "Category", vals = "USD", aggregatorName = "Sum", rendererName = "Table")
})



  output$downloadData <- downloadHandler(
   filename = function() { paste(filename, '.csv', sep='') },
   content = function(file) {
   write.csv(PivotOutput, file)
})

Answer 1

I've just pushed on the master branch of rpivotTable on github a change that addresses the issue of getting the parameters the user is / has looked at on the server side.

Download the rpivotTable code with devtools:

devtools::install_github("smartinsightsfromdata/rpivotTable",ref="master")

This is an example of how to get the selected data on the server side. The example is not complete for your needs: you need to subset the original data frame with what you get back from rpivotTable. But this should be enough to give you an head start.

library(rpivotTable)
library(shiny)

list_to_string <- function(obj, listname) {
  if (is.null(names(obj))) {
    paste(listname, "[[", seq_along(obj), "]] = ", obj,
          sep = "", collapse = "\n")
  } else {
    paste(listname, "$", names(obj), " = ", obj,
          sep = "", collapse = "\n")
  }
}

server <- function(input, output) {

output$pivotRefresh <- renderText({

cnames <- list("cols","rows","vals", "exclusions","aggregatorName", "rendererName")
# Apply a function to all keys, to get corresponding values
allvalues <- lapply(cnames, function(name) {
  item <- input$myPivotData[[name]]
  if (is.list(item)) {
    list_to_string(item, name)
  } else {
    paste(name, item, sep=" = ")
  }
})
paste(allvalues, collapse = "\n")
})

output$mypivot = renderRpivotTable({
    rpivotTable(data=cars, onRefresh=htmlwidgets::JS("function(config) { Shiny.onInputChange('myPivotData', config); }"))
  })
}

ui <- shinyUI(fluidPage(
  fluidRow(column(6,   verbatimTextOutput("pivotRefresh")),
           column(6, rpivotTableOutput("mypivot") ))
)
)

shinyApp(ui = ui, server = server) 

Answer 2

To extend Enzo's excellent answer (Thank you for the awesome package), I mocked up the following as a way to get the summarized data and use it inside shiny.

This uses the onRefresh to watch for changes in the config, then uses the DOM to get the innerHTML of the relevant element. In this case, then uses rvest to clean that html and extract the table, and finally, for demo purposes, shows it inside a DT::datatable.

This might be too hacky, but it can be straightforwardly downloaded as a CSV then, or passed to other shiny elements for further processing.

ui.R

library(shiny)
library(DT)
library(rpivotTable)

FullPage <- fluidPage(
    DT::dataTableOutput('aSummaryTable'),
    rpivotTableOutput('RESULTS')
)

FullPage

server.R:

library(shiny)
library(rpivotTable)
library(DT)
library(rvest)

function(input, output, session) {

  # Make some sample data
  qbdata <- reactive({
    expand.grid(LETTERS,1:3)
  })

  # Clean the html and store as reactive
  summarydf <- eventReactive(input$myData,{
    input$myData %>% 
       read_html %>% 
       html_table(fill = TRUE) %>% 
       # Turns out there are two tables in an rpivotTable, we want the second
       .[[2]]

  })

  # show df as DT::datatable
  output$aSummaryTable <- DT::renderDataTable({
      datatable(summarydf(), rownames = FALSE)
  })

  # Whenever the config is refreshed, call back with the content of the table
  output$RESULTS <- renderRpivotTable({
    rpivotTable(
      qbdata(),
      onRefresh = 
        htmlwidgets::JS("function(config) { 
                           Shiny.onInputChange('myData', document.getElementById('RESULTS').innerHTML); 
                        }")
    )
  })

}