As a data scientist doing a vlookup in Microsoft excel is a handy way of processing data. Now it is possible to do a vlookup in r with two simple functions that form a powerful search tool for processing the contents of a data frame. It is an r tool that you will find invaluable once you learn how to use it.
Introduction to the expess package (for vlookup in R)
The expss package provides two vlookup functions to r programming. The first is the vlookup function which has the form of vlookup(lookup value, lookup table, results column, lookup column) and the vlookup_df function which has the form of vlookup_df(lookup value, lookup table, results column, lookup column).
- lookup value = The value you are looking up.
- lookup table = The data frame or matrix you are looking at.
- results column = The column holding the results you are looking for.
- lookup column = The column holding the value you are looking up.
The vlookup_df function produces a data frame while the vlookup function only produces a single value.
Explanation – Vlookup in R
Even though the vlookup and vlookup_df functions have a superficial resemblance to the insert function, they are completely different. Vlookup searches the lookup column for an exact match to the search value and it returns the first result from the results column in the case of multiple matches. The function goes down the list until it finds a match, and then finds the proper matching value.
Examples of vlookup in R
Here are three sets of example code, which show these two functions in action.
> library(expss)
> head(cars)
speed dist
1 4 2
2 4 10
3 7 4
4 7 22
5 8 16
6 9 10
> vlookup(9, cars, 2, 1)
[1] 10
This is a simple example of the vlookup function using a basic data set that comes with r studio.
> library(expss)
> df = data.frame(Typ = c(“A”, “B”, “C”, “D”, “E”),
+ Num = c(15, 25, 30, 57, 46),
+ Pet = c(“cat”, “dog”, “fish”, “gerbal”, “hampster”))
> vlookup(“A”, df, 2, 1)
[1] 15
> vlookup(“A”, df, 3, 1)
[1] cat
Here is an example using the vlookup function on a data frame with multiple columns.
> library(expss)
> df = data.frame(Typ = c(“A”, “B”, “B”, “D”, “E”),
+ Num = c(15, 25, 30, 57, 46),
+ Pet = c(“cat”, “dog”, “fish”, “gerbal”, “hampster”))
> lu = “B”
> vlookup_df(lu, df, NULL, 1)
Typ Num Pet
1 B 25 dog
> vlookup_df(lu, df, 2, 1)
Num
1 25
Here is an example using the vlookup_df function on the same data frame with multiple columns. Note that in the first instance, with the return being null, we get the entire row along with the column names. Furthermore, we illustrate setting the selection criteria using a variable.
Applications of Vlookup in R
These functions have many applications, they are a handy way of pulling out specific related data from a data frame. If you need a value from a data frame based on a specific set of criteria, these functions can get it for you as long as that criterion is part of the data frame.
The vlookup and vlookup_df functions are handy tools for extracting information from data frames and matrixes. They provide r programming with a helpful tool from Microsoft excel without having to leave r studio. Once you understand these powerful tools you will find them abundantly useful in ways, that are not obvious at first.