Add data from one matrix to another by the values of one column

2 views (last 30 days)
I have two matrix of different sizes and they both have two columns, one with ID of the data and other with the data i want. Is there any way to add data with the same ID number from the first column in a third column without using a loop?
The matrix are both cell arrays like these:
1000 [10370;10371;10372] 1002 52
1001 [12933;12934] 2000 60
1002 10001 1000 42
2000 11320 2003 57
2001 [11347;11348]
2002 [10362;10363]
2003 [12632;12633]
And the desired result would be something like this
1000 [10370;10371;10372] 42
1001 [12933;12934]
1002 10001 52
2000 11320 60
2001 [11347;11348]
2002 [10362;10363]
2003 [12632;12633] 57

Accepted Answer

LeoAiE
LeoAiE on 3 Jul 2023
If the ID numbers in the first column of both matrices are sorted in ascending order, then you could use the intersect function with 'stable' option to match the data with the same ID.
% Let's assume the cell arrays are cell1 and cell2
cell1 = {1000, [10370;10371;10372];
1001, [12933;12934];
1002, 10001;
2000, 11320;
2001, [11347;11348];
2002, [10362;10363];
2003, [12632;12633]};
cell2 = {1002, 52;
2000, 60;
1000, 42;
2003, 57};
% Convert the first column into double for both cell arrays
id1 = cell2mat(cell1(:,1));
id2 = cell2mat(cell2(:,1));
% Find the common elements in both ID lists
[common_id, idx1, idx2] = intersect(id1, id2, 'stable');
% Create a new cell array with matched IDs and corresponding data
cell3 = cell1; % Copy cell1 to cell3
cell3(:,3) = {[]}; % Add a third column filled with empty arrays
cell3(idx1,3) = cell2(idx2,2); % Copy data from cell2 to matched rows in cell3
Note that the intersect function with 'stable' option retains the order of the input arrays. If your ID numbers are not sorted, this method will still work. The idx1 and idx2 outputs are the indices of the common elements in the respective input arrays. We use these indices to copy the corresponding data from cell2 to the correct rows in cell3.
In the resulting cell3, if an ID from cell1 does not exist in cell2, the corresponding element in the third column of cell3 will remain an empty array. You can replace these empty arrays with NaN or other placeholder values if needed.

More Answers (1)

the cyclist
the cyclist on 3 Jul 2023
C1 = {
1000 [10370;10371;10372]
1001 [12933;12934]
1002 10001
2000 11320
2001 [11347;11348]
2002 [10362;10363]
2003 [12632;12633]};
C2 = {1002 52
2000 60
1000 42
2003 57};
[tf,loc] = ismember([C2{:,1}]',[C1{:,1}]');
C3 =[C1, cell(height(C1),1)];
C3(loc,3) = C2(:,2)
C3 = 7×3 cell array
{[1000]} {3×1 double} {[ 42]} {[1001]} {2×1 double} {0×0 double} {[1002]} {[ 10001]} {[ 52]} {[2000]} {[ 11320]} {[ 60]} {[2001]} {2×1 double} {0×0 double} {[2002]} {2×1 double} {0×0 double} {[2003]} {2×1 double} {[ 57]}

Categories

Find more on Matrices and Arrays in Help Center and File Exchange

Products

Community Treasure Hunt

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

Start Hunting!