MATLAB Answers

how to add rows at missing times in a table?

4 views (last 30 days)
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!

  1 Comment

Turlough Hughes
Turlough Hughes on 6 Jan 2020
Could you attach the data as a .mat file?

Sign in to comment.

Accepted Answer

Guillaume
Guillaume on 6 Jan 2020
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}.

  1 Comment

Andrea Cecilia
Andrea Cecilia on 6 Jan 2020
this is exactly what I needed! it does it by itself, great!

Sign in to comment.

More Answers (0)

Sign in to answer this question.