Synchronize two timetables with millisecond precision

15 次查看（过去 30 天）
Ford Creighton
Ford Creighton 2018-5-13
编辑： Tushar Agarwal 2018-8-2
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?

请先登录，再回答此问题。

回答（3 个）

Walter Roberson
Walter Roberson 2018-5-13
DataTable.Time = datetime(YYYY, MM, DD, hh, mm, ss + DataTable.Time / 1000, 'Format', 'uuuu-MMM-dd HH:mm:ss.SSS');
  1 个评论
Ford Creighton
Ford Creighton 2018-5-14
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

请先登录，再进行评论。

Peter Perkins
Peter Perkins 2018-5-14
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'.
  2 个评论
Ford Creighton
Ford Creighton 2018-5-14
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
Peter Perkins 2018-5-17
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.

请先登录，再进行评论。

Tushar Agarwal
Tushar Agarwal 2018-8-2
编辑：Tushar Agarwal 2018-8-2
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.

类别

在 Help Center 和 File Exchange 中查找有关 Timetables 的更多信息

标签

产品

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by