Use retime() to create rows of empty data points
3 次查看(过去 30 天)
显示 更早的评论
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
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 ?
采纳的回答
Seth Furman
2020-3-16
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 个评论
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 个)
另请参阅
类别
在 Help Center 和 File Exchange 中查找有关 Timetables 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!