03_basic_transformations_2.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "9913130b-9ed3-4a87-a01d-a13cae0e3204",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "            <div>\n",
       "                <p><b>SparkSession - in-memory</b></p>\n",
       "                \n",
       "        <div>\n",
       "            <p><b>SparkContext</b></p>\n",
       "\n",
       "            <p><a href=\"http://b96cbc5ef428:4040\">Spark UI</a></p>\n",
       "\n",
       "            <dl>\n",
       "              <dt>Version</dt>\n",
       "                <dd><code>v3.3.0</code></dd>\n",
       "              <dt>Master</dt>\n",
       "                <dd><code>local[*]</code></dd>\n",
       "              <dt>AppName</dt>\n",
       "                <dd><code>Basic Transformation - II</code></dd>\n",
       "            </dl>\n",
       "        </div>\n",
       "        \n",
       "            </div>\n",
       "        "
      ],
      "text/plain": [
       "<pyspark.sql.session.SparkSession at 0x7f220815bfd0>"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Spark Session\n",
    "from pyspark.sql import SparkSession\n",
    "\n",
    "spark = (\n",
    "    SparkSession\n",
    "    .builder\n",
    "    .appName(\"Basic Transformation - II\")\n",
    "    .master(\"local[*]\")\n",
    "    .getOrCreate()\n",
    ")\n",
    "\n",
    "spark"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "7f6357c0-ccf8-43de-8f7d-69d5a9314fef",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Emp Data & Schema\n",
    "\n",
    "emp_data = [\n",
    "    [\"001\",\"101\",\"John Doe\",\"30\",\"Male\",\"50000\",\"2015-01-01\"],\n",
    "    [\"002\",\"101\",\"Jane Smith\",\"25\",\"Female\",\"45000\",\"2016-02-15\"],\n",
    "    [\"003\",\"102\",\"Bob Brown\",\"35\",\"Male\",\"55000\",\"2014-05-01\"],\n",
    "    [\"004\",\"102\",\"Alice Lee\",\"28\",\"Female\",\"48000\",\"2017-09-30\"],\n",
    "    [\"005\",\"103\",\"Jack Chan\",\"40\",\"Male\",\"60000\",\"2013-04-01\"],\n",
    "    [\"006\",\"103\",\"Jill Wong\",\"32\",\"Female\",\"52000\",\"2018-07-01\"],\n",
    "    [\"007\",\"101\",\"James Johnson\",\"42\",\"Male\",\"70000\",\"2012-03-15\"],\n",
    "    [\"008\",\"102\",\"Kate Kim\",\"29\",\"Female\",\"51000\",\"2019-10-01\"],\n",
    "    [\"009\",\"103\",\"Tom Tan\",\"33\",\"Male\",\"58000\",\"2016-06-01\"],\n",
    "    [\"010\",\"104\",\"Lisa Lee\",\"27\",\"Female\",\"47000\",\"2018-08-01\"],\n",
    "    [\"011\",\"104\",\"David Park\",\"38\",\"Male\",\"65000\",\"2015-11-01\"],\n",
    "    [\"012\",\"105\",\"Susan Chen\",\"31\",\"Female\",\"54000\",\"2017-02-15\"],\n",
    "    [\"013\",\"106\",\"Brian Kim\",\"45\",\"Male\",\"75000\",\"2011-07-01\"],\n",
    "    [\"014\",\"107\",\"Emily Lee\",\"26\",\"Female\",\"46000\",\"2019-01-01\"],\n",
    "    [\"015\",\"106\",\"Michael Lee\",\"37\",\"Male\",\"63000\",\"2014-09-30\"],\n",
    "    [\"016\",\"107\",\"Kelly Zhang\",\"30\",\"Female\",\"49000\",\"2018-04-01\"],\n",
    "    [\"017\",\"105\",\"George Wang\",\"34\",\"Male\",\"57000\",\"2016-03-15\"],\n",
    "    [\"018\",\"104\",\"Nancy Liu\",\"29\",\"Female\",\"50000\",\"2017-06-01\"],\n",
    "    [\"019\",\"103\",\"Steven Chen\",\"36\",\"Male\",\"62000\",\"2015-08-01\"],\n",
    "    [\"020\",\"102\",\"Grace Kim\",\"32\",\"Female\",\"53000\",\"2018-11-01\"]\n",
    "]\n",
    "\n",
    "emp_schema = \"employee_id string, department_id string, name string, age string, gender string, salary string, hire_date string\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "9bdc19a5-b213-498e-8e1f-63650523c441",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create emp DataFrame\n",
    "\n",
    "emp = spark.createDataFrame(data=emp_data, schema=emp_schema)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "9d81001d-2633-439f-9153-9b1ef27e64a8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------+-------------+-------------+---+------+------+----------+\n",
      "|employee_id|department_id|         name|age|gender|salary| hire_date|\n",
      "+-----------+-------------+-------------+---+------+------+----------+\n",
      "|        001|          101|     John Doe| 30|  Male| 50000|2015-01-01|\n",
      "|        002|          101|   Jane Smith| 25|Female| 45000|2016-02-15|\n",
      "|        003|          102|    Bob Brown| 35|  Male| 55000|2014-05-01|\n",
      "|        004|          102|    Alice Lee| 28|Female| 48000|2017-09-30|\n",
      "|        005|          103|    Jack Chan| 40|  Male| 60000|2013-04-01|\n",
      "|        006|          103|    Jill Wong| 32|Female| 52000|2018-07-01|\n",
      "|        007|          101|James Johnson| 42|  Male| 70000|2012-03-15|\n",
      "|        008|          102|     Kate Kim| 29|Female| 51000|2019-10-01|\n",
      "|        009|          103|      Tom Tan| 33|  Male| 58000|2016-06-01|\n",
      "|        010|          104|     Lisa Lee| 27|Female| 47000|2018-08-01|\n",
      "|        011|          104|   David Park| 38|  Male| 65000|2015-11-01|\n",
      "|        012|          105|   Susan Chen| 31|Female| 54000|2017-02-15|\n",
      "|        013|          106|    Brian Kim| 45|  Male| 75000|2011-07-01|\n",
      "|        014|          107|    Emily Lee| 26|Female| 46000|2019-01-01|\n",
      "|        015|          106|  Michael Lee| 37|  Male| 63000|2014-09-30|\n",
      "|        016|          107|  Kelly Zhang| 30|Female| 49000|2018-04-01|\n",
      "|        017|          105|  George Wang| 34|  Male| 57000|2016-03-15|\n",
      "|        018|          104|    Nancy Liu| 29|Female| 50000|2017-06-01|\n",
      "|        019|          103|  Steven Chen| 36|  Male| 62000|2015-08-01|\n",
      "|        020|          102|    Grace Kim| 32|Female| 53000|2018-11-01|\n",
      "+-----------+-------------+-------------+---+------+------+----------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Show emp dataframe (ACTION)\n",
    "\n",
    "emp.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "d340333a-1ff1-4b60-a602-6342a056cf3a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- employee_id: string (nullable = true)\n",
      " |-- department_id: string (nullable = true)\n",
      " |-- name: string (nullable = true)\n",
      " |-- age: string (nullable = true)\n",
      " |-- gender: string (nullable = true)\n",
      " |-- salary: string (nullable = true)\n",
      " |-- hire_date: string (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Print Schema\n",
    "\n",
    "emp.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "6b99ead2-9509-43b0-83e9-a8f1eaebffe9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Casting Column\n",
    "# select employee_id, name, age, cast(salary as double) as salary from emp\n",
    "from pyspark.sql.functions import col, cast\n",
    "\n",
    "emp_casted = emp.select(\"employee_id\", \"name\", \"age\", col(\"salary\").cast(\"double\"))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "9eeb53b0-ff7f-4e0e-85ca-fef8ef15ac22",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- employee_id: string (nullable = true)\n",
      " |-- name: string (nullable = true)\n",
      " |-- age: string (nullable = true)\n",
      " |-- salary: double (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "emp_casted.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "40e8d717-c449-42f4-a3e3-6bff56077b6b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Adding Columns\n",
    "# select employee_id, name, age, salary, (salary * 0.2) as tax from emp_casted\n",
    "\n",
    "emp_taxed = emp_casted.withColumn(\"tax\", col(\"salary\") * 0.2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "1ef03a22-a05f-40f6-8134-9e555b0871b3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------+-------------+---+-------+-------+\n",
      "|employee_id|         name|age| salary|    tax|\n",
      "+-----------+-------------+---+-------+-------+\n",
      "|        001|     John Doe| 30|50000.0|10000.0|\n",
      "|        002|   Jane Smith| 25|45000.0| 9000.0|\n",
      "|        003|    Bob Brown| 35|55000.0|11000.0|\n",
      "|        004|    Alice Lee| 28|48000.0| 9600.0|\n",
      "|        005|    Jack Chan| 40|60000.0|12000.0|\n",
      "|        006|    Jill Wong| 32|52000.0|10400.0|\n",
      "|        007|James Johnson| 42|70000.0|14000.0|\n",
      "|        008|     Kate Kim| 29|51000.0|10200.0|\n",
      "|        009|      Tom Tan| 33|58000.0|11600.0|\n",
      "|        010|     Lisa Lee| 27|47000.0| 9400.0|\n",
      "|        011|   David Park| 38|65000.0|13000.0|\n",
      "|        012|   Susan Chen| 31|54000.0|10800.0|\n",
      "|        013|    Brian Kim| 45|75000.0|15000.0|\n",
      "|        014|    Emily Lee| 26|46000.0| 9200.0|\n",
      "|        015|  Michael Lee| 37|63000.0|12600.0|\n",
      "|        016|  Kelly Zhang| 30|49000.0| 9800.0|\n",
      "|        017|  George Wang| 34|57000.0|11400.0|\n",
      "|        018|    Nancy Liu| 29|50000.0|10000.0|\n",
      "|        019|  Steven Chen| 36|62000.0|12400.0|\n",
      "|        020|    Grace Kim| 32|53000.0|10600.0|\n",
      "+-----------+-------------+---+-------+-------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "emp_taxed.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "ae92a9a3-dd82-41b0-ae7d-bbb341371bb2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Literals\n",
    "# select employee_id, name, age, salary, tax, 1 as columnOne, 'two' as columnTwo from emp_taxed\n",
    "from pyspark.sql.functions import lit\n",
    "\n",
    "emp_new_cols = emp_taxed.withColumn(\"columnOne\", lit(1)).withColumn(\"columnTwo\", lit('two'))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "e3bc6295-4e8f-44a4-8baf-e45f1739248b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------+-------------+---+-------+-------+---------+---------+\n",
      "|employee_id|         name|age| salary|    tax|columnOne|columnTwo|\n",
      "+-----------+-------------+---+-------+-------+---------+---------+\n",
      "|        001|     John Doe| 30|50000.0|10000.0|        1|      two|\n",
      "|        002|   Jane Smith| 25|45000.0| 9000.0|        1|      two|\n",
      "|        003|    Bob Brown| 35|55000.0|11000.0|        1|      two|\n",
      "|        004|    Alice Lee| 28|48000.0| 9600.0|        1|      two|\n",
      "|        005|    Jack Chan| 40|60000.0|12000.0|        1|      two|\n",
      "|        006|    Jill Wong| 32|52000.0|10400.0|        1|      two|\n",
      "|        007|James Johnson| 42|70000.0|14000.0|        1|      two|\n",
      "|        008|     Kate Kim| 29|51000.0|10200.0|        1|      two|\n",
      "|        009|      Tom Tan| 33|58000.0|11600.0|        1|      two|\n",
      "|        010|     Lisa Lee| 27|47000.0| 9400.0|        1|      two|\n",
      "|        011|   David Park| 38|65000.0|13000.0|        1|      two|\n",
      "|        012|   Susan Chen| 31|54000.0|10800.0|        1|      two|\n",
      "|        013|    Brian Kim| 45|75000.0|15000.0|        1|      two|\n",
      "|        014|    Emily Lee| 26|46000.0| 9200.0|        1|      two|\n",
      "|        015|  Michael Lee| 37|63000.0|12600.0|        1|      two|\n",
      "|        016|  Kelly Zhang| 30|49000.0| 9800.0|        1|      two|\n",
      "|        017|  George Wang| 34|57000.0|11400.0|        1|      two|\n",
      "|        018|    Nancy Liu| 29|50000.0|10000.0|        1|      two|\n",
      "|        019|  Steven Chen| 36|62000.0|12400.0|        1|      two|\n",
      "|        020|    Grace Kim| 32|53000.0|10600.0|        1|      two|\n",
      "+-----------+-------------+---+-------+-------+---------+---------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "emp_new_cols.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "f5a249be-bfab-4bde-ae61-eb6782660b49",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Renaming Columns\n",
    "# select employee_id as emp_id, name, age, salary, tax, columnOne, columnTwo from emp_new_cols\n",
    "\n",
    "emp_1 = emp_new_cols.withColumnRenamed(\"employee_id\", \"emp_id\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "e590c9bb-3519-4511-921b-e851f385deda",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------+-------------+---+-------+-------+---------+---------+\n",
      "|emp_id|         name|age| salary|    tax|columnOne|columnTwo|\n",
      "+------+-------------+---+-------+-------+---------+---------+\n",
      "|   001|     John Doe| 30|50000.0|10000.0|        1|      two|\n",
      "|   002|   Jane Smith| 25|45000.0| 9000.0|        1|      two|\n",
      "|   003|    Bob Brown| 35|55000.0|11000.0|        1|      two|\n",
      "|   004|    Alice Lee| 28|48000.0| 9600.0|        1|      two|\n",
      "|   005|    Jack Chan| 40|60000.0|12000.0|        1|      two|\n",
      "|   006|    Jill Wong| 32|52000.0|10400.0|        1|      two|\n",
      "|   007|James Johnson| 42|70000.0|14000.0|        1|      two|\n",
      "|   008|     Kate Kim| 29|51000.0|10200.0|        1|      two|\n",
      "|   009|      Tom Tan| 33|58000.0|11600.0|        1|      two|\n",
      "|   010|     Lisa Lee| 27|47000.0| 9400.0|        1|      two|\n",
      "|   011|   David Park| 38|65000.0|13000.0|        1|      two|\n",
      "|   012|   Susan Chen| 31|54000.0|10800.0|        1|      two|\n",
      "|   013|    Brian Kim| 45|75000.0|15000.0|        1|      two|\n",
      "|   014|    Emily Lee| 26|46000.0| 9200.0|        1|      two|\n",
      "|   015|  Michael Lee| 37|63000.0|12600.0|        1|      two|\n",
      "|   016|  Kelly Zhang| 30|49000.0| 9800.0|        1|      two|\n",
      "|   017|  George Wang| 34|57000.0|11400.0|        1|      two|\n",
      "|   018|    Nancy Liu| 29|50000.0|10000.0|        1|      two|\n",
      "|   019|  Steven Chen| 36|62000.0|12400.0|        1|      two|\n",
      "|   020|    Grace Kim| 32|53000.0|10600.0|        1|      two|\n",
      "+------+-------------+---+-------+-------+---------+---------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "emp_1.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "ae885c86-5d7a-4744-8ea7-d16849e68eea",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Column names with Spaces\n",
    "# select employee_id as emp_id, name, age, salary, tax, columnOne, columnTwo as `Column Two` from emp_new_cols\n",
    "\n",
    "emp_2 = emp_new_cols.withColumnRenamed(\"columnTwo\", \"Column Two\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "fe56bb7d-8fb7-40b3-83ce-0e9edf37d1ba",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------+-------------+---+-------+-------+---------+----------+\n",
      "|employee_id|         name|age| salary|    tax|columnOne|Column Two|\n",
      "+-----------+-------------+---+-------+-------+---------+----------+\n",
      "|        001|     John Doe| 30|50000.0|10000.0|        1|       two|\n",
      "|        002|   Jane Smith| 25|45000.0| 9000.0|        1|       two|\n",
      "|        003|    Bob Brown| 35|55000.0|11000.0|        1|       two|\n",
      "|        004|    Alice Lee| 28|48000.0| 9600.0|        1|       two|\n",
      "|        005|    Jack Chan| 40|60000.0|12000.0|        1|       two|\n",
      "|        006|    Jill Wong| 32|52000.0|10400.0|        1|       two|\n",
      "|        007|James Johnson| 42|70000.0|14000.0|        1|       two|\n",
      "|        008|     Kate Kim| 29|51000.0|10200.0|        1|       two|\n",
      "|        009|      Tom Tan| 33|58000.0|11600.0|        1|       two|\n",
      "|        010|     Lisa Lee| 27|47000.0| 9400.0|        1|       two|\n",
      "|        011|   David Park| 38|65000.0|13000.0|        1|       two|\n",
      "|        012|   Susan Chen| 31|54000.0|10800.0|        1|       two|\n",
      "|        013|    Brian Kim| 45|75000.0|15000.0|        1|       two|\n",
      "|        014|    Emily Lee| 26|46000.0| 9200.0|        1|       two|\n",
      "|        015|  Michael Lee| 37|63000.0|12600.0|        1|       two|\n",
      "|        016|  Kelly Zhang| 30|49000.0| 9800.0|        1|       two|\n",
      "|        017|  George Wang| 34|57000.0|11400.0|        1|       two|\n",
      "|        018|    Nancy Liu| 29|50000.0|10000.0|        1|       two|\n",
      "|        019|  Steven Chen| 36|62000.0|12400.0|        1|       two|\n",
      "|        020|    Grace Kim| 32|53000.0|10600.0|        1|       two|\n",
      "+-----------+-------------+---+-------+-------+---------+----------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "emp_2.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "585c81ff-0ffd-4ef8-9a4f-24fcafab93fd",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Remove Column\n",
    "\n",
    "emp_dropped = emp_new_cols.drop(\"columnTwo\", \"columnOne\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "3dce357a-11ec-4e80-9cd4-8a59223b1a78",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------+-------------+---+-------+-------+\n",
      "|employee_id|         name|age| salary|    tax|\n",
      "+-----------+-------------+---+-------+-------+\n",
      "|        001|     John Doe| 30|50000.0|10000.0|\n",
      "|        002|   Jane Smith| 25|45000.0| 9000.0|\n",
      "|        003|    Bob Brown| 35|55000.0|11000.0|\n",
      "|        004|    Alice Lee| 28|48000.0| 9600.0|\n",
      "|        005|    Jack Chan| 40|60000.0|12000.0|\n",
      "|        006|    Jill Wong| 32|52000.0|10400.0|\n",
      "|        007|James Johnson| 42|70000.0|14000.0|\n",
      "|        008|     Kate Kim| 29|51000.0|10200.0|\n",
      "|        009|      Tom Tan| 33|58000.0|11600.0|\n",
      "|        010|     Lisa Lee| 27|47000.0| 9400.0|\n",
      "|        011|   David Park| 38|65000.0|13000.0|\n",
      "|        012|   Susan Chen| 31|54000.0|10800.0|\n",
      "|        013|    Brian Kim| 45|75000.0|15000.0|\n",
      "|        014|    Emily Lee| 26|46000.0| 9200.0|\n",
      "|        015|  Michael Lee| 37|63000.0|12600.0|\n",
      "|        016|  Kelly Zhang| 30|49000.0| 9800.0|\n",
      "|        017|  George Wang| 34|57000.0|11400.0|\n",
      "|        018|    Nancy Liu| 29|50000.0|10000.0|\n",
      "|        019|  Steven Chen| 36|62000.0|12400.0|\n",
      "|        020|    Grace Kim| 32|53000.0|10600.0|\n",
      "+-----------+-------------+---+-------+-------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "emp_dropped.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "13a83875-2022-4c1b-8845-dce7121767d4",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Filter data \n",
    "# select employee_id as emp_id, name, age, salary, tax, columnOne from emp_col_dropped where tax > 1000\n",
    "\n",
    "emp_filtered = emp_dropped.where(\"tax > 10000\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "e52d1830-4c09-474f-8324-4dc16ca9e29b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------+-------------+---+-------+-------+\n",
      "|employee_id|         name|age| salary|    tax|\n",
      "+-----------+-------------+---+-------+-------+\n",
      "|        003|    Bob Brown| 35|55000.0|11000.0|\n",
      "|        005|    Jack Chan| 40|60000.0|12000.0|\n",
      "|        006|    Jill Wong| 32|52000.0|10400.0|\n",
      "|        007|James Johnson| 42|70000.0|14000.0|\n",
      "|        008|     Kate Kim| 29|51000.0|10200.0|\n",
      "|        009|      Tom Tan| 33|58000.0|11600.0|\n",
      "|        011|   David Park| 38|65000.0|13000.0|\n",
      "|        012|   Susan Chen| 31|54000.0|10800.0|\n",
      "|        013|    Brian Kim| 45|75000.0|15000.0|\n",
      "|        015|  Michael Lee| 37|63000.0|12600.0|\n",
      "|        017|  George Wang| 34|57000.0|11400.0|\n",
      "|        019|  Steven Chen| 36|62000.0|12400.0|\n",
      "|        020|    Grace Kim| 32|53000.0|10600.0|\n",
      "+-----------+-------------+---+-------+-------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "emp_filtered.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "40a102d5-88bb-4183-ab16-a04a01f727d1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# LIMIT data\n",
    "# select employee_id as emp_id, name, age, salary, tax, columnOne from emp_filtered limit 5\n",
    "\n",
    "emp_limit = emp_filtered.limit(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "eb8efee9-c081-40b0-a311-22f058be87c0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------+---------+---+-------+-------+\n",
      "|employee_id|     name|age| salary|    tax|\n",
      "+-----------+---------+---+-------+-------+\n",
      "|        003|Bob Brown| 35|55000.0|11000.0|\n",
      "|        005|Jack Chan| 40|60000.0|12000.0|\n",
      "+-----------+---------+---+-------+-------+\n",
      "only showing top 2 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Show data\n",
    "\n",
    "emp_limit.show(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c6c5dee2-e504-4f31-bf38-482bd0a7f7fa",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Bonus TIP\n",
    "# Add multiple columns\n",
    "\n",
    "columns = {\n",
    "    \"tax\" : col(\"salary\") * 0.2 ,\n",
    "    \"oneNumber\" : lit(1), \n",
    "    \"columnTwo\" : lit(\"two\")\n",
    "}\n",
    "\n",
    "emp_final = emp.withColumns(columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "263c1d47-3bba-4d0d-889d-e3782baec974",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------+-------------+-------------+---+------+------+----------+-------+---------+---------+\n",
      "|employee_id|department_id|         name|age|gender|salary| hire_date|    tax|oneNumber|columnTwo|\n",
      "+-----------+-------------+-------------+---+------+------+----------+-------+---------+---------+\n",
      "|        001|          101|     John Doe| 30|  Male| 50000|2015-01-01|10000.0|        1|      two|\n",
      "|        002|          101|   Jane Smith| 25|Female| 45000|2016-02-15| 9000.0|        1|      two|\n",
      "|        003|          102|    Bob Brown| 35|  Male| 55000|2014-05-01|11000.0|        1|      two|\n",
      "|        004|          102|    Alice Lee| 28|Female| 48000|2017-09-30| 9600.0|        1|      two|\n",
      "|        005|          103|    Jack Chan| 40|  Male| 60000|2013-04-01|12000.0|        1|      two|\n",
      "|        006|          103|    Jill Wong| 32|Female| 52000|2018-07-01|10400.0|        1|      two|\n",
      "|        007|          101|James Johnson| 42|  Male| 70000|2012-03-15|14000.0|        1|      two|\n",
      "|        008|          102|     Kate Kim| 29|Female| 51000|2019-10-01|10200.0|        1|      two|\n",
      "|        009|          103|      Tom Tan| 33|  Male| 58000|2016-06-01|11600.0|        1|      two|\n",
      "|        010|          104|     Lisa Lee| 27|Female| 47000|2018-08-01| 9400.0|        1|      two|\n",
      "|        011|          104|   David Park| 38|  Male| 65000|2015-11-01|13000.0|        1|      two|\n",
      "|        012|          105|   Susan Chen| 31|Female| 54000|2017-02-15|10800.0|        1|      two|\n",
      "|        013|          106|    Brian Kim| 45|  Male| 75000|2011-07-01|15000.0|        1|      two|\n",
      "|        014|          107|    Emily Lee| 26|Female| 46000|2019-01-01| 9200.0|        1|      two|\n",
      "|        015|          106|  Michael Lee| 37|  Male| 63000|2014-09-30|12600.0|        1|      two|\n",
      "|        016|          107|  Kelly Zhang| 30|Female| 49000|2018-04-01| 9800.0|        1|      two|\n",
      "|        017|          105|  George Wang| 34|  Male| 57000|2016-03-15|11400.0|        1|      two|\n",
      "|        018|          104|    Nancy Liu| 29|Female| 50000|2017-06-01|10000.0|        1|      two|\n",
      "|        019|          103|  Steven Chen| 36|  Male| 62000|2015-08-01|12400.0|        1|      two|\n",
      "|        020|          102|    Grace Kim| 32|Female| 53000|2018-11-01|10600.0|        1|      two|\n",
      "+-----------+-------------+-------------+---+------+------+----------+-------+---------+---------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "emp_final.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a2260194-4921-46d3-8e45-d67cfec20816",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}