# Perform a calculation using data from multiple tables

1 view (last 30 days)
Mark Maders on 27 Jul 2021
Commented: Peter Perkins on 29 Jul 2021
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.
##### 2 CommentsShowHide 1 older comment
Mark Maders on 27 Jul 2021
% ------------------------- Energy Calculation ---------------------------
% ------------------------------------------------------------------------
% % import fleet excel spreadsheet
% 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.

Peter Perkins on 27 Jul 2021
Mark, have you tried using join?
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.Airport = erase(T_Distance.Airport,"'")
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;
Peter Perkins on 29 Jul 2021
Agreed.

Simon Chan on 27 Jul 2021
Try the following code to see whether it can accommodate into your work:
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.

dpb on 27 Jul 2021
You can probably most simply just augment your flight table...something like--
% preparations
tDist.Airport=categorical(strrep(tDist.Airport,'''',''));
tFuel.AircraftType=categorical(tFuel.AircraftType);
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...

R2021a

### Community Treasure Hunt

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

Start Hunting!