-1

I have two data frames. i want to write a while loop to check for match values and Replace. Eg. if item = item2 and Rep !=Rep2 then create a new column in df2 with Rep. else if item=item2 and Rep = Rep2 then check if Rep2 has another replacement in df1 by comparing Rep2 with all items in df1.

 df1
Item  Rep
1. A  F
2. B  G
3. C  H
4. D    
5. H  I 
6. F   
7. E  Y

df2
item2  Rep2
1. c  H
2. A  F
3. E  Y
4. X  Y
5. B  W

My code that finds the common values and replaces it this. i want to put this code in a while loop to check if every match Rep2 has a new replacement in df2. is there an alternative approach to do this 

library(sqldf) 
wdf1<- sqldf("select 
      df2.*,
      case
      when Item = item2 and Rep = Rep2 then 'Match' # i want a loop to go back and check if Rep2 has another replacement in df1
      when Item = item2 and Rep != rep2 then Rep 
      else 'Item no found'
      end Rep3
      from df2 left join df1 on Item = item2")

This is how the result should look like

  item1 Rep2  Rep3
1. c     H     I
2. A     F     F
3. E     Y     Y
4. X     Y     not found
5. B     W     G

if Rep3 (acting as a new item2 in df2) matches an item in df1 but there's no Rep then Rep3 is the latest replacement.

BenT
  • 37
  • 5
  • 1
    It would be nice to see some effort made by you. Give us some code. – Elis Byberi Nov 02 '17 at 21:30
  • Put that code in question. – Elis Byberi Nov 03 '17 at 13:09
  • I tried this code but it give me the following error. Ops.factor(tdf1$item[i], tdf2$item2[i]) : level sets of factors are different – BenT Nov 03 '17 at 13:28
  • Im relatively new to R and so any advice to point me in the right direction will be really helpful. I also created a new column Rep3 in dt2. i put the code in for loop because i get an error about returning only one single output – BenT Nov 03 '17 at 13:42

1 Answers1

0

for (i in nrow(tdf2)){

  while (tdf1$item[i] == tdf2$item2[i] && tdf1$Rep[i] !='') {
    wdf1[i]<- sqldf("update tdf2 
                 set Rep3 = 
                 case 
                 when Item = item2 and Rep = Rep2 then Rep
                 when Item = item2 and Rep = '' then 'Empty Rep'
                 when Item = item2 and Rep != '' then Rep
                 else 'Not found'
                 end Rep3
                 Where tdf2 left join tdf1 on Item = item2")
  }
}
BenT
  • 37
  • 5
  • Note that update has no return value. To return something you need to use select: `sqldf(c("update ...", "select * from main.tdf2"))` – G. Grothendieck Nov 03 '17 at 13:45