How to add cumulative values of a set of unique combinations within a Table?

Greetings,

I'd like to know if it is possible to add up the cumulative sum from the values in a particular column that comply with the rule of being part of unique combinations of two ore more columns.

Please find below a simple sample code that could serve as illustration.

T = table({'John';'Mary';'John';'Mary';'John';'John';'Mary';'John'},...
  {'Lot A';'Lot A';'Lot A';'Lot Z';'Lot Z';'Lot A';'Lot Z'; 'Lot Z'},...
  datetime({'01/07/2015';'03/08/2015';'05/25/2014';'03/26/2016';'07/08/2009';...
  '03/01/2020';'08/23/2010';'06/15/2008'}),[12; 25; 36; 89; 11; 56; 87; 93],...
  'VariableNames', {'Salesperson','Lot','Date','Quantity'});

I'd like to know if I can produce a table that would return the cumulative sum of four possible unique combinations that are derived from the "Salesperson" and the "Lot" columns:

John - Lot A, John - Lot Z, Mary - Lot A, Mary - Lot Z

I'd like a table that would merge and add the cumulative sum of what John sold from Lot A, what John sold from Lot Z, what Mary sold from Lot A and what Mary sold from lot Z, which are the only four possible unique combinations derived from Columns 1 & 2 ("Salesperson & Lot").

As for the date, I'd like this solution to be able to include the date column. I know that the date is not part of the inputs required to derive this cumulative sum, but I'd like each unique combinations to include either the earliest or latest date.

Thank you in advance for your help.

Answer 1

[BhaTTa]

You can achieve this in MATLAB by using the groupsummary function to group the data by the unique combinations of the "Salesperson" and "Lot" columns, and then calculate the cumulative sum of the "Quantity" column for each group. Additionally, you can extract the earliest or latest date for each group.

Here's how you can do it:

% Sample data
T = table({'John';'Mary';'John';'Mary';'John';'John';'Mary';'John'},...
  {'Lot A';'Lot A';'Lot A';'Lot Z';'Lot Z';'Lot A';'Lot Z'; 'Lot Z'},...
  datetime({'01/07/2015';'03/08/2015';'05/25/2014';'03/26/2016';'07/08/2009';...
  '03/01/2020';'08/23/2010';'06/15/2008'}),[12; 25; 36; 89; 11; 56; 87; 93],...
  'VariableNames', {'Salesperson','Lot','Date','Quantity'});

% Group by Salesperson and Lot, and calculate cumulative sum of Quantity
G = groupsummary(T, {'Salesperson', 'Lot'}, {'sum'}, 'Quantity');

% Find the earliest date for each unique combination
[~, earliestIdx] = unique(T(:, {'Salesperson', 'Lot'}), 'rows', 'stable');
earliestDates = T.Date(earliestIdx);

% Add the earliest date to the grouped table
G.EarliestDate = earliestDates;

% Display the result
disp(G);