CODEWITHSUNDEEP
rdataframelookup
santoku
santoku

Reputation: 3427

R lookup between 2 data frames

suppose i have two data frames 

df1=data.frame(item=c(rep("a",2),rep("b",3),"c","NA",rep("d",4)),
product=paste0("prd",seq(1:11)))
df2=data.frame(item=c("b","d"), price=c(10,20))

for df1, i need to add a col to indicate if it's in df2 item col, as well as for each row, indicate how many products are there, unless it's na,like this

item product#
a    2
a    2
b    3
b    3
b    3

how should i get the product count repeat for each row?

for lookup i'm using

df1$hasDF2=ifelse(is.na(match(df1$item,df2$item)),"N","Y")

is there a more efficient alternative?

thanks!

Upvotes: 0

Views: 193

Answers (1)

akrun
akrun

Reputation: 886928

Try:

 df1$productNo<- with(df1, ave(seq_along(product), item, FUN=length))
  df1$productNo
  #[1] 2 2 3 3 3 1 1 4 4 4 4

 df1$hasDF2 <- c("N", "Y")[(!is.na(match(df1$item, df2$item))) +1]
 df1$hasDF2
 #[1] "N" "N" "Y" "Y" "Y" "N" "N" "Y" "Y" "Y" "Y"

Or using data.table

 library(data.table)
 setDT(df1)[,c("produtNo", "hasDF2") := list(.N, "N"), 
              by=item][item %in% df2$item, hasDF2:= "Y"]

Update

For the unique count, you could do:

 #creating a dataset with duplicate products
 df1 <- data.frame(item=c(rep("a",2),rep("b",3),"c","NA",rep("d",5)),
 product=paste0("prd",c(1:11,11)))

 setDT(df1)[,c("productNo", "hasDF2") := list(length(unique(product)), "N"), 
          by=item][item %in% df2$item, hasDF2:= "Y"]

Upvotes: 0

Related Questions