Spark_Transformations_and_Actions.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "Spark Transformations and Actions.ipynb",
      "provenance": [],
      "collapsed_sections": [],
      "authorship_tag": "ABX9TyMf7wgQ0Pe88ssJeX66Y4yi",
      "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.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-2.4.5/spark-2.4.5-bin-hadoop2.7.tgz\n",
        "#!tar xf spark-2.4.5-bin-hadoop2.7.tgz\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",
        "outputId": "3afeb227-3825-44a1-ea48-be5082f3fe1b",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 54
        }
      },
      "source": [
        "!ls /usr/lib/jvm/"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "default-java\t\t   java-11-openjdk-amd64     java-8-openjdk-amd64\n",
            "java-1.11.0-openjdk-amd64  java-1.8.0-openjdk-amd64\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "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": "6BYTMZszPuj8",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "BASE_DIR = '/tmp'\n",
        "TWITTER_DATA_FILE = os.path.join(BASE_DIR, 'twitter_data.csv')"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Zw3TS2baPuOZ",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "tweet_data=urllib.request.urlretrieve('https://raw.githubusercontent.com/srivatsan88/YouTubeLI/master/dataset/coronavirus/tweets.csv', TWITTER_DATA_FILE)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "qzCJUSOIR-Bp",
        "colab_type": "code",
        "outputId": "2eaead91-710d-461b-f95e-26ad9261578d",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 126
        }
      },
      "source": [
        "!ls /tmp"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "blockmgr-d16dd4f4-c72d-4ca5-9475-4a57ddf0704d\n",
            "corona_data.csv\n",
            "hsperfdata_root\n",
            "spark-acc1d8d9-416b-48a7-8a5e-121b4ad3b887\n",
            "spark-b0521225-70d2-44d1-a96e-48a3e3ea6007\n",
            "twitter_data.csv\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "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": "68692332-058f-40a2-86cc-75db7811cc40",
        "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": "923fdc70-920d-415a-aca3-f0187a5cae0b",
        "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": 13
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "0i5dIR3l_Hks",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "twitter_df = spark.read.option(\"inferSchema\", \"true\").csv(\"/tmp/twitter_data.csv\", header=True)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "m8gJhxqx_Ogk",
        "colab_type": "code",
        "outputId": "b9a8228d-e8da-4e2f-c75e-764fe73d92b2",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 35
        }
      },
      "source": [
        "twitter_df.count()"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "1000"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 15
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "LK1IzQkJ_OYc",
        "colab_type": "code",
        "outputId": "7a63d454-1319-4037-fdec-d2d50f785cdf",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 490
        }
      },
      "source": [
        "twitter_df.show()"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+---+----+--------------------+--------------------+--------------------+--------------------+--------------------+--------------------+\n",
            "|_c0| geo|                text|                user|            location|            entities|           sentiment|             country|\n",
            "+---+----+--------------------+--------------------+--------------------+--------------------+--------------------+--------------------+\n",
            "|  0|null|What is God sayin...|          petodinice|               Lagos|[('about #', 'CAR...|{'neg': 0.0, 'neu...|             Nigeria|\n",
            "|  1|null|\"BREAKING: \"\"this...| but i took the t...|                -… \"|     JerryfranksonJF|      Abuja, Nigeria|\"[(\"\"Arsenal's Mi...|\n",
            "|  2|null| #Coronavirus tes...|              cek422|   Pennsylvania, USA|                  []|{'neg': 0.173, 'n...|                 USA|\n",
            "|  3|null| Get ready for ma...|        InfectiousDz|                 NYC|[('World', 'ORG')...|{'neg': 0.085, 'n...|                 USA|\n",
            "|  4|null| The #coronavirus...|          vic_gibson|                null|                ['']|{'neg': 0.0, 'neu...|                null|\n",
            "|  5|null| COVID-19 update ...|      StewartNgilana|Durban | Port Eli...|[('Italy', 'GPE')...|{'neg': 0.178, 'n...|                 USA|\n",
            "|  6|null| It’s painful to ...|           BWheatnyc|             Florida|                  []|{'neg': 0.098, 'n...|                 USA|\n",
            "|  7|null| 📽️Friends, I wi...|             LorseaR|New South Wales, ...|[('Twitter', 'GPE...|{'neg': 0.123, 'n...|           Australia|\n",
            "|  8|null| Questions about ...|         straightj23|        Columbus, OH|[('NAfME', 'CARDI...|{'neg': 0.0, 'neu...|                 USA|\n",
            "|  9|null|How they’re deali...|       _______coolio| South East, England|['', ('Portsmouth...|{'neg': 0.126, 'n...|                  UK|\n",
            "| 10|null| BREAKING: Democr...|      champagneaylin|                null|[('Democrats', 'N...|{'neg': 0.221, 'n...|                null|\n",
            "| 11|null| “If we close dow...|       YorkLawLondon|                  UK|                  []|{'neg': 0.0, 'neu...|      United Kingdom|\n",
            "| 12|null| I pity the poor ...|      BeesonMargaret|                null|                ['']|{'neg': 0.227, 'n...|                null|\n",
            "| 13|null| We’re the heck w...|      harrytiffanyiv| Fort Washington, PA|[('the last 6 wee...|{'neg': 0.0, 'neu...|                 USA|\n",
            "| 14|null| I don't think sh...|         grammyheath|                null|[('Joy', 'PERSON'...|{'neg': 0.0, 'neu...|                null|\n",
            "| 15|null| Well written, so...|      barbara_ellena|Christchurch City...|                  []|{'neg': 0.0, 'neu...|         New Zealand|\n",
            "| 16|null| 1/2 CDC Director...|               fatal|                 USA|[('1/2', 'CARDINA...|{'neg': 0.139, 'n...|       United States|\n",
            "| 17|null| In all seriousne...|          pwjkmiller|                null|                ['']|{'neg': 0.218, 'n...|                null|\n",
            "| 18|null| Wash your hands....|        Mrrandy123RP|          Texas, USA|            ['', '']|{'neg': 0.0, 'neu...|                 USA|\n",
            "| 19|null| #CoronaVirusCana...|           aliensky0|           Worldwide|['', ('CoronaViru...|{'neg': 0.0, 'neu...|                 USA|\n",
            "+---+----+--------------------+--------------------+--------------------+--------------------+--------------------+--------------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "-aiUCb1W_OND",
        "colab_type": "code",
        "outputId": "4b4f2cfa-7768-42c1-dabd-4b1ddb2d2711",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 35
        }
      },
      "source": [
        "twitter_df.filter(\"country='USA'\")"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "DataFrame[_c0: int, geo: string, text: string, user: string, location: string, entities: string, sentiment: string, country: string]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 17
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "LNewRwAVAj9w",
        "colab_type": "code",
        "outputId": "478d5c53-658d-4501-e9d3-99b2ff8c52e9",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 490
        }
      },
      "source": [
        "twitter_df.filter(\"country='USA'\").show()"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+---+----+--------------------+---------------+--------------------+--------------------+--------------------+-------+\n",
            "|_c0| geo|                text|           user|            location|            entities|           sentiment|country|\n",
            "+---+----+--------------------+---------------+--------------------+--------------------+--------------------+-------+\n",
            "|  2|null| #Coronavirus tes...|         cek422|   Pennsylvania, USA|                  []|{'neg': 0.173, 'n...|    USA|\n",
            "|  3|null| Get ready for ma...|   InfectiousDz|                 NYC|[('World', 'ORG')...|{'neg': 0.085, 'n...|    USA|\n",
            "|  5|null| COVID-19 update ...| StewartNgilana|Durban | Port Eli...|[('Italy', 'GPE')...|{'neg': 0.178, 'n...|    USA|\n",
            "|  6|null| It’s painful to ...|      BWheatnyc|             Florida|                  []|{'neg': 0.098, 'n...|    USA|\n",
            "|  8|null| Questions about ...|    straightj23|        Columbus, OH|[('NAfME', 'CARDI...|{'neg': 0.0, 'neu...|    USA|\n",
            "| 13|null| We’re the heck w...| harrytiffanyiv| Fort Washington, PA|[('the last 6 wee...|{'neg': 0.0, 'neu...|    USA|\n",
            "| 18|null| Wash your hands....|   Mrrandy123RP|          Texas, USA|            ['', '']|{'neg': 0.0, 'neu...|    USA|\n",
            "| 19|null| #CoronaVirusCana...|      aliensky0|           Worldwide|['', ('CoronaViru...|{'neg': 0.0, 'neu...|    USA|\n",
            "| 24|null| Dr. Dena Grayson...|billymocolorad2|         Denver area|[('Dena Grayson',...|{'neg': 0.0, 'neu...|    USA|\n",
            "| 29|null| #BREAKING Univer...|  _pulido_nenaa|         Fontana, CA|['', ('BREAKING U...|{'neg': 0.0, 'neu...|    USA|\n",
            "| 31|null| I ordered Alex J...|       rcgillan|       New York, USA|[('Alex Jones', '...|{'neg': 0.109, 'n...|    USA|\n",
            "| 32|null| #CoronavirusExpe...|        AD1968F|California / Florida|['', ('Coronaviru...|{'neg': 0.0, 'neu...|    USA|\n",
            "| 34|null| There is no obje...|       SFAuGrrl|   San Francisco, CA|['', ('FamiliesFi...|{'neg': 0.224, 'n...|    USA|\n",
            "| 36|null| Before #coronavi...|   Frankapistan|       Nashville, TN|[('China', 'GPE')...|{'neg': 0.144, 'n...|    USA|\n",
            "| 40|null|  When it's actua...|      Happyjam5|     Los Angeles, Ca|[('GOP', 'ORG'), ...|{'neg': 0.215, 'n...|    USA|\n",
            "| 41|null|#Coronavirus in #...|   GregoryGLake|              Global|['', ('5', 'CARDI...|{'neg': 0.318, 'n...|    USA|\n",
            "| 49|null| This week we are...|  JamesWithers3|  New York, New York|[('This week', 'D...|{'neg': 0.0, 'neu...|    USA|\n",
            "| 50|null| Trump is now doi...|      NewNewsey|          everywhere|[('Trump', 'ORG')...|{'neg': 0.0, 'neu...|    USA|\n",
            "| 55|null| The last patient...|   Khattarperla|          Highgarden|  [('Wuhan', 'GPE')]|{'neg': 0.0, 'neu...|    USA|\n",
            "| 56|null|...Hope y’all sta...|  Brandon4Brody|          Pueblo, CO|[('CDC', 'ORG'), ...|{'neg': 0.0, 'neu...|    USA|\n",
            "+---+----+--------------------+---------------+--------------------+--------------------+--------------------+-------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "O5IKyWAN5bBy",
        "colab_type": "code",
        "outputId": "6cf59b5f-eb7c-4242-f812-a72dceb7a86f",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 490
        }
      },
      "source": [
        "twitter_df.filter(\"country='USA' and location like 'New%'\").show()"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+---+----+--------------------+---------------+------------------+--------------------+--------------------+-------+\n",
            "|_c0| geo|                text|           user|          location|            entities|           sentiment|country|\n",
            "+---+----+--------------------+---------------+------------------+--------------------+--------------------+-------+\n",
            "| 31|null| I ordered Alex J...|       rcgillan|     New York, USA|[('Alex Jones', '...|{'neg': 0.109, 'n...|    USA|\n",
            "| 49|null| This week we are...|  JamesWithers3|New York, New York|[('This week', 'D...|{'neg': 0.0, 'neu...|    USA|\n",
            "|228|null|This is a very co...|baskingntheGlow|     New York City|[('hourly', 'TIME')]|{'neg': 0.12, 'ne...|    USA|\n",
            "|238|null|I’m reposting thi...|   Veronicaromm|   New Jersey, USA|[('English', 'LAN...|{'neg': 0.0, 'neu...|    USA|\n",
            "|261|null|Too early ...?  #...|      HJeppesen|      New York, NY|                ['']|{'neg': 0.0, 'neu...|    USA|\n",
            "|275|null|  The Republican ...|  GenetBataiile|New Hampshire, USA|[('Republican', '...|{'neg': 0.0, 'neu...|    USA|\n",
            "|369|null| I still look to ...|   SusanSeaside|       New England|[('SouthKorea', '...|{'neg': 0.0, 'neu...|    USA|\n",
            "|438|null|Can we call the #...|       nilchaks|   New Jersey, USA|                ['']|{'neg': 0.194, 'n...|    USA|\n",
            "|458|null| New deadly #coro...| ssummersabrina|   New Jersey, USA|                ['']|{'neg': 0.236, 'n...|    USA|\n",
            "|471|null|What the Caged Bi...|    AaronNetsky|      New York, NY|                  []|{'neg': 0.0, 'neu...|    USA|\n",
            "|553|null|Me: Tired of hear...|     AmandaB_B8|New Hampshire, USA|['', ('tonight', ...|{'neg': 0.252, 'n...|    USA|\n",
            "|612|null| New deadly #coro...|       klazimi1|     New York, USA|                ['']|{'neg': 0.236, 'n...|    USA|\n",
            "|622|null|Can someone pleas...|         pitaru|          New York|                ['']|{'neg': 0.168, 'n...|    USA|\n",
            "|664|null|Marriage in 2020 ...|           sciz|      New York, NY|[('2020', 'DATE')...|{'neg': 0.111, 'n...|    USA|\n",
            "|679|null|ALL MY MONEY ON E...|  misterauyeung|      New York, NY|[('ETF', 'ORG'), '']|{'neg': 0.148, 'n...|    USA|\n",
            "|724|null|#UniversalStudios...| cosmicbooknews|     New York, USA|['', ('UniversalS...|{'neg': 0.0, 'neu...|    USA|\n",
            "|850|null| There is no obje...|         rm1268|     New York, USA|['', ('FamiliesFi...|{'neg': 0.224, 'n...|    USA|\n",
            "|857|null| #Coronavirus Isr...| itsbenjamindoe|      New York, NY|['', ('Coronaviru...|{'neg': 0.0, 'neu...|    USA|\n",
            "|866|null| What a lousy day...|        insur58|        New Jersey|[('USA', 'GPE'), ...|{'neg': 0.085, 'n...|    USA|\n",
            "|919|null|\" #NEW \"\"Presiden...|      smcapgreg|   New Jersey, USA|[('NEW', 'ORG'), ...|{'neg': 0.167, 'n...|    USA|\n",
            "+---+----+--------------------+---------------+------------------+--------------------+--------------------+-------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "CUONMeGwdYxI",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "tw_filter_df=twitter_df.filter(\"country='USA'\")"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "R4MCw7dheL7F",
        "colab_type": "code",
        "outputId": "221262b2-6226-4b13-e7e8-464d6b7ba7b5",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 146
        }
      },
      "source": [
        "tw_filter_df.explain()"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "== Physical Plan ==\n",
            "*(1) Project [_c0#144, geo#145, text#146, user#147, location#148, entities#149, sentiment#150, country#151]\n",
            "+- *(1) Filter (isnotnull(country#151) AND (country#151 = USA))\n",
            "   +- BatchScan[_c0#144, geo#145, text#146, user#147, location#148, entities#149, sentiment#150, country#151] CSVScan Location: InMemoryFileIndex[file:/tmp/twitter_data.csv], ReadSchema: struct<_c0:int,geo:string,text:string,user:string,location:string,entities:string,sentiment:strin...\n",
            "\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "FTTPszNr588l",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "tw_filter_df=twitter_df.filter(\"country='USA' and location like '%New%'\")"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "w4v5R6il6GKz",
        "colab_type": "code",
        "outputId": "e94d94eb-f2a1-4b1e-e8ed-e15bea3b417a",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 146
        }
      },
      "source": [
        "tw_filter_df.explain()"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "== Physical Plan ==\n",
            "*(1) Project [_c0#144, geo#145, text#146, user#147, location#148, entities#149, sentiment#150, country#151]\n",
            "+- *(1) Filter (((isnotnull(country#151) AND isnotnull(location#148)) AND (country#151 = USA)) AND Contains(location#148, New))\n",
            "   +- BatchScan[_c0#144, geo#145, text#146, user#147, location#148, entities#149, sentiment#150, country#151] CSVScan Location: InMemoryFileIndex[file:/tmp/twitter_data.csv], ReadSchema: struct<_c0:int,geo:string,text:string,user:string,location:string,entities:string,sentiment:strin...\n",
            "\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "SLurJ8G48Pt3",
        "colab_type": "code",
        "outputId": "230b8f77-bf52-4140-cd30-17be49c9049e",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 217
        }
      },
      "source": [
        "tw_filter_df.show(5)"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+---+----+--------------------+---------------+--------------------+--------------------+--------------------+-------+\n",
            "|_c0| geo|                text|           user|            location|            entities|           sentiment|country|\n",
            "+---+----+--------------------+---------------+--------------------+--------------------+--------------------+-------+\n",
            "| 31|null| I ordered Alex J...|       rcgillan|       New York, USA|[('Alex Jones', '...|{'neg': 0.109, 'n...|    USA|\n",
            "| 49|null| This week we are...|  JamesWithers3|  New York, New York|[('This week', 'D...|{'neg': 0.0, 'neu...|    USA|\n",
            "| 62|null|Fear will kill #C...| RobertPPurcell|norther New Jerse...|                ['']|{'neg': 0.798, 'n...|    USA|\n",
            "|228|null|This is a very co...|baskingntheGlow|       New York City|[('hourly', 'TIME')]|{'neg': 0.12, 'ne...|    USA|\n",
            "|238|null|I’m reposting thi...|   Veronicaromm|     New Jersey, USA|[('English', 'LAN...|{'neg': 0.0, 'neu...|    USA|\n",
            "+---+----+--------------------+---------------+--------------------+--------------------+--------------------+-------+\n",
            "only showing top 5 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "NodXrltQdh2E",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "tw_filter_df=twitter_df.filter(col(\"location\").startswith(\"N\"))"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "SUE5FmiqdgnA",
        "colab_type": "code",
        "outputId": "4d3d25e6-ad7a-4c21-84fe-a286f9d3c852",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 146
        }
      },
      "source": [
        "tw_filter_df.explain()"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "== Physical Plan ==\n",
            "*(1) Project [_c0#144, geo#145, text#146, user#147, location#148, entities#149, sentiment#150, country#151]\n",
            "+- *(1) Filter (isnotnull(location#148) AND StartsWith(location#148, N))\n",
            "   +- BatchScan[_c0#144, geo#145, text#146, user#147, location#148, entities#149, sentiment#150, country#151] CSVScan Location: InMemoryFileIndex[file:/tmp/twitter_data.csv], ReadSchema: struct<_c0:int,geo:string,text:string,user:string,location:string,entities:string,sentiment:strin...\n",
            "\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "UC6HEM45e3Sa",
        "colab_type": "code",
        "outputId": "415ddbc6-f151-4cea-9bba-35bca4fbb30e",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 490
        }
      },
      "source": [
        "tw_filter_df.show()"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+---+----+--------------------+---------------+--------------------+--------------------+--------------------+-------------+\n",
            "|_c0| geo|                text|           user|            location|            entities|           sentiment|      country|\n",
            "+---+----+--------------------+---------------+--------------------+--------------------+--------------------+-------------+\n",
            "|  3|null| Get ready for ma...|   InfectiousDz|                 NYC|[('World', 'ORG')...|{'neg': 0.085, 'n...|          USA|\n",
            "|  7|null| 📽️Friends, I wi...|        LorseaR|New South Wales, ...|[('Twitter', 'GPE...|{'neg': 0.123, 'n...|    Australia|\n",
            "| 31|null| I ordered Alex J...|       rcgillan|       New York, USA|[('Alex Jones', '...|{'neg': 0.109, 'n...|          USA|\n",
            "| 36|null| Before #coronavi...|   Frankapistan|       Nashville, TN|[('China', 'GPE')...|{'neg': 0.144, 'n...|          USA|\n",
            "| 49|null| This week we are...|  JamesWithers3|  New York, New York|[('This week', 'D...|{'neg': 0.0, 'neu...|          USA|\n",
            "| 66|null| I am very surpri...|MariosTsoutsouk| Nottingham, England|[('UK', 'GPE'), (...|{'neg': 0.0, 'neu...|           UK|\n",
            "| 79|null| Mr. Jones has be...|       star1965|                 NYC|[('Jones', 'PERSO...|{'neg': 0.0, 'neu...|          USA|\n",
            "|146|null| . launches rapid...|   JessPerry_NJ|                  NJ|['', ('Center for...|{'neg': 0.0, 'neu...|          USA|\n",
            "|163|null| WATCH: The Serbi...|NTexasRealtor4u|         North Texas|[('Serbian', 'NOR...|{'neg': 0.161, 'n...|          USA|\n",
            "|167|null|I dont think we a...|   PitLanePhoto|   Nantwich, England|[('Motorsport', '...|{'neg': 0.0, 'neu...|           UK|\n",
            "|168|null|But seriously I N...|    kuamiufresh|             Nigeria|[('China', 'GPE')...|{'neg': 0.09, 'ne...|      Nigeria|\n",
            "|198|null| This is how the ...|       WINTUR78|           New Delhi|[('Islamophobia',...|{'neg': 0.0, 'neu...|        India|\n",
            "|228|null|This is a very co...|baskingntheGlow|       New York City|[('hourly', 'TIME')]|{'neg': 0.12, 'ne...|          USA|\n",
            "|238|null|I’m reposting thi...|   Veronicaromm|     New Jersey, USA|[('English', 'LAN...|{'neg': 0.0, 'neu...|          USA|\n",
            "|250|null|  The Republican ...|CoronaVirusCall|       North America|[('Republican', '...|{'neg': 0.0, 'neu...|North America|\n",
            "|259|null| New deadly #coro...|TorqueyLunatico|     Not outer space|                ['']|{'neg': 0.236, 'n...|United States|\n",
            "|261|null|Too early ...?  #...|      HJeppesen|        New York, NY|                ['']|{'neg': 0.0, 'neu...|          USA|\n",
            "|275|null|  The Republican ...|  GenetBataiile|  New Hampshire, USA|[('Republican', '...|{'neg': 0.0, 'neu...|          USA|\n",
            "|284|null|Last beer tonight...| organictech551| Northern hemisphere|[('tonight', 'TIM...|{'neg': 0.0, 'neu...|          USA|\n",
            "|285|null| A question for y...| GlynnAtkinson1| North East, England|[('Boris Johnson'...|{'neg': 0.104, 'n...|        Error|\n",
            "+---+----+--------------------+---------------+--------------------+--------------------+--------------------+-------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "jgmzLf-s76vm",
        "colab_type": "code",
        "outputId": "10ace9cf-8011-49c6-da89-0acbdd6936a0",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 35
        }
      },
      "source": [
        "twitter_df"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "DataFrame[_c0: int, geo: string, text: string, user: string, location: string, entities: string, sentiment: string, country: string]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 28
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "nJ-xJv8rvb5Z",
        "colab_type": "code",
        "outputId": "a8d03ef0-d419-41cf-9cee-db80fe53771f",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 55
        }
      },
      "source": [
        "twitter_df.first()"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "Row(_c0=0, geo=None, text='What is God saying to us about #coronavirus ?', user='petodinice', location='Lagos', entities=\"[('about #', 'CARDINAL')]\", sentiment=\"{'neg': 0.0, 'neu': 0.769, 'pos': 0.231, 'compound': 0.2732}\", country='Nigeria')"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 29
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ZjbvTdiAvboK",
        "colab_type": "code",
        "outputId": "f39c0234-abc5-4dff-da2b-8c7deabaa8ee",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 128
        }
      },
      "source": [
        "twitter_df.take(5)"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[Row(_c0=0, geo=None, text='What is God saying to us about #coronavirus ?', user='petodinice', location='Lagos', entities=\"[('about #', 'CARDINAL')]\", sentiment=\"{'neg': 0.0, 'neu': 0.769, 'pos': 0.231, 'compound': 0.2732}\", country='Nigeria'),\n",
              " Row(_c0=1, geo=None, text='\"BREAKING: \"\"this is disappointing', user=' but i took the test\"\". Arsenal\\'s Mikel Arteta tests positive for #coronavirus', location='  -… \"', entities='JerryfranksonJF', sentiment='Abuja, Nigeria', country='\"[(\"\"Arsenal\\'s Mikel Arteta\"\"'),\n",
              " Row(_c0=2, geo=None, text=' #Coronavirus testing must be made free to the public if we are going to understand the scope of this crisis. Anything le…', user='cek422', location='Pennsylvania, USA', entities='[]', sentiment=\"{'neg': 0.173, 'neu': 0.71, 'pos': 0.117, 'compound': -0.3767}\", country='USA'),\n",
              " Row(_c0=3, geo=None, text=' Get ready for mass event crowd cancellations across the World starting this weekend: cricket in #Australia in empty st…', user='InfectiousDz', location='NYC', entities=\"[('World', 'ORG'), ('this weekend', 'DATE'), '']\", sentiment=\"{'neg': 0.085, 'neu': 0.798, 'pos': 0.117, 'compound': 0.1779}\", country='USA'),\n",
              " Row(_c0=4, geo=None, text=' The #coronavirus pandemic is revealing just how closely we are all bound together...[A thread] ', user='vic_gibson', location=None, entities=\"['']\", sentiment=\"{'neg': 0.0, 'neu': 1.0, 'pos': 0.0, 'compound': 0.0}\", country=None)]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 30
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "raOHWiLTpcSb",
        "colab_type": "text"
      },
      "source": [
        "![Transformation](https://drive.google.com/uc?id=1UWFKuyP-ZazKdccGphi7T78ukEW4VjfC)"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "yaJnm3RRAj6u",
        "colab_type": "code",
        "outputId": "1dafd44b-ddb6-48bf-941e-81028f3cefaa",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 54
        }
      },
      "source": [
        "x = spark.sparkContext.parallelize([1,4,8])\n",
        "y = x.flatMap(lambda x: (x, x*x))\n",
        "print(x.collect())\n",
        "print(y.collect())\n"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "[1, 4, 8]\n",
            "[1, 1, 4, 16, 8, 64]\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "RVIy8iEOAj3z",
        "colab_type": "code",
        "outputId": "fde5cc5d-6a9b-4204-f3c3-e4bd00c6a8c6",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 54
        }
      },
      "source": [
        "x = spark.sparkContext.parallelize([1,4,8])\n",
        "y = x.map(lambda x: (x, x*x))\n",
        "print(x.collect())\n",
        "print(y.collect())"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "[1, 4, 8]\n",
            "[(1, 1), (4, 16), (8, 64)]\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "wEdBjdgoottv",
        "colab_type": "code",
        "outputId": "cba714bc-4191-442f-9022-8c21465835f7",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 35
        }
      },
      "source": [
        "x"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "ParallelCollectionRDD[62] at readRDDFromFile at PythonRDD.scala:247"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 33
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "fsAp3bh3A2-I",
        "colab_type": "code",
        "outputId": "dffdf081-1170-455a-c62e-9269c099add8",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 490
        }
      },
      "source": [
        "twitter_df.select(\"text\").show()"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+--------------------+\n",
            "|                text|\n",
            "+--------------------+\n",
            "|What is God sayin...|\n",
            "|\"BREAKING: \"\"this...|\n",
            "| #Coronavirus tes...|\n",
            "| Get ready for ma...|\n",
            "| The #coronavirus...|\n",
            "| COVID-19 update ...|\n",
            "| It’s painful to ...|\n",
            "| 📽️Friends, I wi...|\n",
            "| Questions about ...|\n",
            "|How they’re deali...|\n",
            "| BREAKING: Democr...|\n",
            "| “If we close dow...|\n",
            "| I pity the poor ...|\n",
            "| We’re the heck w...|\n",
            "| I don't think sh...|\n",
            "| Well written, so...|\n",
            "| 1/2 CDC Director...|\n",
            "| In all seriousne...|\n",
            "| Wash your hands....|\n",
            "| #CoronaVirusCana...|\n",
            "+--------------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "BG4pk9gVBGyZ",
        "colab_type": "code",
        "outputId": "7920963c-9a46-4199-bb6b-e7943364789d",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 490
        }
      },
      "source": [
        "twitter_df.select(\"text\",\"user\").show()"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+--------------------+--------------------+\n",
            "|                text|                user|\n",
            "+--------------------+--------------------+\n",
            "|What is God sayin...|          petodinice|\n",
            "|\"BREAKING: \"\"this...| but i took the t...|\n",
            "| #Coronavirus tes...|              cek422|\n",
            "| Get ready for ma...|        InfectiousDz|\n",
            "| The #coronavirus...|          vic_gibson|\n",
            "| COVID-19 update ...|      StewartNgilana|\n",
            "| It’s painful to ...|           BWheatnyc|\n",
            "| 📽️Friends, I wi...|             LorseaR|\n",
            "| Questions about ...|         straightj23|\n",
            "|How they’re deali...|       _______coolio|\n",
            "| BREAKING: Democr...|      champagneaylin|\n",
            "| “If we close dow...|       YorkLawLondon|\n",
            "| I pity the poor ...|      BeesonMargaret|\n",
            "| We’re the heck w...|      harrytiffanyiv|\n",
            "| I don't think sh...|         grammyheath|\n",
            "| Well written, so...|      barbara_ellena|\n",
            "| 1/2 CDC Director...|               fatal|\n",
            "| In all seriousne...|          pwjkmiller|\n",
            "| Wash your hands....|        Mrrandy123RP|\n",
            "| #CoronaVirusCana...|           aliensky0|\n",
            "+--------------------+--------------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "_crRa3cMAj0l",
        "colab_type": "code",
        "outputId": "1df01eb1-2cd1-4da0-a33c-3bc4d545dcef",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 1000
        }
      },
      "source": [
        "twitter_df.rdd.map(lambda line: line.text.split(\" \")).take(5)"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[['What', 'is', 'God', 'saying', 'to', 'us', 'about', '#coronavirus', '?'],\n",
              " ['\"BREAKING:', '\"\"this', 'is', 'disappointing'],\n",
              " ['',\n",
              "  '#Coronavirus',\n",
              "  'testing',\n",
              "  'must',\n",
              "  'be',\n",
              "  'made',\n",
              "  'free',\n",
              "  'to',\n",
              "  'the',\n",
              "  'public',\n",
              "  'if',\n",
              "  'we',\n",
              "  'are',\n",
              "  'going',\n",
              "  'to',\n",
              "  'understand',\n",
              "  'the',\n",
              "  'scope',\n",
              "  'of',\n",
              "  'this',\n",
              "  'crisis.',\n",
              "  'Anything',\n",
              "  'le…'],\n",
              " ['',\n",
              "  'Get',\n",
              "  'ready',\n",
              "  'for',\n",
              "  'mass',\n",
              "  'event',\n",
              "  'crowd',\n",
              "  'cancellations',\n",
              "  'across',\n",
              "  'the',\n",
              "  'World',\n",
              "  'starting',\n",
              "  'this',\n",
              "  'weekend:',\n",
              "  'cricket',\n",
              "  'in',\n",
              "  '#Australia',\n",
              "  'in',\n",
              "  'empty',\n",
              "  'st…'],\n",
              " ['',\n",
              "  'The',\n",
              "  '#coronavirus',\n",
              "  'pandemic',\n",
              "  'is',\n",
              "  'revealing',\n",
              "  'just',\n",
              "  'how',\n",
              "  'closely',\n",
              "  'we',\n",
              "  'are',\n",
              "  'all',\n",
              "  'bound',\n",
              "  'together...[A',\n",
              "  'thread]',\n",
              "  '']]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 36
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "X3Dl9_JPAjvB",
        "colab_type": "code",
        "outputId": "c50b44ba-1f6f-4f81-cbfd-7a5f44e4600a",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 1000
        }
      },
      "source": [
        "twitter_df.rdd.flatMap(lambda line: line.text.split(\" \")).take(100)"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "['What',\n",
              " 'is',\n",
              " 'God',\n",
              " 'saying',\n",
              " 'to',\n",
              " 'us',\n",
              " 'about',\n",
              " '#coronavirus',\n",
              " '?',\n",
              " '\"BREAKING:',\n",
              " '\"\"this',\n",
              " 'is',\n",
              " 'disappointing',\n",
              " '',\n",
              " '#Coronavirus',\n",
              " 'testing',\n",
              " 'must',\n",
              " 'be',\n",
              " 'made',\n",
              " 'free',\n",
              " 'to',\n",
              " 'the',\n",
              " 'public',\n",
              " 'if',\n",
              " 'we',\n",
              " 'are',\n",
              " 'going',\n",
              " 'to',\n",
              " 'understand',\n",
              " 'the',\n",
              " 'scope',\n",
              " 'of',\n",
              " 'this',\n",
              " 'crisis.',\n",
              " 'Anything',\n",
              " 'le…',\n",
              " '',\n",
              " 'Get',\n",
              " 'ready',\n",
              " 'for',\n",
              " 'mass',\n",
              " 'event',\n",
              " 'crowd',\n",
              " 'cancellations',\n",
              " 'across',\n",
              " 'the',\n",
              " 'World',\n",
              " 'starting',\n",
              " 'this',\n",
              " 'weekend:',\n",
              " 'cricket',\n",
              " 'in',\n",
              " '#Australia',\n",
              " 'in',\n",
              " 'empty',\n",
              " 'st…',\n",
              " '',\n",
              " 'The',\n",
              " '#coronavirus',\n",
              " 'pandemic',\n",
              " 'is',\n",
              " 'revealing',\n",
              " 'just',\n",
              " 'how',\n",
              " 'closely',\n",
              " 'we',\n",
              " 'are',\n",
              " 'all',\n",
              " 'bound',\n",
              " 'together...[A',\n",
              " 'thread]',\n",
              " '',\n",
              " '',\n",
              " 'COVID-19',\n",
              " 'update',\n",
              " 'as',\n",
              " 'of',\n",
              " 'this',\n",
              " 'morning:1.',\n",
              " 'Death',\n",
              " 'toll',\n",
              " 'in',\n",
              " 'Italy',\n",
              " 'passes',\n",
              " '1,0002.',\n",
              " \"Arsenal's\",\n",
              " 'head',\n",
              " 'coach',\n",
              " 'Arteta',\n",
              " 'tests',\n",
              " 'positive3.',\n",
              " 'US…',\n",
              " '',\n",
              " 'It’s',\n",
              " 'painful',\n",
              " 'to',\n",
              " 'say,',\n",
              " 'but',\n",
              " 'as',\n",
              " 'an']"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 37
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "MbFsRBucAjka",
        "colab_type": "code",
        "outputId": "9e5c39a4-8427-401d-eea3-e7690135aa29",
        "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": "z4z97_2MmL9W",
        "colab_type": "code",
        "outputId": "c8c8fa69-66a4-4a95-acff-c5f14ca3675e",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 490
        }
      },
      "source": [
        "corona_df.filter(\"Country='US'\").show()"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+---+----------------+-------+------------------+---------+----------+---------+-----+---------+----------------+----------------+\n",
            "|_c0|           State|Country|               Lat|     Long|      Date|Confirmed|Death|Recovered|   state_cleaned|            City|\n",
            "+---+----------------+-------+------------------+---------+----------+---------+-----+---------+----------------+----------------+\n",
            "| 98|      Washington|     US|           47.4009|-121.4905|2020-01-22|        0|    0|        0|      Washington|      Washington|\n",
            "| 99|        New York|     US|           42.1657| -74.9481|2020-01-22|        0|    0|        0|        New York|        New York|\n",
            "|100|      California|     US|           36.1162|-119.6816|2020-01-22|        0|    0|        0|      California|      California|\n",
            "|101|   Massachusetts|     US|           42.2302| -71.5301|2020-01-22|        0|    0|        0|   Massachusetts|   Massachusetts|\n",
            "|102|Diamond Princess|     US|           35.4437|  139.638|2020-01-22|        0|    0|        0|Diamond Princess|Diamond Princess|\n",
            "|103|  Grand Princess|     US|           37.6489|-122.6655|2020-01-22|        0|    0|        0|  Grand Princess|  Grand Princess|\n",
            "|104|         Georgia|     US|           33.0406| -83.6431|2020-01-22|        0|    0|        0|         Georgia|         Georgia|\n",
            "|105|        Colorado|     US|           39.0598|-105.3111|2020-01-22|        0|    0|        0|        Colorado|        Colorado|\n",
            "|106|         Florida|     US|           27.7663| -81.6868|2020-01-22|        0|    0|        0|         Florida|         Florida|\n",
            "|107|      New Jersey|     US|           40.2989|  -74.521|2020-01-22|        0|    0|        0|      New Jersey|      New Jersey|\n",
            "|108|          Oregon|     US|44.571999999999996|-122.0709|2020-01-22|        0|    0|        0|          Oregon|          Oregon|\n",
            "|109|           Texas|     US|           31.0545| -97.5635|2020-01-22|        0|    0|        0|           Texas|           Texas|\n",
            "|110|        Illinois|     US|           40.3495| -88.9861|2020-01-22|        0|    0|        0|        Illinois|        Illinois|\n",
            "|111|    Pennsylvania|     US|           40.5908| -77.2098|2020-01-22|        0|    0|        0|    Pennsylvania|    Pennsylvania|\n",
            "|112|            Iowa|     US|           42.0115| -93.2105|2020-01-22|        0|    0|        0|            Iowa|            Iowa|\n",
            "|113|        Maryland|     US|           39.0639| -76.8021|2020-01-22|        0|    0|        0|        Maryland|        Maryland|\n",
            "|114|  North Carolina|     US|           35.6301| -79.8064|2020-01-22|        0|    0|        0|  North Carolina|  North Carolina|\n",
            "|115|  South Carolina|     US|           33.8569|  -80.945|2020-01-22|        0|    0|        0|  South Carolina|  South Carolina|\n",
            "|116|       Tennessee|     US|           35.7478| -86.6923|2020-01-22|        0|    0|        0|       Tennessee|       Tennessee|\n",
            "|117|        Virginia|     US|           37.7693|   -78.17|2020-01-22|        0|    0|        0|        Virginia|        Virginia|\n",
            "+---+----------------+-------+------------------+---------+----------+---------+-----+---------+----------------+----------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "uCpEtD8mm7zq",
        "colab_type": "code",
        "outputId": "7eae3591-2e33-409e-e041-111aa44ec058",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 490
        }
      },
      "source": [
        "corona_df.filter(\"Country='US'\").sort(col(\"Date\"), ascending=False).show()"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+-----+----------------+-------+------------------+---------+----------+---------+-----+---------+----------------+----------------+\n",
            "|  _c0|           State|Country|               Lat|     Long|      Date|Confirmed|Death|Recovered|   state_cleaned|            City|\n",
            "+-----+----------------+-------+------------------+---------+----------+---------+-----+---------+----------------+----------------+\n",
            "|27764|      Washington|     US|           47.4009|-121.4905|2020-03-20|     1524|   83|        0|      Washington|      Washington|\n",
            "|27784|         Arizona|     US|           33.7298|-111.4312|2020-03-20|       78|    0|        0|         Arizona|         Arizona|\n",
            "|27765|        New York|     US|           42.1657| -74.9481|2020-03-20|     8310|   42|        0|        New York|        New York|\n",
            "|27766|      California|     US|           36.1162|-119.6816|2020-03-20|     1177|   23|        0|      California|      California|\n",
            "|27767|   Massachusetts|     US|           42.2302| -71.5301|2020-03-20|      413|    1|        0|   Massachusetts|   Massachusetts|\n",
            "|27768|Diamond Princess|     US|           35.4437|  139.638|2020-03-20|       49|    0|        0|Diamond Princess|Diamond Princess|\n",
            "|27769|  Grand Princess|     US|           37.6489|-122.6655|2020-03-20|       23|    0|        0|  Grand Princess|  Grand Princess|\n",
            "|27770|         Georgia|     US|           33.0406| -83.6431|2020-03-20|      420|   13|        0|         Georgia|         Georgia|\n",
            "|27771|        Colorado|     US|           39.0598|-105.3111|2020-03-20|      363|    4|        0|        Colorado|        Colorado|\n",
            "|27772|         Florida|     US|           27.7663| -81.6868|2020-03-20|      563|   10|        0|         Florida|         Florida|\n",
            "|27773|      New Jersey|     US|           40.2989|  -74.521|2020-03-20|      890|   11|        0|      New Jersey|      New Jersey|\n",
            "|27774|          Oregon|     US|44.571999999999996|-122.0709|2020-03-20|      114|    3|        0|          Oregon|          Oregon|\n",
            "|27775|           Texas|     US|           31.0545| -97.5635|2020-03-20|      394|    5|        0|           Texas|           Texas|\n",
            "|27776|        Illinois|     US|           40.3495| -88.9861|2020-03-20|      585|    5|        0|        Illinois|        Illinois|\n",
            "|27777|    Pennsylvania|     US|           40.5908| -77.2098|2020-03-20|      303|    1|        0|    Pennsylvania|    Pennsylvania|\n",
            "|27778|            Iowa|     US|           42.0115| -93.2105|2020-03-20|       45|    0|        0|            Iowa|            Iowa|\n",
            "|27779|        Maryland|     US|           39.0639| -76.8021|2020-03-20|      149|    1|        0|        Maryland|        Maryland|\n",
            "|27780|  North Carolina|     US|           35.6301| -79.8064|2020-03-20|      172|    0|        0|  North Carolina|  North Carolina|\n",
            "|27781|  South Carolina|     US|           33.8569|  -80.945|2020-03-20|      126|    1|        0|  South Carolina|  South Carolina|\n",
            "|27782|       Tennessee|     US|           35.7478| -86.6923|2020-03-20|      233|    0|        0|       Tennessee|       Tennessee|\n",
            "+-----+----------------+-------+------------------+---------+----------+---------+-----+---------+----------------+----------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "o5Ng29fypYT7",
        "colab_type": "code",
        "outputId": "bbe94f72-2f57-4431-f663-637cdd880da7",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 490
        }
      },
      "source": [
        "corona_df.filter(\"Country='US'\").orderBy(col(\"Date\"), ascending=False).show()"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+-----+----------------+-------+------------------+---------+----------+---------+-----+---------+----------------+----------------+\n",
            "|  _c0|           State|Country|               Lat|     Long|      Date|Confirmed|Death|Recovered|   state_cleaned|            City|\n",
            "+-----+----------------+-------+------------------+---------+----------+---------+-----+---------+----------------+----------------+\n",
            "|27764|      Washington|     US|           47.4009|-121.4905|2020-03-20|     1524|   83|        0|      Washington|      Washington|\n",
            "|27784|         Arizona|     US|           33.7298|-111.4312|2020-03-20|       78|    0|        0|         Arizona|         Arizona|\n",
            "|27765|        New York|     US|           42.1657| -74.9481|2020-03-20|     8310|   42|        0|        New York|        New York|\n",
            "|27766|      California|     US|           36.1162|-119.6816|2020-03-20|     1177|   23|        0|      California|      California|\n",
            "|27767|   Massachusetts|     US|           42.2302| -71.5301|2020-03-20|      413|    1|        0|   Massachusetts|   Massachusetts|\n",
            "|27768|Diamond Princess|     US|           35.4437|  139.638|2020-03-20|       49|    0|        0|Diamond Princess|Diamond Princess|\n",
            "|27769|  Grand Princess|     US|           37.6489|-122.6655|2020-03-20|       23|    0|        0|  Grand Princess|  Grand Princess|\n",
            "|27770|         Georgia|     US|           33.0406| -83.6431|2020-03-20|      420|   13|        0|         Georgia|         Georgia|\n",
            "|27771|        Colorado|     US|           39.0598|-105.3111|2020-03-20|      363|    4|        0|        Colorado|        Colorado|\n",
            "|27772|         Florida|     US|           27.7663| -81.6868|2020-03-20|      563|   10|        0|         Florida|         Florida|\n",
            "|27773|      New Jersey|     US|           40.2989|  -74.521|2020-03-20|      890|   11|        0|      New Jersey|      New Jersey|\n",
            "|27774|          Oregon|     US|44.571999999999996|-122.0709|2020-03-20|      114|    3|        0|          Oregon|          Oregon|\n",
            "|27775|           Texas|     US|           31.0545| -97.5635|2020-03-20|      394|    5|        0|           Texas|           Texas|\n",
            "|27776|        Illinois|     US|           40.3495| -88.9861|2020-03-20|      585|    5|        0|        Illinois|        Illinois|\n",
            "|27777|    Pennsylvania|     US|           40.5908| -77.2098|2020-03-20|      303|    1|        0|    Pennsylvania|    Pennsylvania|\n",
            "|27778|            Iowa|     US|           42.0115| -93.2105|2020-03-20|       45|    0|        0|            Iowa|            Iowa|\n",
            "|27779|        Maryland|     US|           39.0639| -76.8021|2020-03-20|      149|    1|        0|        Maryland|        Maryland|\n",
            "|27780|  North Carolina|     US|           35.6301| -79.8064|2020-03-20|      172|    0|        0|  North Carolina|  North Carolina|\n",
            "|27781|  South Carolina|     US|           33.8569|  -80.945|2020-03-20|      126|    1|        0|  South Carolina|  South Carolina|\n",
            "|27782|       Tennessee|     US|           35.7478| -86.6923|2020-03-20|      233|    0|        0|       Tennessee|       Tennessee|\n",
            "+-----+----------------+-------+------------------+---------+----------+---------+-----+---------+----------------+----------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "te7cZjPvKnzz",
        "colab_type": "code",
        "outputId": "f4fae6b8-e59d-48b9-f9aa-8e428d1a3865",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 490
        }
      },
      "source": [
        "corona_df.filter(\"Country='US'\").sortWithinPartitions([col(\"Date\"),col(\"Confirmed\")], ascending=False).show()"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+-----+--------------+-------+-------+---------+----------+---------+-----+---------+--------------+--------------+\n",
            "|  _c0|         State|Country|    Lat|     Long|      Date|Confirmed|Death|Recovered| state_cleaned|          City|\n",
            "+-----+--------------+-------+-------+---------+----------+---------+-----+---------+--------------+--------------+\n",
            "|27765|      New York|     US|42.1657| -74.9481|2020-03-20|     8310|   42|        0|      New York|      New York|\n",
            "|27764|    Washington|     US|47.4009|-121.4905|2020-03-20|     1524|   83|        0|    Washington|    Washington|\n",
            "|27766|    California|     US|36.1162|-119.6816|2020-03-20|     1177|   23|        0|    California|    California|\n",
            "|27773|    New Jersey|     US|40.2989|  -74.521|2020-03-20|      890|   11|        0|    New Jersey|    New Jersey|\n",
            "|27776|      Illinois|     US|40.3495| -88.9861|2020-03-20|      585|    5|        0|      Illinois|      Illinois|\n",
            "|27772|       Florida|     US|27.7663| -81.6868|2020-03-20|      563|   10|        0|       Florida|       Florida|\n",
            "|27812|      Michigan|     US|43.3266| -84.5361|2020-03-20|      552|    3|        0|      Michigan|      Michigan|\n",
            "|27804|     Louisiana|     US|31.1695| -91.8678|2020-03-20|      538|   14|        0|     Louisiana|     Louisiana|\n",
            "|27770|       Georgia|     US|33.0406| -83.6431|2020-03-20|      420|   13|        0|       Georgia|       Georgia|\n",
            "|27767| Massachusetts|     US|42.2302| -71.5301|2020-03-20|      413|    1|        0| Massachusetts| Massachusetts|\n",
            "|27775|         Texas|     US|31.0545| -97.5635|2020-03-20|      394|    5|        0|         Texas|         Texas|\n",
            "|27771|      Colorado|     US|39.0598|-105.3111|2020-03-20|      363|    4|        0|      Colorado|      Colorado|\n",
            "|27777|  Pennsylvania|     US|40.5908| -77.2098|2020-03-20|      303|    1|        0|  Pennsylvania|  Pennsylvania|\n",
            "|27782|     Tennessee|     US|35.7478| -86.6923|2020-03-20|      233|    0|        0|     Tennessee|     Tennessee|\n",
            "|27794|     Wisconsin|     US|44.2685| -89.6165|2020-03-20|      207|    3|        0|     Wisconsin|     Wisconsin|\n",
            "|27795|   Connecticut|     US|41.5978| -72.7554|2020-03-20|      194|    3|        0|   Connecticut|   Connecticut|\n",
            "|27792|          Ohio|     US|40.3888| -82.7649|2020-03-20|      173|    2|        0|          Ohio|          Ohio|\n",
            "|27780|North Carolina|     US|35.6301| -79.8064|2020-03-20|      172|    0|        0|North Carolina|North Carolina|\n",
            "|27779|      Maryland|     US|39.0639| -76.8021|2020-03-20|      149|    1|        0|      Maryland|      Maryland|\n",
            "|27781|South Carolina|     US|33.8569|  -80.945|2020-03-20|      126|    1|        0|South Carolina|South Carolina|\n",
            "+-----+--------------+-------+-------+---------+----------+---------+-----+---------+--------------+--------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "tk_FiNnEnbCp",
        "colab_type": "code",
        "outputId": "32298d1c-cf68-4518-91be-3b335fd8916f",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 219
        }
      },
      "source": [
        "corona_df.describe().show()"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+-------+-----------------+---------+-----------+------------------+------------------+----------+------------------+------------------+------------------+-------------+-----------+\n",
            "|summary|              _c0|    State|    Country|               Lat|              Long|      Date|         Confirmed|             Death|         Recovered|state_cleaned|       City|\n",
            "+-------+-----------------+---------+-----------+------------------+------------------+----------+------------------+------------------+------------------+-------------+-----------+\n",
            "|  count|            28143|    19116|      28143|             28143|             28143|     28143|             28143|             28143|             28143|        28143|      14573|\n",
            "|   mean|          14071.0|     null|       null|30.965553459118834|-34.57031257861667|      null|161.88245744945456| 5.494368048893153| 60.17290267562094|         null|       null|\n",
            "| stddev|8124.328649186959|     null|       null|19.365472826597646| 80.78375872452575|      null| 2519.847217725942|109.29475709869875|1346.6597829124426|         null|       null|\n",
            "|    min|                0|Adams, IN|Afghanistan|          -41.4545|         -157.8584|2020-01-22|                 0|                 0|                 0|    Abu Dhabi|      Adams|\n",
            "|    max|            28142| Zhejiang|   Zimbabwe|           71.7069|           178.065|2020-03-20|             67800|              4032|             58382|     erdészet|Yolo County|\n",
            "+-------+-----------------+---------+-----------+------------------+------------------+----------+------------------+------------------+------------------+-------------+-----------+\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "yKK7tAs6nrrC",
        "colab_type": "code",
        "outputId": "73c69730-e7f2-4dd8-af33-67fd17e8ba31",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 254
        }
      },
      "source": [
        "corona_df.printSchema()"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "root\n",
            " |-- _c0: integer (nullable = true)\n",
            " |-- State: string (nullable = true)\n",
            " |-- Country: string (nullable = true)\n",
            " |-- Lat: double (nullable = true)\n",
            " |-- Long: double (nullable = true)\n",
            " |-- Date: string (nullable = true)\n",
            " |-- Confirmed: integer (nullable = true)\n",
            " |-- Death: integer (nullable = true)\n",
            " |-- Recovered: integer (nullable = true)\n",
            " |-- state_cleaned: string (nullable = true)\n",
            " |-- City: string (nullable = true)\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Qj5_GgxwzYjR",
        "colab_type": "code",
        "outputId": "50139f20-9bb4-40e8-e907-30d3c4bd3f8e",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 490
        }
      },
      "source": [
        "corona_df.filter('Confirmed>10000').sort(col('Confirmed')).show()"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+-----+------+-------+-------+--------+----------+---------+-----+---------+-------------+----+\n",
            "|  _c0| State|Country|    Lat|    Long|      Date|Confirmed|Death|Recovered|state_cleaned|City|\n",
            "+-----+------+-------+-------+--------+----------+---------+-----+---------+-------------+----+\n",
            "|24005|  null|   Iran|   32.0|    53.0|2020-03-12|    10075|  429|     2959|       Tehran|null|\n",
            "|22912|  null|  Italy|   43.0|    12.0|2020-03-10|    10149|  631|      724|         Rome|null|\n",
            "|27346|France| France|46.2276|  2.2137|2020-03-19|    10871|  243|       12|       France|null|\n",
            "| 5401| Hubei|  China|30.9756|112.2707|2020-02-02|    11177|  350|      295|        Hubei|null|\n",
            "|24482|  null|   Iran|   32.0|    53.0|2020-03-13|    11364|  514|     2959|       Tehran|null|\n",
            "|26253|  null|  Spain|   40.0|    -4.0|2020-03-17|    11748|  533|     1028|       Toledo|null|\n",
            "|26723|  null|Germany|   51.0|     9.0|2020-03-18|    12327|   28|      105|       Berlin|null|\n",
            "|23866|  null|  Italy|   43.0|    12.0|2020-03-12|    12462|  827|     1045|         Rome|null|\n",
            "|23389|  null|  Italy|   43.0|    12.0|2020-03-11|    12462|  827|     1045|         Rome|null|\n",
            "|27823|France| France|46.2276|  2.2137|2020-03-20|    12612|  450|       12|       France|null|\n",
            "|24959|  null|   Iran|   32.0|    53.0|2020-03-14|    12729|  611|     2959|       Tehran|null|\n",
            "| 5878| Hubei|  China|30.9756|112.2707|2020-02-03|    13522|  414|      386|        Hubei|null|\n",
            "|26730|  null|  Spain|   40.0|    -4.0|2020-03-18|    13910|  623|     1081|       Toledo|null|\n",
            "|25436|  null|   Iran|   32.0|    53.0|2020-03-15|    13938|  724|     4590|       Tehran|null|\n",
            "|25913|  null|   Iran|   32.0|    53.0|2020-03-16|    14991|  853|     4590|       Tehran|null|\n",
            "|27200|  null|Germany|   51.0|     9.0|2020-03-19|    15320|   44|      113|       Berlin|null|\n",
            "|26390|  null|   Iran|   32.0|    53.0|2020-03-17|    16169|  988|     5389|       Tehran|null|\n",
            "| 6355| Hubei|  China|30.9756|112.2707|2020-02-04|    16678|  479|      522|        Hubei|null|\n",
            "|26867|  null|   Iran|   32.0|    53.0|2020-03-18|    17361| 1135|     5389|       Tehran|null|\n",
            "|24343|  null|  Italy|   43.0|    12.0|2020-03-13|    17660| 1266|     1439|         Rome|null|\n",
            "+-----+------+-------+-------+--------+----------+---------+-----+---------+-------------+----+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "kOXCPtwDn1QX",
        "colab_type": "code",
        "outputId": "72d7fe5d-baa7-4fde-bfd6-1e9bdcaa879f",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 35
        }
      },
      "source": [
        "corona_df.filter('Confirmed>10000').approxQuantile('Confirmed',[0.25, 0.5, 0.75, 0.9, 0.95],0.9)"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[10075.0, 10075.0, 10075.0, 10075.0, 67800.0]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 46
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "mdwPSDn41_nE",
        "colab_type": "code",
        "outputId": "ca478104-b015-4d53-9cf1-3cacc03fd846",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 35
        }
      },
      "source": [
        "corona_df.agg({\"Date\":\"max\"}).collect()"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[Row(max(Date)='2020-03-20')]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 47
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "X_GU4UQwojDx",
        "colab_type": "code",
        "outputId": "199366b1-fa27-4c25-ec14-a9ae6152e503",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 35
        }
      },
      "source": [
        "corona_df.agg({\"Date\":\"max\",\"confirmed\":\"max\"}).collect()"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[Row(max(confirmed)=67800, max(Date)='2020-03-20')]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 48
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Rdj3U-corLw6",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "max_date=corona_df.agg({\"Date\":\"max\"})"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "12HIerF9rZ-u",
        "colab_type": "code",
        "outputId": "c5a39dc4-e3c6-4933-89e1-80d6e21a15dc",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 126
        }
      },
      "source": [
        "max_date.show()"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+----------+\n",
            "| max(Date)|\n",
            "+----------+\n",
            "|2020-03-20|\n",
            "+----------+\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "CA1ff0Zzo2gq",
        "colab_type": "code",
        "outputId": "2507e3b6-2152-4791-91b3-5c071a6dae39",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 490
        }
      },
      "source": [
        "import pyspark.sql.functions as F\n",
        "corona_df.groupBy(\"Country\",\"State_cleaned\").agg(F.max(\"Date\")).show()"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+-------------------+--------------------+----------+\n",
            "|            Country|       State_cleaned| max(Date)|\n",
            "+-------------------+--------------------+----------+\n",
            "|        Afghanistan|             Baghran|2020-03-20|\n",
            "|            Albania|                E852|2020-03-20|\n",
            "|            Algeria|            Timokten|2020-03-20|\n",
            "|            Andorra|    Andorra la Vella|2020-03-20|\n",
            "|             Angola|             Sautari|2020-03-20|\n",
            "|Antigua and Barbuda|          St. John's|2020-03-20|\n",
            "|          Argentina|        Buenos Aires|2020-03-20|\n",
            "|            Armenia|             Yerevan|2020-03-20|\n",
            "|          Australia|Australian Capita...|2020-03-20|\n",
            "|          Australia|From Diamond Prin...|2020-03-20|\n",
            "|          Australia|     New South Wales|2020-03-20|\n",
            "|          Australia|  Northern Territory|2020-03-20|\n",
            "|          Australia|          Queensland|2020-03-20|\n",
            "|          Australia|     South Australia|2020-03-20|\n",
            "|          Australia|            Tasmania|2020-03-20|\n",
            "|          Australia|            Victoria|2020-03-20|\n",
            "|          Australia|   Western Australia|2020-03-20|\n",
            "|            Austria|              Vienna|2020-03-20|\n",
            "|         Azerbaijan|            Agjabadi|2020-03-20|\n",
            "|       Bahamas, The|              Nassau|2020-03-20|\n",
            "+-------------------+--------------------+----------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "AQyGu9iXpGHb",
        "colab_type": "code",
        "outputId": "16969145-3669-4b8f-e129-38201227cede",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 490
        }
      },
      "source": [
        "corona_df.join(corona_df.groupBy(\"Country\",\"State_cleaned\").agg(F.max(\"Date\").alias(\"Date\")),on=['Country', 'State_cleaned','Date'],how=\"inner\").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": "QmqFgw9CNyi4",
        "colab_type": "code",
        "outputId": "e0826e7d-00eb-4ad2-edd4-20b98cde2890",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 308
        }
      },
      "source": [
        "corona_df.join(corona_df.groupBy(\"Country\",\"State_cleaned\").agg(F.max(\"Date\").alias(\"Date\")),on=['Country', 'State_cleaned','Date'],how=\"inner\").sort(\"Confirmed\",ascending=False).show(10)"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+--------------+----------------+----------+-----+--------------+-------+-------------------+---------+-----+---------+--------+\n",
            "|       Country|   state_cleaned|      Date|  _c0|         State|    Lat|               Long|Confirmed|Death|Recovered|    City|\n",
            "+--------------+----------------+----------+-----+--------------+-------+-------------------+---------+-----+---------+--------+\n",
            "|         China|           Hubei|2020-03-20|27820|         Hubei|30.9756|           112.2707|    67800| 3133|    58382|    null|\n",
            "|         Italy|            Rome|2020-03-20|27682|          null|   43.0|               12.0|    47021| 4032|     4440|    null|\n",
            "|         Spain|          Toledo|2020-03-20|27684|          null|   40.0|               -4.0|    20410| 1043|     1588|    null|\n",
            "|       Germany|          Berlin|2020-03-20|27677|          null|   51.0|                9.0|    19848|   67|      180|    null|\n",
            "|          Iran|          Tehran|2020-03-20|27821|          null|   32.0|               53.0|    19644| 1433|     6745|    null|\n",
            "|        France|          France|2020-03-20|27823|        France|46.2276|             2.2137|    12612|  450|       12|    null|\n",
            "|  Korea, South|Gyeongsangbuk-do|2020-03-20|27822|          null|   36.0|              128.0|     8652|   94|     1540|    null|\n",
            "|            US|        New York|2020-03-20|27765|      New York|42.1657|           -74.9481|     8310|   42|        0|New York|\n",
            "|   Switzerland|            Bern|2020-03-20|27697|          null|46.8182|             8.2275|     5294|   54|       15|    null|\n",
            "|United Kingdom|  United Kingdom|2020-03-20|28069|United Kingdom|55.3781|-3.4360000000000004|     3983|  177|       65|    null|\n",
            "+--------------+----------------+----------+-----+--------------+-------+-------------------+---------+-----+---------+--------+\n",
            "only showing top 10 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "SNGCViTC4czN",
        "colab_type": "code",
        "outputId": "c0cf252d-7ddd-42ef-b763-83b8e16ad4d1",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 490
        }
      },
      "source": [
        "from pyspark.sql.functions import row_number\n",
        "from pyspark.sql.window import Window\n",
        "\n",
        "ws=Window().partitionBy(\"Country\",\"State_cleaned\").orderBy(col(\"Date\").desc())\n",
        "\n",
        "corona_df.withColumn(\"row_num\", row_number().over(ws)).show()"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+-----+-----+-----------+----+----+----------+---------+-----+---------+-------------+----+-------+\n",
            "|  _c0|State|    Country| Lat|Long|      Date|Confirmed|Death|Recovered|state_cleaned|City|row_num|\n",
            "+-----+-----+-----------+----+----+----------+---------+-----+---------+-------------+----+-------+\n",
            "|27692| null|Afghanistan|33.0|65.0|2020-03-20|       24|    0|        1|      Baghran|null|      1|\n",
            "|27215| null|Afghanistan|33.0|65.0|2020-03-19|       22|    0|        1|      Baghran|null|      2|\n",
            "|26738| null|Afghanistan|33.0|65.0|2020-03-18|       22|    0|        1|      Baghran|null|      3|\n",
            "|26261| null|Afghanistan|33.0|65.0|2020-03-17|       22|    0|        1|      Baghran|null|      4|\n",
            "|25784| null|Afghanistan|33.0|65.0|2020-03-16|       21|    0|        1|      Baghran|null|      5|\n",
            "|25307| null|Afghanistan|33.0|65.0|2020-03-15|       16|    0|        0|      Baghran|null|      6|\n",
            "|24830| null|Afghanistan|33.0|65.0|2020-03-14|       11|    0|        0|      Baghran|null|      7|\n",
            "|24353| null|Afghanistan|33.0|65.0|2020-03-13|        7|    0|        0|      Baghran|null|      8|\n",
            "|23876| null|Afghanistan|33.0|65.0|2020-03-12|        7|    0|        0|      Baghran|null|      9|\n",
            "|23399| null|Afghanistan|33.0|65.0|2020-03-11|        7|    0|        0|      Baghran|null|     10|\n",
            "|22922| null|Afghanistan|33.0|65.0|2020-03-10|        5|    0|        0|      Baghran|null|     11|\n",
            "|22445| null|Afghanistan|33.0|65.0|2020-03-09|        4|    0|        0|      Baghran|null|     12|\n",
            "|21968| null|Afghanistan|33.0|65.0|2020-03-08|        4|    0|        0|      Baghran|null|     13|\n",
            "|21491| null|Afghanistan|33.0|65.0|2020-03-07|        1|    0|        0|      Baghran|null|     14|\n",
            "|21014| null|Afghanistan|33.0|65.0|2020-03-06|        1|    0|        0|      Baghran|null|     15|\n",
            "|20537| null|Afghanistan|33.0|65.0|2020-03-05|        1|    0|        0|      Baghran|null|     16|\n",
            "|20060| null|Afghanistan|33.0|65.0|2020-03-04|        1|    0|        0|      Baghran|null|     17|\n",
            "|19583| null|Afghanistan|33.0|65.0|2020-03-03|        1|    0|        0|      Baghran|null|     18|\n",
            "|19106| null|Afghanistan|33.0|65.0|2020-03-02|        1|    0|        0|      Baghran|null|     19|\n",
            "|18629| null|Afghanistan|33.0|65.0|2020-03-01|        1|    0|        0|      Baghran|null|     20|\n",
            "+-----+-----+-----------+----+----+----------+---------+-----+---------+-------------+----+-------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "00CUroHn510O",
        "colab_type": "code",
        "outputId": "02044c1f-59cc-4feb-8943-d83f675c2aca",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 490
        }
      },
      "source": [
        "corona_df.withColumn(\"row_num\", row_number().over(ws)).where(col(\"row_num\")==1).show()"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+-----+--------------------+-------------------+--------+--------+----------+---------+-----+---------+--------------------+----+-------+\n",
            "|  _c0|               State|            Country|     Lat|    Long|      Date|Confirmed|Death|Recovered|       state_cleaned|City|row_num|\n",
            "+-----+--------------------+-------------------+--------+--------+----------+---------+-----+---------+--------------------+----+-------+\n",
            "|27692|                null|        Afghanistan|    33.0|    65.0|2020-03-20|       24|    0|        1|             Baghran|null|      1|\n",
            "|27761|                null|            Albania| 41.1533| 20.1683|2020-03-20|       70|    2|        0|                E852|null|      1|\n",
            "|27695|                null|            Algeria| 28.0339|  1.6596|2020-03-20|       90|   11|       32|            Timokten|null|      1|\n",
            "|27726|                null|            Andorra| 42.5063|  1.5218|2020-03-20|       75|    0|        1|    Andorra la Vella|null|      1|\n",
            "|28136|                null|             Angola|-11.2027| 17.8739|2020-03-20|        1|    0|        0|             Sautari|null|      1|\n",
            "|28079|                null|Antigua and Barbuda| 17.0608|-61.7964|2020-03-20|        1|    0|        0|          St. John's|null|      1|\n",
            "|27732|                null|          Argentina|-38.4161|-63.6167|2020-03-20|      128|    3|        3|        Buenos Aires|null|      1|\n",
            "|27722|                null|            Armenia| 40.0691| 45.0382|2020-03-20|      136|    0|        1|             Yerevan|null|      1|\n",
            "|28068|Australian Capita...|          Australia|-35.4735|149.0124|2020-03-20|        6|    0|        0|Australian Capita...|null|      1|\n",
            "|27688|From Diamond Prin...|          Australia| 35.4437| 139.638|2020-03-20|        0|    0|        0|From Diamond Prin...|null|      1|\n",
            "|27672|     New South Wales|          Australia|-33.8688|151.2093|2020-03-20|      353|    6|        4|     New South Wales|null|      1|\n",
            "|27737|  Northern Territory|          Australia|-12.4634|130.8456|2020-03-20|        3|    0|        0|  Northern Territory|null|      1|\n",
            "|27674|          Queensland|          Australia|-28.0167|   153.4|2020-03-20|      184|    0|        8|          Queensland|null|      1|\n",
            "|27685|     South Australia|          Australia|-34.9285|138.6007|2020-03-20|       50|    0|        3|     South Australia|null|      1|\n",
            "|27727|            Tasmania|          Australia|-41.4545|145.9707|2020-03-20|       10|    0|        3|            Tasmania|null|      1|\n",
            "|27673|            Victoria|          Australia|-37.8136|144.9631|2020-03-20|      121|    0|        8|            Victoria|null|      1|\n",
            "|27715|   Western Australia|          Australia|-31.9505|115.8605|2020-03-20|       64|    1|        0|   Western Australia|null|      1|\n",
            "|27698|                null|            Austria| 47.5162| 14.5501|2020-03-20|     2388|    6|        9|              Vienna|null|      1|\n",
            "|27721|                null|         Azerbaijan| 40.1431| 47.5769|2020-03-20|       44|    1|        6|            Agjabadi|null|      1|\n",
            "|28126|                null|       Bahamas, The| 25.0343|-77.3963|2020-03-20|        3|    0|        0|              Nassau|null|      1|\n",
            "+-----+--------------------+-------------------+--------+--------+----------+---------+-----+---------+--------------------+----+-------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "2U5RD8Hj0eu6",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "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": "_S1RSmFE2RBh",
        "colab_type": "code",
        "outputId": "9ef1335e-248d-4a43-ea2b-6ee5fbc77965",
        "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": "RIm5bTc67a0B",
        "colab_type": "code",
        "outputId": "58155a7d-92e8-4266-c294-2d830ece797b",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 510
        }
      },
      "source": [
        "corona_df.groupby(\"Country\").pivot(\"Date\").agg(F.sum(\"Confirmed\")).show()"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+--------------------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+\n",
            "|             Country|2020-01-22|2020-01-23|2020-01-24|2020-01-25|2020-01-26|2020-01-27|2020-01-28|2020-01-29|2020-01-30|2020-01-31|2020-02-01|2020-02-02|2020-02-03|2020-02-04|2020-02-05|2020-02-06|2020-02-07|2020-02-08|2020-02-09|2020-02-10|2020-02-11|2020-02-12|2020-02-13|2020-02-14|2020-02-15|2020-02-16|2020-02-17|2020-02-18|2020-02-19|2020-02-20|2020-02-21|2020-02-22|2020-02-23|2020-02-24|2020-02-25|2020-02-26|2020-02-27|2020-02-28|2020-02-29|2020-03-01|2020-03-02|2020-03-03|2020-03-04|2020-03-05|2020-03-06|2020-03-07|2020-03-08|2020-03-09|2020-03-10|2020-03-11|2020-03-12|2020-03-13|2020-03-14|2020-03-15|2020-03-16|2020-03-17|2020-03-18|2020-03-19|2020-03-20|\n",
            "+--------------------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+\n",
            "|            Thailand|         2|         3|         5|         7|         8|         8|        14|        14|        14|        19|        19|        19|        19|        25|        25|        25|        25|        32|        32|        32|        33|        33|        33|        33|        33|        34|        35|        35|        35|        35|        35|        35|        35|        35|        37|        40|        40|        41|        42|        42|        43|        43|        43|        47|        48|        50|        50|        50|        53|        59|        70|        75|        82|       114|       147|       177|       212|       272|       322|\n",
            "|               Japan|         2|         1|         2|         2|         4|         4|         7|         7|        11|        15|        20|        20|        20|        22|        22|        45|        25|        25|        26|        26|        26|        28|        28|        29|        43|        59|        66|        74|        84|        94|       105|       122|       147|       159|       170|       189|       214|       228|       241|       256|       274|       293|       331|       360|       420|       461|       502|       511|       581|       639|       639|       701|       773|       839|       825|       878|       889|       924|       963|\n",
            "|           Singapore|         0|         1|         3|         3|         4|         5|         7|         7|        10|        13|        16|        18|        18|        24|        28|        28|        30|        33|        40|        45|        47|        50|        58|        67|        72|        75|        77|        81|        84|        84|        85|        85|        89|        89|        91|        93|        93|        93|       102|       106|       108|       110|       110|       117|       130|       138|       150|       150|       160|       178|       178|       200|       212|       226|       243|       266|       313|       345|       385|\n",
            "|               Nepal|         0|         0|         0|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|\n",
            "|            Malaysia|         0|         0|         0|         3|         4|         4|         4|         7|         8|         8|         8|         8|         8|        10|        12|        12|        12|        16|        16|        18|        18|        18|        19|        19|        22|        22|        22|        22|        22|        22|        22|        22|        22|        22|        22|        22|        23|        23|        25|        29|        29|        36|        50|        50|        83|        93|        99|       117|       129|       149|       149|       197|       238|       428|       566|       673|       790|       900|      1030|\n",
            "|              Canada|         0|         0|         0|         0|         1|         1|         2|         2|         2|         4|         4|         4|         4|         4|         5|         5|         7|         7|         7|         7|         7|         7|         7|         7|         7|         7|         8|         8|         8|         8|         9|         9|         9|        10|        11|        11|        13|        14|        20|        24|        27|        30|        33|        37|        49|        54|        64|        77|        79|       108|       117|       193|       198|       252|       415|       478|       657|       800|       943|\n",
            "|           Australia|         0|         0|         0|         0|         4|         5|         5|         6|         9|         9|        12|        12|        12|        13|        13|        14|        15|        15|        15|        15|        15|        15|        15|        15|        15|        15|        15|        15|        15|        15|        19|        22|        22|        22|        22|        22|        23|        23|        25|        27|        30|        39|        52|        55|        60|        63|        76|        91|       107|       128|       128|       200|       250|       297|       377|       452|       568|       681|       791|\n",
            "|            Cambodia|         0|         0|         0|         0|         0|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         2|         2|         2|         3|         3|         5|         7|         7|         7|        33|        35|        37|        51|\n",
            "|           Sri Lanka|         0|         0|         0|         0|         0|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         2|         2|         6|        10|        18|        28|        44|        51|        60|        73|\n",
            "|             Germany|         0|         0|         0|         0|         0|         1|         4|         4|         4|         5|         8|        10|        12|        12|        12|        12|        13|        13|        14|        14|        16|        16|        16|        16|        16|        16|        16|        16|        16|        16|        16|        16|        16|        16|        17|        27|        46|        48|        79|       130|       159|       196|       262|       482|       670|       799|      1040|      1176|      1457|      1908|      2078|      3675|      4585|      5795|      7272|      9257|     12327|     15320|     19848|\n",
            "|             Finland|         0|         0|         0|         0|         0|         0|         0|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         2|         2|         2|         3|         6|         6|         6|         6|        12|        15|        15|        23|        30|        40|        59|        59|       155|       225|       244|       277|       321|       336|       400|       450|\n",
            "|United Arab Emirates|         0|         0|         0|         0|         0|         0|         0|         4|         4|         4|         4|         5|         5|         5|         5|         5|         5|         7|         7|         8|         8|         8|         8|         8|         8|         9|         9|         9|         9|         9|         9|        13|        13|        13|        13|        13|        13|        19|        21|        21|        21|        27|        27|        29|        29|        45|        45|        45|        74|        74|        85|        85|        85|        98|        98|        98|       113|       140|       140|\n",
            "|         Philippines|         0|         0|         0|         0|         0|         0|         0|         0|         1|         1|         1|         2|         2|         2|         2|         2|         3|         3|         3|         3|         3|         3|         3|         3|         3|         3|         3|         3|         3|         3|         3|         3|         3|         3|         3|         3|         3|         3|         3|         3|         3|         3|         3|         3|         5|         6|        10|        20|        33|        49|        52|        64|       111|       140|       142|       187|       202|       217|       230|\n",
            "|               India|         0|         0|         0|         0|         0|         0|         0|         0|         1|         1|         1|         2|         3|         3|         3|         3|         3|         3|         3|         3|         3|         3|         3|         3|         3|         3|         3|         3|         3|         3|         3|         3|         3|         3|         3|         3|         3|         3|         3|         3|         5|         5|        28|        30|        31|        34|        39|        43|        56|        62|        73|        82|       102|       113|       119|       142|       156|       194|       244|\n",
            "|               Italy|         0|         0|         0|         0|         0|         0|         0|         0|         0|         2|         2|         2|         2|         2|         2|         2|         3|         3|         3|         3|         3|         3|         3|         3|         3|         3|         3|         3|         3|         3|        20|        62|       155|       229|       322|       453|       655|       888|      1128|      1694|      2036|      2502|      3089|      3858|      4636|      5883|      7375|      9172|     10149|     12462|     12462|     17660|     21157|     24747|     27980|     31506|     35713|     41035|     47021|\n",
            "|              Sweden|         0|         0|         0|         0|         0|         0|         0|         0|         0|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         2|         7|         7|        12|        14|        15|        21|        35|        94|       101|       161|       203|       248|       355|       500|       599|       814|       961|      1022|      1103|      1190|      1279|      1439|      1639|\n",
            "|               Spain|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         1|         1|         1|         1|         1|         1|         1|         1|         2|         2|         2|         2|         2|         2|         2|         2|         2|         2|         2|         2|         2|         2|         2|         2|         6|        13|        15|        32|        45|        84|       120|       165|       222|       259|       400|       500|       673|      1073|      1695|      2277|      2277|      5232|      6391|      7798|      9942|     11748|     13910|     17963|     20410|\n",
            "|             Belgium|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         2|         8|        13|        23|        50|       109|       169|       200|       239|       267|       314|       314|       559|       689|       886|      1058|      1243|      1486|      1795|      2257|\n",
            "|               Egypt|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         2|         2|         2|         2|         3|        15|        15|        49|        55|        59|        60|        67|        80|       109|       110|       150|       196|       196|       256|       285|\n",
            "|             Lebanon|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         1|         1|         1|         1|         1|         2|         2|         2|         4|        10|        13|        13|        13|        16|        22|        22|        32|        32|        41|        61|        61|        77|        93|       110|        99|       120|       133|       157|       163|\n",
            "+--------------------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ew6ajciv7Iy6",
        "colab_type": "code",
        "outputId": "4c519852-dc1b-49b3-df36-e6fb1507bb9c",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 510
        }
      },
      "source": [
        "corona_df.filter(\"Country=='US'\").crosstab(\"State\",\"Date\").show()"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+-------------------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+\n",
            "|         State_Date|2020-01-22|2020-01-23|2020-01-24|2020-01-25|2020-01-26|2020-01-27|2020-01-28|2020-01-29|2020-01-30|2020-01-31|2020-02-01|2020-02-02|2020-02-03|2020-02-04|2020-02-05|2020-02-06|2020-02-07|2020-02-08|2020-02-09|2020-02-10|2020-02-11|2020-02-12|2020-02-13|2020-02-14|2020-02-15|2020-02-16|2020-02-17|2020-02-18|2020-02-19|2020-02-20|2020-02-21|2020-02-22|2020-02-23|2020-02-24|2020-02-25|2020-02-26|2020-02-27|2020-02-28|2020-02-29|2020-03-01|2020-03-02|2020-03-03|2020-03-04|2020-03-05|2020-03-06|2020-03-07|2020-03-08|2020-03-09|2020-03-10|2020-03-11|2020-03-12|2020-03-13|2020-03-14|2020-03-15|2020-03-16|2020-03-17|2020-03-18|2020-03-19|2020-03-20|\n",
            "+-------------------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+\n",
            "|         New Mexico|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|\n",
            "|   Clark County, NV|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|\n",
            "|         Summit, UT|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|\n",
            "|     Montgomery, TX|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|\n",
            "|           Dane, WI|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|\n",
            "|Prince George's, MD|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|\n",
            "|Maricopa County, AZ|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|\n",
            "|  Harris County, TX|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|\n",
            "|     New Castle, DE|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|\n",
            "|      Deschutes, OR|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|\n",
            "|        Connecticut|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|\n",
            "|          Gregg, TX|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|\n",
            "|    Wake County, NC|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|\n",
            "|          Boone, IN|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|\n",
            "|         Beadle, SD|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|\n",
            "| Johnson County, IA|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|\n",
            "|   Tulsa County, OK|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|\n",
            "|         California|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|\n",
            "|  Collin County, TX|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|\n",
            "|       Charlton, GA|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|\n",
            "+-------------------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "FI7SWknD811b",
        "colab_type": "code",
        "outputId": "eaea23eb-e3e5-46d1-e190-06d8dae916bb",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 490
        }
      },
      "source": [
        "corona_max_df.groupBy(\"Country\").agg({'Confirmed':'sum'}).show()"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+--------------------+--------------+\n",
            "|             Country|sum(Confirmed)|\n",
            "+--------------------+--------------+\n",
            "|            Thailand|           322|\n",
            "|               Japan|           963|\n",
            "|           Singapore|           385|\n",
            "|               Nepal|             1|\n",
            "|            Malaysia|          1030|\n",
            "|              Canada|           943|\n",
            "|           Australia|           791|\n",
            "|            Cambodia|            51|\n",
            "|           Sri Lanka|            73|\n",
            "|             Germany|         19848|\n",
            "|             Finland|           450|\n",
            "|United Arab Emirates|           140|\n",
            "|         Philippines|           230|\n",
            "|               India|           244|\n",
            "|               Italy|         47021|\n",
            "|              Sweden|          1639|\n",
            "|               Spain|         20410|\n",
            "|             Belgium|          2257|\n",
            "|               Egypt|           285|\n",
            "|             Lebanon|           163|\n",
            "+--------------------+--------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "k5J5yFA6B_f3",
        "colab_type": "code",
        "outputId": "470500ec-9c18-4864-a44c-cafb83bd8d38",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 490
        }
      },
      "source": [
        "corona_max_df.groupBy(\"Country\").agg({'Confirmed':'sum','Recovered':'sum','Death':'sum'}).orderBy(\"sum(Confirmed)\", ascending=False).show()"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+--------------+--------------+----------+--------------+\n",
            "|       Country|sum(Recovered)|sum(Death)|sum(Confirmed)|\n",
            "+--------------+--------------+----------+--------------+\n",
            "|         China|         71266|      3253|         81250|\n",
            "|         Italy|          4440|      4032|         47021|\n",
            "|         Spain|          1588|      1043|         20410|\n",
            "|       Germany|           180|        67|         19848|\n",
            "|          Iran|          6745|      1433|         19644|\n",
            "|            US|             0|       244|         19100|\n",
            "|        France|            12|       450|         12726|\n",
            "|  Korea, South|          1540|        94|          8652|\n",
            "|   Switzerland|            15|        54|          5294|\n",
            "|United Kingdom|            67|       178|          4014|\n",
            "|   Netherlands|             2|       107|          3003|\n",
            "|       Austria|             9|         6|          2388|\n",
            "|       Belgium|             1|        37|          2257|\n",
            "|        Norway|             1|         7|          1914|\n",
            "|        Sweden|            16|        16|          1639|\n",
            "|       Denmark|             1|         9|          1337|\n",
            "|      Malaysia|            87|         3|          1030|\n",
            "|      Portugal|             5|         6|          1020|\n",
            "|         Japan|           191|        33|           963|\n",
            "|        Canada|             9|        12|           943|\n",
            "+--------------+--------------+----------+--------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "oO5PydSsCLSc",
        "colab_type": "code",
        "outputId": "dfec5261-7803-4dc8-9d8b-53c96f2a328b",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 490
        }
      },
      "source": [
        "corona_df.filter(\"Country=='Italy'\").sort(\"Date\",ascending=False).show()"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+-----+-----+-------+----+----+----------+---------+-----+---------+-------------+----+\n",
            "|  _c0|State|Country| Lat|Long|      Date|Confirmed|Death|Recovered|state_cleaned|City|\n",
            "+-----+-----+-------+----+----+----------+---------+-----+---------+-------------+----+\n",
            "|27682| null|  Italy|43.0|12.0|2020-03-20|    47021| 4032|     4440|         Rome|null|\n",
            "|27205| null|  Italy|43.0|12.0|2020-03-19|    41035| 3405|     4440|         Rome|null|\n",
            "|26728| null|  Italy|43.0|12.0|2020-03-18|    35713| 2978|     4025|         Rome|null|\n",
            "|26251| null|  Italy|43.0|12.0|2020-03-17|    31506| 2503|     2941|         Rome|null|\n",
            "|25774| null|  Italy|43.0|12.0|2020-03-16|    27980| 2158|     2749|         Rome|null|\n",
            "|25297| null|  Italy|43.0|12.0|2020-03-15|    24747| 1809|     2335|         Rome|null|\n",
            "|24820| null|  Italy|43.0|12.0|2020-03-14|    21157| 1441|     1966|         Rome|null|\n",
            "|24343| null|  Italy|43.0|12.0|2020-03-13|    17660| 1266|     1439|         Rome|null|\n",
            "|23866| null|  Italy|43.0|12.0|2020-03-12|    12462|  827|     1045|         Rome|null|\n",
            "|23389| null|  Italy|43.0|12.0|2020-03-11|    12462|  827|     1045|         Rome|null|\n",
            "|22912| null|  Italy|43.0|12.0|2020-03-10|    10149|  631|      724|         Rome|null|\n",
            "|22435| null|  Italy|43.0|12.0|2020-03-09|     9172|  463|      724|         Rome|null|\n",
            "|21958| null|  Italy|43.0|12.0|2020-03-08|     7375|  366|      622|         Rome|null|\n",
            "|21481| null|  Italy|43.0|12.0|2020-03-07|     5883|  233|      589|         Rome|null|\n",
            "|21004| null|  Italy|43.0|12.0|2020-03-06|     4636|  197|      523|         Rome|null|\n",
            "|20527| null|  Italy|43.0|12.0|2020-03-05|     3858|  148|      414|         Rome|null|\n",
            "|20050| null|  Italy|43.0|12.0|2020-03-04|     3089|  107|      276|         Rome|null|\n",
            "|19573| null|  Italy|43.0|12.0|2020-03-03|     2502|   79|      160|         Rome|null|\n",
            "|19096| null|  Italy|43.0|12.0|2020-03-02|     2036|   52|      149|         Rome|null|\n",
            "|18619| null|  Italy|43.0|12.0|2020-03-01|     1694|   34|       83|         Rome|null|\n",
            "+-----+-----+-------+----+----+----------+---------+-----+---------+-------------+----+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "lxdIMq36DtXm",
        "colab_type": "code",
        "outputId": "632ef28b-0d17-4461-85df-32776de496be",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 490
        }
      },
      "source": [
        "corona_max_df.withColumn(\"Active\",corona_max_df.Confirmed - corona_max_df.Recovered - corona_max_df.Death).sort(\"Active\",ascending=False).show()"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+--------------+----------------+----------+-----+--------------+-------+-------------------+---------+-----+---------+----------+------+\n",
            "|       Country|   state_cleaned|      Date|  _c0|         State|    Lat|               Long|Confirmed|Death|Recovered|      City|Active|\n",
            "+--------------+----------------+----------+-----+--------------+-------+-------------------+---------+-----+---------+----------+------+\n",
            "|         Italy|            Rome|2020-03-20|27682|          null|   43.0|               12.0|    47021| 4032|     4440|      null| 38549|\n",
            "|       Germany|          Berlin|2020-03-20|27677|          null|   51.0|                9.0|    19848|   67|      180|      null| 19601|\n",
            "|         Spain|          Toledo|2020-03-20|27684|          null|   40.0|               -4.0|    20410| 1043|     1588|      null| 17779|\n",
            "|        France|          France|2020-03-20|27823|        France|46.2276|             2.2137|    12612|  450|       12|      null| 12150|\n",
            "|          Iran|          Tehran|2020-03-20|27821|          null|   32.0|               53.0|    19644| 1433|     6745|      null| 11466|\n",
            "|            US|        New York|2020-03-20|27765|      New York|42.1657|           -74.9481|     8310|   42|        0|  New York|  8268|\n",
            "|  Korea, South|Gyeongsangbuk-do|2020-03-20|27822|          null|   36.0|              128.0|     8652|   94|     1540|      null|  7018|\n",
            "|         China|           Hubei|2020-03-20|27820|         Hubei|30.9756|           112.2707|    67800| 3133|    58382|      null|  6285|\n",
            "|   Switzerland|            Bern|2020-03-20|27697|          null|46.8182|             8.2275|     5294|   54|       15|      null|  5225|\n",
            "|United Kingdom|  United Kingdom|2020-03-20|28069|United Kingdom|55.3781|-3.4360000000000004|     3983|  177|       65|      null|  3741|\n",
            "|   Netherlands|     Netherlands|2020-03-20|28106|   Netherlands|52.1326|             5.2913|     2994|  106|        2|      null|  2886|\n",
            "|       Austria|          Vienna|2020-03-20|27698|          null|47.5162|            14.5501|     2388|    6|        9|      null|  2373|\n",
            "|       Belgium|        Brussels|2020-03-20|27686|          null|50.8333|                4.0|     2257|   37|        1|      null|  2219|\n",
            "|        Norway|             Hol|2020-03-20|27705|          null| 60.472|             8.4689|     1914|    7|        1|      null|  1906|\n",
            "|        Sweden|       Stockholm|2020-03-20|27683|          null|   63.0|               16.0|     1639|   16|       16|      null|  1607|\n",
            "|            US|      Washington|2020-03-20|27764|    Washington|47.4009|          -121.4905|     1524|   83|        0|Washington|  1441|\n",
            "|       Denmark|         Denmark|2020-03-20|27836|       Denmark|56.2639|             9.5018|     1255|    9|        1|      null|  1245|\n",
            "|            US|      California|2020-03-20|27766|    California|36.1162|          -119.6816|     1177|   23|        0|California|  1154|\n",
            "|      Portugal|          Lisbon|2020-03-20|27725|          null|39.3999|            -8.2245|     1020|    6|        5|      null|  1009|\n",
            "|      Malaysia|         Sarawak|2020-03-20|27670|          null|    2.5|              112.5|     1030|    3|       87|      null|   940|\n",
            "+--------------+----------------+----------+-----+--------------+-------+-------------------+---------+-----+---------+----------+------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "IjwFC3pPSqNj",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "corona_max_df=corona_max_df.withColumn(\"Active\",corona_max_df.Confirmed - corona_max_df.Recovered - corona_max_df.Death)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "zq58lG3LWQ9_",
        "colab_type": "code",
        "outputId": "a54216ef-b18f-4a15-f1d7-53c042ae993a",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 308
        }
      },
      "source": [
        "corona_max_df.show(10)"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+---------+----------------+----------+-----+----------------+--------+---------+---------+-----+---------+----+------+\n",
            "|  Country|   state_cleaned|      Date|  _c0|           State|     Lat|     Long|Confirmed|Death|Recovered|City|Active|\n",
            "+---------+----------------+----------+-----+----------------+--------+---------+---------+-----+---------+----+------+\n",
            "| Thailand|         Bangkok|2020-03-20|27666|            null|    15.0|    101.0|      322|    1|       42|null|   279|\n",
            "|    Japan|         Hiraide|2020-03-20|27667|            null|    36.0|    138.0|      963|   33|      191|null|   739|\n",
            "|Singapore|       Singapore|2020-03-20|27668|            null|  1.2833| 103.8333|      385|    0|      124|null|   261|\n",
            "|    Nepal|       Kathmandu|2020-03-20|27669|            null| 28.1667|    84.25|        1|    0|        1|null|     0|\n",
            "| Malaysia|         Sarawak|2020-03-20|27670|            null|     2.5|    112.5|     1030|    3|       87|null|   940|\n",
            "|   Canada|British Columbia|2020-03-20|27671|British Columbia| 49.2827|-123.1207|      271|    8|        4|null|   259|\n",
            "|Australia| New South Wales|2020-03-20|27672| New South Wales|-33.8688| 151.2093|      353|    6|        4|null|   343|\n",
            "|Australia|        Victoria|2020-03-20|27673|        Victoria|-37.8136| 144.9631|      121|    0|        8|null|   113|\n",
            "|Australia|      Queensland|2020-03-20|27674|      Queensland|-28.0167|    153.4|      184|    0|        8|null|   176|\n",
            "| Cambodia|      Phnom Penh|2020-03-20|27675|            null|   11.55| 104.9167|       51|    0|        1|null|    50|\n",
            "+---------+----------------+----------+-----+----------------+--------+---------+---------+-----+---------+----+------+\n",
            "only showing top 10 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "2JHO6_GJWURB",
        "colab_type": "code",
        "outputId": "3e07a2a3-e771-4bd6-9590-9b580fc98699",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 490
        }
      },
      "source": [
        "corona_max_df.groupBy(\"Country\").sum(\"Active\").orderBy(\"sum(Active)\",ascending=False).show()"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+--------------+-----------+\n",
            "|       Country|sum(Active)|\n",
            "+--------------+-----------+\n",
            "|         Italy|      38549|\n",
            "|       Germany|      19601|\n",
            "|            US|      18856|\n",
            "|         Spain|      17779|\n",
            "|        France|      12264|\n",
            "|          Iran|      11466|\n",
            "|  Korea, South|       7018|\n",
            "|         China|       6731|\n",
            "|   Switzerland|       5225|\n",
            "|United Kingdom|       3769|\n",
            "|   Netherlands|       2894|\n",
            "|       Austria|       2373|\n",
            "|       Belgium|       2219|\n",
            "|        Norway|       1906|\n",
            "|        Sweden|       1607|\n",
            "|       Denmark|       1327|\n",
            "|      Portugal|       1009|\n",
            "|      Malaysia|        940|\n",
            "|        Canada|        922|\n",
            "|       Czechia|        829|\n",
            "+--------------+-----------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Qr4LTRYl2PVI",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        ""
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ErERE-dQ2PJt",
        "colab_type": "code",
        "outputId": "c6caf1d5-6c23-47c8-cb83-9c7681ec8785",
        "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": "ef7499e0-ab00-44b0-8d01-102782cb68a6",
        "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|            Tasmania|            10|             3|\n",
            "|     null|                null|          1734|            35|\n",
            "|     null|            Manitoba|            17|             0|\n",
            "|     null|   Western Australia|            64|             0|\n",
            "|     null|        Saskatchewan|            20|             0|\n",
            "|     null|From Diamond Prin...|             0|             0|\n",
            "|     null|    British Columbia|           271|             4|\n",
            "|     null|             Ontario|           308|             5|\n",
            "|     null|  Northern Territory|             3|             0|\n",
            "|     null|         Nova Scotia|            15|             0|\n",
            "|     null|Newfoundland and ...|             4|             0|\n",
            "|     null|             Alberta|           146|             0|\n",
            "|     null|Australian Capita...|             6|             0|\n",
            "|     null|          Queensland|           184|             8|\n",
            "|     null|     South Australia|            50|             3|\n",
            "|     null|     New South Wales|           353|             4|\n",
            "|     null|            Victoria|           121|             8|\n",
            "|     null|       New Brunswick|            11|             0|\n",
            "|     null|Prince Edward Island|             2|             0|\n",
            "|     null|              Quebec|           139|             0|\n",
            "|     null|      Grand Princess|            10|             0|\n",
            "|Australia|  Northern Territory|             3|             0|\n",
            "|Australia|          Queensland|           184|             8|\n",
            "|Australia|   Western Australia|            64|             0|\n",
            "|Australia|                null|           791|            26|\n",
            "|Australia|            Tasmania|            10|             3|\n",
            "|Australia|     New South Wales|           353|             4|\n",
            "|Australia|Australian Capita...|             6|             0|\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|             Alberta|           146|             0|\n",
            "|   Canada|              Quebec|           139|             0|\n",
            "|   Canada|Newfoundland and ...|             4|             0|\n",
            "|   Canada|Prince Edward Island|             2|             0|\n",
            "|   Canada|      Grand Princess|            10|             0|\n",
            "|   Canada|         Nova Scotia|            15|             0|\n",
            "|   Canada|            Manitoba|            17|             0|\n",
            "|   Canada|    British Columbia|           271|             4|\n",
            "|   Canada|             Ontario|           308|             5|\n",
            "|   Canada|                null|           943|             9|\n",
            "+---------+--------------------+--------------+--------------+\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "N0BxDa-93t3c",
        "colab_type": "code",
        "outputId": "c24a1f6c-0a3b-45a5-972e-0b900eced48a",
        "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|          Queensland|           184|             8|\n",
            "|Australia|Australian Capita...|             6|             0|\n",
            "|Australia|   Western Australia|            64|             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",
            "|Australia|     New South Wales|           353|             4|\n",
            "|   Canada|        Saskatchewan|            20|             0|\n",
            "|   Canada|       New Brunswick|            11|             0|\n",
            "|   Canada|             Alberta|           146|             0|\n",
            "|   Canada|              Quebec|           139|             0|\n",
            "|   Canada|Newfoundland and ...|             4|             0|\n",
            "|   Canada|      Grand Princess|            10|             0|\n",
            "|   Canada|    British Columbia|           271|             4|\n",
            "|   Canada|Prince Edward Island|             2|             0|\n",
            "|   Canada|             Ontario|           308|             5|\n",
            "|   Canada|                null|           943|             9|\n",
            "|   Canada|         Nova Scotia|            15|             0|\n",
            "|   Canada|            Manitoba|            17|             0|\n",
            "+---------+--------------------+--------------+--------------+\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "NJsN18yx3tYu",
        "colab_type": "code",
        "outputId": "ebe5658b-e65d-4ee9-dd2d-bc17c29b7e86",
        "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": 79
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "TPSh56yt3tNl",
        "colab_type": "code",
        "outputId": "ec7f8688-3356-406b-cb2d-02871263abaf",
        "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, Active: int]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 80
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "1aJst2PC3tBG",
        "colab_type": "code",
        "outputId": "3abd3f4d-5658-4d62-c1f2-18ab11a8c67f",
        "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, Active: int]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 84
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Kh4TnB4w8qx0",
        "colab_type": "code",
        "outputId": "6fec1ced-7493-47aa-9cfa-e01147325873",
        "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: 47.3 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": "3bf6bba0-b1e8-4ed1-a078-4d7cc2c8e1bd",
        "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": 93
        }
      ]
    },
    {
      "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": "351fdb2b-687b-4862-b193-65e171538ae0",
        "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|Active|\n",
            "+--------------------+--------------------+----------+-----+----------------+--------+---------+---------+-----+---------+----+------+\n",
            "|            Thailand|             Bangkok|2020-03-20|27666|            null|    15.0|    101.0|      322|    1|       42|null|   279|\n",
            "|               Japan|             Hiraide|2020-03-20|27667|            null|    36.0|    138.0|      963|   33|      191|null|   739|\n",
            "|           Singapore|           Singapore|2020-03-20|27668|            null|  1.2833| 103.8333|      385|    0|      124|null|   261|\n",
            "|               Nepal|           Kathmandu|2020-03-20|27669|            null| 28.1667|    84.25|        1|    0|        1|null|     0|\n",
            "|            Malaysia|             Sarawak|2020-03-20|27670|            null|     2.5|    112.5|     1030|    3|       87|null|   940|\n",
            "|              Canada|    British Columbia|2020-03-20|27671|British Columbia| 49.2827|-123.1207|      271|    8|        4|null|   259|\n",
            "|           Australia|     New South Wales|2020-03-20|27672| New South Wales|-33.8688| 151.2093|      353|    6|        4|null|   343|\n",
            "|           Australia|            Victoria|2020-03-20|27673|        Victoria|-37.8136| 144.9631|      121|    0|        8|null|   113|\n",
            "|           Australia|          Queensland|2020-03-20|27674|      Queensland|-28.0167|    153.4|      184|    0|        8|null|   176|\n",
            "|            Cambodia|          Phnom Penh|2020-03-20|27675|            null|   11.55| 104.9167|       51|    0|        1|null|    50|\n",
            "|           Sri Lanka|Sri Jayawardenapu...|2020-03-20|27676|            null|     7.0|     81.0|       73|    0|        3|null|    70|\n",
            "|             Germany|              Berlin|2020-03-20|27677|            null|    51.0|      9.0|    19848|   67|      180|null| 19601|\n",
            "|             Finland|            Helsinki|2020-03-20|27678|            null|    64.0|     26.0|      450|    0|       10|null|   440|\n",
            "|United Arab Emirates|           Abu Dhabi|2020-03-20|27679|            null|    24.0|     54.0|      140|    2|       31|null|   107|\n",
            "|         Philippines|              Manila|2020-03-20|27680|            null|    13.0|    122.0|      230|   18|        8|null|   204|\n",
            "|               India|           New Delhi|2020-03-20|27681|            null|    21.0|     78.0|      244|    5|       20|null|   219|\n",
            "|               Italy|                Rome|2020-03-20|27682|            null|    43.0|     12.0|    47021| 4032|     4440|null| 38549|\n",
            "|              Sweden|           Stockholm|2020-03-20|27683|            null|    63.0|     16.0|     1639|   16|       16|null|  1607|\n",
            "|               Spain|              Toledo|2020-03-20|27684|            null|    40.0|     -4.0|    20410| 1043|     1588|null| 17779|\n",
            "|           Australia|     South Australia|2020-03-20|27685| South Australia|-34.9285| 138.6007|       50|    0|        3|null|    47|\n",
            "+--------------------+--------------------+----------+-----+----------------+--------+---------+---------+-----+---------+----+------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "LagPitKeIZ8F",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        ""
      ],
      "execution_count": 0,
      "outputs": []
    }
  ]
}