Select subset of data going backwards

Hi, I've got a matrix of data and need to start at a specific row within the dataset and, based on a condition, select the previous 5 rows. For example:

if column 6 > 0

a = iterate through and get the value of column 4 that corresponds to column 6 > 0 (i.e. 694)

starting at position a, get the previous 5 rows of data from column 4 where column 3 = 1

iterate through the whole file

In this case I would expect 361, 360, 349, 343, 331 to be returned (yellow).

What I really need to do is get the mean of these numbers but I need to select them first.

I hope I've explained it well. I'm just really stuck on how to go backwards in Matlab.

Thanks

Answer 1

[rocketboyjka]

Try this:

 targetIndex = find(myMatrix(:,6) > 0);
 desiredData = myMatrix(targetIndex-5:targetIndex-1,4);

If there is more than one location in your matrix where the condition is met, then targetIndex will be a vector of indices where the condition occurs and you could adapt it like this:

 targetIndex = find(myMatrix(:,6) > 0);
 for ii = 1:length(targetIndex)
     desiredData(:,ii) = myMatrix(targetIndex(ii)-5:targetIndex(ii)-1,4);
 end

Then each column will be the numbers that met your condition.

[chels19]

Thanks. The line desiredData is throwing an error though:

Subscript indices must either be real positive integers or logicals.

What I really need to do is get the mean of these numbers but I want to check first that the correct 5 are being selected.

[rocketboyjka]

Two issues: 1) I made a typo, the targetIndex = find...line should be outside of the loop. 2) Assuming you corrected that, it sounds like the issue may be that your first row meeting the criteria is before row 5. Then the previous '5' rows of data would include a negative index. You could prevent that issue like this:

 if targetIndex(ii) <= 5
      desiredData(:,ii) = myMatrix(1:targetIndex(ii)-1,4);
 else
      desiredData(:,ii) = myMatrix(targetIndex(ii)-5:targetIndex(ii)-1,4);
 end

A similar approach could be used to prevent issues if the first index in your array meets the criteria. The trouble here is that it could lead to different lengths vectors being stored to desiredData, so averaging immediately might be in your best interest.

[rocketboyjka]

I missed your part about sorting by column 3. I tested this on your data. Should work if you're still looking.

 targetIndex = find(myMatrix(:,6) > 0);
 for ii = 1:length(targetIndex)
     candiateRows = find(myMatrix(1:targetIndex(ii)-1,3) ~= 0);
     if length(candiateRows) < 5
          startIndex = 1;
          numRows = length(candiateRows);
     else
          startIndex = length(candiateRows)-4;
          numRows = 5;
     end
     desiredData(:,ii) = myMatrix(candiateRows(startIndex:end),4);
 end

[chels19]

That works perfectly, thank you.

Answer 2

[Thorsten]

i1 = find(a(:,6) > 0, 1, 'first')
i2 = find(a(1:i1, 3) == 1, 5, 'last')
res = flipud(a(i2, 4))

[chels19]

Thanks. It is returning a number but I'm having trouble looping it. I tried to combine yours with the answer below but it's not working. With the data I have, I would expect 4 subsets of data.

What I really need to do is get the mean of these numbers but I want to check first that the correct 5 are being selected.