Copying Data from Excel to R and Back

February 24, 2013
By

(This article was first published on Statistically Significant, and kindly contributed to R-bloggers)

A lot of times we are given a data set in Excel format and we want to run a quick analysis using R's functionality to look at advanced statistics or make better visualizations. There are packages for importing/exporting data from/to Excel, but I have found them to be hard to work with or only work with old versions of Excel (*.xls, not *.xlsx). So for a one time analysis, I usually save the file as a csv and import it into R.

This can be a little burdensome if you are trying to do something quick and creates a file that needs to be cleaned up later. An easier option is to copy and paste the data directly into R. This can be done by using "clipboard" as the file and specifying that it is tab delimited, since that is how Excel's clipboard stores the data.

For example, say you have a table in excel you want to copy into R. First, copy it in Excel.

Then go into R and use this function.

read.excel <- function(header=TRUE,...) {
read.table("clipboard",sep="\t",header=header,...)
}
 
dat=read.excel()

This function specifies that you are reading data from the clipboard, that it is tab delimited, and that it has a header.

Similarly, you can copy from R to Excel using the same logic. Here I also make row.name=FALSE as default since I rarely have meaningful row names and they mess up the header alignment.

write.excel <- function(x,row.names=FALSE,col.names=TRUE,...) {
write.table(x,"clipboard",sep="\t",row.names=row.names,col.names=col.names,...)
}
 
write.excel(dat)
Created by Pretty R at inside-R.org

These functions can be added to you .RProfile so that they are always ready for a quick analysis!

Obviously, this technique does not encourage reproducible research. It is meant to be used for quick, ad hoc analysis and plotting; not something you would use for an analysis that needs to be done on a regular basis.

To leave a comment for the author, please follow the link and comment on his blog: Statistically Significant.

R-bloggers.com offers daily e-mail updates about R news and tutorials on topics such as: visualization (ggplot2, Boxplots, maps, animation), programming (RStudio, Sweave, LaTeX, SQL, Eclipse, git, hadoop, Web Scraping) statistics (regression, PCA, time series, trading) and more...



If you got this far, why not subscribe for updates from the site? Choose your flavor: e-mail, twitter, RSS, or facebook...

Comments are closed.