R Notebooks Are Very Useful

Because I started learning R many years ago, prior to the inclusion of the notebook format in RStudio, I stubbornly stuck with using R scripts for the past few years as the notebook format grew in popularity. Now my mind has been changed.

The biggest benefit to notebooks is that a couple of years ago RStudio added some nifty SQL capabilities that I had ignored for far too long. I was using SQL in the odbc package in R scripts. It was fine, but had it’s quirks. For one, the actual query had to be passed into the dbGetQuery() function as plain text. That meany escaping all the single quotes within the query. I would usually write the query in SQL Server Manager and the copy/paste into R. It wasn’t terrible, but that does introduce a space for errors.

Here’s a relatively simple SQL query in an R script:

The second odd thing you probably noticed is that I had to put all of my variable names in quotes. For whatever reason the queries wouldn’t run without the quoted variable names. I still don’t really know why that’s the case.

Now let’s just compare that to this SQL chunk from an R notebook:

So. Much. Cleaner.

No quotes on the variable names. Check. Easier to read IDE styling. Check. And the best of all, RStudio keeps a live connection to the SQL server, so that I get predictive text of the tables and variables. No more writing in one IDE and copying it over to RStudio.

You also may have noticed ?maildate and ?ninetyday in the where clause. They’re R objects being passed to the query. Those two variables are actually parameters in the notebooks header allowing me to change the values based on the different criteria. It makes this particular report very reusable.

In addition to the queries and parameters, R Notebooks are very easy to output into a variety of formats – html, .docx, .pdf, markdown, .xlsx – really just about any format you can dream up. In just the couple of weeks that I’ve been using R notebooks, I’ve already automated the tasks for creating several regular reports. That creates so much more time for me to focus on the more fun analyses of my job.

That’s all for this week. See you next week!

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s