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

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?

Thanks in advance for any answers.

Cheers

Ludo

Answer 1

[Ludo Houben]

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

Answer 2

[Cris LaPierre]

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")

[Ludo Houben]

Hi Chris

First of all thank you for your reply. Unfortunally it is not a 'table' it is a YxZ matrix? I don't know how to add data but here is a screenshot maybe that helps. Filtering/grouping of data should be based on column 4 and the minimum value should be based on column 10 of that group.

[Cris LaPierre]

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]

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

Answer 3

[Jan]

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

[Ludo Houben]

Hi Jan

Thanks for the reply. Sorry that I didn't add the data in usable form. Its my first question/post. Coumn 4 was just an example but could be used in my case. However in case that this was 'random' data. What would needed to be changed to your code?

Cheers

Ludo

[Jan]

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]

Hi Jan

Thanks for the thorrow explaination. This helps me in my further development :-)

Regards

Ludo