0

I am struggeling to get maximum value of variable from last year of observations (Not each year!) and implement it to each row (observation).

I think the best way to do so is using the rollapply function but I cannot figure out how the width should look like since it may vary for each observation (each observation represents a day but not all days have observations). I know that using list will make offset values so how sould these values look like ?

The code I got:

mutate(data,"Feature"=rollapplyr(variable,list(0,"Go back one year"),max,fill=NA))

Example in order to clarify: a row has date of 31/8/2016. I want the new column (using mutate of dplyr package) to display in this row the maximum value of variablefrom 31/8/2015 to 31/8/2016 (this row).

For those who want to go further: Instead of displaying the variable value - display TRUE or FALSE(or 1 / 0) when calculated maximum variable is above threshold value.

pogibas
  • 27,303
  • 19
  • 84
  • 117
Yoni W
  • 37
  • 5
  • 1
    It's more likely you will get a good answer if you provide a [complete minimal reproducible example](http://stackoverflow.com/help/mcve) to go along with your question. Something we can work from and use to show you how it might be possible to answer your question. This also makes your question and answer more useful for others in the future. – Eric Fail Oct 21 '17 at 09:34

1 Answers1

1

Difficult to answer without further details. But see if this is what you need:

data=data.frame(Data=seq.Date(as.Date("2001-01-01"),as.Date("2005-12-31"),by = "month"),Var=sample(1:1000,60,TRUE))
#exclude some lines
data=data[-c(10,15,17:21),]


 # using for
    for (i  in 1:nrow(data)){ # i=1
      data$Max[i]=max(data[data$Data>(data$Data[i]-360) & data$Data<=data$Data[i],"Var"])
    }


# using rollapply
 # one year interval from dates   
    for (i  in 1:nrow(data)){ # i=1
      data$Oneyear[i]=length(data$Data[data$Data>(data$Data[i]-360) & data$Data<=data$Data[i]])
    }  

data$Maxr=rollapplyr(data$Var, data$Oneyear, max)

Using 

set.seed(123)

you will get:

> tail(data)
         Data Var Oneyear Max Maxr
55 2005-07-01 561      12 858  858
56 2005-08-01 207      12 858  858
57 2005-09-01 128      12 858  858
58 2005-10-01 754      12 858  858
59 2005-11-01 896      12 896  896
60 2005-12-01 375      12 896  896
Robert
  • 5,038
  • 1
  • 25
  • 43
  • Thank you @Robert ! was very helpful ! I saw you used 360 days as year. Is it possible to exchange this with complete year depending on observation date? (By complete year I mean minus 1 in the year of the date while day and month remain the same. – Yoni W Oct 21 '17 at 10:47
  • Yes. Instead of `(data$Data[i]-360)` use `as.Date(paste(as.numeric(format(data$Data[i],"%Y"))-1,format(data$Data[i],"%m-%d"),sep="-"))` – Robert Oct 21 '17 at 11:36