Satish_Week_2_Project_v2_PFDI.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/nandi-github/corise-datascience/blob/main/Satish_Week_2_Project_v2_PFDI.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "a2040e3d-bfb8-40b8-8861-4485db413cf7",
      "metadata": {
        "id": "a2040e3d-bfb8-40b8-8861-4485db413cf7"
      },
      "source": [
        "> 1. DUPLICATE THIS COLAB DOCUMENT TO START WORKING ON IT: On the top-left corner of this page, go to File > Save a copy to drive.\n",
        "> 2. SHARE SETTINGS: In the new notebook, set the sharing settings to \"Anyone with the link\" by clicking \"Share\" on the top-right corner.\n",
        "\n",
        "<center>\n",
        "  <img src=https://www.freevector.com/uploads/vector/preview/31087/07Januari2021-06_generated.jpg width=\"500\" align=\"center\" />\n",
        "</center>\n",
        "<br/>\n",
        "\n",
        "# Week 2: Discover the Airbnb Dataset (and Filter It!)\n",
        "\n",
        "Hi! 👋👋👋 Are you excited to start the second week's project for Python for Data Science? \n",
        "\n",
        "This week's lecture and material on CoRise showed you how to clean a DataFrame and merge one DataFrame into another. As you might have noticed, our dataset this week has way more columns than our dataset from Week 1. This is much more true to real life. It's messy, it's bloated, every time is unique, and above all, it's MUCH MORE interesting!\n",
        "\n",
        "For this project, we saved an older version of the \"clean\" **Listings DataFrame** so that you can apply all the steps we performed on the **Calendar DataFrame** directly to this older version and reproduce the methods you learned on slightly different data. Let's get started 💪💪!"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Downloading the Dataset\n",
        "\n",
        "You'll need to download some prerequisite Python packages in order to run all the code below. Let's install them!"
      ],
      "metadata": {
        "id": "rISRrL2X93Yx"
      },
      "id": "rISRrL2X93Yx"
    },
    {
      "cell_type": "code",
      "source": [
        "%%capture\n",
        "!pip install numpy pandas streamlit gdown pyarrow"
      ],
      "metadata": {
        "id": "JspZZKEj9ytH"
      },
      "id": "JspZZKEj9ytH",
      "execution_count": 2,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "id": "0c21b1ad-25c8-4a99-a4e0-764d568f711b",
      "metadata": {
        "id": "0c21b1ad-25c8-4a99-a4e0-764d568f711b"
      },
      "source": [
        "We will download the datasets from Google Drive just like we did last week, but this time the datasets are in [Pickle](https://pythonnumericalmethods.berkeley.edu/notebooks/chapter11.03-Pickle-Files.html) and [Parquet](https://arrow.apache.org/docs/python/parquet.html) format. "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 3,
      "id": "383cc4c4-c43c-4e77-bfce-8b4f1ab29bb4",
      "metadata": {
        "id": "383cc4c4-c43c-4e77-bfce-8b4f1ab29bb4",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 174
        },
        "outputId": "9c38ca9d-240b-49c3-8687-480e48ae5b8f"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "Downloading...\n",
            "From: https://drive.google.com/uc?id=1m185vTdh-u7_A2ZElBvUD4SCO6oETll2\n",
            "To: /content/listings_project.pkl\n",
            "100%|██████████| 1.42M/1.42M [00:00<00:00, 43.1MB/s]\n",
            "Downloading...\n",
            "From: https://drive.google.com/uc?id=1w41V1oWHJrBdaNJJQ4oxVBuml5CO7MQX\n",
            "To: /content/calendar_project.parquet\n",
            "100%|██████████| 1.23M/1.23M [00:00<00:00, 92.3MB/s]\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "'calendar_project.parquet'"
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "string"
            }
          },
          "metadata": {},
          "execution_count": 3
        }
      ],
      "source": [
        "import os\n",
        "import shutil\n",
        "\n",
        "import gdown\n",
        "import numpy as np\n",
        "import pandas as pd\n",
        "\n",
        "# Download files from Google Drive\n",
        "# Based on data from: http://insideairbnb.com/get-the-data/\n",
        "file_id_1 = \"1m185vTdh-u7_A2ZElBvUD4SCO6oETll2\"\n",
        "file_id_2 = \"1w41V1oWHJrBdaNJJQ4oxVBuml5CO7MQX\"\n",
        "downloaded_file_1 = \"listings_project.pkl\"\n",
        "downloaded_file_2 = \"calendar_project.parquet\"\n",
        "# Download the files from Google Drive\n",
        "gdown.download(id=file_id_1, output=downloaded_file_1)\n",
        "gdown.download(id=file_id_2, output=downloaded_file_2)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 4,
      "id": "70509240-553f-4c0a-9ba8-54559efc606d",
      "metadata": {
        "id": "70509240-553f-4c0a-9ba8-54559efc606d"
      },
      "outputs": [],
      "source": [
        "# Show all columns (instead of cascading columns in the middle)\n",
        "pd.set_option(\"display.max_columns\", None)\n",
        "# Don't show numbers in scientific notation\n",
        "pd.set_option(\"display.float_format\", \"{:.2f}\".format)"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Preprocessing the Dataset\n",
        "Please load the downloaded files as DataFrames (dfs). The method for loading these datasets is the same as what we did on the CoRise platform."
      ],
      "metadata": {
        "id": "RNofYa-GU8D2"
      },
      "id": "RNofYa-GU8D2"
    },
    {
      "cell_type": "markdown",
      "id": "4511c023-4bc6-4399-b5bf-c4977fdf1ea0",
      "metadata": {
        "id": "4511c023-4bc6-4399-b5bf-c4977fdf1ea0"
      },
      "source": [
        "#### Task 1: Read Pickle and Parquet\n",
        "\n",
        "[*\\[Related section on CoRise\\]*](https://corise.com/course/python-for-data-science/v2/module/details-dataset-410sxl#corise_cl9wjv699001s3b6p875ii4j1)\n",
        "\n",
        "Read the Python Pickle and PyArrow Parquet files we've just downloaded as `df_list` and `df_cal`."
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "df_list = pd.read_pickle(\"listings_project.pkl\") # YOUR CODE HERE\n",
        "df_cal = pd.read_parquet(\"calendar_project.parquet\", engine=\"pyarrow\")# YOUR CODE HERE\n",
        "# print(df_list.head())\n",
        "# print(df_cal.head())"
      ],
      "metadata": {
        "id": "cnFmfiydVawy"
      },
      "id": "cnFmfiydVawy",
      "execution_count": 5,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "<details>\n",
        "<summary>Show Solution</summary>\n",
        "\n",
        "```python\n",
        "df_list = pd.read_pickle(\"listings_project.pkl\")\n",
        "df_cal = pd.read_parquet(\"calendar_project.parquet\", engine=\"pyarrow\")\n",
        "```\n",
        "\n",
        "</details>"
      ],
      "metadata": {
        "id": "u6Q-4irSHWmN"
      },
      "id": "u6Q-4irSHWmN"
    },
    {
      "cell_type": "markdown",
      "id": "3f375704-9648-49cc-a6fc-a7f7208b476f",
      "metadata": {
        "tags": [],
        "id": "3f375704-9648-49cc-a6fc-a7f7208b476f"
      },
      "source": [
        "Now instead of cleaning the **Calendar DataFrame**, you are going to clean the **Listings DataFrame**. You will use the same steps we used to clean the Calendar data on the CoRise platform this week. Let's first get an overview of the columns that are in this particular DataFrame 🧐."
      ]
    },
    {
      "cell_type": "markdown",
      "id": "38b21f27-24ea-4622-8c2a-c4bb065b5d4e",
      "metadata": {
        "id": "38b21f27-24ea-4622-8c2a-c4bb065b5d4e"
      },
      "source": [
        "#### Task 2: Print column names, types, and non-null values \n",
        "\n",
        "[*\\[Related section on CoRise\\]*](https://corise.com/course/python-for-data-science/v2/module/clean-inspect-6wg1lh#corise_cl9wmgzix001k3b6puh0swwer)\n",
        "\n",
        "Let's try and get an overview of the **Listings DataFrame**, called `df_list`. This should show us some details about the columns in the DataFrame, like the column names, their data types, and the number of non-null values."
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "df_list.info(verbose=True, show_counts=True) # YOUR CODE HERE"
      ],
      "metadata": {
        "id": "wOKwdo8gH9Th",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "87a7a6bb-43f8-41d0-8b88-6acad6966390"
      },
      "id": "wOKwdo8gH9Th",
      "execution_count": 6,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "<class 'pandas.core.frame.DataFrame'>\n",
            "Int64Index: 6165 entries, 0 to 6172\n",
            "Data columns (total 34 columns):\n",
            " #   Column                                Non-Null Count  Dtype  \n",
            "---  ------                                --------------  -----  \n",
            " 0   id                                    6165 non-null   int64  \n",
            " 1   host_acceptance_rate                  5365 non-null   float64\n",
            " 2   host_is_superhost                     6165 non-null   object \n",
            " 3   host_listings_count                   6165 non-null   int64  \n",
            " 4   host_total_listings_count             6165 non-null   int64  \n",
            " 5   neighbourhood_cleansed                6165 non-null   object \n",
            " 6   latitude                              6165 non-null   float64\n",
            " 7   longitude                             6165 non-null   float64\n",
            " 8   room_type                             6165 non-null   object \n",
            " 9   accommodates                          6165 non-null   int64  \n",
            " 10  bedrooms                              5859 non-null   float64\n",
            " 11  beds                                  6082 non-null   float64\n",
            " 12  amenities                             6165 non-null   int64  \n",
            " 13  price                                 6165 non-null   object \n",
            " 14  minimum_nights                        6165 non-null   int64  \n",
            " 15  maximum_nights                        6165 non-null   int64  \n",
            " 16  has_availability                      6165 non-null   object \n",
            " 17  availability_30                       6165 non-null   int64  \n",
            " 18  availability_60                       6165 non-null   int64  \n",
            " 19  availability_90                       6165 non-null   int64  \n",
            " 20  availability_365                      6165 non-null   int64  \n",
            " 21  number_of_reviews                     6165 non-null   int64  \n",
            " 22  number_of_reviews_ltm                 6165 non-null   int64  \n",
            " 23  number_of_reviews_l30d                6165 non-null   int64  \n",
            " 24  review_scores_rating                  5581 non-null   float64\n",
            " 25  instant_bookable                      6165 non-null   object \n",
            " 26  reviews_per_month                     5581 non-null   float64\n",
            " 27  price_in_euros                        0 non-null      object \n",
            " 28  price_per_person                      6165 non-null   object \n",
            " 29  minimum_price                         6165 non-null   object \n",
            " 30  discount_per_5_days_booked            6165 non-null   object \n",
            " 31  discount_per_10_days_booked           6165 non-null   object \n",
            " 32  discount_per_30_and_more_days_booked  6165 non-null   object \n",
            " 33  service_cost                          6165 non-null   object \n",
            "dtypes: float64(7), int64(14), object(13)\n",
            "memory usage: 1.6+ MB\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "<details>\n",
        "<summary>Show Solution</summary>\n",
        "\n",
        "```python\n",
        "df_list.info(verbose=True, show_counts=True)\n",
        "```\n",
        "\n",
        "</details>"
      ],
      "metadata": {
        "id": "lqgzxUOiGuyB"
      },
      "id": "lqgzxUOiGuyB"
    },
    {
      "cell_type": "markdown",
      "source": [
        "This info printout provides a good overview of which columns we need to investigate further. We saw on CoRise this week that columns with the dtype **object** and sometimes **float** require inspection and cleaning.\n",
        "\n",
        "Let's start first with the `discount_per_...` columns."
      ],
      "metadata": {
        "id": "4ml13_W2eKvm"
      },
      "id": "4ml13_W2eKvm"
    },
    {
      "cell_type": "code",
      "source": [
        "df_list.discount_per_5_days_booked.head(5)"
      ],
      "metadata": {
        "id": "e-FGn3l8Nd3u",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "60ef0226-9354-42bf-df68-087e7d0e7350"
      },
      "id": "e-FGn3l8Nd3u",
      "execution_count": 7,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "0    5%\n",
              "1    5%\n",
              "2    7%\n",
              "3    6%\n",
              "4    9%\n",
              "Name: discount_per_5_days_booked, dtype: object"
            ]
          },
          "metadata": {},
          "execution_count": 7
        }
      ]
    },
    {
      "cell_type": "markdown",
      "id": "0ec3d60a-f71b-4c00-9953-2aaf200ff149",
      "metadata": {
        "id": "0ec3d60a-f71b-4c00-9953-2aaf200ff149"
      },
      "source": [
        "#### Task 3: Remove, convert, and format\n",
        "\n",
        "[*\\[Related section on CoRise\\]*](https://corise.com/course/python-for-data-science/v2/module/clean-inspect-6wg1lh#corise_cl9wmqqgx001x3b6p54g1ka2y)\n",
        "\n",
        "Perform this four-step process to change each of the three `discount_per_...` columns into their proper format:\n",
        "1. Remove non-numeric characters, like the percent symbol, so you can perform mathematical calculations on the column\n",
        "1. Change the column into a `float` data type in order to convert the data into a ratio\n",
        "1. Multiply the whole column by 0.01 so you end up with a probability ratio instead of a percentage\n",
        "1. Overwrite the old `discount_per_...` column with this new column\n",
        "\n",
        "Perform these four steps for all thee columns.\n",
        "\n",
        "***Please note that running this code block more than once might cause an error. This is because you are re-assigning your columns with this code, and if you run the code again, the variable/column you are referring to has already been changed to its preferred state.***\n"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "df_list[\"discount_per_5_days_booked\"] = df_list[\"discount_per_5_days_booked\"] = (\n",
        "    df_list[\"discount_per_5_days_booked\"]\n",
        "    .str.replace(\"%\", \"\", regex=True)\n",
        "    .astype(\"float\")\n",
        "    * 0.01) # YOUR CODE HERE\n",
        "\n",
        "df_list[\"discount_per_10_days_booked\"] = (\n",
        "    df_list[\"discount_per_10_days_booked\"]\n",
        "    .str.replace(\"%\", \"\", regex=True)\n",
        "    .astype(\"float\")\n",
        "    * 0.01 )# YOUR CODE HERE\n",
        "\n",
        "df_list[\"discount_per_30_and_more_days_booked\"] = (\n",
        "    df_list[\"discount_per_30_and_more_days_booked\"]\n",
        "    .str.replace(\"%\", \"\", regex=True)\n",
        "    .astype(\"float\")\n",
        "    * 0.01) # YOUR CODE HERE\n",
        "\n",
        "df_list.discount_per_5_days_booked.head(5)"
      ],
      "metadata": {
        "id": "OdlvrgxDf-7j",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "227192d8-0a80-4fd9-d79d-537b94e979cb"
      },
      "id": "OdlvrgxDf-7j",
      "execution_count": 8,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "0   0.05\n",
              "1   0.05\n",
              "2   0.07\n",
              "3   0.06\n",
              "4   0.09\n",
              "Name: discount_per_5_days_booked, dtype: float64"
            ]
          },
          "metadata": {},
          "execution_count": 8
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "<details>\n",
        "  <summary>Show Expected Output</summary>\n",
        "  \n",
        "```\n",
        "0   0.05\n",
        "1   0.05\n",
        "2   0.07\n",
        "3   0.06\n",
        "4   0.09\n",
        "Name: discount_per_5_days_booked, dtype: float64\n",
        "```\n",
        "\n",
        "<details>\n",
        "<summary>Show Solution</summary>\n",
        "\n",
        "```python\n",
        "df_list[\"discount_per_5_days_booked\"] = (\n",
        "    df_list[\"discount_per_5_days_booked\"]\n",
        "    .str.replace(\"%\", \"\", regex=True)\n",
        "    .astype(\"float\")\n",
        "    * 0.01\n",
        ")\n",
        "df_list[\"discount_per_10_days_booked\"] = (\n",
        "    df_list[\"discount_per_10_days_booked\"]\n",
        "    .str.replace(\"%\", \"\", regex=True)\n",
        "    .astype(\"float\")\n",
        "    * 0.01\n",
        ")\n",
        "df_list[\"discount_per_30_and_more_days_booked\"] = (\n",
        "    df_list[\"discount_per_30_and_more_days_booked\"]\n",
        "    .str.replace(\"%\", \"\", regex=True)\n",
        "    .astype(\"float\")\n",
        "    * 0.01\n",
        ")\n",
        "```\n",
        "</details>"
      ],
      "metadata": {
        "id": "0WtFWoWegFM4"
      },
      "id": "0WtFWoWegFM4"
    },
    {
      "cell_type": "markdown",
      "source": [
        "This data looks great for performing calculations!\n",
        "\n",
        "Next, the columns `host_is_superhost`, `instant_bookable`, and `has_availability` are all boolean columns in the sense that their data represents true and false values, but currently are recognized as objects."
      ],
      "metadata": {
        "id": "q5qtc6L0gYQB"
      },
      "id": "q5qtc6L0gYQB"
    },
    {
      "cell_type": "code",
      "source": [
        "df_list[[\"host_is_superhost\", \"instant_bookable\", \"has_availability\"]].head(5)"
      ],
      "metadata": {
        "id": "Zgg-sLPhgtIc",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 206
        },
        "outputId": "1bbb0afc-8cf0-467e-c541-309cc8b4925c"
      },
      "id": "Zgg-sLPhgtIc",
      "execution_count": 9,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "  host_is_superhost instant_bookable has_availability\n",
              "0                 f                t                t\n",
              "1                 t                f                t\n",
              "2                 f                f                t\n",
              "3                 f                f                t\n",
              "4                 t                f                t"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-e4430308-2b07-480d-9d78-b6419d1a9770\">\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>host_is_superhost</th>\n",
              "      <th>instant_bookable</th>\n",
              "      <th>has_availability</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>f</td>\n",
              "      <td>t</td>\n",
              "      <td>t</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>t</td>\n",
              "      <td>f</td>\n",
              "      <td>t</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>f</td>\n",
              "      <td>f</td>\n",
              "      <td>t</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>f</td>\n",
              "      <td>f</td>\n",
              "      <td>t</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>t</td>\n",
              "      <td>f</td>\n",
              "      <td>t</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-e4430308-2b07-480d-9d78-b6419d1a9770')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-e4430308-2b07-480d-9d78-b6419d1a9770 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-e4430308-2b07-480d-9d78-b6419d1a9770');\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": 9
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "This is because the letters in these columns (**t** and **f**) are written as **strings** and not as **boolean** data types. This means we need to replace our string values with the boolean equivalent dtype."
      ],
      "metadata": {
        "id": "ThpOkbPKg2SA"
      },
      "id": "ThpOkbPKg2SA"
    },
    {
      "cell_type": "markdown",
      "id": "c3df5f1d-f758-4ab7-9cd2-fba8b49f6b22",
      "metadata": {
        "id": "c3df5f1d-f758-4ab7-9cd2-fba8b49f6b22"
      },
      "source": [
        "#### Task 4: Booleans!\n",
        "\n",
        "[*\\[Related section on CoRise\\]*](https://corise.com/course/python-for-data-science/v2/module/clean-inspect-6wg1lh#corise_cl9wmkhbi001r3b6pb2o5kfxd)\n",
        "\n",
        "Change the columns `host_is_superhost`, `instant_bookable`, and `has_availability` into a boolean data type for better data processing:\n",
        "\n",
        "1. Replace `f` and `t` with `False` and `True`\n",
        "1. Set the column as type `bool`\n",
        "1. Overwrite the old columns with the new values\n"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "df_list[\"host_is_superhost\"] = df_list[\"host_is_superhost\"].replace({\"f\": False, \"t\": True}).astype(\"bool\") # YOUR CODE HERE\n",
        "df_list[\"instant_bookable\"] = df_list[\"instant_bookable\"].replace({\"f\": False, \"t\": True}).astype(\"bool\") # YOUR CODE HERE\n",
        "df_list[\"has_availability\"] = df_list[\"has_availability\"].replace({\"f\": False, \"t\": True}).astype(\"bool\") # YOUR CODE HERE\n",
        "\n",
        "df_list[[\"host_is_superhost\", \"instant_bookable\", \"has_availability\"]].head(5)"
      ],
      "metadata": {
        "id": "eSdr3z8Ug-mB",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 206
        },
        "outputId": "930583a8-07d7-4ea2-80cf-27767672fe9f"
      },
      "id": "eSdr3z8Ug-mB",
      "execution_count": 10,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "   host_is_superhost  instant_bookable  has_availability\n",
              "0              False              True              True\n",
              "1               True             False              True\n",
              "2              False             False              True\n",
              "3              False             False              True\n",
              "4               True             False              True"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-cb2b087e-bd0e-414e-a03c-8fefd9bd3a91\">\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>host_is_superhost</th>\n",
              "      <th>instant_bookable</th>\n",
              "      <th>has_availability</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>False</td>\n",
              "      <td>True</td>\n",
              "      <td>True</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>True</td>\n",
              "      <td>False</td>\n",
              "      <td>True</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>False</td>\n",
              "      <td>False</td>\n",
              "      <td>True</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>False</td>\n",
              "      <td>False</td>\n",
              "      <td>True</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>True</td>\n",
              "      <td>False</td>\n",
              "      <td>True</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-cb2b087e-bd0e-414e-a03c-8fefd9bd3a91')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-cb2b087e-bd0e-414e-a03c-8fefd9bd3a91 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-cb2b087e-bd0e-414e-a03c-8fefd9bd3a91');\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": 10
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "<details>\n",
        "<summary>Show Expected Output</summary>\n",
        "\n",
        "|index|host\\_is\\_superhost|instant\\_bookable|has\\_availability|\n",
        "|---|---|---|---|\n",
        "|0|false|true|true|\n",
        "|1|true|false|true|\n",
        "|2|false|false|true|\n",
        "|3|false|false|true|\n",
        "|4|true|false|true| \n",
        "\n",
        "<details>\n",
        "<summary>Show Solution</summary>\n",
        "\n",
        "```python\n",
        "df_list[\"host_is_superhost\"] = (\n",
        "    df_list[\"host_is_superhost\"].replace({\"f\": False, \"t\": True}).astype(\"bool\")\n",
        ")\n",
        "df_list[\"instant_bookable\"] = (\n",
        "    df_list[\"instant_bookable\"].replace({\"f\": False, \"t\": True}).astype(\"bool\")\n",
        ")\n",
        "df_list[\"has_availability\"] = (\n",
        "    df_list[\"has_availability\"].replace({\"f\": False, \"t\": True}).astype(\"bool\")\n",
        ")\n",
        "```\n",
        "</details>\n",
        "</details>"
      ],
      "metadata": {
        "id": "RGaAzUy0QBnn"
      },
      "id": "RGaAzUy0QBnn"
    },
    {
      "cell_type": "markdown",
      "source": [
        "Great, you are making a lot of progress! Let's continue!"
      ],
      "metadata": {
        "id": "_BAAPr8lhkmX"
      },
      "id": "_BAAPr8lhkmX"
    },
    {
      "cell_type": "markdown",
      "id": "99f65a90-261f-4382-ad13-ce9f9c898673",
      "metadata": {
        "id": "99f65a90-261f-4382-ad13-ce9f9c898673"
      },
      "source": [
        "#### Task 5: \\~\\~ Float away ~~\n",
        "\n",
        "[*\\[Related section on CoRise\\]*](https://corise.com/course/python-for-data-science/v2/module/clean-inspect-6wg1lh#corise_cl9wmqqgx001x3b6p54g1ka2y)\n",
        "\n",
        "A closer look at the prices in the four columns `price`, `price_per_person`, `minimum_price`, and `service_cost` reveals that they all follow the same pattern:"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "df_list[[\"price\", \"price_per_person\", \"minimum_price\", 'service_cost']].head(5)"
      ],
      "metadata": {
        "id": "LGZRcYsnyeLD",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 206
        },
        "outputId": "09f42d99-0d5a-41e5-dce7-8d906621983d"
      },
      "id": "LGZRcYsnyeLD",
      "execution_count": 11,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "     price price_per_person minimum_price service_cost\n",
              "0   $88.00              $44          $176        $4.99\n",
              "1  $105.00            $52.5          $315        $4.99\n",
              "2  $152.00              $38          $304        $4.99\n",
              "3   $87.00            $43.5          $174        $4.99\n",
              "4  $160.00              $40          $320        $4.99"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-e5174216-5141-4e02-a564-a7152fc00e9e\">\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>price</th>\n",
              "      <th>price_per_person</th>\n",
              "      <th>minimum_price</th>\n",
              "      <th>service_cost</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>$88.00</td>\n",
              "      <td>$44</td>\n",
              "      <td>$176</td>\n",
              "      <td>$4.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>$105.00</td>\n",
              "      <td>$52.5</td>\n",
              "      <td>$315</td>\n",
              "      <td>$4.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>$152.00</td>\n",
              "      <td>$38</td>\n",
              "      <td>$304</td>\n",
              "      <td>$4.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>$87.00</td>\n",
              "      <td>$43.5</td>\n",
              "      <td>$174</td>\n",
              "      <td>$4.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>$160.00</td>\n",
              "      <td>$40</td>\n",
              "      <td>$320</td>\n",
              "      <td>$4.99</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-e5174216-5141-4e02-a564-a7152fc00e9e')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-e5174216-5141-4e02-a564-a7152fc00e9e 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-e5174216-5141-4e02-a564-a7152fc00e9e');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 11
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "All of these four columns have some special characters that you will need to remove before you can change the dtype from object to float.\n",
        "1. Remove dollar signs and commas\n",
        "1. Convert to `float`\n",
        "\n"
      ],
      "metadata": {
        "id": "_s0hBaWfyb5x"
      },
      "id": "_s0hBaWfyb5x"
    },
    {
      "cell_type": "code",
      "source": [
        "df_list[\"price\"] = (\n",
        "    df_list[\"price\"]\n",
        "    .str.replace(\"$\", \"\", regex=True)\n",
        "    .str.replace(\",\", \"\", regex=True)\n",
        "    .astype(\"float\") \n",
        "    ) # YOUR CODE HERE\n",
        "\n",
        "df_list[\"price_per_person\"] = (\n",
        "    df_list[\"price_per_person\"]\n",
        "    .str.replace(\"$\", \"\", regex=True)\n",
        "    .str.replace(\",\", \"\", regex=True)\n",
        "    .astype(\"float\") \n",
        "    ) # YOUR CODE HERE\n",
        "\n",
        "df_list[\"minimum_price\"] = (\n",
        "    df_list[\"minimum_price\"]\n",
        "    .str.replace(\"$\", \"\", regex=True)\n",
        "    .str.replace(\",\", \"\", regex=True)\n",
        "    .astype(\"float\")\n",
        "    ) # YOUR CODE HERE\n",
        "\n",
        "df_list[\"service_cost\"] = (\n",
        "    df_list[\"service_cost\"]\n",
        "    .str.replace(\"$\", \"\", regex=True)\n",
        "    .str.replace(\",\", \"\", regex=True)\n",
        "    .astype(\"float\")\n",
        "    ) # YOUR CODE HERE\n",
        "\n",
        "df_list[[\"price\", \"price_per_person\", \"minimum_price\", 'service_cost']].head(5)"
      ],
      "metadata": {
        "id": "MGHiOAZJIniT",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 206
        },
        "outputId": "4ef919be-802f-4c43-c94e-efe83148b1ae"
      },
      "id": "MGHiOAZJIniT",
      "execution_count": 12,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "   price  price_per_person  minimum_price  service_cost\n",
              "0  88.00             44.00         176.00          4.99\n",
              "1 105.00             52.50         315.00          4.99\n",
              "2 152.00             38.00         304.00          4.99\n",
              "3  87.00             43.50         174.00          4.99\n",
              "4 160.00             40.00         320.00          4.99"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-8ce51d5b-45cd-44f8-ad99-ed39e9f97c8c\">\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>price</th>\n",
              "      <th>price_per_person</th>\n",
              "      <th>minimum_price</th>\n",
              "      <th>service_cost</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>88.00</td>\n",
              "      <td>44.00</td>\n",
              "      <td>176.00</td>\n",
              "      <td>4.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>105.00</td>\n",
              "      <td>52.50</td>\n",
              "      <td>315.00</td>\n",
              "      <td>4.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>152.00</td>\n",
              "      <td>38.00</td>\n",
              "      <td>304.00</td>\n",
              "      <td>4.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>87.00</td>\n",
              "      <td>43.50</td>\n",
              "      <td>174.00</td>\n",
              "      <td>4.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>160.00</td>\n",
              "      <td>40.00</td>\n",
              "      <td>320.00</td>\n",
              "      <td>4.99</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-8ce51d5b-45cd-44f8-ad99-ed39e9f97c8c')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-8ce51d5b-45cd-44f8-ad99-ed39e9f97c8c 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-8ce51d5b-45cd-44f8-ad99-ed39e9f97c8c');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 12
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "<details>\n",
        "  <summary>Show Expected Output\n",
        "  </summary>\n",
        "\n",
        "|index|price|price\\_per\\_person|minimum\\_price|service\\_cost|\n",
        "|---|---|---|---|---|\n",
        "|0|88\\.0|44\\.0|176\\.0|4\\.99|\n",
        "|1|105\\.0|52\\.5|315\\.0|4\\.99|\n",
        "|2|152\\.0|38\\.0|304\\.0|4\\.99|\n",
        "|3|87\\.0|43\\.5|174\\.0|4\\.99|\n",
        "|4|160\\.0|40\\.0|320\\.0|4\\.99|\n",
        "\n",
        "<details>\n",
        "<summary>Show Solution</summary>\n",
        "\n",
        "```python\n",
        "df_list[\"price\"] = (\n",
        "    df_list[\"price\"]\n",
        "    .str.replace(\"$\", \"\", regex=True)\n",
        "    .str.replace(\",\", \"\", regex=True)\n",
        "    .astype(\"float\")\n",
        ")\n",
        "df_list[\"price_per_person\"] = (\n",
        "    df_list[\"price_per_person\"]\n",
        "    .str.replace(\"$\", \"\", regex=True)\n",
        "    .str.replace(\",\", \"\", regex=True)\n",
        "    .astype(\"float\")\n",
        ")\n",
        "df_list[\"minimum_price\"] = (\n",
        "    df_list[\"minimum_price\"]\n",
        "    .str.replace(\"$\", \"\", regex=True)\n",
        "    .str.replace(\",\", \"\", regex=True)\n",
        "    .astype(\"float\")\n",
        ")\n",
        "df_list[\"service_cost\"] = (\n",
        "    df_list[\"service_cost\"]\n",
        "    .str.replace(\"$\", \"\", regex=True)\n",
        "    .str.replace(\",\", \"\", regex=True)\n",
        "    .astype(\"float\")\n",
        ")\n",
        "```\n",
        "\n",
        "</details>\n",
        "</details>"
      ],
      "metadata": {
        "id": "9nXhplvdPy9D"
      },
      "id": "9nXhplvdPy9D"
    },
    {
      "cell_type": "markdown",
      "source": [
        "#### Task 6: Columns with other names\n",
        "\n",
        "[*\\[Related section on CoRise\\]*](https://corise.com/course/python-for-data-science/v2/module/clean-inspect-6wg1lh#corise_cl9wmxdm800313b6p8z422k7r)\n",
        "\n",
        "The following column names need to be changed:\n",
        "- `price` into `price_in_dollar`\n",
        "- `neighbourhood_cleansed` into `neighbourhood`  \n",
        "\n",
        "Please finish the code below."
      ],
      "metadata": {
        "id": "cN3B8ZTg0Dvz"
      },
      "id": "cN3B8ZTg0Dvz"
    },
    {
      "cell_type": "code",
      "source": [
        "df_list = df_list.rename(columns={\"price\": \"price_in_dollar\", \"neighbourhood_cleansed\": \"neighbourhood\"}) # YOUR CODE HERE"
      ],
      "metadata": {
        "id": "silFcFrGI0gB"
      },
      "id": "silFcFrGI0gB",
      "execution_count": 13,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "<details>\n",
        "<summary>Show Solution</summary>\n",
        "\n",
        "```python\n",
        "df_list = df_list.rename(columns={\"price\": \"price_in_dollar\", \"neighbourhood_cleansed\": \"neighbourhood\"})\n",
        "```\n",
        "</details>"
      ],
      "metadata": {
        "id": "zvp4KI7iVxgo"
      },
      "id": "zvp4KI7iVxgo"
    },
    {
      "cell_type": "markdown",
      "id": "2edb088a-619b-470b-b1fd-d9f848e91b03",
      "metadata": {
        "id": "2edb088a-619b-470b-b1fd-d9f848e91b03"
      },
      "source": [
        "#### Task 7: Categories aren't objects\n",
        "\n",
        "[*\\[Related section on CoRise\\]*](https://corise.com/course/python-for-data-science/v2/module/clean-inspect-6wg1lh#corise_clb5s64pp00052a6lmbk0wk8k)\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "Taking a closer look at the `neighbourhood` and `room_type` columns reveals that these columns are assigned an **object** dtype. We want them to be a **category** dtype. Please set the correct data type below."
      ],
      "metadata": {
        "id": "FfzOT7_N02j8"
      },
      "id": "FfzOT7_N02j8"
    },
    {
      "cell_type": "code",
      "source": [
        "df_list = df_list.astype(\n",
        "    {\n",
        "        \"neighbourhood\": \"category\",\n",
        "        \"room_type\": \"category\",\n",
        "    }\n",
        ") # YOUR CODE HERE"
      ],
      "metadata": {
        "id": "KmrVlXc6JITM"
      },
      "id": "KmrVlXc6JITM",
      "execution_count": 14,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "<details>\n",
        "<summary>Show Solution</summary>\n",
        "\n",
        "```python\n",
        "df_list = df_list.astype(\n",
        "    {\n",
        "        \"neighbourhood\": \"category\",\n",
        "        \"room_type\": \"category\",\n",
        "    }\n",
        ")\n",
        "```\n",
        "</details>"
      ],
      "metadata": {
        "id": "RFZ7klC0XkKb"
      },
      "id": "RFZ7klC0XkKb"
    },
    {
      "cell_type": "markdown",
      "id": "cc90e78d-99ca-45b9-af83-523ee7d5d316",
      "metadata": {
        "id": "cc90e78d-99ca-45b9-af83-523ee7d5d316"
      },
      "source": [
        "We've made quite a few changes 👌, but we're not done yet! Next we need to delete any columns in the DataFrame that we won't use in our analysis. "
      ]
    },
    {
      "cell_type": "markdown",
      "id": "39f08964-e685-4e65-81da-d6fadbddfbd1",
      "metadata": {
        "id": "39f08964-e685-4e65-81da-d6fadbddfbd1"
      },
      "source": [
        "#### Task 8: Delete irrelevant columns\n",
        "\n",
        "[*\\[Related section on CoRise\\]*](https://corise.com/course/python-for-data-science/v2/module/clean-inspect-6wg1lh#corise_cl9wo15ct005h3b6pbbez3a3u)\n",
        "\n",
        "It might seem intuitive that more data is always better. But that's not always the case. Often in data science you want *just* the right amount of data — nothing more, nothing less.\n",
        "\n",
        "We need to delete some columns that are irrelevant to our current use case. Those irrelevant columns are:\n",
        "* `host_listings_count`\n",
        "* `host_total_listings_count`\n",
        "* `availability_60`\n",
        "* `availability_90`\n",
        "* `availability_365`\n",
        "* `number_of_reviews`\n",
        "* `number_of_reviews_ltm`\n",
        "* `reviews_per_month`"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "df_list = df_list.drop(\n",
        "    columns=[\n",
        "        \"host_listings_count\",\n",
        "        \"host_total_listings_count\",\n",
        "        \"availability_60\",\n",
        "        \"availability_90\",\n",
        "        \"availability_365\",\n",
        "        \"number_of_reviews\",\n",
        "        \"number_of_reviews_ltm\",\n",
        "        \"reviews_per_month\",\n",
        "    ]\n",
        ") # YOUR CODE HERE"
      ],
      "metadata": {
        "id": "ljXUO3JNJUxm"
      },
      "id": "ljXUO3JNJUxm",
      "execution_count": 15,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "<details>\n",
        "<summary>Show Solution</summary>\n",
        "\n",
        "```python\n",
        "df_list = df_list.drop(\n",
        "    columns=[\n",
        "        \"host_listings_count\",\n",
        "        \"host_total_listings_count\",\n",
        "        \"availability_60\",\n",
        "        \"availability_90\",\n",
        "        \"availability_365\",\n",
        "        \"number_of_reviews\",\n",
        "        \"number_of_reviews_ltm\",\n",
        "        \"reviews_per_month\",\n",
        "    ]\n",
        ")\n",
        "```\n",
        "</details>"
      ],
      "metadata": {
        "id": "VUP96I1oX-Bz"
      },
      "id": "VUP96I1oX-Bz"
    },
    {
      "cell_type": "markdown",
      "id": "b442f925-1192-4451-9276-0ff78cd8ec4c",
      "metadata": {
        "id": "b442f925-1192-4451-9276-0ff78cd8ec4c"
      },
      "source": [
        "## -- Your Progress --\n",
        "\n",
        "Let's now have a look at which data types we still need to change and which columns have some null values."
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "df_list.info(verbose=True, show_counts=True)"
      ],
      "metadata": {
        "id": "S48Xv1Vv4LaH",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "28808932-ab9c-4d1d-dbde-3a2996a40826"
      },
      "id": "S48Xv1Vv4LaH",
      "execution_count": 16,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "<class 'pandas.core.frame.DataFrame'>\n",
            "Int64Index: 6165 entries, 0 to 6172\n",
            "Data columns (total 26 columns):\n",
            " #   Column                                Non-Null Count  Dtype   \n",
            "---  ------                                --------------  -----   \n",
            " 0   id                                    6165 non-null   int64   \n",
            " 1   host_acceptance_rate                  5365 non-null   float64 \n",
            " 2   host_is_superhost                     6165 non-null   bool    \n",
            " 3   neighbourhood                         6165 non-null   category\n",
            " 4   latitude                              6165 non-null   float64 \n",
            " 5   longitude                             6165 non-null   float64 \n",
            " 6   room_type                             6165 non-null   category\n",
            " 7   accommodates                          6165 non-null   int64   \n",
            " 8   bedrooms                              5859 non-null   float64 \n",
            " 9   beds                                  6082 non-null   float64 \n",
            " 10  amenities                             6165 non-null   int64   \n",
            " 11  price_in_dollar                       6165 non-null   float64 \n",
            " 12  minimum_nights                        6165 non-null   int64   \n",
            " 13  maximum_nights                        6165 non-null   int64   \n",
            " 14  has_availability                      6165 non-null   bool    \n",
            " 15  availability_30                       6165 non-null   int64   \n",
            " 16  number_of_reviews_l30d                6165 non-null   int64   \n",
            " 17  review_scores_rating                  5581 non-null   float64 \n",
            " 18  instant_bookable                      6165 non-null   bool    \n",
            " 19  price_in_euros                        0 non-null      object  \n",
            " 20  price_per_person                      6165 non-null   float64 \n",
            " 21  minimum_price                         6165 non-null   float64 \n",
            " 22  discount_per_5_days_booked            6165 non-null   float64 \n",
            " 23  discount_per_10_days_booked           6165 non-null   float64 \n",
            " 24  discount_per_30_and_more_days_booked  6165 non-null   float64 \n",
            " 25  service_cost                          6165 non-null   float64 \n",
            "dtypes: bool(3), category(2), float64(13), int64(7), object(1)\n",
            "memory usage: 1.1+ MB\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "id": "f9ebf852-d145-4ce9-84f2-b5eafb35f3a0",
      "metadata": {
        "id": "f9ebf852-d145-4ce9-84f2-b5eafb35f3a0"
      },
      "source": [
        "We can see from the output above that the columns `host_acceptance_rate`, `review_scores_rating`, `bedrooms`, `beds`, and `price_in_euros` still require some processing, as they contain missing values, and/or have dtypes like object or float when they need an integer data type.\n",
        "\n",
        "To summarize, your cleanup so far has reduced memory usage by almost half, which admittedly given the current amount of memory we are using does not really matter that much. But more importantly, our DataFrame is also more readable now, and we can continue to process it in the next sections."
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "#### Task 9: No unique values\n",
        "\n",
        "[*\\[Related section on CoRise\\]*](https://corise.com/course/python-for-data-science/v2/module/clean-inspect-6wg1lh#corise_cl9wo15ct005h3b6pbbez3a3u)\n",
        "\n",
        "Let's inspect the `price_in_euros` column first, because this column seems to contain only null values, which inherently do not add any meaning to our dataset. Remember our `unique` approach?"
      ],
      "metadata": {
        "id": "j1lKXknZ5559"
      },
      "id": "j1lKXknZ5559"
    },
    {
      "cell_type": "code",
      "source": [
        "df_list.info(verbose=True, show_counts=True) # YOUR CODE HERE"
      ],
      "metadata": {
        "id": "3keZMkvQMDvX",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "e8665d9e-45d1-4b06-c3dc-d4f90f7e63ef"
      },
      "id": "3keZMkvQMDvX",
      "execution_count": 17,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "<class 'pandas.core.frame.DataFrame'>\n",
            "Int64Index: 6165 entries, 0 to 6172\n",
            "Data columns (total 26 columns):\n",
            " #   Column                                Non-Null Count  Dtype   \n",
            "---  ------                                --------------  -----   \n",
            " 0   id                                    6165 non-null   int64   \n",
            " 1   host_acceptance_rate                  5365 non-null   float64 \n",
            " 2   host_is_superhost                     6165 non-null   bool    \n",
            " 3   neighbourhood                         6165 non-null   category\n",
            " 4   latitude                              6165 non-null   float64 \n",
            " 5   longitude                             6165 non-null   float64 \n",
            " 6   room_type                             6165 non-null   category\n",
            " 7   accommodates                          6165 non-null   int64   \n",
            " 8   bedrooms                              5859 non-null   float64 \n",
            " 9   beds                                  6082 non-null   float64 \n",
            " 10  amenities                             6165 non-null   int64   \n",
            " 11  price_in_dollar                       6165 non-null   float64 \n",
            " 12  minimum_nights                        6165 non-null   int64   \n",
            " 13  maximum_nights                        6165 non-null   int64   \n",
            " 14  has_availability                      6165 non-null   bool    \n",
            " 15  availability_30                       6165 non-null   int64   \n",
            " 16  number_of_reviews_l30d                6165 non-null   int64   \n",
            " 17  review_scores_rating                  5581 non-null   float64 \n",
            " 18  instant_bookable                      6165 non-null   bool    \n",
            " 19  price_in_euros                        0 non-null      object  \n",
            " 20  price_per_person                      6165 non-null   float64 \n",
            " 21  minimum_price                         6165 non-null   float64 \n",
            " 22  discount_per_5_days_booked            6165 non-null   float64 \n",
            " 23  discount_per_10_days_booked           6165 non-null   float64 \n",
            " 24  discount_per_30_and_more_days_booked  6165 non-null   float64 \n",
            " 25  service_cost                          6165 non-null   float64 \n",
            "dtypes: bool(3), category(2), float64(13), int64(7), object(1)\n",
            "memory usage: 1.1+ MB\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "The approach should reveal that this column contains no unique values and is thus empty. Please `drop` this column."
      ],
      "metadata": {
        "id": "Lco7cMRM6nEn"
      },
      "id": "Lco7cMRM6nEn"
    },
    {
      "cell_type": "code",
      "source": [
        "df_list = df_list.drop(columns=[\"price_in_euros\"]) # YOUR CODE HERE"
      ],
      "metadata": {
        "id": "3FK-xAZ-MKh-"
      },
      "id": "3FK-xAZ-MKh-",
      "execution_count": 18,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "<details>\n",
        "<summary>Show Solution</summary>\n",
        "\n",
        "```python\n",
        "df_list = df_list.drop(columns=[\"price_in_euros\"])\n",
        "```\n",
        "</details>"
      ],
      "metadata": {
        "id": "iMZiGC_3Zbra"
      },
      "id": "iMZiGC_3Zbra"
    },
    {
      "cell_type": "markdown",
      "id": "67b0eb3d-c8fe-4a31-aa2e-740a4b70e495",
      "metadata": {
        "id": "67b0eb3d-c8fe-4a31-aa2e-740a4b70e495"
      },
      "source": [
        "#### Task 10: Dropping rows\n",
        "\n",
        "[*\\[Related section on CoRise\\]*](https://corise.com/course/python-for-data-science/v2/module/clean-inspect-6wg1lh#corise_clb5s64pp00052a6lmbk0wk8k)\n",
        "\n",
        "DataFrame [`info()`](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.info.html) revealed that some listings have no reviews and an unknown host acceptance rate. Most Airbnb users exclude such listings from their search results. To mimic this filtering approach, please filter out any rows that do not have a `review_scores_rating` and without a `host_acceptance_rate`.\n",
        "\n",
        "A useful method for this approach is the [`dropna()`](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.dropna.html) function."
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "df_list = df_list = df_list.dropna(subset=[\"review_scores_rating\", \"host_acceptance_rate\"]) # YOUR CODE HERE"
      ],
      "metadata": {
        "id": "mELc4tTd8C4L"
      },
      "id": "mELc4tTd8C4L",
      "execution_count": 19,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "<details>\n",
        "<summary>Show Solution</summary>\n",
        "\n",
        "```python\n",
        "df_list = df_list.dropna(subset=[\"review_scores_rating\", \"host_acceptance_rate\"])\n",
        "```\n",
        "</details>"
      ],
      "metadata": {
        "id": "L95IEJOPaize"
      },
      "id": "L95IEJOPaize"
    },
    {
      "cell_type": "markdown",
      "id": "a56ee44e-f810-4a42-96d9-2bfbb65834ff",
      "metadata": {
        "id": "a56ee44e-f810-4a42-96d9-2bfbb65834ff"
      },
      "source": [
        "#### Task 11: Reason through your missing data\n",
        "\n",
        "After setting the right data types, you are often left with making some hard decisions and assumptions about any *partially* incomplete data in your working dataset. In this case, some `beds` and `bedrooms` have no properly assigned values. You can check this by running `df_list.info(verbose=True, show_counts=True)`, which will show that `beds` and `bedrooms` have some missing values.\n",
        "\n",
        "\n",
        "Let's try and make some simple assumptions based on the `room_type` assigned to the listing. First, inspect which room types are found in the dataset. "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 20,
      "id": "5e0aa7f3-8b04-45af-b3a3-47bc3e9eb340",
      "metadata": {
        "id": "5e0aa7f3-8b04-45af-b3a3-47bc3e9eb340",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "eacf2441-d0dc-46a6-e329-56ab115e475d"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "['Private room', 'Entire home/apt', 'Hotel room', 'Shared room']\n",
              "Categories (4, object): ['Entire home/apt', 'Hotel room', 'Private room', 'Shared room']"
            ]
          },
          "metadata": {},
          "execution_count": 20
        }
      ],
      "source": [
        "df_list[\"room_type\"].unique()"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "5917ddc5-cc89-4773-8ed4-4caac60be33d",
      "metadata": {
        "id": "5917ddc5-cc89-4773-8ed4-4caac60be33d"
      },
      "source": [
        "There are four room types. Let's make the assumption that the columns `bedrooms` and `beds` are potentially influenced by `room_type`.\n",
        "\n",
        "Therefore, we can make the following rules:\n",
        "- If you have a **\"Private room\"** or **\"Shared room\"** as `room_type`, then we believe the listing only has one bedroom. \n",
        "- If the listing has **\"Hotel room\"** or **\"Entire home/apt\"** as `room_type`, then we can divide the number of guests the listing accomodates by 2 and round up. \n",
        "- If any of these numbers are missing, then we can leave it empty.\n",
        "\n",
        "Translate these requirements into a Python function, and you get:\n",
        "\n",
        "```python\n",
        "def fill_empty_bedrooms(accommodates: int, bedrooms: int, room_type: str) -> int:\n",
        "    if (room_type == \"Private room\") or (room_type == \"Shared room\"):\n",
        "        return 1\n",
        "    elif (room_type == \"Hotel room\") or (room_type == \"Entire home/apt\"):\n",
        "        return np.ceil(accommodates / 2)\n",
        "    else:\n",
        "        return bedrooms\n",
        "```\n",
        "\n",
        "Let's time this function and see its performance 💪💪! Please run both cells."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 21,
      "id": "f710c2e4-86df-4c22-8e59-f8e14792133f",
      "metadata": {
        "id": "f710c2e4-86df-4c22-8e59-f8e14792133f"
      },
      "outputs": [],
      "source": [
        "def fill_empty_bedrooms(accommodates: int, bedrooms: int, room_type: str) -> int:\n",
        "    if (room_type == \"Private room\") or (room_type == \"Shared room\"):\n",
        "        return 1\n",
        "    elif (room_type == \"Hotel room\") or (room_type == \"Entire home/apt\"):\n",
        "        return np.ceil(accommodates / 2)\n",
        "    else:\n",
        "        return bedrooms"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 22,
      "id": "6f7f7b33-f85d-46a2-85cd-6816267c0926",
      "metadata": {
        "id": "6f7f7b33-f85d-46a2-85cd-6816267c0926",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "1d557c21-bdb8-4bf1-f8b2-584541854484"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "116 ms ± 39.8 ms per loop (mean ± std. dev. of 4 runs, 100 loops each)\n"
          ]
        }
      ],
      "source": [
        "%%timeit -r 4 -n 100\n",
        "\n",
        "temp_df = df_list.copy()  # Deep copy of the df, not a \"view\"\n",
        "temp_df[\"rooms\"] = df_list[[\"accommodates\", \"bedrooms\", \"room_type\"]].apply(\n",
        "    lambda x: fill_empty_bedrooms(x[\"accommodates\"], x[\"bedrooms\"], x[\"room_type\"]),\n",
        "    axis=1,\n",
        ")"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "b79ff3cd-3890-442e-b637-68b794a5a311",
      "metadata": {
        "id": "b79ff3cd-3890-442e-b637-68b794a5a311"
      },
      "source": [
        "Just like in Week 1, we use `timeit` to measure the performance of our function. In the case of Pandas, we are using [`apply()`](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.apply.html) to semi-vectorize our function, but secretly this function just implements something that mimics a for loop. Using [a lambda](https://www.geeksforgeeks.org/applying-lambda-functions-to-pandas-dataframe/) together with [`apply()`](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.apply.html) allows us to access multiple columns to generate an outcome.\n",
        "\n",
        "\n",
        "This approach is often good enough, but not always, especially if you are dealing with large datasets. Below we will run the [`apply()`](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.apply.html) function for output. We've also provided some alternative functions, though they are often found to be slower. (However, it can be a bit of a grey area, [so it really depends](https://stackoverflow.com/questions/24870953/does-pandas-iterrows-have-performance-issues/24871316#24871316).)"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "%%timeit -r 4 -n 100\n",
        "\n",
        "df_list[\"bedrooms\"] = df_list[[\"accommodates\", \"bedrooms\", \"room_type\"]].apply(\n",
        "    lambda x: fill_empty_bedrooms(x[\"accommodates\"], x[\"bedrooms\"], x[\"room_type\"]),\n",
        "    axis=1,\n",
        ") # YOUR CODE HERE"
      ],
      "metadata": {
        "id": "m74v9tZHA0c_",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "72f8be49-597f-4092-a68e-f9af2f294248"
      },
      "id": "m74v9tZHA0c_",
      "execution_count": 23,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "92.7 ms ± 3.91 ms per loop (mean ± std. dev. of 4 runs, 100 loops each)\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "id": "37fb2d2b-e631-4630-bbf6-3de6bcecae94",
      "metadata": {
        "id": "37fb2d2b-e631-4630-bbf6-3de6bcecae94"
      },
      "source": [
        "| Related functions ([In *general* order of preference](https://stackoverflow.com/questions/24870953/does-pandas-iterrows-have-performance-issues/24871316#24871316))|\n",
        "| ---- |\n",
        "| [apply()](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.apply.html): Apply a function along one or multiple columns |\n",
        "| [pipe()](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.pipe.html): Chain multiple transformations/functions after each other |\n",
        "| [applymap()](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.applymap.html): Use strictly as a transformation of current value to a new value |\n",
        "| [itertuples()](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.itertuples.html): Iterate over DataFrame rows as named tuples |\n",
        "| [iteritems()](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.iteritems.html): Iterate  over DataFrame columns |\n",
        "| [iterrows()](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.iterrows.html): Iterate over DataFrame rows |"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "<details>\n",
        "<summary>Show Solution</summary>\n",
        "\n",
        "```python\n",
        "df_list[\"bedrooms\"] = df_list[[\"accommodates\", \"bedrooms\", \"room_type\"]].apply(\n",
        "    lambda x: fill_empty_bedrooms(x[\"accommodates\"], x[\"bedrooms\"], x[\"room_type\"]),\n",
        "    axis=1,\n",
        ")\n",
        "```\n",
        "</details>"
      ],
      "metadata": {
        "id": "KwFGUTPXa6NH"
      },
      "id": "KwFGUTPXa6NH"
    },
    {
      "cell_type": "markdown",
      "id": "6c1926ac-a65c-4636-ab6f-a2ea8926eafe",
      "metadata": {
        "id": "6c1926ac-a65c-4636-ab6f-a2ea8926eafe"
      },
      "source": [
        "If you feel like you want to take the function's performance to the next level, we have a **bonus** (**NOT** required) question 😱!"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "e71cc408-ec9a-4139-8636-ee6e9aad3244",
      "metadata": {
        "id": "e71cc408-ec9a-4139-8636-ee6e9aad3244"
      },
      "source": [
        "---\n",
        "\n",
        "#### (Extra Credit) Task 12: Vectorize!\n",
        "\n",
        "Can you vectorize the function by using the method described under \"Pandas Vectorization\", with inspiration from [this link](https://towardsdatascience.com/do-you-use-apply-in-pandas-there-is-a-600x-faster-way-d2497facfa66)? On our hardware, applying vectorization led to results that were at least 30-40x faster. (This might differ a bit on your hardware.)"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "%%timeit -r 4 -n 100\n",
        "\n",
        "temp_df = df_list.copy()\n",
        "\n",
        "# Please use as many lines as you think you need to \n",
        "# implement this function. We required 5 separate\n",
        "# statements.\n",
        "temp_df[\"beds\"] = temp_df.bedrooms\n",
        "priv_shared_mask = (temp_df.room_type == \"Private room\") | (\n",
        "    temp_df.room_type == \"Shared room\"\n",
        ")\n",
        "temp_df.loc[priv_shared_mask, \"beds\"] = 1\n",
        "hotel_apt_mask = (temp_df.room_type == \"Hotel room\") | (\n",
        "    temp_df.room_type == \"Entire home/apt\"\n",
        ")\n",
        "temp_df.loc[hotel_apt_mask, \"beds\"] = np.ceil(temp_df.accommodates / 2)"
      ],
      "metadata": {
        "id": "3CjW4XiYBOLL",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "f327b853-1a07-4890-a477-4ce35997a65e"
      },
      "id": "3CjW4XiYBOLL",
      "execution_count": 24,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "2.46 ms ± 115 µs per loop (mean ± std. dev. of 4 runs, 100 loops each)\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "<details>\n",
        "<summary>Show Solution</summary>\n",
        "\n",
        "```python\n",
        "%%timeit -r 4 -n 100\n",
        "\n",
        "temp_df = df_list.copy()\n",
        "\n",
        "\n",
        "temp_df[\"beds\"] = temp_df.bedrooms\n",
        "priv_shared_mask = (temp_df.room_type == \"Private room\") | (\n",
        "    temp_df.room_type == \"Shared room\"\n",
        ")\n",
        "temp_df.loc[priv_shared_mask, \"beds\"] = 1\n",
        "hotel_apt_mask = (temp_df.room_type == \"Hotel room\") | (\n",
        "    temp_df.room_type == \"Entire home/apt\"\n",
        ")\n",
        "temp_df.loc[hotel_apt_mask, \"beds\"] = np.ceil(temp_df.accommodates / 2)\n",
        "```\n",
        "</details>"
      ],
      "metadata": {
        "id": "c0vgmgvEbPf_"
      },
      "id": "c0vgmgvEbPf_"
    },
    {
      "cell_type": "markdown",
      "id": "0d720d45-b510-45eb-995f-11e43ddcba09",
      "metadata": {
        "id": "0d720d45-b510-45eb-995f-11e43ddcba09"
      },
      "source": [
        "---\n",
        "\n",
        "#### Task 13: Clean-up crew\n",
        "\n",
        "[*\\[Related section on CoRise\\]*](https://corise.com/course/python-for-data-science/v2/module/clean-inspect-6wg1lh#corise_clb5s64pp00052a6lmbk0wk8k)\n",
        "\n",
        "Thanks to our logic and assumptions, most listings now have a proper amount of defined rooms. However, there are still a few listings without any number of rooms defined. Remove all rows/entries that have an empty `bedrooms`, `beds`."
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "df_list = df_list = df_list.dropna(subset=[\"bedrooms\", \"beds\"]) # YOUR CODE HERE"
      ],
      "metadata": {
        "id": "evofgflhBxx4"
      },
      "id": "evofgflhBxx4",
      "execution_count": 25,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "<details>\n",
        "<summary>Show Solution</summary>\n",
        "\n",
        "```python\n",
        "df_list = df_list.dropna(subset=[\"bedrooms\", \"beds\"])\n",
        "```\n",
        "</details>"
      ],
      "metadata": {
        "id": "J3WiZJ1ufHMg"
      },
      "id": "J3WiZJ1ufHMg"
    },
    {
      "cell_type": "markdown",
      "id": "b5095beb-d2ce-40bb-9536-e26e06a729bd",
      "metadata": {
        "id": "b5095beb-d2ce-40bb-9536-e26e06a729bd"
      },
      "source": [
        "Now that we have removed all the empty values, finally we can assign the dtype `int` instead of `float` to these two columns.\n",
        "\n",
        "Please set the columns `beds` and `bedrooms` as `int`. "
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "df_list[\"beds\"] = df_list[\"beds\"].astype(\"int\") # YOUR CODE HERE\n",
        "df_list[\"bedrooms\"] = df_list[\"bedrooms\"].astype(\"int\") # YOUR CODE HERE"
      ],
      "metadata": {
        "id": "TNWvLCDqC9Fv"
      },
      "id": "TNWvLCDqC9Fv",
      "execution_count": 26,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "<details>\n",
        "<summary>Show Solution</summary>\n",
        "\n",
        "```python\n",
        "df_list[\"beds\"] = df_list[\"beds\"].astype(\"int\")\n",
        "df_list[\"bedrooms\"] = df_list[\"bedrooms\"].astype(\"int\")\n",
        "```\n",
        "</details>"
      ],
      "metadata": {
        "id": "5ubuKddufJMu"
      },
      "id": "5ubuKddufJMu"
    },
    {
      "cell_type": "markdown",
      "id": "f2deb99a-8ce8-4853-88c9-21293e1744a0",
      "metadata": {
        "id": "f2deb99a-8ce8-4853-88c9-21293e1744a0"
      },
      "source": [
        "---\n",
        "\n",
        "#### (Extra Credit) Task 14: Speed it up\n",
        "\n",
        "Lastly, and this is entirely optional, but you can further speed-up data processing by taking the appropriate number of bytes for a given data type, especially when dealing with large datasets (more info [here](https://towardsdatascience.com/reducing-memory-usage-in-pandas-with-smaller-datatypes-b527635830af)). Currently, the data types are set to 64 bits by default, but most of these could be set to lower values. Good luck 💪💪!"
      ]
    },
    {
      "cell_type": "code",
      "source": [],
      "metadata": {
        "id": "tRiUEo1pDYPS"
      },
      "id": "tRiUEo1pDYPS",
      "execution_count": 26,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "id": "696eb00f-acf8-44ee-8074-e548fa36886b",
      "metadata": {
        "id": "696eb00f-acf8-44ee-8074-e548fa36886b"
      },
      "source": [
        "---\n",
        "\n",
        "## Cleaning Is DONE!\n",
        "\n",
        "<center>\n",
        "  <img src=https://i.ibb.co/f9wQt8T/2016-celebrate-celebration-city-preview.jpg width=\"500\" align=\"center\" />\n",
        "</center>\n",
        "<br/>\n",
        "\n",
        "✨ Awesome ✨! You cleaned our data and made sure to watch that the intermediate results aligned with what we expected."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 27,
      "id": "4b15e8cc-5aa3-49e7-81de-c2e30229d95b",
      "metadata": {
        "id": "4b15e8cc-5aa3-49e7-81de-c2e30229d95b",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "335149bd-e81b-47f8-d1e9-eb04815cd284"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "<class 'pandas.core.frame.DataFrame'>\n",
            "Int64Index: 4817 entries, 0 to 6172\n",
            "Data columns (total 25 columns):\n",
            " #   Column                                Non-Null Count  Dtype   \n",
            "---  ------                                --------------  -----   \n",
            " 0   id                                    4817 non-null   int64   \n",
            " 1   host_acceptance_rate                  4817 non-null   float64 \n",
            " 2   host_is_superhost                     4817 non-null   bool    \n",
            " 3   neighbourhood                         4817 non-null   category\n",
            " 4   latitude                              4817 non-null   float64 \n",
            " 5   longitude                             4817 non-null   float64 \n",
            " 6   room_type                             4817 non-null   category\n",
            " 7   accommodates                          4817 non-null   int64   \n",
            " 8   bedrooms                              4817 non-null   int64   \n",
            " 9   beds                                  4817 non-null   int64   \n",
            " 10  amenities                             4817 non-null   int64   \n",
            " 11  price_in_dollar                       4817 non-null   float64 \n",
            " 12  minimum_nights                        4817 non-null   int64   \n",
            " 13  maximum_nights                        4817 non-null   int64   \n",
            " 14  has_availability                      4817 non-null   bool    \n",
            " 15  availability_30                       4817 non-null   int64   \n",
            " 16  number_of_reviews_l30d                4817 non-null   int64   \n",
            " 17  review_scores_rating                  4817 non-null   float64 \n",
            " 18  instant_bookable                      4817 non-null   bool    \n",
            " 19  price_per_person                      4817 non-null   float64 \n",
            " 20  minimum_price                         4817 non-null   float64 \n",
            " 21  discount_per_5_days_booked            4817 non-null   float64 \n",
            " 22  discount_per_10_days_booked           4817 non-null   float64 \n",
            " 23  discount_per_30_and_more_days_booked  4817 non-null   float64 \n",
            " 24  service_cost                          4817 non-null   float64 \n",
            "dtypes: bool(3), category(2), float64(11), int64(9)\n",
            "memory usage: 814.7 KB\n"
          ]
        }
      ],
      "source": [
        "df_list.info(verbose=True, show_counts=True)"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "Using the function [`head()`](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.head.html) reveals the same. "
      ],
      "metadata": {
        "id": "ZIJvCU2GDwDQ"
      },
      "id": "ZIJvCU2GDwDQ"
    },
    {
      "cell_type": "code",
      "execution_count": 28,
      "id": "bb6771c3-67cc-43f8-bb72-f1737e74bb16",
      "metadata": {
        "id": "bb6771c3-67cc-43f8-bb72-f1737e74bb16",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 260
        },
        "outputId": "6c049bf9-9332-4798-aa56-6a906dbc755e"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "         id  host_acceptance_rate  host_is_superhost  \\\n",
              "0  23726706                  0.95              False   \n",
              "1  35815036                  1.00               True   \n",
              "2  31553121                  1.00              False   \n",
              "\n",
              "              neighbourhood  latitude  longitude        room_type  \\\n",
              "0  IJburg - Zeeburgereiland     52.35       4.98     Private room   \n",
              "1                Noord-Oost     52.42       4.96  Entire home/apt   \n",
              "2                Noord-West     52.43       4.92  Entire home/apt   \n",
              "\n",
              "   accommodates  bedrooms  beds  amenities  price_in_dollar  minimum_nights  \\\n",
              "0             2         1     1          6            88.00               2   \n",
              "1             2         1     1          5           105.00               3   \n",
              "2             4         2     3          3           152.00               2   \n",
              "\n",
              "   maximum_nights  has_availability  availability_30  number_of_reviews_l30d  \\\n",
              "0              14              True                0                       3   \n",
              "1             100              True                4                       6   \n",
              "2              60              True                0                       1   \n",
              "\n",
              "   review_scores_rating  instant_bookable  price_per_person  minimum_price  \\\n",
              "0                  4.99              True             44.00         176.00   \n",
              "1                  4.96             False             52.50         315.00   \n",
              "2                  4.74             False             38.00         304.00   \n",
              "\n",
              "   discount_per_5_days_booked  discount_per_10_days_booked  \\\n",
              "0                        0.05                         0.11   \n",
              "1                        0.05                         0.12   \n",
              "2                        0.07                         0.11   \n",
              "\n",
              "   discount_per_30_and_more_days_booked  service_cost  \n",
              "0                                  0.16          4.99  \n",
              "1                                  0.16          4.99  \n",
              "2                                  0.22          4.99  "
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-92553f34-0cc9-4cf5-bb08-a186f025d622\">\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>id</th>\n",
              "      <th>host_acceptance_rate</th>\n",
              "      <th>host_is_superhost</th>\n",
              "      <th>neighbourhood</th>\n",
              "      <th>latitude</th>\n",
              "      <th>longitude</th>\n",
              "      <th>room_type</th>\n",
              "      <th>accommodates</th>\n",
              "      <th>bedrooms</th>\n",
              "      <th>beds</th>\n",
              "      <th>amenities</th>\n",
              "      <th>price_in_dollar</th>\n",
              "      <th>minimum_nights</th>\n",
              "      <th>maximum_nights</th>\n",
              "      <th>has_availability</th>\n",
              "      <th>availability_30</th>\n",
              "      <th>number_of_reviews_l30d</th>\n",
              "      <th>review_scores_rating</th>\n",
              "      <th>instant_bookable</th>\n",
              "      <th>price_per_person</th>\n",
              "      <th>minimum_price</th>\n",
              "      <th>discount_per_5_days_booked</th>\n",
              "      <th>discount_per_10_days_booked</th>\n",
              "      <th>discount_per_30_and_more_days_booked</th>\n",
              "      <th>service_cost</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>23726706</td>\n",
              "      <td>0.95</td>\n",
              "      <td>False</td>\n",
              "      <td>IJburg - Zeeburgereiland</td>\n",
              "      <td>52.35</td>\n",
              "      <td>4.98</td>\n",
              "      <td>Private room</td>\n",
              "      <td>2</td>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "      <td>6</td>\n",
              "      <td>88.00</td>\n",
              "      <td>2</td>\n",
              "      <td>14</td>\n",
              "      <td>True</td>\n",
              "      <td>0</td>\n",
              "      <td>3</td>\n",
              "      <td>4.99</td>\n",
              "      <td>True</td>\n",
              "      <td>44.00</td>\n",
              "      <td>176.00</td>\n",
              "      <td>0.05</td>\n",
              "      <td>0.11</td>\n",
              "      <td>0.16</td>\n",
              "      <td>4.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>35815036</td>\n",
              "      <td>1.00</td>\n",
              "      <td>True</td>\n",
              "      <td>Noord-Oost</td>\n",
              "      <td>52.42</td>\n",
              "      <td>4.96</td>\n",
              "      <td>Entire home/apt</td>\n",
              "      <td>2</td>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "      <td>5</td>\n",
              "      <td>105.00</td>\n",
              "      <td>3</td>\n",
              "      <td>100</td>\n",
              "      <td>True</td>\n",
              "      <td>4</td>\n",
              "      <td>6</td>\n",
              "      <td>4.96</td>\n",
              "      <td>False</td>\n",
              "      <td>52.50</td>\n",
              "      <td>315.00</td>\n",
              "      <td>0.05</td>\n",
              "      <td>0.12</td>\n",
              "      <td>0.16</td>\n",
              "      <td>4.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>31553121</td>\n",
              "      <td>1.00</td>\n",
              "      <td>False</td>\n",
              "      <td>Noord-West</td>\n",
              "      <td>52.43</td>\n",
              "      <td>4.92</td>\n",
              "      <td>Entire home/apt</td>\n",
              "      <td>4</td>\n",
              "      <td>2</td>\n",
              "      <td>3</td>\n",
              "      <td>3</td>\n",
              "      <td>152.00</td>\n",
              "      <td>2</td>\n",
              "      <td>60</td>\n",
              "      <td>True</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>4.74</td>\n",
              "      <td>False</td>\n",
              "      <td>38.00</td>\n",
              "      <td>304.00</td>\n",
              "      <td>0.07</td>\n",
              "      <td>0.11</td>\n",
              "      <td>0.22</td>\n",
              "      <td>4.99</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-92553f34-0cc9-4cf5-bb08-a186f025d622')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-92553f34-0cc9-4cf5-bb08-a186f025d622 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-92553f34-0cc9-4cf5-bb08-a186f025d622');\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": 28
        }
      ],
      "source": [
        "df_list.head(3)"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "df_list = df_list.astype({'bedrooms':np.int8, 'beds':np.int8, 'amenities':np.int8})\n",
        "# You can change the storage type as above for others\n",
        "df_list.info(verbose=True, show_counts=True)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "U4uxmWoT_pBU",
        "outputId": "69701532-9c3f-43a1-be6f-9a9646f63302"
      },
      "id": "U4uxmWoT_pBU",
      "execution_count": 36,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "<class 'pandas.core.frame.DataFrame'>\n",
            "Int64Index: 4817 entries, 0 to 6172\n",
            "Data columns (total 25 columns):\n",
            " #   Column                                Non-Null Count  Dtype   \n",
            "---  ------                                --------------  -----   \n",
            " 0   id                                    4817 non-null   int64   \n",
            " 1   host_acceptance_rate                  4817 non-null   float64 \n",
            " 2   host_is_superhost                     4817 non-null   bool    \n",
            " 3   neighbourhood                         4817 non-null   category\n",
            " 4   latitude                              4817 non-null   float64 \n",
            " 5   longitude                             4817 non-null   float64 \n",
            " 6   room_type                             4817 non-null   category\n",
            " 7   accommodates                          4817 non-null   int64   \n",
            " 8   bedrooms                              4817 non-null   int8    \n",
            " 9   beds                                  4817 non-null   int8    \n",
            " 10  amenities                             4817 non-null   int8    \n",
            " 11  price_in_dollar                       4817 non-null   float64 \n",
            " 12  minimum_nights                        4817 non-null   int64   \n",
            " 13  maximum_nights                        4817 non-null   int64   \n",
            " 14  has_availability                      4817 non-null   bool    \n",
            " 15  availability_30                       4817 non-null   int64   \n",
            " 16  number_of_reviews_l30d                4817 non-null   int64   \n",
            " 17  review_scores_rating                  4817 non-null   float64 \n",
            " 18  instant_bookable                      4817 non-null   bool    \n",
            " 19  price_per_person                      4817 non-null   float64 \n",
            " 20  minimum_price                         4817 non-null   float64 \n",
            " 21  discount_per_5_days_booked            4817 non-null   float64 \n",
            " 22  discount_per_10_days_booked           4817 non-null   float64 \n",
            " 23  discount_per_30_and_more_days_booked  4817 non-null   float64 \n",
            " 24  service_cost                          4817 non-null   float64 \n",
            "dtypes: bool(3), category(2), float64(11), int64(6), int8(3)\n",
            "memory usage: 715.9 KB\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "id": "74572dff-7ed3-4265-a90f-11280a7974e2",
      "metadata": {
        "id": "74572dff-7ed3-4265-a90f-11280a7974e2"
      },
      "source": [
        "As you might have noticed, the steps you take and the methods you use to clean your data is very dependent on the data that is given. If you'd like more practice, we encourage you to take a look at this [Kaggle tutorial](https://www.kaggle.com/learn/data-cleaning) which shows some other problems that might occur when dealing with data."
      ]
    },
    {
      "cell_type": "markdown",
      "id": "efa15f97-5cd1-47fd-bd5c-2d5eb1b0ac04",
      "metadata": {
        "id": "efa15f97-5cd1-47fd-bd5c-2d5eb1b0ac04"
      },
      "source": [
        "## Mix and Match\n",
        "\n",
        "<center>\n",
        "  <img src=https://successrice.com/wp-content/uploads/2020/08/Pride-038-1-980x551.jpg.webp width=\"500\" align=\"center\" />\n",
        "</center>\n",
        "<br/>\n",
        "\n",
        "As a next step, you will again merge these two datasets, as was shown in this week's content. However, this time around we will take a slightly different angle with the data."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 29,
      "id": "eb9c7999-da32-4898-8672-4ab0f0220fa2",
      "metadata": {
        "id": "eb9c7999-da32-4898-8672-4ab0f0220fa2",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 143
        },
        "outputId": "f0847a0f-011a-49ee-ddfc-86ca140129b3"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "   listing_id       date  available  price_in_dollar  minimum_nights  \\\n",
              "0    23726706 2022-06-05      False            90.00               2   \n",
              "1    23726706 2022-06-06      False            90.00               2   \n",
              "2    23726706 2022-06-07      False            90.00               2   \n",
              "\n",
              "   maximum_nights  \n",
              "0            1125  \n",
              "1            1125  \n",
              "2            1125  "
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-646b97c2-105d-4dfc-bfcc-64002a35e467\">\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>listing_id</th>\n",
              "      <th>date</th>\n",
              "      <th>available</th>\n",
              "      <th>price_in_dollar</th>\n",
              "      <th>minimum_nights</th>\n",
              "      <th>maximum_nights</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>23726706</td>\n",
              "      <td>2022-06-05</td>\n",
              "      <td>False</td>\n",
              "      <td>90.00</td>\n",
              "      <td>2</td>\n",
              "      <td>1125</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>23726706</td>\n",
              "      <td>2022-06-06</td>\n",
              "      <td>False</td>\n",
              "      <td>90.00</td>\n",
              "      <td>2</td>\n",
              "      <td>1125</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>23726706</td>\n",
              "      <td>2022-06-07</td>\n",
              "      <td>False</td>\n",
              "      <td>90.00</td>\n",
              "      <td>2</td>\n",
              "      <td>1125</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-646b97c2-105d-4dfc-bfcc-64002a35e467')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-646b97c2-105d-4dfc-bfcc-64002a35e467 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-646b97c2-105d-4dfc-bfcc-64002a35e467');\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": 29
        }
      ],
      "source": [
        "# The Calendar DataFrame!\n",
        "df_cal.head(3)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "b686c3f4-2f95-4a19-912f-b950cea40d0c",
      "metadata": {
        "id": "b686c3f4-2f95-4a19-912f-b950cea40d0c"
      },
      "source": [
        "---\n",
        "\n",
        "#### (Extra Credit) Task 15: Minimum stay\n",
        "\n",
        "[*\\[Related section on CoRise\\]*](https://corise.com/course/python-for-data-science/v2/module/merging-pros-vlyoh)\n",
        "\n",
        "You are looking to stay for a minimum of 3 days. Since you are unsure when it will be booked, you'd like to exclude all `listing_ids` that go below that threshold of 3 days no matter what time of year. This is because you think that bookings with a minimum stay of 3 days are more likely to have discount prices.\n",
        "\n",
        "With these excluded, you would like to see the expected booking price for 5 days: \n",
        "\n",
        "- Using the helper code, create a conditional index for entries `listing_id` that are above the 3 day threshold\n",
        "- Remove them using `.loc[conditional_index]` over the conditional index.\n",
        "- Calculate the price of booking a listing for 5 days by multiplying the current day multiplied by 5, and assign this to a column called `five_day_dollar_price`"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "67b93fad-e42f-42b8-bb85-0a57166ecf3a",
      "metadata": {
        "id": "67b93fad-e42f-42b8-bb85-0a57166ecf3a"
      },
      "outputs": [],
      "source": [
        "# First start by making a copy, for debugging purposes\n",
        "calendar_newdf = df_cal.copy()\n",
        "\n",
        "include_list = (\n",
        "    calendar_newdf[\"minimum_nights\"] >= 3\n",
        ")"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Get all the listings with a minimum nights of 3+\n",
        "# Use the include_list\n",
        "calendar_newdf = ... # YOUR CODE HERE"
      ],
      "metadata": {
        "id": "P_lkhpOtF5ul"
      },
      "id": "P_lkhpOtF5ul",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "<details>\n",
        "<summary>Show Solution</summary>\n",
        "\n",
        "```python\n",
        "calendar_newdf = calendar_newdf.loc[include_list]\n",
        "```\n",
        "\n",
        "</details>"
      ],
      "metadata": {
        "id": "YSKKN28fl63p"
      },
      "id": "YSKKN28fl63p"
    },
    {
      "cell_type": "markdown",
      "id": "b181c54a-0339-422e-853f-0e88a212d009",
      "metadata": {
        "id": "b181c54a-0339-422e-853f-0e88a212d009"
      },
      "source": [
        "| Related functions |\n",
        "| ---- |\n",
        "| [isin()](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.isin.html): Filter the DataFrame on provided values |\n",
        "| [eq()](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.eq.html#pandas.DataFrame.eq): Filter the DataFrame for all values equal to the provided input |\n",
        "| [ne()](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.ne.html#pandas.DataFrame.ne): Filter the DataFrame for all values not equal to the provided input |"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "calendar_newdf[\"five_day_dollar_price\"] = ... # YOUR CODE HERE"
      ],
      "metadata": {
        "id": "0wA7LRZfGOk-"
      },
      "id": "0wA7LRZfGOk-",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "<details>\n",
        "<summary>Show Solution</summary>\n",
        "\n",
        "```python\n",
        "calendar_newdf[\"five_day_dollar_price\"] = calendar_newdf[\"price_in_dollar\"] * 5\n",
        "```\n",
        "\n",
        "</details>"
      ],
      "metadata": {
        "id": "4TAje5qlb77h"
      },
      "id": "4TAje5qlb77h"
    },
    {
      "cell_type": "markdown",
      "id": "250f2e99-471f-42c2-91b3-7e7176e3f600",
      "metadata": {
        "id": "250f2e99-471f-42c2-91b3-7e7176e3f600"
      },
      "source": [
        "Now let's transform our newly created DataFrame into a **pivot table**, where we aggregate our rows using the `listing_id` as the index, and the columns `available` and `five_day_dollar_price` as values."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "23ba3117-bd59-48af-82b4-91bb4562244d",
      "metadata": {
        "id": "23ba3117-bd59-48af-82b4-91bb4562244d"
      },
      "outputs": [],
      "source": [
        "calendar_summarizeddf = pd.pivot_table(\n",
        "    data=calendar_newdf,\n",
        "    index=[\"listing_id\"],\n",
        "    values=[\"available\", \"five_day_dollar_price\"],\n",
        "    aggfunc=np.mean,  # The default aggregation function used\n",
        "    # for merging multiple related rows of data.\n",
        ")\n",
        "\n",
        "calendar_summarizeddf.head(3)"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "<details>\n",
        "  <summary>Show Expected Output\n",
        "  </summary>\n",
        "\n",
        "|listing\\_id|available|five\\_day\\_dollar\\_price|\n",
        "|---|---|---|\n",
        "|2818|0\\.20821917808219179|346\\.90410958904107|\n",
        "|44391|0\\.0|1200\\.0|\n",
        "|49552|0\\.4581005586592179|1162\\.5|\n",
        "\n",
        "</details>\n"
      ],
      "metadata": {
        "id": "L8108nxGrYWU"
      },
      "id": "L8108nxGrYWU"
    },
    {
      "cell_type": "markdown",
      "id": "d8d6a84b-1b7d-4d7d-b5b5-ab5b6b039db4",
      "metadata": {
        "id": "d8d6a84b-1b7d-4d7d-b5b5-ab5b6b039db4"
      },
      "source": [
        "#### (Extra Credit) Task 16: Maximum price and date\n",
        "\n",
        "[*\\[Related section on CoRise\\]*](https://corise.com/course/python-for-data-science/v2/module/merging-pros-vlyoh#corise_cl9woiqve006p3b6p0vsdbqge)\n",
        "\n",
        "Can you make a **pivot table** that states the **maximum `price_in_dollar`** for every Airbnb listing?"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "temp_sum_df = pd.pivot_table(\n",
        "    ... # YOUR CODE HERE\n",
        ")\n",
        "\n",
        "temp_sum_df.head(3)"
      ],
      "metadata": {
        "id": "n6p8HXByHsup"
      },
      "id": "n6p8HXByHsup",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "<details>\n",
        "  <summary>Show Expected Output\n",
        "  </summary>\n",
        "\n",
        "|listing\\_id|price\\_in\\_dollar|\n",
        "|---|---|\n",
        "|2818|80\\.0|\n",
        "|44391|240\\.0|\n",
        "|49552|300\\.0|\n",
        "\n",
        "<details>\n",
        "  <summary>Show Solution </summary>\n",
        "  \n",
        "```\n",
        "temp_sum_df = pd.pivot_table(\n",
        "    data=calendar_newdf,\n",
        "    index=[\"listing_id\"],\n",
        "    values=[\"price_in_dollar\"],\n",
        "    aggfunc=np.max,  # The default aggregation function used\n",
        "    # for merging multiple related rows of data.\n",
        ")\n",
        "```\n",
        "</details>\n",
        "</details>"
      ],
      "metadata": {
        "id": "Fs9jFq75dEz9"
      },
      "id": "Fs9jFq75dEz9"
    },
    {
      "cell_type": "markdown",
      "id": "5db7aac6-bff7-4e52-b810-5b219bb9985c",
      "metadata": {
        "id": "5db7aac6-bff7-4e52-b810-5b219bb9985c"
      },
      "source": [
        "---\n",
        "\n",
        "## Join Us?!\n",
        "\n",
        "<center>\n",
        "  <img src=https://upload.wikimedia.org/wikipedia/commons/9/95/Merge_left_%28259959%29_-_The_Noun_Project.svg width=\"200\" align=\"center\" />\n",
        "</center>\n",
        "<br/>\n",
        "\n",
        "We are going to merge the pivot table that includes the `five_day_dollar_price` for each listing with `listings_df`. We have to keep in mind that we want to keep only those rows of Airbnb listing IDs that are present in both datasets."
      ]
    },
    {
      "cell_type": "markdown",
      "id": "43c76adb-67da-4c37-ad9d-550b03c1d332",
      "metadata": {
        "id": "43c76adb-67da-4c37-ad9d-550b03c1d332"
      },
      "source": [
        "#### Task 17: Merging\n",
        "\n",
        "[*\\[Related section on CoRise\\]*](https://corise.com/course/python-for-data-science/v2/module/merging-pros-vlyoh#corise_cl9wp1zf9007m3b6pfiawiagd)\n",
        "\n",
        "Let's use the [`pd.merge()`](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.merge.html) operation as was shown on CoRise, with the pivot table on the right and the **Listings DataFrame** on the left. Make sure to provide which columns you want to join on for our pivot table and the DataFrame."
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "final_df = pd.merge(\n",
        "    ... # YOUR CODE HERE\n",
        ")\n",
        "final_df.head(3)"
      ],
      "metadata": {
        "id": "f0FIdh0CIyY6"
      },
      "id": "f0FIdh0CIyY6",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "<details>\n",
        "  <summary>Show Expected Output\n",
        "  </summary>\n",
        "\n",
        "|index|id|host\\_acceptance\\_rate|host\\_is\\_superhost|neighbourhood\\_cleansed|latitude|longitude|room\\_type|accommodates|bedrooms|beds|amenities|price|minimum\\_nights|maximum\\_nights|has\\_availability|availability\\_30|number\\_of\\_reviews\\_l30d|review\\_scores\\_rating|instant\\_bookable|price\\_per\\_person|\n",
        "|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|\n",
        "|0|35815036|1\\.0|true|Noord-Oost|52\\.42419|4\\.95689|Entire home/apt|2|1|1|5|105\\.0|3|100|true|4|6|4\\.96|false|52\\.5|\n",
        "|1|19572024|1\\.0|false|Watergraafsmeer|52\\.30739|4\\.90833|Entire home/apt|6|3|6|14|279\\.0|3|300|true|6|3|4\\.69|false|46\\.5|\n",
        "|2|2973384|0\\.38|false|Watergraafsmeer|52\\.30989|4\\.90528|Entire home/apt|5|3|3|7|185\\.0|6|21|true|0|0|4\\.83|false|37\\.0|\n",
        "\n",
        "<details>\n",
        "  <summary>Show Solution </summary>\n",
        "  \n",
        "```\n",
        "final_df = pd.merge(\n",
        "    df_list,\n",
        "    calendar_summarizeddf,\n",
        "    left_on=[\"id\"],\n",
        "    right_on=[\"listing_id\"],\n",
        "    how=\"inner\",\n",
        ")\n",
        "```\n",
        "</details>\n",
        "</details>"
      ],
      "metadata": {
        "id": "EU59_RsPf9cT"
      },
      "id": "EU59_RsPf9cT"
    },
    {
      "cell_type": "markdown",
      "id": "1c891740-71b1-4617-b41c-a3f310906eb6",
      "metadata": {
        "id": "1c891740-71b1-4617-b41c-a3f310906eb6"
      },
      "source": [
        "---\n",
        "\n",
        "#### (Extra Credit) Task 18: Groups are great\n",
        "\n",
        "[*\\[Related section on CoRise\\]*](https://corise.com/course/python-for-data-science/v2/module/data-explore-stats-wsuy7l#corise_cl9wpp3t100n53b6pmv167sm7)\n",
        "\n",
        "Now, let's perform a [`groupby`](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.groupby.html) where we look at the median values of `five_day_dollar_price` and `review_scores_rating` with respect to the `room_type`. Do these results match your intuition?"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "... # YOUR CODE HERE"
      ],
      "metadata": {
        "id": "0wyuhz4wJRgt"
      },
      "id": "0wyuhz4wJRgt",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "<details>\n",
        "  <summary>Show Expected Output\n",
        "  </summary>\n",
        "\n",
        "|room\\_type|review\\_scores\\_rating|five\\_day\\_dollar\\_price|\n",
        "|---|---|---|\n",
        "|Entire home/apt|4\\.88|975\\.0|\n",
        "|Hotel room|4\\.5649999999999995|1110\\.1637785291632|\n",
        "|Private room|4\\.79|710\\.9090909090909|\n",
        "|Shared room|4\\.6|724\\.1052631578947|\n",
        "\n",
        "<details>\n",
        "  <summary>Show Solution </summary>\n",
        "  \n",
        "```\n",
        "final_df.groupby(by=[\"room_type\"])[\n",
        "    [\n",
        "        \"review_scores_rating\",\n",
        "        \"five_day_dollar_price\",\n",
        "    ]\n",
        "].median()\n",
        "```\n",
        "</details>\n",
        "</details>"
      ],
      "metadata": {
        "id": "bxrNncQXggv4"
      },
      "id": "bxrNncQXggv4"
    },
    {
      "cell_type": "markdown",
      "id": "3050a784-f466-48f7-8a39-d86e4be34fcb",
      "metadata": {
        "id": "3050a784-f466-48f7-8a39-d86e4be34fcb"
      },
      "source": [
        "You might have expected that shared rooms are the cheapest and thus have the lowest rating with respect to median scores. The same can't be said for the most expensive option — a hotel room. Will this influence your future considerations when booking 🤔?\n",
        "\n",
        "(But before you let this influence your decisions too much, it might be better to assume that this data might be biased in favor of Airbnb and not hotels in general. 🤷)\n",
        "\n",
        "---"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "You've walked through all the most important parts of Pandas. It's a really easy-to-use library that shares a lot of syntax with NumPy. It is great for analyzing and cleaning datasets, and as you might have discovered with the previous code, Pandas allows you to really go into the nitty-gritty details of your dataset. These skills are invaluable for a data scientist, and will empower you to utilize data where you work now, or even where you could work in the future!\n",
        "\n",
        "The next steps involve downloading the files to your local computer so that you can make an app for your portfolio. After, we will provide some suggestions on how you can extend this project, along with some interesting links to investigate."
      ],
      "metadata": {
        "id": "zskplBMla6zo"
      },
      "id": "zskplBMla6zo"
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Download the Dataset to Your Local Machine\n",
        "\n",
        "Let's first export our final DataFrame."
      ],
      "metadata": {
        "id": "vtFPkCD49UbC"
      },
      "id": "vtFPkCD49UbC"
    },
    {
      "cell_type": "code",
      "source": [
        "final_df.to_csv(\n",
        "    \"WK2_Airbnb_Amsterdam_listings_proj_solution.csv\",\n",
        "    index=True,\n",
        ")"
      ],
      "metadata": {
        "id": "ke4c3potKoCa"
      },
      "id": "ke4c3potKoCa",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "Google Colab comes with its own Python packages that allow us to quickly download generated files, like so:"
      ],
      "metadata": {
        "id": "kVpFcu8ULC8-"
      },
      "id": "kVpFcu8ULC8-"
    },
    {
      "cell_type": "code",
      "source": [
        "from google.colab import files\n",
        "\n",
        "# Download the file locally\n",
        "files.download('WK2_Airbnb_Amsterdam_listings_proj_solution.csv')"
      ],
      "metadata": {
        "id": "x0nVFL3_6bCC"
      },
      "execution_count": null,
      "outputs": [],
      "id": "x0nVFL3_6bCC"
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Make an App for Your Portfolio!\n",
        "\n",
        "<center>\n",
        "  <img src=https://griddb-pro.azureedge.net/en/wp-content/uploads/2021/08/streamlit-1160x650.png width=\"500\" align=\"center\" />\n",
        "</center>\n",
        "<br/>\n",
        "\n",
        "**Participants such as yourselves often want to use the weekly CoRise projects for their portfolios. To facilitate that, we've created this section. It might seem like a lot, but it's actually just following instructions and copy-pasting. Reach out on Slack if you get stuck!** \n",
        "\n",
        "You will make an app that visualizes the dataset as a DataFrame and as a geographic visualization like so:\n",
        "\n",
        "<center>\n",
        "  <img src=https://i.ibb.co/N9JKbd8/Screen-Shot-2022-11-10-at-4-07-17-PM.png width=\"500\" align=\"center\" />\n",
        "</center>\n",
        "<br/>\n",
        "\n",
        "To visualize this, we will again use a library called [Streamlit](https://streamlit.io/). For now you are not expected to know how Streamlit works, but you are expected to be able to copy-paste and follow instructions if you want to share this project as part of your portfolio!\n",
        "\n",
        "We are going to use [Streamlit Share](https://share.streamlit.io/) to host your projects. It's a website that allows us to host our interactive projects for free online! Again, we don't expect you to understand how to use and/or modify the code shown below. We do expect you to read the instructions and copy-paste our code to the Streamlit Share platform. Feel free to change it any way you like. Some great starting points are [here](https://python.plainenglish.io/how-to-build-web-app-using-streamlit-pandas-numpy-5e134f0cf552), [here](https://docs.streamlit.io/library/get-started/create-an-app), [here](https://streamlit.io/components), and [here](https://streamlit.io/gallery)!\n",
        "\n",
        "The `app.py` below is based on [this code](https://github.com/tylerjrichards/st-filter-dataframe/blob/main/streamlit_app.py). Feel free to extend it if you like!"
      ],
      "metadata": {
        "id": "Uk00nl03LJtI"
      },
      "id": "Uk00nl03LJtI"
    },
    {
      "cell_type": "code",
      "source": [
        "%%writefile app.py\n",
        "import pandas as pd\n",
        "import streamlit as st\n",
        "from pandas.api.types import (\n",
        "    is_categorical_dtype,\n",
        "    is_datetime64_any_dtype,\n",
        "    is_numeric_dtype,\n",
        "    is_object_dtype\n",
        ")\n",
        "\n",
        "st.title(\"Filter your Airbnb Listings dataframe!\")\n",
        "\n",
        "st.write(\n",
        "    \"\"\"This app is based on this blog [here](https://blog.streamlit.io/auto-generate-a-dataframe-filtering-ui-in-streamlit-with-filter_dataframe/). \n",
        "    Can you think of ways to extend it with visuals?\n",
        "    \"\"\"\n",
        ")\n",
        "\n",
        "\n",
        "def filter_dataframe(df: pd.DataFrame) -> pd.DataFrame:\n",
        "    \"\"\"\n",
        "    Adds a UI on top of a dataframe to let viewers filter columns\n",
        "    Args:\n",
        "        df (pd.DataFrame): Original dataframe\n",
        "    Returns:\n",
        "        pd.DataFrame: Filtered dataframe\n",
        "    \"\"\"\n",
        "    modify = st.checkbox(\"Add filters\")\n",
        "\n",
        "    if not modify:\n",
        "        return df\n",
        "\n",
        "    df = df.copy()\n",
        "\n",
        "    # Try to convert datetimes into a standard format (datetime, no timezone)\n",
        "    for col in df.columns:\n",
        "        if is_object_dtype(df[col]):\n",
        "            try:\n",
        "                df[col] = pd.to_datetime(df[col])\n",
        "            except Exception:\n",
        "                pass\n",
        "\n",
        "        if is_datetime64_any_dtype(df[col]):\n",
        "            df[col] = df[col].dt.tz_localize(None)\n",
        "\n",
        "    modification_container = st.container()\n",
        "\n",
        "    with modification_container:\n",
        "        to_filter_columns = st.multiselect(\"Filter dataframe on\", df.columns)\n",
        "        for column in to_filter_columns:\n",
        "            left, right = st.columns((1, 20))\n",
        "            left.write(\"↳\")\n",
        "            # Treat columns with < 10 unique values as categorical\n",
        "            if is_categorical_dtype(df[column]) or df[column].nunique() < 10:\n",
        "                user_cat_input = right.multiselect(\n",
        "                    f\"Values for {column}\",\n",
        "                    df[column].unique(),\n",
        "                    default=list(df[column].unique()),\n",
        "                )\n",
        "                df = df[df[column].isin(user_cat_input)]\n",
        "            elif is_numeric_dtype(df[column]):\n",
        "                _min = float(df[column].min())\n",
        "                _max = float(df[column].max())\n",
        "                step = (_max - _min) / 100\n",
        "                user_num_input = right.slider(\n",
        "                    f\"Values for {column}\",\n",
        "                    _min,\n",
        "                    _max,\n",
        "                    (_min, _max),\n",
        "                    step=step,\n",
        "                )\n",
        "                df = df[df[column].between(*user_num_input)]\n",
        "            elif is_datetime64_any_dtype(df[column]):\n",
        "                user_date_input = right.date_input(\n",
        "                    f\"Values for {column}\",\n",
        "                    value=(\n",
        "                        df[column].min(),\n",
        "                        df[column].max(),\n",
        "                    ),\n",
        "                )\n",
        "                if len(user_date_input) == 2:\n",
        "                    user_date_input = tuple(map(pd.to_datetime, user_date_input))\n",
        "                    start_date, end_date = user_date_input\n",
        "                    df = df.loc[df[column].between(start_date, end_date)]\n",
        "            else:\n",
        "                user_text_input = right.text_input(\n",
        "                    f\"Substring or regex in {column}\",\n",
        "                )\n",
        "                if user_text_input:\n",
        "                    df = df[df[column].str.contains(user_text_input)]\n",
        "\n",
        "    return df\n",
        "\n",
        "\n",
        "df = pd.read_csv(\n",
        "    \"WK2_Airbnb_Amsterdam_listings_proj_solution.csv\", index_col=0\n",
        ")\n",
        "st.dataframe(filter_dataframe(df))"
      ],
      "metadata": {
        "id": "awNqC9ezLitX"
      },
      "id": "awNqC9ezLitX",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "The **%%writefile [FILE_NAME].[FILE_EXTENSION]** command let's us save the code written in the cells in your Google Colab instance. Having it saved like that enables us to download it as a file, as seen below."
      ],
      "metadata": {
        "id": "HV5Os71FhUFA"
      },
      "id": "HV5Os71FhUFA"
    },
    {
      "cell_type": "code",
      "source": [
        "from google.colab import files\n",
        "\n",
        "# Download the file locally\n",
        "files.download('app.py')"
      ],
      "metadata": {
        "id": "2MAXRGePLeAR"
      },
      "id": "2MAXRGePLeAR",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "%%writefile requirements.txt\n",
        "pandas\n",
        "streamlit"
      ],
      "metadata": {
        "id": "qTjHh6dwLcdy"
      },
      "id": "qTjHh6dwLcdy",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "from google.colab import files\n",
        "\n",
        "# Download the file locally\n",
        "files.download('requirements.txt')"
      ],
      "metadata": {
        "id": "AXjHxtJXNVZf"
      },
      "execution_count": null,
      "outputs": [],
      "id": "AXjHxtJXNVZf"
    },
    {
      "cell_type": "markdown",
      "source": [
        "Please verify that you've downloaded three files:\n",
        "- `WK2_Airbnb_Amsterdam_listings_proj_solution.csv`\n",
        "- `app.py`\n",
        "- `requirements.txt`\n",
        "\n",
        "Now let's head over to GitHub and [create an account](https://github.com/signup).\n",
        "\n",
        "Then, since you are logged in, [go to GitHub.com](https://github.com) and click on the **+** icon at the top-right corner and select **New repository**.\n",
        "\n",
        "<center>\n",
        "  <img src=https://i.ibb.co/4gkPBCp/Screen-Shot-2022-11-28-at-1-51-02-PM.png width=\"300\" align=\"center\" />\n",
        "</center>\n",
        "<br/>\n",
        "\n",
        "Here you provide:\n",
        "- **Repository name**: Up to you\n",
        "- **License**: Up to you. We recommend **apache-2.0**.\n",
        "\n",
        "- **Public or private?** Public, otherwise you can't host it on [Streamlit Share](https://share.streamlit.io)!\n",
        "\n",
        "<center>\n",
        "  <img src=https://i.ibb.co/0B533dw/Screen-Shot-2022-11-28-at-1-55-14-PM.png width=\"450\" align=\"center\" />\n",
        "</center>\n",
        "<br/>\n",
        "\n",
        "Then upload the three files to this URL below. ***Please modify it before copy-pasting it***.\n",
        "\n",
        "```https://github.com/[YOUR_ACCOUNT_NAME]/[YOUR_REPOSITORY_NAME]/upload/main```\n",
        "\n",
        "<center>\n",
        "  <img src=https://i.ibb.co/jTsrgJw/Screen-Shot-2022-11-28-at-1-58-31-PM.png width=\"500\" align=\"center\" />\n",
        "</center>\n",
        "<br/>\n",
        "\n",
        "Commit directly to the `main` branch, then click **Commit changes**.\n",
        "\n",
        "Next, you have to create an account on [Streamlit Share](https://share.streamlit.io/signup). \n",
        "\n",
        "<center>\n",
        "  <img src=https://i.ibb.co/znFngJc/Screen-Shot-2022-11-28-at-1-59-47-PM.png width=\"500\" align=\"center\" />\n",
        "</center>\n",
        "<br/>\n",
        "\n",
        "It's recommended that you click **Continue with GitHub**. \n",
        "\n",
        "Then, select **New app** **>** **Deploy a new app...** **>** **From existing repo**.\n",
        "\n",
        "<center>\n",
        "  <img src=https://i.ibb.co/VQPQzt3/Screen-Shot-2022-11-28-at-2-05-04-PM.png width=\"500\" align=\"center\" />\n",
        "</center>\n",
        "\n",
        "Followed by providing your:\n",
        "\n",
        "```[GITHUB_ACCOUNT_NAME]/[GITHUB_REPOSITORY]```\n",
        "\n",
        "and your `streamlit_app.py` as `app.py`\n",
        "<center>\n",
        "  <img src=https://i.ibb.co/PDSQccD/Screen-Shot-2022-11-28-at-2-10-47-PM.png width=\"500\" align=\"center\" />\n",
        "</center>\n",
        "\n",
        "You will have to wait around 1-5 minutes, then an automatic hyperlink is generated for your new website. An example is this app:\n",
        "\n",
        "```https://[GITHUB_ACCOUNT_NAME]-[GITHUB_REPOSITORY]-[RANDOM_6_LETTER_STRING].streamlit.app/```\n",
        "\n",
        "***Please modify the link before copy-pasting it.***"
      ],
      "metadata": {
        "id": "pkmVcoiWQL6s"
      },
      "id": "pkmVcoiWQL6s"
    },
    {
      "cell_type": "markdown",
      "id": "cd9b1585-367b-42ed-8a6c-8d6c7eac41eb",
      "metadata": {
        "id": "cd9b1585-367b-42ed-8a6c-8d6c7eac41eb"
      },
      "source": [
        "---\n",
        "\n",
        "# 🎉 CONGRATULATIONS!!!\n",
        "\n",
        "You've made it to the end of the Week 2 assignment! You should be proud. \n",
        "\n",
        "If you have any lingering questions, post them on Slack! As you know, we're always here to help.\n",
        "\n",
        "And if you want any additional challenge questions, check out the bonus extensions below.\n",
        "\n",
        "---\n",
        "\n",
        "## Extensions (Optional)\n",
        "\n",
        "<center>\n",
        "  <img src=https://raw.githubusercontent.com/aschonfeld/dtale-media/master/images/Title.png width=\"500\" align=\"center\" />\n",
        "</center>\n",
        "<br/>\n",
        "\n",
        "Awesome 🎉🎉🎉 you are finished with all the tasks! How about considering running [D-Tale](https://github.com/man-group/dtale) on your DataFrames, so you can interactively explore your dataset? What about getting a visual representation of the data and thus a quick overview of what's goin' on by using [Lux](https://github.com/lux-org/lux) or [Sweetviz](https://pypi.org/project/sweetviz/)?\n",
        "\n",
        "If optimizing code gets you excited, then try to see how you can maximize these performance numbers even more. Maybe it's time to try running this code using [cuDF](https://github.com/rapidsai/cudf), [Polars](https://github.com/pola-rs/polars), or [Vaex](https://github.com/vaexio/vaex)?\n",
        "\n",
        "# Next Up?\n",
        "This is the last project of our course 😭😭. Please do not be sad! We would like to invite you to read the \"What's Next?\" post in Week 3, as it contains great suggestions on how you can proceed with your learning journey at CoRise (yes, please!) or somewhere else!"
      ]
    }
  ],
  "metadata": {
    "kernelspec": {
      "display_name": "Python 3 (ipykernel)",
      "language": "python",
      "name": "python3"
    },
    "language_info": {
      "codemirror_mode": {
        "name": "ipython",
        "version": 3
      },
      "file_extension": ".py",
      "mimetype": "text/x-python",
      "name": "python",
      "nbconvert_exporter": "python",
      "pygments_lexer": "ipython3",
      "version": "3.10.4"
    },
    "colab": {
      "provenance": [],
      "include_colab_link": true
    }
  },
  "nbformat": 4,
  "nbformat_minor": 5
}