Flight Delay Analysis

American Airlines: Separate the delay a flight inherits from the delay it creates, and trace the worst delays back to the flights that caused them.

One American Airlines aircraft flies four to six flights a day, one after another, and the schedule leaves it just enough time on the ground between them. So a flight that lands late makes the next one late, and the delay follows that aircraft until a long gap lets it catch up. You're an analyst on the network operations team, which can list the late flights but not say what started them.

Split every departure delay into what the aircraft inherited and what the airport added, then rank the flights that cost the most minutes to the ones behind them.

1. 1Load and clean the flights. The table arrives unordered, so put them in order by tail number (the aircraft's registration) and then scheduled departure, treating each aircraft's first flight of the day as a fresh start.
2. 2Join each airport and aircraft type to its minimum turn time, which is the least time that aircraft needs on the ground there before it can fly again.
3. 3The earliest a flight could leave is the previous flight's actual arrival plus that minimum turn. Its inherited delay is how much later that is than its scheduled departure, and zero when the aircraft was back in time.
4. 4The created delay is the departure delay minus the inherited delay. Keep its sign, so a flight turned faster than the minimum shows as negative rather than zero.
5. 5Decide what to do with flights that have no actual times, where the aircraft never went, and explain your choice. Save leg_delays.csv with the columns leg_id, departure_delay_minutes, inherited_delay_minutes and created_delay_minutes.
6. 6A delay starts at a flight whose created delay is 15 minutes or more and whose inherited delay is under 15. Count the unbroken run of later flights on the same aircraft inheriting 15 or more, and save cascade_roots.csv with the columns leg_id, flight_number, tail_number, origin, scheduled_departure, created_delay_minutes, downstream_legs_affected and downstream_delay_minutes, worst first.
7. 7Report the airports and local times of day where delays start most often, and say how often the recorded reason agrees with your arithmetic.

All files come in one download.

A public GitHub repo with your analysis (a notebook, scripts or SQL), leg_delays.csv and cascade_roots.csv, and a README with how you built the sequence, your handling of flights that never went, and what you found.

When you're done, post your repo in the Solutions tab to share it with other learners.