Week10-Exercise1-BigQuery.ipynb

{"nbformat":4,"nbformat_minor":0,"metadata":{"colab":{"name":"Week10- Exercise1-BigQuery.ipynb","provenance":[{"file_id":"1iF5h-8Vz2l_OqpJwgKeT6Kquk9BvzSc-","timestamp":1607382881698}],"collapsed_sections":[],"authorship_tag":"ABX9TyN6+JuyqSzQqfWDzMb5Z+mK"},"kernelspec":{"name":"python3","display_name":"Python 3"}},"cells":[{"cell_type":"markdown","metadata":{"id":"wIAXoU2VLsGP"},"source":["# Tasks\n","\n","- We will work with Google BigQuery from Colab\n","- Some part of the instructions is coming from https://colab.research.google.com/notebooks/bigquery.ipynb\n","- We will use covid-19-public-data\n","- In particular, we use extract the UK data\n","  1. Check what kind of data are available\n","  2. Find out what we can do with the data\n","\n"]},{"cell_type":"markdown","metadata":{"id":"H1nUZfvpMfzf"},"source":["## Authentication\n","\n","The first step is to "]},{"cell_type":"code","metadata":{"colab":{"base_uri":"https://localhost:8080/"},"id":"8gLcMGytLruW","executionInfo":{"status":"ok","timestamp":1607378251632,"user_tz":0,"elapsed":18939,"user":{"displayName":"Akitaka Matsuo","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14GjufrSJCzMPgfdmlZz3GFX5n09CyEWx1rg5lY90=s64","userId":"13583983347915062182"}},"outputId":"869cac3d-f982-446d-c8e6-c0f7a9a945fa"},"source":["from google.colab import auth\n","auth.authenticate_user()\n","print('Authenticated')"],"execution_count":null,"outputs":[{"output_type":"stream","text":["Authenticated\n"],"name":"stdout"}]},{"cell_type":"markdown","metadata":{"id":"5HnTw4X_MmOJ"},"source":["## Load pacakges"]},{"cell_type":"code","metadata":{"id":"Va7sjKyJLjcY"},"source":["from google.cloud import bigquery\n","import pandas as pd"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"9JEFeinsNhEu"},"source":["## Connect to big query"]},{"cell_type":"markdown","metadata":{"id":"tvdEdycIM39t"},"source":["Go to the console and check your project name and paste here"]},{"cell_type":"code","metadata":{"id":"3K5m03VpM13u"},"source":["project_id = \"\""],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"id":"GxGGYUXvNpQw"},"source":["client = bigquery.Client(project=project_id)"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"20-SSUOSM--9"},"source":["## Run queries\n","\n","We will use `bigquery-public-data.covid19_open_data.covid19_open_data`"]},{"cell_type":"markdown","metadata":{"id":"t3xk9dSQNMWH"},"source":["### Simple query\n","\n","Get five rows from the table\n","\n","- How many rows in the table?\n","- How many columns exist?"]},{"cell_type":"code","metadata":{"id":"fxc8pn3-NLrO"},"source":["df = client.query('''\n","''').to_dataframe()"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"j3-5mDRjOLuw"},"source":["### Explore the table\n","\n","- How many countries in the data\n","- How many UK rows?\n","- What's the geographical units for the UK subnational levels"]},{"cell_type":"code","metadata":{"id":"JSA5JT2kfQkX"},"source":[""],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"_NZQf_pJPvNz"},"source":["- What's the latest date in the UK?"]},{"cell_type":"code","metadata":{"id":"x4_AtTwafR5M"},"source":[""],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"NmT5Tq_RQIqN"},"source":["- On 1 December 2020, at subregion2 level\n","  - Which sub region had the highest number of qumulative cases"]},{"cell_type":"code","metadata":{"id":"R7uhP7XefUFW"},"source":[""],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"N2QRe4qtSRLQ"},"source":["- Mobility check on 1 Dec\n","  - How is the modiblity data look like?\n","    - `mobility_retail_and_recreation`\n","    - `mobility_parks`\n","    - `mobility_workplaces`"]},{"cell_type":"markdown","metadata":{"id":"YpM_Pi7xS09t"},"source":["## Work with the Essex data\n","\n","We can get the essex county data. Using it:\n","\n","- Plot the case figure growth by date\n","- Plot mobility changes by date\n","\n","Anything interesting?\n"]},{"cell_type":"code","metadata":{"id":"eVCmOU4qVh8U"},"source":["  "],"execution_count":null,"outputs":[]}]}