How to convert time column from hours to decimal days to HH:MM:SS

3 vues (au cours des 30 derniers jours)
emily bristow
emily bristow le 6 Déc 2020
Modifié(e) : Star Strider le 6 Déc 2020
I need to convert the first column in the excel sheet to HH:MM:SS, this is so I can plot a time series for surface water (Column B) and bottom water (Column C) temperatures.
There's two excel sheets for the years 2014 and 2015.
3 columns of data: time (days since January 1st), surface temperature, near-bed temperature
I'm not sure where to start!

Réponse acceptée

Star Strider
Star Strider le 6 Déc 2020
Modifié(e) : Star Strider le 6 Déc 2020
Try this for the 2014 file (do essentially the same for any others), noting that with this format for ‘filename’, the code will automatically extract the years:
filename = 'ccs_temperatures2014.xlsx';
T1 = readtable(filename, 'VariableNamingRule','preserve');
fileyear = regexp(filename, '\d*', 'match');
fyear = str2double(fileyear{:});
dn = datenum([repmat([fyear 0 0], size(T1,1), 1) T1{:,1} zeros(size(T1,1),2)]);
Col1 = datetime(dn, 'ConvertFrom','datenum', 'Format','HH:mm:ss');
T2 = table(Col1);
T2 = [T2,T1(:,2:end)];
T2.Properties.VariableNames = {'Time (HH:mm:ss:)','Surface Temperature (°C)', 'Bottom Temperature (°C)'};
that with:
FirstFiveRows = T2(1:5,:)
produces:
FirstFiveRows =
5×3 table
Time (HH:mm:ss:) Surface Temperature (°C) Bottom Temperature (°C)
________________ ________________________ _______________________
13:00:00 9.5343 9.4499
13:00:12 9.5223 9.4494
13:00:24 9.5344 9.4493
13:00:37 9.5315 9.4489
13:00:50 9.5251 9.448
The dates and times were (again) the problem, however with a bit of creativity, that was relatively easily dealt with.
Here, only the 'HH:mm:ss' displays, although the full date information is retained internally.
EDIT — (6 Dec020 at 21:05)
I misread it the first time.
Change to:
dn = datenum([repmat([fyear 0], size(T1,1), 1) T1{:,1} zeros(size(T1,1),3)]);
and then:
FirstFiveRows = T2(1:5,:)
produces:
FirstFiveRows =
5×3 table
Time (HH:mm:ss:) Surface Temperature (°C) Bottom Temperature (°C)
________________ ________________________ _______________________
00:00:00 9.5343 9.4499
00:05:02 9.5223 9.4494
00:09:56 9.5344 9.4493
00:14:58 9.5315 9.4489
00:20:00 9.5251 9.448
.

Plus de réponses (1)

Cris LaPierre
Cris LaPierre le 6 Déc 2020
I thought of 2 ways to do it.
1. Treat the times in first column as durations in days, and add it to Jan 0, 2014
opts = detectImportOptions("ccs_temperatures2014.xlsx");
opts.VariableNames = ["Time","sTemp","nbTemp"];
opts = setvartype(opts,"Time","duration");
opts = setvaropts(opts,"Time","DurationFormat","d");
data = readtable("ccs_temperatures2014.xlsx",opts);
data.Time = data.Time + datetime(2014,01,0);
data.Time.Format = "HH:mm:ss"
data = 80306x3 table
Time sTemp nbTemp ________ ______ ______ 00:00:00 9.5343 9.4499 00:05:02 9.5223 9.4494 00:09:56 9.5344 9.4493 00:14:58 9.5315 9.4489 00:20:00 9.5251 9.448 00:25:03 9.525 9.4478 00:29:57 9.5271 9.4472 00:34:59 9.5248 9.4468 00:40:01 9.5166 9.4465 00:44:55 9.5209 9.4446 00:49:58 9.516 9.4448 00:55:00 9.5177 9.4444 01:00:02 9.5149 9.4423 01:04:56 9.513 9.442 01:09:59 9.5042 9.4415 01:15:01 9.5081 9.4404
2. Import using the 'excel1904' format, and add the appropriate number of years to get it to the years of the file.
opts = detectImportOptions("ccs_temperatures2014.xlsx");
opts.VariableNames = ["Time","sTemp","nbTemp"];
data = readtable("ccs_temperatures2014.xlsx",opts);
data.Time = datetime(data.Time,'ConvertFrom',"excel1904") + calyears(110);
data.Time.Format = "HH:mm:ss"
data = 80306x3 table
Time sTemp nbTemp ________ ______ ______ 00:00:00 9.5343 9.4499 00:05:02 9.5223 9.4494 00:09:56 9.5344 9.4493 00:14:58 9.5315 9.4489 00:20:00 9.5251 9.448 00:25:03 9.525 9.4478 00:29:57 9.5271 9.4472 00:34:59 9.5248 9.4468 00:40:01 9.5166 9.4465 00:44:55 9.5209 9.4446 00:49:58 9.516 9.4448 00:55:00 9.5177 9.4444 01:00:02 9.5149 9.4423 01:04:56 9.513 9.442 01:09:59 9.5042 9.4415 01:15:01 9.5081 9.4404

Community Treasure Hunt

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

Start Hunting!

Translated by