Insert new rows of NaN for missing timestamps in time series.
13 views (last 30 days)
Show older comments
Eric Escoto
on 31 Jan 2019
Answered: Peter Perkins
on 7 Feb 2019
Hello, I have a time series that is missing some data. In the places where there is a data gap I would like to insert rows of NaN values at the same incremetal time step. The data is organized by a datenum.
Here is the current script I'm using to get the timeseries.
Thanks!
% Read the data.
[num, txt, raw] = xlsread('SFL_2018.xlsx'); % Creates three variables. A number (double), raw (cell), and txt (cell).
% Note that the 'txt' and 'raw' variables contain the decriptions of the columns.
% Rename the numerical matrix and remove the upper 4 rows that originally contained the data headers.
SFL2018 = num(5:end, :);
% Change the first column (excel datenum value) to MATLAB datenum value.
datevec = datetime(SFL2018(:,1),'ConvertFrom','excel');
date_num = datenum(datevec);
SFL2018 = [date_num SFL2018(:, 2:end)];
% Rename the 'txt' cell to 'header' and clear unused variables.
header = txt(1:4,:);
clearvars('num', 'txt', 'raw', 'date_num');
0 Comments
Accepted Answer
Peter Perkins
on 7 Feb 2019
retime "adjusts" your data to a new set of row times. In general, "adjust" means things like interpolate, or aggregate, or take the neaest value, but by default it fiills with missing values. So
>> tt = timetable([1;2;5;6],'RowTimes',datetime(2019,2,[1;2;5;6]))
tt =
4×1 timetable
Time Var1
___________ ____
01-Feb-2019 1
02-Feb-2019 2
05-Feb-2019 5
06-Feb-2019 6
>> retime(tt,datetime(2019,2,1:6))
ans =
6×1 timetable
Time Var1
___________ ____
01-Feb-2019 1
02-Feb-2019 2
03-Feb-2019 NaN
04-Feb-2019 NaN
05-Feb-2019 5
06-Feb-2019 6
Or in this simple case
>> retime(tt,'daily')
ans =
6×1 timetable
Time Var1
___________ ____
01-Feb-2019 1
02-Feb-2019 2
03-Feb-2019 NaN
04-Feb-2019 NaN
05-Feb-2019 5
06-Feb-2019 6
0 Comments
More Answers (1)
Peter Perkins
on 31 Jan 2019
I recommend you use a timetable. Read your data in using readtable, then use table2timetable. The retime function makes what you want one line.
See Also
Categories
Find more on Calendar in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!