How to convert time column from hours to decimal days to HH:MM:SS
조회 수: 3 (최근 30일)
이전 댓글 표시
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!
댓글 수: 0
채택된 답변
Star Strider
2020년 12월 6일
편집: Star Strider
2020년 12월 6일
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
.
댓글 수: 0
추가 답변 (1개)
Cris LaPierre
2020년 12월 6일
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"
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"
댓글 수: 0
참고 항목
카테고리
Help Center 및 File Exchange에서 String Parsing에 대해 자세히 알아보기
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!