Tutorial 5- Pyspark With Python-GroupBy And Aggregate Functions.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "2efa0fce",
   "metadata": {},
   "source": [
    "### Pyspark GroupBy And Aggregate Functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "8f336300",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql import SparkSession"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "23513a5d",
   "metadata": {},
   "outputs": [],
   "source": [
    "spark=SparkSession.builder.appName('Agg').getOrCreate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "248b3a5c",
   "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://host.docker.internal:4041\">Spark UI</a></p>\n",
       "\n",
       "            <dl>\n",
       "              <dt>Version</dt>\n",
       "                <dd><code>v3.1.1</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 0x1e6c8c9db08>"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "spark"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "4d3bd081",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_pyspark=spark.read.csv('test3.csv',header=True,inferSchema=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "7ed791ed",
   "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": 6,
   "id": "d57d24ca",
   "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": 9,
   "id": "f15f8197",
   "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",
    "### Grouped to find the maximum salary\n",
    "df_pyspark.groupBy('Name').sum().show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "fc122ace",
   "metadata": {},
   "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": [
    "df_pyspark.groupBy('Name').avg().show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "151d2264",
   "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 Departmernts  which gives maximum salary\n",
    "df_pyspark.groupBy('Departments').sum().show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "66fe5552",
   "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": [
    "df_pyspark.groupBy('Departments').mean().show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "bc7bf192",
   "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": [
    "df_pyspark.groupBy('Departments').count().show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "37b26cbe",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------+\n",
      "|sum(Salary)|\n",
      "+-----------+\n",
      "|      73000|\n",
      "+-----------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df_pyspark.agg({'Salary':'sum'}).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bb21f03f",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1c7d8f83",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9dc7aa65",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fdd3fbac",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "375a6fda",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0aa434e6",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c82781ad",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.7.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}