mardi 18 juillet 2017

r match data in two data frames then check text in another column for matched row

I have two data frames stu1 and stu2. Both have matching ID columns but different variables in other columns.

For example, stu1:

ID, Grade, Group, Age

ad1, A, Green, 14

bc1, B, Green, 13

cd1, B, Blue, 14

fs3, C, Red, 13

stu2:

ID, Prog, Loc, Year

bc1, LSC1, Ext, 2013

cd1, LSC1, Ext, 2013

cd1, BSC1, Int, 2013

ad1, BSC2, Int, 2012

rs2, KHL4, Ext, 2014

What I'm trying to do is check whether the student ID in stu1 exists in stu2 then check whether the text in another column for the corresponding row matches my string, e.g. Prog =='BSC*' then create a new column in stu1 which states "Yes" or "No".

So, the result for stu1 should be:

ID, Grade, Group, Age, BSCProg

ad1, A, Green, 14, Yes

bc1, B, Green, 13, No

cd1, B, Blue, 14, Yes

fs3, C, Red, 13, No

I've tried a number of different ways unsuccessfully, e.g:

stu1$BSCProg <- ifelse(stu2[grepl("BSC", stu2$Prog) & match(paste0(stu1$ID), paste0(stu1$ID)),], "Yes", "No")

stu1$BSCProg <- ifelse(is.na(match(paste0(stu1$ID),paste0(stu2$ID) & stu2[grepl("BSC", stu2$Prog),])),"No","Yes")

stu1$BSCProg <- ifelse(stu1$ID %in% stu2$ID & grepl('BSC', stu2$Prog), "Yes", "No")

Thanks!

Aucun commentaire:

Enregistrer un commentaire