pyspark_basic.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/snknitin/pyspark-snippets/blob/main/pyspark_basic.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "jPyDpDqvZuLo"
      },
      "source": [
        "# **Welcome to the Notebook**"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "F2YNhXuUwGNl"
      },
      "source": [
        "### Let's mount the google drive"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "MmQC7J5cvkzE",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "6d532a34-2e4e-4fa0-9000-69638c7a8988"
      },
      "source": [
        "from google.colab import drive\n",
        "drive.mount('/content/drive')"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Mounted at /content/drive\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "cs9EInKyWicZ"
      },
      "source": [
        "# Task 1 :\n",
        "Installing pyspark module"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "LJq2nzUKWujx",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "d141d2d2-74f2-41d6-b1d3-7ded8afe8c81"
      },
      "source": [
        "!pip install pyspark"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Collecting pyspark\n",
            "  Downloading pyspark-3.5.1.tar.gz (317.0 MB)\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m317.0/317.0 MB\u001b[0m \u001b[31m4.6 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[?25h  Preparing metadata (setup.py) ... \u001b[?25l\u001b[?25hdone\n",
            "Requirement already satisfied: py4j==0.10.9.7 in /usr/local/lib/python3.10/dist-packages (from pyspark) (0.10.9.7)\n",
            "Building wheels for collected packages: pyspark\n",
            "  Building wheel for pyspark (setup.py) ... \u001b[?25l\u001b[?25hdone\n",
            "  Created wheel for pyspark: filename=pyspark-3.5.1-py2.py3-none-any.whl size=317488491 sha256=a6b794b9aef169453831401660d2024bade7c1ac32ca1b3ebe1fef96f688a2f2\n",
            "  Stored in directory: /root/.cache/pip/wheels/80/1d/60/2c256ed38dddce2fdd93be545214a63e02fbd8d74fb0b7f3a6\n",
            "Successfully built pyspark\n",
            "Installing collected packages: pyspark\n",
            "Successfully installed pyspark-3.5.1\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "dVNAlw2jWiWb"
      },
      "source": [
        "Importing the modules"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "gS3YIWJiW7I3"
      },
      "source": [
        "from pyspark.sql import SparkSession\n",
        "from pyspark.sql.functions import count, desc , col, max ,struct\n",
        "import matplotlib.pyplot as plts"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "RKuIUYLwSkNP"
      },
      "source": [
        "creating spark session"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "XXSZBvRgSnCN",
        "cellView": "both"
      },
      "source": [
        "spark =  SparkSession.builder.appName(\"spark_app\").getOrCreate()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "vv_SKqr8T9mT"
      },
      "source": [
        "# Task 2 :\n",
        "importing the *Listenings.csv* file:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "gK22lJDRTuKY"
      },
      "source": [
        "listening_csv_path = \"/content/drive/MyDrive/dataset/listenings.csv\"\n",
        "listening_df = spark.read.format('csv').option('inferSchema',True).option(\"header\",True).load(listening_csv_path)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "s1i6KVshykdn"
      },
      "source": [
        "let's check the data:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "5Ji0zViUyjUE",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "70f3fbcc-71a5-4569-821c-a27a46fe0a9b"
      },
      "source": [
        "listening_df.show()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-----------+-------------+--------------------+---------------+--------------------+\n",
            "|    user_id|         date|               track|         artist|               album|\n",
            "+-----------+-------------+--------------------+---------------+--------------------+\n",
            "|000Silenced|1299680100000|           Price Tag|       Jessie J|         Who You Are|\n",
            "|000Silenced|1299679920000|Price Tag (Acoust...|       Jessie J|           Price Tag|\n",
            "|000Silenced|1299679440000|Be Mine! (Ballad ...|          Robyn|            Be Mine!|\n",
            "|000Silenced|1299679200000|            Acapella|          Kelis|            Acapella|\n",
            "|000Silenced|1299675660000|   I'm Not Invisible|      The Tease|   I'm Not Invisible|\n",
            "|000Silenced|1297511400000|Bounce (Feat NORE...|       MSTRKRFT|         Fist of God|\n",
            "|000Silenced|1294498440000|Don't Stop The Mu...|        Rihanna|Addicted 2 Bassli...|\n",
            "|000Silenced|1292438340000|               ObZen|      Meshuggah|               ObZen|\n",
            "|000Silenced|1292437740000|   Yama's Messengers|         Gojira|The Way of All Flesh|\n",
            "|000Silenced|1292436360000|On the Brink of E...|   Napalm Death|Time Waits For No...|\n",
            "|000Silenced|1292436360000|On the Brink of E...|   Napalm Death|Time Waits For No...|\n",
            "|000Silenced|1292435940000|        In Deference|   Napalm Death|      Smear Campaign|\n",
            "|000Silenced|1292434920000|      Post(?)organic|    Decapitated|Organic Hallucinosis|\n",
            "|000Silenced|1292434560000|        Mind Feeders|   Dom & Roland| No Strings Attached|\n",
            "|000Silenced|1292434320000|Necrosadistic War...|Cannibal Corpse|                Kill|\n",
            "|000Silenced|1292365560000|     Dance All Night|   Dom & Roland|          Chronology|\n",
            "|000Silenced|1292365260000|          Late Night|   Dom & Roland|          Chronology|\n",
            "|000Silenced|1292365020000|          Freak Seen|   Dom & Roland|          Chronology|\n",
            "|000Silenced|1292364720000|Paradrenasite (Hi...|   Dom & Roland|          Chronology|\n",
            "|000Silenced|1292364300000|               Rhino|   Dom & Roland|          Chronology|\n",
            "+-----------+-------------+--------------------+---------------+--------------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "HS6wd2d_woNC"
      },
      "source": [
        "let's delete useless columns:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "pIMzBAglwtNP"
      },
      "source": [
        "listening_df = listening_df.drop('date')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "MwpJJeWa4qmn"
      },
      "source": [
        "drop the null rows:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Botf6-Vb4uqs"
      },
      "source": [
        "listening_df=listening_df.na.drop()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "tTN6jr3K4xkF"
      },
      "source": [
        "let's check the dataset again:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "JDp_rdEY40u3",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "11cd4aea-644b-4001-96c9-539951eebd08"
      },
      "source": [
        "listening_df.show()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-----------+--------------------+---------------+--------------------+\n",
            "|    user_id|               track|         artist|               album|\n",
            "+-----------+--------------------+---------------+--------------------+\n",
            "|000Silenced|           Price Tag|       Jessie J|         Who You Are|\n",
            "|000Silenced|Price Tag (Acoust...|       Jessie J|           Price Tag|\n",
            "|000Silenced|Be Mine! (Ballad ...|          Robyn|            Be Mine!|\n",
            "|000Silenced|            Acapella|          Kelis|            Acapella|\n",
            "|000Silenced|   I'm Not Invisible|      The Tease|   I'm Not Invisible|\n",
            "|000Silenced|Bounce (Feat NORE...|       MSTRKRFT|         Fist of God|\n",
            "|000Silenced|Don't Stop The Mu...|        Rihanna|Addicted 2 Bassli...|\n",
            "|000Silenced|               ObZen|      Meshuggah|               ObZen|\n",
            "|000Silenced|   Yama's Messengers|         Gojira|The Way of All Flesh|\n",
            "|000Silenced|On the Brink of E...|   Napalm Death|Time Waits For No...|\n",
            "|000Silenced|On the Brink of E...|   Napalm Death|Time Waits For No...|\n",
            "|000Silenced|        In Deference|   Napalm Death|      Smear Campaign|\n",
            "|000Silenced|      Post(?)organic|    Decapitated|Organic Hallucinosis|\n",
            "|000Silenced|        Mind Feeders|   Dom & Roland| No Strings Attached|\n",
            "|000Silenced|Necrosadistic War...|Cannibal Corpse|                Kill|\n",
            "|000Silenced|     Dance All Night|   Dom & Roland|          Chronology|\n",
            "|000Silenced|          Late Night|   Dom & Roland|          Chronology|\n",
            "|000Silenced|          Freak Seen|   Dom & Roland|          Chronology|\n",
            "|000Silenced|Paradrenasite (Hi...|   Dom & Roland|          Chronology|\n",
            "|000Silenced|               Rhino|   Dom & Roland|          Chronology|\n",
            "+-----------+--------------------+---------------+--------------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Z7nKCYoZltnv"
      },
      "source": [
        "let's see the schema:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "qVg1jt1OyWdh",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "78529b45-446f-4079-eb60-6683c0bde658"
      },
      "source": [
        "listening_df.printSchema()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "root\n",
            " |-- user_id: string (nullable = true)\n",
            " |-- track: string (nullable = true)\n",
            " |-- artist: string (nullable = true)\n",
            " |-- album: string (nullable = true)\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "JMkQsQt2xSb5"
      },
      "source": [
        "let's see the shape of our dataframe:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "6POkV3YFmh6b",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "7f8c335a-c57c-4edf-9fc4-780573b79791"
      },
      "source": [
        "shape = (listening_df.count(), len(listening_df.columns))\n",
        "print(shape)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "(13758905, 4)\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "XMD0DhFl2FEJ"
      },
      "source": [
        "# Task 3:\n",
        "\n",
        "**Query #0:**\n",
        "select two columns: track and artist"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "FZTdA5wn2TZy",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "3a92a76e-3b44-4c00-a775-0189c4f7c5b7"
      },
      "source": [
        "q0 =  listening_df.select('track','artist')\n",
        "q0.show()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------------+---------------+\n",
            "|               track|         artist|\n",
            "+--------------------+---------------+\n",
            "|           Price Tag|       Jessie J|\n",
            "|Price Tag (Acoust...|       Jessie J|\n",
            "|Be Mine! (Ballad ...|          Robyn|\n",
            "|            Acapella|          Kelis|\n",
            "|   I'm Not Invisible|      The Tease|\n",
            "|Bounce (Feat NORE...|       MSTRKRFT|\n",
            "|Don't Stop The Mu...|        Rihanna|\n",
            "|               ObZen|      Meshuggah|\n",
            "|   Yama's Messengers|         Gojira|\n",
            "|On the Brink of E...|   Napalm Death|\n",
            "|On the Brink of E...|   Napalm Death|\n",
            "|        In Deference|   Napalm Death|\n",
            "|      Post(?)organic|    Decapitated|\n",
            "|        Mind Feeders|   Dom & Roland|\n",
            "|Necrosadistic War...|Cannibal Corpse|\n",
            "|     Dance All Night|   Dom & Roland|\n",
            "|          Late Night|   Dom & Roland|\n",
            "|          Freak Seen|   Dom & Roland|\n",
            "|Paradrenasite (Hi...|   Dom & Roland|\n",
            "|               Rhino|   Dom & Roland|\n",
            "+--------------------+---------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "QRcgXOFs2hjw"
      },
      "source": [
        "**Query #1**:\n",
        "\n",
        "Let's find all of the records of those users who have listened to ***Rihanna***"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ICyiTMVnppLw",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "6bbf4c90-9a5c-4983-bacf-4c8fd8941464"
      },
      "source": [
        "# prompt: query to select artist ass rihanna\n",
        "\n",
        "q1 = listening_df.select('*').filter(listening_df['artist'] == 'Rihanna')\n",
        "q1.show()\n"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-----------+--------------------+-------+--------------------+\n",
            "|    user_id|               track| artist|               album|\n",
            "+-----------+--------------------+-------+--------------------+\n",
            "|000Silenced|Don't Stop The Mu...|Rihanna|Addicted 2 Bassli...|\n",
            "|000Silenced|           Disturbia|Rihanna|Good Girl Gone Ba...|\n",
            "|00williamsl|   Hatin On The Club|Rihanna|              Random|\n",
            "|00williamsl|   Hatin On The Club|Rihanna|              Random|\n",
            "|00williamsl|         Complicated|Rihanna|                Loud|\n",
            "|00williamsl|What's My Name (f...|Rihanna|                Loud|\n",
            "|00williamsl|Kanye West feat R...|Rihanna|                Loud|\n",
            "|    0502008|Only Girl (In the...|Rihanna|                Loud|\n",
            "|      0rdos|Pon De Replay (Re...|Rihanna|    Music of the Sun|\n",
            "|      0rdos|          Now I Know|Rihanna|    Music of the Sun|\n",
            "|      0rdos|There's a Thug in...|Rihanna|    Music of the Sun|\n",
            "|      0rdos|                Rush|Rihanna|    Music of the Sun|\n",
            "|      0rdos|              Let Me|Rihanna|    Music of the Sun|\n",
            "|      0rdos|    Music of the Sun|Rihanna|    Music of the Sun|\n",
            "|      0rdos|     Willing to Wait|Rihanna|    Music of the Sun|\n",
            "|      0rdos|       The Last Time|Rihanna|    Music of the Sun|\n",
            "|      0rdos|If It's Lovin' Th...|Rihanna|    Music of the Sun|\n",
            "|      0rdos|     Here I Go Again|Rihanna|    Music of the Sun|\n",
            "|      0rdos|       Pon de Replay|Rihanna|    Music of the Sun|\n",
            "|      0rdos|                 Cry|Rihanna|  Good Girl Gone Bad|\n",
            "+-----------+--------------------+-------+--------------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "I0IafeyvFU9O"
      },
      "source": [
        "**Query #2:**\n",
        "\n",
        "Let's find top 10 users who are fan of ***Rihanna***"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "3-hM9kMm7JmI",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "9a763e47-99b3-4188-f486-08fc86a3e948"
      },
      "source": [
        "# prompt: find top 10 users who are fans of Rihanna\n",
        "\n",
        "q2 = q1.groupby('user_id').agg(count('user_id').alias('count')).orderBy(desc('count')).limit(10)\n",
        "q2.show()\n"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+---------------+-----+\n",
            "|        user_id|count|\n",
            "+---------------+-----+\n",
            "|        thiessu|  179|\n",
            "|    eyessetkyle|  166|\n",
            "|           adxx|  164|\n",
            "|missnumberthree|  156|\n",
            "|helloiamnatalie|  128|\n",
            "|          nmjnb|  124|\n",
            "|        AndyyyA|  123|\n",
            "|     BIGBANG186|  121|\n",
            "|     mixedvibes|  120|\n",
            "|       AndyKitt|  115|\n",
            "+---------------+-----+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "hgAAl6aAcp41"
      },
      "source": [
        "**Query #3:**\n",
        "\n",
        "find top 10 famous tracks"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "qlh3IUzfJ3_I",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "ac278d00-fad5-4f83-8f41-c343e236dfb1"
      },
      "source": [
        "q3 = listening_df.select('artist','track').groupby('artist','track').agg(count('*').alias('count')).orderBy(desc('count')).limit(10)\n",
        "q3.show()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------+----------------+-----+\n",
            "|        artist|           track|count|\n",
            "+--------------+----------------+-----+\n",
            "| Justin Bieber|           Sorry| 3381|\n",
            "|Arctic Monkeys|Do I Wanna Know?| 2865|\n",
            "|      Bon Iver|     Skinny Love| 2836|\n",
            "|          Zayn|      PILLOWTALK| 2701|\n",
            "|   The Killers|   Mr Brightside| 2690|\n",
            "|       Rihanna|            Work| 2646|\n",
            "|      Bastille|         Pompeii| 2606|\n",
            "|Mumford & Sons| Little Lion Man| 2520|\n",
            "|Mumford & Sons|        The Cave| 2485|\n",
            "| Justin Bieber|   Love Yourself| 2481|\n",
            "+--------------+----------------+-----+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "HqhPhQvjeXt0"
      },
      "source": [
        "**Query #4:**\n",
        "\n",
        "find top 10 famous tracks of ***Rihanna***"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "D_npmdh1ec8y",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "c6eb861a-8304-4282-fb02-6a173c58439c"
      },
      "source": [
        "q4 = q1.select('artist','track').groupby('artist','track').agg(count('*').alias('count')).orderBy(desc('count')).limit(10)\n",
        "q4.show()\n"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-------+--------------------+-----+\n",
            "| artist|               track|count|\n",
            "+-------+--------------------+-----+\n",
            "|Rihanna|                Work| 2646|\n",
            "|Rihanna|Only Girl (In the...| 1749|\n",
            "|Rihanna|We Found Love (fe...| 1575|\n",
            "|Rihanna|                 S&M| 1307|\n",
            "|Rihanna|            Rude Boy| 1303|\n",
            "|Rihanna|            Diamonds| 1224|\n",
            "|Rihanna|      Kiss it Better|  945|\n",
            "|Rihanna| Where Have You Been|  844|\n",
            "|Rihanna|Cheers (Drink to ...|  697|\n",
            "|Rihanna|           Needed Me|  679|\n",
            "+-------+--------------------+-----+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "E6pgcH0p1ZXo"
      },
      "source": [
        "**Query #5:**\n",
        "\n",
        "find top 10 famous albums"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "c5YHm6yKenE7",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "7d3dadfd-32a9-4045-acbf-705144896979"
      },
      "source": [
        "q5 = listening_df.select('artist','album').groupby('artist','album').agg(count('*').alias('count')).orderBy(desc('count')).limit(10)\n",
        "q5.show()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------------+--------------------+-----+\n",
            "|              artist|               album|count|\n",
            "+--------------------+--------------------+-----+\n",
            "|          Kanye West|   The Life Of Pablo|22310|\n",
            "|              The xx|                  xx|14195|\n",
            "|      Arctic Monkeys|                  AM|14090|\n",
            "|               alt-J|     An Awesome Wave|13635|\n",
            "|      Mumford & Sons|        Sigh No More|13543|\n",
            "|      Arctic Monkeys|Whatever People S...|12731|\n",
            "|            Bon Iver|            For Emma|11994|\n",
            "|              Grimes|          Art Angels|11655|\n",
            "|Florence + the Ma...|               Lungs|11362|\n",
            "|               Adele|                  21|11215|\n",
            "+--------------------+--------------------+-----+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "YXev8HQ57bdq"
      },
      "source": [
        "# Task 4 :\n",
        "importing the ***genre.csv*** file:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "tpXSrYfu14PB"
      },
      "source": [
        "genre_csv_path = \"/content/drive/MyDrive/dataset/genre.csv\"\n",
        "genre_df = spark.read.format('csv').option('inferSchema',True).option(\"header\",True).load(genre_csv_path)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "aCHSo36W9RcP"
      },
      "source": [
        "let's check the data"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "LJu4Ouz89O6_",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "1aaaa797-9f4d-4c55-faee-57e00bae9369"
      },
      "source": [
        "genre_df.show()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------------+-----+\n",
            "|              artist|genre|\n",
            "+--------------------+-----+\n",
            "|                Muse| rock|\n",
            "|             Nirvana| rock|\n",
            "|            Bon Jovi| rock|\n",
            "|          The Police| rock|\n",
            "|                Kiss| rock|\n",
            "|       Guns N' Roses| rock|\n",
            "|         Rusted Root| rock|\n",
            "|Katrina and the W...|  pop|\n",
            "|         The Beatles| rock|\n",
            "|        Hall & Oates|  pop|\n",
            "|        Otis Redding| soul|\n",
            "|         Marvin Gaye| soul|\n",
            "|     The Cranberries| rock|\n",
            "|            Survivor| rock|\n",
            "|       Fleetwood Mac|blues|\n",
            "|           Radiohead| rock|\n",
            "|                Toto| rock|\n",
            "|                  U2| rock|\n",
            "|Creedence Clearwa...| rock|\n",
            "|                 REM| rock|\n",
            "+--------------------+-----+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "data = listening_df.join(genre_df, how=\"inner\",on=[\"artist\"])\n",
        "data.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "f8TLiJ5QMq93",
        "outputId": "ca977eaa-3a49-4c6c-e9b3-b8db1e554965"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+------------+-----------+--------------------+--------------------+--------------------+\n",
            "|      artist|    user_id|               track|               album|               genre|\n",
            "+------------+-----------+--------------------+--------------------+--------------------+\n",
            "|    Jessie J|000Silenced|           Price Tag|         Who You Are| Ariana Grande & ...|\n",
            "|    Jessie J|000Silenced|           Price Tag|         Who You Are| Jhene Aiko & Rixton|\n",
            "|    Jessie J|000Silenced|           Price Tag|         Who You Are| Nicki Minaj & Ar...|\n",
            "|    Jessie J|000Silenced|           Price Tag|         Who You Are|                 pop|\n",
            "|    Jessie J|000Silenced|Price Tag (Acoust...|           Price Tag| Ariana Grande & ...|\n",
            "|    Jessie J|000Silenced|Price Tag (Acoust...|           Price Tag| Jhene Aiko & Rixton|\n",
            "|    Jessie J|000Silenced|Price Tag (Acoust...|           Price Tag| Nicki Minaj & Ar...|\n",
            "|    Jessie J|000Silenced|Price Tag (Acoust...|           Price Tag|                 pop|\n",
            "|       Robyn|000Silenced|Be Mine! (Ballad ...|            Be Mine!|                 pop|\n",
            "|       Kelis|000Silenced|            Acapella|            Acapella|                 pop|\n",
            "|   The Tease|000Silenced|   I'm Not Invisible|   I'm Not Invisible|             hip hop|\n",
            "|    MSTRKRFT|000Silenced|Bounce (Feat NORE...|         Fist of God|          electronic|\n",
            "|     Rihanna|000Silenced|Don't Stop The Mu...|Addicted 2 Bassli...|                 SZA|\n",
            "|     Rihanna|000Silenced|Don't Stop The Mu...|Addicted 2 Bassli...| Paul McCartney &...|\n",
            "|     Rihanna|000Silenced|Don't Stop The Mu...|Addicted 2 Bassli...|          Kanye West|\n",
            "|     Rihanna|000Silenced|Don't Stop The Mu...|Addicted 2 Bassli...| Kanye West & Pau...|\n",
            "|     Rihanna|000Silenced|Don't Stop The Mu...|Addicted 2 Bassli...|                 pop|\n",
            "|   Meshuggah|000Silenced|               ObZen|               ObZen|               metal|\n",
            "|      Gojira|000Silenced|   Yama's Messengers|The Way of All Flesh|               metal|\n",
            "|Napalm Death|000Silenced|On the Brink of E...|Time Waits For No...|           grindcore|\n",
            "+------------+-----------+--------------------+--------------------+--------------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "72OpWX7F98qg"
      },
      "source": [
        "Let's inner join these two data frames"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "yM_f5qILBNeI"
      },
      "source": [
        "**Query #6**\n",
        "\n",
        "find top 10 users who are fan of ***pop*** music"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "dognQVlxBi2n",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "c894a7c2-ae13-4bb5-ef06-654a73ad5786"
      },
      "source": [
        "q6 = data.select('user_id').filter(data.genre==\"pop\").groupby('user_id').agg(count(\"*\").alias('count')).orderBy(desc('count')).limit(10)\n",
        "q6.show()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+---------------+-----+\n",
            "|        user_id|count|\n",
            "+---------------+-----+\n",
            "|        01Green|  496|\n",
            "|      momousagi|  400|\n",
            "|          mrpsb|  400|\n",
            "|   BlueKnockOut|  378|\n",
            "|    musicboy80s|  376|\n",
            "|  incultojurgis|  374|\n",
            "| ElektricOrchid|  370|\n",
            "|foreign_fanatic|  350|\n",
            "|   Kevin_Soutar|  346|\n",
            "|  landrover2171|  301|\n",
            "+---------------+-----+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "63quzy7t-zb7"
      },
      "source": [
        "**Query #7**\n",
        "\n",
        "find top 10 famous genres"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "aDFcoPPk-Rhf",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "7a17bcd8-b3f2-4d61-c6d8-d300f1c65d3a"
      },
      "source": [
        "q7 = data.select('genre').groupby('genre').agg(count(\"*\").alias('count')).orderBy(desc('count')).limit(10)\n",
        "q7.show()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+----------+-------+\n",
            "|     genre|  count|\n",
            "+----------+-------+\n",
            "|      rock|2691934|\n",
            "|       pop|1544747|\n",
            "|electronic| 551509|\n",
            "|   hip hop| 532984|\n",
            "|      folk| 438174|\n",
            "|indie rock| 431439|\n",
            "|      punk| 380915|\n",
            "|       r&b| 344101|\n",
            "|     metal| 208107|\n",
            "|     indie| 206726|\n",
            "+----------+-------+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "hrZOAWVgLMZo"
      },
      "source": [
        "# Task 5:\n",
        "**Query #8**\n",
        "\n",
        "find out each user favourite genre"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "H3AWxlkbLvCg",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "550237cd-73f3-4e6e-a39b-fb59ae3d6829"
      },
      "source": [
        "q8_1 =  data.select('user_id','genre').groupby('user_id','genre').agg(count(\"*\").alias('count')).orderBy('user_id')\n",
        "q8_1.show()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-------+--------------------+-----+\n",
            "|user_id|               genre|count|\n",
            "+-------+--------------------+-----+\n",
            "| --Seph| Boston Pops Orch...|    1|\n",
            "| --Seph|               Bjorn|    8|\n",
            "| --Seph|                 pop|    9|\n",
            "| --Seph| brazilian pop music|    1|\n",
            "| --Seph|          Nils Frahm|    1|\n",
            "| --Seph| Snoop Dogg & Dev...|    4|\n",
            "| --Seph|               dance|    1|\n",
            "| --Seph|        instrumental|    1|\n",
            "| --Seph|  progressive trance|    1|\n",
            "| --Seph|   Vladimir Spivakov|    1|\n",
            "| --Seph|          Thom Yorke|    2|\n",
            "| --Seph|               samba|    2|\n",
            "| --Seph|               Ruben|    4|\n",
            "| --Seph|             ambient|    6|\n",
            "| --Seph|           classical|   31|\n",
            "| --Seph| Sara Mingardo & ...|    1|\n",
            "| --Seph|               indie|    3|\n",
            "| --Seph|             baroque|    1|\n",
            "| --Seph|         heavy metal|    1|\n",
            "| --Seph|     Hilko Schomerus|    1|\n",
            "+-------+--------------------+-----+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Soy2bMxQN-Ub",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "792bc0d5-9bee-4116-b670-32e52a5a67c8"
      },
      "source": [
        "q8_2 = q8_1.groupby('user_id').agg(max(struct(col('count'),col('genre'))).alias('max')).select(col('user_id'),col('max.genre'))\n",
        "q8_2.show()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+---------------+-----------+\n",
            "|        user_id|      genre|\n",
            "+---------------+-----------+\n",
            "|      --Shinn--|       rock|\n",
            "|       --ollz--|       rock|\n",
            "|     -Amnesiac-|      metal|\n",
            "|         -Bish-|       rock|\n",
            "|        -Danni-|       rock|\n",
            "|          -DeWp|    hip hop|\n",
            "|      -Faction-|       rock|\n",
            "|-February-Star-|       rock|\n",
            "|        -Gemma-|british pop|\n",
            "|   -Gingergirl-|        pop|\n",
            "|        -Hazel-|   synthpop|\n",
            "|        -Heiko-| electronic|\n",
            "|         -Katie|      lo-fi|\n",
            "|      -Krystal-|       rock|\n",
            "|         -Mark-| indie rock|\n",
            "|       -Mullet-|  metalcore|\n",
            "|          -Robs|       rock|\n",
            "| -SleepingStar-|black metal|\n",
            "|      -Stifler-|       rock|\n",
            "|  -Toxic-Waste-|  aggrotech|\n",
            "+---------------+-----------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "6oIyhOHkCDuv"
      },
      "source": [
        "**Query #9**\n",
        "\n",
        "find out how many pop,rock,metal and hip hop singers we have\n",
        "\n",
        "and then visulize it using bar chart"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "7_lEjNKVCDJv",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "7c1c8f27-3534-463c-be84-7dbc61934925"
      },
      "source": [
        "q9 =  genre_df.select(\"genre\").filter((col('genre')==\"pop\")|(col('genre')==\"rock\")|(col('genre')==\"metal\")|(col('genre')==\"hip hop\")).groupby(\"genre\").agg(count('genre').alias('count'))\n",
        "q9.show()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-------+-----+\n",
            "|  genre|count|\n",
            "+-------+-----+\n",
            "|    pop| 6960|\n",
            "|hip hop| 4288|\n",
            "|  metal| 1854|\n",
            "|   rock| 9066|\n",
            "+-------+-----+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "h0h2SSk8InMH"
      },
      "source": [
        "Now, let's visualize the results using ***matplotlib***"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "J-n8gOC0Imj5"
      },
      "source": [
        "q9_list = q9.collect()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "CMigHLrEQgKv"
      },
      "source": [
        "labels = [row[\"genre\"] for row in q9_list]\n",
        "counts = [row[\"count\"] for row in q9_list]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "FYLE4Mbu_Lbu",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "b4405e45-5218-4bcc-b6aa-d47e4a8bf645"
      },
      "source": [
        "print(labels)\n",
        "print(counts)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "['pop', 'hip hop', 'metal', 'rock']\n",
            "[6960, 4288, 1854, 9066]\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "1iDrvEwYQ4AE"
      },
      "source": [
        "now lets visualize these two lists using a bar chart"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "XOOq1U9BQjKI",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 447
        },
        "outputId": "c248af7a-4805-4478-890e-3c6792e225f5"
      },
      "source": [
        "plts.bar(labels,counts)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "<BarContainer object of 4 artists>"
            ]
          },
          "metadata": {},
          "execution_count": 38
        },
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "<Figure size 640x480 with 1 Axes>"
            ],
            "image/png": "iVBORw0KGgoAAAANSUhEUgAAAjAAAAGdCAYAAAAMm0nCAAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjcuMSwgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy/bCgiHAAAACXBIWXMAAA9hAAAPYQGoP6dpAAAkXklEQVR4nO3de1CVdeLH8Q+IXLwc8JIHSVRmdRQ2854eLbNkpKS2NrbNIrUkLQVXpLz1S7KrSnktV9cypUkrm8kyNZWwrAhRScyUyG11pAzYUjh5CYTz/P7Y8RlPmYmXDl98v2bOjOd5vs9zvo/PCG+fc/OzLMsSAACAQfx9PQEAAIDaImAAAIBxCBgAAGAcAgYAABiHgAEAAMYhYAAAgHEIGAAAYBwCBgAAGCfA1xO4VDwejw4dOqSmTZvKz8/P19MBAADnwLIs/fTTT4qIiJC//29fZ6m3AXPo0CFFRkb6ehoAAOA8FBcXq02bNr+5vt4GTNOmTSX97y/A4XD4eDYAAOBcuN1uRUZG2r/Hf0u9DZhTTxs5HA4CBgAAw/zeyz94ES8AADAOAQMAAIxDwAAAAOMQMAAAwDgEDAAAMA4BAwAAjEPAAAAA4xAwAADAOAQMAAAwDgEDAACMQ8AAAADjEDAAAMA4BAwAADAOAQMAAIwT4OsJAABwvtpPWefrKVy2DsyM9+njcwUGAAAYh4ABAADGIWAAAIBxCBgAAGAcAgYAABiHgAEAAMYhYAAAgHEIGAAAYBwCBgAAGIeAAQAAxiFgAACAcQgYAABgHAIGAAAYh4ABAADGIWAAAIBxCBgAAGAcAgYAABiHgAEAAMYhYAAAgHEIGAAAYBwCBgAAGIeAAQAAxiFgAACAcQgYAABgHAIGAAAYh4ABAADGIWAAAIBxCBgAAGAcAgYAABiHgAEAAMYhYAAAgHEIGAAAYBwCBgAAGIeAAQAAxiFgAACAcQgYAABgnFoFTE1NjaZNm6aoqCiFhIToT3/6k5566ilZlmWPsSxL6enpat26tUJCQhQbG6t9+/Z57efw4cNKTEyUw+FQWFiYkpKSdPToUa8xX3zxha677joFBwcrMjJSGRkZF3CYAACgPqlVwMyaNUuLFi3Siy++qMLCQs2aNUsZGRl64YUX7DEZGRlasGCBFi9erLy8PDVu3FhxcXH6+eef7TGJiYnas2ePsrKytHbtWn388ccaPXq0vd7tdmvw4MFq166d8vPz9dxzz2n69OlasmTJRThkAABgOj/r9Msnv+OWW26R0+nU0qVL7WUJCQkKCQnRa6+9JsuyFBERoYcffliPPPKIJKmiokJOp1PLly/X0KFDVVhYqJiYGG3fvl29evWSJG3YsEFDhgzRt99+q4iICC1atEj/93//p5KSEgUGBkqSpkyZonfeeUdfffXVOc3V7XYrNDRUFRUVcjgc5/wXAgAwR/sp63w9hcvWgZnxl2S/5/r7u1ZXYPr166fs7Gx9/fXXkqRdu3bp008/1c033yxJ2r9/v0pKShQbG2tvExoaqj59+ig3N1eSlJubq7CwMDteJCk2Nlb+/v7Ky8uzxwwYMMCOF0mKi4tTUVGRjhw5UpspAwCAeiigNoOnTJkit9utzp07q0GDBqqpqdEzzzyjxMRESVJJSYkkyel0em3ndDrtdSUlJWrVqpX3JAIC1Lx5c68xUVFRv9rHqXXNmjX71dwqKytVWVlp33e73bU5NAAAYJBaXYFZtWqVVqxYoZUrV+rzzz9XZmamnn/+eWVmZl6q+Z2zGTNmKDQ01L5FRkb6ekoAAOASqVXATJw4UVOmTNHQoUPVpUsXDRs2TBMmTNCMGTMkSeHh4ZKk0tJSr+1KS0vtdeHh4SorK/NaX11drcOHD3uNOdM+Tn+MX5o6daoqKirsW3FxcW0ODQAAGKRWAXP8+HH5+3tv0qBBA3k8HklSVFSUwsPDlZ2dba93u93Ky8uTy+WSJLlcLpWXlys/P98es3nzZnk8HvXp08ce8/HHH+vkyZP2mKysLHXq1OmMTx9JUlBQkBwOh9cNAADUT7UKmFtvvVXPPPOM1q1bpwMHDmj16tWaM2eO/vrXv0qS/Pz8lJqaqqefflpr1qzR7t27NXz4cEVEROj222+XJEVHR+umm27SqFGjtG3bNuXk5CglJUVDhw5VRESEJOmee+5RYGCgkpKStGfPHr355puaP3++0tLSLu7RAwAAI9XqRbwvvPCCpk2bprFjx6qsrEwRERF68MEHlZ6ebo+ZNGmSjh07ptGjR6u8vFzXXnutNmzYoODgYHvMihUrlJKSokGDBsnf318JCQlasGCBvT40NFSbNm1ScnKyevbsqZYtWyo9Pd3rs2IAAMDlq1afA2MSPgcGAOo/PgfGd4z6HBgAAIC6gIABAADGIWAAAIBxCBgAAGAcAgYAABiHgAEAAMYhYAAAgHEIGAAAYBwCBgAAGIeAAQAAxiFgAACAcQgYAABgHAIGAAAYh4ABAADGIWAAAIBxCBgAAGAcAgYAABiHgAEAAMYhYAAAgHEIGAAAYBwCBgAAGIeAAQAAxiFgAACAcQgYAABgHAIGAAAYh4ABAADGIWAAAIBxCBgAAGAcAgYAABiHgAEAAMYhYAAAgHEIGAAAYBwCBgAAGIeAAQAAxgnw9QRM1H7KOl9P4bJ1YGa8r6cAAKgDuAIDAACMQ8AAAADjEDAAAMA4BAwAADAOAQMAAIxDwAAAAOMQMAAAwDgEDAAAMA4BAwAAjEPAAAAA4xAwAADAOAQMAAAwDgEDAACMQ8AAAADjEDAAAMA4BAwAADAOAQMAAIxDwAAAAOMQMAAAwDgEDAAAMA4BAwAAjEPAAAAA4xAwAADAOAQMAAAwDgEDAACMQ8AAAADjEDAAAMA4BAwAADAOAQMAAIxDwAAAAOMQMAAAwDgEDAAAME6tA+a7777TvffeqxYtWigkJERdunTRjh077PWWZSk9PV2tW7dWSEiIYmNjtW/fPq99HD58WImJiXI4HAoLC1NSUpKOHj3qNeaLL77Qddddp+DgYEVGRiojI+M8DxEAANQ3tQqYI0eOqH///mrYsKHef/997d27V7Nnz1azZs3sMRkZGVqwYIEWL16svLw8NW7cWHFxcfr555/tMYmJidqzZ4+ysrK0du1affzxxxo9erS93u12a/DgwWrXrp3y8/P13HPPafr06VqyZMlFOGQAAGC6gNoMnjVrliIjI7Vs2TJ7WVRUlP1ny7I0b948PfbYY7rtttskSa+++qqcTqfeeecdDR06VIWFhdqwYYO2b9+uXr16SZJeeOEFDRkyRM8//7wiIiK0YsUKVVVV6ZVXXlFgYKD+/Oc/q6CgQHPmzPEKHQAAcHmq1RWYNWvWqFevXrrzzjvVqlUrde/eXS+99JK9fv/+/SopKVFsbKy9LDQ0VH369FFubq4kKTc3V2FhYXa8SFJsbKz8/f2Vl5dnjxkwYIACAwPtMXFxcSoqKtKRI0fOOLfKykq53W6vGwAAqJ9qFTD/+c9/tGjRInXs2FEbN27UmDFj9I9//EOZmZmSpJKSEkmS0+n02s7pdNrrSkpK1KpVK6/1AQEBat68udeYM+3j9Mf4pRkzZig0NNS+RUZG1ubQAACAQWoVMB6PRz169NCzzz6r7t27a/To0Ro1apQWL158qeZ3zqZOnaqKigr7Vlxc7OspAQCAS6RWAdO6dWvFxMR4LYuOjtbBgwclSeHh4ZKk0tJSrzGlpaX2uvDwcJWVlXmtr66u1uHDh73GnGkfpz/GLwUFBcnhcHjdAABA/VSrgOnfv7+Kioq8ln399ddq166dpP+9oDc8PFzZ2dn2erfbrby8PLlcLkmSy+VSeXm58vPz7TGbN2+Wx+NRnz597DEff/yxTp48aY/JyspSp06dvN7xBAAALk+1CpgJEyZo69atevbZZ/Xvf/9bK1eu1JIlS5ScnCxJ8vPzU2pqqp5++mmtWbNGu3fv1vDhwxUREaHbb79d0v+u2Nx0000aNWqUtm3bppycHKWkpGjo0KGKiIiQJN1zzz0KDAxUUlKS9uzZozfffFPz589XWlraxT16AABgpFq9jbp3795avXq1pk6dqieffFJRUVGaN2+eEhMT7TGTJk3SsWPHNHr0aJWXl+vaa6/Vhg0bFBwcbI9ZsWKFUlJSNGjQIPn7+yshIUELFiyw14eGhmrTpk1KTk5Wz5491bJlS6Wnp/MWagAAIEnysyzL8vUkLgW3263Q0FBVVFRc9NfDtJ+y7qLuD+fuwMx4X08BQB3Cz2PfuVQ/j8/19zffhQQAAIxDwAAAAOMQMAAAwDgEDAAAMA4BAwAAjEPAAAAA4xAwAADAOAQMAAAwDgEDAACMQ8AAAADjEDAAAMA4BAwAADAOAQMAAIxDwAAAAOMQMAAAwDgEDAAAMA4BAwAAjEPAAAAA4xAwAADAOAQMAAAwDgEDAACMQ8AAAADjEDAAAMA4BAwAADAOAQMAAIxDwAAAAOMQMAAAwDgEDAAAMA4BAwAAjEPAAAAA4xAwAADAOAQMAAAwDgEDAACMQ8AAAADjEDAAAMA4BAwAADAOAQMAAIxDwAAAAOMQMAAAwDgEDAAAMA4BAwAAjEPAAAAA4xAwAADAOAQMAAAwDgEDAACMQ8AAAADjEDAAAMA4BAwAADAOAQMAAIxDwAAAAOMQMAAAwDgEDAAAMA4BAwAAjEPAAAAA4xAwAADAOAQMAAAwDgEDAACMQ8AAAADjEDAAAMA4BAwAADBOgK8nANQl7aes8/UULlsHZsb7egoADMIVGAAAYBwCBgAAGIeAAQAAxiFgAACAcQgYAABgHAIGAAAY54ICZubMmfLz81Nqaqq97Oeff1ZycrJatGihJk2aKCEhQaWlpV7bHTx4UPHx8WrUqJFatWqliRMnqrq62mvMRx99pB49eigoKEgdOnTQ8uXLL2SqAACgHjnvgNm+fbv+9a9/6eqrr/ZaPmHCBL333nt66623tGXLFh06dEh33HGHvb6mpkbx8fGqqqrSZ599pszMTC1fvlzp6en2mP379ys+Pl433HCDCgoKlJqaqgceeEAbN2483+kCAIB65LwC5ujRo0pMTNRLL72kZs2a2csrKiq0dOlSzZkzRzfeeKN69uypZcuW6bPPPtPWrVslSZs2bdLevXv12muvqVu3brr55pv11FNPaeHChaqqqpIkLV68WFFRUZo9e7aio6OVkpKiv/3tb5o7d+5FOGQAAGC68wqY5ORkxcfHKzY21mt5fn6+Tp486bW8c+fOatu2rXJzcyVJubm56tKli5xOpz0mLi5Obrdbe/bsscf8ct9xcXH2Ps6ksrJSbrfb6wYAAOqnWn+VwBtvvKHPP/9c27dv/9W6kpISBQYGKiwszGu50+lUSUmJPeb0eDm1/tS6s41xu906ceKEQkJCfvXYM2bM0BNPPFHbwwEAAAaq1RWY4uJijR8/XitWrFBwcPClmtN5mTp1qioqKuxbcXGxr6cEAAAukVoFTH5+vsrKytSjRw8FBAQoICBAW7Zs0YIFCxQQECCn06mqqiqVl5d7bVdaWqrw8HBJUnh4+K/elXTq/u+NcTgcZ7z6IklBQUFyOBxeNwAAUD/VKmAGDRqk3bt3q6CgwL716tVLiYmJ9p8bNmyo7Oxse5uioiIdPHhQLpdLkuRyubR7926VlZXZY7KysuRwOBQTE2OPOX0fp8ac2gcAALi81eo1ME2bNtVVV13ltaxx48Zq0aKFvTwpKUlpaWlq3ry5HA6Hxo0bJ5fLpb59+0qSBg8erJiYGA0bNkwZGRkqKSnRY489puTkZAUFBUmSHnroIb344ouaNGmSRo4cqc2bN2vVqlVat27dxThmAABguFq/iPf3zJ07V/7+/kpISFBlZaXi4uL0z3/+017foEEDrV27VmPGjJHL5VLjxo01YsQIPfnkk/aYqKgorVu3ThMmTND8+fPVpk0bvfzyy4qLi7vY0wUAAAa64ID56KOPvO4HBwdr4cKFWrhw4W9u065dO61fv/6s+x04cKB27tx5odMDAAD1EN+FBAAAjEPAAAAA4xAwAADAOAQMAAAwDgEDAACMQ8AAAADjEDAAAMA4BAwAADAOAQMAAIxDwAAAAOMQMAAAwDgEDAAAMA4BAwAAjEPAAAAA4xAwAADAOAQMAAAwDgEDAACMQ8AAAADjEDAAAMA4BAwAADAOAQMAAIxDwAAAAOMQMAAAwDgEDAAAMA4BAwAAjEPAAAAA4xAwAADAOAQMAAAwDgEDAACMQ8AAAADjEDAAAMA4BAwAADAOAQMAAIxDwAAAAOMQMAAAwDgEDAAAMA4BAwAAjEPAAAAA4xAwAADAOAQMAAAwDgEDAACMQ8AAAADjEDAAAMA4BAwAADAOAQMAAIxDwAAAAOMQMAAAwDgEDAAAMA4BAwAAjEPAAAAA4xAwAADAOAQMAAAwDgEDAACMQ8AAAADjEDAAAMA4BAwAADAOAQMAAIxDwAAAAOMQMAAAwDgEDAAAMA4BAwAAjEPAAAAA4xAwAADAOAQMAAAwDgEDAACMQ8AAAADj1CpgZsyYod69e6tp06Zq1aqVbr/9dhUVFXmN+fnnn5WcnKwWLVqoSZMmSkhIUGlpqdeYgwcPKj4+Xo0aNVKrVq00ceJEVVdXe4356KOP1KNHDwUFBalDhw5avnz5+R0hAACod2oVMFu2bFFycrK2bt2qrKwsnTx5UoMHD9axY8fsMRMmTNB7772nt956S1u2bNGhQ4d0xx132OtramoUHx+vqqoqffbZZ8rMzNTy5cuVnp5uj9m/f7/i4+N1ww03qKCgQKmpqXrggQe0cePGi3DIAADAdAG1Gbxhwwav+8uXL1erVq2Un5+vAQMGqKKiQkuXLtXKlSt14403SpKWLVum6Ohobd26VX379tWmTZu0d+9effDBB3I6nerWrZueeuopTZ48WdOnT1dgYKAWL16sqKgozZ49W5IUHR2tTz/9VHPnzlVcXNxFOnQAAGCqC3oNTEVFhSSpefPmkqT8/HydPHlSsbGx9pjOnTurbdu2ys3NlSTl5uaqS5cucjqd9pi4uDi53W7t2bPHHnP6Pk6NObUPAABweavVFZjTeTwepaamqn///rrqqqskSSUlJQoMDFRYWJjXWKfTqZKSEnvM6fFyav2pdWcb43a7deLECYWEhPxqPpWVlaqsrLTvu93u8z00AABQx533FZjk5GR9+eWXeuONNy7mfM7bjBkzFBoaat8iIyN9PSUAAHCJnFfApKSkaO3atfrwww/Vpk0be3l4eLiqqqpUXl7uNb60tFTh4eH2mF++K+nU/d8b43A4znj1RZKmTp2qiooK+1ZcXHw+hwYAAAxQq4CxLEspKSlavXq1Nm/erKioKK/1PXv2VMOGDZWdnW0vKyoq0sGDB+VyuSRJLpdLu3fvVllZmT0mKytLDodDMTEx9pjT93FqzKl9nElQUJAcDofXDQAA1E+1eg1McnKyVq5cqXfffVdNmza1X7MSGhqqkJAQhYaGKikpSWlpaWrevLkcDofGjRsnl8ulvn37SpIGDx6smJgYDRs2TBkZGSopKdFjjz2m5ORkBQUFSZIeeughvfjii5o0aZJGjhypzZs3a9WqVVq3bt1FPnwAAGCiWl2BWbRokSoqKjRw4EC1bt3avr355pv2mLlz5+qWW25RQkKCBgwYoPDwcL399tv2+gYNGmjt2rVq0KCBXC6X7r33Xg0fPlxPPvmkPSYqKkrr1q1TVlaWunbtqtmzZ+vll1/mLdQAAEBSLa/AWJb1u2OCg4O1cOFCLVy48DfHtGvXTuvXrz/rfgYOHKidO3fWZnoA8JvaT+EKrq8cmBnv6ymgHuK7kAAAgHEIGAAAYBwCBgAAGIeAAQAAxiFgAACAcQgYAABgHAIGAAAYh4ABAADGIWAAAIBxCBgAAGAcAgYAABiHgAEAAMYhYAAAgHEIGAAAYBwCBgAAGIeAAQAAxiFgAACAcQgYAABgHAIGAAAYh4ABAADGIWAAAIBxCBgAAGAcAgYAABiHgAEAAMYhYAAAgHEIGAAAYBwCBgAAGIeAAQAAxiFgAACAcQgYAABgHAIGAAAYh4ABAADGIWAAAIBxCBgAAGAcAgYAABiHgAEAAMYhYAAAgHEIGAAAYBwCBgAAGIeAAQAAxiFgAACAcQgYAABgHAIGAAAYh4ABAADGIWAAAIBxCBgAAGAcAgYAABiHgAEAAMYhYAAAgHEIGAAAYBwCBgAAGIeAAQAAxiFgAACAcQgYAABgHAIGAAAYh4ABAADGIWAAAIBxCBgAAGAcAgYAABiHgAEAAMYhYAAAgHEIGAAAYBwCBgAAGIeAAQAAxiFgAACAcQgYAABgHAIGAAAYp04HzMKFC9W+fXsFBwerT58+2rZtm6+nBAAA6oA6GzBvvvmm0tLS9Pjjj+vzzz9X165dFRcXp7KyMl9PDQAA+FidDZg5c+Zo1KhRuv/++xUTE6PFixerUaNGeuWVV3w9NQAA4GMBvp7AmVRVVSk/P19Tp061l/n7+ys2Nla5ubln3KayslKVlZX2/YqKCkmS2+2+6PPzVB6/6PvEubkU5/N0nFvf4dzWX5fy3HJefedSnddT+7Us66zj6mTA/PDDD6qpqZHT6fRa7nQ69dVXX51xmxkzZuiJJ5741fLIyMhLMkf4Rug8X88Alwrntv7i3NZPl/q8/vTTTwoNDf3N9XUyYM7H1KlTlZaWZt/3eDw6fPiwWrRoIT8/Px/OrG5xu92KjIxUcXGxHA6Hr6eDi4hzWz9xXusvzu2ZWZaln376SREREWcdVycDpmXLlmrQoIFKS0u9lpeWlio8PPyM2wQFBSkoKMhrWVhY2KWaovEcDgf/YOopzm39xHmtvzi3v3a2Ky+n1MkX8QYGBqpnz57Kzs62l3k8HmVnZ8vlcvlwZgAAoC6ok1dgJCktLU0jRoxQr169dM0112jevHk6duyY7r//fl9PDQAA+FidDZi77rpL//3vf5Wenq6SkhJ169ZNGzZs+NULe1E7QUFBevzxx3/1dBvMx7mtnziv9Rfn9sL4Wb/3PiUAAIA6pk6+BgYAAOBsCBgAAGAcAgYAABiHgAH+IAMHDlRqaupZx/j5+emdd965oMdp37695s2bd0H7QP1y33336fbbb/f1NFALBw4ckJ+fnwoKCnw9lTqrzr4LCbgcff/992rWrJmvp4E6auDAgerWrRuBCoiAAeqU3/qkaQBmqaqqUmBgoK+nUa/xFFI9MHDgQKWkpCglJUWhoaFq2bKlpk2bZn+T55EjRzR8+HA1a9ZMjRo10s0336x9+/bZ2y9fvlxhYWF655131LFjRwUHBysuLk7FxcW+OqR6y+PxaNKkSWrevLnCw8M1ffp0r/WnP4V06hLyG2+8oX79+ik4OFhXXXWVtmzZ8ruPc/z4cY0cOVJNmzZV27ZttWTJEq/1u3fv1o033qiQkBC1aNFCo0eP1tGjR+31p55yeOKJJ3TFFVfI4XDooYceUlVV1QX/HVwuBg4cqHHjxik1NVXNmjWT0+nUSy+9ZH8gZ9OmTdWhQwe9//779jZffvmlbr75ZjVp0kROp1PDhg3TDz/8IOl/52TLli2aP3++/Pz85OfnpwMHDqimpkZJSUmKiopSSEiIOnXqpPnz5/vqsC9bp34Op6amqmXLloqLi9OWLVt0zTXXKCgoSK1bt9aUKVNUXV1tb+PxeJSRkaEOHTooKChIbdu21TPPPHPG/dfU1GjkyJHq3LmzDh48+EcdVp1GwNQTmZmZCggI0LZt2zR//nzNmTNHL7/8sqT//eDbsWOH1qxZo9zcXFmWpSFDhujkyZP29sePH9czzzyjV199VTk5OSovL9fQoUN9dTj1VmZmpho3bqy8vDxlZGToySefVFZW1lm3mThxoh5++GHt3LlTLpdLt956q3788cezbjN79mz16tVLO3fu1NixYzVmzBgVFRVJko4dO6a4uDg1a9ZM27dv11tvvaUPPvhAKSkpXvvIzs5WYWGhPvroI73++ut6++23z/iN7/htmZmZatmypbZt26Zx48ZpzJgxuvPOO9WvXz99/vnnGjx4sIYNG6bjx4+rvLxcN954o7p3764dO3Zow4YNKi0t1d///ndJ0vz58+VyuTRq1Ch9//33+v777xUZGSmPx6M2bdrorbfe0t69e5Wenq5HH31Uq1at8vHRX34yMzMVGBionJwcTZ8+XUOGDFHv3r21a9cuLVq0SEuXLtXTTz9tj586dapmzpypadOmae/evVq5cuUZP6y1srJSd955pwoKCvTJJ5+obdu2f+Rh1V0WjHf99ddb0dHRlsfjsZdNnjzZio6Otr7++mtLkpWTk2Ov++GHH6yQkBBr1apVlmVZ1rJlyyxJ1tatW+0xhYWFliQrLy/vjzuQeu7666+3rr32Wq9lvXv3tiZPnmzfl2StXr3asizL2r9/vyXJmjlzpr3+5MmTVps2baxZs2b95uO0a9fOuvfee+37Ho/HatWqlbVo0SLLsixryZIlVrNmzayjR4/aY9atW2f5+/tbJSUllmVZ1ogRI6zmzZtbx44ds8csWrTIatKkiVVTU3MeR3/5+eX5rq6utho3bmwNGzbMXvb9999bkqzc3FzrqaeesgYPHuy1j+LiYkuSVVRUZO9z/Pjxv/vYycnJVkJCgn1/xIgR1m233XZhB4Szuv76663u3bvb9x999FGrU6dOXj+XFy5caP8bcrvdVlBQkPXSSy+dcX+n/v1/8skn1qBBg6xrr73WKi8vv+THYRKuwNQTffv2lZ+fn33f5XJp37592rt3rwICAtSnTx97XYsWLdSpUycVFhbaywICAtS7d2/7fufOnRUWFuY1Bhfu6quv9rrfunVrlZWVnXWb07/ANCAgQL169frd83L64/j5+Sk8PNx+nMLCQnXt2lWNGze2x/Tv318ej8e+SiNJXbt2VaNGjbzmcfToUZ5arIXTz0ODBg3UokULdenSxV526n/bZWVl2rVrlz788EM1adLEvnXu3FmS9M0335z1cRYuXKiePXvqiiuuUJMmTbRkyRKeZvCBnj172n8uLCyUy+Xy+rncv39/HT16VN9++60KCwtVWVmpQYMGnXWfd999t44dO6ZNmzad0zc0X04IGOAP1LBhQ6/7fn5+8ng8xj4Ozu5M5+H0Zad+uXk8Hh09elS33nqrCgoKvG779u3TgAEDfvMx3njjDT3yyCNKSkrSpk2bVFBQoPvvv5/XK/nA6f8p+D0hISHnNG7IkCH64osvlJube77TqrcImHoiLy/P6/7WrVvVsWNHxcTEqLq62mv9jz/+qKKiIsXExNjLqqurtWPHDvt+UVGRysvLFR0dfeknj7PaunWr/efq6mrl5+df0HmJjo7Wrl27dOzYMXtZTk6O/P391alTJ3vZrl27dOLECa95NGnSRJGRkef92PhtPXr00J49e9S+fXt16NDB63bqF2NgYKBqamq8tsvJyVG/fv00duxYde/eXR06dPjdKza49KKjo+3XHJ6Sk5Ojpk2bqk2bNurYsaNCQkKUnZ191v2MGTNGM2fO1F/+8pdzegH/5YSAqScOHjyotLQ0FRUV6fXXX9cLL7yg8ePHq2PHjrrttts0atQoffrpp9q1a5fuvfdeXXnllbrtttvs7Rs2bKhx48YpLy9P+fn5uu+++9S3b19dc801PjwqSP97emD16tX66quvlJycrCNHjmjkyJHnvb/ExEQFBwdrxIgR+vLLL/Xhhx9q3LhxGjZsmNcLCKuqqpSUlKS9e/dq/fr1evzxx5WSkiJ/f35sXArJyck6fPiw7r77bm3fvl3ffPONNm7cqPvvv9+Olvbt2ysvL08HDhzQDz/8II/Ho44dO2rHjh3auHGjvv76a02bNk3bt2/38dFg7NixKi4u1rhx4/TVV1/p3Xff1eOPP660tDT5+/srODhYkydP1qRJk/Tqq6/qm2++0datW7V06dJf7WvcuHF6+umndcstt+jTTz/1wdHUTXwOTD0xfPhwnThxQtdcc40aNGig8ePHa/To0ZKkZcuWafz48brllltUVVWlAQMGaP369V6Xshs1aqTJkyfrnnvu0XfffafrrrvujP+Q8MebOXOmZs6cqYKCAnXo0EFr1qxRy5Ytz3t/jRo10saNGzV+/Hj17t1bjRo1UkJCgubMmeM1btCgQerYsaMGDBigyspK3X333b962zcunoiICOXk5Gjy5MkaPHiwKisr1a5dO9100012ND7yyCMaMWKEYmJidOLECe3fv18PPvigdu7cqbvuukt+fn66++67NXbsWK+3Z+OPd+WVV2r9+vWaOHGiunbtqubNmyspKUmPPfaYPWbatGkKCAhQenq6Dh06pNatW+uhhx464/5SU1Pl8Xg0ZMgQbdiwQf369fujDqXO8rNOv74FI13op3MuX75cqampKi8vv6jzwoU5cOCAoqKitHPnTnXr1u0Pfez77rtP5eXlF/y1BgBwqXAtGAAAGIeAAQAAxuEpJAAAYByuwAAAAOMQMAAAwDgEDAAAMA4BAwAAjEPAAAAA4xAwAADAOAQMAAAwDgEDAACMQ8AAAADj/D9OJNaZVVIXYQAAAABJRU5ErkJggg==\n"
          },
          "metadata": {}
        }
      ]
    }
  ]
}