{ "nbformat": 4, "nbformat_minor": 0, "metadata": { "colab": { "name": "Inspecting air pollution data from OpenAQ using Colab, Pandas, and BigQuery.ipynb", "version": "0.3.2", "provenance": [], "collapsed_sections": [] }, "kernelspec": { "name": "python3", "display_name": "Python 3" } }, "cells": [ { "metadata": { "id": "Qb_7EOWgmDWa", "colab_type": "code", "colab": {} }, "cell_type": "code", "source": [ "#@title Authenticate user\n", "from google.colab import auth\n", "auth.authenticate_user()" ], "execution_count": 0, "outputs": [] }, { "metadata": { "id": "aa3wz8aUpm44", "colab_type": "code", "outputId": "40bd0e10-6bb1-4f59-9a5f-236b2d7f1a15", "colab": { "base_uri": "https://localhost:8080/", "height": 1969 } }, "cell_type": "code", "source": [ "#@title Pull OpenAQ data via BigQuery\n", "%%bigquery --project silicon-perigee-208521 p\n", "#standardSQL\n", "select\n", " pollutant,\n", " country,\n", " city,\n", " location,\n", " value\n", "from `bigquery-public-data.openaq.global_air_quality`;" ], "execution_count": 0, "outputs": [ { "output_type": "execute_result", "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
pollutantcountrycitylocationvalue
0pm25US007Plain_Mobile0.000000
1pm25US007Cle Elum_Mobile0.000000
2pm25US007Leavenworth_Mobile10.000000
3pm25US037Mobile_Cle Elum62.000000
4pm25US039Mobile_WhiteSalmon58.000000
5pm25US047Brewster_Mobile7.000000
6pm25US051Mobile_Newport29.000000
7pm25US051Newport_Mobile-2.000000
8pm25US051Mobile_Newport_201726.000000
9coUSADANear Road0.290000
10bcUSADANear Road0.600000
11no2USADANear Road0.008000
12pm25USADANear Road12.000000
13pm10USADANear Road46.000000
14o3FRAinFR33305136.000000
15pm10FRAinFR3330534.830000
16no2FRAinFR3330566.800000
17o3FRAinCOTIERE AIN128.350000
18no2FRAinCOTIERE AIN3.750000
19pm10FRAinCOTIERE AIN21.280000
20pm25USFRAMMFRA10018.000000
21pm25USGBUGBUAPCD EBAM 13.000000
22pm25USGBUGBUAPCD EBAM @ Coso20.000000
23pm25USGBUGBUAPCD EBAM @ June-2.000000
24pm25USGBUGBUAPCD EBAM @ Alpin14.000000
25pm25USGBUGBUAPCD EBAM @ Bisho1.000000
26pm25USGBUGBUAPCD EBAM @ Bridg4.000000
27pm25USGBUGBUAPCD EBAM @ Crowl12.000000
28pm25USGBUGBUAPCD EBAM @ Lee V5.000000
29so2USKAYPonca City0.000000
..................
25170no2HRDržavna mreža za trajno praćenje kvalitete zra...SLAVONSKI BROD-1 - RH0109 - HR0010A; Državna m...3.804000
25171so2HRDržavna mreža za trajno praćenje kvalitete zra...SLAVONSKI BROD-1 - RH0109 - HR0010A; Državna m...3.573000
25172pm25HRDržavna mreža za trajno praćenje kvalitete zra...SLAVONSKI BROD-1 - RH0109 - HR0010A; Državna m...12.185000
25173o3HRDržavna mreža za trajno praćenje kvalitete zra...SLAVONSKI BROD-1 - RH0109 - HR0010A; Državna m...57.693000
25174o3HRDržavna mreža za trajno praćenje kvalitete zra...ŽARKOVICA (Dubrovnik) - RH0120 - HR0020A; Drža...59.200000
25175no2HRDržavna mreža za trajno praćenje kvalitete zra...ŽARKOVICA (Dubrovnik) - RH0120 - HR0020A; Drža...0.900000
25176pm10HUHungarian Regional Background Air Pollution M...K-puszta32.600000
25177no2GIUK Air Quality e-Reporting monitoring network ...Rosia Road26.800000
25178so2GIUK Air Quality e-Reporting monitoring network ...Rosia Road5.300000
25179coGIUK Air Quality e-Reporting monitoring network ...Rosia Road350.000000
25180no2GIUK Air Quality e-Reporting monitoring network ...Bleak House24.900000
25181o3GIUK Air Quality e-Reporting monitoring network ...Bleak House55.900000
25182o3IEIreland Air Quality e-Reporting monitoring net...Laois Emo Court35.150000
25183no2IEIreland Air Quality e-Reporting monitoring net...Laois Emo Court3.438000
25184so2IEIreland Air Quality e-Reporting monitoring net...Clare Ennis Simms Lane1.862000
25185no2IEIreland Air Quality e-Reporting monitoring net...Kilkenny Seville Lodge0.191000
25186coIEIreland Air Quality e-Reporting monitoring net...Dublin Coleraine Street0.345422
25187so2IEIreland Air Quality e-Reporting monitoring net...Dublin Coleraine Street0.000000
25188no2IEIreland Air Quality e-Reporting monitoring net...Dublin Coleraine Street0.000000
25189so2IEIreland Air Quality e-Reporting monitoring net...Dublin Winetavern Street0.000000
25190coIEIreland Air Quality e-Reporting monitoring net...Dublin Winetavern Street0.198059
25191so2IEIreland Air Quality e-Reporting monitoring net...Monaghan Kilkitt Waterworks2.726500
25192no2IEIreland Air Quality e-Reporting monitoring net...Monaghan Kilkitt Waterworks1.528000
25193o3IEIreland Air Quality e-Reporting monitoring net...Mayo Castlebar John Moore Road37.950000
25194o3IEIreland Air Quality e-Reporting monitoring net...Dublin Clonskeagh Road Richview42.784000
25195pm25IEIreland Air Quality e-Reporting monitoring net...Dublin Rathmines Wynnefield Road0.377067
25196no2IEIreland Air Quality e-Reporting monitoring net...Dublin Rathmines Wynnefield Road9.836500
25197o3IEIreland Air Quality e-Reporting monitoring net...Dublin Rathmines Wynnefield Road18.050000
25198pm10IEIreland Air Quality e-Reporting monitoring net...Dublin Rathmines Wynnefield Road2.238530
25199so2IEIreland Air Quality e-Reporting monitoring net...Dublin Rathmines Wynnefield Road1.662500
\n", "

25200 rows × 5 columns

\n", "
" ], "text/plain": [ " pollutant country city \\\n", "0 pm25 US 007 \n", "1 pm25 US 007 \n", "2 pm25 US 007 \n", "3 pm25 US 037 \n", "4 pm25 US 039 \n", "5 pm25 US 047 \n", "6 pm25 US 051 \n", "7 pm25 US 051 \n", "8 pm25 US 051 \n", "9 co US ADA \n", "10 bc US ADA \n", "11 no2 US ADA \n", "12 pm25 US ADA \n", "13 pm10 US ADA \n", "14 o3 FR Ain \n", "15 pm10 FR Ain \n", "16 no2 FR Ain \n", "17 o3 FR Ain \n", "18 no2 FR Ain \n", "19 pm10 FR Ain \n", "20 pm25 US FRA \n", "21 pm25 US GBU \n", "22 pm25 US GBU \n", "23 pm25 US GBU \n", "24 pm25 US GBU \n", "25 pm25 US GBU \n", "26 pm25 US GBU \n", "27 pm25 US GBU \n", "28 pm25 US GBU \n", "29 so2 US KAY \n", "... ... ... ... \n", "25170 no2 HR Državna mreža za trajno praćenje kvalitete zra... \n", "25171 so2 HR Državna mreža za trajno praćenje kvalitete zra... \n", "25172 pm25 HR Državna mreža za trajno praćenje kvalitete zra... \n", "25173 o3 HR Državna mreža za trajno praćenje kvalitete zra... \n", "25174 o3 HR Državna mreža za trajno praćenje kvalitete zra... \n", "25175 no2 HR Državna mreža za trajno praćenje kvalitete zra... \n", "25176 pm10 HU Hungarian Regional Background Air Pollution M... \n", "25177 no2 GI UK Air Quality e-Reporting monitoring network ... \n", "25178 so2 GI UK Air Quality e-Reporting monitoring network ... \n", "25179 co GI UK Air Quality e-Reporting monitoring network ... \n", "25180 no2 GI UK Air Quality e-Reporting monitoring network ... \n", "25181 o3 GI UK Air Quality e-Reporting monitoring network ... \n", "25182 o3 IE Ireland Air Quality e-Reporting monitoring net... \n", "25183 no2 IE Ireland Air Quality e-Reporting monitoring net... \n", "25184 so2 IE Ireland Air Quality e-Reporting monitoring net... \n", "25185 no2 IE Ireland Air Quality e-Reporting monitoring net... \n", "25186 co IE Ireland Air Quality e-Reporting monitoring net... \n", "25187 so2 IE Ireland Air Quality e-Reporting monitoring net... \n", "25188 no2 IE Ireland Air Quality e-Reporting monitoring net... \n", "25189 so2 IE Ireland Air Quality e-Reporting monitoring net... \n", "25190 co IE Ireland Air Quality e-Reporting monitoring net... \n", "25191 so2 IE Ireland Air Quality e-Reporting monitoring net... \n", "25192 no2 IE Ireland Air Quality e-Reporting monitoring net... \n", "25193 o3 IE Ireland Air Quality e-Reporting monitoring net... \n", "25194 o3 IE Ireland Air Quality e-Reporting monitoring net... \n", "25195 pm25 IE Ireland Air Quality e-Reporting monitoring net... \n", "25196 no2 IE Ireland Air Quality e-Reporting monitoring net... \n", "25197 o3 IE Ireland Air Quality e-Reporting monitoring net... \n", "25198 pm10 IE Ireland Air Quality e-Reporting monitoring net... \n", "25199 so2 IE Ireland Air Quality e-Reporting monitoring net... \n", "\n", " location value \n", "0 Plain_Mobile 0.000000 \n", "1 Cle Elum_Mobile 0.000000 \n", "2 Leavenworth_Mobile 10.000000 \n", "3 Mobile_Cle Elum 62.000000 \n", "4 Mobile_WhiteSalmon 58.000000 \n", "5 Brewster_Mobile 7.000000 \n", "6 Mobile_Newport 29.000000 \n", "7 Newport_Mobile -2.000000 \n", "8 Mobile_Newport_2017 26.000000 \n", "9 Near Road 0.290000 \n", "10 Near Road 0.600000 \n", "11 Near Road 0.008000 \n", "12 Near Road 12.000000 \n", "13 Near Road 46.000000 \n", "14 FR33305 136.000000 \n", "15 FR33305 34.830000 \n", "16 FR33305 66.800000 \n", "17 COTIERE AIN 128.350000 \n", "18 COTIERE AIN 3.750000 \n", "19 COTIERE AIN 21.280000 \n", "20 MMFRA1001 8.000000 \n", "21 GBUAPCD EBAM 1 3.000000 \n", "22 GBUAPCD EBAM @ Coso 20.000000 \n", "23 GBUAPCD EBAM @ June -2.000000 \n", "24 GBUAPCD EBAM @ Alpin 14.000000 \n", "25 GBUAPCD EBAM @ Bisho 1.000000 \n", "26 GBUAPCD EBAM @ Bridg 4.000000 \n", "27 GBUAPCD EBAM @ Crowl 12.000000 \n", "28 GBUAPCD EBAM @ Lee V 5.000000 \n", "29 Ponca City 0.000000 \n", "... ... ... \n", "25170 SLAVONSKI BROD-1 - RH0109 - HR0010A; Državna m... 3.804000 \n", "25171 SLAVONSKI BROD-1 - RH0109 - HR0010A; Državna m... 3.573000 \n", "25172 SLAVONSKI BROD-1 - RH0109 - HR0010A; Državna m... 12.185000 \n", "25173 SLAVONSKI BROD-1 - RH0109 - HR0010A; Državna m... 57.693000 \n", "25174 ŽARKOVICA (Dubrovnik) - RH0120 - HR0020A; Drža... 59.200000 \n", "25175 ŽARKOVICA (Dubrovnik) - RH0120 - HR0020A; Drža... 0.900000 \n", "25176 K-puszta 32.600000 \n", "25177 Rosia Road 26.800000 \n", "25178 Rosia Road 5.300000 \n", "25179 Rosia Road 350.000000 \n", "25180 Bleak House 24.900000 \n", "25181 Bleak House 55.900000 \n", "25182 Laois Emo Court 35.150000 \n", "25183 Laois Emo Court 3.438000 \n", "25184 Clare Ennis Simms Lane 1.862000 \n", "25185 Kilkenny Seville Lodge 0.191000 \n", "25186 Dublin Coleraine Street 0.345422 \n", "25187 Dublin Coleraine Street 0.000000 \n", "25188 Dublin Coleraine Street 0.000000 \n", "25189 Dublin Winetavern Street 0.000000 \n", "25190 Dublin Winetavern Street 0.198059 \n", "25191 Monaghan Kilkitt Waterworks 2.726500 \n", "25192 Monaghan Kilkitt Waterworks 1.528000 \n", "25193 Mayo Castlebar John Moore Road 37.950000 \n", "25194 Dublin Clonskeagh Road Richview 42.784000 \n", "25195 Dublin Rathmines Wynnefield Road 0.377067 \n", "25196 Dublin Rathmines Wynnefield Road 9.836500 \n", "25197 Dublin Rathmines Wynnefield Road 18.050000 \n", "25198 Dublin Rathmines Wynnefield Road 2.238530 \n", "25199 Dublin Rathmines Wynnefield Road 1.662500 \n", "\n", "[25200 rows x 5 columns]" ] }, "metadata": { "tags": [] }, "execution_count": 2 } ] }, { "metadata": { "id": "CqY8FVV6nOYT", "colab_type": "code", "outputId": "00d9dcf7-aa25-45c8-bf39-f24e92592ee5", "colab": { "base_uri": "https://localhost:8080/", "height": 2479 } }, "cell_type": "code", "source": [ "#@title For each pollutant, calculate rank of each location within its country and world-wide \n", "# Rank within world\n", "p = p.assign(world_rank=(\n", " p.groupby(['pollutant'])['value']\n", " .rank(method='min', ascending=False)\n", " .astype(int)))\n", "# Rank within country\n", "p = p.assign(country_rank=(\n", " p.groupby(['pollutant', 'country'])['value']\n", " .rank(method='min', ascending=False)\n", " .astype(int)))\n", "# Rank within city\n", "p = p.assign(city_rank=(\n", " p.groupby(['pollutant', 'country', 'city'])['value']\n", " .rank(method='min', ascending=False)\n", " .astype(int)))\n", "p" ], "execution_count": 0, "outputs": [ { "output_type": "execute_result", "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
pollutantcountrycitylocationvalueworld_rankcountry_rankcity_rank
0pm25US007Plain_Mobile0.00000037579642
1pm25US007Cle Elum_Mobile0.00000037579642
2pm25US007Leavenworth_Mobile10.00000022222531
3pm25US037Mobile_Cle Elum62.00000052341
4pm25US039Mobile_WhiteSalmon58.00000055651
5pm25US047Brewster_Mobile7.00000026314351
6pm25US051Mobile_Newport29.0000001079351
7pm25US051Newport_Mobile-2.000000385810093
8pm25US051Mobile_Newport_201726.0000001191402
9coUSADANear Road0.2900002453851
10bcUSADANear Road0.6000004961
11no2USADANear Road0.00800045411211
12pm25USADANear Road12.00000020251941
13pm10USADANear Road46.0000001213131
14o3FRAinFR33305136.00000046111
15pm10FRAinFR3330534.8300001646561
16no2FRAinFR3330566.800000301411
17o3FRAinCOTIERE AIN128.35000078262
18no2FRAinCOTIERE AIN3.75000039964292
19pm10FRAinCOTIERE AIN21.28000025011622
20pm25USFRAMMFRA10018.00000024763721
21pm25USGBUGBUAPCD EBAM 13.00000033437726
22pm25USGBUGBUAPCD EBAM @ Coso20.0000001490621
23pm25USGBUGBUAPCD EBAM @ June-2.000000385810098
24pm25USGBUGBUAPCD EBAM @ Alpin14.00000018541472
25pm25USGBUGBUAPCD EBAM @ Bisho1.00000036579157
26pm25USGBUGBUAPCD EBAM @ Bridg4.00000031376935
27pm25USGBUGBUAPCD EBAM @ Crowl12.00000020251943
28pm25USGBUGBUAPCD EBAM @ Lee V5.00000029646054
29so2USKAYPonca City0.00000032501161
...........................
25170no2HRDržavna mreža za trajno praćenje kvalitete zra...SLAVONSKI BROD-1 - RH0109 - HR0010A; Državna m...3.804000399374
25171so2HRDržavna mreža za trajno praćenje kvalitete zra...SLAVONSKI BROD-1 - RH0109 - HR0010A; Državna m...3.573000187611
25172pm25HRDržavna mreža za trajno praćenje kvalitete zra...SLAVONSKI BROD-1 - RH0109 - HR0010A; Državna m...12.185000202231
25173o3HRDržavna mreža za trajno praćenje kvalitete zra...SLAVONSKI BROD-1 - RH0109 - HR0010A; Državna m...57.693000170354
25174o3HRDržavna mreža za trajno praćenje kvalitete zra...ŽARKOVICA (Dubrovnik) - RH0120 - HR0020A; Drža...59.200000161643
25175no2HRDržavna mreža za trajno praćenje kvalitete zra...ŽARKOVICA (Dubrovnik) - RH0120 - HR0020A; Drža...0.9000004238105
25176pm10HUHungarian Regional Background Air Pollution M...K-puszta32.600000175181
25177no2GIUK Air Quality e-Reporting monitoring network ...Rosia Road26.800000167521
25178so2GIUK Air Quality e-Reporting monitoring network ...Rosia Road5.300000136811
25179coGIUK Air Quality e-Reporting monitoring network ...Rosia Road350.000000178211
25180no2GIUK Air Quality e-Reporting monitoring network ...Bleak House24.900000176932
25181o3GIUK Air Quality e-Reporting monitoring network ...Bleak House55.900000177711
25182o3IEIreland Air Quality e-Reporting monitoring net...Laois Emo Court35.150000241593
25183no2IEIreland Air Quality e-Reporting monitoring net...Laois Emo Court3.4380004014102
25184so2IEIreland Air Quality e-Reporting monitoring net...Clare Ennis Simms Lane1.862000241442
25185no2IEIreland Air Quality e-Reporting monitoring net...Kilkenny Seville Lodge0.1910004257124
25186coIEIreland Air Quality e-Reporting monitoring net...Dublin Coleraine Street0.345422239711
25187so2IEIreland Air Quality e-Reporting monitoring net...Dublin Coleraine Street0.000000325074
25188no2IEIreland Air Quality e-Reporting monitoring net...Dublin Coleraine Street0.0000004862135
25189so2IEIreland Air Quality e-Reporting monitoring net...Dublin Winetavern Street0.000000325074
25190coIEIreland Air Quality e-Reporting monitoring net...Dublin Winetavern Street0.198059254822
25191so2IEIreland Air Quality e-Reporting monitoring net...Monaghan Kilkitt Waterworks2.726500216331
25192no2IEIreland Air Quality e-Reporting monitoring net...Monaghan Kilkitt Waterworks1.5280004194113
25193o3IEIreland Air Quality e-Reporting monitoring net...Mayo Castlebar John Moore Road37.950000236282
25194o3IEIreland Air Quality e-Reporting monitoring net...Dublin Clonskeagh Road Richview42.784000221571
25195pm25IEIreland Air Quality e-Reporting monitoring net...Dublin Rathmines Wynnefield Road0.377067374051
25196no2IEIreland Air Quality e-Reporting monitoring net...Dublin Rathmines Wynnefield Road9.836500319471
25197o3IEIreland Air Quality e-Reporting monitoring net...Dublin Rathmines Wynnefield Road18.0500002771114
25198pm10IEIreland Air Quality e-Reporting monitoring net...Dublin Rathmines Wynnefield Road2.2385304276101
25199so2IEIreland Air Quality e-Reporting monitoring net...Dublin Rathmines Wynnefield Road1.662500243353
\n", "

25200 rows × 8 columns

\n", "
" ], "text/plain": [ " pollutant country city \\\n", "0 pm25 US 007 \n", "1 pm25 US 007 \n", "2 pm25 US 007 \n", "3 pm25 US 037 \n", "4 pm25 US 039 \n", "5 pm25 US 047 \n", "6 pm25 US 051 \n", "7 pm25 US 051 \n", "8 pm25 US 051 \n", "9 co US ADA \n", "10 bc US ADA \n", "11 no2 US ADA \n", "12 pm25 US ADA \n", "13 pm10 US ADA \n", "14 o3 FR Ain \n", "15 pm10 FR Ain \n", "16 no2 FR Ain \n", "17 o3 FR Ain \n", "18 no2 FR Ain \n", "19 pm10 FR Ain \n", "20 pm25 US FRA \n", "21 pm25 US GBU \n", "22 pm25 US GBU \n", "23 pm25 US GBU \n", "24 pm25 US GBU \n", "25 pm25 US GBU \n", "26 pm25 US GBU \n", "27 pm25 US GBU \n", "28 pm25 US GBU \n", "29 so2 US KAY \n", "... ... ... ... \n", "25170 no2 HR Državna mreža za trajno praćenje kvalitete zra... \n", "25171 so2 HR Državna mreža za trajno praćenje kvalitete zra... \n", "25172 pm25 HR Državna mreža za trajno praćenje kvalitete zra... \n", "25173 o3 HR Državna mreža za trajno praćenje kvalitete zra... \n", "25174 o3 HR Državna mreža za trajno praćenje kvalitete zra... \n", "25175 no2 HR Državna mreža za trajno praćenje kvalitete zra... \n", "25176 pm10 HU Hungarian Regional Background Air Pollution M... \n", "25177 no2 GI UK Air Quality e-Reporting monitoring network ... \n", "25178 so2 GI UK Air Quality e-Reporting monitoring network ... \n", "25179 co GI UK Air Quality e-Reporting monitoring network ... \n", "25180 no2 GI UK Air Quality e-Reporting monitoring network ... \n", "25181 o3 GI UK Air Quality e-Reporting monitoring network ... \n", "25182 o3 IE Ireland Air Quality e-Reporting monitoring net... \n", "25183 no2 IE Ireland Air Quality e-Reporting monitoring net... \n", "25184 so2 IE Ireland Air Quality e-Reporting monitoring net... \n", "25185 no2 IE Ireland Air Quality e-Reporting monitoring net... \n", "25186 co IE Ireland Air Quality e-Reporting monitoring net... \n", "25187 so2 IE Ireland Air Quality e-Reporting monitoring net... \n", "25188 no2 IE Ireland Air Quality e-Reporting monitoring net... \n", "25189 so2 IE Ireland Air Quality e-Reporting monitoring net... \n", "25190 co IE Ireland Air Quality e-Reporting monitoring net... \n", "25191 so2 IE Ireland Air Quality e-Reporting monitoring net... \n", "25192 no2 IE Ireland Air Quality e-Reporting monitoring net... \n", "25193 o3 IE Ireland Air Quality e-Reporting monitoring net... \n", "25194 o3 IE Ireland Air Quality e-Reporting monitoring net... \n", "25195 pm25 IE Ireland Air Quality e-Reporting monitoring net... \n", "25196 no2 IE Ireland Air Quality e-Reporting monitoring net... \n", "25197 o3 IE Ireland Air Quality e-Reporting monitoring net... \n", "25198 pm10 IE Ireland Air Quality e-Reporting monitoring net... \n", "25199 so2 IE Ireland Air Quality e-Reporting monitoring net... \n", "\n", " location value \\\n", "0 Plain_Mobile 0.000000 \n", "1 Cle Elum_Mobile 0.000000 \n", "2 Leavenworth_Mobile 10.000000 \n", "3 Mobile_Cle Elum 62.000000 \n", "4 Mobile_WhiteSalmon 58.000000 \n", "5 Brewster_Mobile 7.000000 \n", "6 Mobile_Newport 29.000000 \n", "7 Newport_Mobile -2.000000 \n", "8 Mobile_Newport_2017 26.000000 \n", "9 Near Road 0.290000 \n", "10 Near Road 0.600000 \n", "11 Near Road 0.008000 \n", "12 Near Road 12.000000 \n", "13 Near Road 46.000000 \n", "14 FR33305 136.000000 \n", "15 FR33305 34.830000 \n", "16 FR33305 66.800000 \n", "17 COTIERE AIN 128.350000 \n", "18 COTIERE AIN 3.750000 \n", "19 COTIERE AIN 21.280000 \n", "20 MMFRA1001 8.000000 \n", "21 GBUAPCD EBAM 1 3.000000 \n", "22 GBUAPCD EBAM @ Coso 20.000000 \n", "23 GBUAPCD EBAM @ June -2.000000 \n", "24 GBUAPCD EBAM @ Alpin 14.000000 \n", "25 GBUAPCD EBAM @ Bisho 1.000000 \n", "26 GBUAPCD EBAM @ Bridg 4.000000 \n", "27 GBUAPCD EBAM @ Crowl 12.000000 \n", "28 GBUAPCD EBAM @ Lee V 5.000000 \n", "29 Ponca City 0.000000 \n", "... ... ... \n", "25170 SLAVONSKI BROD-1 - RH0109 - HR0010A; Državna m... 3.804000 \n", "25171 SLAVONSKI BROD-1 - RH0109 - HR0010A; Državna m... 3.573000 \n", "25172 SLAVONSKI BROD-1 - RH0109 - HR0010A; Državna m... 12.185000 \n", "25173 SLAVONSKI BROD-1 - RH0109 - HR0010A; Državna m... 57.693000 \n", "25174 ŽARKOVICA (Dubrovnik) - RH0120 - HR0020A; Drža... 59.200000 \n", "25175 ŽARKOVICA (Dubrovnik) - RH0120 - HR0020A; Drža... 0.900000 \n", "25176 K-puszta 32.600000 \n", "25177 Rosia Road 26.800000 \n", "25178 Rosia Road 5.300000 \n", "25179 Rosia Road 350.000000 \n", "25180 Bleak House 24.900000 \n", "25181 Bleak House 55.900000 \n", "25182 Laois Emo Court 35.150000 \n", "25183 Laois Emo Court 3.438000 \n", "25184 Clare Ennis Simms Lane 1.862000 \n", "25185 Kilkenny Seville Lodge 0.191000 \n", "25186 Dublin Coleraine Street 0.345422 \n", "25187 Dublin Coleraine Street 0.000000 \n", "25188 Dublin Coleraine Street 0.000000 \n", "25189 Dublin Winetavern Street 0.000000 \n", "25190 Dublin Winetavern Street 0.198059 \n", "25191 Monaghan Kilkitt Waterworks 2.726500 \n", "25192 Monaghan Kilkitt Waterworks 1.528000 \n", "25193 Mayo Castlebar John Moore Road 37.950000 \n", "25194 Dublin Clonskeagh Road Richview 42.784000 \n", "25195 Dublin Rathmines Wynnefield Road 0.377067 \n", "25196 Dublin Rathmines Wynnefield Road 9.836500 \n", "25197 Dublin Rathmines Wynnefield Road 18.050000 \n", "25198 Dublin Rathmines Wynnefield Road 2.238530 \n", "25199 Dublin Rathmines Wynnefield Road 1.662500 \n", "\n", " world_rank country_rank city_rank \n", "0 3757 964 2 \n", "1 3757 964 2 \n", "2 2222 253 1 \n", "3 523 4 1 \n", "4 556 5 1 \n", "5 2631 435 1 \n", "6 1079 35 1 \n", "7 3858 1009 3 \n", "8 1191 40 2 \n", "9 2453 85 1 \n", "10 49 6 1 \n", "11 4541 121 1 \n", "12 2025 194 1 \n", "13 1213 13 1 \n", "14 46 11 1 \n", "15 1646 56 1 \n", "16 301 41 1 \n", "17 78 26 2 \n", "18 3996 429 2 \n", "19 2501 162 2 \n", "20 2476 372 1 \n", "21 3343 772 6 \n", "22 1490 62 1 \n", "23 3858 1009 8 \n", "24 1854 147 2 \n", "25 3657 915 7 \n", "26 3137 693 5 \n", "27 2025 194 3 \n", "28 2964 605 4 \n", "29 3250 116 1 \n", "... ... ... ... \n", "25170 3993 7 4 \n", "25171 1876 1 1 \n", "25172 2022 3 1 \n", "25173 1703 5 4 \n", "25174 1616 4 3 \n", "25175 4238 10 5 \n", "25176 1751 8 1 \n", "25177 1675 2 1 \n", "25178 1368 1 1 \n", "25179 1782 1 1 \n", "25180 1769 3 2 \n", "25181 1777 1 1 \n", "25182 2415 9 3 \n", "25183 4014 10 2 \n", "25184 2414 4 2 \n", "25185 4257 12 4 \n", "25186 2397 1 1 \n", "25187 3250 7 4 \n", "25188 4862 13 5 \n", "25189 3250 7 4 \n", "25190 2548 2 2 \n", "25191 2163 3 1 \n", "25192 4194 11 3 \n", "25193 2362 8 2 \n", "25194 2215 7 1 \n", "25195 3740 5 1 \n", "25196 3194 7 1 \n", "25197 2771 11 4 \n", "25198 4276 10 1 \n", "25199 2433 5 3 \n", "\n", "[25200 rows x 8 columns]" ] }, "metadata": { "tags": [] }, "execution_count": 3 } ] }, { "metadata": { "id": "2UT0Xu0Vr77C", "colab_type": "code", "outputId": "27a0da96-57db-4147-a011-be618d4b9d0e", "colab": { "base_uri": "https://localhost:8080/", "height": 35 } }, "cell_type": "code", "source": [ "#@title Pollutants in dataset\n", "print(', '.join(p['pollutant'].unique()))" ], "execution_count": 0, "outputs": [ { "output_type": "stream", "text": [ "pm25, co, bc, no2, pm10, o3, so2\n" ], "name": "stdout" } ] }, { "metadata": { "id": "0IRDaNmZpt6K", "colab_type": "code", "cellView": "both", "outputId": "8520a341-fbf7-4209-e87c-b986b9240972", "colab": { "base_uri": "https://localhost:8080/", "height": 142 } }, "cell_type": "code", "source": [ "#@title Top three locations by Nitrogen Dioxide (NO2)\n", "(p.query('pollutant == \"no2\"')\n", " .nsmallest(3, 'world_rank')\n", " [['country', 'city', 'location', 'value', 'world_rank']])" ], "execution_count": 0, "outputs": [ { "output_type": "execute_result", "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
countrycitylocationvalueworld_rank
19239XKHani i ElezitHani i Elezit397.9261
6078INSolapurSolapur, Solapur - MPCB268.3602
16204INKalaburagiLal Bahadur Shastri Nagar, Kalaburagi - KSPCB229.2103
\n", "
" ], "text/plain": [ " country city location \\\n", "19239 XK Hani i Elezit Hani i Elezit \n", "6078 IN Solapur Solapur, Solapur - MPCB \n", "16204 IN Kalaburagi Lal Bahadur Shastri Nagar, Kalaburagi - KSPCB \n", "\n", " value world_rank \n", "19239 397.926 1 \n", "6078 268.360 2 \n", "16204 229.210 3 " ] }, "metadata": { "tags": [] }, "execution_count": 19 } ] }, { "metadata": { "id": "9NMN-eBAskKJ", "colab_type": "code", "outputId": "89987f07-9121-4556-e5ea-207d2cbc92da", "colab": { "base_uri": "https://localhost:8080/", "height": 142 } }, "cell_type": "code", "source": [ "#@title Top three locations in Great Britain by Nitrogen Dioxide (NO2)\n", "(p.query('pollutant == \"no2\" & country == \"GB\"')\n", " .nsmallest(3, 'country_rank')\n", " [['city', 'location', 'value', 'world_rank', 'country_rank']])" ], "execution_count": 0, "outputs": [ { "output_type": "execute_result", "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
citylocationvalueworld_rankcountry_rank
4585BelfastBelfast Stockman's Lane63.03591
6707ChepstowChepstow A4860.04182
6442AberdeenAberdeen Union Street Roadside54.05443
\n", "
" ], "text/plain": [ " city location value world_rank \\\n", "4585 Belfast Belfast Stockman's Lane 63.0 359 \n", "6707 Chepstow Chepstow A48 60.0 418 \n", "6442 Aberdeen Aberdeen Union Street Roadside 54.0 544 \n", "\n", " country_rank \n", "4585 1 \n", "6707 2 \n", "6442 3 " ] }, "metadata": { "tags": [] }, "execution_count": 22 } ] }, { "metadata": { "id": "QDgN8aDWrort", "colab_type": "code", "outputId": "d494aaf9-c65a-45da-d2a8-1a9a069e6e2b", "colab": { "base_uri": "https://localhost:8080/", "height": 142 } }, "cell_type": "code", "source": [ "#@title Top three locations by Sulphur Dioxide (SO2)\n", "(p.query('pollutant == \"so2\"')\n", " .nsmallest(3, 'world_rank')\n", " [['country', 'city', 'location', 'value', 'world_rank']])" ], "execution_count": 0, "outputs": [ { "output_type": "execute_result", "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
countrycitylocationvalueworld_rank
24629MKState Air Quality Monitoring NetworkKarpos2.050019e+151
7092CLHualpénJUNJI2.088350e+032
3412ESMurciaES1633A1.301000e+033
\n", "
" ], "text/plain": [ " country city location value \\\n", "24629 MK State Air Quality Monitoring Network Karpos 2.050019e+15 \n", "7092 CL Hualpén JUNJI 2.088350e+03 \n", "3412 ES Murcia ES1633A 1.301000e+03 \n", "\n", " world_rank \n", "24629 1 \n", "7092 2 \n", "3412 3 " ] }, "metadata": { "tags": [] }, "execution_count": 24 } ] }, { "metadata": { "id": "WNHx57DiufCl", "colab_type": "code", "outputId": "78fd04c0-7382-4a04-efe2-acfe0bad7cf0", "colab": { "base_uri": "https://localhost:8080/", "height": 142 } }, "cell_type": "code", "source": [ "#@title Top three locations in Great Britain by Sulphur Dioxide (SO2)\n", "(p.query('pollutant == \"so2\" & country == \"GB\"')\n", " .nsmallest(3, 'country_rank')\n", " [['city', 'location', 'value', 'world_rank', 'country_rank']])" ], "execution_count": 0, "outputs": [ { "output_type": "execute_result", "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
citylocationvalueworld_rankcountry_rank
19339MiddlesbroughMiddlesbrough19.03231
17408SouthamptonSouthampton Centre14.04972
1188DerryDerry Rosemount13.05453
\n", "
" ], "text/plain": [ " city location value world_rank country_rank\n", "19339 Middlesbrough Middlesbrough 19.0 323 1\n", "17408 Southampton Southampton Centre 14.0 497 2\n", "1188 Derry Derry Rosemount 13.0 545 3" ] }, "metadata": { "tags": [] }, "execution_count": 25 } ] }, { "metadata": { "colab_type": "code", "id": "4gNbyccWvIFR", "outputId": "aa74d56c-eb15-42ce-f65e-f5e8f986e7f7", "colab": { "base_uri": "https://localhost:8080/", "height": 142 } }, "cell_type": "code", "source": [ "#@title Top three locations by PM2.5\n", "(p.query('pollutant == \"pm25\"')\n", " .nsmallest(3, 'world_rank')\n", " [['country', 'city', 'location', 'value', 'world_rank']])" ], "execution_count": 0, "outputs": [ { "output_type": "execute_result", "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
countrycitylocationvalueworld_rank
9849CN义乌市江东690.01
1695INPatnaIGSC Planetarium Complex, Patna - BSPCB674.02
9845CN义乌市北苑494.03
\n", "
" ], "text/plain": [ " country city location value \\\n", "9849 CN 义乌市 江东 690.0 \n", "1695 IN Patna IGSC Planetarium Complex, Patna - BSPCB 674.0 \n", "9845 CN 义乌市 北苑 494.0 \n", "\n", " world_rank \n", "9849 1 \n", "1695 2 \n", "9845 3 " ] }, "metadata": { "tags": [] }, "execution_count": 26 } ] }, { "metadata": { "colab_type": "code", "id": "aFr052OMvIFp", "outputId": "d136ba42-6d47-4e43-f6c7-076d0a566cc6", "colab": { "base_uri": "https://localhost:8080/", "height": 142 } }, "cell_type": "code", "source": [ "#@title Top three locations in Great Britain by PM2.5\n", "(p.query('pollutant == \"pm25\" & country == \"GB\"')\n", " .nsmallest(3, 'country_rank')\n", " [['city', 'location', 'value', 'world_rank', 'country_rank']])" ], "execution_count": 0, "outputs": [ { "output_type": "execute_result", "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
citylocationvalueworld_rankcountry_rank
17442StorringtonStorrington Roadside62.05231
19611West MidlandsBirmingham A4540 Roadside28.011182
8973NewcastleNewcastle Centre17.016723
\n", "
" ], "text/plain": [ " city location value world_rank \\\n", "17442 Storrington Storrington Roadside 62.0 523 \n", "19611 West Midlands Birmingham A4540 Roadside 28.0 1118 \n", "8973 Newcastle Newcastle Centre 17.0 1672 \n", "\n", " country_rank \n", "17442 1 \n", "19611 2 \n", "8973 3 " ] }, "metadata": { "tags": [] }, "execution_count": 27 } ] }, { "metadata": { "id": "U2wl_l8UwDVH", "colab_type": "code", "outputId": "e0d956e2-ccfa-43a6-f7e2-cd46e806825a", "colab": { "base_uri": "https://localhost:8080/", "height": 111 } }, "cell_type": "code", "source": [ "#@title Compare the worst locations (NO2) in Great Britain and Sweden\n", "(p.query('pollutant == \"no2\"')\n", " .query('country_rank == 1')\n", " .query('(country == \"GB\") | (country == \"SE\")')\n", " [['country', 'city', 'location', 'value', 'world_rank', 'country_rank']])" ], "execution_count": 0, "outputs": [ { "output_type": "execute_result", "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
countrycitylocationvalueworld_rankcountry_rank
4585GBBelfastBelfast Stockman's Lane63.00003591
9241SEStockholmSveavägen42.61759171
\n", "
" ], "text/plain": [ " country city location value world_rank \\\n", "4585 GB Belfast Belfast Stockman's Lane 63.0000 359 \n", "9241 SE Stockholm Sveavägen 42.6175 917 \n", "\n", " country_rank \n", "4585 1 \n", "9241 1 " ] }, "metadata": { "tags": [] }, "execution_count": 31 } ] }, { "metadata": { "id": "F8kCr7r4ulXZ", "colab_type": "text" }, "cell_type": "markdown", "source": [ "Note that we can also do a lot of the work using BigQuery. But since the dataset is fairly small, we don't need to do that. Example on how to compute the rank in BigQuery:" ] }, { "metadata": { "id": "ubfCRGbmg34B", "colab_type": "code", "outputId": "931a6671-2d92-4ec2-e704-362177d0861f", "colab": { "base_uri": "https://localhost:8080/", "height": 1969 } }, "cell_type": "code", "source": [ "#@title Rank cities within each country for each pollutant\n", "%%bigquery --project silicon-perigee-208521\n", "#standardSQL\n", "select\n", " pollutant,\n", " country,\n", " city,\n", " location,\n", " value,\n", " rank() over (\n", " partition by\n", " pollutant,\n", " country\n", " order by value desc) as world_rank\n", "from `bigquery-public-data.openaq.global_air_quality`;" ], "execution_count": 0, "outputs": [ { "output_type": "execute_result", "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
pollutantcountrycitylocationvalueworld_rank
0bcPLKielceKielce, ul. Targowa5.730001
1bcPLRybnikRybnik, ul. Borki 37 d5.270912
2bcPLNowy TargNowy Targ, Plac Słowackiego4.701683
3bcPLCzerwionka-LeszczynyCzerwionka-Leszczyny, ul. Kopalniana4.249804
4bcPLZamośćZamość ul. Hrubieszowska 69A2.980005
5bcPLZgorzelecZgorzelec - Bohaterów Getta2.792476
6bcPLMielecMielec-Biernackiego-WIOS2.447037
7bcPLŁódźŁódź-Gdańska 162.400008
8bcPLRadomRadom-Tochtermana2.137009
9bcPLStarachowiceStarachowice, ul. Złota2.0500010
10bcPLNałęczówNałęczówMOB1.8300011
11bcPLElblągWIOŚ Elbląg ul. Bażyńskiego1.7144112
12bcPLCiechocinekCiechocinek1.6700013
13bcPLOlsztynWIOŚ Olsztyn ul. Puszkina1.6011614
14bcPLLublinLublin ul. Obywatelska1.5800015
15bcPLPoznańPoznan-Dabrowskiego1.5460216
16bcPLŻaryŻary, ul. Szymanowskiego 81.5220817
17bcPLDąbrowa GórniczaDąbrowa Górnicza, ul. Tysiąclecia 25a1.4789518
18bcPLZakopaneZakopane, ul. Sienkiewicza1.4085519
19bcPLBydgoszczBydgoszcz Plac Poznański1.4000020
20bcPLZłoty PotokZłoty Potok, Leśniczówka1.3088821
21bcPLRzeszówRzeszów-Piłsudskiego1.2819622
22bcPLLegnicaLegnica - Rzeczypospolitej1.2442523
23bcPLJelenia GóraJelenia Góra - Ogińskiego1.2231624
24bcPLRzeszówRzeszów-Nowe Miasto1.2013225
25bcPLKędzierzyn-KoźleK-Koźle automat 11.1755926
26bcPLZdzieszowiceZdzieszowice automat 21.0731927
27bcPLKrakówKraków, ul. Bujaka1.0471828
28bcPLOpoleOpole manualna 40.9503929
29bcPLGdańskAM2 Gdańsk Stogi0.9503929
.....................
25170so2USHonoluluKapolei0.00000116
25171so2USHonoluluHonolulu0.00000116
25172so2USLakelandSikes Elementary Sch0.00000116
25173so2USMuskogeeMuskogee0.00000116
25174so2USMuskogeeFt. Gibson0.00000116
25175so2USRichmondSHIRLEY PLANTATION0.00000116
25176so2USSheridanSheridan Mobile0.00000116
25177so2USARCHULETA70040.00000116
25178so2USAROOSTOOKMICMAC0.00000116
25179so2USAshevilleSkyland DRR0.00000116
25180so2USHoustonClinton C403/C304/AH113-0.00010282
25181so2USHouston-Sugar Land-BaytownHouston Croquet C409-0.00010282
25182so2USHoustonBaytown Garth C1017-0.00010282
25183so2USHoustonPark Place C416-0.00020285
25184so2USHoustonHouston Monroe C406-0.00040286
25185so2USKingsport-Bristol-BristolAndrew Johnson Elem.-0.00100287
25186so2USCheyenneCheyenne Mobile-0.00100287
25187so2USEvansvilleEvansville-Buena Vis-0.00100287
25188so2USBirmingham-HooverLNA-0.00100287
25189so2USLEEBlackstone-0.00100287
25190so2XKPrishtinePrishtine, Rilindje23.356001
25191so2XKGjilanGjilan16.400002
25192so2XKPrizrenPrizren6.461003
25193so2XKDrenasDrenas6.366004
25194so2XKMitroviceMitrovice5.892005
25195so2XKPrishtinePrishtine - IHMK1.796006
25196so2XKHani i ElezitHani i Elezit1.574007
25197so2ZARichards BayBrackenham0.001801
25198so2ZARichards BayArboretum-0.000902
25199so2ZARichards BayCBD-0.001903
\n", "

25200 rows × 6 columns

\n", "
" ], "text/plain": [ " pollutant country city \\\n", "0 bc PL Kielce \n", "1 bc PL Rybnik \n", "2 bc PL Nowy Targ \n", "3 bc PL Czerwionka-Leszczyny \n", "4 bc PL Zamość \n", "5 bc PL Zgorzelec \n", "6 bc PL Mielec \n", "7 bc PL Łódź \n", "8 bc PL Radom \n", "9 bc PL Starachowice \n", "10 bc PL Nałęczów \n", "11 bc PL Elbląg \n", "12 bc PL Ciechocinek \n", "13 bc PL Olsztyn \n", "14 bc PL Lublin \n", "15 bc PL Poznań \n", "16 bc PL Żary \n", "17 bc PL Dąbrowa Górnicza \n", "18 bc PL Zakopane \n", "19 bc PL Bydgoszcz \n", "20 bc PL Złoty Potok \n", "21 bc PL Rzeszów \n", "22 bc PL Legnica \n", "23 bc PL Jelenia Góra \n", "24 bc PL Rzeszów \n", "25 bc PL Kędzierzyn-Koźle \n", "26 bc PL Zdzieszowice \n", "27 bc PL Kraków \n", "28 bc PL Opole \n", "29 bc PL Gdańsk \n", "... ... ... ... \n", "25170 so2 US Honolulu \n", "25171 so2 US Honolulu \n", "25172 so2 US Lakeland \n", "25173 so2 US Muskogee \n", "25174 so2 US Muskogee \n", "25175 so2 US Richmond \n", "25176 so2 US Sheridan \n", "25177 so2 US ARCHULETA \n", "25178 so2 US AROOSTOOK \n", "25179 so2 US Asheville \n", "25180 so2 US Houston \n", "25181 so2 US Houston-Sugar Land-Baytown \n", "25182 so2 US Houston \n", "25183 so2 US Houston \n", "25184 so2 US Houston \n", "25185 so2 US Kingsport-Bristol-Bristol \n", "25186 so2 US Cheyenne \n", "25187 so2 US Evansville \n", "25188 so2 US Birmingham-Hoover \n", "25189 so2 US LEE \n", "25190 so2 XK Prishtine \n", "25191 so2 XK Gjilan \n", "25192 so2 XK Prizren \n", "25193 so2 XK Drenas \n", "25194 so2 XK Mitrovice \n", "25195 so2 XK Prishtine \n", "25196 so2 XK Hani i Elezit \n", "25197 so2 ZA Richards Bay \n", "25198 so2 ZA Richards Bay \n", "25199 so2 ZA Richards Bay \n", "\n", " location value world_rank \n", "0 Kielce, ul. Targowa 5.73000 1 \n", "1 Rybnik, ul. Borki 37 d 5.27091 2 \n", "2 Nowy Targ, Plac Słowackiego 4.70168 3 \n", "3 Czerwionka-Leszczyny, ul. Kopalniana 4.24980 4 \n", "4 Zamość ul. Hrubieszowska 69A 2.98000 5 \n", "5 Zgorzelec - Bohaterów Getta 2.79247 6 \n", "6 Mielec-Biernackiego-WIOS 2.44703 7 \n", "7 Łódź-Gdańska 16 2.40000 8 \n", "8 Radom-Tochtermana 2.13700 9 \n", "9 Starachowice, ul. Złota 2.05000 10 \n", "10 NałęczówMOB 1.83000 11 \n", "11 WIOŚ Elbląg ul. Bażyńskiego 1.71441 12 \n", "12 Ciechocinek 1.67000 13 \n", "13 WIOŚ Olsztyn ul. Puszkina 1.60116 14 \n", "14 Lublin ul. Obywatelska 1.58000 15 \n", "15 Poznan-Dabrowskiego 1.54602 16 \n", "16 Żary, ul. Szymanowskiego 8 1.52208 17 \n", "17 Dąbrowa Górnicza, ul. Tysiąclecia 25a 1.47895 18 \n", "18 Zakopane, ul. Sienkiewicza 1.40855 19 \n", "19 Bydgoszcz Plac Poznański 1.40000 20 \n", "20 Złoty Potok, Leśniczówka 1.30888 21 \n", "21 Rzeszów-Piłsudskiego 1.28196 22 \n", "22 Legnica - Rzeczypospolitej 1.24425 23 \n", "23 Jelenia Góra - Ogińskiego 1.22316 24 \n", "24 Rzeszów-Nowe Miasto 1.20132 25 \n", "25 K-Koźle automat 1 1.17559 26 \n", "26 Zdzieszowice automat 2 1.07319 27 \n", "27 Kraków, ul. Bujaka 1.04718 28 \n", "28 Opole manualna 4 0.95039 29 \n", "29 AM2 Gdańsk Stogi 0.95039 29 \n", "... ... ... ... \n", "25170 Kapolei 0.00000 116 \n", "25171 Honolulu 0.00000 116 \n", "25172 Sikes Elementary Sch 0.00000 116 \n", "25173 Muskogee 0.00000 116 \n", "25174 Ft. Gibson 0.00000 116 \n", "25175 SHIRLEY PLANTATION 0.00000 116 \n", "25176 Sheridan Mobile 0.00000 116 \n", "25177 7004 0.00000 116 \n", "25178 MICMAC 0.00000 116 \n", "25179 Skyland DRR 0.00000 116 \n", "25180 Clinton C403/C304/AH113 -0.00010 282 \n", "25181 Houston Croquet C409 -0.00010 282 \n", "25182 Baytown Garth C1017 -0.00010 282 \n", "25183 Park Place C416 -0.00020 285 \n", "25184 Houston Monroe C406 -0.00040 286 \n", "25185 Andrew Johnson Elem. -0.00100 287 \n", "25186 Cheyenne Mobile -0.00100 287 \n", "25187 Evansville-Buena Vis -0.00100 287 \n", "25188 LNA -0.00100 287 \n", "25189 Blackstone -0.00100 287 \n", "25190 Prishtine, Rilindje 23.35600 1 \n", "25191 Gjilan 16.40000 2 \n", "25192 Prizren 6.46100 3 \n", "25193 Drenas 6.36600 4 \n", "25194 Mitrovice 5.89200 5 \n", "25195 Prishtine - IHMK 1.79600 6 \n", "25196 Hani i Elezit 1.57400 7 \n", "25197 Brackenham 0.00180 1 \n", "25198 Arboretum -0.00090 2 \n", "25199 CBD -0.00190 3 \n", "\n", "[25200 rows x 6 columns]" ] }, "metadata": { "tags": [] }, "execution_count": 12 } ] } ] }