data_cleaning_with_python-checkpoint.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "QNIdiGfl7W3U"
   },
   "source": [
    "## Data Cleaning with Python\n",
    "\n",
    "by: Serhii Viskushenko"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "rDIALHauqzGu"
   },
   "source": [
    "## Load libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "id": "MY3TiCty7uVP"
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import datetime as dt"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "qnGfIdZqq3KF"
   },
   "source": [
    "## Load data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "id": "Tr3ZGVo7vu3a"
   },
   "outputs": [],
   "source": [
    "# data from github repo: https://github.com/dyvenia/data_cleaning\n",
    "url = 'https://raw.githubusercontent.com/dyvenia/lms/data_cleaning/data_cleaning/dataset.csv?token=GHSAT0AAAAAABZPXVXN6MSAUMN73MYDSIMIY2X6XLQ'\n",
    "df = pd.read_csv(url)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Explore data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 464
    },
    "id": "TeSPCN8QrFcP",
    "outputId": "39f942ad-f44c-497c-a449-57950c6ff40c"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<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>NamE</th>\n",
       "      <th>host ID</th>\n",
       "      <th>host_name</th>\n",
       "      <th>neighbourhood_group</th>\n",
       "      <th>neighbourhood</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>Room Type</th>\n",
       "      <th>price</th>\n",
       "      <th>minimum_nights</th>\n",
       "      <th>number_of_reviews</th>\n",
       "      <th>LAST review</th>\n",
       "      <th>reviews_per_month</th>\n",
       "      <th>calculated_host_listings_count</th>\n",
       "      <th>availability_365</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2539</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2787</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Brooklyn</td>\n",
       "      <td>Kensington</td>\n",
       "      <td>40.64749</td>\n",
       "      <td>-73.97237</td>\n",
       "      <td>Private room</td>\n",
       "      <td>$149</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>43392</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2595</td>\n",
       "      <td>Skylit Midtown Castle</td>\n",
       "      <td>2845</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>Midtown</td>\n",
       "      <td>40.75362</td>\n",
       "      <td>-73.98377</td>\n",
       "      <td>Entire home/apt</td>\n",
       "      <td>$225</td>\n",
       "      <td>1</td>\n",
       "      <td>45</td>\n",
       "      <td>43606</td>\n",
       "      <td>0.38</td>\n",
       "      <td>2</td>\n",
       "      <td>355</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3831</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4869</td>\n",
       "      <td>LisaRoxanne</td>\n",
       "      <td>Brooklyn</td>\n",
       "      <td>Clinton Hill</td>\n",
       "      <td>40.68514</td>\n",
       "      <td>-73.95976</td>\n",
       "      <td>Entire home/apt</td>\n",
       "      <td>$89</td>\n",
       "      <td>1</td>\n",
       "      <td>270</td>\n",
       "      <td>43651</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>194</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5022</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7192</td>\n",
       "      <td>Laura</td>\n",
       "      <td>Manhatan</td>\n",
       "      <td>East Harlem</td>\n",
       "      <td>40.79851</td>\n",
       "      <td>-73.94399</td>\n",
       "      <td>Entire home/apt</td>\n",
       "      <td>$80</td>\n",
       "      <td>10</td>\n",
       "      <td>9</td>\n",
       "      <td>43423</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5099</td>\n",
       "      <td>Large Cozy 1 BR Apartment In Midtown East</td>\n",
       "      <td>7322</td>\n",
       "      <td>Chris</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>Murray Hill</td>\n",
       "      <td>40.74767</td>\n",
       "      <td>-73.97500</td>\n",
       "      <td>Entire home/apt</td>\n",
       "      <td>$200</td>\n",
       "      <td>3</td>\n",
       "      <td>74</td>\n",
       "      <td>43638</td>\n",
       "      <td>0.59</td>\n",
       "      <td>1</td>\n",
       "      <td>129</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     id                                       NamE  host ID    host_name  \\\n",
       "0  2539                                        NaN     2787          NaN   \n",
       "1  2595                      Skylit Midtown Castle     2845          NaN   \n",
       "2  3831                                        NaN     4869  LisaRoxanne   \n",
       "3  5022                                        NaN     7192        Laura   \n",
       "4  5099  Large Cozy 1 BR Apartment In Midtown East     7322        Chris   \n",
       "\n",
       "  neighbourhood_group neighbourhood  latitude  longitude        Room Type  \\\n",
       "0            Brooklyn    Kensington  40.64749  -73.97237     Private room   \n",
       "1           Manhattan       Midtown  40.75362  -73.98377  Entire home/apt   \n",
       "2            Brooklyn  Clinton Hill  40.68514  -73.95976  Entire home/apt   \n",
       "3            Manhatan   East Harlem  40.79851  -73.94399  Entire home/apt   \n",
       "4           Manhattan   Murray Hill  40.74767  -73.97500  Entire home/apt   \n",
       "\n",
       "  price  minimum_nights  number_of_reviews  LAST review  reviews_per_month  \\\n",
       "0  $149               1                  9        43392                NaN   \n",
       "1  $225               1                 45        43606               0.38   \n",
       "2   $89               1                270        43651                NaN   \n",
       "3   $80              10                  9        43423                NaN   \n",
       "4  $200               3                 74        43638               0.59   \n",
       "\n",
       "   calculated_host_listings_count  availability_365  \n",
       "0                               6               365  \n",
       "1                               2               355  \n",
       "2                               1               194  \n",
       "3                               1                 0  \n",
       "4                               1               129  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#This function returns the first n rows (default 5) for the object based on position. It is useful for quickly testing if your object has the right type of data in it.\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 516
    },
    "id": "b1KxzMxD0i8I",
    "outputId": "70f58600-72b3-49ee-a78d-c56f465a748b"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<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>NamE</th>\n",
       "      <th>host ID</th>\n",
       "      <th>host_name</th>\n",
       "      <th>neighbourhood_group</th>\n",
       "      <th>neighbourhood</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>Room Type</th>\n",
       "      <th>price</th>\n",
       "      <th>minimum_nights</th>\n",
       "      <th>number_of_reviews</th>\n",
       "      <th>LAST review</th>\n",
       "      <th>reviews_per_month</th>\n",
       "      <th>calculated_host_listings_count</th>\n",
       "      <th>availability_365</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>38842</th>\n",
       "      <td>36425863</td>\n",
       "      <td>Lovely Privet Bedroom with Privet Restroom</td>\n",
       "      <td>83554966</td>\n",
       "      <td>Rusaa</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>Upper East Side</td>\n",
       "      <td>40.78099</td>\n",
       "      <td>-73.95366</td>\n",
       "      <td>Private room</td>\n",
       "      <td>$129</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>43653</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>147</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38843</th>\n",
       "      <td>36427429</td>\n",
       "      <td>No.2 with queen size bed</td>\n",
       "      <td>257683179</td>\n",
       "      <td>H Ai</td>\n",
       "      <td>Queens</td>\n",
       "      <td>Flushing</td>\n",
       "      <td>40.75104</td>\n",
       "      <td>-73.81459</td>\n",
       "      <td>Private room</td>\n",
       "      <td>$45</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>43653</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6</td>\n",
       "      <td>339</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38844</th>\n",
       "      <td>36438336</td>\n",
       "      <td>Seas The Moment</td>\n",
       "      <td>211644523</td>\n",
       "      <td>Ben</td>\n",
       "      <td>Staten Island</td>\n",
       "      <td>Great Kills</td>\n",
       "      <td>40.54179</td>\n",
       "      <td>-74.14275</td>\n",
       "      <td>Private room</td>\n",
       "      <td>$235</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>43653</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38845</th>\n",
       "      <td>36442252</td>\n",
       "      <td>1B-1B apartment near by Metro</td>\n",
       "      <td>273841667</td>\n",
       "      <td>Blaine</td>\n",
       "      <td>Bronx</td>\n",
       "      <td>Mott Haven</td>\n",
       "      <td>40.80787</td>\n",
       "      <td>-73.92400</td>\n",
       "      <td>Entire home/apt</td>\n",
       "      <td>$100</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>43653</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38846</th>\n",
       "      <td>36455809</td>\n",
       "      <td>Cozy Private Room in Bushwick, Brooklyn</td>\n",
       "      <td>74162901</td>\n",
       "      <td>Christine</td>\n",
       "      <td>Brooklyn</td>\n",
       "      <td>Bushwick</td>\n",
       "      <td>40.69805</td>\n",
       "      <td>-73.92801</td>\n",
       "      <td>Private room</td>\n",
       "      <td>$30</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>43654</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             id                                        NamE    host ID  \\\n",
       "38842  36425863  Lovely Privet Bedroom with Privet Restroom   83554966   \n",
       "38843  36427429                    No.2 with queen size bed  257683179   \n",
       "38844  36438336                             Seas The Moment  211644523   \n",
       "38845  36442252               1B-1B apartment near by Metro  273841667   \n",
       "38846  36455809     Cozy Private Room in Bushwick, Brooklyn   74162901   \n",
       "\n",
       "       host_name neighbourhood_group    neighbourhood  latitude  longitude  \\\n",
       "38842      Rusaa           Manhattan  Upper East Side  40.78099  -73.95366   \n",
       "38843       H Ai              Queens         Flushing  40.75104  -73.81459   \n",
       "38844        Ben       Staten Island      Great Kills  40.54179  -74.14275   \n",
       "38845     Blaine               Bronx       Mott Haven  40.80787  -73.92400   \n",
       "38846  Christine            Brooklyn         Bushwick  40.69805  -73.92801   \n",
       "\n",
       "             Room Type price  minimum_nights  number_of_reviews  LAST review  \\\n",
       "38842     Private room  $129               1                  1        43653   \n",
       "38843     Private room   $45               1                  1        43653   \n",
       "38844     Private room  $235               1                  1        43653   \n",
       "38845  Entire home/apt  $100               1                  2        43653   \n",
       "38846     Private room   $30               1                  1        43654   \n",
       "\n",
       "       reviews_per_month  calculated_host_listings_count  availability_365  \n",
       "38842                1.0                               1               147  \n",
       "38843                1.0                               6               339  \n",
       "38844                1.0                               1                87  \n",
       "38845                2.0                               1                40  \n",
       "38846                1.0                               1                 1  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#This function returns last n rows (default 5) from the object based on position. It is useful for quickly verifying data, for example, after sorting or appending rows.\n",
    "df.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 516
    },
    "id": "i3yP7FR50KdZ",
    "outputId": "7b6c9eb3-a3ea-4350-b191-11355cb2c4d4",
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<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>NamE</th>\n",
       "      <th>host ID</th>\n",
       "      <th>host_name</th>\n",
       "      <th>neighbourhood_group</th>\n",
       "      <th>neighbourhood</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>Room Type</th>\n",
       "      <th>price</th>\n",
       "      <th>minimum_nights</th>\n",
       "      <th>number_of_reviews</th>\n",
       "      <th>LAST review</th>\n",
       "      <th>reviews_per_month</th>\n",
       "      <th>calculated_host_listings_count</th>\n",
       "      <th>availability_365</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>7518</th>\n",
       "      <td>6571441</td>\n",
       "      <td>Brooklyn Brownstone Master Bedroom</td>\n",
       "      <td>31458100</td>\n",
       "      <td>Drew</td>\n",
       "      <td>Brooklyn</td>\n",
       "      <td>Bedford-Stuyvesant</td>\n",
       "      <td>40.68527</td>\n",
       "      <td>-73.95307</td>\n",
       "      <td>Private room</td>\n",
       "      <td>$53</td>\n",
       "      <td>2</td>\n",
       "      <td>11</td>\n",
       "      <td>42471</td>\n",
       "      <td>0.23</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32030</th>\n",
       "      <td>30084221</td>\n",
       "      <td>Live in the heart of East Village, Manhatan</td>\n",
       "      <td>44642799</td>\n",
       "      <td>Judith</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>East Village</td>\n",
       "      <td>40.72818</td>\n",
       "      <td>-73.98075</td>\n",
       "      <td>Entire home/apt</td>\n",
       "      <td>$225</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>43468</td>\n",
       "      <td>0.31</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38670</th>\n",
       "      <td>35916489</td>\n",
       "      <td>Sun-drenched LGBT Cozy Private QueenRoom @Bush...</td>\n",
       "      <td>135563904</td>\n",
       "      <td>Michael</td>\n",
       "      <td>Queens</td>\n",
       "      <td>Ridgewood</td>\n",
       "      <td>40.70550</td>\n",
       "      <td>-73.90960</td>\n",
       "      <td>Private room</td>\n",
       "      <td>$180</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>43647</td>\n",
       "      <td>2.00</td>\n",
       "      <td>3</td>\n",
       "      <td>78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34207</th>\n",
       "      <td>31898844</td>\n",
       "      <td>Eddies place #2</td>\n",
       "      <td>98214533</td>\n",
       "      <td>Edwin’S Place</td>\n",
       "      <td>Staten Island</td>\n",
       "      <td>Huguenot</td>\n",
       "      <td>40.53982</td>\n",
       "      <td>-74.17280</td>\n",
       "      <td>Entire home/apt</td>\n",
       "      <td>$75</td>\n",
       "      <td>2</td>\n",
       "      <td>20</td>\n",
       "      <td>43653</td>\n",
       "      <td>4.38</td>\n",
       "      <td>2</td>\n",
       "      <td>229</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28593</th>\n",
       "      <td>27018851</td>\n",
       "      <td>Bright and Spacious Apartment</td>\n",
       "      <td>32866463</td>\n",
       "      <td>Bassam</td>\n",
       "      <td>Brooklyn</td>\n",
       "      <td>Bay Ridge</td>\n",
       "      <td>40.63328</td>\n",
       "      <td>-74.01978</td>\n",
       "      <td>Entire home/apt</td>\n",
       "      <td>$145</td>\n",
       "      <td>1</td>\n",
       "      <td>41</td>\n",
       "      <td>43639</td>\n",
       "      <td>3.63</td>\n",
       "      <td>2</td>\n",
       "      <td>139</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             id                                               NamE    host ID  \\\n",
       "7518    6571441                 Brooklyn Brownstone Master Bedroom   31458100   \n",
       "32030  30084221        Live in the heart of East Village, Manhatan   44642799   \n",
       "38670  35916489  Sun-drenched LGBT Cozy Private QueenRoom @Bush...  135563904   \n",
       "34207  31898844                                    Eddies place #2   98214533   \n",
       "28593  27018851                      Bright and Spacious Apartment   32866463   \n",
       "\n",
       "           host_name neighbourhood_group       neighbourhood  latitude  \\\n",
       "7518            Drew            Brooklyn  Bedford-Stuyvesant  40.68527   \n",
       "32030         Judith           Manhattan        East Village  40.72818   \n",
       "38670        Michael              Queens           Ridgewood  40.70550   \n",
       "34207  Edwin’S Place       Staten Island            Huguenot  40.53982   \n",
       "28593         Bassam            Brooklyn           Bay Ridge  40.63328   \n",
       "\n",
       "       longitude        Room Type price  minimum_nights  number_of_reviews  \\\n",
       "7518   -73.95307     Private room   $53               2                 11   \n",
       "32030  -73.98075  Entire home/apt  $225               3                  2   \n",
       "38670  -73.90960     Private room  $180               1                  2   \n",
       "34207  -74.17280  Entire home/apt   $75               2                 20   \n",
       "28593  -74.01978  Entire home/apt  $145               1                 41   \n",
       "\n",
       "       LAST review  reviews_per_month  calculated_host_listings_count  \\\n",
       "7518         42471               0.23                               1   \n",
       "32030        43468               0.31                               1   \n",
       "38670        43647               2.00                               3   \n",
       "34207        43653               4.38                               2   \n",
       "28593        43639               3.63                               2   \n",
       "\n",
       "       availability_365  \n",
       "7518                  0  \n",
       "32030                 0  \n",
       "38670                78  \n",
       "34207               229  \n",
       "28593               139  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Return a random sample of items from an axis of object. (default 1)\n",
    "df.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "TOND6uRpT-PF",
    "outputId": "0abda99f-0cb3-48bc-f9df-67c2f460c51c"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(38847, 16)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Return a tuple representing the dimensionality of the DataFrame.\n",
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "qGSud0K31NWM",
    "outputId": "76ff36ea-c62a-4026-8b8d-5d227daff414"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 38847 entries, 0 to 38846\n",
      "Data columns (total 16 columns):\n",
      " #   Column                          Non-Null Count  Dtype  \n",
      "---  ------                          --------------  -----  \n",
      " 0   id                              38847 non-null  int64  \n",
      " 1   NamE                            37133 non-null  object \n",
      " 2   host ID                         38847 non-null  int64  \n",
      " 3   host_name                       38522 non-null  object \n",
      " 4   neighbourhood_group             38847 non-null  object \n",
      " 5   neighbourhood                   38847 non-null  object \n",
      " 6   latitude                        38847 non-null  float64\n",
      " 7   longitude                       38847 non-null  float64\n",
      " 8   Room Type                       38847 non-null  object \n",
      " 9   price                           38847 non-null  object \n",
      " 10  minimum_nights                  38847 non-null  int64  \n",
      " 11  number_of_reviews               38847 non-null  int64  \n",
      " 12  LAST review                     38847 non-null  int64  \n",
      " 13  reviews_per_month               38812 non-null  float64\n",
      " 14  calculated_host_listings_count  38847 non-null  int64  \n",
      " 15  availability_365                38847 non-null  int64  \n",
      "dtypes: float64(3), int64(7), object(6)\n",
      "memory usage: 4.7+ MB\n"
     ]
    }
   ],
   "source": [
    "#This method prints information about a DataFrame including the index dtype and columns, non-null values and memory usage.\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "gL1MHJNm13VG",
    "outputId": "028fa2f4-9e6e-464f-c7cb-993486fda4c4"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "id                                  int64\n",
       "NamE                               object\n",
       "host ID                             int64\n",
       "host_name                          object\n",
       "neighbourhood_group                object\n",
       "neighbourhood                      object\n",
       "latitude                          float64\n",
       "longitude                         float64\n",
       "Room Type                          object\n",
       "price                              object\n",
       "minimum_nights                      int64\n",
       "number_of_reviews                   int64\n",
       "LAST review                         int64\n",
       "reviews_per_month                 float64\n",
       "calculated_host_listings_count      int64\n",
       "availability_365                    int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Return the dtypes in the DataFrame.\n",
    "df.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "rv4r9ebssu7k"
   },
   "source": [
    "## Renaming columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "AzpGRpLSrH5V",
    "outputId": "c4062234-3279-498c-8cb7-5f2477236c08"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['id', 'NamE', 'host ID', 'host_name', 'neighbourhood_group',\n",
       "       'neighbourhood', 'latitude', 'longitude', 'Room Type', 'price',\n",
       "       'minimum_nights', 'number_of_reviews', 'LAST review',\n",
       "       'reviews_per_month', 'calculated_host_listings_count',\n",
       "       'availability_365'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# The column labels of the DataFrame.\n",
    "df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "c1mUuiiL23m-",
    "outputId": "80e37819-a8c1-4ec1-b8b4-d335a3b41dc4"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['id',\n",
       " 'NamE',\n",
       " 'host ID',\n",
       " 'host_name',\n",
       " 'neighbourhood_group',\n",
       " 'neighbourhood',\n",
       " 'latitude',\n",
       " 'longitude',\n",
       " 'Room Type',\n",
       " 'price',\n",
       " 'minimum_nights',\n",
       " 'number_of_reviews',\n",
       " 'LAST review',\n",
       " 'reviews_per_month',\n",
       " 'calculated_host_listings_count',\n",
       " 'availability_365']"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Turn into list\n",
    "df.columns.to_list()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "3RTkBBQD7bM4"
   },
   "source": [
    "### String methods"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "ZcEfyftBtW1L",
    "outputId": "b09d1e6d-dcce-4b86-dcdd-abae387703f9"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['id',\n",
       " 'name',\n",
       " 'host_id',\n",
       " 'host_name',\n",
       " 'neighbourhood_group',\n",
       " 'neighbourhood',\n",
       " 'latitude',\n",
       " 'longitude',\n",
       " 'room_type',\n",
       " 'price',\n",
       " 'minimum_nights',\n",
       " 'number_of_reviews',\n",
       " 'last_review',\n",
       " 'reviews_per_month',\n",
       " 'calculated_host_listings_count',\n",
       " 'availability_365']"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# replace whitespace with underscore '_'\n",
    "df.columns = df.columns.str.replace(' ', \"_\")\n",
    "\n",
    "# lower case column names\n",
    "df.columns = df.columns.str.lower()\n",
    "\n",
    "# See the result\n",
    "df.columns.to_list()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "MxqIUh3c7eBn"
   },
   "source": [
    "### Renaming columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "_-U4o250tGAL",
    "outputId": "db038763-aa03-458f-b017-9c1e5de43934"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['id', 'name', 'host_id', 'host_name', 'neighbourhood_group',\n",
       "       'neighbourhood', 'latitude', 'longitude', 'room_type', 'price',\n",
       "       'minimum_nights', 'number_of_reviews', 'last_review_date',\n",
       "       'reviews_per_month', 'listings_count', 'availability_365'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#The rename() method allows you to relabel an axis based on some mapping (a dict or Series) or an arbitrary function.\n",
    "#Requires a dict with 'old_name':'new_name'\n",
    "new_names = {'last_review':'last_review_date',\n",
    "             'calculated_host_listings_count':'listings_count'}\n",
    "\n",
    "df.rename(columns = new_names).columns "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['id', 'name', 'host_id', 'host_name', 'neighbourhood_group',\n",
       "       'neighbourhood', 'latitude', 'longitude', 'room_type', 'price',\n",
       "       'minimum_nights', 'number_of_reviews', 'last_review',\n",
       "       'reviews_per_month', 'calculated_host_listings_count',\n",
       "       'availability_365'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Without inplace=True, not updating dataframe. Only returning output.\n",
    "df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "KQdWE2D9xD7Q",
    "outputId": "34668d61-fe3e-42f6-f7d9-8266066407c1"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['id', 'name', 'host_id', 'host_name', 'neighbourhood_group',\n",
       "       'neighbourhood', 'latitude', 'longitude', 'room_type', 'price',\n",
       "       'minimum_nights', 'number_of_reviews', 'last_review_date',\n",
       "       'reviews_per_month', 'listings_count', 'availability_365'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#The rename() method also provides an inplace named parameter that is by default False and copies the underlying data. Pass inplace=True to rename the data in place.\n",
    "\n",
    "df.rename(columns = new_names, inplace = True)\n",
    "df.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "mkvN-xoLydRd"
   },
   "source": [
    "## Remove junk from values\n",
    "\n",
    "We will remove dollar sign from the price field"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "NshgknrzkmYE",
    "outputId": "bf74dae5-9302-458f-b936-df32b166058d"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    $149\n",
       "1    $225\n",
       "2     $89\n",
       "3     $80\n",
       "4    $200\n",
       "Name: price, dtype: object"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# looking at the data\n",
    "df['price'].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "FXEyBl9FydFm",
    "outputId": "84d74da9-9818-44a2-d1f3-b64f492e3c7e"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    149\n",
       "1    225\n",
       "2     89\n",
       "3     80\n",
       "4    200\n",
       "Name: price, dtype: object"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Removing $ sign\n",
    "df['price'] = df['price'].str.replace(\"$\", \"\", regex=True)\n",
    "df['price'].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "CUu8PJSPJatP",
    "outputId": "dadf4026-8982-4117-df47-c8cb30abecc9"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dtype('O')"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check price column data type\n",
    "df['price'].dtype"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "S3rssiXpJcjv",
    "tags": []
   },
   "source": [
    "## Converting data types\n",
    "[Article about data types](https://pbpython.com/pandas_dtypes.html)\n",
    "\n",
    "| Pandas dtype    | Python type  | NumPy type                                                      | Usage                                        |\n",
    "| --------------- | ------------ | --------------------------------------------------------------- | -------------------------------------------- |\n",
    "| object          | str or mixed | string\\_, unicode\\_, mixed types                                | Text or mixed numeric and non-numeric values |\n",
    "| int64           | int          | int\\_, int8, int16, int32, int64, uint8, uint16, uint32, uint64 | Integer numbers                              |\n",
    "| float64         | float        | float\\_, float16, float32, float64                              | Floating point numbers                       |\n",
    "| bool            | bool         | bool\\_                                                          | True/False values                            |\n",
    "| datetime64      | NA           | datetime64\\[ns\\]                                                | Date and time values                         |\n",
    "| timedelta\\[ns\\] | NA           | NA                                                              | Differences between two datetimes            |\n",
    "| category        | NA           | NA                                                              | Finite list of text values                   |\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "r7HsAlNPT8Ym"
   },
   "source": [
    "### Converting to numeric"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "-WGXOfXdJhpf",
    "outputId": "cf8fec81-73b9-423c-f8a0-67ec4d01f3ec"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dtype('int64')"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['price'] = pd.to_numeric(df['price'])\n",
    "df['price'].dtype"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 134
    },
    "id": "3RsfaehAm-U1",
    "outputId": "b383b099-c302-4eb9-9b58-7a47ed327b9f"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<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>name</th>\n",
       "      <th>host_id</th>\n",
       "      <th>host_name</th>\n",
       "      <th>neighbourhood_group</th>\n",
       "      <th>neighbourhood</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>room_type</th>\n",
       "      <th>price</th>\n",
       "      <th>minimum_nights</th>\n",
       "      <th>number_of_reviews</th>\n",
       "      <th>last_review_date</th>\n",
       "      <th>reviews_per_month</th>\n",
       "      <th>listings_count</th>\n",
       "      <th>availability_365</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>12089</th>\n",
       "      <td>11343196</td>\n",
       "      <td>Charming Studio in Great Location</td>\n",
       "      <td>5368671</td>\n",
       "      <td>Kevin</td>\n",
       "      <td>Brooklyn</td>\n",
       "      <td>Cobble Hill</td>\n",
       "      <td>40.68963</td>\n",
       "      <td>-73.99259</td>\n",
       "      <td>Entire home/apt</td>\n",
       "      <td>94</td>\n",
       "      <td>1</td>\n",
       "      <td>39</td>\n",
       "      <td>43618</td>\n",
       "      <td>0.98</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20101</th>\n",
       "      <td>19424191</td>\n",
       "      <td>Lovely studio in Midtown Manhattan</td>\n",
       "      <td>120762452</td>\n",
       "      <td>Stanley</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>Murray Hill</td>\n",
       "      <td>40.74857</td>\n",
       "      <td>-73.97722</td>\n",
       "      <td>Entire home/apt</td>\n",
       "      <td>150</td>\n",
       "      <td>30</td>\n",
       "      <td>5</td>\n",
       "      <td>43519</td>\n",
       "      <td>0.26</td>\n",
       "      <td>50</td>\n",
       "      <td>344</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20705</th>\n",
       "      <td>19914209</td>\n",
       "      <td>Luxury Private Bedroom (near JFK)</td>\n",
       "      <td>141052003</td>\n",
       "      <td>Nicole</td>\n",
       "      <td>Queens</td>\n",
       "      <td>Jamaica</td>\n",
       "      <td>40.68558</td>\n",
       "      <td>-73.78018</td>\n",
       "      <td>Private room</td>\n",
       "      <td>65</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>43369</td>\n",
       "      <td>0.42</td>\n",
       "      <td>4</td>\n",
       "      <td>155</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26462</th>\n",
       "      <td>24695406</td>\n",
       "      <td>Spacious Private Basement Apartment</td>\n",
       "      <td>98157128</td>\n",
       "      <td>Michael</td>\n",
       "      <td>Bronx</td>\n",
       "      <td>Wakefield</td>\n",
       "      <td>40.88764</td>\n",
       "      <td>-73.85580</td>\n",
       "      <td>Entire home/apt</td>\n",
       "      <td>100</td>\n",
       "      <td>2</td>\n",
       "      <td>38</td>\n",
       "      <td>43635</td>\n",
       "      <td>2.66</td>\n",
       "      <td>1</td>\n",
       "      <td>107</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7237</th>\n",
       "      <td>6322937</td>\n",
       "      <td>NaN</td>\n",
       "      <td>80447</td>\n",
       "      <td>Francesca</td>\n",
       "      <td>Queens</td>\n",
       "      <td>Long Island City</td>\n",
       "      <td>40.76450</td>\n",
       "      <td>-73.93181</td>\n",
       "      <td>Entire home/apt</td>\n",
       "      <td>115</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>42659</td>\n",
       "      <td>0.13</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             id                                 name    host_id  host_name  \\\n",
       "12089  11343196    Charming Studio in Great Location    5368671      Kevin   \n",
       "20101  19424191   Lovely studio in Midtown Manhattan  120762452    Stanley   \n",
       "20705  19914209    Luxury Private Bedroom (near JFK)  141052003     Nicole   \n",
       "26462  24695406  Spacious Private Basement Apartment   98157128    Michael   \n",
       "7237    6322937                                  NaN      80447  Francesca   \n",
       "\n",
       "      neighbourhood_group     neighbourhood  latitude  longitude  \\\n",
       "12089            Brooklyn       Cobble Hill  40.68963  -73.99259   \n",
       "20101           Manhattan       Murray Hill  40.74857  -73.97722   \n",
       "20705              Queens           Jamaica  40.68558  -73.78018   \n",
       "26462               Bronx         Wakefield  40.88764  -73.85580   \n",
       "7237               Queens  Long Island City  40.76450  -73.93181   \n",
       "\n",
       "             room_type  price  minimum_nights  number_of_reviews  \\\n",
       "12089  Entire home/apt     94               1                 39   \n",
       "20101  Entire home/apt    150              30                  5   \n",
       "20705     Private room     65               1                  8   \n",
       "26462  Entire home/apt    100               2                 38   \n",
       "7237   Entire home/apt    115               5                  6   \n",
       "\n",
       "       last_review_date  reviews_per_month  listings_count  availability_365  \n",
       "12089             43618               0.98               1                 4  \n",
       "20101             43519               0.26              50               344  \n",
       "20705             43369               0.42               4               155  \n",
       "26462             43635               2.66               1               107  \n",
       "7237              42659               0.13               1                 0  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "wRM5ckGF9j52",
    "outputId": "90905b7a-c209-40c1-8dc8-a7019443b24e"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "id                       int64\n",
       "name                    object\n",
       "host_id                  int64\n",
       "host_name               object\n",
       "neighbourhood_group     object\n",
       "neighbourhood           object\n",
       "latitude               float64\n",
       "longitude              float64\n",
       "room_type               object\n",
       "price                    int64\n",
       "minimum_nights           int64\n",
       "number_of_reviews        int64\n",
       "last_review_date         int64\n",
       "reviews_per_month      float64\n",
       "listings_count           int64\n",
       "availability_365         int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# view dtype of all columns\n",
    "df.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "K-Ez79xJUQq5"
   },
   "source": [
    "### Convert to category\n",
    "\n",
    "- `neighbourhood_group`\n",
    "- `neighbourhood`\n",
    "- `room_type`\n",
    "\n",
    "Guidelines for conversion\n",
    "- to reduce memory and increase performance with operations related to categorical data\n",
    "- make sure data is clean before converting it\n",
    "\n",
    "[More on this](https://pbpython.com/pandas_dtypes_cat.html)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "1a-ZFG9gVwWp",
    "outputId": "af235688-5108-4d55-d4f8-d0cb89fb6fac"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Brooklyn', 'Manhattan', 'Manhatan', 'Broklyn', 'Queens',\n",
       "       'Staten Island', 'Bronx'], dtype=object)"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check unique values in categorical column\n",
    "df['neighbourhood_group'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "rylmsc8TV--f",
    "outputId": "b4901b38-38e1-469d-d4f9-ca2100974fe7"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "218"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# show amount of categories in column\n",
    "len(df['neighbourhood'].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "NvIgpAc_WBfY",
    "outputId": "9da5fdca-1495-40fa-b4c7-0185db4299e9"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Private room', 'Entire home/apt', 'Shared room'], dtype=object)"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['room_type'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "SxpBjB17WEex",
    "outputId": "68e395a8-93ff-40e0-d4d1-a4c63354af09"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "17402573"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.memory_usage(deep=True).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "id": "7ftAbmnHWDhn"
   },
   "outputs": [],
   "source": [
    "# for categorical data with small amounts of categories, converting them to dtype category can save memory and make some operations more efficient \n",
    "\n",
    "df['room_type'] = df['room_type'].astype('category')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "IzLjlr3Vn7lo",
    "outputId": "2817fcb9-12db-4df6-94e8-c79e6b54475f"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "CategoricalDtype(categories=['Entire home/apt', 'Private room', 'Shared room'], ordered=False)"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['room_type'].dtype"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "3Q2DRCaLWQvR",
    "outputId": "3fc03b0e-f743-4948-e78e-ef2c2cea46be"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Private room', 'Entire home/apt', 'Shared room']\n",
       "Categories (3, object): ['Entire home/apt', 'Private room', 'Shared room']"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['room_type'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "wAJdthzvWStD",
    "outputId": "1fe707b9-c803-4be2-db5a-5724a4fd951f"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "14701138"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.memory_usage(deep=True).sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "N2xWoHsmW0ii"
   },
   "source": [
    "### Convert to datetime "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "9ZCa4f-vW01Z",
    "outputId": "fe596587-8ab1-417b-99d4-d6e69fcc52e3"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "25592    43633\n",
       "10312    43640\n",
       "13739    42961\n",
       "2493     43652\n",
       "20703    42961\n",
       "34053    43647\n",
       "7129     43589\n",
       "8293     43461\n",
       "4649     43498\n",
       "8614     42283\n",
       "Name: last_review_date, dtype: int64"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['last_review_date'].sample(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "9ra-p4VkW9H2",
    "outputId": "ed393fab-6096-44db-f184-29c573a7c12c"
   },
   "outputs": [],
   "source": [
    "df['last_review_date'] = pd.to_datetime(df['last_review_date'], unit='D', origin='1899-12-30')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "JdEe7hDl6-KW"
   },
   "source": [
    "Python strftime cheatsheet https://strftime.org/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "fvyzwQxvW_S6",
    "outputId": "75f8c7eb-249a-4700-91c1-d1371835577f"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "id                              int64\n",
       "name                           object\n",
       "host_id                         int64\n",
       "host_name                      object\n",
       "neighbourhood_group            object\n",
       "neighbourhood                  object\n",
       "latitude                      float64\n",
       "longitude                     float64\n",
       "room_type                    category\n",
       "price                           int64\n",
       "minimum_nights                  int64\n",
       "number_of_reviews               int64\n",
       "last_review_date       datetime64[ns]\n",
       "reviews_per_month             float64\n",
       "listings_count                  int64\n",
       "availability_365                int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "RDAyDuGyY0BX",
    "outputId": "5feb0ad5-e9f9-4313-cd7a-08a8e3e59c4f"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0   2018-10-19\n",
       "1   2019-05-21\n",
       "2   2019-07-05\n",
       "3   2018-11-19\n",
       "4   2019-06-22\n",
       "Name: last_review_date, dtype: datetime64[ns]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['last_review_date'].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "CECVg8UcXIM1"
   },
   "source": [
    "### Changing numeric types\n",
    "\n",
    "ex: changing int types (int8 | int16 | int32 | int64)\n",
    "\n",
    "- the numbers stands for bit\n",
    "- int8 can store integers from -128 to 127.\n",
    "- int16 can store integers from -32768 to 32767.\n",
    "- int64 can store integers from -9223372036854775808 to 9223372036854775807."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "RMfY9ASzXN7C",
    "outputId": "f9344306-ae8f-4195-baab-d08d38c1cc2c"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1250"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# get maximum from column\n",
    "df['minimum_nights'].max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "Am-7WBcioHSr",
    "outputId": "31ae4260-acf9-4209-eec4-873591db0eb7"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# get minimum from column\n",
    "df['minimum_nights'].min()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "Nm-jBdCNXu0X",
    "outputId": "e9edae3d-ba7c-41de-8931-bb4c18f09dfb"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dtype('int64')"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['minimum_nights'].dtype"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "gKOJXk1tXrxH",
    "outputId": "454b40b7-52e8-44df-b459-1dce4c22a947"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "14701138"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.memory_usage(deep=True).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "z_rvRWciXnlX",
    "outputId": "c38bb6ad-c55a-4490-c134-f4b83468c706"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dtype('int16')"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['minimum_nights'] = df['minimum_nights'].astype('int16')\n",
    "df['minimum_nights'].dtype"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "I4mQDpNPXtzI",
    "outputId": "ad5a8709-5ca4-4fa2-dae5-99c99d89eabb"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "14468056"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.memory_usage(deep=True).sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "8diBhfloR-PO"
   },
   "source": [
    "## Missing data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 222
    },
    "id": "CeWJizLm0tJD",
    "outputId": "8085b428-056b-413e-b83d-03fc4bbe237e"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "id                        0\n",
       "name                   1714\n",
       "host_id                   0\n",
       "host_name               325\n",
       "neighbourhood_group       0\n",
       "neighbourhood             0\n",
       "latitude                  0\n",
       "longitude                 0\n",
       "room_type                 0\n",
       "price                     0\n",
       "minimum_nights            0\n",
       "number_of_reviews         0\n",
       "last_review_date          0\n",
       "reviews_per_month        35\n",
       "listings_count            0\n",
       "availability_365          0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Checking which columns have missing data\n",
    "df.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "BCXNOb5P5yJ1"
   },
   "source": [
    "### percentage of missing data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<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>column_name</th>\n",
       "      <th>percent_missing</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <td>id</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>name</th>\n",
       "      <td>name</td>\n",
       "      <td>4.412181</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>host_id</th>\n",
       "      <td>host_id</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>host_name</th>\n",
       "      <td>host_name</td>\n",
       "      <td>0.836615</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>neighbourhood_group</th>\n",
       "      <td>neighbourhood_group</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>neighbourhood</th>\n",
       "      <td>neighbourhood</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>latitude</th>\n",
       "      <td>latitude</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>longitude</th>\n",
       "      <td>longitude</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>room_type</th>\n",
       "      <td>room_type</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>price</th>\n",
       "      <td>price</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>minimum_nights</th>\n",
       "      <td>minimum_nights</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>number_of_reviews</th>\n",
       "      <td>number_of_reviews</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>last_review_date</th>\n",
       "      <td>last_review_date</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>reviews_per_month</th>\n",
       "      <td>reviews_per_month</td>\n",
       "      <td>0.090097</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>listings_count</th>\n",
       "      <td>listings_count</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>availability_365</th>\n",
       "      <td>availability_365</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             column_name  percent_missing\n",
       "id                                    id         0.000000\n",
       "name                                name         4.412181\n",
       "host_id                          host_id         0.000000\n",
       "host_name                      host_name         0.836615\n",
       "neighbourhood_group  neighbourhood_group         0.000000\n",
       "neighbourhood              neighbourhood         0.000000\n",
       "latitude                        latitude         0.000000\n",
       "longitude                      longitude         0.000000\n",
       "room_type                      room_type         0.000000\n",
       "price                              price         0.000000\n",
       "minimum_nights            minimum_nights         0.000000\n",
       "number_of_reviews      number_of_reviews         0.000000\n",
       "last_review_date        last_review_date         0.000000\n",
       "reviews_per_month      reviews_per_month         0.090097\n",
       "listings_count            listings_count         0.000000\n",
       "availability_365        availability_365         0.000000"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "percent_missing = df.isnull().sum() * 100 / len(df)\n",
    "missing_value_df = pd.DataFrame({'column_name': df.columns,\n",
    "                                 'percent_missing': percent_missing})\n",
    "missing_value_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "yhgUL5yW7K8n"
   },
   "source": [
    "### How to deal with missing data?\n",
    "\n",
    "Dealing with missing data is not simple task, you have need to consider why the data is missing in the first place, and domain knowledge to know what to impute. \n",
    "\n",
    "There also isn't a specific threshold for what percentage of missing data is accepted, it depends on the data.\n",
    "\n",
    "If you mess it up, you will introduce bias to your data.  \n",
    "\n",
    "\n",
    "#### Techniques \n",
    "1. Drop feature \n",
    "1. Drop the rows\n",
    "1. Impute missing values (manually or automatically)\n",
    "\n",
    "Read this [article](https://towardsdatascience.com/how-to-handle-missing-data-8646b18db0d4) to go deeper into this topic"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "id": "kgqwkvE8yAS7"
   },
   "outputs": [],
   "source": [
    "# copying original data set is good practice when you're experimenting with things\n",
    "\n",
    "df_ori = df.copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "clL4rrdm3pjq"
   },
   "source": [
    "#### Dropping columns with missing values\n",
    "\n",
    "- usually worst strategy unless it has a lot of missing data (over 80 or 90%), or feature is not useful"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "id": "tqudL93ZF_La"
   },
   "outputs": [],
   "source": [
    "# Create list of columns you want to drop\n",
    "colsToDrop = ['name','host_name','reviews_per_month']\n",
    "\n",
    "# axis = 1 / 'columns' stands for column\n",
    "# axis = 0 / 'index' stands for row\n",
    "df.drop(colsToDrop, axis = 'columns', inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "FCy89KB8DDGy",
    "outputId": "a1640f32-8c99-421c-92c4-fc03e1f2ebe3"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "id                     0\n",
       "host_id                0\n",
       "neighbourhood_group    0\n",
       "neighbourhood          0\n",
       "latitude               0\n",
       "longitude              0\n",
       "room_type              0\n",
       "price                  0\n",
       "minimum_nights         0\n",
       "number_of_reviews      0\n",
       "last_review_date       0\n",
       "listings_count         0\n",
       "availability_365       0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "bzHz3X0REVt6",
    "outputId": "4de70ccd-0548-4763-9517-16ab2f5bc450"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(38847, 13)\n"
     ]
    }
   ],
   "source": [
    "print(df.shape)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "Adv4-ZqH8w3T"
   },
   "source": [
    "#### Remove rows with missing values\n",
    "- Losing even more information (from other columns), so not the best method"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "eJgf68aiR-9Z",
    "outputId": "ea8f4c86-aaf5-4154-891f-82f27966c483"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(38847, 16)\n",
      "(36781, 16)\n"
     ]
    }
   ],
   "source": [
    "# restoring original dataset\n",
    "df = df_ori.copy()\n",
    "\n",
    "# before .dropna\n",
    "print(df.shape) \n",
    "\n",
    "# after .dropna\n",
    "print(df.dropna().shape)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "Lhu8RMKg80Bb"
   },
   "source": [
    "#### Impute missing values\n",
    "\n",
    "\n",
    "- A constant value related to the data, such as 0 for `reviews_per_month`, or \"None\" for `name`.\n",
    "- The value before or after the data point (backward fill, forward fill)\n",
    "- Summary statistics such as mean, median or mode value for the column.\n",
    "- A value estimated by algorithms or ML models like KNN.\n",
    "\n",
    "More detailed imputation techniques in this [article](https://towardsdatascience.com/6-different-ways-to-compensate-for-missing-values-data-imputation-with-examples-6022d9ca0779)  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "r6W_-LfYpYud",
    "outputId": "d54ad18e-0fe3-4727-80aa-f4a593731105"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     NaN\n",
       "1    0.38\n",
       "2     NaN\n",
       "3     NaN\n",
       "4    0.59\n",
       "Name: reviews_per_month, dtype: float64"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Mean of reviews_per_month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "QP1jjR-dvxNX",
    "outputId": "6344bcf0-1b1d-4cd3-bfee-9a52d1bb8ac4"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1.3736321240853344\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "0    1.373632\n",
       "1    0.380000\n",
       "2    1.373632\n",
       "3    1.373632\n",
       "4    0.590000\n",
       "Name: reviews_per_month, dtype: float64"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# restoring original dataset\n",
    "df = df_ori.copy()\n",
    "\n",
    "# calculating the mean\n",
    "reviews_mean = df['reviews_per_month'].mean()\n",
    "print(reviews_mean)\n",
    "\n",
    "# filling missing values with mean\n",
    "df = df.fillna(value = {'reviews_per_month' : reviews_mean})\n",
    "df['reviews_per_month'].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### median of reviews_per_month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "GxIZSbBAwNNS",
    "outputId": "a5e2d7cc-f044-4921-fef3-6fc1eef96edb"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0.72\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "0    0.72\n",
       "1    0.38\n",
       "2    0.72\n",
       "3    0.72\n",
       "4    0.59\n",
       "Name: reviews_per_month, dtype: float64"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# restoring original dataset\n",
    "df = df_ori.copy()\n",
    "\n",
    "# calculating the median\n",
    "reviews_median = df['reviews_per_month'].median()\n",
    "print(reviews_median)\n",
    "\n",
    "# filling missing values with median\n",
    "# df.fillna(value = {'reviews_per_month' : reviews_median}).head()['reviews_per_month']\n",
    "\n",
    "df = df.fillna(value = {'reviews_per_month' : reviews_median})\n",
    "df['reviews_per_month'].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "# restoring original dataset\n",
    "df = df_ori.copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "RnHolWGVF_7Q"
   },
   "source": [
    "##### Imputing with bfill and ffill\n",
    "\n",
    "bfill\n",
    "- bfill stands for backwards fill\n",
    "- means filling in missing value with value after it (fill it backwards)\n",
    "\n",
    "ffill\n",
    "- ffill stands for forward fill\n",
    "- means filling in missing value with value before it (fill it forwards)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "OG7XJpVM3e3F",
    "outputId": "43538a6b-1f6a-486b-f4fe-8a1b36dca113"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     NaN\n",
       "1    0.38\n",
       "2     NaN\n",
       "3     NaN\n",
       "4    0.59\n",
       "Name: reviews_per_month, dtype: float64"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['reviews_per_month'].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "XEDzyORYGdwc",
    "outputId": "306492c8-faaf-4377-9b31-5339e56e70f4"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    0.38\n",
       "1    0.38\n",
       "2    0.59\n",
       "3    0.59\n",
       "4    0.59\n",
       "Name: reviews_per_month, dtype: float64"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# imputing with bfill\n",
    "df['reviews_per_month'].bfill().head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "Rx8X58MTxeGa",
    "outputId": "9cc289f0-225c-41f7-fe90-e43f9268f764"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     NaN\n",
       "1    0.38\n",
       "2    0.38\n",
       "3    0.38\n",
       "4    0.59\n",
       "Name: reviews_per_month, dtype: float64"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# imputing with ffill\n",
    "df['reviews_per_month'].ffill().head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [],
   "source": [
    "# restoring original dataset\n",
    "df = df_ori.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "##### Imputing manually with pandas fillna"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<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>name</th>\n",
       "      <th>host_id</th>\n",
       "      <th>host_name</th>\n",
       "      <th>neighbourhood_group</th>\n",
       "      <th>neighbourhood</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>room_type</th>\n",
       "      <th>price</th>\n",
       "      <th>minimum_nights</th>\n",
       "      <th>number_of_reviews</th>\n",
       "      <th>last_review_date</th>\n",
       "      <th>reviews_per_month</th>\n",
       "      <th>listings_count</th>\n",
       "      <th>availability_365</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2539</td>\n",
       "      <td>None</td>\n",
       "      <td>2787</td>\n",
       "      <td>None</td>\n",
       "      <td>Brooklyn</td>\n",
       "      <td>Kensington</td>\n",
       "      <td>40.64749</td>\n",
       "      <td>-73.97237</td>\n",
       "      <td>Private room</td>\n",
       "      <td>149</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>2018-10-19</td>\n",
       "      <td>0.00</td>\n",
       "      <td>6</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2595</td>\n",
       "      <td>Skylit Midtown Castle</td>\n",
       "      <td>2845</td>\n",
       "      <td>None</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>Midtown</td>\n",
       "      <td>40.75362</td>\n",
       "      <td>-73.98377</td>\n",
       "      <td>Entire home/apt</td>\n",
       "      <td>225</td>\n",
       "      <td>1</td>\n",
       "      <td>45</td>\n",
       "      <td>2019-05-21</td>\n",
       "      <td>0.38</td>\n",
       "      <td>2</td>\n",
       "      <td>355</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3831</td>\n",
       "      <td>None</td>\n",
       "      <td>4869</td>\n",
       "      <td>LisaRoxanne</td>\n",
       "      <td>Brooklyn</td>\n",
       "      <td>Clinton Hill</td>\n",
       "      <td>40.68514</td>\n",
       "      <td>-73.95976</td>\n",
       "      <td>Entire home/apt</td>\n",
       "      <td>89</td>\n",
       "      <td>1</td>\n",
       "      <td>270</td>\n",
       "      <td>2019-07-05</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1</td>\n",
       "      <td>194</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5022</td>\n",
       "      <td>None</td>\n",
       "      <td>7192</td>\n",
       "      <td>Laura</td>\n",
       "      <td>Manhatan</td>\n",
       "      <td>East Harlem</td>\n",
       "      <td>40.79851</td>\n",
       "      <td>-73.94399</td>\n",
       "      <td>Entire home/apt</td>\n",
       "      <td>80</td>\n",
       "      <td>10</td>\n",
       "      <td>9</td>\n",
       "      <td>2018-11-19</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5099</td>\n",
       "      <td>Large Cozy 1 BR Apartment In Midtown East</td>\n",
       "      <td>7322</td>\n",
       "      <td>Chris</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>Murray Hill</td>\n",
       "      <td>40.74767</td>\n",
       "      <td>-73.97500</td>\n",
       "      <td>Entire home/apt</td>\n",
       "      <td>200</td>\n",
       "      <td>3</td>\n",
       "      <td>74</td>\n",
       "      <td>2019-06-22</td>\n",
       "      <td>0.59</td>\n",
       "      <td>1</td>\n",
       "      <td>129</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     id                                       name  host_id    host_name  \\\n",
       "0  2539                                       None     2787         None   \n",
       "1  2595                      Skylit Midtown Castle     2845         None   \n",
       "2  3831                                       None     4869  LisaRoxanne   \n",
       "3  5022                                       None     7192        Laura   \n",
       "4  5099  Large Cozy 1 BR Apartment In Midtown East     7322        Chris   \n",
       "\n",
       "  neighbourhood_group neighbourhood  latitude  longitude        room_type  \\\n",
       "0            Brooklyn    Kensington  40.64749  -73.97237     Private room   \n",
       "1           Manhattan       Midtown  40.75362  -73.98377  Entire home/apt   \n",
       "2            Brooklyn  Clinton Hill  40.68514  -73.95976  Entire home/apt   \n",
       "3            Manhatan   East Harlem  40.79851  -73.94399  Entire home/apt   \n",
       "4           Manhattan   Murray Hill  40.74767  -73.97500  Entire home/apt   \n",
       "\n",
       "   price  minimum_nights  number_of_reviews last_review_date  \\\n",
       "0    149               1                  9       2018-10-19   \n",
       "1    225               1                 45       2019-05-21   \n",
       "2     89               1                270       2019-07-05   \n",
       "3     80              10                  9       2018-11-19   \n",
       "4    200               3                 74       2019-06-22   \n",
       "\n",
       "   reviews_per_month  listings_count  availability_365  \n",
       "0               0.00               6               365  \n",
       "1               0.38               2               355  \n",
       "2               0.00               1               194  \n",
       "3               0.00               1                 0  \n",
       "4               0.59               1               129  "
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# We will deal with missing values manually\n",
    "missing_val = { 'reviews_per_month': 0,\n",
    "                'name':'None',\n",
    "                'host_name':'None'}\n",
    "\n",
    "df = df.fillna(value = missing_val)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "5WrQ12BDFTh0"
   },
   "source": [
    "## Split Date column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "qqlorLHyFU24",
    "outputId": "284ea66e-bb5b-40b3-e307-0de3aba54169"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0   2018-10-19\n",
       "1   2019-05-21\n",
       "2   2019-07-05\n",
       "3   2018-11-19\n",
       "4   2019-06-22\n",
       "Name: last_review_date, dtype: datetime64[ns]"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['last_review_date'].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {
    "id": "mqt3bKAvC4wf"
   },
   "outputs": [],
   "source": [
    "# Int64 are pandas data types that can handle missing values\n",
    "df['year'] = df['last_review_date'].dt.year.astype('Int64')\n",
    "df['month'] = df['last_review_date'].dt.month.astype('Int64')\n",
    "df['day'] = df['last_review_date'].dt.day.astype('Int64')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 264
    },
    "id": "b8aHeHpxC5JG",
    "outputId": "39b5019f-c8a6-48fc-d62e-cbf3b5454a0c"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<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>name</th>\n",
       "      <th>host_id</th>\n",
       "      <th>host_name</th>\n",
       "      <th>neighbourhood_group</th>\n",
       "      <th>neighbourhood</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>room_type</th>\n",
       "      <th>price</th>\n",
       "      <th>minimum_nights</th>\n",
       "      <th>number_of_reviews</th>\n",
       "      <th>last_review_date</th>\n",
       "      <th>reviews_per_month</th>\n",
       "      <th>listings_count</th>\n",
       "      <th>availability_365</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2539</td>\n",
       "      <td>None</td>\n",
       "      <td>2787</td>\n",
       "      <td>None</td>\n",
       "      <td>Brooklyn</td>\n",
       "      <td>Kensington</td>\n",
       "      <td>40.64749</td>\n",
       "      <td>-73.97237</td>\n",
       "      <td>Private room</td>\n",
       "      <td>149</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>2018-10-19</td>\n",
       "      <td>0.00</td>\n",
       "      <td>6</td>\n",
       "      <td>365</td>\n",
       "      <td>2018</td>\n",
       "      <td>10</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2595</td>\n",
       "      <td>Skylit Midtown Castle</td>\n",
       "      <td>2845</td>\n",
       "      <td>None</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>Midtown</td>\n",
       "      <td>40.75362</td>\n",
       "      <td>-73.98377</td>\n",
       "      <td>Entire home/apt</td>\n",
       "      <td>225</td>\n",
       "      <td>1</td>\n",
       "      <td>45</td>\n",
       "      <td>2019-05-21</td>\n",
       "      <td>0.38</td>\n",
       "      <td>2</td>\n",
       "      <td>355</td>\n",
       "      <td>2019</td>\n",
       "      <td>5</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3831</td>\n",
       "      <td>None</td>\n",
       "      <td>4869</td>\n",
       "      <td>LisaRoxanne</td>\n",
       "      <td>Brooklyn</td>\n",
       "      <td>Clinton Hill</td>\n",
       "      <td>40.68514</td>\n",
       "      <td>-73.95976</td>\n",
       "      <td>Entire home/apt</td>\n",
       "      <td>89</td>\n",
       "      <td>1</td>\n",
       "      <td>270</td>\n",
       "      <td>2019-07-05</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1</td>\n",
       "      <td>194</td>\n",
       "      <td>2019</td>\n",
       "      <td>7</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5022</td>\n",
       "      <td>None</td>\n",
       "      <td>7192</td>\n",
       "      <td>Laura</td>\n",
       "      <td>Manhatan</td>\n",
       "      <td>East Harlem</td>\n",
       "      <td>40.79851</td>\n",
       "      <td>-73.94399</td>\n",
       "      <td>Entire home/apt</td>\n",
       "      <td>80</td>\n",
       "      <td>10</td>\n",
       "      <td>9</td>\n",
       "      <td>2018-11-19</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2018</td>\n",
       "      <td>11</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5099</td>\n",
       "      <td>Large Cozy 1 BR Apartment In Midtown East</td>\n",
       "      <td>7322</td>\n",
       "      <td>Chris</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>Murray Hill</td>\n",
       "      <td>40.74767</td>\n",
       "      <td>-73.97500</td>\n",
       "      <td>Entire home/apt</td>\n",
       "      <td>200</td>\n",
       "      <td>3</td>\n",
       "      <td>74</td>\n",
       "      <td>2019-06-22</td>\n",
       "      <td>0.59</td>\n",
       "      <td>1</td>\n",
       "      <td>129</td>\n",
       "      <td>2019</td>\n",
       "      <td>6</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     id                                       name  host_id    host_name  \\\n",
       "0  2539                                       None     2787         None   \n",
       "1  2595                      Skylit Midtown Castle     2845         None   \n",
       "2  3831                                       None     4869  LisaRoxanne   \n",
       "3  5022                                       None     7192        Laura   \n",
       "4  5099  Large Cozy 1 BR Apartment In Midtown East     7322        Chris   \n",
       "\n",
       "  neighbourhood_group neighbourhood  latitude  longitude        room_type  \\\n",
       "0            Brooklyn    Kensington  40.64749  -73.97237     Private room   \n",
       "1           Manhattan       Midtown  40.75362  -73.98377  Entire home/apt   \n",
       "2            Brooklyn  Clinton Hill  40.68514  -73.95976  Entire home/apt   \n",
       "3            Manhatan   East Harlem  40.79851  -73.94399  Entire home/apt   \n",
       "4           Manhattan   Murray Hill  40.74767  -73.97500  Entire home/apt   \n",
       "\n",
       "   price  minimum_nights  number_of_reviews last_review_date  \\\n",
       "0    149               1                  9       2018-10-19   \n",
       "1    225               1                 45       2019-05-21   \n",
       "2     89               1                270       2019-07-05   \n",
       "3     80              10                  9       2018-11-19   \n",
       "4    200               3                 74       2019-06-22   \n",
       "\n",
       "   reviews_per_month  listings_count  availability_365  year  month  day  \n",
       "0               0.00               6               365  2018     10   19  \n",
       "1               0.38               2               355  2019      5   21  \n",
       "2               0.00               1               194  2019      7    5  \n",
       "3               0.00               1                 0  2018     11   19  \n",
       "4               0.59               1               129  2019      6   22  "
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "HlW5jIRlpP94"
   },
   "source": [
    "## Data Inconsistencies"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "gw3cxFoAE5vN"
   },
   "source": [
    "#### Are the values in the column `availability_365` within 365 ?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "d61QIuo_EIIs",
    "outputId": "063e2270-0eed-408f-c946-c44c21003943"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    38847.000000\n",
       "mean       116.172858\n",
       "std        135.411287\n",
       "min         -1.000000\n",
       "25%          0.000000\n",
       "50%         55.000000\n",
       "75%        229.000000\n",
       "max       5205.000000\n",
       "Name: availability_365, dtype: float64"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# show statistics of a column\n",
    "df['availability_365'].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {
    "id": "Mx4pJDv5EgZx"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 155 values greater then 365.\n",
      "There are 9 values lesser then 0.\n"
     ]
    }
   ],
   "source": [
    "print('There are {} values greater then 365.'.format(df['availability_365'][df['availability_365'] > 365].count()))\n",
    "print('There are {} values lesser then 0.'.format(df['availability_365'][df['availability_365'] < 0].count()))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    38847.000000\n",
       "mean       115.091050\n",
       "std        129.921701\n",
       "min          0.000000\n",
       "25%          0.000000\n",
       "50%         55.000000\n",
       "75%        229.000000\n",
       "max        365.000000\n",
       "Name: availability_365, dtype: float64"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[df['availability_365'] > 365, 'availability_365'] = 365\n",
    "df.loc[df['availability_365'] < 0, 'availability_365'] = 0\n",
    "df['availability_365'].describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "187ZH4tkzjO1"
   },
   "source": [
    "### Categorical data inconsistency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "zYlP3mKFFi8R",
    "outputId": "4cd84cd2-d5ae-4098-dec3-0fd5438309da"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Brooklyn', 'Manhattan', 'Manhatan', 'Broklyn', 'Queens',\n",
       "       'Staten Island', 'Bronx'], dtype=object)"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# show all neighbourhood_groups\n",
    "df['neighbourhood_group'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "0itOIHybFuDV",
    "outputId": "3bb98faf-feda-4a82-fcbe-88f44a2b37e1"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Brooklyn', 'Manhattan', 'Broklyn', 'Queens', 'Staten Island',\n",
       "       'Bronx'], dtype=object)"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wrong = ['Brokln', 'Manhatan']\n",
    "right = ['Brooklyn', 'Manhattan']\n",
    "\n",
    "# replace the wrong spelling with right spelling\n",
    "df['neighbourhood_group'].replace(to_replace = wrong, \n",
    "                                  value = right, \n",
    "                                  inplace = True)\n",
    "\n",
    "# changing values removes the category date type\n",
    "df['neighbourhood_group'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "Cfda1EoxGX5f"
   },
   "source": [
    "This was a simple example with only 6 categories. In real life scenarios there could be hundreds of categories. To deal with that you can use these techniques.\n",
    "\n",
    "1. Preprocess the text (lowercase, strip whitespace)\n",
    "1. Use fuzzy matching to find similar words, and replace them. ([example](https://www.kaggle.com/rtatman/data-cleaning-challenge-inconsistent-data-entry/?scriptVersionId=3012975&cellId=14))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "6AbUg11Czf4N"
   },
   "source": [
    "### Duplicate rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "MMoaHQzVFvTJ",
    "outputId": "7d7cca70-d8ea-482c-8d08-7b5cade820c5"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check if there any duplicate rows?\n",
    "df.duplicated().any()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "FOgQDNd31Pic",
    "outputId": "904b0785-0d13-4364-9f05-062a2a1ee078"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# How many rows are duplicated?\n",
    "df.duplicated().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 117
    },
    "id": "WnSpnT3LHqCX",
    "outputId": "22073972-f5ae-4a19-e043-097ce223f9fc"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<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>name</th>\n",
       "      <th>host_id</th>\n",
       "      <th>host_name</th>\n",
       "      <th>neighbourhood_group</th>\n",
       "      <th>neighbourhood</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>room_type</th>\n",
       "      <th>price</th>\n",
       "      <th>minimum_nights</th>\n",
       "      <th>number_of_reviews</th>\n",
       "      <th>last_review_date</th>\n",
       "      <th>reviews_per_month</th>\n",
       "      <th>listings_count</th>\n",
       "      <th>availability_365</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>11101</th>\n",
       "      <td>9920941</td>\n",
       "      <td>Jewel On Parkside</td>\n",
       "      <td>11546043</td>\n",
       "      <td>Liz And Cesar</td>\n",
       "      <td>Brooklyn</td>\n",
       "      <td>Prospect-Lefferts Gardens</td>\n",
       "      <td>40.65586</td>\n",
       "      <td>-73.95934</td>\n",
       "      <td>Private room</td>\n",
       "      <td>65</td>\n",
       "      <td>28</td>\n",
       "      <td>11</td>\n",
       "      <td>2019-06-01</td>\n",
       "      <td>0.26</td>\n",
       "      <td>3</td>\n",
       "      <td>320</td>\n",
       "      <td>2019</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11108</th>\n",
       "      <td>9924665</td>\n",
       "      <td>Private Room in Greenpoint</td>\n",
       "      <td>26714887</td>\n",
       "      <td>Anthony</td>\n",
       "      <td>Brooklyn</td>\n",
       "      <td>Greenpoint</td>\n",
       "      <td>40.73036</td>\n",
       "      <td>-73.95453</td>\n",
       "      <td>Private room</td>\n",
       "      <td>60</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>2016-08-23</td>\n",
       "      <td>0.07</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2016</td>\n",
       "      <td>8</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11129</th>\n",
       "      <td>9935992</td>\n",
       "      <td>Lux Studio Apt in Downtown NYC - Gym included!</td>\n",
       "      <td>30283594</td>\n",
       "      <td>Kara</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>Financial District</td>\n",
       "      <td>40.70749</td>\n",
       "      <td>-74.01413</td>\n",
       "      <td>Entire home/apt</td>\n",
       "      <td>169</td>\n",
       "      <td>30</td>\n",
       "      <td>2</td>\n",
       "      <td>2017-11-17</td>\n",
       "      <td>0.06</td>\n",
       "      <td>121</td>\n",
       "      <td>364</td>\n",
       "      <td>2017</td>\n",
       "      <td>11</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11280</th>\n",
       "      <td>10035098</td>\n",
       "      <td>Upper West ... Museums/Parks/Subway</td>\n",
       "      <td>51513795</td>\n",
       "      <td>Doug</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>Upper West Side</td>\n",
       "      <td>40.77845</td>\n",
       "      <td>-73.97985</td>\n",
       "      <td>Entire home/apt</td>\n",
       "      <td>175</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-01-03</td>\n",
       "      <td>0.02</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             id                                            name   host_id  \\\n",
       "11101   9920941                               Jewel On Parkside  11546043   \n",
       "11108   9924665                      Private Room in Greenpoint  26714887   \n",
       "11129   9935992  Lux Studio Apt in Downtown NYC - Gym included!  30283594   \n",
       "11280  10035098             Upper West ... Museums/Parks/Subway  51513795   \n",
       "\n",
       "           host_name neighbourhood_group              neighbourhood  latitude  \\\n",
       "11101  Liz And Cesar            Brooklyn  Prospect-Lefferts Gardens  40.65586   \n",
       "11108        Anthony            Brooklyn                 Greenpoint  40.73036   \n",
       "11129           Kara           Manhattan         Financial District  40.70749   \n",
       "11280           Doug           Manhattan            Upper West Side  40.77845   \n",
       "\n",
       "       longitude        room_type  price  minimum_nights  number_of_reviews  \\\n",
       "11101  -73.95934     Private room     65              28                 11   \n",
       "11108  -73.95453     Private room     60               1                  3   \n",
       "11129  -74.01413  Entire home/apt    169              30                  2   \n",
       "11280  -73.97985  Entire home/apt    175               2                  1   \n",
       "\n",
       "      last_review_date  reviews_per_month  listings_count  availability_365  \\\n",
       "11101       2019-06-01               0.26               3               320   \n",
       "11108       2016-08-23               0.07               2                 0   \n",
       "11129       2017-11-17               0.06             121               364   \n",
       "11280       2016-01-03               0.02               1                 0   \n",
       "\n",
       "       year  month  day  \n",
       "11101  2019      6    1  \n",
       "11108  2016      8   23  \n",
       "11129  2017     11   17  \n",
       "11280  2016      1    3  "
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Print duplicated rows\n",
    "df[df.duplicated()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {
    "id": "ZXrYdnkLH7cH"
   },
   "outputs": [],
   "source": [
    "# Remove duplicated rows\n",
    "df.drop_duplicates(inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "zxGOl13bH9r5",
    "outputId": "340e0560-98ee-4df2-903c-dcb3d055afe5"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check again if there any duplicate rows?\n",
    "df.duplicated().any()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "i1NjHgTqGcDW"
   },
   "source": [
    "## Outliers\n",
    "\n",
    "> You should be very carefull handling outliers and have a good knowledge about what the data represents and information about where the data come from and what they mean ; it also depends on what analysis one is planning to perform.\n",
    "\n",
    "Some articles about outliers:\n",
    "- https://www.freecodecamp.org/news/what-is-an-outlier-definition-and-how-to-find-outliers-in-statistics/\n",
    "- https://www.pluralsight.com/guides/cleaning-up-data-from-outliers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "GIHKrCTGKme7",
    "outputId": "67b75d67-6e32-414e-dcdf-401addce8143"
   },
   "outputs": [],
   "source": [
    "# Let's look at the price field\n",
    "df['price'].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Boxplot could help us to visualise outliers\n",
    "df.boxplot(column=['price'],figsize=(5,10));\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Let's say we decided to treat everything above 99 percentile and everything lower 1 percentile as outliers\n",
    "small = df['price'].quantile(0.01)\n",
    "big = df['price'].quantile(0.99)\n",
    "print(small)\n",
    "print(big)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Trimming the outliers\n",
    "index = df[(df['price'] >= big)|(df['price'] <= small)].index\n",
    "df.drop(index, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 262
    },
    "id": "w6BhZklcKapy",
    "outputId": "8e5f7da9-d6f4-477e-abf8-8d28e192ab51"
   },
   "outputs": [],
   "source": [
    "# Let's plot again to see the result\n",
    "df.boxplot(column=['price'],figsize=(5,10));\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "Vjdr-1ucvVE3"
   },
   "source": [
    "## Save final cleaned dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "Xl8rTrkZvVw9"
   },
   "outputs": [],
   "source": [
    "df.to_csv(\"clean_data\")"
   ]
  }
 ],
 "metadata": {
  "colab": {
   "collapsed_sections": [],
   "provenance": []
  },
  "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.8.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}