ht_spark_sql.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "spark.ipynb",
      "provenance": [],
      "collapsed_sections": []
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "source": [
        "https://github.com/owid/covid-19-data/blob/master/public/data/README.md"
      ],
      "metadata": {
        "id": "_jFkdeTu-MSu"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "!pip install pyspark\n",
        "from pyspark.sql import SparkSession"
      ],
      "metadata": {
        "id": "u0hjwKXGlpCz",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "b92888d7-6fbd-4503-faf8-f023ae1de8f9"
      },
      "execution_count": 1,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Requirement already satisfied: pyspark in /usr/local/lib/python3.7/dist-packages (3.2.1)\n",
            "Requirement already satisfied: py4j==0.10.9.3 in /usr/local/lib/python3.7/dist-packages (from pyspark) (0.10.9.3)\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "spark=SparkSession.builder.appName('data_processing').getOrCreate()"
      ],
      "metadata": {
        "id": "-nPflrEKvx2Q"
      },
      "execution_count": 2,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "import pyspark.sql.functions as F\n",
        "from pyspark.sql.types import *"
      ],
      "metadata": {
        "id": "yqs5_yFYv0uc"
      },
      "execution_count": 3,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Чтение данных из файла и их анализ                                                       "
      ],
      "metadata": {
        "id": "b2jjcbrGYhdk"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "df=spark.read.csv(\"/content/drive/MyDrive/Netology/owid-covid-data.csv\",header=True,inferSchema=True)"
      ],
      "metadata": {
        "id": "fmuXTZ1WTvUz"
      },
      "execution_count": 4,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        " # посмотрим схему \n",
        " df.printSchema()"
      ],
      "metadata": {
        "id": "fTUZGGMCTvec",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "63e1716a-32c9-4306-f590-33a150d249f4"
      },
      "execution_count": 5,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "root\n",
            " |-- iso_code: string (nullable = true)\n",
            " |-- continent: string (nullable = true)\n",
            " |-- location: string (nullable = true)\n",
            " |-- date: string (nullable = true)\n",
            " |-- total_cases: double (nullable = true)\n",
            " |-- new_cases: double (nullable = true)\n",
            " |-- new_cases_smoothed: double (nullable = true)\n",
            " |-- total_deaths: double (nullable = true)\n",
            " |-- new_deaths: double (nullable = true)\n",
            " |-- new_deaths_smoothed: double (nullable = true)\n",
            " |-- total_cases_per_million: double (nullable = true)\n",
            " |-- new_cases_per_million: double (nullable = true)\n",
            " |-- new_cases_smoothed_per_million: double (nullable = true)\n",
            " |-- total_deaths_per_million: double (nullable = true)\n",
            " |-- new_deaths_per_million: double (nullable = true)\n",
            " |-- new_deaths_smoothed_per_million: double (nullable = true)\n",
            " |-- reproduction_rate: double (nullable = true)\n",
            " |-- icu_patients: double (nullable = true)\n",
            " |-- icu_patients_per_million: double (nullable = true)\n",
            " |-- hosp_patients: double (nullable = true)\n",
            " |-- hosp_patients_per_million: double (nullable = true)\n",
            " |-- weekly_icu_admissions: double (nullable = true)\n",
            " |-- weekly_icu_admissions_per_million: double (nullable = true)\n",
            " |-- weekly_hosp_admissions: double (nullable = true)\n",
            " |-- weekly_hosp_admissions_per_million: double (nullable = true)\n",
            " |-- new_tests: double (nullable = true)\n",
            " |-- total_tests: double (nullable = true)\n",
            " |-- total_tests_per_thousand: double (nullable = true)\n",
            " |-- new_tests_per_thousand: double (nullable = true)\n",
            " |-- new_tests_smoothed: double (nullable = true)\n",
            " |-- new_tests_smoothed_per_thousand: double (nullable = true)\n",
            " |-- positive_rate: double (nullable = true)\n",
            " |-- tests_per_case: double (nullable = true)\n",
            " |-- tests_units: string (nullable = true)\n",
            " |-- total_vaccinations: double (nullable = true)\n",
            " |-- people_vaccinated: double (nullable = true)\n",
            " |-- people_fully_vaccinated: double (nullable = true)\n",
            " |-- new_vaccinations: double (nullable = true)\n",
            " |-- new_vaccinations_smoothed: double (nullable = true)\n",
            " |-- total_vaccinations_per_hundred: double (nullable = true)\n",
            " |-- people_vaccinated_per_hundred: double (nullable = true)\n",
            " |-- people_fully_vaccinated_per_hundred: double (nullable = true)\n",
            " |-- new_vaccinations_smoothed_per_million: double (nullable = true)\n",
            " |-- stringency_index: double (nullable = true)\n",
            " |-- population: double (nullable = true)\n",
            " |-- population_density: double (nullable = true)\n",
            " |-- median_age: double (nullable = true)\n",
            " |-- aged_65_older: double (nullable = true)\n",
            " |-- aged_70_older: double (nullable = true)\n",
            " |-- gdp_per_capita: double (nullable = true)\n",
            " |-- extreme_poverty: double (nullable = true)\n",
            " |-- cardiovasc_death_rate: double (nullable = true)\n",
            " |-- diabetes_prevalence: double (nullable = true)\n",
            " |-- female_smokers: double (nullable = true)\n",
            " |-- male_smokers: double (nullable = true)\n",
            " |-- handwashing_facilities: double (nullable = true)\n",
            " |-- hospital_beds_per_thousand: double (nullable = true)\n",
            " |-- life_expectancy: double (nullable = true)\n",
            " |-- human_development_index: double (nullable = true)\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "df.show(15)"
      ],
      "metadata": {
        "id": "QbSUxktYwZ8N",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "6cdafdbc-821d-446e-c529-8260e78fb180"
      },
      "execution_count": 6,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------+---------+-----------+----------+-----------+---------+------------------+------------+----------+-------------------+-----------------------+---------------------+------------------------------+------------------------+----------------------+-------------------------------+-----------------+------------+------------------------+-------------+-------------------------+---------------------+---------------------------------+----------------------+----------------------------------+---------+-----------+------------------------+----------------------+------------------+-------------------------------+-------------+--------------+-----------+------------------+-----------------+-----------------------+----------------+-------------------------+------------------------------+-----------------------------+-----------------------------------+-------------------------------------+----------------+-----------+------------------+----------+-------------+-------------+--------------+---------------+---------------------+-------------------+--------------+------------+----------------------+--------------------------+---------------+-----------------------+\n",
            "|iso_code|continent|   location|      date|total_cases|new_cases|new_cases_smoothed|total_deaths|new_deaths|new_deaths_smoothed|total_cases_per_million|new_cases_per_million|new_cases_smoothed_per_million|total_deaths_per_million|new_deaths_per_million|new_deaths_smoothed_per_million|reproduction_rate|icu_patients|icu_patients_per_million|hosp_patients|hosp_patients_per_million|weekly_icu_admissions|weekly_icu_admissions_per_million|weekly_hosp_admissions|weekly_hosp_admissions_per_million|new_tests|total_tests|total_tests_per_thousand|new_tests_per_thousand|new_tests_smoothed|new_tests_smoothed_per_thousand|positive_rate|tests_per_case|tests_units|total_vaccinations|people_vaccinated|people_fully_vaccinated|new_vaccinations|new_vaccinations_smoothed|total_vaccinations_per_hundred|people_vaccinated_per_hundred|people_fully_vaccinated_per_hundred|new_vaccinations_smoothed_per_million|stringency_index| population|population_density|median_age|aged_65_older|aged_70_older|gdp_per_capita|extreme_poverty|cardiovasc_death_rate|diabetes_prevalence|female_smokers|male_smokers|handwashing_facilities|hospital_beds_per_thousand|life_expectancy|human_development_index|\n",
            "+--------+---------+-----------+----------+-----------+---------+------------------+------------+----------+-------------------+-----------------------+---------------------+------------------------------+------------------------+----------------------+-------------------------------+-----------------+------------+------------------------+-------------+-------------------------+---------------------+---------------------------------+----------------------+----------------------------------+---------+-----------+------------------------+----------------------+------------------+-------------------------------+-------------+--------------+-----------+------------------+-----------------+-----------------------+----------------+-------------------------+------------------------------+-----------------------------+-----------------------------------+-------------------------------------+----------------+-----------+------------------+----------+-------------+-------------+--------------+---------------+---------------------+-------------------+--------------+------------+----------------------+--------------------------+---------------+-----------------------+\n",
            "|     AFG|     Asia|Afghanistan|2020-02-24|        1.0|      1.0|              null|        null|      null|               null|                  0.026|                0.026|                          null|                    null|                  null|                           null|             null|        null|                    null|         null|                     null|                 null|                             null|                  null|                              null|     null|       null|                    null|                  null|              null|                           null|         null|          null|       null|              null|             null|                   null|            null|                     null|                          null|                         null|                               null|                                 null|            8.33|3.8928341E7|            54.422|      18.6|        2.581|        1.337|      1803.987|           null|              597.029|               9.59|          null|        null|                37.746|                       0.5|          64.83|                  0.511|\n",
            "|     AFG|     Asia|Afghanistan|2020-02-25|        1.0|      0.0|              null|        null|      null|               null|                  0.026|                  0.0|                          null|                    null|                  null|                           null|             null|        null|                    null|         null|                     null|                 null|                             null|                  null|                              null|     null|       null|                    null|                  null|              null|                           null|         null|          null|       null|              null|             null|                   null|            null|                     null|                          null|                         null|                               null|                                 null|            8.33|3.8928341E7|            54.422|      18.6|        2.581|        1.337|      1803.987|           null|              597.029|               9.59|          null|        null|                37.746|                       0.5|          64.83|                  0.511|\n",
            "|     AFG|     Asia|Afghanistan|2020-02-26|        1.0|      0.0|              null|        null|      null|               null|                  0.026|                  0.0|                          null|                    null|                  null|                           null|             null|        null|                    null|         null|                     null|                 null|                             null|                  null|                              null|     null|       null|                    null|                  null|              null|                           null|         null|          null|       null|              null|             null|                   null|            null|                     null|                          null|                         null|                               null|                                 null|            8.33|3.8928341E7|            54.422|      18.6|        2.581|        1.337|      1803.987|           null|              597.029|               9.59|          null|        null|                37.746|                       0.5|          64.83|                  0.511|\n",
            "|     AFG|     Asia|Afghanistan|2020-02-27|        1.0|      0.0|              null|        null|      null|               null|                  0.026|                  0.0|                          null|                    null|                  null|                           null|             null|        null|                    null|         null|                     null|                 null|                             null|                  null|                              null|     null|       null|                    null|                  null|              null|                           null|         null|          null|       null|              null|             null|                   null|            null|                     null|                          null|                         null|                               null|                                 null|            8.33|3.8928341E7|            54.422|      18.6|        2.581|        1.337|      1803.987|           null|              597.029|               9.59|          null|        null|                37.746|                       0.5|          64.83|                  0.511|\n",
            "|     AFG|     Asia|Afghanistan|2020-02-28|        1.0|      0.0|              null|        null|      null|               null|                  0.026|                  0.0|                          null|                    null|                  null|                           null|             null|        null|                    null|         null|                     null|                 null|                             null|                  null|                              null|     null|       null|                    null|                  null|              null|                           null|         null|          null|       null|              null|             null|                   null|            null|                     null|                          null|                         null|                               null|                                 null|            8.33|3.8928341E7|            54.422|      18.6|        2.581|        1.337|      1803.987|           null|              597.029|               9.59|          null|        null|                37.746|                       0.5|          64.83|                  0.511|\n",
            "|     AFG|     Asia|Afghanistan|2020-02-29|        1.0|      0.0|             0.143|        null|      null|                0.0|                  0.026|                  0.0|                         0.004|                    null|                  null|                            0.0|             null|        null|                    null|         null|                     null|                 null|                             null|                  null|                              null|     null|       null|                    null|                  null|              null|                           null|         null|          null|       null|              null|             null|                   null|            null|                     null|                          null|                         null|                               null|                                 null|            8.33|3.8928341E7|            54.422|      18.6|        2.581|        1.337|      1803.987|           null|              597.029|               9.59|          null|        null|                37.746|                       0.5|          64.83|                  0.511|\n",
            "|     AFG|     Asia|Afghanistan|2020-03-01|        1.0|      0.0|             0.143|        null|      null|                0.0|                  0.026|                  0.0|                         0.004|                    null|                  null|                            0.0|             null|        null|                    null|         null|                     null|                 null|                             null|                  null|                              null|     null|       null|                    null|                  null|              null|                           null|         null|          null|       null|              null|             null|                   null|            null|                     null|                          null|                         null|                               null|                                 null|           27.78|3.8928341E7|            54.422|      18.6|        2.581|        1.337|      1803.987|           null|              597.029|               9.59|          null|        null|                37.746|                       0.5|          64.83|                  0.511|\n",
            "|     AFG|     Asia|Afghanistan|2020-03-02|        1.0|      0.0|               0.0|        null|      null|                0.0|                  0.026|                  0.0|                           0.0|                    null|                  null|                            0.0|             null|        null|                    null|         null|                     null|                 null|                             null|                  null|                              null|     null|       null|                    null|                  null|              null|                           null|         null|          null|       null|              null|             null|                   null|            null|                     null|                          null|                         null|                               null|                                 null|           27.78|3.8928341E7|            54.422|      18.6|        2.581|        1.337|      1803.987|           null|              597.029|               9.59|          null|        null|                37.746|                       0.5|          64.83|                  0.511|\n",
            "|     AFG|     Asia|Afghanistan|2020-03-03|        2.0|      1.0|             0.143|        null|      null|                0.0|                  0.051|                0.026|                         0.004|                    null|                  null|                            0.0|             null|        null|                    null|         null|                     null|                 null|                             null|                  null|                              null|     null|       null|                    null|                  null|              null|                           null|         null|          null|       null|              null|             null|                   null|            null|                     null|                          null|                         null|                               null|                                 null|           27.78|3.8928341E7|            54.422|      18.6|        2.581|        1.337|      1803.987|           null|              597.029|               9.59|          null|        null|                37.746|                       0.5|          64.83|                  0.511|\n",
            "|     AFG|     Asia|Afghanistan|2020-03-04|        4.0|      2.0|             0.429|        null|      null|                0.0|                  0.103|                0.051|                         0.011|                    null|                  null|                            0.0|             null|        null|                    null|         null|                     null|                 null|                             null|                  null|                              null|     null|       null|                    null|                  null|              null|                           null|         null|          null|       null|              null|             null|                   null|            null|                     null|                          null|                         null|                               null|                                 null|           27.78|3.8928341E7|            54.422|      18.6|        2.581|        1.337|      1803.987|           null|              597.029|               9.59|          null|        null|                37.746|                       0.5|          64.83|                  0.511|\n",
            "|     AFG|     Asia|Afghanistan|2020-03-05|        4.0|      0.0|             0.429|        null|      null|                0.0|                  0.103|                  0.0|                         0.011|                    null|                  null|                            0.0|             null|        null|                    null|         null|                     null|                 null|                             null|                  null|                              null|     null|       null|                    null|                  null|              null|                           null|         null|          null|       null|              null|             null|                   null|            null|                     null|                          null|                         null|                               null|                                 null|           27.78|3.8928341E7|            54.422|      18.6|        2.581|        1.337|      1803.987|           null|              597.029|               9.59|          null|        null|                37.746|                       0.5|          64.83|                  0.511|\n",
            "|     AFG|     Asia|Afghanistan|2020-03-06|        4.0|      0.0|             0.429|        null|      null|                0.0|                  0.103|                  0.0|                         0.011|                    null|                  null|                            0.0|             null|        null|                    null|         null|                     null|                 null|                             null|                  null|                              null|     null|       null|                    null|                  null|              null|                           null|         null|          null|       null|              null|             null|                   null|            null|                     null|                          null|                         null|                               null|                                 null|           27.78|3.8928341E7|            54.422|      18.6|        2.581|        1.337|      1803.987|           null|              597.029|               9.59|          null|        null|                37.746|                       0.5|          64.83|                  0.511|\n",
            "|     AFG|     Asia|Afghanistan|2020-03-07|        4.0|      0.0|             0.429|        null|      null|                0.0|                  0.103|                  0.0|                         0.011|                    null|                  null|                            0.0|             null|        null|                    null|         null|                     null|                 null|                             null|                  null|                              null|     null|       null|                    null|                  null|              null|                           null|         null|          null|       null|              null|             null|                   null|            null|                     null|                          null|                         null|                               null|                                 null|           27.78|3.8928341E7|            54.422|      18.6|        2.581|        1.337|      1803.987|           null|              597.029|               9.59|          null|        null|                37.746|                       0.5|          64.83|                  0.511|\n",
            "|     AFG|     Asia|Afghanistan|2020-03-08|        5.0|      1.0|             0.571|        null|      null|                0.0|                  0.128|                0.026|                         0.015|                    null|                  null|                            0.0|             null|        null|                    null|         null|                     null|                 null|                             null|                  null|                              null|     null|       null|                    null|                  null|              null|                           null|         null|          null|       null|              null|             null|                   null|            null|                     null|                          null|                         null|                               null|                                 null|           27.78|3.8928341E7|            54.422|      18.6|        2.581|        1.337|      1803.987|           null|              597.029|               9.59|          null|        null|                37.746|                       0.5|          64.83|                  0.511|\n",
            "|     AFG|     Asia|Afghanistan|2020-03-09|        7.0|      2.0|             0.857|        null|      null|                0.0|                   0.18|                0.051|                         0.022|                    null|                  null|                            0.0|             null|        null|                    null|         null|                     null|                 null|                             null|                  null|                              null|     null|       null|                    null|                  null|              null|                           null|         null|          null|       null|              null|             null|                   null|            null|                     null|                          null|                         null|                               null|                                 null|           27.78|3.8928341E7|            54.422|      18.6|        2.581|        1.337|      1803.987|           null|              597.029|               9.59|          null|        null|                37.746|                       0.5|          64.83|                  0.511|\n",
            "+--------+---------+-----------+----------+-----------+---------+------------------+------------+----------+-------------------+-----------------------+---------------------+------------------------------+------------------------+----------------------+-------------------------------+-----------------+------------+------------------------+-------------+-------------------------+---------------------+---------------------------------+----------------------+----------------------------------+---------+-----------+------------------------+----------------------+------------------+-------------------------------+-------------+--------------+-----------+------------------+-----------------+-----------------------+----------------+-------------------------+------------------------------+-----------------------------+-----------------------------------+-------------------------------------+----------------+-----------+------------------+----------+-------------+-------------+--------------+---------------+---------------------+-------------------+--------------+------------+----------------------+--------------------------+---------------+-----------------------+\n",
            "only showing top 15 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Очистка данных"
      ],
      "metadata": {
        "id": "ni1ASQQ1ZYuY"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Удаляем все строки с OWID, так как это, по всей видимости, агрегированные данные по регионам\n",
        "df_clean = df.filter(~df['iso_code'].startswith('OWID'))\n",
        "\n",
        "# изменяем null на 0\n",
        "df_clean = df_clean.fillna({'total_cases': 0, 'new_cases': 0})\n",
        "df_clean.show(5)"
      ],
      "metadata": {
        "id": "u-F5SS-SUiMp",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "9047b538-de45-4554-a9f6-03bce6b5a4ad"
      },
      "execution_count": 7,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------+---------+-----------+----------+-----------+---------+------------------+------------+----------+-------------------+-----------------------+---------------------+------------------------------+------------------------+----------------------+-------------------------------+-----------------+------------+------------------------+-------------+-------------------------+---------------------+---------------------------------+----------------------+----------------------------------+---------+-----------+------------------------+----------------------+------------------+-------------------------------+-------------+--------------+-----------+------------------+-----------------+-----------------------+----------------+-------------------------+------------------------------+-----------------------------+-----------------------------------+-------------------------------------+----------------+-----------+------------------+----------+-------------+-------------+--------------+---------------+---------------------+-------------------+--------------+------------+----------------------+--------------------------+---------------+-----------------------+\n",
            "|iso_code|continent|   location|      date|total_cases|new_cases|new_cases_smoothed|total_deaths|new_deaths|new_deaths_smoothed|total_cases_per_million|new_cases_per_million|new_cases_smoothed_per_million|total_deaths_per_million|new_deaths_per_million|new_deaths_smoothed_per_million|reproduction_rate|icu_patients|icu_patients_per_million|hosp_patients|hosp_patients_per_million|weekly_icu_admissions|weekly_icu_admissions_per_million|weekly_hosp_admissions|weekly_hosp_admissions_per_million|new_tests|total_tests|total_tests_per_thousand|new_tests_per_thousand|new_tests_smoothed|new_tests_smoothed_per_thousand|positive_rate|tests_per_case|tests_units|total_vaccinations|people_vaccinated|people_fully_vaccinated|new_vaccinations|new_vaccinations_smoothed|total_vaccinations_per_hundred|people_vaccinated_per_hundred|people_fully_vaccinated_per_hundred|new_vaccinations_smoothed_per_million|stringency_index| population|population_density|median_age|aged_65_older|aged_70_older|gdp_per_capita|extreme_poverty|cardiovasc_death_rate|diabetes_prevalence|female_smokers|male_smokers|handwashing_facilities|hospital_beds_per_thousand|life_expectancy|human_development_index|\n",
            "+--------+---------+-----------+----------+-----------+---------+------------------+------------+----------+-------------------+-----------------------+---------------------+------------------------------+------------------------+----------------------+-------------------------------+-----------------+------------+------------------------+-------------+-------------------------+---------------------+---------------------------------+----------------------+----------------------------------+---------+-----------+------------------------+----------------------+------------------+-------------------------------+-------------+--------------+-----------+------------------+-----------------+-----------------------+----------------+-------------------------+------------------------------+-----------------------------+-----------------------------------+-------------------------------------+----------------+-----------+------------------+----------+-------------+-------------+--------------+---------------+---------------------+-------------------+--------------+------------+----------------------+--------------------------+---------------+-----------------------+\n",
            "|     AFG|     Asia|Afghanistan|2020-02-24|        1.0|      1.0|              null|        null|      null|               null|                  0.026|                0.026|                          null|                    null|                  null|                           null|             null|        null|                    null|         null|                     null|                 null|                             null|                  null|                              null|     null|       null|                    null|                  null|              null|                           null|         null|          null|       null|              null|             null|                   null|            null|                     null|                          null|                         null|                               null|                                 null|            8.33|3.8928341E7|            54.422|      18.6|        2.581|        1.337|      1803.987|           null|              597.029|               9.59|          null|        null|                37.746|                       0.5|          64.83|                  0.511|\n",
            "|     AFG|     Asia|Afghanistan|2020-02-25|        1.0|      0.0|              null|        null|      null|               null|                  0.026|                  0.0|                          null|                    null|                  null|                           null|             null|        null|                    null|         null|                     null|                 null|                             null|                  null|                              null|     null|       null|                    null|                  null|              null|                           null|         null|          null|       null|              null|             null|                   null|            null|                     null|                          null|                         null|                               null|                                 null|            8.33|3.8928341E7|            54.422|      18.6|        2.581|        1.337|      1803.987|           null|              597.029|               9.59|          null|        null|                37.746|                       0.5|          64.83|                  0.511|\n",
            "|     AFG|     Asia|Afghanistan|2020-02-26|        1.0|      0.0|              null|        null|      null|               null|                  0.026|                  0.0|                          null|                    null|                  null|                           null|             null|        null|                    null|         null|                     null|                 null|                             null|                  null|                              null|     null|       null|                    null|                  null|              null|                           null|         null|          null|       null|              null|             null|                   null|            null|                     null|                          null|                         null|                               null|                                 null|            8.33|3.8928341E7|            54.422|      18.6|        2.581|        1.337|      1803.987|           null|              597.029|               9.59|          null|        null|                37.746|                       0.5|          64.83|                  0.511|\n",
            "|     AFG|     Asia|Afghanistan|2020-02-27|        1.0|      0.0|              null|        null|      null|               null|                  0.026|                  0.0|                          null|                    null|                  null|                           null|             null|        null|                    null|         null|                     null|                 null|                             null|                  null|                              null|     null|       null|                    null|                  null|              null|                           null|         null|          null|       null|              null|             null|                   null|            null|                     null|                          null|                         null|                               null|                                 null|            8.33|3.8928341E7|            54.422|      18.6|        2.581|        1.337|      1803.987|           null|              597.029|               9.59|          null|        null|                37.746|                       0.5|          64.83|                  0.511|\n",
            "|     AFG|     Asia|Afghanistan|2020-02-28|        1.0|      0.0|              null|        null|      null|               null|                  0.026|                  0.0|                          null|                    null|                  null|                           null|             null|        null|                    null|         null|                     null|                 null|                             null|                  null|                              null|     null|       null|                    null|                  null|              null|                           null|         null|          null|       null|              null|             null|                   null|            null|                     null|                          null|                         null|                               null|                                 null|            8.33|3.8928341E7|            54.422|      18.6|        2.581|        1.337|      1803.987|           null|              597.029|               9.59|          null|        null|                37.746|                       0.5|          64.83|                  0.511|\n",
            "+--------+---------+-----------+----------+-----------+---------+------------------+------------+----------+-------------------+-----------------------+---------------------+------------------------------+------------------------+----------------------+-------------------------------+-----------------+------------+------------------------+-------------+-------------------------+---------------------+---------------------------------+----------------------+----------------------------------+---------+-----------+------------------------+----------------------+------------------+-------------------------------+-------------+--------------+-----------+------------------+-----------------+-----------------------+----------------+-------------------------+------------------------------+-----------------------------+-----------------------------------+-------------------------------------+----------------+-----------+------------------+----------+-------------+-------------+--------------+---------------+---------------------+-------------------+--------------+------------+----------------------+--------------------------+---------------+-----------------------+\n",
            "only showing top 5 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Задние 1. Выберите 15 стран с наибольшим процентом переболевших на 31 марта (в выходящем датасете необходимы колонки: iso_code, страна, процент переболевших) "
      ],
      "metadata": {
        "id": "FLdfmM87RSQM"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Считаем процент заболевших относительно популяции\n",
        "df_clean = df_clean.withColumn(\"recover_per_population\", 100*(df_clean['total_cases'] - df_clean['total_deaths']) /df_clean['population']) \n",
        "df_clean.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "efqhccCCzKoS",
        "outputId": "0ad0ddd4-2cd8-4a0d-fe72-5847770980d9"
      },
      "execution_count": 8,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------+---------+-----------+----------+-----------+---------+------------------+------------+----------+-------------------+-----------------------+---------------------+------------------------------+------------------------+----------------------+-------------------------------+-----------------+------------+------------------------+-------------+-------------------------+---------------------+---------------------------------+----------------------+----------------------------------+---------+-----------+------------------------+----------------------+------------------+-------------------------------+-------------+--------------+-----------+------------------+-----------------+-----------------------+----------------+-------------------------+------------------------------+-----------------------------+-----------------------------------+-------------------------------------+----------------+-----------+------------------+----------+-------------+-------------+--------------+---------------+---------------------+-------------------+--------------+------------+----------------------+--------------------------+---------------+-----------------------+----------------------+\n",
            "|iso_code|continent|   location|      date|total_cases|new_cases|new_cases_smoothed|total_deaths|new_deaths|new_deaths_smoothed|total_cases_per_million|new_cases_per_million|new_cases_smoothed_per_million|total_deaths_per_million|new_deaths_per_million|new_deaths_smoothed_per_million|reproduction_rate|icu_patients|icu_patients_per_million|hosp_patients|hosp_patients_per_million|weekly_icu_admissions|weekly_icu_admissions_per_million|weekly_hosp_admissions|weekly_hosp_admissions_per_million|new_tests|total_tests|total_tests_per_thousand|new_tests_per_thousand|new_tests_smoothed|new_tests_smoothed_per_thousand|positive_rate|tests_per_case|tests_units|total_vaccinations|people_vaccinated|people_fully_vaccinated|new_vaccinations|new_vaccinations_smoothed|total_vaccinations_per_hundred|people_vaccinated_per_hundred|people_fully_vaccinated_per_hundred|new_vaccinations_smoothed_per_million|stringency_index| population|population_density|median_age|aged_65_older|aged_70_older|gdp_per_capita|extreme_poverty|cardiovasc_death_rate|diabetes_prevalence|female_smokers|male_smokers|handwashing_facilities|hospital_beds_per_thousand|life_expectancy|human_development_index|recover_per_population|\n",
            "+--------+---------+-----------+----------+-----------+---------+------------------+------------+----------+-------------------+-----------------------+---------------------+------------------------------+------------------------+----------------------+-------------------------------+-----------------+------------+------------------------+-------------+-------------------------+---------------------+---------------------------------+----------------------+----------------------------------+---------+-----------+------------------------+----------------------+------------------+-------------------------------+-------------+--------------+-----------+------------------+-----------------+-----------------------+----------------+-------------------------+------------------------------+-----------------------------+-----------------------------------+-------------------------------------+----------------+-----------+------------------+----------+-------------+-------------+--------------+---------------+---------------------+-------------------+--------------+------------+----------------------+--------------------------+---------------+-----------------------+----------------------+\n",
            "|     AFG|     Asia|Afghanistan|2020-02-24|        1.0|      1.0|              null|        null|      null|               null|                  0.026|                0.026|                          null|                    null|                  null|                           null|             null|        null|                    null|         null|                     null|                 null|                             null|                  null|                              null|     null|       null|                    null|                  null|              null|                           null|         null|          null|       null|              null|             null|                   null|            null|                     null|                          null|                         null|                               null|                                 null|            8.33|3.8928341E7|            54.422|      18.6|        2.581|        1.337|      1803.987|           null|              597.029|               9.59|          null|        null|                37.746|                       0.5|          64.83|                  0.511|                  null|\n",
            "|     AFG|     Asia|Afghanistan|2020-02-25|        1.0|      0.0|              null|        null|      null|               null|                  0.026|                  0.0|                          null|                    null|                  null|                           null|             null|        null|                    null|         null|                     null|                 null|                             null|                  null|                              null|     null|       null|                    null|                  null|              null|                           null|         null|          null|       null|              null|             null|                   null|            null|                     null|                          null|                         null|                               null|                                 null|            8.33|3.8928341E7|            54.422|      18.6|        2.581|        1.337|      1803.987|           null|              597.029|               9.59|          null|        null|                37.746|                       0.5|          64.83|                  0.511|                  null|\n",
            "|     AFG|     Asia|Afghanistan|2020-02-26|        1.0|      0.0|              null|        null|      null|               null|                  0.026|                  0.0|                          null|                    null|                  null|                           null|             null|        null|                    null|         null|                     null|                 null|                             null|                  null|                              null|     null|       null|                    null|                  null|              null|                           null|         null|          null|       null|              null|             null|                   null|            null|                     null|                          null|                         null|                               null|                                 null|            8.33|3.8928341E7|            54.422|      18.6|        2.581|        1.337|      1803.987|           null|              597.029|               9.59|          null|        null|                37.746|                       0.5|          64.83|                  0.511|                  null|\n",
            "|     AFG|     Asia|Afghanistan|2020-02-27|        1.0|      0.0|              null|        null|      null|               null|                  0.026|                  0.0|                          null|                    null|                  null|                           null|             null|        null|                    null|         null|                     null|                 null|                             null|                  null|                              null|     null|       null|                    null|                  null|              null|                           null|         null|          null|       null|              null|             null|                   null|            null|                     null|                          null|                         null|                               null|                                 null|            8.33|3.8928341E7|            54.422|      18.6|        2.581|        1.337|      1803.987|           null|              597.029|               9.59|          null|        null|                37.746|                       0.5|          64.83|                  0.511|                  null|\n",
            "|     AFG|     Asia|Afghanistan|2020-02-28|        1.0|      0.0|              null|        null|      null|               null|                  0.026|                  0.0|                          null|                    null|                  null|                           null|             null|        null|                    null|         null|                     null|                 null|                             null|                  null|                              null|     null|       null|                    null|                  null|              null|                           null|         null|          null|       null|              null|             null|                   null|            null|                     null|                          null|                         null|                               null|                                 null|            8.33|3.8928341E7|            54.422|      18.6|        2.581|        1.337|      1803.987|           null|              597.029|               9.59|          null|        null|                37.746|                       0.5|          64.83|                  0.511|                  null|\n",
            "|     AFG|     Asia|Afghanistan|2020-02-29|        1.0|      0.0|             0.143|        null|      null|                0.0|                  0.026|                  0.0|                         0.004|                    null|                  null|                            0.0|             null|        null|                    null|         null|                     null|                 null|                             null|                  null|                              null|     null|       null|                    null|                  null|              null|                           null|         null|          null|       null|              null|             null|                   null|            null|                     null|                          null|                         null|                               null|                                 null|            8.33|3.8928341E7|            54.422|      18.6|        2.581|        1.337|      1803.987|           null|              597.029|               9.59|          null|        null|                37.746|                       0.5|          64.83|                  0.511|                  null|\n",
            "|     AFG|     Asia|Afghanistan|2020-03-01|        1.0|      0.0|             0.143|        null|      null|                0.0|                  0.026|                  0.0|                         0.004|                    null|                  null|                            0.0|             null|        null|                    null|         null|                     null|                 null|                             null|                  null|                              null|     null|       null|                    null|                  null|              null|                           null|         null|          null|       null|              null|             null|                   null|            null|                     null|                          null|                         null|                               null|                                 null|           27.78|3.8928341E7|            54.422|      18.6|        2.581|        1.337|      1803.987|           null|              597.029|               9.59|          null|        null|                37.746|                       0.5|          64.83|                  0.511|                  null|\n",
            "|     AFG|     Asia|Afghanistan|2020-03-02|        1.0|      0.0|               0.0|        null|      null|                0.0|                  0.026|                  0.0|                           0.0|                    null|                  null|                            0.0|             null|        null|                    null|         null|                     null|                 null|                             null|                  null|                              null|     null|       null|                    null|                  null|              null|                           null|         null|          null|       null|              null|             null|                   null|            null|                     null|                          null|                         null|                               null|                                 null|           27.78|3.8928341E7|            54.422|      18.6|        2.581|        1.337|      1803.987|           null|              597.029|               9.59|          null|        null|                37.746|                       0.5|          64.83|                  0.511|                  null|\n",
            "|     AFG|     Asia|Afghanistan|2020-03-03|        2.0|      1.0|             0.143|        null|      null|                0.0|                  0.051|                0.026|                         0.004|                    null|                  null|                            0.0|             null|        null|                    null|         null|                     null|                 null|                             null|                  null|                              null|     null|       null|                    null|                  null|              null|                           null|         null|          null|       null|              null|             null|                   null|            null|                     null|                          null|                         null|                               null|                                 null|           27.78|3.8928341E7|            54.422|      18.6|        2.581|        1.337|      1803.987|           null|              597.029|               9.59|          null|        null|                37.746|                       0.5|          64.83|                  0.511|                  null|\n",
            "|     AFG|     Asia|Afghanistan|2020-03-04|        4.0|      2.0|             0.429|        null|      null|                0.0|                  0.103|                0.051|                         0.011|                    null|                  null|                            0.0|             null|        null|                    null|         null|                     null|                 null|                             null|                  null|                              null|     null|       null|                    null|                  null|              null|                           null|         null|          null|       null|              null|             null|                   null|            null|                     null|                          null|                         null|                               null|                                 null|           27.78|3.8928341E7|            54.422|      18.6|        2.581|        1.337|      1803.987|           null|              597.029|               9.59|          null|        null|                37.746|                       0.5|          64.83|                  0.511|                  null|\n",
            "|     AFG|     Asia|Afghanistan|2020-03-05|        4.0|      0.0|             0.429|        null|      null|                0.0|                  0.103|                  0.0|                         0.011|                    null|                  null|                            0.0|             null|        null|                    null|         null|                     null|                 null|                             null|                  null|                              null|     null|       null|                    null|                  null|              null|                           null|         null|          null|       null|              null|             null|                   null|            null|                     null|                          null|                         null|                               null|                                 null|           27.78|3.8928341E7|            54.422|      18.6|        2.581|        1.337|      1803.987|           null|              597.029|               9.59|          null|        null|                37.746|                       0.5|          64.83|                  0.511|                  null|\n",
            "|     AFG|     Asia|Afghanistan|2020-03-06|        4.0|      0.0|             0.429|        null|      null|                0.0|                  0.103|                  0.0|                         0.011|                    null|                  null|                            0.0|             null|        null|                    null|         null|                     null|                 null|                             null|                  null|                              null|     null|       null|                    null|                  null|              null|                           null|         null|          null|       null|              null|             null|                   null|            null|                     null|                          null|                         null|                               null|                                 null|           27.78|3.8928341E7|            54.422|      18.6|        2.581|        1.337|      1803.987|           null|              597.029|               9.59|          null|        null|                37.746|                       0.5|          64.83|                  0.511|                  null|\n",
            "|     AFG|     Asia|Afghanistan|2020-03-07|        4.0|      0.0|             0.429|        null|      null|                0.0|                  0.103|                  0.0|                         0.011|                    null|                  null|                            0.0|             null|        null|                    null|         null|                     null|                 null|                             null|                  null|                              null|     null|       null|                    null|                  null|              null|                           null|         null|          null|       null|              null|             null|                   null|            null|                     null|                          null|                         null|                               null|                                 null|           27.78|3.8928341E7|            54.422|      18.6|        2.581|        1.337|      1803.987|           null|              597.029|               9.59|          null|        null|                37.746|                       0.5|          64.83|                  0.511|                  null|\n",
            "|     AFG|     Asia|Afghanistan|2020-03-08|        5.0|      1.0|             0.571|        null|      null|                0.0|                  0.128|                0.026|                         0.015|                    null|                  null|                            0.0|             null|        null|                    null|         null|                     null|                 null|                             null|                  null|                              null|     null|       null|                    null|                  null|              null|                           null|         null|          null|       null|              null|             null|                   null|            null|                     null|                          null|                         null|                               null|                                 null|           27.78|3.8928341E7|            54.422|      18.6|        2.581|        1.337|      1803.987|           null|              597.029|               9.59|          null|        null|                37.746|                       0.5|          64.83|                  0.511|                  null|\n",
            "|     AFG|     Asia|Afghanistan|2020-03-09|        7.0|      2.0|             0.857|        null|      null|                0.0|                   0.18|                0.051|                         0.022|                    null|                  null|                            0.0|             null|        null|                    null|         null|                     null|                 null|                             null|                  null|                              null|     null|       null|                    null|                  null|              null|                           null|         null|          null|       null|              null|             null|                   null|            null|                     null|                          null|                         null|                               null|                                 null|           27.78|3.8928341E7|            54.422|      18.6|        2.581|        1.337|      1803.987|           null|              597.029|               9.59|          null|        null|                37.746|                       0.5|          64.83|                  0.511|                  null|\n",
            "|     AFG|     Asia|Afghanistan|2020-03-10|        8.0|      1.0|             0.857|        null|      null|                0.0|                  0.206|                0.026|                         0.022|                    null|                  null|                            0.0|             null|        null|                    null|         null|                     null|                 null|                             null|                  null|                              null|     null|       null|                    null|                  null|              null|                           null|         null|          null|       null|              null|             null|                   null|            null|                     null|                          null|                         null|                               null|                                 null|           27.78|3.8928341E7|            54.422|      18.6|        2.581|        1.337|      1803.987|           null|              597.029|               9.59|          null|        null|                37.746|                       0.5|          64.83|                  0.511|                  null|\n",
            "|     AFG|     Asia|Afghanistan|2020-03-11|       11.0|      3.0|               1.0|        null|      null|                0.0|                  0.283|                0.077|                         0.026|                    null|                  null|                            0.0|             null|        null|                    null|         null|                     null|                 null|                             null|                  null|                              null|     null|       null|                    null|                  null|              null|                           null|         null|          null|       null|              null|             null|                   null|            null|                     null|                          null|                         null|                               null|                                 null|           27.78|3.8928341E7|            54.422|      18.6|        2.581|        1.337|      1803.987|           null|              597.029|               9.59|          null|        null|                37.746|                       0.5|          64.83|                  0.511|                  null|\n",
            "|     AFG|     Asia|Afghanistan|2020-03-12|       12.0|      1.0|             1.143|        null|      null|                0.0|                  0.308|                0.026|                         0.029|                    null|                  null|                            0.0|             null|        null|                    null|         null|                     null|                 null|                             null|                  null|                              null|     null|       null|                    null|                  null|              null|                           null|         null|          null|       null|              null|             null|                   null|            null|                     null|                          null|                         null|                               null|                                 null|           27.78|3.8928341E7|            54.422|      18.6|        2.581|        1.337|      1803.987|           null|              597.029|               9.59|          null|        null|                37.746|                       0.5|          64.83|                  0.511|                  null|\n",
            "|     AFG|     Asia|Afghanistan|2020-03-13|       13.0|      1.0|             1.286|        null|      null|                0.0|                  0.334|                0.026|                         0.033|                    null|                  null|                            0.0|             null|        null|                    null|         null|                     null|                 null|                             null|                  null|                              null|     null|       null|                    null|                  null|              null|                           null|         null|          null|       null|              null|             null|                   null|            null|                     null|                          null|                         null|                               null|                                 null|           27.78|3.8928341E7|            54.422|      18.6|        2.581|        1.337|      1803.987|           null|              597.029|               9.59|          null|        null|                37.746|                       0.5|          64.83|                  0.511|                  null|\n",
            "|     AFG|     Asia|Afghanistan|2020-03-14|       15.0|      2.0|             1.571|        null|      null|                0.0|                  0.385|                0.051|                          0.04|                    null|                  null|                            0.0|             null|        null|                    null|         null|                     null|                 null|                             null|                  null|                              null|     null|       null|                    null|                  null|              null|                           null|         null|          null|       null|              null|             null|                   null|            null|                     null|                          null|                         null|                               null|                                 null|           38.89|3.8928341E7|            54.422|      18.6|        2.581|        1.337|      1803.987|           null|              597.029|               9.59|          null|        null|                37.746|                       0.5|          64.83|                  0.511|                  null|\n",
            "+--------+---------+-----------+----------+-----------+---------+------------------+------------+----------+-------------------+-----------------------+---------------------+------------------------------+------------------------+----------------------+-------------------------------+-----------------+------------+------------------------+-------------+-------------------------+---------------------+---------------------------------+----------------------+----------------------------------+---------+-----------+------------------------+----------------------+------------------+-------------------------------+-------------+--------------+-----------+------------------+-----------------+-----------------------+----------------+-------------------------+------------------------------+-----------------------------+-----------------------------------+-------------------------------------+----------------+-----------+------------------+----------+-------------+-------------+--------------+---------------+---------------------+-------------------+--------------+------------+----------------------+--------------------------+---------------+-----------------------+----------------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "df_clean.select(['iso_code','location','date', 'recover_per_population']).filter(df['date']=='2021-03-31').orderBy('recover_per_population', ascending=False).show(15)"
      ],
      "metadata": {
        "id": "-oOlijftLxg_",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "32d4c402-57f7-4f2d-b983-abf5ad73804d"
      },
      "execution_count": 9,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------+-------------+----------+----------------------+\n",
            "|iso_code|     location|      date|recover_per_population|\n",
            "+--------+-------------+----------+----------------------+\n",
            "|     AND|      Andorra|2021-03-31|    15.395068918656571|\n",
            "|     MNE|   Montenegro|2021-03-31|    14.320879148873837|\n",
            "|     CZE|      Czechia|2021-03-31|    14.062130275314685|\n",
            "|     SMR|   San Marino|2021-03-31|    13.689669397135955|\n",
            "|     SVN|     Slovenia|2021-03-31|     10.17613851727714|\n",
            "|     LUX|   Luxembourg|2021-03-31|     9.728168492082764|\n",
            "|     ISR|       Israel|2021-03-31|      9.55337164944398|\n",
            "|     USA|United States|2021-03-31|     9.036138614323528|\n",
            "|     SRB|       Serbia|2021-03-31|     8.748322457350884|\n",
            "|     BHR|      Bahrain|2021-03-31|      8.45824153156208|\n",
            "|     PAN|       Panama|2021-03-31|     8.087039673975518|\n",
            "|     EST|      Estonia|2021-03-31|     7.954685086529683|\n",
            "|     PRT|     Portugal|2021-03-31|     7.893469921220645|\n",
            "|     SWE|       Sweden|2021-03-31|     7.836417879708137|\n",
            "|     LTU|    Lithuania|2021-03-31|     7.807578249349537|\n",
            "+--------+-------------+----------+----------------------+\n",
            "only showing top 15 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Задние 2.Top 10 стран с максимальным зафиксированным кол-вом новых случаев за последнюю неделю марта 2021 в отсортированном порядке по убыванию\n",
        "(в выходящем датасете необходимы колонки: число, страна, кол-во новых случаев)"
      ],
      "metadata": {
        "id": "qZQRKhVeRYZM"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Фильтруем записи по условию поподанию в требуемый временной промежуток\n",
        "df_in_time_range = df_clean.select(\n",
        "    ['iso_code',\n",
        "    'location',\n",
        "    'date',\n",
        "    'new_cases']).filter( \n",
        "        (df['date'] > '2021-03-23') & (df['date'] < '2021-04-01'))\n",
        "\n",
        "df_in_time_range.show()"
      ],
      "metadata": {
        "id": "gMiUYwQtRgP_",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "895afb55-85c6-475a-dd79-98b4dbd312b2"
      },
      "execution_count": 10,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------+-----------+----------+---------+\n",
            "|iso_code|   location|      date|new_cases|\n",
            "+--------+-----------+----------+---------+\n",
            "|     AFG|Afghanistan|2021-03-24|     15.0|\n",
            "|     AFG|Afghanistan|2021-03-25|     34.0|\n",
            "|     AFG|Afghanistan|2021-03-26|     28.0|\n",
            "|     AFG|Afghanistan|2021-03-27|     36.0|\n",
            "|     AFG|Afghanistan|2021-03-28|      4.0|\n",
            "|     AFG|Afghanistan|2021-03-29|     28.0|\n",
            "|     AFG|Afghanistan|2021-03-30|     62.0|\n",
            "|     AFG|Afghanistan|2021-03-31|     70.0|\n",
            "|     ALB|    Albania|2021-03-24|    448.0|\n",
            "|     ALB|    Albania|2021-03-25|    472.0|\n",
            "|     ALB|    Albania|2021-03-26|    449.0|\n",
            "|     ALB|    Albania|2021-03-27|    425.0|\n",
            "|     ALB|    Albania|2021-03-28|    493.0|\n",
            "|     ALB|    Albania|2021-03-29|    285.0|\n",
            "|     ALB|    Albania|2021-03-30|    304.0|\n",
            "|     ALB|    Albania|2021-03-31|    434.0|\n",
            "|     DZA|    Algeria|2021-03-24|     89.0|\n",
            "|     DZA|    Algeria|2021-03-25|    105.0|\n",
            "|     DZA|    Algeria|2021-03-26|    114.0|\n",
            "|     DZA|    Algeria|2021-03-27|     93.0|\n",
            "+--------+-----------+----------+---------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Группируем по странам и считаем сумму случаев \n",
        "df_sum_new_cases_per_time = df_in_time_range.groupby('iso_code').max()\n",
        "df_sum_new_cases_per_time.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "VpFYALXM3E3c",
        "outputId": "befd0f78-eccd-4318-da52-5f9da70a1d23"
      },
      "execution_count": 11,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------+--------------+\n",
            "|iso_code|max(new_cases)|\n",
            "+--------+--------------+\n",
            "|     HTI|          22.0|\n",
            "|     BRB|          20.0|\n",
            "|     LVA|         776.0|\n",
            "|     JAM|         480.0|\n",
            "|     BRA|      100158.0|\n",
            "|     ARM|        1257.0|\n",
            "|     CUB|        1051.0|\n",
            "|     JOR|        9130.0|\n",
            "|     FRA|       65392.0|\n",
            "|     BRN|           3.0|\n",
            "|     COD|          93.0|\n",
            "|     FSM|           0.0|\n",
            "|     BOL|        1072.0|\n",
            "|     GIB|           0.0|\n",
            "|     LBY|         912.0|\n",
            "|     ETH|        2173.0|\n",
            "|     ATG|          23.0|\n",
            "|     GNQ|          71.0|\n",
            "|     ITA|       24076.0|\n",
            "|     CMR|        7047.0|\n",
            "+--------+--------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# calculate using python"
      ],
      "metadata": {
        "id": "83Rr4hz7T9aZ"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Выводим TOP10 стран по количеству новых\n",
        "df_sum_new_cases_per_time.orderBy('max(new_cases)', ascending=False).show(10)"
      ],
      "metadata": {
        "id": "fjSVeauqT4qO",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "7c68d1b4-a3e1-4409-e921-58bba74f9925"
      },
      "execution_count": 12,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------+--------------+\n",
            "|iso_code|max(new_cases)|\n",
            "+--------+--------------+\n",
            "|     BRA|      100158.0|\n",
            "|     USA|       86960.0|\n",
            "|     IND|       72330.0|\n",
            "|     FRA|       65392.0|\n",
            "|     TUR|       39302.0|\n",
            "|     POL|       35145.0|\n",
            "|     DEU|       25014.0|\n",
            "|     ITA|       24076.0|\n",
            "|     PER|       19206.0|\n",
            "|     UKR|       18226.0|\n",
            "+--------+--------------+\n",
            "only showing top 10 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# calculate using sql "
      ],
      "metadata": {
        "id": "uZyKZ5i4LIui"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "df_in_time_range.registerTempTable(\"df_table\")\n",
        "df_sum_new_cases_per_time = spark.sql(\n",
        "          \"SELECT iso_code, max(new_cases) \"\n",
        "          \"FROM df_table \"\n",
        "          \"GROUP BY iso_code \" \n",
        "          \"ORDER BY max(new_cases) DESC \"\n",
        "          \"LIMIT 10 \" \n",
        "           )\n",
        "\n",
        "df_sum_new_cases_per_time.show() "
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "MD8wlS3QIlRV",
        "outputId": "d948518f-9cc1-4a65-d98e-8e49d12e8f69"
      },
      "execution_count": 13,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "/usr/local/lib/python3.7/dist-packages/pyspark/sql/dataframe.py:140: FutureWarning: Deprecated in 2.0, use createOrReplaceTempView instead.\n",
            "  FutureWarning\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------+--------------+\n",
            "|iso_code|max(new_cases)|\n",
            "+--------+--------------+\n",
            "|     BRA|      100158.0|\n",
            "|     USA|       86960.0|\n",
            "|     IND|       72330.0|\n",
            "|     FRA|       65392.0|\n",
            "|     TUR|       39302.0|\n",
            "|     POL|       35145.0|\n",
            "|     DEU|       25014.0|\n",
            "|     ITA|       24076.0|\n",
            "|     PER|       19206.0|\n",
            "|     UKR|       18226.0|\n",
            "+--------+--------------+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Не придумал, ничего лучше, кроме того как сделать join, чтобы получить дату"
      ],
      "metadata": {
        "id": "nWXRck-JUcUA"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "cond = [df_sum_new_cases_per_time[\"max(new_cases)\"] == df_in_time_range[\"new_cases\"], df_sum_new_cases_per_time[\"iso_code\"] == df_in_time_range[\"iso_code\"] ]\n",
        "        \n",
        "df_sum_new_cases_per_time_with_time = df_sum_new_cases_per_time.join(df_in_time_range, cond, \"left_outer\")\n",
        "df_sum_new_cases_per_time_with_time.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "GvTItu8WTqNf",
        "outputId": "b7415cd0-f522-4d2c-f90b-31af07f6cea1"
      },
      "execution_count": 14,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------+--------------+--------+-------------+----------+---------+\n",
            "|iso_code|max(new_cases)|iso_code|     location|      date|new_cases|\n",
            "+--------+--------------+--------+-------------+----------+---------+\n",
            "|     BRA|      100158.0|     BRA|       Brazil|2021-03-25| 100158.0|\n",
            "|     USA|       86960.0|     USA|United States|2021-03-24|  86960.0|\n",
            "|     IND|       72330.0|     IND|        India|2021-03-31|  72330.0|\n",
            "|     FRA|       65392.0|     FRA|       France|2021-03-24|  65392.0|\n",
            "|     TUR|       39302.0|     TUR|       Turkey|2021-03-31|  39302.0|\n",
            "|     POL|       35145.0|     POL|       Poland|2021-03-26|  35145.0|\n",
            "|     DEU|       25014.0|     DEU|      Germany|2021-03-31|  25014.0|\n",
            "|     ITA|       24076.0|     ITA|        Italy|2021-03-26|  24076.0|\n",
            "|     PER|       19206.0|     PER|         Peru|2021-03-25|  19206.0|\n",
            "|     UKR|       18226.0|     UKR|      Ukraine|2021-03-26|  18226.0|\n",
            "+--------+--------------+--------+-------------+----------+---------+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "df = df_sum_new_cases_per_time_with_time.drop(df_in_time_range.new_cases)\n",
        "df = df.drop(df_in_time_range.iso_code)\n",
        "df.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "x38dXWDoYHGP",
        "outputId": "3df1a188-230b-440a-a05d-c1f28d6f8d8f"
      },
      "execution_count": 15,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------+--------+-------------+----------+\n",
            "|max(new_cases)|iso_code|     location|      date|\n",
            "+--------------+--------+-------------+----------+\n",
            "|      100158.0|     BRA|       Brazil|2021-03-25|\n",
            "|       86960.0|     USA|United States|2021-03-24|\n",
            "|       72330.0|     IND|        India|2021-03-31|\n",
            "|       65392.0|     FRA|       France|2021-03-24|\n",
            "|       39302.0|     TUR|       Turkey|2021-03-31|\n",
            "|       35145.0|     POL|       Poland|2021-03-26|\n",
            "|       25014.0|     DEU|      Germany|2021-03-31|\n",
            "|       24076.0|     ITA|        Italy|2021-03-26|\n",
            "|       19206.0|     PER|         Peru|2021-03-25|\n",
            "|       18226.0|     UKR|      Ukraine|2021-03-26|\n",
            "+--------------+--------+-------------+----------+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Задние 3. Посчитайте изменение случаев относительно предыдущего дня в России за последнюю неделю марта 2021. (например: в россии вчера было 9150 , сегодня 8763, итог: -387) (в выходящем датасете необходимы колонки: число, кол-во новых случаев вчера, кол-во новых случаев сегодня, дельта)"
      ],
      "metadata": {
        "id": "-MArUbiBiwA5"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import pyspark.sql.functions as f\n",
        "from pyspark.sql import Window"
      ],
      "metadata": {
        "id": "isbIkCG_6H7j"
      },
      "execution_count": 16,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "df_in_time_range_RUS = df_in_time_range.filter(df_in_time_range['iso_code'] == 'RUS')\n",
        "df_in_time_range_RUS.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "-BsA6fjp5qz4",
        "outputId": "ab33b4fe-0889-40f4-c349-684f91dc06d5"
      },
      "execution_count": 17,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------+--------+----------+---------+\n",
            "|iso_code|location|      date|new_cases|\n",
            "+--------+--------+----------+---------+\n",
            "|     RUS|  Russia|2021-03-24|   8769.0|\n",
            "|     RUS|  Russia|2021-03-25|   9128.0|\n",
            "|     RUS|  Russia|2021-03-26|   9073.0|\n",
            "|     RUS|  Russia|2021-03-27|   8783.0|\n",
            "|     RUS|  Russia|2021-03-28|   8979.0|\n",
            "|     RUS|  Russia|2021-03-29|   8589.0|\n",
            "|     RUS|  Russia|2021-03-30|   8162.0|\n",
            "|     RUS|  Russia|2021-03-31|   8156.0|\n",
            "+--------+--------+----------+---------+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "w = Window.partitionBy('iso_code', 'location').orderBy('date') \n",
        "df_in_time_range_RUS.withColumn('lead', f.lag('new_cases', 1).over(w)) \\\n",
        "  .withColumn('new_cases_diff', f.when(f.col('lead').isNotNull(), f.col('new_cases') - f.col('lead')).otherwise(f.lit(None))) \\\n",
        "  .show(10, False)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "NDc3Ywoz7mkC",
        "outputId": "b7e0e2fa-ea2f-4ae6-e684-0f1269a8b3db"
      },
      "execution_count": 18,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------+--------+----------+---------+------+--------------+\n",
            "|iso_code|location|date      |new_cases|lead  |new_cases_diff|\n",
            "+--------+--------+----------+---------+------+--------------+\n",
            "|RUS     |Russia  |2021-03-24|8769.0   |null  |null          |\n",
            "|RUS     |Russia  |2021-03-25|9128.0   |8769.0|359.0         |\n",
            "|RUS     |Russia  |2021-03-26|9073.0   |9128.0|-55.0         |\n",
            "|RUS     |Russia  |2021-03-27|8783.0   |9073.0|-290.0        |\n",
            "|RUS     |Russia  |2021-03-28|8979.0   |8783.0|196.0         |\n",
            "|RUS     |Russia  |2021-03-29|8589.0   |8979.0|-390.0        |\n",
            "|RUS     |Russia  |2021-03-30|8162.0   |8589.0|-427.0        |\n",
            "|RUS     |Russia  |2021-03-31|8156.0   |8162.0|-6.0          |\n",
            "+--------+--------+----------+---------+------+--------------+\n",
            "\n"
          ]
        }
      ]
    }
  ]
}