Use retime() to create rows of empty data points

2020 3 13
1 个回答
回答已采纳
更新时间：2020 3 19
4 次查看（30 天）

0 个投票

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 个评论
显示 无 隐藏 无

Mohammad Sami
Mohammad Sami 2020-3-13
编辑：Mohammad Sami 2020-3-13
Can you upload a sample of your file. Also how do you wish the missing values to be filled in ?
Louise Wilson
Louise Wilson 2020-3-16
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.

请先登录，再进行评论。

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

 采纳的回答

Seth Furman
Seth Furman 2020-3-16

1 个投票

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 个评论
显示 3更早的评论 隐藏 3更早的评论

Seth Furman
Seth Furman 2020-3-16
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
Louise Wilson 2020-3-17
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
Louise Wilson 2020-3-17
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
Louise Wilson 2020-3-17
编辑：Louise Wilson 2020-3-17
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 2020-3-19
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

请先登录，再进行评论。

更多回答（0 个）

类别

在 帮助中心 和 File Exchange 中查找有关 Timetables 的更多信息

产品

版本

R2019a

标签

Community Treasure Hunt

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

Start Hunting!

Translated by