extract_and_load.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "Projeto Gama Parte 1: SQL, Python e AWS.ipynb",
      "provenance": [],
      "collapsed_sections": []
    },
    "kernelspec": {
      "display_name": "Python 3",
      "name": "python3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "032Aaw5J_8HL"
      },
      "source": [
        "# Extract and Load Data"
      ]
    },
    {
      "source": [
        "## Installing dependencies"
      ],
      "cell_type": "markdown",
      "metadata": {}
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "dta_G6Tv_--p",
        "outputId": "3b9b8c82-5708-4989-9d08-a3d5c109b858"
      },
      "source": [
        "!pip install boto3\n",
        "!pip install mysql-connector-python"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Collecting boto3\n",
            "\u001b[?25l  Downloading https://files.pythonhosted.org/packages/e6/94/4774265ce7a53c38f93e062e8b5ffea3f0ec1efde9e8d91d81e2d6ccd7e8/boto3-1.17.93-py2.py3-none-any.whl (131kB)\n",
            "\u001b[K     |████████████████████████████████| 133kB 31.7MB/s \n",
            "\u001b[?25hCollecting jmespath<1.0.0,>=0.7.1\n",
            "  Downloading https://files.pythonhosted.org/packages/07/cb/5f001272b6faeb23c1c9e0acc04d48eaaf5c862c17709d20e3469c6e0139/jmespath-0.10.0-py2.py3-none-any.whl\n",
            "Collecting botocore<1.21.0,>=1.20.93\n",
            "\u001b[?25l  Downloading https://files.pythonhosted.org/packages/cb/29/043bafa9c268b3fbcc12f25f1d9a7d963272e6ec386045c4425323002f80/botocore-1.20.93.tar.gz (7.8MB)\n",
            "\u001b[K     |████████████████████████████████| 7.8MB 40.2MB/s \n",
            "\u001b[?25hCollecting s3transfer<0.5.0,>=0.4.0\n",
            "\u001b[?25l  Downloading https://files.pythonhosted.org/packages/63/d0/693477c688348654ddc21dcdce0817653a294aa43f41771084c25e7ff9c7/s3transfer-0.4.2-py2.py3-none-any.whl (79kB)\n",
            "\u001b[K     |████████████████████████████████| 81kB 12.2MB/s \n",
            "\u001b[?25hRequirement already satisfied: python-dateutil<3.0.0,>=2.1 in /usr/local/lib/python3.7/dist-packages (from botocore<1.21.0,>=1.20.93->boto3) (2.8.1)\n",
            "Collecting urllib3<1.27,>=1.25.4\n",
            "\u001b[?25l  Downloading https://files.pythonhosted.org/packages/0c/cd/1e2ec680ec7b09846dc6e605f5a7709dfb9d7128e51a026e7154e18a234e/urllib3-1.26.5-py2.py3-none-any.whl (138kB)\n",
            "\u001b[K     |████████████████████████████████| 143kB 60.3MB/s \n",
            "\u001b[?25hRequirement already satisfied: six>=1.5 in /usr/local/lib/python3.7/dist-packages (from python-dateutil<3.0.0,>=2.1->botocore<1.21.0,>=1.20.93->boto3) (1.15.0)\n",
            "Building wheels for collected packages: botocore\n",
            "  Building wheel for botocore (setup.py) ... \u001b[?25l\u001b[?25hdone\n",
            "  Created wheel for botocore: filename=botocore-1.20.93-py2.py3-none-any.whl size=7615687 sha256=a1d3cb30f524747e20b7af0a55ac81ee86b50f3e3fe9493972d54950978f20ef\n",
            "  Stored in directory: /root/.cache/pip/wheels/8f/c8/ec/4e78ad6cfd59266c51515843591b22790eb4754bd644daaaca\n",
            "Successfully built botocore\n",
            "\u001b[31mERROR: requests 2.23.0 has requirement urllib3!=1.25.0,!=1.25.1,<1.26,>=1.21.1, but you'll have urllib3 1.26.5 which is incompatible.\u001b[0m\n",
            "\u001b[31mERROR: datascience 0.10.6 has requirement folium==0.2.1, but you'll have folium 0.8.3 which is incompatible.\u001b[0m\n",
            "Installing collected packages: jmespath, urllib3, botocore, s3transfer, boto3\n",
            "  Found existing installation: urllib3 1.24.3\n",
            "    Uninstalling urllib3-1.24.3:\n",
            "      Successfully uninstalled urllib3-1.24.3\n",
            "Successfully installed boto3-1.17.93 botocore-1.20.93 jmespath-0.10.0 s3transfer-0.4.2 urllib3-1.26.5\n",
            "Collecting mysql-connector-python\n",
            "\u001b[?25l  Downloading https://files.pythonhosted.org/packages/ef/5b/a7dc32e711e4a065896188afef6864489ccf4bdab0928581c4262e84110d/mysql_connector_python-8.0.25-cp37-cp37m-manylinux1_x86_64.whl (25.4MB)\n",
            "\u001b[K     |████████████████████████████████| 25.4MB 46.3MB/s \n",
            "\u001b[?25hRequirement already satisfied: protobuf>=3.0.0 in /usr/local/lib/python3.7/dist-packages (from mysql-connector-python) (3.12.4)\n",
            "Requirement already satisfied: setuptools in /usr/local/lib/python3.7/dist-packages (from protobuf>=3.0.0->mysql-connector-python) (57.0.0)\n",
            "Requirement already satisfied: six>=1.9 in /usr/local/lib/python3.7/dist-packages (from protobuf>=3.0.0->mysql-connector-python) (1.15.0)\n",
            "Installing collected packages: mysql-connector-python\n",
            "Successfully installed mysql-connector-python-8.0.25\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "source": [
        "## Importing dependencies"
      ],
      "cell_type": "markdown",
      "metadata": {}
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "import json\n",
        "import boto3\n",
        "import datetime\n",
        "import requests\n",
        "import pandas as pd\n",
        "from time import sleep\n",
        "from tabulate import tabulate\n",
        "from mysql.connector import connect\n",
        "from configparser import ConfigParser\n",
        "from botocore.exceptions import NoCredentialsError"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Z-vtEs2LFUzQ"
      },
      "source": [
        "## Creating Settings File in FS"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "aaPcVtL_Fjp_",
        "outputId": "f31d99a7-79b6-4395-d6b5-e77330201c38"
      },
      "source": [
        "from configparser import ConfigParser\n",
        "SETTINGS = ConfigParser()\n",
        "\n",
        "SETTINGS['S3_AUTH'] = {'ACCESS_KEY': '', 'SECRET_KEY': ''}\n",
        "SETTINGS['RDS_SETTINGS'] = {\n",
        "    'HOST': '',\n",
        "    'USER': '',\n",
        "    'PASSWORD': '',\n",
        "    'DATABASE': ''\n",
        "}\n",
        "\n",
        "# Creating \n",
        "with open('settings.ini', 'w') as file:\n",
        "  SETTINGS.write(file)\n",
        "\n",
        "# Reading file to memory\n",
        "SETTINGS.read(\"settings.ini\")"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "['settings.ini']"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 5
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "_RLsGXBudpfo"
      },
      "source": [
        "## Getting data from external API COVID-19"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "LQMOgNfBRXVK",
        "outputId": "47f81638-d318-4387-d417-c76413f6ba33"
      },
      "source": [
        "url = 'https://api.covid19api.com/countries'\n",
        "countries = requests.request(\"GET\", url).json()\n",
        "\n",
        "measurements = []\n",
        "print('Getting Measurements')\n",
        "for country in countries:\n",
        "  if country['Slug'] != 'united-states':\n",
        "    url = f\"https://api.covid19api.com/country/{country['Slug']}?from=2020-01-01T00:00:00Z&to=2021-06-09T00:00:00Z\"\n",
        "    data = requests.request(\"GET\", url).json()\n",
        "    sleep(1)\n",
        "    print(f\"{country['Slug']} - {len(data)}\")\n",
        "    measurements += data\n",
        "\n",
        "# Getting US data\n",
        "week_days = pd.date_range(start='2020-01-01', end='2021-06-09', freq='7d')\n",
        "for week_day in week_days:\n",
        "  week_ago = week_day - datetime.timedelta(days=6)\n",
        "  url = f\"https://api.covid19api.com/country/united-states?from={week_ago}&to={week_day}\"\n",
        "  data = requests.request(\"GET\", url).json()\n",
        "  sleep(1)\n",
        "  measurements += filter(lambda measurement: measurement['Province'] == \"\" , data)\n",
        "  print(f\"united-states - {len(data)}\")\n",
        "\n",
        "\n",
        "print('Measurements obtained')"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "/usr/local/lib/python3.7/dist-packages/requests/__init__.py:91: RequestsDependencyWarning: urllib3 (1.26.5) or chardet (3.0.4) doesn't match a supported version!\n",
            "  RequestsDependencyWarning)\n"
          ],
          "name": "stderr"
        },
        {
          "output_type": "stream",
          "text": [
            "Getting Measurements\n",
            "angola - 505\n",
            "luxembourg - 505\n",
            "american-samoa - 0\n",
            "mongolia - 505\n",
            "morocco - 505\n",
            "rwanda - 505\n",
            "british-indian-ocean-territory - 0\n",
            "bosnia-and-herzegovina - 505\n",
            "costa-rica - 505\n",
            "kenya - 505\n",
            "niger - 505\n",
            "tajikistan - 505\n",
            "ukraine - 505\n",
            "wallis-and-futuna-islands - 0\n",
            "bulgaria - 505\n",
            "armenia - 505\n",
            "haiti - 505\n",
            "monaco - 505\n",
            "poland - 505\n",
            "antigua-and-barbuda - 505\n",
            "cocos-keeling-islands - 0\n",
            "macedonia - 505\n",
            "australia - 4537\n",
            "chad - 505\n",
            "denmark - 1515\n",
            "papua-new-guinea - 505\n",
            "philippines - 505\n",
            "spain - 505\n",
            "tanzania - 505\n",
            "colombia - 505\n",
            "jamaica - 505\n",
            "timor-leste - 505\n",
            "ecuador - 505\n",
            "russia - 505\n",
            "réunion - 0\n",
            "saint-kitts-and-nevis - 505\n",
            "belgium - 505\n",
            "norfolk-island - 0\n",
            "solomon-islands - 505\n",
            "uzbekistan - 505\n",
            "antarctica - 0\n",
            "botswana - 505\n",
            "british-virgin-islands - 0\n",
            "chile - 505\n",
            "united-arab-emirates - 505\n",
            "estonia - 505\n",
            "myanmar - 505\n",
            "south-sudan - 505\n",
            "zimbabwe - 505\n",
            "brunei - 505\n",
            "anguilla - 0\n",
            "portugal - 505\n",
            "sri-lanka - 505\n",
            "grenada - 505\n",
            "korea-north - 0\n",
            "cameroon - 505\n",
            "lithuania - 505\n",
            "micronesia - 505\n",
            "vietnam - 505\n",
            "romania - 505\n",
            "sierra-leone - 505\n",
            "bahrain - 505\n",
            "turks-and-caicos-islands - 0\n",
            "congo-brazzaville - 505\n",
            "isle-of-man - 0\n",
            "lebanon - 505\n",
            "saint-barthélemy - 0\n",
            "saudi-arabia - 505\n",
            "yemen - 505\n",
            "france - 6060\n",
            "czech-republic - 505\n",
            "latvia - 505\n",
            "saint-pierre-and-miquelon - 0\n",
            "serbia - 505\n",
            "benin - 505\n",
            "saint-helena - 0\n",
            "central-african-republic - 505\n",
            "kyrgyzstan - 505\n",
            "tonga - 0\n",
            "venezuela - 505\n",
            "georgia - 505\n",
            "comoros - 505\n",
            "faroe-islands - 0\n",
            "greenland - 0\n",
            "guernsey - 0\n",
            "indonesia - 505\n",
            "israel - 505\n",
            "afghanistan - 505\n",
            "ala-aland-islands - 0\n",
            "singapore - 505\n",
            "sudan - 505\n",
            "hong-kong-sar-china - 0\n",
            "palestine - 505\n",
            "belize - 505\n",
            "cayman-islands - 0\n",
            "maldives - 505\n",
            "ireland - 505\n",
            "algeria - 505\n",
            "aruba - 0\n",
            "japan - 505\n",
            "jersey - 0\n",
            "mozambique - 505\n",
            "uruguay - 505\n",
            "syria - 505\n",
            "niue - 0\n",
            "bahamas - 505\n",
            "korea-south - 505\n",
            "malta - 505\n",
            "netherlands - 2525\n",
            "christmas-island - 0\n",
            "paraguay - 505\n",
            "eritrea - 505\n",
            "guadeloupe - 0\n",
            "namibia - 505\n",
            "brazil - 505\n",
            "lesotho - 505\n",
            "mali - 505\n",
            "suriname - 505\n",
            "trinidad-and-tobago - 505\n",
            "dominica - 505\n",
            "guinea-bissau - 505\n",
            "lao-pdr - 505\n",
            "panama - 505\n",
            "qatar - 505\n",
            "tunisia - 505\n",
            "cyprus - 505\n",
            "burundi - 505\n",
            "cape-verde - 505\n",
            "guinea - 505\n",
            "mauritania - 505\n",
            "burkina-faso - 505\n",
            "malawi - 505\n",
            "san-marino - 505\n",
            "seychelles - 505\n",
            "macao-sar-china - 0\n",
            "belarus - 505\n",
            "egypt - 505\n",
            "mexico - 505\n",
            "nigeria - 505\n",
            "oman - 505\n",
            "palau - 0\n",
            "china - 17162\n",
            "cote-divoire - 505\n",
            "guyana - 505\n",
            "kiribati - 505\n",
            "northern-mariana-islands - 0\n",
            "el-salvador - 505\n",
            "finland - 505\n",
            "liechtenstein - 505\n",
            "norway - 505\n",
            "sao-tome-and-principe - 505\n",
            "slovenia - 505\n",
            "austria - 505\n",
            "french-polynesia - 0\n",
            "hungary - 505\n",
            "iraq - 505\n",
            "jordan - 505\n",
            "libya - 505\n",
            "sweden - 505\n",
            "switzerland - 505\n",
            "congo-kinshasa - 505\n",
            "turkey - 505\n",
            "andorra - 505\n",
            "cook-islands - 0\n",
            "moldova - 505\n",
            "nicaragua - 505\n",
            "uganda - 505\n",
            "equatorial-guinea - 505\n",
            "nepal - 505\n",
            "saint-lucia - 505\n",
            "saint-vincent-and-the-grenadines - 505\n",
            "croatia - 505\n",
            "marshall-islands - 505\n",
            "south-africa - 505\n",
            "svalbard-and-jan-mayen-islands - 0\n",
            "swaziland - 505\n",
            "tuvalu - 0\n",
            "pitcairn - 0\n",
            "taiwan - 505\n",
            "us-minor-outlying-islands - 0\n",
            "french-southern-territories - 0\n",
            "montenegro - 505\n",
            "new-caledonia - 0\n",
            "united-kingdom - 6060\n",
            "zambia - 505\n",
            "djibouti - 505\n",
            "new-zealand - 505\n",
            "fiji - 505\n",
            "ghana - 505\n",
            "gibraltar - 0\n",
            "peru - 505\n",
            "guatemala - 505\n",
            "argentina - 505\n",
            "cuba - 505\n",
            "gambia - 505\n",
            "greece - 505\n",
            "holy-see-vatican-city-state - 505\n",
            "india - 505\n",
            "south-georgia-and-the-south-sandwich-islands - 0\n",
            "cambodia - 505\n",
            "falkland-islands-malvinas - 0\n",
            "iran - 505\n",
            "kazakhstan - 505\n",
            "mauritius - 505\n",
            "samoa - 505\n",
            "slovakia - 505\n",
            "thailand - 505\n",
            "barbados - 505\n",
            "honduras - 505\n",
            "senegal - 505\n",
            "kosovo - 505\n",
            "albania - 505\n",
            "bermuda - 0\n",
            "guam - 0\n",
            "iceland - 505\n",
            "liberia - 505\n",
            "madagascar - 505\n",
            "montserrat - 0\n",
            "togo - 505\n",
            "bouvet-island - 0\n",
            "dominican-republic - 505\n",
            "martinique - 0\n",
            "virgin-islands - 0\n",
            "ethiopia - 505\n",
            "germany - 505\n",
            "pakistan - 505\n",
            "canada - 505\n",
            "gabon - 505\n",
            "malaysia - 505\n",
            "somalia - 505\n",
            "bangladesh - 505\n",
            "italy - 505\n",
            "nauru - 0\n",
            "saint-martin-french-part - 0\n",
            "turkmenistan - 0\n",
            "heard-and-mcdonald-islands - 0\n",
            "bhutan - 505\n",
            "bolivia - 505\n",
            "french-guiana - 0\n",
            "netherlands-antilles - 0\n",
            "puerto-rico - 0\n",
            "azerbaijan - 505\n",
            "tokelau - 0\n",
            "vanuatu - 505\n",
            "western-sahara - 0\n",
            "mayotte - 0\n",
            "kuwait - 505\n",
            "united-states - 0\n",
            "united-states - 0\n",
            "united-states - 0\n",
            "united-states - 1\n",
            "united-states - 7\n",
            "united-states - 7\n",
            "united-states - 7\n",
            "united-states - 7\n",
            "united-states - 7\n",
            "united-states - 7\n",
            "united-states - 7\n",
            "united-states - 7\n",
            "united-states - 7\n",
            "united-states - 7\n",
            "united-states - 7\n",
            "united-states - 7\n",
            "united-states - 7\n",
            "united-states - 7\n",
            "united-states - 7\n",
            "united-states - 7\n",
            "united-states - 7\n",
            "united-states - 7\n",
            "united-states - 7\n",
            "united-states - 7\n",
            "united-states - 7\n",
            "united-states - 7\n",
            "united-states - 7\n",
            "united-states - 7\n",
            "united-states - 7\n",
            "united-states - 7\n",
            "united-states - 7\n",
            "united-states - 7\n",
            "united-states - 7\n",
            "united-states - 7\n",
            "united-states - 7\n",
            "united-states - 7\n",
            "united-states - 7\n",
            "united-states - 7\n",
            "united-states - 7\n",
            "united-states - 7\n",
            "united-states - 7\n",
            "united-states - 7\n",
            "united-states - 7\n",
            "united-states - 7\n",
            "united-states - 7\n",
            "united-states - 7\n",
            "united-states - 7\n",
            "united-states - 13351\n",
            "united-states - 23359\n",
            "united-states - 23359\n",
            "united-states - 23359\n",
            "united-states - 23359\n",
            "united-states - 23359\n",
            "united-states - 23359\n",
            "united-states - 23359\n",
            "united-states - 23352\n",
            "united-states - 23352\n",
            "united-states - 23352\n",
            "united-states - 23352\n",
            "united-states - 23352\n",
            "united-states - 23352\n",
            "united-states - 23352\n",
            "united-states - 23352\n",
            "united-states - 23352\n",
            "united-states - 23352\n",
            "united-states - 23352\n",
            "united-states - 23352\n",
            "united-states - 23352\n",
            "united-states - 23352\n",
            "united-states - 23352\n",
            "united-states - 23352\n",
            "united-states - 23352\n",
            "united-states - 23352\n",
            "united-states - 23352\n",
            "united-states - 23352\n",
            "united-states - 23352\n",
            "Measurements obtained\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "source": [
        "## Writing data in measurements.json "
      ],
      "cell_type": "markdown",
      "metadata": {}
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "nEngSZjCwrmr"
      },
      "source": [
        "with open('measurements.json', 'w') as outfile:\n",
        "    json.dump(measurements, outfile)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "KJRlABXEpeHN"
      },
      "source": [
        "## Uploading measurements to AWS S3 "
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "ThxJeYKXpqdN",
        "outputId": "bb12d26e-b647-48b6-a755-ae5004d89964"
      },
      "source": [
        "ACCESS_KEY = SETTINGS[\"S3_AUTH\"][\"ACCESS_KEY\"]\n",
        "SECRET_KEY = SETTINGS[\"S3_AUTH\"][\"SECRET_KEY\"]\n",
        "S3 = boto3.client('s3', aws_access_key_id=ACCESS_KEY,\n",
        "                  aws_secret_access_key=SECRET_KEY)\n",
        "\n",
        "\n",
        "def upload_to_aws(local_file, bucket, s3_file):\n",
        "    try:\n",
        "        S3.upload_file(local_file, bucket, s3_file)\n",
        "        print(\"Upload Successful\")\n",
        "        print(f'Uploaded file: {local_file}')\n",
        "        print(f'Upload to bucket: {bucket}')\n",
        "    except FileNotFoundError:\n",
        "        print(\"The file was not found\")\n",
        "    except NoCredentialsError:\n",
        "        print(\"Credentials not available\")\n",
        "\n",
        "local_file_name = '/content/measurements.json'\n",
        "bucket_name = 'ornitorrinco'\n",
        "s3_file_name = 'measurements.json'\n",
        "upload_to_aws(local_file_name, bucket_name, s3_file_name)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Upload Successful\n",
            "Uploaded file: /content/measurements.json\n",
            "Upload to bucket: ornitorrinco\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "8AyMikL0SJ4A"
      },
      "source": [
        "## Creating tables in AWS RDS - MySQL"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Levfaa2jIDbZ"
      },
      "source": [
        "from mysql.connector import connect\n",
        "\n",
        "mydb = connect(\n",
        "  host=SETTINGS[\"RDS_SETTINGS\"][\"HOST\"],\n",
        "  user=SETTINGS[\"RDS_SETTINGS\"][\"USER\"],\n",
        "  password=SETTINGS[\"RDS_SETTINGS\"][\"PASSWORD\"],\n",
        "  database=SETTINGS[\"RDS_SETTINGS\"][\"DATABASE\"]\n",
        ")\n",
        "mycursor = mydb.cursor()\n",
        "\n",
        "# Create table queries\n",
        "\n",
        "mycursor.execute(\"\"\"CREATE TABLE IF NOT EXISTS `Locations` (\n",
        "\t`Country` VARCHAR(255) NOT NULL,\n",
        "\t`CountryCode` VARCHAR(255) NOT NULL,\n",
        "\t`Lat` VARCHAR(255) NOT NULL,\n",
        "\t`Lon` VARCHAR(255) NOT NULL,\n",
        "  `City` VARCHAR(255) NOT NULL,\n",
        "\t`CityCode` VARCHAR(255) NOT NULL,\n",
        "\t`Province` VARCHAR(255) NOT NULL,\n",
        "\tPRIMARY KEY (`Country`)\n",
        ");\"\"\")\n",
        "\n",
        "mycursor.execute(\"\"\"CREATE TABLE IF NOT EXISTS `Measurements` (\n",
        "\t`ID` INT NOT NULL AUTO_INCREMENT,\n",
        "\t`Country` VARCHAR(255) NOT NULL,\n",
        "\t`Deaths` INT NOT NULL,\n",
        "\t`Confirmed` INT NOT NULL,\n",
        "\t`Recovered` INT NOT NULL,\n",
        "\t`Active` INT NOT NULL,\n",
        "\t`Date` TIMESTAMP NOT NULL,\n",
        "  `RegisterId` VARCHAR(255) NOT NULL,\n",
        "\tPRIMARY KEY (`ID`),\n",
        "  CONSTRAINT `Measurements_fk0` FOREIGN KEY (`Country`)\n",
        "\tREFERENCES `Locations`(`Country`)\n",
        ");\"\"\")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "I1gd8n9ersrm"
      },
      "source": [
        "## Migrating data from AWS S3 to AWS RDS"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "background_save": true,
          "base_uri": "https://localhost:8080/"
        },
        "id": "yYlZ5EN5rxrd",
        "outputId": "645f40ee-1bbd-4cf9-d563-7e14b223dac3"
      },
      "source": [
        "ACCESS_KEY = SETTINGS[\"S3_AUTH\"][\"ACCESS_KEY\"]\n",
        "SECRET_KEY = SETTINGS[\"S3_AUTH\"][\"SECRET_KEY\"]\n",
        "S3 = boto3.client('s3', aws_access_key_id=ACCESS_KEY,\n",
        "                  aws_secret_access_key=SECRET_KEY)\n",
        "\n",
        "mydb = connect(\n",
        "  host=SETTINGS[\"RDS_SETTINGS\"][\"HOST\"],\n",
        "  user=SETTINGS[\"RDS_SETTINGS\"][\"USER\"],\n",
        "  password=SETTINGS[\"RDS_SETTINGS\"][\"PASSWORD\"],\n",
        "  database=SETTINGS[\"RDS_SETTINGS\"][\"DATABASE\"]\n",
        ")\n",
        "mycursor = mydb.cursor()\n",
        "\n",
        "def download_from_aws(local_file, bucket, s3_file):\n",
        "    try:\n",
        "        S3.download_file(bucket, s3_file, local_file)\n",
        "        print(\"Download Successful\")\n",
        "        print(f'Downloaded file: {s3_file}')\n",
        "        print(f'Download from bucket: {bucket}')\n",
        "    except FileNotFoundError:\n",
        "        print(\"The file was not found\")\n",
        "    except NoCredentialsError:\n",
        "        print(\"Credentials not available\")\n",
        "\n",
        "local_file_name = '/content/s3_measurements.json'\n",
        "bucket_name = 'ornitorrinco'\n",
        "file_name = 'measurements.json'\n",
        "download_from_aws(local_file_name, bucket_name, file_name)\n",
        "\n",
        "measurements = json.load(open(local_file_name))\n",
        "\n",
        "\n",
        "queryInsertCountry = f\"\"\"INSERT IGNORE INTO Locations \n",
        "  (Country, CountryCode, Lat, Lon, City, CityCode, Province) \n",
        "  VALUES (%s, %s, %s, %s, %s, %s, %s);\"\"\"\n",
        "\n",
        "queryInsertMeasurement = f\"\"\"INSERT INTO Measurements \n",
        "  (Country, Deaths, Confirmed, Recovered, Active, Date, RegisterId) \n",
        "  VALUES (%s, %s, %s, %s, %s, %s, %s);\"\"\"\n",
        "\n",
        "print('Uploading Data')\n",
        "locations_data = []\n",
        "measurements_data = []\n",
        "for measurement in measurements:\n",
        "    country = measurement['Country']\n",
        "    country_code = measurement['CountryCode']\n",
        "    lat = measurement['Lat']\n",
        "    lon = measurement['Lon']\n",
        "    city = measurement['City']\n",
        "    city_code = measurement['CityCode']\n",
        "    province = measurement['Province']\n",
        "    register_id = measurement['ID']\n",
        "    deaths = measurement['Deaths']\n",
        "    confirmed = measurement['Confirmed']\n",
        "    recovered = measurement['Recovered']\n",
        "    active = measurement['Active']\n",
        "    date = measurement['Date']\n",
        "\n",
        "    locations_data.append((country, country_code, lat, lon, city, city_code, province))\n",
        "    measurements_data.append((country, deaths, confirmed, recovered, active, date, register_id))\n",
        "\n",
        "    if len(locations_data) == 100:\n",
        "      mycursor.executemany(queryInsertCountry, locations_data)\n",
        "      mycursor.executemany(queryInsertMeasurement, measurements_data)\n",
        "      mydb.commit()\n",
        "      locations_data = []\n",
        "      measurements_data = []\n",
        "\n",
        "mycursor.executemany(queryInsertCountry, locations_data)\n",
        "mycursor.executemany(queryInsertMeasurement, measurements_data)\n",
        "mydb.commit()\n",
        "print(\"Upload Successful\")"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Download Successful\n",
            "Downloaded file: measurements.json\n",
            "Download from bucket: ornitorrinco\n",
            "Uploading Data\n",
            "Upload Successful\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ZT61nV_MkZdV"
      },
      "source": [
        "## COVID-19 Report"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "lUcbmAiaKSWi"
      },
      "source": [
        "# Getting Data\n",
        "\n",
        "from tabulate import tabulate\n",
        "from mysql.connector import connect\n",
        "\n",
        "mydb = connect(\n",
        "  host=SETTINGS[\"RDS_SETTINGS\"][\"HOST\"],\n",
        "  user=SETTINGS[\"RDS_SETTINGS\"][\"USER\"],\n",
        "  password=SETTINGS[\"RDS_SETTINGS\"][\"PASSWORD\"],\n",
        "  database=SETTINGS[\"RDS_SETTINGS\"][\"DATABASE\"]\n",
        ")\n",
        "mycursor = mydb.cursor()\n",
        "\n",
        "def relate_last_500_days(param, label):\n",
        "  query = f\"\"\"\n",
        "  SELECT Country, SUM({param}) AS {param}, Date FROM Measurements\n",
        "  WHERE Country = (\n",
        "    SELECT Country FROM Measurements\n",
        "    WHERE Date = '2021-06-09 00:00:00'\n",
        "    GROUP BY Country, Date\n",
        "    ORDER BY Date DESC, SUM({param}) DESC\n",
        "    LIMIT 1\n",
        "  )\n",
        "  GROUP BY Country, Date\n",
        "  ORDER BY Date DESC LIMIT 500;\n",
        "  \"\"\"\n",
        "  mycursor.execute(query)\n",
        "  data = mycursor.fetchall()\n",
        "\n",
        "  country = data[0][0]\n",
        "  print(f'\\nMost {label}: {country}')\n",
        "  print(tabulate([(str(cases), date.strftime('%d/%m/%Y')) for (_, cases, date) in data], headers=[param, 'Date']))\n",
        "\n",
        "def relate_top_10(param, label):\n",
        "  query = f\"\"\"\n",
        "  SELECT Country, SUM({param}) AS {param} FROM Measurements\n",
        "  WHERE Date = '2021-06-09 00:00:00'\n",
        "  GROUP BY Country, Date\n",
        "  ORDER BY Date DESC, SUM({param}) DESC\n",
        "  LIMIT 10;\n",
        "  \"\"\"\n",
        "  mycursor.execute(query)\n",
        "  data = mycursor.fetchall()\n",
        "\n",
        "  print(f'\\nTop 10 Countries with Most {label}')\n",
        "  print(tabulate([(country, str(cases)) for (country, cases) in data], headers=['Country', param]))"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "source": [
        "### Daily Report of Country with Most Confirmed Cases"
      ],
      "cell_type": "markdown",
      "metadata": {}
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "DRIjSpZLWFjb",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "f99940db-11b8-4a3b-eef9-b99c1749ea3e"
      },
      "source": [
        "relate_last_500_days('Confirmed', 'Confirmed Cases')"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "\n",
            "Most Confirmed Cases: United States of America\n",
            "  Confirmed  Date\n",
            "-----------  ----------\n",
            "   33409753  09/06/2021\n",
            "   33391107  08/06/2021\n",
            "   33378096  07/06/2021\n",
            "   33362600  06/06/2021\n",
            "   33357205  05/06/2021\n",
            "   33343297  04/06/2021\n",
            "   33326437  03/06/2021\n",
            "   33307363  02/06/2021\n",
            "   33290450  01/06/2021\n",
            "   33267507  31/05/2021\n",
            "   33261731  30/05/2021\n",
            "   33254998  29/05/2021\n",
            "   33242999  28/05/2021\n",
            "   33221141  27/05/2021\n",
            "   33193687  26/05/2021\n",
            "   33169715  25/05/2021\n",
            "   33146976  24/05/2021\n",
            "   33121157  23/05/2021\n",
            "   33108291  22/05/2021\n",
            "   33088492  21/05/2021\n",
            "   33060540  20/05/2021\n",
            "   33030332  19/05/2021\n",
            "   33001039  18/05/2021\n",
            "   32973249  17/05/2021\n",
            "   32944627  16/05/2021\n",
            "   32927740  15/05/2021\n",
            "   32898886  14/05/2021\n",
            "   32856627  13/05/2021\n",
            "   32818551  12/05/2021\n",
            "   32782712  11/05/2021\n",
            "   32749068  10/05/2021\n",
            "   32712264  09/05/2021\n",
            "   32690834  08/05/2021\n",
            "   32657158  07/05/2021\n",
            "   32609021  06/05/2021\n",
            "   32561469  05/05/2021\n",
            "   32516756  04/05/2021\n",
            "   32476040  03/05/2021\n",
            "   32425548  02/05/2021\n",
            "   32396144  01/05/2021\n",
            "   32350761  30/04/2021\n",
            "   32292841  29/04/2021\n",
            "   32234586  28/04/2021\n",
            "   32179445  27/04/2021\n",
            "   32128604  26/04/2021\n",
            "   32081038  25/04/2021\n",
            "   32048873  24/04/2021\n",
            "   31995381  23/04/2021\n",
            "   31932976  22/04/2021\n",
            "   31865707  21/04/2021\n",
            "   31802848  20/04/2021\n",
            "   31741907  19/04/2021\n",
            "   31673784  18/04/2021\n",
            "   31631670  17/04/2021\n",
            "   31579124  16/04/2021\n",
            "   31499073  15/04/2021\n",
            "   31424769  14/04/2021\n",
            "   31349356  13/04/2021\n",
            "   31271399  12/04/2021\n",
            "   31201306  11/04/2021\n",
            "   31154800  10/04/2021\n",
            "   31088104  09/04/2021\n",
            "   31005391  08/04/2021\n",
            "   30925509  07/04/2021\n",
            "   30850504  06/04/2021\n",
            "   30789814  05/04/2021\n",
            "   30712415  04/04/2021\n",
            "   30677289  03/04/2021\n",
            "   30614031  02/04/2021\n",
            "   30544145  01/04/2021\n",
            "   30465030  31/03/2021\n",
            "   30398261  30/03/2021\n",
            "   30336835  29/03/2021\n",
            "   30267553  28/03/2021\n",
            "   30224333  27/03/2021\n",
            "   30161485  26/03/2021\n",
            "   30084115  25/03/2021\n",
            "   30016566  24/03/2021\n",
            "   29929612  23/03/2021\n",
            "   29875944  22/03/2021\n",
            "   29824511  21/03/2021\n",
            "   29790671  20/03/2021\n",
            "   29735175  19/03/2021\n",
            "   29673509  18/03/2021\n",
            "   29613135  17/03/2021\n",
            "   29553856  16/03/2021\n",
            "   29499863  15/03/2021\n",
            "   29443317  14/03/2021\n",
            "   29405035  13/03/2021\n",
            "   29352006  12/03/2021\n",
            "   29290495  11/03/2021\n",
            "   29228034  10/03/2021\n",
            "   29170126  09/03/2021\n",
            "   29112472  08/03/2021\n",
            "   29067553  07/03/2021\n",
            "   29026478  06/03/2021\n",
            "   28968231  05/03/2021\n",
            "   28901802  04/03/2021\n",
            "   28833745  03/03/2021\n",
            "   28766544  02/03/2021\n",
            "   28709454  01/03/2021\n",
            "   28651355  28/02/2021\n",
            "   28599936  27/02/2021\n",
            "   28535292  26/02/2021\n",
            "   28457959  25/02/2021\n",
            "   28380445  24/02/2021\n",
            "   28305709  23/02/2021\n",
            "   28233431  22/02/2021\n",
            "   28177280  21/02/2021\n",
            "   28120119  20/02/2021\n",
            "   28048511  19/02/2021\n",
            "   27969229  18/02/2021\n",
            "   27899318  17/02/2021\n",
            "   27829179  16/02/2021\n",
            "   27766681  15/02/2021\n",
            "   27712402  14/02/2021\n",
            "   27647267  13/02/2021\n",
            "   27560048  12/02/2021\n",
            "   27460378  11/02/2021\n",
            "   27354614  10/02/2021\n",
            "   27259364  09/02/2021\n",
            "   27164099  08/02/2021\n",
            "   27073661  07/02/2021\n",
            "   26983915  06/02/2021\n",
            "   26879739  05/02/2021\n",
            "   26745317  04/02/2021\n",
            "   26621311  03/02/2021\n",
            "   26499620  02/02/2021\n",
            "   26384317  01/02/2021\n",
            "   26249342  31/01/2021\n",
            "   26137190  30/01/2021\n",
            "   25994731  29/01/2021\n",
            "   25828118  28/01/2021\n",
            "   25659314  27/01/2021\n",
            "   25505353  26/01/2021\n",
            "   25357727  25/01/2021\n",
            "   25206050  24/01/2021\n",
            "   25074852  23/01/2021\n",
            "   24904093  22/01/2021\n",
            "   24713333  21/01/2021\n",
            "   24519477  20/01/2021\n",
            "   24336216  19/01/2021\n",
            "   24160000  18/01/2021\n",
            "   24016402  17/01/2021\n",
            "   23838471  16/01/2021\n",
            "   23636613  15/01/2021\n",
            "   23393833  14/01/2021\n",
            "   23158067  13/01/2021\n",
            "   22927766  12/01/2021\n",
            "   22700799  11/01/2021\n",
            "   22486135  10/01/2021\n",
            "   22272720  09/01/2021\n",
            "   22011753  08/01/2021\n",
            "   21716496  07/01/2021\n",
            "   21438159  06/01/2021\n",
            "   21182522  05/01/2021\n",
            "   20947480  04/01/2021\n",
            "   20763475  03/01/2021\n",
            "   20554622  02/01/2021\n",
            "   20254160  01/01/2021\n",
            "   20100244  31/12/2020\n",
            "   19864644  30/12/2020\n",
            "   19630943  29/12/2020\n",
            "   19430535  28/12/2020\n",
            "   19256383  27/12/2020\n",
            "   19100538  26/12/2020\n",
            "   18874122  25/12/2020\n",
            "   18776242  24/12/2020\n",
            "   18582087  23/12/2020\n",
            "   18352463  22/12/2020\n",
            "   18154356  21/12/2020\n",
            "   17955653  20/12/2020\n",
            "   17767638  19/12/2020\n",
            "   17575536  18/12/2020\n",
            "   17323704  17/12/2020\n",
            "   17083804  16/12/2020\n",
            "   16837160  15/12/2020\n",
            "   16628168  14/12/2020\n",
            "   16433854  13/12/2020\n",
            "   16245883  12/12/2020\n",
            "   16028086  11/12/2020\n",
            "   15787997  10/12/2020\n",
            "   15556569  09/12/2020\n",
            "   15333921  08/12/2020\n",
            "   15109469  07/12/2020\n",
            "   14915060  06/12/2020\n",
            "   14733807  05/12/2020\n",
            "   14517996  04/12/2020\n",
            "   14285352  03/12/2020\n",
            "   14061931  02/12/2020\n",
            "   13859037  01/12/2020\n",
            "   13670698  30/11/2020\n",
            "   13510377  29/11/2020\n",
            "   13370049  28/11/2020\n",
            "   13214388  27/11/2020\n",
            "   13006455  26/11/2020\n",
            "   12893929  25/11/2020\n",
            "   12710526  24/11/2020\n",
            "   12534982  23/11/2020\n",
            "   12360893  22/11/2020\n",
            "   12213946  21/11/2020\n",
            "   12034510  20/11/2020\n",
            "   11836177  19/11/2020\n",
            "   11644667  18/11/2020\n",
            "   11471416  17/11/2020\n",
            "   11307458  16/11/2020\n",
            "   11144834  15/11/2020\n",
            "   11008478  14/11/2020\n",
            "   10840552  13/11/2020\n",
            "   10660154  12/11/2020\n",
            "   10495404  11/11/2020\n",
            "   10348654  10/11/2020\n",
            "   10208150  09/11/2020\n",
            "   10087716  08/11/2020\n",
            "    9972546  07/11/2020\n",
            "    9845036  06/11/2020\n",
            "    9717003  05/11/2020\n",
            "    9587636  04/11/2020\n",
            "    9483095  03/11/2020\n",
            "    9355897  02/11/2020\n",
            "    9270686  01/11/2020\n",
            "    9165786  31/10/2020\n",
            "    9076032  30/10/2020\n",
            "    8976768  29/10/2020\n",
            "    8885749  28/10/2020\n",
            "    8806323  27/10/2020\n",
            "    8729485  26/10/2020\n",
            "    8662175  25/10/2020\n",
            "    8599971  24/10/2020\n",
            "    8517204  23/10/2020\n",
            "    8435271  22/10/2020\n",
            "    8358961  21/10/2020\n",
            "    8295660  20/10/2020\n",
            "    8233704  19/10/2020\n",
            "    8165987  18/10/2020\n",
            "    8116620  17/10/2020\n",
            "    8059861  16/10/2020\n",
            "    7990714  15/10/2020\n",
            "    7925834  14/10/2020\n",
            "    7866056  13/10/2020\n",
            "    7813825  12/10/2020\n",
            "    7772051  11/10/2020\n",
            "    7726070  10/10/2020\n",
            "    7671117  09/10/2020\n",
            "    7614733  08/10/2020\n",
            "    7556134  07/10/2020\n",
            "    7505078  06/10/2020\n",
            "    7459789  05/10/2020\n",
            "    7420413  04/10/2020\n",
            "    7384675  03/10/2020\n",
            "    7336098  02/10/2020\n",
            "    7281148  01/10/2020\n",
            "    7235498  30/09/2020\n",
            "    7196055  29/09/2020\n",
            "    7152717  28/09/2020\n",
            "    7119423  27/09/2020\n",
            "    7081895  26/09/2020\n",
            "    7037222  25/09/2020\n",
            "    6988927  24/09/2020\n",
            "    6941824  23/09/2020\n",
            "    6902755  22/09/2020\n",
            "    6862889  21/09/2020\n",
            "    6811011  20/09/2020\n",
            "    6772564  19/09/2020\n",
            "    6730362  18/09/2020\n",
            "    6681080  17/09/2020\n",
            "    6635950  16/09/2020\n",
            "    6596920  15/09/2020\n",
            "    6557428  14/09/2020\n",
            "    6523005  13/09/2020\n",
            "    6488633  12/09/2020\n",
            "    6447532  11/09/2020\n",
            "    6399763  10/09/2020\n",
            "    6363707  09/09/2020\n",
            "    6329658  08/09/2020\n",
            "    6302432  07/09/2020\n",
            "    6278819  06/09/2020\n",
            "    6247601  05/09/2020\n",
            "    6204443  04/09/2020\n",
            "    6154061  03/09/2020\n",
            "    6109810  02/09/2020\n",
            "    6068792  01/09/2020\n",
            "    6026946  31/08/2020\n",
            "    5991584  30/08/2020\n",
            "    5957180  29/08/2020\n",
            "    5914426  28/08/2020\n",
            "    5867594  27/08/2020\n",
            "    5822201  26/08/2020\n",
            "    5777029  25/08/2020\n",
            "    5736675  24/08/2020\n",
            "    5700193  23/08/2020\n",
            "    5665946  22/08/2020\n",
            "    5622882  21/08/2020\n",
            "    5574039  20/08/2020\n",
            "    5529998  19/08/2020\n",
            "    5482678  18/08/2020\n",
            "    5437645  17/08/2020\n",
            "    5400993  16/08/2020\n",
            "    5361781  15/08/2020\n",
            "    5314830  14/08/2020\n",
            "    5249494  13/08/2020\n",
            "    5198178  12/08/2020\n",
            "    5142138  11/08/2020\n",
            "    5094135  10/08/2020\n",
            "    5046529  09/08/2020\n",
            "    5000760  08/08/2020\n",
            "    4946626  07/08/2020\n",
            "    4887323  06/08/2020\n",
            "    4827974  05/08/2020\n",
            "    4773508  04/08/2020\n",
            "    4714734  03/08/2020\n",
            "    4669233  02/08/2020\n",
            "    4623665  01/08/2020\n",
            "    4567464  31/07/2020\n",
            "    4498752  30/07/2020\n",
            "    4431286  29/07/2020\n",
            "    4359413  28/07/2020\n",
            "    4293001  27/07/2020\n",
            "    4236218  26/07/2020\n",
            "    4181402  25/07/2020\n",
            "    4116449  24/07/2020\n",
            "    4043130  23/07/2020\n",
            "    3974685  22/07/2020\n",
            "    3904106  21/07/2020\n",
            "    3839645  20/07/2020\n",
            "    3777538  19/07/2020\n",
            "    3717082  18/07/2020\n",
            "    3654546  17/07/2020\n",
            "    3582310  16/07/2020\n",
            "    3506444  15/07/2020\n",
            "    3438354  14/07/2020\n",
            "    3370303  13/07/2020\n",
            "    3311373  12/07/2020\n",
            "    3252949  11/07/2020\n",
            "    3192932  10/07/2020\n",
            "    3124875  09/07/2020\n",
            "    3062366  08/07/2020\n",
            "    3002247  07/07/2020\n",
            "    2941586  06/07/2020\n",
            "    2898480  05/07/2020\n",
            "    2847733  04/07/2020\n",
            "    2802040  03/07/2020\n",
            "    2750689  02/07/2020\n",
            "    2694053  01/07/2020\n",
            "    2642233  30/06/2020\n",
            "    2595813  29/06/2020\n",
            "    2554514  28/06/2020\n",
            "    2513780  27/06/2020\n",
            "    2472450  26/06/2020\n",
            "    2426451  25/06/2020\n",
            "    2386125  24/06/2020\n",
            "    2350247  23/06/2020\n",
            "    2313175  22/06/2020\n",
            "    2281031  21/06/2020\n",
            "    2255881  20/06/2020\n",
            "    2223601  19/06/2020\n",
            "    2192057  18/06/2020\n",
            "    2163520  17/06/2020\n",
            "    2136448  16/06/2020\n",
            "    2112781  15/06/2020\n",
            "    2092954  14/06/2020\n",
            "    2074013  13/06/2020\n",
            "    2048801  12/06/2020\n",
            "    2023973  11/06/2020\n",
            "    2000807  10/06/2020\n",
            "    1979696  09/06/2020\n",
            "    1961313  08/06/2020\n",
            "    1943697  07/06/2020\n",
            "    1925769  06/06/2020\n",
            "    1904627  05/06/2020\n",
            "    1879224  04/06/2020\n",
            "    1857566  03/06/2020\n",
            "    1837709  02/06/2020\n",
            "    1816207  01/06/2020\n",
            "    1798793  31/05/2020\n",
            "    1779810  30/05/2020\n",
            "    1756178  29/05/2020\n",
            "    1731700  28/05/2020\n",
            "    1709380  27/05/2020\n",
            "    1690829  26/05/2020\n",
            "    1671166  25/05/2020\n",
            "    1652504  24/05/2020\n",
            "    1632427  23/05/2020\n",
            "    1611328  22/05/2020\n",
            "    1587679  21/05/2020\n",
            "    1561910  20/05/2020\n",
            "    1539185  19/05/2020\n",
            "    1518206  18/05/2020\n",
            "    1495817  17/05/2020\n",
            "    1477429  16/05/2020\n",
            "    1453294  15/05/2020\n",
            "    1428561  14/05/2020\n",
            "    1401744  13/05/2020\n",
            "    1381311  12/05/2020\n",
            "    1358384  11/05/2020\n",
            "    1339086  10/05/2020\n",
            "    1320281  09/05/2020\n",
            "    1295105  08/05/2020\n",
            "    1268275  07/05/2020\n",
            "    1240846  06/05/2020\n",
            "    1216277  05/05/2020\n",
            "    1191749  04/05/2020\n",
            "    1167681  03/05/2020\n",
            "    1143360  02/05/2020\n",
            "    1116012  01/05/2020\n",
            "    1081105  30/04/2020\n",
            "    1051900  29/04/2020\n",
            "    1025441  28/04/2020\n",
            "    1000866  27/04/2020\n",
            "     977151  26/04/2020\n",
            "     950663  25/04/2020\n",
            "     920283  24/04/2020\n",
            "     887956  23/04/2020\n",
            "     854344  22/04/2020\n",
            "     825433  21/04/2020\n",
            "     799507  20/04/2020\n",
            "     769687  19/04/2020\n",
            "     743625  18/04/2020\n",
            "     715718  17/04/2020\n",
            "     682652  16/04/2020\n",
            "     652650  15/04/2020\n",
            "     627242  14/04/2020\n",
            "     598424  13/04/2020\n",
            "     571485  12/04/2020\n",
            "     544229  11/04/2020\n",
            "     515146  10/04/2020\n",
            "     480743  09/04/2020\n",
            "     444801  08/04/2020\n",
            "     413566  07/04/2020\n",
            "     382787  06/04/2020\n",
            "     351397  05/04/2020\n",
            "     321502  04/04/2020\n",
            "     289116  03/04/2020\n",
            "     256809  02/04/2020\n",
            "     224587  01/04/2020\n",
            "     192301  31/03/2020\n",
            "     165987  30/03/2020\n",
            "     143912  29/03/2020\n",
            "     125013  28/03/2020\n",
            "     105383  27/03/2020\n",
            "      86693  26/03/2020\n",
            "      68837  25/03/2020\n",
            "      56755  24/03/2020\n",
            "      46136  23/03/2020\n",
            "      34898  22/03/2020\n",
            "      26025  21/03/2020\n",
            "      20030  20/03/2020\n",
            "      13663  19/03/2020\n",
            "       9169  18/03/2020\n",
            "       6512  17/03/2020\n",
            "       4679  16/03/2020\n",
            "       3212  15/03/2020\n",
            "       2978  14/03/2020\n",
            "       2219  13/03/2020\n",
            "       1586  12/03/2020\n",
            "       1147  11/03/2020\n",
            "        782  10/03/2020\n",
            "        594  09/03/2020\n",
            "        519  08/03/2020\n",
            "        403  07/03/2020\n",
            "        237  06/03/2020\n",
            "        184  05/03/2020\n",
            "        107  04/03/2020\n",
            "         74  03/03/2020\n",
            "         55  02/03/2020\n",
            "         32  01/03/2020\n",
            "         25  29/02/2020\n",
            "         17  28/02/2020\n",
            "         17  27/02/2020\n",
            "         16  26/02/2020\n",
            "         16  25/02/2020\n",
            "         16  24/02/2020\n",
            "         16  23/02/2020\n",
            "         16  22/02/2020\n",
            "         16  21/02/2020\n",
            "         14  20/02/2020\n",
            "         14  19/02/2020\n",
            "         14  18/02/2020\n",
            "         14  17/02/2020\n",
            "         14  16/02/2020\n",
            "         14  15/02/2020\n",
            "         14  14/02/2020\n",
            "         14  13/02/2020\n",
            "         13  12/02/2020\n",
            "         13  11/02/2020\n",
            "         12  10/02/2020\n",
            "         12  09/02/2020\n",
            "         12  08/02/2020\n",
            "         12  07/02/2020\n",
            "         12  06/02/2020\n",
            "         11  05/02/2020\n",
            "         11  04/02/2020\n",
            "         11  03/02/2020\n",
            "          8  02/02/2020\n",
            "          8  01/02/2020\n",
            "          8  31/01/2020\n",
            "          6  30/01/2020\n",
            "          6  29/01/2020\n",
            "          5  28/01/2020\n",
            "          5  27/01/2020\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "source": [
        "### Daily Report of Country with Most Deaths"
      ],
      "cell_type": "markdown",
      "metadata": {}
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "fEM819xvWAvK",
        "outputId": "2e3e3f73-ed22-40a8-fd54-3426c6997f54"
      },
      "source": [
        "relate_last_500_days('Deaths', 'Deaths')"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "\n",
            "Most Deaths: United States of America\n",
            "  Deaths  Date\n",
            "--------  ----------\n",
            "  598316  09/06/2021\n",
            "  597880  08/06/2021\n",
            "  597504  07/06/2021\n",
            "  597194  06/06/2021\n",
            "  596943  05/06/2021\n",
            "  596573  04/06/2021\n",
            "  596061  03/06/2021\n",
            "  595451  02/06/2021\n",
            "  594902  01/06/2021\n",
            "  594264  31/05/2021\n",
            "  594122  30/05/2021\n",
            "  593996  29/05/2021\n",
            "  593669  28/05/2021\n",
            "  593117  27/05/2021\n",
            "  591772  26/05/2021\n",
            "  590802  25/05/2021\n",
            "  590179  24/05/2021\n",
            "  589742  23/05/2021\n",
            "  589551  22/05/2021\n",
            "  589068  21/05/2021\n",
            "  588456  20/05/2021\n",
            "  587780  19/05/2021\n",
            "  587116  18/05/2021\n",
            "  586349  17/05/2021\n",
            "  585951  16/05/2021\n",
            "  585677  15/05/2021\n",
            "  585187  14/05/2021\n",
            "  584539  13/05/2021\n",
            "  583779  12/05/2021\n",
            "  583013  11/05/2021\n",
            "  582329  10/05/2021\n",
            "  581917  09/05/2021\n",
            "  581672  08/05/2021\n",
            "  581045  07/05/2021\n",
            "  580289  06/05/2021\n",
            "  579490  05/05/2021\n",
            "  578697  04/05/2021\n",
            "  577823  03/05/2021\n",
            "  577322  02/05/2021\n",
            "  576987  01/05/2021\n",
            "  576273  30/04/2021\n",
            "  575522  29/04/2021\n",
            "  574650  28/04/2021\n",
            "  573684  27/04/2021\n",
            "  573030  26/04/2021\n",
            "  572536  25/04/2021\n",
            "  572244  24/04/2021\n",
            "  571496  23/04/2021\n",
            "  570713  22/04/2021\n",
            "  569749  21/04/2021\n",
            "  568890  20/04/2021\n",
            "  568049  19/04/2021\n",
            "  567546  18/04/2021\n",
            "  567209  17/04/2021\n",
            "  566512  16/04/2021\n",
            "  565643  15/04/2021\n",
            "  564740  14/04/2021\n",
            "  563786  13/04/2021\n",
            "  562951  12/04/2021\n",
            "  562472  11/04/2021\n",
            "  562170  10/04/2021\n",
            "  561458  09/04/2021\n",
            "  560562  08/04/2021\n",
            "  559537  07/04/2021\n",
            "  556944  06/04/2021\n",
            "  556083  05/04/2021\n",
            "  555565  04/04/2021\n",
            "  555269  03/04/2021\n",
            "  554530  02/04/2021\n",
            "  553578  01/04/2021\n",
            "  552501  31/03/2021\n",
            "  551426  30/03/2021\n",
            "  550541  29/03/2021\n",
            "  549832  28/03/2021\n",
            "  549307  27/03/2021\n",
            "  548516  26/03/2021\n",
            "  547365  25/03/2021\n",
            "  545962  24/03/2021\n",
            "  544451  23/03/2021\n",
            "  543574  22/03/2021\n",
            "  542853  21/03/2021\n",
            "  542406  20/03/2021\n",
            "  541669  19/03/2021\n",
            "  540522  18/03/2021\n",
            "  538862  17/03/2021\n",
            "  537646  16/03/2021\n",
            "  536458  15/03/2021\n",
            "  535701  14/03/2021\n",
            "  535123  13/03/2021\n",
            "  534117  12/03/2021\n",
            "  532552  11/03/2021\n",
            "  530999  10/03/2021\n",
            "  529427  09/03/2021\n",
            "  527618  08/03/2021\n",
            "  526880  07/03/2021\n",
            "  526181  06/03/2021\n",
            "  524675  05/03/2021\n",
            "  522892  04/03/2021\n",
            "  520951  03/03/2021\n",
            "  518483  02/03/2021\n",
            "  516526  01/03/2021\n",
            "  515005  28/02/2021\n",
            "  513914  27/02/2021\n",
            "  512396  26/02/2021\n",
            "  510329  25/02/2021\n",
            "  507879  24/02/2021\n",
            "  504710  23/02/2021\n",
            "  502428  22/02/2021\n",
            "  501103  21/02/2021\n",
            "  499871  20/02/2021\n",
            "  498048  19/02/2021\n",
            "  495436  18/02/2021\n",
            "  492907  17/02/2021\n",
            "  490498  16/02/2021\n",
            "  488747  15/02/2021\n",
            "  487788  14/02/2021\n",
            "  486624  13/02/2021\n",
            "  484437  12/02/2021\n",
            "  481527  11/02/2021\n",
            "  478299  10/02/2021\n",
            "  475003  09/02/2021\n",
            "  471931  08/02/2021\n",
            "  470312  07/02/2021\n",
            "  468875  06/02/2021\n",
            "  466205  05/02/2021\n",
            "  462539  04/02/2021\n",
            "  458804  03/02/2021\n",
            "  454897  02/02/2021\n",
            "  451463  01/02/2021\n",
            "  449392  31/01/2021\n",
            "  447534  30/01/2021\n",
            "  444751  29/01/2021\n",
            "  441134  28/01/2021\n",
            "  437127  27/01/2021\n",
            "  433161  26/01/2021\n",
            "  429152  25/01/2021\n",
            "  427176  24/01/2021\n",
            "  425284  23/01/2021\n",
            "  421938  22/01/2021\n",
            "  418095  21/01/2021\n",
            "  413903  20/01/2021\n",
            "  409497  19/01/2021\n",
            "  406775  18/01/2021\n",
            "  405222  17/01/2021\n",
            "  403339  16/01/2021\n",
            "  399917  15/01/2021\n",
            "  396030  14/01/2021\n",
            "  392044  13/01/2021\n",
            "  388033  12/01/2021\n",
            "  383558  11/01/2021\n",
            "  381452  10/01/2021\n",
            "  379449  09/01/2021\n",
            "  376125  08/01/2021\n",
            "  372015  07/01/2021\n",
            "  368022  06/01/2021\n",
            "  364118  05/01/2021\n",
            "  360408  04/01/2021\n",
            "  358315  03/01/2021\n",
            "  356862  02/01/2021\n",
            "  354348  01/01/2021\n",
            "  352193  31/12/2020\n",
            "  348731  30/12/2020\n",
            "  344979  29/12/2020\n",
            "  341345  28/12/2020\n",
            "  339337  27/12/2020\n",
            "  337901  26/12/2020\n",
            "  336009  25/12/2020\n",
            "  334575  24/12/2020\n",
            "  331677  23/12/2020\n",
            "  328246  22/12/2020\n",
            "  324841  21/12/2020\n",
            "  322917  20/12/2020\n",
            "  321196  19/12/2020\n",
            "  318543  18/12/2020\n",
            "  315570  17/12/2020\n",
            "  312071  16/12/2020\n",
            "  308402  15/12/2020\n",
            "  305287  14/12/2020\n",
            "  303634  13/12/2020\n",
            "  302000  12/12/2020\n",
            "  299530  11/12/2020\n",
            "  296120  10/12/2020\n",
            "  293124  09/12/2020\n",
            "  289947  08/12/2020\n",
            "  287325  07/12/2020\n",
            "  285717  06/12/2020\n",
            "  284365  05/12/2020\n",
            "  282007  04/12/2020\n",
            "  279307  03/12/2020\n",
            "  276364  02/12/2020\n",
            "  273549  01/12/2020\n",
            "  270998  30/11/2020\n",
            "  269650  29/11/2020\n",
            "  268611  28/11/2020\n",
            "  267246  27/11/2020\n",
            "  265695  26/11/2020\n",
            "  264307  25/11/2020\n",
            "  262048  24/11/2020\n",
            "  259917  23/11/2020\n",
            "  258818  22/11/2020\n",
            "  257782  21/11/2020\n",
            "  256162  20/11/2020\n",
            "  254206  19/11/2020\n",
            "  252149  18/11/2020\n",
            "  250230  17/11/2020\n",
            "  248511  16/11/2020\n",
            "  247677  15/11/2020\n",
            "  246899  14/11/2020\n",
            "  245553  13/11/2020\n",
            "  244351  12/11/2020\n",
            "  243144  11/11/2020\n",
            "  241711  10/11/2020\n",
            "  240292  09/11/2020\n",
            "  239513  08/11/2020\n",
            "  238941  07/11/2020\n",
            "  237860  06/11/2020\n",
            "  236629  05/11/2020\n",
            "  235470  04/11/2020\n",
            "  234354  03/11/2020\n",
            "  232779  02/11/2020\n",
            "  232205  01/11/2020\n",
            "  231706  31/10/2020\n",
            "  230782  30/10/2020\n",
            "  229723  29/10/2020\n",
            "  228719  28/10/2020\n",
            "  227685  27/10/2020\n",
            "  226711  26/10/2020\n",
            "  226178  25/10/2020\n",
            "  225717  24/10/2020\n",
            "  224765  23/10/2020\n",
            "  223800  22/10/2020\n",
            "  222906  21/10/2020\n",
            "  221787  20/10/2020\n",
            "  220864  19/10/2020\n",
            "  220376  18/10/2020\n",
            "  219889  17/10/2020\n",
            "  219128  16/10/2020\n",
            "  218180  15/10/2020\n",
            "  217338  14/10/2020\n",
            "  216351  13/10/2020\n",
            "  215550  12/10/2020\n",
            "  215153  11/10/2020\n",
            "  214688  10/10/2020\n",
            "  214031  09/10/2020\n",
            "  213062  08/10/2020\n",
            "  212073  07/10/2020\n",
            "  211150  06/10/2020\n",
            "  210464  05/10/2020\n",
            "  209983  04/10/2020\n",
            "  209605  03/10/2020\n",
            "  208923  02/10/2020\n",
            "  208063  01/10/2020\n",
            "  207194  30/09/2020\n",
            "  206236  29/09/2020\n",
            "  205400  28/09/2020\n",
            "  205042  27/09/2020\n",
            "  204727  26/09/2020\n",
            "  203958  25/09/2020\n",
            "  203018  24/09/2020\n",
            "  202118  23/09/2020\n",
            "  201062  22/09/2020\n",
            "  200033  21/09/2020\n",
            "  199608  20/09/2020\n",
            "  199342  19/09/2020\n",
            "  198610  18/09/2020\n",
            "  197719  17/09/2020\n",
            "  196861  16/09/2020\n",
            "  195899  15/09/2020\n",
            "  194701  14/09/2020\n",
            "  194269  13/09/2020\n",
            "  193846  12/09/2020\n",
            "  193135  11/09/2020\n",
            "  191971  10/09/2020\n",
            "  191052  09/09/2020\n",
            "  189891  08/09/2020\n",
            "  189436  07/09/2020\n",
            "  189141  06/09/2020\n",
            "  188669  05/09/2020\n",
            "  187907  04/09/2020\n",
            "  186950  03/09/2020\n",
            "  185919  02/09/2020\n",
            "  184846  01/09/2020\n",
            "  183817  31/08/2020\n",
            "  183276  30/08/2020\n",
            "  182835  29/08/2020\n",
            "  181920  28/08/2020\n",
            "  180943  27/08/2020\n",
            "  179828  26/08/2020\n",
            "  178666  25/08/2020\n",
            "  177436  24/08/2020\n",
            "  176961  23/08/2020\n",
            "  176416  22/08/2020\n",
            "  175505  21/08/2020\n",
            "  174417  20/08/2020\n",
            "  173313  19/08/2020\n",
            "  171976  18/08/2020\n",
            "  170750  17/08/2020\n",
            "  170257  16/08/2020\n",
            "  169641  15/08/2020\n",
            "  168641  14/08/2020\n",
            "  167304  13/08/2020\n",
            "  166240  12/08/2020\n",
            "  164735  11/08/2020\n",
            "  163710  10/08/2020\n",
            "  163076  09/08/2020\n",
            "  162553  08/08/2020\n",
            "  161477  07/08/2020\n",
            "  160236  06/08/2020\n",
            "  158997  05/08/2020\n",
            "  157558  04/08/2020\n",
            "  156276  03/08/2020\n",
            "  155693  02/08/2020\n",
            "  155256  01/08/2020\n",
            "  154169  31/07/2020\n",
            "  152932  30/07/2020\n",
            "  151697  29/07/2020\n",
            "  150286  28/07/2020\n",
            "  148954  27/07/2020\n",
            "  147827  26/07/2020\n",
            "  147296  25/07/2020\n",
            "  146365  24/07/2020\n",
            "  145259  23/07/2020\n",
            "  144189  22/07/2020\n",
            "  142966  21/07/2020\n",
            "  141881  20/07/2020\n",
            "  141318  19/07/2020\n",
            "  140833  18/07/2020\n",
            "  139974  17/07/2020\n",
            "  139049  16/07/2020\n",
            "  138094  15/07/2020\n",
            "  137120  14/07/2020\n",
            "  136192  13/07/2020\n",
            "  135744  12/07/2020\n",
            "  135272  11/07/2020\n",
            "  134543  10/07/2020\n",
            "  133727  09/07/2020\n",
            "  132707  08/07/2020\n",
            "  131885  07/07/2020\n",
            "  130724  06/07/2020\n",
            "  130347  05/07/2020\n",
            "  130021  04/07/2020\n",
            "  129706  03/07/2020\n",
            "  129036  02/07/2020\n",
            "  128326  01/07/2020\n",
            "  127625  30/06/2020\n",
            "  127070  29/06/2020\n",
            "  126684  28/06/2020\n",
            "  126368  27/06/2020\n",
            "  125862  26/06/2020\n",
            "  125234  25/06/2020\n",
            "  124696  24/06/2020\n",
            "  123959  23/06/2020\n",
            "  123211  22/06/2020\n",
            "  122804  21/06/2020\n",
            "  122484  20/06/2020\n",
            "  121926  19/06/2020\n",
            "  121299  18/06/2020\n",
            "  120611  17/06/2020\n",
            "  119872  16/06/2020\n",
            "  119059  15/06/2020\n",
            "  118663  14/06/2020\n",
            "  118318  13/06/2020\n",
            "  117578  12/06/2020\n",
            "  116749  11/06/2020\n",
            "  115921  10/06/2020\n",
            "  115042  09/06/2020\n",
            "  114135  08/06/2020\n",
            "  113626  07/06/2020\n",
            "  113166  06/06/2020\n",
            "  112524  05/06/2020\n",
            "  111635  04/06/2020\n",
            "  110629  03/06/2020\n",
            "  109621  02/06/2020\n",
            "  108641  01/06/2020\n",
            "  107863  31/05/2020\n",
            "  107246  30/05/2020\n",
            "  106292  29/05/2020\n",
            "  105162  28/05/2020\n",
            "  104070  27/05/2020\n",
            "  102611  26/05/2020\n",
            "  101933  25/05/2020\n",
            "  101336  24/05/2020\n",
            "  100696  23/05/2020\n",
            "   99610  22/05/2020\n",
            "   98388  21/05/2020\n",
            "   97203  20/05/2020\n",
            "   95704  19/05/2020\n",
            "   94244  18/05/2020\n",
            "   93021  17/05/2020\n",
            "   92196  16/05/2020\n",
            "   90995  15/05/2020\n",
            "   89329  14/05/2020\n",
            "   87547  13/05/2020\n",
            "   85822  12/05/2020\n",
            "   84224  11/05/2020\n",
            "   83180  10/05/2020\n",
            "   82193  09/05/2020\n",
            "   80730  08/05/2020\n",
            "   78972  07/05/2020\n",
            "   77047  06/05/2020\n",
            "   74738  05/05/2020\n",
            "   72523  04/05/2020\n",
            "   71133  03/05/2020\n",
            "   69935  02/05/2020\n",
            "   68206  01/05/2020\n",
            "   66291  30/04/2020\n",
            "   64103  29/04/2020\n",
            "   61694  28/04/2020\n",
            "   59469  27/04/2020\n",
            "   57949  26/04/2020\n",
            "   56545  25/04/2020\n",
            "   54829  24/04/2020\n",
            "   52641  23/04/2020\n",
            "   50183  22/04/2020\n",
            "   47718  21/04/2020\n",
            "   45213  20/04/2020\n",
            "   42977  19/04/2020\n",
            "   41001  18/04/2020\n",
            "   39029  17/04/2020\n",
            "   36916  16/04/2020\n",
            "   34763  15/04/2020\n",
            "   32156  14/04/2020\n",
            "   29709  13/04/2020\n",
            "   27694  12/04/2020\n",
            "   25821  11/04/2020\n",
            "   23695  10/04/2020\n",
            "   21498  09/04/2020\n",
            "   19287  08/04/2020\n",
            "   17126  07/04/2020\n",
            "   14553  06/04/2020\n",
            "   12781  05/04/2020\n",
            "   11166  04/04/2020\n",
            "    9598  03/04/2020\n",
            "    8169  02/04/2020\n",
            "    6655  01/04/2020\n",
            "    5371  31/03/2020\n",
            "    4286  30/03/2020\n",
            "    3573  29/03/2020\n",
            "    2957  28/03/2020\n",
            "    2307  27/03/2020\n",
            "    1785  26/03/2020\n",
            "    1367  25/03/2020\n",
            "    1033  24/03/2020\n",
            "     790  23/03/2020\n",
            "     599  22/03/2020\n",
            "     475  21/03/2020\n",
            "     372  20/03/2020\n",
            "     266  19/03/2020\n",
            "     194  18/03/2020\n",
            "     134  17/03/2020\n",
            "      97  16/03/2020\n",
            "      70  15/03/2020\n",
            "      58  14/03/2020\n",
            "      51  13/03/2020\n",
            "      43  12/03/2020\n",
            "      33  11/03/2020\n",
            "      28  10/03/2020\n",
            "      22  09/03/2020\n",
            "      21  08/03/2020\n",
            "      17  07/03/2020\n",
            "      14  06/03/2020\n",
            "      12  05/03/2020\n",
            "      11  04/03/2020\n",
            "       7  03/03/2020\n",
            "       6  02/03/2020\n",
            "       1  01/03/2020\n",
            "       1  29/02/2020\n",
            "       0  28/02/2020\n",
            "       0  27/02/2020\n",
            "       0  26/02/2020\n",
            "       0  25/02/2020\n",
            "       0  24/02/2020\n",
            "       0  23/02/2020\n",
            "       0  22/02/2020\n",
            "       0  21/02/2020\n",
            "       0  20/02/2020\n",
            "       0  19/02/2020\n",
            "       0  18/02/2020\n",
            "       0  17/02/2020\n",
            "       0  16/02/2020\n",
            "       0  15/02/2020\n",
            "       0  14/02/2020\n",
            "       0  13/02/2020\n",
            "       0  12/02/2020\n",
            "       0  11/02/2020\n",
            "       0  10/02/2020\n",
            "       0  09/02/2020\n",
            "       0  08/02/2020\n",
            "       0  07/02/2020\n",
            "       0  06/02/2020\n",
            "       0  05/02/2020\n",
            "       0  04/02/2020\n",
            "       0  03/02/2020\n",
            "       0  02/02/2020\n",
            "       0  01/02/2020\n",
            "       0  31/01/2020\n",
            "       0  30/01/2020\n",
            "       0  29/01/2020\n",
            "       0  28/01/2020\n",
            "       0  27/01/2020\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "source": [
        "### Top 10 Countries with Most Confirmed Cases"
      ],
      "cell_type": "markdown",
      "metadata": {}
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "g1Und4nTWAfx",
        "outputId": "5bc46741-4bf2-4f54-e4ed-9b5e2c8a5ec9"
      },
      "source": [
        "relate_top_10('Confirmed', 'Confirmed Cases')"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "\n",
            "Top 10 Countries with Most Confirmed Cases\n",
            "Country                     Confirmed\n",
            "------------------------  -----------\n",
            "United States of America     33409753\n",
            "India                        29182532\n",
            "Brazil                       17122877\n",
            "France                        5787125\n",
            "Turkey                        5306690\n",
            "Russian Federation            5096657\n",
            "United Kingdom                4551694\n",
            "Italy                         4237790\n",
            "Argentina                     4038528\n",
            "Germany                       3715870\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "source": [
        "### Top 10 Countries with Most Deaths"
      ],
      "cell_type": "markdown",
      "metadata": {}
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "6BQcr9VBWAJp",
        "outputId": "0cade53e-3b7c-4f1c-81ba-9b73a897c573"
      },
      "source": [
        "relate_top_10('Deaths', 'Deaths')"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "\n",
            "Top 10 Countries with Most Deaths\n",
            "Country                     Deaths\n",
            "------------------------  --------\n",
            "United States of America    598316\n",
            "Brazil                      479515\n",
            "India                       355705\n",
            "Mexico                      229353\n",
            "Peru                        187157\n",
            "United Kingdom              128124\n",
            "Italy                       126767\n",
            "Russian Federation          122802\n",
            "France                      110364\n",
            "Colombia                     93394\n"
          ],
          "name": "stdout"
        }
      ]
    }
  ]
}