RightClickR!

After I took the Statistics One and Data Analysis series courses on Coursera last year, I told myself that I was going to stop doing statistical analysis in Excel. I had a good intro to R, and found that it made a lot of the analysis I did in Excel--from basic statistical analysis and exploratory visualization--extremely easy and quick. And it was reproducible.

But then I got lazy. I stopped exporting Excel files to CSV. I stopped loading those CSVs into R. And because of those two extra steps that I stopped doing, everything I learned in R became a little hazy.

There was really only one barrier to doing more in R and keeping my R skills fresh--getting those spreadsheets into R. So I quickly built RightClickR, a registry setting / batch file combo that lets me right-click on an Excel file and have it open up in R. All tabs get loaded into a dataframe in the format of xlsx.<tab name>. As I write that, I realized I should probably do some tab name cleansing, but that's a project for another day. Maybe Thursday.

Now just to get me some data!

As you might notice in the screenshot above, using RightClickR creates an R file corresponding to the Excel spreadsheet. This file will continue to be loaded each time you use RightClickR for that spreadsheet, and as a result is a good place to drop any munging you need to do on your data set.

If you're handy with batch / registry files and you spend time in Excel that you'd rather spend in R, maybe the files below will help you.

RightClickR.bat

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

@echo off set RCR_RFILE=%~n1.R set RCR_FILEPATH=%1 set RCR_PATH=%~p1 set RCR_NEWPATH=%RCR_FILEPATH:\=/% if exist %RCR_PATH%%RCR_RFILE% goto launchR echo library(xlsx) > %RCR_RFILE% echo rcr_file ^<- '%RCR_NEWPATH%' >> %RCR_RFILE% echo rcr_wb ^<- loadWorkbook(rcr_file) >> %RCR_RFILE% echo rcr_sheets ^<- getSheets(rcr_wb) >> %RCR_RFILE% echo for (rcr_s in 1:length(rcr_sheets)) { >> %RCR_RFILE% echo rcr_data ^<- read.xlsx(rcr_file,rcr_s) >> %RCR_RFILE% echo assign(paste("xlsx.",names(rcr_sheets[rcr_s]),sep=""),rcr_data) >> %RCR_RFILE% echo } >> %RCR_RFILE% echo remove(rcr_data) >> %RCR_RFILE% :launchR start C:\Progra~1\RStudio\bin\rstudio.exe %RCR_PATH%%RCR_RFILE% :exit

And here's the reg file. The usual disclaimers about modifying your registry apply--if you don't know what you're doing you can bork things up, so only do this if you have done this before and are comfortable with making registry changes.

RightClickR.reg

1 2 3 4 5 6

Windows Registry Editor Version 5.00 [HKEY_CLASSES_ROOT\Excel.Sheet.12\shell\OpenInR] [HKEY_CLASSES_ROOT\Excel.Sheet.12\shell\OpenInR\command] @="C:\\\\Users\\\\bhayek\\\\Desktop\\\\Misc\\\\Personal\\\\Data\\\\RightClickR\\\\RightClickR.bat %1"

To adapt this for your setup:

1. Install the xlsx package in R if you haven't already
2. Change line 17 in RightClickR.bat to reflect the path to RStudio on your local machine
3. Update line 6 in RightClickR.reg to reflect the path where you have saved the batch file; note that you will need to double-double-escape your backslashes; each backslash in your path will need to be replaced by four for the registry file
4. Double-click RightClickR.reg file to make the registry changes and set up your right-click shortcut to R!