GroupBy and Aggregate Functions.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "86ada58e",
   "metadata": {},
   "source": [
    "Create a Spark Session"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "131b7a3a",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql import SparkSession"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2a35e316",
   "metadata": {},
   "source": [
    "Start the Spark Session"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "e040f305",
   "metadata": {},
   "outputs": [],
   "source": [
    "spark = SparkSession.builder.appName('GroupBy and Aggregate').getOrCreate()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f4b963c7",
   "metadata": {},
   "source": [
    "Load and display the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "fff40ce8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------+-------+-----+\n",
      "|Company| Person|Sales|\n",
      "+-------+-------+-----+\n",
      "|   GOOG|    Sam|200.0|\n",
      "|   GOOG|Charlie|120.0|\n",
      "|   GOOG|  Frank|340.0|\n",
      "|   MSFT|   Tina|600.0|\n",
      "|   MSFT|    Amy|124.0|\n",
      "|   MSFT|Vanessa|243.0|\n",
      "|     FB|   Carl|870.0|\n",
      "|     FB|  Sarah|350.0|\n",
      "|   APPL|   John|250.0|\n",
      "|   APPL|  Linda|130.0|\n",
      "|   APPL|   Mike|750.0|\n",
      "|   APPL|  Chris|350.0|\n",
      "+-------+-------+-----+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "filePath = ('sales_info.csv')\n",
    "df = spark.read.csv(filePath,inferSchema=True, header=True)\n",
    "df.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9e3ea32b",
   "metadata": {},
   "source": [
    "Check the data type by using the schema"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "123ebdda",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- Company: string (nullable = true)\n",
      " |-- Person: string (nullable = true)\n",
      " |-- Sales: double (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.printSchema()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "409f32eb",
   "metadata": {},
   "source": [
    "Get the mean by company"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "c05e96e2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------+-----------------+\n",
      "|Company|       avg(Sales)|\n",
      "+-------+-----------------+\n",
      "|   APPL|            370.0|\n",
      "|   GOOG|            220.0|\n",
      "|     FB|            610.0|\n",
      "|   MSFT|322.3333333333333|\n",
      "+-------+-----------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.groupBy('Company').mean().show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "db90fd0c",
   "metadata": {},
   "source": [
    "Get the count by company"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "7dd10573",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------+-----+\n",
      "|Company|count|\n",
      "+-------+-----+\n",
      "|   APPL|    4|\n",
      "|   GOOG|    3|\n",
      "|     FB|    2|\n",
      "|   MSFT|    3|\n",
      "+-------+-----+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.groupBy('Company').count().show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "16024724",
   "metadata": {},
   "source": [
    "Get the max by company"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "53196c70",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------+----------+\n",
      "|Company|max(Sales)|\n",
      "+-------+----------+\n",
      "|   APPL|     750.0|\n",
      "|   GOOG|     340.0|\n",
      "|     FB|     870.0|\n",
      "|   MSFT|     600.0|\n",
      "+-------+----------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.groupBy('Company').max().show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1a67b8f7",
   "metadata": {},
   "source": [
    "Get the sum by company"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "663846a8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------+----------+\n",
      "|Company|sum(Sales)|\n",
      "+-------+----------+\n",
      "|   APPL|    1480.0|\n",
      "|   GOOG|     660.0|\n",
      "|     FB|    1220.0|\n",
      "|   MSFT|     967.0|\n",
      "+-------+----------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.groupBy('Company').sum().show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7fcf93da",
   "metadata": {},
   "source": [
    "Using the .agg() method"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "eb6ced83",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+\n",
      "|max(Sales)|\n",
      "+----------+\n",
      "|     870.0|\n",
      "+----------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.agg({'Sales': 'max'}).show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3825bd23",
   "metadata": {},
   "source": [
    "Get the sum of total sales using the agg() property"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "3f352ab4",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+\n",
      "|sum(Sales)|\n",
      "+----------+\n",
      "|    4327.0|\n",
      "+----------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.agg({'Sales':'sum'}).show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c330fd3f",
   "metadata": {},
   "source": [
    "Importing functions "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "e77188c3",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql.functions import countDistinct, stddev, avg, format_number"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "d061bb72",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------+\n",
      "|ComCount|\n",
      "+--------+\n",
      "|       4|\n",
      "+--------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.select(countDistinct('Company').alias('ComCount')).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "51d9c6cf",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------------+\n",
      "|      AvergeSales|\n",
      "+-----------------+\n",
      "|360.5833333333333|\n",
      "+-----------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.select(avg('Sales').alias('AvergeSales')).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "7984f713",
   "metadata": {},
   "outputs": [],
   "source": [
    "showVal = df.select(avg('Sales').alias('AvergeSales'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "03722c47",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------+\n",
      "|AvgSales|\n",
      "+--------+\n",
      "|  360.58|\n",
      "+--------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "showVal.select(format_number('AvergeSales',2).alias('AvgSales')).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "17356246",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DataFrame[AvergeSales: double]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "showVal"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6f7a38a2",
   "metadata": {},
   "source": [
    "Working with Order By"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "88503f6d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------+-------+-----+\n",
      "|Company| Person|Sales|\n",
      "+-------+-------+-----+\n",
      "|   APPL|  Chris|350.0|\n",
      "|   APPL|   John|250.0|\n",
      "|   APPL|  Linda|130.0|\n",
      "|   APPL|   Mike|750.0|\n",
      "|     FB|   Carl|870.0|\n",
      "|     FB|  Sarah|350.0|\n",
      "|   GOOG|  Frank|340.0|\n",
      "|   GOOG|    Sam|200.0|\n",
      "|   GOOG|Charlie|120.0|\n",
      "|   MSFT|   Tina|600.0|\n",
      "|   MSFT|    Amy|124.0|\n",
      "|   MSFT|Vanessa|243.0|\n",
      "+-------+-------+-----+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.orderBy('Company').show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a297ab97",
   "metadata": {},
   "source": [
    "Order by company in descending order"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "e5404407",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------+-------+-----+\n",
      "|Company| Person|Sales|\n",
      "+-------+-------+-----+\n",
      "|   MSFT|    Amy|124.0|\n",
      "|   MSFT|   Tina|600.0|\n",
      "|   MSFT|Vanessa|243.0|\n",
      "|   GOOG|    Sam|200.0|\n",
      "|   GOOG|  Frank|340.0|\n",
      "|   GOOG|Charlie|120.0|\n",
      "|     FB|  Sarah|350.0|\n",
      "|     FB|   Carl|870.0|\n",
      "|   APPL|   John|250.0|\n",
      "|   APPL|  Linda|130.0|\n",
      "|   APPL|  Chris|350.0|\n",
      "|   APPL|   Mike|750.0|\n",
      "+-------+-------+-----+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.orderBy(df['Company'].desc()).show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f242a8e8",
   "metadata": {},
   "source": [
    "THE END"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "59efa16f",
   "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.9.13"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}