Readtable not reading time as expected

46 次查看（过去 30 天）
Jakob Sievers
Jakob Sievers 2023-11-16
评论： Jakob Sievers 2023-11-22
Hi there
I have run into a puzzling mishap by readtable. I'm running it as such::
T = readtable('file.data','FileType','text','NumHeaderLines',7);
Here is a cropped screenshot of the dataset I'm running it on (headerlines not shown):
I am specifically puzzled by the last column shown. This is the time of day. In the resulting table it comes back looking like this:
So it seems readtable has misinterpreted what is a time signature as a duration value. And that it is only able to determine this duration value when the third column (a nanosecond value) cycles back to zero.
Any ideas as to how I might resolve this problem?
Thanks in advance!
  2 个评论
Jon
Jon 2023-11-16
Someone may be able to help you just by looking at your screenshot, but it would be helpful for me if you could attach your data file so I could try to import it myself and see what is going on.

请先登录，再进行评论。

请先登录，再回答此问题。

采纳的回答

Jon
Jon 2023-11-16
编辑：Jon 2023-11-16
I think you are having problems because your time column is of the form 19:10:54:300 and in particular the last separator is a colon not a decimal point. I couldn't find a simple way of dealing with this as it seems all of the MATLAB time formats use a decimal fraction for the last portion.
Here is some code that I wrote to handle this. Probably could be streamlined but I think it does what you want
filename = '2022-08-29T191054_AIU-2371.data'
% Define import options
opts = detectImportOptions(filename,"FileType","text")
% Modify options to have time column read in as a character type for further
% manipulation
opts = setvaropts(opts,'Time','Type','char')
% Read in the table using the customized options
T = readtable('2022-08-29T191054_AIU-2371.data',opts);
% Replace milliseconds with fractions of a second to allow using MATLAB time
% formatting
T.Time = cellfun(@fun,T.Time,'UniformOutput',false);
% Convert to duration
T.Time = duration(T.Time,'InputFormat','hh:mm:ss.SSSSSSSSS')
function t = fun(tChar)
% Replace nanoseconds with fractions of a second to allow using MATLAB time
% formatting
parts = strsplit(tChar,':');
fsec = str2double(parts{4})/1e9; % fraction of second
parts{4} = num2str(fsec,'%.9f'); % e.g '0.000000300'
parts{4} = parts{4}(2:end); % e.g. '.000000300'
% build new character vector with decimal time
t = [parts{1},':',parts{2},':',parts{3},parts{4}];
end
  9 个评论
显示 7更早的评论
Jakob Sievers
Jakob Sievers 2023-11-17
Thank you very much for taking your time to look deeper into this question. I'm glad we both learned something from this :-)
Jakob Sievers
Jakob Sievers 2023-11-22
I finally had a moment to test this out and it did the job splendedly. Thanks again. I will accept this as the answer :)

请先登录，再进行评论。

更多回答（1 个）

Walter Roberson
Walter Roberson 2023-11-16
Use detectImportOptions() on the file. Then use setvartype() to set variable 8 to datetime instead of duration. Then use setvaropts() to set the InputFormat to 'HH:mm:ss:SSS'
Now readtable() the file passing in the modified options.
Variables 7 and 8 will now both be datetime.
Take
day_offset = TheTable{:,8} - dateshift(TheTable{:,8}, 'start', 'day');
TheTable.DateTime = TheTable{:,7} + day_offset;
Now TheTable.DateTime variable will hold the full-precision date and time together.
Yes, there are other ways, including passing a format to readtable() indicating what the datetype is for each column... but using %D and %T properly gets a bit messy. And in order for a time format to not automatically end at the space between date and time, you have to do the hack of telling the parser that whitespace is not a field delimiter... gets ugly.
  4 个评论
显示 2更早的评论
Jakob Sievers
Jakob Sievers 2023-11-17
Thank you so much for looking into this question! I'll explore all answers as soon as I can!
Peter Perkins
Peter Perkins 2023-11-17
Yes, the root cause here is that reading in duration text timestamps in the format hh:mm:ss:SSS is not currently supported. So Walter is correct, use datetime's more flexible parsing, and Chris is right, use timeofday to convert those to durations.
I have made note of adding support for this duration format, it's something we've had questions about before.

请先登录，再进行评论。

类别

在 Help Center 和 File Exchange 中查找有关 Calendar 的更多信息

标签

产品


版本

R2021a

Community Treasure Hunt

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

Start Hunting!

Translated by