4 views (last 30 days)

Hi,

I've got the following 2 column vectors:

"Name" (300x1 Categorical) and "Amount" (300x1 Double).

I wanna calculate the cumulative sum for each category of vector "Name" BUT I don't wanna start my cumulative sum at the first entry of each category, I only want to calculate the cumulative sum of the last n entries (for each category of "Name"). The exmaple table below illustrates my plan for the cumulative sum of the last 2 entries. The (desired) result is shown in "CumAmount".

A is not a good example, so please follow example B :

The row in which on can find the first entry for B shows the value 4 ("Amount") so the cumulative sum ("CumAmount") would be 4 in this row.

Since the next "Amount" for B is 3, the cumulative sum is 7 in this row.

The third "Amount" for B is 1. Since this example refers to the last two entries, the cumulative sum is not 8 but rather 4 (3+1) - the first "Amount" of B is irgnored, only the last 2 values should be used for the cumulative sum.

I was not able to solve my problem using the functions find and ismember.

Is anybody able to help?

Thank you so much!

+------+--------+-----------+

| Name | Amount | CumAmount |

+------+--------+-----------+

| A | 1 | 1 |

+------+--------+-----------+

| B | 4 | 4 |

+------+--------+-----------+

| C | 4 | 4 |

+------+--------+-----------+

| D | 2 | 2 |

+------+--------+-----------+

| E | 0 | 0 |

+------+--------+-----------+

| F | 2 | 2 |

+------+--------+-----------+

| G | 3 | 3 |

+------+--------+-----------+

| H | 3 | 3 |

+------+--------+-----------+

| I | 2 | 2 |

+------+--------+-----------+

| C | 0 | 4 |

+------+--------+-----------+

| E | 1 | 1 |

+------+--------+-----------+

| F | 1 | 3 |

+------+--------+-----------+

| I | 3 | 5 |

+------+--------+-----------+

| B | 3 | 7 |

+------+--------+-----------+

| A | 2 | 3 |

+------+--------+-----------+

| H | 4 | 7 |

+------+--------+-----------+

| D | 2 | 4 |

+------+--------+-----------+

| E | 0 | 1 |

+------+--------+-----------+

| B | 1 | 4 |

+------+--------+-----------+

| H | 1 | 5 |

+------+--------+-----------+

| C | 2 | 2 |

+------+--------+-----------+

| D | 1 | 3 |

+------+--------+-----------+

| F | 2 | 3 |

+------+--------+-----------+

| I | 0 | 3 |

+------+--------+-----------+

Sean de Wolski
on 11 May 2020

Edited: Sean de Wolski
on 11 May 2020

Read saved table

T = readtable('data.txt');

head(T)

ans =

C A CA

_____ _ __

{'A'} 1 1

{'B'} 4 4

{'C'} 4 4

{'D'} 2 2

{'E'} 0 0

{'F'} 2 2

{'G'} 3 3

{'H'} 3 3

Sort the table to get the ordering and then unique indices for each group.

[Tsorted, sortidx] = sortrows(T, "C");

[~, ~, uniqueix] = unique(Tsorted.C, 'stable');

Specify n, index. The sum function is the valid convolution with a window size n preceded by 0.

n = 2;

oneToN = (1:numel(uniqueix)).';

sumXminusN = @(x){conv([0;Tsorted.A(sort(x))],ones(n,1),'valid')};

Accumulate by group and take the convolution of each group. The order matches those in sorted.

ccac = accumarray(uniqueix, oneToN, [], sumXminusN);

ccaf = vertcat(ccac{:});

Undo the sorting operation and check that it worked.

CA(sortidx, 1) = ccaf;

assert(isequal(CA,T.CA))

This works for N=2. You may need to be smarter with the number of zeros to prepend if you want it work for other N...

Opportunities for recent engineering grads.

Apply Today
## 1 Comment

## Direct link to this comment

https://www.mathworks.com/matlabcentral/answers/524692-cumulative-sum-of-last-n-entries-of-column-vectors#comment_845575

⋮## Direct link to this comment

https://www.mathworks.com/matlabcentral/answers/524692-cumulative-sum-of-last-n-entries-of-column-vectors#comment_845575

Sign in to comment.