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

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

Answer 1

[LeoAiE]

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.

[Raúl García Odriozola]

It works perfectly, also great explanation thank you

Answer 2

[the cyclist]

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]}