SQL Data Exploration using Python Notebook for SQLite.ipynb

{"cells":[{"cell_type":"markdown","metadata":{"id":"A4tXX516Sm7v"},"source":["# Reading and exploring the `COVID_DB.db` using SQL though Python\n","\n","To show case the ability to using SQL to understand the COVID data even when using Jupyther Notebook (and Google Drive) we'll try to explore the data using only SQL.\n","\n","Disclaimer: Python's `Numpy` and `Pandas` modules has many useful feature to allow data exploration and even (and especially) data cleaning. However to prove my SQL prowlness I will not use Python's extensive `Numpy` and `Pandas` features."]},{"cell_type":"markdown","metadata":{"id":"lU4QhAkZUt0g"},"source":["## Connect to Google Drive (if needed)\n","\n","If you're working on a project like this using Google Drive (like I am) than it is necessary to for your Python Notebook to have access to your Drive account to edit/modify using the code below:"]},{"cell_type":"code","execution_count":null,"metadata":{"colab":{"base_uri":"https://localhost:8080/"},"executionInfo":{"elapsed":17575,"status":"ok","timestamp":1645274214430,"user":{"displayName":"James Wolfe","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14GjniFB9tD5BdLBJA15GSIxZ-ItGzbUjXLrJb8IrwQ=s64","userId":"02458012257526617222"},"user_tz":300},"id":"31hGby8YVE1N","outputId":"dcb59bd8-ac93-4340-d84c-a528994d6028"},"outputs":[{"name":"stdout","output_type":"stream","text":["Mounted at /content/drive\n"]}],"source":["from google.colab import drive\n","drive.mount(\"/content/drive\")"]},{"cell_type":"markdown","metadata":{"id":"PRCrOCz7Vt23"},"source":["## Import the necessary modules\n","\n","To execute SQL queries in this notebook, I need 3 modules in Python to read the results of the SQL queries. They are:\n","\n","- [Pandas](https://pandas.pydata.org/)\n","- [sqlite3](https://docs.python.org/3/library/sqlite3.html)\n","- [sqlalchemy](https://www.sqlalchemy.org/)"]},{"cell_type":"code","execution_count":null,"metadata":{"id":"ByTn9UFmV0FG"},"outputs":[],"source":["import pandas as pd\n","import sqlite3\n","from sqlalchemy import create_engine"]},{"cell_type":"markdown","metadata":{"id":"JuVi5XEOWJ5s"},"source":["## Connecting to the `COVID_DB` database\n","\n","We'll need a connection to the `COVID_DB` database using an engine. An engine in this case is: an abstraction of the database. It needs to be created using SQLAlchemy's `create_engine` method in Python. I'll name this instance as `my_conn` for \"my connection\"."]},{"cell_type":"markdown","metadata":{"id":"YTzJSuEw-wpv"},"source":["If I'm using my Google Drive Account:"]},{"cell_type":"code","execution_count":null,"metadata":{"id":"qgQeWA4mW930"},"outputs":[],"source":["my_conn = create_engine(\"sqlite:////content/drive/MyDrive/Documents/Data Analysis Projects/SQL Data Exploration/COVID_DB.db\")"]},{"cell_type":"markdown","metadata":{"id":"Q3mLxpHG-wpx"},"source":["If I'm using my own computer:"]},{"cell_type":"code","execution_count":null,"metadata":{"id":"_RWXlshF-wpy"},"outputs":[],"source":["my_conn = create_engine(r\"sqlite:///C:\\Users\\jcwol\\Google Drive\\Documents\\Data Analysis Projects\\SQL Data Exploration/COVID_DB.db\")"]},{"cell_type":"markdown","metadata":{"id":"g-dsZbCWXOjm"},"source":["## SQL Data Exploration"]},{"cell_type":"markdown","metadata":{"id":"U4r0GMVckHs7"},"source":["Let's start with a general select all query statement from the `CovidDeaths` table and read only the first 10 rows."]},{"cell_type":"code","execution_count":null,"metadata":{"id":"PXYpN0VMkUh9"},"outputs":[],"source":["sql_query = '''\n","SELECT * FROM CovidDeaths WHERE continent IS NOT NULL ORDER BY date, total_cases \n","'''"]},{"cell_type":"code","execution_count":null,"metadata":{"id":"sP8ORQmylm3j"},"outputs":[],"source":["sql_df = pd.read_sql_query(sql_query, my_conn)"]},{"cell_type":"code","execution_count":null,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":601},"executionInfo":{"elapsed":160,"status":"ok","timestamp":1644697198885,"user":{"displayName":"James Wolfe","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14GjniFB9tD5BdLBJA15GSIxZ-ItGzbUjXLrJb8IrwQ=s64","userId":"02458012257526617222"},"user_tz":300},"id":"aAI2xsjWlxdO","outputId":"db045ab3-a4c6-41a9-c04d-4f73a7da057f"},"outputs":[{"data":{"text/html":["\n","  <div id=\"df-888fc9cd-bff1-4f3a-8713-43cb2cf2f380\">\n","    <div class=\"colab-df-container\">\n","      <div>\n","<style scoped>\n","    .dataframe tbody tr th:only-of-type {\n","        vertical-align: middle;\n","    }\n","\n","    .dataframe tbody tr th {\n","        vertical-align: top;\n","    }\n","\n","    .dataframe thead th {\n","        text-align: right;\n","    }\n","</style>\n","<table border=\"1\" class=\"dataframe\">\n","  <thead>\n","    <tr style=\"text-align: right;\">\n","      <th></th>\n","      <th>index</th>\n","      <th>iso_code</th>\n","      <th>continent</th>\n","      <th>location</th>\n","      <th>date</th>\n","      <th>population</th>\n","      <th>total_cases</th>\n","      <th>new_cases</th>\n","      <th>new_cases_smoothed</th>\n","      <th>total_deaths</th>\n","      <th>new_deaths</th>\n","      <th>new_deaths_smoothed</th>\n","      <th>total_cases_per_million</th>\n","      <th>new_cases_per_million</th>\n","      <th>new_cases_smoothed_per_million</th>\n","      <th>total_deaths_per_million</th>\n","      <th>new_deaths_per_million</th>\n","      <th>new_deaths_smoothed_per_million</th>\n","      <th>reproduction_rate</th>\n","      <th>icu_patients</th>\n","      <th>icu_patients_per_million</th>\n","      <th>hosp_patients</th>\n","      <th>hosp_patients_per_million</th>\n","      <th>weekly_icu_admissions</th>\n","      <th>weekly_icu_admissions_per_million</th>\n","      <th>weekly_hosp_admissions</th>\n","      <th>weekly_hosp_admissions_per_million</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>5550</td>\n","      <td>ARG</td>\n","      <td>South America</td>\n","      <td>Argentina</td>\n","      <td>2020-01-01</td>\n","      <td>45605823.0</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>92806</td>\n","      <td>MEX</td>\n","      <td>North America</td>\n","      <td>Mexico</td>\n","      <td>2020-01-01</td>\n","      <td>130262220.0</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","    </tr>\n","    <tr>\n","      <th>2</th>\n","      <td>112821</td>\n","      <td>PER</td>\n","      <td>South America</td>\n","      <td>Peru</td>\n","      <td>2020-01-01</td>\n","      <td>33359415.0</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","    </tr>\n","    <tr>\n","      <th>3</th>\n","      <td>5551</td>\n","      <td>ARG</td>\n","      <td>South America</td>\n","      <td>Argentina</td>\n","      <td>2020-01-02</td>\n","      <td>45605823.0</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","    </tr>\n","    <tr>\n","      <th>4</th>\n","      <td>92807</td>\n","      <td>MEX</td>\n","      <td>North America</td>\n","      <td>Mexico</td>\n","      <td>2020-01-02</td>\n","      <td>130262220.0</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","    </tr>\n","    <tr>\n","      <th>5</th>\n","      <td>112822</td>\n","      <td>PER</td>\n","      <td>South America</td>\n","      <td>Peru</td>\n","      <td>2020-01-02</td>\n","      <td>33359415.0</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","    </tr>\n","    <tr>\n","      <th>6</th>\n","      <td>5552</td>\n","      <td>ARG</td>\n","      <td>South America</td>\n","      <td>Argentina</td>\n","      <td>2020-01-03</td>\n","      <td>45605823.0</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","    </tr>\n","    <tr>\n","      <th>7</th>\n","      <td>92808</td>\n","      <td>MEX</td>\n","      <td>North America</td>\n","      <td>Mexico</td>\n","      <td>2020-01-03</td>\n","      <td>130262220.0</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","    </tr>\n","    <tr>\n","      <th>8</th>\n","      <td>112823</td>\n","      <td>PER</td>\n","      <td>South America</td>\n","      <td>Peru</td>\n","      <td>2020-01-03</td>\n","      <td>33359415.0</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","    </tr>\n","    <tr>\n","      <th>9</th>\n","      <td>5553</td>\n","      <td>ARG</td>\n","      <td>South America</td>\n","      <td>Argentina</td>\n","      <td>2020-01-04</td>\n","      <td>45605823.0</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","    </tr>\n","  </tbody>\n","</table>\n","</div>\n","      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-888fc9cd-bff1-4f3a-8713-43cb2cf2f380')\"\n","              title=\"Convert this dataframe to an interactive table.\"\n","              style=\"display:none;\">\n","        \n","  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n","       width=\"24px\">\n","    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n","    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n","  </svg>\n","      </button>\n","      \n","  <style>\n","    .colab-df-container {\n","      display:flex;\n","      flex-wrap:wrap;\n","      gap: 12px;\n","    }\n","\n","    .colab-df-convert {\n","      background-color: #E8F0FE;\n","      border: none;\n","      border-radius: 50%;\n","      cursor: pointer;\n","      display: none;\n","      fill: #1967D2;\n","      height: 32px;\n","      padding: 0 0 0 0;\n","      width: 32px;\n","    }\n","\n","    .colab-df-convert:hover {\n","      background-color: #E2EBFA;\n","      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n","      fill: #174EA6;\n","    }\n","\n","    [theme=dark] .colab-df-convert {\n","      background-color: #3B4455;\n","      fill: #D2E3FC;\n","    }\n","\n","    [theme=dark] .colab-df-convert:hover {\n","      background-color: #434B5C;\n","      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n","      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n","      fill: #FFFFFF;\n","    }\n","  </style>\n","\n","      <script>\n","        const buttonEl =\n","          document.querySelector('#df-888fc9cd-bff1-4f3a-8713-43cb2cf2f380 button.colab-df-convert');\n","        buttonEl.style.display =\n","          google.colab.kernel.accessAllowed ? 'block' : 'none';\n","\n","        async function convertToInteractive(key) {\n","          const element = document.querySelector('#df-888fc9cd-bff1-4f3a-8713-43cb2cf2f380');\n","          const dataTable =\n","            await google.colab.kernel.invokeFunction('convertToInteractive',\n","                                                     [key], {});\n","          if (!dataTable) return;\n","\n","          const docLinkHtml = 'Like what you see? Visit the ' +\n","            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n","            + ' to learn more about interactive tables.';\n","          element.innerHTML = '';\n","          dataTable['output_type'] = 'display_data';\n","          await google.colab.output.renderOutput(dataTable, element);\n","          const docLink = document.createElement('div');\n","          docLink.innerHTML = docLinkHtml;\n","          element.appendChild(docLink);\n","        }\n","      </script>\n","    </div>\n","  </div>\n","  "],"text/plain":["    index iso_code  ... weekly_hosp_admissions weekly_hosp_admissions_per_million\n","0    5550      ARG  ...                    NaN                                NaN\n","1   92806      MEX  ...                    NaN                                NaN\n","2  112821      PER  ...                    NaN                                NaN\n","3    5551      ARG  ...                    NaN                                NaN\n","4   92807      MEX  ...                    NaN                                NaN\n","5  112822      PER  ...                    NaN                                NaN\n","6    5552      ARG  ...                    NaN                                NaN\n","7   92808      MEX  ...                    NaN                                NaN\n","8  112823      PER  ...                    NaN                                NaN\n","9    5553      ARG  ...                    NaN                                NaN\n","\n","[10 rows x 27 columns]"]},"execution_count":8,"metadata":{},"output_type":"execute_result"}],"source":["sql_df.head(10)"]},{"cell_type":"markdown","metadata":{"id":"Ug4Kv_O2ohLR"},"source":["What's the likelyhood of catching COVID in your country (in my case the Unitied States)?"]},{"cell_type":"code","execution_count":null,"metadata":{"id":"ISKd1ntho58y"},"outputs":[],"source":["sql_query = '''\n","Select Location, date, total_cases,total_deaths, (total_deaths/total_cases)*100 as DeathPercentage\n","From CovidDeaths\n","Where location like '%states%'\n","and continent is not null \n","'''"]},{"cell_type":"code","execution_count":null,"metadata":{"id":"CPpqOXqKo5ug"},"outputs":[],"source":["sql_df = pd.read_sql_query(sql_query, my_conn)"]},{"cell_type":"code","execution_count":null,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":423},"executionInfo":{"elapsed":191,"status":"ok","timestamp":1644698379079,"user":{"displayName":"James Wolfe","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14GjniFB9tD5BdLBJA15GSIxZ-ItGzbUjXLrJb8IrwQ=s64","userId":"02458012257526617222"},"user_tz":300},"id":"EQt-UUTLo5a0","outputId":"22d53cbd-c9c9-4f59-beb2-200cc33dcd37"},"outputs":[{"data":{"text/html":["\n","  <div id=\"df-0bf9d03c-7665-40de-8152-c8135ab70945\">\n","    <div class=\"colab-df-container\">\n","      <div>\n","<style scoped>\n","    .dataframe tbody tr th:only-of-type {\n","        vertical-align: middle;\n","    }\n","\n","    .dataframe tbody tr th {\n","        vertical-align: top;\n","    }\n","\n","    .dataframe thead th {\n","        text-align: right;\n","    }\n","</style>\n","<table border=\"1\" class=\"dataframe\">\n","  <thead>\n","    <tr style=\"text-align: right;\">\n","      <th></th>\n","      <th>location</th>\n","      <th>date</th>\n","      <th>total_cases</th>\n","      <th>total_deaths</th>\n","      <th>DeathPercentage</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>United States</td>\n","      <td>2020-01-22</td>\n","      <td>1.0</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>United States</td>\n","      <td>2020-01-23</td>\n","      <td>1.0</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","    </tr>\n","    <tr>\n","      <th>2</th>\n","      <td>United States</td>\n","      <td>2020-01-24</td>\n","      <td>2.0</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","    </tr>\n","    <tr>\n","      <th>3</th>\n","      <td>United States</td>\n","      <td>2020-01-25</td>\n","      <td>2.0</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","    </tr>\n","    <tr>\n","      <th>4</th>\n","      <td>United States</td>\n","      <td>2020-01-26</td>\n","      <td>5.0</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","    </tr>\n","    <tr>\n","      <th>...</th>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","    </tr>\n","    <tr>\n","      <th>731</th>\n","      <td>United States</td>\n","      <td>2022-01-22</td>\n","      <td>70490987.0</td>\n","      <td>866470.0</td>\n","      <td>1.229193</td>\n","    </tr>\n","    <tr>\n","      <th>732</th>\n","      <td>United States</td>\n","      <td>2022-01-23</td>\n","      <td>70845794.0</td>\n","      <td>867042.0</td>\n","      <td>1.223844</td>\n","    </tr>\n","    <tr>\n","      <th>733</th>\n","      <td>United States</td>\n","      <td>2022-01-24</td>\n","      <td>71741698.0</td>\n","      <td>869026.0</td>\n","      <td>1.211326</td>\n","    </tr>\n","    <tr>\n","      <th>734</th>\n","      <td>United States</td>\n","      <td>2022-01-25</td>\n","      <td>72257016.0</td>\n","      <td>872025.0</td>\n","      <td>1.206838</td>\n","    </tr>\n","    <tr>\n","      <th>735</th>\n","      <td>United States</td>\n","      <td>2022-01-26</td>\n","      <td>72910136.0</td>\n","      <td>876065.0</td>\n","      <td>1.201568</td>\n","    </tr>\n","  </tbody>\n","</table>\n","<p>736 rows × 5 columns</p>\n","</div>\n","      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-0bf9d03c-7665-40de-8152-c8135ab70945')\"\n","              title=\"Convert this dataframe to an interactive table.\"\n","              style=\"display:none;\">\n","        \n","  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n","       width=\"24px\">\n","    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n","    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n","  </svg>\n","      </button>\n","      \n","  <style>\n","    .colab-df-container {\n","      display:flex;\n","      flex-wrap:wrap;\n","      gap: 12px;\n","    }\n","\n","    .colab-df-convert {\n","      background-color: #E8F0FE;\n","      border: none;\n","      border-radius: 50%;\n","      cursor: pointer;\n","      display: none;\n","      fill: #1967D2;\n","      height: 32px;\n","      padding: 0 0 0 0;\n","      width: 32px;\n","    }\n","\n","    .colab-df-convert:hover {\n","      background-color: #E2EBFA;\n","      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n","      fill: #174EA6;\n","    }\n","\n","    [theme=dark] .colab-df-convert {\n","      background-color: #3B4455;\n","      fill: #D2E3FC;\n","    }\n","\n","    [theme=dark] .colab-df-convert:hover {\n","      background-color: #434B5C;\n","      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n","      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n","      fill: #FFFFFF;\n","    }\n","  </style>\n","\n","      <script>\n","        const buttonEl =\n","          document.querySelector('#df-0bf9d03c-7665-40de-8152-c8135ab70945 button.colab-df-convert');\n","        buttonEl.style.display =\n","          google.colab.kernel.accessAllowed ? 'block' : 'none';\n","\n","        async function convertToInteractive(key) {\n","          const element = document.querySelector('#df-0bf9d03c-7665-40de-8152-c8135ab70945');\n","          const dataTable =\n","            await google.colab.kernel.invokeFunction('convertToInteractive',\n","                                                     [key], {});\n","          if (!dataTable) return;\n","\n","          const docLinkHtml = 'Like what you see? Visit the ' +\n","            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n","            + ' to learn more about interactive tables.';\n","          element.innerHTML = '';\n","          dataTable['output_type'] = 'display_data';\n","          await google.colab.output.renderOutput(dataTable, element);\n","          const docLink = document.createElement('div');\n","          docLink.innerHTML = docLinkHtml;\n","          element.appendChild(docLink);\n","        }\n","      </script>\n","    </div>\n","  </div>\n","  "],"text/plain":["          location        date  total_cases  total_deaths  DeathPercentage\n","0    United States  2020-01-22          1.0           NaN              NaN\n","1    United States  2020-01-23          1.0           NaN              NaN\n","2    United States  2020-01-24          2.0           NaN              NaN\n","3    United States  2020-01-25          2.0           NaN              NaN\n","4    United States  2020-01-26          5.0           NaN              NaN\n","..             ...         ...          ...           ...              ...\n","731  United States  2022-01-22   70490987.0      866470.0         1.229193\n","732  United States  2022-01-23   70845794.0      867042.0         1.223844\n","733  United States  2022-01-24   71741698.0      869026.0         1.211326\n","734  United States  2022-01-25   72257016.0      872025.0         1.206838\n","735  United States  2022-01-26   72910136.0      876065.0         1.201568\n","\n","[736 rows x 5 columns]"]},"execution_count":24,"metadata":{},"output_type":"execute_result"}],"source":["sql_df"]},{"cell_type":"markdown","metadata":{"id":"DXmZvBaeqe8m"},"source":["What percentage of population infected with Covid? In general?"]},{"cell_type":"code","execution_count":null,"metadata":{"id":"LdEMUTGtxX3z"},"outputs":[],"source":["sql_query = '''\n","Select Location, date, Population, total_cases,  (total_cases/population)*100 \n","as PercentPopulationInfected\n","From CovidDeaths\n","'''"]},{"cell_type":"code","execution_count":null,"metadata":{"id":"Ci3J3afLxpgd"},"outputs":[],"source":["sql_df = pd.read_sql_query(sql_query, my_conn)"]},{"cell_type":"code","execution_count":null,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":424},"executionInfo":{"elapsed":152,"status":"ok","timestamp":1645002317648,"user":{"displayName":"James Wolfe","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14GjniFB9tD5BdLBJA15GSIxZ-ItGzbUjXLrJb8IrwQ=s64","userId":"02458012257526617222"},"user_tz":300},"id":"m-eW-J_hxs1b","outputId":"da8a9676-2793-4fab-f005-7629dff74fcb"},"outputs":[{"data":{"text/html":["\n","  <div id=\"df-588cd2a6-dc75-4699-98ba-1ae34849910c\">\n","    <div class=\"colab-df-container\">\n","      <div>\n","<style scoped>\n","    .dataframe tbody tr th:only-of-type {\n","        vertical-align: middle;\n","    }\n","\n","    .dataframe tbody tr th {\n","        vertical-align: top;\n","    }\n","\n","    .dataframe thead th {\n","        text-align: right;\n","    }\n","</style>\n","<table border=\"1\" class=\"dataframe\">\n","  <thead>\n","    <tr style=\"text-align: right;\">\n","      <th></th>\n","      <th>location</th>\n","      <th>date</th>\n","      <th>population</th>\n","      <th>total_cases</th>\n","      <th>PercentPopulationInfected</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>Afghanistan</td>\n","      <td>2020-02-24</td>\n","      <td>39835428.0</td>\n","      <td>5.0</td>\n","      <td>0.000013</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>Afghanistan</td>\n","      <td>2020-02-25</td>\n","      <td>39835428.0</td>\n","      <td>5.0</td>\n","      <td>0.000013</td>\n","    </tr>\n","    <tr>\n","      <th>2</th>\n","      <td>Afghanistan</td>\n","      <td>2020-02-26</td>\n","      <td>39835428.0</td>\n","      <td>5.0</td>\n","      <td>0.000013</td>\n","    </tr>\n","    <tr>\n","      <th>3</th>\n","      <td>Afghanistan</td>\n","      <td>2020-02-27</td>\n","      <td>39835428.0</td>\n","      <td>5.0</td>\n","      <td>0.000013</td>\n","    </tr>\n","    <tr>\n","      <th>4</th>\n","      <td>Afghanistan</td>\n","      <td>2020-02-28</td>\n","      <td>39835428.0</td>\n","      <td>5.0</td>\n","      <td>0.000013</td>\n","    </tr>\n","    <tr>\n","      <th>...</th>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","    </tr>\n","    <tr>\n","      <th>157471</th>\n","      <td>Zimbabwe</td>\n","      <td>2022-01-22</td>\n","      <td>15092171.0</td>\n","      <td>228179.0</td>\n","      <td>1.511903</td>\n","    </tr>\n","    <tr>\n","      <th>157472</th>\n","      <td>Zimbabwe</td>\n","      <td>2022-01-23</td>\n","      <td>15092171.0</td>\n","      <td>228254.0</td>\n","      <td>1.512400</td>\n","    </tr>\n","    <tr>\n","      <th>157473</th>\n","      <td>Zimbabwe</td>\n","      <td>2022-01-24</td>\n","      <td>15092171.0</td>\n","      <td>228541.0</td>\n","      <td>1.514302</td>\n","    </tr>\n","    <tr>\n","      <th>157474</th>\n","      <td>Zimbabwe</td>\n","      <td>2022-01-25</td>\n","      <td>15092171.0</td>\n","      <td>228776.0</td>\n","      <td>1.515859</td>\n","    </tr>\n","    <tr>\n","      <th>157475</th>\n","      <td>Zimbabwe</td>\n","      <td>2022-01-26</td>\n","      <td>15092171.0</td>\n","      <td>228943.0</td>\n","      <td>1.516965</td>\n","    </tr>\n","  </tbody>\n","</table>\n","<p>157476 rows × 5 columns</p>\n","</div>\n","      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-588cd2a6-dc75-4699-98ba-1ae34849910c')\"\n","              title=\"Convert this dataframe to an interactive table.\"\n","              style=\"display:none;\">\n","        \n","  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n","       width=\"24px\">\n","    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n","    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n","  </svg>\n","      </button>\n","      \n","  <style>\n","    .colab-df-container {\n","      display:flex;\n","      flex-wrap:wrap;\n","      gap: 12px;\n","    }\n","\n","    .colab-df-convert {\n","      background-color: #E8F0FE;\n","      border: none;\n","      border-radius: 50%;\n","      cursor: pointer;\n","      display: none;\n","      fill: #1967D2;\n","      height: 32px;\n","      padding: 0 0 0 0;\n","      width: 32px;\n","    }\n","\n","    .colab-df-convert:hover {\n","      background-color: #E2EBFA;\n","      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n","      fill: #174EA6;\n","    }\n","\n","    [theme=dark] .colab-df-convert {\n","      background-color: #3B4455;\n","      fill: #D2E3FC;\n","    }\n","\n","    [theme=dark] .colab-df-convert:hover {\n","      background-color: #434B5C;\n","      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n","      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n","      fill: #FFFFFF;\n","    }\n","  </style>\n","\n","      <script>\n","        const buttonEl =\n","          document.querySelector('#df-588cd2a6-dc75-4699-98ba-1ae34849910c button.colab-df-convert');\n","        buttonEl.style.display =\n","          google.colab.kernel.accessAllowed ? 'block' : 'none';\n","\n","        async function convertToInteractive(key) {\n","          const element = document.querySelector('#df-588cd2a6-dc75-4699-98ba-1ae34849910c');\n","          const dataTable =\n","            await google.colab.kernel.invokeFunction('convertToInteractive',\n","                                                     [key], {});\n","          if (!dataTable) return;\n","\n","          const docLinkHtml = 'Like what you see? Visit the ' +\n","            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n","            + ' to learn more about interactive tables.';\n","          element.innerHTML = '';\n","          dataTable['output_type'] = 'display_data';\n","          await google.colab.output.renderOutput(dataTable, element);\n","          const docLink = document.createElement('div');\n","          docLink.innerHTML = docLinkHtml;\n","          element.appendChild(docLink);\n","        }\n","      </script>\n","    </div>\n","  </div>\n","  "],"text/plain":["           location        date  ...  total_cases  PercentPopulationInfected\n","0       Afghanistan  2020-02-24  ...          5.0                   0.000013\n","1       Afghanistan  2020-02-25  ...          5.0                   0.000013\n","2       Afghanistan  2020-02-26  ...          5.0                   0.000013\n","3       Afghanistan  2020-02-27  ...          5.0                   0.000013\n","4       Afghanistan  2020-02-28  ...          5.0                   0.000013\n","...             ...         ...  ...          ...                        ...\n","157471     Zimbabwe  2022-01-22  ...     228179.0                   1.511903\n","157472     Zimbabwe  2022-01-23  ...     228254.0                   1.512400\n","157473     Zimbabwe  2022-01-24  ...     228541.0                   1.514302\n","157474     Zimbabwe  2022-01-25  ...     228776.0                   1.515859\n","157475     Zimbabwe  2022-01-26  ...     228943.0                   1.516965\n","\n","[157476 rows x 5 columns]"]},"execution_count":7,"metadata":{},"output_type":"execute_result"}],"source":["sql_df"]},{"cell_type":"markdown","metadata":{"id":"VM8wBM1nyIuM"},"source":["What are the Countries with the Highest Infection Rate compared to Population?"]},{"cell_type":"code","execution_count":null,"metadata":{"id":"gFC94MahyP9s"},"outputs":[],"source":["sql_query = '''\n","Select Location, Population, MAX(total_cases) as HighestInfectionCount, \n"," Max((total_cases/population))*100 as PercentPopulationInfected\n","From CovidDeaths\n","Group by Location, Population\n","order by PercentPopulationInfected desc\n","'''"]},{"cell_type":"code","execution_count":null,"metadata":{"id":"Ig95YcPTydUM"},"outputs":[],"source":["sql_df = pd.read_sql_query(sql_query, my_conn)"]},{"cell_type":"code","execution_count":null,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":424},"executionInfo":{"elapsed":125,"status":"ok","timestamp":1645002496565,"user":{"displayName":"James Wolfe","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14GjniFB9tD5BdLBJA15GSIxZ-ItGzbUjXLrJb8IrwQ=s64","userId":"02458012257526617222"},"user_tz":300},"id":"Yl9J5uewyflc","outputId":"a1953a90-ae65-4370-d9d8-2d9c9b56ae5b"},"outputs":[{"data":{"text/html":["\n","  <div id=\"df-0aca75a3-8509-4df8-a0a1-fe543d29e57d\">\n","    <div class=\"colab-df-container\">\n","      <div>\n","<style scoped>\n","    .dataframe tbody tr th:only-of-type {\n","        vertical-align: middle;\n","    }\n","\n","    .dataframe tbody tr th {\n","        vertical-align: top;\n","    }\n","\n","    .dataframe thead th {\n","        text-align: right;\n","    }\n","</style>\n","<table border=\"1\" class=\"dataframe\">\n","  <thead>\n","    <tr style=\"text-align: right;\">\n","      <th></th>\n","      <th>location</th>\n","      <th>population</th>\n","      <th>HighestInfectionCount</th>\n","      <th>PercentPopulationInfected</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>Andorra</td>\n","      <td>77354.0</td>\n","      <td>35028.0</td>\n","      <td>45.282726</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>Gibraltar</td>\n","      <td>33691.0</td>\n","      <td>12277.0</td>\n","      <td>36.439999</td>\n","    </tr>\n","    <tr>\n","      <th>2</th>\n","      <td>Seychelles</td>\n","      <td>98910.0</td>\n","      <td>35606.0</td>\n","      <td>35.998382</td>\n","    </tr>\n","    <tr>\n","      <th>3</th>\n","      <td>San Marino</td>\n","      <td>34010.0</td>\n","      <td>11995.0</td>\n","      <td>35.269039</td>\n","    </tr>\n","    <tr>\n","      <th>4</th>\n","      <td>Montenegro</td>\n","      <td>628051.0</td>\n","      <td>215950.0</td>\n","      <td>34.384150</td>\n","    </tr>\n","    <tr>\n","      <th>...</th>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","    </tr>\n","    <tr>\n","      <th>233</th>\n","      <td>Pitcairn</td>\n","      <td>47.0</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","    </tr>\n","    <tr>\n","      <th>234</th>\n","      <td>Sint Maarten (Dutch part)</td>\n","      <td>43421.0</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","    </tr>\n","    <tr>\n","      <th>235</th>\n","      <td>Tokelau</td>\n","      <td>1368.0</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","    </tr>\n","    <tr>\n","      <th>236</th>\n","      <td>Turkmenistan</td>\n","      <td>6117933.0</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","    </tr>\n","    <tr>\n","      <th>237</th>\n","      <td>Tuvalu</td>\n","      <td>11925.0</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","    </tr>\n","  </tbody>\n","</table>\n","<p>238 rows × 4 columns</p>\n","</div>\n","      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-0aca75a3-8509-4df8-a0a1-fe543d29e57d')\"\n","              title=\"Convert this dataframe to an interactive table.\"\n","              style=\"display:none;\">\n","        \n","  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n","       width=\"24px\">\n","    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n","    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n","  </svg>\n","      </button>\n","      \n","  <style>\n","    .colab-df-container {\n","      display:flex;\n","      flex-wrap:wrap;\n","      gap: 12px;\n","    }\n","\n","    .colab-df-convert {\n","      background-color: #E8F0FE;\n","      border: none;\n","      border-radius: 50%;\n","      cursor: pointer;\n","      display: none;\n","      fill: #1967D2;\n","      height: 32px;\n","      padding: 0 0 0 0;\n","      width: 32px;\n","    }\n","\n","    .colab-df-convert:hover {\n","      background-color: #E2EBFA;\n","      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n","      fill: #174EA6;\n","    }\n","\n","    [theme=dark] .colab-df-convert {\n","      background-color: #3B4455;\n","      fill: #D2E3FC;\n","    }\n","\n","    [theme=dark] .colab-df-convert:hover {\n","      background-color: #434B5C;\n","      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n","      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n","      fill: #FFFFFF;\n","    }\n","  </style>\n","\n","      <script>\n","        const buttonEl =\n","          document.querySelector('#df-0aca75a3-8509-4df8-a0a1-fe543d29e57d button.colab-df-convert');\n","        buttonEl.style.display =\n","          google.colab.kernel.accessAllowed ? 'block' : 'none';\n","\n","        async function convertToInteractive(key) {\n","          const element = document.querySelector('#df-0aca75a3-8509-4df8-a0a1-fe543d29e57d');\n","          const dataTable =\n","            await google.colab.kernel.invokeFunction('convertToInteractive',\n","                                                     [key], {});\n","          if (!dataTable) return;\n","\n","          const docLinkHtml = 'Like what you see? Visit the ' +\n","            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n","            + ' to learn more about interactive tables.';\n","          element.innerHTML = '';\n","          dataTable['output_type'] = 'display_data';\n","          await google.colab.output.renderOutput(dataTable, element);\n","          const docLink = document.createElement('div');\n","          docLink.innerHTML = docLinkHtml;\n","          element.appendChild(docLink);\n","        }\n","      </script>\n","    </div>\n","  </div>\n","  "],"text/plain":["                      location  ...  PercentPopulationInfected\n","0                      Andorra  ...                  45.282726\n","1                    Gibraltar  ...                  36.439999\n","2                   Seychelles  ...                  35.998382\n","3                   San Marino  ...                  35.269039\n","4                   Montenegro  ...                  34.384150\n","..                         ...  ...                        ...\n","233                   Pitcairn  ...                        NaN\n","234  Sint Maarten (Dutch part)  ...                        NaN\n","235                    Tokelau  ...                        NaN\n","236               Turkmenistan  ...                        NaN\n","237                     Tuvalu  ...                        NaN\n","\n","[238 rows x 4 columns]"]},"execution_count":10,"metadata":{},"output_type":"execute_result"}],"source":["sql_df"]},{"cell_type":"markdown","metadata":{"id":"VJ1nmQriy6yt"},"source":["What Countries have the Highest Death Count per Population?\n"]},{"cell_type":"code","execution_count":null,"metadata":{"id":"FLb8heH1zDds"},"outputs":[],"source":["sql_query='''\n","Select Location, MAX(cast(Total_deaths as int)) as TotalDeathCount, \n","MAX((total_deaths/population))*100 as TotalDeathPercentage\n","From CovidDeaths\n","Where continent is not null \n","Group by Location\n","order by TotalDeathCount desc\n","'''"]},{"cell_type":"code","execution_count":null,"metadata":{"id":"REeCsnrhzQl9"},"outputs":[],"source":["sql_df = pd.read_sql_query(sql_query, my_conn)"]},{"cell_type":"code","execution_count":null,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":424},"executionInfo":{"elapsed":112,"status":"ok","timestamp":1645003751868,"user":{"displayName":"James Wolfe","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14GjniFB9tD5BdLBJA15GSIxZ-ItGzbUjXLrJb8IrwQ=s64","userId":"02458012257526617222"},"user_tz":300},"id":"xZlBTnrczSfd","outputId":"82721fa1-8af9-4ff3-a4c6-8ba2318f098b"},"outputs":[{"data":{"text/html":["\n","  <div id=\"df-dab7cab1-e27f-4992-b2be-fcb877eaabfd\">\n","    <div class=\"colab-df-container\">\n","      <div>\n","<style scoped>\n","    .dataframe tbody tr th:only-of-type {\n","        vertical-align: middle;\n","    }\n","\n","    .dataframe tbody tr th {\n","        vertical-align: top;\n","    }\n","\n","    .dataframe thead th {\n","        text-align: right;\n","    }\n","</style>\n","<table border=\"1\" class=\"dataframe\">\n","  <thead>\n","    <tr style=\"text-align: right;\">\n","      <th></th>\n","      <th>location</th>\n","      <th>TotalDeathCount</th>\n","      <th>TotalDeathPercentage</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>United States</td>\n","      <td>876065.0</td>\n","      <td>0.263150</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>Brazil</td>\n","      <td>624717.0</td>\n","      <td>0.291933</td>\n","    </tr>\n","    <tr>\n","      <th>2</th>\n","      <td>India</td>\n","      <td>491700.0</td>\n","      <td>0.035288</td>\n","    </tr>\n","    <tr>\n","      <th>3</th>\n","      <td>Russia</td>\n","      <td>321484.0</td>\n","      <td>0.220327</td>\n","    </tr>\n","    <tr>\n","      <th>4</th>\n","      <td>Mexico</td>\n","      <td>303776.0</td>\n","      <td>0.233203</td>\n","    </tr>\n","    <tr>\n","      <th>...</th>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","    </tr>\n","    <tr>\n","      <th>220</th>\n","      <td>Tokelau</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","    </tr>\n","    <tr>\n","      <th>221</th>\n","      <td>Tonga</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","    </tr>\n","    <tr>\n","      <th>222</th>\n","      <td>Turkmenistan</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","    </tr>\n","    <tr>\n","      <th>223</th>\n","      <td>Tuvalu</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","    </tr>\n","    <tr>\n","      <th>224</th>\n","      <td>Vatican</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","    </tr>\n","  </tbody>\n","</table>\n","<p>225 rows × 3 columns</p>\n","</div>\n","      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-dab7cab1-e27f-4992-b2be-fcb877eaabfd')\"\n","              title=\"Convert this dataframe to an interactive table.\"\n","              style=\"display:none;\">\n","        \n","  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n","       width=\"24px\">\n","    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n","    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n","  </svg>\n","      </button>\n","      \n","  <style>\n","    .colab-df-container {\n","      display:flex;\n","      flex-wrap:wrap;\n","      gap: 12px;\n","    }\n","\n","    .colab-df-convert {\n","      background-color: #E8F0FE;\n","      border: none;\n","      border-radius: 50%;\n","      cursor: pointer;\n","      display: none;\n","      fill: #1967D2;\n","      height: 32px;\n","      padding: 0 0 0 0;\n","      width: 32px;\n","    }\n","\n","    .colab-df-convert:hover {\n","      background-color: #E2EBFA;\n","      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n","      fill: #174EA6;\n","    }\n","\n","    [theme=dark] .colab-df-convert {\n","      background-color: #3B4455;\n","      fill: #D2E3FC;\n","    }\n","\n","    [theme=dark] .colab-df-convert:hover {\n","      background-color: #434B5C;\n","      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n","      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n","      fill: #FFFFFF;\n","    }\n","  </style>\n","\n","      <script>\n","        const buttonEl =\n","          document.querySelector('#df-dab7cab1-e27f-4992-b2be-fcb877eaabfd button.colab-df-convert');\n","        buttonEl.style.display =\n","          google.colab.kernel.accessAllowed ? 'block' : 'none';\n","\n","        async function convertToInteractive(key) {\n","          const element = document.querySelector('#df-dab7cab1-e27f-4992-b2be-fcb877eaabfd');\n","          const dataTable =\n","            await google.colab.kernel.invokeFunction('convertToInteractive',\n","                                                     [key], {});\n","          if (!dataTable) return;\n","\n","          const docLinkHtml = 'Like what you see? Visit the ' +\n","            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n","            + ' to learn more about interactive tables.';\n","          element.innerHTML = '';\n","          dataTable['output_type'] = 'display_data';\n","          await google.colab.output.renderOutput(dataTable, element);\n","          const docLink = document.createElement('div');\n","          docLink.innerHTML = docLinkHtml;\n","          element.appendChild(docLink);\n","        }\n","      </script>\n","    </div>\n","  </div>\n","  "],"text/plain":["          location  TotalDeathCount  TotalDeathPercentage\n","0    United States         876065.0              0.263150\n","1           Brazil         624717.0              0.291933\n","2            India         491700.0              0.035288\n","3           Russia         321484.0              0.220327\n","4           Mexico         303776.0              0.233203\n","..             ...              ...                   ...\n","220        Tokelau              NaN                   NaN\n","221          Tonga              NaN                   NaN\n","222   Turkmenistan              NaN                   NaN\n","223         Tuvalu              NaN                   NaN\n","224        Vatican              NaN                   NaN\n","\n","[225 rows x 3 columns]"]},"execution_count":49,"metadata":{},"output_type":"execute_result"}],"source":["sql_df"]},{"cell_type":"markdown","metadata":{"id":"qAmYo5vj1KUY"},"source":["Continents with the highest death count per population:"]},{"cell_type":"code","execution_count":null,"metadata":{"id":"qSGoXGCI1jmP"},"outputs":[],"source":["sql_query='''\n","Select continent, MAX(cast(Total_deaths as int)) as TotalDeathCount, \n","MAX((total_deaths/population))*100 as DeathPercentagePerContinent\n","From CovidDeaths\n","Where continent is not null \n","Group by continent\n","order by TotalDeathCount desc\n","'''"]},{"cell_type":"code","execution_count":null,"metadata":{"id":"mDOSmQqO1xe3"},"outputs":[],"source":["sql_df = pd.read_sql_query(sql_query, my_conn)"]},{"cell_type":"code","execution_count":null,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":238},"executionInfo":{"elapsed":145,"status":"ok","timestamp":1645004039305,"user":{"displayName":"James Wolfe","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14GjniFB9tD5BdLBJA15GSIxZ-ItGzbUjXLrJb8IrwQ=s64","userId":"02458012257526617222"},"user_tz":300},"id":"yc7xcMOy1zGv","outputId":"bb5eafba-66e7-4a6e-c391-4854dbb2a156"},"outputs":[{"data":{"text/html":["\n","  <div id=\"df-a2e730d3-eb1b-461d-988c-8eddeebfd4d0\">\n","    <div class=\"colab-df-container\">\n","      <div>\n","<style scoped>\n","    .dataframe tbody tr th:only-of-type {\n","        vertical-align: middle;\n","    }\n","\n","    .dataframe tbody tr th {\n","        vertical-align: top;\n","    }\n","\n","    .dataframe thead th {\n","        text-align: right;\n","    }\n","</style>\n","<table border=\"1\" class=\"dataframe\">\n","  <thead>\n","    <tr style=\"text-align: right;\">\n","      <th></th>\n","      <th>continent</th>\n","      <th>TotalDeathCount</th>\n","      <th>DeathPercentagePerContinent</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>North America</td>\n","      <td>876065</td>\n","      <td>0.263150</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>South America</td>\n","      <td>624717</td>\n","      <td>0.613281</td>\n","    </tr>\n","    <tr>\n","      <th>2</th>\n","      <td>Asia</td>\n","      <td>491700</td>\n","      <td>0.372207</td>\n","    </tr>\n","    <tr>\n","      <th>3</th>\n","      <td>Europe</td>\n","      <td>321484</td>\n","      <td>0.477608</td>\n","    </tr>\n","    <tr>\n","      <th>4</th>\n","      <td>Africa</td>\n","      <td>94625</td>\n","      <td>0.218260</td>\n","    </tr>\n","    <tr>\n","      <th>5</th>\n","      <td>Oceania</td>\n","      <td>3390</td>\n","      <td>0.225106</td>\n","    </tr>\n","  </tbody>\n","</table>\n","</div>\n","      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-a2e730d3-eb1b-461d-988c-8eddeebfd4d0')\"\n","              title=\"Convert this dataframe to an interactive table.\"\n","              style=\"display:none;\">\n","        \n","  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n","       width=\"24px\">\n","    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n","    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n","  </svg>\n","      </button>\n","      \n","  <style>\n","    .colab-df-container {\n","      display:flex;\n","      flex-wrap:wrap;\n","      gap: 12px;\n","    }\n","\n","    .colab-df-convert {\n","      background-color: #E8F0FE;\n","      border: none;\n","      border-radius: 50%;\n","      cursor: pointer;\n","      display: none;\n","      fill: #1967D2;\n","      height: 32px;\n","      padding: 0 0 0 0;\n","      width: 32px;\n","    }\n","\n","    .colab-df-convert:hover {\n","      background-color: #E2EBFA;\n","      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n","      fill: #174EA6;\n","    }\n","\n","    [theme=dark] .colab-df-convert {\n","      background-color: #3B4455;\n","      fill: #D2E3FC;\n","    }\n","\n","    [theme=dark] .colab-df-convert:hover {\n","      background-color: #434B5C;\n","      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n","      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n","      fill: #FFFFFF;\n","    }\n","  </style>\n","\n","      <script>\n","        const buttonEl =\n","          document.querySelector('#df-a2e730d3-eb1b-461d-988c-8eddeebfd4d0 button.colab-df-convert');\n","        buttonEl.style.display =\n","          google.colab.kernel.accessAllowed ? 'block' : 'none';\n","\n","        async function convertToInteractive(key) {\n","          const element = document.querySelector('#df-a2e730d3-eb1b-461d-988c-8eddeebfd4d0');\n","          const dataTable =\n","            await google.colab.kernel.invokeFunction('convertToInteractive',\n","                                                     [key], {});\n","          if (!dataTable) return;\n","\n","          const docLinkHtml = 'Like what you see? Visit the ' +\n","            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n","            + ' to learn more about interactive tables.';\n","          element.innerHTML = '';\n","          dataTable['output_type'] = 'display_data';\n","          await google.colab.output.renderOutput(dataTable, element);\n","          const docLink = document.createElement('div');\n","          docLink.innerHTML = docLinkHtml;\n","          element.appendChild(docLink);\n","        }\n","      </script>\n","    </div>\n","  </div>\n","  "],"text/plain":["       continent  TotalDeathCount  DeathPercentagePerContinent\n","0  North America           876065                     0.263150\n","1  South America           624717                     0.613281\n","2           Asia           491700                     0.372207\n","3         Europe           321484                     0.477608\n","4         Africa            94625                     0.218260\n","5        Oceania             3390                     0.225106"]},"execution_count":52,"metadata":{},"output_type":"execute_result"}],"source":["sql_df"]},{"cell_type":"markdown","metadata":{"id":"23fiCn7r4pQK"},"source":["Total Population vs Vaccinations:\n","What Percentage of Population have received at least one Covid Vaccine\n","\n","*Disclaimer: Using the complex SQL Statement with the OVER clause will produce an error on this Python Notebook. So we'll be using Python's Pandas to get the desired result in a pythonic way.\n"]},{"cell_type":"code","execution_count":null,"metadata":{"id":"vprRBprb47XT"},"outputs":[],"source":["# This will cause an error as using the OVER clause in this complex\n","# SQL statement can not be read by SQLAlchemy. It would work in a more powerful\n","# Database Management System.\n","sql_query='''\n","Select dea.continent, dea.location, dea.date, dea.population, vac.new_vaccinations\n",", SUM(CAST(vac.new_vaccinations as int)) OVER \n","(Partition by dea.Location Order by dea.location, dea.Date) as RollingPeopleVaccinated\n","From CovidDeaths dea\n","Join CovidVaccinations vac\n","\tOn dea.location = vac.location\n","\tand dea.date = vac.date\n","where dea.continent is not null \n","'''"]},{"cell_type":"code","execution_count":null,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":641},"executionInfo":{"elapsed":134,"status":"error","timestamp":1645005568941,"user":{"displayName":"James Wolfe","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14GjniFB9tD5BdLBJA15GSIxZ-ItGzbUjXLrJb8IrwQ=s64","userId":"02458012257526617222"},"user_tz":300},"id":"0HGKW8__5K4L","outputId":"ac675033-1c0e-455b-8690-233cc26d6ef7"},"outputs":[{"ename":"OperationalError","evalue":"ignored","output_type":"error","traceback":["\u001b[0;31m---------------------------------------------------------------------------\u001b[0m","\u001b[0;31mOperationalError\u001b[0m                          Traceback (most recent call last)","\u001b[0;32m/usr/local/lib/python3.7/dist-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_execute_context\u001b[0;34m(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)\u001b[0m\n\u001b[1;32m   1802\u001b[0m                     self.dialect.do_execute(\n\u001b[0;32m-> 1803\u001b[0;31m                         \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1804\u001b[0m                     )\n","\u001b[0;32m/usr/local/lib/python3.7/dist-packages/sqlalchemy/engine/default.py\u001b[0m in \u001b[0;36mdo_execute\u001b[0;34m(self, cursor, statement, parameters, context)\u001b[0m\n\u001b[1;32m    731\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mdo_execute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 732\u001b[0;31m         \u001b[0mcursor\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    733\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n","\u001b[0;31mOperationalError\u001b[0m: near \"(\": syntax error","\nThe above exception was the direct cause of the following exception:\n","\u001b[0;31mOperationalError\u001b[0m                          Traceback (most recent call last)","\u001b[0;32m<ipython-input-72-d97339365068>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0msql_df\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mpd\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mread_sql_query\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0msql_query\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmy_conn\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m","\u001b[0;32m/usr/local/lib/python3.7/dist-packages/pandas/io/sql.py\u001b[0m in \u001b[0;36mread_sql_query\u001b[0;34m(sql, con, index_col, coerce_float, params, parse_dates, chunksize, dtype)\u001b[0m\n\u001b[1;32m    441\u001b[0m         \u001b[0mparse_dates\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mparse_dates\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    442\u001b[0m         \u001b[0mchunksize\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mchunksize\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 443\u001b[0;31m         \u001b[0mdtype\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mdtype\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    444\u001b[0m     )\n\u001b[1;32m    445\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n","\u001b[0;32m/usr/local/lib/python3.7/dist-packages/pandas/io/sql.py\u001b[0m in \u001b[0;36mread_query\u001b[0;34m(self, sql, index_col, coerce_float, parse_dates, params, chunksize, dtype)\u001b[0m\n\u001b[1;32m   1577\u001b[0m         \u001b[0margs\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0m_convert_params\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0msql\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1578\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1579\u001b[0;31m         \u001b[0mresult\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m*\u001b[0m\u001b[0margs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1580\u001b[0m         \u001b[0mcolumns\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mresult\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mkeys\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1581\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n","\u001b[0;32m/usr/local/lib/python3.7/dist-packages/pandas/io/sql.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, *args, **kwargs)\u001b[0m\n\u001b[1;32m   1422\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m*\u001b[0m\u001b[0margs\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1423\u001b[0m         \u001b[0;34m\"\"\"Simple passthrough to SQLAlchemy connectable\"\"\"\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1424\u001b[0;31m         \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mconnectable\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecution_options\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m*\u001b[0m\u001b[0margs\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1425\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1426\u001b[0m     def read_table(\n","\u001b[0;32m<string>\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, statement, *multiparams, **params)\u001b[0m\n","\u001b[0;32m/usr/local/lib/python3.7/dist-packages/sqlalchemy/util/deprecations.py\u001b[0m in \u001b[0;36mwarned\u001b[0;34m(fn, *args, **kwargs)\u001b[0m\n\u001b[1;32m    399\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mskip_warning\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    400\u001b[0m             \u001b[0m_warn_with_version\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mmessage\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mversion\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mwtype\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstacklevel\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;36m3\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 401\u001b[0;31m         \u001b[0;32mreturn\u001b[0m \u001b[0mfn\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m*\u001b[0m\u001b[0margs\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    402\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    403\u001b[0m     \u001b[0mdoc\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mfunc\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m__doc__\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0;32mNone\u001b[0m \u001b[0;32mand\u001b[0m \u001b[0mfunc\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m__doc__\u001b[0m \u001b[0;32mor\u001b[0m \u001b[0;34m\"\"\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n","\u001b[0;32m/usr/local/lib/python3.7/dist-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, statement, *multiparams, **params)\u001b[0m\n\u001b[1;32m   3144\u001b[0m         \"\"\"\n\u001b[1;32m   3145\u001b[0m         \u001b[0mconnection\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mconnect\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mclose_with_result\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mTrue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 3146\u001b[0;31m         \u001b[0;32mreturn\u001b[0m \u001b[0mconnection\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m*\u001b[0m\u001b[0mmultiparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mparams\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   3147\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   3148\u001b[0m     @util.deprecated_20(\n","\u001b[0;32m/usr/local/lib/python3.7/dist-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, statement, *multiparams, **params)\u001b[0m\n\u001b[1;32m   1277\u001b[0m                 \u001b[0mparams\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1278\u001b[0m                 \u001b[0m_EMPTY_EXECUTION_OPTS\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1279\u001b[0;31m                 \u001b[0mfuture\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mFalse\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1280\u001b[0m             )\n\u001b[1;32m   1281\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n","\u001b[0;32m/usr/local/lib/python3.7/dist-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_exec_driver_sql\u001b[0;34m(self, statement, multiparams, params, execution_options, future)\u001b[0m\n\u001b[1;32m   1583\u001b[0m             \u001b[0mexecution_options\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1584\u001b[0m             \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1585\u001b[0;31m             \u001b[0mdistilled_parameters\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1586\u001b[0m         )\n\u001b[1;32m   1587\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n","\u001b[0;32m/usr/local/lib/python3.7/dist-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_execute_context\u001b[0;34m(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)\u001b[0m\n\u001b[1;32m   1844\u001b[0m         \u001b[0;32mexcept\u001b[0m \u001b[0mBaseException\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0me\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1845\u001b[0m             self._handle_dbapi_exception(\n\u001b[0;32m-> 1846\u001b[0;31m                 \u001b[0me\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1847\u001b[0m             )\n\u001b[1;32m   1848\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n","\u001b[0;32m/usr/local/lib/python3.7/dist-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_handle_dbapi_exception\u001b[0;34m(self, e, statement, parameters, cursor, context)\u001b[0m\n\u001b[1;32m   2025\u001b[0m             \u001b[0;32melif\u001b[0m \u001b[0mshould_wrap\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2026\u001b[0m                 util.raise_(\n\u001b[0;32m-> 2027\u001b[0;31m                     \u001b[0msqlalchemy_exception\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mwith_traceback\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mexc_info\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m2\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mfrom_\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0me\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   2028\u001b[0m                 )\n\u001b[1;32m   2029\u001b[0m             \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n","\u001b[0;32m/usr/local/lib/python3.7/dist-packages/sqlalchemy/util/compat.py\u001b[0m in \u001b[0;36mraise_\u001b[0;34m(***failed resolving arguments***)\u001b[0m\n\u001b[1;32m    205\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    206\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 207\u001b[0;31m             \u001b[0;32mraise\u001b[0m \u001b[0mexception\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    208\u001b[0m         \u001b[0;32mfinally\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    209\u001b[0m             \u001b[0;31m# credit to\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n","\u001b[0;32m/usr/local/lib/python3.7/dist-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_execute_context\u001b[0;34m(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)\u001b[0m\n\u001b[1;32m   1801\u001b[0m                 \u001b[0;32mif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mevt_handled\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1802\u001b[0m                     self.dialect.do_execute(\n\u001b[0;32m-> 1803\u001b[0;31m                         \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1804\u001b[0m                     )\n\u001b[1;32m   1805\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n","\u001b[0;32m/usr/local/lib/python3.7/dist-packages/sqlalchemy/engine/default.py\u001b[0m in \u001b[0;36mdo_execute\u001b[0;34m(self, cursor, statement, parameters, context)\u001b[0m\n\u001b[1;32m    730\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    731\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mdo_execute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 732\u001b[0;31m         \u001b[0mcursor\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    733\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    734\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mdo_execute_no_params\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n","\u001b[0;31mOperationalError\u001b[0m: (sqlite3.OperationalError) near \"(\": syntax error\n[SQL: \nSelect dea.continent, dea.location, dea.date, dea.population, vac.new_vaccinations\n, SUM(CAST(vac.new_vaccinations as int)) OVER (Partition by dea.Location Order by dea.location, dea.Date) as RollingPeopleVaccinated\nFrom CovidDeaths dea\nJoin CovidVaccinations vac\n\tOn dea.location = vac.location\n\tand dea.date = vac.date\nwhere dea.continent is not null \n]\n(Background on this error at: https://sqlalche.me/e/14/e3q8)"]}],"source":["sql_df = pd.read_sql_query(sql_query, my_conn)"]},{"cell_type":"markdown","metadata":{"id":"0m8xhhImHWHX"},"source":["The next following lines will use Python instead to answer this question since we can't get an answer from the query result of SQLAlchemy. We'll start the usual way of getting the query result without the OVER clause then get the final result using Python's Pandas."]},{"cell_type":"code","execution_count":null,"metadata":{"id":"imV96nWwBap9"},"outputs":[],"source":["sql_query='''\n","Select dea.continent, dea.location, dea.date, dea.population, \n","vac.new_vaccinations\n","From CovidDeaths dea\n","Join CovidVaccinations vac\n","\tOn dea.location = vac.location\n","\tand dea.date = vac.date\n","where dea.continent is not null \n","'''"]},{"cell_type":"code","execution_count":null,"metadata":{"id":"HYmYRALuFCDF"},"outputs":[],"source":["sql_df = pd.read_sql_query(sql_query, my_conn)"]},{"cell_type":"code","execution_count":null,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":423},"executionInfo":{"elapsed":177,"status":"ok","timestamp":1645274271090,"user":{"displayName":"James Wolfe","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14GjniFB9tD5BdLBJA15GSIxZ-ItGzbUjXLrJb8IrwQ=s64","userId":"02458012257526617222"},"user_tz":300},"id":"hblqZg4DFDuN","outputId":"0f8dd920-768b-4eac-ddec-c382ac1a4c19"},"outputs":[{"data":{"text/html":["\n","  <div id=\"df-95c82208-ef34-4c6a-adde-9fcb005e385e\">\n","    <div class=\"colab-df-container\">\n","      <div>\n","<style scoped>\n","    .dataframe tbody tr th:only-of-type {\n","        vertical-align: middle;\n","    }\n","\n","    .dataframe tbody tr th {\n","        vertical-align: top;\n","    }\n","\n","    .dataframe thead th {\n","        text-align: right;\n","    }\n","</style>\n","<table border=\"1\" class=\"dataframe\">\n","  <thead>\n","    <tr style=\"text-align: right;\">\n","      <th></th>\n","      <th>continent</th>\n","      <th>location</th>\n","      <th>date</th>\n","      <th>population</th>\n","      <th>new_vaccinations</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>Asia</td>\n","      <td>Afghanistan</td>\n","      <td>2020-02-24</td>\n","      <td>39835428.0</td>\n","      <td>NaN</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>Asia</td>\n","      <td>Afghanistan</td>\n","      <td>2020-02-25</td>\n","      <td>39835428.0</td>\n","      <td>NaN</td>\n","    </tr>\n","    <tr>\n","      <th>2</th>\n","      <td>Asia</td>\n","      <td>Afghanistan</td>\n","      <td>2020-02-26</td>\n","      <td>39835428.0</td>\n","      <td>NaN</td>\n","    </tr>\n","    <tr>\n","      <th>3</th>\n","      <td>Asia</td>\n","      <td>Afghanistan</td>\n","      <td>2020-02-27</td>\n","      <td>39835428.0</td>\n","      <td>NaN</td>\n","    </tr>\n","    <tr>\n","      <th>4</th>\n","      <td>Asia</td>\n","      <td>Afghanistan</td>\n","      <td>2020-02-28</td>\n","      <td>39835428.0</td>\n","      <td>NaN</td>\n","    </tr>\n","    <tr>\n","      <th>...</th>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","    </tr>\n","    <tr>\n","      <th>148009</th>\n","      <td>Africa</td>\n","      <td>Zimbabwe</td>\n","      <td>2022-01-22</td>\n","      <td>15092171.0</td>\n","      <td>9904.0</td>\n","    </tr>\n","    <tr>\n","      <th>148010</th>\n","      <td>Africa</td>\n","      <td>Zimbabwe</td>\n","      <td>2022-01-23</td>\n","      <td>15092171.0</td>\n","      <td>6117.0</td>\n","    </tr>\n","    <tr>\n","      <th>148011</th>\n","      <td>Africa</td>\n","      <td>Zimbabwe</td>\n","      <td>2022-01-24</td>\n","      <td>15092171.0</td>\n","      <td>5082.0</td>\n","    </tr>\n","    <tr>\n","      <th>148012</th>\n","      <td>Africa</td>\n","      <td>Zimbabwe</td>\n","      <td>2022-01-25</td>\n","      <td>15092171.0</td>\n","      <td>7589.0</td>\n","    </tr>\n","    <tr>\n","      <th>148013</th>\n","      <td>Africa</td>\n","      <td>Zimbabwe</td>\n","      <td>2022-01-26</td>\n","      <td>15092171.0</td>\n","      <td>NaN</td>\n","    </tr>\n","  </tbody>\n","</table>\n","<p>148014 rows × 5 columns</p>\n","</div>\n","      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-95c82208-ef34-4c6a-adde-9fcb005e385e')\"\n","              title=\"Convert this dataframe to an interactive table.\"\n","              style=\"display:none;\">\n","        \n","  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n","       width=\"24px\">\n","    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n","    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n","  </svg>\n","      </button>\n","      \n","  <style>\n","    .colab-df-container {\n","      display:flex;\n","      flex-wrap:wrap;\n","      gap: 12px;\n","    }\n","\n","    .colab-df-convert {\n","      background-color: #E8F0FE;\n","      border: none;\n","      border-radius: 50%;\n","      cursor: pointer;\n","      display: none;\n","      fill: #1967D2;\n","      height: 32px;\n","      padding: 0 0 0 0;\n","      width: 32px;\n","    }\n","\n","    .colab-df-convert:hover {\n","      background-color: #E2EBFA;\n","      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n","      fill: #174EA6;\n","    }\n","\n","    [theme=dark] .colab-df-convert {\n","      background-color: #3B4455;\n","      fill: #D2E3FC;\n","    }\n","\n","    [theme=dark] .colab-df-convert:hover {\n","      background-color: #434B5C;\n","      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n","      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n","      fill: #FFFFFF;\n","    }\n","  </style>\n","\n","      <script>\n","        const buttonEl =\n","          document.querySelector('#df-95c82208-ef34-4c6a-adde-9fcb005e385e button.colab-df-convert');\n","        buttonEl.style.display =\n","          google.colab.kernel.accessAllowed ? 'block' : 'none';\n","\n","        async function convertToInteractive(key) {\n","          const element = document.querySelector('#df-95c82208-ef34-4c6a-adde-9fcb005e385e');\n","          const dataTable =\n","            await google.colab.kernel.invokeFunction('convertToInteractive',\n","                                                     [key], {});\n","          if (!dataTable) return;\n","\n","          const docLinkHtml = 'Like what you see? Visit the ' +\n","            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n","            + ' to learn more about interactive tables.';\n","          element.innerHTML = '';\n","          dataTable['output_type'] = 'display_data';\n","          await google.colab.output.renderOutput(dataTable, element);\n","          const docLink = document.createElement('div');\n","          docLink.innerHTML = docLinkHtml;\n","          element.appendChild(docLink);\n","        }\n","      </script>\n","    </div>\n","  </div>\n","  "],"text/plain":["       continent     location        date  population  new_vaccinations\n","0           Asia  Afghanistan  2020-02-24  39835428.0               NaN\n","1           Asia  Afghanistan  2020-02-25  39835428.0               NaN\n","2           Asia  Afghanistan  2020-02-26  39835428.0               NaN\n","3           Asia  Afghanistan  2020-02-27  39835428.0               NaN\n","4           Asia  Afghanistan  2020-02-28  39835428.0               NaN\n","...          ...          ...         ...         ...               ...\n","148009    Africa     Zimbabwe  2022-01-22  15092171.0            9904.0\n","148010    Africa     Zimbabwe  2022-01-23  15092171.0            6117.0\n","148011    Africa     Zimbabwe  2022-01-24  15092171.0            5082.0\n","148012    Africa     Zimbabwe  2022-01-25  15092171.0            7589.0\n","148013    Africa     Zimbabwe  2022-01-26  15092171.0               NaN\n","\n","[148014 rows x 5 columns]"]},"execution_count":7,"metadata":{},"output_type":"execute_result"}],"source":["sql_df"]},{"cell_type":"code","execution_count":null,"metadata":{"id":"Wu1Fhx6rGn85"},"outputs":[],"source":["import numpy as np\n","# For how to create a similar result from SQL OVER clause using Python Pandas\n","# https://towardsdatascience.com/sql-window-functions-in-python-pandas-data-science-dc7c7a63cbb4\n","# This a nice reference for those using Pandas if they are familiar with SQL."]},{"cell_type":"code","execution_count":null,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":423},"executionInfo":{"elapsed":374,"status":"ok","timestamp":1645276028979,"user":{"displayName":"James Wolfe","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14GjniFB9tD5BdLBJA15GSIxZ-ItGzbUjXLrJb8IrwQ=s64","userId":"02458012257526617222"},"user_tz":300},"id":"hvNeeZGqJ7nV","outputId":"003213ff-64da-437d-9e7a-9410b354a88b"},"outputs":[{"data":{"text/html":["\n","  <div id=\"df-430805f6-6992-46ec-ac74-f5789a4a5f05\">\n","    <div class=\"colab-df-container\">\n","      <div>\n","<style scoped>\n","    .dataframe tbody tr th:only-of-type {\n","        vertical-align: middle;\n","    }\n","\n","    .dataframe tbody tr th {\n","        vertical-align: top;\n","    }\n","\n","    .dataframe thead th {\n","        text-align: right;\n","    }\n","</style>\n","<table border=\"1\" class=\"dataframe\">\n","  <thead>\n","    <tr style=\"text-align: right;\">\n","      <th></th>\n","      <th>continent</th>\n","      <th>location</th>\n","      <th>date</th>\n","      <th>population</th>\n","      <th>new_vaccinations</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>147336</th>\n","      <td>Africa</td>\n","      <td>Zimbabwe</td>\n","      <td>2020-03-20</td>\n","      <td>15092171.0</td>\n","      <td>NaN</td>\n","    </tr>\n","    <tr>\n","      <th>147337</th>\n","      <td>Africa</td>\n","      <td>Zimbabwe</td>\n","      <td>2020-03-21</td>\n","      <td>15092171.0</td>\n","      <td>NaN</td>\n","    </tr>\n","    <tr>\n","      <th>147338</th>\n","      <td>Africa</td>\n","      <td>Zimbabwe</td>\n","      <td>2020-03-22</td>\n","      <td>15092171.0</td>\n","      <td>NaN</td>\n","    </tr>\n","    <tr>\n","      <th>147339</th>\n","      <td>Africa</td>\n","      <td>Zimbabwe</td>\n","      <td>2020-03-23</td>\n","      <td>15092171.0</td>\n","      <td>NaN</td>\n","    </tr>\n","    <tr>\n","      <th>147340</th>\n","      <td>Africa</td>\n","      <td>Zimbabwe</td>\n","      <td>2020-03-24</td>\n","      <td>15092171.0</td>\n","      <td>NaN</td>\n","    </tr>\n","    <tr>\n","      <th>...</th>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","    </tr>\n","    <tr>\n","      <th>698</th>\n","      <td>Asia</td>\n","      <td>Afghanistan</td>\n","      <td>2022-01-22</td>\n","      <td>39835428.0</td>\n","      <td>NaN</td>\n","    </tr>\n","    <tr>\n","      <th>699</th>\n","      <td>Asia</td>\n","      <td>Afghanistan</td>\n","      <td>2022-01-23</td>\n","      <td>39835428.0</td>\n","      <td>NaN</td>\n","    </tr>\n","    <tr>\n","      <th>700</th>\n","      <td>Asia</td>\n","      <td>Afghanistan</td>\n","      <td>2022-01-24</td>\n","      <td>39835428.0</td>\n","      <td>NaN</td>\n","    </tr>\n","    <tr>\n","      <th>701</th>\n","      <td>Asia</td>\n","      <td>Afghanistan</td>\n","      <td>2022-01-25</td>\n","      <td>39835428.0</td>\n","      <td>NaN</td>\n","    </tr>\n","    <tr>\n","      <th>702</th>\n","      <td>Asia</td>\n","      <td>Afghanistan</td>\n","      <td>2022-01-26</td>\n","      <td>39835428.0</td>\n","      <td>NaN</td>\n","    </tr>\n","  </tbody>\n","</table>\n","<p>148014 rows × 5 columns</p>\n","</div>\n","      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-430805f6-6992-46ec-ac74-f5789a4a5f05')\"\n","              title=\"Convert this dataframe to an interactive table.\"\n","              style=\"display:none;\">\n","        \n","  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n","       width=\"24px\">\n","    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n","    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n","  </svg>\n","      </button>\n","      \n","  <style>\n","    .colab-df-container {\n","      display:flex;\n","      flex-wrap:wrap;\n","      gap: 12px;\n","    }\n","\n","    .colab-df-convert {\n","      background-color: #E8F0FE;\n","      border: none;\n","      border-radius: 50%;\n","      cursor: pointer;\n","      display: none;\n","      fill: #1967D2;\n","      height: 32px;\n","      padding: 0 0 0 0;\n","      width: 32px;\n","    }\n","\n","    .colab-df-convert:hover {\n","      background-color: #E2EBFA;\n","      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n","      fill: #174EA6;\n","    }\n","\n","    [theme=dark] .colab-df-convert {\n","      background-color: #3B4455;\n","      fill: #D2E3FC;\n","    }\n","\n","    [theme=dark] .colab-df-convert:hover {\n","      background-color: #434B5C;\n","      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n","      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n","      fill: #FFFFFF;\n","    }\n","  </style>\n","\n","      <script>\n","        const buttonEl =\n","          document.querySelector('#df-430805f6-6992-46ec-ac74-f5789a4a5f05 button.colab-df-convert');\n","        buttonEl.style.display =\n","          google.colab.kernel.accessAllowed ? 'block' : 'none';\n","\n","        async function convertToInteractive(key) {\n","          const element = document.querySelector('#df-430805f6-6992-46ec-ac74-f5789a4a5f05');\n","          const dataTable =\n","            await google.colab.kernel.invokeFunction('convertToInteractive',\n","                                                     [key], {});\n","          if (!dataTable) return;\n","\n","          const docLinkHtml = 'Like what you see? Visit the ' +\n","            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n","            + ' to learn more about interactive tables.';\n","          element.innerHTML = '';\n","          dataTable['output_type'] = 'display_data';\n","          await google.colab.output.renderOutput(dataTable, element);\n","          const docLink = document.createElement('div');\n","          docLink.innerHTML = docLinkHtml;\n","          element.appendChild(docLink);\n","        }\n","      </script>\n","    </div>\n","  </div>\n","  "],"text/plain":["       continent     location        date  population  new_vaccinations\n","147336    Africa     Zimbabwe  2020-03-20  15092171.0               NaN\n","147337    Africa     Zimbabwe  2020-03-21  15092171.0               NaN\n","147338    Africa     Zimbabwe  2020-03-22  15092171.0               NaN\n","147339    Africa     Zimbabwe  2020-03-23  15092171.0               NaN\n","147340    Africa     Zimbabwe  2020-03-24  15092171.0               NaN\n","...          ...          ...         ...         ...               ...\n","698         Asia  Afghanistan  2022-01-22  39835428.0               NaN\n","699         Asia  Afghanistan  2022-01-23  39835428.0               NaN\n","700         Asia  Afghanistan  2022-01-24  39835428.0               NaN\n","701         Asia  Afghanistan  2022-01-25  39835428.0               NaN\n","702         Asia  Afghanistan  2022-01-26  39835428.0               NaN\n","\n","[148014 rows x 5 columns]"]},"execution_count":17,"metadata":{},"output_type":"execute_result"}],"source":["sql_df.sort_values(by=[\"location\", \"date\"], ascending=[False,True])"]},{"cell_type":"code","execution_count":null,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":423},"executionInfo":{"elapsed":209,"status":"ok","timestamp":1645276037661,"user":{"displayName":"James Wolfe","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14GjniFB9tD5BdLBJA15GSIxZ-ItGzbUjXLrJb8IrwQ=s64","userId":"02458012257526617222"},"user_tz":300},"id":"wT7Yw76eEh0I","outputId":"d8dbe391-0c99-45c2-9c83-4b803ed7edd1"},"outputs":[{"data":{"text/html":["\n","  <div id=\"df-10735d8d-125b-451e-8f67-9608063da5ee\">\n","    <div class=\"colab-df-container\">\n","      <div>\n","<style scoped>\n","    .dataframe tbody tr th:only-of-type {\n","        vertical-align: middle;\n","    }\n","\n","    .dataframe tbody tr th {\n","        vertical-align: top;\n","    }\n","\n","    .dataframe thead th {\n","        text-align: right;\n","    }\n","</style>\n","<table border=\"1\" class=\"dataframe\">\n","  <thead>\n","    <tr style=\"text-align: right;\">\n","      <th></th>\n","      <th>continent</th>\n","      <th>location</th>\n","      <th>date</th>\n","      <th>population</th>\n","      <th>new_vaccinations</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>Asia</td>\n","      <td>Afghanistan</td>\n","      <td>2020-02-24</td>\n","      <td>39835428.0</td>\n","      <td>NaN</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>Asia</td>\n","      <td>Afghanistan</td>\n","      <td>2020-02-25</td>\n","      <td>39835428.0</td>\n","      <td>NaN</td>\n","    </tr>\n","    <tr>\n","      <th>2</th>\n","      <td>Asia</td>\n","      <td>Afghanistan</td>\n","      <td>2020-02-26</td>\n","      <td>39835428.0</td>\n","      <td>NaN</td>\n","    </tr>\n","    <tr>\n","      <th>3</th>\n","      <td>Asia</td>\n","      <td>Afghanistan</td>\n","      <td>2020-02-27</td>\n","      <td>39835428.0</td>\n","      <td>NaN</td>\n","    </tr>\n","    <tr>\n","      <th>4</th>\n","      <td>Asia</td>\n","      <td>Afghanistan</td>\n","      <td>2020-02-28</td>\n","      <td>39835428.0</td>\n","      <td>NaN</td>\n","    </tr>\n","    <tr>\n","      <th>...</th>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","    </tr>\n","    <tr>\n","      <th>148009</th>\n","      <td>Africa</td>\n","      <td>Zimbabwe</td>\n","      <td>2022-01-22</td>\n","      <td>15092171.0</td>\n","      <td>9904.0</td>\n","    </tr>\n","    <tr>\n","      <th>148010</th>\n","      <td>Africa</td>\n","      <td>Zimbabwe</td>\n","      <td>2022-01-23</td>\n","      <td>15092171.0</td>\n","      <td>6117.0</td>\n","    </tr>\n","    <tr>\n","      <th>148011</th>\n","      <td>Africa</td>\n","      <td>Zimbabwe</td>\n","      <td>2022-01-24</td>\n","      <td>15092171.0</td>\n","      <td>5082.0</td>\n","    </tr>\n","    <tr>\n","      <th>148012</th>\n","      <td>Africa</td>\n","      <td>Zimbabwe</td>\n","      <td>2022-01-25</td>\n","      <td>15092171.0</td>\n","      <td>7589.0</td>\n","    </tr>\n","    <tr>\n","      <th>148013</th>\n","      <td>Africa</td>\n","      <td>Zimbabwe</td>\n","      <td>2022-01-26</td>\n","      <td>15092171.0</td>\n","      <td>NaN</td>\n","    </tr>\n","  </tbody>\n","</table>\n","<p>148014 rows × 5 columns</p>\n","</div>\n","      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-10735d8d-125b-451e-8f67-9608063da5ee')\"\n","              title=\"Convert this dataframe to an interactive table.\"\n","              style=\"display:none;\">\n","        \n","  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n","       width=\"24px\">\n","    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n","    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n","  </svg>\n","      </button>\n","      \n","  <style>\n","    .colab-df-container {\n","      display:flex;\n","      flex-wrap:wrap;\n","      gap: 12px;\n","    }\n","\n","    .colab-df-convert {\n","      background-color: #E8F0FE;\n","      border: none;\n","      border-radius: 50%;\n","      cursor: pointer;\n","      display: none;\n","      fill: #1967D2;\n","      height: 32px;\n","      padding: 0 0 0 0;\n","      width: 32px;\n","    }\n","\n","    .colab-df-convert:hover {\n","      background-color: #E2EBFA;\n","      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n","      fill: #174EA6;\n","    }\n","\n","    [theme=dark] .colab-df-convert {\n","      background-color: #3B4455;\n","      fill: #D2E3FC;\n","    }\n","\n","    [theme=dark] .colab-df-convert:hover {\n","      background-color: #434B5C;\n","      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n","      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n","      fill: #FFFFFF;\n","    }\n","  </style>\n","\n","      <script>\n","        const buttonEl =\n","          document.querySelector('#df-10735d8d-125b-451e-8f67-9608063da5ee button.colab-df-convert');\n","        buttonEl.style.display =\n","          google.colab.kernel.accessAllowed ? 'block' : 'none';\n","\n","        async function convertToInteractive(key) {\n","          const element = document.querySelector('#df-10735d8d-125b-451e-8f67-9608063da5ee');\n","          const dataTable =\n","            await google.colab.kernel.invokeFunction('convertToInteractive',\n","                                                     [key], {});\n","          if (!dataTable) return;\n","\n","          const docLinkHtml = 'Like what you see? Visit the ' +\n","            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n","            + ' to learn more about interactive tables.';\n","          element.innerHTML = '';\n","          dataTable['output_type'] = 'display_data';\n","          await google.colab.output.renderOutput(dataTable, element);\n","          const docLink = document.createElement('div');\n","          docLink.innerHTML = docLinkHtml;\n","          element.appendChild(docLink);\n","        }\n","      </script>\n","    </div>\n","  </div>\n","  "],"text/plain":["       continent     location        date  population  new_vaccinations\n","0           Asia  Afghanistan  2020-02-24  39835428.0               NaN\n","1           Asia  Afghanistan  2020-02-25  39835428.0               NaN\n","2           Asia  Afghanistan  2020-02-26  39835428.0               NaN\n","3           Asia  Afghanistan  2020-02-27  39835428.0               NaN\n","4           Asia  Afghanistan  2020-02-28  39835428.0               NaN\n","...          ...          ...         ...         ...               ...\n","148009    Africa     Zimbabwe  2022-01-22  15092171.0            9904.0\n","148010    Africa     Zimbabwe  2022-01-23  15092171.0            6117.0\n","148011    Africa     Zimbabwe  2022-01-24  15092171.0            5082.0\n","148012    Africa     Zimbabwe  2022-01-25  15092171.0            7589.0\n","148013    Africa     Zimbabwe  2022-01-26  15092171.0               NaN\n","\n","[148014 rows x 5 columns]"]},"execution_count":18,"metadata":{},"output_type":"execute_result"}],"source":["sql_df"]},{"cell_type":"code","execution_count":null,"metadata":{"id":"ukBMISTzFKYF"},"outputs":[],"source":["sql_df[\"RollingPeopleVaccinated\"] = np.int_(sql_df.groupby(['location']) [\"new_vaccinations\"].transform(\"sum\"))"]},{"cell_type":"code","execution_count":null,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":423},"executionInfo":{"elapsed":169,"status":"ok","timestamp":1645276144040,"user":{"displayName":"James Wolfe","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14GjniFB9tD5BdLBJA15GSIxZ-ItGzbUjXLrJb8IrwQ=s64","userId":"02458012257526617222"},"user_tz":300},"id":"rNzhIhFtGEfa","outputId":"5e49b1bb-00c8-42ea-9b7c-418760bbad00"},"outputs":[{"data":{"text/html":["\n","  <div id=\"df-7cf1a4e2-0274-4bf4-8487-697740d085b1\">\n","    <div class=\"colab-df-container\">\n","      <div>\n","<style scoped>\n","    .dataframe tbody tr th:only-of-type {\n","        vertical-align: middle;\n","    }\n","\n","    .dataframe tbody tr th {\n","        vertical-align: top;\n","    }\n","\n","    .dataframe thead th {\n","        text-align: right;\n","    }\n","</style>\n","<table border=\"1\" class=\"dataframe\">\n","  <thead>\n","    <tr style=\"text-align: right;\">\n","      <th></th>\n","      <th>continent</th>\n","      <th>location</th>\n","      <th>date</th>\n","      <th>population</th>\n","      <th>new_vaccinations</th>\n","      <th>RollingPeopleVaccinated</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>Asia</td>\n","      <td>Afghanistan</td>\n","      <td>2020-02-24</td>\n","      <td>39835428.0</td>\n","      <td>NaN</td>\n","      <td>6874</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>Asia</td>\n","      <td>Afghanistan</td>\n","      <td>2020-02-25</td>\n","      <td>39835428.0</td>\n","      <td>NaN</td>\n","      <td>6874</td>\n","    </tr>\n","    <tr>\n","      <th>2</th>\n","      <td>Asia</td>\n","      <td>Afghanistan</td>\n","      <td>2020-02-26</td>\n","      <td>39835428.0</td>\n","      <td>NaN</td>\n","      <td>6874</td>\n","    </tr>\n","    <tr>\n","      <th>3</th>\n","      <td>Asia</td>\n","      <td>Afghanistan</td>\n","      <td>2020-02-27</td>\n","      <td>39835428.0</td>\n","      <td>NaN</td>\n","      <td>6874</td>\n","    </tr>\n","    <tr>\n","      <th>4</th>\n","      <td>Asia</td>\n","      <td>Afghanistan</td>\n","      <td>2020-02-28</td>\n","      <td>39835428.0</td>\n","      <td>NaN</td>\n","      <td>6874</td>\n","    </tr>\n","    <tr>\n","      <th>...</th>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","    </tr>\n","    <tr>\n","      <th>148009</th>\n","      <td>Africa</td>\n","      <td>Zimbabwe</td>\n","      <td>2022-01-22</td>\n","      <td>15092171.0</td>\n","      <td>9904.0</td>\n","      <td>6696326</td>\n","    </tr>\n","    <tr>\n","      <th>148010</th>\n","      <td>Africa</td>\n","      <td>Zimbabwe</td>\n","      <td>2022-01-23</td>\n","      <td>15092171.0</td>\n","      <td>6117.0</td>\n","      <td>6696326</td>\n","    </tr>\n","    <tr>\n","      <th>148011</th>\n","      <td>Africa</td>\n","      <td>Zimbabwe</td>\n","      <td>2022-01-24</td>\n","      <td>15092171.0</td>\n","      <td>5082.0</td>\n","      <td>6696326</td>\n","    </tr>\n","    <tr>\n","      <th>148012</th>\n","      <td>Africa</td>\n","      <td>Zimbabwe</td>\n","      <td>2022-01-25</td>\n","      <td>15092171.0</td>\n","      <td>7589.0</td>\n","      <td>6696326</td>\n","    </tr>\n","    <tr>\n","      <th>148013</th>\n","      <td>Africa</td>\n","      <td>Zimbabwe</td>\n","      <td>2022-01-26</td>\n","      <td>15092171.0</td>\n","      <td>NaN</td>\n","      <td>6696326</td>\n","    </tr>\n","  </tbody>\n","</table>\n","<p>148014 rows × 6 columns</p>\n","</div>\n","      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-7cf1a4e2-0274-4bf4-8487-697740d085b1')\"\n","              title=\"Convert this dataframe to an interactive table.\"\n","              style=\"display:none;\">\n","        \n","  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n","       width=\"24px\">\n","    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n","    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n","  </svg>\n","      </button>\n","      \n","  <style>\n","    .colab-df-container {\n","      display:flex;\n","      flex-wrap:wrap;\n","      gap: 12px;\n","    }\n","\n","    .colab-df-convert {\n","      background-color: #E8F0FE;\n","      border: none;\n","      border-radius: 50%;\n","      cursor: pointer;\n","      display: none;\n","      fill: #1967D2;\n","      height: 32px;\n","      padding: 0 0 0 0;\n","      width: 32px;\n","    }\n","\n","    .colab-df-convert:hover {\n","      background-color: #E2EBFA;\n","      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n","      fill: #174EA6;\n","    }\n","\n","    [theme=dark] .colab-df-convert {\n","      background-color: #3B4455;\n","      fill: #D2E3FC;\n","    }\n","\n","    [theme=dark] .colab-df-convert:hover {\n","      background-color: #434B5C;\n","      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n","      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n","      fill: #FFFFFF;\n","    }\n","  </style>\n","\n","      <script>\n","        const buttonEl =\n","          document.querySelector('#df-7cf1a4e2-0274-4bf4-8487-697740d085b1 button.colab-df-convert');\n","        buttonEl.style.display =\n","          google.colab.kernel.accessAllowed ? 'block' : 'none';\n","\n","        async function convertToInteractive(key) {\n","          const element = document.querySelector('#df-7cf1a4e2-0274-4bf4-8487-697740d085b1');\n","          const dataTable =\n","            await google.colab.kernel.invokeFunction('convertToInteractive',\n","                                                     [key], {});\n","          if (!dataTable) return;\n","\n","          const docLinkHtml = 'Like what you see? Visit the ' +\n","            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n","            + ' to learn more about interactive tables.';\n","          element.innerHTML = '';\n","          dataTable['output_type'] = 'display_data';\n","          await google.colab.output.renderOutput(dataTable, element);\n","          const docLink = document.createElement('div');\n","          docLink.innerHTML = docLinkHtml;\n","          element.appendChild(docLink);\n","        }\n","      </script>\n","    </div>\n","  </div>\n","  "],"text/plain":["       continent     location  ... new_vaccinations  RollingPeopleVaccinated\n","0           Asia  Afghanistan  ...              NaN                     6874\n","1           Asia  Afghanistan  ...              NaN                     6874\n","2           Asia  Afghanistan  ...              NaN                     6874\n","3           Asia  Afghanistan  ...              NaN                     6874\n","4           Asia  Afghanistan  ...              NaN                     6874\n","...          ...          ...  ...              ...                      ...\n","148009    Africa     Zimbabwe  ...           9904.0                  6696326\n","148010    Africa     Zimbabwe  ...           6117.0                  6696326\n","148011    Africa     Zimbabwe  ...           5082.0                  6696326\n","148012    Africa     Zimbabwe  ...           7589.0                  6696326\n","148013    Africa     Zimbabwe  ...              NaN                  6696326\n","\n","[148014 rows x 6 columns]"]},"execution_count":22,"metadata":{},"output_type":"execute_result"}],"source":["sql_df"]},{"cell_type":"markdown","source":["## That's it folks. While more complex SQL can't be used without using a more sophisticated DBMS like: MySQL, Microsoft SQL, PostgreSQL, etc. I hope this demonstrates the prowlness of SQL."],"metadata":{"id":"brVY5oNKBhH-"}}],"metadata":{"colab":{"collapsed_sections":[],"name":"SQL Data Exploration using Python Notebook for SQLite.ipynb","provenance":[]},"kernelspec":{"display_name":"Python 3 (ipykernel)","language":"python","name":"python3"},"language_info":{"codemirror_mode":{"name":"ipython","version":3},"file_extension":".py","mimetype":"text/x-python","name":"python","nbconvert_exporter":"python","pygments_lexer":"ipython3","version":"3.8.8"}},"nbformat":4,"nbformat_minor":0}