PySpark Tutorial 5.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "f0a3fe03",
   "metadata": {},
   "source": [
    "# PySpark GroupBy And Aggregation Functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "02d7c5c0",
   "metadata": {},
   "outputs": [],
   "source": [
    "#import paskages \n",
    "from pyspark.sql import SparkSession"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "6a17957e",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Creating a SparkSession \n",
    "spark = SparkSession.builder.appName(\"Agg\").getOrCreate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "b162061f",
   "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-2001:4041\">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>Agg</code></dd>\n",
       "            </dl>\n",
       "        </div>\n",
       "        \n",
       "            </div>\n",
       "        "
      ],
      "text/plain": [
       "<pyspark.sql.session.SparkSession at 0x209d9943ca0>"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "spark"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "af19f1ee",
   "metadata": {},
   "outputs": [],
   "source": [
    "#read data\n",
    "df_pyspark = spark.read.csv(\"text5.csv\" , header=True  , inferSchema=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "fe870a90",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------+------------+------+\n",
      "|     Name| Departments|salary|\n",
      "+---------+------------+------+\n",
      "|    Krish|Data Science| 10000|\n",
      "|    Krish|         IOT|  5000|\n",
      "|   Mahesh|    Big Data|  4000|\n",
      "|    Krish|    Big Data|  4000|\n",
      "|   Mahesh|Data Science|  3000|\n",
      "|Sudhanshu|Data Science| 20000|\n",
      "|Sudhanshu|         IOT| 10000|\n",
      "|Sudhanshu|    Big Data|  5000|\n",
      "|    Sunny|Data Science| 10000|\n",
      "|    Sunny|    Big Data|  2000|\n",
      "+---------+------------+------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "#show data \n",
    "df_pyspark.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "f1cefe22",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- Name: string (nullable = true)\n",
      " |-- Departments: string (nullable = true)\n",
      " |-- salary: integer (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "#print Schema\n",
    "df_pyspark.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f52e8087",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "afa70804",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------+-----------+\n",
      "|     Name|sum(salary)|\n",
      "+---------+-----------+\n",
      "|Sudhanshu|      35000|\n",
      "|    Sunny|      12000|\n",
      "|    Krish|      19000|\n",
      "|   Mahesh|       7000|\n",
      "+---------+-----------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "#groupby\n",
    "# to group DataFrame by \"Name\" column and then calculate the sum for each group.\n",
    "#Groupby ti find sum salary\n",
    "df_pyspark.groupBy(\"Name\").sum().show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "1f2e27af",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------+-----------+\n",
      "|     Name|max(salary)|\n",
      "+---------+-----------+\n",
      "|Sudhanshu|      20000|\n",
      "|    Sunny|      10000|\n",
      "|    Krish|      10000|\n",
      "|   Mahesh|       4000|\n",
      "+---------+-----------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df_pyspark.groupBy(\"Name\").max().show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "2733f02d",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------+-----------+\n",
      "|     Name|min(salary)|\n",
      "+---------+-----------+\n",
      "|Sudhanshu|       5000|\n",
      "|    Sunny|       2000|\n",
      "|    Krish|       4000|\n",
      "|   Mahesh|       3000|\n",
      "+---------+-----------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df_pyspark.groupBy(\"Name\").min().show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "1153eb3b",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------+------------------+\n",
      "|     Name|       avg(salary)|\n",
      "+---------+------------------+\n",
      "|Sudhanshu|11666.666666666666|\n",
      "|    Sunny|            6000.0|\n",
      "|    Krish| 6333.333333333333|\n",
      "|   Mahesh|            3500.0|\n",
      "+---------+------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Group by name and calculate the average values for each column\n",
    "\n",
    "df_pyspark.groupBy(\"Name\").avg().show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "0b6df19a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------------+-----------+\n",
      "| Departments|sum(salary)|\n",
      "+------------+-----------+\n",
      "|         IOT|      15000|\n",
      "|    Big Data|      15000|\n",
      "|Data Science|      43000|\n",
      "+------------+-----------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Group by departments and calculate  sum of salaries\n",
    "df_pyspark.groupBy(\"Departments\").sum().show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "04b9ccb2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------------+-----------+\n",
      "| Departments|avg(salary)|\n",
      "+------------+-----------+\n",
      "|         IOT|     7500.0|\n",
      "|    Big Data|     3750.0|\n",
      "|Data Science|    10750.0|\n",
      "+------------+-----------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Group by departments and calculate  mean of salaries\n",
    "df_pyspark.groupBy(\"Departments\").mean().show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "1b84fe5b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------------+-----------+\n",
      "| Departments|sum(salary)|\n",
      "+------------+-----------+\n",
      "|         IOT|      15000|\n",
      "|    Big Data|      15000|\n",
      "|Data Science|      43000|\n",
      "+------------+-----------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Group by departments and calculate  sum of salaries\n",
    "df_pyspark.groupBy(\"Departments\").sum().show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "87053359",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------------+-----+\n",
      "| Departments|count|\n",
      "+------------+-----+\n",
      "|         IOT|    2|\n",
      "|    Big Data|    4|\n",
      "|Data Science|    4|\n",
      "+------------+-----+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Group by departments and count  number of records in each department\n",
    "\n",
    "df_pyspark.groupBy(\"Departments\").count().show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "008fb59e",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "3d6f3350",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------+\n",
      "|sum(Salary)|\n",
      "+-----------+\n",
      "|      73000|\n",
      "+-----------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Calculate the total sum of salary\n",
    "df_pyspark.agg({\"Salary\":\"sum\"}).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "2954d621",
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------+\n",
      "|avg(Salary)|\n",
      "+-----------+\n",
      "|     7300.0|\n",
      "+-----------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Calculate the total mean of salary\n",
    "\n",
    "df_pyspark.agg({\"Salary\":\"mean\"}).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "d2b463c3",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------------------+\n",
      "|count(Departments)|\n",
      "+------------------+\n",
      "|                10|\n",
      "+------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Calculate the total count of salary\n",
    "\n",
    "df_pyspark.agg({\"Departments\":'count'}).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d45b14fe",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8e25ae4a",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "692cdd47",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "21843e95",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e67155f9",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b64fc435",
   "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"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}