R for Excel Users
blog.yhat.com
blog.yhat.com
Take quick scenario evaluation. In Excel, I change one or two cells and see everything update immediately; in R, you need to re-run your analysis and find the outputs you're after again. There is built-in support for scenarios in Excel. In R you have to code your analysis around it, and do the formatting for comparing, too.
Or take the libraries. Anything that is not statistics is just a pain in R. Everything is possible, sure - but high friction. PMT()/IPMT() functions in R? Good luck. Sure it's easy to code yourself (which is the advice you get when asking R users %| ) but I'm using something high level to not have to bother with that sort of thing!
Graphs? Yeah, there's plot() which is straight out of 1960, or ggplot - which is easy for the simple things and then devolves into afternoons chasing obscure manual pages for this or that setting. Here's one: plot 360 degrees of a sine wave, and it's first and second derivatives. Then explain an Excel user how that works. (this is both because anything non-stats is bolted onto R, and because ggplot is designed around stats graphing.)
Keeping a matrix of data, like a simple database? Sure R can read dozens of file formats from CSV to HDF, but actually editing/maintaining that data is a pain in the ass. Excel, just add a sheet and use vlookup - it will let you sort and filter and copy and validate, all without leaving one software package.
Yes, there are many things wrong with Excel. FFS, there is a conference on how to not screw things up in real life with Excel. But saying 'just use R' is silly. For most applications where starry-eyed grad students advocate R over Excel (because hey, nail/hammer, right?), Excel is just the better choice, even if that means workflows that make programming-literate folk die a little inside every time we have to work with them.
I guess the thing I would ask you to consider when thinking about this stuff is the amount of time you've spent learning Excel. At the time I was learning R I probably had several thousand hours of focused Excel practice under my belt, and I could do a lot with the tool. So Excel was a way better tool for me than R because I was an Excel expert and an R novice. After now putting in about that same amount of time working with R I can say it's a much more powerful and extensible tool. But if you mostly work in areas where Excel...uh...excels, then there's no real reason to make the switch.
I need to use R about once a year -- each time I've forgotten everything from last time, and use a combination of stackoverflow and swearing to do whatever I need to do.
For PML() kinds of functions, I often just google something like "Excel PML() function in R" and something usually turns up: - https://cran.r-project.org/web/packages/optiRum/optiRum.pdf - https://gist.github.com/econ-r/dcd503815bbb271484ff
Another good tactic is to follow some of the R quants on twitter. A really popular package for this is http://www.quantmod.com/
Take your typical Excel user and explain to them that R is significantly more powerful, and they will stop listening the first time they get the arrow wrong on a variable assignment. You probably won't even get that far because the idea of a typing a variable name is foreign even though they've been using "variables" hidden behind cell references. "Why am I assigning a variable... in Excel I just type my data where I want it and click when I want to use it".
Not a month goes by that I don't try to switch some Excel sheet I have into R; I run into the problems with Excel every day. It's not like I don't know Excel isn't great. My point is that the solution to those problems isn't R. I don't know what is, but I know it's not R.
Like, the other day I tried to do a real estate investment analysis in R. I always do that in Excel; I build the model step by step, starting with some basics, then filling in the details of the case at hand as I go. All the time, I can focus on the numbers; adding an indicator or refinement is part of the natural workflow. In R, you always have to switch between 'code mode' and 'data mode'. In Excel, you don't have this difference. Which is at the same time also its weakness, of course.
Sharing R models is a pain in the ass. Others have to get the exact packages, you have to tell them what to look at out of all the variables, ... Shiny sucks for that, it's read-only. In Excel someone makes some changes and sends you back the sheet, boom done. In R you would set up a versioning repo, data is split over multiple files, packages need to be installed, scripts for reporting, ... Fine for big projects, not for the small analysis exercises that make up the bulk of the uses of Excel.
Sure you 'can' blog in R. Last week I didn't want to walk to the shed to get my hammer, so I used a brick to pound in a nail. Doesn't make it right. String manipulation in R is a joke. A function to concatenate strings? Please. Again, R is fine for statistics, but it's not a general purpose replacement of Excel.
> In Excel, I change one or two cells and see everything update immediately
This is the problem - it's all too easy to change cells, misclick, and generally make mistakes. When you do things from a programming language, you need to be explicit about operations, and explicit about what changes you're making.
Spreadsheets should be for entering data only, R is for processing data.
All the examples of enter data, create a chart, oh let's change/update some data, oh let's add something to the file, etc..., are why you end up with incredibly convulated spreadsheets that inevitably are filled with errors.
Enter your data with whatever tool you want (spreadsheet works for this), then process it (R works for this), then shove it in a database. Nice and easy, not error prone, and every tool can have easy access since databases are ubiquitous.
R has the advantage of being easier to do a proper diff between files if a change was made in error, but most people don't think they made a change in error. The change was intentional; they just got it wrong.
Only some errors are obvious just from a glance at the result set. For other errors, Excel makes the developer's job much harder. Subtle bugs can hide the code of an individual cell among thousands, and the user has no means of clearly abstracting that code out.
Seems pretty straightforward to me
x <- seq(from = 0, to = 2*pi, by = .05)
y <- sin(x)
y2 <- cos(x)
y3 <- -sin(x)
plot(x, y, type = 'l')
lines(x, y2, col = 'red')
lines(x, y3, col = 'blue')As the intrinsic complexity of the problem at hand increases, the (intrinsic) difficulty of using Excel just rockets after a certain point.
The more experienced R-user have a lower threshold of preferring R over Excel, since the (accidental) difficulty of using R is low enough for them to do even trivial stuff. It might be much smarter and faster to do the same thing in Excel for just about everyone else.
On the other hand, if people with no previous programming experience whatsoever keep building excel-workflows for larger and larger problems, it WILL turn into a catastrophically incomprehensible mess eventually, since they know no other alternatives than hitting the complexity wall.
(For myself - a fairly decent professional programmer, it requires a bit of swearing and stackoverflow to use R since although a solid core, a lot of syntax and conventions are decidedly non-cs... But so does Excel.)
Excel is fine for opening up a table and doing some quick numberwang.
But, as soon as you have to take your piece of work and start making little variations and tacking bits on, or running it on different bits of data, or God forbid you want to actually test your code (and make no mistake, code is what you are making), well, it all involves rather a lot of clicking and opportunity for fuck-ups.
Of course, R itself hits limitations pretty quickly. While technically it's a general purpose programming language, trying to use it on non-tabular data or to assemble even a medium-sized program leads to pain.
My rough rule of thumb is:
* Checking if it's got vaguely sensible data in? Column https://linux.die.net/man/1/column.
* Just looking, maybe aggregating a column or doing a simple summarise? Excel (or Libreoffice, why not?).
* Giving outputs to someone else or might need to do it more than once? R.
* Need sensible data structures and useful abstractions? Python.
* Need more speed? C or Java or whatever.
I find that approaching Excel as if it were a database or a programming language tends to reduce the replication problem. For example to analyse a daily file:
* set up a data connection to an exemplar file
* refresh the connection with the new file daily
* set connection option to copy formula down so each row is identical
* point to a separate spreadsheet for lookups
* use a recorded macro to paste the calculated values into a cumulative spreadsheet
* do pivots and change over time graphs in the cumulative spreadsheet
I don't think Excel is the best tool for this workflow, but a logical approach makes it "good enough".
This isn't an accurate reflection of where Excel is currently. Excel can run OLAP cubes at this point, which is an exponential multiplier for it's utility as a quick BI solution. Comparisons to programming languages really miss the entire point of why excel is so useful.
[1] http://retractionwatch.com/2013/04/18/influential-reinhart-r...
[2] https://genomebiology.biomedcentral.com/articles/10.1186/s13...
In the real world, there is a huge chasm of difference between people just learning Excel and developers, not many people even understand why you would switch away from the former when it's so convenient, which is why the difficulty v.s. complexity chart is so great, and may actually speak to people in an approachable way.
There are a lot of tutorials for how to do hard things and how to do easy things, but not a lot for how to think of the hard things in terms of the easy things, and this falls in that category. Another good book on this topic is Data Smart by John Foreman, where he goes over basic data science skills in Excel.
Although there is a ML/AI selection bias in HN, there are certainly a lot of people on HN who fall into the intermediate/beginner category (there is a lot of demand for R tutorials which I have been working on), although I would argue that dplyr can legitimately be used at the advanced level. And certainly Keras/Tensorflow is overkill for common business problems.
Well, fast forward half a decade and nobody wrote it, so I did so awhile back. Of all the things I wrote for work, it continues to be the most popular:
https://blog.treasuredata.com/blog/2014/12/05/learn-sql-by-c...
I forget that probably every 5 minutes when working in R.
But man, are arrays a pain in VBA.
The example:
join_and_summarize <- function(df, colour_df){
left_join(df, colour_df, by = "cyl") %>%
group_by(colour) %>%
summarize(mean_displacement = mean(disp))
}
cols <- c("colA", "colB")
dataframe[, cols]Also it's often not as simple as just prefixing an underscore, you end up reading the lazyeval vignette and messing about with quote() and formula syntax.
Where does one "get" R?
For others, the R language website: https://www.r-project.org
Also try the IDE from RStudio to make life more pleasant: https://www.rstudio.com
Good luck! It is a fun journey and a big, deep, wonderful, rewarding rabbit hole.
It's got a lot of good intro materials for R. Though having some understanding of another programming language would be pretty helpful.