I am trying to calculate a moving variance of the max last 5 years of observations.
I have a tabke that looks like this:
id year A
1 1990 10
1 1991 20
1 1992 20
1 1993 55
1 1994 40
1 1995 65
1 1996 45
2 2001 85
2 2002 NaN
2 2003 90
2 2004 95
2 2005 500
2 2006 550
.
.
.
And I want to calculate this:
id year A pastvarA
1 1990 10 NaN
1 1991 20 NaN
1 1992 20 Var of 10 and 20
1 1993 55 Var of 10, 20 and 20
1 1994 40 Var of 10, 20, 20 and 55
1 1995 65 Var of 10, 20, 20, 55 and 40
1 1996 45 Var of 20, 20, 55, 40 and 65
2 2001 85 NaN
2 2002 NaN NaN
2 2003 90 Var of 85 and NaN = NaN
2 2004 95 Var of 85, NaN and 90 = Var of 85 and 90
2 2005 500 Var of 85, NaN, 90 and 95= Var of 85, 90 and 95
2 2006 550 Var of 85, NaN, 90, 95 and 500 = Var of 85, 90, 95 and 500
.
.
.
I use the code below:
pastvarA=NaN(N,1);for i=2:5 for k=i+1:N if myTable{k,'id'} == myTable{k-i,'id'} pastvarA(k,1)=var(myTable.A(k-i:k-1),'omitnan'); end endend
I get a result very similar to what I want. The only problem is that I get 0 if there is one observation that is non NaN in the sample that the variance is calculated. But I want to get NaN when that is the case (for example the row: 2 2003 90 Var of 85 and NaN = 0.) the result I get from the code is below:
And I want to calculate this:
id year A pastvarA
1 1990 10 NaN
1 1991 20 NaN
1 1992 20 Var of 10 and 20
1 1993 55 Var of 10, 20 and 20
1 1994 40 Var of 10, 20, 20 and 55
1 1995 65 Var of 10, 20, 20, 55 and 40
1 1996 45 Var of 20, 20, 55, 40 and 65
2 2001 85 NaN
2 2002 NaN NaN
2 2003 90 Var of 85 and NaN = 0
2 2004 95 Var of 85, NaN and 90 = Var of 85 and 90
2 2005 500 Var of 85, NaN, 90 and 95= Var of 85, 90 and 95
2 2006 550 Var of 85, NaN, 90, 95 and 500 = Var of 85, 90, 95 and 500
.
.
.
Best Answer