Loop to perform calculation from table data and store each result

3 views (last 30 days)
Mark Maders
Mark Maders on 12 Jul 2021
Edited: Cris LaPierre on 13 Jul 2021
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,:} = [];
% -----------------------------------------------------------------------
% --- 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};
% -----------------------------------------------------------------------

Accepted Answer

Cris LaPierre
Cris LaPierre on 12 Jul 2021
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,:) = []
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
Cris LaPierre
Cris LaPierre on 13 Jul 2021
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.

Sign in to comment.

More Answers (0)




Community Treasure Hunt

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

Start Hunting!