Spark DataFrame Groupby and Aggregate Operations.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "4f52d406",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql import SparkSession\n",
    "from pyspark.sql.functions import avg, count_distinct, stddev, percent_rank, kurtosis, skewness, format_number"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "09a1de01",
   "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://DESKTOP-BJS1Q67:4042\">Spark UI</a></p>\n",
       "\n",
       "            <dl>\n",
       "              <dt>Version</dt>\n",
       "                <dd><code>v3.5.0</code></dd>\n",
       "              <dt>Master</dt>\n",
       "                <dd><code>local[*]</code></dd>\n",
       "              <dt>AppName</dt>\n",
       "                <dd><code>aggs</code></dd>\n",
       "            </dl>\n",
       "        </div>\n",
       "        \n",
       "            </div>\n",
       "        "
      ],
      "text/plain": [
       "<pyspark.sql.session.SparkSession at 0x27b18afda50>"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "spark = SparkSession.builder.appName('aggs').getOrCreate()\n",
    "spark"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "6d85e4d4",
   "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": [
    "df = spark.read.csv('sales_info.csv',inferSchema=True,header=True)\n",
    "df.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "f25478b0",
   "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": "1e919e1d",
   "metadata": {},
   "source": [
    "## Group By Aggregation Functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "b27956e6",
   "metadata": {
    "scrolled": true
   },
   "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": "code",
   "execution_count": 8,
   "id": "e04ab45a",
   "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": "code",
   "execution_count": 9,
   "id": "8d9be163",
   "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": "code",
   "execution_count": 10,
   "id": "2abebc69",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------+----------+\n",
      "|Company|min(Sales)|\n",
      "+-------+----------+\n",
      "|   APPL|     130.0|\n",
      "|   GOOG|     120.0|\n",
      "|     FB|     350.0|\n",
      "|   MSFT|     124.0|\n",
      "+-------+----------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.groupby('Company').min().show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "99181bc2",
   "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": "code",
   "execution_count": 12,
   "id": "91db781e",
   "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": "code",
   "execution_count": 13,
   "id": "fe310c2a",
   "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": "code",
   "execution_count": 14,
   "id": "9537fa81",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+\n",
      "|min(Sales)|\n",
      "+----------+\n",
      "|     120.0|\n",
      "+----------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.agg({'Sales': 'min'}).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "7b0bf876",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------+\n",
      "|count(Person)|\n",
      "+-------------+\n",
      "|           12|\n",
      "+-------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.agg({'Person': 'count'}).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "be21d566",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "GroupedData[grouping expressions: [Company], value: [Company: string, Person: string ... 1 more field], type: GroupBy]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped_data = df.groupby('Company')\n",
    "grouped_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "a29cde5b",
   "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": [
    "grouped_data.agg({'Sales': 'sum'}).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "87ae8eda",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------------------+\n",
      "|count(DISTINCT Sales)|\n",
      "+---------------------+\n",
      "|                   11|\n",
      "+---------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.select(count_distinct('Sales')).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "f09ecb3f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------------+\n",
      "|    Average Sales|\n",
      "+-----------------+\n",
      "|360.5833333333333|\n",
      "+-----------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.select(avg('Sales').alias('Average Sales')).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "e3071625",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------------------+\n",
      "|   skewness(Sales)|\n",
      "+------------------+\n",
      "|0.9616687919197199|\n",
      "+------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.select(skewness('Sales')).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "2b2c397e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+\n",
      "|     kurtosis(Sales)|\n",
      "+--------------------+\n",
      "|-0.36479709151572637|\n",
      "+--------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.select(kurtosis('Sales')).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "d3b5c094",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------------------+\n",
      "|     stddev(Sales)|\n",
      "+------------------+\n",
      "|250.08742410799007|\n",
      "+------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.select(stddev('Sales')).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "4fe55ef0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------------------+\n",
      "|               std|\n",
      "+------------------+\n",
      "|250.08742410799007|\n",
      "+------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "sales_std = df.select(stddev('Sales').alias('std'))\n",
    "sales_std.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "c09109d8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------------------------+\n",
      "|Sales Standard Deviation|\n",
      "+------------------------+\n",
      "|                  250.09|\n",
      "+------------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "sales_std.select(format_number('std',2).alias('Sales Standard Deviation')).show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "91f0e3d6",
   "metadata": {},
   "source": [
    "## Sorting data using Order By operation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "4da6e679",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------+-------+-----+\n",
      "|Company| Person|Sales|\n",
      "+-------+-------+-----+\n",
      "|   GOOG|Charlie|120.0|\n",
      "|   MSFT|    Amy|124.0|\n",
      "|   APPL|  Linda|130.0|\n",
      "|   GOOG|    Sam|200.0|\n",
      "|   MSFT|Vanessa|243.0|\n",
      "|   APPL|   John|250.0|\n",
      "|   GOOG|  Frank|340.0|\n",
      "|     FB|  Sarah|350.0|\n",
      "|   APPL|  Chris|350.0|\n",
      "|   MSFT|   Tina|600.0|\n",
      "|   APPL|   Mike|750.0|\n",
      "|     FB|   Carl|870.0|\n",
      "+-------+-------+-----+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.orderBy('Sales').show() # Sort a Spark DataFrame column in ascending order"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "73f5102b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------+-------+-----+\n",
      "|Company| Person|Sales|\n",
      "+-------+-------+-----+\n",
      "|     FB|   Carl|870.0|\n",
      "|   APPL|   Mike|750.0|\n",
      "|   MSFT|   Tina|600.0|\n",
      "|     FB|  Sarah|350.0|\n",
      "|   APPL|  Chris|350.0|\n",
      "|   GOOG|  Frank|340.0|\n",
      "|   APPL|   John|250.0|\n",
      "|   MSFT|Vanessa|243.0|\n",
      "|   GOOG|    Sam|200.0|\n",
      "|   APPL|  Linda|130.0|\n",
      "|   MSFT|    Amy|124.0|\n",
      "|   GOOG|Charlie|120.0|\n",
      "+-------+-------+-----+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Sort a Spark DataFrame column in descending order\n",
    "df.orderBy(df['Sales'].desc()).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "d5e62a43",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(Column<'Sales DESC NULLS LAST'>, pyspark.sql.column.Column)"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Sales'].desc(), type(df['Sales'].desc())"
   ]
  }
 ],
 "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.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}