Making (much) more use of our Pivot Tables in our new (html) windows?

[rdstern]

For spreadsheet users the pivot tables/graphs are probably the best part of the software for doing statistics. We have pivot tables/graphs in R-Instat already, and now perhaps looking even better with our new html system. I have recently found - for the first time - a book with a chapter and video on this rpivotTable package. @Patowhiz one thing he does in the video in the chapter, is to explain how to save the rpivotTable - I think to html - which you have included. Then - according to the video in the book chapter - you can share these files with anyone and they can continue to use the data - and without needing R or R-Instat. That would be brilliant if true. So I tried with the usual survey data, and saved from the maximised window, and got many directories and files, but couldn't see how to use them? @Patowhiz, I don't understand shiny enough, but the rpivotTable package can output or produce shiny stuff. And I notice that there is an R package calledshinymobile?
@berylwaswa and @rachelkg the book is very interesting in many ways. a) The way it is written with embedded videos and then also lots of text. Should we consider having our videos and scripts made similarly as a "book"? If so, then part 1 could be the tutorials - both videos and scripts. We could even include the third video there, with its component of who R-Instat is for! Then part 2 could be the Data - and starting with simple data entry - with the current video. Perhaps even the data checking video could go next. Then importing data and then data from the library.
b) It is yet another book that spends ages on learning and using R. It is for a non-statistical audience and I suggest considering R-Instat is a no-brainer here! We could even try our version of his Chapter 26 video on the pivot tables as an example.

I am doing another issue on some changes in the Describe menu that give more prominence to this package.

[rdstern]

I report here that a new pull request - to be merged soon - means it now works!

I suggest this could also be a great component for @jkmusyoka course next year.

Here is an example of an html file.

survey.zip

Here is a picture from within the file.

That's very exciting, because I know you can do nice tables and charts, with that system, but here is also a simple listing of the data. I wasn't sure about that!

So now I am convinced that we need documentation - as soon as possible about rpivotTable - so a video and a script. This can conveniently follow the documentation about the different types of data we can access within R-Instat.

And I expect the next video can include plotly! Because that is a great way to show visualization. The book is taking shape!

It is ironic that the big change in this version is largely getting rid of a browser when we use R-Instat. And this key feature means that we can process tidy data and get many pivot/chart-type results from within the browser.

That means that the start of our guides is correctly concerned with data. Then, we can do things with the data - if they are tidy. So we need a system that is also good at tidying data. Then for analysis we need much more - of course - than pivot-tables/charts - and that is what we are developing in the describe and model parts of R-Instat. And in the graphs, isn't it good that we have concentrated on ggplot2, because the next part of making statistics/data science fun and easy is via plotly!

[Patowhiz]

@rdstern the html file shared needs to have the folder files as well. The html file loads the necessary java script and css files from the associated folder. To see the associated folder, save the file in a blank directory. This is normal even when saving html files from the web using your browser. So you may have to update the zip file you have attached above.

[lilyclements]

To add, some code here on saving or copying your rpivotTable through shiny from here. This is the best I can find so far on it if we wanted to move it to be a function as an option for outputting and then saving rpivottable objects.

library(rpivotTable)
library(dplyr)
library(readr)
library(rvest)
library(shiny)
library(writexl)
library(htmlwidgets)
library(shinyjs)
library(clipr)

#ui
ui = fluidPage(
    # for the purposes of this exercise, I'm only including csv and xlsx to simplify the download logic
    # but you could certainly add more format options
    radioButtons(inputId = "format", label = "Enter the format to download", 
                 choices = c( "csv", "excel"), inline = FALSE, selected = "csv"),
    downloadButton("download_pivot"),
    actionButton("copy_pivot", "Copy"),
    fluidRow(rpivotTableOutput("pivot")))

#server
server = function (input, output) { 

    output$pivot <- renderRpivotTable(
        rpivotTable(mtcars, rows = "vs", cols = "carb", vals =  "mpg", aggregatorName = "Sum",
                    rendererName = "Table", width="50%", height="550px",
                    onRefresh = htmlwidgets::JS(
                        "function(config) {
                            Shiny.onInputChange('pivot', document.getElementById('pivot').innerHTML); 
                        }"))
    )

    # create an eventReactive dataframe that regenerates anytime the pivot object changes
    # wrapped in a tryCatch to only return table object. errors out when charts are shown
    pivot_tbl <- eventReactive(input$pivot, {
        tryCatch({
            input$pivot %>%
                read_html %>%
                html_table(fill = TRUE) %>%
                .[[2]]
        }, error = function(e) {
            return()
        })
    })

    # allow the user to download once the pivot_tbl object is available
    observe({
        if (is.data.frame(pivot_tbl()) && nrow(pivot_tbl()) > 0) {
            shinyjs::enable("download_pivot")
            shinyjs::enable("copy_pivot")
        } else {
            shinyjs::disable("download_pivot")
            shinyjs::disable("copy_pivot")
        }
    })

    # using shiny's download handler to get the data output
    output$download_pivot <- downloadHandler(
        filename = function() {
            if (input$format == "csv") {
                "pivot.csv"
            } else if (input$format == "excel") {
                "pivot.xlsx"
            }
        },
        content = function(file) {
            if (input$format == "csv") {
                write_csv(pivot_tbl(), path = file)
            } else if (input$format == "excel") {
                writexl::write_xlsx(pivot_tbl(), path = file)
            }
        }
    )

    # copy pivot table - works natively on Windows/OSX. Requires xclip on Linux
    observeEvent(input$copy_pivot,  {
        clipr::write_clip(pivot_tbl(), object_type = "table")
    })

}

shinyApp(ui = ui, server = server)