Loop to perform calculation from table data and store each result

I would like MATLAB to read a table from excel that contains travel arrival and depature data. From this data, I want to calculate the time between each arrival and the departure that follows. I wish to store each result in a new table. I have a very basic outline of what I am trying to achieve already.

I have another minor problem with the "remove first row if movement type is 'D'", with an error message stating:

Operator '==' is not supported for operands of type 'cell'.

Any help with these queries would be appreciated.

% import excel spreadsheet
T_Data = readtable('GGF1.xlsx');

% remove first row if movement type is 'D', else keep
if T_Data{1,1} == 'D'
     T_Data{1,:} = [];
end

% -----------------------------------------------------------------------

% --- This will have to be updated to loop entire dataset and store each  
% --- route time on ground. 
% --- Loop should do row2 - row1; row4 - row3; row6 - row5 etc. and save 
% --- each result in a new table

% take first route from the table for analysis
Route1 = T_Data(1:2,:);

% Determine time on ground for first route
OnGround = Route1{2,2} - Route1{1,2};

% -----------------------------------------------------------------------

Answer 1

[Cris LaPierre]

You are extracting arrays when you use curly braces. Use parentheses to keep the results in a table. See the Access Data in Tables documentation page.

MATLAB is determining the format of your first column to be cell. You can change this using setvartype.

% import excel spreadsheet
opts = detectImportOptions('GGF1.xlsx');
opts = setvartype(opts,"MovementType","categorical");
T_Data = readtable('GGF1.xlsx',opts);

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.

% remove first row if movement type is 'D', else keep
if T_Data.MovementType(1) == 'D'
     T_Data(1,:) = []
end

T_Data = 1075×3 table
    MovementType         Date_Time          Registration
    ____________    ____________________    ____________

         A          02-Jan-2020 13:24:00      {'GGF1'}  
         D          03-Jan-2020 08:55:00      {'GGF1'}  
         A          03-Jan-2020 10:53:00      {'GGF1'}  
         D          03-Jan-2020 12:31:00      {'GGF1'}  
         A          03-Jan-2020 15:10:00      {'GGF1'}  
         D          03-Jan-2020 16:26:00      {'GGF1'}  
         A          03-Jan-2020 17:49:00      {'GGF1'}  
         D          04-Jan-2020 11:04:00      {'GGF1'}  
         A          04-Jan-2020 12:59:00      {'GGF1'}  
         D          04-Jan-2020 13:50:00      {'GGF1'}  
         A          04-Jan-2020 16:18:00      {'GGF1'}  
         D          05-Jan-2020 10:27:00      {'GGF1'}  
         A          05-Jan-2020 13:01:00      {'GGF1'}  
         D          05-Jan-2020 14:51:00      {'GGF1'}  
         A          05-Jan-2020 16:53:00      {'GGF1'}  
         D          06-Jan-2020 10:15:00      {'GGF1'}  

% Determine time on ground for first route
OnGround = diff(T_Data.Date_Time)

OnGround = 1074×1 duration array
   19:31:00
   01:58:00
   01:38:00
   02:39:00
   01:15:59
   01:23:00
   17:15:00
   01:55:00
   00:51:00
   02:28:00
   18:09:00
   02:34:00
   01:50:00
   02:02:00
   17:22:00
   02:19:59
   01:03:00
   01:56:00
   01:04:00
   01:24:00
   39:02:00
   02:00:59
   05:19:00
   01:19:00
   19:10:00
   02:18:00
   01:13:00
   01:16:00
   15:14:00
   01:50:00

[Mark Maders]

Thank you for clearing up the first issue.

I should have been clearer with the time between routes; it must be the time between a row containing D (departure) and a row containing A (arrival). So, the solution you have suggested would not work as this returns the time between each row. Is there a possible solution for this?

[Cris LaPierre]

Details are helpful. Of course you can do it still, it just requires more coding. For example, you could use logical indexing to find the rows that are D and A, and take the difference. Maybe like this.

OnGround = T_Data.Date_Time(T_Data.MovementType=='D') - T_Data.Date_Time(T_Data.MovementType=='A')

This assumes your data has an equal number of Ds and As, and that the rows line up correctly.

[Mark Maders]

One final question. I have performed the calculation, now I wish to generate a table containing only the departure (D) rows. I have an error with the following code:

% New table with departure rows only
Departures = 'D';
Depart = contains(T_Data.MovementType,Departures); 
T_Departures = T_Data(Depart,:); 

The error says 'first arguement must be text' when using contains. I believe the opts at the beginning has altered the MovementType row so that it is no longer text; can I reverse this?

[Cris LaPierre]

I'd create the logical index and new table doing something like this (untested)

T_Departures = T_Data(T_Data.MovementType=='D',:

Contains looks for a pattern in a string. I think for your approach, ismember is more appropriate.