convert 12 hour data to 24 hour datetime data for timetable
Mostra commenti meno recenti
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.
Risposta accettata
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 Commenti
Poison Idea fan
il 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
il 29 Apr 2022
Modificato: Poison Idea fan
il 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
il 29 Apr 2022
Più risposte (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 Commento
Poison Idea fan
il 16 Mar 2022
talha iqbal
il 26 Mag 2023
Modificato: talha iqbal
il 26 Mag 2023
0 voti
What about Noon (12PM) or Midnight (12AM) ?
1 Commento
Poison Idea fan
il 31 Mag 2023
Categorie
Scopri di più su Dates and Time in Centro assistenza e File Exchange
il 15 Mar 2022
il 31 Mag 2023
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!
Translated by 
