Conditional average (need help with speed)

1 view (last 30 days)
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

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'});
dpb on 17 Jan 2021
NB: You could do the same thing with findgroups and splitapply without building the output table from rowfun, too.