CODEWITHSUNDEEP
r
melisa
melisa

Reputation: 103

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

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

Upvotes: 1

Views: 77

Answers (3)

IceCreamToucan
IceCreamToucan

Reputation: 28685

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")

Upvotes: 1

Mohamed Desouky
Mohamed Desouky

Reputation: 4425

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))

Upvotes: 0

Dubukay
Dubukay

Reputation: 2071

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

Upvotes: 0

Related Questions