Retime timetable out of bounds

3 views (last 30 days)
Rub Ron
Rub Ron on 27 Aug 2019
Commented: Rub Ron on 30 Aug 2019
I have a set of timetables inside a cell TT, each of them with a resolution of 15 minutes, the timetables have different starts and ends, different sizes (missing data for each are different). What I want to do is set all these time tables to a common time vector, and then fill all the missing data with NaN. If I use retime (for one timetable) in this from:
TT1new = retime(TT{1},'regular','fillwithmissing','TimeStep',minutes(15));
it fills NaN but within the time range of TT1, and not of my time vector. So it seems to me that in order to use my time vector "retime" is of no use, instead I have done the following:
tini = datetime({'01-Apr-2016 00:00:00'});
tfin = datetime({'01-Apr-2017 00:00:00'});
timeVector = [tini:datenum([0,0,0,0,15,0]):tfin]';
%I built a reference timetable full of NaN, (I have 3 variables in each timetable)
ttref = timetable(timeVector,NaN(length(timeVector),1),NaN(length(timeVector),1),NaN(length(timeVector),1));
%Then I plan to fill the non-missing data in the reference timetable for each timetable, and capture the result in TTFit
num = length(TT);
TTFit = cell(num,1);
for i=1:num
temp=ttref;
range = timerange(max(tini,TT{i}.Time(1)),min(tfin,TT{i}.Time(end)),'closed');
temp(TT{i}(range,:).Time,:).Variables = TT{i}(range,:).Variables;
TTFit{i}=temp;
end
But it takes an eternity (more than 20 minutes so far and yet dont know if it works) to finish the run. Is there an easier and efficient way to do that? Any hints would be really apreciatted.

Accepted Answer

Lei Hou
Lei Hou on 27 Aug 2019
Hi Rub,
First, about your code examples I have some suggestions. I saw you used datenum in your first post. It is good that you replaced datenum with minutes in your second post. It's never a good idea to mix datenum with the newer and preferred datetime. About preallocating a timetable, you can also do it by using timetable preallocation syntax as the following.
ttref = timetable('Size', [length(timeVector), 3], 'VariableTypes', ["double" "double" "double"], 'RowTimes', timeVector);
To answer your question, retime is the right tool for your job. Retime has a syntax TT2 = retime(TT1,NEWTIMES). You can use TTFit{i}=retime(TT{i},timeVector) in your for-loop. Your code will look like the following:
tini = datetime({'01-Apr-2016 00:15:00'});
tfin = datetime({'07-Apr-2017 00:00:00'});
timeVector = [tini:minutes(15):tfin]';
num = length(TT);
TTFit = cell(num,1);
for i=1:num
TTFit{i}=retime(TT{i},timeVector);
end
Furthermore, you can use cellfun to replace your for-loop as
tini = datetime({'01-Apr-2016 00:15:00'});
tfin = datetime({'07-Apr-2017 00:00:00'});
timeVector = [tini:minutes(15):tfin]';
TTFit = cellfun(@(tt)retime(tt, timeVector),TT,'UniformOutput',false);
This runs faster.
I’m interested in why you put your timetables in a cell array, and what you will do with it next. If you are going to concatenate then all to make one timetable, then synchronize is the tool that can help you. Steve suggested calling synchronize using a comma-separated list, which you may not have seen before. Here's what that looks like:
TTFit = synchronize(TT{:},timeVector);
  3 Comments
Peter Perkins
Peter Perkins on 29 Aug 2019
Edited: Steven Lord on 29 Aug 2019
Rub, if you have a moment, it would be helpful to hear more about how the data were organized in Excel, for example do you have multiple sheets in one spreadsheet? multiple spreadsheets? multiple folders each containg multiple spreadsheets?.
It would also be helpful to know what you would do next with that cell array of timetables. They will all have the same time vector, so one thing you might do is to horzcat them together (equivalent to what Steve Lord originally suggested). If they represent different measurements from different groups of sensors, that probably makes sense. But if they represent the same measurements juat a different locations, you might want to keep them separate and iterate over them, doing the same cleaning or calculations on each one.
Thanks in advance.
[SL: fixed typo of my name]
Rub Ron
Rub Ron on 30 Aug 2019
Hello Peter Perkins. The data are same mesurement from different locations. All the data were spread on multiple spreadsheets, and in each spreadsheet there where several units (on the same sheet). I dont know the data was organized in that way but it would have been better either to have one speadsheet per unit, or all units in one spreadsheet to make easy the reading of the data). Cheers!

Sign in to comment.

More Answers (2)

Steven Lord
Steven Lord on 27 Aug 2019
In this case, retime is not the right tool for the job. synchronize them instead. Since you have a cell array of timetable arrays, pass them into synchronize as a comma-separated list.

Rub Ron
Rub Ron on 27 Aug 2019
For an strange reason copying data from one timetable to another as it was doing in this line, it's a time consuming ejecution (I calculated it would take me 2 hours to finish the run before I stop it).
temp(TT{i}(range,:).Time,:).Variables = TT{i}(range,:).Variables;
I could make much faster the rutine with synchronize, as Steven Lord suggested,. Here is the update of the code in case is of any use of someone:
tini = datetime({'01-Apr-2016 00:15:00'});
tfin = datetime({'07-Apr-2017 00:00:00'});
timeVector = [tini:minutes(15):tfin]';
ttref = timetable(timeVector,NaN(length(timeVector),1));
ttref.Variables = [];
num = length(TT);
TTFit = cell(num,1);
for i=1:num
range = timerange(max(tini,TT{i}.Time(1)),min(tfin,TT{i}.Time(end)),'closed');
TTFit{i}=synchronize(ttref,TT{i}(range,:));
end
There was a tricky part with Syncronize, as to my issue, I had to create a emthy timetable (ttref) only with the timeVector.

Categories

Find more on Tables in Help Center and File Exchange

Community Treasure Hunt

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

Start Hunting!