challenge_10_python_pandas.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/abiflynn/python_pandas/blob/main/challenge_10_python_pandas.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "nKP0KGABRBNS"
      },
      "source": [
        "#Challenge 10: Python Pandas"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 1,
      "metadata": {
        "id": "MdfQCLdZRBNU"
      },
      "outputs": [],
      "source": [
        "\n",
        "# import libraries\n",
        "import pandas as pd\n",
        "import numpy as np\n",
        "\n",
        "# load data\n",
        "# This code is made to load our data stored on Google Drive\n",
        "def gd_path(file_id):\n",
        "    \"\"\"Generate a shareable link from Google Drive file id.\"\"\"\n",
        "    return f\"https://drive.google.com/uc?export=download&id={file_id}\"\n",
        "\n",
        "# Google Drive file ids\n",
        "files_id = {\n",
        "    \"titleauthor\": \"1F1JOiYXStWacOBca6coNVfyVtoST7ZgD\",\n",
        "    \"titles\": \"1PLdn50N9GRa53ZbuVWo0l47F_IXdvlEm\",\n",
        "    \"stores\": \"1f-GCgip7O93CpbAkYvOsc21eKnSOSHsQ\", \n",
        "    \"sales\": \"1fzFc9rwYmVIPaGOFmhLVxCi3kg19vNU2\", \n",
        "    \"roysched\": \"1zPRZPoFPEMKyrNR5VSENeYFHGCBZmxbs\", \n",
        "    \"publishers\": \"1s9E8_AVOziTrowb3wyh2jg3PV763VOyq\",\n",
        "    \"pub_info\": \"1OEgogcGKy--EpuVj0kqq7lyBZNGW6YSv\", \n",
        "    \"jobs\": \"1V1Za8hUdXD-vJOyRdX4aQV5wanIff2eM\", \n",
        "    \"employee\": \"1h9mUjsVqpP74b1w0x7KOw37n_n9Ulkt5\", \n",
        "    \"discounts\": \"111dvSxMcCsTgOuV1wDSKFJxO1Xcxd9VS\", \n",
        "    \"authors\": \"1fEF89Nhe61EebAljKlwFwfEuokK0o6aJ\"\n",
        "}\n",
        "\n",
        "# Read data from Google Drive\n",
        "sales = pd.read_csv(gd_path(files_id[\"sales\"]), sep=\";\")\n",
        "titles = pd.read_csv(gd_path(files_id[\"titles\"]), sep=\";\")\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "YUa4e1DTRBNV"
      },
      "source": [
        "## 1. Select everything from the sales table and create a new column called \"sales_category\" to categorise qty:\n",
        "   \n",
        "\t\tqty >= 50 high sales\n",
        "\t\t20 <= qty < 50 medium sales\n",
        "\t\tqty < 20 low sales"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 2,
      "metadata": {
        "id": "lkF8SMasRBNV",
        "outputId": "12f33ac0-3f3d-44e3-a986-7edad019cc9d",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 707
        }
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "    stor_id   ord_num             ord_date  qty    payterms title_id  \\\n",
              "0      6380      6871  1994-09-14 00:00:00    5      Net 60   BU1032   \n",
              "1      6380      722a  1994-09-13 00:00:00    3      Net 60   PS2091   \n",
              "2      7066     A2976  1993-05-24 00:00:00   50      Net 30   PC8888   \n",
              "3      7066  QA7442.3  1994-09-13 00:00:00   75  ON invoice   PS2091   \n",
              "4      7067     D4482  1994-09-14 00:00:00   10      Net 60   PS2091   \n",
              "5      7067     P2121  1992-06-15 00:00:00   40      Net 30   TC3218   \n",
              "6      7067     P2121  1992-06-15 00:00:00   20      Net 30   TC4203   \n",
              "7      7067     P2121  1992-06-15 00:00:00   20      Net 30   TC7777   \n",
              "8      7131   N914008  1994-09-14 00:00:00   20      Net 30   PS2091   \n",
              "9      7131   N914014  1994-09-14 00:00:00   25      Net 30   MC3021   \n",
              "10     7131    P3087a  1993-05-29 00:00:00   20      Net 60   PS1372   \n",
              "11     7131    P3087a  1993-05-29 00:00:00   25      Net 60   PS2106   \n",
              "12     7131    P3087a  1993-05-29 00:00:00   15      Net 60   PS3333   \n",
              "13     7131    P3087a  1993-05-29 00:00:00   25      Net 60   PS7777   \n",
              "14     7896    QQ2299  1993-10-28 00:00:00   15      Net 60   BU7832   \n",
              "15     7896     TQ456  1993-12-12 00:00:00   10      Net 60   MC2222   \n",
              "16     7896      X999  1993-02-21 00:00:00   35  ON invoice   BU2075   \n",
              "17     8042  423LL922  1994-09-14 00:00:00   15  ON invoice   MC3021   \n",
              "18     8042  423LL930  1994-09-14 00:00:00   10  ON invoice   BU1032   \n",
              "19     8042      P723  1993-03-11 00:00:00   25      Net 30   BU1111   \n",
              "20     8042   QA879.1  1993-05-22 00:00:00   30      Net 30   PC1035   \n",
              "\n",
              "   sales_category  \n",
              "0       low sales  \n",
              "1       low sales  \n",
              "2      high sales  \n",
              "3      high sales  \n",
              "4       low sales  \n",
              "5    medium sales  \n",
              "6    medium sales  \n",
              "7    medium sales  \n",
              "8    medium sales  \n",
              "9    medium sales  \n",
              "10   medium sales  \n",
              "11   medium sales  \n",
              "12      low sales  \n",
              "13   medium sales  \n",
              "14      low sales  \n",
              "15      low sales  \n",
              "16   medium sales  \n",
              "17      low sales  \n",
              "18      low sales  \n",
              "19   medium sales  \n",
              "20   medium sales  "
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-018950f8-c899-4ba3-af6c-b0f0b3ea470a\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>stor_id</th>\n",
              "      <th>ord_num</th>\n",
              "      <th>ord_date</th>\n",
              "      <th>qty</th>\n",
              "      <th>payterms</th>\n",
              "      <th>title_id</th>\n",
              "      <th>sales_category</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>6380</td>\n",
              "      <td>6871</td>\n",
              "      <td>1994-09-14 00:00:00</td>\n",
              "      <td>5</td>\n",
              "      <td>Net 60</td>\n",
              "      <td>BU1032</td>\n",
              "      <td>low sales</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>6380</td>\n",
              "      <td>722a</td>\n",
              "      <td>1994-09-13 00:00:00</td>\n",
              "      <td>3</td>\n",
              "      <td>Net 60</td>\n",
              "      <td>PS2091</td>\n",
              "      <td>low sales</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>7066</td>\n",
              "      <td>A2976</td>\n",
              "      <td>1993-05-24 00:00:00</td>\n",
              "      <td>50</td>\n",
              "      <td>Net 30</td>\n",
              "      <td>PC8888</td>\n",
              "      <td>high sales</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>7066</td>\n",
              "      <td>QA7442.3</td>\n",
              "      <td>1994-09-13 00:00:00</td>\n",
              "      <td>75</td>\n",
              "      <td>ON invoice</td>\n",
              "      <td>PS2091</td>\n",
              "      <td>high sales</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>7067</td>\n",
              "      <td>D4482</td>\n",
              "      <td>1994-09-14 00:00:00</td>\n",
              "      <td>10</td>\n",
              "      <td>Net 60</td>\n",
              "      <td>PS2091</td>\n",
              "      <td>low sales</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>7067</td>\n",
              "      <td>P2121</td>\n",
              "      <td>1992-06-15 00:00:00</td>\n",
              "      <td>40</td>\n",
              "      <td>Net 30</td>\n",
              "      <td>TC3218</td>\n",
              "      <td>medium sales</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>7067</td>\n",
              "      <td>P2121</td>\n",
              "      <td>1992-06-15 00:00:00</td>\n",
              "      <td>20</td>\n",
              "      <td>Net 30</td>\n",
              "      <td>TC4203</td>\n",
              "      <td>medium sales</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>7067</td>\n",
              "      <td>P2121</td>\n",
              "      <td>1992-06-15 00:00:00</td>\n",
              "      <td>20</td>\n",
              "      <td>Net 30</td>\n",
              "      <td>TC7777</td>\n",
              "      <td>medium sales</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>7131</td>\n",
              "      <td>N914008</td>\n",
              "      <td>1994-09-14 00:00:00</td>\n",
              "      <td>20</td>\n",
              "      <td>Net 30</td>\n",
              "      <td>PS2091</td>\n",
              "      <td>medium sales</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>7131</td>\n",
              "      <td>N914014</td>\n",
              "      <td>1994-09-14 00:00:00</td>\n",
              "      <td>25</td>\n",
              "      <td>Net 30</td>\n",
              "      <td>MC3021</td>\n",
              "      <td>medium sales</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>10</th>\n",
              "      <td>7131</td>\n",
              "      <td>P3087a</td>\n",
              "      <td>1993-05-29 00:00:00</td>\n",
              "      <td>20</td>\n",
              "      <td>Net 60</td>\n",
              "      <td>PS1372</td>\n",
              "      <td>medium sales</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>11</th>\n",
              "      <td>7131</td>\n",
              "      <td>P3087a</td>\n",
              "      <td>1993-05-29 00:00:00</td>\n",
              "      <td>25</td>\n",
              "      <td>Net 60</td>\n",
              "      <td>PS2106</td>\n",
              "      <td>medium sales</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>12</th>\n",
              "      <td>7131</td>\n",
              "      <td>P3087a</td>\n",
              "      <td>1993-05-29 00:00:00</td>\n",
              "      <td>15</td>\n",
              "      <td>Net 60</td>\n",
              "      <td>PS3333</td>\n",
              "      <td>low sales</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>13</th>\n",
              "      <td>7131</td>\n",
              "      <td>P3087a</td>\n",
              "      <td>1993-05-29 00:00:00</td>\n",
              "      <td>25</td>\n",
              "      <td>Net 60</td>\n",
              "      <td>PS7777</td>\n",
              "      <td>medium sales</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>14</th>\n",
              "      <td>7896</td>\n",
              "      <td>QQ2299</td>\n",
              "      <td>1993-10-28 00:00:00</td>\n",
              "      <td>15</td>\n",
              "      <td>Net 60</td>\n",
              "      <td>BU7832</td>\n",
              "      <td>low sales</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>15</th>\n",
              "      <td>7896</td>\n",
              "      <td>TQ456</td>\n",
              "      <td>1993-12-12 00:00:00</td>\n",
              "      <td>10</td>\n",
              "      <td>Net 60</td>\n",
              "      <td>MC2222</td>\n",
              "      <td>low sales</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>16</th>\n",
              "      <td>7896</td>\n",
              "      <td>X999</td>\n",
              "      <td>1993-02-21 00:00:00</td>\n",
              "      <td>35</td>\n",
              "      <td>ON invoice</td>\n",
              "      <td>BU2075</td>\n",
              "      <td>medium sales</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>17</th>\n",
              "      <td>8042</td>\n",
              "      <td>423LL922</td>\n",
              "      <td>1994-09-14 00:00:00</td>\n",
              "      <td>15</td>\n",
              "      <td>ON invoice</td>\n",
              "      <td>MC3021</td>\n",
              "      <td>low sales</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>18</th>\n",
              "      <td>8042</td>\n",
              "      <td>423LL930</td>\n",
              "      <td>1994-09-14 00:00:00</td>\n",
              "      <td>10</td>\n",
              "      <td>ON invoice</td>\n",
              "      <td>BU1032</td>\n",
              "      <td>low sales</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>19</th>\n",
              "      <td>8042</td>\n",
              "      <td>P723</td>\n",
              "      <td>1993-03-11 00:00:00</td>\n",
              "      <td>25</td>\n",
              "      <td>Net 30</td>\n",
              "      <td>BU1111</td>\n",
              "      <td>medium sales</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>20</th>\n",
              "      <td>8042</td>\n",
              "      <td>QA879.1</td>\n",
              "      <td>1993-05-22 00:00:00</td>\n",
              "      <td>30</td>\n",
              "      <td>Net 30</td>\n",
              "      <td>PC1035</td>\n",
              "      <td>medium sales</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-018950f8-c899-4ba3-af6c-b0f0b3ea470a')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-018950f8-c899-4ba3-af6c-b0f0b3ea470a button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-018950f8-c899-4ba3-af6c-b0f0b3ea470a');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 2
        }
      ],
      "source": [
        "sales.loc[sales[\"qty\"] >= 50, \"sales_category\"] = \"high sales\"\n",
        "sales.loc[(sales[\"qty\"] >= 20, \"sales_category\") and (sales[\"qty\"] < 50, \"sales_category\")] = \"medium sales\"\n",
        "sales.loc[sales[\"qty\"] < 20, \"sales_category\"] = \"low sales\"\n",
        "sales"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "XlkAS0hsRBNW"
      },
      "source": [
        "## 2. Adding to your answer from the previous question. Find out the total amount of books sold (qty) in each sales category \n",
        "    i.e. How many books had high sales, how many had medium sales, and how many had low sales"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 4,
      "metadata": {
        "id": "EJtCKDrHRBNW",
        "outputId": "bbe6b55b-f7d1-44c7-8572-5208747044b5",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 175
        }
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "                qty\n",
              "sales_category     \n",
              "high sales      125\n",
              "low sales        83\n",
              "medium sales    285"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-e81d1ae0-bac4-469f-9bbd-eed63f23c267\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>qty</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>sales_category</th>\n",
              "      <th></th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>high sales</th>\n",
              "      <td>125</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>low sales</th>\n",
              "      <td>83</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>medium sales</th>\n",
              "      <td>285</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-e81d1ae0-bac4-469f-9bbd-eed63f23c267')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-e81d1ae0-bac4-469f-9bbd-eed63f23c267 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-e81d1ae0-bac4-469f-9bbd-eed63f23c267');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 4
        }
      ],
      "source": [
        "(\n",
        "sales \n",
        "    .groupby(by = 'sales_category')\n",
        "    .aggregate({'qty': 'sum'})\n",
        ")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "20DRpsLlRBNX"
      },
      "source": [
        "## 3. Adding to your answer from the previous questions: output only those sales categories that have a SUM(qty) greater than 100, and order them in descending order"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 5,
      "metadata": {
        "id": "S6KeR3ETRBNX",
        "outputId": "3fddefc7-9b14-4651-94f4-ea69bb7b67a0",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 143
        }
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "                qty\n",
              "sales_category     \n",
              "medium sales    285\n",
              "high sales      125"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-8be6d81f-7a5a-4c23-8c25-563e6d34309f\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>qty</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>sales_category</th>\n",
              "      <th></th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>medium sales</th>\n",
              "      <td>285</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>high sales</th>\n",
              "      <td>125</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-8be6d81f-7a5a-4c23-8c25-563e6d34309f')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-8be6d81f-7a5a-4c23-8c25-563e6d34309f button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-8be6d81f-7a5a-4c23-8c25-563e6d34309f');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 5
        }
      ],
      "source": [
        "books_per_category = (\n",
        "sales \n",
        "    .groupby(by = 'sales_category')\n",
        "    .aggregate({'qty': 'sum'})\n",
        ")\n",
        "\n",
        "(\n",
        "books_per_category\n",
        "    .loc[books_per_category['qty']>100]\n",
        "    .sort_values(by = 'qty', ascending = False)\n",
        ")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "lw-waK-VRBNX"
      },
      "source": [
        "## 4. Find out the average book price, per publisher, for the following book types and price categories:\n",
        "\t\tbook types: business, traditional cook and psychology\n",
        "\t\tprice categories: <= 5 super low, <= 10 low, <= 15 medium, > 15 high\n",
        "        \n",
        "        - When displaying the average prices, use ROUND() to hide decimals."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 6,
      "metadata": {
        "id": "x-v6wPZcRBNX",
        "outputId": "0ac12e2d-076c-480a-c8fd-cc217ec86514",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "                                  price\n",
            "pub_id type       price_category       \n",
            "736    business   super low        2.99\n",
            "       psychology high            19.99\n",
            "                  low              7.50\n",
            "                  medium          10.95\n",
            "877    psychology high            21.59\n",
            "       trad_cook  high            20.95\n",
            "                  medium          13.47\n",
            "1389   business   high            19.99\n",
            "                  medium          11.95\n"
          ]
        }
      ],
      "source": [
        "\n",
        "selected_books = titles.loc[titles[\"type\"].isin(['business', 'trad_cook', 'psychology'])].copy()\n",
        "\n",
        "selected_books['price_category']= (\n",
        "    np.where(selected_books['price']<=5, 'super low',\n",
        "    np.where(selected_books['price'] <=10,'low',\n",
        "    np.where(selected_books['price'] <=15, 'medium',\n",
        "    'high')))\n",
        ")\n",
        "\n",
        "average_category_price = (\n",
        "selected_books\n",
        "    .groupby(by = ['pub_id', 'type', 'price_category'])\n",
        "    .aggregate({'price': 'mean'})\n",
        ")\n",
        "\n",
        "average_category_price['price'] = round(average_category_price['price'], 2)\n",
        "\n",
        "print(average_category_price)"
      ]
    }
  ],
  "metadata": {
    "kernelspec": {
      "display_name": "Pandas",
      "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.15"
    },
    "orig_nbformat": 4,
    "vscode": {
      "interpreter": {
        "hash": "d3320157bbf74c474a389ba3c249fd8129c2512710cab608d52eccd3d813ad2b"
      }
    },
    "colab": {
      "provenance": [],
      "include_colab_link": true
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}