groupby.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "1eb203e4-6e0e-47d5-89fc-064e7987ed28",
   "metadata": {},
   "source": [
    "## Groupby and Aggregate Function"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "f41ffa48-352c-48a0-96f4-b39c96db2527",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql import SparkSession"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "f1e63f7e-8a76-4090-8393-b3e8c3154ac4",
   "metadata": {},
   "outputs": [],
   "source": [
    "spark=SparkSession.builder.appName('Agg').getOrCreate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "a97c33f2-60a2-4c35-9ba1-9fa34775d44d",
   "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://hossains-mbp:4044\">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>dataframe</code></dd>\n",
       "            </dl>\n",
       "        </div>\n",
       "        \n",
       "            </div>\n",
       "        "
      ],
      "text/plain": [
       "<pyspark.sql.session.SparkSession at 0x7f8d9870e3d0>"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "spark"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "e9798be0-8d2f-498f-b1f8-b34aa5b192cd",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "df_pyspark=spark.read.csv('test3.csv', header=True, inferSchema=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "649fb843-e30c-4b15-a149-1247cb326263",
   "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": [
    "df_pyspark.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "1467b909-63a4-4059-a5ee-e0299aec65ef",
   "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": [
    "df_pyspark.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "75f3e7ed-399d-4e94-922f-677958b6c5d6",
   "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 operation\n",
    "## grouped to find the maximum salary\n",
    "df_pyspark.groupby('Name').sum().show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "4119e00b-0fa1-44f3-a03c-106e7e8510e5",
   "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": [
    "##groupby operation\n",
    "## groupby Departsments which gives maximum salary\n",
    "df_pyspark.groupby('Departments').sum().show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "b444cc53-f04b-485e-99e1-2f619a9883b2",
   "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": [
    "##groupby operation\n",
    "## groupby Departsments which gives maximum salary\n",
    "df_pyspark.groupby('Departments').mean().show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "fd1dd722-3428-4ec6-80dd-5286ab592c30",
   "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": [
    "##groupby operation\n",
    "## groupby Departsments which gives maximum salary\n",
    "df_pyspark.groupby('Departments').count().show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "22779c95-1040-4309-9bdb-8f307910fff3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------+\n",
      "|sum(Salary)|\n",
      "+-----------+\n",
      "|      73000|\n",
      "+-----------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "##groupby operation\n",
    "## directly agg function can be used\n",
    "df_pyspark.agg({'Salary':'sum'}).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4ba5a98e-4b3c-42a3-b003-ef705aa77418",
   "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.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}