How to do a cross-multiplication by group in a table
30 views (last 30 days)
Show older comments
Blue
on 24 Jun 2024 at 12:42
Commented: Star Strider
on 24 Jun 2024 at 16:33
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 Comments
Accepted Answer
Star Strider
on 24 Jun 2024 at 12:50
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)
.
8 Comments
Star Strider
on 24 Jun 2024 at 16:33
As always, my pleasure!
It took a bit of time and experimentation to devise the most optimal approach.
More Answers (0)
See Also
Categories
Find more on Tables in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!