Fastest way to cross reference two tables

3 次查看（过去 30 天）
deathtime
deathtime 2022-8-18
回答： David Hill 2022-8-18
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.

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

采纳的回答

David Hill
David Hill 2022-8-18
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];

更多回答（1 个）

Cris LaPierre
Cris LaPierre 2022-8-18
编辑：Cris LaPierre 2022-8-18
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.

类别

在 Help Center 和 File Exchange 中查找有关 Cell Arrays 的更多信息

标签

产品


版本

R2020a

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by