challenge_3_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_3_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": "SsX7np6dIc8f"
      },
      "source": [
        "#Challenge 3: Python Pandas"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 2,
      "metadata": {
        "id": "2P1jD0KFIc8h"
      },
      "outputs": [],
      "source": [
        "# import libraries\n",
        "import pandas as pd\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",
        "authors = pd.read_csv(gd_path(files_id[\"authors\"]), sep=\";\")\n",
        "titles = pd.read_csv(gd_path(files_id[\"titles\"]), sep=\";\")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "rKUaF7FRIc8h"
      },
      "source": [
        "## 1. Select the title and ytd_sales from the table titles. Order them by the year to date sales."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 3,
      "metadata": {
        "id": "IqRNwgQsIc8h",
        "outputId": "894da586-3253-4687-f674-27ba2a1f93bb",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 613
        }
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "                                                title  ytd_sales\n",
              "6                  The Psychology of Computer Cooking          0\n",
              "9                                       Net Etiquette          0\n",
              "12                                  Life Without Fear        111\n",
              "15  Onions, Leeks, and Garlic: Cooking Secrets of ...        375\n",
              "10  Computer Phobic AND Non-Phobic Individuals: Be...        375\n",
              "4                   Silicon Valley Gastronomic Treats       2032\n",
              "11                                Is Anger the Enemy?       2045\n",
              "14                Emotional Security: A New Algorithm       3336\n",
              "1   Cooking with Computers: Surreptitious Balance ...       3876\n",
              "13      Prolonged Data Deprivation: Four Case Studies       4072\n",
              "0                 The Busy Executive's Database Guide       4095\n",
              "8                           Secrets of Silicon Valley       4095\n",
              "3                       Straight Talk About Computers       4095\n",
              "17                                     Sushi, Anyone?       4095\n",
              "7                            But Is It User Friendly?       8780\n",
              "16          Fifty Years in Buckingham Palace Kitchens      15096\n",
              "2                     You Can Combat Computer Stress!      18722\n",
              "5                               The Gourmet Microwave      22246"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-4dd2ff03-89c4-4b41-b323-e2a1b887b5be\">\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>title</th>\n",
              "      <th>ytd_sales</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>The Psychology of Computer Cooking</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>Net Etiquette</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>12</th>\n",
              "      <td>Life Without Fear</td>\n",
              "      <td>111</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>15</th>\n",
              "      <td>Onions, Leeks, and Garlic: Cooking Secrets of ...</td>\n",
              "      <td>375</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>10</th>\n",
              "      <td>Computer Phobic AND Non-Phobic Individuals: Be...</td>\n",
              "      <td>375</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>Silicon Valley Gastronomic Treats</td>\n",
              "      <td>2032</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>11</th>\n",
              "      <td>Is Anger the Enemy?</td>\n",
              "      <td>2045</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>14</th>\n",
              "      <td>Emotional Security: A New Algorithm</td>\n",
              "      <td>3336</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>Cooking with Computers: Surreptitious Balance ...</td>\n",
              "      <td>3876</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>13</th>\n",
              "      <td>Prolonged Data Deprivation: Four Case Studies</td>\n",
              "      <td>4072</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>The Busy Executive's Database Guide</td>\n",
              "      <td>4095</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>Secrets of Silicon Valley</td>\n",
              "      <td>4095</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>Straight Talk About Computers</td>\n",
              "      <td>4095</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>17</th>\n",
              "      <td>Sushi, Anyone?</td>\n",
              "      <td>4095</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>But Is It User Friendly?</td>\n",
              "      <td>8780</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>16</th>\n",
              "      <td>Fifty Years in Buckingham Palace Kitchens</td>\n",
              "      <td>15096</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>You Can Combat Computer Stress!</td>\n",
              "      <td>18722</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>The Gourmet Microwave</td>\n",
              "      <td>22246</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-4dd2ff03-89c4-4b41-b323-e2a1b887b5be')\"\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-4dd2ff03-89c4-4b41-b323-e2a1b887b5be 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-4dd2ff03-89c4-4b41-b323-e2a1b887b5be');\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": 3
        }
      ],
      "source": [
        "(\n",
        "titles\n",
        "    [['title', 'ytd_sales']]\n",
        "    .sort_values(by='ytd_sales')\n",
        ")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "yJdsTmm4Ic8i"
      },
      "source": [
        "## 2. Repeat the same query, but this time sort the titles in descending order"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 4,
      "metadata": {
        "id": "C7z0s6A9Ic8i",
        "outputId": "846ece19-7293-46eb-dae1-e4dd5af850a0",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 613
        }
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "                                                title  ytd_sales\n",
              "5                               The Gourmet Microwave      22246\n",
              "2                     You Can Combat Computer Stress!      18722\n",
              "16          Fifty Years in Buckingham Palace Kitchens      15096\n",
              "7                            But Is It User Friendly?       8780\n",
              "0                 The Busy Executive's Database Guide       4095\n",
              "8                           Secrets of Silicon Valley       4095\n",
              "17                                     Sushi, Anyone?       4095\n",
              "3                       Straight Talk About Computers       4095\n",
              "13      Prolonged Data Deprivation: Four Case Studies       4072\n",
              "1   Cooking with Computers: Surreptitious Balance ...       3876\n",
              "14                Emotional Security: A New Algorithm       3336\n",
              "11                                Is Anger the Enemy?       2045\n",
              "4                   Silicon Valley Gastronomic Treats       2032\n",
              "10  Computer Phobic AND Non-Phobic Individuals: Be...        375\n",
              "15  Onions, Leeks, and Garlic: Cooking Secrets of ...        375\n",
              "12                                  Life Without Fear        111\n",
              "6                  The Psychology of Computer Cooking          0\n",
              "9                                       Net Etiquette          0"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-d0a9f986-001b-4c25-8a4b-f298a0ae1622\">\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>title</th>\n",
              "      <th>ytd_sales</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>The Gourmet Microwave</td>\n",
              "      <td>22246</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>You Can Combat Computer Stress!</td>\n",
              "      <td>18722</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>16</th>\n",
              "      <td>Fifty Years in Buckingham Palace Kitchens</td>\n",
              "      <td>15096</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>But Is It User Friendly?</td>\n",
              "      <td>8780</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>The Busy Executive's Database Guide</td>\n",
              "      <td>4095</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>Secrets of Silicon Valley</td>\n",
              "      <td>4095</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>17</th>\n",
              "      <td>Sushi, Anyone?</td>\n",
              "      <td>4095</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>Straight Talk About Computers</td>\n",
              "      <td>4095</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>13</th>\n",
              "      <td>Prolonged Data Deprivation: Four Case Studies</td>\n",
              "      <td>4072</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>Cooking with Computers: Surreptitious Balance ...</td>\n",
              "      <td>3876</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>14</th>\n",
              "      <td>Emotional Security: A New Algorithm</td>\n",
              "      <td>3336</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>11</th>\n",
              "      <td>Is Anger the Enemy?</td>\n",
              "      <td>2045</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>Silicon Valley Gastronomic Treats</td>\n",
              "      <td>2032</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>10</th>\n",
              "      <td>Computer Phobic AND Non-Phobic Individuals: Be...</td>\n",
              "      <td>375</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>15</th>\n",
              "      <td>Onions, Leeks, and Garlic: Cooking Secrets of ...</td>\n",
              "      <td>375</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>12</th>\n",
              "      <td>Life Without Fear</td>\n",
              "      <td>111</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>The Psychology of Computer Cooking</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>Net Etiquette</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-d0a9f986-001b-4c25-8a4b-f298a0ae1622')\"\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-d0a9f986-001b-4c25-8a4b-f298a0ae1622 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-d0a9f986-001b-4c25-8a4b-f298a0ae1622');\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",
        "titles\n",
        "    [['title', 'ytd_sales']]\n",
        "    .sort_values(by = 'ytd_sales', ascending = False)\n",
        ")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "YZ-XiPfEIc8j"
      },
      "source": [
        "## 3. Select the top 5 titles with the most ytd_sales from the table titles"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 12,
      "metadata": {
        "id": "is6CNpw9Ic8j",
        "outputId": "91c34e19-d947-4a28-96ca-c722b7b80b7c",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 206
        }
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "   title_id                                      title          type  pub_id  \\\n",
              "5    MC3021                      The Gourmet Microwave      mod_cook     877   \n",
              "2    BU2075            You Can Combat Computer Stress!      business     736   \n",
              "16   TC4203  Fifty Years in Buckingham Palace Kitchens     trad_cook     877   \n",
              "7    PC1035                   But Is It User Friendly?  popular_comp    1389   \n",
              "0    BU1032        The Busy Executive's Database Guide      business    1389   \n",
              "\n",
              "    price  advance  royalty  ytd_sales  \\\n",
              "5    2.99  15000.0       24      22246   \n",
              "2    2.99  10125.0       24      18722   \n",
              "16  11.95   4000.0       14      15096   \n",
              "7   22.95   7000.0       16       8780   \n",
              "0   19.99   5000.0       10       4095   \n",
              "\n",
              "                                                notes              pubdate  \n",
              "5   Traditional French gourmet recipes adapted for...  1991-06-18 00:00:00  \n",
              "2   The latest medical and psychological technique...  1991-06-30 00:00:00  \n",
              "16  More anecdotes from the Queen's favorite cook ...  1991-06-12 00:00:00  \n",
              "7   A survey of software for the naive user, focus...  1991-06-30 00:00:00  \n",
              "0   An overview of available database systems with...  1991-06-12 00:00:00  "
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-c86c1db2-48e7-49f9-b0b9-f9ffc6ab2106\">\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>title_id</th>\n",
              "      <th>title</th>\n",
              "      <th>type</th>\n",
              "      <th>pub_id</th>\n",
              "      <th>price</th>\n",
              "      <th>advance</th>\n",
              "      <th>royalty</th>\n",
              "      <th>ytd_sales</th>\n",
              "      <th>notes</th>\n",
              "      <th>pubdate</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>MC3021</td>\n",
              "      <td>The Gourmet Microwave</td>\n",
              "      <td>mod_cook</td>\n",
              "      <td>877</td>\n",
              "      <td>2.99</td>\n",
              "      <td>15000.0</td>\n",
              "      <td>24</td>\n",
              "      <td>22246</td>\n",
              "      <td>Traditional French gourmet recipes adapted for...</td>\n",
              "      <td>1991-06-18 00:00:00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>BU2075</td>\n",
              "      <td>You Can Combat Computer Stress!</td>\n",
              "      <td>business</td>\n",
              "      <td>736</td>\n",
              "      <td>2.99</td>\n",
              "      <td>10125.0</td>\n",
              "      <td>24</td>\n",
              "      <td>18722</td>\n",
              "      <td>The latest medical and psychological technique...</td>\n",
              "      <td>1991-06-30 00:00:00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>16</th>\n",
              "      <td>TC4203</td>\n",
              "      <td>Fifty Years in Buckingham Palace Kitchens</td>\n",
              "      <td>trad_cook</td>\n",
              "      <td>877</td>\n",
              "      <td>11.95</td>\n",
              "      <td>4000.0</td>\n",
              "      <td>14</td>\n",
              "      <td>15096</td>\n",
              "      <td>More anecdotes from the Queen's favorite cook ...</td>\n",
              "      <td>1991-06-12 00:00:00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>PC1035</td>\n",
              "      <td>But Is It User Friendly?</td>\n",
              "      <td>popular_comp</td>\n",
              "      <td>1389</td>\n",
              "      <td>22.95</td>\n",
              "      <td>7000.0</td>\n",
              "      <td>16</td>\n",
              "      <td>8780</td>\n",
              "      <td>A survey of software for the naive user, focus...</td>\n",
              "      <td>1991-06-30 00:00:00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>BU1032</td>\n",
              "      <td>The Busy Executive's Database Guide</td>\n",
              "      <td>business</td>\n",
              "      <td>1389</td>\n",
              "      <td>19.99</td>\n",
              "      <td>5000.0</td>\n",
              "      <td>10</td>\n",
              "      <td>4095</td>\n",
              "      <td>An overview of available database systems with...</td>\n",
              "      <td>1991-06-12 00:00:00</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-c86c1db2-48e7-49f9-b0b9-f9ffc6ab2106')\"\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-c86c1db2-48e7-49f9-b0b9-f9ffc6ab2106 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-c86c1db2-48e7-49f9-b0b9-f9ffc6ab2106');\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": 12
        }
      ],
      "source": [
        "(\n",
        "titles\n",
        "    .sort_values(by = 'ytd_sales', ascending = False)\n",
        "    .head(5)\n",
        ")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "cX2q6AeBIc8j"
      },
      "source": [
        "## 4. What's the maximum amount of books ever sold in a single sale?"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 6,
      "metadata": {
        "id": "6QenwjFxIc8j",
        "outputId": "e4c24504-648b-4700-e7ad-f5d87318fd0a",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "75"
            ]
          },
          "metadata": {},
          "execution_count": 6
        }
      ],
      "source": [
        "sales['qty'].max()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "llp929zdIc8j"
      },
      "source": [
        "## 5. What's the price of the cheapest book?"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 7,
      "metadata": {
        "id": "wYIWDpofIc8j",
        "outputId": "194ee7be-21f2-4ba5-9b33-8fc967127409",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "0.0"
            ]
          },
          "metadata": {},
          "execution_count": 7
        }
      ],
      "source": [
        "titles['price'].min()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "qnoHBP0IIc8k"
      },
      "source": [
        "## 6. How many rows are there in the table authors?"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 8,
      "metadata": {
        "id": "lZtLqKF5Ic8k",
        "outputId": "92bf1c35-2da5-4c0d-bc61-94bd25f5caa6",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "23"
            ]
          },
          "metadata": {},
          "execution_count": 8
        }
      ],
      "source": [
        "authors['au_id'].count()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "3DWFv3bZIc8k"
      },
      "source": [
        "## 7. What's the total amount of year-to-date sales?"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 9,
      "metadata": {
        "id": "S7qkpk5IIc8k",
        "outputId": "10f83f50-638c-47ae-f305-8c23bd5861bc",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "97446"
            ]
          },
          "metadata": {},
          "execution_count": 9
        }
      ],
      "source": [
        "(\n",
        "titles\n",
        "    ['ytd_sales']\n",
        "    .sum()\n",
        ")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "pzJf0KBZIc8k"
      },
      "source": [
        "## 8. What's the average price of books?"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 10,
      "metadata": {
        "id": "lZmGxWwuIc8k",
        "outputId": "031b389a-a449-4d30-a3c5-e2f1d0a75e2e",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "13.125555555555556"
            ]
          },
          "metadata": {},
          "execution_count": 10
        }
      ],
      "source": [
        "(\n",
        "titles\n",
        "    ['price']\n",
        "    .mean()\n",
        ")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "QZKNdSCrIc8k"
      },
      "source": [
        "## 9. In a single query, select the count, average and sum of quantity in the table sales"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 11,
      "metadata": {
        "id": "bdoEXdgUIc8k",
        "outputId": "e7761327-b551-4afe-acfb-06d65195dc02",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 143
        }
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "             qty\n",
              "count   21.00000\n",
              "mean    23.47619\n",
              "sum    493.00000"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-616a9b6c-5bb3-4030-a664-0ffde4abd63c\">\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",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>count</th>\n",
              "      <td>21.00000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>mean</th>\n",
              "      <td>23.47619</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>sum</th>\n",
              "      <td>493.00000</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-616a9b6c-5bb3-4030-a664-0ffde4abd63c')\"\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-616a9b6c-5bb3-4030-a664-0ffde4abd63c 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-616a9b6c-5bb3-4030-a664-0ffde4abd63c');\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": 11
        }
      ],
      "source": [
        "(\n",
        "sales\n",
        "    .aggregate({'qty': ['count', 'mean', 'sum']})\n",
        ")"
      ]
    }
  ],
  "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
}