Elementwise calculations when making a new Table column

2 views (last 30 days)
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

Accepted Answer

Voss
Voss on 15 Jun 2022
Edited: Voss on 15 Jun 2022
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

More Answers (2)

Steven Lord
Steven Lord on 15 Jun 2022
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.
  1 Comment
Corey McDowell
Corey McDowell on 15 Jun 2022
Edited: Corey McDowell on 15 Jun 2022
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.

Sign in to comment.


Peter Perkins
Peter Perkins on 16 Jun 2022
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)

Products


Release

R2022a

Community Treasure Hunt

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

Start Hunting!