Dr. Mark Gardener


Providing training for:

  • Ecology
  • Data analysis
  • Statistics
  • R The statistical programming language
  • Data management
  • Data mining

Tips and Tricks - for R and Excel

On these pages you can find tips, tricks and hints for using both R and Excel. At the end of each tip there are links forwards and backwards as appropriate. There is also an index of R tips and an index of Excel tips.

For most analytical purposes the combination of Excel and R is unbeatable! Excel is great as a data management tool and for preparing data for analysis. You can also use it to get an overview of your data or to make simple (and not so simple) graphs. R is an analytical "swiss army knife" and can carry out a mind-boggling array of analytical routines as well as producing great graphics.

Tips & Tricks for R | Tips & Tricks for Excel | An Introduction to R | MonogRaphs | Writer's Bloc

Use sink() to send output to a disk file as text.

Use capture.output() to send output to disk for one command line only.


Sending R output to disk files

Sometimes you want to get the results of an analysis from your R console to a word processor. Copy and paste does not work well unless you are prepared to use fixed width font in the target document. The trick is to send the output to a disk file using the sink() command first.

The sink() command allows you to send anything that would have gone to the console (your screen) to a disk file instead.

sink(file = NULL, append = FALSE, split = FALSE)

You need to supply the filename, setting file = NULL closes the connection and stops sink()ing. To add to an exisiting file use append = TRUE. If you set split = TRUE the output goes to the console and the file you specified.

When you issue the command a file is created, ready to accept the output. If you set append = FALSE and the file already exists, it will be overwritten. If you set file = TRUE a connection is opened and subsequent output goes to the file.

> sink(file = "Out1.txt", split = TRUE, append = FALSE) # Send output to screen and file

> summary(lm(Fertility ~ . , data = swiss))

lm(formula = Fertility ~ ., data = swiss) Residuals:
Min 1Q Median 3Q Max
-15.2743 -5.2617 0.5032 4.1198 15.3213 Coefficients:
Estimate Std. Error t value Pr(>|t|)
(Intercept) 66.91518 10.70604 6.250 1.91e-07 ***
Agriculture -0.17211 0.07030 -2.448 0.01873 *
Examination -0.25801 0.25388 -1.016 0.31546
Education -0.87094 0.18303 -4.758 2.43e-05 ***
Catholic 0.10412 0.03526 2.953 0.00519 **
Infant.Mortality 1.07705 0.38172 2.822 0.00734 **
Signif. codes: 0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1 Residual standard error: 7.165 on 41 degrees of freedom
Multiple R-squared: 0.7067, Adjusted R-squared: 0.671
F-statistic: 19.76 on 5 and 41 DF, p-value: 5.594e-10 > sink(file = NULL) # Stop sending output to file

Note that even if you set append = FALSE subsequent output is appended to the file. Once you issue the command sink(file = NULL) output stops and you can see your file using any kind of text editor.

Output fril from sink() command
Using sink() creates a text file that mimics the console

If you only want to send a single "result" to a disk file you can use the capture.output() command instead.

capture.output(..., file = NULL, append = FALSE)

You provide the commands that will produce the output and the filename. If you set append = TRUE and the target file exists, the output will be added to the file. If you set append = FALSE (the default) the file will be "blanked" and the output will therefore overwrite the original contents.

Note that there is no equivalent of the split argument, all output goes to the file and cannot be "mirrored" to the console. You can supply several commands, separated by commas.

> capture.output(ls(), search(), file = "Out1.txt")

This example sent the ls() command followed by search(), with the results being output to the disk file.

Once you have your output in a text file you can transfer it to your word processor with a little pre-processing via Excel.

Use Excel to open a sink() output text file.

Copy and Paste Special to transfer to Word in a table format.


Processing sink() output text files

Your sink()ed file will be space separated but not exactly fixed width. In any event you'll need to open the file in Excel and do a little processing so that you can get the results into Word in table form.

Most times it is the regression or ANOVA table that you want. So, open Excel then File > Open to bring up the Text Import Wizard.

Text Import Wizard step 1
Excel's text import wizard can help process text files

Tell the wizard that the file is fixed width and also which row you want to start at, in the example here row 9 is the beginning of the regression table. Once you click Next you'll be able to set the boundaries of the columns.

Use the wizard to set and move column boundaries

You can add column boundaries with a click and move them by dragging with the mouse. Once you are done you get the results in cells of the spreadsheet.

Text imported to Excel
Regression table imported into Excel

Now you can copy the cells to the clipboard and switch to Word. In Word you need the Home > Paste button, then you can select various options.

Paste Special in Word
Paste options allow you to format in a neat table layout

You can also use Paste Special and select the RTF format option, which takes Excel cells and transfers them as table cells in Word.

If you have other elements to transfer you can deal with them seaprately. This is not an ideal method but the amount of user intervention is fairly minimal. You might also try opening the file in Word to start with and replacing spaces with Tab characters. You want to keep single spaces as spaces so start by replacing 3-space with 2-space until there are no more double spaces left. Then replace 2-space with Tab (^t in the Word replace box). You'll need to do some manual editing as this will not produce a perfect result but it will do most of the work automatically. Then save the file and use Excel again, setting the delimiter to Tab.

Top << Previous tip: Rotating objects using t() Save all objects to disk as separate files: Next Tip >>
Follow me...
Facebook Twitter Google+ Linkedin Amazon
Top Tips & Tricks Home Index of R Tips Index of Excel Tips  
More links:

An introduction to R

See my Publications about Excel, R, statistics and data analysis Courses in R, data analysis, data management and statistics Visit the R Project website

See my Publications about statistics and data analysis.

MonogRaphs: random topics in R

Writer's Bloc – my latest writing project includes R scripts

Courses in data analysis, data management and statistics.

My Publications about statistics and data analysis

Managing Data Using Excel, Cover

See my personal pages at GardenersOwn

Top Home
Data Analysis
Contact GardenersOwn Homepage