Get min from group(s) of data within an array

14 views (last 30 days)
Ludo Houben on 3 Nov 2022
Answered: Ludo Houben on 4 Nov 2022
Hi All
As a newbie I'm struggeling with the following.
I have created a subset of a data set into a 'value n' x 'value m' double matrix. It contains values for several days and I need a 'minimum' value per day of a specific column. So in example:
Source: 'n' x 'm' double Result: 'v' x 4 double
2022 | 02 | 01 | 1 | ... | ... | 6 | ...
2022 | 02 | 01 | 2 | ... | ... | 6 | ...
2022 | 02 | 01 | 3 | ... | ... | 5 | ...
2022 | 02 | 02 | 1 | ... | ... | 4 | ...
2022 | 02 | 02 | 2 | ... | ... | 3 | ... ==> 2022 | 02 | 01 | 5
2022 | 02 | 02 | 3 | ... | ... | 6 | ... 2022 | 02 | 02 | 3
2022 | 02 | 03 | 1 | ... | ... | 2 | ... 2022 | 02 | 03 | 2
2022 | 02 | 03 | 2 | ... | ... | 6 | ...
2022 | 02 | 03 | 3 | ... | ... | 3 | ...
For getting the minimum value I can use the 'min()' function, but how to create the 'grouping' of the data?
Cheers
Ludo

Ludo Houben on 4 Nov 2022
Hi all
The final solution was combining both answers from Jan and Chris.
% Create table 'X'
X = [2022 02 01 1 6; ...
2022 02 01 2 6; ...
2022 02 01 3 5; ...
2022 02 02 1 4; ...
2022 02 02 2 3; ...
2022 02 02 3 6; ...
2022 02 03 1 2; ...
2022 02 03 2 6; ...
2022 02 03 3 3];
[DayInfo,~] = unique(X(:,1:3),'rows','stable'); % This creates an array 'DayInfo' containing all unique dates
temp = groupsummary(X(:,5),X(:,1:3),"min"); % This gets the minimum value of row 5 for each day
DayInfo = cat(2,DayInfo,temp); % This combines the 2 arrays
%% Combination of formula's temp + DayInfo
DayInfo = cat(2,DayInfo,groupsummary(X(:,5),X(:,1:3),"min")); % This combines the last 2 previous actions
So a big shout out to them for their help.
Regards
Ludo

Cris LaPierre on 3 Nov 2022
Use groupsummary. If you are really new to MATLAB, consider using the Compute by Group task inside a live script.
Since you haven't attached your data, here's a rough guess at what the code might be. This is written assuming your data is in a table.
dailyMin = groupsummary(tblNm,["Var1","Var2","Var3"],"min","Var7")
3 CommentsShow 1 older commentHide 1 older comment
Cris LaPierre on 3 Nov 2022
Edited: Cris LaPierre on 3 Nov 2022
Consider saving your variables to a mat file and attaching it to your post using the paperclip icon.
In that case, use the Array Data syntax instead. I'm guessing at column numbers to use. You'll need to correct.
dailyMin = groupsummary(SubArc(:,7),SubArc(:,2:4),"min")
Ludo Houben on 3 Nov 2022
Hi Chris
The solution is not completely the desired format, but it gives me a good direction to continue my quest with. Thank you for your reply.
Regards
Ludo

Jan on 3 Nov 2022
Is the 4th row a counter for each day? Then this can be used:
(By the way, please post input data in a form, in which they can be used by copy&paste - this makes it much easier to use it in an answer)
X = [2022 02 01 1 6; ...
2022 02 01 2 6; ...
2022 02 01 3 5; ...
2022 02 02 1 4; ...
2022 02 02 2 3; ...
2022 02 02 3 6; ...
2022 02 03 1 2; ...
2022 02 03 2 6; ...
2022 02 03 3 3];
G1 = X(:, 4) == 1;
G = cumsum(double(G1));
minGValue = accumarray(G, X(:, 5), [], @min);
Result = [X(G1, 1:3), minGValue]
Result = 3×4
2022 2 1 5 2022 2 2 3 2022 2 3 2
3 CommentsShow 1 older commentHide 1 older comment
Jan on 3 Nov 2022
Edited: Jan on 3 Nov 2022
I'd use:
X = [2022 02 01 1 6; ...
2022 02 01 2 6; ...
2022 02 01 3 5; ...
2022 02 02 1 4; ...
2022 02 02 2 3; ...
2022 02 02 3 6; ...
2022 02 03 1 2; ...
2022 02 03 2 6; ...
2022 02 03 3 3];
[Xdate, ~, G] = unique(X(:, 1:3), 'rows', 'stable')
Xdate = 3×3
2022 2 1 2022 2 2 2022 2 3
G = 9×1
1 1 1 2 2 2 3 3 3
to obtain the group index for each set of dates.
Ludo Houben on 4 Nov 2022
Hi Jan
Thanks for the thorrow explaination. This helps me in my further development :-)
Regards
Ludo

Categories

Find more on Logical in Help Center and File Exchange

R2022b

Community Treasure Hunt

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

Start Hunting!