"Can I view that in Excel?"
The capabilities of R programming are expanding. Fast. From publication-quality graphics with ggplot2 to the capability to handle large scale computing with Apache Spark, the analytics community embraces R as a core environment. At CANA Advisors, we use the latest developments in order to deliver the fastest, most adaptable solutions. For clients, results need to be in a form that is easy to process by any member of their team-- with little to no learning curve.
As analytics professionals, how can we ensure the best of both worlds? That is, state of the art solutions that produce results in the familiar form clients seek.
In this post, I'll go over one such method: using R programming to export the results of a Shiny analysis to Microsoft Excel. For those not familiar with Shiny, it is a package to create interactive, aesthetically pleasing web apps with all the statistical capability of the R programming language. This brief tutorial will utilize the Shiny and XLConnect packages in R.
The Method
In this example, we'll be working with the iris data set [1], which contains information about the dimensions of different instances of various iris flower species. For the purpose of this tutorial, we'll assume we already have a functioning Shiny app and the data structures we are interested in saving. In this case, the data we'd like to store is reactive in nature. This means, it will change with user inputs. You can recognize calls to reactive expressions in the code below by their distinctive form expression(). To export a worksheet:
1. Lay the groundwork: Create the download button, workbook, and worksheets. 2. Assign the data frames to the worksheets. 3. Save and download.
The Result
The above process will take us from a shiny app like this:
To an excel file like this:
The Implementation
# Load the shiny and XLConnect packages library(shiny); library(XLConnect) # Create and label the download button that will appear in the shiny app renderUI({ downloadButton("downloadExcel", "Download") }) output$downloadFile <- downloadHandler(filename = "Iris_data.xlsx", content = function(file) # Name the file fname <- paste(file, "xlsx", sep = ".") # Create and assign names to the blank workbook and worksheets wb <- loadWorkbook(fname, create = TRUE) createSheet(wb, name = "Sepal Data") createSheet(wb, name = "Petal Data") # Write the reactive datasets to the appropriate worksheets writeWorksheet(wb, sepal(), sheet = "Sepal Data") writeWorksheet(wb, petal(), sheet = "Petal Data") # Save and prepare for download saveWorkbook(wb) file.rename(fname, file) })
To learn more about any of the features discussed above, use the ?topic feature in R. A more comprehensive overview of shiny is provided by RStudio here.
Lucia Darrow is an valued Operation Research Analyst at CANA Advisors to read more R articles by her and other members of the CANA Team visit the CANA Blog.
[1] In R, type ?iris to learn more than you would ever want to know about it.