🌍 Nagpur Air Quality Analysis & Dashboard

An end-to-end Data Analytics Project that analyzes the air quality of Nagpur, Maharashtra using historical data from the Central Pollution Control Board (CPCB). The project covers the complete analytics workflow—from data collection and preprocessing to exploratory data analysis (EDA), statistical summaries, and an interactive Excel dashboard.

---

📌 Project Overview

Air pollution is a major environmental concern that impacts public health and quality of life. This project analyzes historical Air Quality Index (AQI) and pollutant data for Nagpur to identify trends, seasonal patterns, pollution levels, and key contributing pollutants.

The processed data is further used to create interactive dashboards in Microsoft Excel for easy visualization and decision-making.

---

🎯 Objectives

• Collect and analyze historical AQI data for Nagpur.
• Clean and preprocess raw CPCB data.
• Perform exploratory data analysis (EDA).
• Identify seasonal and yearly pollution trends.
• Analyze the contribution of major pollutants.
• Detect outliers and extreme pollution events.
• Generate summary datasets for reporting.
• Build an interactive Excel dashboard using Pivot Tables, Pivot Charts, Power Query, and Power Pivot.

---

📂 Project Structure

Nagpur_AQI_Project/
│
├── Raw_Data/
│   └── nagpur_aqi_raw.csv
│
├── Cleaned_Data/
│   ├── nagpur_aqi_clean.csv
│   ├── pollutant_exploded.csv
│   ├── summary_avg_aqi_by_month.csv
│   ├── summary_avg_aqi_by_season.csv
│   ├── summary_category_distribution.csv
│   ├── summary_data_availability_by_year.csv
│   ├── summary_data_availability_by_year_month.csv
│   ├── summary_outliers.csv
│   ├── summary_pollutant_contribution.csv
│   ├── summary_top10_worst_days.csv
│   ├── summary_weekday_weekend.csv
│   ├── summary_yearly_avg_aqi.csv
│   └── summary_yoy_change.csv
│
├── Python_Notebooks/
│   ├── data_cleaning.ipynb
│   └── eda.ipynb
│
├── Excel_Dashboard/
│   └── Nagpur_AQI_Dashboard.xlsx
│
├── Report/
│   └── project_report.pdf
│
└── README.md

---

📊 Dataset

Source: Central Pollution Control Board (CPCB)

The dataset includes daily air quality measurements for Nagpur.

Features

• Date
• AQI
• PM2.5
• PM10
• NO₂
• SO₂
• CO
• O₃
• NH₃

---

🛠 Technologies Used

• Python

• Pandas

• NumPy

• Matplotlib

• Jupyter Notebook

• Microsoft Excel

  • Pivot Tables
  • Pivot Charts
  • Power Query
  • Power Pivot

---

🔄 Data Processing

The raw dataset was cleaned by:

• Removing duplicate records
• Handling missing values
• Converting date columns
• Creating Year, Month, Weekday, and Season columns
• Categorizing AQI into standard CPCB categories
• Preparing summary datasets for dashboard creation

---

📈 Exploratory Data Analysis

The project answers the following analytical questions:

• How has AQI changed over time?
• Which months experience the highest pollution?
• Which season has the poorest air quality?
• What are the top 10 worst AQI days?
• How does AQI vary between weekdays and weekends?
• Which pollutants contribute most to poor air quality?
• How has AQI changed year over year?

---

📋 Summary Files Generated

The project generates several processed datasets for further analysis.

File	Description
summary_avg_aqi_by_month.csv	Average AQI by month
summary_avg_aqi_by_season.csv	Average AQI by season
summary_yearly_avg_aqi.csv	Average AQI by year
summary_yoy_change.csv	Year-over-year AQI change
summary_category_distribution.csv	Distribution of AQI categories
summary_pollutant_contribution.csv	Average concentration of pollutants
summary_weekday_weekend.csv	AQI comparison between weekdays and weekends
summary_top10_worst_days.csv	Top 10 highest AQI days
summary_outliers.csv	Extreme pollution observations
summary_data_availability_by_year.csv	Available records by year
summary_data_availability_by_year_month.csv	Available records by month and year

---

📊 Excel Dashboard

The interactive Excel dashboard includes:

• KPI Cards
• AQI Trend Analysis
• Monthly Pollution Analysis
• Seasonal AQI Comparison
• Pollutant Contribution Analysis
• AQI Category Distribution
• Interactive Pivot Tables
• Dynamic Filters and Slicers

---

🚀 How to Run

1. Clone the repository.

git clone https://github.com/your-username/Nagpur_AQI_Project.git

2. Install the required Python libraries.

pip install pandas numpy matplotlib openpyxl

3. Open the notebooks in Jupyter Notebook or VS Code.

4. Run data_cleaning.ipynb.

5. Run eda.ipynb.

6. Open the generated CSV files in Excel to explore the dashboard.

---

📌 Future Improvements

• AQI forecasting using Machine Learning
• Time-series forecasting using ARIMA or Prophet
• Interactive Power BI dashboard
• Streamlit web application
• Automated data collection using CPCB APIs or web scraping
• Geospatial visualization using GIS tools

---

👩‍💻 Author

Ruchika Bambal

If you found this project useful, consider giving it a ⭐ on GitHub.