open_air_quality_query.ipynb

{
  "cells": [
    {
      "metadata": {
        "_uuid": "8f2839f25d086af736a60e9eeb907d3b93b6e0e5",
        "_cell_guid": "b1076dfc-b9ad-4769-8c92-a6c4dae69d19"
      },
      "cell_type": "markdown",
      "source": "# Get Started\nFork this notebook by hitting the blue \"Fork Notebook\" button at the top of this page.  \"Forking\" makes a copy that you can edit on your own without changing the original.\n\nAfter forking this notebook, run the code in the following cell."
    },
    {
      "metadata": {
        "_uuid": "10e9310686ffa8666fddf643693d27bf6c2da62f",
        "_cell_guid": "581115e3-6443-4d88-aebc-2b7b64e10312",
        "trusted": true
      },
      "cell_type": "code",
      "source": "# import package with helper functions \nimport bq_helper\n\n# create a helper object for this dataset\nopen_aq = bq_helper.BigQueryHelper(active_project=\"bigquery-public-data\",\n                                   dataset_name=\"openaq\")\n\n# print all the tables in this dataset (there's only one!)\nopen_aq.list_tables()",
      "execution_count": 1,
      "outputs": [
        {
          "output_type": "execute_result",
          "execution_count": 1,
          "data": {
            "text/plain": "['global_air_quality']"
          },
          "metadata": {}
        }
      ]
    },
    {
      "metadata": {
        "_uuid": "40f9f29f2ff5f355747bcd8ddb83bf5beecdb04e",
        "_cell_guid": "6c5a2cf7-e634-49fe-8f66-fec2386128e2"
      },
      "cell_type": "markdown",
      "source": "Then write and run the code to answer the questions below."
    },
    {
      "metadata": {
        "_uuid": "e0c53920e3b893930976a772c17f67f9781087ec",
        "_cell_guid": "00f8161f-9d76-4740-b6b1-d245b00f4587"
      },
      "cell_type": "markdown",
      "source": "# Question\n\n#### 1) Which countries use a unit other than ppm to measure any type of pollution? \n(Hint: to get rows where the value *isn't* something, use \"!=\")"
    },
    {
      "metadata": {
        "_uuid": "d629ff2d2480ee46fbb7e2d37f6b5fab8052498a",
        "_cell_guid": "79c7e3d0-c299-4dcb-8224-4455121ee9b0",
        "trusted": true
      },
      "cell_type": "code",
      "source": "# Your Code Goes Here\nquery = \"\"\" SELECT country\n            FROM `bigquery-public-data.openaq.global_air_quality`\n            WHERE unit != \"ppm\" \"\"\"\ncountries_no_ppm = open_aq.query_to_pandas_safe(query, max_gb_scanned=1.0)\ncountries_no_ppm.country.value_counts()",
      "execution_count": 2,
      "outputs": [
        {
          "output_type": "execute_result",
          "execution_count": 2,
          "data": {
            "text/plain": "ES    1900\nFR    1569\nDE    1402\nUS    1302\nIN     816\nAT     786\nTR     492\nGB     407\nCL     407\nCN     384\nBE     378\nCZ     328\nIT     322\nNL     279\nPT     247\nBR     188\nNO     164\nTW     154\nAU     149\nSK     132\nMN     127\nRU     124\nHU     123\nFI     106\nCH      97\nHK      89\nCA      88\nBA      75\nMK      71\nMX      70\n      ... \nDK      31\nIE      30\nLU      26\nSE      24\nGH      22\nMT      18\nLV      13\nSI       8\nRS       8\nAD       7\nGI       6\nKE       4\nAR       4\nID       3\nVN       3\nET       2\nBD       2\nNP       2\nZA       2\nAE       1\nLK       1\nBH       1\nNG       1\nCO       1\nUG       1\nPH       1\nXK       1\nKW       1\nIL       1\nSG       1\nName: country, Length: 65, dtype: int64"
          },
          "metadata": {}
        }
      ]
    },
    {
      "metadata": {
        "_uuid": "a527d39f5e9e31ae5476505249d1136558e97ede",
        "_cell_guid": "f50aa855-5ae4-4dbd-a7f4-1c0940a76e41"
      },
      "cell_type": "markdown",
      "source": "#### 2) Which pollutants have a value of exactly 0?"
    },
    {
      "metadata": {
        "_uuid": "ac2253ac8ff931cf984f4c80f4fee1aafaa71789",
        "_cell_guid": "0bfeb274-f8ed-4710-a6db-e1e6457e78c4",
        "trusted": true
      },
      "cell_type": "code",
      "source": "# Your Code Goes Here\nquery = \"\"\" SELECT pollutant\n            FROM `bigquery-public-data.openaq.global_air_quality`\n            WHERE value = 0 \"\"\"\npollutant_zero = open_aq.query_to_pandas_safe(query, max_gb_scanned=1.0)\npollutant_zero.pollutant.value_counts()",
      "execution_count": 3,
      "outputs": [
        {
          "output_type": "execute_result",
          "execution_count": 3,
          "data": {
            "text/plain": "so2     337\nno2     152\nco      124\npm10    101\no3       91\npm25     77\nbc        1\nName: pollutant, dtype: int64"
          },
          "metadata": {}
        }
      ]
    },
    {
      "metadata": {
        "_uuid": "2601df8b4255ca1087f794e6f39ca04107e93de6",
        "_cell_guid": "3b323b31-ee71-466f-a328-3b6f83598878"
      },
      "cell_type": "markdown",
      "source": "# Keep Going\nAfter finishing this exercise, click [here](https://www.kaggle.com/dansbecker/group-by-having-count/).  You will learn about the **GROUP BY** command and its extensions.  This is especially valuable in large datasets like what you find in BigQuery.\n\n# Help and Feedback \nBring any comments or questions to the [Learn Discussion Forum](https://www.kaggle.com/learn-forum).\n\nIf you want comments or help on your code, make it \"public\" first using the \"Settings\" tab on this page.\n\n---\n\n*This tutorial is part of the [SQL Series](https://www.kaggle.com/learn/sql) on Kaggle Learn.*"
    }
  ],
  "metadata": {
    "kernelspec": {
      "display_name": "Python 3",
      "language": "python",
      "name": "python3"
    },
    "language_info": {
      "name": "python",
      "version": "3.6.6",
      "mimetype": "text/x-python",
      "codemirror_mode": {
        "name": "ipython",
        "version": 3
      },
      "pygments_lexer": "ipython3",
      "nbconvert_exporter": "python",
      "file_extension": ".py"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 1
}