How to import data from excel into R studio. R programming for beginners

hello people of the Internet welcome back to our programming 101 this is where you discovered that our it's not only powerful and useful but also fun and easy to use in this video we're gonna talk about how to get data from Excel into our in a previous video I talked about how you can save a file as a CSV or comma separated value file and import it using the read CSV function in this video however we're going to talk about getting it straight from Excel straight into our even complicated cases where the doctor might be sitting in a separate tab or the doctor might be sitting in a funny place in the spreadsheet we're gonna get it all into our no miss no FS and I'm gonna finish this video in about three minutes you're gonna know exactly how to do this so stick with me if you want to learn about our programming then you have come to the right place on this YouTube channel we're creating our programming videos on everything so let's think about what we wanted to do okay if we've got a we've got an Excel spreadsheet over here I've got the same spreadsheet but in different kind of ways with different kind of problems it's the same data we want to get that data into our environment here as an object that we can use we can analyze it we can do graphics etc etc now there's more than one way to do that up here at the top on the right there is an import from Excel option if you click on that you're gonna get this little this little screen over here I'm gonna say cancel I'll tell you why if you go and you find the dot on your hard drive down here at the bottom on the right and you click on that you've got an import dot option click on that you get the same screen except in this case it's actually giving you the URL or in this case that position on your hard drive where the dot can be found so that's a little bit more useful so this is a useful tool to help you import your data from Excel if you don't know how to write the code how to write the code to get are to go and fetch the doctor and bring it in and create an object eccentrics if I only teach you how to do both so let's just quickly have a look at this tool at the top is where the dart is gonna be found and in this case you can see it's on my hard drive then underneath that there's a preview of the data this is what its gonna look like when you bring it into our you've got a few options here this and pulldown menus you can change the kind of a variable that it is that it's looking like we might want to make that into a character we might want to say this is numeric we've also got the option of skipping we can skip that variable if we want to - free etc etc down at the bottom on the left you've got a few more options firstly there's a name so this is the name that our will assign to the data set whether in imported and it will create an object in your environment with that name so we might want to call it my daughter for example you can choose the sheet that R is going to look at all the tab so if you look at our spreadsheet here we've got three tabs at the bottom or three sheets that it could look at and by default or it's gonna always look at the first one but you can tell it with this pulldown menu to look at one of the other two or three or whatever you've got you can specify a range in the spreadsheet itself and Rick I'm going to show you an example of doing that in just a minute you can say you a maximum number of rows and it say for example use 25 rows of data you can see that are automatically narrows down the range of dots that it's going to import just to the top five if you just leave if you leave that blank and then port all of it this skip function is very useful because if we have a look at our daughter over here alright has original data set that's all looking nice neat and tidy in the the gap tab with this sheet over here we've got the same data but in this case the first two rows are not very useful so we could tell our using this tool to go to the gap and to skip the first two rows and voila you'll notice that R is always using the first row as the names of the columns or the names of the variables if you can click first name as rows will first Rovers names it'll assign its own names to the variables like at the open daughter in via is an option if you want to view the data immediately after having its imported it then fine you can do that I usually I usually uncheck that right to show you how to use these two functions the range and the n/a what's really they were really talking about missing data let's have a look at our spreadsheet if we go to the third tab the one that's called position firstly our daughter's sitting in a funny place on this break sheet secondly I've deliberately put in some missing daughters and I've just put I've just said in the start frame missing daughters gonna be two stars if we go back to our studio what do we want to do firstly we say this is the third sheet the position sheet that we're looking at we want to tell it what range to look at so let's go back here this has got to be from c4 to G 14 c4 to G 14 and immediately in our data preview we can see that R has found the dot exactly where we are to here we can see these these little two stars are there and it hasn't recognized that as missing data at this point so if we tells if we tell our that two stars bamm-bamm is missing daughter now have a look and it's gotten a it's recognized that as missing daughter now this is where things get interesting in this tool we've got an import button and you're gonna be tempted to click on that don't click on that I'm gonna say it instead of doing that click on this little icon at the top on the right just above this code section and it's gonna copy the code that you need to the data into our click on cancel go into our studio into your source and paste will code straight in there okay let's have a look at that code before we run at chef control one to zoom in on the source the first line of code that could paste it in is it says library read excel read excel is a package we've talked about packages in a previous video packages expand the vocabulary of our and the capabilities of our read excel is automatically installed on your computer if you're working in our studio so you don't need to install it once something's installed once it's there forever but you do need to load it if you want to use the function right so in this case we had type in library or you can use require that's another way of calling or loading a package next my data that's what we've said this object is going to be called so once we've pulled the data into our we want to assign that data to an object so that we can use it we've calling that our daughter my daughter we could call it anything so that's fine next we've got the name of the function so in this case the function is read Excel next to a function we have open and close brackets and within the brackets we have the various parameters and arguments this is how I tell our what to do the first thing we do and we separate them with commas and we can actually put these on funnels on separate lines so we could actually put that on a line like that so then a nice little road it's easy to read the first thing we do is we tell our where to fetch the data and what the file name is in this case the data is in a subfolder within my working directory called data then we put forward slash the name of the file itself and the extension all within inverted commas next in this case we're telling our that the sheet that we want our to look at is called position it's also the case that this was sheet number three so we could change that to three if we wanted to we've said that the range is between C 4 and G 40 said that na it must recognize the two stars as missing values na so let's go back to the four quadrants in our chief to control zero we're gonna make this a little smaller so we can see what we're doing and if I run these two lines of code right I have to run both of them as expected are as import of the data it's created an object called my daughter if we click on that object we can have a look at it we can see that our has identified our missing daughter as na and now we can start our analysis now there are many other arguments that you might want to include in in importing your daughter just so that you know if you push question mark and you push in a function in this case read Excel and then come and enter our brings up all of the help that's required to use that and in this case you can see all of the various arguments that can be used in the read excel function so if you are serious about learning how to analyze data and you want to learn our programming then hit the subscribe button now and hit the little bell notification if you want to get notified of future videos

Channel: R Programming 101

Views: 136,344

Rating: 4.9206848 out of 5

Keywords: import data from excel, importing data from excel, R programming for beginners, R studio, data science, import excel data

Id: cnD1op2Oo3M

Length: 8min 2sec (482 seconds)

Published: Wed Feb 20 2019

Reddit Comments

[u/Demortus]

data <- readxl::read_xlsx("data.xlsx")

^ And that's how you load an excel file into R with 1 line of code.