MATLAB Answers

0

Find and insert missing dates in a table with irregular timestamp

Asked by Ymkje Lize Neuteboom on 22 Nov 2019 at 9:52
Latest activity Commented on by dpb
on 3 Dec 2019 at 17:17
I have a data file with irregular dates, which looks like this:
DATUM STA ComNr FIN
_________ _______ _____ _______
5/21/2017 '00:00' '' '24:00'
5/22/2017 '00:00' '' '24:00'
5/23/2017 '00:00' '' '24:00'
5/24/2017 '00:00' '' '24:00'
5/25/2017 '00:00' '' '24:00'
5/26/2017 '00:00' '' '24:00'
5/27/2017 '00:00' '' '24:00'
5/27/2017 '02:00' '' '01:00'
5/28/2017 '00:00' '' '24:00'
5/29/2017 '00:00' '' '24:00'
5/30/2017 '00:00' '' '24:00'
5/31/2017 '00:00' '' '24:00'
6/1/2017 '00:00' '' '07:00'
6/3/2017 '00:00' '' '24:00'
6/4/2017 '00:00' '' '24:00'
6/5/2017 '00:00' '' '24:00'
6/6/2017 '00:00' '' '24:00'
I would like to find and insert the missing dates (in this case the 2nd of June), but since the dates are irregular the retime command does not work.

  2 Comments

So, how does one know what to insert besides just a date?
I'm sorry for the incomplete question. It doesn't really matter, just the missing dates and zeros would be fine. The problem is that the rest of my code is based on continous data. I only found out when I put in the data from 15 years, hence the results won't really change.

Sign in to comment.

1 Answer

Answer by Steven Lord
on 22 Nov 2019 at 14:46
 Accepted Answer

retime should work. Let's make a sample timetable with a few pieces of data.
dt = datetime(2017, [5; 5; 5; 6; 6; 6; 6], [29; 30; 31; 1; 3; 4; 5]);
tt = timetable(dt, (1:7).')
To make the time vector of the timetable tt a daily vector by filling the missing value in Var1 with NaN (which is the missing value for double precision data) use retime and specify 'daily' as the NEWTIMESTEP input.
tt2 = retime(tt, 'daily')
To interpolate the value of Var1 for June 2nd using linear interpolation:
tt3 = retime(tt, 'daily', 'linear')
The help text and documentation for retime list other methods for filling in the data for new rows.
If you've tried retime and it hasn't worked, can you show us a small example of your timetable and how you call retime?

  4 Comments

Show 1 older comment
I've got meeting in town so don't have time for code but I think if you create a second time series of one element per day over the time frame and missing value for data you can merge the two, then remove any with duplicated days and missing values.
May be a simpler way, but time presses at moment...
It took me some time, but it worked! Thank you!
Glad you managed it...sorry didn't have time to try to code it or work on a more elegant route...

Sign in to comment.