Add NaN values in a timetable based on missing datetimes

Hi guys.

Let's say that I have timetable with n rows containing hourly data starting from date "t1" and ending on date "t2", the datetime vector of the time table is not complete in the sense that some data on certain datetimes are missing.

Is there a simple way to create the complete timetable (i.e. having the complete hourly datetime vector starting from t1 and ending at t2) where the missing datetimes data are filled with NaN?

Hope that the question is clear. Thank you!

Answer 1

[Steven Lord]

Here's some sample data.

MeasurementTime = datetime({'2015-12-18 08:03:05'; ...
                            '2015-12-18 10:03:05'; ...
                            '2015-12-18 12:03:05'});
Temp = [37.3;39.1;42.3];
Pressure = [30.1;30.03;29.9];
WindSpeed = [13.4;6.5;7.3];
TT = timetable(MeasurementTime,Temp,Pressure,WindSpeed)

TT = 3×3 timetable
      MeasurementTime       Temp    Pressure    WindSpeed
    ____________________    ____    ________    _________

    18-Dec-2015 08:03:05    37.3      30.1        13.4   
    18-Dec-2015 10:03:05    39.1     30.03         6.5   
    18-Dec-2015 12:03:05    42.3      29.9         7.3   

Let's create a new time vector.

newTimeVector = (MeasurementTime(1):hours(1):MeasurementTime(3)).'

newTimeVector = 5×1 datetime array
   18-Dec-2015 08:03:05
   18-Dec-2015 09:03:05
   18-Dec-2015 10:03:05
   18-Dec-2015 11:03:05
   18-Dec-2015 12:03:05

Use retime to create a new time table with the newTimeVector as its time vector. By default new rows in the new timetable will be filled with missing data. There are other methods you can select to generate the new data.

TT2 = retime(TT, newTimeVector)

TT2 = 5×3 timetable
      MeasurementTime       Temp    Pressure    WindSpeed
    ____________________    ____    ________    _________

    18-Dec-2015 08:03:05    37.3      30.1        13.4   
    18-Dec-2015 09:03:05     NaN       NaN         NaN   
    18-Dec-2015 10:03:05    39.1     30.03         6.5   
    18-Dec-2015 11:03:05     NaN       NaN         NaN   
    18-Dec-2015 12:03:05    42.3      29.9         7.3   

TT3 = retime(TT, newTimeVector, 'linear') % Linear interpolation

TT3 = 5×3 timetable
      MeasurementTime       Temp    Pressure    WindSpeed
    ____________________    ____    ________    _________

    18-Dec-2015 08:03:05    37.3       30.1       13.4   
    18-Dec-2015 09:03:05    38.2     30.065       9.95   
    18-Dec-2015 10:03:05    39.1      30.03        6.5   
    18-Dec-2015 11:03:05    40.7     29.965        6.9   
    18-Dec-2015 12:03:05    42.3       29.9        7.3