Excel to R #2 - VLOOKUP in Excel to LEFT_JOIN, MERGE in R

Key Takeaways

hey friends welcome to one little coder this is our series excel to our where I'm going to do a task a data analytics task on Microsoft Excel which is still one of the most popular analytics tools on the planet and I'm going to replicate the same task on our art studio specifically this is to help Excel users understand arbiter and then for them to feel how it is easier for them to use a programming language and do the same thing more efficient manner so let's get into this video in this video we're going to see vlookup so this was suggested by one of our viewers so thank you very much for the suggestion and let's go ahead and see what does we look up and how do we do we look up in Excel and then we'll go back to our studio and then we do the same thing so the first thing is we look up is a function now so now let us understand the context the context is this is the same data set that we saw in the first video which is a food consumption video it has a sorry food consumption data set it has got four columns a country column food category column consumption and co2 emission how much of a co2 emission that given food category is been doing in this given country so now we have got another sheet another CSV file where we have got continent and country so for a given country they have got a continent so given this now let us try to see if we can create a new column here which should say continent and that column should have continent for it for example in this case let's look for Argentina when we go to this sheet and search for Argentina you can actually see Argentina belongs to South America so now at the end of this process when we do this we look up stuff South America should come here and it should repeat the respective continent should repeat for each row so this is our objective with vlookup so I'm not going to explain you how to do we look up I this this is not a tutorial for you to learn R or Excel this is more like you know so you learn something in one platform and then you try to understand how to do that same thing in another platform this is not like an individual function tutorial our programming tutorial so let's start with so we look up this is the column I want to look up for and where do I want to look up I want to look up here which is column B okay so the problem okay first let's do this thing and then I'll explain you the problem vlookup and column index is minus 1 because you want the one before it and you want exact match okay if you see minus 1 gives you value which is false let's put 0 okay what does it mistake we have done let's put one so you can see Argentina so the point this vlookup only accepts positive index an index is more like an offset from the column that this plane identified which column you want so because we don't have anything in the right side we have to traverse have said which is not possible directly with we look up and you have to do a lot of work around this is what I understood from the internet now what do we look up so to solve that you have to actually take this column and cut the column then go back here and insert the cut cell so now that you have continent it isn't the right side now you can use the same thing and then say - sorry 1 but you have to expand your table array and then you can see to see you got South America so the idea is now you have got South America and then you can just drag it to all the columns no you have got South America so there are some NS missing values that they could not match so we are not going to deal with all those things all this link formatting all those things so we have just tried vlookup to match a given continent with given country and then we try to extract that back to this particular table or sheet that we have got so now let's go ahead and then try to do the same thing in our how are we going to do so to start with you can see that I have successfully read my first ok we are loading our library start putting comments together here so that it is more helpful library tidy was for data manipulation and plot let's call it plot unplowed and then you read the CSV reading input data set and this is again reading input data set the country status at the second sheet so once we read these things let us just try to okay so if you are completely completely new to our so first step is you have to install this package Tydeus using command called install dot packages tidy verse which I'm not going to run it now because I have to then restart my our session so if you're completely new and you just want to you know use this tutorial to replicate it so I really encourage you to do this thing first once you do that thing then you have to just mark the path where you have got your folder the file input file on your machine please remember CSV is like in the default file format because it's platform-independent and then it doesn't have all these heavy weights that Excel does if you want Excel then you have to use a different package but the methodology whatever we are going to do would remain same so the second sheet which is country sheet we have read successfully both so let's look at it so the first one has four columns the second one has two columns that is all fine and once this is done our objective is to add a column to this data frame which is our CEO to data frame and then say that this is a new column is called going to be called continent okay co2 the way we are going to use this we already saw in the previous video but pipe pipe simply to say that it will take the output of the left-hand side and give it to the right-hand side so that's pipe you can do it without by also but a pipe makes it more intuitive that's the point so this concept what we are going to do now is from Tydeus package that idea was our universe so this concept actually comes from sequel as far as I know it's one of the joints that we are going to do to understand what is a join I would just give you a small hint left joint if you understand when diagram a left join is something where you have two tables and you to join it based on aqui and then you want to take instances in tear instances of your left side table and then the matching instances of your right side table so to differentiate how it differs from other join you can see in a joint in a sign is something where you take something that are common in both the tables so left join is you take everything from left and keep the matching ones from the right inner join is take a intersection B which is not only the matching ones so the advantage of doing a left join in most of the cases this first of all it is easy to read the code and second of all your number of rows would remain the same you are not going to reduce the number of rush so will do a left join left join and then where are we joining with we are joining with this table called countries nice and by on which we are trying to join so for third you have to understand what are the column names the ID unique key on which you are going to join in the first table it is going to be called country please note the small case see here okay so first is country and in the second table it's going to be called country which is capital unlike Excel where you have to move these columns in are you don't have to do it at all itself will take it so country so the first table it's small C country and then the second table its capital C country once you exit it yeah it's showing on console so let us view it so you can see that you have successfully added a new column which is continent and then you have got all the continent names available so now you can just write it back to the same co2 to say co2 is equal to CO 2 now your co2 has 5 columns and then you have got the continent name so that is how you do we look up on Excel which is wheel group is one of the most you know frequently used function and how you would do we look up on our using a tidy verse lift joint but maybe you don't want to use Tydeus package you want to you know use something simpler so in that case again r has got up function called merge first let us look at okay one of the good practices if you are coming to our as a new user or Phi any programming language for that matter is always learn to read the documentation so in this case you should put a question mark and then say merge then you'll be taken to the function where merge is there and then in fact I would recommend you first read the first line most today deference by column name or row name and database join operation and then go to the last middle and then try to understand what what example that they have given so that is quite handy for you to do so they are trying to merge two data frames by these two column names good so we can do the same thing without idea so now we can say okay we'll read this you go to again where we don't have continuity you can see that we have only four columns now objective is to add contents he would ok merge of co2 comma countries come up by sea of country sorry is equal to country okay we have successfully seen the first error original fix by must specify a unique column value okay so you don't have to use equal to you have to use comma in the syntax okay let's see what happens when we do this we are still facing an error by my specific uniquely valid column uniquely valid column let me just getting the same error okay so to solve that a key issue we can explicitly mention where we can say by dot X by dou Y so that's what I'm going to do I'm going to say my by dot X is equal to country sorry country and by dot y is equal to country okay successful Iran let's as any to see what to know you have five columns and then you can see that your continent is available in this case so you can either use this join or you can use base or function this is from this package but if you don't want to use that package you can still use it using baser so this is the exact we look up match using our so there is another aspect of vlookup which is partial match which we call as fuzzy match we'll see that in a different video but the objective for this video is for you to understand how again simpler is to do let's say we look up and one of the aspects that we also see in these videos is that why do you have to use are than you know let's say my excel or something like that let us assume that now you have got another column here and let's say this column is going to be called some number and you are going to have one some numbers okay so you're going to have some numbers in here let's repeat it okay so you're going to have numbers like this now if you want to do add another thing then you have to you know again replicate the entire number of clicks or if you want to teach a junior resource or a new data analyst to do tests and it is you have to explain them the same thing if they want to replicate it but the advantage with the iris if you have such a column all you have to do is you have to copy this code again I understand that people would know tell me that they can do excel formula copy paste but to give you this simple understanding where you don't have to select the table array or anything so all you have to do is you have to say okay I want to join country by but what this function will do is what are all columns you have got in the output file so let's let's do that let's if you see okay where is my okay I don't have here but I was just a manipulative column name here so if I say country countries some number is equal to I'll say C of 2 comma 3 4 if I say this okay I have to replace it okay for this matter will just say 6 okay so 6 is a column some number now when you actually do this do this sea water thing from the first where you don't have anything else you have four columns and now you have three columns here when you do this merge you can actually see that that c02 God also there's some number column so you don't have to explicitly do it with the different different columns you just have to do join once and then you would get all the column values so again that is one advantage of fusing a programming language you probably like starting from sequel or Python anything so I hope this video was helpful in understanding how to do we look up an R and if you have any video suggestions please let me know if you know want to know about any excel function please let me know I would try to do the same thing in our thank you very much for watching I got a lot of good feedback for the previous video that I did for this particular series so thanks to everyone who saw the video and then shade it and subscribed thank you so much take care bye bye