In this video, use DescTools package and XLGetRange function to import data directly into R from an open Excel workbook.
- [Instructor] Before we can actually begin the import of data from Excel there's one more package that we need to install and as long as we're still logged on to R as an administrator we can pull it down by choosing packages and install packages and we'll use the USA mirror. And the name of the package we want to install this time is RDCOMClient. And if it's not obvious as of yet I should say that you only need to install a package once.
You do not need to do this every time you want to import any data from excel. So there's RDCOMClient right there and I'll click OK. And it gets installed on the machine. And at this point I am going to quit R and I'm going to restart R because in order to run this properly, I want to be logged on to R but not as an administrator, and currently in order to download and install the packages I've established myself as an administrator, so I want to first quit R and now I will start it again but when I do it will not be as an administrator.
Okay, now I'm going to get at that DeskTools package and the XlGetRange function. I'll start by typing library and then DeskTools, the name of the package that I want to get at. And before I hit return I'll look back at DeskTools to make sure that I've typed it correctly, because as you know R is case-sensitive and it's easy to mistype the name of a function. Once that's in there I can establish my dataframe, and I'm going to call the dataframe FluData, and then I type the assignment operator.
And I'm going to now type the name of the function I want to use which is XlGetRange and I'll give it one argument, header equals TRUE. And the meaning of that argument is that it informs R that the first row of the selected range in the active Excel worksheet consists of the names of the variables that I'm pulling into this dataframe named FluData. One other point is that if you're really in a hurry you can abbreviate TRUE to just the letter T, although it should still be in upper case.
And I hit return, it then gives me the message that it is loading the RDCOMClient function, and we now have a dataframe in R named FluData, and if we want to make sure that what's in it is what we expect then we can use the head function, and then type the name of the dataframe we're interested in, in this case FluData, and return. And we see the first six cases and they have the same variable names as is the case in Excel and there are our first six cases so we know that the data has come across properly.
I recommend this method for getting data into R because although R has various functions and capabilities as far as putting data into a dataframe, they are somewhat cumbersome, they're somewhat clumsy, and they're nowhere near as easy as it is in Excel to populate a dataset, so my tendency is to populate the dataset in excel and then use a function like XlGetRange to pull the data into R.
There are other methods, you can read CSV files, comma separated values as you probably know, as well as other files, but I find those are somewhat clumsy as well because the length of the path names to those files in the Windows that exist in 2016 can get pretty cumbersome themselves, so I just find this as a matter of course to be the most straightforward way of pulling data into R by way of Excel. So now that we've got the data in there in the dataframe, we can get ready to start actually analyzing it and we'll do that at the start of the next lesson.
Learn how to use R and Excel to analyze data in this course with Conrad Carlberg. He takes you through advanced logistic regression, starting with odds and logarithms and then moving on into binomial distribution and converting predicted odds back to probabilities. After this foundation is established, he shifts the focus to inferential statistics, likelihood ratios, and multinomial regression. Conrad's comprehensive coverage of how to perform logistic regression includes tackling common problems, explaining relationships, reviewing outcomes, and interpreting results.
- Recognizing the problems with ordinary regression on a binary outcome
- Quantifying errors in forecasts
- Managing different slopes
- Forecasting odds instead of probabilities
- Limiting probabilities on the upside and downside
- Working with exponents and bases
- Predicting the logit
- Working with original data and coefficients
- Establishing the Log Likelihood
- Interpreting -2LL or deviance
- Establishing a data frame with XLGetRange
- Using the R functions mlogit or and glm
- Understanding long versus wide shapes in data sets