PySpark_ETL.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "authorship_tag": "ABX9TyM0d5qxBnmVWF1a0R2thVkD",
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/sumitra14/PySpark_ETL/blob/main/PySpark_ETL.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# **PySpark ETL Project - Netflix TV Shows and Movies**\n",
        "\n",
        "\n",
        "   **DataSource** - https://www.kaggle.com/datasets/victorsoeiro/netflix-tv-shows-and-movies\n",
        "\n",
        "   **Pyspark in goggle colab** - https://www.analyticsvidhya.com/blog/2020/11/a-must-read-guide-on-how-to-work-with-pyspark-on-google-colab-for-data-scientists/\n",
        "\n",
        "\n",
        "   **Tasks performed in this Notebook** -\n",
        "\n",
        "\n",
        "\n",
        "*   Set up pyspark\n",
        "*   Unzip Data file\n",
        "*   Extract data from csv file to Pandas Dataframe (Using Pandas DatFrame instead of Spark DataFrame to parse the CSV file as it handles internal commas(comma within data fields) better.\n",
        "*   Dropping unecessary columns and then creating from Spark DF from Pandas DF\n",
        "*   Cleaning Data using transformations\n",
        "*   Exploring Data to get insights\n",
        "*   Loading the Cleaned Data back to csv file(This csv file can then be used by data analysts,data scientists to find more insights and do staistical analysis for predictions)\n",
        "\n",
        "\n",
        "\n",
        "\n",
        "\n",
        "\n",
        "\n",
        "\n",
        "\n",
        "   \n",
        "\n",
        "   \n",
        "  \n",
        "  \n",
        "\n",
        "\n"
      ],
      "metadata": {
        "id": "joGQ2UNrx3fn"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "#Connecting Notebook to Goggle Drive as here Data is Stored in drive, \n",
        "#however in real world it can be any storage system like HDFS(A file system that is distributed amongst many networked computers or nodes) \n",
        "#or S3(aws object storage)\n",
        "\n",
        "from google.colab import drive\n",
        "drive.mount('/content/drive')"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "6HMzhfhlHBqp",
        "outputId": "bff870f1-2530-4897-8f53-75e12db32f0b"
      },
      "execution_count": 1,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Mounted at /content/drive\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#Setting up PySpark in Colab\n",
        "#first task is to download Java.\n",
        "\n",
        "!apt-get install openjdk-8-jdk-headless -qq > /dev/null"
      ],
      "metadata": {
        "id": "SQqaVXy4HLd3"
      },
      "execution_count": 2,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#install Apache Spark \n",
        "\n",
        "!wget -q https://dlcdn.apache.org/spark/spark-3.3.1/spark-3.3.1-bin-hadoop3.tgz\n",
        "!tar xf spark-3.3.1-bin-hadoop3.tgz"
      ],
      "metadata": {
        "id": "Jq8NKI0_HrU_"
      },
      "execution_count": 4,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#install findspark library as it will locate Spark on the system and import it as a regular library.\n",
        "\n",
        "!pip install -q findspark"
      ],
      "metadata": {
        "id": "JFSer85xH0IG"
      },
      "execution_count": 5,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#Setting the environment path as it will enable us to run Pyspark in the Colab environment.\n",
        "\n",
        "import os\n",
        "os.environ[\"JAVA_HOME\"] = \"/usr/lib/jvm/java-8-openjdk-amd64\"\n",
        "os.environ[\"SPARK_HOME\"] = \"/content/spark-3.3.1-bin-hadoop3\""
      ],
      "metadata": {
        "id": "gklEHQBXH_jR"
      },
      "execution_count": 6,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#Reading Data from Drive\n",
        "\n",
        "!unzip \"/content/drive/MyDrive/netflix_tvshows_movies.zip\""
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "wse0ODzfIJNt",
        "outputId": "f225df12-d87c-4c43-efe5-a2daab020eaa"
      },
      "execution_count": 7,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Archive:  /content/drive/MyDrive/netflix_tvshows_movies.zip\n",
            "  inflating: credits.csv             \n",
            "  inflating: titles.csv              \n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#We need to locate Spark in the system. For that, we import findspark and use the findspark.init() method\n",
        "\n",
        "import findspark\n",
        "findspark.init()\n",
        "findspark.find()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 36
        },
        "id": "FfBFUlTtIcfv",
        "outputId": "319d214b-1ebb-4a80-e778-1120967643b8"
      },
      "execution_count": 8,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "'/content/spark-3.3.1-bin-hadoop3'"
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "string"
            }
          },
          "metadata": {},
          "execution_count": 8
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#Creating Spark Session\n",
        "\n",
        "from pyspark.sql import SparkSession\n",
        "\n",
        "spark = SparkSession.builder\\\n",
        "        .master(\"local\")\\\n",
        "        .appName(\"Colab\")\\\n",
        "        .config('spark.ui.port', '4050')\\\n",
        "        .getOrCreate()"
      ],
      "metadata": {
        "id": "5iCACprvI7pK"
      },
      "execution_count": 9,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "**Extracting data from csv file to pandas dataframe**"
      ],
      "metadata": {
        "id": "gztp7-mTK6pI"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import pandas as pd\n",
        "Titlepd=pd.read_csv(\"/content/titles.csv\")\n",
        "Creditpd=pd.read_csv(\"/content/credits.csv\")"
      ],
      "metadata": {
        "id": "LDzvdwUkHQlW"
      },
      "execution_count": 10,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#Dropping unecessary columns\n",
        "\n",
        "Titlepd.drop([\"description\",\"age_certification\",\"imdb_id\"], axis=1,inplace=True)\n",
        "Creditpd.drop([\"person_id\"],axis=1,inplace=True)"
      ],
      "metadata": {
        "id": "_5_Xgju0KID_"
      },
      "execution_count": 11,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#importing datatypes supported by spark dataframe- these will be used to provide schema explicitly while creating spark dataframe from \n",
        "#Pandas DF otherwise there are chances that Spark didn't infer the schema correctly and can result in error or merging of column data\n",
        "\n",
        "from pyspark.sql.types import StringType, StructField, StructType ,IntegerType,FloatType"
      ],
      "metadata": {
        "id": "kCJ5yyKLOfd9"
      },
      "execution_count": 13,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#schema for Titlepd\n",
        "schema1 = StructType([StructField(\"id\", StringType(), True)\\\n",
        "                   ,StructField(\"title\",StringType(), True)\\\n",
        "                   ,StructField(\"type\", StringType(), True)\\\n",
        "                   ,StructField(\"release_year\", IntegerType(), True)\\\n",
        "                   ,StructField(\"runtime\", IntegerType(), True)\\\n",
        "                   ,StructField(\"genres\", StringType(), True)\\\n",
        "                   ,StructField(\"production_countries\", StringType(), True)\\\n",
        "                   ,StructField(\"season\", FloatType(), True)\\\n",
        "                   ,StructField(\"imdb_score\", FloatType(), True)\\\n",
        "                   ,StructField(\"imdb_votes\", FloatType(), True)\\\n",
        "                   ,StructField(\"tmdb_popularity\", FloatType(), True)\\\n",
        "                   ,StructField(\"tmdb_score\", FloatType(), True)\\\n",
        "                   ])"
      ],
      "metadata": {
        "id": "SBgvu0zfUBT0"
      },
      "execution_count": 14,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#schema for Creditpd\n",
        "schema2 = StructType([StructField(\"id\", StringType(), True)\\\n",
        "                   ,StructField(\"name\",StringType(), True)\\\n",
        "                   ,StructField(\"character\", StringType(), True)\\\n",
        "                   ,StructField(\"role\", StringType(), True)\\\n",
        "              \n",
        "                   ])"
      ],
      "metadata": {
        "id": "OrIBL1g31e29"
      },
      "execution_count": 15,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#Cretaing Spark DataFrame from Pandas DataFrame\n",
        "\n",
        "Titledf=spark.createDataFrame(Titlepd,schema=schema1)\n",
        "Creditdf=spark.createDataFrame(Creditpd,schema=schema2)"
      ],
      "metadata": {
        "id": "37bmUi-JHt0M"
      },
      "execution_count": 16,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#importing required Libraries\n",
        "from pyspark.sql.functions import count,col,sum,isnan\n",
        "import numpy as np"
      ],
      "metadata": {
        "id": "MIjaxrsv6wwY"
      },
      "execution_count": 17,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "**Cleaning & Tranforming data**"
      ],
      "metadata": {
        "id": "WI2h4ktHK_pJ"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "#Counting nan(not a number) - basically pandas represent empty or blank value using nan\n",
        "null_count=Titledf.select([sum(isnan(col(c)).cast(\"int\")).alias(c) for c in Titledf.columns])\n",
        "null_count.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "3tA2Dfqk7HMB",
        "outputId": "187ddfb3-f95d-429f-d646-a6758b1fe625"
      },
      "execution_count": 18,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+---+-----+----+------------+-------+------+--------------------+------+----------+----------+---------------+----------+\n",
            "| id|title|type|release_year|runtime|genres|production_countries|season|imdb_score|imdb_votes|tmdb_popularity|tmdb_score|\n",
            "+---+-----+----+------------+-------+------+--------------------+------+----------+----------+---------------+----------+\n",
            "|  0|    1|   0|           0|      0|     0|                   0|  3744|       482|       498|             91|       311|\n",
            "+---+-----+----+------------+-------+------+--------------------+------+----------+----------+---------------+----------+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#We are replacing nan with None type as df.na.fill in spark dataframe does fill only Null or None type values\n",
        "Titledf=Titledf.replace(np.nan,None)"
      ],
      "metadata": {
        "id": "PoJh9jIYBcEj"
      },
      "execution_count": 19,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#Checking if stil there is Nan\n",
        "null_count=Titledf.select([sum(isnan(col(c)).cast(\"int\")).alias(c) for c in Titledf.columns])\n",
        "null_count.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "H16uWMEmMO1l",
        "outputId": "a6430060-b8e1-4bc8-e806-fcf473b8200e"
      },
      "execution_count": 20,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+---+-----+----+------------+-------+------+--------------------+------+----------+----------+---------------+----------+\n",
            "| id|title|type|release_year|runtime|genres|production_countries|season|imdb_score|imdb_votes|tmdb_popularity|tmdb_score|\n",
            "+---+-----+----+------------+-------+------+--------------------+------+----------+----------+---------------+----------+\n",
            "|  0|    1|   0|           0|      0|     0|                   0|     0|         0|         0|              0|         0|\n",
            "+---+-----+----+------------+-------+------+--------------------+------+----------+----------+---------------+----------+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#Check type of value in title\n",
        "Titledf.filter(isnan(col('title'))).show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "OGqbZ5CC8tBU",
        "outputId": "d81394b5-0f05-41ae-fe19-981fb44f968a"
      },
      "execution_count": 21,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+---------+-----+-----+------------+-------+------+--------------------+------+----------+----------+---------------+----------+\n",
            "|       id|title| type|release_year|runtime|genres|production_countries|season|imdb_score|imdb_votes|tmdb_popularity|tmdb_score|\n",
            "+---------+-----+-----+------------+-------+------+--------------------+------+----------+----------+---------------+----------+\n",
            "|tm1063792|  NaN|MOVIE|        2015|     11|    []|                  []|  null|      null|      null|           null|      null|\n",
            "+---------+-----+-----+------------+-------+------+--------------------+------+----------+----------+---------------+----------+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#Removing the last one instance that was of string type, and doesn't get replaced earlier\n",
        "Titledf=Titledf.replace('NaN',None)"
      ],
      "metadata": {
        "id": "JpuDfRigFnX6"
      },
      "execution_count": 22,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#Dropping rows that doesn't have title as title is the main column for the analysis hence we can eliminate rows which don't have the value for it.\n",
        "Titledf=Titledf.dropna(subset=\"title\")"
      ],
      "metadata": {
        "id": "q7SnSIPs9ZwA"
      },
      "execution_count": 23,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#The rows that containg movie will have 0 seasons\n",
        "Titledf=Titledf.na.fill({'season':0})"
      ],
      "metadata": {
        "id": "4lVG4AkT_vhZ"
      },
      "execution_count": 24,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#Filtering out rows that have at least one of the statistics data available for movie or show\n",
        "Titledf=Titledf.filter(~((col('imdb_score').isNull()) & (col('imdb_votes').isNull()) & (col('tmdb_popularity').isNull()) & (col('tmdb_score').isNull())))"
      ],
      "metadata": {
        "id": "0_322puj98Z1"
      },
      "execution_count": 25,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#Agin Checking the null count\n",
        "null_count=[]\n",
        "for c in Titledf.columns:\n",
        "  count=Titledf.filter(Titledf[c].isNull()).count()\n",
        "  null_count.append((c,count))\n",
        "\n",
        "for c, count in null_count:\n",
        "  print(\"column \",c,\"-\",count)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "OqZ43YES-wJU",
        "outputId": "e5c2d6f2-b0da-4d54-fb51-67628347d2cb"
      },
      "execution_count": 26,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "column  id - 0\n",
            "column  title - 0\n",
            "column  type - 0\n",
            "column  release_year - 0\n",
            "column  runtime - 0\n",
            "column  genres - 0\n",
            "column  production_countries - 0\n",
            "column  season - 0\n",
            "column  imdb_score - 466\n",
            "column  imdb_votes - 482\n",
            "column  tmdb_popularity - 75\n",
            "column  tmdb_score - 295\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "**NOTE:** \n",
        "\n",
        "The remaining null value data present in other columns can be ignored as they are less in number and moreover less relevant for analysis\n",
        "\n",
        "Now we will start exploring our data using **dataframe functions** and **spark sql**"
      ],
      "metadata": {
        "id": "H2FvtZPF-iw5"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "Titledf.show(n=5)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "3nFbfYrL_P5I",
        "outputId": "6580d5f0-27d6-41f0-90a0-405782805619"
      },
      "execution_count": 27,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------+--------------------+-----+------------+-------+--------------------+--------------------+------+----------+----------+---------------+----------+\n",
            "|      id|               title| type|release_year|runtime|              genres|production_countries|season|imdb_score|imdb_votes|tmdb_popularity|tmdb_score|\n",
            "+--------+--------------------+-----+------------+-------+--------------------+--------------------+------+----------+----------+---------------+----------+\n",
            "|ts300399|Five Came Back: T...| SHOW|        1945|     51|   ['documentation']|              ['US']|   1.0|      null|      null|            0.6|      null|\n",
            "| tm84618|         Taxi Driver|MOVIE|        1976|    114|  ['drama', 'crime']|              ['US']|   0.0|       8.2|  808582.0|         40.965|     8.179|\n",
            "|tm154986|         Deliverance|MOVIE|        1972|    109|['drama', 'action...|              ['US']|   0.0|       7.7|  107673.0|          10.01|       7.3|\n",
            "|tm127384|Monty Python and ...|MOVIE|        1975|     91|['fantasy', 'acti...|              ['GB']|   0.0|       8.2|  534486.0|         15.461|     7.811|\n",
            "|tm120801|     The Dirty Dozen|MOVIE|        1967|    150|   ['war', 'action']|        ['GB', 'US']|   0.0|       7.7|   72662.0|         20.398|       7.6|\n",
            "+--------+--------------------+-----+------------+-------+--------------------+--------------------+------+----------+----------+---------------+----------+\n",
            "only showing top 5 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "Titledf.groupBy('type').count().show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "7mbEu8fy6UoM",
        "outputId": "d063e38e-c56b-46b5-81f0-3a30de622e6a"
      },
      "execution_count": 28,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-----+-----+\n",
            "| type|count|\n",
            "+-----+-----+\n",
            "| SHOW| 2102|\n",
            "|MOVIE| 3732|\n",
            "+-----+-----+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#Creating view to perform spark sql queries on data\n",
        "Titledf.createOrReplaceTempView(\"titles\")"
      ],
      "metadata": {
        "id": "Ja4QTB4hIl9J"
      },
      "execution_count": 29,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#Total count of movies or shows produced by countries\n",
        "spark.sql(\"select REPLACE(REPLACE(production_countries,'[',''),']','') as Country, count(*) as total_movie_or_shows from titles where production_countries!='[]' group by production_countries  order by total_movie_or_shows desc\").show(n=10)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Ci4diKAaMiAW",
        "outputId": "274d9004-2152-4922-d698-ae2dfae2a01d"
      },
      "execution_count": 30,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-------+--------------------+\n",
            "|Country|total_movie_or_shows|\n",
            "+-------+--------------------+\n",
            "|   'US'|                1957|\n",
            "|   'IN'|                 596|\n",
            "|   'JP'|                 264|\n",
            "|   'KR'|                 222|\n",
            "|   'GB'|                 218|\n",
            "|   'ES'|                 161|\n",
            "|   'FR'|                 125|\n",
            "|   'CA'|                 107|\n",
            "|   'MX'|                  98|\n",
            "|   'BR'|                  90|\n",
            "+-------+--------------------+\n",
            "only showing top 10 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#highest imdb score movie\n",
        "spark.sql(\"select title,imdb_score,tmdb_score from titles where type='MOVIE' order by imdb_score desc, tmdb_score desc\").show(n=1)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "qxzW5iP5ON0t",
        "outputId": "32b8ffaa-a9e3-4077-fc4c-9113eb0e89ca"
      },
      "execution_count": 31,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-----+----------+----------+\n",
            "|title|imdb_score|tmdb_score|\n",
            "+-----+----------+----------+\n",
            "|Major|       9.1|     8.188|\n",
            "+-----+----------+----------+\n",
            "only showing top 1 row\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#hightes imdb voted movie\n",
        "spark.sql(\"select title,imdb_votes from titles where type='MOVIE' order by imdb_votes desc\").show(n=1)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "sB2qYgutOamf",
        "outputId": "61c592d5-06f4-4ccb-9c66-75b345f27258"
      },
      "execution_count": 32,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+---------+----------+\n",
            "|    title|imdb_votes|\n",
            "+---------+----------+\n",
            "|Inception| 2294231.0|\n",
            "+---------+----------+\n",
            "only showing top 1 row\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#hightes imdb voted show\n",
        "spark.sql(\"select title,imdb_votes from titles where type='SHOW' order by imdb_votes desc\").show(n=1)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "g8aHn_5vOml-",
        "outputId": "781d2bdd-4d42-45da-e733-aa81fecaa307"
      },
      "execution_count": 33,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+------------+----------+\n",
            "|       title|imdb_votes|\n",
            "+------------+----------+\n",
            "|Breaking Bad| 1775990.0|\n",
            "+------------+----------+\n",
            "only showing top 1 row\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#most popular tmdb movie\n",
        "spark.sql(\"select title,tmdb_popularity from titles where type='MOVIE' order by tmdb_popularity desc\").show(n=1)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "ZSLkUHusOrXQ",
        "outputId": "d2f43eea-c285-4bb4-d470-e3443ceedccc"
      },
      "execution_count": 34,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-----------+---------------+\n",
            "|      title|tmdb_popularity|\n",
            "+-----------+---------------+\n",
            "|Incantation|       2274.044|\n",
            "+-----------+---------------+\n",
            "only showing top 1 row\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#most tmdb popular show\n",
        "spark.sql(\"select title,tmdb_popularity from titles where type='SHOW' order by tmdb_popularity desc\").show(n=1)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "FeE9wOqOOzA3",
        "outputId": "9e61cd30-4ff6-4ecb-b68e-ea012ef8c7ba"
      },
      "execution_count": 35,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+---------------+---------------+\n",
            "|          title|tmdb_popularity|\n",
            "+---------------+---------------+\n",
            "|Stranger Things|       2226.231|\n",
            "+---------------+---------------+\n",
            "only showing top 1 row\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#top 5 tmdb scored movies\n",
        "spark.sql(\"select title,tmdb_score from titles where type='MOVIE'  order by tmdb_score desc\").show(n=5,truncate=False)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "CG9mV8IsJLnk",
        "outputId": "09b3551f-12d2-4b16-8dbd-297c8ef7eb7c"
      },
      "execution_count": 36,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+------------------------------------------------------------+----------+\n",
            "|title                                                       |tmdb_score|\n",
            "+------------------------------------------------------------+----------+\n",
            "|True: Friendship Day                                        |10.0      |\n",
            "|Motu Patlu in the Game of Zones                             |10.0      |\n",
            "|Kedibone                                                    |10.0      |\n",
            "|Motu Patlu Kung Fu Kings 4 The Challenge of Kung Fu Brothers|10.0      |\n",
            "|True: Rainbow Rescue                                        |10.0      |\n",
            "+------------------------------------------------------------+----------+\n",
            "only showing top 5 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#top 5 tmdb scored shows\n",
        "spark.sql(\"select title,tmdb_score from titles where type='SHOW'  order by tmdb_score desc\").show(n=5,truncate=False)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "4ErMhzGzPY72",
        "outputId": "b0dae92d-382e-404e-94bb-9c7fe05f1ca1"
      },
      "execution_count": 37,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-------------------------------+----------+\n",
            "|title                          |tmdb_score|\n",
            "+-------------------------------+----------+\n",
            "|The Haunted House              |10.0      |\n",
            "|Pink Zone                      |10.0      |\n",
            "|Dharmakshetra                  |10.0      |\n",
            "|Raja, Rasoi Aur Anya Kahaniyaan|10.0      |\n",
            "|Little Baby Bum                |10.0      |\n",
            "+-------------------------------+----------+\n",
            "only showing top 5 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#Oldest movies present in the data\n",
        "spark.sql(\"select title,type,release_year,imdb_score,tmdb_score from titles where type='MOVIE' order by release_year,imdb_score desc\").show(n=5,truncate=False)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "7Ox4hvMhJ8Jz",
        "outputId": "e6cfda89-b507-4a7d-b8f1-909fd7728bd1"
      },
      "execution_count": 38,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+---------------+-----+------------+----------+----------+\n",
            "|title          |type |release_year|imdb_score|tmdb_score|\n",
            "+---------------+-----+------------+----------+----------+\n",
            "|White Christmas|MOVIE|1954        |7.5       |7.2       |\n",
            "|The Blazing Sun|MOVIE|1954        |7.4       |7.0       |\n",
            "|Dark Waters    |MOVIE|1956        |6.7       |5.9       |\n",
            "|Cairo Station  |MOVIE|1958        |7.5       |7.3       |\n",
            "|Ujala          |MOVIE|1959        |6.6       |6.0       |\n",
            "+---------------+-----+------------+----------+----------+\n",
            "only showing top 5 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#Oldest shows present in the data\n",
        "spark.sql(\"select title,type,release_year,imdb_score,tmdb_score from titles where type='SHOW' and (imdb_score is not null or tmdb_score is not null) order by release_year,imdb_score desc\").show(n=5,truncate=False)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "0YRnuMHPKdgO",
        "outputId": "dd0e73ed-8a1c-4bee-cf90-3433861ed980"
      },
      "execution_count": 39,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------------------------+----+------------+----------+----------+\n",
            "|title                           |type|release_year|imdb_score|tmdb_score|\n",
            "+--------------------------------+----+------------+----------+----------+\n",
            "|Monty Python's Flying Circus    |SHOW|1969        |8.8       |8.306     |\n",
            "|Monty Python's Fliegender Zirkus|SHOW|1972        |8.1       |7.0       |\n",
            "|Danger Mouse                    |SHOW|1981        |7.4       |7.5       |\n",
            "|Knight Rider                    |SHOW|1982        |6.9       |7.5       |\n",
            "|Wheel of Fortune                |SHOW|1983        |6.7       |6.8       |\n",
            "+--------------------------------+----+------------+----------+----------+\n",
            "only showing top 5 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#most recent movies data\n",
        "spark.sql(\"select title,release_year,imdb_score,tmdb_score from titles where type='MOVIE' order by release_year desc,imdb_score desc\").show(n=5,truncate=False)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "FyQ40z9DKVzn",
        "outputId": "ff352da9-a5ad-4b51-af5b-937816f719ae"
      },
      "execution_count": 40,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------+------------+----------+----------+\n",
            "|title         |release_year|imdb_score|tmdb_score|\n",
            "+--------------+------------+----------+----------+\n",
            "|Major         |2022        |9.1       |8.188     |\n",
            "|Jana Gana Mana|2022        |8.3       |7.9       |\n",
            "|Godse         |2022        |8.2       |7.0       |\n",
            "|RRR           |2022        |8.0       |7.8       |\n",
            "|Viraata Parvam|2022        |8.0       |5.0       |\n",
            "+--------------+------------+----------+----------+\n",
            "only showing top 5 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#most recent shows data\n",
        "spark.sql(\"select title,type,release_year,imdb_score,tmdb_score from titles where type='SHOW' and (imdb_score is not null or tmdb_score is not null) order by release_year desc,imdb_score desc\").show(n=5,truncate=False)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "9r29btWHLhmv",
        "outputId": "523ad4a4-74bb-4623-b958-e8c66413359b"
      },
      "execution_count": 41,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+----------------------+----+------------+----------+----------+\n",
            "|title                 |type|release_year|imdb_score|tmdb_score|\n",
            "+----------------------+----+------------+----------+----------+\n",
            "|Twenty Five Twenty One|SHOW|2022        |8.7       |8.6       |\n",
            "|Heartstopper          |SHOW|2022        |8.7       |9.027     |\n",
            "|Alchemy of Souls      |SHOW|2022        |8.6       |8.0       |\n",
            "|Our Blues             |SHOW|2022        |8.5       |8.8       |\n",
            "|The Creature Cases    |SHOW|2022        |8.5       |7.0       |\n",
            "+----------------------+----+------------+----------+----------+\n",
            "only showing top 5 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#highest tmdb scored shows produced by india\n",
        "spark.sql(\"select title,tmdb_score from titles where type='SHOW' and production_countries= '[\\\\'IN\\\\']' order by tmdb_score desc\").show(n=5,truncate=False)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "ndinbTk-JobS",
        "outputId": "27800594-9215-4240-8005-08e9bb9555e3"
      },
      "execution_count": 42,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-------------------------------+----------+\n",
            "|title                          |tmdb_score|\n",
            "+-------------------------------+----------+\n",
            "|Raja, Rasoi Aur Anya Kahaniyaan|10.0      |\n",
            "|Dharmakshetra                  |10.0      |\n",
            "|Mai: A Mother's Rage           |9.2       |\n",
            "|Masaba Masaba                  |9.0       |\n",
            "|Cricket Fever: Mumbai Indians  |9.0       |\n",
            "+-------------------------------+----------+\n",
            "only showing top 5 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#highest imdb scored shows produced by india\n",
        "spark.sql(\"select title,imdb_score,int(season) from titles where type='SHOW' and production_countries= '[\\\\'IN\\\\']' order by imdb_score desc\").show(n=5,truncate=False)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "25hCwIaeQm6X",
        "outputId": "11a57be4-d982-4533-ab31-ae3689bac18a"
      },
      "execution_count": 51,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-------------------------------+----------+------+\n",
            "|title                          |imdb_score|season|\n",
            "+-------------------------------+----------+------+\n",
            "|Kota Factory                   |9.1       |2     |\n",
            "|Raja, Rasoi Aur Anya Kahaniyaan|8.9       |3     |\n",
            "|Stories by Rabindranath Tagore |8.7       |1     |\n",
            "|Daughters of Destiny           |8.6       |1     |\n",
            "|Sacred Games                   |8.5       |2     |\n",
            "+-------------------------------+----------+------+\n",
            "only showing top 5 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#most popular movies genres\n",
        "spark.sql(\"select title,REPLACE(REPLACE(genres,'[',''),']','') as genres_i,imdb_score from titles where type='MOVIE' and production_countries= '[\\\\'IN\\\\']' order by imdb_score desc\").show(n=5,truncate=False)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "oyCBFQXwQwLW",
        "outputId": "e9f2737a-4815-46fe-c386-df44890f1ff6"
      },
      "execution_count": 44,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+------------------------------------+--------------------------------+----------+\n",
            "|title                               |genres_i                        |imdb_score|\n",
            "+------------------------------------+--------------------------------+----------+\n",
            "|Major                               |'action', 'drama'               |9.1       |\n",
            "|C/o Kancharapalem                   |'drama'                         |8.9       |\n",
            "|Anbe Sivam                          |'comedy', 'drama'               |8.7       |\n",
            "|Chhota Bheem & Krishna in Mayanagari|'animation', 'action', 'fantasy'|8.7       |\n",
            "|Chhota Bheem and the ShiNobi Secret |'animation'                     |8.6       |\n",
            "+------------------------------------+--------------------------------+----------+\n",
            "only showing top 5 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#5 longest movie present in data \n",
        "spark.sql(\"select title,runtime from titles where type='MOVIE' order by runtime desc\").show(n=5,truncate=False)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "cfVTFfnNUlnQ",
        "outputId": "667af3c0-b512-4519-faae-3053348b358c"
      },
      "execution_count": 45,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+---------------------------------+-------+\n",
            "|title                            |runtime|\n",
            "+---------------------------------+-------+\n",
            "|Bonnie & Clyde                   |240    |\n",
            "|A Lion in the House              |225    |\n",
            "|Lagaan: Once Upon a Time in India|224    |\n",
            "|Jodhaa Akbar                     |214    |\n",
            "|Kabhi Khushi Kabhie Gham         |210    |\n",
            "+---------------------------------+-------+\n",
            "only showing top 5 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#5 shortest movie present in data \n",
        "spark.sql(\"select title,runtime from titles where type='MOVIE' order by runtime\").show(n=5,truncate=False)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "7N9cGgrBVHvi",
        "outputId": "e63041be-487a-44a6-b8ff-4e650a772b85"
      },
      "execution_count": 46,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+---------------------+-------+\n",
            "|title                |runtime|\n",
            "+---------------------+-------+\n",
            "|Time to Dance        |2      |\n",
            "|Silent               |3      |\n",
            "|Sol Levante          |4      |\n",
            "|Amsterdam to Anatolia|6      |\n",
            "|Match                |7      |\n",
            "+---------------------+-------+\n",
            "only showing top 5 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#longest shows and the number of seasons in it\n",
        "spark.sql(\"select title,runtime,season from titles where type='SHOW' order by runtime desc\").show(n=10,truncate=False)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "nrKUX_6HVUAe",
        "outputId": "150f2bd5-9f1c-47c5-ad01-5bcd3d60e5c2"
      },
      "execution_count": 49,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+----------------------+-------+------+\n",
            "|title                 |runtime|season|\n",
            "+----------------------+-------+------+\n",
            "|A Lion in the House   |190    |1.0   |\n",
            "|1994                  |178    |1.0   |\n",
            "|Dead Set              |141    |1.0   |\n",
            "|Maya and the Three    |131    |1.0   |\n",
            "|The Yard              |108    |3.0   |\n",
            "|Intersection          |107    |2.0   |\n",
            "|The Gentlemen's League|98     |2.0   |\n",
            "|On Children           |98     |1.0   |\n",
            "|Hospital Playlist     |93     |2.0   |\n",
            "|jeen-yuhs             |93     |1.0   |\n",
            "+----------------------+-------+------+\n",
            "only showing top 10 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#shortest shows and thier genres\n",
        "spark.sql(\"select title,REPLACE(REPLACE(genres,'[',''),']','') as genres_i,runtime from titles where type='SHOW' and production_countries= '[\\\\'IN\\\\']' order by runtime\").show(n=5,truncate=False)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "ZV0a5vUYVY0I",
        "outputId": "bd2a7f0a-4f03-433a-82f9-8222fbe399a1"
      },
      "execution_count": 48,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------------------------+-------------------------------+-------+\n",
            "|title                           |genres_i                       |runtime|\n",
            "+--------------------------------+-------------------------------+-------+\n",
            "|Little Singham                  |'animation', 'action'          |12     |\n",
            "|Ladies Up                       |'reality', 'comedy'            |17     |\n",
            "|Mighty Raju                     |'animation', 'action'          |20     |\n",
            "|Taarak Mehta Kka Chhota Chashmah|'animation', 'comedy'          |22     |\n",
            "|Chhota Bheem                    |'animation', 'action', 'comedy'|22     |\n",
            "+--------------------------------+-------------------------------+-------+\n",
            "only showing top 5 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "**Now we will look at Credit Dataframe and will join it with title dataframe to find insights**"
      ],
      "metadata": {
        "id": "3kIAGveTD6um"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "#Counting nan in Credit dataframe\n",
        "null_count=Creditdf.select([sum(isnan(col(c)).cast(\"int\")).alias(c) for c in Creditdf.columns])\n",
        "null_count.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "exwAqaJ4Dzx0",
        "outputId": "dd696621-6f08-4a5c-c2e2-89f95c7cccdf"
      },
      "execution_count": 52,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+---+----+---------+----+\n",
            "| id|name|character|role|\n",
            "+---+----+---------+----+\n",
            "|  0|   0|     9774|   0|\n",
            "+---+----+---------+----+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "Creditdf.filter(isnan(col('character'))).show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "6PDNCXivESZT",
        "outputId": "e2b3fbc0-9f6a-4121-a07a-d1c941b7baf9"
      },
      "execution_count": 57,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------+-----------------+---------+--------+\n",
            "|      id|             name|character|    role|\n",
            "+--------+-----------------+---------+--------+\n",
            "| tm84618|  Martin Scorsese|      NaN|DIRECTOR|\n",
            "|tm154986|     John Boorman|      NaN|DIRECTOR|\n",
            "|tm127384|      Terry Jones|      NaN|DIRECTOR|\n",
            "|tm127384|    Terry Gilliam|      NaN|DIRECTOR|\n",
            "|tm120801|   Robert Aldrich|      NaN|DIRECTOR|\n",
            "| tm70993|      Peter Brett|      NaN|   ACTOR|\n",
            "| tm70993|      Terry Jones|      NaN|DIRECTOR|\n",
            "| tm14873|       Don Siegel|      NaN|DIRECTOR|\n",
            "|tm119281|      Arthur Penn|      NaN|DIRECTOR|\n",
            "| tm98978|   Randal Kleiser|      NaN|DIRECTOR|\n",
            "| tm44204|  J. Lee Thompson|      NaN|DIRECTOR|\n",
            "| tm67378|   Richard Brooks|      NaN|DIRECTOR|\n",
            "| tm69997|    Jeff Margolis|      NaN|DIRECTOR|\n",
            "| tm16479|   Michael Curtiz|      NaN|DIRECTOR|\n",
            "|tm135083|  Loutfi El Hakim|      NaN|   ACTOR|\n",
            "|tm135083| F. El Demerdache|      NaN|   ACTOR|\n",
            "|tm135083|    Said El Araby|      NaN|   ACTOR|\n",
            "|tm135083|Hana Abdel Fattah|      NaN|   ACTOR|\n",
            "|tm135083|     Safia Sarwat|      NaN|   ACTOR|\n",
            "|tm135083|          Sherine|      NaN|   ACTOR|\n",
            "+--------+-----------------+---------+--------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "Creditdf=Creditdf.replace('NaN',None)"
      ],
      "metadata": {
        "id": "Ja64T0uBNu1J"
      },
      "execution_count": 60,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "null_count=[]\n",
        "for c in Creditdf.columns:\n",
        "  count=Creditdf.filter(isnan(Creditdf[c])).count()\n",
        "  null_count.append((c,count))\n",
        "\n",
        "for c, count in null_count:\n",
        "  print(\"column \",c,\"-\",count)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "tXtW44ckNqoA",
        "outputId": "8167f819-c900-4b97-a3c5-f21fac1c9061"
      },
      "execution_count": 61,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "column  id - 0\n",
            "column  name - 0\n",
            "column  character - 0\n",
            "column  role - 0\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "Creditdf=Creditdf.fillna(\"Unknown\")"
      ],
      "metadata": {
        "id": "jj2_ySqwEfsj"
      },
      "execution_count": 62,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "Creditdf.show(n=5)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "gJ1lPZtYAJoQ",
        "outputId": "d0fdfb33-6f31-448a-8438-38e3d2e71fad"
      },
      "execution_count": 63,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-------+---------------+--------------------+-----+\n",
            "|     id|           name|           character| role|\n",
            "+-------+---------------+--------------------+-----+\n",
            "|tm84618| Robert De Niro|       Travis Bickle|ACTOR|\n",
            "|tm84618|   Jodie Foster|       Iris Steensma|ACTOR|\n",
            "|tm84618|  Albert Brooks|                 Tom|ACTOR|\n",
            "|tm84618|  Harvey Keitel|Matthew 'Sport' H...|ACTOR|\n",
            "|tm84618|Cybill Shepherd|               Betsy|ACTOR|\n",
            "+-------+---------------+--------------------+-----+\n",
            "only showing top 5 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "Creditdf.createOrReplaceTempView(\"credit\")"
      ],
      "metadata": {
        "id": "7hxOqTcU-5QI"
      },
      "execution_count": 64,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "spark.sql(\"select count(*) as total_count, role from credit group by role\").show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "2nfESHaxArPJ",
        "outputId": "5bbb6f8f-be12-4375-d886-eb3c1cb0eb6d"
      },
      "execution_count": 65,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-----------+--------+\n",
            "|total_count|    role|\n",
            "+-----------+--------+\n",
            "|       4550|DIRECTOR|\n",
            "|      73251|   ACTOR|\n",
            "+-----------+--------+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#Showing the cast and director of top 5 imdb scored movies/shows\n",
        "#array function to merge vcolumn data and collect_list to merge rows as roles are stored in differen rows of smae movie \n",
        "spark.sql(\"select t.title,t.imdb_score,t.type, collect_list(array(c.character,c.role)) as Cast_dir from titles as t inner join credit as c on t.id=c.id group by t.id,t.title,t.imdb_score,t.type order by t.imdb_score desc\").show(n=5)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "IIXBYrmSZXx0",
        "outputId": "7995b9e8-53ce-4faa-9733-7c5b52c4c3cc"
      },
      "execution_count": 77,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------------+----------+-----+--------------------+\n",
            "|               title|imdb_score| type|            Cast_dir|\n",
            "+--------------------+----------+-----+--------------------+\n",
            "|        Breaking Bad|       9.5| SHOW|[[Walter White, A...|\n",
            "|Avatar: The Last ...|       9.3| SHOW|[[Aang (voice), A...|\n",
            "|          Our Planet|       9.3| SHOW|[[Self - Narrator...|\n",
            "|          Reply 1988|       9.2| SHOW|[[Sung Deok-sun, ...|\n",
            "|               Major|       9.1|MOVIE|[[Sandeep Unnikri...|\n",
            "+--------------------+----------+-----+--------------------+\n",
            "only showing top 5 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#Showing the cast and director of top 5 imdb voted movies/shows\n",
        "spark.sql(\"select t.title,t.imdb_votes,t.type, collect_list(array(c.character,c.role)) as Cast_dir from titles as t inner join credit as c on t.id=c.id group by t.id,t.title,t.imdb_votes,t.type order by t.imdb_votes desc\").show(n=5)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "P8DcyvW8dG0H",
        "outputId": "ead8d362-35ef-4fd9-f715-d5d97d9f6f5e"
      },
      "execution_count": 78,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------------+----------+-----+--------------------+\n",
            "|               title|imdb_votes| type|            Cast_dir|\n",
            "+--------------------+----------+-----+--------------------+\n",
            "|           Inception| 2294231.0|MOVIE|[[Dom Cobb, ACTOR...|\n",
            "|        Forrest Gump| 2021343.0|MOVIE|[[Forrest Gump, A...|\n",
            "|        Breaking Bad| 1775990.0| SHOW|[[Walter White, A...|\n",
            "|The Dark Knight R...| 1669067.0|MOVIE|[[Bruce Wayne / B...|\n",
            "|               Se7en| 1606270.0|MOVIE|[[Detective David...|\n",
            "+--------------------+----------+-----+--------------------+\n",
            "only showing top 5 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#Showing the cast and director of top 5 tmdb popular movies/shows\n",
        "spark.sql(\"select t.title,t.tmdb_popularity,t.type, collect_list(array(c.character,c.role)) as Cast_dir from titles as t inner join credit as c on t.id=c.id group by t.id,t.title,t.tmdb_popularity,t.type order by t.tmdb_popularity desc\").show(n=5)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "L2j4EHgrZ25N",
        "outputId": "7f8f4e73-233b-4c89-ddb1-f813124d33ec"
      },
      "execution_count": 79,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------------+---------------+-----+--------------------+\n",
            "|               title|tmdb_popularity| type|            Cast_dir|\n",
            "+--------------------+---------------+-----+--------------------+\n",
            "|         Incantation|       2274.044|MOVIE|[[Li Ronan, ACTOR...|\n",
            "|     Stranger Things|       2226.231| SHOW|[[Joyce Byers, AC...|\n",
            "|       The Sea Beast|       1723.363|MOVIE|[[Jacob Holland (...|\n",
            "|  Valley of the Dead|       1668.296|MOVIE|[[Matacuras, ACTO...|\n",
            "|The Man from Toronto|       1439.906|MOVIE|[[Teddy Jackson, ...|\n",
            "+--------------------+---------------+-----+--------------------+\n",
            "only showing top 5 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "**Loading Cleaned Data from spark dataframes to csv files**"
      ],
      "metadata": {
        "id": "-FRJ1LABKrDE"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "Titledf.write.csv(\"/content/drive/MyDrive/CleanedTitle\",header=True)"
      ],
      "metadata": {
        "id": "LrXRwqPudApA"
      },
      "execution_count": 75,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "Creditdf.write.csv(\"/content/drive/MyDrive/CleanedCredit\",header=True)"
      ],
      "metadata": {
        "id": "1ESFCEWldzYP"
      },
      "execution_count": 76,
      "outputs": []
    }
  ]
}