Databricks_Pyspark_String_Basics.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/abhilas0/pyspark-trasformation-practice/blob/main/Databricks_Pyspark_String_Basics.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {},
          "inputWidgets": {},
          "nuid": "e3a0df14-6bab-4fc3-a877-1d885ba1de8c",
          "showTitle": false,
          "title": ""
        },
        "id": "ov0SDi0p9LCa"
      },
      "source": [
        "<span style=\"color:blue\">\n",
        " <h2> Pyspark String Basics\n",
        "</span>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {},
          "inputWidgets": {},
          "nuid": "88991a3d-6bba-4cbe-99e1-da5d305f6504",
          "showTitle": false,
          "title": ""
        },
        "id": "tuVVFUWz9LCc"
      },
      "source": [
        "## Basic Level Questions"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "from google.colab import drive\n",
        "drive.mount('/content/drive')"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "8tsTMm9r9uMz",
        "outputId": "82d8334d-2736-41ae-a517-8d8e5f32de32"
      },
      "execution_count": 4,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Drive already mounted at /content/drive; to attempt to forcibly remount, call drive.mount(\"/content/drive\", force_remount=True).\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "! pip install pyspark"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "z3WLBY4T-H74",
        "outputId": "350e3e65-3b6f-4b56-dabc-16f7c02c2cbc"
      },
      "execution_count": 5,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Requirement already satisfied: pyspark in /usr/local/lib/python3.10/dist-packages (3.5.1)\n",
            "Requirement already satisfied: py4j==0.10.9.7 in /usr/local/lib/python3.10/dist-packages (from pyspark) (0.10.9.7)\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "from pyspark.sql import SparkSession\n",
        "spark = SparkSession.builder.appName(\"String basics\").getOrCreate()"
      ],
      "metadata": {
        "id": "dSHX0Aj3-VhJ"
      },
      "execution_count": 6,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# read the csv file\n",
        "df = spark.read.csv(\"/content/drive/MyDrive/DE Data/Orders1.csv\", header=True, inferSchema=True)\n",
        "df.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "0nNY7IUvgzNV",
        "outputId": "9b4579aa-506c-427a-e832-ccda7b802d8f"
      },
      "execution_count": 7,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------------+----------+---------------+------------+-------------+---------+---------+---------+--------+------------+----------+-----------+\n",
            "|              Region|   Country|       ItemType|SalesChannel|OrderPriority|  OrderID|UnitsSold|UnitPrice|UnitCost|TotalRevenue| TotalCost|TotalProfit|\n",
            "+--------------------+----------+---------------+------------+-------------+---------+---------+---------+--------+------------+----------+-----------+\n",
            "|Middle East and N...|     Libya|      Cosmetics|     Offline|            M|686800706|     8446|    437.2|  263.33|   3692591.2|2224085.18| 1468506.02|\n",
            "|       North America|    Canada|     Vegetables|      Online|            M|185941302|     3018|   154.06|   90.93|   464953.08| 274426.74|  190526.34|\n",
            "|Middle East and N...|     Libya|      Baby Food|     Offline|            C|246222341|     1517|   255.28|  159.42|   387259.76| 241840.14|  145419.62|\n",
            "|                Asia|     Japan|         Cereal|     Offline|            C|161442649|     3322|    205.7|  117.11|    683335.4| 389039.42|  294295.98|\n",
            "|  Sub-Saharan Africa|      Chad|         Fruits|     Offline|            H|645713555|     9845|     9.33|    6.92|    91853.85|   68127.4|   23726.45|\n",
            "|              Europe|   Armenia|         Cereal|      Online|            H|683458888|     9528|    205.7|  117.11|   1959909.6|1115824.08|  844085.52|\n",
            "|  Sub-Saharan Africa|   Eritrea|         Cereal|      Online|            H|679414975|     2844|    205.7|  117.11|    585010.8| 333060.84|  251949.96|\n",
            "|              Europe|Montenegro|        Clothes|     Offline|            M|208630645|     7299|   109.28|   35.84|   797634.72| 261596.16|  536038.56|\n",
            "|Central America a...|   Jamaica|     Vegetables|      Online|            H|266467225|     2428|   154.06|   90.93|   374057.68| 220778.04|  153279.64|\n",
            "|Australia and Oce...|      Fiji|     Vegetables|     Offline|            H|118598544|     4800|   154.06|   90.93|    739488.0|  436464.0|   303024.0|\n",
            "|  Sub-Saharan Africa|      Togo|        Clothes|      Online|            M|451010930|     3012|   109.28|   35.84|   329151.36| 107950.08|  221201.28|\n",
            "|              Europe|Montenegro|         Snacks|     Offline|            M|220003211|     2694|   152.58|   97.44|   411050.52| 262503.36|  148547.16|\n",
            "|              Europe|    Greece|      Household|      Online|            C|702186715|     1508|   668.27|  502.54|  1007751.16| 757830.32|  249920.84|\n",
            "|  Sub-Saharan Africa|     Sudan|      Cosmetics|      Online|            C|544485270|     4146|    437.2|  263.33|   1812631.2|1091766.18|  720865.02|\n",
            "|                Asia|  Maldives|         Fruits|     Offline|            L|714135205|     7332|     9.33|    6.92|    68407.56|  50737.44|   17670.12|\n",
            "|              Europe|Montenegro|        Clothes|     Offline|            H|448685348|     4820|   109.28|   35.84|    526729.6|  172748.8|   353980.8|\n",
            "|              Europe|   Estonia|Office Supplies|      Online|            H|405997025|     2397|   651.21|  524.96|  1560950.37|1258329.12|  302621.25|\n",
            "|       North America| Greenland|      Beverages|      Online|            M|414244067|     2880|    47.45|   31.79|    136656.0|   91555.2|    45100.8|\n",
            "|  Sub-Saharan Africa|Cape Verde|        Clothes|      Online|            C|821912801|     1117|   109.28|   35.84|   122065.76|  40033.28|   82032.48|\n",
            "|  Sub-Saharan Africa|   Senegal|      Household|     Offline|            L|247802054|     8989|   668.27|  502.54|  6007079.03|4517332.06| 1489746.97|\n",
            "+--------------------+----------+---------------+------------+-------------+---------+---------+---------+--------+------------+----------+-----------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {},
          "inputWidgets": {},
          "nuid": "b92b087b-3920-47e3-8b61-18176d8dc093",
          "showTitle": false,
          "title": ""
        },
        "id": "kODv7iIP9LCc"
      },
      "source": [
        "1. Convert the 'Region' column to lowercase."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 8,
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {},
          "inputWidgets": {},
          "nuid": "d56ec27a-fad9-49ca-b8f6-a4f0b4b13791",
          "showTitle": false,
          "title": ""
        },
        "id": "uozlNqyV9LCc",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "b45aaa3f-0c21-47e8-b019-54b5db44a672"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------------+----------+---------------+------------+-------------+---------+---------+---------+--------+------------+----------+-----------+\n",
            "|              Region|   Country|       ItemType|SalesChannel|OrderPriority|  OrderID|UnitsSold|UnitPrice|UnitCost|TotalRevenue| TotalCost|TotalProfit|\n",
            "+--------------------+----------+---------------+------------+-------------+---------+---------+---------+--------+------------+----------+-----------+\n",
            "|middle east and n...|     Libya|      Cosmetics|     Offline|            M|686800706|     8446|    437.2|  263.33|   3692591.2|2224085.18| 1468506.02|\n",
            "|       north america|    Canada|     Vegetables|      Online|            M|185941302|     3018|   154.06|   90.93|   464953.08| 274426.74|  190526.34|\n",
            "|middle east and n...|     Libya|      Baby Food|     Offline|            C|246222341|     1517|   255.28|  159.42|   387259.76| 241840.14|  145419.62|\n",
            "|                asia|     Japan|         Cereal|     Offline|            C|161442649|     3322|    205.7|  117.11|    683335.4| 389039.42|  294295.98|\n",
            "|  sub-saharan africa|      Chad|         Fruits|     Offline|            H|645713555|     9845|     9.33|    6.92|    91853.85|   68127.4|   23726.45|\n",
            "|              europe|   Armenia|         Cereal|      Online|            H|683458888|     9528|    205.7|  117.11|   1959909.6|1115824.08|  844085.52|\n",
            "|  sub-saharan africa|   Eritrea|         Cereal|      Online|            H|679414975|     2844|    205.7|  117.11|    585010.8| 333060.84|  251949.96|\n",
            "|              europe|Montenegro|        Clothes|     Offline|            M|208630645|     7299|   109.28|   35.84|   797634.72| 261596.16|  536038.56|\n",
            "|central america a...|   Jamaica|     Vegetables|      Online|            H|266467225|     2428|   154.06|   90.93|   374057.68| 220778.04|  153279.64|\n",
            "|australia and oce...|      Fiji|     Vegetables|     Offline|            H|118598544|     4800|   154.06|   90.93|    739488.0|  436464.0|   303024.0|\n",
            "|  sub-saharan africa|      Togo|        Clothes|      Online|            M|451010930|     3012|   109.28|   35.84|   329151.36| 107950.08|  221201.28|\n",
            "|              europe|Montenegro|         Snacks|     Offline|            M|220003211|     2694|   152.58|   97.44|   411050.52| 262503.36|  148547.16|\n",
            "|              europe|    Greece|      Household|      Online|            C|702186715|     1508|   668.27|  502.54|  1007751.16| 757830.32|  249920.84|\n",
            "|  sub-saharan africa|     Sudan|      Cosmetics|      Online|            C|544485270|     4146|    437.2|  263.33|   1812631.2|1091766.18|  720865.02|\n",
            "|                asia|  Maldives|         Fruits|     Offline|            L|714135205|     7332|     9.33|    6.92|    68407.56|  50737.44|   17670.12|\n",
            "|              europe|Montenegro|        Clothes|     Offline|            H|448685348|     4820|   109.28|   35.84|    526729.6|  172748.8|   353980.8|\n",
            "|              europe|   Estonia|Office Supplies|      Online|            H|405997025|     2397|   651.21|  524.96|  1560950.37|1258329.12|  302621.25|\n",
            "|       north america| Greenland|      Beverages|      Online|            M|414244067|     2880|    47.45|   31.79|    136656.0|   91555.2|    45100.8|\n",
            "|  sub-saharan africa|Cape Verde|        Clothes|      Online|            C|821912801|     1117|   109.28|   35.84|   122065.76|  40033.28|   82032.48|\n",
            "|  sub-saharan africa|   Senegal|      Household|     Offline|            L|247802054|     8989|   668.27|  502.54|  6007079.03|4517332.06| 1489746.97|\n",
            "+--------------------+----------+---------------+------------+-------------+---------+---------+---------+--------+------------+----------+-----------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ],
      "source": [
        "# Your code here\n",
        "\n",
        "from pyspark.sql.functions import *\n",
        "\n",
        "df1= df.withColumn(\"Region\", lower(df[\"Region\"]))\n",
        "df1.show()\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {},
          "inputWidgets": {},
          "nuid": "22942c1b-d3a6-4aeb-9213-d20bfea4c1ae",
          "showTitle": false,
          "title": ""
        },
        "id": "MWL-8jsY9LCd"
      },
      "source": [
        "2. Convert the 'Country' column to uppercase."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 9,
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {},
          "inputWidgets": {},
          "nuid": "45be7dbd-bb32-4859-aa92-a7d1cd98e549",
          "showTitle": false,
          "title": ""
        },
        "id": "nF9e_BB59LCd",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "7d2ba28d-d199-4c78-ad14-a4f13e4c5c66"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------------+----------+---------------+------------+-------------+---------+---------+---------+--------+------------+----------+-----------+\n",
            "|              Region|   Country|       ItemType|SalesChannel|OrderPriority|  OrderID|UnitsSold|UnitPrice|UnitCost|TotalRevenue| TotalCost|TotalProfit|\n",
            "+--------------------+----------+---------------+------------+-------------+---------+---------+---------+--------+------------+----------+-----------+\n",
            "|middle east and n...|     LIBYA|      Cosmetics|     Offline|            M|686800706|     8446|    437.2|  263.33|   3692591.2|2224085.18| 1468506.02|\n",
            "|       north america|    CANADA|     Vegetables|      Online|            M|185941302|     3018|   154.06|   90.93|   464953.08| 274426.74|  190526.34|\n",
            "|middle east and n...|     LIBYA|      Baby Food|     Offline|            C|246222341|     1517|   255.28|  159.42|   387259.76| 241840.14|  145419.62|\n",
            "|                asia|     JAPAN|         Cereal|     Offline|            C|161442649|     3322|    205.7|  117.11|    683335.4| 389039.42|  294295.98|\n",
            "|  sub-saharan africa|      CHAD|         Fruits|     Offline|            H|645713555|     9845|     9.33|    6.92|    91853.85|   68127.4|   23726.45|\n",
            "|              europe|   ARMENIA|         Cereal|      Online|            H|683458888|     9528|    205.7|  117.11|   1959909.6|1115824.08|  844085.52|\n",
            "|  sub-saharan africa|   ERITREA|         Cereal|      Online|            H|679414975|     2844|    205.7|  117.11|    585010.8| 333060.84|  251949.96|\n",
            "|              europe|MONTENEGRO|        Clothes|     Offline|            M|208630645|     7299|   109.28|   35.84|   797634.72| 261596.16|  536038.56|\n",
            "|central america a...|   JAMAICA|     Vegetables|      Online|            H|266467225|     2428|   154.06|   90.93|   374057.68| 220778.04|  153279.64|\n",
            "|australia and oce...|      FIJI|     Vegetables|     Offline|            H|118598544|     4800|   154.06|   90.93|    739488.0|  436464.0|   303024.0|\n",
            "|  sub-saharan africa|      TOGO|        Clothes|      Online|            M|451010930|     3012|   109.28|   35.84|   329151.36| 107950.08|  221201.28|\n",
            "|              europe|MONTENEGRO|         Snacks|     Offline|            M|220003211|     2694|   152.58|   97.44|   411050.52| 262503.36|  148547.16|\n",
            "|              europe|    GREECE|      Household|      Online|            C|702186715|     1508|   668.27|  502.54|  1007751.16| 757830.32|  249920.84|\n",
            "|  sub-saharan africa|     SUDAN|      Cosmetics|      Online|            C|544485270|     4146|    437.2|  263.33|   1812631.2|1091766.18|  720865.02|\n",
            "|                asia|  MALDIVES|         Fruits|     Offline|            L|714135205|     7332|     9.33|    6.92|    68407.56|  50737.44|   17670.12|\n",
            "|              europe|MONTENEGRO|        Clothes|     Offline|            H|448685348|     4820|   109.28|   35.84|    526729.6|  172748.8|   353980.8|\n",
            "|              europe|   ESTONIA|Office Supplies|      Online|            H|405997025|     2397|   651.21|  524.96|  1560950.37|1258329.12|  302621.25|\n",
            "|       north america| GREENLAND|      Beverages|      Online|            M|414244067|     2880|    47.45|   31.79|    136656.0|   91555.2|    45100.8|\n",
            "|  sub-saharan africa|CAPE VERDE|        Clothes|      Online|            C|821912801|     1117|   109.28|   35.84|   122065.76|  40033.28|   82032.48|\n",
            "|  sub-saharan africa|   SENEGAL|      Household|     Offline|            L|247802054|     8989|   668.27|  502.54|  6007079.03|4517332.06| 1489746.97|\n",
            "+--------------------+----------+---------------+------------+-------------+---------+---------+---------+--------+------------+----------+-----------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ],
      "source": [
        "# Your code here\n",
        "\n",
        "df2 = df1.withColumn(\"Country\",upper(df1[\"Country\"]))\n",
        "df2.show()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {},
          "inputWidgets": {},
          "nuid": "709fd1b5-c5e0-4e53-98eb-396134f8611a",
          "showTitle": false,
          "title": ""
        },
        "id": "Frr8tbBj9LCd"
      },
      "source": [
        "3. Apply initcap to the 'ItemType' column."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 10,
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {},
          "inputWidgets": {},
          "nuid": "7da3cf64-cbf8-40b4-a6ca-bc98a6c784ba",
          "showTitle": false,
          "title": ""
        },
        "id": "boC3jO-_9LCe",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "9ed224ae-e112-49f8-942e-155f8fadb833"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------------+----------+---------------+------------+-------------+---------+---------+---------+--------+------------+----------+-----------+\n",
            "|              Region|   Country|       ItemType|SalesChannel|OrderPriority|  OrderID|UnitsSold|UnitPrice|UnitCost|TotalRevenue| TotalCost|TotalProfit|\n",
            "+--------------------+----------+---------------+------------+-------------+---------+---------+---------+--------+------------+----------+-----------+\n",
            "|middle east and n...|     LIBYA|      Cosmetics|     Offline|            M|686800706|     8446|    437.2|  263.33|   3692591.2|2224085.18| 1468506.02|\n",
            "|       north america|    CANADA|     Vegetables|      Online|            M|185941302|     3018|   154.06|   90.93|   464953.08| 274426.74|  190526.34|\n",
            "|middle east and n...|     LIBYA|      Baby Food|     Offline|            C|246222341|     1517|   255.28|  159.42|   387259.76| 241840.14|  145419.62|\n",
            "|                asia|     JAPAN|         Cereal|     Offline|            C|161442649|     3322|    205.7|  117.11|    683335.4| 389039.42|  294295.98|\n",
            "|  sub-saharan africa|      CHAD|         Fruits|     Offline|            H|645713555|     9845|     9.33|    6.92|    91853.85|   68127.4|   23726.45|\n",
            "|              europe|   ARMENIA|         Cereal|      Online|            H|683458888|     9528|    205.7|  117.11|   1959909.6|1115824.08|  844085.52|\n",
            "|  sub-saharan africa|   ERITREA|         Cereal|      Online|            H|679414975|     2844|    205.7|  117.11|    585010.8| 333060.84|  251949.96|\n",
            "|              europe|MONTENEGRO|        Clothes|     Offline|            M|208630645|     7299|   109.28|   35.84|   797634.72| 261596.16|  536038.56|\n",
            "|central america a...|   JAMAICA|     Vegetables|      Online|            H|266467225|     2428|   154.06|   90.93|   374057.68| 220778.04|  153279.64|\n",
            "|australia and oce...|      FIJI|     Vegetables|     Offline|            H|118598544|     4800|   154.06|   90.93|    739488.0|  436464.0|   303024.0|\n",
            "|  sub-saharan africa|      TOGO|        Clothes|      Online|            M|451010930|     3012|   109.28|   35.84|   329151.36| 107950.08|  221201.28|\n",
            "|              europe|MONTENEGRO|         Snacks|     Offline|            M|220003211|     2694|   152.58|   97.44|   411050.52| 262503.36|  148547.16|\n",
            "|              europe|    GREECE|      Household|      Online|            C|702186715|     1508|   668.27|  502.54|  1007751.16| 757830.32|  249920.84|\n",
            "|  sub-saharan africa|     SUDAN|      Cosmetics|      Online|            C|544485270|     4146|    437.2|  263.33|   1812631.2|1091766.18|  720865.02|\n",
            "|                asia|  MALDIVES|         Fruits|     Offline|            L|714135205|     7332|     9.33|    6.92|    68407.56|  50737.44|   17670.12|\n",
            "|              europe|MONTENEGRO|        Clothes|     Offline|            H|448685348|     4820|   109.28|   35.84|    526729.6|  172748.8|   353980.8|\n",
            "|              europe|   ESTONIA|Office Supplies|      Online|            H|405997025|     2397|   651.21|  524.96|  1560950.37|1258329.12|  302621.25|\n",
            "|       north america| GREENLAND|      Beverages|      Online|            M|414244067|     2880|    47.45|   31.79|    136656.0|   91555.2|    45100.8|\n",
            "|  sub-saharan africa|CAPE VERDE|        Clothes|      Online|            C|821912801|     1117|   109.28|   35.84|   122065.76|  40033.28|   82032.48|\n",
            "|  sub-saharan africa|   SENEGAL|      Household|     Offline|            L|247802054|     8989|   668.27|  502.54|  6007079.03|4517332.06| 1489746.97|\n",
            "+--------------------+----------+---------------+------------+-------------+---------+---------+---------+--------+------------+----------+-----------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ],
      "source": [
        "# Your code here\n",
        "\n",
        "df3 = df2.withColumn(\"ItemType\", initcap(df2[\"ItemType\"]))\n",
        "df3.show()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {},
          "inputWidgets": {},
          "nuid": "33f19c31-689b-4a06-b061-1ae91f1c3d53",
          "showTitle": false,
          "title": ""
        },
        "id": "Gd008HF09LCe"
      },
      "source": [
        "4. Concatenate 'Region' and 'Country' columns with a hyphen between them."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 11,
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {},
          "inputWidgets": {},
          "nuid": "8f72dfdb-eaaa-4966-af92-690c0789f63d",
          "showTitle": false,
          "title": ""
        },
        "id": "YXZlzxIP9LCe",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "fca7621b-7f30-422e-cd9d-77d0616952e5"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------------+----------+---------------+------------+-------------+---------+---------+---------+--------+------------+----------+-----------+--------------------+\n",
            "|              Region|   Country|       ItemType|SalesChannel|OrderPriority|  OrderID|UnitsSold|UnitPrice|UnitCost|TotalRevenue| TotalCost|TotalProfit|      Region_Country|\n",
            "+--------------------+----------+---------------+------------+-------------+---------+---------+---------+--------+------------+----------+-----------+--------------------+\n",
            "|middle east and n...|     LIBYA|      Cosmetics|     Offline|            M|686800706|     8446|    437.2|  263.33|   3692591.2|2224085.18| 1468506.02|middle east and n...|\n",
            "|       north america|    CANADA|     Vegetables|      Online|            M|185941302|     3018|   154.06|   90.93|   464953.08| 274426.74|  190526.34|north america-CANADA|\n",
            "|middle east and n...|     LIBYA|      Baby Food|     Offline|            C|246222341|     1517|   255.28|  159.42|   387259.76| 241840.14|  145419.62|middle east and n...|\n",
            "|                asia|     JAPAN|         Cereal|     Offline|            C|161442649|     3322|    205.7|  117.11|    683335.4| 389039.42|  294295.98|          asia-JAPAN|\n",
            "|  sub-saharan africa|      CHAD|         Fruits|     Offline|            H|645713555|     9845|     9.33|    6.92|    91853.85|   68127.4|   23726.45|sub-saharan afric...|\n",
            "|              europe|   ARMENIA|         Cereal|      Online|            H|683458888|     9528|    205.7|  117.11|   1959909.6|1115824.08|  844085.52|      europe-ARMENIA|\n",
            "|  sub-saharan africa|   ERITREA|         Cereal|      Online|            H|679414975|     2844|    205.7|  117.11|    585010.8| 333060.84|  251949.96|sub-saharan afric...|\n",
            "|              europe|MONTENEGRO|        Clothes|     Offline|            M|208630645|     7299|   109.28|   35.84|   797634.72| 261596.16|  536038.56|   europe-MONTENEGRO|\n",
            "|central america a...|   JAMAICA|     Vegetables|      Online|            H|266467225|     2428|   154.06|   90.93|   374057.68| 220778.04|  153279.64|central america a...|\n",
            "|australia and oce...|      FIJI|     Vegetables|     Offline|            H|118598544|     4800|   154.06|   90.93|    739488.0|  436464.0|   303024.0|australia and oce...|\n",
            "|  sub-saharan africa|      TOGO|        Clothes|      Online|            M|451010930|     3012|   109.28|   35.84|   329151.36| 107950.08|  221201.28|sub-saharan afric...|\n",
            "|              europe|MONTENEGRO|         Snacks|     Offline|            M|220003211|     2694|   152.58|   97.44|   411050.52| 262503.36|  148547.16|   europe-MONTENEGRO|\n",
            "|              europe|    GREECE|      Household|      Online|            C|702186715|     1508|   668.27|  502.54|  1007751.16| 757830.32|  249920.84|       europe-GREECE|\n",
            "|  sub-saharan africa|     SUDAN|      Cosmetics|      Online|            C|544485270|     4146|    437.2|  263.33|   1812631.2|1091766.18|  720865.02|sub-saharan afric...|\n",
            "|                asia|  MALDIVES|         Fruits|     Offline|            L|714135205|     7332|     9.33|    6.92|    68407.56|  50737.44|   17670.12|       asia-MALDIVES|\n",
            "|              europe|MONTENEGRO|        Clothes|     Offline|            H|448685348|     4820|   109.28|   35.84|    526729.6|  172748.8|   353980.8|   europe-MONTENEGRO|\n",
            "|              europe|   ESTONIA|Office Supplies|      Online|            H|405997025|     2397|   651.21|  524.96|  1560950.37|1258329.12|  302621.25|      europe-ESTONIA|\n",
            "|       north america| GREENLAND|      Beverages|      Online|            M|414244067|     2880|    47.45|   31.79|    136656.0|   91555.2|    45100.8|north america-GRE...|\n",
            "|  sub-saharan africa|CAPE VERDE|        Clothes|      Online|            C|821912801|     1117|   109.28|   35.84|   122065.76|  40033.28|   82032.48|sub-saharan afric...|\n",
            "|  sub-saharan africa|   SENEGAL|      Household|     Offline|            L|247802054|     8989|   668.27|  502.54|  6007079.03|4517332.06| 1489746.97|sub-saharan afric...|\n",
            "+--------------------+----------+---------------+------------+-------------+---------+---------+---------+--------+------------+----------+-----------+--------------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ],
      "source": [
        "# Your code here\n",
        "\n",
        "# concat region and country\n",
        "\n",
        "df4 = df3.withColumn(\"Region_Country\",concat(df3[\"Region\"],lit(\"-\"),df3[\"Country\"]))\n",
        "df4.show()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {},
          "inputWidgets": {},
          "nuid": "5e568d80-ad28-4a3d-bb6e-77bb7ce63969",
          "showTitle": false,
          "title": ""
        },
        "id": "dGghuio69LCe"
      },
      "source": [
        "5. Find the length of the 'OrderID' column."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 12,
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {},
          "inputWidgets": {},
          "nuid": "9a13d978-f2c9-4719-bde6-e7909be88ff8",
          "showTitle": false,
          "title": ""
        },
        "id": "gJF0d61C9LCe",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "8b3094ef-4e6d-4016-dfaa-8f888ffdf536"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------------+----------+---------------+------------+-------------+---------+---------+---------+--------+------------+----------+-----------+--------------------+------------+\n",
            "|              Region|   Country|       ItemType|SalesChannel|OrderPriority|  OrderID|UnitsSold|UnitPrice|UnitCost|TotalRevenue| TotalCost|TotalProfit|      Region_Country|Order_lenght|\n",
            "+--------------------+----------+---------------+------------+-------------+---------+---------+---------+--------+------------+----------+-----------+--------------------+------------+\n",
            "|middle east and n...|     LIBYA|      Cosmetics|     Offline|            M|686800706|     8446|    437.2|  263.33|   3692591.2|2224085.18| 1468506.02|middle east and n...|           9|\n",
            "|       north america|    CANADA|     Vegetables|      Online|            M|185941302|     3018|   154.06|   90.93|   464953.08| 274426.74|  190526.34|north america-CANADA|           9|\n",
            "|middle east and n...|     LIBYA|      Baby Food|     Offline|            C|246222341|     1517|   255.28|  159.42|   387259.76| 241840.14|  145419.62|middle east and n...|           9|\n",
            "|                asia|     JAPAN|         Cereal|     Offline|            C|161442649|     3322|    205.7|  117.11|    683335.4| 389039.42|  294295.98|          asia-JAPAN|           9|\n",
            "|  sub-saharan africa|      CHAD|         Fruits|     Offline|            H|645713555|     9845|     9.33|    6.92|    91853.85|   68127.4|   23726.45|sub-saharan afric...|           9|\n",
            "|              europe|   ARMENIA|         Cereal|      Online|            H|683458888|     9528|    205.7|  117.11|   1959909.6|1115824.08|  844085.52|      europe-ARMENIA|           9|\n",
            "|  sub-saharan africa|   ERITREA|         Cereal|      Online|            H|679414975|     2844|    205.7|  117.11|    585010.8| 333060.84|  251949.96|sub-saharan afric...|           9|\n",
            "|              europe|MONTENEGRO|        Clothes|     Offline|            M|208630645|     7299|   109.28|   35.84|   797634.72| 261596.16|  536038.56|   europe-MONTENEGRO|           9|\n",
            "|central america a...|   JAMAICA|     Vegetables|      Online|            H|266467225|     2428|   154.06|   90.93|   374057.68| 220778.04|  153279.64|central america a...|           9|\n",
            "|australia and oce...|      FIJI|     Vegetables|     Offline|            H|118598544|     4800|   154.06|   90.93|    739488.0|  436464.0|   303024.0|australia and oce...|           9|\n",
            "|  sub-saharan africa|      TOGO|        Clothes|      Online|            M|451010930|     3012|   109.28|   35.84|   329151.36| 107950.08|  221201.28|sub-saharan afric...|           9|\n",
            "|              europe|MONTENEGRO|         Snacks|     Offline|            M|220003211|     2694|   152.58|   97.44|   411050.52| 262503.36|  148547.16|   europe-MONTENEGRO|           9|\n",
            "|              europe|    GREECE|      Household|      Online|            C|702186715|     1508|   668.27|  502.54|  1007751.16| 757830.32|  249920.84|       europe-GREECE|           9|\n",
            "|  sub-saharan africa|     SUDAN|      Cosmetics|      Online|            C|544485270|     4146|    437.2|  263.33|   1812631.2|1091766.18|  720865.02|sub-saharan afric...|           9|\n",
            "|                asia|  MALDIVES|         Fruits|     Offline|            L|714135205|     7332|     9.33|    6.92|    68407.56|  50737.44|   17670.12|       asia-MALDIVES|           9|\n",
            "|              europe|MONTENEGRO|        Clothes|     Offline|            H|448685348|     4820|   109.28|   35.84|    526729.6|  172748.8|   353980.8|   europe-MONTENEGRO|           9|\n",
            "|              europe|   ESTONIA|Office Supplies|      Online|            H|405997025|     2397|   651.21|  524.96|  1560950.37|1258329.12|  302621.25|      europe-ESTONIA|           9|\n",
            "|       north america| GREENLAND|      Beverages|      Online|            M|414244067|     2880|    47.45|   31.79|    136656.0|   91555.2|    45100.8|north america-GRE...|           9|\n",
            "|  sub-saharan africa|CAPE VERDE|        Clothes|      Online|            C|821912801|     1117|   109.28|   35.84|   122065.76|  40033.28|   82032.48|sub-saharan afric...|           9|\n",
            "|  sub-saharan africa|   SENEGAL|      Household|     Offline|            L|247802054|     8989|   668.27|  502.54|  6007079.03|4517332.06| 1489746.97|sub-saharan afric...|           9|\n",
            "+--------------------+----------+---------------+------------+-------------+---------+---------+---------+--------+------------+----------+-----------+--------------------+------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ],
      "source": [
        "# Your code here\n",
        "\n",
        "df5 = df4.withColumn(\"Order_lenght\", length(df4[\"OrderID\"]))\n",
        "df5.show()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {},
          "inputWidgets": {},
          "nuid": "17a5b899-0b74-44ae-aadd-0b4605ffc341",
          "showTitle": false,
          "title": ""
        },
        "id": "_l3Wjn_i9LCe"
      },
      "source": [
        "6. Trim any leading or trailing spaces from the 'ItemType' column."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 13,
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {},
          "inputWidgets": {},
          "nuid": "38c4a82a-70d9-48af-8722-17892e0e43af",
          "showTitle": false,
          "title": ""
        },
        "id": "uZ7iPRsw9LCe",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "29370041-14ab-40c8-9024-66026c19d28c"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------------+----------+---------------+------------+-------------+---------+---------+---------+--------+------------+----------+-----------+--------------------+------------+\n",
            "|              Region|   Country|       ItemType|SalesChannel|OrderPriority|  OrderID|UnitsSold|UnitPrice|UnitCost|TotalRevenue| TotalCost|TotalProfit|      Region_Country|Order_lenght|\n",
            "+--------------------+----------+---------------+------------+-------------+---------+---------+---------+--------+------------+----------+-----------+--------------------+------------+\n",
            "|middle east and n...|     LIBYA|      Cosmetics|     Offline|            M|686800706|     8446|    437.2|  263.33|   3692591.2|2224085.18| 1468506.02|middle east and n...|           9|\n",
            "|       north america|    CANADA|     Vegetables|      Online|            M|185941302|     3018|   154.06|   90.93|   464953.08| 274426.74|  190526.34|north america-CANADA|           9|\n",
            "|middle east and n...|     LIBYA|      Baby Food|     Offline|            C|246222341|     1517|   255.28|  159.42|   387259.76| 241840.14|  145419.62|middle east and n...|           9|\n",
            "|                asia|     JAPAN|         Cereal|     Offline|            C|161442649|     3322|    205.7|  117.11|    683335.4| 389039.42|  294295.98|          asia-JAPAN|           9|\n",
            "|  sub-saharan africa|      CHAD|         Fruits|     Offline|            H|645713555|     9845|     9.33|    6.92|    91853.85|   68127.4|   23726.45|sub-saharan afric...|           9|\n",
            "|              europe|   ARMENIA|         Cereal|      Online|            H|683458888|     9528|    205.7|  117.11|   1959909.6|1115824.08|  844085.52|      europe-ARMENIA|           9|\n",
            "|  sub-saharan africa|   ERITREA|         Cereal|      Online|            H|679414975|     2844|    205.7|  117.11|    585010.8| 333060.84|  251949.96|sub-saharan afric...|           9|\n",
            "|              europe|MONTENEGRO|        Clothes|     Offline|            M|208630645|     7299|   109.28|   35.84|   797634.72| 261596.16|  536038.56|   europe-MONTENEGRO|           9|\n",
            "|central america a...|   JAMAICA|     Vegetables|      Online|            H|266467225|     2428|   154.06|   90.93|   374057.68| 220778.04|  153279.64|central america a...|           9|\n",
            "|australia and oce...|      FIJI|     Vegetables|     Offline|            H|118598544|     4800|   154.06|   90.93|    739488.0|  436464.0|   303024.0|australia and oce...|           9|\n",
            "|  sub-saharan africa|      TOGO|        Clothes|      Online|            M|451010930|     3012|   109.28|   35.84|   329151.36| 107950.08|  221201.28|sub-saharan afric...|           9|\n",
            "|              europe|MONTENEGRO|         Snacks|     Offline|            M|220003211|     2694|   152.58|   97.44|   411050.52| 262503.36|  148547.16|   europe-MONTENEGRO|           9|\n",
            "|              europe|    GREECE|      Household|      Online|            C|702186715|     1508|   668.27|  502.54|  1007751.16| 757830.32|  249920.84|       europe-GREECE|           9|\n",
            "|  sub-saharan africa|     SUDAN|      Cosmetics|      Online|            C|544485270|     4146|    437.2|  263.33|   1812631.2|1091766.18|  720865.02|sub-saharan afric...|           9|\n",
            "|                asia|  MALDIVES|         Fruits|     Offline|            L|714135205|     7332|     9.33|    6.92|    68407.56|  50737.44|   17670.12|       asia-MALDIVES|           9|\n",
            "|              europe|MONTENEGRO|        Clothes|     Offline|            H|448685348|     4820|   109.28|   35.84|    526729.6|  172748.8|   353980.8|   europe-MONTENEGRO|           9|\n",
            "|              europe|   ESTONIA|Office Supplies|      Online|            H|405997025|     2397|   651.21|  524.96|  1560950.37|1258329.12|  302621.25|      europe-ESTONIA|           9|\n",
            "|       north america| GREENLAND|      Beverages|      Online|            M|414244067|     2880|    47.45|   31.79|    136656.0|   91555.2|    45100.8|north america-GRE...|           9|\n",
            "|  sub-saharan africa|CAPE VERDE|        Clothes|      Online|            C|821912801|     1117|   109.28|   35.84|   122065.76|  40033.28|   82032.48|sub-saharan afric...|           9|\n",
            "|  sub-saharan africa|   SENEGAL|      Household|     Offline|            L|247802054|     8989|   668.27|  502.54|  6007079.03|4517332.06| 1489746.97|sub-saharan afric...|           9|\n",
            "+--------------------+----------+---------------+------------+-------------+---------+---------+---------+--------+------------+----------+-----------+--------------------+------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ],
      "source": [
        "# Your code here\n",
        "\n",
        "df6 = df5.withColumn(\"ItemType\",trim(df5[\"ItemType\"]))\n",
        "df6.show()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {},
          "inputWidgets": {},
          "nuid": "2ef698db-41a6-4701-9fe5-172ee2b9e1bf",
          "showTitle": false,
          "title": ""
        },
        "id": "FCfxsKVT9LCe"
      },
      "source": [
        "7. Left pad the 'OrderID' with zeros to ensure it's always 10 characters long."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 14,
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {},
          "inputWidgets": {},
          "nuid": "77809e6d-471a-4186-8d55-8fc788239906",
          "showTitle": false,
          "title": ""
        },
        "id": "RsQCL10q9LCe",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "f4d45eb0-07f8-460e-cc9e-1522bc0d468f"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------------+----------+---------------+------------+-------------+----------+---------+---------+--------+------------+----------+-----------+--------------------+------------+\n",
            "|              Region|   Country|       ItemType|SalesChannel|OrderPriority|   OrderID|UnitsSold|UnitPrice|UnitCost|TotalRevenue| TotalCost|TotalProfit|      Region_Country|Order_lenght|\n",
            "+--------------------+----------+---------------+------------+-------------+----------+---------+---------+--------+------------+----------+-----------+--------------------+------------+\n",
            "|middle east and n...|     LIBYA|      Cosmetics|     Offline|            M|0686800706|     8446|    437.2|  263.33|   3692591.2|2224085.18| 1468506.02|middle east and n...|           9|\n",
            "|       north america|    CANADA|     Vegetables|      Online|            M|0185941302|     3018|   154.06|   90.93|   464953.08| 274426.74|  190526.34|north america-CANADA|           9|\n",
            "|middle east and n...|     LIBYA|      Baby Food|     Offline|            C|0246222341|     1517|   255.28|  159.42|   387259.76| 241840.14|  145419.62|middle east and n...|           9|\n",
            "|                asia|     JAPAN|         Cereal|     Offline|            C|0161442649|     3322|    205.7|  117.11|    683335.4| 389039.42|  294295.98|          asia-JAPAN|           9|\n",
            "|  sub-saharan africa|      CHAD|         Fruits|     Offline|            H|0645713555|     9845|     9.33|    6.92|    91853.85|   68127.4|   23726.45|sub-saharan afric...|           9|\n",
            "|              europe|   ARMENIA|         Cereal|      Online|            H|0683458888|     9528|    205.7|  117.11|   1959909.6|1115824.08|  844085.52|      europe-ARMENIA|           9|\n",
            "|  sub-saharan africa|   ERITREA|         Cereal|      Online|            H|0679414975|     2844|    205.7|  117.11|    585010.8| 333060.84|  251949.96|sub-saharan afric...|           9|\n",
            "|              europe|MONTENEGRO|        Clothes|     Offline|            M|0208630645|     7299|   109.28|   35.84|   797634.72| 261596.16|  536038.56|   europe-MONTENEGRO|           9|\n",
            "|central america a...|   JAMAICA|     Vegetables|      Online|            H|0266467225|     2428|   154.06|   90.93|   374057.68| 220778.04|  153279.64|central america a...|           9|\n",
            "|australia and oce...|      FIJI|     Vegetables|     Offline|            H|0118598544|     4800|   154.06|   90.93|    739488.0|  436464.0|   303024.0|australia and oce...|           9|\n",
            "|  sub-saharan africa|      TOGO|        Clothes|      Online|            M|0451010930|     3012|   109.28|   35.84|   329151.36| 107950.08|  221201.28|sub-saharan afric...|           9|\n",
            "|              europe|MONTENEGRO|         Snacks|     Offline|            M|0220003211|     2694|   152.58|   97.44|   411050.52| 262503.36|  148547.16|   europe-MONTENEGRO|           9|\n",
            "|              europe|    GREECE|      Household|      Online|            C|0702186715|     1508|   668.27|  502.54|  1007751.16| 757830.32|  249920.84|       europe-GREECE|           9|\n",
            "|  sub-saharan africa|     SUDAN|      Cosmetics|      Online|            C|0544485270|     4146|    437.2|  263.33|   1812631.2|1091766.18|  720865.02|sub-saharan afric...|           9|\n",
            "|                asia|  MALDIVES|         Fruits|     Offline|            L|0714135205|     7332|     9.33|    6.92|    68407.56|  50737.44|   17670.12|       asia-MALDIVES|           9|\n",
            "|              europe|MONTENEGRO|        Clothes|     Offline|            H|0448685348|     4820|   109.28|   35.84|    526729.6|  172748.8|   353980.8|   europe-MONTENEGRO|           9|\n",
            "|              europe|   ESTONIA|Office Supplies|      Online|            H|0405997025|     2397|   651.21|  524.96|  1560950.37|1258329.12|  302621.25|      europe-ESTONIA|           9|\n",
            "|       north america| GREENLAND|      Beverages|      Online|            M|0414244067|     2880|    47.45|   31.79|    136656.0|   91555.2|    45100.8|north america-GRE...|           9|\n",
            "|  sub-saharan africa|CAPE VERDE|        Clothes|      Online|            C|0821912801|     1117|   109.28|   35.84|   122065.76|  40033.28|   82032.48|sub-saharan afric...|           9|\n",
            "|  sub-saharan africa|   SENEGAL|      Household|     Offline|            L|0247802054|     8989|   668.27|  502.54|  6007079.03|4517332.06| 1489746.97|sub-saharan afric...|           9|\n",
            "+--------------------+----------+---------------+------------+-------------+----------+---------+---------+--------+------------+----------+-----------+--------------------+------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ],
      "source": [
        "# Your code here\n",
        "df7 = df6.withColumn(\"OrderID\", lpad(df6[\"OrderID\"],10,\"0\"))\n",
        "df7.show()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {},
          "inputWidgets": {},
          "nuid": "f00a75d8-f9f8-459e-a35d-16bb4946cc07",
          "showTitle": false,
          "title": ""
        },
        "id": "A9anLDml9LCf"
      },
      "source": [
        "8. Extract the first 3 characters from the 'Country' column."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 15,
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {},
          "inputWidgets": {},
          "nuid": "2880d2d5-a97e-4642-b428-6c7ad30c5553",
          "showTitle": false,
          "title": ""
        },
        "id": "Ea2Qjesb9LCf",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "feb93b6b-df35-431b-a375-968d91e227d0"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------------+----------+---------------+------------+-------------+----------+---------+---------+--------+------------+----------+-----------+--------------------+------------+-------------------+\n",
            "|              Region|   Country|       ItemType|SalesChannel|OrderPriority|   OrderID|UnitsSold|UnitPrice|UnitCost|TotalRevenue| TotalCost|TotalProfit|      Region_Country|Order_lenght|Country_3_Character|\n",
            "+--------------------+----------+---------------+------------+-------------+----------+---------+---------+--------+------------+----------+-----------+--------------------+------------+-------------------+\n",
            "|middle east and n...|     LIBYA|      Cosmetics|     Offline|            M|0686800706|     8446|    437.2|  263.33|   3692591.2|2224085.18| 1468506.02|middle east and n...|           9|                LIB|\n",
            "|       north america|    CANADA|     Vegetables|      Online|            M|0185941302|     3018|   154.06|   90.93|   464953.08| 274426.74|  190526.34|north america-CANADA|           9|                CAN|\n",
            "|middle east and n...|     LIBYA|      Baby Food|     Offline|            C|0246222341|     1517|   255.28|  159.42|   387259.76| 241840.14|  145419.62|middle east and n...|           9|                LIB|\n",
            "|                asia|     JAPAN|         Cereal|     Offline|            C|0161442649|     3322|    205.7|  117.11|    683335.4| 389039.42|  294295.98|          asia-JAPAN|           9|                JAP|\n",
            "|  sub-saharan africa|      CHAD|         Fruits|     Offline|            H|0645713555|     9845|     9.33|    6.92|    91853.85|   68127.4|   23726.45|sub-saharan afric...|           9|                CHA|\n",
            "|              europe|   ARMENIA|         Cereal|      Online|            H|0683458888|     9528|    205.7|  117.11|   1959909.6|1115824.08|  844085.52|      europe-ARMENIA|           9|                ARM|\n",
            "|  sub-saharan africa|   ERITREA|         Cereal|      Online|            H|0679414975|     2844|    205.7|  117.11|    585010.8| 333060.84|  251949.96|sub-saharan afric...|           9|                ERI|\n",
            "|              europe|MONTENEGRO|        Clothes|     Offline|            M|0208630645|     7299|   109.28|   35.84|   797634.72| 261596.16|  536038.56|   europe-MONTENEGRO|           9|                MON|\n",
            "|central america a...|   JAMAICA|     Vegetables|      Online|            H|0266467225|     2428|   154.06|   90.93|   374057.68| 220778.04|  153279.64|central america a...|           9|                JAM|\n",
            "|australia and oce...|      FIJI|     Vegetables|     Offline|            H|0118598544|     4800|   154.06|   90.93|    739488.0|  436464.0|   303024.0|australia and oce...|           9|                FIJ|\n",
            "|  sub-saharan africa|      TOGO|        Clothes|      Online|            M|0451010930|     3012|   109.28|   35.84|   329151.36| 107950.08|  221201.28|sub-saharan afric...|           9|                TOG|\n",
            "|              europe|MONTENEGRO|         Snacks|     Offline|            M|0220003211|     2694|   152.58|   97.44|   411050.52| 262503.36|  148547.16|   europe-MONTENEGRO|           9|                MON|\n",
            "|              europe|    GREECE|      Household|      Online|            C|0702186715|     1508|   668.27|  502.54|  1007751.16| 757830.32|  249920.84|       europe-GREECE|           9|                GRE|\n",
            "|  sub-saharan africa|     SUDAN|      Cosmetics|      Online|            C|0544485270|     4146|    437.2|  263.33|   1812631.2|1091766.18|  720865.02|sub-saharan afric...|           9|                SUD|\n",
            "|                asia|  MALDIVES|         Fruits|     Offline|            L|0714135205|     7332|     9.33|    6.92|    68407.56|  50737.44|   17670.12|       asia-MALDIVES|           9|                MAL|\n",
            "|              europe|MONTENEGRO|        Clothes|     Offline|            H|0448685348|     4820|   109.28|   35.84|    526729.6|  172748.8|   353980.8|   europe-MONTENEGRO|           9|                MON|\n",
            "|              europe|   ESTONIA|Office Supplies|      Online|            H|0405997025|     2397|   651.21|  524.96|  1560950.37|1258329.12|  302621.25|      europe-ESTONIA|           9|                EST|\n",
            "|       north america| GREENLAND|      Beverages|      Online|            M|0414244067|     2880|    47.45|   31.79|    136656.0|   91555.2|    45100.8|north america-GRE...|           9|                GRE|\n",
            "|  sub-saharan africa|CAPE VERDE|        Clothes|      Online|            C|0821912801|     1117|   109.28|   35.84|   122065.76|  40033.28|   82032.48|sub-saharan afric...|           9|                CAP|\n",
            "|  sub-saharan africa|   SENEGAL|      Household|     Offline|            L|0247802054|     8989|   668.27|  502.54|  6007079.03|4517332.06| 1489746.97|sub-saharan afric...|           9|                SEN|\n",
            "+--------------------+----------+---------------+------------+-------------+----------+---------+---------+--------+------------+----------+-----------+--------------------+------------+-------------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ],
      "source": [
        "# Your code here\n",
        "\n",
        "df8 = df7.withColumn(\"Country_3_Character\",substring(df7[\"Country\"],1,3))\n",
        "df8.show()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {},
          "inputWidgets": {},
          "nuid": "5224aba1-6c0c-46af-ba08-73a542fa6215",
          "showTitle": false,
          "title": ""
        },
        "id": "1hFullYz9LCf"
      },
      "source": [
        "9. Concatenate 'ItemType' and 'OrderPriority' with a comma and space between them."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 16,
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {},
          "inputWidgets": {},
          "nuid": "70de4151-8ab4-4443-ad6e-662b0bc58943",
          "showTitle": false,
          "title": ""
        },
        "id": "WSbee9289LCf",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "2472d1f7-e91e-47e2-a916-117ada8f735c"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------------+----------+---------------+------------+-------------+----------+---------+---------+--------+------------+----------+-----------+--------------------+------------+-------------------+-------------------+\n",
            "|              Region|   Country|       ItemType|SalesChannel|OrderPriority|   OrderID|UnitsSold|UnitPrice|UnitCost|TotalRevenue| TotalCost|TotalProfit|      Region_Country|Order_lenght|Country_3_Character|  ItemType_Priprity|\n",
            "+--------------------+----------+---------------+------------+-------------+----------+---------+---------+--------+------------+----------+-----------+--------------------+------------+-------------------+-------------------+\n",
            "|middle east and n...|     LIBYA|      Cosmetics|     Offline|            M|0686800706|     8446|    437.2|  263.33|   3692591.2|2224085.18| 1468506.02|middle east and n...|           9|                LIB|      Cosmetics , M|\n",
            "|       north america|    CANADA|     Vegetables|      Online|            M|0185941302|     3018|   154.06|   90.93|   464953.08| 274426.74|  190526.34|north america-CANADA|           9|                CAN|     Vegetables , M|\n",
            "|middle east and n...|     LIBYA|      Baby Food|     Offline|            C|0246222341|     1517|   255.28|  159.42|   387259.76| 241840.14|  145419.62|middle east and n...|           9|                LIB|      Baby Food , C|\n",
            "|                asia|     JAPAN|         Cereal|     Offline|            C|0161442649|     3322|    205.7|  117.11|    683335.4| 389039.42|  294295.98|          asia-JAPAN|           9|                JAP|         Cereal , C|\n",
            "|  sub-saharan africa|      CHAD|         Fruits|     Offline|            H|0645713555|     9845|     9.33|    6.92|    91853.85|   68127.4|   23726.45|sub-saharan afric...|           9|                CHA|         Fruits , H|\n",
            "|              europe|   ARMENIA|         Cereal|      Online|            H|0683458888|     9528|    205.7|  117.11|   1959909.6|1115824.08|  844085.52|      europe-ARMENIA|           9|                ARM|         Cereal , H|\n",
            "|  sub-saharan africa|   ERITREA|         Cereal|      Online|            H|0679414975|     2844|    205.7|  117.11|    585010.8| 333060.84|  251949.96|sub-saharan afric...|           9|                ERI|         Cereal , H|\n",
            "|              europe|MONTENEGRO|        Clothes|     Offline|            M|0208630645|     7299|   109.28|   35.84|   797634.72| 261596.16|  536038.56|   europe-MONTENEGRO|           9|                MON|        Clothes , M|\n",
            "|central america a...|   JAMAICA|     Vegetables|      Online|            H|0266467225|     2428|   154.06|   90.93|   374057.68| 220778.04|  153279.64|central america a...|           9|                JAM|     Vegetables , H|\n",
            "|australia and oce...|      FIJI|     Vegetables|     Offline|            H|0118598544|     4800|   154.06|   90.93|    739488.0|  436464.0|   303024.0|australia and oce...|           9|                FIJ|     Vegetables , H|\n",
            "|  sub-saharan africa|      TOGO|        Clothes|      Online|            M|0451010930|     3012|   109.28|   35.84|   329151.36| 107950.08|  221201.28|sub-saharan afric...|           9|                TOG|        Clothes , M|\n",
            "|              europe|MONTENEGRO|         Snacks|     Offline|            M|0220003211|     2694|   152.58|   97.44|   411050.52| 262503.36|  148547.16|   europe-MONTENEGRO|           9|                MON|         Snacks , M|\n",
            "|              europe|    GREECE|      Household|      Online|            C|0702186715|     1508|   668.27|  502.54|  1007751.16| 757830.32|  249920.84|       europe-GREECE|           9|                GRE|      Household , C|\n",
            "|  sub-saharan africa|     SUDAN|      Cosmetics|      Online|            C|0544485270|     4146|    437.2|  263.33|   1812631.2|1091766.18|  720865.02|sub-saharan afric...|           9|                SUD|      Cosmetics , C|\n",
            "|                asia|  MALDIVES|         Fruits|     Offline|            L|0714135205|     7332|     9.33|    6.92|    68407.56|  50737.44|   17670.12|       asia-MALDIVES|           9|                MAL|         Fruits , L|\n",
            "|              europe|MONTENEGRO|        Clothes|     Offline|            H|0448685348|     4820|   109.28|   35.84|    526729.6|  172748.8|   353980.8|   europe-MONTENEGRO|           9|                MON|        Clothes , H|\n",
            "|              europe|   ESTONIA|Office Supplies|      Online|            H|0405997025|     2397|   651.21|  524.96|  1560950.37|1258329.12|  302621.25|      europe-ESTONIA|           9|                EST|Office Supplies , H|\n",
            "|       north america| GREENLAND|      Beverages|      Online|            M|0414244067|     2880|    47.45|   31.79|    136656.0|   91555.2|    45100.8|north america-GRE...|           9|                GRE|      Beverages , M|\n",
            "|  sub-saharan africa|CAPE VERDE|        Clothes|      Online|            C|0821912801|     1117|   109.28|   35.84|   122065.76|  40033.28|   82032.48|sub-saharan afric...|           9|                CAP|        Clothes , C|\n",
            "|  sub-saharan africa|   SENEGAL|      Household|     Offline|            L|0247802054|     8989|   668.27|  502.54|  6007079.03|4517332.06| 1489746.97|sub-saharan afric...|           9|                SEN|      Household , L|\n",
            "+--------------------+----------+---------------+------------+-------------+----------+---------+---------+--------+------------+----------+-----------+--------------------+------------+-------------------+-------------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ],
      "source": [
        "# Your code here\n",
        "df9 =df8.withColumn(\"ItemType_Priprity\", concat(df8[\"ItemType\"],lit(\" , \"),df8[\"OrderPriority\"]))\n",
        "df9.show()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {},
          "inputWidgets": {},
          "nuid": "0607c7ab-7232-4593-b335-352e7c8356b9",
          "showTitle": false,
          "title": ""
        },
        "id": "4Izbm8ID9LCf"
      },
      "source": [
        "10. Replace any occurrence of 'and' with '&' in the 'Region' column."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 17,
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {},
          "inputWidgets": {},
          "nuid": "4e86081c-9997-486b-9065-d9f23fdb3fe7",
          "showTitle": false,
          "title": ""
        },
        "id": "9GueNTmb9LCf",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "a0c3ec4a-9852-4d35-b16a-102c20df2dd5"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------------+----------+---------------+------------+-------------+----------+---------+---------+--------+------------+----------+-----------+--------------------+------------+-------------------+-------------------+-------------------------+\n",
            "|              Region|   Country|       ItemType|SalesChannel|OrderPriority|   OrderID|UnitsSold|UnitPrice|UnitCost|TotalRevenue| TotalCost|TotalProfit|      Region_Country|Order_lenght|Country_3_Character|  ItemType_Priprity|Region_Replace_and_with_&|\n",
            "+--------------------+----------+---------------+------------+-------------+----------+---------+---------+--------+------------+----------+-----------+--------------------+------------+-------------------+-------------------+-------------------------+\n",
            "|middle east and n...|     LIBYA|      Cosmetics|     Offline|            M|0686800706|     8446|    437.2|  263.33|   3692591.2|2224085.18| 1468506.02|middle east and n...|           9|                LIB|      Cosmetics , M|     middle east & nor...|\n",
            "|       north america|    CANADA|     Vegetables|      Online|            M|0185941302|     3018|   154.06|   90.93|   464953.08| 274426.74|  190526.34|north america-CANADA|           9|                CAN|     Vegetables , M|            north america|\n",
            "|middle east and n...|     LIBYA|      Baby Food|     Offline|            C|0246222341|     1517|   255.28|  159.42|   387259.76| 241840.14|  145419.62|middle east and n...|           9|                LIB|      Baby Food , C|     middle east & nor...|\n",
            "|                asia|     JAPAN|         Cereal|     Offline|            C|0161442649|     3322|    205.7|  117.11|    683335.4| 389039.42|  294295.98|          asia-JAPAN|           9|                JAP|         Cereal , C|                     asia|\n",
            "|  sub-saharan africa|      CHAD|         Fruits|     Offline|            H|0645713555|     9845|     9.33|    6.92|    91853.85|   68127.4|   23726.45|sub-saharan afric...|           9|                CHA|         Fruits , H|       sub-saharan africa|\n",
            "|              europe|   ARMENIA|         Cereal|      Online|            H|0683458888|     9528|    205.7|  117.11|   1959909.6|1115824.08|  844085.52|      europe-ARMENIA|           9|                ARM|         Cereal , H|                   europe|\n",
            "|  sub-saharan africa|   ERITREA|         Cereal|      Online|            H|0679414975|     2844|    205.7|  117.11|    585010.8| 333060.84|  251949.96|sub-saharan afric...|           9|                ERI|         Cereal , H|       sub-saharan africa|\n",
            "|              europe|MONTENEGRO|        Clothes|     Offline|            M|0208630645|     7299|   109.28|   35.84|   797634.72| 261596.16|  536038.56|   europe-MONTENEGRO|           9|                MON|        Clothes , M|                   europe|\n",
            "|central america a...|   JAMAICA|     Vegetables|      Online|            H|0266467225|     2428|   154.06|   90.93|   374057.68| 220778.04|  153279.64|central america a...|           9|                JAM|     Vegetables , H|     central america &...|\n",
            "|australia and oce...|      FIJI|     Vegetables|     Offline|            H|0118598544|     4800|   154.06|   90.93|    739488.0|  436464.0|   303024.0|australia and oce...|           9|                FIJ|     Vegetables , H|      australia & oceania|\n",
            "|  sub-saharan africa|      TOGO|        Clothes|      Online|            M|0451010930|     3012|   109.28|   35.84|   329151.36| 107950.08|  221201.28|sub-saharan afric...|           9|                TOG|        Clothes , M|       sub-saharan africa|\n",
            "|              europe|MONTENEGRO|         Snacks|     Offline|            M|0220003211|     2694|   152.58|   97.44|   411050.52| 262503.36|  148547.16|   europe-MONTENEGRO|           9|                MON|         Snacks , M|                   europe|\n",
            "|              europe|    GREECE|      Household|      Online|            C|0702186715|     1508|   668.27|  502.54|  1007751.16| 757830.32|  249920.84|       europe-GREECE|           9|                GRE|      Household , C|                   europe|\n",
            "|  sub-saharan africa|     SUDAN|      Cosmetics|      Online|            C|0544485270|     4146|    437.2|  263.33|   1812631.2|1091766.18|  720865.02|sub-saharan afric...|           9|                SUD|      Cosmetics , C|       sub-saharan africa|\n",
            "|                asia|  MALDIVES|         Fruits|     Offline|            L|0714135205|     7332|     9.33|    6.92|    68407.56|  50737.44|   17670.12|       asia-MALDIVES|           9|                MAL|         Fruits , L|                     asia|\n",
            "|              europe|MONTENEGRO|        Clothes|     Offline|            H|0448685348|     4820|   109.28|   35.84|    526729.6|  172748.8|   353980.8|   europe-MONTENEGRO|           9|                MON|        Clothes , H|                   europe|\n",
            "|              europe|   ESTONIA|Office Supplies|      Online|            H|0405997025|     2397|   651.21|  524.96|  1560950.37|1258329.12|  302621.25|      europe-ESTONIA|           9|                EST|Office Supplies , H|                   europe|\n",
            "|       north america| GREENLAND|      Beverages|      Online|            M|0414244067|     2880|    47.45|   31.79|    136656.0|   91555.2|    45100.8|north america-GRE...|           9|                GRE|      Beverages , M|            north america|\n",
            "|  sub-saharan africa|CAPE VERDE|        Clothes|      Online|            C|0821912801|     1117|   109.28|   35.84|   122065.76|  40033.28|   82032.48|sub-saharan afric...|           9|                CAP|        Clothes , C|       sub-saharan africa|\n",
            "|  sub-saharan africa|   SENEGAL|      Household|     Offline|            L|0247802054|     8989|   668.27|  502.54|  6007079.03|4517332.06| 1489746.97|sub-saharan afric...|           9|                SEN|      Household , L|       sub-saharan africa|\n",
            "+--------------------+----------+---------------+------------+-------------+----------+---------+---------+--------+------------+----------+-----------+--------------------+------------+-------------------+-------------------+-------------------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ],
      "source": [
        "# Your code here\n",
        "\n",
        "df10 = df9.withColumn(\"Region_Replace_and_with_&\", regexp_replace(df9[\"Region\"],'and','&'))\n",
        "df10.show()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {},
          "inputWidgets": {},
          "nuid": "f03e47c1-33fa-4f68-871f-6d1b4b471889",
          "showTitle": false,
          "title": ""
        },
        "id": "JjefhmxD9LCf"
      },
      "source": [
        "11. Create a new column 'OrderCategory' that shows 'High' for High priority, 'Medium' for Medium, and 'Low' for Low priority orders."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 18,
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {},
          "inputWidgets": {},
          "nuid": "c1ec3032-a9ab-4193-b523-5726419aae27",
          "showTitle": false,
          "title": ""
        },
        "id": "V9n5Fafs9LCf",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "73c6d638-8e99-420e-ce73-c19d58b6693f"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------------+----------+---------------+------------+-------------+----------+---------+---------+--------+------------+----------+-----------+--------------------+------------+-------------------+-------------------+-------------------------+------------------+\n",
            "|              Region|   Country|       ItemType|SalesChannel|OrderPriority|   OrderID|UnitsSold|UnitPrice|UnitCost|TotalRevenue| TotalCost|TotalProfit|      Region_Country|Order_lenght|Country_3_Character|  ItemType_Priprity|Region_Replace_and_with_&|OrderPriority_Full|\n",
            "+--------------------+----------+---------------+------------+-------------+----------+---------+---------+--------+------------+----------+-----------+--------------------+------------+-------------------+-------------------+-------------------------+------------------+\n",
            "|middle east and n...|     LIBYA|      Cosmetics|     Offline|            M|0686800706|     8446|    437.2|  263.33|   3692591.2|2224085.18| 1468506.02|middle east and n...|           9|                LIB|      Cosmetics , M|     middle east & nor...|            Medium|\n",
            "|       north america|    CANADA|     Vegetables|      Online|            M|0185941302|     3018|   154.06|   90.93|   464953.08| 274426.74|  190526.34|north america-CANADA|           9|                CAN|     Vegetables , M|            north america|            Medium|\n",
            "|middle east and n...|     LIBYA|      Baby Food|     Offline|            C|0246222341|     1517|   255.28|  159.42|   387259.76| 241840.14|  145419.62|middle east and n...|           9|                LIB|      Baby Food , C|     middle east & nor...|              NULL|\n",
            "|                asia|     JAPAN|         Cereal|     Offline|            C|0161442649|     3322|    205.7|  117.11|    683335.4| 389039.42|  294295.98|          asia-JAPAN|           9|                JAP|         Cereal , C|                     asia|              NULL|\n",
            "|  sub-saharan africa|      CHAD|         Fruits|     Offline|            H|0645713555|     9845|     9.33|    6.92|    91853.85|   68127.4|   23726.45|sub-saharan afric...|           9|                CHA|         Fruits , H|       sub-saharan africa|              High|\n",
            "|              europe|   ARMENIA|         Cereal|      Online|            H|0683458888|     9528|    205.7|  117.11|   1959909.6|1115824.08|  844085.52|      europe-ARMENIA|           9|                ARM|         Cereal , H|                   europe|              High|\n",
            "|  sub-saharan africa|   ERITREA|         Cereal|      Online|            H|0679414975|     2844|    205.7|  117.11|    585010.8| 333060.84|  251949.96|sub-saharan afric...|           9|                ERI|         Cereal , H|       sub-saharan africa|              High|\n",
            "|              europe|MONTENEGRO|        Clothes|     Offline|            M|0208630645|     7299|   109.28|   35.84|   797634.72| 261596.16|  536038.56|   europe-MONTENEGRO|           9|                MON|        Clothes , M|                   europe|            Medium|\n",
            "|central america a...|   JAMAICA|     Vegetables|      Online|            H|0266467225|     2428|   154.06|   90.93|   374057.68| 220778.04|  153279.64|central america a...|           9|                JAM|     Vegetables , H|     central america &...|              High|\n",
            "|australia and oce...|      FIJI|     Vegetables|     Offline|            H|0118598544|     4800|   154.06|   90.93|    739488.0|  436464.0|   303024.0|australia and oce...|           9|                FIJ|     Vegetables , H|      australia & oceania|              High|\n",
            "|  sub-saharan africa|      TOGO|        Clothes|      Online|            M|0451010930|     3012|   109.28|   35.84|   329151.36| 107950.08|  221201.28|sub-saharan afric...|           9|                TOG|        Clothes , M|       sub-saharan africa|            Medium|\n",
            "|              europe|MONTENEGRO|         Snacks|     Offline|            M|0220003211|     2694|   152.58|   97.44|   411050.52| 262503.36|  148547.16|   europe-MONTENEGRO|           9|                MON|         Snacks , M|                   europe|            Medium|\n",
            "|              europe|    GREECE|      Household|      Online|            C|0702186715|     1508|   668.27|  502.54|  1007751.16| 757830.32|  249920.84|       europe-GREECE|           9|                GRE|      Household , C|                   europe|              NULL|\n",
            "|  sub-saharan africa|     SUDAN|      Cosmetics|      Online|            C|0544485270|     4146|    437.2|  263.33|   1812631.2|1091766.18|  720865.02|sub-saharan afric...|           9|                SUD|      Cosmetics , C|       sub-saharan africa|              NULL|\n",
            "|                asia|  MALDIVES|         Fruits|     Offline|            L|0714135205|     7332|     9.33|    6.92|    68407.56|  50737.44|   17670.12|       asia-MALDIVES|           9|                MAL|         Fruits , L|                     asia|               Low|\n",
            "|              europe|MONTENEGRO|        Clothes|     Offline|            H|0448685348|     4820|   109.28|   35.84|    526729.6|  172748.8|   353980.8|   europe-MONTENEGRO|           9|                MON|        Clothes , H|                   europe|              High|\n",
            "|              europe|   ESTONIA|Office Supplies|      Online|            H|0405997025|     2397|   651.21|  524.96|  1560950.37|1258329.12|  302621.25|      europe-ESTONIA|           9|                EST|Office Supplies , H|                   europe|              High|\n",
            "|       north america| GREENLAND|      Beverages|      Online|            M|0414244067|     2880|    47.45|   31.79|    136656.0|   91555.2|    45100.8|north america-GRE...|           9|                GRE|      Beverages , M|            north america|            Medium|\n",
            "|  sub-saharan africa|CAPE VERDE|        Clothes|      Online|            C|0821912801|     1117|   109.28|   35.84|   122065.76|  40033.28|   82032.48|sub-saharan afric...|           9|                CAP|        Clothes , C|       sub-saharan africa|              NULL|\n",
            "|  sub-saharan africa|   SENEGAL|      Household|     Offline|            L|0247802054|     8989|   668.27|  502.54|  6007079.03|4517332.06| 1489746.97|sub-saharan afric...|           9|                SEN|      Household , L|       sub-saharan africa|               Low|\n",
            "+--------------------+----------+---------------+------------+-------------+----------+---------+---------+--------+------------+----------+-----------+--------------------+------------+-------------------+-------------------+-------------------------+------------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ],
      "source": [
        "# Your code here\n",
        "\n",
        "df11 = df10.withColumn(\"OrderPriority_Full\", when(df10[\"OrderPriority\"] ==\"H\",\"High\").when(df10[\"OrderPriority\"] ==\"M\",\"Medium\").when(df10[\"OrderPriority\"] ==\"L\",\"Low\"))\n",
        "df11.show()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {},
          "inputWidgets": {},
          "nuid": "4eb7d460-3552-4b43-b824-90bf3c7f56b7",
          "showTitle": false,
          "title": ""
        },
        "id": "J9DUoyyc9LCf"
      },
      "source": [
        "12. Right pad the 'SalesChannel' column with '-' to make it always 10 characters long."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 19,
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {},
          "inputWidgets": {},
          "nuid": "9e9269bb-5df5-4bda-ae7a-231491f8aa0f",
          "showTitle": false,
          "title": ""
        },
        "id": "g8P1_08-9LCf",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "ca8dd69a-0db9-40cc-a508-a1978f6d6db9"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------------+----------+---------------+------------+-------------+-----------+---------+---------+--------+------------+----------+-----------+--------------------+------------+-------------------+-------------------+-------------------------+------------------+\n",
            "|              Region|   Country|       ItemType|SalesChannel|OrderPriority|    OrderID|UnitsSold|UnitPrice|UnitCost|TotalRevenue| TotalCost|TotalProfit|      Region_Country|Order_lenght|Country_3_Character|  ItemType_Priprity|Region_Replace_and_with_&|OrderPriority_Full|\n",
            "+--------------------+----------+---------------+------------+-------------+-----------+---------+---------+--------+------------+----------+-----------+--------------------+------------+-------------------+-------------------+-------------------------+------------------+\n",
            "|middle east and n...|     LIBYA|      Cosmetics|     Offline|            M|0686800706-|     8446|    437.2|  263.33|   3692591.2|2224085.18| 1468506.02|middle east and n...|           9|                LIB|      Cosmetics , M|     middle east & nor...|            Medium|\n",
            "|       north america|    CANADA|     Vegetables|      Online|            M|0185941302-|     3018|   154.06|   90.93|   464953.08| 274426.74|  190526.34|north america-CANADA|           9|                CAN|     Vegetables , M|            north america|            Medium|\n",
            "|middle east and n...|     LIBYA|      Baby Food|     Offline|            C|0246222341-|     1517|   255.28|  159.42|   387259.76| 241840.14|  145419.62|middle east and n...|           9|                LIB|      Baby Food , C|     middle east & nor...|              NULL|\n",
            "|                asia|     JAPAN|         Cereal|     Offline|            C|0161442649-|     3322|    205.7|  117.11|    683335.4| 389039.42|  294295.98|          asia-JAPAN|           9|                JAP|         Cereal , C|                     asia|              NULL|\n",
            "|  sub-saharan africa|      CHAD|         Fruits|     Offline|            H|0645713555-|     9845|     9.33|    6.92|    91853.85|   68127.4|   23726.45|sub-saharan afric...|           9|                CHA|         Fruits , H|       sub-saharan africa|              High|\n",
            "|              europe|   ARMENIA|         Cereal|      Online|            H|0683458888-|     9528|    205.7|  117.11|   1959909.6|1115824.08|  844085.52|      europe-ARMENIA|           9|                ARM|         Cereal , H|                   europe|              High|\n",
            "|  sub-saharan africa|   ERITREA|         Cereal|      Online|            H|0679414975-|     2844|    205.7|  117.11|    585010.8| 333060.84|  251949.96|sub-saharan afric...|           9|                ERI|         Cereal , H|       sub-saharan africa|              High|\n",
            "|              europe|MONTENEGRO|        Clothes|     Offline|            M|0208630645-|     7299|   109.28|   35.84|   797634.72| 261596.16|  536038.56|   europe-MONTENEGRO|           9|                MON|        Clothes , M|                   europe|            Medium|\n",
            "|central america a...|   JAMAICA|     Vegetables|      Online|            H|0266467225-|     2428|   154.06|   90.93|   374057.68| 220778.04|  153279.64|central america a...|           9|                JAM|     Vegetables , H|     central america &...|              High|\n",
            "|australia and oce...|      FIJI|     Vegetables|     Offline|            H|0118598544-|     4800|   154.06|   90.93|    739488.0|  436464.0|   303024.0|australia and oce...|           9|                FIJ|     Vegetables , H|      australia & oceania|              High|\n",
            "|  sub-saharan africa|      TOGO|        Clothes|      Online|            M|0451010930-|     3012|   109.28|   35.84|   329151.36| 107950.08|  221201.28|sub-saharan afric...|           9|                TOG|        Clothes , M|       sub-saharan africa|            Medium|\n",
            "|              europe|MONTENEGRO|         Snacks|     Offline|            M|0220003211-|     2694|   152.58|   97.44|   411050.52| 262503.36|  148547.16|   europe-MONTENEGRO|           9|                MON|         Snacks , M|                   europe|            Medium|\n",
            "|              europe|    GREECE|      Household|      Online|            C|0702186715-|     1508|   668.27|  502.54|  1007751.16| 757830.32|  249920.84|       europe-GREECE|           9|                GRE|      Household , C|                   europe|              NULL|\n",
            "|  sub-saharan africa|     SUDAN|      Cosmetics|      Online|            C|0544485270-|     4146|    437.2|  263.33|   1812631.2|1091766.18|  720865.02|sub-saharan afric...|           9|                SUD|      Cosmetics , C|       sub-saharan africa|              NULL|\n",
            "|                asia|  MALDIVES|         Fruits|     Offline|            L|0714135205-|     7332|     9.33|    6.92|    68407.56|  50737.44|   17670.12|       asia-MALDIVES|           9|                MAL|         Fruits , L|                     asia|               Low|\n",
            "|              europe|MONTENEGRO|        Clothes|     Offline|            H|0448685348-|     4820|   109.28|   35.84|    526729.6|  172748.8|   353980.8|   europe-MONTENEGRO|           9|                MON|        Clothes , H|                   europe|              High|\n",
            "|              europe|   ESTONIA|Office Supplies|      Online|            H|0405997025-|     2397|   651.21|  524.96|  1560950.37|1258329.12|  302621.25|      europe-ESTONIA|           9|                EST|Office Supplies , H|                   europe|              High|\n",
            "|       north america| GREENLAND|      Beverages|      Online|            M|0414244067-|     2880|    47.45|   31.79|    136656.0|   91555.2|    45100.8|north america-GRE...|           9|                GRE|      Beverages , M|            north america|            Medium|\n",
            "|  sub-saharan africa|CAPE VERDE|        Clothes|      Online|            C|0821912801-|     1117|   109.28|   35.84|   122065.76|  40033.28|   82032.48|sub-saharan afric...|           9|                CAP|        Clothes , C|       sub-saharan africa|              NULL|\n",
            "|  sub-saharan africa|   SENEGAL|      Household|     Offline|            L|0247802054-|     8989|   668.27|  502.54|  6007079.03|4517332.06| 1489746.97|sub-saharan afric...|           9|                SEN|      Household , L|       sub-saharan africa|               Low|\n",
            "+--------------------+----------+---------------+------------+-------------+-----------+---------+---------+--------+------------+----------+-----------+--------------------+------------+-------------------+-------------------+-------------------------+------------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ],
      "source": [
        "# Your code here\n",
        "\n",
        "df12 = df11.withColumn(\"OrderID\",rpad(df11[\"OrderID\"],11,'-'))  # already 10 char to made it 11\n",
        "df12.show()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {},
          "inputWidgets": {},
          "nuid": "efea7cb4-88dc-4da4-85b6-c961135c07c7",
          "showTitle": false,
          "title": ""
        },
        "id": "qDAX2zse9LCf"
      },
      "source": [
        "13. Create a new column that shows the length difference between 'Region' and 'Country' names."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 20,
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {},
          "inputWidgets": {},
          "nuid": "aa436d9b-0273-4d00-9c34-e37e4ae9732f",
          "showTitle": false,
          "title": ""
        },
        "id": "INYuhlLi9LCf",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "ed86278d-9727-44c2-9f63-0275f740b972"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------------+----------+---------------+------------+-------------+-----------+---------+---------+--------+------------+----------+-----------+--------------------+------------+-------------------+-------------------+-------------------------+------------------+-----------------+\n",
            "|              Region|   Country|       ItemType|SalesChannel|OrderPriority|    OrderID|UnitsSold|UnitPrice|UnitCost|TotalRevenue| TotalCost|TotalProfit|      Region_Country|Order_lenght|Country_3_Character|  ItemType_Priprity|Region_Replace_and_with_&|OrderPriority_Full|Length_Difference|\n",
            "+--------------------+----------+---------------+------------+-------------+-----------+---------+---------+--------+------------+----------+-----------+--------------------+------------+-------------------+-------------------+-------------------------+------------------+-----------------+\n",
            "|middle east and n...|     LIBYA|      Cosmetics|     Offline|            M|0686800706-|     8446|    437.2|  263.33|   3692591.2|2224085.18| 1468506.02|middle east and n...|           9|                LIB|      Cosmetics , M|     middle east & nor...|            Medium|               23|\n",
            "|       north america|    CANADA|     Vegetables|      Online|            M|0185941302-|     3018|   154.06|   90.93|   464953.08| 274426.74|  190526.34|north america-CANADA|           9|                CAN|     Vegetables , M|            north america|            Medium|                7|\n",
            "|middle east and n...|     LIBYA|      Baby Food|     Offline|            C|0246222341-|     1517|   255.28|  159.42|   387259.76| 241840.14|  145419.62|middle east and n...|           9|                LIB|      Baby Food , C|     middle east & nor...|              NULL|               23|\n",
            "|                asia|     JAPAN|         Cereal|     Offline|            C|0161442649-|     3322|    205.7|  117.11|    683335.4| 389039.42|  294295.98|          asia-JAPAN|           9|                JAP|         Cereal , C|                     asia|              NULL|               -1|\n",
            "|  sub-saharan africa|      CHAD|         Fruits|     Offline|            H|0645713555-|     9845|     9.33|    6.92|    91853.85|   68127.4|   23726.45|sub-saharan afric...|           9|                CHA|         Fruits , H|       sub-saharan africa|              High|               14|\n",
            "|              europe|   ARMENIA|         Cereal|      Online|            H|0683458888-|     9528|    205.7|  117.11|   1959909.6|1115824.08|  844085.52|      europe-ARMENIA|           9|                ARM|         Cereal , H|                   europe|              High|               -1|\n",
            "|  sub-saharan africa|   ERITREA|         Cereal|      Online|            H|0679414975-|     2844|    205.7|  117.11|    585010.8| 333060.84|  251949.96|sub-saharan afric...|           9|                ERI|         Cereal , H|       sub-saharan africa|              High|               11|\n",
            "|              europe|MONTENEGRO|        Clothes|     Offline|            M|0208630645-|     7299|   109.28|   35.84|   797634.72| 261596.16|  536038.56|   europe-MONTENEGRO|           9|                MON|        Clothes , M|                   europe|            Medium|               -4|\n",
            "|central america a...|   JAMAICA|     Vegetables|      Online|            H|0266467225-|     2428|   154.06|   90.93|   374057.68| 220778.04|  153279.64|central america a...|           9|                JAM|     Vegetables , H|     central america &...|              High|               26|\n",
            "|australia and oce...|      FIJI|     Vegetables|     Offline|            H|0118598544-|     4800|   154.06|   90.93|    739488.0|  436464.0|   303024.0|australia and oce...|           9|                FIJ|     Vegetables , H|      australia & oceania|              High|               17|\n",
            "|  sub-saharan africa|      TOGO|        Clothes|      Online|            M|0451010930-|     3012|   109.28|   35.84|   329151.36| 107950.08|  221201.28|sub-saharan afric...|           9|                TOG|        Clothes , M|       sub-saharan africa|            Medium|               14|\n",
            "|              europe|MONTENEGRO|         Snacks|     Offline|            M|0220003211-|     2694|   152.58|   97.44|   411050.52| 262503.36|  148547.16|   europe-MONTENEGRO|           9|                MON|         Snacks , M|                   europe|            Medium|               -4|\n",
            "|              europe|    GREECE|      Household|      Online|            C|0702186715-|     1508|   668.27|  502.54|  1007751.16| 757830.32|  249920.84|       europe-GREECE|           9|                GRE|      Household , C|                   europe|              NULL|                0|\n",
            "|  sub-saharan africa|     SUDAN|      Cosmetics|      Online|            C|0544485270-|     4146|    437.2|  263.33|   1812631.2|1091766.18|  720865.02|sub-saharan afric...|           9|                SUD|      Cosmetics , C|       sub-saharan africa|              NULL|               13|\n",
            "|                asia|  MALDIVES|         Fruits|     Offline|            L|0714135205-|     7332|     9.33|    6.92|    68407.56|  50737.44|   17670.12|       asia-MALDIVES|           9|                MAL|         Fruits , L|                     asia|               Low|               -4|\n",
            "|              europe|MONTENEGRO|        Clothes|     Offline|            H|0448685348-|     4820|   109.28|   35.84|    526729.6|  172748.8|   353980.8|   europe-MONTENEGRO|           9|                MON|        Clothes , H|                   europe|              High|               -4|\n",
            "|              europe|   ESTONIA|Office Supplies|      Online|            H|0405997025-|     2397|   651.21|  524.96|  1560950.37|1258329.12|  302621.25|      europe-ESTONIA|           9|                EST|Office Supplies , H|                   europe|              High|               -1|\n",
            "|       north america| GREENLAND|      Beverages|      Online|            M|0414244067-|     2880|    47.45|   31.79|    136656.0|   91555.2|    45100.8|north america-GRE...|           9|                GRE|      Beverages , M|            north america|            Medium|                4|\n",
            "|  sub-saharan africa|CAPE VERDE|        Clothes|      Online|            C|0821912801-|     1117|   109.28|   35.84|   122065.76|  40033.28|   82032.48|sub-saharan afric...|           9|                CAP|        Clothes , C|       sub-saharan africa|              NULL|                8|\n",
            "|  sub-saharan africa|   SENEGAL|      Household|     Offline|            L|0247802054-|     8989|   668.27|  502.54|  6007079.03|4517332.06| 1489746.97|sub-saharan afric...|           9|                SEN|      Household , L|       sub-saharan africa|               Low|               11|\n",
            "+--------------------+----------+---------------+------------+-------------+-----------+---------+---------+--------+------------+----------+-----------+--------------------+------------+-------------------+-------------------+-------------------------+------------------+-----------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ],
      "source": [
        "# Your code here\n",
        "\n",
        "df13= df12.withColumn(\"Length_Difference\",length(df12[\"Region\"])-length(df12[\"Country\"]))\n",
        "df13.show()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {},
          "inputWidgets": {},
          "nuid": "cf8216c0-6333-4acf-8ff3-ea6e286ee5c7",
          "showTitle": false,
          "title": ""
        },
        "id": "vhWMJGpk9LCf"
      },
      "source": [
        "14. Convert the 'OrderPriority' to show the full word: 'High', 'Medium', 'Low', or 'Critical'."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 21,
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {},
          "inputWidgets": {},
          "nuid": "782ff4d4-2f50-4581-8998-8e8d18616594",
          "showTitle": false,
          "title": ""
        },
        "id": "9LdijUrj9LCf",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "cb281744-ec44-4ed4-e2fa-18bbadbf9ff3"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------------+----------+---------------+------------+-------------+----------+---------+---------+--------+------------+----------+-----------+--------------------+------------+-------------------+-------------------+-------------------------+------------------+\n",
            "|              Region|   Country|       ItemType|SalesChannel|OrderPriority|   OrderID|UnitsSold|UnitPrice|UnitCost|TotalRevenue| TotalCost|TotalProfit|      Region_Country|Order_lenght|Country_3_Character|  ItemType_Priprity|Region_Replace_and_with_&|OrderPriority_Full|\n",
            "+--------------------+----------+---------------+------------+-------------+----------+---------+---------+--------+------------+----------+-----------+--------------------+------------+-------------------+-------------------+-------------------------+------------------+\n",
            "|middle east and n...|     LIBYA|      Cosmetics|     Offline|       Medium|0686800706|     8446|    437.2|  263.33|   3692591.2|2224085.18| 1468506.02|middle east and n...|           9|                LIB|      Cosmetics , M|     middle east & nor...|            Medium|\n",
            "|       north america|    CANADA|     Vegetables|      Online|       Medium|0185941302|     3018|   154.06|   90.93|   464953.08| 274426.74|  190526.34|north america-CANADA|           9|                CAN|     Vegetables , M|            north america|            Medium|\n",
            "|middle east and n...|     LIBYA|      Baby Food|     Offline|     Critical|0246222341|     1517|   255.28|  159.42|   387259.76| 241840.14|  145419.62|middle east and n...|           9|                LIB|      Baby Food , C|     middle east & nor...|              NULL|\n",
            "|                asia|     JAPAN|         Cereal|     Offline|     Critical|0161442649|     3322|    205.7|  117.11|    683335.4| 389039.42|  294295.98|          asia-JAPAN|           9|                JAP|         Cereal , C|                     asia|              NULL|\n",
            "|  sub-saharan africa|      CHAD|         Fruits|     Offline|         High|0645713555|     9845|     9.33|    6.92|    91853.85|   68127.4|   23726.45|sub-saharan afric...|           9|                CHA|         Fruits , H|       sub-saharan africa|              High|\n",
            "|              europe|   ARMENIA|         Cereal|      Online|         High|0683458888|     9528|    205.7|  117.11|   1959909.6|1115824.08|  844085.52|      europe-ARMENIA|           9|                ARM|         Cereal , H|                   europe|              High|\n",
            "|  sub-saharan africa|   ERITREA|         Cereal|      Online|         High|0679414975|     2844|    205.7|  117.11|    585010.8| 333060.84|  251949.96|sub-saharan afric...|           9|                ERI|         Cereal , H|       sub-saharan africa|              High|\n",
            "|              europe|MONTENEGRO|        Clothes|     Offline|       Medium|0208630645|     7299|   109.28|   35.84|   797634.72| 261596.16|  536038.56|   europe-MONTENEGRO|           9|                MON|        Clothes , M|                   europe|            Medium|\n",
            "|central america a...|   JAMAICA|     Vegetables|      Online|         High|0266467225|     2428|   154.06|   90.93|   374057.68| 220778.04|  153279.64|central america a...|           9|                JAM|     Vegetables , H|     central america &...|              High|\n",
            "|australia and oce...|      FIJI|     Vegetables|     Offline|         High|0118598544|     4800|   154.06|   90.93|    739488.0|  436464.0|   303024.0|australia and oce...|           9|                FIJ|     Vegetables , H|      australia & oceania|              High|\n",
            "|  sub-saharan africa|      TOGO|        Clothes|      Online|       Medium|0451010930|     3012|   109.28|   35.84|   329151.36| 107950.08|  221201.28|sub-saharan afric...|           9|                TOG|        Clothes , M|       sub-saharan africa|            Medium|\n",
            "|              europe|MONTENEGRO|         Snacks|     Offline|       Medium|0220003211|     2694|   152.58|   97.44|   411050.52| 262503.36|  148547.16|   europe-MONTENEGRO|           9|                MON|         Snacks , M|                   europe|            Medium|\n",
            "|              europe|    GREECE|      Household|      Online|     Critical|0702186715|     1508|   668.27|  502.54|  1007751.16| 757830.32|  249920.84|       europe-GREECE|           9|                GRE|      Household , C|                   europe|              NULL|\n",
            "|  sub-saharan africa|     SUDAN|      Cosmetics|      Online|     Critical|0544485270|     4146|    437.2|  263.33|   1812631.2|1091766.18|  720865.02|sub-saharan afric...|           9|                SUD|      Cosmetics , C|       sub-saharan africa|              NULL|\n",
            "|                asia|  MALDIVES|         Fruits|     Offline|          Low|0714135205|     7332|     9.33|    6.92|    68407.56|  50737.44|   17670.12|       asia-MALDIVES|           9|                MAL|         Fruits , L|                     asia|               Low|\n",
            "|              europe|MONTENEGRO|        Clothes|     Offline|         High|0448685348|     4820|   109.28|   35.84|    526729.6|  172748.8|   353980.8|   europe-MONTENEGRO|           9|                MON|        Clothes , H|                   europe|              High|\n",
            "|              europe|   ESTONIA|Office Supplies|      Online|         High|0405997025|     2397|   651.21|  524.96|  1560950.37|1258329.12|  302621.25|      europe-ESTONIA|           9|                EST|Office Supplies , H|                   europe|              High|\n",
            "|       north america| GREENLAND|      Beverages|      Online|       Medium|0414244067|     2880|    47.45|   31.79|    136656.0|   91555.2|    45100.8|north america-GRE...|           9|                GRE|      Beverages , M|            north america|            Medium|\n",
            "|  sub-saharan africa|CAPE VERDE|        Clothes|      Online|     Critical|0821912801|     1117|   109.28|   35.84|   122065.76|  40033.28|   82032.48|sub-saharan afric...|           9|                CAP|        Clothes , C|       sub-saharan africa|              NULL|\n",
            "|  sub-saharan africa|   SENEGAL|      Household|     Offline|          Low|0247802054|     8989|   668.27|  502.54|  6007079.03|4517332.06| 1489746.97|sub-saharan afric...|           9|                SEN|      Household , L|       sub-saharan africa|               Low|\n",
            "+--------------------+----------+---------------+------------+-------------+----------+---------+---------+--------+------------+----------+-----------+--------------------+------------+-------------------+-------------------+-------------------------+------------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ],
      "source": [
        "# Your code here\n",
        "\n",
        "df14 = df11.withColumn(\"OrderPriority\", when(df11[\"OrderPriority\"] ==\"H\",\"High\").when(df11[\"OrderPriority\"] ==\"M\",\"Medium\").when(df11[\"OrderPriority\"] ==\"L\",\"Low\").otherwise(\"Critical\"))\n",
        "df14.show()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {},
          "inputWidgets": {},
          "nuid": "7c2d0865-3d55-4ca9-bab0-15a89d645271",
          "showTitle": false,
          "title": ""
        },
        "id": "GZEYx7cW9LCf"
      },
      "source": [
        "15. Create a new column 'ItemCode' by extracting the first 3 characters of 'ItemType' and converting them to uppercase."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 22,
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {},
          "inputWidgets": {},
          "nuid": "1905223d-cb0e-477d-aaa3-65fa278b71d2",
          "showTitle": false,
          "title": ""
        },
        "id": "H1T-RXTX9LCf",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "d68dde61-b763-4a7a-aa9c-425de9494a99"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------------+----------+---------------+------------+-------------+----------+---------+---------+--------+------------+----------+-----------+--------------------+------------+-------------------+-------------------+-------------------------+------------------+--------+\n",
            "|              Region|   Country|       ItemType|SalesChannel|OrderPriority|   OrderID|UnitsSold|UnitPrice|UnitCost|TotalRevenue| TotalCost|TotalProfit|      Region_Country|Order_lenght|Country_3_Character|  ItemType_Priprity|Region_Replace_and_with_&|OrderPriority_Full|ItemCode|\n",
            "+--------------------+----------+---------------+------------+-------------+----------+---------+---------+--------+------------+----------+-----------+--------------------+------------+-------------------+-------------------+-------------------------+------------------+--------+\n",
            "|middle east and n...|     LIBYA|      Cosmetics|     Offline|       Medium|0686800706|     8446|    437.2|  263.33|   3692591.2|2224085.18| 1468506.02|middle east and n...|           9|                LIB|      Cosmetics , M|     middle east & nor...|            Medium|     COS|\n",
            "|       north america|    CANADA|     Vegetables|      Online|       Medium|0185941302|     3018|   154.06|   90.93|   464953.08| 274426.74|  190526.34|north america-CANADA|           9|                CAN|     Vegetables , M|            north america|            Medium|     VEG|\n",
            "|middle east and n...|     LIBYA|      Baby Food|     Offline|     Critical|0246222341|     1517|   255.28|  159.42|   387259.76| 241840.14|  145419.62|middle east and n...|           9|                LIB|      Baby Food , C|     middle east & nor...|              NULL|     BAB|\n",
            "|                asia|     JAPAN|         Cereal|     Offline|     Critical|0161442649|     3322|    205.7|  117.11|    683335.4| 389039.42|  294295.98|          asia-JAPAN|           9|                JAP|         Cereal , C|                     asia|              NULL|     CER|\n",
            "|  sub-saharan africa|      CHAD|         Fruits|     Offline|         High|0645713555|     9845|     9.33|    6.92|    91853.85|   68127.4|   23726.45|sub-saharan afric...|           9|                CHA|         Fruits , H|       sub-saharan africa|              High|     FRU|\n",
            "|              europe|   ARMENIA|         Cereal|      Online|         High|0683458888|     9528|    205.7|  117.11|   1959909.6|1115824.08|  844085.52|      europe-ARMENIA|           9|                ARM|         Cereal , H|                   europe|              High|     CER|\n",
            "|  sub-saharan africa|   ERITREA|         Cereal|      Online|         High|0679414975|     2844|    205.7|  117.11|    585010.8| 333060.84|  251949.96|sub-saharan afric...|           9|                ERI|         Cereal , H|       sub-saharan africa|              High|     CER|\n",
            "|              europe|MONTENEGRO|        Clothes|     Offline|       Medium|0208630645|     7299|   109.28|   35.84|   797634.72| 261596.16|  536038.56|   europe-MONTENEGRO|           9|                MON|        Clothes , M|                   europe|            Medium|     CLO|\n",
            "|central america a...|   JAMAICA|     Vegetables|      Online|         High|0266467225|     2428|   154.06|   90.93|   374057.68| 220778.04|  153279.64|central america a...|           9|                JAM|     Vegetables , H|     central america &...|              High|     VEG|\n",
            "|australia and oce...|      FIJI|     Vegetables|     Offline|         High|0118598544|     4800|   154.06|   90.93|    739488.0|  436464.0|   303024.0|australia and oce...|           9|                FIJ|     Vegetables , H|      australia & oceania|              High|     VEG|\n",
            "|  sub-saharan africa|      TOGO|        Clothes|      Online|       Medium|0451010930|     3012|   109.28|   35.84|   329151.36| 107950.08|  221201.28|sub-saharan afric...|           9|                TOG|        Clothes , M|       sub-saharan africa|            Medium|     CLO|\n",
            "|              europe|MONTENEGRO|         Snacks|     Offline|       Medium|0220003211|     2694|   152.58|   97.44|   411050.52| 262503.36|  148547.16|   europe-MONTENEGRO|           9|                MON|         Snacks , M|                   europe|            Medium|     SNA|\n",
            "|              europe|    GREECE|      Household|      Online|     Critical|0702186715|     1508|   668.27|  502.54|  1007751.16| 757830.32|  249920.84|       europe-GREECE|           9|                GRE|      Household , C|                   europe|              NULL|     HOU|\n",
            "|  sub-saharan africa|     SUDAN|      Cosmetics|      Online|     Critical|0544485270|     4146|    437.2|  263.33|   1812631.2|1091766.18|  720865.02|sub-saharan afric...|           9|                SUD|      Cosmetics , C|       sub-saharan africa|              NULL|     COS|\n",
            "|                asia|  MALDIVES|         Fruits|     Offline|          Low|0714135205|     7332|     9.33|    6.92|    68407.56|  50737.44|   17670.12|       asia-MALDIVES|           9|                MAL|         Fruits , L|                     asia|               Low|     FRU|\n",
            "|              europe|MONTENEGRO|        Clothes|     Offline|         High|0448685348|     4820|   109.28|   35.84|    526729.6|  172748.8|   353980.8|   europe-MONTENEGRO|           9|                MON|        Clothes , H|                   europe|              High|     CLO|\n",
            "|              europe|   ESTONIA|Office Supplies|      Online|         High|0405997025|     2397|   651.21|  524.96|  1560950.37|1258329.12|  302621.25|      europe-ESTONIA|           9|                EST|Office Supplies , H|                   europe|              High|     OFF|\n",
            "|       north america| GREENLAND|      Beverages|      Online|       Medium|0414244067|     2880|    47.45|   31.79|    136656.0|   91555.2|    45100.8|north america-GRE...|           9|                GRE|      Beverages , M|            north america|            Medium|     BEV|\n",
            "|  sub-saharan africa|CAPE VERDE|        Clothes|      Online|     Critical|0821912801|     1117|   109.28|   35.84|   122065.76|  40033.28|   82032.48|sub-saharan afric...|           9|                CAP|        Clothes , C|       sub-saharan africa|              NULL|     CLO|\n",
            "|  sub-saharan africa|   SENEGAL|      Household|     Offline|          Low|0247802054|     8989|   668.27|  502.54|  6007079.03|4517332.06| 1489746.97|sub-saharan afric...|           9|                SEN|      Household , L|       sub-saharan africa|               Low|     HOU|\n",
            "+--------------------+----------+---------------+------------+-------------+----------+---------+---------+--------+------------+----------+-----------+--------------------+------------+-------------------+-------------------+-------------------------+------------------+--------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ],
      "source": [
        "# Your code here\n",
        "\n",
        "df15 = df14.withColumn(\"ItemCode\", upper(substring(df14[\"ItemType\"],1,3)))\n",
        "df15.show()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {},
          "inputWidgets": {},
          "nuid": "edb7e5d2-14ee-48b0-8993-346c2ced9877",
          "showTitle": false,
          "title": ""
        },
        "id": "5AYUISPZ9LCg"
      },
      "source": [
        "## Advanced Level Questions"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {},
          "inputWidgets": {},
          "nuid": "1985add1-bfb7-465e-bc0d-73cdc7788f7b",
          "showTitle": false,
          "title": ""
        },
        "id": "V_JymKIs9LCg"
      },
      "source": [
        "16. Create a new column 'RegionCode' that consists of the first letter of each word in the 'Region' column."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 23,
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {},
          "inputWidgets": {},
          "nuid": "c3a58051-c690-4164-91a4-1fc2b1ad4291",
          "showTitle": false,
          "title": ""
        },
        "id": "FOT93y8S9LCg",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "21a29cc4-2b34-46c3-d81b-9cae20cf5c0f"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------------+----------+---------------+------------+-------------+----------+---------+---------+--------+------------+----------+-----------+--------------------+------------+-------------------+-------------------+-------------------------+------------------+--------+----------+\n",
            "|              Region|   Country|       ItemType|SalesChannel|OrderPriority|   OrderID|UnitsSold|UnitPrice|UnitCost|TotalRevenue| TotalCost|TotalProfit|      Region_Country|Order_lenght|Country_3_Character|  ItemType_Priprity|Region_Replace_and_with_&|OrderPriority_Full|ItemCode|RegionCode|\n",
            "+--------------------+----------+---------------+------------+-------------+----------+---------+---------+--------+------------+----------+-----------+--------------------+------------+-------------------+-------------------+-------------------------+------------------+--------+----------+\n",
            "|middle east and n...|     LIBYA|      Cosmetics|     Offline|       Medium|0686800706|     8446|    437.2|  263.33|   3692591.2|2224085.18| 1468506.02|middle east and n...|           9|                LIB|      Cosmetics , M|     middle east & nor...|            Medium|     COS|     MEANA|\n",
            "|       north america|    CANADA|     Vegetables|      Online|       Medium|0185941302|     3018|   154.06|   90.93|   464953.08| 274426.74|  190526.34|north america-CANADA|           9|                CAN|     Vegetables , M|            north america|            Medium|     VEG|        NA|\n",
            "|middle east and n...|     LIBYA|      Baby Food|     Offline|     Critical|0246222341|     1517|   255.28|  159.42|   387259.76| 241840.14|  145419.62|middle east and n...|           9|                LIB|      Baby Food , C|     middle east & nor...|              NULL|     BAB|     MEANA|\n",
            "|                asia|     JAPAN|         Cereal|     Offline|     Critical|0161442649|     3322|    205.7|  117.11|    683335.4| 389039.42|  294295.98|          asia-JAPAN|           9|                JAP|         Cereal , C|                     asia|              NULL|     CER|         A|\n",
            "|  sub-saharan africa|      CHAD|         Fruits|     Offline|         High|0645713555|     9845|     9.33|    6.92|    91853.85|   68127.4|   23726.45|sub-saharan afric...|           9|                CHA|         Fruits , H|       sub-saharan africa|              High|     FRU|        SA|\n",
            "|              europe|   ARMENIA|         Cereal|      Online|         High|0683458888|     9528|    205.7|  117.11|   1959909.6|1115824.08|  844085.52|      europe-ARMENIA|           9|                ARM|         Cereal , H|                   europe|              High|     CER|         E|\n",
            "|  sub-saharan africa|   ERITREA|         Cereal|      Online|         High|0679414975|     2844|    205.7|  117.11|    585010.8| 333060.84|  251949.96|sub-saharan afric...|           9|                ERI|         Cereal , H|       sub-saharan africa|              High|     CER|        SA|\n",
            "|              europe|MONTENEGRO|        Clothes|     Offline|       Medium|0208630645|     7299|   109.28|   35.84|   797634.72| 261596.16|  536038.56|   europe-MONTENEGRO|           9|                MON|        Clothes , M|                   europe|            Medium|     CLO|         E|\n",
            "|central america a...|   JAMAICA|     Vegetables|      Online|         High|0266467225|     2428|   154.06|   90.93|   374057.68| 220778.04|  153279.64|central america a...|           9|                JAM|     Vegetables , H|     central america &...|              High|     VEG|     CAATC|\n",
            "|australia and oce...|      FIJI|     Vegetables|     Offline|         High|0118598544|     4800|   154.06|   90.93|    739488.0|  436464.0|   303024.0|australia and oce...|           9|                FIJ|     Vegetables , H|      australia & oceania|              High|     VEG|       AAO|\n",
            "|  sub-saharan africa|      TOGO|        Clothes|      Online|       Medium|0451010930|     3012|   109.28|   35.84|   329151.36| 107950.08|  221201.28|sub-saharan afric...|           9|                TOG|        Clothes , M|       sub-saharan africa|            Medium|     CLO|        SA|\n",
            "|              europe|MONTENEGRO|         Snacks|     Offline|       Medium|0220003211|     2694|   152.58|   97.44|   411050.52| 262503.36|  148547.16|   europe-MONTENEGRO|           9|                MON|         Snacks , M|                   europe|            Medium|     SNA|         E|\n",
            "|              europe|    GREECE|      Household|      Online|     Critical|0702186715|     1508|   668.27|  502.54|  1007751.16| 757830.32|  249920.84|       europe-GREECE|           9|                GRE|      Household , C|                   europe|              NULL|     HOU|         E|\n",
            "|  sub-saharan africa|     SUDAN|      Cosmetics|      Online|     Critical|0544485270|     4146|    437.2|  263.33|   1812631.2|1091766.18|  720865.02|sub-saharan afric...|           9|                SUD|      Cosmetics , C|       sub-saharan africa|              NULL|     COS|        SA|\n",
            "|                asia|  MALDIVES|         Fruits|     Offline|          Low|0714135205|     7332|     9.33|    6.92|    68407.56|  50737.44|   17670.12|       asia-MALDIVES|           9|                MAL|         Fruits , L|                     asia|               Low|     FRU|         A|\n",
            "|              europe|MONTENEGRO|        Clothes|     Offline|         High|0448685348|     4820|   109.28|   35.84|    526729.6|  172748.8|   353980.8|   europe-MONTENEGRO|           9|                MON|        Clothes , H|                   europe|              High|     CLO|         E|\n",
            "|              europe|   ESTONIA|Office Supplies|      Online|         High|0405997025|     2397|   651.21|  524.96|  1560950.37|1258329.12|  302621.25|      europe-ESTONIA|           9|                EST|Office Supplies , H|                   europe|              High|     OFF|         E|\n",
            "|       north america| GREENLAND|      Beverages|      Online|       Medium|0414244067|     2880|    47.45|   31.79|    136656.0|   91555.2|    45100.8|north america-GRE...|           9|                GRE|      Beverages , M|            north america|            Medium|     BEV|        NA|\n",
            "|  sub-saharan africa|CAPE VERDE|        Clothes|      Online|     Critical|0821912801|     1117|   109.28|   35.84|   122065.76|  40033.28|   82032.48|sub-saharan afric...|           9|                CAP|        Clothes , C|       sub-saharan africa|              NULL|     CLO|        SA|\n",
            "|  sub-saharan africa|   SENEGAL|      Household|     Offline|          Low|0247802054|     8989|   668.27|  502.54|  6007079.03|4517332.06| 1489746.97|sub-saharan afric...|           9|                SEN|      Household , L|       sub-saharan africa|               Low|     HOU|        SA|\n",
            "+--------------------+----------+---------------+------------+-------------+----------+---------+---------+--------+------------+----------+-----------+--------------------+------------+-------------------+-------------------+-------------------------+------------------+--------+----------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ],
      "source": [
        "# Your code here\n",
        "from pyspark.sql import functions as F\n",
        "df16 = df15.withColumn(\n",
        "    \"RegionCode\",\n",
        "    upper(\n",
        "        F.array_join(\n",
        "            F.expr(\"transform(split(Region, ' '), x -> left(x, 1))\"),\n",
        "            \"\"\n",
        "        )\n",
        "    )\n",
        ")\n",
        "\n",
        "df16.show()\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {},
          "inputWidgets": {},
          "nuid": "292ad236-d34c-4fc4-a46f-50ee5e5d1b96",
          "showTitle": false,
          "title": ""
        },
        "id": "P8aGXaLD9LCg"
      },
      "source": [
        "17. Create a new column 'FormattedRevenue' that formats 'TotalRevenue' as a string with 2 decimal places and thousands separator."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 24,
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {},
          "inputWidgets": {},
          "nuid": "a22e4a38-173c-4584-9a78-7ffeccedc666",
          "showTitle": false,
          "title": ""
        },
        "id": "xADfg5CG9LCg",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "1d218f90-4222-4308-87de-b7168bdf15f9"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------------+----------+---------------+------------+-------------+----------+---------+---------+--------+------------+----------+-----------+--------------------+------------+-------------------+-------------------+-------------------------+------------------+--------+----------+----------------+\n",
            "|              Region|   Country|       ItemType|SalesChannel|OrderPriority|   OrderID|UnitsSold|UnitPrice|UnitCost|TotalRevenue| TotalCost|TotalProfit|      Region_Country|Order_lenght|Country_3_Character|  ItemType_Priprity|Region_Replace_and_with_&|OrderPriority_Full|ItemCode|RegionCode|FormattedRevenue|\n",
            "+--------------------+----------+---------------+------------+-------------+----------+---------+---------+--------+------------+----------+-----------+--------------------+------------+-------------------+-------------------+-------------------------+------------------+--------+----------+----------------+\n",
            "|middle east and n...|     LIBYA|      Cosmetics|     Offline|       Medium|0686800706|     8446|    437.2|  263.33|   3692591.2|2224085.18| 1468506.02|middle east and n...|           9|                LIB|      Cosmetics , M|     middle east & nor...|            Medium|     COS|     MEANA|      3692591.20|\n",
            "|       north america|    CANADA|     Vegetables|      Online|       Medium|0185941302|     3018|   154.06|   90.93|   464953.08| 274426.74|  190526.34|north america-CANADA|           9|                CAN|     Vegetables , M|            north america|            Medium|     VEG|        NA|       464953.08|\n",
            "|middle east and n...|     LIBYA|      Baby Food|     Offline|     Critical|0246222341|     1517|   255.28|  159.42|   387259.76| 241840.14|  145419.62|middle east and n...|           9|                LIB|      Baby Food , C|     middle east & nor...|              NULL|     BAB|     MEANA|       387259.76|\n",
            "|                asia|     JAPAN|         Cereal|     Offline|     Critical|0161442649|     3322|    205.7|  117.11|    683335.4| 389039.42|  294295.98|          asia-JAPAN|           9|                JAP|         Cereal , C|                     asia|              NULL|     CER|         A|       683335.40|\n",
            "|  sub-saharan africa|      CHAD|         Fruits|     Offline|         High|0645713555|     9845|     9.33|    6.92|    91853.85|   68127.4|   23726.45|sub-saharan afric...|           9|                CHA|         Fruits , H|       sub-saharan africa|              High|     FRU|        SA|        91853.85|\n",
            "|              europe|   ARMENIA|         Cereal|      Online|         High|0683458888|     9528|    205.7|  117.11|   1959909.6|1115824.08|  844085.52|      europe-ARMENIA|           9|                ARM|         Cereal , H|                   europe|              High|     CER|         E|      1959909.60|\n",
            "|  sub-saharan africa|   ERITREA|         Cereal|      Online|         High|0679414975|     2844|    205.7|  117.11|    585010.8| 333060.84|  251949.96|sub-saharan afric...|           9|                ERI|         Cereal , H|       sub-saharan africa|              High|     CER|        SA|       585010.80|\n",
            "|              europe|MONTENEGRO|        Clothes|     Offline|       Medium|0208630645|     7299|   109.28|   35.84|   797634.72| 261596.16|  536038.56|   europe-MONTENEGRO|           9|                MON|        Clothes , M|                   europe|            Medium|     CLO|         E|       797634.72|\n",
            "|central america a...|   JAMAICA|     Vegetables|      Online|         High|0266467225|     2428|   154.06|   90.93|   374057.68| 220778.04|  153279.64|central america a...|           9|                JAM|     Vegetables , H|     central america &...|              High|     VEG|     CAATC|       374057.68|\n",
            "|australia and oce...|      FIJI|     Vegetables|     Offline|         High|0118598544|     4800|   154.06|   90.93|    739488.0|  436464.0|   303024.0|australia and oce...|           9|                FIJ|     Vegetables , H|      australia & oceania|              High|     VEG|       AAO|       739488.00|\n",
            "|  sub-saharan africa|      TOGO|        Clothes|      Online|       Medium|0451010930|     3012|   109.28|   35.84|   329151.36| 107950.08|  221201.28|sub-saharan afric...|           9|                TOG|        Clothes , M|       sub-saharan africa|            Medium|     CLO|        SA|       329151.36|\n",
            "|              europe|MONTENEGRO|         Snacks|     Offline|       Medium|0220003211|     2694|   152.58|   97.44|   411050.52| 262503.36|  148547.16|   europe-MONTENEGRO|           9|                MON|         Snacks , M|                   europe|            Medium|     SNA|         E|       411050.52|\n",
            "|              europe|    GREECE|      Household|      Online|     Critical|0702186715|     1508|   668.27|  502.54|  1007751.16| 757830.32|  249920.84|       europe-GREECE|           9|                GRE|      Household , C|                   europe|              NULL|     HOU|         E|      1007751.16|\n",
            "|  sub-saharan africa|     SUDAN|      Cosmetics|      Online|     Critical|0544485270|     4146|    437.2|  263.33|   1812631.2|1091766.18|  720865.02|sub-saharan afric...|           9|                SUD|      Cosmetics , C|       sub-saharan africa|              NULL|     COS|        SA|      1812631.20|\n",
            "|                asia|  MALDIVES|         Fruits|     Offline|          Low|0714135205|     7332|     9.33|    6.92|    68407.56|  50737.44|   17670.12|       asia-MALDIVES|           9|                MAL|         Fruits , L|                     asia|               Low|     FRU|         A|        68407.56|\n",
            "|              europe|MONTENEGRO|        Clothes|     Offline|         High|0448685348|     4820|   109.28|   35.84|    526729.6|  172748.8|   353980.8|   europe-MONTENEGRO|           9|                MON|        Clothes , H|                   europe|              High|     CLO|         E|       526729.60|\n",
            "|              europe|   ESTONIA|Office Supplies|      Online|         High|0405997025|     2397|   651.21|  524.96|  1560950.37|1258329.12|  302621.25|      europe-ESTONIA|           9|                EST|Office Supplies , H|                   europe|              High|     OFF|         E|      1560950.37|\n",
            "|       north america| GREENLAND|      Beverages|      Online|       Medium|0414244067|     2880|    47.45|   31.79|    136656.0|   91555.2|    45100.8|north america-GRE...|           9|                GRE|      Beverages , M|            north america|            Medium|     BEV|        NA|       136656.00|\n",
            "|  sub-saharan africa|CAPE VERDE|        Clothes|      Online|     Critical|0821912801|     1117|   109.28|   35.84|   122065.76|  40033.28|   82032.48|sub-saharan afric...|           9|                CAP|        Clothes , C|       sub-saharan africa|              NULL|     CLO|        SA|       122065.76|\n",
            "|  sub-saharan africa|   SENEGAL|      Household|     Offline|          Low|0247802054|     8989|   668.27|  502.54|  6007079.03|4517332.06| 1489746.97|sub-saharan afric...|           9|                SEN|      Household , L|       sub-saharan africa|               Low|     HOU|        SA|      6007079.03|\n",
            "+--------------------+----------+---------------+------------+-------------+----------+---------+---------+--------+------------+----------+-----------+--------------------+------------+-------------------+-------------------+-------------------------+------------------+--------+----------+----------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ],
      "source": [
        "# Your code here\n",
        "\n",
        "df17 =df16.withColumn(\"FormattedRevenue\",format_string(\"%.2f\",df16[\"TotalRevenue\"]))\n",
        "#df = df.withColumn(\"FormattedRevenue\", format_string(\"%,.2f\", col(\"TotalRevenue\")))\n",
        "df17.show()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {},
          "inputWidgets": {},
          "nuid": "90ba52cb-1272-493a-971f-73a1f4827220",
          "showTitle": false,
          "title": ""
        },
        "id": "MN1pLDPs9LCg"
      },
      "source": [
        "18. Create a new column 'ItemSummary' that combines 'ItemType', 'UnitPrice', and 'UnitCost' in the format: 'ItemType: $UnitPrice ($UnitCost)'."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 32,
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {},
          "inputWidgets": {},
          "nuid": "7c5fa7c3-c18b-4fac-bac9-67eb7ba85cce",
          "showTitle": false,
          "title": ""
        },
        "id": "GaK79_lj9LCg",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "60be9a51-d616-47e0-bd16-c17495ed05fa"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------------+----------+---------------+------------+-------------+----------+---------+---------+--------+------------+----------+-----------+--------------------+------------+-------------------+-------------------+-------------------------+------------------+--------+----------+----------------+--------------------+\n",
            "|              Region|   Country|       ItemType|SalesChannel|OrderPriority|   OrderID|UnitsSold|UnitPrice|UnitCost|TotalRevenue| TotalCost|TotalProfit|      Region_Country|Order_lenght|Country_3_Character|  ItemType_Priprity|Region_Replace_and_with_&|OrderPriority_Full|ItemCode|RegionCode|FormattedRevenue|         ItemSummary|\n",
            "+--------------------+----------+---------------+------------+-------------+----------+---------+---------+--------+------------+----------+-----------+--------------------+------------+-------------------+-------------------+-------------------------+------------------+--------+----------+----------------+--------------------+\n",
            "|middle east and n...|     LIBYA|      Cosmetics|     Offline|       Medium|0686800706|     8446|    437.2|  263.33|   3692591.2|2224085.18| 1468506.02|middle east and n...|           9|                LIB|      Cosmetics , M|     middle east & nor...|            Medium|     COS|     MEANA|      3692591.20|Cosmetics:437.2(2...|\n",
            "|       north america|    CANADA|     Vegetables|      Online|       Medium|0185941302|     3018|   154.06|   90.93|   464953.08| 274426.74|  190526.34|north america-CANADA|           9|                CAN|     Vegetables , M|            north america|            Medium|     VEG|        NA|       464953.08|Vegetables:154.06...|\n",
            "|middle east and n...|     LIBYA|      Baby Food|     Offline|     Critical|0246222341|     1517|   255.28|  159.42|   387259.76| 241840.14|  145419.62|middle east and n...|           9|                LIB|      Baby Food , C|     middle east & nor...|              NULL|     BAB|     MEANA|       387259.76|Baby Food:255.28(...|\n",
            "|                asia|     JAPAN|         Cereal|     Offline|     Critical|0161442649|     3322|    205.7|  117.11|    683335.4| 389039.42|  294295.98|          asia-JAPAN|           9|                JAP|         Cereal , C|                     asia|              NULL|     CER|         A|       683335.40|Cereal:205.7(117.11)|\n",
            "|  sub-saharan africa|      CHAD|         Fruits|     Offline|         High|0645713555|     9845|     9.33|    6.92|    91853.85|   68127.4|   23726.45|sub-saharan afric...|           9|                CHA|         Fruits , H|       sub-saharan africa|              High|     FRU|        SA|        91853.85|   Fruits:9.33(6.92)|\n",
            "|              europe|   ARMENIA|         Cereal|      Online|         High|0683458888|     9528|    205.7|  117.11|   1959909.6|1115824.08|  844085.52|      europe-ARMENIA|           9|                ARM|         Cereal , H|                   europe|              High|     CER|         E|      1959909.60|Cereal:205.7(117.11)|\n",
            "|  sub-saharan africa|   ERITREA|         Cereal|      Online|         High|0679414975|     2844|    205.7|  117.11|    585010.8| 333060.84|  251949.96|sub-saharan afric...|           9|                ERI|         Cereal , H|       sub-saharan africa|              High|     CER|        SA|       585010.80|Cereal:205.7(117.11)|\n",
            "|              europe|MONTENEGRO|        Clothes|     Offline|       Medium|0208630645|     7299|   109.28|   35.84|   797634.72| 261596.16|  536038.56|   europe-MONTENEGRO|           9|                MON|        Clothes , M|                   europe|            Medium|     CLO|         E|       797634.72|Clothes:109.28(35...|\n",
            "|central america a...|   JAMAICA|     Vegetables|      Online|         High|0266467225|     2428|   154.06|   90.93|   374057.68| 220778.04|  153279.64|central america a...|           9|                JAM|     Vegetables , H|     central america &...|              High|     VEG|     CAATC|       374057.68|Vegetables:154.06...|\n",
            "|australia and oce...|      FIJI|     Vegetables|     Offline|         High|0118598544|     4800|   154.06|   90.93|    739488.0|  436464.0|   303024.0|australia and oce...|           9|                FIJ|     Vegetables , H|      australia & oceania|              High|     VEG|       AAO|       739488.00|Vegetables:154.06...|\n",
            "|  sub-saharan africa|      TOGO|        Clothes|      Online|       Medium|0451010930|     3012|   109.28|   35.84|   329151.36| 107950.08|  221201.28|sub-saharan afric...|           9|                TOG|        Clothes , M|       sub-saharan africa|            Medium|     CLO|        SA|       329151.36|Clothes:109.28(35...|\n",
            "|              europe|MONTENEGRO|         Snacks|     Offline|       Medium|0220003211|     2694|   152.58|   97.44|   411050.52| 262503.36|  148547.16|   europe-MONTENEGRO|           9|                MON|         Snacks , M|                   europe|            Medium|     SNA|         E|       411050.52|Snacks:152.58(97.44)|\n",
            "|              europe|    GREECE|      Household|      Online|     Critical|0702186715|     1508|   668.27|  502.54|  1007751.16| 757830.32|  249920.84|       europe-GREECE|           9|                GRE|      Household , C|                   europe|              NULL|     HOU|         E|      1007751.16|Household:668.27(...|\n",
            "|  sub-saharan africa|     SUDAN|      Cosmetics|      Online|     Critical|0544485270|     4146|    437.2|  263.33|   1812631.2|1091766.18|  720865.02|sub-saharan afric...|           9|                SUD|      Cosmetics , C|       sub-saharan africa|              NULL|     COS|        SA|      1812631.20|Cosmetics:437.2(2...|\n",
            "|                asia|  MALDIVES|         Fruits|     Offline|          Low|0714135205|     7332|     9.33|    6.92|    68407.56|  50737.44|   17670.12|       asia-MALDIVES|           9|                MAL|         Fruits , L|                     asia|               Low|     FRU|         A|        68407.56|   Fruits:9.33(6.92)|\n",
            "|              europe|MONTENEGRO|        Clothes|     Offline|         High|0448685348|     4820|   109.28|   35.84|    526729.6|  172748.8|   353980.8|   europe-MONTENEGRO|           9|                MON|        Clothes , H|                   europe|              High|     CLO|         E|       526729.60|Clothes:109.28(35...|\n",
            "|              europe|   ESTONIA|Office Supplies|      Online|         High|0405997025|     2397|   651.21|  524.96|  1560950.37|1258329.12|  302621.25|      europe-ESTONIA|           9|                EST|Office Supplies , H|                   europe|              High|     OFF|         E|      1560950.37|Office Supplies:6...|\n",
            "|       north america| GREENLAND|      Beverages|      Online|       Medium|0414244067|     2880|    47.45|   31.79|    136656.0|   91555.2|    45100.8|north america-GRE...|           9|                GRE|      Beverages , M|            north america|            Medium|     BEV|        NA|       136656.00|Beverages:47.45(3...|\n",
            "|  sub-saharan africa|CAPE VERDE|        Clothes|      Online|     Critical|0821912801|     1117|   109.28|   35.84|   122065.76|  40033.28|   82032.48|sub-saharan afric...|           9|                CAP|        Clothes , C|       sub-saharan africa|              NULL|     CLO|        SA|       122065.76|Clothes:109.28(35...|\n",
            "|  sub-saharan africa|   SENEGAL|      Household|     Offline|          Low|0247802054|     8989|   668.27|  502.54|  6007079.03|4517332.06| 1489746.97|sub-saharan afric...|           9|                SEN|      Household , L|       sub-saharan africa|               Low|     HOU|        SA|      6007079.03|Household:668.27(...|\n",
            "+--------------------+----------+---------------+------------+-------------+----------+---------+---------+--------+------------+----------+-----------+--------------------+------------+-------------------+-------------------+-------------------------+------------------+--------+----------+----------------+--------------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ],
      "source": [
        "# Your code here\n",
        "\n",
        "df18 = df17.withColumn(\"ItemSummary\",concat(df17[\"ItemType\"],lit(\":\"),df17[\"UnitPrice\"],lit(\"(\"),df17[\"UnitCost\"],lit(\")\")))\n",
        "df18.show()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {},
          "inputWidgets": {},
          "nuid": "18b6697b-d571-4857-8b55-fe9b6066b0f1",
          "showTitle": false,
          "title": ""
        },
        "id": "AKk4WgR-9LCj"
      },
      "source": [
        "19. Create a new column 'OrderStatus' that shows 'Profitable' if TotalProfit > 0, 'Break-even' if TotalProfit = 0, and 'Loss' if TotalProfit < 0."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 33,
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {},
          "inputWidgets": {},
          "nuid": "738f075a-b32f-4f99-b921-6ceeeb30c586",
          "showTitle": false,
          "title": ""
        },
        "id": "aa4s1R4-9LCj",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "a383f45b-8da4-43ee-9e01-7f9adbc23518"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------------+----------+---------------+------------+-------------+----------+---------+---------+--------+------------+----------+-----------+--------------------+------------+-------------------+-------------------+-------------------------+------------------+--------+----------+----------------+--------------------+-----------+\n",
            "|              Region|   Country|       ItemType|SalesChannel|OrderPriority|   OrderID|UnitsSold|UnitPrice|UnitCost|TotalRevenue| TotalCost|TotalProfit|      Region_Country|Order_lenght|Country_3_Character|  ItemType_Priprity|Region_Replace_and_with_&|OrderPriority_Full|ItemCode|RegionCode|FormattedRevenue|         ItemSummary|OrderStatus|\n",
            "+--------------------+----------+---------------+------------+-------------+----------+---------+---------+--------+------------+----------+-----------+--------------------+------------+-------------------+-------------------+-------------------------+------------------+--------+----------+----------------+--------------------+-----------+\n",
            "|middle east and n...|     LIBYA|      Cosmetics|     Offline|       Medium|0686800706|     8446|    437.2|  263.33|   3692591.2|2224085.18| 1468506.02|middle east and n...|           9|                LIB|      Cosmetics , M|     middle east & nor...|            Medium|     COS|     MEANA|      3692591.20|Cosmetics:437.2(2...| Profitable|\n",
            "|       north america|    CANADA|     Vegetables|      Online|       Medium|0185941302|     3018|   154.06|   90.93|   464953.08| 274426.74|  190526.34|north america-CANADA|           9|                CAN|     Vegetables , M|            north america|            Medium|     VEG|        NA|       464953.08|Vegetables:154.06...| Profitable|\n",
            "|middle east and n...|     LIBYA|      Baby Food|     Offline|     Critical|0246222341|     1517|   255.28|  159.42|   387259.76| 241840.14|  145419.62|middle east and n...|           9|                LIB|      Baby Food , C|     middle east & nor...|              NULL|     BAB|     MEANA|       387259.76|Baby Food:255.28(...| Profitable|\n",
            "|                asia|     JAPAN|         Cereal|     Offline|     Critical|0161442649|     3322|    205.7|  117.11|    683335.4| 389039.42|  294295.98|          asia-JAPAN|           9|                JAP|         Cereal , C|                     asia|              NULL|     CER|         A|       683335.40|Cereal:205.7(117.11)| Profitable|\n",
            "|  sub-saharan africa|      CHAD|         Fruits|     Offline|         High|0645713555|     9845|     9.33|    6.92|    91853.85|   68127.4|   23726.45|sub-saharan afric...|           9|                CHA|         Fruits , H|       sub-saharan africa|              High|     FRU|        SA|        91853.85|   Fruits:9.33(6.92)| Profitable|\n",
            "|              europe|   ARMENIA|         Cereal|      Online|         High|0683458888|     9528|    205.7|  117.11|   1959909.6|1115824.08|  844085.52|      europe-ARMENIA|           9|                ARM|         Cereal , H|                   europe|              High|     CER|         E|      1959909.60|Cereal:205.7(117.11)| Profitable|\n",
            "|  sub-saharan africa|   ERITREA|         Cereal|      Online|         High|0679414975|     2844|    205.7|  117.11|    585010.8| 333060.84|  251949.96|sub-saharan afric...|           9|                ERI|         Cereal , H|       sub-saharan africa|              High|     CER|        SA|       585010.80|Cereal:205.7(117.11)| Profitable|\n",
            "|              europe|MONTENEGRO|        Clothes|     Offline|       Medium|0208630645|     7299|   109.28|   35.84|   797634.72| 261596.16|  536038.56|   europe-MONTENEGRO|           9|                MON|        Clothes , M|                   europe|            Medium|     CLO|         E|       797634.72|Clothes:109.28(35...| Profitable|\n",
            "|central america a...|   JAMAICA|     Vegetables|      Online|         High|0266467225|     2428|   154.06|   90.93|   374057.68| 220778.04|  153279.64|central america a...|           9|                JAM|     Vegetables , H|     central america &...|              High|     VEG|     CAATC|       374057.68|Vegetables:154.06...| Profitable|\n",
            "|australia and oce...|      FIJI|     Vegetables|     Offline|         High|0118598544|     4800|   154.06|   90.93|    739488.0|  436464.0|   303024.0|australia and oce...|           9|                FIJ|     Vegetables , H|      australia & oceania|              High|     VEG|       AAO|       739488.00|Vegetables:154.06...| Profitable|\n",
            "|  sub-saharan africa|      TOGO|        Clothes|      Online|       Medium|0451010930|     3012|   109.28|   35.84|   329151.36| 107950.08|  221201.28|sub-saharan afric...|           9|                TOG|        Clothes , M|       sub-saharan africa|            Medium|     CLO|        SA|       329151.36|Clothes:109.28(35...| Profitable|\n",
            "|              europe|MONTENEGRO|         Snacks|     Offline|       Medium|0220003211|     2694|   152.58|   97.44|   411050.52| 262503.36|  148547.16|   europe-MONTENEGRO|           9|                MON|         Snacks , M|                   europe|            Medium|     SNA|         E|       411050.52|Snacks:152.58(97.44)| Profitable|\n",
            "|              europe|    GREECE|      Household|      Online|     Critical|0702186715|     1508|   668.27|  502.54|  1007751.16| 757830.32|  249920.84|       europe-GREECE|           9|                GRE|      Household , C|                   europe|              NULL|     HOU|         E|      1007751.16|Household:668.27(...| Profitable|\n",
            "|  sub-saharan africa|     SUDAN|      Cosmetics|      Online|     Critical|0544485270|     4146|    437.2|  263.33|   1812631.2|1091766.18|  720865.02|sub-saharan afric...|           9|                SUD|      Cosmetics , C|       sub-saharan africa|              NULL|     COS|        SA|      1812631.20|Cosmetics:437.2(2...| Profitable|\n",
            "|                asia|  MALDIVES|         Fruits|     Offline|          Low|0714135205|     7332|     9.33|    6.92|    68407.56|  50737.44|   17670.12|       asia-MALDIVES|           9|                MAL|         Fruits , L|                     asia|               Low|     FRU|         A|        68407.56|   Fruits:9.33(6.92)| Profitable|\n",
            "|              europe|MONTENEGRO|        Clothes|     Offline|         High|0448685348|     4820|   109.28|   35.84|    526729.6|  172748.8|   353980.8|   europe-MONTENEGRO|           9|                MON|        Clothes , H|                   europe|              High|     CLO|         E|       526729.60|Clothes:109.28(35...| Profitable|\n",
            "|              europe|   ESTONIA|Office Supplies|      Online|         High|0405997025|     2397|   651.21|  524.96|  1560950.37|1258329.12|  302621.25|      europe-ESTONIA|           9|                EST|Office Supplies , H|                   europe|              High|     OFF|         E|      1560950.37|Office Supplies:6...| Profitable|\n",
            "|       north america| GREENLAND|      Beverages|      Online|       Medium|0414244067|     2880|    47.45|   31.79|    136656.0|   91555.2|    45100.8|north america-GRE...|           9|                GRE|      Beverages , M|            north america|            Medium|     BEV|        NA|       136656.00|Beverages:47.45(3...| Profitable|\n",
            "|  sub-saharan africa|CAPE VERDE|        Clothes|      Online|     Critical|0821912801|     1117|   109.28|   35.84|   122065.76|  40033.28|   82032.48|sub-saharan afric...|           9|                CAP|        Clothes , C|       sub-saharan africa|              NULL|     CLO|        SA|       122065.76|Clothes:109.28(35...| Profitable|\n",
            "|  sub-saharan africa|   SENEGAL|      Household|     Offline|          Low|0247802054|     8989|   668.27|  502.54|  6007079.03|4517332.06| 1489746.97|sub-saharan afric...|           9|                SEN|      Household , L|       sub-saharan africa|               Low|     HOU|        SA|      6007079.03|Household:668.27(...| Profitable|\n",
            "+--------------------+----------+---------------+------------+-------------+----------+---------+---------+--------+------------+----------+-----------+--------------------+------------+-------------------+-------------------+-------------------------+------------------+--------+----------+----------------+--------------------+-----------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ],
      "source": [
        "# Your code here\n",
        "\n",
        "df19 = df18.withColumn(\"OrderStatus\",when(df18[\"TotalProfit\"]>0,\"Profitable\").when(df18[\"TotalProfit\"] == 0,\"Break-Even\").when(df18[\"TotalProfit\"]<0,\"Loss\"))\n",
        "df19.show()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {},
          "inputWidgets": {},
          "nuid": "ad43bf67-1662-4b7b-945e-16b2f07dc026",
          "showTitle": false,
          "title": ""
        },
        "id": "F1_IKBSe9LCj"
      },
      "source": [
        "20. Create a new column 'ComplexCode' that combines the first letter of Region, first 3 letters of Country, last 3 digits of OrderID, and the first letter of ItemType, all separated by hyphens."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 39,
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {},
          "inputWidgets": {},
          "nuid": "fa0a585c-3b00-47ef-acb3-8e5a67231ba9",
          "showTitle": false,
          "title": ""
        },
        "id": "lQuN2emh9LCj",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "8ed946d4-5cd2-4235-cdae-925d9ba81861"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------------+----------+---------------+------------+-------------+----------+---------+---------+--------+------------+----------+-----------+--------------------+------------+-------------------+-------------------+-------------------------+------------------+--------+----------+----------------+--------------------+-----------+-----------+\n",
            "|              Region|   Country|       ItemType|SalesChannel|OrderPriority|   OrderID|UnitsSold|UnitPrice|UnitCost|TotalRevenue| TotalCost|TotalProfit|      Region_Country|Order_lenght|Country_3_Character|  ItemType_Priprity|Region_Replace_and_with_&|OrderPriority_Full|ItemCode|RegionCode|FormattedRevenue|         ItemSummary|OrderStatus|ComplexCode|\n",
            "+--------------------+----------+---------------+------------+-------------+----------+---------+---------+--------+------------+----------+-----------+--------------------+------------+-------------------+-------------------+-------------------------+------------------+--------+----------+----------------+--------------------+-----------+-----------+\n",
            "|middle east and n...|     LIBYA|      Cosmetics|     Offline|       Medium|0686800706|     8446|    437.2|  263.33|   3692591.2|2224085.18| 1468506.02|middle east and n...|           9|                LIB|      Cosmetics , M|     middle east & nor...|            Medium|     COS|     MEANA|      3692591.20|Cosmetics:437.2(2...| Profitable|m-LIB-706-C|\n",
            "|       north america|    CANADA|     Vegetables|      Online|       Medium|0185941302|     3018|   154.06|   90.93|   464953.08| 274426.74|  190526.34|north america-CANADA|           9|                CAN|     Vegetables , M|            north america|            Medium|     VEG|        NA|       464953.08|Vegetables:154.06...| Profitable|n-CAN-302-V|\n",
            "|middle east and n...|     LIBYA|      Baby Food|     Offline|     Critical|0246222341|     1517|   255.28|  159.42|   387259.76| 241840.14|  145419.62|middle east and n...|           9|                LIB|      Baby Food , C|     middle east & nor...|              NULL|     BAB|     MEANA|       387259.76|Baby Food:255.28(...| Profitable|m-LIB-341-B|\n",
            "|                asia|     JAPAN|         Cereal|     Offline|     Critical|0161442649|     3322|    205.7|  117.11|    683335.4| 389039.42|  294295.98|          asia-JAPAN|           9|                JAP|         Cereal , C|                     asia|              NULL|     CER|         A|       683335.40|Cereal:205.7(117.11)| Profitable|a-JAP-649-C|\n",
            "|  sub-saharan africa|      CHAD|         Fruits|     Offline|         High|0645713555|     9845|     9.33|    6.92|    91853.85|   68127.4|   23726.45|sub-saharan afric...|           9|                CHA|         Fruits , H|       sub-saharan africa|              High|     FRU|        SA|        91853.85|   Fruits:9.33(6.92)| Profitable|s-CHA-555-F|\n",
            "|              europe|   ARMENIA|         Cereal|      Online|         High|0683458888|     9528|    205.7|  117.11|   1959909.6|1115824.08|  844085.52|      europe-ARMENIA|           9|                ARM|         Cereal , H|                   europe|              High|     CER|         E|      1959909.60|Cereal:205.7(117.11)| Profitable|e-ARM-888-C|\n",
            "|  sub-saharan africa|   ERITREA|         Cereal|      Online|         High|0679414975|     2844|    205.7|  117.11|    585010.8| 333060.84|  251949.96|sub-saharan afric...|           9|                ERI|         Cereal , H|       sub-saharan africa|              High|     CER|        SA|       585010.80|Cereal:205.7(117.11)| Profitable|s-ERI-975-C|\n",
            "|              europe|MONTENEGRO|        Clothes|     Offline|       Medium|0208630645|     7299|   109.28|   35.84|   797634.72| 261596.16|  536038.56|   europe-MONTENEGRO|           9|                MON|        Clothes , M|                   europe|            Medium|     CLO|         E|       797634.72|Clothes:109.28(35...| Profitable|e-MON-645-C|\n",
            "|central america a...|   JAMAICA|     Vegetables|      Online|         High|0266467225|     2428|   154.06|   90.93|   374057.68| 220778.04|  153279.64|central america a...|           9|                JAM|     Vegetables , H|     central america &...|              High|     VEG|     CAATC|       374057.68|Vegetables:154.06...| Profitable|c-JAM-225-V|\n",
            "|australia and oce...|      FIJI|     Vegetables|     Offline|         High|0118598544|     4800|   154.06|   90.93|    739488.0|  436464.0|   303024.0|australia and oce...|           9|                FIJ|     Vegetables , H|      australia & oceania|              High|     VEG|       AAO|       739488.00|Vegetables:154.06...| Profitable|a-FIJ-544-V|\n",
            "|  sub-saharan africa|      TOGO|        Clothes|      Online|       Medium|0451010930|     3012|   109.28|   35.84|   329151.36| 107950.08|  221201.28|sub-saharan afric...|           9|                TOG|        Clothes , M|       sub-saharan africa|            Medium|     CLO|        SA|       329151.36|Clothes:109.28(35...| Profitable|s-TOG-930-C|\n",
            "|              europe|MONTENEGRO|         Snacks|     Offline|       Medium|0220003211|     2694|   152.58|   97.44|   411050.52| 262503.36|  148547.16|   europe-MONTENEGRO|           9|                MON|         Snacks , M|                   europe|            Medium|     SNA|         E|       411050.52|Snacks:152.58(97.44)| Profitable|e-MON-211-S|\n",
            "|              europe|    GREECE|      Household|      Online|     Critical|0702186715|     1508|   668.27|  502.54|  1007751.16| 757830.32|  249920.84|       europe-GREECE|           9|                GRE|      Household , C|                   europe|              NULL|     HOU|         E|      1007751.16|Household:668.27(...| Profitable|e-GRE-715-H|\n",
            "|  sub-saharan africa|     SUDAN|      Cosmetics|      Online|     Critical|0544485270|     4146|    437.2|  263.33|   1812631.2|1091766.18|  720865.02|sub-saharan afric...|           9|                SUD|      Cosmetics , C|       sub-saharan africa|              NULL|     COS|        SA|      1812631.20|Cosmetics:437.2(2...| Profitable|s-SUD-270-C|\n",
            "|                asia|  MALDIVES|         Fruits|     Offline|          Low|0714135205|     7332|     9.33|    6.92|    68407.56|  50737.44|   17670.12|       asia-MALDIVES|           9|                MAL|         Fruits , L|                     asia|               Low|     FRU|         A|        68407.56|   Fruits:9.33(6.92)| Profitable|a-MAL-205-F|\n",
            "|              europe|MONTENEGRO|        Clothes|     Offline|         High|0448685348|     4820|   109.28|   35.84|    526729.6|  172748.8|   353980.8|   europe-MONTENEGRO|           9|                MON|        Clothes , H|                   europe|              High|     CLO|         E|       526729.60|Clothes:109.28(35...| Profitable|e-MON-348-C|\n",
            "|              europe|   ESTONIA|Office Supplies|      Online|         High|0405997025|     2397|   651.21|  524.96|  1560950.37|1258329.12|  302621.25|      europe-ESTONIA|           9|                EST|Office Supplies , H|                   europe|              High|     OFF|         E|      1560950.37|Office Supplies:6...| Profitable|e-EST-025-O|\n",
            "|       north america| GREENLAND|      Beverages|      Online|       Medium|0414244067|     2880|    47.45|   31.79|    136656.0|   91555.2|    45100.8|north america-GRE...|           9|                GRE|      Beverages , M|            north america|            Medium|     BEV|        NA|       136656.00|Beverages:47.45(3...| Profitable|n-GRE-067-B|\n",
            "|  sub-saharan africa|CAPE VERDE|        Clothes|      Online|     Critical|0821912801|     1117|   109.28|   35.84|   122065.76|  40033.28|   82032.48|sub-saharan afric...|           9|                CAP|        Clothes , C|       sub-saharan africa|              NULL|     CLO|        SA|       122065.76|Clothes:109.28(35...| Profitable|s-CAP-801-C|\n",
            "|  sub-saharan africa|   SENEGAL|      Household|     Offline|          Low|0247802054|     8989|   668.27|  502.54|  6007079.03|4517332.06| 1489746.97|sub-saharan afric...|           9|                SEN|      Household , L|       sub-saharan africa|               Low|     HOU|        SA|      6007079.03|Household:668.27(...| Profitable|s-SEN-054-H|\n",
            "+--------------------+----------+---------------+------------+-------------+----------+---------+---------+--------+------------+----------+-----------+--------------------+------------+-------------------+-------------------+-------------------------+------------------+--------+----------+----------------+--------------------+-----------+-----------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ],
      "source": [
        "# Your code here\n",
        "\n",
        "df20 = df19.withColumn(\n",
        "    \"ComplexCode\",\n",
        "    concat(\n",
        "        substring(df19[\"Region\"], 1, 1),\n",
        "        lit(\"-\"),\n",
        "        substring(df19[\"Country\"], 1, 3),\n",
        "        lit(\"-\"),\n",
        "        substring(df19[\"OrderID\"], -3,3),\n",
        "        lit(\"-\"),\n",
        "        substring(df19[\"ItemType\"], 1, 1)\n",
        "    )\n",
        ")\n",
        "\n",
        "df20.show()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {},
          "inputWidgets": {},
          "nuid": "1fd059ae-d128-4123-83da-44083e0b5394",
          "showTitle": false,
          "title": ""
        },
        "id": "RLnIpVV19LCj"
      },
      "source": [
        "## Bonus Challenge Question"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {},
          "inputWidgets": {},
          "nuid": "ece022b4-54a9-4716-90b4-304f201637b2",
          "showTitle": false,
          "title": ""
        },
        "id": "BxpAhQ1U9LCk"
      },
      "source": [
        "21. Create a new column 'DescriptiveStatus' that combines information from multiple columns:\n",
        "- Start with the Region (first 3 letters in uppercase)\n",
        "- Add the SalesChannel (full word, capitalized)\n",
        "- Include the ItemType (full word)\n",
        "- Add OrderPriority (H->High, M->Medium, L->Low, C->Critical)\n",
        "- End with a profitability indicator: '+' if TotalProfit > 1000, '=' if 0 <= TotalProfit <= 1000, '-' if TotalProfit < 0\n",
        "\n",
        "Example: \"EUR-Online-Cosmetics-High+\" for a highly profitable online cosmetics order in Europe."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 40,
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {},
          "inputWidgets": {},
          "nuid": "763cdcaa-fdb2-465b-a0fc-2768d0843892",
          "showTitle": false,
          "title": ""
        },
        "id": "_u2T8HUy9LCk",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "d312a5ea-bf18-4c8e-e4d5-259964441160"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------------+----------+---------------+------------+-------------+----------+---------+---------+--------+------------+----------+-----------+--------------------+------------+-------------------+-------------------+-------------------------+------------------+--------+----------+----------------+--------------------+-----------+-----------+--------------------+\n",
            "|              Region|   Country|       ItemType|SalesChannel|OrderPriority|   OrderID|UnitsSold|UnitPrice|UnitCost|TotalRevenue| TotalCost|TotalProfit|      Region_Country|Order_lenght|Country_3_Character|  ItemType_Priprity|Region_Replace_and_with_&|OrderPriority_Full|ItemCode|RegionCode|FormattedRevenue|         ItemSummary|OrderStatus|ComplexCode|   DescriptiveStatus|\n",
            "+--------------------+----------+---------------+------------+-------------+----------+---------+---------+--------+------------+----------+-----------+--------------------+------------+-------------------+-------------------+-------------------------+------------------+--------+----------+----------------+--------------------+-----------+-----------+--------------------+\n",
            "|middle east and n...|     LIBYA|      Cosmetics|     Offline|       Medium|0686800706|     8446|    437.2|  263.33|   3692591.2|2224085.18| 1468506.02|middle east and n...|           9|                LIB|      Cosmetics , M|     middle east & nor...|            Medium|     COS|     MEANA|      3692591.20|Cosmetics:437.2(2...| Profitable|m-LIB-706-C|MID-Offline-Cosme...|\n",
            "|       north america|    CANADA|     Vegetables|      Online|       Medium|0185941302|     3018|   154.06|   90.93|   464953.08| 274426.74|  190526.34|north america-CANADA|           9|                CAN|     Vegetables , M|            north america|            Medium|     VEG|        NA|       464953.08|Vegetables:154.06...| Profitable|n-CAN-302-V|NOR-Online-Vegeta...|\n",
            "|middle east and n...|     LIBYA|      Baby Food|     Offline|     Critical|0246222341|     1517|   255.28|  159.42|   387259.76| 241840.14|  145419.62|middle east and n...|           9|                LIB|      Baby Food , C|     middle east & nor...|              NULL|     BAB|     MEANA|       387259.76|Baby Food:255.28(...| Profitable|m-LIB-341-B|MID-Offline-Baby ...|\n",
            "|                asia|     JAPAN|         Cereal|     Offline|     Critical|0161442649|     3322|    205.7|  117.11|    683335.4| 389039.42|  294295.98|          asia-JAPAN|           9|                JAP|         Cereal , C|                     asia|              NULL|     CER|         A|       683335.40|Cereal:205.7(117.11)| Profitable|a-JAP-649-C|ASI-Offline-Cerea...|\n",
            "|  sub-saharan africa|      CHAD|         Fruits|     Offline|         High|0645713555|     9845|     9.33|    6.92|    91853.85|   68127.4|   23726.45|sub-saharan afric...|           9|                CHA|         Fruits , H|       sub-saharan africa|              High|     FRU|        SA|        91853.85|   Fruits:9.33(6.92)| Profitable|s-CHA-555-F|SUB-Offline-Fruit...|\n",
            "|              europe|   ARMENIA|         Cereal|      Online|         High|0683458888|     9528|    205.7|  117.11|   1959909.6|1115824.08|  844085.52|      europe-ARMENIA|           9|                ARM|         Cereal , H|                   europe|              High|     CER|         E|      1959909.60|Cereal:205.7(117.11)| Profitable|e-ARM-888-C|EUR-Online-Cereal...|\n",
            "|  sub-saharan africa|   ERITREA|         Cereal|      Online|         High|0679414975|     2844|    205.7|  117.11|    585010.8| 333060.84|  251949.96|sub-saharan afric...|           9|                ERI|         Cereal , H|       sub-saharan africa|              High|     CER|        SA|       585010.80|Cereal:205.7(117.11)| Profitable|s-ERI-975-C|SUB-Online-Cereal...|\n",
            "|              europe|MONTENEGRO|        Clothes|     Offline|       Medium|0208630645|     7299|   109.28|   35.84|   797634.72| 261596.16|  536038.56|   europe-MONTENEGRO|           9|                MON|        Clothes , M|                   europe|            Medium|     CLO|         E|       797634.72|Clothes:109.28(35...| Profitable|e-MON-645-C|EUR-Offline-Cloth...|\n",
            "|central america a...|   JAMAICA|     Vegetables|      Online|         High|0266467225|     2428|   154.06|   90.93|   374057.68| 220778.04|  153279.64|central america a...|           9|                JAM|     Vegetables , H|     central america &...|              High|     VEG|     CAATC|       374057.68|Vegetables:154.06...| Profitable|c-JAM-225-V|CEN-Online-Vegeta...|\n",
            "|australia and oce...|      FIJI|     Vegetables|     Offline|         High|0118598544|     4800|   154.06|   90.93|    739488.0|  436464.0|   303024.0|australia and oce...|           9|                FIJ|     Vegetables , H|      australia & oceania|              High|     VEG|       AAO|       739488.00|Vegetables:154.06...| Profitable|a-FIJ-544-V|AUS-Offline-Veget...|\n",
            "|  sub-saharan africa|      TOGO|        Clothes|      Online|       Medium|0451010930|     3012|   109.28|   35.84|   329151.36| 107950.08|  221201.28|sub-saharan afric...|           9|                TOG|        Clothes , M|       sub-saharan africa|            Medium|     CLO|        SA|       329151.36|Clothes:109.28(35...| Profitable|s-TOG-930-C|SUB-Online-Clothe...|\n",
            "|              europe|MONTENEGRO|         Snacks|     Offline|       Medium|0220003211|     2694|   152.58|   97.44|   411050.52| 262503.36|  148547.16|   europe-MONTENEGRO|           9|                MON|         Snacks , M|                   europe|            Medium|     SNA|         E|       411050.52|Snacks:152.58(97.44)| Profitable|e-MON-211-S|EUR-Offline-Snack...|\n",
            "|              europe|    GREECE|      Household|      Online|     Critical|0702186715|     1508|   668.27|  502.54|  1007751.16| 757830.32|  249920.84|       europe-GREECE|           9|                GRE|      Household , C|                   europe|              NULL|     HOU|         E|      1007751.16|Household:668.27(...| Profitable|e-GRE-715-H|EUR-Online-Househ...|\n",
            "|  sub-saharan africa|     SUDAN|      Cosmetics|      Online|     Critical|0544485270|     4146|    437.2|  263.33|   1812631.2|1091766.18|  720865.02|sub-saharan afric...|           9|                SUD|      Cosmetics , C|       sub-saharan africa|              NULL|     COS|        SA|      1812631.20|Cosmetics:437.2(2...| Profitable|s-SUD-270-C|SUB-Online-Cosmet...|\n",
            "|                asia|  MALDIVES|         Fruits|     Offline|          Low|0714135205|     7332|     9.33|    6.92|    68407.56|  50737.44|   17670.12|       asia-MALDIVES|           9|                MAL|         Fruits , L|                     asia|               Low|     FRU|         A|        68407.56|   Fruits:9.33(6.92)| Profitable|a-MAL-205-F|ASI-Offline-Fruit...|\n",
            "|              europe|MONTENEGRO|        Clothes|     Offline|         High|0448685348|     4820|   109.28|   35.84|    526729.6|  172748.8|   353980.8|   europe-MONTENEGRO|           9|                MON|        Clothes , H|                   europe|              High|     CLO|         E|       526729.60|Clothes:109.28(35...| Profitable|e-MON-348-C|EUR-Offline-Cloth...|\n",
            "|              europe|   ESTONIA|Office Supplies|      Online|         High|0405997025|     2397|   651.21|  524.96|  1560950.37|1258329.12|  302621.25|      europe-ESTONIA|           9|                EST|Office Supplies , H|                   europe|              High|     OFF|         E|      1560950.37|Office Supplies:6...| Profitable|e-EST-025-O|EUR-Online-Office...|\n",
            "|       north america| GREENLAND|      Beverages|      Online|       Medium|0414244067|     2880|    47.45|   31.79|    136656.0|   91555.2|    45100.8|north america-GRE...|           9|                GRE|      Beverages , M|            north america|            Medium|     BEV|        NA|       136656.00|Beverages:47.45(3...| Profitable|n-GRE-067-B|NOR-Online-Bevera...|\n",
            "|  sub-saharan africa|CAPE VERDE|        Clothes|      Online|     Critical|0821912801|     1117|   109.28|   35.84|   122065.76|  40033.28|   82032.48|sub-saharan afric...|           9|                CAP|        Clothes , C|       sub-saharan africa|              NULL|     CLO|        SA|       122065.76|Clothes:109.28(35...| Profitable|s-CAP-801-C|SUB-Online-Clothe...|\n",
            "|  sub-saharan africa|   SENEGAL|      Household|     Offline|          Low|0247802054|     8989|   668.27|  502.54|  6007079.03|4517332.06| 1489746.97|sub-saharan afric...|           9|                SEN|      Household , L|       sub-saharan africa|               Low|     HOU|        SA|      6007079.03|Household:668.27(...| Profitable|s-SEN-054-H|SUB-Offline-House...|\n",
            "+--------------------+----------+---------------+------------+-------------+----------+---------+---------+--------+------------+----------+-----------+--------------------+------------+-------------------+-------------------+-------------------------+------------------+--------+----------+----------------+--------------------+-----------+-----------+--------------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ],
      "source": [
        "# Your code here\n",
        "\n",
        "df21 = df20.withColumn(\n",
        "    \"DescriptiveStatus\",\n",
        "    concat(\n",
        "        upper(substring(df19[\"Region\"], 1, 3)),\n",
        "        lit(\"-\"),\n",
        "        df19[\"SalesChannel\"],\n",
        "        lit(\"-\"),\n",
        "        df19[\"ItemType\"],\n",
        "        lit(\"-\"),\n",
        "        when(df19[\"OrderPriority\"] == \"H\", \"High\")\n",
        "            .when(df19[\"OrderPriority\"] == \"M\", \"Medium\")\n",
        "            .when(df19[\"OrderPriority\"] == \"L\", \"Low\")\n",
        "            .otherwise(\"Critical\"),\n",
        "        when(df19[\"TotalProfit\"] > 1000, \"+\")\n",
        "            .when(df19[\"TotalProfit\"] >= 0, \"=\")\n",
        "            .otherwise(\"-\")\n",
        "    )\n",
        ")\n",
        "\n",
        "df21.show()\n"
      ]
    },
    {
      "cell_type": "code",
      "source": [],
      "metadata": {
        "id": "dGZXtgmvlFcW"
      },
      "execution_count": null,
      "outputs": []
    }
  ],
  "metadata": {
    "application/vnd.databricks.v1+notebook": {
      "dashboards": [],
      "environmentMetadata": null,
      "language": "python",
      "notebookMetadata": {
        "pythonIndentUnit": 2
      },
      "notebookName": "Databircks-Assignemnt 3 (New): Pyspark String Basics",
      "widgets": {}
    },
    "kernelspec": {
      "display_name": "Python 3",
      "language": "python",
      "name": "python3"
    },
    "language_info": {
      "codemirror_mode": {
        "name": "ipython",
        "version": 3
      },
      "file_extension": ".py",
      "mimetype": "text/x-python",
      "name": "python",
      "nbconvert_exporter": "python",
      "pygments_lexer": "ipython3",
      "version": "3.8.8"
    },
    "colab": {
      "provenance": [],
      "include_colab_link": true
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}