Big_Data_CA2_PySpark_Aggregate_Functions.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "Big Data CA2 - PySpark Aggregate Functions",
      "provenance": [],
      "collapsed_sections": [],
      "toc_visible": true,
      "include_colab_link": true
    },
    "kernelspec": {
      "display_name": "Python 3",
      "name": "python3"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/Manusha17/Hadoop-3-MapReduce/blob/master/Big_Data_CA2_PySpark_Aggregate_Functions.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "5fCEDCU_qrC0"
      },
      "source": [
        "\n",
        "<h1>Big Data CA2 - PySpark Aggregate Functions</h1>"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "fUhBhrGmyAvs"
      },
      "source": [
        "!pip install pyspark"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "vwlwUCb9cyE1"
      },
      "source": [
        "from pyspark.sql import functions as f\n",
        "from pyspark.sql import SparkSession\n",
        "import pandas as pd\n",
        "\n",
        "spark = SparkSession.builder.getOrCreate()\n",
        "\n",
        "df = pd.read_csv('https://query.data.world/s/ucswsevlpdlypgtml7z2gpy2pgnhgj')\n",
        "df.to_csv(\"sales_table.csv\")\n",
        "\n",
        "dataframe_sales = spark.read.csv('sales_table.csv',header=True,inferSchema=True)\n",
        "\n",
        "dataframe_sales.show(2)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "FPxBGIeYhP3j"
      },
      "source": [
        "**Min Function**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "hBQW4pZTVJsU",
        "outputId": "51305090-bf56-4e26-e0f6-17f55da60013"
      },
      "source": [
        "dataframe_sales.agg(f.min('bottle_qty')).show()\n",
        "dataframe_sales.groupby('county').min('bottle_qty').show(2)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+---------------+\n",
            "|min(bottle_qty)|\n",
            "+---------------+\n",
            "|              1|\n",
            "+---------------+\n",
            "\n",
            "+--------+---------------+\n",
            "|  county|min(bottle_qty)|\n",
            "+--------+---------------+\n",
            "|Harrison|              1|\n",
            "|   Scott|              1|\n",
            "+--------+---------------+\n",
            "only showing top 2 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "BOZMfvJ9hgId"
      },
      "source": [
        "**Max Function**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "4S5R5QKzboMf",
        "outputId": "a1c30b14-7602-462e-ec9c-a2c88f0b3ae4"
      },
      "source": [
        "dataframe_sales.agg(f.max('bottle_qty')).show()\n",
        "dataframe_sales.groupby('county').max('bottle_qty').show(2)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+---------------+\n",
            "|max(bottle_qty)|\n",
            "+---------------+\n",
            "|           3456|\n",
            "+---------------+\n",
            "\n",
            "+--------+---------------+\n",
            "|  county|max(bottle_qty)|\n",
            "+--------+---------------+\n",
            "|Harrison|            180|\n",
            "|   Scott|           1440|\n",
            "+--------+---------------+\n",
            "only showing top 2 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "v-B0aVjthozx"
      },
      "source": [
        "**Count Function**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Rt8ZPOg3UWUg",
        "outputId": "a0af34d6-6012-447c-c476-ccc853fe186c"
      },
      "source": [
        "dataframe_sales.agg(f.count('bottle_qty')).show()\n",
        "dataframe_sales.groupby('category_name').count().show(2)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+-----------------+\n",
            "|count(bottle_qty)|\n",
            "+-----------------+\n",
            "|          3049913|\n",
            "+-----------------+\n",
            "\n",
            "+-----------------+-----+\n",
            "|    category_name|count|\n",
            "+-----------------+-----+\n",
            "|IMPORTED SCHNAPPS|51859|\n",
            "|   PEACH BRANDIES| 3868|\n",
            "+-----------------+-----+\n",
            "only showing top 2 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "OyQXu0lNhtdY"
      },
      "source": [
        "**Sum Function**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "dYoFWedeVRV8",
        "outputId": "d97d3e85-f533-4c5f-8ce5-f294ce756d06"
      },
      "source": [
        "dataframe_sales.agg(f.sum('bottle_qty')).show()\n",
        "dataframe_sales.groupby('county').sum('bottle_qty').show(2)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+---------------+\n",
            "|sum(bottle_qty)|\n",
            "+---------------+\n",
            "|       30075263|\n",
            "+---------------+\n",
            "\n",
            "+--------+---------------+\n",
            "|  county|sum(bottle_qty)|\n",
            "+--------+---------------+\n",
            "|Harrison|          52909|\n",
            "|   Scott|        2298322|\n",
            "+--------+---------------+\n",
            "only showing top 2 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "p4AN--qihwNI"
      },
      "source": [
        "**Average Function**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "g2mtfSWBWt7K",
        "outputId": "42e3f8af-9e91-4568-978c-8ea0de1aedc0"
      },
      "source": [
        "dataframe_sales.agg(f.avg('bottle_qty')).show()\n",
        "dataframe_sales.groupby('county').avg('bottle_qty').show(2)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+-----------------+\n",
            "|  avg(bottle_qty)|\n",
            "+-----------------+\n",
            "|9.861023248859885|\n",
            "+-----------------+\n",
            "\n",
            "+--------+------------------+\n",
            "|  county|   avg(bottle_qty)|\n",
            "+--------+------------------+\n",
            "|Harrison| 4.679727578277021|\n",
            "|   Scott|12.204343670348344|\n",
            "+--------+------------------+\n",
            "only showing top 2 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Q95uMBaIh2I4"
      },
      "source": [
        "**Mean Function**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "cJ3v5fFqXmvC",
        "outputId": "be33178d-6c4c-4c54-d22a-4082848ae055"
      },
      "source": [
        "dataframe_sales.agg(f.mean('bottle_qty')).show()\n",
        "dataframe_sales.groupby('county').mean('bottle_qty').show(2)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+-----------------+\n",
            "|  avg(bottle_qty)|\n",
            "+-----------------+\n",
            "|9.861023248859885|\n",
            "+-----------------+\n",
            "\n",
            "+--------+------------------+\n",
            "|  county|   avg(bottle_qty)|\n",
            "+--------+------------------+\n",
            "|Harrison| 4.679727578277021|\n",
            "|   Scott|12.204343670348344|\n",
            "+--------+------------------+\n",
            "only showing top 2 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "YqetA92RokDq"
      },
      "source": [
        "**Standard deviation Function**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "DMP9YNn7kp8R",
        "outputId": "4673a71d-666f-4b67-c512-4683f85a1381"
      },
      "source": [
        "dataframe_sales.agg(f.stddev('bottle_qty')).show()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+-----------------------+\n",
            "|stddev_samp(bottle_qty)|\n",
            "+-----------------------+\n",
            "|      23.50626713268944|\n",
            "+-----------------------+\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Q-80wnzgh4UY"
      },
      "source": [
        "**Skewness Function**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "0Y2imLwZY2Ia",
        "outputId": "57df46d4-9dc7-4f80-e61f-a0e11eb0da43"
      },
      "source": [
        "dataframe_sales.agg(f.skewness('bottle_qty')).show()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+--------------------+\n",
            "|skewness(bottle_qty)|\n",
            "+--------------------+\n",
            "|  25.079418524487952|\n",
            "+--------------------+\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    }
  ]
}