# Is it possible to use Arrayfun across rows

2 views (last 30 days)
William Ambrose on 6 Oct 2020
Commented: Mohammad Sami on 8 Oct 2020
Hi,
I currently have a FOR LOOP which works its way through a table with almost 20 million records. It is as expected pretty slow, I want to look into alternatives and I wondered if there is a way to use for arrayfun - or another MATLAB function - across rows which will work with high performance. The example below captures the issue of working across rows:
A = table([1;1;1;2;2;2;],[1;2;3;4;5;6]);
A.Var3 = zeros(height(A),1)
A.Var3(1) = A.Var1(1)
for i = 2:height(A)
if A.Var1(i) == A.Var1(i-1)
A.Var3(i) = A.Var2(i) .* A.Var2(i-1);
else A.Var3(i) = A.Var2(i);
end
end
Any suggestions will be appreciated.
Kind regards,
William

William Ambrose on 6 Oct 2020
thanks Rik, I just worked out how to paste the code and format. Pitty on the code part, looping through the data takes such a long time. The challenge is the dependency on the value in the previous row.
Rik on 6 Oct 2020
The longer the runs are, the more efficient calculating the runs will be. So if you have long stretches of true and/or long stretches of false it might be worth looking into. I think the first branch can also be vectorized (e.g. with cumprod), although I haven't tried yet.
William Ambrose on 6 Oct 2020
You are right with respect to the true/false, I will definitly use
i = [false; A.Var1(1:end-1) == A.Var1(2:end)]
to get the logical array, I suspect it will make a big impact.

Mohammad Sami on 6 Oct 2020
Something like this will work.
i = [false; A.Var1(1:end-1) == A.Var1(2:end)];
j = find(i);
A.Var3(i) = A.Var2(j) .* A.Var2(j-1);
A.Var3(~i) = A.Var2(~i);

Mohammad Sami on 6 Oct 2020
In that case you can use this
A = table([1;1;1;1;1;2;2;2;3],[1;2;3;4;5;6;7;8;500]);
i = [true; A.Var1(1:end-1) ~= A.Var1(2:end)];
id = cumsum(i);
A.Var3 = grouptransform(A.Var2,id,@cumprod);
The above is assuming that Var1 maynot be in sequence e.g. [1 1 1 2 2 2 4 4 4] e.t.c
If it is always in sequence you can shorten it as follows.
A = table([1;1;1;1;1;2;2;2;3],[1;2;3;4;5;6;7;8;500]);
A = grouptransform(A,'Var1',@cumprod,"ReplaceValues",false);
% or explicitly specify which variable to transform if you have other variables
% A = grouptransform(A,'Var1',@cumprod,"Var2","ReplaceValues",false);
William Ambrose on 8 Oct 2020
Hi Mohammed,
I like the use of grouptransform but unfortunately the use of @cumprod as the function isn't correct. What I need is an iterative cascade through the rows where the result in the previous row - in another column - is input into the calculation. Almost like a FOR LOOP solution for the Fibonnaci sequence.
To be sure I will include the corrected version of the code, with some of your suggestions embedded in, and expected outcome:
B = table([1;1;1;1;1;2;2;2;3],[1;2;3;4;5;6;7;8;500]);
B.Var3 = zeros(height(B),1);
i = [false; B.Var1(1:end-1) == B.Var1(2:end)];
j = find(~i);
B.Var3(j) = B.Var2(j);
tic;
for n = 1:height(B)
if i(n) == 1
B.Var3(n) = B.Var3(n-1) .* B.Var2(n);
end
end ; toc
and the result
B =
9×3 table
Var1 Var2 Var3
____ ____ ____
1 1 1
1 2 2
1 3 6
1 4 24
1 5 120
2 6 6
2 7 42
2 8 336
3 500 500
I have appreciate all your suggestions so far so if you have input which could solve the above but without the FOR LOOP I would be very greatful for any input.
Mohammad Sami on 8 Oct 2020
Hi William,
For the updated problem as stated, grouptransform with cumprod will work just as well.
My testing shows the result is identical to the expected result.
A =
9×3 table
Var1 Var2 fun_Var2
____ ____ ________
1 1 1
1 2 2
1 3 6
1 4 24
1 5 120
2 6 6
2 7 42
2 8 336
3 500 500
Ofcourse if the formula changes, for loop may be more generalizable.

R2019b

### Community Treasure Hunt

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

Start Hunting!