How can I find the state of each element in a state transition table at time X?

I have a state transition table of the following form:

[Elements State Timestamp]:
[1 IN_PROCESS 702]
[1 UPDATING 705]
[1 COMPLETE 742]
[2 IN_PROCESS 17]
[2 UPDATING 86]
[3 IN_PROCESS 926]
[4 IN_PROCESS 17]
[4 IN_PROCESS 800]
[4 IN_PROCESS 950]
[4 FAILED 975]

I'd like to find the state of each element at timestamp 'x'. I'd prefer a solution that outputs a column vector of length equal to the number of elements (in this example, length 4), with the index to row of the state table with the corresponding element and the largest timestamp less than or equal to x. If x is less than the first timestamp, the result would be 0. It's ok to assume that the timestamps are in ascending order for a given element and that the element numbers are also in ascending order, but bonus points if these assumptions can be relaxed. So, for example:

    FindStateAtTime(86) would return
  [0]
  [5]
  [0]
  [7]

The part above is the tricky part. A second (simpler) task that I'd welcome a good technique for would be a method to convert this to a 4x1 vector with the actual state value. The state value is stored as a categorical. I'd like 0 to be represented as 'NULL', as follows:

NULL
UPDATING
NULL
IN_PROCESS

My goal is to be faster than a for-loop, but hopefully not too obscure or long of a function. For reference, the state table has about 1.5 million rows referencing a few hundred thousand elements.

Thanks in advance!

Answer 1

[Jon Glassman]

I was able to come up with an answer. It's a bit more complicated than I would like, but it works. Let me know if anyone has a simpler idea!

Basically, I add a column "i" with the starting index. I then add a set of "NULL" rows with a fake timestamp of -1, and "i" of 0. I remove all rows with timestamp>x. I then sort the rows with timestamp in descending order, and use the second output of ismember to find the index in the new table of the row I am interested in.

Here's the starting table:

statetable =

10×3 table

    elements      state       timestamp
    ________    __________    _________

    1           IN_PROCESS    702      
    1           UPDATING      705      
    1           COMPLETE      742      
    2           IN_PROCESS     17      
    2           UPDATING       86      
    3           IN_PROCESS    926      
    4           IN_PROCESS     17      
    4           IN_PROCESS    800      
    4           IN_PROCESS    950      
    4           FAILED        975

And here's the script to generate the desired answer:

x = 86;
elements_vector = (1:max(statetable.elements))';

statetable.i = (1:height(statetable))';
nullrows = table(...
  elements_vector,...
  repmat(categorical({'NULL'}),max(statetable.elements),1),...
  -ones(max(statetable.elements),1),...
  zeros(max(statetable.elements),1),...
  'VariableNames',{'elements','state','timestamp','i'});

statetable = [statetable;nullrows];
statetable = statetable(statetable.timestamp<=x,statetable.Properties.VariableNames);
statetable = sortrows(statetable,{'elements','timestamp'},{'ascend','descend'});
[~,tmp] = ismember(elements_vector,statetable.elements);

After that, the answer is easily produced as follows:

>> IndexAtTimeX = statetable.i(tmp)

IndexAtTimeX =

       0
       5
       0
       7

>> StateAtTimeX = statetable.state(tmp)

StateAtTimeX = 

    4×1 categorical array

       NULL 
       UPDATING 
       NULL 
       IN_PROCESS