Elementwise calculations when making a new Table column

I am trying to add a new column to an existing table. The main issue arises in that I am dividing by a match case value from another table.

Each row of value1 has a protocol associated with it. in the other tables each protocol has a minimum and maximum associated with it. I am trying to find (value1-min)/(max-min) using the appropriate max and min for the protocol associated with that particular value1. the protocols are not unique to the value and each protocol is used more than once in Stats3. here is an example code for a small dataset utilizing a for loop. I am looking for a solution that does not use a for loop as my table is very large and I need to do this for many values

Protocol = ["A" "A" "B" "C"]';
Operator = [1 2 2 1]';
Value1 =[4 3 3 8]';
Stats3 = table(Protocol,Operator,Value1)

Stats3 = 4×3 table
    Protocol    Operator    Value1
    ________    ________    ______

      "A"          1          4   
      "A"          2          3   
      "B"          2          3   
      "C"          1          8   

Protocols = unique(Protocol);
Min = [1 1.5 2]';
Max = [5 4 10]';

limits = table(Protocols,Min, Max)

limits = 3×3 table
    Protocols    Min    Max
    _________    ___    ___

       "A"         1     5 
       "B"       1.5     4 
       "C"         2    10 

A=[];
for n = 1:length(Stats3.Protocol)
A = [A' (Stats3.Value1(n) - limits.Min(ismember(limits.Protocols(:),Stats3.Protocol(n))))...
    ./(limits.Max(ismember(limits.Protocols(:),Stats3.Protocol(n)))-limits.Min(ismember(limits.Protocols(:),Stats3.Protocol(n))))]';
end
Stats3.Value1norm = A

Stats3 = 4×4 table
    Protocol    Operator    Value1    Value1norm
    ________    ________    ______    __________

      "A"          1          4          0.75   
      "A"          2          3           0.5   
      "B"          2          3           0.6   
      "C"          1          8          0.75   

Answer 1

[Voss]

Protocol = ["A" "A" "B" "C"]';
Operator = [1 2 2 1]';
Value1 =[4 3 3 8]';
Stats3 = table(Protocol,Operator,Value1)

Stats3 = 4×3 table
    Protocol    Operator    Value1
    ________    ________    ______

      "A"          1          4   
      "A"          2          3   
      "B"          2          3   
      "C"          1          8   

If limits is built from Stats3 using unique(Protocol), as it is in this example, you can use the third output from unique as the indices into rows of limits:

[Protocols,~,idx] = unique(Protocol);
disp(idx);

     1
     1
     2
     3

Min = [1 1.5 2]';
Max = [5 4 10]';
limits = table(Protocols,Min, Max)

limits = 3×3 table
    Protocols    Min    Max
    _________    ___    ___

       "A"         1     5 
       "B"       1.5     4 
       "C"         2    10 

On the other hand, if limits.Protocols is not necessarily unique(Stats3.Protocol), e.g., because limits comes from somewhere else and is not built from unique(Stats3.Protocol) as it is in this example, you can use ismember to get the correct indices into rows of limits in general:

[~,idx] = ismember(Stats3.Protocol,limits.Protocols)

idx = 4×1

1 1 2 3

Then, in either case, use the indices idx to do the calculation (this assumes that every Stats3.Protocol appears in limits.Protocols somewhere):

Stats3.Value1norm = (Stats3.Value1-limits.Min(idx))./(limits.Max(idx)-limits.Min(idx))

Stats3 = 4×4 table
    Protocol    Operator    Value1    Value1norm
    ________    ________    ______    __________

      "A"          1          4          0.75   
      "A"          2          3           0.5   
      "B"          2          3           0.6   
      "C"          1          8          0.75   

[Corey McDowell]

Thank you this is exactly what I was looking for!

[Voss]

You're welcome!

Answer 2

[Steven Lord]

It's not clear to me where the data in your limits variable is coming from, but if you want to use the minimum and maximum values computed from the data in Stats3 the grouptransform and normalize functions may be of use to you. Let's make some sample data.

rng default % For reproducibility
possibleProtocols = ["A"; "B"; "C"];
n = numel(possibleProtocols);
protocols = possibleProtocols(randi(n, 20, 1));
value = randi([-10 10], 20, 1);
Stats3 = table(protocols, value)

Stats3 = 20×2 table
    protocols    value
    _________    _____

       "C"          3 
       "C"        -10 
       "A"          7 
       "C"          9 
       "B"          4 
       "A"          5 
       "A"          5 
       "B"         -2 
       "C"          3 
       "C"         -7 
       "A"          4 
       "C"        -10 
       "C"         -5 
       "B"        -10 
       "C"         -8 
       "A"          7 

Now use protocols as the grouping variable and value as the data variable in a grouptransform call, applying the normalize transformation.

Stats3Transformed = grouptransform(Stats3, ... % Transform the Stats3 table
    'protocols', ... % using its variable protocols as the grouping variable
    @(x) normalize(x, 'range'), ... % and normalizing using the 'range' method
    'value') % applied to the data variable value

Stats3Transformed = 20×2 table
    protocols      value   
    _________    __________

       "C"          0.68421
       "C"                0
       "A"                1
       "C"                1
       "B"                1
       "A"          0.33333
       "A"          0.33333
       "B"          0.57143
       "C"          0.68421
       "C"          0.15789
       "A"                0
       "C"                0
       "C"          0.26316
       "B"       1.1102e-16
       "C"          0.10526
       "A"                1

Let's spot check a value. Row 7 in the original table is:

Stats3(7, :)

ans = 1×2 table
    protocols    value
    _________    _____

       "A"         5  

What are the other values in the other rows in Stats3 with the same protocol?

p = Stats3{7, 'protocols'}

p = "A"

v = Stats3(Stats3.protocols == p, :)

v = 5×2 table
    protocols    value
    _________    _____

       "A"         7  
       "A"         5  
       "A"         5  
       "A"         4  
       "A"         7  

What do we get if we normalize v to the appropriate range?

normalize(v.value, 'range')

ans = 5×1

1.0000 0.3333 0.3333 0 1.0000

So the transformed value of 5 in protocol A should be 0.3333. Let's check the transformed table.

Stats3Transformed(7, :)

ans = 1×2 table
    protocols     value 
    _________    _______

       "A"       0.33333

That checks out.

[Corey McDowell]

Thank you I did not know of this method and will keep it in mind for the future, but in my specific problem the limits come from outside the data set. They are acceptable ranges set to maintain image quality but stay under dose limits and I am looking at how technologists may differ within this range for many different protocols. Voss's idea of using the idx of the ismember set for my calculations did solve my problem.

Answer 3

[Peter Perkins]

Will noone think of the joins?

"I am trying to find (value1-min)/(max-min) using the appropriate max and min for the protocol associated with that particular value1." This is a join. Temporarily add new Min and Max variables to Stats3 by joining using limits, with Protocol as the key. Then it's just

Stats3.Value1norm = (Stats3.Value1-Stats3.Min)./(Stats3.Max-Stats3.Min)