How to do a cross-multiplication by group in a table

Question

Blue 2024-6-24

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/2131416-how-to-do-a-cross-multiplication-by-group-in-a-table

评论： Star Strider 2024-6-24

Hi. I am wondering how to do a cross-multiplication (rule of three) by group in a table. More specifically I have an initial table in which I want to fill the NaNs by doing a cross-multiplication between c and d. For example the first NaN is (10*40) / 1 == 400. This need to be applied for unique combinations of a and b.

% Initial table
a = [1, 1, 1, 1, 1, 1, 2, 2]';
b = [45,45,12, 17, 56, 56, 45, 45]';
c = [1, 10, 3, 9, 2, 10, 1, 10]';
d = [40, NaN, 1, 9, 20, NaN, 40, NaN]';
t = table(a, b, c, d)
% Desired end result
e = [1, 1, 1, 1, 1, 1, 2, 2]';
f = [45,45,12, 17, 56, 56, 45, 45]';
g = [1, 10, 3, 9, 2, 10, 1, 10]';
h = [40, 400, 1, 9, 20, 100, 40, 400]';
result = table(e, f, g, h)

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

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

Answer 1

Star Strider 2024-6-24

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/2131416-how-to-do-a-cross-multiplication-by-group-in-a-table#answer_1476331

在 MATLAB Online 中打开

I am not certain what you want to do, however the fillmissing function can replace the NaN values in ‘d’ with the scalar 400.

Try this —

d = [40, NaN, 1, 9, 20, NaN, 40, NaN]';
h = fillmissing(d, 'Constant',400)
h = 8x1
    40
   400
     1
     9
    20
   400
    40
   400
<mw-icon class=""></mw-icon>
<mw-icon class=""></mw-icon>

.

8 个评论
显示 6更早的评论隐藏 6更早的评论

Star Strider 2024-6-24

在 MATLAB Online 中打开

Thank you for the clarification.

Try this —

% Initial table
a = [1, 1, 1, 1, 1, 1, 2, 2]';
b = [45,45,12, 17, 56, 56, 45, 45]';
c = [1, 10, 3, 9, 2, 10, 1, 10]';
d = [40, NaN, 1, 9, 20, NaN, 40, NaN]';
t = table(a, b, c, d)
t = 8x4 table
    a    b     c      d 
    _    __    __    ___

    1    45     1     40
    1    45    10    NaN
    1    12     3      1
    1    17     9      9
    1    56     2     20
    1    56    10    NaN
    2    45     1     40
    2    45    10    NaN
idx = find(ismissing(d));
h = d;
h(idx) = t.c(idx) .* t.d(idx-1) ./ t.c(idx-1)           % Use Element-Wise (Array) Operations For A One-Line Solution
h = 8x1
    40
   400
     1
     9
    20
   100
    40
   400
<mw-icon class=""></mw-icon>
<mw-icon class=""></mw-icon>
e = [1, 1, 1, 1, 1, 1, 2, 2]';
f = [45,45,12, 17, 56, 56, 45, 45]';
g = [1, 10, 3, 9, 2, 10, 1, 10]';
% h = [40, 400, 1, 9, 20, 100, 40, 400]';
result = table(e, f, g, h)
result = 8x4 table
    e    f     g      h 
    _    __    __    ___

    1    45     1     40
    1    45    10    400
    1    12     3      1
    1    17     9      9
    1    56     2     20
    1    56    10    100
    2    45     1     40
    2    45    10    400

If you are not familiar with element-wise operations, see tthe documentation section on Array vs. Matrix Operations.

.

Star Strider 2024-6-24

在 MATLAB Online 中打开

This is getting a bit more complicated.

In this instance, ther are four rows (1,2,7,8) of ‘b’ with the same values (45). And ‘b’ hasn’t changed from the previous examples, while ‘d’ has a NaN where a 9 was previously.

This may not be completely robust. (It may not work correctly in other situations because of the diff call and the necessary offset to make the ‘db’ result the same length as the other vectors, so I added an if block to possibly correct for that.)

However it works here, witth the new ‘d’ as well as the previous ‘d’ —

% Initial table
a = [1, 1, 1, 1, 1, 1, 2, 2]';
b = [45,45,12, 17, 56, 56, 45, 45]';
c = [1, 10, 3, 9, 2, 10, 1, 10]';
d = [40, NaN, 1, NaN, 20, NaN, 40, NaN]';
t = table(a, b, c, d)
t = 8x4 table
    a    b     c      d 
    _    __    __    ___

    1    45     1     40
    1    45    10    NaN
    1    12     3      1
    1    17     9    NaN
    1    56     2     20
    1    56    10    NaN
    2    45     1     40
    2    45    10    NaN
db = diff(b);                                           % Element-Wise Differences, Offset By 1 In The Beginning, To Detect Consecutive Duplicates
[Ub,ia] = unique(b,'stable');
if ia(1) == 1
    db = [1; db];
else
    db = [db; 1];
end
% db
idx = find(db == 0);                                    % Locations Of Second Duplicate
h = d;
h(idx) = t.c(idx) .* t.d(idx-1) ./ t.c(idx-1);          % Use Element-Wise (Array) Operations For A One-Line Solution
e = [1, 1, 1, 1, 1, 1, 2, 2]';
f = [45,45,12, 17, 56, 56, 45, 45]';
g = [1, 10, 3, 9, 2, 10, 1, 10]';
result = table(e, f, g, h)
result = 8x4 table
    e    f     g      h 
    _    __    __    ___

    1    45     1     40
    1    45    10    400
    1    12     3      1
    1    17     9    NaN
    1    56     2     20
    1    56    10    100
    2    45     1     40
    2    45    10    400
% % End result
% e = [1, 1, 1, 1, 1, 1, 2, 2]';
% f = [45,45,12, 17, 56, 56, 45, 45]';
% g = [1, 10, 3, 9, 2, 10, 1, 10]';
% h = [40, 400, 1, NaN, 20, 100, 40, 400]';
% result = table(e, f, g, h)
% ===== TEST MY NEW CODE WITH THE EARLIER VERSION OF THE VARIABLES —  
% Initial table
a = [1, 1, 1, 1, 1, 1, 2, 2]';
b = [45,45,12, 17, 56, 56, 45, 45]';
c = [1, 10, 3, 9, 2, 10, 1, 10]';
d = [40, NaN, 1, 9, 20, NaN, 40, NaN]';
t = table(a, b, c, d)
t = 8x4 table
    a    b     c      d 
    _    __    __    ___

    1    45     1     40
    1    45    10    NaN
    1    12     3      1
    1    17     9      9
    1    56     2     20
    1    56    10    NaN
    2    45     1     40
    2    45    10    NaN
db = diff(b);                                           % Element-Wise Differences, Offset By 1 In The Beginning, To Detect Consecutive Duplicates
[Ub,ia] = unique(b,'stable');
if ia(1) == 1
    db = [1; db];
else
    db = [db; 1];
end
% db
idx = find(db == 0);                                    % Locations Of Second Duplicate
h = d;
h(idx) = t.c(idx) .* t.d(idx-1) ./ t.c(idx-1);          % Use Element-Wise (Array) Operations For A One-Line Solution
e = [1, 1, 1, 1, 1, 1, 2, 2]';
f = [45,45,12, 17, 56, 56, 45, 45]';
g = [1, 10, 3, 9, 2, 10, 1, 10]';
result = table(e, f, g, h)
result = 8x4 table
    e    f     g      h 
    _    __    __    ___

    1    45     1     40
    1    45    10    400
    1    12     3      1
    1    17     9      9
    1    56     2     20
    1    56    10    100
    2    45     1     40
    2    45    10    400

.

My code seems to be robust enough to work with the earlier version as well.

.

Blue 2024-6-24

Thats pretty great ! Thank you !

Star Strider 2024-6-24

As always, my pleasure!

It took a bit of time and experimentation to devise the most optimal approach.

请先登录，再进行评论。

How to do a cross-multiplication by group in a table

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

采纳的回答

8 个评论
显示 6更早的评论隐藏 6更早的评论

更多回答（0 个）

另请参阅

类别

标签

产品

版本

Community Treasure Hunt

How to do a cross-multiplication by group in a table

0 个评论 显示 -2更早的评论隐藏 -2更早的评论

采纳的回答

8 个评论 显示 6更早的评论隐藏 6更早的评论

更多回答（0 个）

另请参阅

类别

标签

产品

版本

Community Treasure Hunt

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

8 个评论
显示 6更早的评论隐藏 6更早的评论