Get all positions (row,column) that have a certain value in a matrix and get the values of these same positions in a new matrix

Hi,

So basically I have 2 matrices 60x60 (D and cos). Let's say matrix D stores the euclidean distance between each pair of vectors and cos stores the cosine similarity between each pair of the same vectors.

I want to plot the cosine similarity as a function of the distance between 2 vectors. Since the distance between two vectors repeats itself several times in the matrix D, I've got the unique values in the matrix. Then, I've found the all the positions (rows and columns) that had this unique values and stored them in a matrix M.

Now, I want to go to the matrix that contains the cosine similarity values and get the values at the same positions stored in matrix M. In the end, I want to average all the values so I can plot them as a function of the distance.

This is my code:

D =xlsread('C:\Users\Joana\Desktop\PIV_dist_cosSim\dist\dist_CoordVectorPIV.xlsx');

uD = unique(D(:,1));

source_dir = 'C:\Users\Joana\Desktop\PIV_dist_cosSim\cosSim';
source_files = dir(fullfile(source_dir, '*.xls'));
c={source_files.name};
s = sort_nat(c);
struct_files = cell2struct(s,'name',1);

for i=1:length(uD)

      v = uD(i);

      [x,y] = find (D == v);

      for j = 1:length(struct_files)

          cos = xlsread(fullfile(source_dir, struct_files(j).name));
          szCos = size(cos);

          M = [x,y];
          cm{i} = num2cell(M,1);

          %idx = sub2ind(szCos,M(:,1),M(:,2));
          %resCos = cos(idx);

      end
  end

When I run this, I get this error (this is why the last 2 lines are commented on the code I posted):

Error using sub2ind (line 43)
Out of range subscript.

Error in Dist_cosSim (line 33)
        idx = sub2ind(szCos,M(:,1),M(:,2));

which indeed seems to be related to the number of rows and columns to search in the size of cos (which is 60x60). Indeed, when I store all matrices M from the loop in a cell cm{i} = num2cell(M,1); I see that some matrices M have more than 60 rows and 60 columns, and this is due to the repetition of values (the same row appears for example twice and the same happens for the column, which is strange).

If anyone has an idea on how to solve this or a better way to solve the problem, it will be much appreciated!

Thanks a lot,

Joana

[Guillaume]

From your code, it looks like you have more than one cos matrix. Is it these matrix that you want to average? Or for a single cos matrix, is the values corresponding to a unique D that you want to average?

Note that reloading all the cos matrix for each unique D value is extremely inefficient. You want to read each excel file only once.

There is certainly a better way to do what you want to do (although I'm not exactly clear what it is). However, the reason you're getting an 'Out of range subscript' has nothing to do with the size of M but everything to do with its values. One of the x or y is larger than the size of cos, which can only happen if D has more rows or columns than cos. Hence your assertion that both are always 60x60 must be broken.

[Joana Leite]

Yes, I have more, it depends on the number of files in the source directory. But the loop is not my problem at the moment. For each cos matrix I want to get all the values at the same positions stored at M. For each i, I will have several rows and columns at D, because the same distance value (v) appears several times in the matrix D. So, I've got all of then into matrix M. Now I want to find all of them (rows and columns) in each cos matrix and average their values. However, my problem is that error, because I've checked and all the cos matrices have indeed 60x60 dimensions. However, at M, sometimes the same rows appears repeated and I get a M matrix with 68*2, let's say. But I really don't know how to solve this.

About the inefficiency, you are absolutely right, I should have opened the files before, not inside the other loop.

Thanks a lot

[Guillaume]

So for a single cos, you want the average of the cos values at identical D values? That's easy to do without a loop.

However, the only cause for an 'Out of range subscript' error when you do

sub2ind([nrows, ncols], x, y)

is if any of the x is greater than nrows or any of the y is greater than ncols (or x or y <= 1). Again, the number of elements in x and y is irrelevant. M could be 5000000x2, the call would still succeed as long as all M(:, 1) <= szCos(1) and all M(:, 2) <= szCos(2).

To find the problem with your current code you could add, before the sub2ind:

assert(all(max(M, [], 1) <= size(cos)), 'Size of cos is %d x %d yet wanting to access row %d or column %d', size(cos, 1), size(cos, 2), max(M(:, 1)), max(M(:, 2)))

Answer 1

[Guillaume]

D = xlsread('C:\Users\Joana\Desktop\PIV_dist_cosSim\dist\dist_CoordVectorPIV.xlsx');
[uD, ~, uid] = unique(D(:,1));   

source_dir = 'C:\Users\Joana\Desktop\PIV_dist_cosSim\cosSim';
source_files = dir(fullfile(source_dir, '*.xls'));
source_files = sort_nat({source_files.name});  %no need to convert back to structure

meancos = zeros(numel(uD), numel(source_files));

for fileidx = 1:numel(source_files)
  cos = xlsread(fullfile(source_dir, source_files{fileidx}));
  assert(isequal(size(cos), size(D)), 'Size of cos is not equal to size of D for file %s', source_files{idx});
  meancos(:, fileidx) = accumarray(uid, cos(:), [], @mean);
end

In the loop above, meancos(r, c) is the mean of all the cos values corresponding to unique value uD(r) for source_files{c}

[Joana Leite]

Thanks a lot Guillaume! I've managed to find the problem with my code with your previous suggestion and it is now working (something very very stupid and I didn't notice this at first when searching all of them, one of the files did not have 60x60 dimensions, not really sure why, since I've took this matrices as an output from a function to calculate the cosine similarity).

    D = xlsread('C:\Users\Joana\Desktop\PIV_dist_cosSim\dist\dist_CoordVectorPIV.xlsx');
    uD = unique(D(:,1));

    source_dir = 'C:\Users\Joana\Desktop\PIV_dist_cosSim\cosSim';
    source_files = dir(fullfile(source_dir, '*.xls'));
    c={source_files.name};
    s = sort_nat(c);
    struct_files = cell2struct(s,'name',1);

    for j = 1:length(struct_files)

            cos = xlsread(fullfile(source_dir, struct_files(j).name));
            szCos = size(cos);

            for i=1:length(uD)

                v = uD(i);
                [x,y] = find (D == v);
                M = [x,y];
                idx = sub2ind(szCos,M(:,1),M(:,2));
                resCos = cos(idx);  %find the positions (rows and columns) in the cell array in each cos

                a(i,:) = mean(resCos); %average of all these positions in each cos matrix

            end

            av_cos(:,j) = a; %average all cos matrices

    end

Well, I am a beginner with matlab and far from a programmer, so this looks like very childish code compared to yours actually! =P

I've tried yours now, because it is much better, but currently having this error (probably something simple I am missing):

Help Context ID: 0

Error in Dist_cosSim_alt (line 11)
    cos = xlsread(fullfile(source_dir, source_files{fileidx}));

[Guillaume]

I would need the actual text of the error, not just the line it occurs on, to diagnose the problem.

[Joana Leite]

That's all I've got actually =/ no additional text

[Guillaume]

Ah, so it's an error coming from excel. You'll probably get the same error with your code. Anyway, it's a problem with reading the file, not the code logic.

What is source_files{fileidx} when the error occurs? It may give you a clue.