07-DataFrame-Operations.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "fa80df4e-9ddc-4db6-a801-717cd67ae883",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Set the PySpark environment variables\n",
    "import os\n",
    "os.environ['SPARK_HOME'] = \"/Users/coder2j/Apps/Spark\"\n",
    "os.environ['PYSPARK_DRIVER_PYTHON'] = 'jupyter'\n",
    "os.environ['PYSPARK_DRIVER_PYTHON_OPTS'] = 'lab'\n",
    "os.environ['PYSPARK_PYTHON'] = 'python'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "01cd1819-fc7d-422e-916f-4ef8fc180bfb",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql import SparkSession\n",
    "\n",
    "# Create a SparkSession\n",
    "spark = SparkSession.builder.appName(\"DataFrame-Operations\").getOrCreate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "3a81039c-3491-4c8a-8ae4-5dab6b0af501",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "id,name,category,quantity,price\n",
      "1,iPhone,Electronics,10,899.99\n",
      "2,Macbook,Electronics,5,1299.99\n",
      "3,iPad,Electronics,15,499.99\n",
      "4,Samsung TV,Electronics,8,799.99\n",
      "5,LG TV,Electronics,10,699.99\n",
      "6,Nike Shoes,Clothing,30,99.99\n",
      "7,Adidas Shoes,Clothing,25,89.99\n",
      "8,Sony Headphones,Electronics,12,149.99\n",
      "9,Beats Headphones,Electronics,20,199.99\n"
     ]
    }
   ],
   "source": [
    "%%bash\n",
    "head -10 data/stocks.txt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "5b7e24a1-861d-4929-b3bd-5683f5bc5c1d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load the synthetic data into a DataFrame\n",
    "data_file_path = \"./data/stocks.txt\"\n",
    "df = spark.read.csv(data_file_path, header=True, inferSchema=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "cdacf979-c105-4f65-9f28-4efc4c88c07a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- id: integer (nullable = true)\n",
      " |-- name: string (nullable = true)\n",
      " |-- category: string (nullable = true)\n",
      " |-- quantity: integer (nullable = true)\n",
      " |-- price: double (nullable = true)\n",
      "\n",
      "Initial DataFrame:\n",
      "+---+----------------+-----------+--------+-------+\n",
      "| id|            name|   category|quantity|  price|\n",
      "+---+----------------+-----------+--------+-------+\n",
      "|  1|          iPhone|Electronics|      10| 899.99|\n",
      "|  2|         Macbook|Electronics|       5|1299.99|\n",
      "|  3|            iPad|Electronics|      15| 499.99|\n",
      "|  4|      Samsung TV|Electronics|       8| 799.99|\n",
      "|  5|           LG TV|Electronics|      10| 699.99|\n",
      "|  6|      Nike Shoes|   Clothing|      30|  99.99|\n",
      "|  7|    Adidas Shoes|   Clothing|      25|  89.99|\n",
      "|  8| Sony Headphones|Electronics|      12| 149.99|\n",
      "|  9|Beats Headphones|Electronics|      20| 199.99|\n",
      "| 10|    Dining Table|  Furniture|      10| 249.99|\n",
      "+---+----------------+-----------+--------+-------+\n",
      "only showing top 10 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Display schema of DataFrame\n",
    "df.printSchema()\n",
    "\n",
    "# Show the initial DataFrame\n",
    "print(\"Initial DataFrame:\")\n",
    "df.show(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f43e6f84-16f0-4f6f-a7cf-a3de49d6ea53",
   "metadata": {},
   "source": [
    "### Select: Choose specific columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "7552160c-a792-4817-ab7c-117d5440a52c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Selected Columns:\n",
      "+---+----------------+-------+\n",
      "| id|            name|  price|\n",
      "+---+----------------+-------+\n",
      "|  1|          iPhone| 899.99|\n",
      "|  2|         Macbook|1299.99|\n",
      "|  3|            iPad| 499.99|\n",
      "|  4|      Samsung TV| 799.99|\n",
      "|  5|           LG TV| 699.99|\n",
      "|  6|      Nike Shoes|  99.99|\n",
      "|  7|    Adidas Shoes|  89.99|\n",
      "|  8| Sony Headphones| 149.99|\n",
      "|  9|Beats Headphones| 199.99|\n",
      "| 10|    Dining Table| 249.99|\n",
      "+---+----------------+-------+\n",
      "only showing top 10 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Select specific columns\n",
    "selected_columns = df.select(\"id\", \"name\", \"price\")\n",
    "print(\"Selected Columns:\")\n",
    "selected_columns.show(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1ba2c8d3-1e26-456d-94e3-6afac5a4a4a7",
   "metadata": {},
   "source": [
    "### Filter: Apply conditions to filter rows."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "0fc8c608-2fa3-4565-849b-ebc2b62025fe",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Filtered Data: 12\n",
      "+---+--------------+-----------+--------+-----+\n",
      "| id|          name|   category|quantity|price|\n",
      "+---+--------------+-----------+--------+-----+\n",
      "|  6|    Nike Shoes|   Clothing|      30|99.99|\n",
      "|  7|  Adidas Shoes|   Clothing|      25|89.99|\n",
      "| 12|        Apples|       Food|     100|  0.5|\n",
      "| 13|       Bananas|       Food|     150| 0.25|\n",
      "| 14|       Oranges|       Food|     120| 0.75|\n",
      "| 15|Chicken Breast|       Food|      50| 3.99|\n",
      "| 16| Salmon Fillet|       Food|      30| 5.99|\n",
      "| 24|    Laptop Bag|Accessories|      25|29.99|\n",
      "| 25|      Backpack|Accessories|      30|24.99|\n",
      "| 28|         Jeans|   Clothing|      30|59.99|\n",
      "| 29|       T-shirt|   Clothing|      50|14.99|\n",
      "| 30|      Sneakers|   Clothing|      40|79.99|\n",
      "+---+--------------+-----------+--------+-----+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Filter rows based on a condition\n",
    "filtered_data = df.filter(df.quantity > 20)\n",
    "print(\"Filtered Data:\", filtered_data.count())\n",
    "filtered_data.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "18283acf-69eb-4140-a4dd-273c9eb5eafd",
   "metadata": {},
   "source": [
    "### GroupBy: Group data based on specific columns \n",
    "### Aggregations: Perform functions like sum, average, etc., on grouped data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "96d2db93-de0e-4707-81a8-3cfbb84dcf3d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Grouped and Aggregated Data:\n",
      "+-----------+-------------+------------------+\n",
      "|   category|sum(quantity)|        avg(price)|\n",
      "+-----------+-------------+------------------+\n",
      "|       Food|          450|2.2960000000000003|\n",
      "|     Sports|           35|             34.99|\n",
      "|Electronics|           98| 586.6566666666665|\n",
      "|   Clothing|          200|  99.2757142857143|\n",
      "|  Furniture|           41|            141.99|\n",
      "|Accessories|           55|             27.49|\n",
      "+-----------+-------------+------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# GroupBy and Aggregations\n",
    "grouped_data = df.groupBy(\"category\").agg({\"quantity\": \"sum\", \"price\": \"avg\"})\n",
    "print(\"Grouped and Aggregated Data:\")\n",
    "grouped_data.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a233a823-73b1-404f-b8e0-90ee5e78c4e4",
   "metadata": {},
   "source": [
    "### Join: Combine multiple DataFrames based on specified columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "494f996e-57ee-44c0-82d8-814ee777653e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Joined Data:\n",
      "+---+----------------+-----------+--------+-------+-----------+\n",
      "| id|            name|   category|quantity|  price|   category|\n",
      "+---+----------------+-----------+--------+-------+-----------+\n",
      "|  1|          iPhone|Electronics|      10| 899.99|Electronics|\n",
      "|  2|         Macbook|Electronics|       5|1299.99|Electronics|\n",
      "|  3|            iPad|Electronics|      15| 499.99|Electronics|\n",
      "|  4|      Samsung TV|Electronics|       8| 799.99|Electronics|\n",
      "|  5|           LG TV|Electronics|      10| 699.99|Electronics|\n",
      "|  6|      Nike Shoes|   Clothing|      30|  99.99|   Clothing|\n",
      "|  7|    Adidas Shoes|   Clothing|      25|  89.99|   Clothing|\n",
      "|  8| Sony Headphones|Electronics|      12| 149.99|Electronics|\n",
      "|  9|Beats Headphones|Electronics|      20| 199.99|Electronics|\n",
      "| 10|    Dining Table|  Furniture|      10| 249.99|  Furniture|\n",
      "+---+----------------+-----------+--------+-------+-----------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Join with another DataFrame\n",
    "df2 = df.select(\"id\", \"category\").limit(10)\n",
    "joined_data = df.join(df2, \"id\", \"inner\")\n",
    "print(\"Joined Data:\")\n",
    "joined_data.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6e71e549-8194-4a95-ae3a-9db0a6afa5dc",
   "metadata": {},
   "source": [
    "### Sort: Arrange rows based on one or more columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "133ab21b-84ca-48e7-a8bf-8c7e487e455c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Sorted Data:\n",
      "+---+--------------+-----------+--------+-----+\n",
      "| id|          name|   category|quantity|price|\n",
      "+---+--------------+-----------+--------+-----+\n",
      "| 13|       Bananas|       Food|     150| 0.25|\n",
      "| 12|        Apples|       Food|     100|  0.5|\n",
      "| 14|       Oranges|       Food|     120| 0.75|\n",
      "| 15|Chicken Breast|       Food|      50| 3.99|\n",
      "| 16| Salmon Fillet|       Food|      30| 5.99|\n",
      "| 29|       T-shirt|   Clothing|      50|14.99|\n",
      "| 19|      Yoga Mat|     Sports|      20|19.99|\n",
      "| 25|      Backpack|Accessories|      30|24.99|\n",
      "| 24|    Laptop Bag|Accessories|      25|29.99|\n",
      "| 20|  Dumbbell Set|     Sports|      15|49.99|\n",
      "+---+--------------+-----------+--------+-----+\n",
      "only showing top 10 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Sort by a column\n",
    "sorted_data = df.orderBy(\"price\")\n",
    "print(\"Sorted Data:\")\n",
    "sorted_data.show(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "a5a0a80e-dc5c-4569-ade9-e209560eccb8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Sorted Data Descending:\n",
      "+---+----------------+-----------+--------+-------+\n",
      "| id|            name|   category|quantity|  price|\n",
      "+---+----------------+-----------+--------+-------+\n",
      "|  2|         Macbook|Electronics|       5|1299.99|\n",
      "|  1|          iPhone|Electronics|      10| 899.99|\n",
      "|  4|      Samsung TV|Electronics|       8| 799.99|\n",
      "|  5|           LG TV|Electronics|      10| 699.99|\n",
      "| 26|          Camera|Electronics|      10| 599.99|\n",
      "|  3|            iPad|Electronics|      15| 499.99|\n",
      "| 10|    Dining Table|  Furniture|      10| 249.99|\n",
      "| 17|  Leather Jacket|   Clothing|      15| 199.99|\n",
      "|  9|Beats Headphones|Electronics|      20| 199.99|\n",
      "| 18|     Winter Coat|   Clothing|      10| 149.99|\n",
      "+---+----------------+-----------+--------+-------+\n",
      "only showing top 10 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Sort by a column desc\n",
    "from pyspark.sql.functions import col, desc\n",
    "sorted_data = df.orderBy(col(\"price\").desc(), col(\"id\").desc())\n",
    "print(\"Sorted Data Descending:\")\n",
    "sorted_data.show(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "67580e00-93f8-4579-9972-fc64a0654366",
   "metadata": {},
   "source": [
    "### Distinct: Get unique rows."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "744e6638-5689-4509-9df4-4abb03cd9e9b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Distinct Product Categories:\n",
      "+-----------+\n",
      "|   category|\n",
      "+-----------+\n",
      "|       Food|\n",
      "|     Sports|\n",
      "|Electronics|\n",
      "|   Clothing|\n",
      "|  Furniture|\n",
      "|Accessories|\n",
      "+-----------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Get distinct product category\n",
    "distinct_rows = df.select(\"category\").distinct()\n",
    "print(\"Distinct Product Categories:\")\n",
    "distinct_rows.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4c847aac-dcde-4589-aef7-c0ec95c2f80f",
   "metadata": {},
   "source": [
    "### Drop: Remove specified columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "21d4afa3-20b5-4299-931a-0fba2655b509",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Dropped Columns:\n",
      "+---+----------------+-------+\n",
      "| id|            name|  price|\n",
      "+---+----------------+-------+\n",
      "|  1|          iPhone| 899.99|\n",
      "|  2|         Macbook|1299.99|\n",
      "|  3|            iPad| 499.99|\n",
      "|  4|      Samsung TV| 799.99|\n",
      "|  5|           LG TV| 699.99|\n",
      "|  6|      Nike Shoes|  99.99|\n",
      "|  7|    Adidas Shoes|  89.99|\n",
      "|  8| Sony Headphones| 149.99|\n",
      "|  9|Beats Headphones| 199.99|\n",
      "| 10|    Dining Table| 249.99|\n",
      "+---+----------------+-------+\n",
      "only showing top 10 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Drop columns\n",
    "dropped_columns = df.drop(\"quantity\", \"category\")\n",
    "print(\"Dropped Columns:\")\n",
    "dropped_columns.show(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "afc28820-f951-4c1e-99bc-e56ff434cc11",
   "metadata": {},
   "source": [
    "### WithColumn: Add new calculated columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "fb391702-9e54-4e3d-822b-0d0f1d0d08e6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DataFrame with New Column:\n",
      "+---+----------------+-----------+--------+-------+-------+\n",
      "| id|            name|   category|quantity|  price|revenue|\n",
      "+---+----------------+-----------+--------+-------+-------+\n",
      "|  1|          iPhone|Electronics|      10| 899.99| 8999.9|\n",
      "|  2|         Macbook|Electronics|       5|1299.99|6499.95|\n",
      "|  3|            iPad|Electronics|      15| 499.99|7499.85|\n",
      "|  4|      Samsung TV|Electronics|       8| 799.99|6399.92|\n",
      "|  5|           LG TV|Electronics|      10| 699.99| 6999.9|\n",
      "|  6|      Nike Shoes|   Clothing|      30|  99.99| 2999.7|\n",
      "|  7|    Adidas Shoes|   Clothing|      25|  89.99|2249.75|\n",
      "|  8| Sony Headphones|Electronics|      12| 149.99|1799.88|\n",
      "|  9|Beats Headphones|Electronics|      20| 199.99| 3999.8|\n",
      "| 10|    Dining Table|  Furniture|      10| 249.99| 2499.9|\n",
      "+---+----------------+-----------+--------+-------+-------+\n",
      "only showing top 10 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Add a new calculated column\n",
    "df_with_new_column = df.withColumn(\"revenue\", df.quantity * df.price)\n",
    "print(\"DataFrame with New Column:\")\n",
    "df_with_new_column.show(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c2138074-68c4-403b-aac6-41fee4595417",
   "metadata": {},
   "source": [
    "### Alias: Rename columns for better readability."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "669657f4-63a0-48f6-bc9e-a1bebeae466e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DataFrame with Aliased Column:\n",
      "+---+----------------+-----------+--------+-------------+\n",
      "| id|            name|   category|quantity|product_price|\n",
      "+---+----------------+-----------+--------+-------------+\n",
      "|  1|          iPhone|Electronics|      10|       899.99|\n",
      "|  2|         Macbook|Electronics|       5|      1299.99|\n",
      "|  3|            iPad|Electronics|      15|       499.99|\n",
      "|  4|      Samsung TV|Electronics|       8|       799.99|\n",
      "|  5|           LG TV|Electronics|      10|       699.99|\n",
      "|  6|      Nike Shoes|   Clothing|      30|        99.99|\n",
      "|  7|    Adidas Shoes|   Clothing|      25|        89.99|\n",
      "|  8| Sony Headphones|Electronics|      12|       149.99|\n",
      "|  9|Beats Headphones|Electronics|      20|       199.99|\n",
      "| 10|    Dining Table|  Furniture|      10|       249.99|\n",
      "+---+----------------+-----------+--------+-------------+\n",
      "only showing top 10 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Rename columns using alias\n",
    "df_with_alias = df.withColumnRenamed(\"price\", \"product_price\")\n",
    "print(\"DataFrame with Aliased Column:\")\n",
    "df_with_alias.show(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "f305239c-fa2b-4378-9de8-bfc58e5f244f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Stop the SparkSession\n",
    "spark.stop()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b89aa6d1-157a-46da-b9a2-c09feeb2c82e",
   "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.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}