MATLAB Answers

Perform a calculation using data from multiple tables

1 view (last 30 days)
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 Comments
Mark Maders
Mark Maders on 27 Jul 2021
% ------------------------- 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.

Sign in to comment.

Accepted Answer

Peter Perkins
Peter Perkins on 27 Jul 2021
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;
  4 Comments

Sign in to comment.

More Answers (2)

Simon Chan
Simon Chan on 27 Jul 2021
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.

dpb
dpb on 27 Jul 2021
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...

Products


Release

R2021a

Community Treasure Hunt

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

Start Hunting!