How do I Subscript Duration Row times with Date Time values?

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,:)

[Paolo]

Can you please attach the .csv file?

Answer 1

[Xel Ch]

Sure, this should be it, thanks in advance!

[Paolo]

In your .csv file you have:

 Year                Date
        
        
2017  1  2  23:18:23  2017:01:02
2017  1  3  04:17:25  2017:01:03
2017  1  6  05:03:17  2017:01:06
2018  1  7  16:51:36  2018:01:07
2018  1  17  11:11:34  2018:01:17

Am I right in assuming that the correct format for the dates should be something like 2017/1/2 23:18:23 (first row) ?

[Xel Ch]

Yes, the date for that row would be 2017/1/2, time for the event 23:18:23. Then I moved the date all into the rightmost column with the header "Date". Sorry for any confusion.

[Xel Ch]

Just deleted the time data in my table because it is irrelevant to what I am trying to do right now, and fixed format of my code so that it catches everything. Here is the cleaned up version of my file and code if it is easier to work with. After running it this way, I am now getting the error, "Row index exceeds table dimensions" on line 7 (last line of the code).

T = readtable('QuakeTrials.csv');
TT = table2timetable(T);
TT(3:7,:)

TR = timerange('2017:01:01','2017:12:30');
TT2 = TT(TR,6);
TT2(4:8,:)

[Paolo]

Right, I am assuming that '2017:01:01' is 'yyyy:mm:dd'. I have submitted an answer for the updated QuakeTrials.csv below, hope that it solves the problem.

Answer 2

[Walter Roberson]

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)

[Walter Roberson]

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.

Answer 3

[Paolo]

 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

Answer 4

[Xel Ch]

Hi Paolo, thanks for the suggestion. I am still getting an error on the line starting with "T.Date", which says

Error using cellfun
Input #2 expected to be a cell array, was duration instead.

Any tips for getting through this?

[Paolo]

Works fine on Matlab2017b... try changing that line to:

 T.Date = datetime(T.Date,'InputFormat','yyyy:mm:dd','Format','dd/mm/yyyy')

P.s. please submit comments and not answers when replying to someone.

[Xel Ch]

Sorry about that, still new to asking questions on here. I am working with 2018a, so issue might be a change in formatting? I just tried the new line you sent me, capitalized the mm to MM in output because that was causing a small issue, and am given another error for that line:

Input data must be a numeric array, a string array, a cell array
containing character vectors, or a char matrix.

Would something like the Datestring function work to correct this? Thank you!

[Paolo]

No worries at all. Right, from your previous comment the input is actually of type duration, so please ignore my previous comment.

Could you put a breakpoint at the line T.Date ... and show what T.Date is? Perhaps the behavior of readtable has changed in 2018a and it no longer reads T.Date as a cell array but as a duration array.

[Walter Roberson]

I showed how to handle that in my response.

dt = datetime(T{:,1}, T{:,2}, T{:,3}) + T{:,4};

[Xel Ch]

I do not think I was able to successfully use a breakpoint as I am still a beginner at Matlab, but did try class(T.Date) and Matlab returned 'duration' in the command window. Does that help at all?

I also tried ischar(), illogical(), iscellstr(), and a few other data identifiers, but Matlab returned '=0' for all of those.

[Walter Roberson]

duration is a datetype that is a relative amount of clock time. You can process the durations the way I show -- T{:,4} is the duration column, and you can add it to a datetime created from the year, month, day from the first three columns.

[Paolo]

@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')

[Walter Roberson]

Looks like R2018a added duration support. I have updated my answer.

Answer 5

[Peter Perkins]

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