convert 12 hour data to 24 hour datetime data for timetable
Show older comments
I have a data table with the first 3 variables corresponding to the date/time. I would like to convert the date time variables to
'yyy-MM-dd hh:mm:ss' data like I have for my other timetable. I'm just confused on how to efficiently convert this. In the images attached, the raw image is the table from my data txt file and desired is the desired timetable format.
Accepted Answer
Edit: read the comments to handle ambiguous midday/midnight:
MeasurementTime = datetime({'2015-12-18 08:03:05';'2015-12-18 10:03:17';'2015-12-18 12:03:13'});
Temp = [37.3;39.1;42.3];
Pressure = [30.1;30.03;29.9];
WindSpeed = [13.4;6.5;7.3];
AMPM = cellstr(['AM';'PM';'AM']);
TT = timetable(MeasurementTime,Temp,Pressure,WindSpeed,AMPM)
ix = strcmpi(TT.AMPM,'PM'); % avoids implicit ordering of FINDGROUPS
TT.MeasurementTime = TT.MeasurementTime + hours(12).*ix
5 Comments
Poison Idea fan
on 28 Apr 2022
"This incorrectly adds 12 hours to cases at 12AM and 12 PM."
Aaah, everyone's favorite ambiguous times of day:
I am guessing that you intend 12AM=midnight and 12PM=midday. Here are two approaches to handle that.
MeasurementTime = datetime({'2015-12-18 12:00:00';'2015-12-18 12:00:01';'2015-12-18 01:00:00';'2015-12-18 11:00:59';'2015-12-18 12:00:00';'2015-12-18 12:00:59';'2015-12-18 11:00:59'});
Temp = [23;37.3;38;39.1;42.3;64;99];
Pressure = [28;30.1;30;30.03;29.9;28;32];
WindSpeed = [17;13.4;11;6.5;7.3;5;7];
AMPM = cellstr(['AM';'AM';'AM';'AM';'PM';'PM';'PM']);
TT = timetable(MeasurementTime,Temp,Pressure,WindSpeed,AMPM)
1- Use DATETIME itself, e.g.:
dt = datetime(strcat(string(TT.MeasurementTime),TT.AMPM),'InputFormat','d-MMM-y h:m:sa')
Although it looks temptingly easy, note that this approach may/will lose precision during the string conversion.
2- Or we can modify my earlier code to handle those cases, at full precision:
hh = TT.MeasurementTime.Hour; % modified
ip = strcmpi(TT.AMPM,'PM'); % modified
ix = (ip & hh<12)-(~ip & hh==12); % modified
TT.MeasurementTime = TT.MeasurementTime + hours(12).*ix
Please test this and let me know if it works on your data sets. If it works, I will add it to my answer.
Poison Idea fan
on 29 Apr 2022
Edited: Poison Idea fan
on 29 Apr 2022
"Maybe there is a better way to import the data file that can avoid this issue in the first place." I doubt that there is a simple way to import such a mixed-up datestamp as that: the units are totally out of order. We can import the time-of-day part as DURATION, the AM/PM part as text, and the date as DATETIME, for example:
fnm = 'CRD445_20220427.txt';
opt = detectImportOptions(fnm);
opt.VariableNames = {'time','AP','date','val'};
opt = setvaropts(opt,'date','InputFormat','MM/dd/u');
tbl = readtable('CRD445_20220427.txt',opt)
And then make the adjustment for AM/PM, just as I showed before:
hh = hms(tbl.time);
ip = strcmpi(tbl.AP,'PM');
ix = (ip & hh<12)-(~ip & hh==12);
tmp = tbl.date + tbl.time + hours(12).*ix;
tmp.Format = 'yyyy-MM-dd HH:mm:ss.SSS';
tbl.dt = tmp
Now lets check the DATETIMEs. Lets have a closer look at the rows around midnight:
[~,idx] = min(abs(tbl.dt-datetime(2022,4,28,0,0,0)))
tbl(idx-4:idx+4,:)
and around midday:
[~,idx] = min(abs(tbl.dt-datetime(2022,4,28,12,0,0)))
tbl(idx-4:idx+4,:)
and also around 13:00:
[~,idx] = min(abs(tbl.dt-datetime(2022,4,28,13,0,0)))
tbl(idx-4:idx+4,:)
So far everything looks okay. A better file format would make this much much easier.
Poison Idea fan
on 29 Apr 2022
More Answers (2)
Once imported, you could query the AM/PM column, and if the answer is PM, add 12 hours to the value, and then delete the AM/PM column from the table.
Borrowing the windspeed example table:
MeasurementTime = datetime({'2015-12-18 08:03:05';'2015-12-18 10:03:17';'2015-12-18 12:03:13'});
Temp = [37.3;39.1;42.3];
Pressure = [30.1;30.03;29.9];
WindSpeed = [13.4;6.5;7.3];
AMPM = cellstr(['AM';'PM';'AM']);
TT = timetable(MeasurementTime,Temp,Pressure,WindSpeed,AMPM)
G = findgroups(TT.AMPM) % Ordered alphabetically, so PM is G ==2
TT.MeasurementTime(G==2,:) = TT.MeasurementTime(G==2,:) + hours(12)
TT.AMPM = [] % Drop AMPM field.
1 Comment
Poison Idea fan
on 16 Mar 2022
talha iqbal
on 26 May 2023
Edited: talha iqbal
on 26 May 2023
0 votes
What about Noon (12PM) or Midnight (12AM) ?
1 Comment
Poison Idea fan
on 31 May 2023
Categories
Find more on Tables in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!
