Cumulative sum of last n entries of column vectors

3 visualizaciones (últimos 30 días)
buhmatlab
buhmatlab el 11 de Mayo de 2020
Editada: buhmatlab el 11 de Mayo de 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 comentario
Ameer Hamza
Ameer Hamza el 11 de Mayo de 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.

Iniciar sesión para comentar.

Respuesta aceptada

Sean de Wolski
Sean de Wolski el 11 de Mayo de 2020
Editada: Sean de Wolski el 11 de Mayo de 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 comentario
buhmatlab
buhmatlab el 11 de Mayo de 2020
Editada: buhmatlab el 11 de Mayo de 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!

Iniciar sesión para comentar.

Más respuestas (0)

Categorías

Más información sobre Logical en Help Center y File Exchange.

Community Treasure Hunt

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

Start Hunting!

Translated by