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)
Protocols = unique(Protocol);
Min = [1 1.5 2]';
Max = [5 4 10]';
limits = table(Protocols,Min, Max)
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
采纳的回答
Protocol = ["A" "A" "B" "C"]';
Operator = [1 2 2 1]';
Value1 =[4 3 3 8]';
Stats3 = table(Protocol,Operator,Value1)
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);
Min = [1 1.5 2]';
Max = [5 4 10]';
limits = table(Protocols,Min, Max)
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)
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))
更多回答（2 个）
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)
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
Let's spot check a value. Row 7 in the original table is:
Stats3(7, :)
What are the other values in the other rows in Stats3 with the same protocol?
p = Stats3{7, 'protocols'}
v = Stats3(Stats3.protocols == p, :)
What do we get if we normalize v to the appropriate range?
normalize(v.value, 'range')
So the transformed value of 5 in protocol A should be 0.3333. Let's check the transformed table.
Stats3Transformed(7, :)
That checks out.
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)
类别
在 帮助中心 和 File Exchange 中查找有关 Tables 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!
Translated by 
