How do I Subscript Duration Row times with Date Time values?
7 views (last 30 days)
Show older comments
Hello,
I am plotting some data from a .csv file, one year at a time. When I select a certain time range, I am given the error, "A timetable with duration row times cannot be subscripted using datetime values."
I am attaching the code below, and it says the error is in the sixth line (TT2 = TT(TR,6);). Any help is appreciated, thanks!
[
T = readtable('QuakeTrials.csv');
TT = table2timetable(T);
TT(3:7,:)
TR = timerange('2017-01-01','2017-12-31');
TT2 = TT(TR,6);
TT2(3:7,:)
Answers (5)
Walter Roberson
on 19 Jun 2018
Edited: Walter Roberson
on 19 Jun 2018
T = readtable('QuakeTrials.csv');
T = T(3:end,:);
dt = datetime(T{:,1}, T{:,2}, T{:,3}) + T{:,4};
TT = table2timetable(T, 'rowtimes', dt);
TR = timerange('2017-01-01','2017-12-31');
TT_lat = TT(TR,6)
1 Comment
Walter Roberson
on 19 Jun 2018
In R2018a the above code produces
TT_lat =
3×1 timetable
Time Latitude
____________________ ________
02-Jan-2017 23:18:23 46.59
03-Jan-2017 04:17:25 45.81
06-Jan-2017 05:03:17 45.13
In earlier versions you might need to do
filename = 'QuakeTrials.csv';
opt = detectImportOptions(filename);
opt = setvartype(opt, 4, 'datetime');
T = readtable(filename, opt);
%do not subselect T(3:end,:) here as detectImportOptions already skips it
dt = datetime(T{:,1}, T{:,2}, T{:,3}) + (T{:,4} - dateshift(T{:,4},'start','day'));
TT = table2timetable(T, 'rowtimes', dt);
TR = timerange('2017-01-01','2017-12-31');
TT_lat = TT(TR,6)
Tested in R2017b.
Paolo
on 19 Jun 2018
Edited: Paolo
on 19 Jun 2018
opts = detectImportOptions('QuakeTrials.csv');
opts.MissingRule = 'omitvar';
T = readtable('QuakeTrials.csv',opts);
T.Date = cellfun(@(x) datetime(x,'InputFormat','yyyy:mm:dd','Format','dd/mm/yyyy'),T.Date);
TT = table2timetable(T);
TR = timerange('01/02/2017','30/12/2017');
TT2 = TT(TR,6);
TT2 =
3×1 timetable
Date Var13
__________ _____
02/01/2017 1.6
03/01/2017 2.1
06/01/2017 1.4
0 Comments
Xel Ch
on 19 Jun 2018
8 Comments
Paolo
on 19 Jun 2018
@Walter The behavior for readtable must have changed then because I am unable to run your solution. T{:,2} and T{:,3} are in fact NaN.
Error in game (line 266)
dt = datetime(T{:,1}, T{:,2}, T{:,3}) + T{:,4};
Parameter name must be text.
@Alexander You can place a breakpoint by clicking on the gray vertical line of the editor, a red circle will appear. Yes, T.Date is definitely of type duration. Have you tried using the solution proposed by Walter?
Alternatively you can try:
T.Date = char(duration(T.Date,'Format','hh:mm:ss'))
T.Date = datetime(T.Date,'InputFormat','yyyy:mm:dd','Format','dd/mm/yyyy')
Peter Perkins
on 5 Jul 2018
I'm coming to this thread late, so I may be repeating what Walter and Paolo already sorted out. It looks like the root cause was that in R2018a, readtable began supporting directly reading durations as well as datetimes. And because the "time" stamps precede the "date" stamps in the file, this line from the table2timetable doc
"The first datetime or duration variable in T becomes TT's time vector"
explains what happened. Walter's suggestion seems right, although I would have used
dt = T.Date + T.Var4; % csv is missing some col headings
By the way, it's easy to switch a timetable back and forth between absolute and relative time: just add or subtract an offset:
>> tt = timetable([1;2;3],'RowTimes',datetime('now') + minutes(0:2))
tt =
3×1 timetable
Time Var1
____________________ ____
05-Jul-2018 10:33:50 1
05-Jul-2018 10:34:50 2
05-Jul-2018 10:35:50 3
>> t1 = tt.Time(1)
t1 =
datetime
05-Jul-2018 10:33:50
>> tt.Time = tt.Time - t1
tt =
3×1 timetable
Time Var1
________ ____
00:00:00 1
00:01:00 2
00:02:00 3
>> tt.Time = tt.Time + t1
tt =
3×1 timetable
Time Var1
____________________ ____
05-Jul-2018 10:33:50 1
05-Jul-2018 10:34:50 2
05-Jul-2018 10:35:50 3
0 Comments
See Also
Categories
Find more on Dates and Time in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!