Resources
Mar 15, 2022 |
59 min
|
4.7k views
|

George Mount | R for Excel Users - First Steps | RStudio Meetup

Abstract:
Excel's built-in programming language has served as an entry point to coding for many. If you’re a data analyst steeped in Excel, chances are you could also benefit from learning R for projects of increased scope and complexity.

This presentation serves as a hands-on introduction to R for Excel users:

How R differs from Excel as an open source software tool
How to translate common Excel concepts such as cells, ranges, and tables to R equivalents
Example use cases that you can take and apply to your own work
How to enhance Excel and Power BI with R
By the end of this presentation, you will have a clear path forward for building repeatable processes, compelling visualizations, and robust data analyses in R.

Speaker Bio:
George Mount is the founder of Stringfest Analytics, a consulting firm specializing in analytics education and upskilling. He has worked with leading bootcamps, learning platforms and practice organizations to help individuals excel at analytics. George regularly blogs and speaks on data analysis, data education and workforce development. He is the author of Advancing into Analytics: From Excel to Python and R (O’Reilly).

Link to George's white paper “Five things Excel users should know about R”https://stringfestanalytics.com/five-things-r-excel/

Working group sign-up for those interested!

Within many organizations Microsoft Excel is a preferred tool for working with data for non data analytics users. In order to build a data driven organization, source data and analytical models must be accessible to all data users (technical and non-technical) within their preferred tool. Let’s rally the R community to welcome Excel users into our data driven culture by building an Excel add-on to access data and models available within RStudio. If you're interested in continuing this conversation and joining a working group, let us know! rstd.io/excel-r-community

Links shared at the meetup!
✨ George's GitHub/ Presentation Resources: https://github.com/stringfestdata/rstudio-mar-2022

✨ Packages? Where to find them & recommendations:
CRAN Task Views: https://cran.r-project.org/web/views/
Mark shared: for folks who primarily use excel to present formatted tables, the `gt` package is a great way to start doing this programmatically in R: https://gt.rstudio.com/
Ivan shared: In addition to regular Google, I'd recommend https://rseek.org/, given that the character 'R' is sometimes not search friendly :)
Jeff shared: Fpp2 is great for forecasting and time series analysis - https://otexts.com/fpp2/
Floris shared: https://otexts.com/fpp3/
Ivan shared: If you're into tidyverse, there's an equivalent for time-series: https://tidyverts.org/
George shared: https://dplyr.tidyverse.org/
Ryan shared: This can be a helpful package for dynamically editing tables, like in excel https://github.com/DillonHammill/DataEditR
Ryan shared: This is a great package for making and learning ggplot visualizations: https://cran.r-project.org/web/packages/esquisse/vignettes/get-started.html

✨ Other resources:
Monaly shared: There is a R help group: r-help@r-project.org
George shared: Helpful book/site on statistics: https://moderndive.com/
Ryan shared:Harvard has a good online source (free options) that has a number of classes, the following for stats: https://www.edx.org/professional-certificate/harvardx-data-science
George shared: R for Data Science free book: https://r4ds.had.co.nz/
Fernando shared: big book of R https://www.bigbookofr.com/index.html
Floris shared: Advanced R Book: https://adv-r.hadley.nz/
Pedro shared: The R for Data Science Slack channel is a great learning resource! r4ds.io/join (we just made a channel there called #chat-excel_to_r
Ivan shared: For teams who are deeply entrenched in Excel (like my old team), this tool may be useful - https://bert-toolkit.com/. It allows running R code in .xls, so you can learn R while doing .xls :)

✨ Re: Glossary of terms:
Ivan shared: inner_join() is like VLOOKUP in .xls.
Dan shared: Here's one cheat sheet (glossary of Excel to R) that I just found; https://paulvanderlaken.com/2018/07/31/transitioning-from-excel-to-r-dictionary-of-common-functions/

✨ Extra Meetup Links
Feedback: rstd.io/meetup-feedback
Talk submission: rstd.io/meetup-speaker-form
If you'd like to find out about upcoming events you can also add this calendar: rstd.io/community-events
RStudio conference/submit a talk: https://www.rstudio.com/conference/
Recordings of all meetups: https://www.youtube.com/playlist?list=PL9HYL-VRX0oRKK9ByULWulAOO5jN70eXv

image: thumbnail.jpg

Transcript#

This transcript was generated automatically and may contain errors.

Thank you, Rachel. Thanks, everybody, for coming. As Rachel mentioned, lines are open at any time. We want to get your questions answered and they can be, as Rachel mentioned, anonymous or not.

So let me kind of switch gears here and get to my slides. Okay. All right. I will share some links to get my resources and everything in a moment as well. But if you want to let me know at the beginning, again, we want to make this as conversational as possible. So let me know, what brought you here today? What have you tried? What's worked? What's not worked? I don't have all the answers on this, but we're all helping each other. So let us know. And again, we want to, and there will be an announcement later to help you continue on in this path. It's not the easiest thing to get a team who's used to using Excel to kind of think the art of the possible and using R and see how possible it is. I hope the presentation shows you how easy it is. And then we can take you to the next level to actually see that through to your organization.

Okay. So just for a little cold open, I want to really get it out of your head that R is necessarily harder than Excel. I think that's the word on the street. You do have to code it, but you have to code a lot of things in Excel as well. So I'm not sure that that's the best thing to think about it as being necessarily harder. I've got a few other case studies like this, and then we're going to move into a short demo and then we'll go for questions. I've also got a couple of books to give away. So make sure that you stick around and you may get a copy of my book, Advancing in Analytics. If you don't want a copy, you get a consolation prize. You can read it for free. I will give you a link to do that. So everybody's a winner with the book.

Excel vs R: a few comparisons

Okay. Let's say you want to visualize a bunch of variables. This is how I see you would do it. Now, again, I could be wrong with these examples where I'm showing Excel and R and how you do something. So let me know if there's another way, because there's almost always another way in Excel or R, but this is how I would do it. Let's say, you know, I have a bunch of variables in my data set and I want to see the relationships between all of them. Okay. This would be a lot of code in Excel, right? That's a lot. This is literally three lines in R. Okay. And this is a pretty nice thing to have. You know, you have data, you want to visualize it. That's not a bad idea. A lot easier to do in R. Okay.

So we're going to do a few more examples of that. But again, just to give you a sense of what we're going to do, really understanding how Excel and R fit together in the analytics stack. I'm not going to tell you to get rid of Excel. I'm not going to tell you that Excel sucks. I'm not going to spread any lies about Excel that often happen when people try to push other tools. We're going to introduce R for you, kind of see how all these tools fit together. Hopefully you'll have fun. I hope you're telling that I'm having fun. And I think Rachel is too. We'll see, you know, try to get everybody else on board. And then maybe, like I said, win a book, or if you don't want a book, you can get the link and read it for free. So we're all winners on that.

Okay. The first link is going to be here. Now, if you want to follow along, and I'll put this in the chat as well. And then if there's any place that should be shared also, we can get that circulated. But this is going to have all the resources and everything. Now I'm aware that a lot of people may be coming to this for the first time in R, they don't have it installed, they can't get it installed, because they're at work, etc. Just click this link, you can follow along and that will run, it'll take a few minutes, but you'll be able to run everything that we're doing on the cloud. And then when you read the book, you'll learn how to download it. And then we'll help you make the case to get it installed at work, hopefully.

Okay, so if there are any questions there, let me know. I'm going to go back to the slides and we'll keep going here. And questions are welcome at any time. Okay, I'm going to skip this slide. This is from the book, just to situate all the various parts of Excel. Again, I'm telling you that don't uninstall Excel if you're here. That's not my takeaway by any means.

Where R can help Excel users

All right. So where's R going to help you? Now, these are some things and if you've used R before, if you're an Excel user, I just kind of boiled this down into a few categories of tasks that I find really helpful if you're an Excel user, where R can help. Now, the first thing I'm going to mention is Power Query. It's kind of the elephant in the room. That's the first question I often get. If you're using Power Query, you're not in Excel, let us know. But Power Query is a data preparation, data transformation tool in Excel. And it can do a lot of the things that R can do, to be honest. It can build a reproducible workflow. It can work with big data. So this thing about, oh, you can't do a million rows in Excel past that. If you're using Power Query, that's not necessarily true. And this is not an Excel course per se. But just to know that Power Query does exist, but there's still things that are helpful with Excel.

So like data visualizations we saw. You have a bunch of variables, you want to visualize them. You want to iterate on that. R is going to help you there. Anything I found that where you're working with groups, where you have some data and you want to go through group by group and do some kind of complex transformation, think about R for that. Again, window functions kind of related to that. Cumulative averages, lagging variables, leading variables. You're going to see some examples where Power Query is kind of challenging for that. And then anything having to do with randomization. Excel, I recently learned that Excel's random number generator is proprietary intentionally. They have not shared how the random numbers are actually randomly generated for a reason. And that has proprietary benefits. But if you're trying to make your workflow reproducible, that might not be a good thing if you want to seed your random data. So those are just a few things that would be helpful.

So again, just to make this really concrete, I want to create an index column by group. Now, again, if you have another way to do this in Excel, let me know. This is the way that I found. It's a lot of code. It can't even fit on the slide. R for four lines of code. And it's pretty readable too, right? We're grouping by something. We're going to take an index number that looks like it's based on the row. Then I'm going to print the head of it. I guess that's the first few rows. So it's pretty English sounding. I don't know. I guess it's English, but I don't know what decompressed binary is. What the heck is that? So again, it's not necessarily easier.

Creating a lag variable, another one. You actually have to create your own custom function to do this, as far as I know, in Power Query. In R, somebody's already made that function for you. That's another thing. We have packages in R. They're like apps. Somebody's built a package, and you can use it, and it's bundled the code. And then that means that all I have to do is say, oh, I want a lagged variable. And you're seeing that these are all kind of the same pattern, right? I mean, the whole idea with what we're doing here in R is that these are really supposed to be predictable. And when you're going to do something, it should follow kind of the same paradigm. Whereas you're seeing in Excel, we've been all over the place already, right? We were using VBA, if you remember, to do back here. There's that. And then we wanted to do this, and that looks a little different. And now this is a little different. So everything, nothing's really kind of syntactically matching. And that's really a big thing of R, specifically with the tidyverse we talk about. Everything's kind of meant to be interchangeable parts.

Nothing's really kind of syntactically matching. And that's really a big thing of R, specifically with the tidyverse we talk about. Everything's kind of meant to be interchangeable parts.

Okay. Data profiling. Again, I see some static here. Cool. So data profiling. Let's say you have missing data. Now, Power Query has some ways to do that for us, but it's not necessarily things that you can code. Again, I want to be able to check my data, check my metadata, be able to see what values are missing, how many columns I have. It really worked programmatically, not just with the data, but with the data about the data. And that's one thing that I find that Excel and a lot of the BI tools aren't really so great at. Even something like, oh, I have a database, and I want to list all the tables in that database, understand the schema. It's not really any code generation that you can per se do in a lot of these tools.

And then let's go, I think I have a couple more examples here. Random data again. I mean, what the heck's going on here? I don't know, because I can just do this in one line with R. And this is nice, right? You have a big data set, you want to spot check, right? And this, obviously, if you're familiar with machine learning, there's a lot of randomization that goes on there. You got to split your data, all that stuff. Not only is this really, really hard, but you can't see this randomly so that you know when your colleague does the same simulation, they're going to get the same results. And there's some ways around that in Excel, but by default, that random number generator in Excel, you can't set a seed on.

Okay. This slide, I think, is good to really understand that learning R is an investment of your time. And you have a couple ways when you do something. You can hack it together manually, and sometimes needs must, and, right, you need to get something done by the end of the day, you can hack it together in Excel. But in the long run, you're going to save time, and you're going to win, right? You spend all this time, you're goofing around, you're running scripts, and it doesn't work, and your boss, your colleagues are thinking, what the heck? Why can't they just get the report done? But lo and behold, you get to that tipping point, and everything's automated, you don't have to do things manually anymore, and you're out of that kind of infinite loop, so to speak, of just coding things, hacking them together in Excel. And not to beat up on Excel, because that can happen in any program, but R is going to have a lot of tools that are going to help you get out of that rut.

Book giveaway and demo intro

Okay. So, let's see how we're doing on time. Oh, yeah, one thing I want to do is a giveaway. So, we're about halfway through my formal remarks, and then we can get to the questions soon. We have a chat, I believe. I see a lot of comments, so we'll make sure that we kind of get the conversation kicked off. But I think the first thing I'm going to do is, okay, I see one from Ivan. I did say that it is sort of reproducible. Yes, I fully agree. So, yes. Okay. I have, hopefully nobody saw that, I have a few data brain teasers. So, what I'm going to do is pull one up here on the screen, and the first person who types the right answer in. So, this is going to be an image of some kind of data or statistical concept. If you get the answer, and you type it in the chat, you'll get a copy of the book. Okay. So, in three, two, one, this is going to go up on the screen, and actually a little later than that. But whoever gets this right, just type it in the chat, try to sound this out verbally, and you get a word. Okay, John. All right. Congratulations. We got a lot of people. Maybe that one was too easy. But John, send me a message, and you can get the copy of the book. So, congratulations, John. And again, if you don't win, don't worry, because you can still read it for free. So, it's not the end of the world.

All right. So, for the next 15 or so minutes, maybe less, we're going to do, and I don't know how much of this we'll get through, but I just want to show you a little example of using R to conduct some data analysis. Okay. So, I'm going to call this data baseball, and I've split this into a few steps here. Okay. So, you'll find this, we'll navigate to this together in a minute. But the idea is, let's say, is Major League Baseball, it's almost baseball season. I want to know, is there a relationship between a team's payroll and other variables, right? Can we use the team payroll to understand how much they win, how many people attend, right? And if you're a data person, you may be thinking these are dumb questions. That's cool, but we can still start here, right? And we'll look at how to do that.

A few steps. So, we're going to import the data. Think of this as the little game of baseball, right? Bring in the data. We're going to do some basic data profiling. So, how many rows, how many columns, how many tables, et cetera. Then we'll analyze the data. Visualization, I kind of call the shortstop here. So, we'll get to that. And generally with R, and that's, I think, important to understand if you're an Excel user, your data generally isn't going to live in R. You're probably going to get it from some other place, and that could be a database, or an API, or an Excel file, or wherever, right? And then you'll do your work in R, and then you're going to send it out. Now, probably for a lot of you, that might be sending it out to Excel. So, I'll show you a couple ways to do that. There are some options. If you wanted to, you could fully automate your Excel report and do that from R. You might not even probably have to open Excel if you don't want to. You could do everything in R.

Okay. So, I'm going to go to here. Now, if you have the binder open, you'll see RStudio open and it'll look something like this. I think you'll go to files, and you'll see this database ball. This is RMB for R Markdown. Now, if you're familiar with Markdown, the idea is we're able to use plain text characters to render text. And you'll end up, and this is another cool thing to know, you could make a fully reproducible report here with R. And, right, this looks like something that you could share around the office, no problem. Okay. So, we're going to work through this. And again, just to be cognizant of time, I may not go through this line by line, but just to show you kind of what R is, what it does, et cetera.

Okay. So, the first thing to understand is we're going to use a lot of packages. This, think of these as like the apps of R. Okay. So, you get a smartphone. It's pretty cool. And it's got some basic utilities. You can make calls and

schedule appointments and stuff like that, take notes, et cetera. But really, a lot of the power comes in the apps that you download from the app store that let you do whatever special use case, or help you do things a little more productively. So, that's going to be the same thing with R, right? So, we're going to download apps. We're not going to get into the specifics of how you download them. You can read the book on that. But just for the mental model here, you'll download the app, you'll open that app to use it. So, you'll see that I've got all sorts of stuff that we're going to use here.

So, there's a lot of cool data that you can find to practice with in R, which is alone a real blessing. The tidyverse, I mentioned a time or two. So, this is really a package of packages. We're going to use that a lot to create visualizations, to model our data, to do all those data analysis tasks. And then there's some other ones that I'm going to use, just for maybe I really like a function that happens to be in that package. A lot of different ways that you can use it. And then to write to Excel, which might be helpful. So, you'll see that I'm running this and everything's being generated down here in the console. I do have in the book a brief tour of RStudio. I will also say that RStudio has a lot of tremendous learning resources. And really the idea of RStudio is everything you need to do your job in R is here. So, there's a reason there are so many panes and windows. But you'll get more familiar with it. I would definitely encourage you early and often to check the help window up here.

There are some really great cheat sheets. There's also a help tab over here. You can learn more about different functions, packages, things like that. So, I would say that's another real nice benefit of using R is just the really robust documentation and help specifically in RStudio.

So, we're doing that. We sent this down to the console. Now, again, this is in a script and this is in a markdown, R markdown script. So, I can save this. I can render it later if I want to, to make a nice report that I can show around the office. So, I'm going to bring in my data.

And let's see why. Okay, there we go. Control shift enter if you want to run these chunks is going to be the keyboard shortcut. I believe on Windows, Mac might be a little different.

But now I'm going to start with selecting my data, visualizing it, all that kind of stuff. It's all going to come down to profiling the data. Now, what's really nice with R, and again, to be fair, someone mentioned Power Query 2, but there's never going to be a need to like randomly delete data or hide columns or any of that messy business that gets people in a lot of trouble. Everything's going to be programmatic. So, if I have a data set and I only need four columns and you can almost kind of read it here, I'm not going to delete those other columns. That's not a good idea. You want to keep your source data intact, right? And then you can make a copy of that and do what you want to from it. And that's what I'm doing here. I'm saying, okay, I have a data set. I'm going to select a few columns. I'm not going to hide them and I'm not going to delete them or anything like that. And then this looks pretty nice too. I mean, aesthetics matter. And I think that R Markdown understands that.

Aggregating and joining data

Okay. So, let's see. I'm going to aggregate this data. Now, again, this is kind of like a pivot table. I know somebody had mentioned in the comments about, well, how would I do something like a pivot table in R? That's basically what I'm doing here. So, you're going to see I have a salaries table. Okay. Then you see this funny little icon here. So, basically what I'm doing, think of this as a pipe where line by line by command, I'm cascading my output into the next input. So, I have salaries. I want to group that by your ID and team ID. So, imagine a pivot table and you're putting those in the rows sections, right? And then you need to aggregate it by something. So, these are kind of twin functions in a way. So, I'm going to group that by, then I'm going to say, okay, well, I want the total salary for each team. And then I can see what I have there. Okay. So, that would be your pivot table.

And now what we can do is join them. So, again, if you're using Power Query, if you're using SQL, we have data from different tables based on those relationships. We want to put them into one place. Very easy to do here in R, specifically with the tidyverse. And I'm going to use an inner join, as you'll see. And I have a merged data set there. So, now I have all the data that I need to conduct the analysis. Again, I don't know how much of it we'll get through, but all this stuff will stay up in perpetuity. You want to run it, hang around with it later, you're welcome to do that. But I have my teams, I have the variables I want to check out. So, now I'm able to start iterating on what I'd like to know about this data. And hey, maybe you thought, oh, actually, I wanted to use these other variables. Well, you didn't delete it. So, you're welcome to just iterate on that script and you're not losing anything there.

And another thing, just to know, is that you can easily check, like I said, the metadata about your data. What, okay, in this case, it's not really metadata, but you're still able to see the sum of one table versus another and easily compare it. I find that trying to do that kind of on the fly work in a lot of tools, I mean, Excel is pretty good at it. I'm thinking about things like Power Query, Power BI. They're not really meant to let you do kind of on the fly analyses with your data. It's all based on that relational database and writing any kind of function can get messy pretty quickly. With R, everything's saved in memory, right? So, I'm able to just call up anything, write a function on it, move on with my work, and have this all saved in a script so that it's easily shared, easily reproduced, and so forth.

With R, everything's saved in memory, right? So, I'm able to just call up anything, write a function on it, move on with my work, and have this all saved in a script so that it's easily shared, easily reproduced, and so forth.

Summary statistics and data profiling

Okay. So, data profiling, let's see how we're doing. I'll give this a few more minutes, and then we'll get to questions and all that.

Summary statistics, I'll also say R was really built for statistics. So, you even look at the official R, the R project for statistical computing. So, a lot of the things that you want to do related to statistics are going to be kind of built in. Now, often you can augment those things with, again, these packages, depending on what you're looking for. But something like, I want to get the summary statistics, there it is. A lot of data, a lot of packages help you, like I said, augment that exploration. So, for example, I like this SCIM package. This is from our SCIM function. This is from a package called SCIM R. I'll make this a little bit bigger.

But again, you know, you could get some of these things in the data profiler in Power Query, but it's not necessarily going to be something that I can just keep in my script and run to it. There's going to be a lot of pointing and clicking. I can't necessarily easily share the output, right? So, in this case, I can see a lot of what I want to know about the data, and things like missing values, things like the distribution, everything. So, you're really able to get a high level view of the data, and then zoom in from there and work on what you want to do with it.

Okay. And I can move this back down. I like this describe function. This is from a package called Cyc. This just gives you a little bit more meat to those descriptive statistics. So, if you want to know things like the range, the term to mean, all that, right? So, a lot of options. Usually, just like in Excel, there's almost always multiple ways to do something. It's probably going to be the case in R as well. So, you can get to know your favorite packages, share them, and help them. You can even support them because they're almost all going to be open source. They're taking contributors, so you can join that community, help with the documentation. As you get really good at R, you can maybe even push to the code base. So, it's pretty cool to know that all this stuff is being built by you, right?

Data visualization

Okay. So, let's see what we'll do here. I know we've got a couple of minutes. So, data visualization is a great one to know. I'm not going to get too much into the syntax here, but one thing that I really want you to see is that if you have like a histogram in Excel and you wanted to change it to, I don't know, a tree map or a bar chart or something, it would be kind of hard, I think. You have to change the chart type, and sometimes your axes get messed up. You want to switch the axes, and there's a lot of pointing and clicking. It's hard to know exactly what happened. Generally, with what we're doing here in ggplot, and this is another tidyverse package, you just have to change a function, change an argument, and that's another thing I try to really point out to Excel users. You've used functions a lot, right? You've used index. You've used VLOOKUP or XLOOKUP. You understand, okay, well, this argument is optional, and this one is required, and this has a default. That's the same kind of stuff. We're going to use a lot of functions in R.

So, like I said in the description, you have a big head start, and this thing may not really look like much right now, but if you break it down, you're seeing, okay, well, I have a function, and here's an argument, data. Okay, so that's probably where the data is coming from. That makes sense. Okay, AES, that might not make as much sense right now, but let's drill into that, and we see, okay, X, so this is a chart. Okay, so the X is payroll. That makes sense, and then I don't know about geom, but this says histogram, so you're kind of thinking, okay, so basically, I'm making a histogram with payroll on the X axis from data.

Understandable, and again, if you wanted to change something, it's very easy. In this case, I think I'm just changing my variable on the X axis. Okay, so I think it's 1230 right now. Again, you're welcome to run through any of this on your own. The end product is going to be, and let me actually pull that up in my, because I had already done this. Okay, where is that file?

The joys of having a lot of windows open. Okay, what I wanted to show you is the output that you could get. Let's say you wanted to use R and do all the cool things you learned today, but you still have to put it into Excel because that's what people at work demand.

This could be, and again, there's the code for this in that notebook.

Okay, it's opening just on the other window. Okay, so here we go. So we have a table. I wrote this from Excel. Now, I'm sorry, from R. Now, if you needed to maybe, I don't know, make this formatted and add the currency and all that, you could do that in R. If you're a VBA person and you've worked with programmatically formatted columns and things like that, you could add these charts. Now, I put a chart from ggplot. You could build an Excel chart from R as well. Again, really anything you needed to do to build some Excel product, you could do it in R. This is just the tip of the iceberg. We only have so much time and you're seeing that I already didn't get to show you everything, but like I said, you can check it all out later.

Q&A and discussion

Okay, so let me go back to the slides here. I think we'll do a giveaway in a minute here. Okay, the other thing, and I'll make sure that this gets, and then we'll move to the questions and discussion and everything. If you want to summarize what you've learned today, this is going to be a white paper that I have. It's just a few pages just to learn about R as an Excel user. So, you can download that white paper from my website and we'll get that sent out to you quickly.

Okay, so let me go to questions. I think this is good. Yeah, let's do-

I can have both questions, George.

Yeah, that would be great. Yeah, and then we'll do a giveaway in a bit too.

Awesome, thank you. I see a few people have put questions into the chat, but just a reminder, you can use Slido as well to either ask anonymously or put your name. It's just easy to see them all there and organize them, but I think Gustavo asked a question.

One barrier I found was translating terms from Excel to R. For example, what's the equivalent of a data frame in Excel? It'd be nice to have a kind of like a dictionary or glossary of terms.

Yeah, it would be good to have that all summarized. I do, I mean, that's really a foundation of the book is to translate those structures and functions from Excel into R and that's the way we learn things is to associate what we already know with things that we're learning now. So, it's cool that you, Gustavo, are thinking in that way. That's going to make things a lot quicker. Now, as far as a cheat sheet or something like that, that's going to get added to my to-do list imminently here. However, I will say that the book does talk about that, right? What would a vector look like in Excel or vice versa? What do you do with a pivot table in R and so forth? So, that's a good question with kind of an answer. It's just not as condensed as you might be looking for.

Yeah, and if anybody has other resources, too, or things that you found helpful, feel free to put them into the chat, too. I see Mark here. I'm going to turn it on so people can unmute themselves. Okay. There we go. I see, Mark, you mentioned for people who primarily use Excel to present tables, the gt package really helps. Do you want to add anything there?

Yeah, sure. I was just going to say I think that there's a lot of use cases for using R that go beyond just data analysis, you know, just

speeding things up and making your life easier in other areas, and GT is a good example of that.

Awesome. Thank you. Jacob, I see you had your hand raised earlier. Do you want to jump in with a question? No, sorry. It was accidental. Sorry, we were too busy. Thank you.

Okay. No worries. Awesome. I see, Mickey, Stuart, you had asked a question into the chat. Would you want to jump in and ask that live? Sure. One of the questions I had just as a novice playing around with R is that there's an enormous number of packages.

I am struggling with finding which ones I need and how to look for a function if it's not in a package I've already got. Does that make sense? It seems overwhelming. Somebody pointed me in the chat to the CRAN webpage, and that's got... I can see where somebody would find that helpful, but it's still too abstract. There's 20,000 packages. Yeah, it's hard. Yeah. So that's my thought for the day. Yeah. Is there, Mickey, any particular area that you're looking to do, a domain or a use case or something like that, that might be the first place to start? Mostly looking at financial applications, George.

Okay, cool. So R has a pretty sizable number of time series related things. I would say that a lot of the packages and functionality are going to be built off of some of the tools used in the book. So the first thing would be, going back to RStudio, the help menu here. Check out these cheat sheets and get familiar with some of these. Maybe not all of them, but dplyr, ggplot2, right? So just learning the fundamentals, because there's definitely going to be a power curve. And even in my script, most of these, probably 80% of these functions are coming from the same, I don't know, two or three or four packages. And then there's like, oh, hey, I really like this function, and I'm going to call on this package. It could be a little dangerous to do that, because it's just more bloat to your script. But yeah, get really good at probably a handful of them. Now, the CRAN task queues are good, but they are maybe overwhelming, because there's so many of them. But yeah, get familiar with these basic ones, like the tidyverse, right? And that's actually more than one package. And then from there, I would say you can start to specialize in, and I'm sure RStudio has some resources for finance. But that would be my kind of loose recommendation. And if anybody has other ideas on what to do with that, then we're happy to hear that too. But thanks for the question. Yeah. No, thanks, George.

I see a lot of great links being shared in the chat too, and I can collect those together. But I feel you on that. Sometimes there's also so many different ways to do the same thing as well. And I think something that has been most helpful for me is when I'm actually googling, how do I do this? And I see the packages that other people were using for that too.

But Ryan, I see your hands raised too. You want to jump in? Yeah, I just wanted to add, I feel your pain. Our journey started in 2018 when I decided for my first New Year's resolution in my life, I was going to stop using Excel or Google Sheets, because I saw them as the same, and only do stuff in R. So it's really hard to go there. Fast forward four or five years later, I think I would look at it differently. Rather than trying to solve, find the technology to solve the problem, what's the problem you're trying to do? In R, I think you could think of it as broken out into different kinds of steps.

So there's always, you're going to use packages to bring in data. So that would be like a step, right? Your financial questions is no good unless you can bring stuff in. So there are definitely some great packages around that. Get to know ReadR. Again, this comes from tidyverse. So ReadR, R-E-A-D-R. And then there's also ReadXL for Excel stuff, seeing how we're in here. Those are two great sources. There's other things if you're connecting to databases, and if you think of it that way, that's there. Like George was saying, dplyr is going to be your best friend. That's your toolbox. It has your wrenches, your hammers for cleaning up stuff. You're going to do a whole bunch of manipulation in there. That would be your package. And then TidyR would be another one for cleaning. And then depending on the analysis, right? So that's step one. Step two is like connect, doing clean. In the data world, we call that wrangling or munging, like cleaning up kind of stuff, your broom and your dustpan. And then there's the analysis kind of point. And like George said, ours got a ton of financial stuff in there, like lots. And then you have your visualization and then you have your output. So if you break it into like four or five sections, connect, cleaning, wrangling, analysis, output, then it's a little bit less daunting to go through 20,000 packages on CRAN plus the internet of like everyone's hosted GitHub ones. Not sure if that helped or if you're like, yeah, thanks. I've kind of already done that. But like that's how I've stepped through my thought patterns. What am I trying to do in this section rather than what are all the packages that I need to learn? Thank you, Ryan. That's really helpful.

What am I trying to do in this section rather than what are all the packages that I need to learn?

Reactivity in Excel vs R

I see there's a few other anonymous questions on Slido I want to make sure I get to as well. And one is Excel is reactive. So for example, output is automatically updated when input changes, while R is generally not except with Shiny. How do you explain this to Excel users?

Okay. Yeah. When you have R and you're storing, the way I generally demonstrate it is that you in Excel, like was mentioned, you make one change and everything triggers. So I'll just open up a spreadsheet and make one reference point to another, point to another, and then change the first one and you see everything trickle down. And I think everybody has a horror story of that going wrong. So I don't have to really do too much convincing there. Whereas in R, I'll generally just store a couple of items as objects and pretend that we're putting each of these into a box and we're naming this box, my object or my string or something like that. So this object is in your computer's memory now. It's tucked away. It's a box that nobody's going to open unless you tell your computer to, and they're all kind of separate entities now so that I'm not going to make one change here and it's going to randomly change some other object. I guess you could still do that given how computers sometimes behave, but that's the general idea is you're right, that it's not the kind of spaghetti of all these references. So that was a good question. I have some of those examples. I think I have some box illustrations in the book as well. So that might be cool to check out. But yeah, that is a key kind of mental model milestone for getting into this.

Learning R and Python together

Thanks, George. There's another anonymous question that says, I would like to ask a hypothetical question. Can a person upscale in both R and Python simultaneously?

Oh, yeah. So the big one. Okay. So the book talks about from Excel to Python and R. Okay. Although you will, if you read it, R is the second section right after Excel, because in my opinion, and some people think differently, but I think R is a little easier for Excel people to get into because, like I said, it was really built for statistics and data analysis. Everything in Excel is built around rows and columns, right? That you open it up and there are rows and columns that you see. And R is kind of built the same way with that data frame that's been talked about a couple of times. Python, not necessarily so much was built around the idea of rows and columns. And that's not a problem. That's not to knock Python or anything, but it just takes a little bit more of a setup to do that. So I think that R is probably easier for Excel people. Now, as far as the question about learning both, I would, again, suggest depending on what you want, depending on your time, individual results may vary, as I always like to say. So there are really a lot of contingencies, but this idea of the mental cross training, right? And if you're going to learn to do something in R, try to see if you can refactor it into Python or vice versa. Doing something in Excel, try to do it in Python or R. So I think that kind of interdisciplinary, if you will, approach is great. But as far as how to diversify your learning portfolio, it's, like I said, it's really an individual kind of question.

Resources for learning stats with R

Thank you. I see Manali, and apologies if I pronounce your name wrong, you had asked a question earlier, would you want to jump in live and ask that?

And I can add the question, I can ask the question, but feel free to jump in and add any more context. But the question was, any recommendations on resources that teach stats using R as the tool?

Oh, okay. I like the Modern Dive book. It's a nice introduction that's pretty compatible with a lot of what we've covered today with R. I do a little bit of stats teaching in the book. But we start with Excel, just because people are familiar with that, and then reproduce those. So if anybody else has favorite resources for learning, learning stats, you're welcome to let us know. But I think that Modern Dive is a pretty good start for that. Awesome. Is it moderndive.com? I just put it in the chat. Yeah.

Okay, cool. And I know a few people had asked me about the recording, if you joined a bit later, and yes, the recording will be shared to the RStudio YouTube, and I'll put it on the meetup page and LinkedIn and Twitter, or wherever you found the meetup too. And I'll try and go through the chat to save all these awesome links and recommendations that people have too.

Building logic and structure in code

But just to go over to a few other questions from Slido. I see Gustavo, you had a question about building the logic behind the code. Would you want to jump in and add some context to that question? Can you hear me?

Yes, I can. Perfectly. Thank you. Yeah, I mean, I think sometimes, you know, so I'm sort of at the beginning of my learning path with switching to R. And I don't know if this is my lack of, you know, having strong logic, even though I think I'm a logical person. But, you know, you know, you have to kind of have, when you're building the code, you have to know what comes first and what comes after. And then you sort of build in that structure in your code. I think we're, for better or worse, we're taught to, to kind of react. I think somebody said that earlier in Excel, you just kind of do things and you see and then you, and you hide things and you delete things. But you're not building a sequence that you, you know, like you do in coding. And I think, you know, I don't know if I'm explaining myself right. But I think there's an order on how you code and what comes first and after when you're trying to build your data, you know, for analysis. And I think that's where sometimes I get stuck.

Okay. Yeah. So I think it was Ryan was talking about trying to break it into discrete steps is helpful. If you're an Excel user, then getting familiar with Power Query and seeing each of your steps, again, transformed into discrete things, right? That even the idea of extract, transform, and load, right? Three distinct steps. We're getting data from somewhere, we're transforming it, and then we're loading it somewhere else. There's always going to be iteration. There's also a really cool, if you read R for data science, there's that tidy workflow. And some people in the audience might know what I'm talking about, where it shows you each of the steps, but then there's the part of the middle where you're modeling and you're visualizing. And it's really meant to be iterative, right? But, but there is, there is a sequence that you follow, but there is going to be some give and take there. So if anybody knows, okay. Yeah. Rachel, thanks for that. So hopefully that was a little helpful. Thank you. Cool. Thanks.

Thank you all for the great questions too. I'm looking over to Slido and I see we've answered everything there, but I know there's definitely things I've missed from the Zoom chat with some of the comments. So feel free to raise your hand too and jump in.

I'm curious to hear from all of you where you are in your learning journey, or also

if you're starting to maybe help teach other people across your team, what has worked best for you all too. I know a few people are just getting started even today with joining this talk. So it may be helpful to also share with each other what has worked best for just getting started and resources that were most helpful for you. It sounds like having that glossary of Excel to our terms would be really useful for us all. I see a few people had mentioned that.

Yeah. Good point, Ryan. I see working on a real work problem can be really helpful too.

What I've found, like looking back on my own thoughts and then also like this past year where I've been teaching people in our organization is, no offense to anyone with what I'm about to say, most our journeys seem to be very linear. Start here, end here, and you'll be perfect. And that's just how it works. And everyone has 10 hours a day to learn that, like we did in high school and undergrad and stuff like that. Whereas in the workforce, you're trying to solve a little tiny problem and it seems ginormous. Like it seems so big, right? Like when you're first learning. So breaking them down into real problems. And it could be like, I just want to know how to open R, read in a piece of data, right? Like, so we're supposed to do this, get into functions and all those piece, but breaking them down into small little things that you're trying to do and not trying to have a goal of, I'm going to be a superstar, or I'm going to wear a cape that has a big R on the back of it. There are some of those people in the world, and I think we know those names, but do we all profess to want to be that? I think it's being able to be really comfortable and efficient within your own pieces. So breaking it down that way. So in work, like what are you trying to do? Well, I'm trying to bring in this data and like change the name so that they're all capitals, or I want to calculate the average, the mean, the max, or I want to see our relationship, those little pieces and breaking them into modular little goals is really helpful. And for me, when I'm teaching, I try to not use any jargon, any actual names of stuff, because that can be a barrier until people's confidence comes up. And then with confidence, they get the capability and then they're okay to use terms.

any jargon, any actual names of stuff, because that can be a barrier until people's confidence comes up. And then with confidence, they get the capability and then they're okay to use terms.

That's really helpful, Ryan. And also just thinking about, like, what is that first big win for you or like a project that you maybe can save a bunch of time once you actually get to doing it in R or automating something? And I'm wondering if people could share maybe what those are for them or what are the areas or certain projects that you'd recommend starting with? I don't have a particular recommendation of where to start, but in most of my roles, typically I'd be doing all this ETL work in SQL and then pushing it down to Excel. So in that environment, as a self-taught person, you figure out how to do something.

And it's important to build up a bunch of little chunks like that that you can remember, oh yes, one time I had to do this and it was in this job stream. And then I can go there, get and say, aha, I remember now. So building up a bunch of little chunks of stuff that you know work and you can remember where you put them to go back and refer to again will speed the process.

So that's kind of like where I am now. So I'm just getting started in R. So we'll see where this goes. Thanks. Definitely. That's a really good point. Like just being able to reuse your own code and being able to find it is very important too. I see someone else said in to the chat, any regular reporting, especially if the reporting requires copying paste.

I'm going through that right now with making, trying to make slides with R so that I'm not copying and pasting into Google Slides so that every time the data updates, I can just automatically Yeah, good call out. You can make a slideshow with R and the tools that we're using today. I didn't do that because it's that trade-off and sometimes don't kill yourself, right? There's always going to be a trade-off. If you just have to do things once, maybe the sloppy way is okay. Right? It's okay to make PowerPoints depending on what you're doing. If you have a case like with Rachel where the data is going to update and you don't want things to break in that, then it's probably a good idea. If you have to copy and paste, like somebody mentioned, then yeah, think about automating it. But, you know, sometimes I think we as tech people want to just automate everything. And that's a good instinct to have, but it can backfire at times. Let's be honest.

So definitely taking a lot longer, but I'm hoping that it pays off. Oh yeah. Yeah, no, it's an awesome tool to have. And even I've had things where, you know, people have their weekly reporting and they're copying and pasting out of Excel and dumping it into an email or a PDF or whatever. And just, I think it's that art of the possible too, right? Just knowing that you can automate it, even if you don't know how to yet, just knowing that you can and having the aspirations to doing it already puts you ahead of the majority of your cohort. So that's great.

Community building and next steps

Definitely. I see Jamaica had a great idea in the chat and you said you'd love to partner with individuals to work on small projects and build on real world projects to build your portfolio. And it looks like quite a few people are interested in that too. I'm trying to think what's the best way for us all to connect here after the fact. And I wish Pedro, he just read my mind too. That's great. The R for data science Slack channel is a great resource. So maybe we can make a channel there to follow up from the meetup. And just to make it easy to find after the fact, we'll call it Excel meetup. I'll reach out to John who runs that group. But if anyone's interested in joining that Slack, I'll put the link to join in the chat.

But also, before we hit the top of the hour, I also wanted to introduce the new working group that we'd like to kick off. And for that, I'd love to turn it over to you, Ryan, to introduce it.

Thanks, Rachel. It just took me a long time to click those buttons. No worries. Sorry. Thank you, everyone, for welcoming me in and allowing me to be here and take a few minutes of your time. Excel is the bane of my existence and everyone's existence. It's actually where I learned to code. Like you said, George, right? Like you learn functions and that kind of stuff. It got me in that path. I think the superhero in me wants to turn everyone into an R user. And I'm a fan boy and I see its power. But I do know a little bit of the reality that not every single user in my organization, current or potentially future, will probably share the same sentiment that I do. So thinking through it and like in other places, what's the goal? And in our organization, in our hope, the goal is to turn people into data users and using data to make informed decision making. So getting them to use it. And the likelihood of removing Excel is probably 20 plus years away, like realistically in my organization and maybe even more in others. So at another place at the City of Toronto, we took that on and was like, well, let's have people go where they're comfortable. And we made something for Google Sheets. And that's where I'm talking when I go to is knowing that Excel is a big part of my life. And I'm not going to be part of many people's data life, maybe not individually, but organizationally. I would love to make and have something that allows people to gain the power of R with inside of Excel.

Realistically, what could that be? We've been playing with it and creating APIs and hosting them and connect so that people can pull data right into Excel, so that some of that stuff is already taken out of the equation. And then also maybe accessing different models, right? So if you have models, but currently today, it's kind of a pain, right? You have to write VBA macros and all these other things to link up to API. So my thought was, is there a chance for desire or a thought that people would love to collaboratively work on building an add in that connects up where people connect to data sources for their organization and pull it in whether you have sorry for the shameless plug or connect or another source of where you host your data. So love to chat about it. I don't know if that was what you're thinking, Rachel, but like these are some of the thoughts to really push R and help out that transition to a data organization and maybe another step for users to jump into the R world.

Definitely and curious to hear other people's thoughts too. Because I know also Ryan, when we talked about it, I mentioned, doesn't have to be someone who uses R every day. If you use Excel, every day, we want to hear your perspectives to around what would be useful.

So I did just put a link into the chat right now. If you'd want to be part of that working group, you can kind of you can just put your email and name in there. And we can schedule a smaller zoom meeting to just chat about it too. And see what what works best.

But this is always the hard part of silence on here. See if somebody wants to jump in. But feel free if if you have other thoughts to want to expand on anything Ryan said.

But again, I put that form right there. And if you want to join that, that next call, feel free to just put your email in there. I know we're getting to the top of the hour. And a few people mentioned they have to run. I do want to make sure we also get to all the questions that you had for George too. And thank you so much, George, for an awesome presentation. Really appreciate your time here and sharing with all of us. Sure. Yeah. Love to see the people making connections, wanting to start a group, all that stuff. So that's great to see the community starting already within an hour.

I did want to do, I picked another winner. So Michelle C., congratulations. You won a copy of Advancing into Analytics. So if you want to, let me put my, let me put my email in the chat again. And just send me a note and we'll get that sent to you.

And the other thing is, just for my kind of wrap up here, I'll put this in the chat as well. If you did win a copy, don't fear because you can still read the book. Yes, Francis, it is available. You can learn a lot more about the book, the chapters, where to get it and so forth at this link. Amazon, wherever you buy a book, you can read it from the publisher's website. You'll find a link to read it for free. So happy reading. And if you could, please leave a review, right? Because that lets people know of its quality. It's again, the community helping each other. So all I can ask for, for having attended today is if you choose to read the book, leave a review and I'll feel like we've had a pretty nice community building session here. So I think that's everything from me. And I'm happy to answer any more questions. You can find me on LinkedIn if you'd like. I'll put my note in the chat there as well. I've got the website as well. You can check that out where the book is. And yeah, thanks. Thanks again, everybody. I really, really appreciate everybody's time and interest in the topic.

Thank you so much, George. I'm going to stop the recording now if anybody wants to jump in with other questions.