MATLAB Answers

Cumulative sum of last n entries of column vectors

4 views (last 30 days)
buhmatlab
buhmatlab on 11 May 2020
Edited: buhmatlab on 11 May 2020
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 |
+------+--------+-----------+

  1 Comment

Ameer Hamza
Ameer Hamza on 11 May 2020
Can you attach the data as a mat file. That will at least make it easy to test a solution. This table format is not very useful for importing the data to MATLAB.

Sign in to comment.

Accepted Answer

Sean de Wolski
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...

  1 Comment

buhmatlab
buhmatlab on 11 May 2020
Awesome! I have not really understood your code and it will take some time to retrace your answer but at least I can tell that it just works fine! Thank you so much!

Sign in to comment.

More Answers (0)