Why do we use R rather than Excel?

I have more than once found myself answering that sort of question. I gave similar reasoning.

In 2020, for a grassroots PPE-relief organization, however, I found that I had sometimes been mistaken. What our group managed to achieve by eschewing (eventually as a watchword) fancy tools and building our entire backend around Google Sheets was speed. Moreover, I learned along the way that simple database tools are sometimes more-efficient or faster than anything I'd have written myself.

I had been blinded by the GUIs -- the (frequently correct) notion that GUIs are generally inferior in the long run to scripting/programming had blinded me to the very idea that perhaps another tool could be superior.

As my career takes me in new directions, I'm presently reprising that experience, this time with SQL. Physicists rarely use it, so we have no idea what it can do. The syntax looks old/quirky/muddy, but the tools behind it are extremely powerful.

If you're great at R, consider sitting down with someone whom you know is just crushing problems with Excel. I'm pretty sure you'll both learn something useful.

I have some objections, as someone who doesn't use R but does quite a lot of Excel. The author seems to miss PowerQuery, data model, and PowerPivot functions.

"Visibility: How do you see the code inside an Excel document? How do you tell exactly what is going on? You have to go clicking through cells, or reverse engineer what settings a graph has."

PowerQuery it's a functional way of transforming data step after step. You can see the code/function of each step and it's very easy to reason about those functions. You can transform data to the format you want, and do calculations on it before it enters the spreadsheet. One of the main advantages is that it's easy! I've taught non programmers to reliably use this tool.

"Repeatability: [...] With R, you just change read.csv("1.csv") to read.csv("2.csv") and the exact same calculations are run on two different data sets."

Again, with PowerQuery this is doable, and a normal procedure on my day to day. You change the file parameter PowerQuery will use on step 1, and the rest of steps will follow.

"Batch processing: Related to the above, you can read every CSV in a directory and produce a graph for each of them. You can read data from an API and run the same process on it that you did yesterday."

You can use PowerQuery on folders, it can take a set of files and transform or aggregate them all at once.

In my opinion Excel has gotten pretty powerful after data model and powerquery were added, I think around excel 2013. I barely use cell functions anymore; data model and pivot tables make for robust spreadsheets that are easy to reason about. I know a decent programmer can do most of it in many other ways, but the accessibility of Excel is amazing.

The biggest defect Excel has for me at the moment is control change tracking. Wish I could git Excel changes.

Shameless plug, if you want to use R-Studio and R on AWS,GCP,Azure, we provide VMs with pre-installed R-Studio and popular R libraries which are far cheaper than the official R-Studio . Below are the links:

AWS : https://aws.amazon.com/marketplace/pp/prodview-brc4ybuoee6he...

GCP : https://console.cloud.google.com/marketplace/product/techlat...

Azure : https://azuremarketplace.microsoft.com/en-us/marketplace/app...

Support & Documentation : http://www.techlatest.net/support/r-studio-support/

EDIT: i gotta admit, you sound like you've got more experience with teaching R than me. so perhaps my opinions here are a bit strong for what they're based on, i.e. tutoring a couple of non-programmer friends and my own learning process. still...

> My experience is that this weird evaluation order stuff is only confusing for students with a lot of programming experience who already expect nice lexical scope

fair point, but for the most part, R itself does use pretty standard lexical scoping unless you opt into "non-standard evaluation" by using `substitute`[1]. so building a mental model of lexical scoping and "standard evaluation" is a pretty important thing to learn. after that, the student can see how quoting can "break" it, or at least be able to understand a sentence like "you know how evaluation usually works? this is different! but don't worry about it too much for now". and i think dropping someone new straight into tidyverse stuff gets in the way of this process.

> and even then, base R isn’t any simpler: the confusing evaluation order is built into R itself at the deepest level.

i mean, quoting can't really work without being deeply integrated into the language, can it? besides:

- AFAICT base R data manipulation functions don't use it a lot. [2]

- for the most part, R's evaluation order can be ignored (at a certain learning stage) because it's not observable if you stick to pure stuff, which you probably should anyway.

---

[1] http://adv-r.had.co.nz/Computing-on-the-language.html#captur...

[2] admittedly, stuff with `formula`s is similarly wacky, and if you're doing stats you're going to run into that sooner or later...