Fastest way to cross reference two tables

I have data in table form read in from two different files: "Grid points" and "Forces".

Each row of "Grid points" contains 4 column entries, from left to right: Grid ID, x-coordinate, y-coordinate and z-coordinate. For example:

GRIDID X Y Z

2 0.1 5.0 0.3

5 0.8 2.5 0.7

1 0.01 3.0 0.4

This file contains several thousand rows, each with a unique GRIDID and corresponding x,y,z coordinates.

Each row of "Forces" contains 4 column entries, from left to right: Grid ID, force x-component, force y-component and force z-component. For example:

GRIDID FX FY FZ

7 120 10 11

2 130 9 6

12 250 5 5

There are also several thousand rows in this file, each with unique GRIDIDs and force components - however there are less than the "Grid points" file i.e. the GRIDIDs in the "Forces" file is a subset of those in the "Grid points" file. The x,y,z force components at a GRIDID in the "Forces" file are the force components at the corresponding x,y,z coordinates for the same GRIDID in the "Grid points" file. The GRIDIDs in both files are in random orders.

I am now trying to construct a new matrix of data in MATLAB, which will contain all of the Force info as well as the corresponding x,y,z coordinates for each GRIDID in the "Forces" file i.e. this file should have the same amount of rows in the "Forces" file and 7 columns: GRIDID, x,y,z,Fx,Fy,Fz.

Currently I am using ismember to check at what index the "Forces" GRIDID matches the "Grid points" ID. I am then adding the x,y,z coordinates with this row index from the "Grid points" file to the row with the same GRIDID in the "Forces" file. A sample code of this strategy with some sample data is shown below:

forces_GRIDID = [2 5 3 9 8 10];
forces_X = [120 100 90 180 190 100]; 
forces_Y = [14 13 9 18 10 1]; 
forces_Z = [0 2 1 4 3 0]; 

grids_GRIDID = [2 1 4 3 6 5 8 7 10 9];
grids_X = [0.0 1.5 2.1 0.1 0.5 1.8 2.0 1.5 8.4 3.3]; 
grids_Y = [0.0 1.4 2.4 0.1 0.6 2.8 0.0 7.2 4.1 8.6]; 
grids_Z = [0.0 1.7 1.2 1.0 0.7 9.1 3.1 8.5 2.5 1.3];  

for i = 1:length(forces_GRIDID)
    if ismember(forces_GRIDID(i), grids_GRIDID(:))
        [~,idx] = ismember(forces_GRIDID(i), grids_GRIDID(:));
        new_matrix(i,1) = grids_GRIDID(idx);
        new_matrix(i,2) = grids_X(idx);
        new_matrix(i,3) = grids_Y(idx);
        new_matrix(i,4) = grids_Z(idx);
        new_matrix(i,5) = forces_X(i);
        new_matrix(i,6) = forces_Y(i);
        new_matrix(i,7) = forces_Z(i);
    end
end

For files with several thousand rows. This is taking several minutes. Is there a faster way to do what I am trying to do?

Thanks.

Answer 1

[David Hill]

It would help if you attached your data.

gp=sortrows(table2array(grid_points));
f=sortrows(table2array(forces));
idx=ismember(gp(:,1),f(:,1));
new_matrix=[gp(idx,:),f];

Answer 2

[Cris LaPierre]

This sounds like a good usecase for the Join Tables functionality (see corresponding section on this page). I find it easiest to do this interactively in a live scrip using the Join Tables live task. That way I can inspect the output and quickly adjust the settings accordingly.

Once you have the settings you want, you can view the corresponding code, convert the live task to code, or just use it as is.