Use retime() to create rows of empty data points

2 views (last 30 days)
Hello,
I have a dataset which looks like this:
Date Time V1 V2
'29.05.2019 10:30' 1 0
'31.05.2019 08:20' 0 1
'31.05.2019 08:30' 0 1
'31.05.2019 08:40' 0 1
'31.05.2019 08:50' 0 1
'31.05.2019 09:00' 0 1
'31.05.2019 09:10' 0 1
'31.05.2019 09:20' 0 1
'31.05.2019 09:30' 0 1
Where the data jumps from time to time, I would like to insert rows so that the DateTime variable increases in 10 minute increments in each row. The associated variables V1 and V2 should be filled with zeroes, whilst the existing values are left alone.
I have tried retime and while I can get this function to produce the array of DateTimes I am looking for, I cannot do this without removing the existing data in v1 and V2. Furthermore, I cannot get an interval of ten minutes without an error.
clc;clear;
m=readtable('G:\My Drive\Results\Tawh.csv'); %read in table
m.Date=datetime(m.Date, 'Format', 'dd/MM/yyyy'); %read columns as datetime values
m.Time=datetime(datevec(m.Time),'Format','HH:mm:ss');
m.Date.Format='dd.MM.uuuu HH:mm'; %change the display format to view both date and time info.
m.Time.Format='dd.MM.uuuu HH:mm';
m.DateTime = m.Date + timeofday(m.Time); %combine the date and time in one new column
m=m(:,[4:5,7]); %remove unnecessary columns
TT = table2timetable(m, 'RowTimes', 'DateTime'); %convert to timetable
dt = minutes(10);
%TT2 = retime(TT,'regular', 'TimeStep', dt);
%creates correct time values but deletes any data from other columns
%TT.Properties
%TT.Properties.VariableContinuity = {'event','event'};
TT2=retime(TT, 'minutely', 'fillwithmissing');
  2 Comments
Mohammad Sami
Mohammad Sami on 13 Mar 2020
Edited: Mohammad Sami on 13 Mar 2020
Can you upload a sample of your file. Also how do you wish the missing values to be filled in ?
Louise Wilson
Louise Wilson on 16 Mar 2020
Hi Mohammad, thank you for getting back to me. I've uploaded a file example here. I would like the time to step in increments of 10 minutes, and the missing values to be filled with zeroes. The time column would then move from the first file at 10:30ish to 20:30 that day. Then the next value would be the following day at 06:00. So, stepping in ten minutes every day between 0600-2030.

Sign in to comment.

Accepted Answer

Seth Furman
Seth Furman on 16 Mar 2020
Hi Louise,
I have tried retime and while I can get this function to produce the array of DateTimes I am looking for, I cannot do this without removing the existing data in v1 and V2.
It looks like we need to shift each value in the DateTime variable to the start of each minute (i.e. zero out the seconds part of each value in DateTime) before calling 'retime'.
Modifying the Format of TT.DateTime, we see that each value has a non-zero number of seconds. If we use 'retime' at this point, then we will lose our existing data, as you have seen.
>> TT.DateTime.Format = 'dd.MM.uuuu HH:mm:ss';
>> head(TT)
ans =
8×2 timetable
DateTime BoatCount PV
___________________ _________ __
29.05.2019 10:30:41 1 0
31.05.2019 08:20:07 0 1
31.05.2019 08:30:07 0 1
31.05.2019 08:40:07 0 1
31.05.2019 08:50:07 0 1
31.05.2019 09:00:07 0 1
31.05.2019 09:10:07 0 1
31.05.2019 09:20:07 0 1
>> temp = retime(head(TT),'regular','fillwithmissing','TimeStep',minutes(10));
>> head(temp)
ans =
8×2 timetable
DateTime BoatCount PV
___________________ _________ ___
29.05.2019 10:30:00 NaN NaN
29.05.2019 10:40:00 NaN NaN
29.05.2019 10:50:00 NaN NaN
29.05.2019 11:00:00 NaN NaN
29.05.2019 11:10:00 NaN NaN
29.05.2019 11:20:00 NaN NaN
29.05.2019 11:30:00 NaN NaN
29.05.2019 11:40:00 NaN NaN
We can fix this by zeroing the seconds part of the DateTime variable with 'dateshift'.
>> TT.DateTime = dateshift(TT.DateTime,'start','minute');
>> head(TT)
ans =
8×2 timetable
DateTime BoatCount PV
___________________ _________ __
29.05.2019 10:30:00 1 0
31.05.2019 08:20:00 0 1
31.05.2019 08:30:00 0 1
31.05.2019 08:40:00 0 1
31.05.2019 08:50:00 0 1
31.05.2019 09:00:00 0 1
31.05.2019 09:10:00 0 1
31.05.2019 09:20:00 0 1
Now, when we use 'retime', our original data won't be lost.
>> TT2 = retime(TT,'regular','fillwithmissing','TimeStep',minutes(10));
>> head(TT2)
ans =
8×2 timetable
DateTime BoatCount PV
___________________ _________ ___
29.05.2019 10:30:00 1 0
29.05.2019 10:40:00 NaN NaN
29.05.2019 10:50:00 NaN NaN
29.05.2019 11:00:00 NaN NaN
29.05.2019 11:10:00 NaN NaN
29.05.2019 11:20:00 NaN NaN
29.05.2019 11:30:00 NaN NaN
29.05.2019 11:40:00 NaN NaN
>> tail(TT2)
ans =
8×2 timetable
DateTime BoatCount PV
___________________ _________ __
18.12.2019 18:20:00 0 0
18.12.2019 18:30:00 0 0
18.12.2019 18:40:00 0 0
18.12.2019 18:50:00 0 0
18.12.2019 19:00:00 0 0
18.12.2019 19:10:00 0 0
18.12.2019 19:20:00 0 0
18.12.2019 19:30:00 0 0
The associated variables V1 and V2 should be filled with zeroes
To fill with 0's instead of NaN values for numeric variables, we can use the 'fillwithconstant' method instead of 'fillwithmissing'.
>> TT2 = retime(TT,'regular','fillwithconstant','Constant',0,'TimeStep',minutes(10));
>> head(TT2)
ans =
8×2 timetable
DateTime BoatCount PV
___________________ _________ __
29.05.2019 10:30:00 1 0
29.05.2019 10:40:00 0 0
29.05.2019 10:50:00 0 0
29.05.2019 11:00:00 0 0
29.05.2019 11:10:00 0 0
29.05.2019 11:20:00 0 0
29.05.2019 11:30:00 0 0
29.05.2019 11:40:00 0 0
>> tail(TT2)
ans =
8×2 timetable
DateTime BoatCount PV
___________________ _________ __
18.12.2019 18:20:00 0 0
18.12.2019 18:30:00 0 0
18.12.2019 18:40:00 0 0
18.12.2019 18:50:00 0 0
18.12.2019 19:00:00 0 0
18.12.2019 19:10:00 0 0
18.12.2019 19:20:00 0 0
18.12.2019 19:30:00 0 0
Best,
Seth
  5 Comments
Louise Wilson
Louise Wilson on 17 Mar 2020
Edited: Louise Wilson on 17 Mar 2020
Hi Seth, I realise now it's because I kept the 'site' and 'comments' column in. Is there away to fill with zeroes in some columns and fill with NAN in others, if I wanted to keep these columns? It's not crucial but could be useful later to have this as an option.
My final task with this is to include only times between 0600-2030... trying to find out how to do that now...
Seth Furman
Seth Furman on 19 Mar 2020
Hi Louise,
Is there a way to fill with zeroes in some columns and fill with NaN in others, if I wanted to keep these columns?
While it is not currently possible to provide multiple methods for adjusting timetable data in a single call to 'retime', we can accomplish the same task by calling retime twice, once for the numeric data and once for the text data, and then concatenating the results.
>> head(t)
ans =
8×4 timetable
DateTime Site BoatCount PV Comments
___________________ ______________ _________ __ __________
29/05/2019 10:30:00 {'Tawharanui'} 1 0 {'Hawere'}
31/05/2019 08:20:00 {'Tawharanui'} 0 1 {0×0 char}
31/05/2019 08:30:00 {'Tawharanui'} 0 1 {0×0 char}
31/05/2019 08:40:00 {'Tawharanui'} 0 1 {0×0 char}
31/05/2019 08:50:00 {'Tawharanui'} 0 1 {0×0 char}
31/05/2019 09:00:00 {'Tawharanui'} 0 1 {0×0 char}
31/05/2019 09:10:00 {'Tawharanui'} 0 1 {0×0 char}
31/05/2019 09:20:00 {'Tawharanui'} 0 1 {0×0 char}
>> retimedNumericVariables = retime(t(:,["BoatCount","PV"]),'regular','fillwithconstant','Constant',0,'TimeStep',minutes(10));
>> retimedTextVariables = retime(t(:,["Comments","Site"]),'regular','fillwithconstant','Constant',{'placeholder'},'TimeStep',minutes(10));
>> t = [retimedNumericVariables,retimedTextVariables];
>> head(t)
ans =
8×4 timetable
DateTime BoatCount PV Comments Site
___________________ _________ __ _______________ _______________
29/05/2019 10:30:00 1 0 {'Hawere' } {'Tawharanui' }
29/05/2019 10:40:00 0 0 {'placeholder'} {'placeholder'}
29/05/2019 10:50:00 0 0 {'placeholder'} {'placeholder'}
29/05/2019 11:00:00 0 0 {'placeholder'} {'placeholder'}
29/05/2019 11:10:00 0 0 {'placeholder'} {'placeholder'}
29/05/2019 11:20:00 0 0 {'placeholder'} {'placeholder'}
29/05/2019 11:30:00 0 0 {'placeholder'} {'placeholder'}
29/05/2019 11:40:00 0 0 {'placeholder'} {'placeholder'}
My final task with this is to include only times between 0600-2030
We can do this with logical indexing and the 'timeofday' function
>> may30thOrLaterIdx = t.DateTime >= datetime('30/05/2019','InputFormat','dd/MM/uuuu');
>> head(t(may30thOrLaterIdx,:))
ans =
8×4 timetable
DateTime BoatCount PV Comments Site
___________________ _________ __ _______________ _______________
30/05/2019 00:00:00 0 0 {'placeholder'} {'placeholder'}
30/05/2019 00:10:00 0 0 {'placeholder'} {'placeholder'}
30/05/2019 00:20:00 0 0 {'placeholder'} {'placeholder'}
30/05/2019 00:30:00 0 0 {'placeholder'} {'placeholder'}
30/05/2019 00:40:00 0 0 {'placeholder'} {'placeholder'}
30/05/2019 00:50:00 0 0 {'placeholder'} {'placeholder'}
30/05/2019 01:00:00 0 0 {'placeholder'} {'placeholder'}
30/05/2019 01:10:00 0 0 {'placeholder'} {'placeholder'}
>> timesBetween0600And2030Idx = hours(6) <= timeofday(t.DateTime) & timeofday(t.DateTime) <= (hours(20)+minutes(30));
>> t = t(timesBetween0600And2030Idx,:);
>> may30thOrLaterIdx = t.DateTime >= datetime('30/05/2019','InputFormat','dd/MM/uuuu');
>> head(t(may30thOrLaterIdx,:))
ans =
8×4 timetable
DateTime BoatCount PV Comments Site
___________________ _________ __ _______________ _______________
30/05/2019 06:00:00 0 0 {'placeholder'} {'placeholder'}
30/05/2019 06:10:00 0 0 {'placeholder'} {'placeholder'}
30/05/2019 06:20:00 0 0 {'placeholder'} {'placeholder'}
30/05/2019 06:30:00 0 0 {'placeholder'} {'placeholder'}
30/05/2019 06:40:00 0 0 {'placeholder'} {'placeholder'}
30/05/2019 06:50:00 0 0 {'placeholder'} {'placeholder'}
30/05/2019 07:00:00 0 0 {'placeholder'} {'placeholder'}
30/05/2019 07:10:00 0 0 {'placeholder'} {'placeholder'}
>> may31stOrEarlierIdx = t.DateTime <= datetime('31/05/2019','InputFormat','dd/MM/uuuu');
>> tail(t(may31stOrEarlierIdx,:))
ans =
8×4 timetable
DateTime BoatCount PV Comments Site
___________________ _________ __ _______________ _______________
30/05/2019 19:20:00 0 0 {'placeholder'} {'placeholder'}
30/05/2019 19:30:00 0 0 {'placeholder'} {'placeholder'}
30/05/2019 19:40:00 0 0 {'placeholder'} {'placeholder'}
30/05/2019 19:50:00 0 0 {'placeholder'} {'placeholder'}
30/05/2019 20:00:00 0 0 {'placeholder'} {'placeholder'}
30/05/2019 20:10:00 0 0 {'placeholder'} {'placeholder'}
30/05/2019 20:20:00 0 0 {'placeholder'} {'placeholder'}
30/05/2019 20:30:00 0 0 {'placeholder'} {'placeholder'}
Best,
Seth

Sign in to comment.

More Answers (0)

Categories

Find more on Timetables in Help Center and File Exchange

Products


Release

R2019a

Community Treasure Hunt

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

Start Hunting!