19 March 2009
As those of us who have worked on empirical projects surely know, at times a frustratingly large amount of time can be spent packaging results into tables or figures for publication or review. Fortunately, a number of modules have been developed to facilitate this process. For example, "write.csv" can be used from within R to output a table directly into an Excel-readable format. Likewise, practitioners of Stata can use the package "xml_tab" to do the same with a bit more flexibility.
Recently I've been involved in a large-scale modeling effort that requires a very detailed multi-worksheet Excel output that, depending on the task, includes a mix of tables, graphs and figures created in both R and Stata. Given the amount of modeling we're doing, creating this output manually every time would either take up 90% of my time, or would require hiring of an army of RAs whose sole task is creating these Excel files. So, while the above packages are no doubt helpful in specific contexts, we've had to scour through what's out there come up with our own way to do the outputs most efficiently.
What follows is what (I think) is a neat way to automate outputs directly from R. Hopefully, readers of this blog can benefit from using this in their own research. Basically, what one can do is use the "write" function in R to write a perl file that is then fed into the terrific Spreadsheet-WriteExcel module. This gives one the flexibility to, among other things, output to separate worksheets, format tables (with merged cells, different column widths, cell borders, etc.), include figures, and create charts, all in the same Excel file.
The example below is fairly simple -- it outputs two generic tables into separate worksheets -- but gives a good sense of how the powers of R and WriteExcel can be harnessed to really speed up the research process. Also, I'd appreciate any other thoughts on this from folks who have done similar things!