Pakistani Airports Traffic Analysis: A Data-Driven Approach Using Excel
Did you know that in 2021, more than 133,000 airplanes passed through Pakistani airports, transporting 250,000 metric tons of cargo and moving over 17 million passengers. These numbers are, on average, 20% lower than the numbers in 2016 due to the huge drop during Covid-19 period.
Pakistan had 38 actively operating airports in 2021, with the top three being Jinnah International Airport (Karachi), Islamabad International Airport, and Lahore International Airport.
In this article, we will explore how I used Excel to analyze the performance of the Pakistani aviation industry, identify trends in airport activities, and investigate the reasons for those changes.
The Data
Note: The data used in this project is publicly available stats on official website of Civil Aviation Authority Pakistan, and is used for learning purposes only.
The data represent 6 years (2016–2021) of Major Traffic flows in Pakistani Airports.
Original Datasets can be found here
Transformed Dataset Here.
The purpose of this project is to extract interesting and helpful insights as a data analyst for aviation enthusiasts.
Insights
- The number of active airports in Pakistan increased by 19% from 32 in 2016 to 38 in 2021, suggesting the expansion of the aviation industry.
- Five new airports became operational in 2021: Juzzak , Muzaffarabad , PAF Shorekote/Rafiqui , Rawalkot and Parachinar Airport.
- The aviation industry in Pakistan is recovering from the COVID-19 pandemic, with aircraft movements, passengers, and cargo all increasing from 2020 levels, even though they are still around 20% lower than 2016 levels.
- The top three airports in Pakistan by aircraft movements, passengers, and cargo are Jinnah International Airport (Karachi), Islamabad International Airport, and Lahore International Airport.
The Analysis
1. Cleaning and Transformation
- To prepare the data for analysis, I used Power Query to transform it into three separate tables, one for aircraft movements, one for passengers, and one for cargo, removing any unnecessary columns or data while doing so.
- To combine data of every year (2016–2021) together, I created query connections for each table in each year and appended the results.
- To create unique IDs for each airport I used the CONCATENATE function.
2. Getting Insights
- How many major airports are operational and does it vary overtime?
- I used pivot table to organize data with years in the rows and airport names in the values to count the total number of active airports in each year.
- With the use of line chart, I visualizes the changes in number of active airports over the years.
- According to this chart, the number of operational airports in Pakistan has increased gradually, from 32 in 2016 to 37 in 2018. However, the COVID-19 pandemic caused a significant decline in airport operations, and the number of operational airports fell back to 32 in 2020. Despite this setback, the aviation industry has recovered strongly, and the number of operational airports increased to 38 in 2021.
2. Which airports have been active/inactive the longest, and what are some new airports?
- To check the (Active/Inactive) status of an airport in each year, I created a pivot table with the following settings:
- Column field: Years
- Row field: Airport
- Values field: Count of airport
- This gives us a table with a count of the number of times each airport appears in each year. For active airports, the count will be 1. For inactive airports, the count will be null.
- for better visualization I used conditional formatting to display different icons for different values.
- According to the data, from 2016 to 2021, 24 airports were active every year, 9 airports were active intermittently, and 12 airports were the least active, including 5 new airports that became operational in 2021.
3. What are the top 5 airports in Pakistan each year by aircraft movements, passengers, and cargo?
To find the top 5 airports in each year :
- I created a pivot table for each metric (aircraft movements, passengers, and cargo), plotted the values of each metric on the pivot table, applied a top 5 filter, created a bar chart for each pivot table, and added a slicer to filter the data by year.
- The top 5 airports in Pakistan by aircraft movements and passengers are Jinnah International Airport (Karachi), Islamabad International Airport, Allama Iqbal International Airport (Lahore), Bacha Khan International Airport (Peshawar), and Multan International Airport. This makes sense because they are in the biggest and major cities of Pakistan.
- In terms of cargo, Sialkot International Airport replaces Multan International Airport in the top 5, due to the high demand and export of sports goods from Sialkot.
4. What are the trends in airport movements, passengers, and cargo?
- To analyze the changes in airport movements, passengers, and cargo over time, I plotted the data on a line chart.
- According to this chart, airport movement, passengers, and cargo had the same trend in 2016–2017, with a slight increase. In 2017–2018, we saw a decline in cargo and aircraft movement, but a slight incline in passengers.
- In 2018–2020, all three metrics experienced a huge dip due to COVID-19, but cargo performed better than passengers and aircraft movements.
- In 2020–2021, cargo remained the same, while the other two metrics improved.
- However, all three metrics have not reached their 2016 levels and are still quite lower, although improved compared to 2020.
- Overall, the chart shows that the aviation industry has been impacted by the COVID-19 pandemic, but it is starting to recover.
To visually showcase the insights from this analysis, I have created a dashboard that allows to explore the data and trends in airport activities.
Closing Remarks
The Pakistani aviation industry has been resilient in the face of the COVID-19 pandemic. With the number of active airports increasing, the top airports recovering, and traffic trends improving, the industry is poised for further growth in the coming years.
If you found this information helpful and would like to stay updated with more insightful content like this, be sure to follow me on LinkedIn!
