transformation_and_actions.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql import SparkSession\n",
    "\n",
    "spark = SparkSession \\\n",
    "    .builder \\\n",
    "    .appName(\"Python Spark data cleaning and engineering\") \\\n",
    "    .config(\"spark.some.config.option\", \"some-value\") \\\n",
    "    .getOrCreate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sys, tempfile, urllib, os\n",
    "from pyspark.sql.functions import *"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "base_dir = \"datasets/\"\n",
    "corona_data_file = os.path.join(base_dir, \"corona_data.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "corona_data = urllib.request.urlretrieve(\"https://raw.githubusercontent.com/srivatsan88/YouTubeLI/master/dataset/coronavirus/corona_dataset_latest.csv\", corona_data_file)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "base_dir = \"datasets/\"\n",
    "twitter_data_file = os.path.join(base_dir, \"twitter_data.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "tweet_data = urllib.request.urlretrieve(\"https://raw.githubusercontent.com/srivatsan88/YouTubeLI/master/dataset/coronavirus/tweets.csv\", twitter_data_file)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "corona_df = spark.read.option(\"inferschema\", \"true\").csv(\"datasets/corona_data.csv\", header=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "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"
     ]
    }
   ],
   "source": [
    "corona_df.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "28143"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "corona_df.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "twitter_df = spark.read.option(\"inferschema\", \"true\").csv(\"datasets/twitter_data.csv\", header=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "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"
     ]
    }
   ],
   "source": [
    "twitter_df.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1000"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "twitter_df.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DataFrame[_c0: int, geo: string, text: string, user: string, location: string, entities: string, sentiment: string, country: string]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "twitter_df.filter(\"country='USA'\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "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"
     ]
    }
   ],
   "source": [
    "twitter_df.filter(\"country='USA'\").show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DataFrame[_c0: int, geo: string, text: string, user: string, location: string, entities: string, sentiment: string, country: string]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "twitter_df.filter(\"country='USA' and location like '%New%'\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "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",
      "|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",
      "|611|null| Apparently the #...|  BuffaloInABox|   Buffalo, New York|['', ('CoronaViru...|{'neg': 0.171, '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",
      "|624|null| Trump is now doi...| swirlysunburst|Northwest, New Je...|[('Trump', 'ORG')...|{'neg': 0.0, 'neu...|    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",
      "+---+----+--------------------+---------------+--------------------+--------------------+--------------------+-------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "twitter_df.filter(\"country='USA' and location like '%New%'\").show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "tw_filter_df = twitter_df.filter(\"country='USA'\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "== Physical Plan ==\n",
      "*(1) Project [_c0#126, geo#127, text#128, user#129, location#130, entities#131, sentiment#132, country#133]\n",
      "+- *(1) Filter (isnotnull(country#133) AND (country#133 = USA))\n",
      "   +- FileScan csv [_c0#126,geo#127,text#128,user#129,location#130,entities#131,sentiment#132,country#133] Batched: false, DataFilters: [isnotnull(country#133), (country#133 = USA)], Format: CSV, Location: InMemoryFileIndex[file:/C:/Users/Rahul Ghosh/Desktop/Spark-for-ML/datasets/twitter_data.csv], PartitionFilters: [], PushedFilters: [IsNotNull(country), EqualTo(country,USA)], ReadSchema: struct<_c0:int,geo:string,text:string,user:string,location:string,entities:string,sentiment:strin...\n",
      "\n",
      "\n"
     ]
    }
   ],
   "source": [
    "tw_filter_df.explain()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "== Physical Plan ==\n",
      "*(1) Project [_c0#126, geo#127, text#128, user#129, location#130, entities#131, sentiment#132, country#133]\n",
      "+- *(1) Filter (((isnotnull(country#133) AND isnotnull(location#130)) AND (country#133 = USA)) AND Contains(location#130, New))\n",
      "   +- FileScan csv [_c0#126,geo#127,text#128,user#129,location#130,entities#131,sentiment#132,country#133] Batched: false, DataFilters: [isnotnull(country#133), isnotnull(location#130), (country#133 = USA), Contains(location#130, New)], Format: CSV, Location: InMemoryFileIndex[file:/C:/Users/Rahul Ghosh/Desktop/Spark-for-ML/datasets/twitter_data.csv], PartitionFilters: [], PushedFilters: [IsNotNull(country), IsNotNull(location), EqualTo(country,USA), StringContains(location,New)], ReadSchema: struct<_c0:int,geo:string,text:string,user:string,location:string,entities:string,sentiment:strin...\n",
      "\n",
      "\n"
     ]
    }
   ],
   "source": [
    "tw_filter_df = twitter_df.filter(\"country='USA' and location like '%New%'\")\n",
    "tw_filter_df.explain()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "tw_filter_df = twitter_df.filter(col(\"location\").startswith(\"N\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "== Physical Plan ==\n",
      "*(1) Project [_c0#126, geo#127, text#128, user#129, location#130, entities#131, sentiment#132, country#133]\n",
      "+- *(1) Filter (isnotnull(location#130) AND StartsWith(location#130, N))\n",
      "   +- FileScan csv [_c0#126,geo#127,text#128,user#129,location#130,entities#131,sentiment#132,country#133] Batched: false, DataFilters: [isnotnull(location#130), StartsWith(location#130, N)], Format: CSV, Location: InMemoryFileIndex[file:/C:/Users/Rahul Ghosh/Desktop/Spark-for-ML/datasets/twitter_data.csv], PartitionFilters: [], PushedFilters: [IsNotNull(location), StringStartsWith(location,N)], ReadSchema: struct<_c0:int,geo:string,text:string,user:string,location:string,entities:string,sentiment:strin...\n",
      "\n",
      "\n"
     ]
    }
   ],
   "source": [
    "tw_filter_df.explain()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "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')"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "twitter_df.first()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "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)]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "twitter_df.take(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "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"
     ]
    }
   ],
   "source": [
    "twitter_df.select(\"text\").show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "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"
     ]
    }
   ],
   "source": [
    "twitter_df.select(\"text\", \"user\").show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "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"
     ]
    }
   ],
   "source": [
    "corona_df.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "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"
     ]
    }
   ],
   "source": [
    "corona_df.filter(\"Country='US'\").show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "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"
     ]
    }
   ],
   "source": [
    "corona_df.filter(\"Country='US'\").sort(col(\"Date\"), ascending=False).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "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"
     ]
    }
   ],
   "source": [
    "corona_df.filter(\"Country='US'\").orderBy(col(\"Date\"), ascending=False).show() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "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"
     ]
    }
   ],
   "source": [
    "corona_df.filter(\"Country='US'\").sortWithinPartitions([col(\"Date\"), col(\"Confirmed\")], ascending=False).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "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"
     ]
    }
   ],
   "source": [
    "corona_df.describe().show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "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"
     ]
    }
   ],
   "source": [
    "corona_df.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----+----------+-------+-------+---------+----------+---------+-----+---------+-------------+----------+\n",
      "|  _c0|     State|Country|    Lat|     Long|      Date|Confirmed|Death|Recovered|state_cleaned|      City|\n",
      "+-----+----------+-------+-------+---------+----------+---------+-----+---------+-------------+----------+\n",
      "|11609|     Hunan|  China|27.6104| 111.7088|2020-02-15|     1001|    2|      425|        Hunan|      null|\n",
      "|12086|     Hunan|  China|27.6104| 111.7088|2020-02-16|     1004|    3|      464|        Hunan|      null|\n",
      "| 7792|  Zhejiang|  China|29.1832| 120.0934|2020-02-07|     1006|    0|      123|     Zhejiang|      null|\n",
      "|12563|     Hunan|  China|27.6104| 111.7088|2020-02-17|     1006|    3|      498|        Hunan|      null|\n",
      "|13040|     Hunan|  China|27.6104| 111.7088|2020-02-18|     1007|    4|      527|        Hunan|      null|\n",
      "|13517|     Hunan|  China|27.6104| 111.7088|2020-02-19|     1008|    4|      561|        Hunan|      null|\n",
      "|13994|     Hunan|  China|27.6104| 111.7088|2020-02-20|     1010|    4|      634|        Hunan|      null|\n",
      "|14471|     Hunan|  China|27.6104| 111.7088|2020-02-21|     1011|    4|      661|        Hunan|      null|\n",
      "|14948|     Hunan|  China|27.6104| 111.7088|2020-02-22|     1013|    4|      692|        Hunan|      null|\n",
      "|26810|Washington|     US|47.4009|-121.4905|2020-03-18|     1014|   55|        0|   Washington|Washington|\n",
      "|15425|     Hunan|  China|27.6104| 111.7088|2020-02-23|     1016|    4|      714|        Hunan|      null|\n",
      "|16379|     Hunan|  China|27.6104| 111.7088|2020-02-25|     1016|    4|      768|        Hunan|      null|\n",
      "|15902|     Hunan|  China|27.6104| 111.7088|2020-02-24|     1016|    4|      731|        Hunan|      null|\n",
      "|16856|     Hunan|  China|27.6104| 111.7088|2020-02-26|     1016|    4|      783|        Hunan|      null|\n",
      "|17333|     Hunan|  China|27.6104| 111.7088|2020-02-27|     1017|    4|      804|        Hunan|      null|\n",
      "|17810|     Hunan|  China|27.6104| 111.7088|2020-02-28|     1017|    4|      830|        Hunan|      null|\n",
      "|22580|     Hunan|  China|27.6104| 111.7088|2020-03-09|     1018|    4|      979|        Hunan|      null|\n",
      "|18764|     Hunan|  China|27.6104| 111.7088|2020-03-01|     1018|    4|      866|        Hunan|      null|\n",
      "|23057|     Hunan|  China|27.6104| 111.7088|2020-03-10|     1018|    4|      990|        Hunan|      null|\n",
      "|23534|     Hunan|  China|27.6104| 111.7088|2020-03-11|     1018|    4|      995|        Hunan|      null|\n",
      "+-----+----------+-------+-------+---------+----------+---------+-----+---------+-------------+----------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "corona_df.filter(\"Confirmed>1000\").sort(col(\"Confirmed\")).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[10075.0, 10075.0, 10075.0, 10075.0, 67800.0]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "corona_df.filter(\"Confirmed>10000\").approxQuantile('Confirmed', [0.25, 0.5, 0.75, 0.9, 0.95], 0.9)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[Row(max(Date)='2020-03-20')]"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "corona_df.agg({\"Date\":\"max\"}).collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[Row(max(Confirmed)=67800, max(Date)='2020-03-20')]"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "corona_df.agg({\"Date\":\"max\", \"Confirmed\":\"max\"}).collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "max_date = corona_df.agg({\"Date\":\"max\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+\n",
      "| max(Date)|\n",
      "+----------+\n",
      "|2020-03-20|\n",
      "+----------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "max_date.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------+--------------------+----------+\n",
      "|       Country|       State_cleaned| max(Date)|\n",
      "+--------------+--------------------+----------+\n",
      "|      Cameroon|             Yaounde|2020-03-20|\n",
      "|         China|             Qinghai|2020-03-20|\n",
      "|        Cyprus|             Nicosia|2020-03-20|\n",
      "|            US|            Michigan|2020-03-20|\n",
      "|      Portugal|              Lisbon|2020-03-20|\n",
      "|            US|            Colorado|2020-03-20|\n",
      "|United Kingdom|      Cayman Islands|2020-03-20|\n",
      "|         China|              Hainan|2020-03-20|\n",
      "|            US|            Missouri|2020-03-20|\n",
      "|     Australia|Australian Capita...|2020-03-20|\n",
      "|            US|                Guam|2020-03-20|\n",
      "|        France|             Reunion|2020-03-20|\n",
      "|      Colombia|        Cundinamarca|2020-03-20|\n",
      "|          Cuba|              Havana|2020-03-20|\n",
      "|     Mauritius|          Port Louis|2020-03-20|\n",
      "|       Ukraine|                Kiev|2020-03-20|\n",
      "|         Benin|          Porto-Novo|2020-03-20|\n",
      "|   Switzerland|                Bern|2020-03-20|\n",
      "|         Malta|               Mgarr|2020-03-20|\n",
      "|United Kingdom|         Isle of Man|2020-03-20|\n",
      "+--------------+--------------------+----------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "import pyspark.sql.functions as F\n",
    "corona_df.groupBy(\"Country\", \"State_cleaned\").agg(F.max(\"Date\")).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "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"
     ]
    }
   ],
   "source": [
    "corona_df.join(corona_df.groupBy(\"Country\", \"State_cleaned\").agg(F.max(\"Date\").alias(\"Date\")), \n",
    "               on=[\"Country\", \"State_cleaned\", \"Date\"], how=\"inner\").show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "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"
     ]
    }
   ],
   "source": [
    "corona_df.join(corona_df.groupBy(\"Country\", \"State_cleaned\").agg(F.max(\"Date\").alias(\"Date\")), \n",
    "               on=[\"Country\", \"State_cleaned\", \"Date\"], how=\"inner\").sort(\"Confirmed\", ascending=False).show(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----+-----+--------+------------------+-------+----------+---------+-----+---------+-------------+----+-------+\n",
      "|  _c0|State| Country|               Lat|   Long|      Date|Confirmed|Death|Recovered|state_cleaned|City|row_num|\n",
      "+-----+-----+--------+------------------+-------+----------+---------+-----+---------+-------------+----+-------+\n",
      "|27745| null|Cameroon|3.8480000000000003|11.5021|2020-03-20|       20|    0|        0|      Yaounde|null|      1|\n",
      "|27268| null|Cameroon|3.8480000000000003|11.5021|2020-03-19|       13|    0|        0|      Yaounde|null|      2|\n",
      "|26791| null|Cameroon|3.8480000000000003|11.5021|2020-03-18|       10|    0|        0|      Yaounde|null|      3|\n",
      "|26314| null|Cameroon|3.8480000000000003|11.5021|2020-03-17|       10|    0|        0|      Yaounde|null|      4|\n",
      "|25837| null|Cameroon|3.8480000000000003|11.5021|2020-03-16|        4|    0|        0|      Yaounde|null|      5|\n",
      "|25360| null|Cameroon|3.8480000000000003|11.5021|2020-03-15|        2|    0|        0|      Yaounde|null|      6|\n",
      "|24883| null|Cameroon|3.8480000000000003|11.5021|2020-03-14|        2|    0|        0|      Yaounde|null|      7|\n",
      "|24406| null|Cameroon|3.8480000000000003|11.5021|2020-03-13|        2|    0|        0|      Yaounde|null|      8|\n",
      "|23929| null|Cameroon|3.8480000000000003|11.5021|2020-03-12|        2|    0|        0|      Yaounde|null|      9|\n",
      "|23452| null|Cameroon|3.8480000000000003|11.5021|2020-03-11|        2|    0|        0|      Yaounde|null|     10|\n",
      "|22975| null|Cameroon|3.8480000000000003|11.5021|2020-03-10|        2|    0|        0|      Yaounde|null|     11|\n",
      "|22498| null|Cameroon|3.8480000000000003|11.5021|2020-03-09|        2|    0|        0|      Yaounde|null|     12|\n",
      "|22021| null|Cameroon|3.8480000000000003|11.5021|2020-03-08|        2|    0|        0|      Yaounde|null|     13|\n",
      "|21544| null|Cameroon|3.8480000000000003|11.5021|2020-03-07|        1|    0|        0|      Yaounde|null|     14|\n",
      "|21067| null|Cameroon|3.8480000000000003|11.5021|2020-03-06|        1|    0|        0|      Yaounde|null|     15|\n",
      "|20590| null|Cameroon|3.8480000000000003|11.5021|2020-03-05|        0|    0|        0|      Yaounde|null|     16|\n",
      "|20113| null|Cameroon|3.8480000000000003|11.5021|2020-03-04|        0|    0|        0|      Yaounde|null|     17|\n",
      "|19636| null|Cameroon|3.8480000000000003|11.5021|2020-03-03|        0|    0|        0|      Yaounde|null|     18|\n",
      "|19159| null|Cameroon|3.8480000000000003|11.5021|2020-03-02|        0|    0|        0|      Yaounde|null|     19|\n",
      "|18682| null|Cameroon|3.8480000000000003|11.5021|2020-03-01|        0|    0|        0|      Yaounde|null|     20|\n",
      "+-----+-----+--------+------------------+-------+----------+---------+-----+---------+-------------+----+-------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "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",
    "corona_df.withColumn(\"row_num\", row_number().over(ws)).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----+--------------------+--------------+------------------+---------+----------+---------+-----+---------+--------------------+--------+-------+\n",
      "|  _c0|               State|       Country|               Lat|     Long|      Date|Confirmed|Death|Recovered|       state_cleaned|    City|row_num|\n",
      "+-----+--------------------+--------------+------------------+---------+----------+---------+-----+---------+--------------------+--------+-------+\n",
      "|27745|                null|      Cameroon|3.8480000000000003|  11.5021|2020-03-20|       20|    0|        0|             Yaounde|    null|      1|\n",
      "|27859|             Qinghai|         China|           35.7452|  95.9956|2020-03-20|       18|    0|       18|             Qinghai|    null|      1|\n",
      "|27762|                null|        Cyprus|           35.1264|  33.4299|2020-03-20|       67|    0|        0|             Nicosia|    null|      1|\n",
      "|27812|            Michigan|            US|           43.3266| -84.5361|2020-03-20|      552|    3|        0|            Michigan|Michigan|      1|\n",
      "|27725|                null|      Portugal|           39.3999|  -8.2245|2020-03-20|     1020|    6|        5|              Lisbon|    null|      1|\n",
      "|27771|            Colorado|            US|           39.0598|-105.3111|2020-03-20|      363|    4|        0|            Colorado|Colorado|      1|\n",
      "|28115|      Cayman Islands|United Kingdom|           19.3133| -81.2546|2020-03-20|        3|    1|        0|      Cayman Islands|    null|      1|\n",
      "|27844|              Hainan|         China|           19.1959| 109.7453|2020-03-20|      168|    6|      161|              Hainan|    null|      1|\n",
      "|27805|            Missouri|            US|           38.4561| -92.2884|2020-03-20|       53|    2|        0|            Missouri|Missouri|      1|\n",
      "|28068|Australian Capita...|     Australia|          -35.4735| 149.0124|2020-03-20|        6|    0|        0|Australian Capita...|    null|      1|\n",
      "|28097|                Guam|            US|           13.4443| 144.7937|2020-03-20|       14|    0|        0|                Guam|    Guam|      1|\n",
      "|28116|             Reunion|        France|          -21.1351|  55.2471|2020-03-20|       28|    0|        0|             Reunion|    null|      1|\n",
      "|27746|                null|      Colombia|            4.5709| -74.2973|2020-03-20|      128|    0|        1|        Cundinamarca|    null|      1|\n",
      "|28066|                null|          Cuba|              22.0|    -80.0|2020-03-20|       16|    1|        0|              Havana|    null|      1|\n",
      "|28120|                null|     Mauritius|             -20.2|     57.5|2020-03-20|       12|    0|        0|          Port Louis|    null|      1|\n",
      "|27735|                null|       Ukraine|           48.3794|  31.1656|2020-03-20|       29|    3|        0|                Kiev|    null|      1|\n",
      "|28109|                null|         Benin|            9.3077|   2.3158|2020-03-20|        2|    0|        0|          Porto-Novo|    null|      1|\n",
      "|27697|                null|   Switzerland|           46.8182|   8.2275|2020-03-20|     5294|   54|       15|                Bern|    null|      1|\n",
      "|27752|                null|         Malta|           35.9375|  14.3754|2020-03-20|       64|    0|        2|               Mgarr|    null|      1|\n",
      "|28141|         Isle of Man|United Kingdom|           54.2361|  -4.5481|2020-03-20|        1|    0|        0|         Isle of Man|    null|      1|\n",
      "+-----+--------------------+--------------+------------------+---------+----------+---------+-----+---------+--------------------+--------+-------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "corona_df.withColumn(\"row_num\", row_number().over(ws)).where(col(\"row_num\") == 1).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "corona_max_df = corona_df.join(corona_df.groupBy(\"Country\", \"State_cleaned\").agg(F.max(\"Date\").alias(\"Date\")), \n",
    "               on=[\"Country\", \"State_cleaned\", \"Date\"], how=\"inner\").sort(\"Confirmed\", ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "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",
      "|       Chad|         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|         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|\n",
      "|     Russia|         0|         0|         0|         0|         0|         0|         0|         0|         0|         2|         2|         2|         2|         2|         2|         2|         2|         2|         2|         2|         2|         2|         2|         2|         2|         2|         2|         2|         2|         2|         2|         2|         2|         2|         2|         2|         2|         2|         2|         2|         3|         3|         3|         4|        13|        13|        17|        17|        20|        20|        28|        45|        59|        63|        90|       114|       147|       199|       253|\n",
      "|   Paraguay|         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|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         1|         1|         1|         5|         5|         6|         6|         6|         8|         9|        11|        11|        13|\n",
      "|    Senegal|         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|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         1|         2|         4|         4|         4|         4|         4|         4|         4|         4|         4|        10|        10|        24|        24|        26|        31|        31|        38|\n",
      "| Cabo Verde|         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|         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|\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",
      "|     Guyana|         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|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         1|         1|         1|         4|         4|         7|         7|         7|         7|\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",
      "|   Djibouti|         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|         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|\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",
      "|   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",
      "|       Fiji|         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|         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|\n",
      "|     Turkey|         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|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         1|         1|         5|         5|         6|        18|        47|        98|       192|       359|\n",
      "|       Iraq|         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|         0|         0|         0|         1|         1|         5|         7|         7|        13|        19|        26|        32|        35|        35|        40|        54|        60|        60|        71|        71|        71|       101|       110|       116|       124|       154|       164|       192|       208|\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",
      "|   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",
      "|Afghanistan|         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|         0|         0|         0|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         4|         4|         5|         7|         7|         7|        11|        16|        21|        22|        22|        22|        24|\n",
      "|     Jordan|         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|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         1|         8|        17|        34|        52|        69|        85|\n",
      "|     Rwanda|         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|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         1|         1|         5|         7|         8|         8|        17|\n",
      "|   Maldives|         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|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         0|         4|         4|         6|         8|         8|         9|        10|        13|        13|        13|        13|        13|        13|\n",
      "+-----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "corona_df.groupBy(\"Country\").pivot(\"Date\").agg(F.sum(\"Confirmed\")).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "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"
     ]
    }
   ],
   "source": [
    "corona_df.filter(\"Country='US'\").crosstab(\"State\", \"Date\").show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------+--------------+\n",
      "|    Country|sum(Confirmed)|\n",
      "+-----------+--------------+\n",
      "|       Chad|             1|\n",
      "|   Paraguay|            13|\n",
      "|     Russia|           253|\n",
      "|    Senegal|            38|\n",
      "|     Sweden|          1639|\n",
      "| Cabo Verde|             1|\n",
      "|     Guyana|             7|\n",
      "|Philippines|           230|\n",
      "|   Djibouti|             1|\n",
      "|  Singapore|           385|\n",
      "|   Malaysia|          1030|\n",
      "|       Fiji|             1|\n",
      "|     Turkey|           359|\n",
      "|       Iraq|           208|\n",
      "|    Germany|         19848|\n",
      "|   Cambodia|            51|\n",
      "|Afghanistan|            24|\n",
      "|     Jordan|            85|\n",
      "|   Maldives|            13|\n",
      "|     Rwanda|            17|\n",
      "+-----------+--------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "corona_max_df.groupBy(\"Country\").agg({\"Confirmed\":\"sum\"}).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "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"
     ]
    }
   ],
   "source": [
    "corona_max_df.groupBy(\"Country\").agg({\"Confirmed\":\"sum\", \"Recovered\":\"sum\", \"Death\":\"sum\"}).orderBy(\"sum(Confirmed)\", ascending=False).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "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"
     ]
    }
   ],
   "source": [
    "corona_df.filter(\"Country='Italy'\").sort(\"Date\", ascending=False).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "corona_max_df = corona_max_df.withColumn(\"Active\", corona_max_df.Confirmed - corona_max_df.Recovered - corona_max_df.Death)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "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"
     ]
    }
   ],
   "source": [
    "corona_max_df.groupBy(\"Country\").sum(\"Active\").orderBy(\"sum(Active)\", ascending=False).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}