Jeromy Anglim's Notes

As I mention here, I quite like to export an R data frame to a spreadsheet for exploration. Functions like head, tail, str dim, table, etc are very useful, but it’s also quite nice to peruse a spreadsheet for anomalies.

Furthermore, the various alternative interfaces such as the built in viewers in R, Rstudio, Deducer (triggered by the View function) lack the functionality and familiarity of Spreadsheet software.

In particular, setting up a table or filter in the Spreadsheet on a data.frame can be a way of quickly exploring the data.

Thus, I have the following function in my .Rprofile file.

view <- function(data, autofilter=TRUE) {
    # data: data frame
    # autofilter: whether to apply a filter to make sorting and filtering easier
    open_command <- switch(Sys.info()[['sysname']],
                           Windows= 'open',
                           Linux  = 'xdg-open',
                           Darwin = 'open')
    require(XLConnect)
    temp_file <- paste0(tempfile(), '.xlsx')
    wb <- loadWorkbook(temp_file, create = TRUE)
    createSheet(wb, name = "temp")
    writeWorksheet(wb, data, sheet = "temp", startRow = 1, startCol = 1)
    if (autofilter) setAutoFilter(wb, 'temp', aref('A1', dim(data)))
    saveWorkbook(wb, )
    system(paste(open_command, temp_file))
}

To use the command enter view(foo) where foo is the data.frame.

The command requires the package XLConnect. It saves the data.frame as an xlsx file and opens it in the associated spreadsheet software. In my case this is LibreOffice Calc. The code is designed to work on Ubuntu where xdg-open is a command for opening files in applications. The code could presumably be adapted to other operating systems (e.g., Mac has a command called open). I updated the code so that hopefully it works on different platforms using the OS switch code here. That said, I’ve only tested it on OSX.

I tried avoiding the dependency on XLConnect by saving as csv, but that meant that there was an annoying text import dialog box every time the function was run.

For other ideas, see this question on StackOverflow