MATLAB Answers

converting a table (having as first col a datetime col) col into a datetime (having both date & time in its composition)

3 views (last 30 days)
Andy on 7 Oct 2019
Edited: Andy on 7 Oct 2019
Oftentimes a datetable has 2 adjacent columns: a datetime with a date and a string with a time. If one wants to concatenate these two into a datetime col having both date and time in it, one would be compelled to do:
Tab5Min = aImport5Min('myFile.txt');
Tab5Min.Date = datetime(strcat(datestr(Tab5Min.Date), " ", Tab5Min.Time),'InputFormat','dd-MMM-yyyy HH:mm');
Tab5Min.Time = [];
Tab5Min.Date = table2timetable(Tab5Min.Time ,"RowTimes","Date");
which is slow and unoptimal ... is there a better way?
further the the above ... subscripting by time ranges within a day within a resulting table as the above...
Say, one must substract all data samples between 01:00:00 and 02:30:00, one must do:
temp = Tab5Min;
temp.Date = timeofday(Tab5Min.Date);
S = timerange(duration('01:00:00','InputFormat','hh:mm:ss'),duration('02:30:00','InputFormat','hh:mm:ss'))
myResult = temp(S,:);
It's just painful, especially compared to Pandas.
Ok, one might say - let's do 2 functions:
function tsTable = ConcatDateTimeCols(tsTable, inputFormat)
% first 2 cols need to be named: Date (and to have datetime fields w/
% dates), and Time (having strings of type: "hh:mm:ss" or "hh:mm"
tsTable.Date = datetime(strcat(datestr(tsTable.Date), " ", tsTable.Time),'InputFormat',inputFormat);
tsTable.Time = [];
tsTable = table2timetable(tsTable, "RowTimes", "Date");
function subScript = GetDayTimeRange(tsTable,startTime,endTime)
% startTime & endTime need to be of format "18:45:00"
tsTable.Date = timeofday(tsTable.Date);
% if there are returns, compute them here;
S = timerange(duration(startTime,'InputFormat','hh:mm:ss'), duration(endTime,'InputFormat','hh:mm:ss'));
subScript = tsTable(S,:);
% if there are returns, compute cumsums here
but still is cumbersome ...
  1 Comment
Rik on 7 Oct 2019
The faster way is to edit the parser function. Functions that do a lot of work for you tend to be slow, especially if you need to parse the output another time.

Sign in to comment.

Answers (1)

Steven Lord
Steven Lord on 7 Oct 2019
Edited: Steven Lord on 7 Oct 2019
Convert your text data into a duration array, specifying an 'InputFormat', and add the datetime and duration arrays together.
For your second section of code, store the duration array you created either in a separate variable or as a variable in your table or timetable then use logical indexing.
% Arbitrary data
n = 25;
dur = hours(randi([0 24], n, 1)) + minutes(randi([0 60], n, 1));
% Thresholds
start = duration(1, 30, 0);
stop = duration(6, 45, 0);
% Logical arrays
afterStart = start <= dur;
beforeStop = dur <= stop;
isInRange = afterStart & beforeStop;
% Display the data about which condition(s) the data satisfied
t = table(dur, afterStart, beforeStop, isInRange)
% Show which durations are between 1:30 AM (1 hour 30 minutes post midnight)
% and 6:45 AM
% or
tooEarly = t(~afterStart, :)
tooLate = t(~beforeStop, :)
justRight = t(isInRange, :)




Community Treasure Hunt

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

Start Hunting!