The windows clipboard is a quick way to get data in and out of R. How can we exploit this feature to accomplish our basic data exploration needs and when might its use be inappropriate? Read on.

Overview

1. Paste Data from Clipboard to R
1. HTML Tables
2. Spreadsheets
2. Copy Data from R to Clipboard
3. Summary and Usage Notes

Paste Data from Clipboard to R

We won't be using it in this post, but you can see the contents of the Windows clipboard in R using the readClipboard() command. Going through the documentation you'll note a variety of formats that can be read. For our purposes, we are looking at moving text based data into R from HTML tables or spreadsheets that happen to be open on our desktop.

HTML Tables

HTML tables are easy to copy into R via the windows clipboard if you're using Chrome or Firefox. To demonstrate, copy the simple HTML table shown below by highlighting the text and pressing CTRL + C on your keyboard (or whatever copy method works best for you.)

Next, grab the windows clipboard content and “paste” it into an R data frame using the following R-code:

My_HTML_Data <- 
  read.table(file = "clipboard", 
             header = T, 
             sep = "\t")

Notice that the file argument of the read.table command is calling out to the windows “clipboard” (line 2).

Note 1: I've gotten this to work with Mozilla Firefox and Google Chrome but Not Microsoft Edge or Internet Explorer.
Note 2: You copied all the data, but you might have gotten a warning that looks like this:

Warning message:
In read.table(file = “clipboard”, header = T, sep = “\t”) :
incomplete final line found by readTableHeader on ‘clipboard’

There seems to be a hidden character at the end of the HTML table. If you miss it, you get the error mentioned above. Digging deeper into the warning with the readClipboard command, you get something that looks like this:

readClipboard()
## [1] "Items \tInventory" "Apple \t5" "Kiwi \t7" "Meat Balls \t253 "

If you get the hidden character, you don't get the error. Running the readClipboard command show there is an extra empty item in the list (see line 3):

readClipboard()
##[1] "Items \tInventory" "Apple \t5" "Kiwi \t7" "Meat Balls \t253" 
##[5] "" 

Either way, all the data ends up in your target data frame.

Spreadsheets

I can think of numerous times when I've had multiple small tables in the same spreadsheet as shown below.

In these example tables, we see data related to notable serial killers. The left table provides the Name and Sex of a given serial killer. The right table provides their year of Birth and Death as well as the number of victims. Both tables are in the same spreadsheet.

Often, I want to merge or perform a quick analysis on tables like these in R but saving each table to a csv first is a nuisance. To get around this problem, we can leverage the Window's clipboard using the following code:

#Copy the Left table from Spread Sheet
Left_Table  <- 
  read.table(file = "clipboard", 
             header = T, 
             sep = "\t")

#Copy the Right table from Spread Sheet
Right_Table  <- 
  read.table(file = "clipboard", 
             header = T, 
             sep = "\t")

Note: Between each read.table command, you will need to copy the appropriate data from the target spreadsheet to the clipboard. Once you have the tables in R, you can use the write.csv command to write them to disk.

Copy Data from R to Clipboard

In the previous section, we copied two tables containing information about serial killers into R. In this section, we are going to merge those tables together and copy the result back to our spreadsheet. The code to merge and copy to clipboard is shown below:

Merged_TBLs <- 
  merge(Left_Table, Right_Table, 
        by="KEY", all.x = T)
write.table(Merged_TBLs, file="clipboard", row.names = F)

Here is a screenshot showing the merged table pasted back into our spreadsheet software:

Note: after pasting the table back into the spreadsheet, a text-to-column process was required to put the data back into the individual columns.

Summary and Usage Notes

Copying and pasting data to and from the Window's clipboard to R is quick and easy. All we need to do is use the standard read.table and write.table syntax and set the file argument equal to “clipboard”. Despite the ease of moving data around in this manner, we are straying away from reproducible data processing. For example, it would be hard for me to reproduce your result if your R code referenced the clipboard. With this in mind, get your source data into a csv file if your quick glace at HTML or spreadsheet data becomes more than a 30 minute affair or you want to share you analysis. Finally, if your running a OS X or X11 based windowing systems check out the clipr package.

Other Articles at r-bar.net: XmR Plots with ggQC

The post Windows Clipboard Access with R appeared first on R-BAR.