How can I speed up my script? I'm using loops and contains function and I have to screen 14K X 167K variables

Hello, I'm doing text mining in attempt to organize my data. I have a table with more then 200K rows and 12 columns and I want extract some information from one of columns. Indeed, I'm looking for names that match with my reference table (approx. 14K names). For that, I'm using contains function. For make this search, I'm using two loops. First to lock one of 14K names and second for look for this name in the 200K rows. This takes a very long time. Could help to speed up my script? Thanks

Here I show you the code:

if true
  % code
1st loop (reference name table)
for k=2:14045;

    clear test
    clear Genr

    test=DNP(k,10);
    virgula=',';
    Space= ' ';

    Genr=Space+test+Space;

 Second loop (my raw table with more than 200K rows)
    for i=15001:16000;
        clear Presence
        clear A
        clear B
        clear C
        BiolSource=DNP(i,3);
        Presence=contains(BiolSource, Genr, 'IgnoreCase',true);
            if Presence ==1;
                A=DNP(i,13);
                B=DNP(k,11);
                    DNP(i,13)=A+virgula+Space+test+Space+B;
                    C=DNP(i,13);
                     DNP(i,13)=erase(C,"0, ");
            end
    end

end

[Guillaume]

For a start, you can get rid of all the clear xxxx which are absolutely pointless. Similarly, you can get rid of the ==1 which is also pointless.

Constants can also be taken out of the loops. There is no point assigning 14045 times ',' to virgula.

It is most likely that at least one of the loop, if not both, is not needed. A better explanation of what you want to do would be needed. Some example data would also help.

[Rostislav Teryaev]

Try to launch script by pressing "Run And Time" button and show the screenshot with the list with Total Time in descending order (Press on Total time).

[Alan Cesar Pilon Miro]

Hello, Follows my table example Matlab ASk2 represents my reference table and contains 14K rows (plant genera and families)

Matlab ASk3 represents my raw table containing 100 rows (in my original table i have about 200K rows) My interest is in column denominated text. My idea is extract some information from that (plant genera). This column describes the plant which this substance (column Name) were isolated. I want extract only the genera names present in this column and added in the first empty column (column 9). For that, I thought to use matching to extract the genera names. So, the MATLAB ASK2 represent all plant genera. So, since that I found I match between a name (text column) with one of name present Matlab AsK2 i put the name in the first empty column.

Thanks

[Guillaume]

So, to be clear, you want to identify in column C of Ask3 which term is the genera. All possible genera are stored in column A of Ask2?

Assumption: there is always one and only one genera in column C.

[Alan Cesar Pilon Miro]

Yes, I want to find one of genera present in A ASk2 in Column C. Can there is more than one genus by row of column C

Answer 1

[Guillaume]

reference = readtable('MATLAB ASk2.xls', 'ReadVariableNames', false, 'Range', 'A:B');
raw = readtable('MATLAB ASk3.xls');

genera = lower(reference.Var1);  %convert everything to lower case for easier comparison

matched_genera = rowfun(@(t) {genera(ismember(genera, strsplit(lower(t))))}, raw, 'InputVariables', 'Text', 'ExtractCellContent', true, 'OutputVariableNames', 'matched');

Each row of matched_genera is a cell array contain 0, 1 or more of the genera found in 'Text' (case insensitive). You can concatenate that with the original table if you wish:

newraw = [raw, matched_genera]

[Alan Cesar Pilon Miro]

Could you include column 2 (family) from the reference table as well in the newraw?

[Guillaume]

The easiest way to do that is to create a separate m file for the rowfun function:

In its own match_raw.m file:

function [matched_genera, family] = match_raw(raw_row, genera_lower, family)
     ismatch = ismember(genera_lower, strsplit(lower(raw_row)));
     matched_genera = {genera_lower(ismatch)};
     family = {family(ismatch)};
end

The rowfun call then becomes:

matches = rowfun(@(t) match_raw(t, genera, reference.Var2), raw, 'InputVariables', 'Text', 'ExtractCellContent', true, 'OutputVariableNames', {'matched_genera', 'family'})