Comparing values in a table for each row

Hello, I'm new to Matlab and i don't really know how to efficiently solve this seemingly simple problem:

I have a really big bunch of data (89257x114 table) and for each row i want to compare the element of the 4th column with the elements from all columns between 14 and 113. My goal is to find the next higher value to the element in the 4th column. The next higher value for each row should then be stored inside another column (column 13 in the following example). I've already figured out how to do this with a for-loop, but it seems like it would take multiple days to execute. Here it is.

for i=1:89257
    for z=1:100
        if (M2{i,4} < M2{i,13+z})
            M2{i,13} = M2{i,13+z}
        end
    end
end

I have already tried to use rowfun in combination with some anonymus functions i've created, but i just can't figure out a syntax that works.

I'm greatful for any kind of help!

[David Fletcher]

As a possible lane of exploration you could try the following (just try it on one row of data to start):

Extract all values from the range of columns and subtract column 4 from each:

temp=table{1,14:113} - table{1,4}

Create a logical index for everything that is zero or below and use it to turn those values into nan:

temp(temp<=0)=nan

Apply the min function with the omitnan option to find the index of the smallest value (because you subtracted col 4, the min of what remains should be the next highest value to the one in column 4). Use the index to copy the value from the original table to wherever you want

[Maximilian Hauschel]

Hello, thank you very much for that helpful instruction.

After temp(temp<=0)=nan I tried to find the min value (which is the value I am looking for) for one row and it worked perfectly fine with min(temp(1,:),[],'omitnan')

However, when i want to do the process for the whole table as follows

temp=M2{:,14:113} - M2{:,4}

temp(temp<=0)=nan

fnc = @(x) min(x,[],'omitnan')

M = rowfun(fnc,temp)

an error occurs (Check for incorrect argument data type or missing argument in call to function 'rowfun'). Well fnc is a function and temp is table format, as specified by Matlab. I dont understand why it doesn't work :(

[David Fletcher]

Ignoring the business regarding function handles (if I'm honest, they have a tendency to bonzle my brain so I avoid them unless I have to absolutely use them) if you amend the min function to act on the full matrix by adding a dimension to work along i.e

temp=M2{:,14:113} - M2{:,4}
temp(temp<=0)=nan
[val,idx]=min(temp,[],2,'omitnan')

Wouldn't that return a set of indices that you can work back to the original table by adding a suitable offset so that you can index out the required values to save in your column? Or perhaps simpler yet, just add the values from column four back onto the min return values to recreate the original value and save that into your column

[Maximilian Hauschel]

Yey, that's it. This was such a helpful lesson. I guess I focused way too much on solving it with a function handle and the rowfun function. Thank you very much, you've probably saved me houres of trial and error.