Saving multiple data-frames to single csv file using Shiny downloadHandler

Question

I have an RShiny application where I'm displaying multiple data-frames across different tab-setted panels. I want to be able to write data-frames appearing across each panel to one csv file per panel. I am currently trying to render this using Shiny's downloadHandler option.

Did a little bit of research and found out I could use the sink() function to divert R output to a file.

Here's my attempt at trying to incorporate sink() into one such button on the server.R side

output$downloadDemographic <- downloadHandler(
filename = function() {
  paste('Demographics', '.csv', sep='')
},
content = function(file) {
  sink("Demographics.csv")
  cat('Population Demographics')
  write.csv(Population)
  cat('Geography Demographics')
  write.csv(Geography)
  sink()
}
)

where Population and Geography are two data-frames with the same number of columns but different names.

The sink function automatically writes the above dataframes to a csv file 'Demographics.csv' into my working directory but the downloadHandler option on Shiny prompts the user to save the csv file on the fly. Kind of contradicting what I am trying to achieve here. I'm probably missing something or making an obvious mistake, I don't know.

Desired output - https://i.stack.imgur.com/yddrE.jpg

I could render multiple download buttons, write each of the data-frames to multiple csv files but some of my tab setted elements have as many as 8 data-frames and this would make the UI messy.

or

I could coerce multiple data-frames into a single one before writing to a csv file but they are all unequal sized dataframes and formatting them into the desired output would be a huge pain.

Any thoughts on how I could establish this any other way?

Thanks!

score 1 · Accepted Answer · answered Sep 27 '17 at 00:19

Other options:

Here's a sample of both options using four dataframes from the R Datasets Package.

library(shiny)
library(xlsx)

shinyApp(
    ui = fluidPage(
        downloadButton("downloadExcelSheet", "Download Excel Workbook with Multiple Sheets"),
        downloadButton("downloadZippedCSV", "Download zipped csv files")
    ),
    server = function(input, output) { 

        #### Write an Excel workbook with one sheet per dataframe ####
        output$downloadExcelSheet <- downloadHandler(
            filename = function() {
                "excelWorkbook.xlsx"
            },
            content = function(file) {
                # write workbook and first sheet
                write.xlsx(mtcars, file, sheetName = "mtcars", append = FALSE)

                # add other sheets for each dataframe
                listOtherFiles <- list(iris = iris, 
                                       airquality = airquality, 
                                       sleep = sleep)
                for(i in 1:length(listOtherFiles)) {
                    write.xlsx(listOtherFiles[i], file, 
                               sheetName = names(listOtherFiles)[i], append = TRUE)
                }
            }
        )

        #### Zip together multiple csv files ####
        output$downloadZippedCSV <- downloadHandler(
            filename = function() {
                "zippedCSV.zip"
            },
            content = function(file) {
                # go to temp dir to avoid permission issues
                owd <- setwd(tempdir())
                on.exit(setwd(owd))

                # create list of dataframes and NULL value to store fileNames
                listDataFrames <- list(mtcars = mtcars, 
                                       iris = iris,
                                       airquality = airquality,
                                       sleep = sleep)
                allFileNames <- NULL

                # loop through each dataframe
                for(i in 1:length(listDataFrames)) {
                    # write each dataframe as csv and save fileName
                    fileName <- paste0(names(listDataFrames)[i], ".csv")
                    write.csv(listDataFrames[1], fileName)
                    allFileNames <- c(fileName, allFileNames)
                }

                # write the zip file
                zip(file, allFileNames) 

            }
        )
    }
)

Thanks @blondeclover, you're a life saver! I love the first example where I can stitch a workbook together with different sheets. The formatting on the Excel was a little off but converting the text to number did the trick for me. — Ashwin Ramesh, Sep 27 '17 at 02:09
I still want to be able to parse these two data-frames onto the same sheet. Each tabset panel in my shiny application is supposed to show slices of different information - in this case 'Population' and 'Geography' are two different data-frames conveying information on the "Demographics" tab on my Application — Ashwin Ramesh, Sep 27 '17 at 02:12
@AshwinRamesh does your user need to be able to edit the downloaded tables or import them into R? If not, you could use RMarkdown to make a PDF with both tables on the same page — Hallie Swan, Sep 27 '17 at 16:24
@blonderclover The tables need to be outputted as Excel files. The application calculates 14 data-frames based on the input provided by the user (Csv File upload). These data-frames appear across 7 tab panels. Downloading them onto 14 excel sheets would involve some post-processing work because some of these data-frames are essentially different metrics belonging to the characteristic. Like Population & Geography belonging to the Demographic characteristic. 7 excel sheets - one for each tab would be perfect. So a combination of your solution and my initial question. — Ashwin Ramesh, Sep 30 '17 at 21:08

Saving multiple data-frames to single csv file using Shiny downloadHandler

1 Answers1