Rowfun and sum of array elements based on start and stop position from another array

Hi everybody,

I'm trying to sum values of a timetable based on start and endposition from another array. The results will be inserted in the timetable in a new column.

It works with loops but the size of my table makes it quite slow and since I use vectorization for other purposes, and it is quite fast, I only assume that there is a way to achive it with rowfun for example. But I tried several scenarios but it does not work.

Here is what I try to achieve through an example .

start position array : Start=[1;3;6]

stop position array : Stop=[2;5;8]

Values from timetable: Values=[10;20;10;30;50;40;80;20;10]

I want an output vector with the sum based on start and stop position, so a 3X1.

For example:

First result: sum of Values from position 1 to 2: 10+20=30

Second result: sum of Values from position 3 to 5: 10+30+50=90

Third result: sum of Values from position 6 to 8: 40+80+20=140

So, how to achieve that simple operation with rowfun?

Thanks.

Michel Rathé

Answer 1

[Matt J]

It works with loops but the size of my table makes it quite slow

It shouldn't be. Even with several thousand table columns, a loop should still run in well less than 1 sec.

T=array2table(rand(1e4,3e3));

[M,N]=size(T);
    
Start=ones(1,N);
Stop=M*Start; %Worst case

matrix=table2array(T);
result=nan(1,N);

tic
for i=1:N
    
    result(i)=sum(matrix(Start(i):Stop(i),i));
    
end
toc

Elapsed time is 0.068844 seconds.

[Michel Rathé]

Thanks so much Matt to provide me 2 solutions that covers new grouds, especially the fisrt one.

I found the culprit in the loop that I had:

Work(ind_start(i),3)=num2cell(sum(Work.Rend(ind_start(i):ind_stop(i)-1)));

To prevent an error message, I converted with numcell at each row!!!

Now I use a temp array and convert ourside the loop and insert it in .008 seconds.

a_temp=zeros(size(ind_start,1),1);
for i=1:size(ind_start,1)
a_temp(i)=sum(Work.Rend(ind_start(i):ind_stop(i)-1)); 
end
Work(ind_start,3)=array2table(a_temp);

Thanks again,

Michel

Answer 2

[Matt J]

So, how to achieve that simple operation with rowfun?

I don't see rowfun as being part of the solution. Just pull the values out into matrices as you have done and use matrix operations,

 Start=[1;3;6];
 Stop=[2;5;8];
 Values=[10;20;10;30;50;40;80;20;10];
 
  e=(1:size(Values,1)).';
  
  result=sum( (e<=Stop(:).' & e>=Start(:).').*Values ,1)

result = 1×3

30 90 140