How do I select rows with matching values for certain columns from two differently sized arrays?

I have two arrays (M1 and M2) of common length but different height, where the first three columns contain indices I would eventually like to match between the arrays. I would like to make a new array with only the rows where the values of the first three columns of M2 equal the values of the first three columns for some row in M1. Here is my current strategy:

M2_trimmed = M2(any((M2(:,1:3)==(M1(:,1:3))),2),:);

However, I get the error "Arrays have incompatible sizes for this operation", presumably because I am unable to use the "==" operator between arrays of different size. Any suggestions on how else I might accomplish this?

Answer 1

[Image Analyst]

Try this:

% Sample data
M1 = [1,2,3,4
    4,5,6,7]
M2 = [1,2,3,4;5,3,2,7;5,6,7,9]
[rows1, columns1] = size(M1)
[rows2, columns2] = size(M2)
% Find the number of rows in the tallest matrix.
maxRows = max(rows1, rows2)
% Expand arrays so both are the same height.
if rows1 < maxRows
    M1(maxRows, end) = 0
end
if rows1 < maxRows
    M2(maxRows, end) = 0
end
% Find rows where columns 1-3 of M1 match columns 1-3 of M2.
matchingRows = all(M2(:, 1 : 3) == M1(:, 1 : 3), 2)
M2_trimmed = M2(matchingRows,:)

[Niko Vlahakis]

I tried this approach:

M1 = table2array(readtable('2021_11_02_mov04b_fcf.csv'));
M2 = table2array(readtable('2021_10_28_mov03b_fcf.csv'));

[rows1,columns1]=size(M1);
[rows2,columns2]=size(M2);

maxRows = max(rows1, rows2);

if rows1 < maxRows
    M1(maxRows, end) = 0;
end

if rows2 < maxRows
    M2(maxRows, end) = 0;
end

matchingRows2 = all(M2(:, 1 : 3) == M1(:, 1 : 3), 2);
M2_trimmed = M2(matchingRows2,:);

However, the resulting M2_trimmed is a 0x7 array, suggesting no matches were found which is not true of the data in my two arrays. I also tried switching all() to any() when defining "matchingRows" but this doesn't correctly select rows with the first 3 indices correctly matching either. I've attached my .csv files I've been using to this message.

[Image Analyst]

Tell me which row of M1 and M2 has the first 3 columns with the same value. I'm not seeing any. Like is it row 123 or row 349 or what?

[Niko Vlahakis]

Ah I see the problem, the rows are not necessarily in the same order between arrays. For example, row 4 of M1 has the same first three columns as row 1 of M2, and this would be an example of a row I would like to conserve in both trimmed arrays. My goal is to generate an M2_trimmed that preserves all rows of M2 where the first three column values exist in the same sequence as some row in M1.

In other words, I want to create two arrays, M1_trimmed and M2_trimmed. These will only retain entries where the first three columns were conserved across the original arrays.

Apologies, I should've been more clear, thanks!

[Image Analyst]

So let's clarify. Do you want to keep the row in M2 is those first 3 column numbers occur anywhere in M1 (i.e. not necessarily the very same row number, but anywhere at all)? If so use ismember().

And conversely, you want to keep the row in M1 is those first 3 column numbers occur anywhere in M2 (i.e. not necessarily the very same row number, but anywhere at all)? If so use ismember().

[Niko Vlahakis]

Yes, that's correct. Here's what I tried:

matchingRows2 = ismember(M2(:, 1 : 3),M1(:, 1 : 3));
M2_trimmed = M2(matchingRows2,:);

I get the following error. How would I implement ismember() differently to solve this?

"The logical indices in position 1 contain a true value outside of the array bounds.

Error in fobs_vs_fobs (line 18)

M2_trimmed = M2(matchingRows2,:);"