Return the last time in a datetime column containing NaT

3 次查看（过去 30 天）
Numbers
Numbers 2024-3-19
评论： Stephen23 2024-3-19
Ran in:
Hi, I'm trying to get the value of the last recognised time entry in an imported excel column of data. At a point, the column changes from datetime values to NaT values, I think due to some of the other columns being longer than my datetime column. It's not practical to edit the excel file to use a simple function. I have tried using find with ~isnat,but I'm not sure I'm applying it correctly. See code below and sample data attached. Any help is appreciated!
ReadSS = readtable('2columns.xlsx','Sheet','Trend Data')
Warning: Column headers from the file were modified to make them valid MATLAB identifiers before creating variable names for the table. The original column headers are saved in the VariableDescriptions property.
Set 'VariableNamingRule' to 'preserve' to use the original column headers as table variable names.
ReadSS = 28981×2 table
DateAndTime value _________________________________ _____ {'08/02/2024 07:40:00.000000 PM'} 0 {'08/02/2024 07:40:00.100000 PM'} 0.04 {'08/02/2024 07:40:00.200000 PM'} 0.08 {'08/02/2024 07:40:00.300000 PM'} 0.12 {'08/02/2024 07:40:00.400000 PM'} 0.16 {'08/02/2024 07:40:00.500000 PM'} 0.2 {'08/02/2024 07:40:00.600000 PM'} 0.24 {'08/02/2024 07:40:00.700000 PM'} 0.28 {'08/02/2024 07:40:00.800000 PM'} 0.32 {'08/02/2024 07:40:00.900000 PM'} 0.36 {'08/02/2024 07:40:01.000000 PM'} 0.4 {'08/02/2024 07:40:01.100000 PM'} 0.44 {'08/02/2024 07:40:01.200000 PM'} 0.48 {'08/02/2024 07:40:01.300000 PM'} 0.52 {'08/02/2024 07:40:01.400000 PM'} 0.56 {'08/02/2024 07:40:01.500000 PM'} 0.6
TimeCol = datetime(ReadSS{:,1}, 'InputFormat', 'dd/MM/yyyy hh:mm:ss.SSSSSS a ''', 'TimeZone', 'UTC'); %identify the format of time and date in excel column 1
TimeCol.Format = 'dd/MM/uuuu HH:mm:ss.SSSSSS'; %Set the format of the time data in matlab
first_ele=TimeCol(1,:) % first value
first_ele = datetime
08/02/2024 19:40:00.000000
last_ele=TimeCol(end,:) %last value
last_ele = datetime
NaT
last_ele= find(~isnat(TimeCol(end,:)))
last_ele = []

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

采纳的回答

Dyuman Joshi
Dyuman Joshi 2024-3-19
Ran in:
Your data is already a column, using 1 and end as indices on it will provide scalars (see the edit above).
ReadSS = readtable('2columns.xlsx','Sheet','Trend Data')
Warning: Column headers from the file were modified to make them valid MATLAB identifiers before creating variable names for the table. The original column headers are saved in the VariableDescriptions property.
Set 'VariableNamingRule' to 'preserve' to use the original column headers as table variable names.
ReadSS = 28981×2 table
DateAndTime value _________________________________ _____ {'08/02/2024 07:40:00.000000 PM'} 0 {'08/02/2024 07:40:00.100000 PM'} 0.04 {'08/02/2024 07:40:00.200000 PM'} 0.08 {'08/02/2024 07:40:00.300000 PM'} 0.12 {'08/02/2024 07:40:00.400000 PM'} 0.16 {'08/02/2024 07:40:00.500000 PM'} 0.2 {'08/02/2024 07:40:00.600000 PM'} 0.24 {'08/02/2024 07:40:00.700000 PM'} 0.28 {'08/02/2024 07:40:00.800000 PM'} 0.32 {'08/02/2024 07:40:00.900000 PM'} 0.36 {'08/02/2024 07:40:01.000000 PM'} 0.4 {'08/02/2024 07:40:01.100000 PM'} 0.44 {'08/02/2024 07:40:01.200000 PM'} 0.48 {'08/02/2024 07:40:01.300000 PM'} 0.52 {'08/02/2024 07:40:01.400000 PM'} 0.56 {'08/02/2024 07:40:01.500000 PM'} 0.6
%You can include the output format in datetime() call
TimeCol = datetime(ReadSS{:,1}, 'InputFormat', 'dd/MM/yyyy hh:mm:ss.SSSSSS a ''', 'TimeZone', 'UTC', ...
'Format', 'dd/MM/uuuu HH:mm:ss.SSSSSS'); %identify the format of time and date in excel column 1
%Set the format of the time data in matlab
Specify the direction of search i.e. last in the find() call -
%Find the last not-a-Time value in the given column data
last_ele= find(~isnat(TimeCol), 1, 'last')
last_ele = 12000
  6 个评论
显示 4更早的评论
Voss
Voss 2024-3-19
I see what you're saying about the 12000 datetimes. Nevertheless, NaT is also a datetime, and in a table all columns must be the same length, so technically there are 28981 datetimes in column 1.
You should accept Dyuman Joshi's answer, as my response was merely a follow-up comment.
Stephen23
Stephen23 2024-3-19
Ran in:
"I think it's 12000 datetime... with the rest of the column importing as NaT"
As Voss correctly wrote, NaT are also DATETIME objects. This is very easy to confirm:
isdatetime(NaT)
ans = logical
1

请先登录，再进行评论。

更多回答（0 个）

类别

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

标签

产品


版本

R2023b

Community Treasure Hunt

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

Start Hunting!

Translated by