Variance when only one observation is not NaN
2 views (last 30 days)
Show older comments
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
end
end
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
.
.
.
0 Comments
Accepted Answer
Matt J
on 12 Jan 2021
Edited: Matt J
on 12 Jan 2021
A=myTable.A;
pastvarofA=movvar(A,[4,0],'omitnan');
pastNaNs=movsum(~isnan(A),[4,0],'omitnan');
pastvarofA(pastNaNs<=1)=nan;
pastvarofA=[nan;pastvarofA(1:end-1)]
3 Comments
Matt J
on 12 Jan 2021
Edited: Matt J
on 12 Jan 2021
Just generalize the solution with splitapply()
pastVarofA=splitapply(@func,myTable.A,myTable.id);
function pastVarofA = func(A)
pastvarofA=movvar(A,[4,0],'omitnan');
pastNaNs=movsum(~isnan(A),[4,0],'omitnan');
pastvarofA(pastNaNs<=1)=nan;
pastvarofA=[nan;pastvarofA(1:end-1)];
end
More Answers (0)
See Also
Categories
Find more on Variables in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!