vlookup | R Documentation |
vlookup
function is inspired by VLOOKUP spreadsheet
function. It looks for a lookup_value
in the lookup_column
of
the dict
, and then returns values in the same rows from
result_column
.
xlookup
is simplified version of vlookup
. It searches for a
lookup_value
in the lookup_vector
and return values in the same
position from the result_vector
.
vlookup( lookup_value, dict, result_column = 2, lookup_column = 1, no_match = NA ) xlookup(lookup_value, lookup_vector, result_vector, no_match = NA)
lookup_value |
Vector of looked up values |
dict |
data.frame. Dictionary. |
result_column |
numeric or character. Resulting columns in the
|
lookup_column |
Column of |
no_match |
vector of length one. NA by default. Where a valid match is not found, return the 'no_match' value you supply. |
lookup_vector |
vector in which 'lookup_value' will be searched during 'xlookup'. |
result_vector |
vector with resulting values for 'xlookup'. |
xlookup
always return vector, vlookup
returns vector if
the result_column
is single value. In the opposite case data.frame will
be returned.
# with data.frame dict = data.frame(num=1:26, small=letters, cap=LETTERS) vlookup(1:3, dict) vlookup(c(45,1:3,58), dict, result_column='cap') vlookup(c(45,1:3,58), dict, result_column='cap', no_match = "Not found") # the same with xlookup xlookup(1:3, dict$num, dict$small) xlookup(c(45,1:3,58), dict$num, dict$cap) xlookup(c(45,1:3,58), dict$num, dict$cap, no_match = "Not found") # example from base 'merge' authors = data.table( surname = c("Tukey", "Venables", "Tierney", "Ripley", "McNeil"), nationality = c("US", "Australia", "US", "UK", "Australia"), deceased = c("yes", rep("no", 4)) ) books = data.table( surname = c("Tukey", "Venables", "Tierney", "Ripley", "Ripley", "McNeil", "R Core"), title = c("Exploratory Data Analysis", "Modern Applied Statistics ...", "LISP-STAT", "Spatial Statistics", "Stochastic Simulation", "Interactive Data Analysis", "An Introduction to R") ) let(books, c("author_nationality", "author_deceased") := vlookup(surname, dict = authors, result_column = 2:3 ) )[] # Just for fun. Examples borrowed from Microsoft Excel. # It is not the R way of doing things. # Example 2 ex2 = fread(" Item_ID Item Cost Markup ST-340 Stroller 145.67 0.30 BI-567 Bib 3.56 0.40 DI-328 Diapers 21.45 0.35 WI-989 Wipes 5.12 0.40 AS-469 Aspirator 2.56 0.45 ") # Calculates the retail price of diapers by adding the markup percentage to the cost. vlookup("DI-328", ex2, 3) * (1 + vlookup("DI-328", ex2, 4)) # 28.9575 # Calculates the sale price of wipes by subtracting a specified discount from # the retail price. (vlookup("WI-989", ex2, "Cost") * (1 + vlookup("WI-989", ex2, "Markup"))) * (1 - 0.2) # 5.7344 A2 = ex2[["Item_ID"]][1] A3 = ex2[["Item_ID"]][2] # If the cost of an item is greater than or equal to $20.00, displays the string # "Markup is nn%"; otherwise, displays the string "Cost is under $20.00". ifelse(vlookup(A2, ex2, "Cost") >= 20, paste0("Markup is " , 100 * vlookup(A2, ex2, "Markup"),"%"), "Cost is under $20.00") # Markup is 30% # If the cost of an item is greater than or equal to $20.00, displays the string # Markup is nn%"; otherwise, displays the string "Cost is $n.nn". ifelse(vlookup(A3, ex2, "Cost") >= 20, paste0("Markup is: " , 100 * vlookup(A3, ex2, "Markup") , "%"), paste0("Cost is $", vlookup(A3, ex2, "Cost"))) #Cost is $3.56 # Example 3 ex3 = fread(' ID Last_name First_name Title Birth_date 1 Davis Sara "Sales Rep." 12/8/1968 2 Fontana Olivier "V.P. of Sales" 2/19/1952 3 Leal Karina "Sales Rep." 8/30/1963 4 Patten Michael "Sales Rep." 9/19/1958 5 Burke Brian "Sales Mgr." 3/4/1955 6 Sousa Luis "Sales Rep." 7/2/1963 ') # If there is an employee with an ID of 5, displays the employee's last name; # otherwise, displays the message "Employee not found". vlookup(5, ex3, "Last_name", no_match = "Employee not found") # Burke # Many employees vlookup(1:10, ex3, "Last_name", no_match = "Employee not found") # For the employee with an ID of 4, concatenates the values of three cells into # a complete sentence. paste0(vlookup(4, ex3, "First_name"), " ", vlookup(4, ex3, "Last_name"), " is a ", vlookup(4, ex3, "Title")) # Michael Patten is a Sales Rep.
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.