Excel to R #3 - APPROXIMATE VLOOKUP in Excel to FUZZY LEFT_JOIN in R

hey guys welcome to one little coder this is our excel to our series in which we try to do a task excel task and are for us to understand how easy it is and how beneficial it is to do something on our so in that series in the last video we had seen we look up but when I was going through vlookup I had also mentioned that we are going to look at only the exact match not the approximate match to give you a sense of it if you would want to do we look up let's say in this case I've got two sheets one is called sheet the other one is called main and I'm I want to now copy this values from this sheet to my sheet okay so let's call it stock this column and I'm going to do a vlookup when I do a vlookup we look up the first value and the table which is this 1 comma 1 the second value and I want exact match let's start with exact match when you do this thing and when you drag it you would find out that you have sorry I have not logged the column okay when you do this thing and when you drag it you would see that every value has been matched except Apple Inc up because in the main sheet we have only Apple Inc not Apple in cob so this is where your approximate match or also known as fuzzy match comes into play where Excel internally tries to find out which is the closest word for this given word that you are trying to match and then it will do the match so only for this value let us say instead of false which is for exact match let's say we want approximate match when you do that and press Enter you actually get the value 300 which is placed against Apple Inc so Excel Microsoft Excel has internally figured out that ok Apple Inc is the closest one to Apple in cop from all these options like when you compare it with Microsoft Google or Facebook and that is how it has matched this thing and this is called as a proximate match of vlookup and it is quite handy and you handle a lot of not clean dataset let's say like survey data set or census data set in those cases and now we are going to look at how to do the same task on Microsoft sorry our studio are okay so the way we are going to do that is using a package called fuzzy join in case if you do not have that package let's let me first show you what will happen when you don't have the package and you don't have the package when you try to load the package you would get an error like this package not a value which means this package is not installed so let's first install packages for Z join while it is getting installed let's go to the packages github repo so let's see who's the developer developer is david robinson quite a popular figure in our community so he has a nice channel on youtube which you can follow and so david robertson you show totem thank you for this package this package helps you do fuzzy join which means it helps you join two different tables data frames on approximate or enact exact matching in his own words which means you don't have to have exact matching you can do it on in exact matching and it has a lot of functions so you can do with respect to string like the example just we just now saw you can do it with numeric values also you can do it with regular expressions and you have a lot of other options like geographic distance Euclidean distance all those things so we are going to look at only the case that we just looked at which is in this case it is a string match so that's the only thing that we are going to look at but you are free to explore other options on you if you have question you can let me know so now we can see that this package has been successfully installed and let us load tidy voice also where we will first initially do the actual lookup that we did in the previous class sorry previous video and then we are going to do the fuzzy join okay so let's load both the packages two packages have been successfully loaded so now we are going to recreate this this one this one under the main one so you can see that in the first one you have only one column which is to say that the second column we have to create and then the second main data frame you have two columns one is the company name and the other one is the value saw also you can see that I've got one Apple Inc and then the other one is Apple in cop and you have Microsoft Google all these other companies so now first let us try the first left join so the way we do left join us in this case is so you have the lift you can do it in multiple ways you can do it like this also left join and say what is your left your left is sheet your right is main and on what you want to join it you want to join it on the basis of company because the column name is company company and it is company it could be ID or anything in your case in our example it is company so let's run it and you can see that it has successfully taken value and only for Apple in cop it is any for everything else it is it has matched the value and it is primarily because the same reason we know because left joint works based on exact matching it was looking for exact matching but what we actually want this approximate or fuzzy matching so let's let's do approximate or fuzzy matching the way we can do it is within fuzzy join there is a function called string distance left join so which means this left join works based on string distance so string distance is to find out how close to string values are characters or words okay so now what we are going to do is we are going to lose sheet main sorry main and oops main and by is equal to company but we are going to add one more detail to say that what is the distance max distance what is the maximum distance show you the documentation first string just left join and you look at it you can actually see the arguments that you can pass maximum distance method what kind of distance method that you want to follow which is out of scope of this video so what we are going to focus particularly in this in this particular example is the maximum distance it is to say that number of distance number of character you want to look and you want to do this matching okay so max distance is very important and if you can see from our example we are matching until this point and we are not matching with for the extra three characters so approximately we can give probably like max max distance is equal to five and let's try to turn it so yeah so you have got company because we have got to the same name it has taken company from both the sides so we can we can ignore it if you want but yeah so the main thing is you have got the company name now you have got 300 here also and it also shows in which value of Y you have matched so you can see that in cop has matched with Apple Inc and you have got the same value so this is how we can successfully replicate the approximate match feature of vlookup that we usually do on itself which is quite handy if you predominantly handle unclean or messy data and the same thing you can do it with R and to again say this function is not only for this thing so you can also do approximate match on numeric values based on regular expression you can do it based on distance matrix and you can also do it for geographical like if let's say you're doing some kind of clustering zip code clustering or longitude latitude clustering you can still match the closest to zone you you have plenty of option I would recommend you to look at this packages github repo fuzzy joined by David Robinson and also you can now guess that how easy it is because this Excel Excel vlookup approximate vlookup doesn't give you option to fix the number of characters but because you are using a programming language like R in this case it gives you the flexibility to to add whatever you know on the number of characters you want to give so let's let's just do one more small check for us to see how whether it works or not so instead of Facebook I'm going to call it Facebook Inc and when I do main you can see that I've got two ns and because I have got Distin five it should ideally work and you can see that no Facebook Inc also has got 50 if I do just normal left joint is going to give me an a but if I do string string distance left joint it is giving me the actual value so that's the power of programming language for you to iterate quite fast so I hope this video was helpful for you and if you have any questions or if you want to see any of the excel feature please let me know in the comment section see you in the next video