0

I have a data frame with a date and average value. I need to shift the average value a month behind i.e. 2016-May's average should be 2016-Apr's average and so on. The first month will be 0.

The original data set

concat      avg_sa
2016 - Apr  43.075
2016 - Aug  53.18387097
2016 - Dec  70.87903226
2016 - Jul  51.1516129
2016 - Jun  46.40166667
2016 - May  42.76935484
2016 - Nov  73.81666667
2016 - Oct  67.6
2016 - Sep  56.35833333
2017 - Apr  67.34166667
2017 - Aug  72.67741935
2017 - Dec  79.01612903
2017 - Feb  71.85714286
2017 - Jan  73.30645161
2017 - Jul  72.50806452
2017 - Jun  67.78333333
2017 - Mar  65.06451613
2017 - May     63.40645161

Please help.Thanks in advance!!

The expected data set

concat         new_avg_sa
2016 - Apr  0
2016 - May  43.075
2016 - Jun  42.76935484
2016 - Jul  46.40166667
2016 - Aug  51.1516129
2016 - Sep  53.18387097
2016 - Oct  56.35833333
2016 - Nov  67.6
2016 - Dec  73.81666667
2017 - Jan  70.87903226
2017 - Feb  73.30645161
2017 - Mar  71.85714286
2017 - Apr  65.06451613
2017 - May  67.34166667
Ami
  • 197
  • 1
  • 12
  • 1
    Convert to `zoo` with a `yearmon` index. This also sorts the data. Then merge lagged value with original times filling in with 0. Finally convert back to data frame. `library(zoo); z <- read.zoo(DF, FUN = as.yearmon, format = "%Y - %b", drop = FALSE); z <- merge(lag(z, -1), zoo(, time(z)), all = TRUE, fill = 0); fortify.zoo(z, names = names(DF))` Can omit the last line if a zoo object `z` is ok. – G. Grothendieck Jul 04 '19 at 12:14
  • Thanks @G.Grothendieck for the reply. This surely did help. Furthermore, I simply added as.data.frame() to wrap the fortify.zoo() in order to save it in a data frame format. Thanks again. – Ami Jul 05 '19 at 05:52
  • No. `fortify.zoo` already already produces a data.frame. That is its purpose. – G. Grothendieck Jul 05 '19 at 11:12
  • Ok, thanks for the highlight. I have made the changes. – Ami Jul 05 '19 at 12:33

0 Answers0