R - Excel VLOOKUP equivalent - Lookup, replace

Question

I have looked pretty much everywhere and cannot find the answer to this; R equivalent of VLOOKUP on Excel. VLOOKUP allows me to look up for a specific value throughout a column and apply it to each row of my data frame.

In this case I want to find the country a particular city is in (from a database) and return the name of the country in a new column.

So I have this database:

countries <- c("UK", "US", "RUS")
cities <- c("LDN", "NY", "MOSC")
db <- cbind(countries, cities)
db
      countries cities
[1,] "UK"      "LDN" 
[2,] "US"      "NY"  
[3,] "RUS"     "MOSC"

And want to find the country those cities are in (replace NA) based on the db above:

df
     countries cities
[1,]   NA      "LDN" 
[2,]   NA      "NY"  
[3,]   NA     "MOSC"

I have absolutely no idea how to go about this on R.

There are some solutions at http://stackoverflow.com/questions/15303283/how-to-do-vlookup-and-fill-down-like-in-excel-in-r — James Trimble, Jun 03 '14 at 10:22

score 9 · Accepted Answer · answered Jun 03 '14 at 10:23

9

You are performing a join which in R is performed using the function merge

merge(db, df)

Using the dplyr package allows more natural verbs:

library(dplyr)
inner_join(db, df)

or perhaps (if you want non-matches to be shown; see ?left_join for further information):

left_join(db, df)

answered Jun 03 '14 at 10:23

Hugh

14,547
9
54
94

@ Hugh I constructed df with countries – user2165379 Jan 24 '20 at 20:10

score 2 · Answer 2 · answered Jun 03 '14 at 12:32

2

Here's another approach:

library(qdapTools)
df[, 1] <- df[, 2] %l% db[, 2:1]

answered Jun 03 '14 at 12:32

Tyler Rinker

103,777
62
306
498

R - Excel VLOOKUP equivalent - Lookup, replace

2 Answers2

Linked