Max-min of a matrix column over rows where other columns have given values

Problem A. A is a matrix. I want to pick the matrix rows on which columns 2:9 have certain values and compute the max-min of column 10 over those rows.

[Even an efficient solution to just problem A would be very nice. Even better, if somebody solves the whole problem (problem B) that I describe below.]

Problem B1. I go through all possible value combinations in columns 2:9, and pick a the max-min for each such combination as in "problem A" above. Then I take the max over all those "max-min" values.

[I call this "the maximal effect of parameter 1 (column 1) to column 10 (output 10)". Indeed, columns 1:9 are considered parameters (or "inputs") and columns 10:20 are outputs or "test results".]

I also want to get the effects on columns 11:20, and also the effects of columns 2:9 (in place of column 1).

Problem B2. [This is the same as problem B1, but I describe it using different words.]

A is a matrix. Columns 1:9 are parameters and columns 10:20 are outputs.

For any o in [10,20] and any parameter values p=(p1,p2,...,p9), let O(p;o) be the set of values A(k,o), where the row number k is such that A(k,j)=pj for all j. That is: the value of output o on the lines that have parameters p.

For each parameter pk, I want to know its maximal effect on the outputs. That is:

effect(1,o) = max_{p2,p3,...,p9} (max(P(:,p2,p3,...,p9;o) - min(A(:,p2,p3,...,p9;o))

So for each fixed values of the other parameters p2, p3, ..., p9, we check how big and small o-value we get by changing just p1. Their difference is the max-min (the effect of p1 with those fixed p2,p3,...,p9).

Then effect(1,o) is the maximum of these effects (over all possible fixed fixed p2,p3,...,p9).

Analogously for the other parameters 2,...,9.

Solution B. This code solves problem B1 = problem B2 (hence also A). However, I believe that this code is much slower than a medium-level Matlab worker could produce; I just don't know that much of Matlab. (My true A has many lines and more columns, and the same might apply to many readers.)

(This code works even if there are many rows with the same parameter combination (so the outputs need not be uniquely determined by the parameters but may differ even with same parameters).)

Nrows = size(A,1); %A is a Nrows*20 matrix. 
Npars = 9; %We consider columns 1:9 parameters
Nouts = 11;
effect = zeros(Npars, Nouts); %effects of 9 pars on 11 outputs
for n = 1:9
    otherpars = [1:n-1 n+1:Npars]; %all pars but the n'th
    B = unique(A(:,otherpars),'rows');  %Kx(Npar-1) matrix of all par columns but the n'th (all unique value combinations)
    K = size(B,1); %number of different combinations of otherpars
    doutsK = zeros(K, Nouts); %doutsK(k,nout) = to be max(nout)-min(nout) of for the k'th otherpars combination
    for k=1:K % otherpar combinations
        ind = ismember(A(:, otherpars), B(k,:), 'rows'); %Nrows*1 vector. "1" on rows having the k'th combination of otherpars, "0" on others.
        doutsK(k, :) = max(A(ind, Npars+1:end), [], 1) - min(A(ind, Npars+1:end), [], 1); %max-min for that k'th combination (row vector)
    end
    effect(n, :) = max(doutsK, [], 1); % max_otherparcombinations (max-min spread) (1xNouts row vector)
end

[Jan]

Reading the descitption is a challenge. A hint: Avoid clutter like "An efficient solution to just this problem would be very nice". Anyway, even without understanding the details of the description, there are some standard ways to improve the code by avoiding repetaed work. If you provide some input data, we could run the code and this is will make it much easier to test the suggestions.

Nrows  = size(A, 1);
Npars  = 9;
Nouts  = 11;
effect = zeros(Npars, Nouts);
other  = true(1, Npars);
AEnd   = A(:, Npars+1:end);
for n = 1:9
    other(n) = false;        % Logical indexing
    AA       = A(:, other);  % Create the submatrix once only
    B = unique(AA, 'rows');
    K = size(B, 1);
    doutsK = zeros(1, Nouts);  % Do not collect a matrix just to obtain the maximum
    for k = 1:K
        % ind  = ismember(AA, B(k,:), 'rows');
        % This is faster:
        ind    = all(AA == B(k, :), 2);
        Aind   = AEnd(ind, :);
        range  = max(Aind, [], 1) - min(Aind, [], 1);
        doutsK = max(doutsK, range);
    end
    effect(n, :) = doutsK;
    
    other(n) = true;  % restore logical index vector
end

[Real User]

Thank you very much. With a non-big file, I got 30 sec with my code, 1.6 secs with your code, and 0.18 secs with your "Maybe this is faster" code. I guess that even the that 1.6 secs would likely have been good enough for my biggest future files, but I'll use the 0.18 code. Sorry for the unclarity.

Your code worked as such with the following obvious corrections:

• other -> others
• remove the obvious extra ")" in "range = ... (Aind) ...".

The results were the same for both codes, so I'm very happy with this.

I also tried the following test data, but this data is quite bad; it does not test all the features of the code (unlike my data):

A = magic(20);
A([1 5 6 10 13 14 17],1:9) = repelem(A(1,1:9), 7, 1);
A([2 7 8 12 16 20],1:9) = repelem(A(2,1:9), 6, 1);
A([3 9 18 ],1:9) = repelem(A(3,1:9), 3, 1);
A([4 11 15 19],1:9) = repelem(A(4,1:9), 4, 1);
A(17,10:20) = 320; %then some columns have max there but not all

[Jan]

@Real User: I've fixed the typos and post the code in the section for answers.

Answer 1

[Jan]

In addition to my comment: max(x)-min(x) expressed as range(x):

Nrows  = size(A, 1);
Npars  = 9;
Nouts  = 11;
effect = zeros(Npars, Nouts);
other  = true(1, Npars);
AEnd   = A(:, Npars+1:end);
for n = 1:9
    other(n) = false;        % Logical indexing
    AA       = A(:, other);  % Create the submatrix once only
    other(n) = true;         % restore logical index vector

    B = unique(AA, 'rows');
    K = size(B, 1);
    doutsK = zeros(1, Nouts);  % Do not collect a matrix just to obtain the maximum
    for k = 1:K
        ind    = all(AA == B(k, :), 2);
        Aind   = AEnd(ind, :);
        doutsK = max(doutsK, range(Aind, 1));
    end
    effect(n, :) = doutsK;
end

[Real User]

So that was 99.4% faster than my code in my test (the commented alternative was 95% faster than mine) and the results were the same.