how to add rows at missing times in a table?

Hello,

I have a dataset stored in the following table:

>> data(1:30,:)

ans =

  30×11 table

    AltitudeASL    TimestampUTC     DatesNTimes      Temp_C    DewPoint_C    RelativeHumidity    Pressure_hPa    WindAvgSpeed_kph    WindAvgDir_deg    WindGust_kph    RainRate_mmh
    ___________    ____________    ______________    ______    __________    ________________    ____________    ________________    ______________    ____________    ____________

        83          1.5593e+09     31/05/19 10:00     14.8        13.8              94              1020.3             1.6                180              3.2              0      
        83          1.5593e+09     31/05/19 10:05     14.7        13.7              94              1020.3               0                NaN              3.2              0      
        83          1.5593e+09     31/05/19 10:10     14.6        13.7              94              1020.3               0                NaN              3.2              0      
        83          1.5593e+09     31/05/19 10:15     14.6        13.6              94              1020.3               0                NaN                0              0      
        83          1.5593e+09     31/05/19 10:20     14.5        13.5              94              1020.3               0                NaN                0              0      
        83          1.5593e+09     31/05/19 10:25     14.5        13.5              94              1020.2               0                NaN                0              0      
        83          1.5593e+09     31/05/19 10:30     14.4        13.5              94              1020.1               0                NaN                0              0      
        83          1.5593e+09     31/05/19 10:35     14.4        13.5              94              1020.1               0                NaN                0              0      
        83          1.5593e+09     31/05/19 10:40     14.4        13.4              94              1020.1               0                NaN                0              0      
        83          1.5593e+09     31/05/19 10:45     14.3        13.4              94              1020.1               0                NaN                0              0      
        83          1.5593e+09     31/05/19 10:50     14.2        13.3              94              1020.1               0                NaN              1.6              0      
        83          1.5593e+09     31/05/19 10:55     14.2        13.4              95              1020.1               0                NaN                0              0      
        83          1.5593e+09     31/05/19 11:00     14.1        13.3              95              1020.3               0                NaN              3.2              0      
        83          1.5593e+09     31/05/19 11:05     14.1        13.3              95              1020.3             3.2                112              4.8              0      
        83          1.5593e+09     31/05/19 11:10     14.2        13.4              95              1020.3               0                NaN              3.2              0      
        83          1.5593e+09     31/05/19 11:15     14.3        13.5              95              1020.3               0                NaN              1.6              0      
        83          1.5593e+09     31/05/19 11:20     14.3        13.5              95              1020.2               0                NaN                0              0      
        83          1.5593e+09     31/05/19 11:25     14.2        13.4              95              1020.1               0                NaN              1.6              0      
        83          1.5593e+09     31/05/19 11:30     14.1        13.3              95              1020.1               0                NaN              1.6              0      
        83          1.5593e+09     31/05/19 11:35       14        13.2              95              1020.1               0                NaN              3.2              0      
        83          1.5593e+09     31/05/19 11:40     13.8        12.9              94                1020             1.6                112              3.2              0      
        83          1.5593e+09     31/05/19 11:45     13.7        12.9              95              1020.1               0                NaN                0              0      
        83          1.5593e+09     31/05/19 11:50     13.8        13.2              96                1020               0                NaN                0              0      
        83          1.5593e+09     31/05/19 11:55     13.8        13.2              96              1020.1               0                NaN                0              0      
        83          1.5593e+09     01/06/19 12:00     13.9        13.3              96              1020.1               0                NaN                0              0      
        83          1.5593e+09     01/06/19 12:05     13.9        13.3              96              1020.1               0                NaN                0              0      
        83          1.5593e+09     01/06/19 12:10     13.9        13.3              96              1020.1             1.6                112              3.2              0      
        83          1.5593e+09     01/06/19 12:15     14.2        13.7              97              1020.1             1.6                112              4.8              0      
        83          1.5593e+09     01/06/19 12:20     14.3        13.7              96              1020.1             1.6                315              3.2              0      
        83          1.5593e+09     01/06/19 12:25     14.2        13.6              96              1020.1             1.6                  0              4.8              0      
        

as you can see, the records are stored every 5 minutes. There can happen that one or more records are missing, so that there is a jump for example from 15:30 to 15:45, without the lines 15:35 and 15:40. What I need to do is to fill these missing lines with the correct date/time and values as NaN.

For doing this it is surely possible to use the "TimestampUTC" variable, which stores the time in unix timestamp format, and so the idea is to run a loop over all the rows like

tot_data=numel(table2array(:,1));
for i=1:(tot_data-1)
    DT=(table2array(data(i+1,2)))-(table2array(data(i,2)));
    if DT>300
        N=DT/300; %number of missing rows
        %add N rows to the data table after the i-th with the correct date/time and NaN values for parameters
    end
end

but I honestly have no idea of what code I need to write for executing the operation written in the last commented line.

Can you help me?

Thanks!

[Turlough Hughes]

Could you attach the data as a .mat file?

Answer 1

[Guillaume]

The easiest would be to convert your table into a timetable. You could indeed use the unix time after converting it into datetime but why not use DatesNTimes?

tt_data = table2timetable(data, 'RowTimes', 'DateNTimes');

It is then trivial to retime in interval of 5 minutes:

tt_data = retime(tt_data, 'regular', 'TimeStep', minutes(5));  %default option is fillwithmissing

---

Note that there is rarely a need for table2array. numel(table2array(data(:,1))) is simply height(data), and table2array(data(i+1,2)) is simply data{i+1, 2}.

[Andrea Cecilia]

this is exactly what I needed! it does it by itself, great!