Create a cumulative sum under certain conditions

7 views (last 30 days)
Hello together, I am pretty new to MATLAB since I has been forced to switch. Accordingly I have not many skills that would help me solve my problem wherefore I would appreciate any help!
My Problem: I have a matrix (actually it's an imported CSV-table) of following form:
  • column1: contains numbers of days incrementing every 10th value (1,1,1,1,1,1,1,1,1,1,2,2,2,....,40)
  • column2: contains names where each name only appears once a day (e.g. "Paul" would appear once on day 1,2,..,40).
  • column3: cotains the output
My goal is to create a further column where I can create the cumulative sum of the column containing the output numbers.
For example:
On day 1 Paul has Output 5, on day 2 it is an output of 3 and on day 3 it is 4.
So in my newly created cumulative-sum-column (column4) the following should happen in every line:
  • If column1 = 1, column2 = Paul, column3 = 5 then column4 = 5
  • If column1 = 2, column2 = Paul, column3 = 3 then column4 = 8
  • If column1 = 3, column2 = Paul, column3 = 4 then column4 = 12
Is there anybody able to help?
Many thanks in advance!

Accepted Answer

Bhaskar R
Bhaskar R on 17 Nov 2019
Edited: Bhaskar R on 17 Nov 2019
Assuming your imported data from csv as variables Day, Name, Output all are column matrices with same length
I don't know variance of the conditions over here
Then take a dummy cumulative sum of column Output as column 4 value
csum = cumsum(Output);
Then take indeces from conditions as
column_4_value = csum(Day == 1 & strcmp(Name, 'Paul') & Output == 5);
Hope helps you !!

More Answers (1)

Akira Agata
Akira Agata on 18 Nov 2019
How about the following?
% Sample data
days = repelem((1:10)',10,1);
names = repmat(['A':'J']',10,1);
output = randi(100,100,1);
% Arrange them to create table variable
Data = table(days,names,output);
% Apply findgroups function
group = findgroups(Data.names);
% Apply cumsum function for each group
cSum = splitapply(@(x) {cumsum(x)},Data.output,group);
% Store the calculated result to 4th column
Data.result = nan(height(Data),1);
for kk = 1:max(group)
idx = group == kk;
Data.result(idx) = cSum{kk};

Community Treasure Hunt

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

Start Hunting!