Perform a calculation using data from multiple tables
3 visualizzazioni (ultimi 30 giorni)
Mostra commenti meno recenti
Mark Maders
il 27 Lug 2021
Commentato: Peter Perkins
il 29 Lug 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 Commenti
Risposta accettata
Peter Perkins
il 27 Lug 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 Commenti
dpb
il 27 Lug 2021
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.
Più risposte (2)
Simon Chan
il 27 Lug 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.
0 Commenti
dpb
il 27 Lug 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...
0 Commenti
Vedere anche
Categorie
Scopri di più su Guidance, Navigation, and Control (GNC) in Help Center e File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!