I am looking to create a benchmarking tool for airport flights for personal use.
I have airport data where each row is a different flight (See attached photo of example data structure in excel). I have this data for many airports all in the one data set, where the airport names are given as Orig and Dest (origin and destination of the flight). I wish to create a table that produces a number of variables for each airport. Each airport has a number of departing seats, arriving seats, and total seats (arr + dep) for each hour, day, week etc. if the airport is the "Orig" of the flight, the seats represent a departing flight. Whereas if the airport is the "Dest" of the flight, the seats are considered arriving seats.
The following variables must be produced each year for each airport:
- Departure Peak Month Seats: the number of departure seats in the busiest month of year when summing up departure seats.
- Departure Peak Month: The month in which this occurred.
- Departure Peak Month Ratio: The ratio of the peak month seats to the number of seats in the whole year
-Departure Design Day Seats: Defined as "the 2nd busiest day of the average week of the peak month". You work out how many 7-day weeks there are in the peak month, starting on a monday, and find the average number of departure seats across that month in those weeks. The week that is closest in seats to the average, i need the number of seats on the 2nd busiest day.
- Departure Design Day: the day of the week and date of the design day
- Departure Design day ratio: The ratio of the design day seats to the seats in the whole month
- Departure Peak Hour Seats: the number of departure seats in busiest hour of the design day. This is done by taking each flight and summing the number of departing seats for the airport in the next 59 minutes, including the current flight. The departure peak hour seat number is just the maximum value of this during the day
- Departure Peak Hour Seats ratio: the ratio of seats in the departure peak hour to the number of seats on the departure design day
For each airport, this needs to be done for arriving seats and total seats as well
Hi, I am Ibrahim and I am a data scientist, I can help you with tableau, you have given some themes, do you want only those sheets or something extra would also fit. Regards, Ibrahim Anjum
16 freelancers are bidding on average $155 for this job
Hello, I am an independent, experienced data analysis expert. I can help with this task with a quick turn-around. Looking to hearing from you. Kind regards Rina B.
Hi there, I will use Matlab to read the excel data file, then do analysis in Matlab, and write the required output back to the excel file for each airport. Please ping me for further progress of this work. Thanks.
I am expert in Tableau , I had done many projects using tableau. I can create interactive dashboards and can create accurate analysis. so for further details we can discuss in chat.