find the closest higher date

A=[1000  716606
1000  716971
1000  717336
1000  717702
1000  718067
1000  718432
1000  718797
1000  719163
1000  719528
1000  719893
1000  720258
1000  720624
1000  720989
1000  721354
1000  721719
1000  722085
1000  722450
1005  720928
1005  721293
1005  721658
1005  722024
1005  722389
1005  722754
1005  723119
1005  723485
1005  723850
1006  721170
1006  721535];

where the first column is a group unique identifier and the second column is for dates.

I have matrix B with also unique identifiers and dates.

B=[1000  721450
1005  720928
1006  721335];

how can i find for each row of B the respective row in A with the closest higher date? (if there isn't any then nan should be assigned). for example, the closest date for the 1st row in B is 721719 for id 1000.

any help is greatly appreciated

Answer 1

[Walter Roberson]

First = @(V) V(1);
FirstOrNan = @(V) First([V;nan]);
result = arrayfun(@(rowidx) FirstOrNan( A(A(:,1) == B(rowidx,1) & A(:,2) >= B(rowidx,2), 2)), (1:size(B,1)).' );

There might be a better way.

[Walter Roberson]

Note: this code assumed that A was sorted. If not, then

FirstOrNan = @(V) min([V;nan]);
result = arrayfun(@(rowidx) FirstOrNan( A(A(:,1) == B(rowidx,1) & A(:,2) >= B(rowidx,2), 2)), (1:size(B,1)).' );

[Image Analyst]

I don't believe Walter's code gives "the respective row in A with the closest higher date?" For example his code, for the second one in B, 720928, finds 720928 (which is the same, not higher), and that occurs at row 18, later than my code which finds a higher value of 720989 at row 13. My code finds a higher value, and finds it in a row before Walter's code.

[Walter Roberson]

"closest higher date" is minimum difference in date, I would say.

[Image Analyst]

Danielle, it may not be a problem anymore, but if it is, then define more precisely:

closest to what? The signal, or the date?

And higher than what? The signal, or the date?

[pruth]

can you tell us how to find coresponding value from first column ??

Answer 2

[Image Analyst]

For a simple brute force check, try this:

clc;
A=[1000  716606
1000  716971
1000  717336
1000  717702
1000  718067
1000  718432
1000  718797
1000  719163
1000  719528
1000  719893
1000  720258
1000  720624
1000  720989
1000  721354
1000  721719
1000  722085
1000  722450
1005  720928
1005  721293
1005  721658
1005  722024
1005  722389
1005  722754
1005  723119
1005  723485
1005  723850
1006  721170
1006  721535];

B=[1000  721450
1005  720928
1006  721335];

for row = 1 : size(B, 1)
  matchedRow = find(A(:, 2) > B(row, 2), 1, 'first');
  fprintf('Row %d of B, which is %d, was first greater than that at row %d of A, which is %d\n', ...
    row, B(row, 2), matchedRow, A(matchedRow, 2));
  rowOfA(row) = matchedRow;
end

Results:

Row 1 of B, which is 721450, was first greater than that at row 15 of A, which is 721719
Row 2 of B, which is 720928, was first greater than that at row 13 of A, which is 720989
Row 3 of B, which is 721335, was first greater than that at row 14 of A, which is 721354

Note that this work even though your second column of A is not sorted. But there may be a closer row of A later on, because it's not sorted.

[pruth]

and can you tell us how to find coresponding value from first column of A ??