Use retime() to create rows of empty data points

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');

[Mohammad Sami]

Can you upload a sample of your file. Also how do you wish the missing values to be filled in ?

[Louise Wilson]

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.

Answer 1

[Seth Furman]

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

[Seth Furman]

Also worth mentioning:

datetimes and durations can be added directly

datetimes and durations can be added together without needing to first convert the durations to datetimes, set the Format property, etc. The lines

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

can be replaced with

m.DateTime = m.Date + m.Time; % combine the date and time in one new column
m.DateTime.Format = "dd/MM/uuuu HH:mm:ss"; % change the display format to view both date and time info

'removevars' can be used to remove table variables by name or index

The line

m=m(:,[4:5,7]); %remove unnecessary columns

can be made a little clearer by instead writing

m = removevars(m,["Date","Time","Site","Comments"]);

[Louise Wilson]

Hi Seth, thanks so much for taking the time to give such a thorough answer. I will be working through this today and comment with how I get on. This looks to be tremendously helpful.

[Louise Wilson]

Hi Seth, I have followed the steps and it's looking good. The code is a lot clearer and more concise now. Thanks for your help.

I get one error in the last step when I try to fill with zeros rather than NaN:

>> TT2 = retime(TT,'regular','fillwithconstant','Constant',0,'TimeStep',minutes(10));
%Error using timetable/retime (line 140)
%Conversion to cell from double is not possible.

Filling with NAN worked fine so I'm not sure where I'm going wrong here. I am using the same data I provided previously to try this.

[Louise Wilson]

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]

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