Cumulative sum of last n entries of column vectors

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         |
+------+--------+-----------+

[Ameer Hamza]

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.

Answer 1

[Sean de Wolski]

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...

[buhmatlab]

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!