Built in look-up style interpolation of timetables for non-numeric data?

Is there no built-in function similar to interp1 for numeric data, that can operate on timetables with arbitrary data types?

Some random illustrative data:

x = 1:10;                  % numeric x values
y = sin(x);                % numeric y values
t = datetime()+(1:10);     % datetime x values
yNN = "foo"+strings(1,10); % non-numeric y values
yNN(3:6) = "bar";           % non-numeric y values

yNN = 1×10 string array
    "foo"    "foo"    "bar"    "bar"    "bar"    "bar"    "foo"    "foo"    "foo"    "foo"

T = timetable(t',yNN',y')

T = 10×2 timetable
            Time            Var1       Var2  
    ____________________    _____    ________

    05-Mar-2022 12:20:12    "foo"     0.84147
    06-Mar-2022 12:20:12    "foo"      0.9093
    07-Mar-2022 12:20:12    "bar"     0.14112
    08-Mar-2022 12:20:12    "bar"     -0.7568
    09-Mar-2022 12:20:12    "bar"    -0.95892
    10-Mar-2022 12:20:12    "bar"    -0.27942
    11-Mar-2022 12:20:12    "foo"     0.65699
    12-Mar-2022 12:20:12    "foo"     0.98936
    13-Mar-2022 12:20:12    "foo"     0.41212
    14-Mar-2022 12:20:12    "foo"    -0.54402

With interp1, we can take a "series" and interpolate on it with arbitrary (unsorted, repeated) values:

xProbe = [4 4 6 2 1 1 9];
yProbe = interp1(x,y,xProbe,"nearest")

yProbe = 1×7

-0.7568 -0.7568 -0.2794 0.9093 0.8415 0.8415 0.4121

However, it appears we cannot use timetable tools like retime or synchronize to do something similar on timetables...

tProbe = t(xProbe)

tProbe = 1×7 datetime array
   08-Mar-2022 12:20:12   08-Mar-2022 12:20:12   10-Mar-2022 12:20:12   06-Mar-2022 12:20:12   05-Mar-2022 12:20:12   05-Mar-2022 12:20:12   13-Mar-2022 12:20:12

R = retime(T,tProbe,"nearest")

Error using timetable/retime (line 142)
Target time vector for synchronization must contain unique times.

One also cannot use interp1 on non-numeric data

% yProbe = interp1(x,yNN,xProbe,"nearest") % uncomment to see it does not work

so that we cannot even do a simple hack like

% yProbe = interp1(seconds(t-datetime()),yNN,seconds(tProbe-datetime()),"nearest");

I can think of several ways to implement what I ultimately want, but is there some built in analog to "interp" function to operate on timetables that I am just not finding?

Answer 1

[Peter Perkins]

You absolutely can use interp1 on a numeric vector over a datetime grid with unsorted, repeated query points:

>> interp1(t,y,tProbe)
ans =
      -0.7568      -0.7568     -0.27942       0.9093      0.84147      0.84147      0.41212

retime won't let you do that:

"Target time vector for synchronization must contain unique times."

If you can do this

      >> R = retime(T,unique(tProbe),"nearest")
      R =
      5×2 timetable
      Time            Var1       Var2  
      ____________________    _____    ________
      05-Mar-2022 14:43:18    "foo"     0.84147
      06-Mar-2022 14:43:18    "foo"      0.9093
      08-Mar-2022 14:43:18    "bar"     -0.7568
      10-Mar-2022 14:43:18    "bar"    -0.27942
      13-Mar-2022 14:43:18    "foo"     0.41212

you can then use xProbe:

      >> R(t(xProbe),:)
      ans =
      7×2 timetable
      Time            Var1       Var2  
      ____________________    _____    ________
      08-Mar-2022 14:43:18    "bar"     -0.7568
      08-Mar-2022 14:43:18    "bar"     -0.7568
      10-Mar-2022 14:43:18    "bar"    -0.27942
      06-Mar-2022 14:43:18    "foo"      0.9093
      05-Mar-2022 14:43:18    "foo"     0.84147
      05-Mar-2022 14:43:18    "foo"     0.84147
      13-Mar-2022 14:43:18    "foo"     0.41212

Should retime allow you to use tProbe? Maybe, at least for nearest neighbor and interoplation methods, but not for some others, I will make a note to look into that.

[J. Alex Lee]

To your first point, yes that is exactly why I wanted something like interp1 for interpolating on tables, which does not have the limitation that retime has w.r.t. unsorted non-unique probes. What interp1 cannot do is work over non-numeric y as my commented example above would fail using the variable yNN.

To your second point, ok, nice answer, and yes the problem is stated in the error, but clearly I did not land upon your insight solely based on the description of the problem...of course in my real application I don't actually have an "xProbe" as an index for my time probes....I just did that for the sake of making up the example, so in actual practice I would need to do some book-keeping on the outputs of "unique", which always requires me to go into the documentation and re-learn exactly how everything fits...

And to your final question: i agree, maybe...but to be fair semantically "retime" does imply some different purpose than "interpolate"...perhaps a new method for timetables called "interpolate" would make some sense that can handle interpolation methods separately for numeric and non-numeric data (or even column-wise)?

And to address your edit about how it should only work on nearest/next/previous and interpolation (vs aggregation functions), yea, that's why it should be in a separate functionality called interpolation and not bloat retime

[J. Alex Lee]

So here would be the prototype custom function using @Peter Perkins's solution but with the duplicating indices computed from the call to unique()

function RT = interptt(TT,tv,opts)

arguments
	TT (:,:) timetable
	tv datetime {isvector}
	opts.Method {mustBeMember(opts.Method,["nearest","next","previous"])} = "nearest"
end

[x,~,ic] = unique(tv);

Ru = retime(TT,x,opts.Method);

RT = Ru(ic,:);

end % interptt

[Peter Perkins]

If it helps, retime does allow you to interpolate on non-numeric, at least using the nnbr methods:

>> retime(T,unique(tProbe),"nearest")
ans =
  5×2 timetable
            Time            Var1       Var2  
    ____________________    _____    ________
    05-Mar-2022 15:51:02    "foo"     0.84147
    06-Mar-2022 15:51:02    "foo"      0.9093
    08-Mar-2022 15:51:02    "bar"     -0.7568
    10-Mar-2022 15:51:02    "bar"    -0.27942
    13-Mar-2022 15:51:02    "foo"     0.41212

Why? interp1 is math. Timetables are for mixed type, including non-numeric, data. In any case, the trick that retime uses is nothing more than this:

>> yProbe = interp1(x,1:length(yNN),xProbe,"nearest")
yProbe =
     4     4     6     2     1     1     9
>> yProbe = yNN(yProbe)
yProbe = 
  1×7 string array
    "bar"    "bar"    "bar"    "foo"    "foo"    "foo"    "foo"

[J. Alex Lee]

Yes...again the whole point of my question was

interp1 | retime | function unbeknownst to me? |

----------------------+--------+--------+-----------------------------+

non-numeric variables | no | yes | yes |

--------------------- +--------+--------+-----------------------------+

unsorted/duplicate x | yes | no | yes |

----------------------+--------+--------+-----------------------------+

appears that the answer is no, but we can build upon retime (or whatever underlying NN, interp, aggregation tools it relies upon) by using @Peter Perkins's main solution (duplicating and unsorting on the reduced sorted probe).

The "under-the-hood" of how retime uses interp1 for NN methods is good to know, but now that I think about it, why shouldn't we be able to use aggregation methods to "interpolate" on unsorted and non-unique probes? It is more convenient from end-user perspective to call on retime with the entry duplication trick using the indices from unique.

[J. Alex Lee]

Oops, I hadn't thanked you yet @Peter Perkins for the answer. Notwithstanding the back-and-forth about the context and details, appreciate your help getting around this problem and significantly improving on the work-around I was employing before this answer.