# data[].attributes.body
DC_Bootcamp_Week_7_questions_submission

# data[].attributes.attachment_link.readable_text
{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "NpPlK3f9VIJg"
   },
   "source": [
    "# Pandas: Transforming Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "gN2RFGn3VIJi"
   },
   "source": [
    "## Run The Following Code\n",
    "Hit SHIFT + ENTER when your cusor is inside the cell of code.<br/>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "dg1hBaA7VIJl"
   },
   "source": [
    "## Importing and Checking the Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "3fskFP6UVIJo"
   },
   "source": [
    "> Importing pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "id": "GChe2i8UVIJo"
   },
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "gctnxHQEVIJo"
   },
   "source": [
    "> Read the data from CSV file into a DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "id": "1Wyy8PlXVIJp"
   },
   "outputs": [],
   "source": [
    "hdb = 'data/0_raw_data/hdb_resale_sample_raw.csv'\n",
    "\n",
    "df = pd.read_csv(hdb)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "8oNcfodGVIJq"
   },
   "source": [
    "> viewing the contents of the dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The main dataset that we will work on is on HDB resale data.<br/>\n",
    "See the description below for the fields given. <br/>\n",
    "<br/>\n",
    "<table>\n",
    "    <tr><th>Field</th><th>Description</th></tr>\n",
    "    <tr><td>Trans_YearMonth</td><td>year-month (YYYY-MM) for transaction</td></tr>\n",
    "    <tr><td>town</td><td>town for the resale flat</td></tr>\n",
    "    <tr><td>flat_type</td><td>flat type for the resale flat</td></tr>\n",
    "    <tr><td>block</td><td>block for the resale flat</td></tr>\n",
    "    <tr><td>street_name</td><td>street for the resale flat</td></tr>\n",
    "    <tr><td>storey_range</td><td>storey range for the resale flat</td></tr>\n",
    "    <tr><td>floor_area_sqm</td><td>floor area in square meter</td></tr>\n",
    "    <tr><td>flat_model</td><td>model of resale flat</td></tr>\n",
    "    <tr><td>lease_commence_date</td><td>year of lease commencement</td></tr>\n",
    "    <tr><td>resale_price</td><td>price of the resale flat</td></tr>\n",
    "</table>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(15000, 10)"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape #num of rows and columns 15000 rows, 10 columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "id": "nB2DLyrQVIJq"
   },
   "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>Tranc_YearMonth</th>\n",
       "      <th>town</th>\n",
       "      <th>flat_type</th>\n",
       "      <th>block</th>\n",
       "      <th>street_name</th>\n",
       "      <th>storey_range</th>\n",
       "      <th>floor_area_sqm</th>\n",
       "      <th>flat_model</th>\n",
       "      <th>lease_commence_date</th>\n",
       "      <th>resale_price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2015-01</td>\n",
       "      <td>ANG MOO KIO</td>\n",
       "      <td>3 ROOM</td>\n",
       "      <td>218</td>\n",
       "      <td>ANG MO KIO AVE 1</td>\n",
       "      <td>07 TO 09</td>\n",
       "      <td>67.0</td>\n",
       "      <td>New Generation</td>\n",
       "      <td>1976</td>\n",
       "      <td>308000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2015-01</td>\n",
       "      <td>ANG MOO KIO</td>\n",
       "      <td>4 ROOM</td>\n",
       "      <td>207</td>\n",
       "      <td>ANG MO KIO AVE 1</td>\n",
       "      <td>01 TO 03</td>\n",
       "      <td>97.0</td>\n",
       "      <td>New Generation</td>\n",
       "      <td>1976</td>\n",
       "      <td>475000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2015-02</td>\n",
       "      <td>ANG MOO KIO</td>\n",
       "      <td>3 ROOM</td>\n",
       "      <td>214</td>\n",
       "      <td>ANG MO KIO AVE 3</td>\n",
       "      <td>07 TO 09</td>\n",
       "      <td>67.0</td>\n",
       "      <td>New Generation</td>\n",
       "      <td>1976</td>\n",
       "      <td>315000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2015-04</td>\n",
       "      <td>ANG MOO KIO</td>\n",
       "      <td>3 ROOM</td>\n",
       "      <td>214</td>\n",
       "      <td>ANG MO KIO AVE 3</td>\n",
       "      <td>07 TO 09</td>\n",
       "      <td>67.0</td>\n",
       "      <td>New Generation</td>\n",
       "      <td>1976</td>\n",
       "      <td>285000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2015-04</td>\n",
       "      <td>ANG MOO KIO</td>\n",
       "      <td>3 ROOM</td>\n",
       "      <td>213</td>\n",
       "      <td>ANG MO KIO AVE 3</td>\n",
       "      <td>07 TO 09</td>\n",
       "      <td>67.0</td>\n",
       "      <td>New Generation</td>\n",
       "      <td>1976</td>\n",
       "      <td>305000.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Tranc_YearMonth         town flat_type block       street_name storey_range  \\\n",
       "0         2015-01  ANG MOO KIO    3 ROOM   218  ANG MO KIO AVE 1     07 TO 09   \n",
       "1         2015-01  ANG MOO KIO    4 ROOM   207  ANG MO KIO AVE 1     01 TO 03   \n",
       "2         2015-02  ANG MOO KIO    3 ROOM   214  ANG MO KIO AVE 3     07 TO 09   \n",
       "3         2015-04  ANG MOO KIO    3 ROOM   214  ANG MO KIO AVE 3     07 TO 09   \n",
       "4         2015-04  ANG MOO KIO    3 ROOM   213  ANG MO KIO AVE 3     07 TO 09   \n",
       "\n",
       "   floor_area_sqm      flat_model  lease_commence_date  resale_price  \n",
       "0            67.0  New Generation                 1976      308000.0  \n",
       "1            97.0  New Generation                 1976      475000.0  \n",
       "2            67.0  New Generation                 1976      315000.0  \n",
       "3            67.0  New Generation                 1976      285000.0  \n",
       "4            67.0  New Generation                 1976      305000.0  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head() #first 5 rows of data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Sorting Rows in DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>Tranc_YearMonth</th>\n",
       "      <th>town</th>\n",
       "      <th>flat_type</th>\n",
       "      <th>block</th>\n",
       "      <th>street_name</th>\n",
       "      <th>storey_range</th>\n",
       "      <th>floor_area_sqm</th>\n",
       "      <th>flat_model</th>\n",
       "      <th>lease_commence_date</th>\n",
       "      <th>resale_price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>13185</th>\n",
       "      <td>2020-02</td>\n",
       "      <td>TOA PAYOH</td>\n",
       "      <td>3 ROOM</td>\n",
       "      <td>26</td>\n",
       "      <td>TOA PAYOH EAST</td>\n",
       "      <td>10 TO 12</td>\n",
       "      <td>67.0</td>\n",
       "      <td>Improved</td>\n",
       "      <td>1971</td>\n",
       "      <td>140000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4246</th>\n",
       "      <td>2019-10</td>\n",
       "      <td>GEYLANG</td>\n",
       "      <td>2 ROOM</td>\n",
       "      <td>68</td>\n",
       "      <td>CIRCUIT RD</td>\n",
       "      <td>04 TO 06</td>\n",
       "      <td>45.0</td>\n",
       "      <td>Standard</td>\n",
       "      <td>1969</td>\n",
       "      <td>150000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2273</th>\n",
       "      <td>2020-02</td>\n",
       "      <td>BUKIT MERAH</td>\n",
       "      <td>1 ROOM</td>\n",
       "      <td>7</td>\n",
       "      <td>TELOK BLANGAH CRES</td>\n",
       "      <td>10 TO 12</td>\n",
       "      <td>31.0</td>\n",
       "      <td>Improved</td>\n",
       "      <td>1975</td>\n",
       "      <td>157000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4279</th>\n",
       "      <td>2020-07</td>\n",
       "      <td>GEYLANG</td>\n",
       "      <td>2 ROOM</td>\n",
       "      <td>72</td>\n",
       "      <td>CIRCUIT RD</td>\n",
       "      <td>10 TO 12</td>\n",
       "      <td>42.0</td>\n",
       "      <td>Standard</td>\n",
       "      <td>1969</td>\n",
       "      <td>160000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4228</th>\n",
       "      <td>2019-07</td>\n",
       "      <td>GEYLANG</td>\n",
       "      <td>2 ROOM</td>\n",
       "      <td>71</td>\n",
       "      <td>CIRCUIT RD</td>\n",
       "      <td>04 TO 06</td>\n",
       "      <td>42.0</td>\n",
       "      <td>Standard</td>\n",
       "      <td>1969</td>\n",
       "      <td>160000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6699</th>\n",
       "      <td>2015-03</td>\n",
       "      <td>KALLANG/WHAMPOA</td>\n",
       "      <td>3 ROOM</td>\n",
       "      <td>53</td>\n",
       "      <td>JLN MA'MOR</td>\n",
       "      <td>01 TO 03</td>\n",
       "      <td>280.0</td>\n",
       "      <td>Terrace</td>\n",
       "      <td>1972</td>\n",
       "      <td>1060000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1724</th>\n",
       "      <td>2020-08</td>\n",
       "      <td>BUKIT MERAH</td>\n",
       "      <td>4 ROOM</td>\n",
       "      <td>43</td>\n",
       "      <td>MOH GUAN TER</td>\n",
       "      <td>04 TO 06</td>\n",
       "      <td>131.0</td>\n",
       "      <td>Adjoined flat</td>\n",
       "      <td>1973</td>\n",
       "      <td>1088000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6897</th>\n",
       "      <td>2021-04</td>\n",
       "      <td>KALLANG/WHAMPOA</td>\n",
       "      <td>3 ROOM</td>\n",
       "      <td>51</td>\n",
       "      <td>JLN BAHAGIA</td>\n",
       "      <td>01 TO 03</td>\n",
       "      <td>178.0</td>\n",
       "      <td>Terrace</td>\n",
       "      <td>1972</td>\n",
       "      <td>1140000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6746</th>\n",
       "      <td>2016-12</td>\n",
       "      <td>KALLANG/WHAMPOA</td>\n",
       "      <td>3 ROOM</td>\n",
       "      <td>57</td>\n",
       "      <td>JLN MA'MOR</td>\n",
       "      <td>01 TO 03</td>\n",
       "      <td>259.0</td>\n",
       "      <td>Terrace</td>\n",
       "      <td>1972</td>\n",
       "      <td>1150000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6806</th>\n",
       "      <td>2018-09</td>\n",
       "      <td>KALLANG/WHAMPOA</td>\n",
       "      <td>3 ROOM</td>\n",
       "      <td>41</td>\n",
       "      <td>JLN BAHAGIA</td>\n",
       "      <td>01 TO 03</td>\n",
       "      <td>237.0</td>\n",
       "      <td>Terrace</td>\n",
       "      <td>1972</td>\n",
       "      <td>1185000.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>15000 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Tranc_YearMonth             town flat_type block         street_name  \\\n",
       "13185         2020-02        TOA PAYOH    3 ROOM    26      TOA PAYOH EAST   \n",
       "4246          2019-10          GEYLANG    2 ROOM    68          CIRCUIT RD   \n",
       "2273          2020-02      BUKIT MERAH    1 ROOM     7  TELOK BLANGAH CRES   \n",
       "4279          2020-07          GEYLANG    2 ROOM    72          CIRCUIT RD   \n",
       "4228          2019-07          GEYLANG    2 ROOM    71          CIRCUIT RD   \n",
       "...               ...              ...       ...   ...                 ...   \n",
       "6699          2015-03  KALLANG/WHAMPOA    3 ROOM    53          JLN MA'MOR   \n",
       "1724          2020-08      BUKIT MERAH    4 ROOM    43        MOH GUAN TER   \n",
       "6897          2021-04  KALLANG/WHAMPOA    3 ROOM    51         JLN BAHAGIA   \n",
       "6746          2016-12  KALLANG/WHAMPOA    3 ROOM    57          JLN MA'MOR   \n",
       "6806          2018-09  KALLANG/WHAMPOA    3 ROOM    41         JLN BAHAGIA   \n",
       "\n",
       "      storey_range  floor_area_sqm     flat_model  lease_commence_date  \\\n",
       "13185     10 TO 12            67.0       Improved                 1971   \n",
       "4246      04 TO 06            45.0       Standard                 1969   \n",
       "2273      10 TO 12            31.0       Improved                 1975   \n",
       "4279      10 TO 12            42.0       Standard                 1969   \n",
       "4228      04 TO 06            42.0       Standard                 1969   \n",
       "...            ...             ...            ...                  ...   \n",
       "6699      01 TO 03           280.0        Terrace                 1972   \n",
       "1724      04 TO 06           131.0  Adjoined flat                 1973   \n",
       "6897      01 TO 03           178.0        Terrace                 1972   \n",
       "6746      01 TO 03           259.0        Terrace                 1972   \n",
       "6806      01 TO 03           237.0        Terrace                 1972   \n",
       "\n",
       "       resale_price  \n",
       "13185      140000.0  \n",
       "4246       150000.0  \n",
       "2273       157000.0  \n",
       "4279       160000.0  \n",
       "4228       160000.0  \n",
       "...             ...  \n",
       "6699      1060000.0  \n",
       "1724      1088000.0  \n",
       "6897      1140000.0  \n",
       "6746      1150000.0  \n",
       "6806      1185000.0  \n",
       "\n",
       "[15000 rows x 10 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.sort_values('resale_price') #default ascending"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>Tranc_YearMonth</th>\n",
       "      <th>town</th>\n",
       "      <th>flat_type</th>\n",
       "      <th>block</th>\n",
       "      <th>street_name</th>\n",
       "      <th>storey_range</th>\n",
       "      <th>floor_area_sqm</th>\n",
       "      <th>flat_model</th>\n",
       "      <th>lease_commence_date</th>\n",
       "      <th>resale_price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6806</th>\n",
       "      <td>2018-09</td>\n",
       "      <td>KALLANG/WHAMPOA</td>\n",
       "      <td>3 ROOM</td>\n",
       "      <td>41</td>\n",
       "      <td>JLN BAHAGIA</td>\n",
       "      <td>01 TO 03</td>\n",
       "      <td>237.0</td>\n",
       "      <td>Terrace</td>\n",
       "      <td>1972</td>\n",
       "      <td>1185000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6746</th>\n",
       "      <td>2016-12</td>\n",
       "      <td>KALLANG/WHAMPOA</td>\n",
       "      <td>3 ROOM</td>\n",
       "      <td>57</td>\n",
       "      <td>JLN MA'MOR</td>\n",
       "      <td>01 TO 03</td>\n",
       "      <td>259.0</td>\n",
       "      <td>Terrace</td>\n",
       "      <td>1972</td>\n",
       "      <td>1150000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6897</th>\n",
       "      <td>2021-04</td>\n",
       "      <td>KALLANG/WHAMPOA</td>\n",
       "      <td>3 ROOM</td>\n",
       "      <td>51</td>\n",
       "      <td>JLN BAHAGIA</td>\n",
       "      <td>01 TO 03</td>\n",
       "      <td>178.0</td>\n",
       "      <td>Terrace</td>\n",
       "      <td>1972</td>\n",
       "      <td>1140000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1724</th>\n",
       "      <td>2020-08</td>\n",
       "      <td>BUKIT MERAH</td>\n",
       "      <td>4 ROOM</td>\n",
       "      <td>43</td>\n",
       "      <td>MOH GUAN TER</td>\n",
       "      <td>04 TO 06</td>\n",
       "      <td>131.0</td>\n",
       "      <td>Adjoined flat</td>\n",
       "      <td>1973</td>\n",
       "      <td>1088000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6699</th>\n",
       "      <td>2015-03</td>\n",
       "      <td>KALLANG/WHAMPOA</td>\n",
       "      <td>3 ROOM</td>\n",
       "      <td>53</td>\n",
       "      <td>JLN MA'MOR</td>\n",
       "      <td>01 TO 03</td>\n",
       "      <td>280.0</td>\n",
       "      <td>Terrace</td>\n",
       "      <td>1972</td>\n",
       "      <td>1060000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2276</th>\n",
       "      <td>2020-03</td>\n",
       "      <td>BUKIT MERAH</td>\n",
       "      <td>1 ROOM</td>\n",
       "      <td>7</td>\n",
       "      <td>TELOK BLANGAH CRES</td>\n",
       "      <td>01 TO 03</td>\n",
       "      <td>31.0</td>\n",
       "      <td>Improved</td>\n",
       "      <td>1975</td>\n",
       "      <td>160000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4633</th>\n",
       "      <td>2018-09</td>\n",
       "      <td>GEYLANG</td>\n",
       "      <td>2 ROOM</td>\n",
       "      <td>39</td>\n",
       "      <td>CIRCUIT RD</td>\n",
       "      <td>04 TO 06</td>\n",
       "      <td>47.0</td>\n",
       "      <td>Standard</td>\n",
       "      <td>1971</td>\n",
       "      <td>160000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2273</th>\n",
       "      <td>2020-02</td>\n",
       "      <td>BUKIT MERAH</td>\n",
       "      <td>1 ROOM</td>\n",
       "      <td>7</td>\n",
       "      <td>TELOK BLANGAH CRES</td>\n",
       "      <td>10 TO 12</td>\n",
       "      <td>31.0</td>\n",
       "      <td>Improved</td>\n",
       "      <td>1975</td>\n",
       "      <td>157000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4246</th>\n",
       "      <td>2019-10</td>\n",
       "      <td>GEYLANG</td>\n",
       "      <td>2 ROOM</td>\n",
       "      <td>68</td>\n",
       "      <td>CIRCUIT RD</td>\n",
       "      <td>04 TO 06</td>\n",
       "      <td>45.0</td>\n",
       "      <td>Standard</td>\n",
       "      <td>1969</td>\n",
       "      <td>150000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13185</th>\n",
       "      <td>2020-02</td>\n",
       "      <td>TOA PAYOH</td>\n",
       "      <td>3 ROOM</td>\n",
       "      <td>26</td>\n",
       "      <td>TOA PAYOH EAST</td>\n",
       "      <td>10 TO 12</td>\n",
       "      <td>67.0</td>\n",
       "      <td>Improved</td>\n",
       "      <td>1971</td>\n",
       "      <td>140000.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>15000 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Tranc_YearMonth             town flat_type block         street_name  \\\n",
       "6806          2018-09  KALLANG/WHAMPOA    3 ROOM    41         JLN BAHAGIA   \n",
       "6746          2016-12  KALLANG/WHAMPOA    3 ROOM    57          JLN MA'MOR   \n",
       "6897          2021-04  KALLANG/WHAMPOA    3 ROOM    51         JLN BAHAGIA   \n",
       "1724          2020-08      BUKIT MERAH    4 ROOM    43        MOH GUAN TER   \n",
       "6699          2015-03  KALLANG/WHAMPOA    3 ROOM    53          JLN MA'MOR   \n",
       "...               ...              ...       ...   ...                 ...   \n",
       "2276          2020-03      BUKIT MERAH    1 ROOM     7  TELOK BLANGAH CRES   \n",
       "4633          2018-09          GEYLANG    2 ROOM    39          CIRCUIT RD   \n",
       "2273          2020-02      BUKIT MERAH    1 ROOM     7  TELOK BLANGAH CRES   \n",
       "4246          2019-10          GEYLANG    2 ROOM    68          CIRCUIT RD   \n",
       "13185         2020-02        TOA PAYOH    3 ROOM    26      TOA PAYOH EAST   \n",
       "\n",
       "      storey_range  floor_area_sqm     flat_model  lease_commence_date  \\\n",
       "6806      01 TO 03           237.0        Terrace                 1972   \n",
       "6746      01 TO 03           259.0        Terrace                 1972   \n",
       "6897      01 TO 03           178.0        Terrace                 1972   \n",
       "1724      04 TO 06           131.0  Adjoined flat                 1973   \n",
       "6699      01 TO 03           280.0        Terrace                 1972   \n",
       "...            ...             ...            ...                  ...   \n",
       "2276      01 TO 03            31.0       Improved                 1975   \n",
       "4633      04 TO 06            47.0       Standard                 1971   \n",
       "2273      10 TO 12            31.0       Improved                 1975   \n",
       "4246      04 TO 06            45.0       Standard                 1969   \n",
       "13185     10 TO 12            67.0       Improved                 1971   \n",
       "\n",
       "       resale_price  \n",
       "6806      1185000.0  \n",
       "6746      1150000.0  \n",
       "6897      1140000.0  \n",
       "1724      1088000.0  \n",
       "6699      1060000.0  \n",
       "...             ...  \n",
       "2276       160000.0  \n",
       "4633       160000.0  \n",
       "2273       157000.0  \n",
       "4246       150000.0  \n",
       "13185      140000.0  \n",
       "\n",
       "[15000 rows x 10 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.sort_values('resale_price', ascending=False) #descending"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>Tranc_YearMonth</th>\n",
       "      <th>town</th>\n",
       "      <th>flat_type</th>\n",
       "      <th>block</th>\n",
       "      <th>street_name</th>\n",
       "      <th>storey_range</th>\n",
       "      <th>floor_area_sqm</th>\n",
       "      <th>flat_model</th>\n",
       "      <th>lease_commence_date</th>\n",
       "      <th>resale_price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>11639</th>\n",
       "      <td>2019-08</td>\n",
       "      <td>QUEENSTOWN</td>\n",
       "      <td>5 ROOM</td>\n",
       "      <td>3</td>\n",
       "      <td>GHIM MOH RD</td>\n",
       "      <td>13 TO 15</td>\n",
       "      <td>150.0</td>\n",
       "      <td>Adjoined flat</td>\n",
       "      <td>1976</td>\n",
       "      <td>860000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2831</th>\n",
       "      <td>2018-09</td>\n",
       "      <td>BUKIT MERAH</td>\n",
       "      <td>5 ROOM</td>\n",
       "      <td>48</td>\n",
       "      <td>TELOK BLANGAH DR</td>\n",
       "      <td>13 TO 15</td>\n",
       "      <td>123.0</td>\n",
       "      <td>Standard</td>\n",
       "      <td>1976</td>\n",
       "      <td>830000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11541</th>\n",
       "      <td>2016-08</td>\n",
       "      <td>QUEENSTOWN</td>\n",
       "      <td>5 ROOM</td>\n",
       "      <td>2</td>\n",
       "      <td>GHIM MOH RD</td>\n",
       "      <td>04 TO 06</td>\n",
       "      <td>150.0</td>\n",
       "      <td>Adjoined flat</td>\n",
       "      <td>1976</td>\n",
       "      <td>830000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2946</th>\n",
       "      <td>2020-01</td>\n",
       "      <td>BUKIT MERAH</td>\n",
       "      <td>5 ROOM</td>\n",
       "      <td>48</td>\n",
       "      <td>TELOK BLANGAH DR</td>\n",
       "      <td>22 TO 24</td>\n",
       "      <td>123.0</td>\n",
       "      <td>Standard</td>\n",
       "      <td>1976</td>\n",
       "      <td>825000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2738</th>\n",
       "      <td>2017-05</td>\n",
       "      <td>BUKIT MERAH</td>\n",
       "      <td>5 ROOM</td>\n",
       "      <td>49</td>\n",
       "      <td>TELOK BLANGAH DR</td>\n",
       "      <td>22 TO 24</td>\n",
       "      <td>125.0</td>\n",
       "      <td>Standard</td>\n",
       "      <td>1976</td>\n",
       "      <td>820000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5400</th>\n",
       "      <td>2020-01</td>\n",
       "      <td>JURONG EAST</td>\n",
       "      <td>3 ROOM</td>\n",
       "      <td>37</td>\n",
       "      <td>TEBAN GDNS RD</td>\n",
       "      <td>04 TO 06</td>\n",
       "      <td>67.0</td>\n",
       "      <td>Improved</td>\n",
       "      <td>1966</td>\n",
       "      <td>220000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5398</th>\n",
       "      <td>2019-07</td>\n",
       "      <td>JURONG EAST</td>\n",
       "      <td>3 ROOM</td>\n",
       "      <td>39</td>\n",
       "      <td>TEBAN GDNS RD</td>\n",
       "      <td>01 TO 03</td>\n",
       "      <td>67.0</td>\n",
       "      <td>Improved</td>\n",
       "      <td>1966</td>\n",
       "      <td>218000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5397</th>\n",
       "      <td>2018-09</td>\n",
       "      <td>JURONG EAST</td>\n",
       "      <td>3 ROOM</td>\n",
       "      <td>37</td>\n",
       "      <td>TEBAN GDNS RD</td>\n",
       "      <td>01 TO 03</td>\n",
       "      <td>67.0</td>\n",
       "      <td>Improved</td>\n",
       "      <td>1966</td>\n",
       "      <td>215000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5401</th>\n",
       "      <td>2020-03</td>\n",
       "      <td>JURONG EAST</td>\n",
       "      <td>3 ROOM</td>\n",
       "      <td>38</td>\n",
       "      <td>TEBAN GDNS RD</td>\n",
       "      <td>04 TO 06</td>\n",
       "      <td>67.0</td>\n",
       "      <td>Improved</td>\n",
       "      <td>1966</td>\n",
       "      <td>210000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5393</th>\n",
       "      <td>2017-10</td>\n",
       "      <td>JURONG EAST</td>\n",
       "      <td>3 ROOM</td>\n",
       "      <td>37</td>\n",
       "      <td>TEBAN GDNS RD</td>\n",
       "      <td>01 TO 03</td>\n",
       "      <td>67.0</td>\n",
       "      <td>Improved</td>\n",
       "      <td>1966</td>\n",
       "      <td>200000.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>15000 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Tranc_YearMonth         town flat_type block       street_name  \\\n",
       "11639         2019-08   QUEENSTOWN    5 ROOM     3       GHIM MOH RD   \n",
       "2831          2018-09  BUKIT MERAH    5 ROOM    48  TELOK BLANGAH DR   \n",
       "11541         2016-08   QUEENSTOWN    5 ROOM     2       GHIM MOH RD   \n",
       "2946          2020-01  BUKIT MERAH    5 ROOM    48  TELOK BLANGAH DR   \n",
       "2738          2017-05  BUKIT MERAH    5 ROOM    49  TELOK BLANGAH DR   \n",
       "...               ...          ...       ...   ...               ...   \n",
       "5400          2020-01  JURONG EAST    3 ROOM    37     TEBAN GDNS RD   \n",
       "5398          2019-07  JURONG EAST    3 ROOM    39     TEBAN GDNS RD   \n",
       "5397          2018-09  JURONG EAST    3 ROOM    37     TEBAN GDNS RD   \n",
       "5401          2020-03  JURONG EAST    3 ROOM    38     TEBAN GDNS RD   \n",
       "5393          2017-10  JURONG EAST    3 ROOM    37     TEBAN GDNS RD   \n",
       "\n",
       "      storey_range  floor_area_sqm     flat_model  lease_commence_date  \\\n",
       "11639     13 TO 15           150.0  Adjoined flat                 1976   \n",
       "2831      13 TO 15           123.0       Standard                 1976   \n",
       "11541     04 TO 06           150.0  Adjoined flat                 1976   \n",
       "2946      22 TO 24           123.0       Standard                 1976   \n",
       "2738      22 TO 24           125.0       Standard                 1976   \n",
       "...            ...             ...            ...                  ...   \n",
       "5400      04 TO 06            67.0       Improved                 1966   \n",
       "5398      01 TO 03            67.0       Improved                 1966   \n",
       "5397      01 TO 03            67.0       Improved                 1966   \n",
       "5401      04 TO 06            67.0       Improved                 1966   \n",
       "5393      01 TO 03            67.0       Improved                 1966   \n",
       "\n",
       "       resale_price  \n",
       "11639      860000.0  \n",
       "2831       830000.0  \n",
       "11541      830000.0  \n",
       "2946       825000.0  \n",
       "2738       820000.0  \n",
       "...             ...  \n",
       "5400       220000.0  \n",
       "5398       218000.0  \n",
       "5397       215000.0  \n",
       "5401       210000.0  \n",
       "5393       200000.0  \n",
       "\n",
       "[15000 rows x 10 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.sort_values(['lease_commence_date', 'resale_price'], ascending=False) #descending by 2 columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>Tranc_YearMonth</th>\n",
       "      <th>town</th>\n",
       "      <th>flat_type</th>\n",
       "      <th>block</th>\n",
       "      <th>street_name</th>\n",
       "      <th>storey_range</th>\n",
       "      <th>floor_area_sqm</th>\n",
       "      <th>flat_model</th>\n",
       "      <th>lease_commence_date</th>\n",
       "      <th>resale_price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>14881</th>\n",
       "      <td>2019-04</td>\n",
       "      <td>WOODLANDS</td>\n",
       "      <td>3 ROOM</td>\n",
       "      <td>15</td>\n",
       "      <td>MARSILING LANE</td>\n",
       "      <td>01 TO 03</td>\n",
       "      <td>65.0</td>\n",
       "      <td>Improved</td>\n",
       "      <td>1976</td>\n",
       "      <td>183500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6180</th>\n",
       "      <td>2019-08</td>\n",
       "      <td>JURONG WEST</td>\n",
       "      <td>3 ROOM</td>\n",
       "      <td>208</td>\n",
       "      <td>BOON LAY PL</td>\n",
       "      <td>01 TO 03</td>\n",
       "      <td>65.0</td>\n",
       "      <td>Improved</td>\n",
       "      <td>1976</td>\n",
       "      <td>185000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6147</th>\n",
       "      <td>2018-10</td>\n",
       "      <td>JURONG WEST</td>\n",
       "      <td>3 ROOM</td>\n",
       "      <td>209</td>\n",
       "      <td>BOON LAY PL</td>\n",
       "      <td>04 TO 06</td>\n",
       "      <td>65.0</td>\n",
       "      <td>Improved</td>\n",
       "      <td>1976</td>\n",
       "      <td>190000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6189</th>\n",
       "      <td>2019-09</td>\n",
       "      <td>JURONG WEST</td>\n",
       "      <td>3 ROOM</td>\n",
       "      <td>208</td>\n",
       "      <td>BOON LAY PL</td>\n",
       "      <td>07 TO 09</td>\n",
       "      <td>65.0</td>\n",
       "      <td>Improved</td>\n",
       "      <td>1976</td>\n",
       "      <td>190000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2975</th>\n",
       "      <td>2020-07</td>\n",
       "      <td>BUKIT MERAH</td>\n",
       "      <td>2 ROOM</td>\n",
       "      <td>45</td>\n",
       "      <td>TELOK BLANGAH DR</td>\n",
       "      <td>01 TO 03</td>\n",
       "      <td>45.0</td>\n",
       "      <td>Improved</td>\n",
       "      <td>1976</td>\n",
       "      <td>194000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5389</th>\n",
       "      <td>2015-03</td>\n",
       "      <td>JURONG EAST</td>\n",
       "      <td>3 ROOM</td>\n",
       "      <td>39</td>\n",
       "      <td>TEBAN GDNS RD</td>\n",
       "      <td>01 TO 03</td>\n",
       "      <td>67.0</td>\n",
       "      <td>Improved</td>\n",
       "      <td>1966</td>\n",
       "      <td>260000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5390</th>\n",
       "      <td>2015-08</td>\n",
       "      <td>JURONG EAST</td>\n",
       "      <td>3 ROOM</td>\n",
       "      <td>37</td>\n",
       "      <td>TEBAN GDNS RD</td>\n",
       "      <td>04 TO 06</td>\n",
       "      <td>67.0</td>\n",
       "      <td>Improved</td>\n",
       "      <td>1966</td>\n",
       "      <td>289000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5406</th>\n",
       "      <td>2013-07</td>\n",
       "      <td>JURONG EAST</td>\n",
       "      <td>3 ROOM</td>\n",
       "      <td>38</td>\n",
       "      <td>TEBAN GDNS RD</td>\n",
       "      <td>01 TO 03</td>\n",
       "      <td>67.0</td>\n",
       "      <td>Improved</td>\n",
       "      <td>1966</td>\n",
       "      <td>310000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5405</th>\n",
       "      <td>2013-04</td>\n",
       "      <td>JURONG EAST</td>\n",
       "      <td>3 ROOM</td>\n",
       "      <td>37</td>\n",
       "      <td>TEBAN GDNS RD</td>\n",
       "      <td>04 TO 06</td>\n",
       "      <td>67.0</td>\n",
       "      <td>Improved</td>\n",
       "      <td>1966</td>\n",
       "      <td>311000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5404</th>\n",
       "      <td>2013-01</td>\n",
       "      <td>JURONG EAST</td>\n",
       "      <td>3 ROOM</td>\n",
       "      <td>39</td>\n",
       "      <td>TEBAN GDNS RD</td>\n",
       "      <td>01 TO 03</td>\n",
       "      <td>67.0</td>\n",
       "      <td>Improved</td>\n",
       "      <td>1966</td>\n",
       "      <td>315000.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>15000 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Tranc_YearMonth         town flat_type block       street_name  \\\n",
       "14881         2019-04    WOODLANDS    3 ROOM    15    MARSILING LANE   \n",
       "6180          2019-08  JURONG WEST    3 ROOM   208       BOON LAY PL   \n",
       "6147          2018-10  JURONG WEST    3 ROOM   209       BOON LAY PL   \n",
       "6189          2019-09  JURONG WEST    3 ROOM   208       BOON LAY PL   \n",
       "2975          2020-07  BUKIT MERAH    2 ROOM    45  TELOK BLANGAH DR   \n",
       "...               ...          ...       ...   ...               ...   \n",
       "5389          2015-03  JURONG EAST    3 ROOM    39     TEBAN GDNS RD   \n",
       "5390          2015-08  JURONG EAST    3 ROOM    37     TEBAN GDNS RD   \n",
       "5406          2013-07  JURONG EAST    3 ROOM    38     TEBAN GDNS RD   \n",
       "5405          2013-04  JURONG EAST    3 ROOM    37     TEBAN GDNS RD   \n",
       "5404          2013-01  JURONG EAST    3 ROOM    39     TEBAN GDNS RD   \n",
       "\n",
       "      storey_range  floor_area_sqm flat_model  lease_commence_date  \\\n",
       "14881     01 TO 03            65.0   Improved                 1976   \n",
       "6180      01 TO 03            65.0   Improved                 1976   \n",
       "6147      04 TO 06            65.0   Improved                 1976   \n",
       "6189      07 TO 09            65.0   Improved                 1976   \n",
       "2975      01 TO 03            45.0   Improved                 1976   \n",
       "...            ...             ...        ...                  ...   \n",
       "5389      01 TO 03            67.0   Improved                 1966   \n",
       "5390      04 TO 06            67.0   Improved                 1966   \n",
       "5406      01 TO 03            67.0   Improved                 1966   \n",
       "5405      04 TO 06            67.0   Improved                 1966   \n",
       "5404      01 TO 03            67.0   Improved                 1966   \n",
       "\n",
       "       resale_price  \n",
       "14881      183500.0  \n",
       "6180       185000.0  \n",
       "6147       190000.0  \n",
       "6189       190000.0  \n",
       "2975       194000.0  \n",
       "...             ...  \n",
       "5389       260000.0  \n",
       "5390       289000.0  \n",
       "5406       310000.0  \n",
       "5405       311000.0  \n",
       "5404       315000.0  \n",
       "\n",
       "[15000 rows x 10 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.sort_values(['lease_commence_date', 'resale_price'], ascending=[False,True]) #specify different orders by 2 columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Handling Missing & Dirty Values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> Dealing with null values and dirty data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 15000 entries, 0 to 14999\n",
      "Data columns (total 10 columns):\n",
      " #   Column               Non-Null Count  Dtype  \n",
      "---  ------               --------------  -----  \n",
      " 0   Tranc_YearMonth      15000 non-null  object \n",
      " 1   town                 14429 non-null  object \n",
      " 2   flat_type            15000 non-null  object \n",
      " 3   block                15000 non-null  object \n",
      " 4   street_name          15000 non-null  object \n",
      " 5   storey_range         15000 non-null  object \n",
      " 6   floor_area_sqm       15000 non-null  float64\n",
      " 7   flat_model           15000 non-null  object \n",
      " 8   lease_commence_date  15000 non-null  int64  \n",
      " 9   resale_price         15000 non-null  float64\n",
      "dtypes: float64(2), int64(1), object(7)\n",
      "memory usage: 1.1+ MB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> remove null values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>Tranc_YearMonth</th>\n",
       "      <th>town</th>\n",
       "      <th>flat_type</th>\n",
       "      <th>block</th>\n",
       "      <th>street_name</th>\n",
       "      <th>storey_range</th>\n",
       "      <th>floor_area_sqm</th>\n",
       "      <th>flat_model</th>\n",
       "      <th>lease_commence_date</th>\n",
       "      <th>resale_price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2015-01</td>\n",
       "      <td>ANG MOO KIO</td>\n",
       "      <td>3 ROOM</td>\n",
       "      <td>218</td>\n",
       "      <td>ANG MO KIO AVE 1</td>\n",
       "      <td>07 TO 09</td>\n",
       "      <td>67.0</td>\n",
       "      <td>New Generation</td>\n",
       "      <td>1976</td>\n",
       "      <td>308000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2015-01</td>\n",
       "      <td>ANG MOO KIO</td>\n",
       "      <td>4 ROOM</td>\n",
       "      <td>207</td>\n",
       "      <td>ANG MO KIO AVE 1</td>\n",
       "      <td>01 TO 03</td>\n",
       "      <td>97.0</td>\n",
       "      <td>New Generation</td>\n",
       "      <td>1976</td>\n",
       "      <td>475000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2015-02</td>\n",
       "      <td>ANG MOO KIO</td>\n",
       "      <td>3 ROOM</td>\n",
       "      <td>214</td>\n",
       "      <td>ANG MO KIO AVE 3</td>\n",
       "      <td>07 TO 09</td>\n",
       "      <td>67.0</td>\n",
       "      <td>New Generation</td>\n",
       "      <td>1976</td>\n",
       "      <td>315000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2015-04</td>\n",
       "      <td>ANG MOO KIO</td>\n",
       "      <td>3 ROOM</td>\n",
       "      <td>214</td>\n",
       "      <td>ANG MO KIO AVE 3</td>\n",
       "      <td>07 TO 09</td>\n",
       "      <td>67.0</td>\n",
       "      <td>New Generation</td>\n",
       "      <td>1976</td>\n",
       "      <td>285000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2015-04</td>\n",
       "      <td>ANG MOO KIO</td>\n",
       "      <td>3 ROOM</td>\n",
       "      <td>213</td>\n",
       "      <td>ANG MO KIO AVE 3</td>\n",
       "      <td>07 TO 09</td>\n",
       "      <td>67.0</td>\n",
       "      <td>New Generation</td>\n",
       "      <td>1976</td>\n",
       "      <td>305000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14995</th>\n",
       "      <td>2013-04</td>\n",
       "      <td>WOODLANDS</td>\n",
       "      <td>5 ROOM</td>\n",
       "      <td>11</td>\n",
       "      <td>MARSILING DR</td>\n",
       "      <td>19 TO 21</td>\n",
       "      <td>123.0</td>\n",
       "      <td>Standard</td>\n",
       "      <td>1976</td>\n",
       "      <td>445000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14996</th>\n",
       "      <td>2013-04</td>\n",
       "      <td>WOODLANDS</td>\n",
       "      <td>5 ROOM</td>\n",
       "      <td>9</td>\n",
       "      <td>MARSILING DR</td>\n",
       "      <td>19 TO 21</td>\n",
       "      <td>117.0</td>\n",
       "      <td>Standard</td>\n",
       "      <td>1976</td>\n",
       "      <td>510000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14997</th>\n",
       "      <td>2013-05</td>\n",
       "      <td>WOODLANDS</td>\n",
       "      <td>3 ROOM</td>\n",
       "      <td>6</td>\n",
       "      <td>MARSILING DR</td>\n",
       "      <td>04 TO 06</td>\n",
       "      <td>65.0</td>\n",
       "      <td>Improved</td>\n",
       "      <td>1976</td>\n",
       "      <td>292000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14998</th>\n",
       "      <td>2013-05</td>\n",
       "      <td>WOODLANDS</td>\n",
       "      <td>4 ROOM</td>\n",
       "      <td>7</td>\n",
       "      <td>MARSILING DR</td>\n",
       "      <td>04 TO 06</td>\n",
       "      <td>88.0</td>\n",
       "      <td>Improved</td>\n",
       "      <td>1976</td>\n",
       "      <td>360000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14999</th>\n",
       "      <td>2013-05</td>\n",
       "      <td>WOODLANDS</td>\n",
       "      <td>5 ROOM</td>\n",
       "      <td>10</td>\n",
       "      <td>MARSILING DR</td>\n",
       "      <td>07 TO 09</td>\n",
       "      <td>123.0</td>\n",
       "      <td>Standard</td>\n",
       "      <td>1976</td>\n",
       "      <td>440000.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>14429 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Tranc_YearMonth         town flat_type block       street_name  \\\n",
       "0             2015-01  ANG MOO KIO    3 ROOM   218  ANG MO KIO AVE 1   \n",
       "1             2015-01  ANG MOO KIO    4 ROOM   207  ANG MO KIO AVE 1   \n",
       "2             2015-02  ANG MOO KIO    3 ROOM   214  ANG MO KIO AVE 3   \n",
       "3             2015-04  ANG MOO KIO    3 ROOM   214  ANG MO KIO AVE 3   \n",
       "4             2015-04  ANG MOO KIO    3 ROOM   213  ANG MO KIO AVE 3   \n",
       "...               ...          ...       ...   ...               ...   \n",
       "14995         2013-04    WOODLANDS    5 ROOM    11      MARSILING DR   \n",
       "14996         2013-04    WOODLANDS    5 ROOM     9      MARSILING DR   \n",
       "14997         2013-05    WOODLANDS    3 ROOM     6      MARSILING DR   \n",
       "14998         2013-05    WOODLANDS    4 ROOM     7      MARSILING DR   \n",
       "14999         2013-05    WOODLANDS    5 ROOM    10      MARSILING DR   \n",
       "\n",
       "      storey_range  floor_area_sqm      flat_model  lease_commence_date  \\\n",
       "0         07 TO 09            67.0  New Generation                 1976   \n",
       "1         01 TO 03            97.0  New Generation                 1976   \n",
       "2         07 TO 09            67.0  New Generation                 1976   \n",
       "3         07 TO 09            67.0  New Generation                 1976   \n",
       "4         07 TO 09            67.0  New Generation                 1976   \n",
       "...            ...             ...             ...                  ...   \n",
       "14995     19 TO 21           123.0        Standard                 1976   \n",
       "14996     19 TO 21           117.0        Standard                 1976   \n",
       "14997     04 TO 06            65.0        Improved                 1976   \n",
       "14998     04 TO 06            88.0        Improved                 1976   \n",
       "14999     07 TO 09           123.0        Standard                 1976   \n",
       "\n",
       "       resale_price  \n",
       "0          308000.0  \n",
       "1          475000.0  \n",
       "2          315000.0  \n",
       "3          285000.0  \n",
       "4          305000.0  \n",
       "...             ...  \n",
       "14995      445000.0  \n",
       "14996      510000.0  \n",
       "14997      292000.0  \n",
       "14998      360000.0  \n",
       "14999      440000.0  \n",
       "\n",
       "[14429 rows x 10 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# this does not store the result\n",
    "# we will need to assign the output to a variable or overwrite the current df\n",
    "# df = df.dropna()\n",
    "df.dropna()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> update null values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# This replace the missing values (NaN) with the string 'BEDOK'\n",
    "df['town'] = df['town'].fillna('BEDOK')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 15000 entries, 0 to 14999\n",
      "Data columns (total 10 columns):\n",
      " #   Column               Non-Null Count  Dtype  \n",
      "---  ------               --------------  -----  \n",
      " 0   Tranc_YearMonth      15000 non-null  object \n",
      " 1   town                 15000 non-null  object \n",
      " 2   flat_type            15000 non-null  object \n",
      " 3   block                15000 non-null  object \n",
      " 4   street_name          15000 non-null  object \n",
      " 5   storey_range         15000 non-null  object \n",
      " 6   floor_area_sqm       15000 non-null  float64\n",
      " 7   flat_model           15000 non-null  object \n",
      " 8   lease_commence_date  15000 non-null  int64  \n",
      " 9   resale_price         15000 non-null  float64\n",
      "dtypes: float64(2), int64(1), object(7)\n",
      "memory usage: 1.1+ MB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> replace values in a DataFrame's column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['ANG MOO KIO', 'ANG MO KIO', 'BEDOK', 'BISHAN', 'BUKIT MERAH',\n",
       "       'BUKIT TIMAH', 'CENTRAL AREA', 'GEYLANG', 'HOUGANG', 'JURONG EAST',\n",
       "       'JURONG WEST', 'KALLANG/WHAMPOA', 'MARINE PARADE', 'QUEENSTOWN',\n",
       "       'TOA PAYOH', 'WOODLANDS'], dtype=object)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['town'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Remember to reassigned the revised values into existing column (or a new column)\n",
    "df['town'] = df['town'].replace('ANG MOO KIO', 'ANG MO KIO')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['ANG MO KIO', 'BEDOK', 'BISHAN', 'BUKIT MERAH', 'BUKIT TIMAH',\n",
       "       'CENTRAL AREA', 'GEYLANG', 'HOUGANG', 'JURONG EAST', 'JURONG WEST',\n",
       "       'KALLANG/WHAMPOA', 'MARINE PARADE', 'QUEENSTOWN', 'TOA PAYOH',\n",
       "       'WOODLANDS'], dtype=object)"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['town'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Aggregating Numbers based on Category"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "qIkatepWVIJu"
   },
   "source": [
    "> GroupBy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "id": "16JqmZT7VIJu"
   },
   "outputs": [],
   "source": [
    "groupby_town = df.groupby('town')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "id": "RtaSERBsVIJv"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "town\n",
       "ANG MO KIO         326890.235294\n",
       "BEDOK              360959.922942\n",
       "BISHAN             295249.210084\n",
       "BUKIT MERAH        381027.219172\n",
       "BUKIT TIMAH        550988.675159\n",
       "CENTRAL AREA       382266.286996\n",
       "GEYLANG            287563.004905\n",
       "HOUGANG            300874.426667\n",
       "JURONG EAST        245500.000000\n",
       "JURONG WEST        265373.744797\n",
       "KALLANG/WHAMPOA    369058.760257\n",
       "MARINE PARADE      507970.847682\n",
       "QUEENSTOWN         380540.040733\n",
       "TOA PAYOH          318940.731699\n",
       "WOODLANDS          273703.839506\n",
       "Name: resale_price, dtype: float64"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "groupby_town['resale_price'].mean() #mean of numerical values, you can try other Math functions!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "groupby_town_flattype = df.groupby(['town','flat_type'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "id": "MKrGzwLZVIJw"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "town             flat_type\n",
       "ANG MO KIO       3 ROOM       309624.379562\n",
       "                 4 ROOM       398569.696970\n",
       "BEDOK            3 ROOM       299674.814070\n",
       "                 4 ROOM       378142.461538\n",
       "                 5 ROOM       639348.487805\n",
       "BISHAN           3 ROOM       278461.940594\n",
       "                 4 ROOM       376375.000000\n",
       "                 5 ROOM       494000.000000\n",
       "BUKIT MERAH      1 ROOM       206901.696970\n",
       "                 2 ROOM       256891.258216\n",
       "                 3 ROOM       362486.258861\n",
       "                 4 ROOM       465457.716088\n",
       "                 5 ROOM       671653.861635\n",
       "BUKIT TIMAH      3 ROOM       416806.256410\n",
       "                 4 ROOM       559840.291667\n",
       "                 5 ROOM       874903.225806\n",
       "CENTRAL AREA     2 ROOM       266767.681818\n",
       "                 3 ROOM       398194.405063\n",
       "                 4 ROOM       504423.238095\n",
       "GEYLANG          2 ROOM       225962.870588\n",
       "                 3 ROOM       274412.751502\n",
       "                 4 ROOM       465122.857143\n",
       "                 5 ROOM       676912.000000\n",
       "HOUGANG          3 ROOM       258502.705882\n",
       "                 4 ROOM       348261.411765\n",
       "                 5 ROOM       494500.000000\n",
       "JURONG EAST      3 ROOM       245500.000000\n",
       "JURONG WEST      2 ROOM       210800.000000\n",
       "                 3 ROOM       254670.994097\n",
       "                 4 ROOM       302000.000000\n",
       "                 5 ROOM       420398.283333\n",
       "KALLANG/WHAMPOA  2 ROOM       250901.375000\n",
       "                 3 ROOM       339375.547464\n",
       "                 4 ROOM       442574.228374\n",
       "                 5 ROOM       590965.225806\n",
       "MARINE PARADE    2 ROOM       245857.142857\n",
       "                 3 ROOM       399567.667257\n",
       "                 4 ROOM       521356.759717\n",
       "                 5 ROOM       801507.381188\n",
       "QUEENSTOWN       2 ROOM       244910.376471\n",
       "                 3 ROOM       354952.585253\n",
       "                 4 ROOM       544913.954918\n",
       "                 5 ROOM       771895.862069\n",
       "TOA PAYOH        2 ROOM       229655.162162\n",
       "                 3 ROOM       299230.152174\n",
       "                 4 ROOM       401168.859694\n",
       "                 5 ROOM       547929.495327\n",
       "WOODLANDS        3 ROOM       254830.024823\n",
       "                 4 ROOM       279292.986301\n",
       "                 5 ROOM       371992.000000\n",
       "Name: resale_price, dtype: float64"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "groupby_town_flattype['resale_price'].mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> Pivot Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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>flat_type</th>\n",
       "      <th>1 ROOM</th>\n",
       "      <th>2 ROOM</th>\n",
       "      <th>3 ROOM</th>\n",
       "      <th>4 ROOM</th>\n",
       "      <th>5 ROOM</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>town</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>ANG MO KIO</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>309624.379562</td>\n",
       "      <td>398569.696970</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BEDOK</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>299674.814070</td>\n",
       "      <td>378142.461538</td>\n",
       "      <td>639348.487805</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BISHAN</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>278461.940594</td>\n",
       "      <td>376375.000000</td>\n",
       "      <td>494000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BUKIT MERAH</th>\n",
       "      <td>206901.69697</td>\n",
       "      <td>256891.258216</td>\n",
       "      <td>362486.258861</td>\n",
       "      <td>465457.716088</td>\n",
       "      <td>671653.861635</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BUKIT TIMAH</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>416806.256410</td>\n",
       "      <td>559840.291667</td>\n",
       "      <td>874903.225806</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CENTRAL AREA</th>\n",
       "      <td>NaN</td>\n",
       "      <td>266767.681818</td>\n",
       "      <td>398194.405063</td>\n",
       "      <td>504423.238095</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GEYLANG</th>\n",
       "      <td>NaN</td>\n",
       "      <td>225962.870588</td>\n",
       "      <td>274412.751502</td>\n",
       "      <td>465122.857143</td>\n",
       "      <td>676912.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>HOUGANG</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>258502.705882</td>\n",
       "      <td>348261.411765</td>\n",
       "      <td>494500.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>JURONG EAST</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>245500.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>JURONG WEST</th>\n",
       "      <td>NaN</td>\n",
       "      <td>210800.000000</td>\n",
       "      <td>254670.994097</td>\n",
       "      <td>302000.000000</td>\n",
       "      <td>420398.283333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>KALLANG/WHAMPOA</th>\n",
       "      <td>NaN</td>\n",
       "      <td>250901.375000</td>\n",
       "      <td>339375.547464</td>\n",
       "      <td>442574.228374</td>\n",
       "      <td>590965.225806</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MARINE PARADE</th>\n",
       "      <td>NaN</td>\n",
       "      <td>245857.142857</td>\n",
       "      <td>399567.667257</td>\n",
       "      <td>521356.759717</td>\n",
       "      <td>801507.381188</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>QUEENSTOWN</th>\n",
       "      <td>NaN</td>\n",
       "      <td>244910.376471</td>\n",
       "      <td>354952.585253</td>\n",
       "      <td>544913.954918</td>\n",
       "      <td>771895.862069</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TOA PAYOH</th>\n",
       "      <td>NaN</td>\n",
       "      <td>229655.162162</td>\n",
       "      <td>299230.152174</td>\n",
       "      <td>401168.859694</td>\n",
       "      <td>547929.495327</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WOODLANDS</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>254830.024823</td>\n",
       "      <td>279292.986301</td>\n",
       "      <td>371992.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "flat_type              1 ROOM         2 ROOM         3 ROOM         4 ROOM  \\\n",
       "town                                                                         \n",
       "ANG MO KIO                NaN            NaN  309624.379562  398569.696970   \n",
       "BEDOK                     NaN            NaN  299674.814070  378142.461538   \n",
       "BISHAN                    NaN            NaN  278461.940594  376375.000000   \n",
       "BUKIT MERAH      206901.69697  256891.258216  362486.258861  465457.716088   \n",
       "BUKIT TIMAH               NaN            NaN  416806.256410  559840.291667   \n",
       "CENTRAL AREA              NaN  266767.681818  398194.405063  504423.238095   \n",
       "GEYLANG                   NaN  225962.870588  274412.751502  465122.857143   \n",
       "HOUGANG                   NaN            NaN  258502.705882  348261.411765   \n",
       "JURONG EAST               NaN            NaN  245500.000000            NaN   \n",
       "JURONG WEST               NaN  210800.000000  254670.994097  302000.000000   \n",
       "KALLANG/WHAMPOA           NaN  250901.375000  339375.547464  442574.228374   \n",
       "MARINE PARADE             NaN  245857.142857  399567.667257  521356.759717   \n",
       "QUEENSTOWN                NaN  244910.376471  354952.585253  544913.954918   \n",
       "TOA PAYOH                 NaN  229655.162162  299230.152174  401168.859694   \n",
       "WOODLANDS                 NaN            NaN  254830.024823  279292.986301   \n",
       "\n",
       "flat_type               5 ROOM  \n",
       "town                            \n",
       "ANG MO KIO                 NaN  \n",
       "BEDOK            639348.487805  \n",
       "BISHAN           494000.000000  \n",
       "BUKIT MERAH      671653.861635  \n",
       "BUKIT TIMAH      874903.225806  \n",
       "CENTRAL AREA               NaN  \n",
       "GEYLANG          676912.000000  \n",
       "HOUGANG          494500.000000  \n",
       "JURONG EAST                NaN  \n",
       "JURONG WEST      420398.283333  \n",
       "KALLANG/WHAMPOA  590965.225806  \n",
       "MARINE PARADE    801507.381188  \n",
       "QUEENSTOWN       771895.862069  \n",
       "TOA PAYOH        547929.495327  \n",
       "WOODLANDS        371992.000000  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.pivot_table(values='resale_price', index='town', columns='flat_type', aggfunc='mean')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Processing Data with Customized Logics"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Involving Values from One Column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Splitting year month\n",
    "def get_year(row): \n",
    "    yearmonth_string = row['Tranc_YearMonth']\n",
    "    year_string = yearmonth_string.split('-')[0]\n",
    "    year_string = int(year_string)\n",
    "    return year_string"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0        2015\n",
       "1        2015\n",
       "2        2015\n",
       "3        2015\n",
       "4        2015\n",
       "         ... \n",
       "14995    2013\n",
       "14996    2013\n",
       "14997    2013\n",
       "14998    2013\n",
       "14999    2013\n",
       "Length: 15000, dtype: int64"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# The apply() method will produce output as a Pandas Series object\n",
    "# Which is similar a column of values\n",
    "# So if we don't assign/store the output, it will just display on Jupyter Notebook\n",
    "# !!!! Always remember to use axis=1\n",
    "df.apply(get_year, axis=1) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Remember to assign the output to a new column (or overwriting the existing column if applicabble)\n",
    "df['Tranc_Year'] = df.apply(get_year, axis=1) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Involving Values from Multple Columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Function for row data\n",
    "def get_hdb_type_model(row):\n",
    "    type_model = row['flat_type'] + ' ' + row['flat_model']\n",
    "    return type_model"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0        3 ROOM New Generation\n",
       "1        4 ROOM New Generation\n",
       "2        3 ROOM New Generation\n",
       "3        3 ROOM New Generation\n",
       "4        3 ROOM New Generation\n",
       "                 ...          \n",
       "14995          5 ROOM Standard\n",
       "14996          5 ROOM Standard\n",
       "14997          3 ROOM Improved\n",
       "14998          4 ROOM Improved\n",
       "14999          5 ROOM Standard\n",
       "Length: 15000, dtype: object"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.apply(get_hdb_type_model, axis=1) #applying function to all rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['flat_type_model'] = df.apply(get_hdb_type_model, axis=1) #assign as new column"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exporting the Processed DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "ename": "FileNotFoundError",
     "evalue": "[Errno 2] No such file or directory: 'data/1_Processed_Data/hdb_resale_sample_processed.csv'",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mFileNotFoundError\u001b[0m                         Traceback (most recent call last)",
      "Input \u001b[0;32mIn [29]\u001b[0m, in \u001b[0;36m<cell line: 4>\u001b[0;34m()\u001b[0m\n\u001b[1;32m      1\u001b[0m output_filepath \u001b[38;5;241m=\u001b[39m \u001b[38;5;124m'\u001b[39m\u001b[38;5;124mdata/1_Processed_Data/hdb_resale_sample_processed.csv\u001b[39m\u001b[38;5;124m'\u001b[39m\n\u001b[1;32m      3\u001b[0m \u001b[38;5;66;03m# set \"index=False\" to exclude the index being exported as part of the CSV\u001b[39;00m\n\u001b[0;32m----> 4\u001b[0m \u001b[43mdf\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mto_csv\u001b[49m\u001b[43m(\u001b[49m\u001b[43moutput_filepath\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mindex\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[38;5;28;43;01mFalse\u001b[39;49;00m\u001b[43m)\u001b[49m\n",
      "File \u001b[0;32m/usr/lib/python3/dist-packages/pandas/core/generic.py:3170\u001b[0m, in \u001b[0;36mNDFrame.to_csv\u001b[0;34m(self, path_or_buf, sep, na_rep, float_format, columns, header, index, index_label, mode, encoding, compression, quoting, quotechar, line_terminator, chunksize, date_format, doublequote, escapechar, decimal, errors)\u001b[0m\n\u001b[1;32m   3145\u001b[0m \u001b[38;5;28;01mfrom\u001b[39;00m \u001b[38;5;21;01mpandas\u001b[39;00m\u001b[38;5;21;01m.\u001b[39;00m\u001b[38;5;21;01mio\u001b[39;00m\u001b[38;5;21;01m.\u001b[39;00m\u001b[38;5;21;01mformats\u001b[39;00m\u001b[38;5;21;01m.\u001b[39;00m\u001b[38;5;21;01mcsvs\u001b[39;00m \u001b[38;5;28;01mimport\u001b[39;00m CSVFormatter\n\u001b[1;32m   3147\u001b[0m formatter \u001b[38;5;241m=\u001b[39m CSVFormatter(\n\u001b[1;32m   3148\u001b[0m     df,\n\u001b[1;32m   3149\u001b[0m     path_or_buf,\n\u001b[0;32m   (...)\u001b[0m\n\u001b[1;32m   3168\u001b[0m     decimal\u001b[38;5;241m=\u001b[39mdecimal,\n\u001b[1;32m   3169\u001b[0m )\n\u001b[0;32m-> 3170\u001b[0m \u001b[43mformatter\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43msave\u001b[49m\u001b[43m(\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m   3172\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m path_or_buf \u001b[38;5;129;01mis\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m:\n\u001b[1;32m   3173\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m formatter\u001b[38;5;241m.\u001b[39mpath_or_buf\u001b[38;5;241m.\u001b[39mgetvalue()\n",
      "File \u001b[0;32m/usr/lib/python3/dist-packages/pandas/io/formats/csvs.py:185\u001b[0m, in \u001b[0;36mCSVFormatter.save\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m    183\u001b[0m     close \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;01mFalse\u001b[39;00m\n\u001b[1;32m    184\u001b[0m \u001b[38;5;28;01melse\u001b[39;00m:\n\u001b[0;32m--> 185\u001b[0m     f, handles \u001b[38;5;241m=\u001b[39m \u001b[43mget_handle\u001b[49m\u001b[43m(\u001b[49m\n\u001b[1;32m    186\u001b[0m \u001b[43m        \u001b[49m\u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mpath_or_buf\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    187\u001b[0m \u001b[43m        \u001b[49m\u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mmode\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    188\u001b[0m \u001b[43m        \u001b[49m\u001b[43mencoding\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mencoding\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    189\u001b[0m \u001b[43m        \u001b[49m\u001b[43merrors\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43merrors\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    190\u001b[0m \u001b[43m        \u001b[49m\u001b[43mcompression\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[38;5;28;43mdict\u001b[39;49m\u001b[43m(\u001b[49m\u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mcompression_args\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mmethod\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mcompression\u001b[49m\u001b[43m)\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    191\u001b[0m \u001b[43m    \u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m    192\u001b[0m     close \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;01mTrue\u001b[39;00m\n\u001b[1;32m    194\u001b[0m \u001b[38;5;28;01mtry\u001b[39;00m:\n\u001b[1;32m    195\u001b[0m     \u001b[38;5;66;03m# Note: self.encoding is irrelevant here\u001b[39;00m\n",
      "File \u001b[0;32m/usr/lib/python3/dist-packages/pandas/io/common.py:493\u001b[0m, in \u001b[0;36mget_handle\u001b[0;34m(path_or_buf, mode, encoding, compression, memory_map, is_text, errors)\u001b[0m\n\u001b[1;32m    490\u001b[0m \u001b[38;5;28;01melif\u001b[39;00m is_path:\n\u001b[1;32m    491\u001b[0m     \u001b[38;5;28;01mif\u001b[39;00m encoding:\n\u001b[1;32m    492\u001b[0m         \u001b[38;5;66;03m# Encoding\u001b[39;00m\n\u001b[0;32m--> 493\u001b[0m         f \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;43mopen\u001b[39;49m\u001b[43m(\u001b[49m\u001b[43mpath_or_buf\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mmode\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mencoding\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mencoding\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43merrors\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43merrors\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mnewline\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[43m)\u001b[49m\n\u001b[1;32m    494\u001b[0m     \u001b[38;5;28;01melif\u001b[39;00m is_text:\n\u001b[1;32m    495\u001b[0m         \u001b[38;5;66;03m# No explicit encoding\u001b[39;00m\n\u001b[1;32m    496\u001b[0m         f \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mopen\u001b[39m(path_or_buf, mode, errors\u001b[38;5;241m=\u001b[39m\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mreplace\u001b[39m\u001b[38;5;124m\"\u001b[39m, newline\u001b[38;5;241m=\u001b[39m\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124m\"\u001b[39m)\n",
      "\u001b[0;31mFileNotFoundError\u001b[0m: [Errno 2] No such file or directory: 'data/1_Processed_Data/hdb_resale_sample_processed.csv'"
     ]
    }
   ],
   "source": [
    "output_filepath = 'data/1_Processed_Data/hdb_resale_sample_processed.csv'\n",
    "\n",
    "# set \"index=False\" to exclude the index being exported as part of the CSV\n",
    "df.to_csv(output_filepath, index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# [OPTIONAL]: Extra Learning"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Quickly Passing Data between Pandas and Excel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "# After running this cell, head over a new Excel's Spreahsheet and paste (Ctrl + V)\n",
    "df_temp = df.head(100)\n",
    "df_temp.to_clipboard(excel=True, index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "# The reverse is also possible\n",
    "# Select the cells (i.e. ranges) on an Excel's Spreahsheet\n",
    "# Then run the this cell\n",
    "df_temp = pd.read_clipboard()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exporting DataFrame as a Static Webpage (HTML File)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_temp = df.head(100)\n",
    "df_temp.to_html('data/export_dataframe_as_webpage.html')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "<br>\n",
    "\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# !! Your Turn !!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Replace the code marked as <..> or fill the empty cell COMPLETELY with your own code\n",
    "Don't constrained by the <..>, it's just a guide. Feel free to insert more lines of code.\n",
    "Or using less lines of code is also fine."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br>\n",
    "\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Question 1 \n",
    "A) Read hdb_resale_full_raw.csv into pandas and named it <b>df</b> <br/>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.read_csv('data/0_raw_data/hdb_resale_full_raw.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br>\n",
    "B) Find out which column(s) has records with missing values\n",
    "\n",
    "💡 Hint: use info() or any other methods"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 185968 entries, 0 to 185967\n",
      "Data columns (total 10 columns):\n",
      " #   Column               Non-Null Count   Dtype  \n",
      "---  ------               --------------   -----  \n",
      " 0   Tranc_YearMonth      184086 non-null  object \n",
      " 1   town                 185968 non-null  object \n",
      " 2   flat_type            185945 non-null  object \n",
      " 3   block                185968 non-null  object \n",
      " 4   street_name          185968 non-null  object \n",
      " 5   storey_range         185968 non-null  object \n",
      " 6   floor_area_sqm       185968 non-null  float64\n",
      " 7   flat_model           185968 non-null  object \n",
      " 8   lease_commence_date  185968 non-null  int64  \n",
      " 9   resale_price         185968 non-null  float64\n",
      "dtypes: float64(2), int64(1), object(7)\n",
      "memory usage: 14.2+ MB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br>\n",
    "\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Question 2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "A) Let's examine the **Tranc_YearMonth column** by listing the all unique values in the dataset <br/>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['2012-03', '2012-04', '2012-05', '2012-06', '2012-07', '2012-08',\n",
       "       '2012-09', '2012-10', '2012-11', '2012-12', '2013-01', '2013-02',\n",
       "       '2013-03', '2013-04', '2013-05', '2013-06', '2013-07', '2013-08',\n",
       "       '2013-09', '2013-10', '2013-11', '2013-12', '2014-01', '2014-02',\n",
       "       '2014-03', '2014-04', '2014-05', '2014-06', '2014-07', '2014-08',\n",
       "       '2014-09', '2014-10', '2014-11', '2014-12', '2015-01', '2015-02',\n",
       "       '2015-03', '2015-04', '2015-05', '2015-06', '2015-07', '2015-08',\n",
       "       '2015-09', '2015-10', '2015-11', '2015-12', '2016-01', '2016-02',\n",
       "       '2016-03', '2016-04', '2016-05', '2016-06', '2016-07', nan,\n",
       "       '2016-09', '2016-10', '2016-11', '2016-12', '2017-01', '2017-02',\n",
       "       '2017-03', '2017-04', '2017-05', '2017-06', '2017-07', '2017-08',\n",
       "       '2017-09', '2017-10', '2017-11', '2017-12', '2018-01', '2018-02',\n",
       "       '2018-03', '2018-04', '2018-05', '2018-06', '2018-07', '2018-08',\n",
       "       '2018-09', '2018-10', '2018-11', '2018-12', '2019-01', '2019-02',\n",
       "       '2019-03', '2019-04', '2019-05', '2019-06', '2019-07', '2019-08',\n",
       "       '2019-09', '2019-10', '2019-11', '2019-12', '2020-01', '2020-02',\n",
       "       '2020-03', '2020-04', '2020-05', '2020-06', '2020-07', '2020-08',\n",
       "       '2020-09', '2020-10', '2020-11', '2020-12', '2021-01', '2021-02',\n",
       "       '2021-03', '2021-04'], dtype=object)"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Tranc_YearMonth'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br>\n",
    "\n",
    "B) You are being informed that the records are ordered from the **earliest** to the **latest** transactions.\\\n",
    "Use this information to fill the missing values in the **Tranc_YearMonth** column. \\\n",
    "⚠️ Note: Remember to assign the values back to the column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['2012-03', '2012-04', '2012-05', '2012-06', '2012-07', '2012-08',\n",
       "       '2012-09', '2012-10', '2012-11', '2012-12', '2013-01', '2013-02',\n",
       "       '2013-03', '2013-04', '2013-05', '2013-06', '2013-07', '2013-08',\n",
       "       '2013-09', '2013-10', '2013-11', '2013-12', '2014-01', '2014-02',\n",
       "       '2014-03', '2014-04', '2014-05', '2014-06', '2014-07', '2014-08',\n",
       "       '2014-09', '2014-10', '2014-11', '2014-12', '2015-01', '2015-02',\n",
       "       '2015-03', '2015-04', '2015-05', '2015-06', '2015-07', '2015-08',\n",
       "       '2015-09', '2015-10', '2015-11', '2015-12', '2016-01', '2016-02',\n",
       "       '2016-03', '2016-04', '2016-05', '2016-06', '2016-07', '2016-08',\n",
       "       '2016-09', '2016-10', '2016-11', '2016-12', '2017-01', '2017-02',\n",
       "       '2017-03', '2017-04', '2017-05', '2017-06', '2017-07', '2017-08',\n",
       "       '2017-09', '2017-10', '2017-11', '2017-12', '2018-01', '2018-02',\n",
       "       '2018-03', '2018-04', '2018-05', '2018-06', '2018-07', '2018-08',\n",
       "       '2018-09', '2018-10', '2018-11', '2018-12', '2019-01', '2019-02',\n",
       "       '2019-03', '2019-04', '2019-05', '2019-06', '2019-07', '2019-08',\n",
       "       '2019-09', '2019-10', '2019-11', '2019-12', '2020-01', '2020-02',\n",
       "       '2020-03', '2020-04', '2020-05', '2020-06', '2020-07', '2020-08',\n",
       "       '2020-09', '2020-10', '2020-11', '2020-12', '2021-01', '2021-02',\n",
       "       '2021-03', '2021-04'], dtype=object)"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Tranc_YearMonth'] = df['Tranc_YearMonth'].fillna('2016-08')\n",
    "df['Tranc_YearMonth'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br>\n",
    "\n",
    "B) You are also being informed there are typos for same values in **flat_type** column due to some errors.\\\n",
    "There are \"3 ROOM\" and \"3 ROOMS\". **Correct the typos**.\n",
    "\n",
    "💡 # Hint: Use .replace() or any other methods\n",
    "\n",
    "⚠️ Note: Ignore the missing values in this column for now."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['2 ROOM', '3 ROOM', '4 ROOM', '5 ROOM', 'EXECUTIVE',\n",
       "       'MULTI-GENERATION', '1 ROOM', '3 ROOMS', nan], dtype=object)"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['flat_type'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['2 ROOM', '3 ROOM', '4 ROOM', '5 ROOM', 'EXECUTIVE',\n",
       "       'MULTI-GENERATION', '1 ROOM', nan], dtype=object)"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['flat_type'] = df['flat_type'].replace ('3 ROOMS', '3 ROOM')\n",
    "df['flat_type'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br>\n",
    "\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "# Question 3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "A) Retrieve all the transactions for **4-room flats** from **Clementi** and\\\n",
    "the records must be in the order which the **latest transactions are at the top of the DataFrame**.\\\n",
    "Note: There is no need to store the final output. Just display in Jupyter Notebook."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>Tranc_YearMonth</th>\n",
       "      <th>town</th>\n",
       "      <th>flat_type</th>\n",
       "      <th>block</th>\n",
       "      <th>street_name</th>\n",
       "      <th>storey_range</th>\n",
       "      <th>floor_area_sqm</th>\n",
       "      <th>flat_model</th>\n",
       "      <th>lease_commence_date</th>\n",
       "      <th>resale_price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>167818</th>\n",
       "      <td>2020-09</td>\n",
       "      <td>CENTRAL AREA</td>\n",
       "      <td>5 ROOM</td>\n",
       "      <td>1B</td>\n",
       "      <td>CANTONMENT RD</td>\n",
       "      <td>43 TO 45</td>\n",
       "      <td>107.0</td>\n",
       "      <td>Type S2</td>\n",
       "      <td>2011</td>\n",
       "      <td>1258000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>184265</th>\n",
       "      <td>2021-04</td>\n",
       "      <td>BISHAN</td>\n",
       "      <td>5 ROOM</td>\n",
       "      <td>273B</td>\n",
       "      <td>BISHAN ST 24</td>\n",
       "      <td>31 TO 33</td>\n",
       "      <td>120.0</td>\n",
       "      <td>DBSS</td>\n",
       "      <td>2011</td>\n",
       "      <td>1250000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>172688</th>\n",
       "      <td>2020-11</td>\n",
       "      <td>CENTRAL AREA</td>\n",
       "      <td>5 ROOM</td>\n",
       "      <td>1A</td>\n",
       "      <td>CANTONMENT RD</td>\n",
       "      <td>49 TO 51</td>\n",
       "      <td>105.0</td>\n",
       "      <td>Type S2</td>\n",
       "      <td>2011</td>\n",
       "      <td>1248000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157657</th>\n",
       "      <td>2020-03</td>\n",
       "      <td>CENTRAL AREA</td>\n",
       "      <td>5 ROOM</td>\n",
       "      <td>1B</td>\n",
       "      <td>CANTONMENT RD</td>\n",
       "      <td>40 TO 42</td>\n",
       "      <td>107.0</td>\n",
       "      <td>Type S2</td>\n",
       "      <td>2011</td>\n",
       "      <td>1232000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>181866</th>\n",
       "      <td>2021-03</td>\n",
       "      <td>BISHAN</td>\n",
       "      <td>5 ROOM</td>\n",
       "      <td>273B</td>\n",
       "      <td>BISHAN ST 24</td>\n",
       "      <td>28 TO 30</td>\n",
       "      <td>120.0</td>\n",
       "      <td>DBSS</td>\n",
       "      <td>2011</td>\n",
       "      <td>1220000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157457</th>\n",
       "      <td>2020-03</td>\n",
       "      <td>BUKIT MERAH</td>\n",
       "      <td>1 ROOM</td>\n",
       "      <td>7</td>\n",
       "      <td>TELOK BLANGAH CRES</td>\n",
       "      <td>01 TO 03</td>\n",
       "      <td>31.0</td>\n",
       "      <td>Improved</td>\n",
       "      <td>1975</td>\n",
       "      <td>160000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>155769</th>\n",
       "      <td>2020-02</td>\n",
       "      <td>BUKIT MERAH</td>\n",
       "      <td>1 ROOM</td>\n",
       "      <td>7</td>\n",
       "      <td>TELOK BLANGAH CRES</td>\n",
       "      <td>10 TO 12</td>\n",
       "      <td>31.0</td>\n",
       "      <td>Improved</td>\n",
       "      <td>1975</td>\n",
       "      <td>157000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>155156</th>\n",
       "      <td>2020-01</td>\n",
       "      <td>TOA PAYOH</td>\n",
       "      <td>2 ROOM</td>\n",
       "      <td>52</td>\n",
       "      <td>LOR 6 TOA PAYOH</td>\n",
       "      <td>01 TO 03</td>\n",
       "      <td>43.0</td>\n",
       "      <td>Standard</td>\n",
       "      <td>1983</td>\n",
       "      <td>150000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>148309</th>\n",
       "      <td>2019-10</td>\n",
       "      <td>GEYLANG</td>\n",
       "      <td>2 ROOM</td>\n",
       "      <td>68</td>\n",
       "      <td>CIRCUIT RD</td>\n",
       "      <td>04 TO 06</td>\n",
       "      <td>45.0</td>\n",
       "      <td>Standard</td>\n",
       "      <td>1969</td>\n",
       "      <td>150000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156912</th>\n",
       "      <td>2020-02</td>\n",
       "      <td>TOA PAYOH</td>\n",
       "      <td>3 ROOM</td>\n",
       "      <td>26</td>\n",
       "      <td>TOA PAYOH EAST</td>\n",
       "      <td>10 TO 12</td>\n",
       "      <td>67.0</td>\n",
       "      <td>Improved</td>\n",
       "      <td>1971</td>\n",
       "      <td>140000.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>185968 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Tranc_YearMonth          town flat_type block         street_name  \\\n",
       "167818         2020-09  CENTRAL AREA    5 ROOM    1B       CANTONMENT RD   \n",
       "184265         2021-04        BISHAN    5 ROOM  273B        BISHAN ST 24   \n",
       "172688         2020-11  CENTRAL AREA    5 ROOM    1A       CANTONMENT RD   \n",
       "157657         2020-03  CENTRAL AREA    5 ROOM    1B       CANTONMENT RD   \n",
       "181866         2021-03        BISHAN    5 ROOM  273B        BISHAN ST 24   \n",
       "...                ...           ...       ...   ...                 ...   \n",
       "157457         2020-03   BUKIT MERAH    1 ROOM     7  TELOK BLANGAH CRES   \n",
       "155769         2020-02   BUKIT MERAH    1 ROOM     7  TELOK BLANGAH CRES   \n",
       "155156         2020-01     TOA PAYOH    2 ROOM    52     LOR 6 TOA PAYOH   \n",
       "148309         2019-10       GEYLANG    2 ROOM    68          CIRCUIT RD   \n",
       "156912         2020-02     TOA PAYOH    3 ROOM    26      TOA PAYOH EAST   \n",
       "\n",
       "       storey_range  floor_area_sqm flat_model  lease_commence_date  \\\n",
       "167818     43 TO 45           107.0    Type S2                 2011   \n",
       "184265     31 TO 33           120.0       DBSS                 2011   \n",
       "172688     49 TO 51           105.0    Type S2                 2011   \n",
       "157657     40 TO 42           107.0    Type S2                 2011   \n",
       "181866     28 TO 30           120.0       DBSS                 2011   \n",
       "...             ...             ...        ...                  ...   \n",
       "157457     01 TO 03            31.0   Improved                 1975   \n",
       "155769     10 TO 12            31.0   Improved                 1975   \n",
       "155156     01 TO 03            43.0   Standard                 1983   \n",
       "148309     04 TO 06            45.0   Standard                 1969   \n",
       "156912     10 TO 12            67.0   Improved                 1971   \n",
       "\n",
       "        resale_price  \n",
       "167818     1258000.0  \n",
       "184265     1250000.0  \n",
       "172688     1248000.0  \n",
       "157657     1232000.0  \n",
       "181866     1220000.0  \n",
       "...              ...  \n",
       "157457      160000.0  \n",
       "155769      157000.0  \n",
       "155156      150000.0  \n",
       "148309      150000.0  \n",
       "156912      140000.0  \n",
       "\n",
       "[185968 rows x 10 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_temp = df[(df['flat_type'] == '4 ROOM') & (df['town'] == 'CLEMENTI')]\n",
    "df.sort_values ('resale_price', ascending = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br>\n",
    "\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "B) Sort the records in the **df**, which the records are ordered by:\n",
    "1. Lease Commence Year (Newest to Oldest)\n",
    "2. Transaction Year (Newest to Oldest)\n",
    "3. Town (In alphabetical order)\n",
    "4. Flat Type (In alphabetical order)\n",
    "5. Resale Price (Lowest to Highest Price)\n",
    "\n",
    "Note: There is no need to store the final output. Just display in Jupyter Notebook.\n",
    "\n",
    "Your DataFrame should look something like this.\\\n",
    "![](data/Q3.jpg)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>Tranc_YearMonth</th>\n",
       "      <th>town</th>\n",
       "      <th>flat_type</th>\n",
       "      <th>block</th>\n",
       "      <th>street_name</th>\n",
       "      <th>storey_range</th>\n",
       "      <th>floor_area_sqm</th>\n",
       "      <th>flat_model</th>\n",
       "      <th>lease_commence_date</th>\n",
       "      <th>resale_price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>182023</th>\n",
       "      <td>2021-03</td>\n",
       "      <td>BUKIT MERAH</td>\n",
       "      <td>3 ROOM</td>\n",
       "      <td>95A</td>\n",
       "      <td>HENDERSON RD</td>\n",
       "      <td>40 TO 42</td>\n",
       "      <td>68.0</td>\n",
       "      <td>Model A</td>\n",
       "      <td>2019</td>\n",
       "      <td>680000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>182024</th>\n",
       "      <td>2021-03</td>\n",
       "      <td>BUKIT MERAH</td>\n",
       "      <td>3 ROOM</td>\n",
       "      <td>96B</td>\n",
       "      <td>HENDERSON RD</td>\n",
       "      <td>04 TO 06</td>\n",
       "      <td>68.0</td>\n",
       "      <td>Model A</td>\n",
       "      <td>2019</td>\n",
       "      <td>620000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>182027</th>\n",
       "      <td>2021-03</td>\n",
       "      <td>BUKIT MERAH</td>\n",
       "      <td>4 ROOM</td>\n",
       "      <td>96A</td>\n",
       "      <td>HENDERSON RD</td>\n",
       "      <td>25 TO 27</td>\n",
       "      <td>93.0</td>\n",
       "      <td>Model A</td>\n",
       "      <td>2019</td>\n",
       "      <td>868000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>182028</th>\n",
       "      <td>2021-03</td>\n",
       "      <td>BUKIT MERAH</td>\n",
       "      <td>4 ROOM</td>\n",
       "      <td>96A</td>\n",
       "      <td>HENDERSON RD</td>\n",
       "      <td>07 TO 09</td>\n",
       "      <td>93.0</td>\n",
       "      <td>Model A</td>\n",
       "      <td>2019</td>\n",
       "      <td>820000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179832</th>\n",
       "      <td>2021-02</td>\n",
       "      <td>BUKIT MERAH</td>\n",
       "      <td>3 ROOM</td>\n",
       "      <td>95B</td>\n",
       "      <td>HENDERSON RD</td>\n",
       "      <td>22 TO 24</td>\n",
       "      <td>68.0</td>\n",
       "      <td>Model A</td>\n",
       "      <td>2019</td>\n",
       "      <td>660000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63013</th>\n",
       "      <td>2015-08</td>\n",
       "      <td>JURONG EAST</td>\n",
       "      <td>3 ROOM</td>\n",
       "      <td>37</td>\n",
       "      <td>TEBAN GDNS RD</td>\n",
       "      <td>04 TO 06</td>\n",
       "      <td>67.0</td>\n",
       "      <td>Improved</td>\n",
       "      <td>1966</td>\n",
       "      <td>289000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55171</th>\n",
       "      <td>2015-03</td>\n",
       "      <td>JURONG EAST</td>\n",
       "      <td>3 ROOM</td>\n",
       "      <td>39</td>\n",
       "      <td>TEBAN GDNS RD</td>\n",
       "      <td>01 TO 03</td>\n",
       "      <td>67.0</td>\n",
       "      <td>Improved</td>\n",
       "      <td>1966</td>\n",
       "      <td>260000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29021</th>\n",
       "      <td>2013-07</td>\n",
       "      <td>JURONG EAST</td>\n",
       "      <td>3 ROOM</td>\n",
       "      <td>38</td>\n",
       "      <td>TEBAN GDNS RD</td>\n",
       "      <td>01 TO 03</td>\n",
       "      <td>67.0</td>\n",
       "      <td>Improved</td>\n",
       "      <td>1966</td>\n",
       "      <td>310000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24484</th>\n",
       "      <td>2013-04</td>\n",
       "      <td>JURONG EAST</td>\n",
       "      <td>3 ROOM</td>\n",
       "      <td>37</td>\n",
       "      <td>TEBAN GDNS RD</td>\n",
       "      <td>04 TO 06</td>\n",
       "      <td>67.0</td>\n",
       "      <td>Improved</td>\n",
       "      <td>1966</td>\n",
       "      <td>311000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20611</th>\n",
       "      <td>2013-01</td>\n",
       "      <td>JURONG EAST</td>\n",
       "      <td>3 ROOM</td>\n",
       "      <td>39</td>\n",
       "      <td>TEBAN GDNS RD</td>\n",
       "      <td>01 TO 03</td>\n",
       "      <td>67.0</td>\n",
       "      <td>Improved</td>\n",
       "      <td>1966</td>\n",
       "      <td>315000.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>185968 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Tranc_YearMonth         town flat_type block    street_name  \\\n",
       "182023         2021-03  BUKIT MERAH    3 ROOM   95A   HENDERSON RD   \n",
       "182024         2021-03  BUKIT MERAH    3 ROOM   96B   HENDERSON RD   \n",
       "182027         2021-03  BUKIT MERAH    4 ROOM   96A   HENDERSON RD   \n",
       "182028         2021-03  BUKIT MERAH    4 ROOM   96A   HENDERSON RD   \n",
       "179832         2021-02  BUKIT MERAH    3 ROOM   95B   HENDERSON RD   \n",
       "...                ...          ...       ...   ...            ...   \n",
       "63013          2015-08  JURONG EAST    3 ROOM    37  TEBAN GDNS RD   \n",
       "55171          2015-03  JURONG EAST    3 ROOM    39  TEBAN GDNS RD   \n",
       "29021          2013-07  JURONG EAST    3 ROOM    38  TEBAN GDNS RD   \n",
       "24484          2013-04  JURONG EAST    3 ROOM    37  TEBAN GDNS RD   \n",
       "20611          2013-01  JURONG EAST    3 ROOM    39  TEBAN GDNS RD   \n",
       "\n",
       "       storey_range  floor_area_sqm flat_model  lease_commence_date  \\\n",
       "182023     40 TO 42            68.0    Model A                 2019   \n",
       "182024     04 TO 06            68.0    Model A                 2019   \n",
       "182027     25 TO 27            93.0    Model A                 2019   \n",
       "182028     07 TO 09            93.0    Model A                 2019   \n",
       "179832     22 TO 24            68.0    Model A                 2019   \n",
       "...             ...             ...        ...                  ...   \n",
       "63013      04 TO 06            67.0   Improved                 1966   \n",
       "55171      01 TO 03            67.0   Improved                 1966   \n",
       "29021      01 TO 03            67.0   Improved                 1966   \n",
       "24484      04 TO 06            67.0   Improved                 1966   \n",
       "20611      01 TO 03            67.0   Improved                 1966   \n",
       "\n",
       "        resale_price  \n",
       "182023      680000.0  \n",
       "182024      620000.0  \n",
       "182027      868000.0  \n",
       "182028      820000.0  \n",
       "179832      660000.0  \n",
       "...              ...  \n",
       "63013       289000.0  \n",
       "55171       260000.0  \n",
       "29021       310000.0  \n",
       "24484       311000.0  \n",
       "20611       315000.0  \n",
       "\n",
       "[185968 rows x 10 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.sort_values(['lease_commence_date','Tranc_YearMonth','town','flat_type','resale_price'], ascending = [False,False,True,True,False])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br>\n",
    "\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "# Question 4\n",
    "A) Find out the highest resale price value for 4-ROOM flat across all the towns.\\\n",
    "To simplify this, assume the calculation is\n",
    "- based on the resale price only\n",
    "- regardless of the age of the flats\n",
    "\n",
    "Note: Just display the resale price in Jupyter Notebook"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1186888.0"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['flat_type'] == '4 ROOM'].resale_price.max()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br>\n",
    "\n",
    "B) Display the record for the transactio from A) above\n",
    "\n",
    "The output should look something like this:\\\n",
    "![](data/Q4.jpg)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>Tranc_YearMonth</th>\n",
       "      <th>town</th>\n",
       "      <th>flat_type</th>\n",
       "      <th>block</th>\n",
       "      <th>street_name</th>\n",
       "      <th>storey_range</th>\n",
       "      <th>floor_area_sqm</th>\n",
       "      <th>flat_model</th>\n",
       "      <th>lease_commence_date</th>\n",
       "      <th>resale_price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>160493</th>\n",
       "      <td>2020-06</td>\n",
       "      <td>CENTRAL AREA</td>\n",
       "      <td>4 ROOM</td>\n",
       "      <td>1B</td>\n",
       "      <td>CANTONMENT RD</td>\n",
       "      <td>40 TO 42</td>\n",
       "      <td>93.0</td>\n",
       "      <td>Type S1</td>\n",
       "      <td>2011</td>\n",
       "      <td>1186888.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Tranc_YearMonth          town flat_type block    street_name  \\\n",
       "160493         2020-06  CENTRAL AREA    4 ROOM    1B  CANTONMENT RD   \n",
       "\n",
       "       storey_range  floor_area_sqm flat_model  lease_commence_date  \\\n",
       "160493     40 TO 42            93.0    Type S1                 2011   \n",
       "\n",
       "        resale_price  \n",
       "160493     1186888.0  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "highest_resale_price = df[df['flat_type'] == '4 ROOM'].resale_price.max()\n",
    "\n",
    "df[df['resale_price'] == highest_resale_price]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br>\n",
    "   \n",
    "C) Find out the what is the highest price for the different **Flat Types**\n",
    "\n",
    "💡Hint: use .groupby() method\\\n",
    "Note: Just display the resale price in Jupyter Notebook"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "flat_type\n",
       "1 ROOM               260000.0\n",
       "2 ROOM               510000.0\n",
       "3 ROOM              1185000.0\n",
       "3 ROOMS              275000.0\n",
       "4 ROOM              1186888.0\n",
       "5 ROOM              1258000.0\n",
       "EXECUTIVE           1210000.0\n",
       "MULTI-GENERATION     990000.0\n",
       "Name: resale_price, dtype: float64"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "groupby_flattype = df.groupby('flat_type')\n",
    "groupby_flattype['resale_price'].max()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br>\n",
    "D) For each of the towns, find out what is the highest resale price for the different Flat Types.\n",
    "\n",
    "Store the result in new DataFrame, called **df_highest_price**.\n",
    "\n",
    "💡Hint: You might need .reset_index() to convert the result into a typical DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "groupby_town_flattype = df.groupby(['town','flat_type'])\n",
    "df_highest_price = groupby_town_flattype['resale_price'].max().reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# No need to modify this cell. Just execute.\n",
    "# if above codes are correct, this should return \"True\"\n",
    "# The idea is that if the groupby result is in DataFrame, then we can use the data easily\n",
    "type(df_highest_price) == pd.core.frame.DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>town</th>\n",
       "      <th>flat_type</th>\n",
       "      <th>resale_price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ANG MO KIO</td>\n",
       "      <td>2 ROOM</td>\n",
       "      <td>315000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ANG MO KIO</td>\n",
       "      <td>3 ROOM</td>\n",
       "      <td>570000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ANG MO KIO</td>\n",
       "      <td>4 ROOM</td>\n",
       "      <td>782000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ANG MO KIO</td>\n",
       "      <td>5 ROOM</td>\n",
       "      <td>1008888.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ANG MO KIO</td>\n",
       "      <td>EXECUTIVE</td>\n",
       "      <td>1030000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124</th>\n",
       "      <td>YISHUN</td>\n",
       "      <td>3 ROOM</td>\n",
       "      <td>460000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>125</th>\n",
       "      <td>YISHUN</td>\n",
       "      <td>4 ROOM</td>\n",
       "      <td>625000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126</th>\n",
       "      <td>YISHUN</td>\n",
       "      <td>5 ROOM</td>\n",
       "      <td>770000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>127</th>\n",
       "      <td>YISHUN</td>\n",
       "      <td>EXECUTIVE</td>\n",
       "      <td>818000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>128</th>\n",
       "      <td>YISHUN</td>\n",
       "      <td>MULTI-GENERATION</td>\n",
       "      <td>868000.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>129 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           town         flat_type  resale_price\n",
       "0    ANG MO KIO            2 ROOM      315000.0\n",
       "1    ANG MO KIO            3 ROOM      570000.0\n",
       "2    ANG MO KIO            4 ROOM      782000.0\n",
       "3    ANG MO KIO            5 ROOM     1008888.0\n",
       "4    ANG MO KIO         EXECUTIVE     1030000.0\n",
       "..          ...               ...           ...\n",
       "124      YISHUN            3 ROOM      460000.0\n",
       "125      YISHUN            4 ROOM      625000.0\n",
       "126      YISHUN            5 ROOM      770000.0\n",
       "127      YISHUN         EXECUTIVE      818000.0\n",
       "128      YISHUN  MULTI-GENERATION      868000.0\n",
       "\n",
       "[129 rows x 3 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# No need to modify this cell. Just execute.\n",
    "# Display the result\n",
    "df_highest_price"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Question 5"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A) Find the **number of transactions** which **resale price** is **less than $500,000** for each town. \\\n",
    "**<u>Optional</u>**: Display the result, with the **town with most number of transaction first row** and the **town with least number of transaction as the last row**.\n",
    "<br/>\n",
    "\n",
    "💡 Hint: There is an aggregation method for the groupby object, called **.count()&** and **.size()**.\\\n",
    "You may look up for more information on how use these two methods."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "town\n",
       "ANG MO KIO          6733\n",
       "BEDOK               8295\n",
       "BISHAN               887\n",
       "BUKIT BATOK         5478\n",
       "BUKIT MERAH         2965\n",
       "BUKIT PANJANG       5244\n",
       "BUKIT TIMAH           87\n",
       "CENTRAL AREA         746\n",
       "CHOA CHU KANG       6478\n",
       "CLEMENTI            2888\n",
       "GEYLANG             3397\n",
       "HOUGANG             6791\n",
       "JURONG EAST         3025\n",
       "JURONG WEST        11278\n",
       "KALLANG/WHAMPOA     3057\n",
       "MARINE PARADE        663\n",
       "PASIR RIS           3306\n",
       "PUNGGOL             6378\n",
       "QUEENSTOWN          2446\n",
       "SEMBAWANG           3981\n",
       "SENGKANG            9502\n",
       "SERANGOON           2267\n",
       "TAMPINES            8046\n",
       "TOA PAYOH           3610\n",
       "WOODLANDS          12062\n",
       "YISHUN             10838\n",
       "Name: Tranc_YearMonth, dtype: int64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_temp = df[df['resale_price'] < 500000]\n",
    "\n",
    "df_temp = df_temp.groupby('town')\n",
    "\n",
    "df_temp['Tranc_YearMonth'].count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "B) Based on the transactions where the  **resale price** is **less than $500,000**,\\\n",
    "generate a pivot table to show the **median** **resale price** for the different **flat types** from each of the towns.\n",
    "\n",
    "The output looks something like this:\\\n",
    "![](data/Q5.jpg)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>flat_type</th>\n",
       "      <th>1 ROOM</th>\n",
       "      <th>2 ROOM</th>\n",
       "      <th>3 ROOM</th>\n",
       "      <th>3 ROOMS</th>\n",
       "      <th>4 ROOM</th>\n",
       "      <th>5 ROOM</th>\n",
       "      <th>EXECUTIVE</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>town</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>ANG MO KIO</th>\n",
       "      <td>NaN</td>\n",
       "      <td>242418.615385</td>\n",
       "      <td>324035.366458</td>\n",
       "      <td>NaN</td>\n",
       "      <td>428713.391010</td>\n",
       "      <td>472558.823529</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BEDOK</th>\n",
       "      <td>NaN</td>\n",
       "      <td>240803.833333</td>\n",
       "      <td>314298.723534</td>\n",
       "      <td>NaN</td>\n",
       "      <td>407809.241332</td>\n",
       "      <td>461113.179625</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BISHAN</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>362752.986425</td>\n",
       "      <td>NaN</td>\n",
       "      <td>457224.262115</td>\n",
       "      <td>473000.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BUKIT BATOK</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>290284.282184</td>\n",
       "      <td>NaN</td>\n",
       "      <td>393437.381359</td>\n",
       "      <td>454637.677551</td>\n",
       "      <td>491250.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BUKIT MERAH</th>\n",
       "      <td>206901.69697</td>\n",
       "      <td>261221.126437</td>\n",
       "      <td>351652.827909</td>\n",
       "      <td>NaN</td>\n",
       "      <td>448913.207792</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BUKIT PANJANG</th>\n",
       "      <td>NaN</td>\n",
       "      <td>236832.344828</td>\n",
       "      <td>306437.174279</td>\n",
       "      <td>NaN</td>\n",
       "      <td>389986.957742</td>\n",
       "      <td>440678.885667</td>\n",
       "      <td>461347.139785</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BUKIT TIMAH</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>415851.623529</td>\n",
       "      <td>NaN</td>\n",
       "      <td>480250.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CENTRAL AREA</th>\n",
       "      <td>NaN</td>\n",
       "      <td>270695.589041</td>\n",
       "      <td>415163.764605</td>\n",
       "      <td>NaN</td>\n",
       "      <td>461237.389474</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CHOA CHU KANG</th>\n",
       "      <td>NaN</td>\n",
       "      <td>234086.046512</td>\n",
       "      <td>298745.595937</td>\n",
       "      <td>262269.714286</td>\n",
       "      <td>373468.605603</td>\n",
       "      <td>423058.341969</td>\n",
       "      <td>466385.406250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CLEMENTI</th>\n",
       "      <td>NaN</td>\n",
       "      <td>294548.387097</td>\n",
       "      <td>340790.225198</td>\n",
       "      <td>NaN</td>\n",
       "      <td>435859.358333</td>\n",
       "      <td>473500.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GEYLANG</th>\n",
       "      <td>NaN</td>\n",
       "      <td>227372.184615</td>\n",
       "      <td>302975.918999</td>\n",
       "      <td>NaN</td>\n",
       "      <td>412160.645660</td>\n",
       "      <td>468364.695652</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>HOUGANG</th>\n",
       "      <td>NaN</td>\n",
       "      <td>238804.651163</td>\n",
       "      <td>309941.932551</td>\n",
       "      <td>NaN</td>\n",
       "      <td>402424.959123</td>\n",
       "      <td>455817.174123</td>\n",
       "      <td>465000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>JURONG EAST</th>\n",
       "      <td>NaN</td>\n",
       "      <td>240772.704545</td>\n",
       "      <td>307915.796890</td>\n",
       "      <td>NaN</td>\n",
       "      <td>408457.634035</td>\n",
       "      <td>436635.611285</td>\n",
       "      <td>495000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>JURONG WEST</th>\n",
       "      <td>NaN</td>\n",
       "      <td>234011.647059</td>\n",
       "      <td>287821.626537</td>\n",
       "      <td>NaN</td>\n",
       "      <td>386615.164680</td>\n",
       "      <td>438897.689492</td>\n",
       "      <td>462855.298246</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>KALLANG/WHAMPOA</th>\n",
       "      <td>NaN</td>\n",
       "      <td>249101.790476</td>\n",
       "      <td>330848.312392</td>\n",
       "      <td>NaN</td>\n",
       "      <td>440942.711692</td>\n",
       "      <td>485800.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MARINE PARADE</th>\n",
       "      <td>NaN</td>\n",
       "      <td>245857.142857</td>\n",
       "      <td>397155.651568</td>\n",
       "      <td>NaN</td>\n",
       "      <td>466312.988764</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PASIR RIS</th>\n",
       "      <td>NaN</td>\n",
       "      <td>262809.523810</td>\n",
       "      <td>336213.684211</td>\n",
       "      <td>NaN</td>\n",
       "      <td>420264.691203</td>\n",
       "      <td>466786.838028</td>\n",
       "      <td>483375.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PUNGGOL</th>\n",
       "      <td>NaN</td>\n",
       "      <td>255134.775000</td>\n",
       "      <td>360472.730886</td>\n",
       "      <td>NaN</td>\n",
       "      <td>435383.252791</td>\n",
       "      <td>437045.639330</td>\n",
       "      <td>484000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>QUEENSTOWN</th>\n",
       "      <td>NaN</td>\n",
       "      <td>257538.769231</td>\n",
       "      <td>353626.920197</td>\n",
       "      <td>NaN</td>\n",
       "      <td>443812.386364</td>\n",
       "      <td>430000.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SEMBAWANG</th>\n",
       "      <td>NaN</td>\n",
       "      <td>226361.411765</td>\n",
       "      <td>297231.260870</td>\n",
       "      <td>NaN</td>\n",
       "      <td>371182.261486</td>\n",
       "      <td>421491.351900</td>\n",
       "      <td>466995.149718</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SENGKANG</th>\n",
       "      <td>NaN</td>\n",
       "      <td>253299.761194</td>\n",
       "      <td>348758.774963</td>\n",
       "      <td>NaN</td>\n",
       "      <td>414183.107067</td>\n",
       "      <td>441973.042112</td>\n",
       "      <td>473579.616667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SERANGOON</th>\n",
       "      <td>NaN</td>\n",
       "      <td>240187.800000</td>\n",
       "      <td>332497.259574</td>\n",
       "      <td>NaN</td>\n",
       "      <td>419162.425150</td>\n",
       "      <td>471066.904762</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TAMPINES</th>\n",
       "      <td>NaN</td>\n",
       "      <td>266612.765957</td>\n",
       "      <td>343234.041012</td>\n",
       "      <td>NaN</td>\n",
       "      <td>431510.504108</td>\n",
       "      <td>474591.493846</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TOA PAYOH</th>\n",
       "      <td>NaN</td>\n",
       "      <td>232039.937173</td>\n",
       "      <td>309817.003597</td>\n",
       "      <td>NaN</td>\n",
       "      <td>406065.801223</td>\n",
       "      <td>478363.636364</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WOODLANDS</th>\n",
       "      <td>NaN</td>\n",
       "      <td>226640.631579</td>\n",
       "      <td>283987.147181</td>\n",
       "      <td>NaN</td>\n",
       "      <td>364656.826436</td>\n",
       "      <td>427241.383634</td>\n",
       "      <td>475634.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>YISHUN</th>\n",
       "      <td>NaN</td>\n",
       "      <td>224576.604651</td>\n",
       "      <td>297048.345005</td>\n",
       "      <td>NaN</td>\n",
       "      <td>368687.728207</td>\n",
       "      <td>448391.394316</td>\n",
       "      <td>480773.629630</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "flat_type              1 ROOM         2 ROOM         3 ROOM        3 ROOMS  \\\n",
       "town                                                                         \n",
       "ANG MO KIO                NaN  242418.615385  324035.366458            NaN   \n",
       "BEDOK                     NaN  240803.833333  314298.723534            NaN   \n",
       "BISHAN                    NaN            NaN  362752.986425            NaN   \n",
       "BUKIT BATOK               NaN            NaN  290284.282184            NaN   \n",
       "BUKIT MERAH      206901.69697  261221.126437  351652.827909            NaN   \n",
       "BUKIT PANJANG             NaN  236832.344828  306437.174279            NaN   \n",
       "BUKIT TIMAH               NaN            NaN  415851.623529            NaN   \n",
       "CENTRAL AREA              NaN  270695.589041  415163.764605            NaN   \n",
       "CHOA CHU KANG             NaN  234086.046512  298745.595937  262269.714286   \n",
       "CLEMENTI                  NaN  294548.387097  340790.225198            NaN   \n",
       "GEYLANG                   NaN  227372.184615  302975.918999            NaN   \n",
       "HOUGANG                   NaN  238804.651163  309941.932551            NaN   \n",
       "JURONG EAST               NaN  240772.704545  307915.796890            NaN   \n",
       "JURONG WEST               NaN  234011.647059  287821.626537            NaN   \n",
       "KALLANG/WHAMPOA           NaN  249101.790476  330848.312392            NaN   \n",
       "MARINE PARADE             NaN  245857.142857  397155.651568            NaN   \n",
       "PASIR RIS                 NaN  262809.523810  336213.684211            NaN   \n",
       "PUNGGOL                   NaN  255134.775000  360472.730886            NaN   \n",
       "QUEENSTOWN                NaN  257538.769231  353626.920197            NaN   \n",
       "SEMBAWANG                 NaN  226361.411765  297231.260870            NaN   \n",
       "SENGKANG                  NaN  253299.761194  348758.774963            NaN   \n",
       "SERANGOON                 NaN  240187.800000  332497.259574            NaN   \n",
       "TAMPINES                  NaN  266612.765957  343234.041012            NaN   \n",
       "TOA PAYOH                 NaN  232039.937173  309817.003597            NaN   \n",
       "WOODLANDS                 NaN  226640.631579  283987.147181            NaN   \n",
       "YISHUN                    NaN  224576.604651  297048.345005            NaN   \n",
       "\n",
       "flat_type               4 ROOM         5 ROOM      EXECUTIVE  \n",
       "town                                                          \n",
       "ANG MO KIO       428713.391010  472558.823529            NaN  \n",
       "BEDOK            407809.241332  461113.179625            NaN  \n",
       "BISHAN           457224.262115  473000.000000            NaN  \n",
       "BUKIT BATOK      393437.381359  454637.677551  491250.000000  \n",
       "BUKIT MERAH      448913.207792            NaN            NaN  \n",
       "BUKIT PANJANG    389986.957742  440678.885667  461347.139785  \n",
       "BUKIT TIMAH      480250.000000            NaN            NaN  \n",
       "CENTRAL AREA     461237.389474            NaN            NaN  \n",
       "CHOA CHU KANG    373468.605603  423058.341969  466385.406250  \n",
       "CLEMENTI         435859.358333  473500.000000            NaN  \n",
       "GEYLANG          412160.645660  468364.695652            NaN  \n",
       "HOUGANG          402424.959123  455817.174123  465000.000000  \n",
       "JURONG EAST      408457.634035  436635.611285  495000.000000  \n",
       "JURONG WEST      386615.164680  438897.689492  462855.298246  \n",
       "KALLANG/WHAMPOA  440942.711692  485800.000000            NaN  \n",
       "MARINE PARADE    466312.988764            NaN            NaN  \n",
       "PASIR RIS        420264.691203  466786.838028  483375.000000  \n",
       "PUNGGOL          435383.252791  437045.639330  484000.000000  \n",
       "QUEENSTOWN       443812.386364  430000.000000            NaN  \n",
       "SEMBAWANG        371182.261486  421491.351900  466995.149718  \n",
       "SENGKANG         414183.107067  441973.042112  473579.616667  \n",
       "SERANGOON        419162.425150  471066.904762            NaN  \n",
       "TAMPINES         431510.504108  474591.493846            NaN  \n",
       "TOA PAYOH        406065.801223  478363.636364            NaN  \n",
       "WOODLANDS        364656.826436  427241.383634  475634.666667  \n",
       "YISHUN           368687.728207  448391.394316  480773.629630  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_temp = df[df['resale_price'] < 500000]\n",
    "\n",
    "df_temp.pivot_table(values='resale_price',index='town',columns='flat_type',aggfunc='mean')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Question 6"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br>\n",
    "\n",
    "A) Create a new column **storey_midpoint** to store the middle value of the **storey_range**.\\\n",
    "For example, if the **storey_range** is '05 to 07\", it should will be 6 (as an Integer).\n",
    "\n",
    "\n",
    "💡Hint: use apply() method and you will need to define a function to be used with the apply() method.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>Tranc_YearMonth</th>\n",
       "      <th>town</th>\n",
       "      <th>flat_type</th>\n",
       "      <th>block</th>\n",
       "      <th>street_name</th>\n",
       "      <th>storey_range</th>\n",
       "      <th>floor_area_sqm</th>\n",
       "      <th>flat_model</th>\n",
       "      <th>lease_commence_date</th>\n",
       "      <th>resale_price</th>\n",
       "      <th>storey_midpoint</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2012-03</td>\n",
       "      <td>ANG MO KIO</td>\n",
       "      <td>2 ROOM</td>\n",
       "      <td>172</td>\n",
       "      <td>ANG MO KIO AVE 4</td>\n",
       "      <td>06 TO 10</td>\n",
       "      <td>45.0</td>\n",
       "      <td>Improved</td>\n",
       "      <td>1986</td>\n",
       "      <td>250000.0</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2012-03</td>\n",
       "      <td>ANG MO KIO</td>\n",
       "      <td>2 ROOM</td>\n",
       "      <td>510</td>\n",
       "      <td>ANG MO KIO AVE 8</td>\n",
       "      <td>01 TO 05</td>\n",
       "      <td>44.0</td>\n",
       "      <td>Improved</td>\n",
       "      <td>1980</td>\n",
       "      <td>265000.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2012-03</td>\n",
       "      <td>ANG MO KIO</td>\n",
       "      <td>3 ROOM</td>\n",
       "      <td>610</td>\n",
       "      <td>ANG MO KIO AVE 4</td>\n",
       "      <td>06 TO 10</td>\n",
       "      <td>68.0</td>\n",
       "      <td>New Generation</td>\n",
       "      <td>1980</td>\n",
       "      <td>315000.0</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2012-03</td>\n",
       "      <td>ANG MO KIO</td>\n",
       "      <td>3 ROOM</td>\n",
       "      <td>474</td>\n",
       "      <td>ANG MO KIO AVE 10</td>\n",
       "      <td>01 TO 05</td>\n",
       "      <td>67.0</td>\n",
       "      <td>New Generation</td>\n",
       "      <td>1984</td>\n",
       "      <td>320000.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2012-03</td>\n",
       "      <td>ANG MO KIO</td>\n",
       "      <td>3 ROOM</td>\n",
       "      <td>604</td>\n",
       "      <td>ANG MO KIO AVE 5</td>\n",
       "      <td>06 TO 10</td>\n",
       "      <td>67.0</td>\n",
       "      <td>New Generation</td>\n",
       "      <td>1980</td>\n",
       "      <td>321000.0</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>185963</th>\n",
       "      <td>2021-04</td>\n",
       "      <td>YISHUN</td>\n",
       "      <td>EXECUTIVE</td>\n",
       "      <td>277</td>\n",
       "      <td>YISHUN ST 22</td>\n",
       "      <td>04 TO 06</td>\n",
       "      <td>146.0</td>\n",
       "      <td>Maisonette</td>\n",
       "      <td>1985</td>\n",
       "      <td>625000.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>185964</th>\n",
       "      <td>2021-04</td>\n",
       "      <td>YISHUN</td>\n",
       "      <td>EXECUTIVE</td>\n",
       "      <td>877</td>\n",
       "      <td>YISHUN ST 81</td>\n",
       "      <td>10 TO 12</td>\n",
       "      <td>142.0</td>\n",
       "      <td>Apartment</td>\n",
       "      <td>1987</td>\n",
       "      <td>728000.0</td>\n",
       "      <td>11.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>185965</th>\n",
       "      <td>2021-04</td>\n",
       "      <td>YISHUN</td>\n",
       "      <td>4 ROOM</td>\n",
       "      <td>506C</td>\n",
       "      <td>YISHUN AVE 4</td>\n",
       "      <td>07 TO 09</td>\n",
       "      <td>93.0</td>\n",
       "      <td>Model A</td>\n",
       "      <td>2018</td>\n",
       "      <td>460888.0</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>185966</th>\n",
       "      <td>2021-04</td>\n",
       "      <td>YISHUN</td>\n",
       "      <td>5 ROOM</td>\n",
       "      <td>511B</td>\n",
       "      <td>YISHUN ST 51</td>\n",
       "      <td>13 TO 15</td>\n",
       "      <td>113.0</td>\n",
       "      <td>Improved</td>\n",
       "      <td>2017</td>\n",
       "      <td>600000.0</td>\n",
       "      <td>14.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>185967</th>\n",
       "      <td>2021-04</td>\n",
       "      <td>YISHUN</td>\n",
       "      <td>5 ROOM</td>\n",
       "      <td>511A</td>\n",
       "      <td>YISHUN ST 51</td>\n",
       "      <td>07 TO 09</td>\n",
       "      <td>113.0</td>\n",
       "      <td>Improved</td>\n",
       "      <td>2017</td>\n",
       "      <td>575000.0</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>185968 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Tranc_YearMonth        town  flat_type block        street_name  \\\n",
       "0              2012-03  ANG MO KIO     2 ROOM   172   ANG MO KIO AVE 4   \n",
       "1              2012-03  ANG MO KIO     2 ROOM   510   ANG MO KIO AVE 8   \n",
       "2              2012-03  ANG MO KIO     3 ROOM   610   ANG MO KIO AVE 4   \n",
       "3              2012-03  ANG MO KIO     3 ROOM   474  ANG MO KIO AVE 10   \n",
       "4              2012-03  ANG MO KIO     3 ROOM   604   ANG MO KIO AVE 5   \n",
       "...                ...         ...        ...   ...                ...   \n",
       "185963         2021-04      YISHUN  EXECUTIVE   277       YISHUN ST 22   \n",
       "185964         2021-04      YISHUN  EXECUTIVE   877       YISHUN ST 81   \n",
       "185965         2021-04      YISHUN     4 ROOM  506C       YISHUN AVE 4   \n",
       "185966         2021-04      YISHUN     5 ROOM  511B       YISHUN ST 51   \n",
       "185967         2021-04      YISHUN     5 ROOM  511A       YISHUN ST 51   \n",
       "\n",
       "       storey_range  floor_area_sqm      flat_model  lease_commence_date  \\\n",
       "0          06 TO 10            45.0        Improved                 1986   \n",
       "1          01 TO 05            44.0        Improved                 1980   \n",
       "2          06 TO 10            68.0  New Generation                 1980   \n",
       "3          01 TO 05            67.0  New Generation                 1984   \n",
       "4          06 TO 10            67.0  New Generation                 1980   \n",
       "...             ...             ...             ...                  ...   \n",
       "185963     04 TO 06           146.0      Maisonette                 1985   \n",
       "185964     10 TO 12           142.0       Apartment                 1987   \n",
       "185965     07 TO 09            93.0         Model A                 2018   \n",
       "185966     13 TO 15           113.0        Improved                 2017   \n",
       "185967     07 TO 09           113.0        Improved                 2017   \n",
       "\n",
       "        resale_price  storey_midpoint  \n",
       "0           250000.0              8.0  \n",
       "1           265000.0              3.0  \n",
       "2           315000.0              8.0  \n",
       "3           320000.0              3.0  \n",
       "4           321000.0              8.0  \n",
       "...              ...              ...  \n",
       "185963      625000.0              5.0  \n",
       "185964      728000.0             11.0  \n",
       "185965      460888.0              8.0  \n",
       "185966      600000.0             14.0  \n",
       "185967      575000.0              8.0  \n",
       "\n",
       "[185968 rows x 11 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def get_mid_storey(row):\n",
    "    top = int(row['storey_range'].split(' TO ')[1])\n",
    "    bottom = int(row['storey_range'].split(' TO ')[0])\n",
    "    mid = (top+bottom)/2\n",
    "    return mid\n",
    "\n",
    "df['storey_midpoint'] = df.apply(get_mid_storey, axis=1)\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br>\n",
    "\n",
    "B) Create a new column **address** to that combine the **block number** and the **street name**.\\\n",
    "For example: the address for the first row is \"172, ANG MO KIO AVE 4\".\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_address(row):\n",
    "    address = row['block'] + \", \" + row['street_name']\n",
    "    return address\n",
    "\n",
    "df['address'] = df.apply(get_address, axis=1)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br>\n",
    "\n",
    "C) There are some missing values in the **flat_type** model.\\\n",
    "After confirming with the domain/business users, there are only 2 possibilities:\n",
    "\n",
    "- if the floor_area_sqm is **at least 110 squared meters** then it's 5-room flat\n",
    "- **otherwise** it's 4-room flat\n",
    "\n",
    "you requested to use the following logics to fill up the missing values:\\\n",
    "hint: \n",
    "- you can use the .apply() method or any other approaches. \n",
    "- you will also need to check if the value is missing\n",
    "\n",
    "Note: This is simplified logic solely for practice purpose"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 185968 entries, 0 to 185967\n",
      "Data columns (total 12 columns):\n",
      " #   Column               Non-Null Count   Dtype  \n",
      "---  ------               --------------   -----  \n",
      " 0   Tranc_YearMonth      184086 non-null  object \n",
      " 1   town                 185968 non-null  object \n",
      " 2   flat_type            185968 non-null  object \n",
      " 3   block                185968 non-null  object \n",
      " 4   street_name          185968 non-null  object \n",
      " 5   storey_range         185968 non-null  object \n",
      " 6   floor_area_sqm       185968 non-null  float64\n",
      " 7   flat_model           185968 non-null  object \n",
      " 8   lease_commence_date  185968 non-null  int64  \n",
      " 9   resale_price         185968 non-null  float64\n",
      " 10  storey_midpoint      185968 non-null  float64\n",
      " 11  address              185968 non-null  object \n",
      "dtypes: float64(3), int64(1), object(8)\n",
      "memory usage: 17.0+ MB\n"
     ]
    }
   ],
   "source": [
    "def fill_missing_flat_type(row):\n",
    "    value_to_return = '' #empty string\n",
    "    \n",
    "    # Checking if the value in this column is missing\n",
    "    if pd.isna(row['flat_type']):\n",
    "        if row['floor_area_sqm'] >= 110:\n",
    "            output_value = '5 ROOM'\n",
    "        else:\n",
    "            output_value = '4 ROOM'\n",
    "    \n",
    "    else:\n",
    "        value_to_return = row['flat_type']\n",
    "    \n",
    "        \n",
    "    return value_to_return\n",
    "\n",
    "df['flat_type'] = df.apply (fill_missing_flat_type, axis=1)\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "# Exporting the Processed Data\n",
    "Save the processed df in **Processed Data folder** as <b>hdb_resale_full_processed.csv</b> <br/>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv ('data/1_processed_data/hdb_resale_full_processed.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "colab": {
   "name": "DC Bootcamp Week 17.ipynb",
   "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.9.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}

# data[].attributes.body
Please help to take a look, thanks!

# data[].attributes.body
Completed

# data[].attributes.body
Week 7 Assignment