Find and insert missing dates in a table with irregular timestamp

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.

[dpb]

So, how does one know what to insert besides just a date?

[Ymkje Lize Neuteboom]

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.

Answer 1

[Steven Lord]

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?

[Ymkje Lize Neuteboom]

  16×3 timetable

     DATUM        STA      ComNr      FIN  
    ________    _______    _____    _______

    1/1/2004    '00:00'     ''      '04:00'
    1/1/2004    '04:00'     ''      '14:15'
    1/1/2004    '14:15'     ''      '24:00'
    1/3/2004    '00:00'     ''      '13:31'
    1/3/2004    '13:31'     ''      '21:23'
    1/3/2004    '21:23'     ''      '24:00'
    1/4/2004    '00:00'     ''      '24:00'
    1/5/2004    '00:00'     ''      '24:00'
    1/6/2004    '00:00'     ''      '02:37'
    1/6/2004    '02:37'     ''      '04:36'
    1/6/2004    '04:36'     ''      '24:00'
    1/7/2004    '00:00'     ''      '11:00'
    1/7/2004    '11:00'     ''      '13:02'
    1/7/2004    '13:02'     ''      '24:00'
    1/8/2004    '00:00'     ''      '24:00'
    1/9/2004    '00:00'     ''      '24:00'

Hi, thanks for your answer, however, if I use your approach, I get one row for every date. In my data, not all dates exist the same number of times. For example the piece of the timetable above, the second of January is missing, most of the other dates are present multiple times, exept for the 8th and 9th of January, which only appear one time.

I only want to insert the missing 2nd of January.

[dpb]

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...

[Ymkje Lize Neuteboom]

It took me some time, but it worked! Thank you!

[dpb]

Glad you managed it...sorry didn't have time to try to code it or work on a more elegant route...