MATLAB Answers


How to split a table based on date and hour

Asked by Piro93
on 15 Jul 2019
Latest activity Answered by Steven Lord
on 15 Jul 2019
Hi to everybody, I'm a MATLAB beginner and I have a problem with a table of data related with the production of metallic pieces.
This is an example of my table:
Date Pieces
2012-01-07 05:55:47 128
2012-01-07 06:05:07 267
2012-01-07 08:24:14 589
2012-01-07 22:05:07 341
2012-01-08 11:45:23 160
2012-01-08 12:13:53 157
I would like to summarized the pieces produced, divided per days. The real problem is that (for the problem) the day does not start at 00:00:00 but at 06:00:00 and it finishes at the 6am of the subsequent day.
Considering the example, the first row refers to 2012-01-06, second, third and fourth to 2012-01-07 and so on.
How can I split the data in this way?
Thanks in advance!


Sign in to comment.


2 Answers

Answer by Guillaume
on 15 Jul 2019
Edited by Guillaume
on 15 Jul 2019
 Accepted Answer

The easiest way is to use retime (requires a timetable) or groupsummary grouping by day, Of course, for matlab a day starts at 00:00 and there's no option to tell it that you want a day to start at 6:00. The easiest workaround is to simply subtract 6 hours from your dates:
%T: a table
Toffset = T;
Toffset.Date = Toffset.Date - hours(6);
result = groupsummary(Toffset, 'Date', 'day', 'sum')
%TT: a timetable
TToffset = TT;
TToffset.RowTimes = TToffset.RowTimes - hours(6);
result = retime(TToffset, 'daily', 'sum')

  1 Comment

Thanks Guillaume, very good!!

Sign in to comment.

Answer by Steven Lord
on 15 Jul 2019

In addition to being able to specify 'day' as the newTimeStep input to retime you can specify a newTimes vector. Similarly, in groupsummary you can specify the groupbins input as 'day' or as a list of bin edges. Let's make a newTimes vector / vector of bin edges.
Start off with a vector of random datetime values.
d = datetime('now') + hours(48*randn(10, 1));
Let's get the earliest and latest datetime values in d.
firstTime = min(d);
lastTime = max(d);
Shift the earliest datetime to the start of its day (midnight) then add six hours. Similarly, shift the latest datetime to the end of its day and add six hours.
firstSixAM = dateshift(firstTime, 'start', 'day') + hours(6);
lastSixAM = dateshift(lastTime, 'end', 'day') + hours(6);
Actually, we need to shift firstSixAM earlier if the earliest time occurs before 6 AM on its day. In that case, dateshift determined midnight on that day but then the added six hours stepped past the earliest time. So we actually need 6 AM the day before.
if firstTime < firstSixAM
firstSixAM = firstSixAM - days(1);
The allSixAMs vector created below contains a vector of datetime values, spaced one day apart, each of which represents 6 AM on that date. You can use this as the newTimes for a retime call or as the bin edges for groupsummary.
allSixAMs = firstSixAM:days(1):lastSixAM;
We can check that every element of d is in the range spanned by the elements of allSixAMs.
whichBin = discretize(d, allSixAMs);
t = timetable(d, whichBin, allSixAMs(whichBin).', allSixAMs(whichBin+1).', ...
'VariableNames', {'BinNumber', 'StartOfBin', 'EndOfBin'})
The value of d in each row in t should be between the value of StartOfBin and EndOfBin in that row.


Sign in to comment.