Description Usage Arguments Value Examples
vlookup
/vlookup_df
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
. add_columns
inspired by MATCH FILES (Add
variables...) from SPSS Statistics. It works similar to SQL left join but
number of cases in the left part always remain the same. If there are
duplicated keys in the dict
then error will be raised by default.
.add_columns
is the same function for default dataset.
1 2 3 4 5 6 7  vlookup(lookup_value, dict, result_column = 2, lookup_column = 1)
vlookup_df(lookup_value, dict, result_column = NULL, lookup_column = 1)
add_columns(data, dict, by = NULL, ignore_duplicates = FALSE)
.add_columns(dict, by = NULL, ignore_duplicates = FALSE)

lookup_value 
Vector of looked up values 
dict 
data.frame/matrix. Dictionary. Can be vector for

result_column 
numeric or character. Resulting columns of 
lookup_column 
Column of 
data 
data.frame to be joined with 
by 
character vector or NULL(default) or 1. Names of common variables
in the 
ignore_duplicates 
logical Should we ignore duplicates in the 
vlookup
always return vector, vlookup_df
always returns
data.frame. row.names
in result of vlookup_df
are not
preserved.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100  # with data.frame
dict = data.frame(num=1:26, small=letters, cap=LETTERS, stringsAsFactors = FALSE)
rownames(dict) = paste0('rows', 1:26)
identical(vlookup_df(1:3, dict), dict[1:3,]) # should be TRUE
vlookup(c(45,1:3,58), dict, result_column='cap')
vlookup_df(c('z','d','f'), dict, lookup_column = 'small')
vlookup_df(c('rows7', 'rows2', 'rows5'), dict, lookup_column = 'row.names')
# with vector
dict=1:26
names(dict) = letters
vlookup(c(2,4,6), dict, result_column='row.names')
# The same results
vlookup(c(2,4,6), dict, result_column='rownames')
vlookup(c(2,4,6), dict, result_column='names')
# example for 'add_columns' from base 'merge'
authors = sheet(
surname = c("Tukey", "Venables", "Tierney", "Ripley", "McNeil"),
nationality = c("US", "Australia", "US", "UK", "Australia"),
deceased = c("yes", rep("no", 4))
)
books = sheet(
surname = c("Tukey", "Venables", "Tierney",
"Ripley", "Ripley", "McNeil", "R Core"),
title = c("Exploratory Data Analysis",
"Modern Applied Statistics ...",
"LISPSTAT",
"Spatial Statistics", "Stochastic Simulation",
"Interactive Data Analysis",
"An Introduction to R")
)
add_columns(books, authors)
# Just for fun. Examples borrowed from Microsoft Excel.
# It is not the R way of doing things.
# Example 2
ex2 = utils::read.table(header = TRUE, text = "
Item_ID Item Cost Markup
ST340 Stroller 145.67 0.30
BI567 Bib 3.56 0.40
DI328 Diapers 21.45 0.35
WI989 Wipes 5.12 0.40
AS469 Aspirator 2.56 0.45
", stringsAsFactors = FALSE)
# Calculates the retail price of diapers by adding the markup percentage to the cost.
vlookup("DI328", ex2, 3) * (1 + vlookup("DI328", ex2, 4)) # 28.9575
# Calculates the sale price of wipes by subtracting a specified discount from
# the retail price.
(vlookup("WI989", ex2, "Cost") * (1 + vlookup("WI989", ex2, "Markup"))) * (1  0.2) # 5.7344
A2 = ex2[1, "Item_ID"]
A3 = ex2[2, "Item_ID"]
# 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 = utils::read.table(header = TRUE, text = "
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
", stringsAsFactors = FALSE)
# If there is an employee with an ID of 5, displays the employee's last name;
# otherwise, displays the message "Employee not found".
if_na(vlookup(5, ex3, "Last_name"), "Employee not found") # Burke
# Many employees
if_na(vlookup(1:10, ex3, "Last_name"), "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.