cumulative sum table over group

tableA
Name Day Color Quantity 
A    1    0        3 
A    1    2        3 
A    1    3        3 
A    2    0        2
A    2    2        4 
A    2    3        5
B    1    0        3 
B    1    1        3 
B    1    3        3 
B    2    0        4 
B    2    2        1
B    2    3        0
C    1    0        2 
C    1    1        1 
C    1    2        3 
C    2    0        1
C    2    1        2
C    2    2        3

Results : Cumulative sum for each color over the days.

tableA_cumsum
Name Day Color Quantity 
A    1    0        3 
A    1    2        3 
A    1    3        3 
A    2    0        5
A    2    2        7 
A    2    3        8
B    1    0        3 
B    1    1        3 
B    1    3        3 
B    2    0       12
B    2    2        1
B    2    3        3
C    1    0        2 
C    1    1        1 
C    1    2        3 
C    2    0        4
C    2    1        3
C    2    2        6

Was trying cumsum but not sure how to get the groupings done.

[Frederick Awuah-Gyasi]

check @Bruno Luong solution below (middle) . @Steven Lord and @_ yours is the 3rd one

Answer 1

[Bruno Luong]

tableA = {"A"    1    0        3 ;
"A"    1    2        3 ;
"A"    1    3        3 ;
"A"    2    0        2;
"A"    2    2        4 ;
"A"    2    3        5;
"B"    1    0        3 ;
"B"    1    1        3 ;
"B"    1    3        3 ;
"B"    2    0        4 ;
"B"    2    2        1;
"C"    1    0        2 ;
"C"    1    1        1 ;
"C"    1    2        3 ;
"C"    2    0        1;
"C"    2    1        2;
"C"    2    2        3};
tableA = cell2table(tableA, "VariableNames",["Name" "Day" "Color" "Quantity"])

tableA = 17×4 table
    Name    Day    Color    Quantity
    ____    ___    _____    ________

    "A"      1       0         3    
    "A"      1       2         3    
    "A"      1       3         3    
    "A"      2       0         2    
    "A"      2       2         4    
    "A"      2       3         5    
    "B"      1       0         3    
    "B"      1       1         3    
    "B"      1       3         3    
    "B"      2       0         4    
    "B"      2       2         1    
    "C"      1       0         2    
    "C"      1       1         1    
    "C"      1       2         3    
    "C"      2       0         1    
    "C"      2       1         2    

[~,~,G]=unique([tableA.Name tableA.Color],"rows");
b = zeros(1,max(G));
tableA_cumsum = tableA;
for k=1:length(G)
    Gk = G(k);
    s = b(Gk) + tableA.Quantity(k);
    tableA_cumsum.Quantity(k) = s;
    b(Gk) = s;
end

tableA_cumsum

tableA_cumsum = 17×4 table
    Name    Day    Color    Quantity
    ____    ___    _____    ________

    "A"      1       0         3    
    "A"      1       2         3    
    "A"      1       3         3    
    "A"      2       0         5    
    "A"      2       2         7    
    "A"      2       3         8    
    "B"      1       0         3    
    "B"      1       1         3    
    "B"      1       3         3    
    "B"      2       0         7    
    "B"      2       2         1    
    "C"      1       0         2    
    "C"      1       1         1    
    "C"      1       2         3    
    "C"      2       0         3    
    "C"      2       1         3    

[Frederick Awuah-Gyasi]

Thank you @Bruno Luong. I will test this out.

[Frederick Awuah-Gyasi]

Tested further and this solution proved rather right. Thank you @Bruno Luong

Answer 2

[Steven Lord]

Take a look at the grouptransform function.

[Frederick Awuah-Gyasi]

Thanks you @Steven Lord I checked it out but does not seem to have 'sum' as an option. For this problem I want to have a cumulative sum for Quantity

[Steven Lord]

You can specify a function handle to a function that satisfies certain conditions as the method input.

A = randi(3, 6, 1);
B = randi(10, 6, 1);
T = table(A, B)

T = 6×2 table
    A    B
    _    _

    1    8
    2    3
    3    2
    3    5
    3    8
    1    9

grouptransform(T, 'A', @cumsum)

ans = 6×2 table
    A    B 
    _    __

    1     8
    2     3
    3     2
    3     7
    3    15
    1    17

[Frederick Awuah-Gyasi]

@Steven Lord. Thank you. I see that it will work but the challenge is there is a 3 column which is needed but will not be used for the grouping .

Error using grouptransform (line 301)

Unable to apply method to data variable 'Days'.

I wish there is a way to take it out do the groupTransform and join to have it back in place.

[Voss]

"I wish there is a way to take it out do the groupTransform and join to have it back in place."

Try this:

Name = char('A' + randi([0 2], 18, 1));
Day = randi(2, 18, 1);
Color = randi([0 3], 18, 1);
Quantity = randi([0 5], 18, 1);
T = table(Name, Day, Color, Quantity)

T = 18×4 table
    Name    Day    Color    Quantity
    ____    ___    _____    ________

     C       2       0         1    
     C       2       2         2    
     C       2       3         3    
     A       1       0         3    
     C       2       0         2    
     C       2       2         0    
     A       2       1         2    
     B       2       2         1    
     C       2       1         5    
     C       1       1         0    
     B       2       2         1    
     C       1       0         4    
     A       2       3         4    
     C       1       1         2    
     A       2       1         4    
     C       1       1         0    

T(:,{'Color' 'Quantity'}) = grouptransform(T(:,{'Color' 'Quantity'}), 'Color', @cumsum)

T = 18×4 table
    Name    Day    Color    Quantity
    ____    ___    _____    ________

     C       2       0          1   
     C       2       2          2   
     C       2       3          3   
     A       1       0          4   
     C       2       0          6   
     C       2       2          2   
     A       2       1          2   
     B       2       2          3   
     C       2       1          7   
     C       1       1          7   
     B       2       2          4   
     C       1       0         10   
     A       2       3          7   
     C       1       1          9   
     A       2       1         13   
     C       1       1         13   

[Frederick Awuah-Gyasi]

@_ This worked perfectly. Thanks so much.Thank you @Steven Lord

[Frederick Awuah-Gyasi]

@_ yours provided the 3rd results.