Perform a calculation using data from multiple tables

I have a table of flight data, with each row containing information including destination airport and aircraft type used. I have two extra tables; the first table holds the distances from the origin airport to multiple destinations, and the second table containing values of fuel consumption for specific aircraft types.

I need my code to look at the first row of the flight data table, find the destination and aircraft type, and then give the product of the distance to destination and fuel consumption for the aircraft type. I will need the code to do this for every row and store each result.

I have attached the three excel sheets I shall be using to help clarify the question.

[dpb]

So what have you done so far and where did you have a MATLAB Q?

[Mark Maders]

% ------------------------- Energy Calculation ---------------------------
% ------------------------------------------------------------------------

% % import fleet excel spreadsheet
T_Distance = readtable('Airport distances.xlsx');
T_Fuel = readtable('Aircraft Fuel Consumption.xlsx');

% kWh per kg of kerosene
Kerosene = 12.67;
% efficiency of turboprop aircraft
PropEff = 0.5*0.98*0.8;
% efficiency of electric aircraft
AircraftEff = 0.98*0.95*0.98*0.8;

% energy requirement of aircraft
AircraftKWH = (Kerosene*PropEff*T_Fuel.kg_kmOfFuel)/AircraftEff;
AircraftKWH = array2table(AircraftKWH, 'VariableNames',{'AircraftKWH_km'});
T_Fuel = [T_Fuel AircraftKWH];

This is a section of my code. The 'flight data' excel sheet I provided is a very simplified version of what I am actually using. I was hoping to find a simplified answer so I could then accomodate it to fit my work.

Answer 1

[Peter Perkins]

Mark, have you tried using join?

t = readtable("flight data.xlsx","TextType","string")
t = join(t,T_Distance,"LeftKey","Destination","RightKey","Airport")
t = join(t,T_Fuel,"Keys","AircraftType")
t.Result = t.Distance_km_ .* t.kg_kmOfFuel

I also recommend that you import as strings, not as cell arrays of char rows, your life will be easier. And remove those extra quotes!

T_Distance = readtable('Airport distances.xlsx',"TextType","string")
T_Distance.Airport = erase(T_Distance.Airport,"'")
T_Fuel = readtable('Aircraft Fuel Consumption.xlsx',"TextType","string");

Also, I would replace the three lines under

% energy requirement of aircraft

with just

T_Fuel.AircraftKWH_km = (Kerosene*PropEff*T_Fuel.kg_kmOfFuel)/AircraftEff;

[dpb]

"Mark, have you tried using join?" @Peter Perkins

Great minds and all that!!! <VBG>

[Mark Maders]

This is exactly what I'm after, thank you both!

[dpb]

"recommend that you import as strings, not as cell arrays of char rows..." @Peter Perkins

I wonder, re: the suggestion Peter--do mean to recommend string over categorical or just as a better alternative than the cellstr? I would think excepting for the airport name, the rest are naturally categorical.

[Peter Perkins]

Agreed.

Answer 2

[Simon Chan]

Try the following code to see whether it can accommodate into your work:

flight = readtable('flight data.xlsx');
distance = readtable('Airport distances.xlsx');
fuel = readtable('Aircraft Fuel Consumption.xlsx');
num_flight = size(flight,1);
AircraftType = repmat({string(fuel.AircraftType)'},num_flight,1);
flightTypeidx = cellfun(@(x,y) strcmp(x,y),flight.AircraftType,AircraftType,'UniformOutput',false);
AirportDistance = repmat({string(strrep(distance.Airport, '''', ''))'},num_flight,1);
flightDistanceidx = cellfun(@(x,y) strcmp(x,y),flight.Destination,AirportDistance,'UniformOutput',false);
%
fuel_used = cell2mat(flightTypeidx)*fuel.kg_kmOfFuel;
distance_travel = cell2mat(flightDistanceidx)*distance.Distance_km_;
Consumption = fuel_used.*distance_travel;

fuel_used for each flight:

fuel_used =

   1.301204819277108
   1.569014084507042
   2.980281690140845
   1.569014084507042
   1.301204819277108
   1.569014084507042
   1.301204819277108
   1.569014084507042
   2.980281690140845

distance_travel for each flight:

   distance_travel =

    41
   116
   253
   482
    41
   116
   224
   355
   253

Consumption:

   Consumption =

   1.0e+02 *

   0.533493975903614
   1.820056338028169
   7.540112676056339
   7.562647887323943
   0.533493975903614
   1.820056338028169
   2.914698795180723
   5.570000000000000
   7.540112676056339

You may need to take care of the unit yourself.

Answer 3

[dpb]

You can probably most simply just augment your flight table...something like--

% preparations
tDist=readtable('Airport distances.xlsx');
tDist.Airport=categorical(strrep(tDist.Airport,'''',''));
tFuel=readtable('Aircraft Fuel Consumption.xlsx');
tFuel.AircraftType=categorical(tFuel.AircraftType);
tFlight=readtable('flight data.xlsx');
tFlight.Destination=categorical(tFlight.Destination);
tFlight.AircraftType=categorical(tFlight.AircraftType);
% the engine
tFlight=join(tFlight,tFuel);
tFlight=join(tFlight,tDist,'LeftKeys','Destination','RightKeys','Airport');
tFlight.FuelConsumption=tFlight.kg_kmOfFuel.*tFlight.Distance_km_;

results in

>> tFlight
tFlight =
  9×7 table
    MovementType    AircraftType    Destination                 AirportName                 kg_kmOfFuel    Distance_km_    FuelConsumption
    ____________    ____________    ___________    _____________________________________    ___________    ____________    _______________
       {'D'}            DHC6            PIK        {'PRESTWICK,SCOTLAND, UK'           }       1.30            41.00            53.35     
       {'D'}            SF34            ILY        {'ISLAY IS, (PT ELLEN) ARG SCOT, UK'}       1.57           116.00           182.01     
       {'D'}            E145            BEB        {'BENBECULA IS, O.HEBRS SCOTLAND'   }       2.98           253.00           754.01     
       {'D'}            SF34            LSI        {'SUMBURGH, SHETLAND IS, UK'        }       1.57           482.00           756.26     
       {'A'}            DHC6            PIK        {'PRESTWICK,SCOTLAND, UK'           }       1.30            41.00            53.35     
       {'A'}            SF34            ILY        {'ISLAY IS, (PT ELLEN) ARG SCOT, UK'}       1.57           116.00           182.01     
       {'D'}            DHC6            BRR        {'BARRA IS, O.HEBRS  SCOTLAND'      }       1.30           224.00           291.47     
       {'D'}            SF34            KOI        {'KIRKWALL, UK'                     }       1.57           355.00           557.00     
       {'A'}            E145            BEB        {'BENBECULA IS, O.HEBRS SCOTLAND'   }       2.98           253.00           754.01     
>> 

I'll let you fixup the variable names and units...

I'd suggest using the import options object to import the various variables as categorical and also strip the extraneous single quotes, etc., etc,. etc., instead of having to do the cleanup afterwards. See the documentation for detectImportOptions for all the skinny on that...