6

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.

JohnCoene
  • 2,107
  • 1
  • 14
  • 31
  • 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

2 Answers2

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)
Hugh
  • 15,521
  • 12
  • 57
  • 100
  • @ Hugh I constructed df with countries <- c(NA, NA, NA) cities <- c("LDN", "NY", "MOSC") df <- cbind(countries, cities). Although when I do merge (db,df) I get a dataframe with 0 rows. If I do inner_join I receive this error: "joining factors with different levels, coercing to character vector " and also a dataframe with 0 rows. Do you know what can be the cause? Thanks! – user2165379 Jan 24 '20 at 20:10
2

Here's another approach:

library(qdapTools)
df[, 1] <- df[, 2] %l% db[, 2:1]
Tyler Rinker
  • 108,132
  • 65
  • 322
  • 519