How to assign multiple categories based on the mean of multiple groups

Hi,

I want to assign categories based on the mean variable of multiple groups in the table below. For each unique combination of set and sp, if there is a single combination then category should be 0; if there is more than a single combination then the biggest mean should have a category of 3; the smallest mean should have a category of 1 and any other mean should have a category of 2.

% Original table

set = [1, 1, 1, 1, 2, 3, 3, 3]';

sp = [23, 23, 45, 45, 10, 23, 23, 23]';

mean = [5.1, 10.6, 23.0, 56.1, 7.8, 78.5, 102.2, 234]';

T = table(set, sp, mean)

The desired output is here:

% Final table

set = [1, 1, 1, 1, 2, 3, 3, 3]';

sp = [23, 23, 45, 45, 10, 23, 23, 23]';

mean = [5.1, 10.6, 23.0, 56.1, 7.8, 78.5, 102.2, 234]';

category = [1, 3, 1, 3, 0, 1, 2, 3]';

T = table(set, sp, mean, category)

Any ideas ?

Thank you

Answer 1

[Piyush Patil]

Hello Blue,

As per my understanding, you want to create a "category" vector such that:

a) If there is single combination of “set” and “sp”, then the “category” should be 0

b) If there are more than one combination, then –

1. "category" corresponding to the largest "mean" will have value 3.
2. "category" corresponding to smallest "mean" will have value 1.
3. "category" corresponding to all the "mean" in between will have value 2.

As an example code, you can check this:

set = [1, 1, 1, 2, 2, 3, 3, 1, 1]

sp = [23, 23, 34, 45, 55, 22, 22, 23, 23]

mean = [100, 80, 60, 70, 80, 50, 20, 50, 40]

then the "category" should be -

category = [3, 2, 0, 0, 0, 3, 1, 2, 1]

I would suggest you perform the following steps to create "category" vector:

1. Find all the indices for the current combination of "set" and "sp".
2. Get the "mean" values for current combination.
3. Assign values to “category” vector for given indices according to the rules.

Here is an example showing how you can do it:

% Original table
set = [1, 1, 1, 2, 2, 3, 3, 1, 1]';
sp = [23, 23, 34, 45, 55, 22, 22, 23, 23]';
mean = [100, 80, 60, 70, 80, 50, 20, 50, 40]';
T = table(set, sp, mean);

% Group the table by set and sp
groupedTable = groupsummary(T, {'set', 'sp'}, 'mean');

% Initialize the category array
category = zeros(height(T), 1);

% Assign categories based on the mean values
for i = 1:height(groupedTable)
    % Find the indices of the rows with the current combination of set and sp
    indices = find(T.set == groupedTable.set(i) & T.sp == groupedTable.sp(i));
    
    % Get the mean values for the current combination
    currentMeans = T.mean(indices);
    
    % Assign categories based on the mean values
    if numel(currentMeans) == 1
        category(indices) = 0;
    else
        [~, maxIndex] = max(currentMeans);
        [~, minIndex] = min(currentMeans);
        category(indices(maxIndex)) = 3;
        category(indices(minIndex)) = 1;
        category(indices(setdiff(1:numel(currentMeans), [maxIndex, minIndex]))) = 2;
    end
end

% Display the desired answer
category'

I hope this resolves the issue you are facing.