convert 12 hour data to 24 hour datetime data for timetable
63 次查看(过去 30 天)
显示 更早的评论
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.
0 个评论
采纳的回答
Stephen23
2022-3-16
编辑:Stephen23
2022-4-29
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 个评论
Stephen23
2022-4-29
编辑:Stephen23
2022-4-29
"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.
更多回答(2 个)
Peter O
2022-3-16
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.
另请参阅
类别
在 Help Center 和 File Exchange 中查找有关 Data Type Conversion 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!