EX_12/ex_12_pandas_groupby.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "pandas_groupby.ipynb",
      "provenance": []
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    }
  },
  "cells": [
    {
      "cell_type": "code",
      "metadata": {
        "id": "Z5oQsRnjU01-",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 34
        },
        "outputId": "1fb3143f-5120-469d-e75f-fe86a58f996b"
      },
      "source": [
        "print(\"Hello Pandas - Group By lesson!\")"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Hello Pandas - Group By lesson!\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "cy9LRaMFU99s"
      },
      "source": [
        "import numpy as np\n",
        "import pandas as pd"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Am80Xx56VICR"
      },
      "source": [
        "data = { 'Company':['GOOG', 'GOOG', 'MSFT', 'MSFT', 'FB', 'FB'],\n",
        "        'Person':['Sam', 'Charlie', 'Amy', 'Vanessa', 'Carl', 'Sarah'],\n",
        "        'Sales':[200,120, 340, 124, 243, 350]}"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "pXmHe4O0V4MQ"
      },
      "source": [
        "df = pd.DataFrame(data)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "eK0RPW7xV-Yt",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 235
        },
        "outputId": "30e13903-15c7-4d72-e2e1-e6c1d26d3ead"
      },
      "source": [
        "df"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>Company</th>\n",
              "      <th>Person</th>\n",
              "      <th>Sales</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>GOOG</td>\n",
              "      <td>Sam</td>\n",
              "      <td>200</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>GOOG</td>\n",
              "      <td>Charlie</td>\n",
              "      <td>120</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>MSFT</td>\n",
              "      <td>Amy</td>\n",
              "      <td>340</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>MSFT</td>\n",
              "      <td>Vanessa</td>\n",
              "      <td>124</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>FB</td>\n",
              "      <td>Carl</td>\n",
              "      <td>243</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>FB</td>\n",
              "      <td>Sarah</td>\n",
              "      <td>350</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "  Company   Person  Sales\n",
              "0    GOOG      Sam    200\n",
              "1    GOOG  Charlie    120\n",
              "2    MSFT      Amy    340\n",
              "3    MSFT  Vanessa    124\n",
              "4      FB     Carl    243\n",
              "5      FB    Sarah    350"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 5
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "DJ6R0q_RV_Ng",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 34
        },
        "outputId": "b5be3ab1-5302-4956-db94-42212f510ddb"
      },
      "source": [
        "df.groupby('Company')"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "<pandas.core.groupby.generic.DataFrameGroupBy object at 0x7f6fc0e5e320>"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 6
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "-gNEIXEgWuFh"
      },
      "source": [
        "byComp = df.groupby('Company')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "zoGJO4UrW5RL",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 173
        },
        "outputId": "6a150ae6-39f8-493e-8cb5-2e7287ce4050"
      },
      "source": [
        "byComp.mean()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>Sales</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Company</th>\n",
              "      <th></th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>FB</th>\n",
              "      <td>296.5</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>GOOG</th>\n",
              "      <td>160.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>MSFT</th>\n",
              "      <td>232.0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "         Sales\n",
              "Company       \n",
              "FB       296.5\n",
              "GOOG     160.0\n",
              "MSFT     232.0"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 8
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "GhmPftnPXDzm",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 173
        },
        "outputId": "db27286a-b729-4de8-bba5-8d19e50a6a54"
      },
      "source": [
        "byComp.std()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>Sales</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Company</th>\n",
              "      <th></th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>FB</th>\n",
              "      <td>75.660426</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>GOOG</th>\n",
              "      <td>56.568542</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>MSFT</th>\n",
              "      <td>152.735065</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "              Sales\n",
              "Company            \n",
              "FB        75.660426\n",
              "GOOG      56.568542\n",
              "MSFT     152.735065"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 9
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Gad7hWW9XO8H",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 173
        },
        "outputId": "c49a75da-5a7d-4536-9adf-33094eb3aa5e"
      },
      "source": [
        "byComp.sum()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>Sales</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Company</th>\n",
              "      <th></th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>FB</th>\n",
              "      <td>593</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>GOOG</th>\n",
              "      <td>320</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>MSFT</th>\n",
              "      <td>464</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "         Sales\n",
              "Company       \n",
              "FB         593\n",
              "GOOG       320\n",
              "MSFT       464"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 10
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "mGE-szb-XR5o",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 51
        },
        "outputId": "e8b86c99-d872-48ee-cb0e-4d93b535a1cc"
      },
      "source": [
        "byComp.sum().loc['FB']"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "Sales    593\n",
              "Name: FB, dtype: int64"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 11
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "dk1IKR_cXjJB",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 51
        },
        "outputId": "b8bfa8d8-9a64-46bb-d141-609c3046b4ad"
      },
      "source": [
        "# In one single line!\n",
        "df.groupby('Company').sum().loc['FB']"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "Sales    593\n",
              "Name: FB, dtype: int64"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 12
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "KBC7cPg1X63l",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 173
        },
        "outputId": "cf2b0f73-eafc-4712-babb-be0b7e4a63f0"
      },
      "source": [
        "df.groupby('Company').count()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>Person</th>\n",
              "      <th>Sales</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Company</th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>FB</th>\n",
              "      <td>2</td>\n",
              "      <td>2</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>GOOG</th>\n",
              "      <td>2</td>\n",
              "      <td>2</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>MSFT</th>\n",
              "      <td>2</td>\n",
              "      <td>2</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "         Person  Sales\n",
              "Company               \n",
              "FB            2      2\n",
              "GOOG          2      2\n",
              "MSFT          2      2"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 13
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "MVFf0f5MYSHq",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 173
        },
        "outputId": "d03a7c0e-07e8-450c-bcd3-8d9287bea55a"
      },
      "source": [
        "df.groupby('Company').max()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>Person</th>\n",
              "      <th>Sales</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Company</th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>FB</th>\n",
              "      <td>Sarah</td>\n",
              "      <td>350</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>GOOG</th>\n",
              "      <td>Sam</td>\n",
              "      <td>200</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>MSFT</th>\n",
              "      <td>Vanessa</td>\n",
              "      <td>340</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "          Person  Sales\n",
              "Company                \n",
              "FB         Sarah    350\n",
              "GOOG         Sam    200\n",
              "MSFT     Vanessa    340"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 14
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "x3kEfQ7rYdHW",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 173
        },
        "outputId": "6c95bc7d-5031-4a78-a2cb-0883614bb945"
      },
      "source": [
        "# We probably use these aggregate functions with the numeric number (here is inverse of alphabet order:)\n",
        "df.groupby('Company').min()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>Person</th>\n",
              "      <th>Sales</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Company</th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>FB</th>\n",
              "      <td>Carl</td>\n",
              "      <td>243</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>GOOG</th>\n",
              "      <td>Charlie</td>\n",
              "      <td>120</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>MSFT</th>\n",
              "      <td>Amy</td>\n",
              "      <td>124</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "          Person  Sales\n",
              "Company                \n",
              "FB          Carl    243\n",
              "GOOG     Charlie    120\n",
              "MSFT         Amy    124"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 15
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "UQUanI_oYfQw",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 204
        },
        "outputId": "6b92baaa-72a0-4060-f02a-c83cb0faef01"
      },
      "source": [
        "# That will give you a bunch of information!\n",
        "df.groupby('Company').describe()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead tr th {\n",
              "        text-align: left;\n",
              "    }\n",
              "\n",
              "    .dataframe thead tr:last-of-type th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr>\n",
              "      <th></th>\n",
              "      <th colspan=\"8\" halign=\"left\">Sales</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th></th>\n",
              "      <th>count</th>\n",
              "      <th>mean</th>\n",
              "      <th>std</th>\n",
              "      <th>min</th>\n",
              "      <th>25%</th>\n",
              "      <th>50%</th>\n",
              "      <th>75%</th>\n",
              "      <th>max</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Company</th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>FB</th>\n",
              "      <td>2.0</td>\n",
              "      <td>296.5</td>\n",
              "      <td>75.660426</td>\n",
              "      <td>243.0</td>\n",
              "      <td>269.75</td>\n",
              "      <td>296.5</td>\n",
              "      <td>323.25</td>\n",
              "      <td>350.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>GOOG</th>\n",
              "      <td>2.0</td>\n",
              "      <td>160.0</td>\n",
              "      <td>56.568542</td>\n",
              "      <td>120.0</td>\n",
              "      <td>140.00</td>\n",
              "      <td>160.0</td>\n",
              "      <td>180.00</td>\n",
              "      <td>200.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>MSFT</th>\n",
              "      <td>2.0</td>\n",
              "      <td>232.0</td>\n",
              "      <td>152.735065</td>\n",
              "      <td>124.0</td>\n",
              "      <td>178.00</td>\n",
              "      <td>232.0</td>\n",
              "      <td>286.00</td>\n",
              "      <td>340.0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "        Sales                                                        \n",
              "        count   mean         std    min     25%    50%     75%    max\n",
              "Company                                                              \n",
              "FB        2.0  296.5   75.660426  243.0  269.75  296.5  323.25  350.0\n",
              "GOOG      2.0  160.0   56.568542  120.0  140.00  160.0  180.00  200.0\n",
              "MSFT      2.0  232.0  152.735065  124.0  178.00  232.0  286.00  340.0"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 16
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "z_x-u2VOZGFK",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 170
        },
        "outputId": "085bb96c-9e14-47ad-f240-48a3090b906d"
      },
      "source": [
        "df.groupby('Company').describe().transpose()['FB']"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "Sales  count      2.000000\n",
              "       mean     296.500000\n",
              "       std       75.660426\n",
              "       min      243.000000\n",
              "       25%      269.750000\n",
              "       50%      296.500000\n",
              "       75%      323.250000\n",
              "       max      350.000000\n",
              "Name: FB, dtype: float64"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 17
        }
      ]
    }
  ]
}