Synchronize two timetables with millisecond precision

I'm trying to synchronize two timetables, and linearly interpolate the missing datapoints. I need the resulting, synchronized timetable to be accurate to the nearest 10th of a second, though. When I try to

I load in two tables with "readtable". The first table is straightforward. The second table "DataTable", has a time column with elapsed times, in milliseconds. Knowing the date and time of the start of the sample collection, I take this datetime, and add the datetime to the "time" column (which is in milliseconds),

DataTable.Time = datetime(YYYY, MM, DD, hh, mm, ss) + milliseconds(DataTable.Time);

This might be where the problem starts, as the new DataTable.Time is in a datetime format, but doesn't hold the time accuracy to the nearest millisecond.

Then I convert to timetable:

TimeTable2 =  table2timetable(DataTable,'RowTimes','Time');

Then I synchronize the two tables. One method works fine, but there are repeated timestamps,

newTable = synchronize(TimeTable1, TimeTable2,'commonrange')

I really would like this to work:

newTable = synchronize(TimeTable1, TimeTable2,'commonrange', 'linear')

But because of the repeated timestamps, I get this error:

Error using timetable/synchronize (line 321)
Input timetables must contain unique row times when synchronizing using 'linear'.

Is there a way to use the "synchronize" function with row-times at a rate of 10 Hz?

Answer 1

[Walter Roberson]

DataTable.Time = datetime(YYYY, MM, DD, hh, mm, ss + DataTable.Time / 1000, 'Format', 'uuuu-MMM-dd HH:mm:ss.SSS');

[Ford Creighton]

Thanks Walter, this helps with the display issues, and helps me confirm I have the right datetimes. I still get this error when trying to sync two timetables:

Error using timetable/synchronize (line 321)
Input timetables must contain unique row times when synchronizing using 'linear'.

I checked my timestamps and I don't see any that repeat. However, when I aggregate the data to 1 sample/second, I don't get the error. It makes me think that synchronize doesn't linearly interpolate data faster than 1 Hz

Answer 2

[Peter Perkins]

As Walter says, the ms thing is just a display format issue.

You say you are starting out with ms-resolutiuon data, and you want something that's regular at 10Hz. That sounds more like aggregation tyhan interpolation. You also say that there are missing data points, so that probably explains why you used interpolation.

You are going to need to reconcile the repeated timestamps. Having two values at one timestamp just isn't allowed for interpolation. You can run retime on one timetable, spacifying the existing time vector as the target, and using something like 'mean' or 'firstvalue'.

[Ford Creighton]

Thanks Peter, I mis-typed when I said the data was ms-resolution. It is data that is logged at 10 Hz frequency, but the timestamp is to the nearest millisecond. So I have time(1) = 123456 ms, time(2) = 123556 ms. (and the collected samples are not always perfectly 100 ms apart). Here's a sample of my dataset when I run:

TT = synchronize(table1, table2)

This works, but gives me NaNs, so I try and run with the 'union', and 'linear' options set...

'2/23/18 13:23:53.696'  148873696  0  0  1443  0  5401  NaN
'2/23/18 13:23:53.792'  148873792  0  0  1445  0  43764  NaN
'2/23/18 13:23:53.888'  148873888  0  0  1444  0  467  NaN
'2/23/18 13:23:53.984'  148873984  0  0  1451  0  41970  NaN
'2/23/18 13:23:54.000'  NaN  NaN  NaN  NaN  NaN  NaN  22776
'2/23/18 13:23:54.080'  148874080  0  0  1443  0  13722  NaN
'2/23/18 13:23:54.192'  148874192  0  0  1435  0  53307  NaN
'2/23/18 13:23:54.288'  148874288  204.75  19  1440  1445  20568  NaN
'2/23/18 13:23:54.384'  148874384  0  0  1449  0  14988  NaN

But then I still get this error:

Error using timetable/synchronize (line 321)
Input timetables must contain unique row times when synchronizing using 'linear'.

I tried checking all of my timestamps and none seem to repeat. I also took the 1Hz data (like that at time 13:23:54.288) and removed the timestamps where the "0" values occur frequently, and everything works nominally. This makes me wonder: does "synchronize" work at sample rates faster than 1 Hz?

[Peter Perkins]

From that error, it seems like you must have duplicate times in there somewhere. One quick way to find them would be something like

retime(tt,tt.Time,'count')

and then look for values bigger than 1. If that's not it, I think we'd have to see the data.

Answer 3

[Tushar Agarwal]

Hi Ford. I had a similar problem and indeed there were 5 duplicated time-stamps in my dataset (which I think is not easy to manually find in huge datasets). In fact, I didn't find the duplicates myself but just removed them using 2 simple lines of code.

% Find the unique set of times
uniqueTimes = unique(TimeTable2.Time or the name of the time-index column in newer MATLAB);
% retime all the data using this unique set
TimeTable2 = retime(TimeTable2,uniqueTimes,'mean');

This should solve your problem. The last option 'mean' basically replaces the duplicate-time data-entries with their means. You can use 'previous' to replace with the last duplicate-time entry or leave this argument out to replace with the first duplicate-time entry (by default). Hope this helps.