Using %in% to identify if an element is in a df

Here is my sample code:

DF$Owner <- ifelse (ExcelData$Brand %in% Reference$Brand, Reference$Owner, "NULL")

Basically what I'm trying to do is to identify whether there is a brand from ExcelData to Reference. If there is then I'll get its owner

Example: In the ExcelData there is a brand 'Baja', there is also a brand 'Baja' in the reference file. Now with the logic of %in% I should get the owner of brand 'Baja' which in this case is 'AMBC01' but the owner that I get when running the code is different.

Now I'm thinking that maybe I need to track the row or anything in order to get the right owner for each identified brand.

I hope that you can understand the explanation of my problem, if there are questions please comment.

Thanks

set.seed(42) # for reproduciblity of the random sample
(somecars <- c("missme",sample(rownames(mtcars),size = 5)))

#poor results
ifelse(somecars%in%rownames(mtcars),mtcars$mpg,NA)

#correct results 

library(dplyr)
(result_df <- left_join(tibble(name=somecars),as_tibble(mtcars,rownames="name")) %>% select(name,mpg))
result_df$mpg
1 Like

This sounds like something you could do with left_join (tidyverse) or merge(base)
As a gross simplification this will do a VLOOKUP (In Excel terms) on your key field (Brand) and return NA if no match.
a key difference from VLOOKUP is that if it matches more than 1 row in the lookup then you get multiple rows returned.

1 Like

is there a reason why using %in% has poor results? Just curious, many thanks

When you use ifelse, it checks conditions using first vector. After all the checks are complete, it notes indices of TRUE from that, and picks those values from second vector. This picking up is done by position. Same happens for FALSE.

So in this example, if the 2nd element (which is Chrysler Imperial here) is present in mtcars, it'll return the 2nd element (in this case 14.7) of your 2nd vector (which is mtcars$mpg here). It doesn't bother itself whether that element corresponds to the element you checked for or not.

On the other hand, while you join the tables, the correspondence is being taken care of, and that's what you want.

Hope this helps.

2 Likes

I somewhat get it know, many thanks!