Compare two columns, find the common data, and get the value from the first column in R

user_19559385 0
Viewed 44

I am new in R and I am trying to solve something.

I have three columns Employee, Location and Manager. I need to find if the manager for each employee is local or not, meaning that they have the same location (based on the employee column, as every manager is also an employee). For example, Manager 24 checks on locations A and B, but is a local manager only for employee 12, but not for employee 54. I want to add a new column named localManager and return "yes" or "no".

I hope that is clear. Thank you for the help!

Employee Location Manager
12 A 24
54 B 24
24 A 52
30 C 63
r
3 Answers

Base R

transform(dat,  localManager = ifelse(Location[match(Manager, Employee)] == Location, 
                                      'yes', 'no'))
#>   Employee Location Manager localManager
#> 1       12        A      24          yes
#> 2       54        B      24           no
#> 3       24        A      52         <NA>
#> 4       30        C      63         <NA>

data.table package

library(data.table)
setDT(dat)

dat[dat, on = .(Manager = Employee), 
    localManager := fifelse(Location == i.Location, 'yes', 'no')]

dat
#>    Employee Location Manager localManager
#>       <int>   <char>   <int>       <char>
#> 1:       12        A      24          yes
#> 2:       54        B      24           no
#> 3:       24        A      52         <NA>
#> 4:       30        C      63         <NA>

Created on 2022-07-15 by the reprex package (v2.0.1)

Data used:

structure(list(Employee = c(12L, 54L, 24L, 30L), Location = c("A", 
"B", "A", "C"), Manager = c(24L, 24L, 52L, 63L)), row.names = c(NA, 
-4L), class = "data.frame")
user_67627550

The easiest way I can think to solve this is by creating a second table of the manager locations alone, then joining that table to the original one so we have a new column for "manager's location" as well as "employee location". Then creating your new boolean column is as simple as checking if manager location is equal to employee location:

dat <- dplyr::tribble(
  ~employee, ~location, ~manager,
  12, "A", 24,
  54, "B", 24,
  24, "A", 52,
  30, "C", 63
)

# Create a new table of managers only
mgr_dat <- dat[dat$employee%in%dat$manager,c("employee", "location")]
names(mgr_dat) <- c("manager", "mgr_loc")
# Join the two tables (all.x makes this a left join instead of inner)
# NA values in mgr_loc mean that the manager wasn't found
merged_dat <- merge(dat, mgr_dat, all.x = TRUE)
# Create the boolean column
merged_dat$localManager <- merged_dat$location==merged_dat$mgr_loc

Output:

  manager employee location mgr_loc localManager
1      24       12        A       A         TRUE
2      24       54        B       A        FALSE
3      52       24        A    <NA>           NA
4      63       30        C    <NA>           NA
user_78354350

Try this in your data where all Manager codes are listed in Employee column

localManager <- c()
for(i in 1:nrow(df)){
    s <- df$Location[which(df$Manager[i] == df$Employee)] == df$Location[i]
    if(length(s) == 0 ) localManager[i] <- "not Employee"
    else if(s) localManager[i] <- "yes"
    else localManager[i] <- "no"
}

df$localManager <- localManager
  • output
  Employee Location Manager localManager
1       12        A      24          yes
2       54        B      24           no
3       24        A      52 not Employee
4       30        C      63 not Employee
  • data
df <- structure(list(Employee = c(12L, 54L, 24L, 30L), Location = c("A", 
"B", "A", "C"), Manager = c(24L, 24L, 52L, 63L)), class = "data.frame", row.names = c(NA, 
-4L))
user_191983370
Related