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

2 views (last 30 days)
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");
end
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
end
but still is cumbersome ...
thanks
  1 Comment
Rik
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
dur(isInRange)
% or
tooEarly = t(~afterStart, :)
tooLate = t(~beforeStop, :)
justRight = t(isInRange, :)

Categories

Find more on Tables in Help Center and File Exchange

Products


Release

R2019b

Community Treasure Hunt

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

Start Hunting!