challenge_11_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_11_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": "GJvYIRpgLwCh"
      },
      "source": [
        "#Challenge 11: Python Pandas"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 1,
      "metadata": {
        "id": "St6aySthLwCl"
      },
      "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",
        "titles = pd.read_csv(gd_path(files_id[\"titles\"]), sep=\";\")\n",
        "publishers = pd.read_csv(gd_path(files_id[\"publishers\"]), sep=\";\")\n",
        "employee = pd.read_csv(gd_path(files_id[\"employee\"]), sep=\";\")\n",
        "authors = pd.read_csv(gd_path(files_id[\"authors\"]), sep=\";\")\n",
        "titleauthor = pd.read_csv(gd_path(files_id[\"titleauthor\"]), sep=\";\")\n",
        "roysched = pd.read_csv(gd_path(files_id[\"roysched\"]), sep=\";\")\n",
        "stores = pd.read_csv(gd_path(files_id[\"stores\"]), sep=\";\")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "AOJbkq5_LwCm"
      },
      "source": [
        "## 1. Using LEFT JOIN: in which cities has \"Is Anger the Enemy?\" been sold?"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 2,
      "metadata": {
        "id": "0aV1Asj1LwCn",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 81
        },
        "outputId": "47b1fff1-fd0b-4301-9387-79d1c1f83c38"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "                 title    city\n",
              "1  Is Anger the Enemy?  Boston"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-1bf341ee-ed79-429f-b91b-29e45905b149\">\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>city</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>Is Anger the Enemy?</td>\n",
              "      <td>Boston</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-1bf341ee-ed79-429f-b91b-29e45905b149')\"\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-1bf341ee-ed79-429f-b91b-29e45905b149 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-1bf341ee-ed79-429f-b91b-29e45905b149');\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": [
        "titles_pub =(pd.merge(publishers, titles, on=\"pub_id\", how=\"left\"))\n",
        "titles_pub[[\"title\", \"city\"]][(titles_pub[\"title\"] == \"Is Anger the Enemy?\")]"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "YjrPDFbuLwCn"
      },
      "source": [
        "## 2. Select all the book titles that have a link to the employee Howard Snyder "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 3,
      "metadata": {
        "id": "AtTew9ItLwCo",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 206
        },
        "outputId": "80ce7933-d4b5-4375-f19f-ef1715f9fe43"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "                                             title   fname   lname\n",
              "21                 You Can Combat Computer Stress!  Howard  Snyder\n",
              "111                            Is Anger the Enemy?  Howard  Snyder\n",
              "121                              Life Without Fear  Howard  Snyder\n",
              "131  Prolonged Data Deprivation: Four Case Studies  Howard  Snyder\n",
              "141            Emotional Security: A New Algorithm  Howard  Snyder"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-0189e07f-2b20-4d6b-a9d5-565b476eee4d\">\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>fname</th>\n",
              "      <th>lname</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>21</th>\n",
              "      <td>You Can Combat Computer Stress!</td>\n",
              "      <td>Howard</td>\n",
              "      <td>Snyder</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>111</th>\n",
              "      <td>Is Anger the Enemy?</td>\n",
              "      <td>Howard</td>\n",
              "      <td>Snyder</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>121</th>\n",
              "      <td>Life Without Fear</td>\n",
              "      <td>Howard</td>\n",
              "      <td>Snyder</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>131</th>\n",
              "      <td>Prolonged Data Deprivation: Four Case Studies</td>\n",
              "      <td>Howard</td>\n",
              "      <td>Snyder</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>141</th>\n",
              "      <td>Emotional Security: A New Algorithm</td>\n",
              "      <td>Howard</td>\n",
              "      <td>Snyder</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-0189e07f-2b20-4d6b-a9d5-565b476eee4d')\"\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-0189e07f-2b20-4d6b-a9d5-565b476eee4d 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-0189e07f-2b20-4d6b-a9d5-565b476eee4d');\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": [
        "titles_employee =(pd.merge(titles, employee, on=\"pub_id\", how=\"left\"))\n",
        "titles_employee[[\"title\", \"fname\", \"lname\"]][(titles_employee[\"fname\"] == \"Howard\") & (titles_employee[\"lname\"] == \"Snyder\")]"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "c000jQaiLwCp"
      },
      "source": [
        "## 3. Using the `merge` of your choice: Select the book title with highest number of sales (qty)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 4,
      "metadata": {
        "id": "RRCblu3fLwCp",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 112
        },
        "outputId": "70633c96-5210-4359-8e63-94f5236661a3"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "                       qty\n",
              "title                     \n",
              "Is Anger the Enemy?  108.0"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-8dbb610b-2d03-4361-9818-2741336788d2\">\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>title</th>\n",
              "      <th></th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>Is Anger the Enemy?</th>\n",
              "      <td>108.0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-8dbb610b-2d03-4361-9818-2741336788d2')\"\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-8dbb610b-2d03-4361-9818-2741336788d2 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-8dbb610b-2d03-4361-9818-2741336788d2');\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": [
        "titles_sales =(pd.merge(titles, sales, on=\"title_id\", how=\"left\"))\n",
        "titles_sales\n",
        "(titles_sales.groupby(\"title\")\n",
        "             .agg({\"qty\":\"sum\"})\n",
        "             .sort_values(\"qty\", ascending=False)\n",
        "             .head(1)\n",
        ")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "X3lgEbQMLwCp"
      },
      "source": [
        "# 4. Select all book titles and the full name of their author(s).\n",
        "      \n",
        "      - If a book has multiple authors, all authors must be displayed (in \n",
        "      multiple rows).\n",
        "      \n",
        "      - Books with no authors and authors with no books should not be displayed."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 5,
      "metadata": {
        "id": "cuC6hilALwCp",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 833
        },
        "outputId": "edff1921-07eb-4706-ab08-ec15428ecd6c"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "    au_fname        au_lname  \\\n",
              "0   Marjorie           Green   \n",
              "1   Marjorie           Green   \n",
              "2    Abraham          Bennet   \n",
              "3    Michael         O'Leary   \n",
              "4    Michael         O'Leary   \n",
              "5    Stearns      MacFeather   \n",
              "6    Stearns      MacFeather   \n",
              "7       Dean        Straight   \n",
              "8      Innes    del Castillo   \n",
              "9     Michel        DeFrance   \n",
              "10      Anne          Ringer   \n",
              "11      Anne          Ringer   \n",
              "12    Cheryl          Carson   \n",
              "13       Ann            Dull   \n",
              "14    Sheryl          Hunter   \n",
              "15  Charlene        Locksley   \n",
              "16  Charlene        Locksley   \n",
              "17     Livia          Karsen   \n",
              "18    Albert          Ringer   \n",
              "19    Albert          Ringer   \n",
              "20   Johnson           White   \n",
              "21    Sylvia        Panteley   \n",
              "22  Reginald  Blotchet-Halls   \n",
              "23      Burt      Gringlesby   \n",
              "24     Akiko        Yokomoto   \n",
              "\n",
              "                                                title  \n",
              "0                 The Busy Executive's Database Guide  \n",
              "1                     You Can Combat Computer Stress!  \n",
              "2                 The Busy Executive's Database Guide  \n",
              "3   Cooking with Computers: Surreptitious Balance ...  \n",
              "4                                      Sushi, Anyone?  \n",
              "5   Cooking with Computers: Surreptitious Balance ...  \n",
              "6   Computer Phobic AND Non-Phobic Individuals: Be...  \n",
              "7                       Straight Talk About Computers  \n",
              "8                   Silicon Valley Gastronomic Treats  \n",
              "9                               The Gourmet Microwave  \n",
              "10                              The Gourmet Microwave  \n",
              "11                                Is Anger the Enemy?  \n",
              "12                           But Is It User Friendly?  \n",
              "13                          Secrets of Silicon Valley  \n",
              "14                          Secrets of Silicon Valley  \n",
              "15                                      Net Etiquette  \n",
              "16                Emotional Security: A New Algorithm  \n",
              "17  Computer Phobic AND Non-Phobic Individuals: Be...  \n",
              "18                                Is Anger the Enemy?  \n",
              "19                                  Life Without Fear  \n",
              "20      Prolonged Data Deprivation: Four Case Studies  \n",
              "21  Onions, Leeks, and Garlic: Cooking Secrets of ...  \n",
              "22          Fifty Years in Buckingham Palace Kitchens  \n",
              "23                                     Sushi, Anyone?  \n",
              "24                                     Sushi, Anyone?  "
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-00225c0a-6eaf-426a-be80-a42218b31e88\">\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>au_fname</th>\n",
              "      <th>au_lname</th>\n",
              "      <th>title</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Marjorie</td>\n",
              "      <td>Green</td>\n",
              "      <td>The Busy Executive's Database Guide</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>Marjorie</td>\n",
              "      <td>Green</td>\n",
              "      <td>You Can Combat Computer Stress!</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>Abraham</td>\n",
              "      <td>Bennet</td>\n",
              "      <td>The Busy Executive's Database Guide</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>Michael</td>\n",
              "      <td>O'Leary</td>\n",
              "      <td>Cooking with Computers: Surreptitious Balance ...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>Michael</td>\n",
              "      <td>O'Leary</td>\n",
              "      <td>Sushi, Anyone?</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>Stearns</td>\n",
              "      <td>MacFeather</td>\n",
              "      <td>Cooking with Computers: Surreptitious Balance ...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>Stearns</td>\n",
              "      <td>MacFeather</td>\n",
              "      <td>Computer Phobic AND Non-Phobic Individuals: Be...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>Dean</td>\n",
              "      <td>Straight</td>\n",
              "      <td>Straight Talk About Computers</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>Innes</td>\n",
              "      <td>del Castillo</td>\n",
              "      <td>Silicon Valley Gastronomic Treats</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>Michel</td>\n",
              "      <td>DeFrance</td>\n",
              "      <td>The Gourmet Microwave</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>10</th>\n",
              "      <td>Anne</td>\n",
              "      <td>Ringer</td>\n",
              "      <td>The Gourmet Microwave</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>11</th>\n",
              "      <td>Anne</td>\n",
              "      <td>Ringer</td>\n",
              "      <td>Is Anger the Enemy?</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>12</th>\n",
              "      <td>Cheryl</td>\n",
              "      <td>Carson</td>\n",
              "      <td>But Is It User Friendly?</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>13</th>\n",
              "      <td>Ann</td>\n",
              "      <td>Dull</td>\n",
              "      <td>Secrets of Silicon Valley</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>14</th>\n",
              "      <td>Sheryl</td>\n",
              "      <td>Hunter</td>\n",
              "      <td>Secrets of Silicon Valley</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>15</th>\n",
              "      <td>Charlene</td>\n",
              "      <td>Locksley</td>\n",
              "      <td>Net Etiquette</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>16</th>\n",
              "      <td>Charlene</td>\n",
              "      <td>Locksley</td>\n",
              "      <td>Emotional Security: A New Algorithm</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>17</th>\n",
              "      <td>Livia</td>\n",
              "      <td>Karsen</td>\n",
              "      <td>Computer Phobic AND Non-Phobic Individuals: Be...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>18</th>\n",
              "      <td>Albert</td>\n",
              "      <td>Ringer</td>\n",
              "      <td>Is Anger the Enemy?</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>19</th>\n",
              "      <td>Albert</td>\n",
              "      <td>Ringer</td>\n",
              "      <td>Life Without Fear</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>20</th>\n",
              "      <td>Johnson</td>\n",
              "      <td>White</td>\n",
              "      <td>Prolonged Data Deprivation: Four Case Studies</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>21</th>\n",
              "      <td>Sylvia</td>\n",
              "      <td>Panteley</td>\n",
              "      <td>Onions, Leeks, and Garlic: Cooking Secrets of ...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>22</th>\n",
              "      <td>Reginald</td>\n",
              "      <td>Blotchet-Halls</td>\n",
              "      <td>Fifty Years in Buckingham Palace Kitchens</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>23</th>\n",
              "      <td>Burt</td>\n",
              "      <td>Gringlesby</td>\n",
              "      <td>Sushi, Anyone?</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>24</th>\n",
              "      <td>Akiko</td>\n",
              "      <td>Yokomoto</td>\n",
              "      <td>Sushi, Anyone?</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-00225c0a-6eaf-426a-be80-a42218b31e88')\"\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-00225c0a-6eaf-426a-be80-a42218b31e88 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-00225c0a-6eaf-426a-be80-a42218b31e88');\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": [
        "pd.merge(pd.merge(titles, titleauthor, on=\"title_id\", how=\"inner\"),authors, on=\"au_id\", how=\"inner\")[[\"au_fname\", \"au_lname\", \"title\"]]"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "wyHbqdhJLwCq"
      },
      "source": [
        "## 5. Select the full name of authors of Psychology books\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 9,
      "metadata": {
        "id": "49cUdadtLwCq",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "6f473560-cbb6-49b1-9aee-be8504767895"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "6     Stearns MacFeather\n",
              "11           Anne Ringer\n",
              "16     Charlene Locksley\n",
              "17          Livia Karsen\n",
              "18         Albert Ringer\n",
              "20         Johnson White\n",
              "Name: full_name, dtype: object"
            ]
          },
          "metadata": {},
          "execution_count": 9
        }
      ],
      "source": [
        "title_authors = (pd.merge(pd.merge(titles, titleauthor, on=\"title_id\", how=\"inner\"),authors, on=\"au_id\", how=\"inner\"))\n",
        "title_authors[\"full_name\"] = (title_authors[\"au_fname\"] + [\" \"] + title_authors[\"au_lname\"])\n",
        "title_authors_psy = (title_authors[[\"full_name\", \"type\"]][title_authors[\"type\"] == \"psychology\"])\n",
        "title_authors_psy[\"full_name\"].drop_duplicates()\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "_7_wG0abLwCq"
      },
      "source": [
        "## 6. Explore the table roysched and try to grasp the meaning of each column. \n",
        "   The notes below will help:\n",
        "   \n",
        "   - \"Royalty\" means the percentage of the sale price paid to the author(s).\n",
        "   \n",
        "   - Sometimes, the royalty may be smaller for the first few sales (which have\n",
        "     to cover the publishing costs to the publisher) but higher for the sales \n",
        "     above a certain threshold.\n",
        "     \n",
        "   - In the \"roysched\" table each title_id can appear multiple times, with\n",
        "     different royalty values for each range of sales.\n",
        "     \n",
        "   - Select all rows for particular title_id, for example \"BU1111\", and explore\n",
        "\t the data."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 7,
      "metadata": {
        "id": "ncvhq8s_LwCr",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 300
        },
        "outputId": "b52a9bd1-922b-4bb4-ea52-b36ef8c4a0c6"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "   title_id  lorange  hirange  royalty\n",
              "49   BU1111        0     4000       10\n",
              "50   BU1111     4001     8000       12\n",
              "51   BU1111     8001    10000       14\n",
              "52   BU1111    12001    16000       16\n",
              "53   BU1111    16001    20000       18\n",
              "54   BU1111    20001    24000       20\n",
              "55   BU1111    24001    28000       22\n",
              "56   BU1111    28001    50000       24"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-11432684-be82-4d15-be0d-99e9bbbce279\">\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>lorange</th>\n",
              "      <th>hirange</th>\n",
              "      <th>royalty</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>49</th>\n",
              "      <td>BU1111</td>\n",
              "      <td>0</td>\n",
              "      <td>4000</td>\n",
              "      <td>10</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>50</th>\n",
              "      <td>BU1111</td>\n",
              "      <td>4001</td>\n",
              "      <td>8000</td>\n",
              "      <td>12</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>51</th>\n",
              "      <td>BU1111</td>\n",
              "      <td>8001</td>\n",
              "      <td>10000</td>\n",
              "      <td>14</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>52</th>\n",
              "      <td>BU1111</td>\n",
              "      <td>12001</td>\n",
              "      <td>16000</td>\n",
              "      <td>16</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>53</th>\n",
              "      <td>BU1111</td>\n",
              "      <td>16001</td>\n",
              "      <td>20000</td>\n",
              "      <td>18</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>54</th>\n",
              "      <td>BU1111</td>\n",
              "      <td>20001</td>\n",
              "      <td>24000</td>\n",
              "      <td>20</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>55</th>\n",
              "      <td>BU1111</td>\n",
              "      <td>24001</td>\n",
              "      <td>28000</td>\n",
              "      <td>22</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>56</th>\n",
              "      <td>BU1111</td>\n",
              "      <td>28001</td>\n",
              "      <td>50000</td>\n",
              "      <td>24</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-11432684-be82-4d15-be0d-99e9bbbce279')\"\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-11432684-be82-4d15-be0d-99e9bbbce279 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-11432684-be82-4d15-be0d-99e9bbbce279');\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": 7
        }
      ],
      "source": [
        "roysched[roysched[\"title_id\"] == \"BU1111\"]"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "1B0wdLM8LwCr"
      },
      "source": [
        "## 7. Select all the book titles and the maximum royalty they can reach.\n",
        "    Display only titles that are present in the roysched table."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 8,
      "metadata": {
        "id": "Xka--NF7LwCr",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 582
        },
        "outputId": "03b84d2c-4253-47fd-c7d5-c5f9e3c89a10"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "                                                    royalty_x\n",
              "title                                                        \n",
              "But Is It User Friendly?                                   18\n",
              "Computer Phobic AND Non-Phobic Individuals: Beh...         18\n",
              "Cooking with Computers: Surreptitious Balance S...         24\n",
              "Emotional Security: A New Algorithm                        12\n",
              "Fifty Years in Buckingham Palace Kitchens                  22\n",
              "Is Anger the Enemy?                                        16\n",
              "Life Without Fear                                          16\n",
              "Onions, Leeks, and Garlic: Cooking Secrets of t...         24\n",
              "Prolonged Data Deprivation: Four Case Studies              16\n",
              "Secrets of Silicon Valley                                  16\n",
              "Silicon Valley Gastronomic Treats                          20\n",
              "Straight Talk About Computers                              24\n",
              "Sushi, Anyone?                                             14\n",
              "The Busy Executive's Database Guide                        12\n",
              "The Gourmet Microwave                                      24\n",
              "You Can Combat Computer Stress!                            24"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-a19e865d-2291-4053-bd12-7deadbdfa8e5\">\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>royalty_x</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>title</th>\n",
              "      <th></th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>But Is It User Friendly?</th>\n",
              "      <td>18</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Computer Phobic AND Non-Phobic Individuals: Behavior Variations</th>\n",
              "      <td>18</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Cooking with Computers: Surreptitious Balance Sheets</th>\n",
              "      <td>24</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Emotional Security: A New Algorithm</th>\n",
              "      <td>12</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Fifty Years in Buckingham Palace Kitchens</th>\n",
              "      <td>22</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Is Anger the Enemy?</th>\n",
              "      <td>16</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Life Without Fear</th>\n",
              "      <td>16</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean</th>\n",
              "      <td>24</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Prolonged Data Deprivation: Four Case Studies</th>\n",
              "      <td>16</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Secrets of Silicon Valley</th>\n",
              "      <td>16</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Silicon Valley Gastronomic Treats</th>\n",
              "      <td>20</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Straight Talk About Computers</th>\n",
              "      <td>24</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Sushi, Anyone?</th>\n",
              "      <td>14</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>The Busy Executive's Database Guide</th>\n",
              "      <td>12</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>The Gourmet Microwave</th>\n",
              "      <td>24</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>You Can Combat Computer Stress!</th>\n",
              "      <td>24</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-a19e865d-2291-4053-bd12-7deadbdfa8e5')\"\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-a19e865d-2291-4053-bd12-7deadbdfa8e5 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-a19e865d-2291-4053-bd12-7deadbdfa8e5');\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": 8
        }
      ],
      "source": [
        "title_royal = (pd.merge(roysched, titles, on=\"title_id\", how=\"left\"))\n",
        "title_royal.groupby(\"title\").agg({\"royalty_x\": \"max\"})"
      ]
    }
  ],
  "metadata": {
    "kernelspec": {
      "display_name": "base",
      "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 (main, Nov  4 2022, 16:35:55) [MSC v.1916 64 bit (AMD64)]"
    },
    "orig_nbformat": 4,
    "vscode": {
      "interpreter": {
        "hash": "ad2bdc8ecc057115af97d19610ffacc2b4e99fae6737bb82f5d7fb13d2f2c186"
      }
    },
    "colab": {
      "provenance": [],
      "include_colab_link": true
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}