Spark_Transformations_and_Actions_Part_2.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "Spark Transformations and Actions Part 2.ipynb",
      "provenance": [],
      "collapsed_sections": [],
      "authorship_tag": "ABX9TyOCKEljkNSBkWBtPrYZVWPC",
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/srivatsan88/Mastering-Apache-Spark/blob/master/Spark_Transformations_and_Actions_Part_2.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "jEymwVEhRU51",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "!apt-get install openjdk-8-jdk-headless -qq > /dev/null\n",
        "!wget -q https://downloads.apache.org/spark/spark-3.0.0-preview2/spark-3.0.0-preview2-bin-hadoop2.7.tgz\n",
        "!tar xf spark-3.0.0-preview2-bin-hadoop2.7.tgz\n",
        "!pip install -q findspark"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "5yAPIY0HKg_X",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "!ls /usr/lib/jvm/"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "CrzF3tsBJ-3r",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "import os\n",
        "os.environ[\"JAVA_HOME\"] = \"/usr/lib/jvm/java-8-openjdk-amd64\"\n",
        "os.environ[\"SPARK_HOME\"] = \"/content/spark-3.0.0-preview2-bin-hadoop2.7\""
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "TyrI8ZghKksx",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "import findspark\n",
        "findspark.init()\n",
        "from pyspark.sql import SparkSession\n",
        "spark = SparkSession.builder.master(\"local[*]\").getOrCreate()"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "L0P8dgheNTRe",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "import sys,tempfile, urllib\n",
        "from pyspark.sql.functions import *"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "WmcAlgXbRBUf",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "BASE_DIR = '/tmp'\n",
        "CORONA_DATA_FILE = os.path.join(BASE_DIR, 'corona_data.csv')"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "vIJbccdzQ-XV",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "corona_data=urllib.request.urlretrieve('https://raw.githubusercontent.com/srivatsan88/YouTubeLI/master/dataset/coronavirus/corona_dataset_latest.csv', CORONA_DATA_FILE)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "qzCJUSOIR-Bp",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "!ls /tmp"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "u2ajT-ksrqtM",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "corona_df = spark.read.option(\"inferSchema\", \"true\").csv(\"/tmp/corona_data.csv\", header=True)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "p31fOLj0R2Tp",
        "colab_type": "code",
        "outputId": "77665a84-ff09-4c4e-b7db-babd0c13b650",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 490
        }
      },
      "source": [
        "corona_df.show()"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+---+----------------+--------------------+--------+---------+----------+---------+-----+---------+--------------------+----+\n",
            "|_c0|           State|             Country|     Lat|     Long|      Date|Confirmed|Death|Recovered|       state_cleaned|City|\n",
            "+---+----------------+--------------------+--------+---------+----------+---------+-----+---------+--------------------+----+\n",
            "|  0|            null|            Thailand|    15.0|    101.0|2020-01-22|        2|    0|        0|             Bangkok|null|\n",
            "|  1|            null|               Japan|    36.0|    138.0|2020-01-22|        2|    0|        0|             Hiraide|null|\n",
            "|  2|            null|           Singapore|  1.2833| 103.8333|2020-01-22|        0|    0|        0|           Singapore|null|\n",
            "|  3|            null|               Nepal| 28.1667|    84.25|2020-01-22|        0|    0|        0|           Kathmandu|null|\n",
            "|  4|            null|            Malaysia|     2.5|    112.5|2020-01-22|        0|    0|        0|             Sarawak|null|\n",
            "|  5|British Columbia|              Canada| 49.2827|-123.1207|2020-01-22|        0|    0|        0|    British Columbia|null|\n",
            "|  6| New South Wales|           Australia|-33.8688| 151.2093|2020-01-22|        0|    0|        0|     New South Wales|null|\n",
            "|  7|        Victoria|           Australia|-37.8136| 144.9631|2020-01-22|        0|    0|        0|            Victoria|null|\n",
            "|  8|      Queensland|           Australia|-28.0167|    153.4|2020-01-22|        0|    0|        0|          Queensland|null|\n",
            "|  9|            null|            Cambodia|   11.55| 104.9167|2020-01-22|        0|    0|        0|          Phnom Penh|null|\n",
            "| 10|            null|           Sri Lanka|     7.0|     81.0|2020-01-22|        0|    0|        0|Sri Jayawardenapu...|null|\n",
            "| 11|            null|             Germany|    51.0|      9.0|2020-01-22|        0|    0|        0|              Berlin|null|\n",
            "| 12|            null|             Finland|    64.0|     26.0|2020-01-22|        0|    0|        0|            Helsinki|null|\n",
            "| 13|            null|United Arab Emirates|    24.0|     54.0|2020-01-22|        0|    0|        0|           Abu Dhabi|null|\n",
            "| 14|            null|         Philippines|    13.0|    122.0|2020-01-22|        0|    0|        0|              Manila|null|\n",
            "| 15|            null|               India|    21.0|     78.0|2020-01-22|        0|    0|        0|           New Delhi|null|\n",
            "| 16|            null|               Italy|    43.0|     12.0|2020-01-22|        0|    0|        0|                Rome|null|\n",
            "| 17|            null|              Sweden|    63.0|     16.0|2020-01-22|        0|    0|        0|           Stockholm|null|\n",
            "| 18|            null|               Spain|    40.0|     -4.0|2020-01-22|        0|    0|        0|              Toledo|null|\n",
            "| 19| South Australia|           Australia|-34.9285| 138.6007|2020-01-22|        0|    0|        0|     South Australia|null|\n",
            "+---+----------------+--------------------+--------+---------+----------+---------+-----+---------+--------------------+----+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "I794SPHNL19B",
        "colab_type": "code",
        "outputId": "45f92d8f-6f3f-421e-f764-6066290da830",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 35
        }
      },
      "source": [
        "corona_df.count()"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "28143"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 12
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "vIWyVVFATvPN",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "import pyspark.sql.functions as F\n",
        "corona_max_df=corona_df.join(corona_df.groupBy(\"Country\",\"State_cleaned\").agg(F.max(\"Date\").alias(\"Date\")),on=['Country', 'State_cleaned','Date'],how=\"inner\")"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "aor5b4igUPNn",
        "colab_type": "code",
        "outputId": "5fb58211-bcf3-4eb5-b13d-a5af40c2c81c",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 490
        }
      },
      "source": [
        "corona_max_df.show()"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+--------------------+--------------------+----------+-----+----------------+--------+---------+---------+-----+---------+----+\n",
            "|             Country|       state_cleaned|      Date|  _c0|           State|     Lat|     Long|Confirmed|Death|Recovered|City|\n",
            "+--------------------+--------------------+----------+-----+----------------+--------+---------+---------+-----+---------+----+\n",
            "|            Thailand|             Bangkok|2020-03-20|27666|            null|    15.0|    101.0|      322|    1|       42|null|\n",
            "|               Japan|             Hiraide|2020-03-20|27667|            null|    36.0|    138.0|      963|   33|      191|null|\n",
            "|           Singapore|           Singapore|2020-03-20|27668|            null|  1.2833| 103.8333|      385|    0|      124|null|\n",
            "|               Nepal|           Kathmandu|2020-03-20|27669|            null| 28.1667|    84.25|        1|    0|        1|null|\n",
            "|            Malaysia|             Sarawak|2020-03-20|27670|            null|     2.5|    112.5|     1030|    3|       87|null|\n",
            "|              Canada|    British Columbia|2020-03-20|27671|British Columbia| 49.2827|-123.1207|      271|    8|        4|null|\n",
            "|           Australia|     New South Wales|2020-03-20|27672| New South Wales|-33.8688| 151.2093|      353|    6|        4|null|\n",
            "|           Australia|            Victoria|2020-03-20|27673|        Victoria|-37.8136| 144.9631|      121|    0|        8|null|\n",
            "|           Australia|          Queensland|2020-03-20|27674|      Queensland|-28.0167|    153.4|      184|    0|        8|null|\n",
            "|            Cambodia|          Phnom Penh|2020-03-20|27675|            null|   11.55| 104.9167|       51|    0|        1|null|\n",
            "|           Sri Lanka|Sri Jayawardenapu...|2020-03-20|27676|            null|     7.0|     81.0|       73|    0|        3|null|\n",
            "|             Germany|              Berlin|2020-03-20|27677|            null|    51.0|      9.0|    19848|   67|      180|null|\n",
            "|             Finland|            Helsinki|2020-03-20|27678|            null|    64.0|     26.0|      450|    0|       10|null|\n",
            "|United Arab Emirates|           Abu Dhabi|2020-03-20|27679|            null|    24.0|     54.0|      140|    2|       31|null|\n",
            "|         Philippines|              Manila|2020-03-20|27680|            null|    13.0|    122.0|      230|   18|        8|null|\n",
            "|               India|           New Delhi|2020-03-20|27681|            null|    21.0|     78.0|      244|    5|       20|null|\n",
            "|               Italy|                Rome|2020-03-20|27682|            null|    43.0|     12.0|    47021| 4032|     4440|null|\n",
            "|              Sweden|           Stockholm|2020-03-20|27683|            null|    63.0|     16.0|     1639|   16|       16|null|\n",
            "|               Spain|              Toledo|2020-03-20|27684|            null|    40.0|     -4.0|    20410| 1043|     1588|null|\n",
            "|           Australia|     South Australia|2020-03-20|27685| South Australia|-34.9285| 138.6007|       50|    0|        3|null|\n",
            "+--------------------+--------------------+----------+-----+----------------+--------+---------+---------+-----+---------+----+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Qr4LTRYl2PVI",
        "colab_type": "code",
        "outputId": "1b3c62d2-aaf4-41a1-9fa8-3de61f726d97",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 145
        }
      },
      "source": [
        "corona_max_df.select(\"Country\",\"State_cleaned\",\"Confirmed\",\"Recovered\").filter(col(\"Country\").isin('Australia','China')).groupBy('Country').sum().show(100)"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+---------+--------------+--------------+\n",
            "|  Country|sum(Confirmed)|sum(Recovered)|\n",
            "+---------+--------------+--------------+\n",
            "|Australia|           791|            26|\n",
            "|    China|         81250|         71266|\n",
            "+---------+--------------+--------------+\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ErERE-dQ2PJt",
        "colab_type": "code",
        "outputId": "7250894a-f5ee-4430-f37a-614a96870e52",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 163
        }
      },
      "source": [
        "corona_max_df.select(\"Country\",\"State_cleaned\",\"Confirmed\",\"Recovered\").filter(col(\"Country\").isin('Australia','China')).cube(\"Country\").sum().sort(asc(\"Country\")).show(100)"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+---------+--------------+--------------+\n",
            "|  Country|sum(Confirmed)|sum(Recovered)|\n",
            "+---------+--------------+--------------+\n",
            "|     null|         82041|         71292|\n",
            "|Australia|           791|            26|\n",
            "|    China|         81250|         71266|\n",
            "+---------+--------------+--------------+\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "CLeMeOwJvDWD",
        "colab_type": "code",
        "outputId": "e47e5b39-fc99-4992-8901-1df1216586b4",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 890
        }
      },
      "source": [
        "corona_max_df.select(\"Country\",\"State_cleaned\",\"Confirmed\",\"Recovered\").filter(col(\"Country\").isin('Australia','Canada')).cube(\"Country\",\"State_cleaned\").sum().sort(asc(\"Country\")).show(100)"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+---------+--------------------+--------------+--------------+\n",
            "|  Country|       State_cleaned|sum(Confirmed)|sum(Recovered)|\n",
            "+---------+--------------------+--------------+--------------+\n",
            "|     null|          Queensland|           184|             8|\n",
            "|     null|            Manitoba|            17|             0|\n",
            "|     null|  Northern Territory|             3|             0|\n",
            "|     null|      Grand Princess|            10|             0|\n",
            "|     null|   Western Australia|            64|             0|\n",
            "|     null|        Saskatchewan|            20|             0|\n",
            "|     null|            Tasmania|            10|             3|\n",
            "|     null|From Diamond Prin...|             0|             0|\n",
            "|     null|    British Columbia|           271|             4|\n",
            "|     null|         Nova Scotia|            15|             0|\n",
            "|     null|Newfoundland and ...|             4|             0|\n",
            "|     null|             Ontario|           308|             5|\n",
            "|     null|Australian Capita...|             6|             0|\n",
            "|     null|             Alberta|           146|             0|\n",
            "|     null|     New South Wales|           353|             4|\n",
            "|     null|     South Australia|            50|             3|\n",
            "|     null|       New Brunswick|            11|             0|\n",
            "|     null|            Victoria|           121|             8|\n",
            "|     null|                null|          1734|            35|\n",
            "|     null|              Quebec|           139|             0|\n",
            "|     null|Prince Edward Island|             2|             0|\n",
            "|Australia|     New South Wales|           353|             4|\n",
            "|Australia|Australian Capita...|             6|             0|\n",
            "|Australia|   Western Australia|            64|             0|\n",
            "|Australia|            Tasmania|            10|             3|\n",
            "|Australia|            Victoria|           121|             8|\n",
            "|Australia|  Northern Territory|             3|             0|\n",
            "|Australia|          Queensland|           184|             8|\n",
            "|Australia|                null|           791|            26|\n",
            "|Australia|From Diamond Prin...|             0|             0|\n",
            "|Australia|     South Australia|            50|             3|\n",
            "|   Canada|        Saskatchewan|            20|             0|\n",
            "|   Canada|Newfoundland and ...|             4|             0|\n",
            "|   Canada|       New Brunswick|            11|             0|\n",
            "|   Canada|             Ontario|           308|             5|\n",
            "|   Canada|              Quebec|           139|             0|\n",
            "|   Canada|Prince Edward Island|             2|             0|\n",
            "|   Canada|             Alberta|           146|             0|\n",
            "|   Canada|         Nova Scotia|            15|             0|\n",
            "|   Canada|            Manitoba|            17|             0|\n",
            "|   Canada|      Grand Princess|            10|             0|\n",
            "|   Canada|                null|           943|             9|\n",
            "|   Canada|    British Columbia|           271|             4|\n",
            "+---------+--------------------+--------------+--------------+\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "N0BxDa-93t3c",
        "colab_type": "code",
        "outputId": "fdaa0736-1f87-43c5-859d-997d87a0e64e",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 526
        }
      },
      "source": [
        "corona_max_df.select(\"Country\",\"State_cleaned\",\"Confirmed\",\"Recovered\").filter(col(\"Country\").isin('Australia','Canada')).rollup(\"Country\",\"State_cleaned\").sum().sort(asc(\"Country\")).show(100)"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+---------+--------------------+--------------+--------------+\n",
            "|  Country|       State_cleaned|sum(Confirmed)|sum(Recovered)|\n",
            "+---------+--------------------+--------------+--------------+\n",
            "|     null|                null|          1734|            35|\n",
            "|Australia|  Northern Territory|             3|             0|\n",
            "|Australia|            Tasmania|            10|             3|\n",
            "|Australia|     New South Wales|           353|             4|\n",
            "|Australia|Australian Capita...|             6|             0|\n",
            "|Australia|   Western Australia|            64|             0|\n",
            "|Australia|            Victoria|           121|             8|\n",
            "|Australia|                null|           791|            26|\n",
            "|Australia|From Diamond Prin...|             0|             0|\n",
            "|Australia|     South Australia|            50|             3|\n",
            "|Australia|          Queensland|           184|             8|\n",
            "|   Canada|       New Brunswick|            11|             0|\n",
            "|   Canada|        Saskatchewan|            20|             0|\n",
            "|   Canada|      Grand Princess|            10|             0|\n",
            "|   Canada|             Alberta|           146|             0|\n",
            "|   Canada|         Nova Scotia|            15|             0|\n",
            "|   Canada|Newfoundland and ...|             4|             0|\n",
            "|   Canada|             Ontario|           308|             5|\n",
            "|   Canada|              Quebec|           139|             0|\n",
            "|   Canada|                null|           943|             9|\n",
            "|   Canada|    British Columbia|           271|             4|\n",
            "|   Canada|Prince Edward Island|             2|             0|\n",
            "|   Canada|            Manitoba|            17|             0|\n",
            "+---------+--------------------+--------------+--------------+\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "NJsN18yx3tYu",
        "colab_type": "code",
        "outputId": "7553e40b-d293-4319-8ec1-4b74bb335754",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 35
        }
      },
      "source": [
        "corona_max_df.corr('Confirmed','Recovered')"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "0.8052934236742176"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 19
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "TPSh56yt3tNl",
        "colab_type": "code",
        "outputId": "9600089b-c15b-4a95-c256-3a460ba389ec",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 55
        }
      },
      "source": [
        "corona_max_df.cache()"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "DataFrame[Country: string, state_cleaned: string, Date: string, _c0: int, State: string, Lat: double, Long: double, Confirmed: int, Death: int, Recovered: int, City: string]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 20
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "kmmtttRDtuZ6",
        "colab_type": "code",
        "outputId": "752b7be3-178d-4691-e1f4-052ee7683509",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 54
        }
      },
      "source": [
        "%timeit corona_max_df.count()"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "The slowest run took 14.90 times longer than the fastest. This could mean that an intermediate result is being cached.\n",
            "1 loop, best of 3: 66.5 ms per loop\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "1aJst2PC3tBG",
        "colab_type": "code",
        "outputId": "878306b1-a519-4aaa-c110-b2ed5e40aec3",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 55
        }
      },
      "source": [
        "from pyspark import StorageLevel\n",
        "corona_max_df.persist(StorageLevel.MEMORY_AND_DISK)\n"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "DataFrame[Country: string, state_cleaned: string, Date: string, _c0: int, State: string, Lat: double, Long: double, Confirmed: int, Death: int, Recovered: int, City: string]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 22
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Kh4TnB4w8qx0",
        "colab_type": "code",
        "outputId": "fc3282aa-157d-4863-ea95-45a64412ee85",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 35
        }
      },
      "source": [
        "%timeit corona_max_df.count()"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "10 loops, best of 3: 63.1 ms per loop\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "9-LC6vpeA2xN",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "pd=corona_df.toPandas()"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "mevXPKgBFsu5",
        "colab_type": "code",
        "outputId": "847da937-f2fd-477a-b484-f8cc550b7d64",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 228
        }
      },
      "source": [
        "pd.corr()"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<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>_c0</th>\n",
              "      <th>Lat</th>\n",
              "      <th>Long</th>\n",
              "      <th>Confirmed</th>\n",
              "      <th>Death</th>\n",
              "      <th>Recovered</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>_c0</th>\n",
              "      <td>1.000000</td>\n",
              "      <td>-0.001594</td>\n",
              "      <td>-0.005303</td>\n",
              "      <td>0.046789</td>\n",
              "      <td>0.043155</td>\n",
              "      <td>0.042857</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Lat</th>\n",
              "      <td>-0.001594</td>\n",
              "      <td>1.000000</td>\n",
              "      <td>-0.383389</td>\n",
              "      <td>0.008531</td>\n",
              "      <td>0.007041</td>\n",
              "      <td>0.000778</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Long</th>\n",
              "      <td>-0.005303</td>\n",
              "      <td>-0.383389</td>\n",
              "      <td>1.000000</td>\n",
              "      <td>0.098893</td>\n",
              "      <td>0.074918</td>\n",
              "      <td>0.079047</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Confirmed</th>\n",
              "      <td>0.046789</td>\n",
              "      <td>0.008531</td>\n",
              "      <td>0.098893</td>\n",
              "      <td>1.000000</td>\n",
              "      <td>0.963376</td>\n",
              "      <td>0.853924</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Death</th>\n",
              "      <td>0.043155</td>\n",
              "      <td>0.007041</td>\n",
              "      <td>0.074918</td>\n",
              "      <td>0.963376</td>\n",
              "      <td>1.000000</td>\n",
              "      <td>0.857292</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Recovered</th>\n",
              "      <td>0.042857</td>\n",
              "      <td>0.000778</td>\n",
              "      <td>0.079047</td>\n",
              "      <td>0.853924</td>\n",
              "      <td>0.857292</td>\n",
              "      <td>1.000000</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                _c0       Lat      Long  Confirmed     Death  Recovered\n",
              "_c0        1.000000 -0.001594 -0.005303   0.046789  0.043155   0.042857\n",
              "Lat       -0.001594  1.000000 -0.383389   0.008531  0.007041   0.000778\n",
              "Long      -0.005303 -0.383389  1.000000   0.098893  0.074918   0.079047\n",
              "Confirmed  0.046789  0.008531  0.098893   1.000000  0.963376   0.853924\n",
              "Death      0.043155  0.007041  0.074918   0.963376  1.000000   0.857292\n",
              "Recovered  0.042857  0.000778  0.079047   0.853924  0.857292   1.000000"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 25
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "N7PuFxr3ASbz",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "corona_max_df.createOrReplaceTempView(\"corona\")"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ObE-FSJqAuie",
        "colab_type": "code",
        "outputId": "84197cea-93e2-4b10-a37f-b00dd29b4bbb",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 490
        }
      },
      "source": [
        "spark.sql(\"select * from corona\").show()"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+--------------------+--------------------+----------+-----+----------------+--------+---------+---------+-----+---------+----+\n",
            "|             Country|       state_cleaned|      Date|  _c0|           State|     Lat|     Long|Confirmed|Death|Recovered|City|\n",
            "+--------------------+--------------------+----------+-----+----------------+--------+---------+---------+-----+---------+----+\n",
            "|            Thailand|             Bangkok|2020-03-20|27666|            null|    15.0|    101.0|      322|    1|       42|null|\n",
            "|               Japan|             Hiraide|2020-03-20|27667|            null|    36.0|    138.0|      963|   33|      191|null|\n",
            "|           Singapore|           Singapore|2020-03-20|27668|            null|  1.2833| 103.8333|      385|    0|      124|null|\n",
            "|               Nepal|           Kathmandu|2020-03-20|27669|            null| 28.1667|    84.25|        1|    0|        1|null|\n",
            "|            Malaysia|             Sarawak|2020-03-20|27670|            null|     2.5|    112.5|     1030|    3|       87|null|\n",
            "|              Canada|    British Columbia|2020-03-20|27671|British Columbia| 49.2827|-123.1207|      271|    8|        4|null|\n",
            "|           Australia|     New South Wales|2020-03-20|27672| New South Wales|-33.8688| 151.2093|      353|    6|        4|null|\n",
            "|           Australia|            Victoria|2020-03-20|27673|        Victoria|-37.8136| 144.9631|      121|    0|        8|null|\n",
            "|           Australia|          Queensland|2020-03-20|27674|      Queensland|-28.0167|    153.4|      184|    0|        8|null|\n",
            "|            Cambodia|          Phnom Penh|2020-03-20|27675|            null|   11.55| 104.9167|       51|    0|        1|null|\n",
            "|           Sri Lanka|Sri Jayawardenapu...|2020-03-20|27676|            null|     7.0|     81.0|       73|    0|        3|null|\n",
            "|             Germany|              Berlin|2020-03-20|27677|            null|    51.0|      9.0|    19848|   67|      180|null|\n",
            "|             Finland|            Helsinki|2020-03-20|27678|            null|    64.0|     26.0|      450|    0|       10|null|\n",
            "|United Arab Emirates|           Abu Dhabi|2020-03-20|27679|            null|    24.0|     54.0|      140|    2|       31|null|\n",
            "|         Philippines|              Manila|2020-03-20|27680|            null|    13.0|    122.0|      230|   18|        8|null|\n",
            "|               India|           New Delhi|2020-03-20|27681|            null|    21.0|     78.0|      244|    5|       20|null|\n",
            "|               Italy|                Rome|2020-03-20|27682|            null|    43.0|     12.0|    47021| 4032|     4440|null|\n",
            "|              Sweden|           Stockholm|2020-03-20|27683|            null|    63.0|     16.0|     1639|   16|       16|null|\n",
            "|               Spain|              Toledo|2020-03-20|27684|            null|    40.0|     -4.0|    20410| 1043|     1588|null|\n",
            "|           Australia|     South Australia|2020-03-20|27685| South Australia|-34.9285| 138.6007|       50|    0|        3|null|\n",
            "+--------------------+--------------------+----------+-----+----------------+--------+---------+---------+-----+---------+----+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "LagPitKeIZ8F",
        "colab_type": "code",
        "outputId": "91be8c5d-5ff7-4b76-f576-7ac57edc7163",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 472
        }
      },
      "source": [
        "spark.sql(\"select * from corona where Country in ('Australia','Canada') order by Country\").show()"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+---------+--------------------+----------+-----+--------------------+------------------+---------+---------+-----+---------+----+\n",
            "|  Country|       state_cleaned|      Date|  _c0|               State|               Lat|     Long|Confirmed|Death|Recovered|City|\n",
            "+---------+--------------------+----------+-----+--------------------+------------------+---------+---------+-----+---------+----+\n",
            "|Australia|     New South Wales|2020-03-20|27672|     New South Wales|          -33.8688| 151.2093|      353|    6|        4|null|\n",
            "|Australia|            Victoria|2020-03-20|27673|            Victoria|          -37.8136| 144.9631|      121|    0|        8|null|\n",
            "|Australia|   Western Australia|2020-03-20|27715|   Western Australia|          -31.9505| 115.8605|       64|    1|        0|null|\n",
            "|Australia|          Queensland|2020-03-20|27674|          Queensland|          -28.0167|    153.4|      184|    0|        8|null|\n",
            "|Australia|            Tasmania|2020-03-20|27727|            Tasmania|          -41.4545| 145.9707|       10|    0|        3|null|\n",
            "|Australia|  Northern Territory|2020-03-20|27737|  Northern Territory|          -12.4634| 130.8456|        3|    0|        0|null|\n",
            "|Australia|     South Australia|2020-03-20|27685|     South Australia|          -34.9285| 138.6007|       50|    0|        3|null|\n",
            "|Australia|Australian Capita...|2020-03-20|28068|Australian Capita...|          -35.4735| 149.0124|        6|    0|        0|null|\n",
            "|Australia|From Diamond Prin...|2020-03-20|27688|From Diamond Prin...|           35.4437|  139.638|        0|    0|        0|null|\n",
            "|   Canada|    British Columbia|2020-03-20|27671|    British Columbia|           49.2827|-123.1207|      271|    8|        4|null|\n",
            "|   Canada|             Ontario|2020-03-20|27758|             Ontario|           51.2538| -85.3232|      308|    2|        5|null|\n",
            "|   Canada|             Alberta|2020-03-20|27759|             Alberta|           53.9333|-116.5765|      146|    1|        0|null|\n",
            "|   Canada|        Saskatchewan|2020-03-20|28073|        Saskatchewan|           52.9399|-106.4509|       20|    0|        0|null|\n",
            "|   Canada|         Nova Scotia|2020-03-20|28107|         Nova Scotia|44.681999999999995| -63.7443|       15|    0|        0|null|\n",
            "|   Canada|              Quebec|2020-03-20|27760|              Quebec|           52.9399| -73.5491|      139|    1|        0|null|\n",
            "|   Canada|       New Brunswick|2020-03-20|27867|       New Brunswick|           46.5653| -66.4619|       11|    0|        0|null|\n",
            "|   Canada|      Grand Princess|2020-03-20|28077|      Grand Princess|           37.6489|-122.6655|       10|    0|        0|null|\n",
            "|   Canada|Newfoundland and ...|2020-03-20|28099|Newfoundland and ...|           53.1355| -57.6604|        4|    0|        0|null|\n",
            "|   Canada|            Manitoba|2020-03-20|28072|            Manitoba|           53.7609| -98.8139|       17|    0|        0|null|\n",
            "|   Canada|Prince Edward Island|2020-03-20|28100|Prince Edward Island|           46.5107| -63.4168|        2|    0|        0|null|\n",
            "+---------+--------------------+----------+-----+--------------------+------------------+---------+---------+-----+---------+----+\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Qq_MnboMbfAb",
        "colab_type": "code",
        "outputId": "9405eb75-a575-4ff9-91e0-9f5f0636f4d8",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 472
        }
      },
      "source": [
        "spark.sql(\"select * from corona where Country in ('Australia','Canada') order by Country\").show()"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+---------+--------------------+----------+-----+--------------------+------------------+---------+---------+-----+---------+----+\n",
            "|  Country|       state_cleaned|      Date|  _c0|               State|               Lat|     Long|Confirmed|Death|Recovered|City|\n",
            "+---------+--------------------+----------+-----+--------------------+------------------+---------+---------+-----+---------+----+\n",
            "|Australia|     New South Wales|2020-03-20|27672|     New South Wales|          -33.8688| 151.2093|      353|    6|        4|null|\n",
            "|Australia|            Victoria|2020-03-20|27673|            Victoria|          -37.8136| 144.9631|      121|    0|        8|null|\n",
            "|Australia|   Western Australia|2020-03-20|27715|   Western Australia|          -31.9505| 115.8605|       64|    1|        0|null|\n",
            "|Australia|          Queensland|2020-03-20|27674|          Queensland|          -28.0167|    153.4|      184|    0|        8|null|\n",
            "|Australia|            Tasmania|2020-03-20|27727|            Tasmania|          -41.4545| 145.9707|       10|    0|        3|null|\n",
            "|Australia|  Northern Territory|2020-03-20|27737|  Northern Territory|          -12.4634| 130.8456|        3|    0|        0|null|\n",
            "|Australia|     South Australia|2020-03-20|27685|     South Australia|          -34.9285| 138.6007|       50|    0|        3|null|\n",
            "|Australia|Australian Capita...|2020-03-20|28068|Australian Capita...|          -35.4735| 149.0124|        6|    0|        0|null|\n",
            "|Australia|From Diamond Prin...|2020-03-20|27688|From Diamond Prin...|           35.4437|  139.638|        0|    0|        0|null|\n",
            "|   Canada|    British Columbia|2020-03-20|27671|    British Columbia|           49.2827|-123.1207|      271|    8|        4|null|\n",
            "|   Canada|             Ontario|2020-03-20|27758|             Ontario|           51.2538| -85.3232|      308|    2|        5|null|\n",
            "|   Canada|             Alberta|2020-03-20|27759|             Alberta|           53.9333|-116.5765|      146|    1|        0|null|\n",
            "|   Canada|        Saskatchewan|2020-03-20|28073|        Saskatchewan|           52.9399|-106.4509|       20|    0|        0|null|\n",
            "|   Canada|         Nova Scotia|2020-03-20|28107|         Nova Scotia|44.681999999999995| -63.7443|       15|    0|        0|null|\n",
            "|   Canada|              Quebec|2020-03-20|27760|              Quebec|           52.9399| -73.5491|      139|    1|        0|null|\n",
            "|   Canada|       New Brunswick|2020-03-20|27867|       New Brunswick|           46.5653| -66.4619|       11|    0|        0|null|\n",
            "|   Canada|      Grand Princess|2020-03-20|28077|      Grand Princess|           37.6489|-122.6655|       10|    0|        0|null|\n",
            "|   Canada|Newfoundland and ...|2020-03-20|28099|Newfoundland and ...|           53.1355| -57.6604|        4|    0|        0|null|\n",
            "|   Canada|            Manitoba|2020-03-20|28072|            Manitoba|           53.7609| -98.8139|       17|    0|        0|null|\n",
            "|   Canada|Prince Edward Island|2020-03-20|28100|Prince Edward Island|           46.5107| -63.4168|        2|    0|        0|null|\n",
            "+---------+--------------------+----------+-----+--------------------+------------------+---------+---------+-----+---------+----+\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "e06hb7wfYni4",
        "colab_type": "code",
        "outputId": "ecda5f85-2ef0-4c87-ac64-961fdbb285af",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 490
        }
      },
      "source": [
        "spark.sql(\"select Country, State_cleaned, SUM(Confirmed), SUM(Recovered) from corona where Country in ('Australia','Canada') GROUP BY ROLLUP (Country, State_cleaned) order by Country\").show()"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+---------+--------------------+--------------+--------------+\n",
            "|  Country|       State_cleaned|sum(Confirmed)|sum(Recovered)|\n",
            "+---------+--------------------+--------------+--------------+\n",
            "|     null|                null|          1734|            35|\n",
            "|Australia|  Northern Territory|             3|             0|\n",
            "|Australia|     New South Wales|           353|             4|\n",
            "|Australia|          Queensland|           184|             8|\n",
            "|Australia|   Western Australia|            64|             0|\n",
            "|Australia|Australian Capita...|             6|             0|\n",
            "|Australia|                null|           791|            26|\n",
            "|Australia|            Tasmania|            10|             3|\n",
            "|Australia|            Victoria|           121|             8|\n",
            "|Australia|From Diamond Prin...|             0|             0|\n",
            "|Australia|     South Australia|            50|             3|\n",
            "|   Canada|        Saskatchewan|            20|             0|\n",
            "|   Canada|       New Brunswick|            11|             0|\n",
            "|   Canada|      Grand Princess|            10|             0|\n",
            "|   Canada|             Alberta|           146|             0|\n",
            "|   Canada|Newfoundland and ...|             4|             0|\n",
            "|   Canada|    British Columbia|           271|             4|\n",
            "|   Canada|Prince Edward Island|             2|             0|\n",
            "|   Canada|              Quebec|           139|             0|\n",
            "|   Canada|             Ontario|           308|             5|\n",
            "+---------+--------------------+--------------+--------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "lW9BkAbJdicQ",
        "colab_type": "code",
        "outputId": "b471f031-ab14-4dc8-a93e-d41a349bae51",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 490
        }
      },
      "source": [
        "spark.sql(\"select Country, State_cleaned, SUM(Confirmed), SUM(Recovered) from corona where Country in ('Australia','Canada') GROUP BY CUBE (Country, State_cleaned) order by Country\").show()"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+-------+--------------------+--------------+--------------+\n",
            "|Country|       State_cleaned|sum(Confirmed)|sum(Recovered)|\n",
            "+-------+--------------------+--------------+--------------+\n",
            "|   null|            Victoria|           121|             8|\n",
            "|   null|     South Australia|            50|             3|\n",
            "|   null|       New Brunswick|            11|             0|\n",
            "|   null|         Nova Scotia|            15|             0|\n",
            "|   null|     New South Wales|           353|             4|\n",
            "|   null|  Northern Territory|             3|             0|\n",
            "|   null|Australian Capita...|             6|             0|\n",
            "|   null|          Queensland|           184|             8|\n",
            "|   null|        Saskatchewan|            20|             0|\n",
            "|   null|Newfoundland and ...|             4|             0|\n",
            "|   null|                null|          1734|            35|\n",
            "|   null|            Tasmania|            10|             3|\n",
            "|   null|    British Columbia|           271|             4|\n",
            "|   null|             Ontario|           308|             5|\n",
            "|   null|From Diamond Prin...|             0|             0|\n",
            "|   null|            Manitoba|            17|             0|\n",
            "|   null|Prince Edward Island|             2|             0|\n",
            "|   null|      Grand Princess|            10|             0|\n",
            "|   null|             Alberta|           146|             0|\n",
            "|   null|   Western Australia|            64|             0|\n",
            "+-------+--------------------+--------------+--------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "y4IQlwv_djvt",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        ""
      ],
      "execution_count": 0,
      "outputs": []
    }
  ]
}