How to delete duplicacy on certain conditions

Question

This is a example of what i am trying to do on a different dataset but this still not working

PORT    STATUS   VESSEL         DWT      IMP/EXP    QTY (Mts)

1 KANDLA    SAILED  CAPTAIN HAMADA  7938 EXP   4500
2 KAKINADA  EXPECTED CELON BREEZE       IMP      30000
3  KAKINADA BERTH    CELON BREEZE       IMP     3000
4 KAKINADA  SAILED   CELON BREEZE       IMP     30000
5 KANDLA    ANCHORAGE CAPTAIN HAMADA    EXP  4500
6 KAKINADA  BERTH    CELON BREEZE       IMP     30000

And i want to compare a row's (PORT,VESSEL,IMP/EXP) with another rows ,if it matches then delete like if IMP/EXP in the row is "IMP" then delete the row in priority order of STATUS: sailed> berth> anchorage > expected It will give highest priority to sailed =status and other have anchorage and delete 2nd row since it matches the qty,port,vessel with the 4th row. and so on if condition matches then see

  1 ) status=sailed and other have berth ,it will delete berth row
  2) sailed and other have expected,it will delete expected row
   3)if some row have berth and other have anchorage will delete anchorage
  4)if some has expected=STATUS & other row have sailed=STATUS it will delete              

    "expected"=STATUS   row        

so on Row should match the condition i.e qty,port,vessel to delete the row according to the conditions

for EXP in IMP/EXP it should matches condition i.e,qty,port,vessel
condition of priority in STATUS:

     priority- sailed>anchorage>expected>  berth

The OUTPUT should be

PORT    STATUS   VESSEL              DWT    IMP/EXP QTY (Mts)

1 KANDLA    SAILED  CAPTAIN HAMADA  7938         EXP    4500
3  KAKINADA BERTH    CELON BREEZE             IMP      3000
4 KAKINADA  SAILED   CELON BREEZE             IMP      30000

2nd,5TH,6th row is deleted is the desired output

Answer 1

First of all you need to read the data into R in a data.frame. The data.frame test should look like this:

>test

#      PORT    STATUS         VESSEL  DWT IMPEXP   QTY
#1   KANDLA    SAILED CAPTAIN HAMADA 7938    EXP  4500
#2 KAKINADA  EXPECTED   CELON BREEZE   NA    IMP 30000
#3 KAKINADA     BERTH   CELON BREEZE   NA    IMP  3000
#4 KAKINADA    SAILED   CELON BREEZE   NA    IMP 30000
#5   KANDLA ANCHORAGE CAPTAIN HAMADA   NA    EXP  4500
#6 KAKINADA     BERTH   CELON BREEZE   NA    IMP 30000

Using the plyr package's ddply function you should be able to get the desired output with the help of tfollowing function.

ddply(test,.variables = c("PORT","VESSEL","IMPEXP","QTY"),
  function(t){if(t$IMPEXP[1]=="IMP"){
    t$STATUS<-factor(x = t$STATUS,levels =c("EXPECTED","ANCHORAGE","BERTH","SAILED"),ordered = T)
    return(t[which.max(as.integer(t$STATUS)),])
  }else{
    t$STATUS<-factor(x = t$STATUS,levels =c("BERTH","EXPECTED","ANCHORAGE","SAILED"),ordered = T)
    return(t[which.max(as.integer(t$STATUS)),])}
  }
)

#PORT STATUS         VESSEL  DWT IMPEXP   QTY
#1 KAKINADA  BERTH   CELON BREEZE   NA    IMP  3000
#2 KAKINADA SAILED   CELON BREEZE   NA    IMP 30000
#3   KANDLA SAILED CAPTAIN HAMADA 7938    EXP  4500