March 2009
Sun Mon Tue Wed Thu Fri Sat
1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30 31        

Authors' Committee

Chair:

Matt Blackwell (Gov)

Members:

Martin Andersen (HealthPol)
Kevin Bartz (Stats)
Deirdre Bloome (Social Policy)
John Graves (HealthPol)
Rich Nielsen (Gov)
Maya Sen (Gov)
Gary King (Gov)

Weekly Research Workshop Sponsors

Alberto Abadie, Lee Fleming, Adam Glynn, Guido Imbens, Gary King, Arthur Spirling, Jamie Robins, Don Rubin, Chris Winship

Weekly Workshop Schedule

Recent Comments

Recent Entries

Categories

Blogroll

SMR Blog
Brad DeLong
Cognitive Daily
Complexity & Social Networks
Developing Intelligence
EconLog
The Education Wonks
Empirical Legal Studies
Free Exchange
Freakonomics
Health Care Economist
Junk Charts
Language Log
Law & Econ Prof Blog
Machine Learning (Theory)
Marginal Revolution
Mixing Memory
Mystery Pollster
New Economist
Political Arithmetik
Political Science Methods
Pure Pedantry
Science & Law Blog
Simon Jackman
Social Science++
Statistical modeling, causal inference, and social science

Archives

Notification

Powered by
Movable Type 4.24-en


« March 18, 2009 | Main | March 25, 2009 »

19 March 2009

Writing Excel Tables, Figures and Graphs Directly from R

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!

output_excel.txt
Here is the final output.

Posted by John Graves at 12:06 PM