Filling missing times in timetable with NaN using retime

30 views (last 30 days)
I am trying to create rows in a timetable for days where data is missing. I have the attached table as an example.
Can anyone explain why the following does not work? Instead of getting one row filled with NaN for the only day missing data (15th Nov), all rows after the missing date are replaced with NaN.
data.ChunkEnd(4:end)=data.ChunkEnd(4:end)+seconds(1); %think this is necessary because the timing is slightly off?
  1 Comment
Louise Wilson
Louise Wilson on 6 Oct 2022
I can get it to work if I do
since the time isn't actually necessary in this context, but I'm wondering why it doesn't work...

Sign in to comment.

Answers (1)

dpb on 7 Oct 2022
ttD=data_site_num_tt1; % just get a shorter variable name to work with
clear data_site_num_tt1;
rule=-1*double(second(ttD.ChunkEnd)==0); % don't round up 00 seconds rule
ttD.ChunkEnd=dateshift(ttD.ChunkEnd,'end','minute',rule); % round up any those with seconds
newt=datetime(ttD.ChunkEnd(1):days(1):ttD.ChunkEnd(end)); % fill in the time vector to match
ttD=retime(ttD,newt) % and retime -- will insert missing automagically
ttD = 8×5 timetable
ChunkEnd podN DPM Nall MinsOn FalsePositiveDPM ____________________ ____ ___ __________ ______ ________________ 12-Nov-2017 23:59:00 2714 7 1756 1440 0 13-Nov-2017 23:59:00 2714 16 11348 1440 0 14-Nov-2017 23:59:00 2714 0 3310 1440 0 15-Nov-2017 23:59:00 NaN NaN NaN NaN NaN 16-Nov-2017 23:59:00 1533 0 32098 1440 0 17-Nov-2017 23:59:00 1533 0 48293 1440 3 18-Nov-2017 23:59:00 1533 0 3.4973e+05 1440 0 19-Nov-2017 23:59:00 1533 0 18702 1440 0
The above is a little esoteric with the use of the secondary rule argument and the logic test to set it so would not turn 23:59 into 24:00 and then 00:00 of the next day, granted, but figured may as well demonstrate a corner feature of retime while at it and had the opportunity.
One might just use regular timing and round to the next day since all are so near midnight; that's a decision the data owner has to decide about which date to report on.


Find more on Timetables 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!