How to add a column to a matrix by matching element (attached data)

Hi I have two matrices A and B with different sizes; B is smaller in size than A. I want to add another column to A by matching elements in both matrices. A small version of my data is as follows:

A=[
1  AUBURN 1  1.035108  1.035108  -64.410133
2  AUBURN 1  1.03706          1.03706          -65.261659
3  AUBURN 1  1.032929  1.032929  -66.801544
4  AUBURN 1  1.037436  1.037436  -65.328455
5  AUBURN 1  1.036071  1.036071  -64.403333
6  AUBURN 1  1.035108  1.035108  -64.410133
7  AUBURN 2  1.042328  1.042328  -64.042951
8  AUBURN 2  1.043484  1.043484  -64.622293
9  AUBURN 2  1.042239  1.042239  -64.048946
10  AUBURN 3  1.040954  1.040954  -64.565492
11  AUBURN 4  1.041164  1.041164  -64.526456

]

B=[
1  AUBURN 1  43.976116
2  AUBURN 2  44.100992
3  AUBURN 3  44.179913
4  AUBURN 4  44.143322
]

The second column in both matrices contain names that I want to be used for matching the elements in both matrices. I want the last column in B to be added to A with the corresponding matched elements. The results should be as C:

C=[
1  AUBURN 1  1.035108  1.035108  -64.410133  43.976116
2  AUBURN 1  1.03706          1.03706          -65.261659  43.976116
3  AUBURN 1  1.032929  1.032929  -66.801544  43.976116
4  AUBURN 1  1.037436  1.037436  -65.328455  43.976116
5  AUBURN 1  1.036071  1.036071  -64.403333  43.976116
6  AUBURN 1  1.035108  1.035108  -64.410133  43.976116
7  AUBURN 2  1.042328  1.042328  -64.042951  44.100992
8  AUBURN 2  1.043484  1.043484  -64.622293  44.100992
9  AUBURN 2  1.042239  1.042239  -64.048946  44.100992
10  AUBURN 3  1.040954  1.040954  -64.565492  44.179913
11  AUBURN 4  1.041164  1.041164  -64.526456  44.143322
]
Any idea?
Thanks

Answer 1

[Jason Whitfield]

As an alternative, you could use tables to accomplish this. It already looks like your data is formatted as a table. To do it, define A and B as tables with matching column names for the AUBURN data. Then, you can simply run "C = join(A, B);" Since the column names match, MATLAB will automatically match up the rows based on the value of the AUBURN data column.

[Ismaeel]

Thanks JW. "define A and B as tables with matching column names for the AUBURN data." Could you please elaborate the statement a little bit in terms of matlab code?

[Jason Whitfield]

Take a look at the excel workbooks I've attached. Notice that the columns you want to match between the tables have the same name, "C1". (I have no idea what this data is, so I'm sure you could give it a better name.) Since they have the same name, MATLAB knows to use the data in these columns to match the rows of the two tables. Now, in MATLAB, you can run the following code to get the result you want.

A = readtable('A.xlsx');
B = readtable('B.xlsx');
C = join(A, B);

Answer 2

[Adam Danz]

Assuming A and B are cell arrays, I loop through each row of B, find the corresponding rows in A, and insert the data from B.

% Add column to A
A(:,end+1) = {0}; 

% Loop through B and add to A as needed
for i = 1:size(B,1)
    rowIdx = strcmp(A(:,2), B(i,2)); 
    A(rowIdx,end) = B(i,3); 
end

[Adam Danz]

Of course this

for i = 1:size(B,1)
    rowIdx = strcmp(A(:,2), B(i,2)); 
    A(rowIdx,end) = B(i,3); 
end

Could be reduced to this

for i = 1:size(B,1)
    A(strcmp(A(:,2), B(i,2)),end) = B(i,3); 
end

[Ismaeel]

Thanks Adam. My data are stored in excel sheets (huge amount of data). As you know, matrices are only for numerical values but my matching elements are char.

[Adam Danz]

That's why I'm assuming your data are stored in a cell array (or table) once you import them to Matlab, not a matrix. This solution will work of that's the case. JW's solution may be more appropriate if your data are stored in a table.

Answer 3

[Ismaeel]

Thank you Adam, thank you Jason. Both methods work. Jason's method is relatively easier since there is no for loop.