# Perform a calculation using data from multiple tables

조회 수: 1(최근 30일)
Mark Maders 2021년 7월 27일
댓글: Peter Perkins 2021년 7월 29일
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표시숨기기 이전 댓글 수: 1
Mark Maders 2021년 7월 27일
% ------------------------- 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.

댓글을 달려면 로그인하십시오.

### 채택된 답변

Peter Perkins 2021년 7월 27일
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표시숨기기 이전 댓글 수: 3
Peter Perkins 2021년 7월 29일
Agreed.

댓글을 달려면 로그인하십시오.

### 추가 답변(2개)

Simon Chan 2021년 7월 27일
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표시숨기기 이전 댓글 수: -1

댓글을 달려면 로그인하십시오.

dpb 2021년 7월 27일
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!

Translated by