CODEWITHSUNDEEP
rlookup
firefly3224
firefly3224

Reputation: 53

Vlookup using R

I've learned about merging in R, but what about if we want to lookup values within the same dataframe?

Below is an example of a supervisor hierarchy. I want to look up who 'Supervisor_1' reports to using the 'Employee' and 'Supervisor_1' columns. The expected output is shown in the 'Supervisor_2' column.

Employee  Supervisor_1  Supervisor_2
   1             2                3
   2             3                4
   3             4                5
   4             5                NA

I've tried looking into this, but have seen a lot of 'merge' recommendations, which I'm not sure applies to my problem.

Thank you!

Upvotes: 0

Views: 65

Answers (2)

r.user.05apr
r.user.05apr

Reputation: 5456

You can read from a vector and avoid merge:

d <- read.table(text = "Employee  Supervisor_1  Supervisor_2
   1             2                3
                2             3                4
                3             4                5
                4             5                NA", header = TRUE, stringsAsFactors = FALSE)

d$Supervisor_2b <- d$Supervisor_1[setNames(d$Supervisor_1, d$Employee)]
d

# Employee Supervisor_1 Supervisor_2 Supervisor_2b
# 1        1            2            3             3
# 2        2            3            4             4
# 3        3            4            5             5
# 4        4            5           NA            NA

Upvotes: 0

Ian Campbell
Ian Campbell

Reputation: 24818

The function merge does apply. Here is a base R approach. 

merge(data,data,by.x="Supervisor_1", by.y="Employee",all.x=TRUE)
  Supervisor_1 Employee Supervisor_1.y
1            2        1              3
2            3        2              4
3            4        3              5
4            5        4             NA

Data

data <- structure(list(Employee = 1:4, Supervisor_1 = 2:5), class = "data.frame", row.names = c(NA, 
-4L))

Upvotes: 2

Related Questions