cumulative sum table over group

5 views (last 30 days)
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.
  1 Comment
Frederick Awuah-Gyasi
Frederick Awuah-Gyasi on 20 May 2022
check @Bruno Luong solution below (middle) . @Steven Lord and @_ yours is the 3rd one

Sign in to comment.

Accepted Answer

Bruno Luong
Bruno Luong on 20 May 2022
Edited: Bruno Luong on 20 May 2022
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
  2 Comments
Frederick Awuah-Gyasi
Frederick Awuah-Gyasi on 23 May 2022
Tested further and this solution proved rather right. Thank you @Bruno Luong

Sign in to comment.

More Answers (1)

Steven Lord
Steven Lord on 20 May 2022
Take a look at the grouptransform function.
  6 Comments
Frederick Awuah-Gyasi
Frederick Awuah-Gyasi on 23 May 2022
@_ yours provided the 3rd results.

Sign in to comment.

Community Treasure Hunt

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

Start Hunting!

Translated by