GroupBy,Filter,joins,more (1).ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": []
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "code",
      "execution_count": 1,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "hBiyo6nZexgs",
        "outputId": "9b0ddcf6-029f-4ead-b63e-cefe0af4289c"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Looking in indexes: https://pypi.org/simple, https://us-python.pkg.dev/colab-wheels/public/simple/\n",
            "Collecting pyspark\n",
            "  Downloading pyspark-3.3.0.tar.gz (281.3 MB)\n",
            "\u001b[K     |████████████████████████████████| 281.3 MB 45 kB/s \n",
            "\u001b[?25hCollecting py4j==0.10.9.5\n",
            "  Downloading py4j-0.10.9.5-py2.py3-none-any.whl (199 kB)\n",
            "\u001b[K     |████████████████████████████████| 199 kB 56.5 MB/s \n",
            "\u001b[?25hBuilding wheels for collected packages: pyspark\n",
            "  Building wheel for pyspark (setup.py) ... \u001b[?25l\u001b[?25hdone\n",
            "  Created wheel for pyspark: filename=pyspark-3.3.0-py2.py3-none-any.whl size=281764026 sha256=41dab2aecd9631cd969a84412ee468b580f24ec0b0a4feb28aefe8b9e5359848\n",
            "  Stored in directory: /root/.cache/pip/wheels/7a/8e/1b/f73a52650d2e5f337708d9f6a1750d451a7349a867f928b885\n",
            "Successfully built pyspark\n",
            "Installing collected packages: py4j, pyspark\n",
            "Successfully installed py4j-0.10.9.5 pyspark-3.3.0\n"
          ]
        }
      ],
      "source": [
        "!pip install pyspark"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Import SparkSession\n",
        "from pyspark.sql import SparkSession\n",
        "\n",
        "# Create SparkSession \n",
        "spark = SparkSession.builder \\\n",
        "      .master(\"local[1]\") \\\n",
        "      .appName(\"SparkByExamples.com\") \\\n",
        "      .getOrCreate() "
      ],
      "metadata": {
        "id": "YpE7VHvDfFGf"
      },
      "execution_count": 2,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "\n",
        "simpleData = [(\"James\",\"Sales\",\"NY\",90000,34,10000), \\\n",
        "    (\"Michael\",\"Sales\",\"NY\",86000,56,20000), \\\n",
        "    (\"Robert\",\"Sales\",\"CA\",81000,30,23000), \\\n",
        "    (\"Maria\",\"Finance\",\"CA\",90000,24,23000), \\\n",
        "    (\"Raman\",\"Finance\",\"CA\",99000,40,24000), \\\n",
        "    (\"Scott\",\"Finance\",\"NY\",83000,36,19000), \\\n",
        "    (\"Jen\",\"Finance\",\"NY\",79000,53,15000), \\\n",
        "    (\"Jeff\",\"Marketing\",\"CA\",80000,25,18000), \\\n",
        "    (\"Kumar\",\"Marketing\",\"NY\",91000,50,21000) \\\n",
        "  ]\n",
        "columns= [\"employee_name\",\"department\",\"state\",\"salary\",\"age\",\"bonus\"]\n",
        "df = spark.createDataFrame(data = simpleData, schema = columns)\n",
        "df.printSchema()\n",
        "df.show(truncate=False)\n"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "7ilMpTt8fKSO",
        "outputId": "2a60bfc8-3ce2-4421-a502-f3369be3993d"
      },
      "execution_count": 3,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "root\n",
            " |-- employee_name: string (nullable = true)\n",
            " |-- department: string (nullable = true)\n",
            " |-- state: string (nullable = true)\n",
            " |-- salary: long (nullable = true)\n",
            " |-- age: long (nullable = true)\n",
            " |-- bonus: long (nullable = true)\n",
            "\n",
            "+-------------+----------+-----+------+---+-----+\n",
            "|employee_name|department|state|salary|age|bonus|\n",
            "+-------------+----------+-----+------+---+-----+\n",
            "|James        |Sales     |NY   |90000 |34 |10000|\n",
            "|Michael      |Sales     |NY   |86000 |56 |20000|\n",
            "|Robert       |Sales     |CA   |81000 |30 |23000|\n",
            "|Maria        |Finance   |CA   |90000 |24 |23000|\n",
            "|Raman        |Finance   |CA   |99000 |40 |24000|\n",
            "|Scott        |Finance   |NY   |83000 |36 |19000|\n",
            "|Jen          |Finance   |NY   |79000 |53 |15000|\n",
            "|Jeff         |Marketing |CA   |80000 |25 |18000|\n",
            "|Kumar        |Marketing |NY   |91000 |50 |21000|\n",
            "+-------------+----------+-----+------+---+-----+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# DataFrame sorting using the sort() function\n",
        "from pyspark.sql.functions import col\n",
        "df.sort(\"department\",\"state\").show(truncate=False)\n",
        "df.sort(col(\"department\"),col(\"state\")).show(truncate=False)\n"
      ],
      "metadata": {
        "id": "nOziX8rzfeI1",
        "outputId": "09372756-bcdb-48a3-dffe-e09ca830e8ce",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "execution_count": 4,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-------------+----------+-----+------+---+-----+\n",
            "|employee_name|department|state|salary|age|bonus|\n",
            "+-------------+----------+-----+------+---+-----+\n",
            "|Maria        |Finance   |CA   |90000 |24 |23000|\n",
            "|Raman        |Finance   |CA   |99000 |40 |24000|\n",
            "|Jen          |Finance   |NY   |79000 |53 |15000|\n",
            "|Scott        |Finance   |NY   |83000 |36 |19000|\n",
            "|Jeff         |Marketing |CA   |80000 |25 |18000|\n",
            "|Kumar        |Marketing |NY   |91000 |50 |21000|\n",
            "|Robert       |Sales     |CA   |81000 |30 |23000|\n",
            "|James        |Sales     |NY   |90000 |34 |10000|\n",
            "|Michael      |Sales     |NY   |86000 |56 |20000|\n",
            "+-------------+----------+-----+------+---+-----+\n",
            "\n",
            "+-------------+----------+-----+------+---+-----+\n",
            "|employee_name|department|state|salary|age|bonus|\n",
            "+-------------+----------+-----+------+---+-----+\n",
            "|Maria        |Finance   |CA   |90000 |24 |23000|\n",
            "|Raman        |Finance   |CA   |99000 |40 |24000|\n",
            "|Jen          |Finance   |NY   |79000 |53 |15000|\n",
            "|Scott        |Finance   |NY   |83000 |36 |19000|\n",
            "|Jeff         |Marketing |CA   |80000 |25 |18000|\n",
            "|Kumar        |Marketing |NY   |91000 |50 |21000|\n",
            "|Robert       |Sales     |CA   |81000 |30 |23000|\n",
            "|James        |Sales     |NY   |90000 |34 |10000|\n",
            "|Michael      |Sales     |NY   |86000 |56 |20000|\n",
            "+-------------+----------+-----+------+---+-----+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "\n",
        "df.orderBy(\"department\",\"state\").show(truncate=False)\n",
        "df.orderBy(col(\"department\"),col(\"state\")).show(truncate=False)\n"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "QFi5pfokfwQ8",
        "outputId": "5ebec5a3-7638-4ff7-f4ff-d393e4f4aad9"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-------------+----------+-----+------+---+-----+\n",
            "|employee_name|department|state|salary|age|bonus|\n",
            "+-------------+----------+-----+------+---+-----+\n",
            "|Maria        |Finance   |CA   |90000 |24 |23000|\n",
            "|Raman        |Finance   |CA   |99000 |40 |24000|\n",
            "|Jen          |Finance   |NY   |79000 |53 |15000|\n",
            "|Scott        |Finance   |NY   |83000 |36 |19000|\n",
            "|Jeff         |Marketing |CA   |80000 |25 |18000|\n",
            "|Kumar        |Marketing |NY   |91000 |50 |21000|\n",
            "|Robert       |Sales     |CA   |81000 |30 |23000|\n",
            "|James        |Sales     |NY   |90000 |34 |10000|\n",
            "|Michael      |Sales     |NY   |86000 |56 |20000|\n",
            "+-------------+----------+-----+------+---+-----+\n",
            "\n",
            "+-------------+----------+-----+------+---+-----+\n",
            "|employee_name|department|state|salary|age|bonus|\n",
            "+-------------+----------+-----+------+---+-----+\n",
            "|Maria        |Finance   |CA   |90000 |24 |23000|\n",
            "|Raman        |Finance   |CA   |99000 |40 |24000|\n",
            "|Jen          |Finance   |NY   |79000 |53 |15000|\n",
            "|Scott        |Finance   |NY   |83000 |36 |19000|\n",
            "|Jeff         |Marketing |CA   |80000 |25 |18000|\n",
            "|Kumar        |Marketing |NY   |91000 |50 |21000|\n",
            "|Robert       |Sales     |CA   |81000 |30 |23000|\n",
            "|James        |Sales     |NY   |90000 |34 |10000|\n",
            "|Michael      |Sales     |NY   |86000 |56 |20000|\n",
            "+-------------+----------+-----+------+---+-----+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#Sort by Ascending (ASC)\n",
        "df.sort(df.department.asc(),df.state.asc()).show(truncate=False)\n",
        "df.sort(col(\"department\").asc(),col(\"state\").asc()).show(truncate=False)\n",
        "df.orderBy(col(\"department\").asc(),col(\"state\").asc()).show(truncate=False)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "fK5lHRlJf5i3",
        "outputId": "a9221b9b-9064-44e9-f1c6-9090e2bb7777"
      },
      "execution_count": 8,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-------------+----------+-----+------+---+-----+\n",
            "|employee_name|department|state|salary|age|bonus|\n",
            "+-------------+----------+-----+------+---+-----+\n",
            "|Maria        |Finance   |CA   |90000 |24 |23000|\n",
            "|Raman        |Finance   |CA   |99000 |40 |24000|\n",
            "|Jen          |Finance   |NY   |79000 |53 |15000|\n",
            "|Scott        |Finance   |NY   |83000 |36 |19000|\n",
            "|Jeff         |Marketing |CA   |80000 |25 |18000|\n",
            "|Kumar        |Marketing |NY   |91000 |50 |21000|\n",
            "|Robert       |Sales     |CA   |81000 |30 |23000|\n",
            "|James        |Sales     |NY   |90000 |34 |10000|\n",
            "|Michael      |Sales     |NY   |86000 |56 |20000|\n",
            "+-------------+----------+-----+------+---+-----+\n",
            "\n",
            "+-------------+----------+-----+------+---+-----+\n",
            "|employee_name|department|state|salary|age|bonus|\n",
            "+-------------+----------+-----+------+---+-----+\n",
            "|Maria        |Finance   |CA   |90000 |24 |23000|\n",
            "|Raman        |Finance   |CA   |99000 |40 |24000|\n",
            "|Jen          |Finance   |NY   |79000 |53 |15000|\n",
            "|Scott        |Finance   |NY   |83000 |36 |19000|\n",
            "|Jeff         |Marketing |CA   |80000 |25 |18000|\n",
            "|Kumar        |Marketing |NY   |91000 |50 |21000|\n",
            "|Robert       |Sales     |CA   |81000 |30 |23000|\n",
            "|James        |Sales     |NY   |90000 |34 |10000|\n",
            "|Michael      |Sales     |NY   |86000 |56 |20000|\n",
            "+-------------+----------+-----+------+---+-----+\n",
            "\n",
            "+-------------+----------+-----+------+---+-----+\n",
            "|employee_name|department|state|salary|age|bonus|\n",
            "+-------------+----------+-----+------+---+-----+\n",
            "|Maria        |Finance   |CA   |90000 |24 |23000|\n",
            "|Raman        |Finance   |CA   |99000 |40 |24000|\n",
            "|Jen          |Finance   |NY   |79000 |53 |15000|\n",
            "|Scott        |Finance   |NY   |83000 |36 |19000|\n",
            "|Jeff         |Marketing |CA   |80000 |25 |18000|\n",
            "|Kumar        |Marketing |NY   |91000 |50 |21000|\n",
            "|Robert       |Sales     |CA   |81000 |30 |23000|\n",
            "|James        |Sales     |NY   |90000 |34 |10000|\n",
            "|Michael      |Sales     |NY   |86000 |56 |20000|\n",
            "+-------------+----------+-----+------+---+-----+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#Sort by Descending (DESC)\n",
        "df.sort(df.department.asc(),df.state.desc()).show(truncate=False)\n",
        "df.sort(col(\"department\").asc(),col(\"state\").desc()).show(truncate=False)\n",
        "df.orderBy(col(\"department\").asc(),col(\"state\").desc()).show(truncate=False)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "0U3nywljgFVI",
        "outputId": "b05059f1-9ee8-4b02-85e0-df9d20ce57f9"
      },
      "execution_count": 9,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-------------+----------+-----+------+---+-----+\n",
            "|employee_name|department|state|salary|age|bonus|\n",
            "+-------------+----------+-----+------+---+-----+\n",
            "|Scott        |Finance   |NY   |83000 |36 |19000|\n",
            "|Jen          |Finance   |NY   |79000 |53 |15000|\n",
            "|Raman        |Finance   |CA   |99000 |40 |24000|\n",
            "|Maria        |Finance   |CA   |90000 |24 |23000|\n",
            "|Kumar        |Marketing |NY   |91000 |50 |21000|\n",
            "|Jeff         |Marketing |CA   |80000 |25 |18000|\n",
            "|James        |Sales     |NY   |90000 |34 |10000|\n",
            "|Michael      |Sales     |NY   |86000 |56 |20000|\n",
            "|Robert       |Sales     |CA   |81000 |30 |23000|\n",
            "+-------------+----------+-----+------+---+-----+\n",
            "\n",
            "+-------------+----------+-----+------+---+-----+\n",
            "|employee_name|department|state|salary|age|bonus|\n",
            "+-------------+----------+-----+------+---+-----+\n",
            "|Scott        |Finance   |NY   |83000 |36 |19000|\n",
            "|Jen          |Finance   |NY   |79000 |53 |15000|\n",
            "|Raman        |Finance   |CA   |99000 |40 |24000|\n",
            "|Maria        |Finance   |CA   |90000 |24 |23000|\n",
            "|Kumar        |Marketing |NY   |91000 |50 |21000|\n",
            "|Jeff         |Marketing |CA   |80000 |25 |18000|\n",
            "|James        |Sales     |NY   |90000 |34 |10000|\n",
            "|Michael      |Sales     |NY   |86000 |56 |20000|\n",
            "|Robert       |Sales     |CA   |81000 |30 |23000|\n",
            "+-------------+----------+-----+------+---+-----+\n",
            "\n",
            "+-------------+----------+-----+------+---+-----+\n",
            "|employee_name|department|state|salary|age|bonus|\n",
            "+-------------+----------+-----+------+---+-----+\n",
            "|Scott        |Finance   |NY   |83000 |36 |19000|\n",
            "|Jen          |Finance   |NY   |79000 |53 |15000|\n",
            "|Raman        |Finance   |CA   |99000 |40 |24000|\n",
            "|Maria        |Finance   |CA   |90000 |24 |23000|\n",
            "|Kumar        |Marketing |NY   |91000 |50 |21000|\n",
            "|Jeff         |Marketing |CA   |80000 |25 |18000|\n",
            "|James        |Sales     |NY   |90000 |34 |10000|\n",
            "|Michael      |Sales     |NY   |86000 |56 |20000|\n",
            "|Robert       |Sales     |CA   |81000 |30 |23000|\n",
            "+-------------+----------+-----+------+---+-----+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#using raw sql commands\n",
        "df.createOrReplaceTempView(\"EMP\")\n",
        "spark.sql(\"select employee_name,department,state,salary,age,bonus from EMP ORDER BY department asc\").show(truncate=False)\n"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "ziJlc9UbgKcz",
        "outputId": "ab08edff-be60-4201-8f70-ff25e6b8fb77"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-------------+----------+-----+------+---+-----+\n",
            "|employee_name|department|state|salary|age|bonus|\n",
            "+-------------+----------+-----+------+---+-----+\n",
            "|Raman        |Finance   |CA   |99000 |40 |24000|\n",
            "|Scott        |Finance   |NY   |83000 |36 |19000|\n",
            "|Jen          |Finance   |NY   |79000 |53 |15000|\n",
            "|Maria        |Finance   |CA   |90000 |24 |23000|\n",
            "|Jeff         |Marketing |CA   |80000 |25 |18000|\n",
            "|Kumar        |Marketing |NY   |91000 |50 |21000|\n",
            "|James        |Sales     |NY   |90000 |34 |10000|\n",
            "|Michael      |Sales     |NY   |86000 |56 |20000|\n",
            "|Robert       |Sales     |CA   |81000 |30 |23000|\n",
            "+-------------+----------+-----+------+---+-----+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "simpleData = [(\"James\",\"Sales\",\"NY\",90000,34,10000),\n",
        "    (\"Michael\",\"Sales\",\"NY\",86000,56,20000),\n",
        "    (\"Robert\",\"Sales\",\"CA\",81000,30,23000),\n",
        "    (\"Maria\",\"Finance\",\"CA\",90000,24,23000),\n",
        "    (\"Raman\",\"Finance\",\"CA\",99000,40,24000),\n",
        "    (\"Scott\",\"Finance\",\"NY\",83000,36,19000),\n",
        "    (\"Jen\",\"Finance\",\"NY\",79000,53,15000),\n",
        "    (\"Jeff\",\"Marketing\",\"CA\",80000,25,18000),\n",
        "    (\"Kumar\",\"Marketing\",\"NY\",91000,50,21000)\n",
        "  ]\n",
        "\n",
        "schema = [\"employee_name\",\"department\",\"state\",\"salary\",\"age\",\"bonus\"]\n",
        "df = spark.createDataFrame(data=simpleData, schema = schema)\n",
        "df.printSchema()\n",
        "df.show(truncate=False)\n"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "aXviBdrHge4Y",
        "outputId": "c506e605-808a-4a17-e0c9-aba21c1afc82"
      },
      "execution_count": 14,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "root\n",
            " |-- employee_name: string (nullable = true)\n",
            " |-- department: string (nullable = true)\n",
            " |-- state: string (nullable = true)\n",
            " |-- salary: long (nullable = true)\n",
            " |-- age: long (nullable = true)\n",
            " |-- bonus: long (nullable = true)\n",
            "\n",
            "+-------------+----------+-----+------+---+-----+\n",
            "|employee_name|department|state|salary|age|bonus|\n",
            "+-------------+----------+-----+------+---+-----+\n",
            "|James        |Sales     |NY   |90000 |34 |10000|\n",
            "|Michael      |Sales     |NY   |86000 |56 |20000|\n",
            "|Robert       |Sales     |CA   |81000 |30 |23000|\n",
            "|Maria        |Finance   |CA   |90000 |24 |23000|\n",
            "|Raman        |Finance   |CA   |99000 |40 |24000|\n",
            "|Scott        |Finance   |NY   |83000 |36 |19000|\n",
            "|Jen          |Finance   |NY   |79000 |53 |15000|\n",
            "|Jeff         |Marketing |CA   |80000 |25 |18000|\n",
            "|Kumar        |Marketing |NY   |91000 |50 |21000|\n",
            "+-------------+----------+-----+------+---+-----+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#Grouping by sum of salary of each department\n",
        "df.groupBy(\"department\").sum(\"salary\").show(truncate=False)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "8WCVPekYgrtJ",
        "outputId": "6035dafa-c494-4342-a110-58c7359f3025"
      },
      "execution_count": 15,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+----------+-----------+\n",
            "|department|sum(salary)|\n",
            "+----------+-----------+\n",
            "|Sales     |257000     |\n",
            "|Finance   |351000     |\n",
            "|Marketing |171000     |\n",
            "+----------+-----------+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#Grouping by the count of each department\n",
        "df.groupBy(\"department\").count().show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "tFi_H_O4hV4w",
        "outputId": "a30e3d7d-14a7-4a85-aef5-7b8527d8755f"
      },
      "execution_count": 16,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+----------+-----+\n",
            "|department|count|\n",
            "+----------+-----+\n",
            "|     Sales|    3|\n",
            "|   Finance|    4|\n",
            "| Marketing|    2|\n",
            "+----------+-----+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#Grouping by each department's minimum salary\n",
        "df.groupBy(\"department\").min(\"salary\").show()\n"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "-6ytA9Bpg08p",
        "outputId": "09c746ed-5ea1-44d8-c965-badf5052c901"
      },
      "execution_count": 17,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+----------+-----------+\n",
            "|department|min(salary)|\n",
            "+----------+-----------+\n",
            "|     Sales|      81000|\n",
            "|   Finance|      79000|\n",
            "| Marketing|      80000|\n",
            "+----------+-----------+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#Grouping by each department's maximum salary\n",
        "df.groupBy(\"department\").max(\"salary\").show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Dbi0PYKQhd2A",
        "outputId": "7c514e70-5462-49a9-dc7a-ad98f7266d4a"
      },
      "execution_count": 18,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+----------+-----------+\n",
            "|department|max(salary)|\n",
            "+----------+-----------+\n",
            "|     Sales|      90000|\n",
            "|   Finance|      99000|\n",
            "| Marketing|      91000|\n",
            "+----------+-----------+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#grouping by each department's average salary\n",
        "df.groupBy(\"department\").avg( \"salary\").show()\n"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "LzJzdBSThkjD",
        "outputId": "a6c36f9a-e5e2-487c-abc0-d79a08bad4cd"
      },
      "execution_count": 19,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+----------+-----------------+\n",
            "|department|      avg(salary)|\n",
            "+----------+-----------------+\n",
            "|     Sales|85666.66666666667|\n",
            "|   Finance|          87750.0|\n",
            "| Marketing|          85500.0|\n",
            "+----------+-----------------+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#GroupBy on multiple columns\n",
        "#Grouping department according to state by their sum of salary and sum of bonus\n",
        "df.groupBy(\"department\",\"state\").sum(\"salary\",\"bonus\").show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "azsEqRaThxpk",
        "outputId": "116b7247-41ac-4085-a850-30b422e2cf5f"
      },
      "execution_count": 21,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+----------+-----+-----------+----------+\n",
            "|department|state|sum(salary)|sum(bonus)|\n",
            "+----------+-----+-----------+----------+\n",
            "|   Finance|   NY|     162000|     34000|\n",
            "| Marketing|   NY|      91000|     21000|\n",
            "|     Sales|   CA|      81000|     23000|\n",
            "| Marketing|   CA|      80000|     18000|\n",
            "|   Finance|   CA|     189000|     47000|\n",
            "|     Sales|   NY|     176000|     30000|\n",
            "+----------+-----+-----------+----------+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "from pyspark.sql.functions import sum,avg,max\n",
        "df.groupBy(\"department\") \\\n",
        "    .agg(sum(\"salary\").alias(\"sum_salary\"), \\\n",
        "         avg(\"salary\").alias(\"avg_salary\"), \\\n",
        "         sum(\"bonus\").alias(\"sum_bonus\"), \\\n",
        "         max(\"bonus\").alias(\"max_bonus\")).show(truncate=False)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "OgvVVQw0iDbj",
        "outputId": "1fa4d025-8f54-4c73-9b43-e01d27eb22a9"
      },
      "execution_count": 23,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+----------+----------+-----------------+---------+---------+\n",
            "|department|sum_salary|avg_salary       |sum_bonus|max_bonus|\n",
            "+----------+----------+-----------------+---------+---------+\n",
            "|Sales     |257000    |85666.66666666667|53000    |23000    |\n",
            "|Finance   |351000    |87750.0          |81000    |24000    |\n",
            "|Marketing |171000    |85500.0          |39000    |21000    |\n",
            "+----------+----------+-----------------+---------+---------+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "from pyspark.sql.functions import sum,avg,max\n",
        "df.groupBy(\"department\") \\\n",
        "    .agg(sum(\"salary\").alias(\"sum_salary\"), \\\n",
        "      avg(\"salary\").alias(\"avg_salary\"), \\\n",
        "      sum(\"bonus\").alias(\"sum_bonus\"), \\\n",
        "      max(\"bonus\").alias(\"max_bonus\")) \\\n",
        "    .where(col(\"sum_bonus\") >= 50000) \\\n",
        "    .show(truncate=False)\n"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "0rct2g1bkv10",
        "outputId": "9f47b9e6-1b67-4257-c7da-3b69f55bede2"
      },
      "execution_count": 24,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+----------+----------+-----------------+---------+---------+\n",
            "|department|sum_salary|avg_salary       |sum_bonus|max_bonus|\n",
            "+----------+----------+-----------------+---------+---------+\n",
            "|Sales     |257000    |85666.66666666667|53000    |23000    |\n",
            "|Finance   |351000    |87750.0          |81000    |24000    |\n",
            "+----------+----------+-----------------+---------+---------+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "\n",
        "emp = [(1,\"Smith\",-1,\"2018\",\"10\",\"M\",3000), \\\n",
        "    (2,\"Rose\",1,\"2010\",\"20\",\"M\",4000), \\\n",
        "    (3,\"Williams\",1,\"2010\",\"10\",\"M\",1000), \\\n",
        "    (4,\"Jones\",2,\"2005\",\"10\",\"F\",2000), \\\n",
        "    (5,\"Brown\",2,\"2010\",\"40\",\"\",-1), \\\n",
        "      (6,\"Brown\",2,\"2010\",\"50\",\"\",-1)]\n",
        "      \n",
        "empColumns = [\"emp_id\",\"name\",\"superior_emp_id\",\"year_joined\", \\\n",
        "       \"emp_dept_id\",\"gender\",\"salary\"]\n",
        "\n",
        "empDF = spark.createDataFrame(data=emp, schema = empColumns)\n",
        "empDF.printSchema()\n",
        "empDF.show(truncate=False)\n",
        "\n",
        "dept = [(\"Finance\",10), \\\n",
        "    (\"Marketing\",20), \\\n",
        "    (\"Sales\",30), \\\n",
        "    (\"IT\",40) \\\n",
        "  ]\n",
        "deptColumns = [\"dept_name\",\"dept_id\"]\n",
        "deptDF = spark.createDataFrame(data=dept, schema = deptColumns)\n",
        "deptDF.printSchema()\n",
        "deptDF.show(truncate=False)\n"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "AwYaVN1tn_Y5",
        "outputId": "52c9c6f1-ad35-41f9-c16f-a9893118568f"
      },
      "execution_count": 26,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "root\n",
            " |-- emp_id: long (nullable = true)\n",
            " |-- name: string (nullable = true)\n",
            " |-- superior_emp_id: long (nullable = true)\n",
            " |-- year_joined: string (nullable = true)\n",
            " |-- emp_dept_id: string (nullable = true)\n",
            " |-- gender: string (nullable = true)\n",
            " |-- salary: long (nullable = true)\n",
            "\n",
            "+------+--------+---------------+-----------+-----------+------+------+\n",
            "|emp_id|name    |superior_emp_id|year_joined|emp_dept_id|gender|salary|\n",
            "+------+--------+---------------+-----------+-----------+------+------+\n",
            "|1     |Smith   |-1             |2018       |10         |M     |3000  |\n",
            "|2     |Rose    |1              |2010       |20         |M     |4000  |\n",
            "|3     |Williams|1              |2010       |10         |M     |1000  |\n",
            "|4     |Jones   |2              |2005       |10         |F     |2000  |\n",
            "|5     |Brown   |2              |2010       |40         |      |-1    |\n",
            "|6     |Brown   |2              |2010       |50         |      |-1    |\n",
            "+------+--------+---------------+-----------+-----------+------+------+\n",
            "\n",
            "root\n",
            " |-- dept_name: string (nullable = true)\n",
            " |-- dept_id: long (nullable = true)\n",
            "\n",
            "+---------+-------+\n",
            "|dept_name|dept_id|\n",
            "+---------+-------+\n",
            "|Finance  |10     |\n",
            "|Marketing|20     |\n",
            "|Sales    |30     |\n",
            "|IT       |40     |\n",
            "+---------+-------+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#Inner join is the default join in PySpark and it’s mostly used. This joins two datasets on key columns, where keys don’t match the rows get dropped from both datasets (emp & dept).\n",
        "empDF.join(deptDF,empDF.emp_dept_id ==  deptDF.dept_id,\"inner\") \\\n",
        "     .show(truncate=False)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "1f9Ir7UYoSfA",
        "outputId": "17242ddf-76ca-4b10-88a4-2b05c628218e"
      },
      "execution_count": 27,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+------+--------+---------------+-----------+-----------+------+------+---------+-------+\n",
            "|emp_id|name    |superior_emp_id|year_joined|emp_dept_id|gender|salary|dept_name|dept_id|\n",
            "+------+--------+---------------+-----------+-----------+------+------+---------+-------+\n",
            "|1     |Smith   |-1             |2018       |10         |M     |3000  |Finance  |10     |\n",
            "|3     |Williams|1              |2010       |10         |M     |1000  |Finance  |10     |\n",
            "|4     |Jones   |2              |2005       |10         |F     |2000  |Finance  |10     |\n",
            "|2     |Rose    |1              |2010       |20         |M     |4000  |Marketing|20     |\n",
            "|5     |Brown   |2              |2010       |40         |      |-1    |IT       |40     |\n",
            "+------+--------+---------------+-----------+-----------+------+------+---------+-------+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#Outer a.k.a full, fullouter join returns all rows from both datasets, where join expression doesn’t match it returns null on respective record columns\n",
        "empDF.join(deptDF,empDF.emp_dept_id ==  deptDF.dept_id,\"outer\") \\\n",
        "    .show(truncate=False)\n",
        "empDF.join(deptDF,empDF.emp_dept_id ==  deptDF.dept_id,\"full\") \\\n",
        "    .show(truncate=False)\n",
        "empDF.join(deptDF,empDF.emp_dept_id ==  deptDF.dept_id,\"fullouter\") \\\n",
        "    .show(truncate=False)\n"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Sj8immONo9XB",
        "outputId": "06ef2567-9482-4bfa-d085-713248b6fee7"
      },
      "execution_count": 28,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+------+--------+---------------+-----------+-----------+------+------+---------+-------+\n",
            "|emp_id|name    |superior_emp_id|year_joined|emp_dept_id|gender|salary|dept_name|dept_id|\n",
            "+------+--------+---------------+-----------+-----------+------+------+---------+-------+\n",
            "|1     |Smith   |-1             |2018       |10         |M     |3000  |Finance  |10     |\n",
            "|3     |Williams|1              |2010       |10         |M     |1000  |Finance  |10     |\n",
            "|4     |Jones   |2              |2005       |10         |F     |2000  |Finance  |10     |\n",
            "|2     |Rose    |1              |2010       |20         |M     |4000  |Marketing|20     |\n",
            "|null  |null    |null           |null       |null       |null  |null  |Sales    |30     |\n",
            "|5     |Brown   |2              |2010       |40         |      |-1    |IT       |40     |\n",
            "|6     |Brown   |2              |2010       |50         |      |-1    |null     |null   |\n",
            "+------+--------+---------------+-----------+-----------+------+------+---------+-------+\n",
            "\n",
            "+------+--------+---------------+-----------+-----------+------+------+---------+-------+\n",
            "|emp_id|name    |superior_emp_id|year_joined|emp_dept_id|gender|salary|dept_name|dept_id|\n",
            "+------+--------+---------------+-----------+-----------+------+------+---------+-------+\n",
            "|1     |Smith   |-1             |2018       |10         |M     |3000  |Finance  |10     |\n",
            "|3     |Williams|1              |2010       |10         |M     |1000  |Finance  |10     |\n",
            "|4     |Jones   |2              |2005       |10         |F     |2000  |Finance  |10     |\n",
            "|2     |Rose    |1              |2010       |20         |M     |4000  |Marketing|20     |\n",
            "|null  |null    |null           |null       |null       |null  |null  |Sales    |30     |\n",
            "|5     |Brown   |2              |2010       |40         |      |-1    |IT       |40     |\n",
            "|6     |Brown   |2              |2010       |50         |      |-1    |null     |null   |\n",
            "+------+--------+---------------+-----------+-----------+------+------+---------+-------+\n",
            "\n",
            "+------+--------+---------------+-----------+-----------+------+------+---------+-------+\n",
            "|emp_id|name    |superior_emp_id|year_joined|emp_dept_id|gender|salary|dept_name|dept_id|\n",
            "+------+--------+---------------+-----------+-----------+------+------+---------+-------+\n",
            "|1     |Smith   |-1             |2018       |10         |M     |3000  |Finance  |10     |\n",
            "|3     |Williams|1              |2010       |10         |M     |1000  |Finance  |10     |\n",
            "|4     |Jones   |2              |2005       |10         |F     |2000  |Finance  |10     |\n",
            "|2     |Rose    |1              |2010       |20         |M     |4000  |Marketing|20     |\n",
            "|null  |null    |null           |null       |null       |null  |null  |Sales    |30     |\n",
            "|5     |Brown   |2              |2010       |40         |      |-1    |IT       |40     |\n",
            "|6     |Brown   |2              |2010       |50         |      |-1    |null     |null   |\n",
            "+------+--------+---------------+-----------+-----------+------+------+---------+-------+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#Left a.k.a Leftouter join returns all rows from the left dataset regardless of match found on the right dataset when join expression doesn’t match, it assigns null for that record and drops records from right where match not found.\n",
        "\n",
        "empDF.join(deptDF,empDF.emp_dept_id ==  deptDF.dept_id,\"left\").show(truncate=False)\n",
        "\n",
        "empDF.join(deptDF,empDF.emp_dept_id ==  deptDF.dept_id,\"leftouter\").show(truncate=False)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "ukDYVkVWpoTE",
        "outputId": "bf129be6-b76e-4ba0-8714-52153da25083"
      },
      "execution_count": 29,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+------+--------+---------------+-----------+-----------+------+------+---------+-------+\n",
            "|emp_id|name    |superior_emp_id|year_joined|emp_dept_id|gender|salary|dept_name|dept_id|\n",
            "+------+--------+---------------+-----------+-----------+------+------+---------+-------+\n",
            "|6     |Brown   |2              |2010       |50         |      |-1    |null     |null   |\n",
            "|1     |Smith   |-1             |2018       |10         |M     |3000  |Finance  |10     |\n",
            "|3     |Williams|1              |2010       |10         |M     |1000  |Finance  |10     |\n",
            "|4     |Jones   |2              |2005       |10         |F     |2000  |Finance  |10     |\n",
            "|2     |Rose    |1              |2010       |20         |M     |4000  |Marketing|20     |\n",
            "|5     |Brown   |2              |2010       |40         |      |-1    |IT       |40     |\n",
            "+------+--------+---------------+-----------+-----------+------+------+---------+-------+\n",
            "\n",
            "+------+--------+---------------+-----------+-----------+------+------+---------+-------+\n",
            "|emp_id|name    |superior_emp_id|year_joined|emp_dept_id|gender|salary|dept_name|dept_id|\n",
            "+------+--------+---------------+-----------+-----------+------+------+---------+-------+\n",
            "|6     |Brown   |2              |2010       |50         |      |-1    |null     |null   |\n",
            "|1     |Smith   |-1             |2018       |10         |M     |3000  |Finance  |10     |\n",
            "|3     |Williams|1              |2010       |10         |M     |1000  |Finance  |10     |\n",
            "|4     |Jones   |2              |2005       |10         |F     |2000  |Finance  |10     |\n",
            "|2     |Rose    |1              |2010       |20         |M     |4000  |Marketing|20     |\n",
            "|5     |Brown   |2              |2010       |40         |      |-1    |IT       |40     |\n",
            "+------+--------+---------------+-----------+-----------+------+------+---------+-------+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "empDF.join(deptDF,empDF.emp_dept_id ==  deptDF.dept_id,\"right\") \\\n",
        "   .show(truncate=False)\n",
        "empDF.join(deptDF,empDF.emp_dept_id ==  deptDF.dept_id,\"rightouter\") \\\n",
        "   .show(truncate=False)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "OG-zrlyUqUTj",
        "outputId": "605a6a28-5deb-4eea-bed8-3ce4a13853e5"
      },
      "execution_count": 30,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+------+--------+---------------+-----------+-----------+------+------+---------+-------+\n",
            "|emp_id|name    |superior_emp_id|year_joined|emp_dept_id|gender|salary|dept_name|dept_id|\n",
            "+------+--------+---------------+-----------+-----------+------+------+---------+-------+\n",
            "|4     |Jones   |2              |2005       |10         |F     |2000  |Finance  |10     |\n",
            "|3     |Williams|1              |2010       |10         |M     |1000  |Finance  |10     |\n",
            "|1     |Smith   |-1             |2018       |10         |M     |3000  |Finance  |10     |\n",
            "|null  |null    |null           |null       |null       |null  |null  |Sales    |30     |\n",
            "|2     |Rose    |1              |2010       |20         |M     |4000  |Marketing|20     |\n",
            "|5     |Brown   |2              |2010       |40         |      |-1    |IT       |40     |\n",
            "+------+--------+---------------+-----------+-----------+------+------+---------+-------+\n",
            "\n",
            "+------+--------+---------------+-----------+-----------+------+------+---------+-------+\n",
            "|emp_id|name    |superior_emp_id|year_joined|emp_dept_id|gender|salary|dept_name|dept_id|\n",
            "+------+--------+---------------+-----------+-----------+------+------+---------+-------+\n",
            "|4     |Jones   |2              |2005       |10         |F     |2000  |Finance  |10     |\n",
            "|3     |Williams|1              |2010       |10         |M     |1000  |Finance  |10     |\n",
            "|1     |Smith   |-1             |2018       |10         |M     |3000  |Finance  |10     |\n",
            "|null  |null    |null           |null       |null       |null  |null  |Sales    |30     |\n",
            "|2     |Rose    |1              |2010       |20         |M     |4000  |Marketing|20     |\n",
            "|5     |Brown   |2              |2010       |40         |      |-1    |IT       |40     |\n",
            "+------+--------+---------------+-----------+-----------+------+------+---------+-------+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "empDF.join(deptDF,empDF.emp_dept_id ==  deptDF.dept_id,\"leftsemi\").show(truncate=False)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "J0ZQ3TFmrIei",
        "outputId": "9a7789c5-9c40-45c5-bebb-ff5904b57f51"
      },
      "execution_count": 31,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+------+--------+---------------+-----------+-----------+------+------+\n",
            "|emp_id|name    |superior_emp_id|year_joined|emp_dept_id|gender|salary|\n",
            "+------+--------+---------------+-----------+-----------+------+------+\n",
            "|1     |Smith   |-1             |2018       |10         |M     |3000  |\n",
            "|3     |Williams|1              |2010       |10         |M     |1000  |\n",
            "|4     |Jones   |2              |2005       |10         |F     |2000  |\n",
            "|2     |Rose    |1              |2010       |20         |M     |4000  |\n",
            "|5     |Brown   |2              |2010       |40         |      |-1    |\n",
            "+------+--------+---------------+-----------+-----------+------+------+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "empDF.join(deptDF,empDF.emp_dept_id ==  deptDF.dept_id,\"leftanti\").show(truncate=False)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "44abusAMrKy4",
        "outputId": "3a910788-b9cd-477b-a3a2-e80c9a06d1a0"
      },
      "execution_count": 32,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+------+-----+---------------+-----------+-----------+------+------+\n",
            "|emp_id|name |superior_emp_id|year_joined|emp_dept_id|gender|salary|\n",
            "+------+-----+---------------+-----------+-----------+------+------+\n",
            "|6     |Brown|2              |2010       |50         |      |-1    |\n",
            "+------+-----+---------------+-----------+-----------+------+------+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "empDF.alias(\"emp1\").join(empDF.alias(\"emp2\"), \\\n",
        "    col(\"emp1.superior_emp_id\") == col(\"emp2.emp_id\"),\"inner\") \\\n",
        "    .select(col(\"emp1.emp_id\"),col(\"emp1.name\"), \\\n",
        "      col(\"emp2.emp_id\").alias(\"superior_emp_id\"), \\\n",
        "      col(\"emp2.name\").alias(\"superior_emp_name\")) \\\n",
        "   .show(truncate=False)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "LJhn0QKzrQv0",
        "outputId": "12d035f6-114b-4a6b-90fb-55c207743d8e"
      },
      "execution_count": 33,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+------+--------+---------------+-----------------+\n",
            "|emp_id|name    |superior_emp_id|superior_emp_name|\n",
            "+------+--------+---------------+-----------------+\n",
            "|2     |Rose    |1              |Smith            |\n",
            "|3     |Williams|1              |Smith            |\n",
            "|4     |Jones   |2              |Rose             |\n",
            "|5     |Brown   |2              |Rose             |\n",
            "|6     |Brown   |2              |Rose             |\n",
            "+------+--------+---------------+-----------------+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "empDF.createOrReplaceTempView(\"EMP\")\n",
        "deptDF.createOrReplaceTempView(\"DEPT\")\n",
        "\n",
        "joinDF = spark.sql(\"select * from EMP e, DEPT d where e.emp_dept_id == d.dept_id\") \\\n",
        "  .show(truncate=False)\n",
        "\n",
        "joinDF2 = spark.sql(\"select * from EMP e INNER JOIN DEPT d ON e.emp_dept_id == d.dept_id\") \\\n",
        "  .show(truncate=False)\n"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "dkrjIhulsnrY",
        "outputId": "3e6d59c0-b7ea-43bb-d2fe-2046baff08a9"
      },
      "execution_count": 34,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+------+--------+---------------+-----------+-----------+------+------+---------+-------+\n",
            "|emp_id|name    |superior_emp_id|year_joined|emp_dept_id|gender|salary|dept_name|dept_id|\n",
            "+------+--------+---------------+-----------+-----------+------+------+---------+-------+\n",
            "|1     |Smith   |-1             |2018       |10         |M     |3000  |Finance  |10     |\n",
            "|3     |Williams|1              |2010       |10         |M     |1000  |Finance  |10     |\n",
            "|4     |Jones   |2              |2005       |10         |F     |2000  |Finance  |10     |\n",
            "|2     |Rose    |1              |2010       |20         |M     |4000  |Marketing|20     |\n",
            "|5     |Brown   |2              |2010       |40         |      |-1    |IT       |40     |\n",
            "+------+--------+---------------+-----------+-----------+------+------+---------+-------+\n",
            "\n",
            "+------+--------+---------------+-----------+-----------+------+------+---------+-------+\n",
            "|emp_id|name    |superior_emp_id|year_joined|emp_dept_id|gender|salary|dept_name|dept_id|\n",
            "+------+--------+---------------+-----------+-----------+------+------+---------+-------+\n",
            "|1     |Smith   |-1             |2018       |10         |M     |3000  |Finance  |10     |\n",
            "|3     |Williams|1              |2010       |10         |M     |1000  |Finance  |10     |\n",
            "|4     |Jones   |2              |2005       |10         |F     |2000  |Finance  |10     |\n",
            "|2     |Rose    |1              |2010       |20         |M     |4000  |Marketing|20     |\n",
            "|5     |Brown   |2              |2010       |40         |      |-1    |IT       |40     |\n",
            "+------+--------+---------------+-----------+-----------+------+------+---------+-------+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "simpleData = [(\"James\",\"Sales\",\"NY\",90000,34,10000), \\\n",
        "    (\"Michael\",\"Sales\",\"NY\",86000,56,20000), \\\n",
        "    (\"Robert\",\"Sales\",\"CA\",81000,30,23000), \\\n",
        "    (\"Maria\",\"Finance\",\"CA\",90000,24,23000) \\\n",
        "  ]\n",
        "\n",
        "columns= [\"employee_name\",\"department\",\"state\",\"salary\",\"age\",\"bonus\"]\n",
        "df = spark.createDataFrame(data = simpleData, schema = columns)\n",
        "df.printSchema()\n",
        "df.show(truncate=False)\n"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Tl39nmL9tQju",
        "outputId": "f42d35ab-1a47-4980-a668-79647a91f52a"
      },
      "execution_count": 35,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "root\n",
            " |-- employee_name: string (nullable = true)\n",
            " |-- department: string (nullable = true)\n",
            " |-- state: string (nullable = true)\n",
            " |-- salary: long (nullable = true)\n",
            " |-- age: long (nullable = true)\n",
            " |-- bonus: long (nullable = true)\n",
            "\n",
            "+-------------+----------+-----+------+---+-----+\n",
            "|employee_name|department|state|salary|age|bonus|\n",
            "+-------------+----------+-----+------+---+-----+\n",
            "|James        |Sales     |NY   |90000 |34 |10000|\n",
            "|Michael      |Sales     |NY   |86000 |56 |20000|\n",
            "|Robert       |Sales     |CA   |81000 |30 |23000|\n",
            "|Maria        |Finance   |CA   |90000 |24 |23000|\n",
            "+-------------+----------+-----+------+---+-----+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "\n",
        "simpleData2 = [(\"James\",\"Sales\",\"NY\",90000,34,10000), \\\n",
        "    (\"Maria\",\"Finance\",\"CA\",90000,24,23000), \\\n",
        "    (\"Jen\",\"Finance\",\"NY\",79000,53,15000), \\\n",
        "    (\"Jeff\",\"Marketing\",\"CA\",80000,25,18000), \\\n",
        "    (\"Kumar\",\"Marketing\",\"NY\",91000,50,21000) \\\n",
        "  ]\n",
        "columns2= [\"employee_name\",\"department\",\"state\",\"salary\",\"age\",\"bonus\"]\n",
        "\n",
        "df2 = spark.createDataFrame(data = simpleData2, schema = columns2)\n",
        "\n",
        "df2.printSchema()\n",
        "df2.show(truncate=False)\n"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "F9f6wbzatSfI",
        "outputId": "06240187-8f35-4785-9774-ff4696c7fe15"
      },
      "execution_count": 36,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "root\n",
            " |-- employee_name: string (nullable = true)\n",
            " |-- department: string (nullable = true)\n",
            " |-- state: string (nullable = true)\n",
            " |-- salary: long (nullable = true)\n",
            " |-- age: long (nullable = true)\n",
            " |-- bonus: long (nullable = true)\n",
            "\n",
            "+-------------+----------+-----+------+---+-----+\n",
            "|employee_name|department|state|salary|age|bonus|\n",
            "+-------------+----------+-----+------+---+-----+\n",
            "|James        |Sales     |NY   |90000 |34 |10000|\n",
            "|Maria        |Finance   |CA   |90000 |24 |23000|\n",
            "|Jen          |Finance   |NY   |79000 |53 |15000|\n",
            "|Jeff         |Marketing |CA   |80000 |25 |18000|\n",
            "|Kumar        |Marketing |NY   |91000 |50 |21000|\n",
            "+-------------+----------+-----+------+---+-----+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#DataFrame union() method merges two DataFrames and returns the new DataFrame with all rows from two Dataframes regardless of duplicate data.\n",
        "unionDF = df.union(df2)\n",
        "unionDF.show(truncate=False)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "3omLIi7NtWFX",
        "outputId": "2fbc1b86-ee0c-459f-c252-791689cd486b"
      },
      "execution_count": 37,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-------------+----------+-----+------+---+-----+\n",
            "|employee_name|department|state|salary|age|bonus|\n",
            "+-------------+----------+-----+------+---+-----+\n",
            "|James        |Sales     |NY   |90000 |34 |10000|\n",
            "|Michael      |Sales     |NY   |86000 |56 |20000|\n",
            "|Robert       |Sales     |CA   |81000 |30 |23000|\n",
            "|Maria        |Finance   |CA   |90000 |24 |23000|\n",
            "|James        |Sales     |NY   |90000 |34 |10000|\n",
            "|Maria        |Finance   |CA   |90000 |24 |23000|\n",
            "|Jen          |Finance   |NY   |79000 |53 |15000|\n",
            "|Jeff         |Marketing |CA   |80000 |25 |18000|\n",
            "|Kumar        |Marketing |NY   |91000 |50 |21000|\n",
            "+-------------+----------+-----+------+---+-----+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "disDF = df.union(df2).distinct()\n",
        "disDF.show(truncate=False)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "XQGLuUxKtoIg",
        "outputId": "94eea4cc-864f-4592-a346-54c03d34dd2d"
      },
      "execution_count": 38,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-------------+----------+-----+------+---+-----+\n",
            "|employee_name|department|state|salary|age|bonus|\n",
            "+-------------+----------+-----+------+---+-----+\n",
            "|James        |Sales     |NY   |90000 |34 |10000|\n",
            "|Maria        |Finance   |CA   |90000 |24 |23000|\n",
            "|Michael      |Sales     |NY   |86000 |56 |20000|\n",
            "|Robert       |Sales     |CA   |81000 |30 |23000|\n",
            "|Kumar        |Marketing |NY   |91000 |50 |21000|\n",
            "|Jen          |Finance   |NY   |79000 |53 |15000|\n",
            "|Jeff         |Marketing |CA   |80000 |25 |18000|\n",
            "+-------------+----------+-----+------+---+-----+\n",
            "\n"
          ]
        }
      ]
    }
  ]
}