How to split a table based on date and hour
13 vues (au cours des 30 derniers jours)
Afficher commentaires plus anciens
Piro93
le 15 Juil 2019
Réponse apportée : Steven Lord
le 15 Juil 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!
0 commentaires
Réponse acceptée
Guillaume
le 15 Juil 2019
Modifié(e) : Guillaume
le 15 Juil 2019
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')
Plus de réponses (1)
Steven Lord
le 15 Juil 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);
end
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.
0 commentaires
Voir également
Catégories
En savoir plus sur Timetables dans Help Center et File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!