How do I read a block of data from excel sheet and store it in an array?

I have ADS-B flight data in an excel sheet. The sheet has groups of flights with corresponding lat, lon. The number of data points for each of the flights are not uniform, each flight has a varying number of data points associarted with it Example flight 1 might have 30 points while flight 2 might have just 10). I want to group all the data for each flight in an array and send it to a function one flight at a time. Could someone help me with the code for this?

I'm attaching the excel file for the same here.

[dpb]

No file attached...

[harman bhaveja]

Sorry for that, I have attached it now.

Answer 1

[per isakson]

This solution uses a containers.Map rather than a cell array because I think there might be gaps in the flight_list. See containers.Map, Object that maps values to unique keys

%%
T = readtable('plot.xlsx', 'ReadVariableNames',true );
flight_list = reshape( unique(T.flight), 1,[] );
flight_data = containers.Map('KeyType','double','ValueType','any');
for jj = flight_list
    flight_data(jj) = T{ T.flight==jj, : };
end

read data for flight 12

>> flight_data( 12 )
ans =
           12           15       11.133       79.362        35025
           12           15       11.162       79.392        35000
           12           15       11.184       79.415        34825
           12           15       11.207       79.439        34600
           12           15       11.215       79.448        34500
           12           15       11.252       79.485        34150
           ...               

Added later:

>> flight_data = cssm( 'plot.xlsx' );

where

function    flight_data = cssm( ffs )
    %%
    T = readtable( ffs, 'ReadVariableNames',true );
    flight_list = reshape( unique(T.flight), 1,[] );
    placeholder = flight_list;
    flight_data = containers.Map( flight_list, placeholder, 'UniformValues', false );
    for key = flight_list
        flight_data(key) = T{ T.flight==key, : };
    end
end

[harman bhaveja]

When I pass flight_data( 1220 ) I get an error...

it says

Error using containers.Map/subsref

The specified key is not present in this container.

When I passed checked >> flight_data

I got this:

Count: 816

KeyType: double

ValueType: any

But I have 1220 flights

[per isakson]

I fail to reproduce the error that you show.

I copied from my answer and executed

>> %%
T = readtable('plot.xlsx', 'ReadVariableNames',true );
flight_list = reshape( unique(T.flight), 1,[] );
flight_data = containers.Map('KeyType','double','ValueType','any');
for jj = flight_list
    flight_data(jj) = T{ T.flight==jj, : };
end
>> flight_data
flight_data = 
  Map with properties:
        Count: 1128
      KeyType: double
    ValueType: any
>> flight_data(1220)
ans =
         1220          767       13.822       75.382        36000
         1220          767       13.846       75.362        36000
         1220          767       13.857       75.353        36000
         1220          767       13.857       75.353        36000
         1220          767       13.857       75.353        36000
         1220          767       13.857       75.353        36000
>> flight_data(1215)
Error using containers.Map/subsref
The specified key is not present in this container. 
>> 

There are 1128 unique flight numbers in plot.xlsx. 92 numbers are missing from the sequence 1:1220, e.g. 1215. To verify this, run

find( not( ismember( 1:1220, flight_list ) ) )

Copy&paste from plot.xlsx

[per isakson]

You sent me a question:

"You said flight_data(12) will give me the table, but if I were to pass a particular column into a function or show output of it, how do I do it?"

No I didn't say "table". In natural English table means something with rows and columns. In Matlab it's a special data type. flight_data(12) actually returns an ordinary matrix of doubles.

Some kind of clever indexing like

>> flight_data(12)(:,3)
Error: ()-indexing must appear last in an index expression. 

isn't possible. One has to do it in steps

>> m = flight_data(12);
>> col3 = m(:,3);
>> mean( col3 )     % pass a particular column into a function 
ans =
        13.33

[harman bhaveja]

Got it, Thanks!

Answer 2

[KSSV]

YOu may proceed like this:

clc ; clear all ;
T = readtable('plot.xlsx') ;
N = length(unique(T.(1))) ;
% loop for each flight 
for i = 1:N
    idx = T.(1)==i ;
    T(idx,:)
end

YOu can access the column you want using T.(1), T.(2) etc. You may also use xlsread as below:

[num,txt,raw] = xlsread('plot.xlsx') ;

N = length(unique(num(:,1))) ;
% loop for each flight 
for i = 1:N
    idx = num(:,1)==i ;
    num(idx,:)
end

[harman bhaveja]

Thanks a lot!

I want to pass the complete flight data (of a particular flight, say flight number x) into a function (like the part of the table). So, which variable is it that I will have to send? Num?

[KSSV]

I have given two options table and a matrix.......you have to decide.

[harman bhaveja]

So, if I were to use the first code, I should put the function within the for loop right? sending sau T.(1), T.(2) etc?

like:

for i = 1:N
    idx = T.(1)==i ;
    T(idx,:)
    checkingcode(T.(1))
end

[KSSV]

T.(1) gives flight numbers.........