Advance_String_Manipulation_&_Transformation.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/Advance_String_Manipulation_%26_Transformation.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "<span style=\"color:orange\">\n",
        " <h2> Advance String Manipulation & Transformation"
      ],
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "showTitle": false,
          "cellMetadata": {},
          "nuid": "56c5b381-2cb2-4701-a84d-63ff0cac11fe",
          "inputWidgets": {},
          "title": ""
        },
        "id": "IwIoUY0JzCmd"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "from google.colab import drive\n",
        "drive.mount('/content/drive')"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "VM8WwXV5z5AJ",
        "outputId": "1058f7ab-f17a-48fd-eaa9-0ec882695de0"
      },
      "execution_count": 1,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Mounted at /content/drive\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# install pyspark\n",
        "!pip install pyspark"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "UqQLjHwt0CV3",
        "outputId": "f01efbd9-5b29-4117-91d1-bc50b34d3779"
      },
      "execution_count": 2,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Collecting pyspark\n",
            "  Downloading pyspark-3.5.1.tar.gz (317.0 MB)\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m317.0/317.0 MB\u001b[0m \u001b[31m4.9 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[?25h  Preparing metadata (setup.py) ... \u001b[?25l\u001b[?25hdone\n",
            "Requirement already satisfied: py4j==0.10.9.7 in /usr/local/lib/python3.10/dist-packages (from pyspark) (0.10.9.7)\n",
            "Building wheels for collected packages: pyspark\n",
            "  Building wheel for pyspark (setup.py) ... \u001b[?25l\u001b[?25hdone\n",
            "  Created wheel for pyspark: filename=pyspark-3.5.1-py2.py3-none-any.whl size=317488490 sha256=c4b9023d9e74974ea1eb877ac58dba72d418306c80cdeaa5b83c7ed6e56e3d2f\n",
            "  Stored in directory: /root/.cache/pip/wheels/80/1d/60/2c256ed38dddce2fdd93be545214a63e02fbd8d74fb0b7f3a6\n",
            "Successfully built pyspark\n",
            "Installing collected packages: pyspark\n",
            "Successfully installed pyspark-3.5.1\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# strat spark session\n",
        "from pyspark.sql import SparkSession\n",
        "spark = SparkSession.builder.appName(\"Advance_srting_manipuation\").getOrCreate()"
      ],
      "metadata": {
        "id": "wdvnI2py1UYY"
      },
      "execution_count": 3,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# read csv file\n",
        "df = spark.read.csv(\"/content/drive/MyDrive/DE Data/EmployeeData.csv\", header=True, inferSchema=True)\n",
        "df.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "55OxRmCw1lOy",
        "outputId": "19ee1b47-96c3-4a89-aba9-d51db0bfc5fa"
      },
      "execution_count": 4,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+---+----------+---------+--------------------+------+------+-------------------+--------+\n",
            "| id|first_name|last_name|               email|gender|salary|       creationDate|   bonus|\n",
            "+---+----------+---------+--------------------+------+------+-------------------+--------+\n",
            "|  1|    Valene|   Ingley|vingley0@livejour...|Female| 44104|1957-09-09 16:44:40|    NULL|\n",
            "|  2|  Lynnelle|    Hurll| lhurll1@answers.com|Female|112411|1907-05-10 17:38:56|    NULL|\n",
            "|  3|   Miranda|    Train|   mtrain2@imgur.com|Female| 91073|1941-01-24 16:05:23|12875.54|\n",
            "|  4|    Dulsea|     Foss|dfoss3@dagondesig...|Female|193291|1942-05-09 20:59:39|    NULL|\n",
            "|  5|    Anatol|  Dunklee| adunklee4@google.de|  Male| 22175|1950-07-26 16:28:00| 1432.12|\n",
            "|  6|     Baily|   Antony| bantony5@sfgate.com|  Male|127337|1913-10-14 11:25:33|    NULL|\n",
            "|  7|    Eunice|   Cardus|ecardus6@scientif...|Female|136574|1901-11-02 15:09:47|38676.94|\n",
            "|  8|  Aubrette|  Lippett| alippett7@nifty.com|Female|165713|1919-12-16 08:20:42|60150.66|\n",
            "|  9|   Sibylla|Sickamore|ssickamore8@faceb...|Female|107243|2020-03-15 15:00:30|    NULL|\n",
            "| 10|      NULL|   Altree|paltree9@dropbox.com|Female|197594|1975-06-21 23:27:12|72533.37|\n",
            "| 11|      Coop|    Richt|   crichta@sogou.com|  Male| 19964|1939-02-13 16:18:24|56973.41|\n",
            "| 12|  Giuseppe|  Scimoni|gscimonib@craigsl...|  Male|176531|1967-04-06 07:23:03|    NULL|\n",
            "| 13|    Lovell|  Iorizzo|liorizzoc@cpanel.net|  Male|189150|1949-06-06 16:25:20| 41474.8|\n",
            "| 14|       Deb|    Mogra|   dmograd@bbc.co.uk|Female|197829|1997-07-27 14:55:52|46528.57|\n",
            "| 15|  Hastings| Jelliman|hjellimane@histat...|  Male|179296|1915-07-05 07:02:06|80194.64|\n",
            "| 16|     Josee|   Burnep|    jburnepf@php.net|  NULL| 78569|1923-11-08 20:59:14|88925.13|\n",
            "| 17|     Gilly|   Fownes|gfownesg@redcross...|Female|  2527|1946-07-30 21:10:57|    NULL|\n",
            "| 18|      NULL|Schruyers|lschruyersh@desde...|  Male|109936|1979-06-16 01:12:48|37243.09|\n",
            "| 19|     Maris|Chatelain| mchatelaini@unc.edu|Female|190047|2008-01-11 17:16:21|76469.65|\n",
            "| 20|    Casper|  Aughtie|    caughtiej@vk.com|  Male| 38689|1982-04-06 05:13:49| 80404.8|\n",
            "+---+----------+---------+--------------------+------+------+-------------------+--------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [],
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "showTitle": false,
          "cellMetadata": {},
          "nuid": "f5625faf-7cad-432c-92a2-75e00388d984",
          "inputWidgets": {},
          "title": ""
        },
        "id": "kPc_YUbqzCme"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "<b> What is the abbreviated version of each employee's last name, with the first letter and the two following letters included?"
      ],
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "showTitle": false,
          "cellMetadata": {},
          "nuid": "01541cf5-29d4-4dbe-a0a2-9666393c4171",
          "inputWidgets": {},
          "title": ""
        },
        "id": "iRPQha6ozCmf"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Write your code using the Spark Function\n",
        "from pyspark.sql.functions import *\n",
        "df1 = df.withColumn(\"Abbreviation\", substring(df[\"Last_name\"], 1, 3))\n",
        "df1.show()"
      ],
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "showTitle": false,
          "cellMetadata": {},
          "nuid": "83f0e040-3b07-4e4d-842b-6eedba9aa448",
          "inputWidgets": {},
          "title": ""
        },
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "usYS8uxjzCmf",
        "outputId": "b719f567-1cd9-41d4-a3f2-805515beafcf"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+---+----------+---------+--------------------+------+------+-------------------+--------+------------+\n",
            "| id|first_name|last_name|               email|gender|salary|       creationDate|   bonus|Abbreviation|\n",
            "+---+----------+---------+--------------------+------+------+-------------------+--------+------------+\n",
            "|  1|    Valene|   Ingley|vingley0@livejour...|Female| 44104|1957-09-09 16:44:40|    NULL|         Ing|\n",
            "|  2|  Lynnelle|    Hurll| lhurll1@answers.com|Female|112411|1907-05-10 17:38:56|    NULL|         Hur|\n",
            "|  3|   Miranda|    Train|   mtrain2@imgur.com|Female| 91073|1941-01-24 16:05:23|12875.54|         Tra|\n",
            "|  4|    Dulsea|     Foss|dfoss3@dagondesig...|Female|193291|1942-05-09 20:59:39|    NULL|         Fos|\n",
            "|  5|    Anatol|  Dunklee| adunklee4@google.de|  Male| 22175|1950-07-26 16:28:00| 1432.12|         Dun|\n",
            "|  6|     Baily|   Antony| bantony5@sfgate.com|  Male|127337|1913-10-14 11:25:33|    NULL|         Ant|\n",
            "|  7|    Eunice|   Cardus|ecardus6@scientif...|Female|136574|1901-11-02 15:09:47|38676.94|         Car|\n",
            "|  8|  Aubrette|  Lippett| alippett7@nifty.com|Female|165713|1919-12-16 08:20:42|60150.66|         Lip|\n",
            "|  9|   Sibylla|Sickamore|ssickamore8@faceb...|Female|107243|2020-03-15 15:00:30|    NULL|         Sic|\n",
            "| 10|      NULL|   Altree|paltree9@dropbox.com|Female|197594|1975-06-21 23:27:12|72533.37|         Alt|\n",
            "| 11|      Coop|    Richt|   crichta@sogou.com|  Male| 19964|1939-02-13 16:18:24|56973.41|         Ric|\n",
            "| 12|  Giuseppe|  Scimoni|gscimonib@craigsl...|  Male|176531|1967-04-06 07:23:03|    NULL|         Sci|\n",
            "| 13|    Lovell|  Iorizzo|liorizzoc@cpanel.net|  Male|189150|1949-06-06 16:25:20| 41474.8|         Ior|\n",
            "| 14|       Deb|    Mogra|   dmograd@bbc.co.uk|Female|197829|1997-07-27 14:55:52|46528.57|         Mog|\n",
            "| 15|  Hastings| Jelliman|hjellimane@histat...|  Male|179296|1915-07-05 07:02:06|80194.64|         Jel|\n",
            "| 16|     Josee|   Burnep|    jburnepf@php.net|  NULL| 78569|1923-11-08 20:59:14|88925.13|         Bur|\n",
            "| 17|     Gilly|   Fownes|gfownesg@redcross...|Female|  2527|1946-07-30 21:10:57|    NULL|         Fow|\n",
            "| 18|      NULL|Schruyers|lschruyersh@desde...|  Male|109936|1979-06-16 01:12:48|37243.09|         Sch|\n",
            "| 19|     Maris|Chatelain| mchatelaini@unc.edu|Female|190047|2008-01-11 17:16:21|76469.65|         Cha|\n",
            "| 20|    Casper|  Aughtie|    caughtiej@vk.com|  Male| 38689|1982-04-06 05:13:49| 80404.8|         Aug|\n",
            "+---+----------+---------+--------------------+------+------+-------------------+--------+------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ],
      "execution_count": 5
    },
    {
      "cell_type": "markdown",
      "source": [
        "<b> What is the domain name of each employee's email address, so that their emails can be filtered and routed to the correct department?"
      ],
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "showTitle": false,
          "cellMetadata": {},
          "nuid": "7596956d-789a-43c8-b3e7-470db0f35fca",
          "inputWidgets": {},
          "title": ""
        },
        "id": "x-TstzobzCmf"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Write your code using the Spark Function\n",
        "\n",
        "df2 = df1.withColumn(\"Domain_Name\",split(df[\"email\"],\"@\").getItem(1))\n",
        "df2.show()\n",
        "\n"
      ],
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "showTitle": false,
          "cellMetadata": {},
          "nuid": "b2bc2ab9-4479-4ed0-a510-b0ec1455a9a8",
          "inputWidgets": {},
          "title": ""
        },
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "8tRUol4AzCmg",
        "outputId": "ac99c57d-88e5-4967-983e-d13cc64a787a"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+---+----------+---------+--------------------+------+------+-------------------+--------+------------+--------------------+\n",
            "| id|first_name|last_name|               email|gender|salary|       creationDate|   bonus|Abbreviation|         Domain_Name|\n",
            "+---+----------+---------+--------------------+------+------+-------------------+--------+------------+--------------------+\n",
            "|  1|    Valene|   Ingley|vingley0@livejour...|Female| 44104|1957-09-09 16:44:40|    NULL|         Ing|     livejournal.com|\n",
            "|  2|  Lynnelle|    Hurll| lhurll1@answers.com|Female|112411|1907-05-10 17:38:56|    NULL|         Hur|         answers.com|\n",
            "|  3|   Miranda|    Train|   mtrain2@imgur.com|Female| 91073|1941-01-24 16:05:23|12875.54|         Tra|           imgur.com|\n",
            "|  4|    Dulsea|     Foss|dfoss3@dagondesig...|Female|193291|1942-05-09 20:59:39|    NULL|         Fos|     dagondesign.com|\n",
            "|  5|    Anatol|  Dunklee| adunklee4@google.de|  Male| 22175|1950-07-26 16:28:00| 1432.12|         Dun|           google.de|\n",
            "|  6|     Baily|   Antony| bantony5@sfgate.com|  Male|127337|1913-10-14 11:25:33|    NULL|         Ant|          sfgate.com|\n",
            "|  7|    Eunice|   Cardus|ecardus6@scientif...|Female|136574|1901-11-02 15:09:47|38676.94|         Car|scientificamerica...|\n",
            "|  8|  Aubrette|  Lippett| alippett7@nifty.com|Female|165713|1919-12-16 08:20:42|60150.66|         Lip|           nifty.com|\n",
            "|  9|   Sibylla|Sickamore|ssickamore8@faceb...|Female|107243|2020-03-15 15:00:30|    NULL|         Sic|        facebook.com|\n",
            "| 10|      NULL|   Altree|paltree9@dropbox.com|Female|197594|1975-06-21 23:27:12|72533.37|         Alt|         dropbox.com|\n",
            "| 11|      Coop|    Richt|   crichta@sogou.com|  Male| 19964|1939-02-13 16:18:24|56973.41|         Ric|           sogou.com|\n",
            "| 12|  Giuseppe|  Scimoni|gscimonib@craigsl...|  Male|176531|1967-04-06 07:23:03|    NULL|         Sci|      craigslist.org|\n",
            "| 13|    Lovell|  Iorizzo|liorizzoc@cpanel.net|  Male|189150|1949-06-06 16:25:20| 41474.8|         Ior|          cpanel.net|\n",
            "| 14|       Deb|    Mogra|   dmograd@bbc.co.uk|Female|197829|1997-07-27 14:55:52|46528.57|         Mog|           bbc.co.uk|\n",
            "| 15|  Hastings| Jelliman|hjellimane@histat...|  Male|179296|1915-07-05 07:02:06|80194.64|         Jel|         histats.com|\n",
            "| 16|     Josee|   Burnep|    jburnepf@php.net|  NULL| 78569|1923-11-08 20:59:14|88925.13|         Bur|             php.net|\n",
            "| 17|     Gilly|   Fownes|gfownesg@redcross...|Female|  2527|1946-07-30 21:10:57|    NULL|         Fow|        redcross.org|\n",
            "| 18|      NULL|Schruyers|lschruyersh@desde...|  Male|109936|1979-06-16 01:12:48|37243.09|         Sch|           desdev.cn|\n",
            "| 19|     Maris|Chatelain| mchatelaini@unc.edu|Female|190047|2008-01-11 17:16:21|76469.65|         Cha|             unc.edu|\n",
            "| 20|    Casper|  Aughtie|    caughtiej@vk.com|  Male| 38689|1982-04-06 05:13:49| 80404.8|         Aug|              vk.com|\n",
            "+---+----------+---------+--------------------+------+------+-------------------+--------+------------+--------------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ],
      "execution_count": 6
    },
    {
      "cell_type": "markdown",
      "source": [
        "<b> In an effort to protect their privacy, what is each employee's name spelled backwards?"
      ],
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "showTitle": false,
          "cellMetadata": {},
          "nuid": "fdb6949e-fbda-47a0-b9e3-9b5aa8e5788b",
          "inputWidgets": {},
          "title": ""
        },
        "id": "Khj6VxNczCmg"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Write your code using the Spark Function\n",
        "\n",
        "df3 = df2.withColumn(\"Reverse_name\", concat(reverse(df[\"First_name\"]),lit(\" \"),reverse(df[\"Last_name\"])))\n",
        "df3.show( )"
      ],
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "showTitle": false,
          "cellMetadata": {},
          "nuid": "d8172873-7db2-4382-850c-569ba2491ad2",
          "inputWidgets": {},
          "title": ""
        },
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "r6u43XJczCmg",
        "outputId": "b3705e8c-7fd0-44f5-f6f5-c1fd1986bc1d"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+---+----------+---------+--------------------+------+------+-------------------+--------+------------+--------------------+-----------------+\n",
            "| id|first_name|last_name|               email|gender|salary|       creationDate|   bonus|Abbreviation|         Domain_Name|     Reverse_name|\n",
            "+---+----------+---------+--------------------+------+------+-------------------+--------+------------+--------------------+-----------------+\n",
            "|  1|    Valene|   Ingley|vingley0@livejour...|Female| 44104|1957-09-09 16:44:40|    NULL|         Ing|     livejournal.com|    enelaV yelgnI|\n",
            "|  2|  Lynnelle|    Hurll| lhurll1@answers.com|Female|112411|1907-05-10 17:38:56|    NULL|         Hur|         answers.com|   ellennyL llruH|\n",
            "|  3|   Miranda|    Train|   mtrain2@imgur.com|Female| 91073|1941-01-24 16:05:23|12875.54|         Tra|           imgur.com|    adnariM niarT|\n",
            "|  4|    Dulsea|     Foss|dfoss3@dagondesig...|Female|193291|1942-05-09 20:59:39|    NULL|         Fos|     dagondesign.com|      aesluD ssoF|\n",
            "|  5|    Anatol|  Dunklee| adunklee4@google.de|  Male| 22175|1950-07-26 16:28:00| 1432.12|         Dun|           google.de|   lotanA eelknuD|\n",
            "|  6|     Baily|   Antony| bantony5@sfgate.com|  Male|127337|1913-10-14 11:25:33|    NULL|         Ant|          sfgate.com|     yliaB ynotnA|\n",
            "|  7|    Eunice|   Cardus|ecardus6@scientif...|Female|136574|1901-11-02 15:09:47|38676.94|         Car|scientificamerica...|    ecinuE sudraC|\n",
            "|  8|  Aubrette|  Lippett| alippett7@nifty.com|Female|165713|1919-12-16 08:20:42|60150.66|         Lip|           nifty.com| etterbuA tteppiL|\n",
            "|  9|   Sibylla|Sickamore|ssickamore8@faceb...|Female|107243|2020-03-15 15:00:30|    NULL|         Sic|        facebook.com|allybiS eromakciS|\n",
            "| 10|      NULL|   Altree|paltree9@dropbox.com|Female|197594|1975-06-21 23:27:12|72533.37|         Alt|         dropbox.com|             NULL|\n",
            "| 11|      Coop|    Richt|   crichta@sogou.com|  Male| 19964|1939-02-13 16:18:24|56973.41|         Ric|           sogou.com|       pooC thciR|\n",
            "| 12|  Giuseppe|  Scimoni|gscimonib@craigsl...|  Male|176531|1967-04-06 07:23:03|    NULL|         Sci|      craigslist.org| eppesuiG inomicS|\n",
            "| 13|    Lovell|  Iorizzo|liorizzoc@cpanel.net|  Male|189150|1949-06-06 16:25:20| 41474.8|         Ior|          cpanel.net|   llevoL ozziroI|\n",
            "| 14|       Deb|    Mogra|   dmograd@bbc.co.uk|Female|197829|1997-07-27 14:55:52|46528.57|         Mog|           bbc.co.uk|        beD argoM|\n",
            "| 15|  Hastings| Jelliman|hjellimane@histat...|  Male|179296|1915-07-05 07:02:06|80194.64|         Jel|         histats.com|sgnitsaH namilleJ|\n",
            "| 16|     Josee|   Burnep|    jburnepf@php.net|  NULL| 78569|1923-11-08 20:59:14|88925.13|         Bur|             php.net|     eesoJ penruB|\n",
            "| 17|     Gilly|   Fownes|gfownesg@redcross...|Female|  2527|1946-07-30 21:10:57|    NULL|         Fow|        redcross.org|     ylliG senwoF|\n",
            "| 18|      NULL|Schruyers|lschruyersh@desde...|  Male|109936|1979-06-16 01:12:48|37243.09|         Sch|           desdev.cn|             NULL|\n",
            "| 19|     Maris|Chatelain| mchatelaini@unc.edu|Female|190047|2008-01-11 17:16:21|76469.65|         Cha|             unc.edu|  siraM nialetahC|\n",
            "| 20|    Casper|  Aughtie|    caughtiej@vk.com|  Male| 38689|1982-04-06 05:13:49| 80404.8|         Aug|              vk.com|   repsaC eithguA|\n",
            "+---+----------+---------+--------------------+------+------+-------------------+--------+------------+--------------------+-----------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ],
      "execution_count": 7
    },
    {
      "cell_type": "markdown",
      "source": [
        "<b> In order to make their email addresses harder to guess, what is each employee's email address spelled backwards?"
      ],
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "showTitle": false,
          "cellMetadata": {},
          "nuid": "5a7155e5-4aa6-4e30-b244-fc6ad8d3fed8",
          "inputWidgets": {},
          "title": ""
        },
        "id": "CQlq40-TzCmg"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Write your code using the Spark Function\n",
        "df4 = df3.withColumn(\"Reverse_email\", concat((reverse(split(df[\"email\"],\"@\").getItem(0))),lit(\"@\"),split(df[\"email\"],\"@\").getItem(1)))\n",
        "df4.show()"
      ],
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "showTitle": false,
          "cellMetadata": {},
          "nuid": "b10ce1ed-e48d-493d-8148-63acadce18e6",
          "inputWidgets": {},
          "title": ""
        },
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "rhZA9mp5zCmg",
        "outputId": "825ee6b5-7415-45cb-98a6-83a6db86650c"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+---+----------+---------+--------------------+------+------+-------------------+--------+------------+--------------------+-----------------+--------------------+\n",
            "| id|first_name|last_name|               email|gender|salary|       creationDate|   bonus|Abbreviation|         Domain_Name|     Reverse_name|       Reverse_email|\n",
            "+---+----------+---------+--------------------+------+------+-------------------+--------+------------+--------------------+-----------------+--------------------+\n",
            "|  1|    Valene|   Ingley|vingley0@livejour...|Female| 44104|1957-09-09 16:44:40|    NULL|         Ing|     livejournal.com|    enelaV yelgnI|0yelgniv@livejour...|\n",
            "|  2|  Lynnelle|    Hurll| lhurll1@answers.com|Female|112411|1907-05-10 17:38:56|    NULL|         Hur|         answers.com|   ellennyL llruH| 1llruhl@answers.com|\n",
            "|  3|   Miranda|    Train|   mtrain2@imgur.com|Female| 91073|1941-01-24 16:05:23|12875.54|         Tra|           imgur.com|    adnariM niarT|   2niartm@imgur.com|\n",
            "|  4|    Dulsea|     Foss|dfoss3@dagondesig...|Female|193291|1942-05-09 20:59:39|    NULL|         Fos|     dagondesign.com|      aesluD ssoF|3ssofd@dagondesig...|\n",
            "|  5|    Anatol|  Dunklee| adunklee4@google.de|  Male| 22175|1950-07-26 16:28:00| 1432.12|         Dun|           google.de|   lotanA eelknuD| 4eelknuda@google.de|\n",
            "|  6|     Baily|   Antony| bantony5@sfgate.com|  Male|127337|1913-10-14 11:25:33|    NULL|         Ant|          sfgate.com|     yliaB ynotnA| 5ynotnab@sfgate.com|\n",
            "|  7|    Eunice|   Cardus|ecardus6@scientif...|Female|136574|1901-11-02 15:09:47|38676.94|         Car|scientificamerica...|    ecinuE sudraC|6sudrace@scientif...|\n",
            "|  8|  Aubrette|  Lippett| alippett7@nifty.com|Female|165713|1919-12-16 08:20:42|60150.66|         Lip|           nifty.com| etterbuA tteppiL| 7tteppila@nifty.com|\n",
            "|  9|   Sibylla|Sickamore|ssickamore8@faceb...|Female|107243|2020-03-15 15:00:30|    NULL|         Sic|        facebook.com|allybiS eromakciS|8eromakciss@faceb...|\n",
            "| 10|      NULL|   Altree|paltree9@dropbox.com|Female|197594|1975-06-21 23:27:12|72533.37|         Alt|         dropbox.com|             NULL|9eertlap@dropbox.com|\n",
            "| 11|      Coop|    Richt|   crichta@sogou.com|  Male| 19964|1939-02-13 16:18:24|56973.41|         Ric|           sogou.com|       pooC thciR|   athcirc@sogou.com|\n",
            "| 12|  Giuseppe|  Scimoni|gscimonib@craigsl...|  Male|176531|1967-04-06 07:23:03|    NULL|         Sci|      craigslist.org| eppesuiG inomicS|binomicsg@craigsl...|\n",
            "| 13|    Lovell|  Iorizzo|liorizzoc@cpanel.net|  Male|189150|1949-06-06 16:25:20| 41474.8|         Ior|          cpanel.net|   llevoL ozziroI|cozziroil@cpanel.net|\n",
            "| 14|       Deb|    Mogra|   dmograd@bbc.co.uk|Female|197829|1997-07-27 14:55:52|46528.57|         Mog|           bbc.co.uk|        beD argoM|   dargomd@bbc.co.uk|\n",
            "| 15|  Hastings| Jelliman|hjellimane@histat...|  Male|179296|1915-07-05 07:02:06|80194.64|         Jel|         histats.com|sgnitsaH namilleJ|enamillejh@histat...|\n",
            "| 16|     Josee|   Burnep|    jburnepf@php.net|  NULL| 78569|1923-11-08 20:59:14|88925.13|         Bur|             php.net|     eesoJ penruB|    fpenrubj@php.net|\n",
            "| 17|     Gilly|   Fownes|gfownesg@redcross...|Female|  2527|1946-07-30 21:10:57|    NULL|         Fow|        redcross.org|     ylliG senwoF|gsenwofg@redcross...|\n",
            "| 18|      NULL|Schruyers|lschruyersh@desde...|  Male|109936|1979-06-16 01:12:48|37243.09|         Sch|           desdev.cn|             NULL|hsreyurhcsl@desde...|\n",
            "| 19|     Maris|Chatelain| mchatelaini@unc.edu|Female|190047|2008-01-11 17:16:21|76469.65|         Cha|             unc.edu|  siraM nialetahC| inialetahcm@unc.edu|\n",
            "| 20|    Casper|  Aughtie|    caughtiej@vk.com|  Male| 38689|1982-04-06 05:13:49| 80404.8|         Aug|              vk.com|   repsaC eithguA|    jeithguac@vk.com|\n",
            "+---+----------+---------+--------------------+------+------+-------------------+--------+------------+--------------------+-----------------+--------------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ],
      "execution_count": 8
    },
    {
      "cell_type": "markdown",
      "source": [
        "<b> What is each employee's first name, repeated twice, to improve personalization of emails and messages?"
      ],
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "showTitle": false,
          "cellMetadata": {},
          "nuid": "15178c86-7b61-4b25-a42e-850363b01dd6",
          "inputWidgets": {},
          "title": ""
        },
        "id": "WEqAFMXlzCmg"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Write your code using the Spark Function\n",
        "\n",
        "df5 = df4.withColumn(\"Repeated_name\", concat(df4[\"first_name\"],lit(\" \"),df4[\"first_name\"]))\n",
        "df5.show()"
      ],
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "showTitle": false,
          "cellMetadata": {},
          "nuid": "c8fd7215-3afd-4fd9-9c43-51ca81c25aa2",
          "inputWidgets": {},
          "title": ""
        },
        "id": "hfItY132zCmh",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "9688b3be-b52a-4fa6-c531-07d5829eb6e7"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+---+----------+---------+--------------------+------+------+-------------------+--------+------------+--------------------+-----------------+--------------------+-----------------+\n",
            "| id|first_name|last_name|               email|gender|salary|       creationDate|   bonus|Abbreviation|         Domain_Name|     Reverse_name|       Reverse_email|    Repeated_name|\n",
            "+---+----------+---------+--------------------+------+------+-------------------+--------+------------+--------------------+-----------------+--------------------+-----------------+\n",
            "|  1|    Valene|   Ingley|vingley0@livejour...|Female| 44104|1957-09-09 16:44:40|    NULL|         Ing|     livejournal.com|    enelaV yelgnI|0yelgniv@livejour...|    Valene Valene|\n",
            "|  2|  Lynnelle|    Hurll| lhurll1@answers.com|Female|112411|1907-05-10 17:38:56|    NULL|         Hur|         answers.com|   ellennyL llruH| 1llruhl@answers.com|Lynnelle Lynnelle|\n",
            "|  3|   Miranda|    Train|   mtrain2@imgur.com|Female| 91073|1941-01-24 16:05:23|12875.54|         Tra|           imgur.com|    adnariM niarT|   2niartm@imgur.com|  Miranda Miranda|\n",
            "|  4|    Dulsea|     Foss|dfoss3@dagondesig...|Female|193291|1942-05-09 20:59:39|    NULL|         Fos|     dagondesign.com|      aesluD ssoF|3ssofd@dagondesig...|    Dulsea Dulsea|\n",
            "|  5|    Anatol|  Dunklee| adunklee4@google.de|  Male| 22175|1950-07-26 16:28:00| 1432.12|         Dun|           google.de|   lotanA eelknuD| 4eelknuda@google.de|    Anatol Anatol|\n",
            "|  6|     Baily|   Antony| bantony5@sfgate.com|  Male|127337|1913-10-14 11:25:33|    NULL|         Ant|          sfgate.com|     yliaB ynotnA| 5ynotnab@sfgate.com|      Baily Baily|\n",
            "|  7|    Eunice|   Cardus|ecardus6@scientif...|Female|136574|1901-11-02 15:09:47|38676.94|         Car|scientificamerica...|    ecinuE sudraC|6sudrace@scientif...|    Eunice Eunice|\n",
            "|  8|  Aubrette|  Lippett| alippett7@nifty.com|Female|165713|1919-12-16 08:20:42|60150.66|         Lip|           nifty.com| etterbuA tteppiL| 7tteppila@nifty.com|Aubrette Aubrette|\n",
            "|  9|   Sibylla|Sickamore|ssickamore8@faceb...|Female|107243|2020-03-15 15:00:30|    NULL|         Sic|        facebook.com|allybiS eromakciS|8eromakciss@faceb...|  Sibylla Sibylla|\n",
            "| 10|      NULL|   Altree|paltree9@dropbox.com|Female|197594|1975-06-21 23:27:12|72533.37|         Alt|         dropbox.com|             NULL|9eertlap@dropbox.com|             NULL|\n",
            "| 11|      Coop|    Richt|   crichta@sogou.com|  Male| 19964|1939-02-13 16:18:24|56973.41|         Ric|           sogou.com|       pooC thciR|   athcirc@sogou.com|        Coop Coop|\n",
            "| 12|  Giuseppe|  Scimoni|gscimonib@craigsl...|  Male|176531|1967-04-06 07:23:03|    NULL|         Sci|      craigslist.org| eppesuiG inomicS|binomicsg@craigsl...|Giuseppe Giuseppe|\n",
            "| 13|    Lovell|  Iorizzo|liorizzoc@cpanel.net|  Male|189150|1949-06-06 16:25:20| 41474.8|         Ior|          cpanel.net|   llevoL ozziroI|cozziroil@cpanel.net|    Lovell Lovell|\n",
            "| 14|       Deb|    Mogra|   dmograd@bbc.co.uk|Female|197829|1997-07-27 14:55:52|46528.57|         Mog|           bbc.co.uk|        beD argoM|   dargomd@bbc.co.uk|          Deb Deb|\n",
            "| 15|  Hastings| Jelliman|hjellimane@histat...|  Male|179296|1915-07-05 07:02:06|80194.64|         Jel|         histats.com|sgnitsaH namilleJ|enamillejh@histat...|Hastings Hastings|\n",
            "| 16|     Josee|   Burnep|    jburnepf@php.net|  NULL| 78569|1923-11-08 20:59:14|88925.13|         Bur|             php.net|     eesoJ penruB|    fpenrubj@php.net|      Josee Josee|\n",
            "| 17|     Gilly|   Fownes|gfownesg@redcross...|Female|  2527|1946-07-30 21:10:57|    NULL|         Fow|        redcross.org|     ylliG senwoF|gsenwofg@redcross...|      Gilly Gilly|\n",
            "| 18|      NULL|Schruyers|lschruyersh@desde...|  Male|109936|1979-06-16 01:12:48|37243.09|         Sch|           desdev.cn|             NULL|hsreyurhcsl@desde...|             NULL|\n",
            "| 19|     Maris|Chatelain| mchatelaini@unc.edu|Female|190047|2008-01-11 17:16:21|76469.65|         Cha|             unc.edu|  siraM nialetahC| inialetahcm@unc.edu|      Maris Maris|\n",
            "| 20|    Casper|  Aughtie|    caughtiej@vk.com|  Male| 38689|1982-04-06 05:13:49| 80404.8|         Aug|              vk.com|   repsaC eithguA|    jeithguac@vk.com|    Casper Casper|\n",
            "+---+----------+---------+--------------------+------+------+-------------------+--------+------------+--------------------+-----------------+--------------------+-----------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ],
      "execution_count": 14
    },
    {
      "cell_type": "markdown",
      "source": [
        "<b> What is the country code of each employee's email domain, which can help identify global market opportunities?"
      ],
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "showTitle": false,
          "cellMetadata": {},
          "nuid": "ca3eba0e-0df9-42b8-bc99-39867c2a7e82",
          "inputWidgets": {},
          "title": ""
        },
        "id": "rHX-GA3HzCmh"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Write your code using the Spark Function\n",
        "\n",
        "df6 = df5.withColumn(\"Country_code\", split(df[\"email\"],\"\\\\.\").getItem(1))\n",
        "df6.show()"
      ],
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "showTitle": false,
          "cellMetadata": {},
          "nuid": "0b3a65e9-58dc-43a4-b926-31bae3f440b4",
          "inputWidgets": {},
          "title": ""
        },
        "id": "Itox1RFBzCmh",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "b6518fd1-8e97-411f-87c9-d1664f8bbd60"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+---+----------+---------+--------------------+------+------+-------------------+--------+------------+--------------------+-----------------+--------------------+-----------------+------------+\n",
            "| id|first_name|last_name|               email|gender|salary|       creationDate|   bonus|Abbreviation|         Domain_Name|     Reverse_name|       Reverse_email|    Repeated_name|Country_code|\n",
            "+---+----------+---------+--------------------+------+------+-------------------+--------+------------+--------------------+-----------------+--------------------+-----------------+------------+\n",
            "|  1|    Valene|   Ingley|vingley0@livejour...|Female| 44104|1957-09-09 16:44:40|    NULL|         Ing|     livejournal.com|    enelaV yelgnI|0yelgniv@livejour...|    Valene Valene|         com|\n",
            "|  2|  Lynnelle|    Hurll| lhurll1@answers.com|Female|112411|1907-05-10 17:38:56|    NULL|         Hur|         answers.com|   ellennyL llruH| 1llruhl@answers.com|Lynnelle Lynnelle|         com|\n",
            "|  3|   Miranda|    Train|   mtrain2@imgur.com|Female| 91073|1941-01-24 16:05:23|12875.54|         Tra|           imgur.com|    adnariM niarT|   2niartm@imgur.com|  Miranda Miranda|         com|\n",
            "|  4|    Dulsea|     Foss|dfoss3@dagondesig...|Female|193291|1942-05-09 20:59:39|    NULL|         Fos|     dagondesign.com|      aesluD ssoF|3ssofd@dagondesig...|    Dulsea Dulsea|         com|\n",
            "|  5|    Anatol|  Dunklee| adunklee4@google.de|  Male| 22175|1950-07-26 16:28:00| 1432.12|         Dun|           google.de|   lotanA eelknuD| 4eelknuda@google.de|    Anatol Anatol|          de|\n",
            "|  6|     Baily|   Antony| bantony5@sfgate.com|  Male|127337|1913-10-14 11:25:33|    NULL|         Ant|          sfgate.com|     yliaB ynotnA| 5ynotnab@sfgate.com|      Baily Baily|         com|\n",
            "|  7|    Eunice|   Cardus|ecardus6@scientif...|Female|136574|1901-11-02 15:09:47|38676.94|         Car|scientificamerica...|    ecinuE sudraC|6sudrace@scientif...|    Eunice Eunice|         com|\n",
            "|  8|  Aubrette|  Lippett| alippett7@nifty.com|Female|165713|1919-12-16 08:20:42|60150.66|         Lip|           nifty.com| etterbuA tteppiL| 7tteppila@nifty.com|Aubrette Aubrette|         com|\n",
            "|  9|   Sibylla|Sickamore|ssickamore8@faceb...|Female|107243|2020-03-15 15:00:30|    NULL|         Sic|        facebook.com|allybiS eromakciS|8eromakciss@faceb...|  Sibylla Sibylla|         com|\n",
            "| 10|      NULL|   Altree|paltree9@dropbox.com|Female|197594|1975-06-21 23:27:12|72533.37|         Alt|         dropbox.com|             NULL|9eertlap@dropbox.com|             NULL|         com|\n",
            "| 11|      Coop|    Richt|   crichta@sogou.com|  Male| 19964|1939-02-13 16:18:24|56973.41|         Ric|           sogou.com|       pooC thciR|   athcirc@sogou.com|        Coop Coop|         com|\n",
            "| 12|  Giuseppe|  Scimoni|gscimonib@craigsl...|  Male|176531|1967-04-06 07:23:03|    NULL|         Sci|      craigslist.org| eppesuiG inomicS|binomicsg@craigsl...|Giuseppe Giuseppe|         org|\n",
            "| 13|    Lovell|  Iorizzo|liorizzoc@cpanel.net|  Male|189150|1949-06-06 16:25:20| 41474.8|         Ior|          cpanel.net|   llevoL ozziroI|cozziroil@cpanel.net|    Lovell Lovell|         net|\n",
            "| 14|       Deb|    Mogra|   dmograd@bbc.co.uk|Female|197829|1997-07-27 14:55:52|46528.57|         Mog|           bbc.co.uk|        beD argoM|   dargomd@bbc.co.uk|          Deb Deb|          co|\n",
            "| 15|  Hastings| Jelliman|hjellimane@histat...|  Male|179296|1915-07-05 07:02:06|80194.64|         Jel|         histats.com|sgnitsaH namilleJ|enamillejh@histat...|Hastings Hastings|         com|\n",
            "| 16|     Josee|   Burnep|    jburnepf@php.net|  NULL| 78569|1923-11-08 20:59:14|88925.13|         Bur|             php.net|     eesoJ penruB|    fpenrubj@php.net|      Josee Josee|         net|\n",
            "| 17|     Gilly|   Fownes|gfownesg@redcross...|Female|  2527|1946-07-30 21:10:57|    NULL|         Fow|        redcross.org|     ylliG senwoF|gsenwofg@redcross...|      Gilly Gilly|         org|\n",
            "| 18|      NULL|Schruyers|lschruyersh@desde...|  Male|109936|1979-06-16 01:12:48|37243.09|         Sch|           desdev.cn|             NULL|hsreyurhcsl@desde...|             NULL|          cn|\n",
            "| 19|     Maris|Chatelain| mchatelaini@unc.edu|Female|190047|2008-01-11 17:16:21|76469.65|         Cha|             unc.edu|  siraM nialetahC| inialetahcm@unc.edu|      Maris Maris|         edu|\n",
            "| 20|    Casper|  Aughtie|    caughtiej@vk.com|  Male| 38689|1982-04-06 05:13:49| 80404.8|         Aug|              vk.com|   repsaC eithguA|    jeithguac@vk.com|    Casper Casper|         com|\n",
            "+---+----------+---------+--------------------+------+------+-------------------+--------+------------+--------------------+-----------------+--------------------+-----------------+------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ],
      "execution_count": 34
    },
    {
      "cell_type": "markdown",
      "source": [
        "<b> What is the position of the first occurrence of a dot ('.') character in each employee's email address, which can be used to standardize email addresses in a database?"
      ],
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "showTitle": false,
          "cellMetadata": {},
          "nuid": "6db5e71a-f747-43fc-8834-1c348a80a44b",
          "inputWidgets": {},
          "title": ""
        },
        "id": "3D6PtWX2zCmh"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Write your code using the Spark Function\n",
        "\n",
        "df7 =df6.withColumn(\"Position_of_dot\", instr(df[\"email\"],\".\"))\n",
        "df7.show()"
      ],
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "showTitle": false,
          "cellMetadata": {},
          "nuid": "0f13f23e-65f5-443a-86cf-b28f16fa7389",
          "inputWidgets": {},
          "title": ""
        },
        "id": "4Xsyx1Z8zCmh",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "d41cfadb-4f78-4919-80c7-c6d13538e88c"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+---+----------+---------+--------------------+------+------+-------------------+--------+------------+--------------------+-----------------+--------------------+-----------------+------------+---------------+\n",
            "| id|first_name|last_name|               email|gender|salary|       creationDate|   bonus|Abbreviation|         Domain_Name|     Reverse_name|       Reverse_email|    Repeated_name|Country_code|Position_of_dot|\n",
            "+---+----------+---------+--------------------+------+------+-------------------+--------+------------+--------------------+-----------------+--------------------+-----------------+------------+---------------+\n",
            "|  1|    Valene|   Ingley|vingley0@livejour...|Female| 44104|1957-09-09 16:44:40|    NULL|         Ing|     livejournal.com|    enelaV yelgnI|0yelgniv@livejour...|    Valene Valene|         com|             21|\n",
            "|  2|  Lynnelle|    Hurll| lhurll1@answers.com|Female|112411|1907-05-10 17:38:56|    NULL|         Hur|         answers.com|   ellennyL llruH| 1llruhl@answers.com|Lynnelle Lynnelle|         com|             16|\n",
            "|  3|   Miranda|    Train|   mtrain2@imgur.com|Female| 91073|1941-01-24 16:05:23|12875.54|         Tra|           imgur.com|    adnariM niarT|   2niartm@imgur.com|  Miranda Miranda|         com|             14|\n",
            "|  4|    Dulsea|     Foss|dfoss3@dagondesig...|Female|193291|1942-05-09 20:59:39|    NULL|         Fos|     dagondesign.com|      aesluD ssoF|3ssofd@dagondesig...|    Dulsea Dulsea|         com|             19|\n",
            "|  5|    Anatol|  Dunklee| adunklee4@google.de|  Male| 22175|1950-07-26 16:28:00| 1432.12|         Dun|           google.de|   lotanA eelknuD| 4eelknuda@google.de|    Anatol Anatol|          de|             17|\n",
            "|  6|     Baily|   Antony| bantony5@sfgate.com|  Male|127337|1913-10-14 11:25:33|    NULL|         Ant|          sfgate.com|     yliaB ynotnA| 5ynotnab@sfgate.com|      Baily Baily|         com|             16|\n",
            "|  7|    Eunice|   Cardus|ecardus6@scientif...|Female|136574|1901-11-02 15:09:47|38676.94|         Car|scientificamerica...|    ecinuE sudraC|6sudrace@scientif...|    Eunice Eunice|         com|             28|\n",
            "|  8|  Aubrette|  Lippett| alippett7@nifty.com|Female|165713|1919-12-16 08:20:42|60150.66|         Lip|           nifty.com| etterbuA tteppiL| 7tteppila@nifty.com|Aubrette Aubrette|         com|             16|\n",
            "|  9|   Sibylla|Sickamore|ssickamore8@faceb...|Female|107243|2020-03-15 15:00:30|    NULL|         Sic|        facebook.com|allybiS eromakciS|8eromakciss@faceb...|  Sibylla Sibylla|         com|             21|\n",
            "| 10|      NULL|   Altree|paltree9@dropbox.com|Female|197594|1975-06-21 23:27:12|72533.37|         Alt|         dropbox.com|             NULL|9eertlap@dropbox.com|             NULL|         com|             17|\n",
            "| 11|      Coop|    Richt|   crichta@sogou.com|  Male| 19964|1939-02-13 16:18:24|56973.41|         Ric|           sogou.com|       pooC thciR|   athcirc@sogou.com|        Coop Coop|         com|             14|\n",
            "| 12|  Giuseppe|  Scimoni|gscimonib@craigsl...|  Male|176531|1967-04-06 07:23:03|    NULL|         Sci|      craigslist.org| eppesuiG inomicS|binomicsg@craigsl...|Giuseppe Giuseppe|         org|             21|\n",
            "| 13|    Lovell|  Iorizzo|liorizzoc@cpanel.net|  Male|189150|1949-06-06 16:25:20| 41474.8|         Ior|          cpanel.net|   llevoL ozziroI|cozziroil@cpanel.net|    Lovell Lovell|         net|             17|\n",
            "| 14|       Deb|    Mogra|   dmograd@bbc.co.uk|Female|197829|1997-07-27 14:55:52|46528.57|         Mog|           bbc.co.uk|        beD argoM|   dargomd@bbc.co.uk|          Deb Deb|          co|             12|\n",
            "| 15|  Hastings| Jelliman|hjellimane@histat...|  Male|179296|1915-07-05 07:02:06|80194.64|         Jel|         histats.com|sgnitsaH namilleJ|enamillejh@histat...|Hastings Hastings|         com|             19|\n",
            "| 16|     Josee|   Burnep|    jburnepf@php.net|  NULL| 78569|1923-11-08 20:59:14|88925.13|         Bur|             php.net|     eesoJ penruB|    fpenrubj@php.net|      Josee Josee|         net|             13|\n",
            "| 17|     Gilly|   Fownes|gfownesg@redcross...|Female|  2527|1946-07-30 21:10:57|    NULL|         Fow|        redcross.org|     ylliG senwoF|gsenwofg@redcross...|      Gilly Gilly|         org|             18|\n",
            "| 18|      NULL|Schruyers|lschruyersh@desde...|  Male|109936|1979-06-16 01:12:48|37243.09|         Sch|           desdev.cn|             NULL|hsreyurhcsl@desde...|             NULL|          cn|             19|\n",
            "| 19|     Maris|Chatelain| mchatelaini@unc.edu|Female|190047|2008-01-11 17:16:21|76469.65|         Cha|             unc.edu|  siraM nialetahC| inialetahcm@unc.edu|      Maris Maris|         edu|             16|\n",
            "| 20|    Casper|  Aughtie|    caughtiej@vk.com|  Male| 38689|1982-04-06 05:13:49| 80404.8|         Aug|              vk.com|   repsaC eithguA|    jeithguac@vk.com|    Casper Casper|         com|             13|\n",
            "+---+----------+---------+--------------------+------+------+-------------------+--------+------------+--------------------+-----------------+--------------------+-----------------+------------+---------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ],
      "execution_count": 37
    },
    {
      "cell_type": "markdown",
      "source": [
        "<b> What is the full name and email address of each employee, concatenated with a space separator, for use in email communication?"
      ],
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "showTitle": false,
          "cellMetadata": {},
          "nuid": "85b1a79d-65b9-4a95-831a-57eea6c251f9",
          "inputWidgets": {},
          "title": ""
        },
        "id": "x_MgEJ25zCmh"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Write your code using the Spark Function\n",
        "\n",
        "df8 = df7.withColumn(\"Full_Name\", concat(df[\"first_name\"],lit(\" \"),df[\"last_name\"]))\n",
        "df8.show()"
      ],
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "showTitle": false,
          "cellMetadata": {},
          "nuid": "4708e08e-0200-4e40-a9ed-87f0a3e72bc6",
          "inputWidgets": {},
          "title": ""
        },
        "id": "JAkUa1b_zCmh",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "6b0fcabf-e115-496e-fad4-f78052044bd2"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+---+----------+---------+--------------------+------+------+-------------------+--------+------------+--------------------+-----------------+--------------------+-----------------+------------+---------------+-----------------+\n",
            "| id|first_name|last_name|               email|gender|salary|       creationDate|   bonus|Abbreviation|         Domain_Name|     Reverse_name|       Reverse_email|    Repeated_name|Country_code|Position_of_dot|        Full_Name|\n",
            "+---+----------+---------+--------------------+------+------+-------------------+--------+------------+--------------------+-----------------+--------------------+-----------------+------------+---------------+-----------------+\n",
            "|  1|    Valene|   Ingley|vingley0@livejour...|Female| 44104|1957-09-09 16:44:40|    NULL|         Ing|     livejournal.com|    enelaV yelgnI|0yelgniv@livejour...|    Valene Valene|         com|             21|    Valene Ingley|\n",
            "|  2|  Lynnelle|    Hurll| lhurll1@answers.com|Female|112411|1907-05-10 17:38:56|    NULL|         Hur|         answers.com|   ellennyL llruH| 1llruhl@answers.com|Lynnelle Lynnelle|         com|             16|   Lynnelle Hurll|\n",
            "|  3|   Miranda|    Train|   mtrain2@imgur.com|Female| 91073|1941-01-24 16:05:23|12875.54|         Tra|           imgur.com|    adnariM niarT|   2niartm@imgur.com|  Miranda Miranda|         com|             14|    Miranda Train|\n",
            "|  4|    Dulsea|     Foss|dfoss3@dagondesig...|Female|193291|1942-05-09 20:59:39|    NULL|         Fos|     dagondesign.com|      aesluD ssoF|3ssofd@dagondesig...|    Dulsea Dulsea|         com|             19|      Dulsea Foss|\n",
            "|  5|    Anatol|  Dunklee| adunklee4@google.de|  Male| 22175|1950-07-26 16:28:00| 1432.12|         Dun|           google.de|   lotanA eelknuD| 4eelknuda@google.de|    Anatol Anatol|          de|             17|   Anatol Dunklee|\n",
            "|  6|     Baily|   Antony| bantony5@sfgate.com|  Male|127337|1913-10-14 11:25:33|    NULL|         Ant|          sfgate.com|     yliaB ynotnA| 5ynotnab@sfgate.com|      Baily Baily|         com|             16|     Baily Antony|\n",
            "|  7|    Eunice|   Cardus|ecardus6@scientif...|Female|136574|1901-11-02 15:09:47|38676.94|         Car|scientificamerica...|    ecinuE sudraC|6sudrace@scientif...|    Eunice Eunice|         com|             28|    Eunice Cardus|\n",
            "|  8|  Aubrette|  Lippett| alippett7@nifty.com|Female|165713|1919-12-16 08:20:42|60150.66|         Lip|           nifty.com| etterbuA tteppiL| 7tteppila@nifty.com|Aubrette Aubrette|         com|             16| Aubrette Lippett|\n",
            "|  9|   Sibylla|Sickamore|ssickamore8@faceb...|Female|107243|2020-03-15 15:00:30|    NULL|         Sic|        facebook.com|allybiS eromakciS|8eromakciss@faceb...|  Sibylla Sibylla|         com|             21|Sibylla Sickamore|\n",
            "| 10|      NULL|   Altree|paltree9@dropbox.com|Female|197594|1975-06-21 23:27:12|72533.37|         Alt|         dropbox.com|             NULL|9eertlap@dropbox.com|             NULL|         com|             17|             NULL|\n",
            "| 11|      Coop|    Richt|   crichta@sogou.com|  Male| 19964|1939-02-13 16:18:24|56973.41|         Ric|           sogou.com|       pooC thciR|   athcirc@sogou.com|        Coop Coop|         com|             14|       Coop Richt|\n",
            "| 12|  Giuseppe|  Scimoni|gscimonib@craigsl...|  Male|176531|1967-04-06 07:23:03|    NULL|         Sci|      craigslist.org| eppesuiG inomicS|binomicsg@craigsl...|Giuseppe Giuseppe|         org|             21| Giuseppe Scimoni|\n",
            "| 13|    Lovell|  Iorizzo|liorizzoc@cpanel.net|  Male|189150|1949-06-06 16:25:20| 41474.8|         Ior|          cpanel.net|   llevoL ozziroI|cozziroil@cpanel.net|    Lovell Lovell|         net|             17|   Lovell Iorizzo|\n",
            "| 14|       Deb|    Mogra|   dmograd@bbc.co.uk|Female|197829|1997-07-27 14:55:52|46528.57|         Mog|           bbc.co.uk|        beD argoM|   dargomd@bbc.co.uk|          Deb Deb|          co|             12|        Deb Mogra|\n",
            "| 15|  Hastings| Jelliman|hjellimane@histat...|  Male|179296|1915-07-05 07:02:06|80194.64|         Jel|         histats.com|sgnitsaH namilleJ|enamillejh@histat...|Hastings Hastings|         com|             19|Hastings Jelliman|\n",
            "| 16|     Josee|   Burnep|    jburnepf@php.net|  NULL| 78569|1923-11-08 20:59:14|88925.13|         Bur|             php.net|     eesoJ penruB|    fpenrubj@php.net|      Josee Josee|         net|             13|     Josee Burnep|\n",
            "| 17|     Gilly|   Fownes|gfownesg@redcross...|Female|  2527|1946-07-30 21:10:57|    NULL|         Fow|        redcross.org|     ylliG senwoF|gsenwofg@redcross...|      Gilly Gilly|         org|             18|     Gilly Fownes|\n",
            "| 18|      NULL|Schruyers|lschruyersh@desde...|  Male|109936|1979-06-16 01:12:48|37243.09|         Sch|           desdev.cn|             NULL|hsreyurhcsl@desde...|             NULL|          cn|             19|             NULL|\n",
            "| 19|     Maris|Chatelain| mchatelaini@unc.edu|Female|190047|2008-01-11 17:16:21|76469.65|         Cha|             unc.edu|  siraM nialetahC| inialetahcm@unc.edu|      Maris Maris|         edu|             16|  Maris Chatelain|\n",
            "| 20|    Casper|  Aughtie|    caughtiej@vk.com|  Male| 38689|1982-04-06 05:13:49| 80404.8|         Aug|              vk.com|   repsaC eithguA|    jeithguac@vk.com|    Casper Casper|         com|             13|   Casper Aughtie|\n",
            "+---+----------+---------+--------------------+------+------+-------------------+--------+------------+--------------------+-----------------+--------------------+-----------------+------------+---------------+-----------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ],
      "execution_count": 38
    },
    {
      "cell_type": "markdown",
      "source": [
        "<b> What is the first letter of each employee's first name in uppercase, followed by the rest of the name in lowercase, to ensure consistency and proper capitalization?"
      ],
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "showTitle": false,
          "cellMetadata": {},
          "nuid": "34faa5af-6a42-4638-860e-0a8248284e63",
          "inputWidgets": {},
          "title": ""
        },
        "id": "lzlKY0FuzCmh"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Write your code using the Spark Function\n",
        "\n",
        "df9 = df8.withColumn(\"First_Letter\", concat(initcap(substring(df7[\"first_name\"], 1, 1)),lower(substring(df7[\"first_name\"],2,len(\"first_name\")))))\n",
        "df9.show()"
      ],
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "showTitle": false,
          "cellMetadata": {},
          "nuid": "bfb43dc0-bb19-4109-bbb4-090859ce1363",
          "inputWidgets": {},
          "title": ""
        },
        "id": "gjqrE25OzCmh",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "45c7beb7-8755-4540-82f9-489eb9c58b29"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+---+----------+---------+--------------------+------+------+-------------------+--------+------------+--------------------+-----------------+--------------------+-----------------+------------+---------------+-----------------+------------+\n",
            "| id|first_name|last_name|               email|gender|salary|       creationDate|   bonus|Abbreviation|         Domain_Name|     Reverse_name|       Reverse_email|    Repeated_name|Country_code|Position_of_dot|        Full_Name|First_Letter|\n",
            "+---+----------+---------+--------------------+------+------+-------------------+--------+------------+--------------------+-----------------+--------------------+-----------------+------------+---------------+-----------------+------------+\n",
            "|  1|    Valene|   Ingley|vingley0@livejour...|Female| 44104|1957-09-09 16:44:40|    NULL|         Ing|     livejournal.com|    enelaV yelgnI|0yelgniv@livejour...|    Valene Valene|         com|             21|    Valene Ingley|      Valene|\n",
            "|  2|  Lynnelle|    Hurll| lhurll1@answers.com|Female|112411|1907-05-10 17:38:56|    NULL|         Hur|         answers.com|   ellennyL llruH| 1llruhl@answers.com|Lynnelle Lynnelle|         com|             16|   Lynnelle Hurll|    Lynnelle|\n",
            "|  3|   Miranda|    Train|   mtrain2@imgur.com|Female| 91073|1941-01-24 16:05:23|12875.54|         Tra|           imgur.com|    adnariM niarT|   2niartm@imgur.com|  Miranda Miranda|         com|             14|    Miranda Train|     Miranda|\n",
            "|  4|    Dulsea|     Foss|dfoss3@dagondesig...|Female|193291|1942-05-09 20:59:39|    NULL|         Fos|     dagondesign.com|      aesluD ssoF|3ssofd@dagondesig...|    Dulsea Dulsea|         com|             19|      Dulsea Foss|      Dulsea|\n",
            "|  5|    Anatol|  Dunklee| adunklee4@google.de|  Male| 22175|1950-07-26 16:28:00| 1432.12|         Dun|           google.de|   lotanA eelknuD| 4eelknuda@google.de|    Anatol Anatol|          de|             17|   Anatol Dunklee|      Anatol|\n",
            "|  6|     Baily|   Antony| bantony5@sfgate.com|  Male|127337|1913-10-14 11:25:33|    NULL|         Ant|          sfgate.com|     yliaB ynotnA| 5ynotnab@sfgate.com|      Baily Baily|         com|             16|     Baily Antony|       Baily|\n",
            "|  7|    Eunice|   Cardus|ecardus6@scientif...|Female|136574|1901-11-02 15:09:47|38676.94|         Car|scientificamerica...|    ecinuE sudraC|6sudrace@scientif...|    Eunice Eunice|         com|             28|    Eunice Cardus|      Eunice|\n",
            "|  8|  Aubrette|  Lippett| alippett7@nifty.com|Female|165713|1919-12-16 08:20:42|60150.66|         Lip|           nifty.com| etterbuA tteppiL| 7tteppila@nifty.com|Aubrette Aubrette|         com|             16| Aubrette Lippett|    Aubrette|\n",
            "|  9|   Sibylla|Sickamore|ssickamore8@faceb...|Female|107243|2020-03-15 15:00:30|    NULL|         Sic|        facebook.com|allybiS eromakciS|8eromakciss@faceb...|  Sibylla Sibylla|         com|             21|Sibylla Sickamore|     Sibylla|\n",
            "| 10|      NULL|   Altree|paltree9@dropbox.com|Female|197594|1975-06-21 23:27:12|72533.37|         Alt|         dropbox.com|             NULL|9eertlap@dropbox.com|             NULL|         com|             17|             NULL|        NULL|\n",
            "| 11|      Coop|    Richt|   crichta@sogou.com|  Male| 19964|1939-02-13 16:18:24|56973.41|         Ric|           sogou.com|       pooC thciR|   athcirc@sogou.com|        Coop Coop|         com|             14|       Coop Richt|        Coop|\n",
            "| 12|  Giuseppe|  Scimoni|gscimonib@craigsl...|  Male|176531|1967-04-06 07:23:03|    NULL|         Sci|      craigslist.org| eppesuiG inomicS|binomicsg@craigsl...|Giuseppe Giuseppe|         org|             21| Giuseppe Scimoni|    Giuseppe|\n",
            "| 13|    Lovell|  Iorizzo|liorizzoc@cpanel.net|  Male|189150|1949-06-06 16:25:20| 41474.8|         Ior|          cpanel.net|   llevoL ozziroI|cozziroil@cpanel.net|    Lovell Lovell|         net|             17|   Lovell Iorizzo|      Lovell|\n",
            "| 14|       Deb|    Mogra|   dmograd@bbc.co.uk|Female|197829|1997-07-27 14:55:52|46528.57|         Mog|           bbc.co.uk|        beD argoM|   dargomd@bbc.co.uk|          Deb Deb|          co|             12|        Deb Mogra|         Deb|\n",
            "| 15|  Hastings| Jelliman|hjellimane@histat...|  Male|179296|1915-07-05 07:02:06|80194.64|         Jel|         histats.com|sgnitsaH namilleJ|enamillejh@histat...|Hastings Hastings|         com|             19|Hastings Jelliman|    Hastings|\n",
            "| 16|     Josee|   Burnep|    jburnepf@php.net|  NULL| 78569|1923-11-08 20:59:14|88925.13|         Bur|             php.net|     eesoJ penruB|    fpenrubj@php.net|      Josee Josee|         net|             13|     Josee Burnep|       Josee|\n",
            "| 17|     Gilly|   Fownes|gfownesg@redcross...|Female|  2527|1946-07-30 21:10:57|    NULL|         Fow|        redcross.org|     ylliG senwoF|gsenwofg@redcross...|      Gilly Gilly|         org|             18|     Gilly Fownes|       Gilly|\n",
            "| 18|      NULL|Schruyers|lschruyersh@desde...|  Male|109936|1979-06-16 01:12:48|37243.09|         Sch|           desdev.cn|             NULL|hsreyurhcsl@desde...|             NULL|          cn|             19|             NULL|        NULL|\n",
            "| 19|     Maris|Chatelain| mchatelaini@unc.edu|Female|190047|2008-01-11 17:16:21|76469.65|         Cha|             unc.edu|  siraM nialetahC| inialetahcm@unc.edu|      Maris Maris|         edu|             16|  Maris Chatelain|       Maris|\n",
            "| 20|    Casper|  Aughtie|    caughtiej@vk.com|  Male| 38689|1982-04-06 05:13:49| 80404.8|         Aug|              vk.com|   repsaC eithguA|    jeithguac@vk.com|    Casper Casper|         com|             13|   Casper Aughtie|      Casper|\n",
            "+---+----------+---------+--------------------+------+------+-------------------+--------+------------+--------------------+-----------------+--------------------+-----------------+------------+---------------+-----------------+------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ],
      "execution_count": 42
    },
    {
      "cell_type": "code",
      "source": [],
      "metadata": {
        "id": "Bs4zoGWKtYKt"
      },
      "execution_count": null,
      "outputs": []
    }
  ],
  "metadata": {
    "application/vnd.databricks.v1+notebook": {
      "notebookName": "Databircks-Assignemnt4 Advance String Manupulation Transformation)",
      "dashboards": [],
      "notebookMetadata": {
        "pythonIndentUnit": 2
      },
      "language": "python",
      "widgets": {},
      "notebookOrigID": 534745604295407
    },
    "colab": {
      "provenance": [],
      "include_colab_link": true
    },
    "language_info": {
      "name": "python"
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}