I know this is probably a novice question, but I am quite a Matlab novice. The while loop in my script begins to run ridiculously slow as the table "nonapattern" increases in size. Is it possible to increase the speed somehow? Thank you.

counter=1;
searchsize=254;
patternsize=92378;
j=1;
i=1;

newlist = zeros(100,2);
while counter<patternsize
while i<searchsize
    i
        if isequal(pinellas{i,3},nonapattern{counter,1})
            newlist(j,1)=pinellas(i,1);
            newlist(j,2)=pinellas(i,2);
            j=j+1;
        end
   i=i+1;     
end
counter=counter+1;
i=1;
end

Pattern trajectory is the script which matches the patterns from "Data" with the list in "nonapattern". When "nonapattern" becomes large (e.g. around 90,000 x 2 element table) the script takes days to run. Thanks so much for any suggestions/help to make this run faster.

Answer 1

[jonas]

Looks like the size of the matrix is increasing by each entry. Read about preallocation and preallocation of matrices of unknown size .

Other than that, the original script loops through one cell array, nonapattern, and finds matching strings in a second cell array, data, including duplicates. Some data is then extracted from the matched rows of data. Faster code given below:

Load data

[~,nonapattern]=xlsread('nonapattern.xlsx');
[numdata,data]=xlsread('Data.xlsx');

Find pairs of identical strings in each cell arrays

[C,ia,ib] = intersect(nonapattern,data)

C =

3×1 cell array

    {'SO5 SO6 SOA SOB SOC SOD SOE SOG SOO'}
    {'SO5 SO6 SOA SOB SOD SOE SOG SOH SOO'}
    {'SO5 SO6 SOD SOE SOF SOG SOK SOM SON'}

Next, find duplicates

index=cellfun(@(x)find(ismember(data,x)==1),C,'uniformoutput',false)

index =

 1×3 cell array

    {5×1 double}    {4×1 double}    {2×1 double}

Grab corresponding numerical data from numdata, columns 1 and 2

out=cellfun(@(x)numdata(x,1:2),index,'uniformoutput',false);

out =

3×1 cell array

    {5×2 double}
    {4×2 double}
    {2×2 double}

[Mark Bodner]

Thanks very much for the answer. Unfortunately I had already tried preallocation and it didn't help. The slow down seems related to the size of the table "nonapattern", which is of fixed size (doesn't change with each iteration of the while loop). "Newlist' does increase, but only on around 10-12 iterations of the while loop. Could the slow down have something to do with having to open and examine the large table "nonapattern" in the "isequal" comparison on every iteration of the while loop. If so is there a way to improve that? Thanks for any suggestions.

[jonas]

Try this,

[~,nonapattern]=xlsread('nonapattern.xlsx');
[~,data]=xlsread('Data.xlsx');
[C,ia,ib] = intersect(nonapattern,data)

Strings which are present in both cell arrays are stored in C, with their respective indices being stored in ia and ib such that nonapattern( ia ) = data( ib ). Is that what you need? There were only 3 matches.

Anyway, there are multiple functions available to help you match cell arrays, such as intersect, setdiff and ismember. The inner loop can be replaced by ismember and I think the entire double-loop can be replaced by intersect.

[Mark Bodner]

Thanks very much. This does work ridiculously much faster, but I need to find all repetitions of the matches (that's why there were only 3 matches since it just returned the unique patterns and not all occurrences of each type). I also need to return the other information from the rows of the matches. Are there functions that might do that? Again thanks very much.

[jonas]

Now I think I understand. All arrays in nonapattern are unique but they may occur more than once in data? Add this line of code.

index=cellfun(@(x)find(ismember(data,x)==1),C,'uniformoutput',false)

It should locate all duplicates in data and store them in another cell array, index. Now you have:

C =

3×1 cell array

    {'SO5 SO6 SOA SOB SOC SOD SOE SOG SOO'}
    {'SO5 SO6 SOA SOB SOD SOE SOG SOH SOO'}
    {'SO5 SO6 SOD SOE SOF SOG SOK SOM SON'}

index =

1×3 cell array

    {5×1 double}    {4×1 double}    {2×1 double}

This means that C{1} occurs 5 times in data, C{2} occurs 4 times and C{3} occurs 2 times, with a total of 11 matches. The matched indices are stored in the arrays.

I will help you extract and structure the data if you describe exactly what you want as output.

[Mark Bodner]

Yes! That's it exactly. Thank you so much. What the final product needed is that each of the pattern matches (e.g. each SO5, SO6,....) is accompanied by the data from the other columns in its row (for example in some table or other array). So the final output will have each occurrence of found matched sequence e.g. SO5 SOD SO6 …, along with the data in its row in "nonapattern" from column 2 (e.g. 280), from column 1, and column 4.

[jonas]

Ok, that's easy, almost there.

I've updated the answer. The numerical data is now loaded alongside the strings and I've added a line of code to grab data from columns 1 and 2. You now have three cell arrays,

• C has unique strings,
• index has their respective matches in cell array |data|out1 and out2 has the corresponding data from the matched rows
• out has the numerical data from the rows given by the indices

You can either continue working with cell arrays, or you can e.g. build a table with the data.

[Mark Bodner]

I keep getting an error now "Error using xlsread (line 260) File contains unexpected record length. Try saving as Excel 98." Apparently this is common, especially with larger excel files, but none of the "fixes" seem to work. Any ideas? Thanks.

[jonas]

Sorry, didn't notice your reply until now. Never seen that error. Did you fix it? Perhaps you can load the data in batches, not all at once.

[Mark Bodner]

No, not yet. The code has no trouble with the size of the excel file before adding the extra formatting code. I tried loading the data in batches get a different problem "Error using xlsread (line 260) Unable to read XLS file C:\Users\mbodn\OneDrive\Documents\MATLAB\nonapattern.xlsx . Cannot open file." Still working on it. Thanks.

[Mark Bodner]

Solved the problem and the code works great for the data files for which you wrote it. Having a problem though with the indexing part of the code when applying it to other excel files with different dimensions. The first 3 lines work great, but the last two formatting lines have trouble when I try to adapt for files (i.e. "state.xlxs" 41351 x 26; "Pinellas.xlsx" 757107 x 17). It finds the intersections between state and Pinellas and puts them in "D" just fine. But then when I try to find the repeats, "indexP" seems fine except for the first cell which winds up as a 6315992 x 1 double. The last line of code (where I try to format everything grabbing columns 8 and 18) just fails because "Index in position 1 exceeds array bounds (must not exceed 41351)"--the size of the file "state". I guess I just don't understand how these last two formatting lines work. The code as I currently tried to adapt it is

[numdataP,patternsizeP]=xlsread('state.xlsx');

[~,dataP]=xlsread('pinellas.xlsx');

[D,ic,id] = intersect(patternsizeP,dataP)

indexP=cellfun(@(x)find(ismember(dataP,x)==1),D,'uniformoutput',false)

outP=cellfun(@(x)numdataP(x,8:18),indexP,'uniformoutput',false)

Could you shed any light on how this formatting works so that I can generalize the script. Thanks so much once again.

[jonas]

I am a bit confused because I don't understand the structure of your new data. Now you are working with 2D cell arrays, which is fine, but what are the dimensions of numdata? Feel free to upload the new data if you want me to take a look.

Anyway, so let's break the code down line by line, using my original notations.

[C,ia,ib] = intersect(nonapattern,data)

You said this works fine, but I suspect there is a problem with the input here. I would take a look at the content of C{1} to make sure it looks OK. The next line of code:

index=cellfun(@(x)find(ismember(data,x)==1),D,'uniformoutput',false)

goes over over each unique cell in D, cell by cell, and finds matches in data. the function ismember outputs a matrix with the same size as data, containing ones where you have matches and zeros otherwise. The find function then takes this matrix and outputs the linear indices of matches, i.e. the ones. It seems C{1} matches 6315992 times, which is not necessarily wrong, but makes me believe there is something sketchy going on with the content of that cell.

out=cellfun(@(x)numdata(x,1:2),index,'uniformoutput',false);

The problem is in this line of code, which only works if both C and data are single column cell arrays. The reason is that the previous line of code outputs linear indices, as opposed to subscripts.

What are linear indices? Assume you have a matrix:

A =

     0     0
     0     0
     1     1

find(A==1)

     3
     6

The linear indices basically describe the position in the 2D-array if you stack each column on top of one another to a long 1D-array.

The next line

out=cellfun(@(x)numdata(x,1:2),index,'uniformoutput',false);

breaks down because we are using linear indices to refer to rows.

This can easily be fixed. In fact, the find column can output both linear indices and subscripts if you add two more outputs:

[linear,row,col]=find()

However, I don't understand the structure of your new numdata so I cannot write the new code for you.