audibleBook_Revenue.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "toc_visible": true,
      "authorship_tag": "ABX9TyNN7q3YSJwUKBtQcQfXvcIz",
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/thunchanokbow/audibleBook_Revenue/blob/main/audibleBook_Revenue.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "#Connect to SQL Database\n",
        "\n",
        "Install PyMySQL"
      ],
      "metadata": {
        "id": "fhVrIRmqA30F"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "! pip install pymysql\n",
        "\n",
        "import pymysql\n",
        "\n",
        "# Connect to the database\n",
        "connection = pymysql.connect(host=Config.MYSQL_HOST,\n",
        "                             port=Config.MYSQL_PORT,\n",
        "                             user=Config.MYSQL_USER,\n",
        "                             password=Config.MYSQL_PASSWORD,\n",
        "                             db=Config.MYSQL_DB,\n",
        "                             charset=Config.MYSQL_CHARSET,\n",
        "                             cursorclass=pymysql.cursors.DictCursor)\n",
        "\n",
        "# list all tables\n",
        "cursor = connection.cursor()\n",
        "cursor.execute(\"show tables;\")      #SQL: show table\n",
        "tables = cursor.fetchall()\n",
        "cursor.close()\n",
        "print(tables)"
      ],
      "metadata": {
        "id": "MnO6txZFA3Ym"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "##Query Table"
      ],
      "metadata": {
        "id": "xMZjeCifD1tT"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "#Table audible_data from MySQL database\n",
        "sql = \"SELECT * FROM audible_data\"\n",
        "audible_data = pd.read_sql(sql, connection)\n",
        "audible_data\n",
        "\n",
        "#Table audible_transaction from MySQL database\n",
        "sql = \"SELECT * FROM audible_transaction\"\n",
        "audible_transaction = pd.read_sql(sql, connection)\n",
        "audible_transaction"
      ],
      "metadata": {
        "id": "MA54mYtID8mY"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Data Collection with Python Library Pandas"
      ],
      "metadata": {
        "id": "8h920UVCOOKU"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "##Import Library pandas"
      ],
      "metadata": {
        "id": "-8ckyyjXPVJm"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import pandas as pd"
      ],
      "metadata": {
        "id": "0ycs-HMMPpCK"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "##Set Column `Book_ID` to be the index"
      ],
      "metadata": {
        "id": "kZZ6AuQIH3mO"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "audible_data = audible_data.set_index(\"Book_ID\")"
      ],
      "metadata": {
        "id": "hS0LAAc1H22c"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "##Delete column Unnamed"
      ],
      "metadata": {
        "id": "QTKk2kuUE0_z"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "audible_data.info()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "1PICPQXyHKOJ",
        "outputId": "8aafb4c0-3666-4ab0-eaac-d21b8a6331b9"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "<class 'pandas.core.frame.DataFrame'>\n",
            "Int64Index: 2269 entries, 1 to 2269\n",
            "Data columns (total 11 columns):\n",
            " #   Column                Non-Null Count  Dtype  \n",
            "---  ------                --------------  -----  \n",
            " 0   Unnamed: 0            2269 non-null   int64  \n",
            " 1   Book Title            2269 non-null   object \n",
            " 2   Book Subtitle         1471 non-null   object \n",
            " 3   Book Author           2269 non-null   object \n",
            " 4   Book Narrator         2211 non-null   object \n",
            " 5   Audio Runtime         2269 non-null   object \n",
            " 6   Audiobook_Type        2206 non-null   object \n",
            " 7   Categories            2152 non-null   object \n",
            " 8   Rating                2033 non-null   object \n",
            " 9   Total No. of Ratings  1975 non-null   float64\n",
            " 10  Price                 2269 non-null   object \n",
            "dtypes: float64(1), int64(1), object(9)\n",
            "memory usage: 212.7+ KB\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "audible_data = audible_data.drop(\"Unnamed: 0\", axis=1)"
      ],
      "metadata": {
        "id": "L_OlAg6JHavY"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "#Transaction Dataframe"
      ],
      "metadata": {
        "id": "aR2Nabz3fgfx"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "##Join table: audible_transaction & audible_data\n",
        "\n",
        "Use column `Book_ID` to merge transaction dataframe and audible dataframe to see the book's price of each transaction   "
      ],
      "metadata": {
        "id": "7ITp4r5eKnpB"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "transaction = audible_transaction.merge(\n",
        "    audible_data, how=\"left\", left_on=\"book_id\", right_on=\"Book_ID\"\n",
        "    )"
      ],
      "metadata": {
        "id": "TMCCYfReM-9K"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "transaction = transaction.drop(\"Unnamed: 0\", axis=1)"
      ],
      "metadata": {
        "id": "soI7MRa4OuqF"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "transaction[:3]"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 381
        },
        "id": "svRS4LDHNU1s",
        "outputId": "0bb7ab96-e795-42da-d131-e4926c37781e"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "             timestamp   user_id  book_id                   country  \\\n",
              "0  2021-05-01 00:00:01  ad8eca41     1584                  Portugal   \n",
              "1  2021-05-01 00:00:03  561b26c1      829  United States of America   \n",
              "2  2021-05-01 00:00:04  81f149e5     1391                     Japan   \n",
              "\n",
              "                         Book Title  \\\n",
              "0                      Sycamore Row   \n",
              "1  The 5 Love Languages of Children   \n",
              "2                     The Guardians   \n",
              "\n",
              "                               Book Subtitle   Book Author Book Narrator  \\\n",
              "0                                        NaN  John Grisham  Michael Beck   \n",
              "1  The Secret to Loving Children Effectively  Gary Chapman   Chris Fabry   \n",
              "2                                    A Novel  John Grisham  Michael Beck   \n",
              "\n",
              "        Audio Runtime        Audiobook_Type  \\\n",
              "0  20 hrs and 46 mins  Unabridged Audiobook   \n",
              "1   5 hrs and 48 mins  Unabridged Audiobook   \n",
              "2  11 hrs and 50 mins  Unabridged Audiobook   \n",
              "\n",
              "                                        Categories Rating  \\\n",
              "0                     Mystery, Thriller & Suspense    4.6   \n",
              "1  Relationships, Parenting & Personal Development    4.8   \n",
              "2                     Mystery, Thriller & Suspense    4.6   \n",
              "\n",
              "   Total No. of Ratings    Price  \n",
              "0               25863.0  $35.00   \n",
              "1                2441.0  $19.59   \n",
              "2               26781.0  $31.50   "
            ],
            "text/html": [
              "\n",
              "\n",
              "  <div id=\"df-cf7cc7a3-0c17-43a3-9b64-19b3b0b3931f\">\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>timestamp</th>\n",
              "      <th>user_id</th>\n",
              "      <th>book_id</th>\n",
              "      <th>country</th>\n",
              "      <th>Book Title</th>\n",
              "      <th>Book Subtitle</th>\n",
              "      <th>Book Author</th>\n",
              "      <th>Book Narrator</th>\n",
              "      <th>Audio Runtime</th>\n",
              "      <th>Audiobook_Type</th>\n",
              "      <th>Categories</th>\n",
              "      <th>Rating</th>\n",
              "      <th>Total No. of Ratings</th>\n",
              "      <th>Price</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>2021-05-01 00:00:01</td>\n",
              "      <td>ad8eca41</td>\n",
              "      <td>1584</td>\n",
              "      <td>Portugal</td>\n",
              "      <td>Sycamore Row</td>\n",
              "      <td>NaN</td>\n",
              "      <td>John Grisham</td>\n",
              "      <td>Michael Beck</td>\n",
              "      <td>20 hrs and 46 mins</td>\n",
              "      <td>Unabridged Audiobook</td>\n",
              "      <td>Mystery, Thriller &amp; Suspense</td>\n",
              "      <td>4.6</td>\n",
              "      <td>25863.0</td>\n",
              "      <td>$35.00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2021-05-01 00:00:03</td>\n",
              "      <td>561b26c1</td>\n",
              "      <td>829</td>\n",
              "      <td>United States of America</td>\n",
              "      <td>The 5 Love Languages of Children</td>\n",
              "      <td>The Secret to Loving Children Effectively</td>\n",
              "      <td>Gary Chapman</td>\n",
              "      <td>Chris Fabry</td>\n",
              "      <td>5 hrs and 48 mins</td>\n",
              "      <td>Unabridged Audiobook</td>\n",
              "      <td>Relationships, Parenting &amp; Personal Development</td>\n",
              "      <td>4.8</td>\n",
              "      <td>2441.0</td>\n",
              "      <td>$19.59</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>2021-05-01 00:00:04</td>\n",
              "      <td>81f149e5</td>\n",
              "      <td>1391</td>\n",
              "      <td>Japan</td>\n",
              "      <td>The Guardians</td>\n",
              "      <td>A Novel</td>\n",
              "      <td>John Grisham</td>\n",
              "      <td>Michael Beck</td>\n",
              "      <td>11 hrs and 50 mins</td>\n",
              "      <td>Unabridged Audiobook</td>\n",
              "      <td>Mystery, Thriller &amp; Suspense</td>\n",
              "      <td>4.6</td>\n",
              "      <td>26781.0</td>\n",
              "      <td>$31.50</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-cf7cc7a3-0c17-43a3-9b64-19b3b0b3931f')\"\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",
              "\n",
              "\n",
              "    <div id=\"df-c34f2a9f-0ddc-4b53-915c-c72df9f693b3\">\n",
              "      <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-c34f2a9f-0ddc-4b53-915c-c72df9f693b3')\"\n",
              "              title=\"Suggest charts.\"\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",
              "    <g>\n",
              "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
              "    </g>\n",
              "</svg>\n",
              "      </button>\n",
              "    </div>\n",
              "\n",
              "<style>\n",
              "  .colab-df-quickchart {\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-quickchart: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-quickchart {\n",
              "    background-color: #3B4455;\n",
              "    fill: #D2E3FC;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart: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",
              "      async function quickchart(key) {\n",
              "        const containerElement = document.querySelector('#' + key);\n",
              "        const charts = await google.colab.kernel.invokeFunction(\n",
              "            'suggestCharts', [key], {});\n",
              "      }\n",
              "    </script>\n",
              "\n",
              "      <script>\n",
              "\n",
              "function displayQuickchartButton(domScope) {\n",
              "  let quickchartButtonEl =\n",
              "    domScope.querySelector('#df-c34f2a9f-0ddc-4b53-915c-c72df9f693b3 button.colab-df-quickchart');\n",
              "  quickchartButtonEl.style.display =\n",
              "    google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "}\n",
              "\n",
              "        displayQuickchartButton(document);\n",
              "      </script>\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-cf7cc7a3-0c17-43a3-9b64-19b3b0b3931f 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-cf7cc7a3-0c17-43a3-9b64-19b3b0b3931f');\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": 26
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "#Conversion Rate Dataframe"
      ],
      "metadata": {
        "id": "eg6_shilgKSE"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "##Get data from REST API\n",
        "\n",
        "URL : [Currency conversion API](https://r2de2-workshop-vmftiryt6q-ts.a.run.app/usd_thb_conversion_rate)"
      ],
      "metadata": {
        "id": "-Fi8oT05O-us"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import requests\n",
        "url = \"https://r2de2-workshop-vmftiryt6q-ts.a.run.app/usd_thb_conversion_rate\"\n",
        "r = requests.get(url)\n",
        "result_conversion_rate = r.json()"
      ],
      "metadata": {
        "id": "FPehjbUMPNDT"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "result_conversion_rate"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "xNoo1oPONe7-",
        "outputId": "f1c317ef-fd25-4c83-ce1f-5ec07787e101"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "{'conversion_rate': {'2021-08-12': 33.084,\n",
              "  '2021-08-11': 33.145,\n",
              "  '2021-08-10': 33.46,\n",
              "  '2021-08-09': 33.464,\n",
              "  '2021-08-08': 33.395,\n",
              "  '2021-08-07': 33.422,\n",
              "  '2021-08-06': 33.45,\n",
              "  '2021-08-05': 33.263,\n",
              "  '2021-08-04': 33.137,\n",
              "  '2021-08-03': 33.022,\n",
              "  '2021-08-02': 32.957,\n",
              "  '2021-08-01': 32.895,\n",
              "  '2021-07-31': 32.887,\n",
              "  '2021-07-30': 32.887,\n",
              "  '2021-07-29': 32.87,\n",
              "  '2021-07-28': 32.82,\n",
              "  '2021-07-27': 32.937,\n",
              "  '2021-07-26': 32.929,\n",
              "  '2021-07-25': 32.94,\n",
              "  '2021-07-24': 32.963,\n",
              "  '2021-07-23': 32.928,\n",
              "  '2021-07-22': 32.887,\n",
              "  '2021-07-21': 32.857,\n",
              "  '2021-07-20': 32.829,\n",
              "  '2021-07-19': 32.854,\n",
              "  '2021-07-18': 32.807,\n",
              "  '2021-07-17': 32.795,\n",
              "  '2021-07-16': 32.783,\n",
              "  '2021-07-15': 32.686,\n",
              "  '2021-07-14': 32.605,\n",
              "  '2021-07-13': 32.654,\n",
              "  '2021-07-12': 32.664,\n",
              "  '2021-07-11': 32.506,\n",
              "  '2021-07-10': 32.482,\n",
              "  '2021-07-09': 32.512,\n",
              "  '2021-07-08': 32.534,\n",
              "  '2021-07-07': 32.334,\n",
              "  '2021-07-06': 32.271,\n",
              "  '2021-07-05': 32.112,\n",
              "  '2021-07-04': 32.136,\n",
              "  '2021-07-03': 32.125,\n",
              "  '2021-07-02': 32.126,\n",
              "  '2021-07-01': 32.073,\n",
              "  '2021-06-30': 32.029,\n",
              "  '2021-06-29': 32.108,\n",
              "  '2021-06-28': 31.918,\n",
              "  '2021-06-27': 31.785,\n",
              "  '2021-06-26': 31.816,\n",
              "  '2021-06-25': 31.816,\n",
              "  '2021-06-24': 31.898,\n",
              "  '2021-06-23': 31.825,\n",
              "  '2021-06-22': 31.681,\n",
              "  '2021-06-21': 31.617,\n",
              "  '2021-06-20': 31.461,\n",
              "  '2021-06-19': 31.437,\n",
              "  '2021-06-18': 31.439,\n",
              "  '2021-06-17': 31.422,\n",
              "  '2021-06-16': 31.297,\n",
              "  '2021-06-15': 31.162,\n",
              "  '2021-06-14': 31.117,\n",
              "  '2021-06-13': 31.077,\n",
              "  '2021-06-12': 31.074,\n",
              "  '2021-06-11': 31.092,\n",
              "  '2021-06-10': 31.14,\n",
              "  '2021-06-09': 31.167,\n",
              "  '2021-06-08': 31.202,\n",
              "  '2021-06-07': 31.176,\n",
              "  '2021-06-06': 31.133,\n",
              "  '2021-06-05': 31.125,\n",
              "  '2021-06-04': 31.125,\n",
              "  '2021-06-03': 31.209,\n",
              "  '2021-06-02': 31.108,\n",
              "  '2021-06-01': 31.169,\n",
              "  '2021-05-31': 31.193,\n",
              "  '2021-05-30': 31.238,\n",
              "  '2021-05-29': 31.252,\n",
              "  '2021-05-28': 31.257,\n",
              "  '2021-05-27': 31.297,\n",
              "  '2021-05-26': 31.281,\n",
              "  '2021-05-25': 31.328,\n",
              "  '2021-05-24': 31.323,\n",
              "  '2021-05-23': 31.37,\n",
              "  '2021-05-22': 31.361,\n",
              "  '2021-05-21': 31.361,\n",
              "  '2021-05-20': 31.378,\n",
              "  '2021-05-19': 31.413,\n",
              "  '2021-05-18': 31.42,\n",
              "  '2021-05-17': 31.492,\n",
              "  '2021-05-16': 31.365,\n",
              "  '2021-05-15': 31.33,\n",
              "  '2021-05-14': 31.33,\n",
              "  '2021-05-13': 31.344,\n",
              "  '2021-05-12': 31.304,\n",
              "  '2021-05-11': 31.158,\n",
              "  '2021-05-10': 31.107,\n",
              "  '2021-05-09': 31.033,\n",
              "  '2021-05-08': 31.042,\n",
              "  '2021-05-07': 31.042,\n",
              "  '2021-05-06': 31.226,\n",
              "  '2021-05-05': 31.13,\n",
              "  '2021-05-04': 31.188,\n",
              "  '2021-05-03': 31.132,\n",
              "  '2021-05-02': 31.122,\n",
              "  '2021-05-01': 31.14,\n",
              "  '2021-04-30': 31.139,\n",
              "  '2021-04-29': 31.197,\n",
              "  '2021-04-28': 31.344,\n",
              "  '2021-04-27': 31.355,\n",
              "  '2021-04-26': 31.427,\n",
              "  '2021-04-25': 31.421,\n",
              "  '2021-04-24': 31.411,\n",
              "  '2021-04-23': 31.412,\n",
              "  '2021-04-22': 31.378,\n",
              "  '2021-04-21': 31.3,\n",
              "  '2021-04-20': 31.281,\n",
              "  '2021-04-19': 31.203,\n",
              "  '2021-04-18': 31.226,\n",
              "  '2021-04-17': 31.204,\n",
              "  '2021-04-16': 31.203,\n",
              "  '2021-04-15': 31.165,\n",
              "  '2021-04-14': 31.329,\n",
              "  '2021-04-13': 31.496,\n",
              "  '2021-04-12': 31.482,\n",
              "  '2021-04-11': 31.462,\n",
              "  '2021-04-10': 31.454,\n",
              "  '2021-04-09': 31.451,\n",
              "  '2021-04-08': 31.433,\n",
              "  '2021-04-07': 31.387,\n",
              "  '2021-04-06': 31.288,\n",
              "  '2021-04-05': 31.342,\n",
              "  '2021-04-04': 31.244,\n",
              "  '2021-04-03': 31.256,\n",
              "  '2021-04-02': 31.29,\n",
              "  '2021-04-01': 31.194}}"
            ]
          },
          "metadata": {},
          "execution_count": 30
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "The data type of the conversion rate API is dictionay.\n",
        "\n",
        "it should to be converted to Dataframe before joining to `transaction dataframe`"
      ],
      "metadata": {
        "id": "m4nCycldRU8V"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "print(type(result_conversion_rate))\n",
        "assert isinstance(result_conversion_rate, dict)"
      ],
      "metadata": {
        "id": "kkUZ4TVWTJ01"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "##Convert to Pandas Dataframe"
      ],
      "metadata": {
        "id": "Nujg6SyiTXfy"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "conversion_rate = pd.DataFrame(result_conversion_rate)\n",
        "conversion_rate"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 424
        },
        "id": "jWr74-2iQ2SW",
        "outputId": "1b654d70-58af-4cbf-bb9c-1538a4edf166"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "            conversion_rate\n",
              "2021-04-01           31.194\n",
              "2021-04-02           31.290\n",
              "2021-04-03           31.256\n",
              "2021-04-04           31.244\n",
              "2021-04-05           31.342\n",
              "...                     ...\n",
              "2021-08-08           33.395\n",
              "2021-08-09           33.464\n",
              "2021-08-10           33.460\n",
              "2021-08-11           33.145\n",
              "2021-08-12           33.084\n",
              "\n",
              "[134 rows x 1 columns]"
            ],
            "text/html": [
              "\n",
              "\n",
              "  <div id=\"df-01a1c3ee-4437-4ec3-a449-a3e1407f2f6f\">\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>conversion_rate</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>2021-04-01</th>\n",
              "      <td>31.194</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2021-04-02</th>\n",
              "      <td>31.290</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2021-04-03</th>\n",
              "      <td>31.256</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2021-04-04</th>\n",
              "      <td>31.244</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2021-04-05</th>\n",
              "      <td>31.342</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2021-08-08</th>\n",
              "      <td>33.395</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2021-08-09</th>\n",
              "      <td>33.464</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2021-08-10</th>\n",
              "      <td>33.460</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2021-08-11</th>\n",
              "      <td>33.145</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2021-08-12</th>\n",
              "      <td>33.084</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>134 rows × 1 columns</p>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-01a1c3ee-4437-4ec3-a449-a3e1407f2f6f')\"\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",
              "\n",
              "\n",
              "    <div id=\"df-e335e4a1-0bc3-471c-b368-ff6b319b011a\">\n",
              "      <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-e335e4a1-0bc3-471c-b368-ff6b319b011a')\"\n",
              "              title=\"Suggest charts.\"\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",
              "    <g>\n",
              "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
              "    </g>\n",
              "</svg>\n",
              "      </button>\n",
              "    </div>\n",
              "\n",
              "<style>\n",
              "  .colab-df-quickchart {\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-quickchart: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-quickchart {\n",
              "    background-color: #3B4455;\n",
              "    fill: #D2E3FC;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart: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",
              "      async function quickchart(key) {\n",
              "        const containerElement = document.querySelector('#' + key);\n",
              "        const charts = await google.colab.kernel.invokeFunction(\n",
              "            'suggestCharts', [key], {});\n",
              "      }\n",
              "    </script>\n",
              "\n",
              "      <script>\n",
              "\n",
              "function displayQuickchartButton(domScope) {\n",
              "  let quickchartButtonEl =\n",
              "    domScope.querySelector('#df-e335e4a1-0bc3-471c-b368-ff6b319b011a button.colab-df-quickchart');\n",
              "  quickchartButtonEl.style.display =\n",
              "    google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "}\n",
              "\n",
              "        displayQuickchartButton(document);\n",
              "      </script>\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-01a1c3ee-4437-4ec3-a449-a3e1407f2f6f 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-01a1c3ee-4437-4ec3-a449-a3e1407f2f6f');\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": 32
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "##Rename index column to `Date` column"
      ],
      "metadata": {
        "id": "54azVp53TwVx"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "conversion_rate = conversion_rate.reset_index().rename(columns={\"index\": \"Date\"})\n",
        "conversion_rate"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 424
        },
        "id": "k5njC5THTDg5",
        "outputId": "de73bae4-b778-449b-ffb5-262d99f2bc13"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "           Date  conversion_rate\n",
              "0    2021-04-01           31.194\n",
              "1    2021-04-02           31.290\n",
              "2    2021-04-03           31.256\n",
              "3    2021-04-04           31.244\n",
              "4    2021-04-05           31.342\n",
              "..          ...              ...\n",
              "129  2021-08-08           33.395\n",
              "130  2021-08-09           33.464\n",
              "131  2021-08-10           33.460\n",
              "132  2021-08-11           33.145\n",
              "133  2021-08-12           33.084\n",
              "\n",
              "[134 rows x 2 columns]"
            ],
            "text/html": [
              "\n",
              "\n",
              "  <div id=\"df-2dd28d9d-5577-48ff-92d0-27923a1265a4\">\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>Date</th>\n",
              "      <th>conversion_rate</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>2021-04-01</td>\n",
              "      <td>31.194</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2021-04-02</td>\n",
              "      <td>31.290</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>2021-04-03</td>\n",
              "      <td>31.256</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>2021-04-04</td>\n",
              "      <td>31.244</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>2021-04-05</td>\n",
              "      <td>31.342</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>129</th>\n",
              "      <td>2021-08-08</td>\n",
              "      <td>33.395</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>130</th>\n",
              "      <td>2021-08-09</td>\n",
              "      <td>33.464</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>131</th>\n",
              "      <td>2021-08-10</td>\n",
              "      <td>33.460</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>132</th>\n",
              "      <td>2021-08-11</td>\n",
              "      <td>33.145</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>133</th>\n",
              "      <td>2021-08-12</td>\n",
              "      <td>33.084</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>134 rows × 2 columns</p>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-2dd28d9d-5577-48ff-92d0-27923a1265a4')\"\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",
              "\n",
              "\n",
              "    <div id=\"df-b2225a0d-f032-4598-98ae-597f33ffbb55\">\n",
              "      <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-b2225a0d-f032-4598-98ae-597f33ffbb55')\"\n",
              "              title=\"Suggest charts.\"\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",
              "    <g>\n",
              "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
              "    </g>\n",
              "</svg>\n",
              "      </button>\n",
              "    </div>\n",
              "\n",
              "<style>\n",
              "  .colab-df-quickchart {\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-quickchart: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-quickchart {\n",
              "    background-color: #3B4455;\n",
              "    fill: #D2E3FC;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart: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",
              "      async function quickchart(key) {\n",
              "        const containerElement = document.querySelector('#' + key);\n",
              "        const charts = await google.colab.kernel.invokeFunction(\n",
              "            'suggestCharts', [key], {});\n",
              "      }\n",
              "    </script>\n",
              "\n",
              "      <script>\n",
              "\n",
              "function displayQuickchartButton(domScope) {\n",
              "  let quickchartButtonEl =\n",
              "    domScope.querySelector('#df-b2225a0d-f032-4598-98ae-597f33ffbb55 button.colab-df-quickchart');\n",
              "  quickchartButtonEl.style.display =\n",
              "    google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "}\n",
              "\n",
              "        displayQuickchartButton(document);\n",
              "      </script>\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-2dd28d9d-5577-48ff-92d0-27923a1265a4 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-2dd28d9d-5577-48ff-92d0-27923a1265a4');\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": 34
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "conversion_rate.info()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "87iCAg5MUNng",
        "outputId": "07d83c4d-3ecc-45e4-f1b9-770c3bc1b771"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "<class 'pandas.core.frame.DataFrame'>\n",
            "RangeIndex: 134 entries, 0 to 133\n",
            "Data columns (total 2 columns):\n",
            " #   Column           Non-Null Count  Dtype  \n",
            "---  ------           --------------  -----  \n",
            " 0   Date             134 non-null    object \n",
            " 1   conversion_rate  134 non-null    float64\n",
            "dtypes: float64(1), object(1)\n",
            "memory usage: 2.2+ KB\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "##convert `Date` Column to datetime"
      ],
      "metadata": {
        "id": "s7ZqCywUVwtE"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "conversion_rate['Date'] = pd.to_datetime(conversion_rate['Date']).dt.date"
      ],
      "metadata": {
        "id": "pZ8wr0PxVRza"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "#Output Dataframe"
      ],
      "metadata": {
        "id": "s18TpLydf2kQ"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "##Join table: transaction & conversion_rate\n",
        "\n",
        "\n",
        "first of all copy the `timestamp` column to the `date` column then convert to datetime."
      ],
      "metadata": {
        "id": "y8cVMKroXKrg"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "transaction['date'] = transaction['timestamp']\n",
        "transaction[:3]"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 381
        },
        "id": "0MMv_tCLXEBv",
        "outputId": "d580459e-3fb7-4340-aec3-05335bb388f6"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "             timestamp   user_id  book_id                   country  \\\n",
              "0  2021-05-01 00:00:01  ad8eca41     1584                  Portugal   \n",
              "1  2021-05-01 00:00:03  561b26c1      829  United States of America   \n",
              "2  2021-05-01 00:00:04  81f149e5     1391                     Japan   \n",
              "\n",
              "                         Book Title  \\\n",
              "0                      Sycamore Row   \n",
              "1  The 5 Love Languages of Children   \n",
              "2                     The Guardians   \n",
              "\n",
              "                               Book Subtitle   Book Author Book Narrator  \\\n",
              "0                                        NaN  John Grisham  Michael Beck   \n",
              "1  The Secret to Loving Children Effectively  Gary Chapman   Chris Fabry   \n",
              "2                                    A Novel  John Grisham  Michael Beck   \n",
              "\n",
              "        Audio Runtime        Audiobook_Type  \\\n",
              "0  20 hrs and 46 mins  Unabridged Audiobook   \n",
              "1   5 hrs and 48 mins  Unabridged Audiobook   \n",
              "2  11 hrs and 50 mins  Unabridged Audiobook   \n",
              "\n",
              "                                        Categories Rating  \\\n",
              "0                     Mystery, Thriller & Suspense    4.6   \n",
              "1  Relationships, Parenting & Personal Development    4.8   \n",
              "2                     Mystery, Thriller & Suspense    4.6   \n",
              "\n",
              "   Total No. of Ratings    Price                 date  \n",
              "0               25863.0  $35.00   2021-05-01 00:00:01  \n",
              "1                2441.0  $19.59   2021-05-01 00:00:03  \n",
              "2               26781.0  $31.50   2021-05-01 00:00:04  "
            ],
            "text/html": [
              "\n",
              "\n",
              "  <div id=\"df-02ccedfe-7fe4-475b-b2b8-9c97104a94d1\">\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>timestamp</th>\n",
              "      <th>user_id</th>\n",
              "      <th>book_id</th>\n",
              "      <th>country</th>\n",
              "      <th>Book Title</th>\n",
              "      <th>Book Subtitle</th>\n",
              "      <th>Book Author</th>\n",
              "      <th>Book Narrator</th>\n",
              "      <th>Audio Runtime</th>\n",
              "      <th>Audiobook_Type</th>\n",
              "      <th>Categories</th>\n",
              "      <th>Rating</th>\n",
              "      <th>Total No. of Ratings</th>\n",
              "      <th>Price</th>\n",
              "      <th>date</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>2021-05-01 00:00:01</td>\n",
              "      <td>ad8eca41</td>\n",
              "      <td>1584</td>\n",
              "      <td>Portugal</td>\n",
              "      <td>Sycamore Row</td>\n",
              "      <td>NaN</td>\n",
              "      <td>John Grisham</td>\n",
              "      <td>Michael Beck</td>\n",
              "      <td>20 hrs and 46 mins</td>\n",
              "      <td>Unabridged Audiobook</td>\n",
              "      <td>Mystery, Thriller &amp; Suspense</td>\n",
              "      <td>4.6</td>\n",
              "      <td>25863.0</td>\n",
              "      <td>$35.00</td>\n",
              "      <td>2021-05-01 00:00:01</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2021-05-01 00:00:03</td>\n",
              "      <td>561b26c1</td>\n",
              "      <td>829</td>\n",
              "      <td>United States of America</td>\n",
              "      <td>The 5 Love Languages of Children</td>\n",
              "      <td>The Secret to Loving Children Effectively</td>\n",
              "      <td>Gary Chapman</td>\n",
              "      <td>Chris Fabry</td>\n",
              "      <td>5 hrs and 48 mins</td>\n",
              "      <td>Unabridged Audiobook</td>\n",
              "      <td>Relationships, Parenting &amp; Personal Development</td>\n",
              "      <td>4.8</td>\n",
              "      <td>2441.0</td>\n",
              "      <td>$19.59</td>\n",
              "      <td>2021-05-01 00:00:03</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>2021-05-01 00:00:04</td>\n",
              "      <td>81f149e5</td>\n",
              "      <td>1391</td>\n",
              "      <td>Japan</td>\n",
              "      <td>The Guardians</td>\n",
              "      <td>A Novel</td>\n",
              "      <td>John Grisham</td>\n",
              "      <td>Michael Beck</td>\n",
              "      <td>11 hrs and 50 mins</td>\n",
              "      <td>Unabridged Audiobook</td>\n",
              "      <td>Mystery, Thriller &amp; Suspense</td>\n",
              "      <td>4.6</td>\n",
              "      <td>26781.0</td>\n",
              "      <td>$31.50</td>\n",
              "      <td>2021-05-01 00:00:04</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-02ccedfe-7fe4-475b-b2b8-9c97104a94d1')\"\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",
              "\n",
              "\n",
              "    <div id=\"df-5d1350e7-cc39-4887-89d8-369653dea528\">\n",
              "      <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-5d1350e7-cc39-4887-89d8-369653dea528')\"\n",
              "              title=\"Suggest charts.\"\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",
              "    <g>\n",
              "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
              "    </g>\n",
              "</svg>\n",
              "      </button>\n",
              "    </div>\n",
              "\n",
              "<style>\n",
              "  .colab-df-quickchart {\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-quickchart: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-quickchart {\n",
              "    background-color: #3B4455;\n",
              "    fill: #D2E3FC;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart: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",
              "      async function quickchart(key) {\n",
              "        const containerElement = document.querySelector('#' + key);\n",
              "        const charts = await google.colab.kernel.invokeFunction(\n",
              "            'suggestCharts', [key], {});\n",
              "      }\n",
              "    </script>\n",
              "\n",
              "      <script>\n",
              "\n",
              "function displayQuickchartButton(domScope) {\n",
              "  let quickchartButtonEl =\n",
              "    domScope.querySelector('#df-5d1350e7-cc39-4887-89d8-369653dea528 button.colab-df-quickchart');\n",
              "  quickchartButtonEl.style.display =\n",
              "    google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "}\n",
              "\n",
              "        displayQuickchartButton(document);\n",
              "      </script>\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-02ccedfe-7fe4-475b-b2b8-9c97104a94d1 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-02ccedfe-7fe4-475b-b2b8-9c97104a94d1');\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": 37
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "transaction['date'] = pd.to_datetime(transaction['date']).dt.date"
      ],
      "metadata": {
        "id": "20CCNm2DZase"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "Use column `Date` to merge transaction dataframe and conversion rate dataframe"
      ],
      "metadata": {
        "id": "KcPCue50YvO6"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "final_df = transaction.merge(conversion_rate, how=\"left\", left_on=\"date\", right_on=\"Date\")\n",
        "final_df.head()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 565
        },
        "id": "8PjiAlS3aNQB",
        "outputId": "81baa5c6-040e-48e1-82fa-fe5d14df5e1f"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "             timestamp   user_id  book_id                   country  \\\n",
              "0  2021-05-01 00:00:01  ad8eca41     1584                  Portugal   \n",
              "1  2021-05-01 00:00:03  561b26c1      829  United States of America   \n",
              "2  2021-05-01 00:00:04  81f149e5     1391                     Japan   \n",
              "3  2021-05-01 00:00:07  4f218413     1586                    Taiwan   \n",
              "4  2021-05-01 00:00:18  a4066781      300  United States of America   \n",
              "\n",
              "                             Book Title  \\\n",
              "0                          Sycamore Row   \n",
              "1      The 5 Love Languages of Children   \n",
              "2                         The Guardians   \n",
              "3                    How to Be a 3% Man   \n",
              "4  Napoleon Hill's Outwitting the Devil   \n",
              "\n",
              "                               Book Subtitle    Book Author    Book Narrator  \\\n",
              "0                                        NaN   John Grisham     Michael Beck   \n",
              "1  The Secret to Loving Children Effectively   Gary Chapman      Chris Fabry   \n",
              "2                                    A Novel   John Grisham     Michael Beck   \n",
              "3                                        NaN    Corey Wayne      Corey Wayne   \n",
              "4          The Secret to Freedom and Success  Napoleon Hill  Dan John Miller   \n",
              "\n",
              "        Audio Runtime        Audiobook_Type  \\\n",
              "0  20 hrs and 46 mins  Unabridged Audiobook   \n",
              "1   5 hrs and 48 mins  Unabridged Audiobook   \n",
              "2  11 hrs and 50 mins  Unabridged Audiobook   \n",
              "3   7 hrs and 33 mins  Unabridged Audiobook   \n",
              "4   5 hrs and 51 mins  Unabridged Audiobook   \n",
              "\n",
              "                                        Categories Rating  \\\n",
              "0                     Mystery, Thriller & Suspense    4.6   \n",
              "1  Relationships, Parenting & Personal Development    4.8   \n",
              "2                     Mystery, Thriller & Suspense    4.6   \n",
              "3  Relationships, Parenting & Personal Development    4.8   \n",
              "4                               Business & Careers    4.8   \n",
              "\n",
              "   Total No. of Ratings    Price        date        Date  conversion_rate  \n",
              "0               25863.0  $35.00   2021-05-01  2021-05-01            31.14  \n",
              "1                2441.0  $19.59   2021-05-01  2021-05-01            31.14  \n",
              "2               26781.0  $31.50   2021-05-01  2021-05-01            31.14  \n",
              "3               11459.0  $19.95   2021-05-01  2021-05-01            31.14  \n",
              "4                9305.0  $17.49   2021-05-01  2021-05-01            31.14  "
            ],
            "text/html": [
              "\n",
              "\n",
              "  <div id=\"df-c5dc7b1f-f084-4bcc-9fa2-73b5bdf3483e\">\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>timestamp</th>\n",
              "      <th>user_id</th>\n",
              "      <th>book_id</th>\n",
              "      <th>country</th>\n",
              "      <th>Book Title</th>\n",
              "      <th>Book Subtitle</th>\n",
              "      <th>Book Author</th>\n",
              "      <th>Book Narrator</th>\n",
              "      <th>Audio Runtime</th>\n",
              "      <th>Audiobook_Type</th>\n",
              "      <th>Categories</th>\n",
              "      <th>Rating</th>\n",
              "      <th>Total No. of Ratings</th>\n",
              "      <th>Price</th>\n",
              "      <th>date</th>\n",
              "      <th>Date</th>\n",
              "      <th>conversion_rate</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>2021-05-01 00:00:01</td>\n",
              "      <td>ad8eca41</td>\n",
              "      <td>1584</td>\n",
              "      <td>Portugal</td>\n",
              "      <td>Sycamore Row</td>\n",
              "      <td>NaN</td>\n",
              "      <td>John Grisham</td>\n",
              "      <td>Michael Beck</td>\n",
              "      <td>20 hrs and 46 mins</td>\n",
              "      <td>Unabridged Audiobook</td>\n",
              "      <td>Mystery, Thriller &amp; Suspense</td>\n",
              "      <td>4.6</td>\n",
              "      <td>25863.0</td>\n",
              "      <td>$35.00</td>\n",
              "      <td>2021-05-01</td>\n",
              "      <td>2021-05-01</td>\n",
              "      <td>31.14</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2021-05-01 00:00:03</td>\n",
              "      <td>561b26c1</td>\n",
              "      <td>829</td>\n",
              "      <td>United States of America</td>\n",
              "      <td>The 5 Love Languages of Children</td>\n",
              "      <td>The Secret to Loving Children Effectively</td>\n",
              "      <td>Gary Chapman</td>\n",
              "      <td>Chris Fabry</td>\n",
              "      <td>5 hrs and 48 mins</td>\n",
              "      <td>Unabridged Audiobook</td>\n",
              "      <td>Relationships, Parenting &amp; Personal Development</td>\n",
              "      <td>4.8</td>\n",
              "      <td>2441.0</td>\n",
              "      <td>$19.59</td>\n",
              "      <td>2021-05-01</td>\n",
              "      <td>2021-05-01</td>\n",
              "      <td>31.14</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>2021-05-01 00:00:04</td>\n",
              "      <td>81f149e5</td>\n",
              "      <td>1391</td>\n",
              "      <td>Japan</td>\n",
              "      <td>The Guardians</td>\n",
              "      <td>A Novel</td>\n",
              "      <td>John Grisham</td>\n",
              "      <td>Michael Beck</td>\n",
              "      <td>11 hrs and 50 mins</td>\n",
              "      <td>Unabridged Audiobook</td>\n",
              "      <td>Mystery, Thriller &amp; Suspense</td>\n",
              "      <td>4.6</td>\n",
              "      <td>26781.0</td>\n",
              "      <td>$31.50</td>\n",
              "      <td>2021-05-01</td>\n",
              "      <td>2021-05-01</td>\n",
              "      <td>31.14</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>2021-05-01 00:00:07</td>\n",
              "      <td>4f218413</td>\n",
              "      <td>1586</td>\n",
              "      <td>Taiwan</td>\n",
              "      <td>How to Be a 3% Man</td>\n",
              "      <td>NaN</td>\n",
              "      <td>Corey Wayne</td>\n",
              "      <td>Corey Wayne</td>\n",
              "      <td>7 hrs and 33 mins</td>\n",
              "      <td>Unabridged Audiobook</td>\n",
              "      <td>Relationships, Parenting &amp; Personal Development</td>\n",
              "      <td>4.8</td>\n",
              "      <td>11459.0</td>\n",
              "      <td>$19.95</td>\n",
              "      <td>2021-05-01</td>\n",
              "      <td>2021-05-01</td>\n",
              "      <td>31.14</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>2021-05-01 00:00:18</td>\n",
              "      <td>a4066781</td>\n",
              "      <td>300</td>\n",
              "      <td>United States of America</td>\n",
              "      <td>Napoleon Hill's Outwitting the Devil</td>\n",
              "      <td>The Secret to Freedom and Success</td>\n",
              "      <td>Napoleon Hill</td>\n",
              "      <td>Dan John Miller</td>\n",
              "      <td>5 hrs and 51 mins</td>\n",
              "      <td>Unabridged Audiobook</td>\n",
              "      <td>Business &amp; Careers</td>\n",
              "      <td>4.8</td>\n",
              "      <td>9305.0</td>\n",
              "      <td>$17.49</td>\n",
              "      <td>2021-05-01</td>\n",
              "      <td>2021-05-01</td>\n",
              "      <td>31.14</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-c5dc7b1f-f084-4bcc-9fa2-73b5bdf3483e')\"\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",
              "\n",
              "\n",
              "    <div id=\"df-bbd484b5-dcea-4d8e-ac0b-df45c3e1058e\">\n",
              "      <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-bbd484b5-dcea-4d8e-ac0b-df45c3e1058e')\"\n",
              "              title=\"Suggest charts.\"\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",
              "    <g>\n",
              "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
              "    </g>\n",
              "</svg>\n",
              "      </button>\n",
              "    </div>\n",
              "\n",
              "<style>\n",
              "  .colab-df-quickchart {\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-quickchart: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-quickchart {\n",
              "    background-color: #3B4455;\n",
              "    fill: #D2E3FC;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart: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",
              "      async function quickchart(key) {\n",
              "        const containerElement = document.querySelector('#' + key);\n",
              "        const charts = await google.colab.kernel.invokeFunction(\n",
              "            'suggestCharts', [key], {});\n",
              "      }\n",
              "    </script>\n",
              "\n",
              "      <script>\n",
              "\n",
              "function displayQuickchartButton(domScope) {\n",
              "  let quickchartButtonEl =\n",
              "    domScope.querySelector('#df-bbd484b5-dcea-4d8e-ac0b-df45c3e1058e button.colab-df-quickchart');\n",
              "  quickchartButtonEl.style.display =\n",
              "    google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "}\n",
              "\n",
              "        displayQuickchartButton(document);\n",
              "      </script>\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-c5dc7b1f-f084-4bcc-9fa2-73b5bdf3483e 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-c5dc7b1f-f084-4bcc-9fa2-73b5bdf3483e');\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": 40
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "##Delete dollar sign ($)\n",
        "\n",
        "The data type of the `Price` column is string. ($20.52) delete dollar sign every single row then converted the price column type to float."
      ],
      "metadata": {
        "id": "_jcu4FWMczLe"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "final_df[\"Price\"] = final_df.apply(lambda x: x[\"Price\"].replace(\"$\",\"\"), axis=1)\n",
        "final_df.head()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 565
        },
        "id": "DQOKwkJKagUn",
        "outputId": "4ee3c5a6-dfa8-462d-8dd9-97743404383b"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "             timestamp   user_id  book_id                   country  \\\n",
              "0  2021-05-01 00:00:01  ad8eca41     1584                  Portugal   \n",
              "1  2021-05-01 00:00:03  561b26c1      829  United States of America   \n",
              "2  2021-05-01 00:00:04  81f149e5     1391                     Japan   \n",
              "3  2021-05-01 00:00:07  4f218413     1586                    Taiwan   \n",
              "4  2021-05-01 00:00:18  a4066781      300  United States of America   \n",
              "\n",
              "                             Book Title  \\\n",
              "0                          Sycamore Row   \n",
              "1      The 5 Love Languages of Children   \n",
              "2                         The Guardians   \n",
              "3                    How to Be a 3% Man   \n",
              "4  Napoleon Hill's Outwitting the Devil   \n",
              "\n",
              "                               Book Subtitle    Book Author    Book Narrator  \\\n",
              "0                                        NaN   John Grisham     Michael Beck   \n",
              "1  The Secret to Loving Children Effectively   Gary Chapman      Chris Fabry   \n",
              "2                                    A Novel   John Grisham     Michael Beck   \n",
              "3                                        NaN    Corey Wayne      Corey Wayne   \n",
              "4          The Secret to Freedom and Success  Napoleon Hill  Dan John Miller   \n",
              "\n",
              "        Audio Runtime        Audiobook_Type  \\\n",
              "0  20 hrs and 46 mins  Unabridged Audiobook   \n",
              "1   5 hrs and 48 mins  Unabridged Audiobook   \n",
              "2  11 hrs and 50 mins  Unabridged Audiobook   \n",
              "3   7 hrs and 33 mins  Unabridged Audiobook   \n",
              "4   5 hrs and 51 mins  Unabridged Audiobook   \n",
              "\n",
              "                                        Categories Rating  \\\n",
              "0                     Mystery, Thriller & Suspense    4.6   \n",
              "1  Relationships, Parenting & Personal Development    4.8   \n",
              "2                     Mystery, Thriller & Suspense    4.6   \n",
              "3  Relationships, Parenting & Personal Development    4.8   \n",
              "4                               Business & Careers    4.8   \n",
              "\n",
              "   Total No. of Ratings   Price        date        Date  conversion_rate  \n",
              "0               25863.0  35.00   2021-05-01  2021-05-01            31.14  \n",
              "1                2441.0  19.59   2021-05-01  2021-05-01            31.14  \n",
              "2               26781.0  31.50   2021-05-01  2021-05-01            31.14  \n",
              "3               11459.0  19.95   2021-05-01  2021-05-01            31.14  \n",
              "4                9305.0  17.49   2021-05-01  2021-05-01            31.14  "
            ],
            "text/html": [
              "\n",
              "\n",
              "  <div id=\"df-0371e6e1-3dca-4149-826e-9242cca723a5\">\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>timestamp</th>\n",
              "      <th>user_id</th>\n",
              "      <th>book_id</th>\n",
              "      <th>country</th>\n",
              "      <th>Book Title</th>\n",
              "      <th>Book Subtitle</th>\n",
              "      <th>Book Author</th>\n",
              "      <th>Book Narrator</th>\n",
              "      <th>Audio Runtime</th>\n",
              "      <th>Audiobook_Type</th>\n",
              "      <th>Categories</th>\n",
              "      <th>Rating</th>\n",
              "      <th>Total No. of Ratings</th>\n",
              "      <th>Price</th>\n",
              "      <th>date</th>\n",
              "      <th>Date</th>\n",
              "      <th>conversion_rate</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>2021-05-01 00:00:01</td>\n",
              "      <td>ad8eca41</td>\n",
              "      <td>1584</td>\n",
              "      <td>Portugal</td>\n",
              "      <td>Sycamore Row</td>\n",
              "      <td>NaN</td>\n",
              "      <td>John Grisham</td>\n",
              "      <td>Michael Beck</td>\n",
              "      <td>20 hrs and 46 mins</td>\n",
              "      <td>Unabridged Audiobook</td>\n",
              "      <td>Mystery, Thriller &amp; Suspense</td>\n",
              "      <td>4.6</td>\n",
              "      <td>25863.0</td>\n",
              "      <td>35.00</td>\n",
              "      <td>2021-05-01</td>\n",
              "      <td>2021-05-01</td>\n",
              "      <td>31.14</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2021-05-01 00:00:03</td>\n",
              "      <td>561b26c1</td>\n",
              "      <td>829</td>\n",
              "      <td>United States of America</td>\n",
              "      <td>The 5 Love Languages of Children</td>\n",
              "      <td>The Secret to Loving Children Effectively</td>\n",
              "      <td>Gary Chapman</td>\n",
              "      <td>Chris Fabry</td>\n",
              "      <td>5 hrs and 48 mins</td>\n",
              "      <td>Unabridged Audiobook</td>\n",
              "      <td>Relationships, Parenting &amp; Personal Development</td>\n",
              "      <td>4.8</td>\n",
              "      <td>2441.0</td>\n",
              "      <td>19.59</td>\n",
              "      <td>2021-05-01</td>\n",
              "      <td>2021-05-01</td>\n",
              "      <td>31.14</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>2021-05-01 00:00:04</td>\n",
              "      <td>81f149e5</td>\n",
              "      <td>1391</td>\n",
              "      <td>Japan</td>\n",
              "      <td>The Guardians</td>\n",
              "      <td>A Novel</td>\n",
              "      <td>John Grisham</td>\n",
              "      <td>Michael Beck</td>\n",
              "      <td>11 hrs and 50 mins</td>\n",
              "      <td>Unabridged Audiobook</td>\n",
              "      <td>Mystery, Thriller &amp; Suspense</td>\n",
              "      <td>4.6</td>\n",
              "      <td>26781.0</td>\n",
              "      <td>31.50</td>\n",
              "      <td>2021-05-01</td>\n",
              "      <td>2021-05-01</td>\n",
              "      <td>31.14</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>2021-05-01 00:00:07</td>\n",
              "      <td>4f218413</td>\n",
              "      <td>1586</td>\n",
              "      <td>Taiwan</td>\n",
              "      <td>How to Be a 3% Man</td>\n",
              "      <td>NaN</td>\n",
              "      <td>Corey Wayne</td>\n",
              "      <td>Corey Wayne</td>\n",
              "      <td>7 hrs and 33 mins</td>\n",
              "      <td>Unabridged Audiobook</td>\n",
              "      <td>Relationships, Parenting &amp; Personal Development</td>\n",
              "      <td>4.8</td>\n",
              "      <td>11459.0</td>\n",
              "      <td>19.95</td>\n",
              "      <td>2021-05-01</td>\n",
              "      <td>2021-05-01</td>\n",
              "      <td>31.14</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>2021-05-01 00:00:18</td>\n",
              "      <td>a4066781</td>\n",
              "      <td>300</td>\n",
              "      <td>United States of America</td>\n",
              "      <td>Napoleon Hill's Outwitting the Devil</td>\n",
              "      <td>The Secret to Freedom and Success</td>\n",
              "      <td>Napoleon Hill</td>\n",
              "      <td>Dan John Miller</td>\n",
              "      <td>5 hrs and 51 mins</td>\n",
              "      <td>Unabridged Audiobook</td>\n",
              "      <td>Business &amp; Careers</td>\n",
              "      <td>4.8</td>\n",
              "      <td>9305.0</td>\n",
              "      <td>17.49</td>\n",
              "      <td>2021-05-01</td>\n",
              "      <td>2021-05-01</td>\n",
              "      <td>31.14</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-0371e6e1-3dca-4149-826e-9242cca723a5')\"\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",
              "\n",
              "\n",
              "    <div id=\"df-247e7041-af6a-4775-9d37-adc3b30b8b73\">\n",
              "      <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-247e7041-af6a-4775-9d37-adc3b30b8b73')\"\n",
              "              title=\"Suggest charts.\"\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",
              "    <g>\n",
              "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
              "    </g>\n",
              "</svg>\n",
              "      </button>\n",
              "    </div>\n",
              "\n",
              "<style>\n",
              "  .colab-df-quickchart {\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-quickchart: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-quickchart {\n",
              "    background-color: #3B4455;\n",
              "    fill: #D2E3FC;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart: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",
              "      async function quickchart(key) {\n",
              "        const containerElement = document.querySelector('#' + key);\n",
              "        const charts = await google.colab.kernel.invokeFunction(\n",
              "            'suggestCharts', [key], {});\n",
              "      }\n",
              "    </script>\n",
              "\n",
              "      <script>\n",
              "\n",
              "function displayQuickchartButton(domScope) {\n",
              "  let quickchartButtonEl =\n",
              "    domScope.querySelector('#df-247e7041-af6a-4775-9d37-adc3b30b8b73 button.colab-df-quickchart');\n",
              "  quickchartButtonEl.style.display =\n",
              "    google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "}\n",
              "\n",
              "        displayQuickchartButton(document);\n",
              "      </script>\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-0371e6e1-3dca-4149-826e-9242cca723a5 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-0371e6e1-3dca-4149-826e-9242cca723a5');\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": 42
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "##Coverted to float\n",
        "\n",
        "converted the `Price` column to float data type"
      ],
      "metadata": {
        "id": "SZWGJ2xxd8UT"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "final_df[\"Price\"] = final_df[\"Price\"].astype(float)\n",
        "final_df.dtypes"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "08a8DXgcdiJz",
        "outputId": "27569995-7470-41b3-efcb-b93bf7434768"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "timestamp                object\n",
              "user_id                  object\n",
              "book_id                   int64\n",
              "country                  object\n",
              "Book Title               object\n",
              "Book Subtitle            object\n",
              "Book Author              object\n",
              "Book Narrator            object\n",
              "Audio Runtime            object\n",
              "Audiobook_Type           object\n",
              "Categories               object\n",
              "Rating                   object\n",
              "Total No. of Ratings    float64\n",
              "Price                   float64\n",
              "date                     object\n",
              "Date                     object\n",
              "conversion_rate         float64\n",
              "dtype: object"
            ]
          },
          "metadata": {},
          "execution_count": 43
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "##Book's price in Thai Baht\n",
        "\n",
        "Added the `THBPrice` column to the final_df dataframe"
      ],
      "metadata": {
        "id": "Yql2M34LeGY0"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "final_df[\"THBprice\"] = final_df[\"Price\"] * final_df[\"conversion_rate\"]\n",
        "final_df.head()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 565
        },
        "id": "o8oo0I-id1Oo",
        "outputId": "417ee826-6c3d-4f1c-e496-b0a6b2e3633d"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "             timestamp   user_id  book_id                   country  \\\n",
              "0  2021-05-01 00:00:01  ad8eca41     1584                  Portugal   \n",
              "1  2021-05-01 00:00:03  561b26c1      829  United States of America   \n",
              "2  2021-05-01 00:00:04  81f149e5     1391                     Japan   \n",
              "3  2021-05-01 00:00:07  4f218413     1586                    Taiwan   \n",
              "4  2021-05-01 00:00:18  a4066781      300  United States of America   \n",
              "\n",
              "                             Book Title  \\\n",
              "0                          Sycamore Row   \n",
              "1      The 5 Love Languages of Children   \n",
              "2                         The Guardians   \n",
              "3                    How to Be a 3% Man   \n",
              "4  Napoleon Hill's Outwitting the Devil   \n",
              "\n",
              "                               Book Subtitle    Book Author    Book Narrator  \\\n",
              "0                                        NaN   John Grisham     Michael Beck   \n",
              "1  The Secret to Loving Children Effectively   Gary Chapman      Chris Fabry   \n",
              "2                                    A Novel   John Grisham     Michael Beck   \n",
              "3                                        NaN    Corey Wayne      Corey Wayne   \n",
              "4          The Secret to Freedom and Success  Napoleon Hill  Dan John Miller   \n",
              "\n",
              "        Audio Runtime        Audiobook_Type  \\\n",
              "0  20 hrs and 46 mins  Unabridged Audiobook   \n",
              "1   5 hrs and 48 mins  Unabridged Audiobook   \n",
              "2  11 hrs and 50 mins  Unabridged Audiobook   \n",
              "3   7 hrs and 33 mins  Unabridged Audiobook   \n",
              "4   5 hrs and 51 mins  Unabridged Audiobook   \n",
              "\n",
              "                                        Categories Rating  \\\n",
              "0                     Mystery, Thriller & Suspense    4.6   \n",
              "1  Relationships, Parenting & Personal Development    4.8   \n",
              "2                     Mystery, Thriller & Suspense    4.6   \n",
              "3  Relationships, Parenting & Personal Development    4.8   \n",
              "4                               Business & Careers    4.8   \n",
              "\n",
              "   Total No. of Ratings  Price        date        Date  conversion_rate  \\\n",
              "0               25863.0  35.00  2021-05-01  2021-05-01            31.14   \n",
              "1                2441.0  19.59  2021-05-01  2021-05-01            31.14   \n",
              "2               26781.0  31.50  2021-05-01  2021-05-01            31.14   \n",
              "3               11459.0  19.95  2021-05-01  2021-05-01            31.14   \n",
              "4                9305.0  17.49  2021-05-01  2021-05-01            31.14   \n",
              "\n",
              "    THBprice  \n",
              "0  1089.9000  \n",
              "1   610.0326  \n",
              "2   980.9100  \n",
              "3   621.2430  \n",
              "4   544.6386  "
            ],
            "text/html": [
              "\n",
              "\n",
              "  <div id=\"df-37b19fae-dd9e-4c1e-b9fc-ddb1a5436ce4\">\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>timestamp</th>\n",
              "      <th>user_id</th>\n",
              "      <th>book_id</th>\n",
              "      <th>country</th>\n",
              "      <th>Book Title</th>\n",
              "      <th>Book Subtitle</th>\n",
              "      <th>Book Author</th>\n",
              "      <th>Book Narrator</th>\n",
              "      <th>Audio Runtime</th>\n",
              "      <th>Audiobook_Type</th>\n",
              "      <th>Categories</th>\n",
              "      <th>Rating</th>\n",
              "      <th>Total No. of Ratings</th>\n",
              "      <th>Price</th>\n",
              "      <th>date</th>\n",
              "      <th>Date</th>\n",
              "      <th>conversion_rate</th>\n",
              "      <th>THBprice</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>2021-05-01 00:00:01</td>\n",
              "      <td>ad8eca41</td>\n",
              "      <td>1584</td>\n",
              "      <td>Portugal</td>\n",
              "      <td>Sycamore Row</td>\n",
              "      <td>NaN</td>\n",
              "      <td>John Grisham</td>\n",
              "      <td>Michael Beck</td>\n",
              "      <td>20 hrs and 46 mins</td>\n",
              "      <td>Unabridged Audiobook</td>\n",
              "      <td>Mystery, Thriller &amp; Suspense</td>\n",
              "      <td>4.6</td>\n",
              "      <td>25863.0</td>\n",
              "      <td>35.00</td>\n",
              "      <td>2021-05-01</td>\n",
              "      <td>2021-05-01</td>\n",
              "      <td>31.14</td>\n",
              "      <td>1089.9000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2021-05-01 00:00:03</td>\n",
              "      <td>561b26c1</td>\n",
              "      <td>829</td>\n",
              "      <td>United States of America</td>\n",
              "      <td>The 5 Love Languages of Children</td>\n",
              "      <td>The Secret to Loving Children Effectively</td>\n",
              "      <td>Gary Chapman</td>\n",
              "      <td>Chris Fabry</td>\n",
              "      <td>5 hrs and 48 mins</td>\n",
              "      <td>Unabridged Audiobook</td>\n",
              "      <td>Relationships, Parenting &amp; Personal Development</td>\n",
              "      <td>4.8</td>\n",
              "      <td>2441.0</td>\n",
              "      <td>19.59</td>\n",
              "      <td>2021-05-01</td>\n",
              "      <td>2021-05-01</td>\n",
              "      <td>31.14</td>\n",
              "      <td>610.0326</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>2021-05-01 00:00:04</td>\n",
              "      <td>81f149e5</td>\n",
              "      <td>1391</td>\n",
              "      <td>Japan</td>\n",
              "      <td>The Guardians</td>\n",
              "      <td>A Novel</td>\n",
              "      <td>John Grisham</td>\n",
              "      <td>Michael Beck</td>\n",
              "      <td>11 hrs and 50 mins</td>\n",
              "      <td>Unabridged Audiobook</td>\n",
              "      <td>Mystery, Thriller &amp; Suspense</td>\n",
              "      <td>4.6</td>\n",
              "      <td>26781.0</td>\n",
              "      <td>31.50</td>\n",
              "      <td>2021-05-01</td>\n",
              "      <td>2021-05-01</td>\n",
              "      <td>31.14</td>\n",
              "      <td>980.9100</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>2021-05-01 00:00:07</td>\n",
              "      <td>4f218413</td>\n",
              "      <td>1586</td>\n",
              "      <td>Taiwan</td>\n",
              "      <td>How to Be a 3% Man</td>\n",
              "      <td>NaN</td>\n",
              "      <td>Corey Wayne</td>\n",
              "      <td>Corey Wayne</td>\n",
              "      <td>7 hrs and 33 mins</td>\n",
              "      <td>Unabridged Audiobook</td>\n",
              "      <td>Relationships, Parenting &amp; Personal Development</td>\n",
              "      <td>4.8</td>\n",
              "      <td>11459.0</td>\n",
              "      <td>19.95</td>\n",
              "      <td>2021-05-01</td>\n",
              "      <td>2021-05-01</td>\n",
              "      <td>31.14</td>\n",
              "      <td>621.2430</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>2021-05-01 00:00:18</td>\n",
              "      <td>a4066781</td>\n",
              "      <td>300</td>\n",
              "      <td>United States of America</td>\n",
              "      <td>Napoleon Hill's Outwitting the Devil</td>\n",
              "      <td>The Secret to Freedom and Success</td>\n",
              "      <td>Napoleon Hill</td>\n",
              "      <td>Dan John Miller</td>\n",
              "      <td>5 hrs and 51 mins</td>\n",
              "      <td>Unabridged Audiobook</td>\n",
              "      <td>Business &amp; Careers</td>\n",
              "      <td>4.8</td>\n",
              "      <td>9305.0</td>\n",
              "      <td>17.49</td>\n",
              "      <td>2021-05-01</td>\n",
              "      <td>2021-05-01</td>\n",
              "      <td>31.14</td>\n",
              "      <td>544.6386</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-37b19fae-dd9e-4c1e-b9fc-ddb1a5436ce4')\"\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",
              "\n",
              "\n",
              "    <div id=\"df-a1117352-024e-4919-a5e2-07eb9467eab2\">\n",
              "      <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-a1117352-024e-4919-a5e2-07eb9467eab2')\"\n",
              "              title=\"Suggest charts.\"\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",
              "    <g>\n",
              "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
              "    </g>\n",
              "</svg>\n",
              "      </button>\n",
              "    </div>\n",
              "\n",
              "<style>\n",
              "  .colab-df-quickchart {\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-quickchart: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-quickchart {\n",
              "    background-color: #3B4455;\n",
              "    fill: #D2E3FC;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart: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",
              "      async function quickchart(key) {\n",
              "        const containerElement = document.querySelector('#' + key);\n",
              "        const charts = await google.colab.kernel.invokeFunction(\n",
              "            'suggestCharts', [key], {});\n",
              "      }\n",
              "    </script>\n",
              "\n",
              "      <script>\n",
              "\n",
              "function displayQuickchartButton(domScope) {\n",
              "  let quickchartButtonEl =\n",
              "    domScope.querySelector('#df-a1117352-024e-4919-a5e2-07eb9467eab2 button.colab-df-quickchart');\n",
              "  quickchartButtonEl.style.display =\n",
              "    google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "}\n",
              "\n",
              "        displayQuickchartButton(document);\n",
              "      </script>\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-37b19fae-dd9e-4c1e-b9fc-ddb1a5436ce4 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-37b19fae-dd9e-4c1e-b9fc-ddb1a5436ce4');\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": 44
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "delete the `date` column."
      ],
      "metadata": {
        "id": "4FFQC9jtf8EP"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "final_df = final_df.drop(\"date\", axis=1)"
      ],
      "metadata": {
        "id": "2oyUA9lFfd_J"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "SAVE file to CSV"
      ],
      "metadata": {
        "id": "zVu7dbq5gsIY"
      }
    },
    {
      "cell_type": "code",
      "source": [],
      "metadata": {
        "id": "XaiBV7RsgWnL"
      },
      "execution_count": null,
      "outputs": []
    }
  ]
}