Intersect, Compare two columns or two matrix and extract common information

56 views (last 30 days)
Hi All,
I have a matrixe with 4 columns (A to D) or 1 to 4. I want to compare columns 2 and 4 and if a match is found, put the entire corresponding rows in a separate matrix called Match. Ex ample when it comes to row 2, under coulms 2 (B) AND 4 (D), the vaule 12.1 is common to both. Hence put this entire matching row in a new matrix which will become 12, 12.1, 12.4, 12.1
The idea is to extract information common to both columns 2 and 4, for each row and after finding this common information, include with them their associated information in coulmns 1 and 3. Please see attached im
I tried using the intersect function but it doesnt work the way I want it to.
Thanks very much.

Accepted Answer

Adam Danz
Adam Danz on 17 Dec 2019
Edited: Adam Danz on 18 Dec 2019
Here's a demo you can follow. It identifies rows of a matrix 'data' where the value in column 2 exactly matches the value in column 4.
% demo, random data
data = randi(3,12,4);
% Find rows where value in col 2 exactly matches value in col 4
isMatch = data(:,2)==data(:,4);
% Put those rows into a new matrix
newMat = data(isMatch,:);
For floating decimals, 4.9999999 and 4.9999998 will not be an exact match. You can subtract the values from columns 2 and 4 and determine if the absolute value of their difference is less than some threshold you choose.
% Find rows where value in col 2 exactly matches value in col 4
isMatch = abs(data(:,2)-data(:,4)) < 0.0001;
% Put those rows into a new matrix
newMat = data(isMatch,:);
  2 Comments
Curious Mind
Curious Mind on 24 Dec 2019
Edited: Curious Mind on 24 Dec 2019
Thanks Adam for your response. What happens if I have 2 different data or matrices, A and B and I am comparing the second columns in each matrix to each other? Also will the code work if one matrix is longer than the other? Such as A being an 6*2 matrix and B being an 8*2 matrix. Thanks you!
Adam Danz
Adam Danz on 24 Dec 2019
"What happens if I have 2 different data or matrices, A and B and I am comparing the second columns in each matrix to each other?"
% For exact matches, instead of
isMatch = data(:,2)==data(:,4);
% use
isMatch = A(:,2)==B(:,2);
% For floating decimal, instead of
isMatch = abs(data(:,2)-data(:,4)) < 0.0001;
% use
isMatch = abs(A(:,2)-B(:,2)) < 0.0001;
"Also will the code work if one matrix is longer than the other?"
This approach will not work if the matrices have a different number of rows. If matrix A has 6 rows and matrix B has 8 rows, which rows should compared? One way around this is to add rows of NaN to the shorter matrix.
A = rand(6,2);
B = rand(8,2);
A = [A;nan(size(B,1)-size(A,1),size(A,2))]

Sign in to comment.

More Answers (0)

Tags

Community Treasure Hunt

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

Start Hunting!