A Pyspark-Sparksql Pipeline for Urban Air Quality Monitoring: 4-Year Trends and Who Assessment (2021–2024)

Air quality monitoring datasets are inherently temporal, multi-pollutant, and long-horizon in character, making them well suited to scalable distributed computing frameworks that can handle the volume, velocity, and variety of multiyear daily observations while supporting transparent, SQL accessible analytical workflows. This paper presents a reproducible PySpark–SparkSQL analytical pipeline applied to four years (2021–2024) of daily urban air quality monitoring data, addressing five structured research questions about seasonal and temporal variability, pollutant drivers of the Air Quality Index (AQI), human-activity effects on ambient concentrations, and compliance with the revised WHO 2021 Air Quality Guidelines. Our findings document: consistently higher AQI values in winter months, with November peaking at a mean AQI of 342.13 across the four-year period; PM2.5 and PM10 as the dominant AQI drivers with Pearson correlations of 0.87 and 0.91 respectively; marginal but consistent reductions in pollutant levels during holiday and weekend periods, with the strongest effects for trafficrelated NO2; and a chronic, near-daily exceedance of WHO 24-hour mean thresholds for PM2.5 (343–364 exceedance days per year) and PM10 (347–359 days). A previously unreported SO2 exceedance spike in 2024—110 exceedance days compared to fewer than 30 in each prior year—is identified as an anomaly of policy significance indicating an emerging industrial emission source. The pipeline, implemented in Python (PySpark, Pandas, Matplotlib, Seaborn) on Google Colab, provides a reproducible, computationally scalable framework adaptable to other urban air quality monitoring contexts.

Air quality, PySpark, SparkSQL, temporal analytics, PM2.5, AQI, WHO guidelines, seasonal analysis, big data, urban environment

1. World Health Organization, “WHO global air quality guidelines: Particulate matter (PM2.5 and PM10), ozone, nitrogen dioxide, sulfur dioxide and carbon monoxide,” World Health Organization, Geneva, Tech. Rep., 2021. [Online]. Available: https://www.who.int/publications/ i/item/9789240034228 [Google Scholar] [Crossref]

2. B. Hoffmann, H. Boogaard, A. de Nazelle, Z. J. Andersen, M. Abramson, M. Brauer, B. Brunekreef, F. Forastiere, W. Huang, H. Kan, J. D. Kaufman, K. Katsouyanni, M. Krzyzanowski, N. Kuenzli, F. Laden, M. Nieuwenhuijsen, A. Mustapha, P. Powell, M. Rice, A. Roca-Barcelo,´ C. J. Roscoe, A. Soares, K. Straif, and G. Thurston, “WHO air quality guidelines 2021: Aiming for healthier air for all – a joint statement by medical, public health, scientific societies and patient representative organisations,” International Journal of Public Health, vol. 66, p.1604465, 2021. [Google Scholar] [Crossref]

3. C. Li, M. S. Hammer, A. van Donkelaar, A. I. Lyapustin, A. M. Sayer, N. C. Hsu, J. S. Apte, L. Zhang, Q. Zhang, R. T. Burnett, and R. V. Martin, “Reversal of trends in global fine particulate matter air pollution,” Nature Communications, vol. 14, p. 5150, 2023. [Google Scholar] [Crossref]

4. Z. S. Venter, K. Aunan, S. Chowdhury, and J. Lelieveld, “COVID19 lockdowns cause global air pollution declines,” Proceedings of the National Academy of Sciences, vol. 117, no. 32, pp. 18984–18990, 2020. [Google Scholar] [Crossref]

5. L. Saha, A. Kumar, S. Kumar, J. Korstad, S. Srivastava, and K. Bauddh, “The impact of the COVID-19 lockdown on global air quality: A review,” Environmental Sustainability, vol. 5, no. 1, pp. 53–69, 2022. [Google Scholar] [Crossref]

6. J. Wei, Z. Li, A. Lyapustin, Y. Zhan, L. Liu, Z. Yang, Y. Zhang, R. Liu, and Z. Ma, “First close insight into global daily gapless 1 km PM2.5 pollution, variability, and health impact,” Nature Communications, vol. 14, p. 8349, 2023. [Google Scholar] [Crossref]

7. S. Liu, P. Valks, G. Curci, Y. Chen, L. Shu, J. Jin, S. Sun, D. Pu, X. Li, [Google Scholar] [Crossref]

8. J. Li, X. Zuo, W. Fu, Y. Li, P. Zhang, X. Yang, T.-M. Fu, and L. Zhu, “Satellite NO2 retrieval complicated by aerosol composition over global urban agglomerations: Seasonal variations and long-term trends (2001– 2018),” Environmental Science & Technology, vol. 58, no. 18, 2024. [Google Scholar] [Crossref]

9. J. Pan, K. He, K. Wang, Q. Mu, and C. Ling, “Spatio-temporal joint analysis of extreme ozone and moderate PM2.5 in california with INLA approach,” arXiv preprint arXiv:2404.14446, 2024. [Google Scholar] [Crossref]

10. C. He, S. Hong, L. Zhang, H. Mu, A. Xin, Y. Zhou, J. Liu, N. Liu, Y. Su, Y. Tian, B. Ke, Y. Wang, and L. Yang, “Global, continental, and national variation in PM2.5, O3, and NO2 concentrations during the early 2020 COVID-19 lockdown,” Atmospheric Pollution Research, vol. 12, no. 3, pp. 136–145, 2021. [Google Scholar] [Crossref]

11. Y. Zhang, Z. Li, J. Wei, Y. Zhan, L. Liu, Z. Yang, Y. Zhang, R. Liu, and Z. Ma, “Long-term exposure to ambient NO2 and adult mortality: A nationwide cohort study in china,” Journal of Advanced Research, vol. 41, pp. 13–22, 2022. [Google Scholar] [Crossref]

12. X. Chen, L. Qi, S. Li, and X. Duan, “Long-term NO2 exposure and mortality: A comprehensive meta-analysis,” Environmental Pollution, vol. 341, p. 122971, 2024. [Google Scholar] [Crossref]

13. C. Tonne, E. Puzzolo, N. Kunzli, Z. J. Andersen, I. Annesi-Maesano,¨ [Google Scholar] [Crossref]

14. F. Forastiere, A. Haines, M.-E. Heroux, F. J. Kelly, M. Nieuwenhuijsen,´ L. Perez, and S. Vardoulakis, “Promoting health through climate change mitigation in Europe,” The Lancet Planetary Health, vol. 9, no. 1, pp. e15–e25, 2025 [Google Scholar] [Crossref]

• Smart Iot Device for Weather And Health
• Merlarchive: A Web-Based Academic Hub for UDM With AI-Powered Natural Language Processing
• Enhanced Social Network Security System: Integrating Biometric Authentication for Improved User Verification and Privacy Protection
• Smart Budget Allocation in Public Policy: A Data-Driven Approach for Equitable Resource Distribution
• Decision Support System for Faculty Selection, Promotion, and Reclassification Using Predictive Analytics