Elementwise calculations when making a new Table column

2 次查看（过去 30 天）
Corey McDowell
Corey McDowell 2022-6-15
回答： Peter Perkins 2022-6-16
Ran in:
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

请先登录，再回答此问题。

采纳的回答

Voss
Voss 2022-6-15
编辑：Voss 2022-6-15
Ran in:
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

更多回答（2 个）

Steven Lord
Steven Lord 2022-6-15
Ran in:
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 个评论
Corey McDowell
Corey McDowell 2022-6-15
编辑：Corey McDowell 2022-6-15
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.

请先登录，再进行评论。

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

类别

在 Help Center 和 File Exchange 中查找有关 Matrix Indexing 的更多信息

标签

产品


版本

R2022a

Community Treasure Hunt

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

Start Hunting!

Translated by