Dear list,
I have the following problem. I have a data frame like this
CLUSTER YEAR variable Delta R_pivot
M1 2005 EC01 NA NA
M1 2006 EC01 2 NA
M1 2007 EC01 4 5
M2 2005 EC01 NA NA
M2 2006 EC01 5 NA
M2 2007 EC01 8 7
M1 2005 EC02 NA NA
M1 2006 EC02 3 NA
M1 2007 EC02 1 8
M2 2005 EC02 NA NA
M2 2006 EC02 9 NA
M2 2007 EC02 6 10
I'm trying to build the time series of the variables by applying the following formulas in a recursive way(by starting from the value of R_pivot at time 2007)
R_EC01(2006)=R_EC01(2007)-Delta_EC01(2007)
R_EC01(2005)=R_EC01(2006)-Delta_EC01(2006)
In terms of number I would have:
R_EC01(2006)=5-4=1
R_eco1(2005)=1-2=-1
And the same should be done for variable EC02. In addition, this calculations should be down grouping by variable e cluster..so the result should be
CLUSTER YEAR variable series
M1 2005 EC01 -1
M1 2006 EC01 1
M1 2007 EC01 5
M2 2005 EC01 -6
M2 2006 EC01 -1
M2 2007 EC01 7
M1 2005 EC02 4
M1 2006 EC02 7
M1 2007 EC02 8
M2 2005 EC02 -5
M2 2006 EC02 4
M2 2007 EC02 10
I applied the following formula which gives me a partial good result but not at all:
series=ddply(x,.(variable,CLUSTER),transform,series=rev(c(R_pivot,rev(R_pivot-cumsum(rev(Delta[-1]))))))
Thanks for your attention!!!
[[alternative HTML version deleted]]