Databricks_Basic_Data_Cleaning_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-assignments/blob/main/Databricks_Basic_Data_Cleaning_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> Basic Data Cleaning & Transformation"
      ],
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "showTitle": false,
          "cellMetadata": {},
          "nuid": "df7e83ba-34cd-4f9e-8fb5-eb5ec719cf28",
          "inputWidgets": {},
          "title": ""
        },
        "id": "tU1uJWz78Qdv"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "from google.colab import drive\n",
        "drive.mount('/content/drive')"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "fP4PO8_l8TD-",
        "outputId": "7182ab4c-afa2-4a18-8a3f-14b11a7f584f"
      },
      "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\n"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "PnKLw5kaw-F-",
        "outputId": "61cb5cfe-221b-4ca2-af3c-ec0c4e9ec7ee"
      },
      "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[31m3.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=f8fb7c43fffd1b853a27a03f79ccb95b0867ac246f8bf0c0aedb28361dfeb17e\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": [
        "from pyspark.sql import SparkSession\n",
        "spark = SparkSession.builder.appName('SparkByAzureLib').getOrCreate()"
      ],
      "metadata": {
        "id": "rycp0936x9X0"
      },
      "execution_count": 3,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# read csv file from one drive\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": "4MA7zg0RyTe3",
        "outputId": "4d3b069c-aea4-4e3d-84f7-0f312543213b"
      },
      "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": [
        "<b> Find the count of duplicate employee records in the input file (based on id)?"
      ],
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "showTitle": false,
          "cellMetadata": {},
          "nuid": "62c53c84-5d19-4115-a775-314575ce42fc",
          "inputWidgets": {},
          "title": ""
        },
        "id": "wJJVJMJ28Qdw"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Write your code using the Spark Function\n",
        "\n",
        "from pyspark.sql.functions import *\n",
        "\n",
        "# unique recorde count\n",
        "\n",
        "df1 = df.select(countDistinct(\"id\")).collect()[0][0]\n",
        "print(\"Number of unique employee records:\", df1)\n",
        "\n",
        "# duplicate record count\n",
        "\n",
        "duplicate_count = df.count() - df1\n",
        "print(\"Number of duplicate employee records:\", duplicate_count)\n"
      ],
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "showTitle": false,
          "cellMetadata": {},
          "nuid": "2d4e6162-dd1d-497a-b403-121b7b3ad8da",
          "inputWidgets": {},
          "title": ""
        },
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "PR8AgqKX8Qdx",
        "outputId": "d07d7295-7665-4fe3-f34c-07c51c961ff9"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Number of unique employee records: 970\n",
            "Number of duplicate employee records: 30\n"
          ]
        }
      ],
      "execution_count": 8
    },
    {
      "cell_type": "markdown",
      "source": [
        "<b> Find out how many records have Gender value missing."
      ],
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "showTitle": false,
          "cellMetadata": {},
          "nuid": "8364e840-c519-4ac5-a1bf-b9b89b994321",
          "inputWidgets": {},
          "title": ""
        },
        "id": "HPZsl9KU8Qdy"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Write your code using the Spark Function\n",
        "df3=df.filter(df.gender.isNull())\n",
        "print(\"Number of records with Gender value missing:\", df3.count())\n",
        "df3.show()"
      ],
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "showTitle": false,
          "cellMetadata": {},
          "nuid": "a0185598-138a-4fd1-a503-3e1a0c301712",
          "inputWidgets": {},
          "title": ""
        },
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "RAwtuMAG8Qdy",
        "outputId": "25588a94-0a81-4409-94a8-abad5df972d5"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Number of records with Gender value missing: 28\n",
            "+---+----------+----------+--------------------+------+------+-------------------+--------+\n",
            "| id|first_name| last_name|               email|gender|salary|       creationDate|   bonus|\n",
            "+---+----------+----------+--------------------+------+------+-------------------+--------+\n",
            "| 16|     Josee|    Burnep|    jburnepf@php.net|  NULL| 78569|1923-11-08 20:59:14|88925.13|\n",
            "| 72|      Elmo|   Polding|epolding1z@prince...|  NULL|180688|1903-09-06 22:25:36|59278.96|\n",
            "|141|  Emmeline|   Hullett|ehullett3w@abc.ne...|  NULL|172454|1973-02-18 08:07:02|96907.76|\n",
            "|182|  Tremayne|     Beers|  tbeers51@imgur.com|  NULL|182456|1914-02-11 19:36:47|    NULL|\n",
            "|257|     Vicki|   Huddart|vhuddart74@nifty.com|  NULL|195466|1919-05-26 14:01:12|91780.06|\n",
            "|296|     Ruthy|  Cartmail|rcartmail87@cnbc.com|  NULL|141158|1988-02-04 01:48:52|95936.58|\n",
            "|410|    Arturo|   O'Kelly| aokellybd@slate.com|  NULL| 93803|1941-07-24 22:46:44|    NULL|\n",
            "|520|      Kara|     Indgs|kindgsef@ycombina...|  NULL|141385|               NULL|75555.27|\n",
            "|522|    Jorrie|   Mustill|  jmustilleh@nps.gov|  NULL|127842|1949-11-17 06:11:11|64231.61|\n",
            "|546| Cristobal|St. Pierre|   cstpierref5@ow.ly|  NULL| 68233|1921-11-11 07:34:15|18450.07|\n",
            "|574|      Dill| Langstone|dlangstonefx@apac...|  NULL|145590|1919-02-07 18:33:33|72661.34|\n",
            "|597|     Jinny|   Wraight|jwraightgk@vkonta...|  NULL|122026|1950-08-23 16:40:08|    NULL|\n",
            "|644|     Titus|      NULL|                NULL|  NULL|115794|1927-07-29 20:03:53|43906.14|\n",
            "|667|   Cristen|     James|  cjamesii@desdev.cn|  NULL|  NULL|1960-04-30 13:59:36|37083.07|\n",
            "|674|   Myrlene|   Moresby|mmoresbyip@freewe...|  NULL| 31450|1972-06-14 03:14:44|    NULL|\n",
            "|686|    Darwin|      NULL|                NULL|  NULL|198016|1998-05-31 01:03:34| 2014.72|\n",
            "|692|     Reese|     Forre|rforrej7@newyorke...|  NULL|127508|               NULL|    NULL|\n",
            "|770|    Durand|   Skottle|dskottleld@cisco.com|  NULL|178390|1977-12-14 11:58:48|84035.63|\n",
            "|791|       Boy|  Berggren|bberggrenly@narod.ru|  NULL| 64047|1929-08-20 21:32:45|50583.07|\n",
            "|824|     Hanny|      NULL|hfarringtonmv@sym...|  NULL|116813|2003-05-09 19:25:56|24492.81|\n",
            "+---+----------+----------+--------------------+------+------+-------------------+--------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ],
      "execution_count": 13
    },
    {
      "cell_type": "markdown",
      "source": [
        "\n",
        "<b> Are there any missing values in the \"bonus\" field? If so, filled them defualt bonus 100."
      ],
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "showTitle": false,
          "cellMetadata": {},
          "nuid": "14772c70-beff-49d9-828a-724188863639",
          "inputWidgets": {},
          "title": ""
        },
        "id": "MA_GWo9R8Qdy"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Write your code using the Spark Function\n",
        "\n",
        "# find the missing values in bonus filds\n",
        "\n",
        "df4 = df.filter(df.bonus.isNull())\n",
        "print(\"Number of records with bonus value missing:\", df4.count())\n",
        "df4.show ()\n",
        "\n",
        "# fill the missing value with 100\n",
        "\n",
        "df5 = df.na.fill(100,['bonus'])\n",
        "df5.show()\n",
        "\n",
        "# check the null values in bonus again\n",
        "\n",
        "df6 = df5.filter(df.bonus.isNull())\n",
        "print(\"Number of records with bonus value missing:\", df6.count())"
      ],
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "showTitle": false,
          "cellMetadata": {},
          "nuid": "5233f351-ed0d-4c5d-8c49-b382053a6e91",
          "inputWidgets": {},
          "title": ""
        },
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "yPlD1wKH8Qdy",
        "outputId": "03f433c1-d331-458c-e672-14240b3983ba"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Number of records with bonus value missing: 166\n",
            "+---+----------+-----------+--------------------+------+------+-------------------+-----+\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",
            "|  4|    Dulsea|       Foss|dfoss3@dagondesig...|Female|193291|1942-05-09 20:59:39| NULL|\n",
            "|  6|     Baily|     Antony| bantony5@sfgate.com|  Male|127337|1913-10-14 11:25:33| NULL|\n",
            "|  9|   Sibylla|  Sickamore|ssickamore8@faceb...|Female|107243|2020-03-15 15:00:30| NULL|\n",
            "| 12|  Giuseppe|    Scimoni|gscimonib@craigsl...|  Male|176531|1967-04-06 07:23:03| NULL|\n",
            "| 17|     Gilly|     Fownes|gfownesg@redcross...|Female|  2527|1946-07-30 21:10:57| NULL|\n",
            "| 30|    Gretta|       NULL|gdeleat@webeden.c...|Female|134143|2001-03-19 12:19:50| NULL|\n",
            "| 36|  Olenolin|       NULL|omaestriniz@googl...|  Male|101428|1929-09-27 09:37:46| NULL|\n",
            "| 39|   Daniele|     Baelde|   dbaelde12@who.int|Female| 85612|1988-05-12 08:40:59| NULL|\n",
            "| 48|    Weylin|     Heatly|    wheatly1b@ed.gov|  Male|185564|2014-10-27 22:42:51| NULL|\n",
            "| 67|    Aloise|   Brombell|                NULL|Female| 51529|1987-02-16 16:02:18| NULL|\n",
            "| 70|    Lianne|       Boam|                NULL|Female| 13695|1981-04-08 20:31:44| NULL|\n",
            "| 75|    Allina|    Sposito|asposito22@japanp...|Female|130641|1925-01-10 03:12:07| NULL|\n",
            "| 76|     Camey|    Crallan|ccrallan23@earthl...|  Male|147297|1911-07-25 23:37:49| NULL|\n",
            "| 85|      NULL|  Learmouth|slearmouth2c@ebay...|Female| 60028|               NULL| NULL|\n",
            "| 91|  Nicolais|Rosenfelder|nrosenfelder2i@pc...|  Male| 70478|               NULL| NULL|\n",
            "| 96|   Katrina|    Snoxall|ksnoxall2n@buzzfe...|Female| 16459|1991-04-22 23:35:19| NULL|\n",
            "|116|      NULL|      Jados|                NULL|  Male| 56578|2002-05-29 06:33:36| NULL|\n",
            "|118|  Karalynn|     Scones| kscones39@weibo.com|Female|180007|               NULL| NULL|\n",
            "+---+----------+-----------+--------------------+------+------+-------------------+-----+\n",
            "only showing top 20 rows\n",
            "\n",
            "+---+----------+---------+--------------------+------+------+-------------------+--------+\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|   100.0|\n",
            "|  2|  Lynnelle|    Hurll| lhurll1@answers.com|Female|112411|1907-05-10 17:38:56|   100.0|\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|   100.0|\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|   100.0|\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|   100.0|\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|   100.0|\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|   100.0|\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",
            "Number of records with bonus value missing: 166\n"
          ]
        }
      ],
      "execution_count": 17
    },
    {
      "cell_type": "markdown",
      "source": [
        "<b> Are there any employees with negative salary or bonus amounts in the input file? If so, how many?"
      ],
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "showTitle": false,
          "cellMetadata": {},
          "nuid": "cae0fe03-6339-4dac-9a18-1a5a1da78612",
          "inputWidgets": {},
          "title": ""
        },
        "id": "pg8Wuth58Qdz"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Write your code using the Spark Function\n",
        "\n",
        "# employees with negative salary and bonus\n",
        "\n",
        "df7 = df.filter((df.salary<0) | (df.bonus<0))\n",
        "print(\"Number of employees with negative salary or bonus:\", df7.count())\n",
        "df7.show()\n",
        "\n",
        "# employee with 0/NULL bonus\n",
        "\n",
        "df8 = df.filter((df.bonus==0) | (df.bonus.isNull()))\n",
        "print(\"Number of employees with NULL bonus:\", df8.count())\n",
        "df8.show()"
      ],
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "showTitle": false,
          "cellMetadata": {},
          "nuid": "0bf0728d-c9ff-4d5f-9cb4-00d7a34bb988",
          "inputWidgets": {},
          "title": ""
        },
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "nak1UYb08Qdz",
        "outputId": "b7bace47-3faa-48c1-9453-80205c3b245e"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Number of employees with negative salary or bonus: 0\n",
            "+---+----------+---------+-----+------+------+------------+-----+\n",
            "| id|first_name|last_name|email|gender|salary|creationDate|bonus|\n",
            "+---+----------+---------+-----+------+------+------------+-----+\n",
            "+---+----------+---------+-----+------+------+------------+-----+\n",
            "\n",
            "Number of employees with NULL bonus: 166\n",
            "+---+----------+-----------+--------------------+------+------+-------------------+-----+\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",
            "|  4|    Dulsea|       Foss|dfoss3@dagondesig...|Female|193291|1942-05-09 20:59:39| NULL|\n",
            "|  6|     Baily|     Antony| bantony5@sfgate.com|  Male|127337|1913-10-14 11:25:33| NULL|\n",
            "|  9|   Sibylla|  Sickamore|ssickamore8@faceb...|Female|107243|2020-03-15 15:00:30| NULL|\n",
            "| 12|  Giuseppe|    Scimoni|gscimonib@craigsl...|  Male|176531|1967-04-06 07:23:03| NULL|\n",
            "| 17|     Gilly|     Fownes|gfownesg@redcross...|Female|  2527|1946-07-30 21:10:57| NULL|\n",
            "| 30|    Gretta|       NULL|gdeleat@webeden.c...|Female|134143|2001-03-19 12:19:50| NULL|\n",
            "| 36|  Olenolin|       NULL|omaestriniz@googl...|  Male|101428|1929-09-27 09:37:46| NULL|\n",
            "| 39|   Daniele|     Baelde|   dbaelde12@who.int|Female| 85612|1988-05-12 08:40:59| NULL|\n",
            "| 48|    Weylin|     Heatly|    wheatly1b@ed.gov|  Male|185564|2014-10-27 22:42:51| NULL|\n",
            "| 67|    Aloise|   Brombell|                NULL|Female| 51529|1987-02-16 16:02:18| NULL|\n",
            "| 70|    Lianne|       Boam|                NULL|Female| 13695|1981-04-08 20:31:44| NULL|\n",
            "| 75|    Allina|    Sposito|asposito22@japanp...|Female|130641|1925-01-10 03:12:07| NULL|\n",
            "| 76|     Camey|    Crallan|ccrallan23@earthl...|  Male|147297|1911-07-25 23:37:49| NULL|\n",
            "| 85|      NULL|  Learmouth|slearmouth2c@ebay...|Female| 60028|               NULL| NULL|\n",
            "| 91|  Nicolais|Rosenfelder|nrosenfelder2i@pc...|  Male| 70478|               NULL| NULL|\n",
            "| 96|   Katrina|    Snoxall|ksnoxall2n@buzzfe...|Female| 16459|1991-04-22 23:35:19| NULL|\n",
            "|116|      NULL|      Jados|                NULL|  Male| 56578|2002-05-29 06:33:36| NULL|\n",
            "|118|  Karalynn|     Scones| kscones39@weibo.com|Female|180007|               NULL| NULL|\n",
            "+---+----------+-----------+--------------------+------+------+-------------------+-----+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ],
      "execution_count": 26
    },
    {
      "cell_type": "markdown",
      "source": [
        "<b> Replace all the null/emtpy value in email column with admin@azurelib.com"
      ],
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "showTitle": false,
          "cellMetadata": {},
          "nuid": "8e58642b-6aff-4351-92a0-cea48bfdc948",
          "inputWidgets": {},
          "title": ""
        },
        "id": "1klI1Iy48Qdz"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Write your code using the Spark Function\n",
        "\n",
        "# find the null/empty email id\n",
        "\n",
        "df9 = df.filter ((df.email.isNull()) | (df.email==\"\"))\n",
        "df9.show()\n",
        "\n",
        "# fill the null email id with \"admin@azurelib.com\" email\n",
        "\n",
        "df10 = df.na.fill(\"admin@azurelib.com\",['email'])\n",
        "df10.show()\n",
        "\n",
        "# check row with \"admin@azurelib\" email\n",
        "\n",
        "df11 = df10.filter((df10.email==\"admin@azurelib.com\"))\n",
        "df11.show()\n"
      ],
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "showTitle": false,
          "cellMetadata": {},
          "nuid": "4288320d-f41e-4719-99a6-93fe790beacb",
          "inputWidgets": {},
          "title": ""
        },
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "YFlBmNbu8Qdz",
        "outputId": "dddfdf3f-2c9c-4f80-8e04-e589ae95536f"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+----+----------+-----------+-----+------+------+-------------------+--------+\n",
            "|  id|first_name|  last_name|email|gender|salary|       creationDate|   bonus|\n",
            "+----+----------+-----------+-----+------+------+-------------------+--------+\n",
            "|  21|    Kristo|    Giraudy| NULL|  Male|152116|1962-08-16 21:27:30| 5982.54|\n",
            "|  28| Sallyanne|  Iacobucci| NULL|Female|  2339|1939-05-10 11:30:31| 66003.0|\n",
            "|  29|      NULL|     Nibley| NULL|Female|165764|2019-08-30 11:23:27|93953.74|\n",
            "|  46|Kristoforo|     Sowray| NULL|  Male|131676|1959-03-14 16:01:23|27022.26|\n",
            "|  64|   Cordell|Baszkiewicz| NULL|  Male| 89429|1998-04-26 03:44:59|50640.06|\n",
            "|  67|    Aloise|   Brombell| NULL|Female| 51529|1987-02-16 16:02:18|    NULL|\n",
            "|  70|    Lianne|       Boam| NULL|Female| 13695|1981-04-08 20:31:44|    NULL|\n",
            "|  78|   Jillian|    Chasmar| NULL|Female| 18805|2019-01-07 02:06:41|93412.65|\n",
            "|  95|     Davey|  Woodworth| NULL|  Male|  8569|2000-05-24 18:03:02|92849.24|\n",
            "|  97|    Thorpe|    Gabotti| NULL|  Male|167464|1982-06-30 21:33:46|  8679.7|\n",
            "|NULL|    Darren|       NULL| NULL|  Male|111848|1940-11-30 02:35:02| 6461.19|\n",
            "| 116|      NULL|      Jados| NULL|  Male| 56578|2002-05-29 06:33:36|    NULL|\n",
            "| 123|    Malchy|  Whitefoot| NULL|  Male| 28052|1918-08-05 15:45:12|39117.02|\n",
            "| 142|       Erv|     Griggs| NULL|  Male|144655|2007-11-09 22:05:38| 95251.8|\n",
            "| 145|     Adele|     Taylot| NULL|Female|  NULL|1968-01-14 05:31:32|    NULL|\n",
            "| 146|    Amargo|   Tresvina| NULL|Female|185482|1962-04-26 20:02:19|45135.71|\n",
            "| 152|    Lorrin|   Stopford| NULL|Female| 53416|1918-07-11 08:09:31|94705.56|\n",
            "| 163|   Jeffrey|     Sugars| NULL|  Male|112937|1978-08-15 19:03:34|92001.92|\n",
            "| 165|      Kirk|     Haddon| NULL|  Male| 46136|1975-07-12 23:15:40|68143.38|\n",
            "| 169|    Jamesy|      Gilby| NULL|  Male|153493|1971-01-28 04:00:18|27030.09|\n",
            "+----+----------+-----------+-----+------+------+-------------------+--------+\n",
            "only showing top 20 rows\n",
            "\n",
            "+---+----------+---------+--------------------+------+------+-------------------+--------+\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",
            "+----+----------+-----------+------------------+------+------+-------------------+--------+\n",
            "|  id|first_name|  last_name|             email|gender|salary|       creationDate|   bonus|\n",
            "+----+----------+-----------+------------------+------+------+-------------------+--------+\n",
            "|  21|    Kristo|    Giraudy|admin@azurelib.com|  Male|152116|1962-08-16 21:27:30| 5982.54|\n",
            "|  28| Sallyanne|  Iacobucci|admin@azurelib.com|Female|  2339|1939-05-10 11:30:31| 66003.0|\n",
            "|  29|      NULL|     Nibley|admin@azurelib.com|Female|165764|2019-08-30 11:23:27|93953.74|\n",
            "|  46|Kristoforo|     Sowray|admin@azurelib.com|  Male|131676|1959-03-14 16:01:23|27022.26|\n",
            "|  64|   Cordell|Baszkiewicz|admin@azurelib.com|  Male| 89429|1998-04-26 03:44:59|50640.06|\n",
            "|  67|    Aloise|   Brombell|admin@azurelib.com|Female| 51529|1987-02-16 16:02:18|    NULL|\n",
            "|  70|    Lianne|       Boam|admin@azurelib.com|Female| 13695|1981-04-08 20:31:44|    NULL|\n",
            "|  78|   Jillian|    Chasmar|admin@azurelib.com|Female| 18805|2019-01-07 02:06:41|93412.65|\n",
            "|  95|     Davey|  Woodworth|admin@azurelib.com|  Male|  8569|2000-05-24 18:03:02|92849.24|\n",
            "|  97|    Thorpe|    Gabotti|admin@azurelib.com|  Male|167464|1982-06-30 21:33:46|  8679.7|\n",
            "|NULL|    Darren|       NULL|admin@azurelib.com|  Male|111848|1940-11-30 02:35:02| 6461.19|\n",
            "| 116|      NULL|      Jados|admin@azurelib.com|  Male| 56578|2002-05-29 06:33:36|    NULL|\n",
            "| 123|    Malchy|  Whitefoot|admin@azurelib.com|  Male| 28052|1918-08-05 15:45:12|39117.02|\n",
            "| 142|       Erv|     Griggs|admin@azurelib.com|  Male|144655|2007-11-09 22:05:38| 95251.8|\n",
            "| 145|     Adele|     Taylot|admin@azurelib.com|Female|  NULL|1968-01-14 05:31:32|    NULL|\n",
            "| 146|    Amargo|   Tresvina|admin@azurelib.com|Female|185482|1962-04-26 20:02:19|45135.71|\n",
            "| 152|    Lorrin|   Stopford|admin@azurelib.com|Female| 53416|1918-07-11 08:09:31|94705.56|\n",
            "| 163|   Jeffrey|     Sugars|admin@azurelib.com|  Male|112937|1978-08-15 19:03:34|92001.92|\n",
            "| 165|      Kirk|     Haddon|admin@azurelib.com|  Male| 46136|1975-07-12 23:15:40|68143.38|\n",
            "| 169|    Jamesy|      Gilby|admin@azurelib.com|  Male|153493|1971-01-28 04:00:18|27030.09|\n",
            "+----+----------+-----------+------------------+------+------+-------------------+--------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ],
      "execution_count": 30
    },
    {
      "cell_type": "markdown",
      "source": [
        "<b> Remove all the records where any record has any null values. Find out the total count of the records now."
      ],
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "showTitle": false,
          "cellMetadata": {},
          "nuid": "d39815e9-f12a-497e-a658-fc9f1c98d271",
          "inputWidgets": {},
          "title": ""
        },
        "id": "1bDHWWZm8Qdz"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Write your code using the Spark Function\n",
        "\n",
        "print(\"Total count of records:\", df.count())\n",
        "\n",
        "# drop all record with null values\n",
        "df12 = df.na.drop()\n",
        "print(\"Total count of records after removing null values:\", df12.count())\n",
        "df12.show()"
      ],
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "showTitle": false,
          "cellMetadata": {},
          "nuid": "c1035982-814b-43ff-b9df-55e89c658f68",
          "inputWidgets": {},
          "title": ""
        },
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "x69w2qzK8Qd0",
        "outputId": "bfe77ad0-7c83-4679-f132-e80fd7fa6987"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Total count of records: 1000\n",
            "Total count of records after removing null values: 512\n",
            "+---+----------+-----------+--------------------+------+------+-------------------+--------+\n",
            "| id|first_name|  last_name|               email|gender|salary|       creationDate|   bonus|\n",
            "+---+----------+-----------+--------------------+------+------+-------------------+--------+\n",
            "|  3|   Miranda|      Train|   mtrain2@imgur.com|Female| 91073|1941-01-24 16:05:23|12875.54|\n",
            "|  5|    Anatol|    Dunklee| adunklee4@google.de|  Male| 22175|1950-07-26 16:28:00| 1432.12|\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",
            "| 11|      Coop|      Richt|   crichta@sogou.com|  Male| 19964|1939-02-13 16:18:24|56973.41|\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",
            "| 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",
            "| 22|     Becki|      Conry|bconryl@examiner.com|Female| 10203|2003-02-07 10:00:05|47067.23|\n",
            "| 23|      Edie|      Orniz|eornizm@moonfruit...|Female| 81920|1982-10-03 14:48:25|20019.85|\n",
            "| 24|   Sharity|     Fronks|sfronksn@economis...|Female|169527|1951-06-14 19:32:49| 4960.71|\n",
            "| 25|     Vally|   Dahlberg|vdahlbergo@dyndns...|Female| 80628|1924-07-07 06:20:37|60914.52|\n",
            "| 26|     Effie|Maffioletti|emaffiolettip@pos...|Female| 34228|1986-03-23 12:03:27|31496.73|\n",
            "| 31|    Blayne|  Yuranovev|byuranovevu@blogt...|  Male|188551|1998-07-27 11:30:51|27111.81|\n",
            "| 32|      Maud|     Formby|mformbyv@macromed...|Female|182359|1964-08-24 11:02:47|77984.03|\n",
            "| 37|    Maggee|     Rablin|mrablin10@mashabl...|Female|134344|2004-10-08 01:50:46|60163.15|\n",
            "| 38|     Nikki|    Woolner|nwoolner11@linked...|Female|  3792|1942-05-13 12:11:21|21054.48|\n",
            "| 40|    Farlee|    Fincken|ffincken13@google...|  Male|126873|1937-06-14 23:34:30|58907.18|\n",
            "+---+----------+-----------+--------------------+------+------+-------------------+--------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ],
      "execution_count": 35
    },
    {
      "cell_type": "code",
      "source": [],
      "metadata": {
        "id": "cpDnH-AO3xeh"
      },
      "execution_count": null,
      "outputs": []
    }
  ],
  "metadata": {
    "application/vnd.databricks.v1+notebook": {
      "notebookName": "Databircks-Assignemnt2 (Basic Data Cleaning Transformation)",
      "dashboards": [],
      "notebookMetadata": {
        "pythonIndentUnit": 2
      },
      "language": "python",
      "widgets": {},
      "notebookOrigID": 534745604295369
    },
    "colab": {
      "provenance": [],
      "include_colab_link": true
    },
    "language_info": {
      "name": "python"
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}