Conditional average (need help with speed)

3 views (last 30 days)
Mia Dier
Mia Dier on 16 Jan 2021
Commented: dpb on 17 Jan 2021
I have a table that looks like this:
country_id year M T average_T
1 2000 10 76 NaN
1 2001 5 39 Mean of 76 and 62
1 2002 NaN 37 Mean of 39 =39
1 2003 15 5 NaN
1 2004 10 28 Mean of 5 and 2
1 2005 10 8 Mean of 8=8
2 1999 15 1 NaN
2 2000 10 62 Mean of 1=1
2 2001 20 32 Mean of 76 and 62
2 2002 10 72 Mean of 32=32
2 2003 15 2 Mean of 5 and 2
I want to calculate the column average_T which is last year's average of the T values for the cases that have the same year and M value. (First entry for each id is NaN because we don't know past year's T for those entries)
I have written a code that can do this but it is impossible to run with my big data set:
mytable.average_T=NaN(N,1);
for k=2:N
if mytable{k,'country_id'} == mytable{k-1,'country_id'}
mytable.average_T(k,1)= mean(T(mytable.M==mytable.M(k-1)& ...
mytable.year==mytable.year(k-1)), 'omitNaN');
end
end

Accepted Answer

dpb
dpb on 16 Jan 2021
Edited: dpb on 17 Jan 2021
Grouping variables and rowfun to the rescue...
tMeans=rowfun(@(x),mean(x,'omitnan'),mytable,'InputVariables','T','GroupingVariables',{'year','M'});
  11 Comments
dpb
dpb on 17 Jan 2021
NB: You could do the same thing with findgroups and splitapply without building the output table from rowfun, too.

Sign in to comment.

More Answers (0)

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by