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

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)

Answer 1

[Star Strider]

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>

.

[Blue]

Hi Star Strider. Sorry I wasnt clear. Here's a more concrete example of what I want to do:

% First NaN
(t.c(2) * t.d(1)) / t.c(1)

% Second NaN
(t.c(6) * t.d(5)) / t.c(5)

% Third NaN
(t.c(8) * t.d(7)) / t.c(7)

% ect etc

This need to be applied to fill all the NaNs in a table for all unique combinations of columns a and b

[Star Strider]

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.

.

[Blue]

Looking good. One last wrinkle: what if I have NaNs I dont want to replace ? In the scenario above, doing these operations only make sense where I have duplicates in column b. What if I have this table here ?

% 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)

In this scenario it only make sense to replace the first, third and fourth NaN since its where I have duplicates in column b.

[Star Strider]

More information is about duplicates in ‘b’. That was not previously stated as a condition, so I didn’t catch it.

It doesn’t look like ‘b’ changed from the previous version. What are the new conditions, and what do you want to do? What result do you want? (I’m now lost.)

.

[Blue]

For each unique a values, when there are two rows of b with the same value, then do the operations above to fill the NaNs

% 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)

% 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)

[Star Strider]

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]

Thats pretty great ! Thank you !

[Star Strider]

As always, my pleasure!

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