mardi 8 août 2017

if condition is true find max in 3 consecutive rows and report it in r

Reproducible example:

Label<-c(0,0,1,1,1,2,2,3,3,3,4,5,5,5,6,6)
Value<-c(NA,NA,1,2,3,1,2,3,2,1,NC,1,3,2,1,NA)
dat1<-as.data.frame(cbind(Label, Value))

The output I am after is a new column "test" that gets the maximum of the column "Value" for each value of the column "Label" when there are 3 consecutives values that are the same and otherwise just report the values of the column "Value". I do not mind about the missing values at the beggining and at the end, they have to stay.

Expected result of the column test: NA, NA, 3,3,3,1,2,3,3,3,NC,3,3,3,NA,NA

in excel it was very easy and I coded successfully as follow: =IF(AND(BN6=BN5,BN6=BN4),X4,Y6)

but in R I cannot.

I tried several methods, the closest to a result is the following:

test <-c(NA,NA)
test_tot <-NULL

for(i in 3:length(dat1$Label)){
  test_tot<-c(test_tot, test)
  if( dat1$Label[i]==dat1$Label[i+1]&& dat1$Label[i]==dat1$Label[i+2] ){
    test<-max(as.numeric(c(dat1$Value[i],dat1$Value[i+1],dat1$Value[i+2])))
  }
  if(dat1$Label[i]==dat1$Label[i-1]&& dat1$Label[i]==dat1$Label[i+1]){
    test<-max(as.numeric(c(dat1$Value[i],dat1$Value[i-1],dat1$Value[i+1])))
  }
  if(dat1$Label[i]==dat1$Label[i-1]&& dat1$Label[i]==dat1$Label[i-2]){
    test<-max(as.numeric(c(dat1$Value[i],dat1$Value[i-1],dat1$Value[i-2])))
  }

  else {test<-dat1$Value[i]}


}

test_tot<-c(test_tot,NA,NA)
dat1$test<-test_tot

Aucun commentaire:

Enregistrer un commentaire