How to take 1 row on table 1 and compute it against every row of table 2 and do this for each row in table 1

3 views (last 30 days)
Hello all, I have table 1 with 30 rows, and I have table 2 with 50 rows. Each table contains an X, Y, and Z coordinate point. I need to take each row individually of table 1 and find the absolute difference from every row of table 2 between the coordinates and have it return the smallest value for that specific row in table 1.
Ideally i would like the code to return the ID number of the smallest value that was found. I will attempt to explain mathmatically below what I need:
For the first row in table 1, called ID:1 values could be [ 100, 200, 300] and the table 2 has 4 rows as such: ID:1 [120, 235, 480], ID:2 [150, 207, 380], ID:3 [109, 210, 391], ID:4 [108, 216, 310].
absolute(100-120)+absolute(200-235)+absolute(300-480)= 235
absolute(100-150)+absolute(200-207)+absolute(300-380)= 137
absolute(100-109)+absolute(200-210)+absolute(300-391)= 110
absolute(100-108)+absolute(200-216)+absolute(300-310)= 34
So the code would recognize that 34 is the smallest value and corelate it to ID:4 on table 2 and would return the output to be ID:4
The above example is for only 1 row in table 1 imagine having to do it 30 times like the attached file has and having to do it 50 times for each of the 30 rows.
Attached is the excel file where I included random numbers for each table.
I am really hoping this makes sense and someone can help me. My experience is low and this is for a work project that I was thrown into. Please help. Thanks

Accepted Answer

Joel Lynch
Joel Lynch on 9 Jun 2021
Edited: Joel Lynch on 9 Jun 2021
Assuming the data is read into two variables, d1(1:N1,1:3) and d2(1:N2,1:3) by
d1 = xlsread('Matlab Help.xlsx','Random','B2:D31'); % left Set
d2 = xlsread('Matlab Help.xlsx','Random','H2:J51'); % right set
Then the simplest and fastest solution I can think of is this:
% Invert d1 to 3xN1 and copy along the third dimension
% so that d1b is size 3xN1xN2, where N1/N2 are number of rows
% in d1/d2 respectively.
d1b = repmat(d1',1,1,size(d2,1));
% Reshape d2 to be 3x1xN2
d2b = reshape(d2,[3,1,size(d2,1)]);
% Copy d2 along second dimension N1 times, such that d2b is
% also 3xN1xN2, matching size of d1b
d2b = repmat( d2b, 1,size(d1,1),1);
% Compute the 2nd norm (2, second argument of vecnorm)
% of the difference in coordinates (d1b-d2b, first argument of vecnorm)
% along the first dimension of the 3xN1xN2 matrix (1, third argument of vecnorm)
% such that vecnorm returns 1xN1xN2 matrix, the distance between all combinations of d1
% and d2. Then take the minimum along the third dimension (N2) and
% return the value and index
[min_distance, jmin_index] = min( vecnorm(d1b-d2b,2,1) , [],3);
This might be hard to follow. You could also do something like this, but it will be slower
% Predefine as Not-A-Number
min_distance = NaN(size(d1,1),1);
jmin_index = NaN(size(d1,1),1);
% Loop each value in d1
for i=1:size(d1,1)
% Copy d1 so that it has the same size as d2, but with
% each row being the d1 coordinate
d1b = repmat( d1(i,:), size(d2,1),1 );
% Get the minimum of distance of the vector of distances
[min_distance(i), jmin_index(i)] = min( vecnorm(d1b-d2,2,2) );
end
You could even unroll this even more if this is still confusing:
% Loop each row in d1
for i=1:size(d1,1)
% Get the local coordinate
d1_local = d1(i,:);
% Default min distance to a high value, index to NaN
min_distance(i) = 1e9;
jmin_index(i) = NaN;
% Loop each row in d2
for j=1:size(d2,1)
% Get distance
distance = norm( d1(i,:)-d2(j,:) ,2);
% Check if smaller
if (distance< min_distance(i))
min_distance(i) = distance;
jmin_index(i) = j;
end
end
end
But this is a very inelegant solution compared to the first, and it will be hundreds of times slower for very large datasets.

More Answers (0)

Categories

Find more on Data Type Conversion 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!