how to add rows at missing times in a table?

5 次查看（过去 30 天）
Andrea Cecilia
Andrea Cecilia 2020-1-6
评论： Andrea Cecilia 2020-1-6
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!

请先登录，再回答此问题。

采纳的回答

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

更多回答（0 个）

类别

在 Help Center 和 File Exchange 中查找有关 Data Type Conversion 的更多信息

标签

产品

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by