Pasting Excel data into R on a Mac

When starting out with R, getting data in and out can be a bit of a pain. It should take long to work out a convenient method – depending on what OS you use and what other packages you work with.

In my case I prefer to work with Excel spreadsheets (which are versatile and – for the most part – convenient for sharing with collaborators or students). For this reason I mostly work with comma separated variable files created in Excel an imported using read.csv(). I even quite like the fact that this method requires me to save the .xls worksheet as a .csv file (as it makes it harder to over-write the original file when I edit it for R). In know that there are many other methods that I could use, but this works fine for me.

I do however occasionally miss some of the functionality of software such as MLwiN that allows me to paste Excel data directly into it. I’ve seen instructions about how to do this on a Windows machine (e.g., see John Cook’s notes), but a while back I stumbled on a simple solution for the Mac. I’ve forgotten where I saw it (but will add a link as soon as I find it or if someone reminds me). The solution uses read.table() but is a bit fiddly and therefore best set up as a function.

paste.data <- function(header=FALSE) {read.table(pipe("pbpaste"), header=header)}

I’ve included this in my master function list so it can be loaded with other functions from the book and blog.

To use it just copy tab-delimited data (the default for copying from an Excel file or Word table) and call the function in R. The data are then imported as a data frame in R. For an empty call it assumes there is no header and adds default variable names. Adding the argument header=TRUE or just TRUE will treat the first row as variable (column) names for the data frame. Copy some data and try the following:

source('http://www2.ntupsychology.net/seriousstats/SeriousStatsAllfunctions.txt')

paste.data()

paste.data(header = TRUE)
paste.data(TRUE)
paste.data(T)

N.B. R code formatted via Pretty R at inside-R.org

UPDATE: Ken Knoblauch pointed out an older discussion of this issue in https://stat.ethz.ch/pipermail/r-help/2005-February/066257.html and also noted the read.clipboard() function in William Revelle’s excellent psych package (which works on both PC and Mac systems).

About these ads
Leave a comment

4 Comments

  1. Hi Thom,
    an another alternative and platform-independent option to deal with Excel files is the XLConnect package. It works with xls and xlsx files and runs under Windows, Linux and Mac OS X.
    Regards,
    Martin

    Reply
  2. rx2

     /  June 4, 2012

    Copy and paste of data directly from Excel, Open Office etc. also works really nice with rkward, an R GUI http://rkward.sourceforge.net/

    Reply
  3. Also worth noting is that this works from Google Docs spreadsheets as well. Very useful for grabbing data quickly.

    Reply
  1. Momento R do dia « De Gustibus Non Est Disputandum

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 35 other followers

%d bloggers like this: