Excel vlookup in R

Posted on: November 25, 2017 Posted by: Guy Manova Comments: 0

Excel vlookup in R

Excel vlookup in R

In this tutorial, we’ll see how to use table merge to mimic Excel vlookup in R. since Excel has an excellent table merge of it’s own in power query (or in the excel 2016 and beyond “get and transform” tab in the data ribbon) – i’ll use that to illustrate and explain so it’s very clear what R is doing, which is exactly the same as that (which is also exactly the same as a table join in sql)

in Excel

regular vlookup:

excel vlookup in R

Table merge via the data ribbon or power query add on (on earlier than 2016 versions,  can be installed from MS website)

excel vlookup in R

excel vlookup in R

excel vlookup in R

excel vlookup in R

excel vlookup in R

excel vlookup in R

excel vlookup in R

excel vlookup in R

excel vlookup in R

In R:

# pre-session options

 

rm(list = ls())
# getwd()
# setwd(“C:\\Users\\User\\Dropbox (Personal)\\Personal\\Courses\\Excel2R\\Dataframes”)

 

# create a dataframe of mtcars:

mtcars <- data.frame(mtcars)

excel vlookup in R

excel vlookup in R

# since the car names are rownames, they are not “available” as a column – fixing that:
# adding the car names as a column, and deleting the row names:

mtcars$carName <- rownames(mtcars)
rownames(mtcars) <- NULL

excel vlookup in R

excel vlookup in R

# now let’s import the lookup table:

countries <- read.csv(“https://www.dropbox.com/s/m6tkyuolbckkyb4/mtcars_countries.csv?dl=1”)

write.csv(mtcars, “mtcars.csv”)
write.csv(countries, “countries.csv”)

# merge the lookup table to the main mtcars table based on the “model” column
# NOTE: if you don’t explicitely tell which columns to join, it’ll join all:

mtcars_merged <- merge(mtcars, countries, by.x = “carName”, by.y = “model”)

excel vlookup in R

excel vlookup in R

# now with explicit column selection:

mtcars_merged <- merge(mtcars, countries[,c(“model”,”manf_Country”)],
by.x = “carName”, by.y = “model”)

# or just use column numbers:

mtcars_merged <- merge(mtcars, countries[,1:2],
by.x = “carName”, by.y = “model”)

excel vlookup in R

excel vlookup in R

# with dplyr:

library(dplyr)

# without excluding columns

mtcars_merged_DL1 <- left_join(mtcars, countries, by = c(“carName” = “model”))

# excluding the useless columns

mtcars_merged_DL2 <- left_join(mtcars, select(countries, c(1:2)) , by = c(“carName” = “model”))

 

Leave a Reply:

Your email address will not be published. Required fields are marked *