8_Data_Cleaning_&_Missing_Values_Class.ipynb

{
  "cells": [
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "d4cNAbGFbI9x"
      },
      "outputs": [],
      "source": [
        "import numpy as np\n",
        "import pandas as pd\n",
        "import plotly.express as px"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "GDXozNx8bI93"
      },
      "source": [
        "# Data Cleaning & Missing Values"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "nmlX7MM6bI93"
      },
      "source": [
        "### Load the Data"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "d6SBrB_2bI94"
      },
      "outputs": [],
      "source": [
        "#df = pd.read_csv('berlin_housing_with_scraped.csv')\n",
        "df = pd.read_csv('https://raw.githubusercontent.com/ReDI-School/data-analytics/master/Class%20material/Class-11_Data_cleaning/berlin_housing_with_scraped.csv')"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "8EtJLvHWbI-C"
      },
      "source": [
        "### Check the Data's `shape`,  `head`, and the `dtypes`"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "nlYfVEnrbI-D",
        "outputId": "571370c3-0653-40a5-a9fe-685eb5b47e1a"
      },
      "outputs": [],
      "source": [
        "df.shape"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 366
        },
        "id": "5bUCCKoBbI-J",
        "outputId": "33e41ef5-4db7-4fc8-9e7e-ca2c57f8aa1e"
      },
      "outputs": [],
      "source": [
        "df.head(3)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "2b47oGWRbI-N",
        "outputId": "19456c20-9265-47d9-d53e-a023861753c7"
      },
      "outputs": [],
      "source": [
        "df.dtypes"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "nxvFzHHVbI-P"
      },
      "source": [
        "### First Question: Is there missing data? If yes, how much?"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "dEnuiNxnbI-Q",
        "outputId": "92895d68-2e43-48cf-d2db-2e1032f2be8f"
      },
      "outputs": [],
      "source": [
        "(df.isnull().sum() / len(df)).sort_values(ascending = False)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "RvuE6wFxbI-S"
      },
      "source": [
        "## Clean `tags`\n",
        "\n",
        "Have a look at the tags columns. What does this information tell us?"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 35
        },
        "id": "UqH7ShmObI-T",
        "outputId": "5323fd56-e823-4824-d0a3-70ffb747ea28"
      },
      "outputs": [],
      "source": [
        "df.tags[0]"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "cATkm2kAbI-V",
        "outputId": "84094fe5-778c-40c2-be1d-078a4dbcc181"
      },
      "outputs": [],
      "source": [
        "df.tags.unique()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "C-VytCkxbI-X"
      },
      "source": [
        "Because this doesn't give us any information, let's get rid of redundant data."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "AHPF5hpubI-Y"
      },
      "outputs": [],
      "source": [
        "df.drop('tags', axis=1, inplace=True)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "DetztCg8bI-a",
        "outputId": "5c1e40c1-9c71-4932-fce2-455009571864"
      },
      "outputs": [],
      "source": [
        "df.shape"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "NHMXbm3zbI-d"
      },
      "source": [
        "## Clean `security_deposit`"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "AHCgGQikbI-e",
        "outputId": "673446d5-0b82-41c0-de15-1ce87acb9c12"
      },
      "outputs": [],
      "source": [
        "df.security_deposit.value_counts()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "uy8EeuYdbI-j"
      },
      "source": [
        "Some values clearly are an amount/ For example:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "i5V_frtibI-k",
        "outputId": "72c3d1b9-caf3-4567-c613-0f246bf1c158"
      },
      "outputs": [],
      "source": [
        "df.security_deposit[df.security_deposit.str.contains(\"€\").fillna(False)]"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "T4h_j3PwbI-n"
      },
      "source": [
        "Other values are impossible to interpret."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "acvGtjm9bI-o",
        "outputId": "f040606b-020c-4189-fe0c-1e28fa06e4c9"
      },
      "outputs": [],
      "source": [
        "df.security_deposit[df.security_deposit.str.contains(\"Kaution\").fillna(False)]"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "bBiXMHB6bI-q"
      },
      "source": [
        "Which values are numeric?"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "5D9q1YNtbI-r"
      },
      "source": [
        "**First, let's remove the currency**"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "XAqezajNQziv",
        "outputId": "57cab71e-d3aa-4cca-fe0a-0511730b093b"
      },
      "outputs": [],
      "source": [
        "type(str(np.nan))"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "fR3D3jWdbI-r"
      },
      "outputs": [],
      "source": [
        "def remove_strings(value, strings_to_remove):\n",
        "    \"\"\"\n",
        "    Remove strings in <strings_to_remove> from <value>. \n",
        "    Removes Euro/euro strings by default. \n",
        "    Assumes that each string occurs only once.\n",
        "    \"\"\"\n",
        "    \n",
        "    # return NaN if value is missing\n",
        "    if pd.isna(value): return np.nan\n",
        "    \n",
        "    # cast to string so that string methods work\n",
        "    value = str(value)\n",
        "    \n",
        "    # remove different strings\n",
        "    for string in strings_to_remove:\n",
        "        value = value.lower().replace(string, '').strip()\n",
        "    \n",
        "    return value"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "Vr9aPAWGbI-u"
      },
      "outputs": [],
      "source": [
        "df['security_deposit'] = df.security_deposit.apply(remove_strings, args=(['€', 'Euro'],)).astype(str)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "dHj-3mqfbI-w",
        "outputId": "cd4e61ea-f547-4cdb-aa80-16edf82d155b"
      },
      "outputs": [],
      "source": [
        "df.security_deposit.unique()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "4e3K78iWbI-y"
      },
      "source": [
        "**Now, we need to convert the decimal into something a computer can better read**"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "GcPJ1zkhbI-z"
      },
      "outputs": [],
      "source": [
        "def convert_string_to_float(value):\n",
        "    \"\"\"Replace a period with empty stringm and comma with period (decimal point).\"\"\"\n",
        "    return value.replace(\".\", \"\").replace(\",\", \".\")\n",
        "\n",
        "\n",
        "def convert_decimal(value):\n",
        "    \"\"\"Convert numeric string to a decimal number.\"\"\"\n",
        "    \n",
        "    # return NaN if missing\n",
        "    if pd.isna(value):\n",
        "        return np.nan       \n",
        "    \n",
        "    # check if wrong format w/o decimal i.e. 3.500\n",
        "    if len(value.split('.')[-1]) == 3:\n",
        "        return convert_string_to_float(value)\n",
        "    \n",
        "    # convert to float\n",
        "    try:                               \n",
        "        return float(value)\n",
        "    except:\n",
        "        return convert_string_to_float(value)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "jfdRe96JbI-1"
      },
      "outputs": [],
      "source": [
        "df['security_deposit'] = df.security_deposit.apply(convert_decimal)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "dRiZo2jYbI-3",
        "outputId": "4f44ae21-3722-4bbb-fdc0-6415807a4c2e"
      },
      "outputs": [],
      "source": [
        "df.security_deposit.unique()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "L6XpUkDdSD7-",
        "outputId": "57f446b1-b2f9-4193-d99f-b82f7920c639"
      },
      "outputs": [],
      "source": [
        "df.rent.isnull().sum()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "YI3psNl2bI-6"
      },
      "source": [
        "**And now, let's get a list of non-numeric values**"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "GwSjFunKbI-6",
        "outputId": "112595aa-f8a9-492d-e58a-2e1ee5cb1712"
      },
      "outputs": [],
      "source": [
        "def is_float(value):\n",
        "    try:\n",
        "        float(value)\n",
        "        return True\n",
        "    except:\n",
        "        return False\n",
        "\n",
        "df.security_deposit[df.security_deposit.apply(lambda x: not is_float(x))].unique()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "VUFeofyMbI-9"
      },
      "source": [
        "**Build a map of what the values should be. This is manually set because it's a small amount of data.**"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "HdsUIIfabI-9"
      },
      "outputs": [],
      "source": [
        "str_security_deposit_map = {\n",
        "    'drei': '3',\n",
        "    '3x': '3',\n",
        "    '9150.-': 9150,\n",
        "    '2xnkm': '2',\n",
        "    '6000.-': 6000,\n",
        "    '3-fache': '3',\n",
        "    '3nkm': '3',    \n",
        "}\n",
        "\n",
        "str_security_deposit_unclear = ['ja', 'kaution', 'kaution:']"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "-WI-rXv7bI_A"
      },
      "outputs": [],
      "source": [
        "df['security_deposit'] = df.security_deposit.apply(lambda x: str_security_deposit_map.get(x, x))"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "I5obd8DTbI_D",
        "outputId": "54f2c682-da08-4fbb-f5cf-075599124b3e"
      },
      "outputs": [],
      "source": [
        "df.security_deposit.unique()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "oNUvcUThbI_I",
        "outputId": "a7a63b9f-044f-46e7-96bc-856c225fd773"
      },
      "outputs": [],
      "source": [
        "def convert_to_float(value):\n",
        "    if is_float(value):\n",
        "        return float(value)\n",
        "    else:\n",
        "        return np.nan\n",
        "\n",
        "# Not storing in df, just want to see output\n",
        "df.security_deposit[df.security_deposit.apply(lambda x: convert_to_float(x)) <= 3].value_counts()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "eErOOWPFbI_K"
      },
      "source": [
        "Let's assume that the unclear values are three months, as it is the most common option."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "asC9OpKNbI_K"
      },
      "outputs": [],
      "source": [
        "unclear_security_deposit_map = {str_security_deposit: '3' for str_security_deposit in str_security_deposit_unclear}"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "9BbMjRCUbI_M"
      },
      "outputs": [],
      "source": [
        "df['security_deposit'] = df.security_deposit.apply(lambda x: unclear_security_deposit_map.get(x, x))"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "VX7Ow3hWbI_P",
        "outputId": "4daec14f-f17d-4a39-f691-2f326acd4e9e"
      },
      "outputs": [],
      "source": [
        "df.security_deposit.unique()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "G8ygO5OFbI_S"
      },
      "source": [
        "The values 1, 2 and 3 refer to the number of months rent as deposit, so let's calculate those values by multiplying with the rent"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "-bqQkaErbI_S",
        "outputId": "2cf70e23-20ca-4c70-9e99-f29e800e5834"
      },
      "outputs": [],
      "source": [
        "df.rent.dtype"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "CwVdGYwdbI_V"
      },
      "source": [
        "**Now, convert the month duration columns (3/2/etc Months) to an actual value, based on the rent**"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "Sd9JCZVQbI_V"
      },
      "outputs": [],
      "source": [
        "def convert_months_to_total(row):\n",
        "    fl_dep = convert_to_float(row.security_deposit)\n",
        "    if fl_dep <= 3:\n",
        "        return float(row.rent * fl_dep)\n",
        "    else:\n",
        "        return fl_dep"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 1000
        },
        "id": "XVrilxfmbI_X",
        "outputId": "810b4b23-9f1b-47d7-9e00-50f2d03773d0"
      },
      "outputs": [],
      "source": [
        "df = df.assign(deposit_cleaned=df.apply(convert_months_to_total, axis = 1))\n",
        "\n",
        "df[[\"security_deposit\", \"deposit_cleaned\", \"rent\"]].head(50)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "fUoDxbKabI_Z"
      },
      "source": [
        "#### Check Distribution and Descriptive Stats"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 542
        },
        "id": "iCLbFwzjRFkf",
        "outputId": "76e6d05e-9e00-4cde-d602-bc2050d24ac9"
      },
      "outputs": [],
      "source": [
        "px.histogram(df, x=\"deposit_cleaned\", marginal='box')"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "GK52nKKhbI_d",
        "outputId": "d720e2d0-3edb-4012-a053-efef94fc71aa"
      },
      "outputs": [],
      "source": [
        "df.deposit_cleaned.agg(['mean', 'median', 'std', 'min', 'max'])"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "6NLx-OKxbI_f"
      },
      "source": [
        "#### Filling the missing values intuitively\n",
        "\n",
        "(Note: these are old advertisements and no longer available)\n",
        "\n",
        " - https://www.immobilienscout24.de/expose/116573177: has all of it's data mising, *will assume three months*\n",
        " - https://www.immobilienscout24.de/expose/115925878: has only some of its data missing, *will assume scraping error and therefore three months*\n",
        " - https://www.immobilienscout24.de/expose/114437800: has only deposit missing, *will assume there is no deposit*\n",
        " \n",
        "**Because the data is missing for different reasons, we should use different fill strategies**"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 343
        },
        "id": "PWTfOO-kbI_f",
        "outputId": "10b03bd6-e1ff-4445-a113-4a6e0b32210b"
      },
      "outputs": [],
      "source": [
        "df[df.deposit_cleaned.isna()].head()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "SQPIgw7JbI_i"
      },
      "outputs": [],
      "source": [
        "df['n_missing'] = df[df.columns[-14:]].isnull().sum(axis=1)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 264
        },
        "id": "OqSUs4g_bI_l",
        "outputId": "0537ce7d-2a9c-4996-e21a-78519f8b999c"
      },
      "outputs": [],
      "source": [
        "df[df.n_missing == 1].head(3)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "qjbpPkoZbI_n"
      },
      "outputs": [],
      "source": [
        "def fill_missing_security_deposit(row):\n",
        "  \n",
        "    # if the value isn't missing, return the value\n",
        "    if not pd.isna(row.deposit_cleaned):\n",
        "        return row.deposit_cleaned\n",
        "  \n",
        "    # if there is only one missing value, return 0. Otherwise return 3 months of rent\n",
        "    if row.n_missing == 1:\n",
        "        return 0\n",
        "    else:\n",
        "        return row.rent * 3"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "12Ystgy0bI_r"
      },
      "outputs": [],
      "source": [
        "df['deposit_cleaned'] = df.apply(fill_missing_security_deposit, axis=1)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 542
        },
        "id": "56Q8mFJHbI_u",
        "outputId": "109cad21-e44e-46ef-cc18-0933ef375a8e"
      },
      "outputs": [],
      "source": [
        "px.histogram(df, x=\"deposit_cleaned\", marginal='box')"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "NeDRk2t9bI_w",
        "outputId": "f6c636a4-7cd5-4024-a370-8a41078c58ca"
      },
      "outputs": [],
      "source": [
        "df.deposit_cleaned.agg(['mean', 'median', 'std', 'min', 'max'])"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "78zmRktIbI_y",
        "outputId": "c0f0bed2-6d16-48e5-c02f-084cd55e1073"
      },
      "outputs": [],
      "source": [
        "df.security_deposit.isnull().sum()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "YJtU_NTrUgDd"
      },
      "outputs": [],
      "source": [
        "tdf = pd.DataFrame({\n",
        "    'val_1': [1, 2, 3, np.nan, np.nan],\n",
        "    'val_2': [1, 2, 3, 4, np.nan],\n",
        "})"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 142
        },
        "id": "Wth9v2mCVmPj",
        "outputId": "41139c1e-d6d8-447d-b502-d884df6ed718"
      },
      "outputs": [],
      "source": [
        "tdf.dropna()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 173
        },
        "id": "TLwROSfFVocq",
        "outputId": "4880c34b-5fb7-4967-c863-d353750ceb37"
      },
      "outputs": [],
      "source": [
        "tdf.dropna(how='all')"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 204
        },
        "id": "1hl0zjkWU-Bb",
        "outputId": "8399ec13-6930-4f9c-9be7-5e22dbd74c4d"
      },
      "outputs": [],
      "source": [
        "tdf"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "egHxAgCKU-KF"
      },
      "outputs": [],
      "source": [
        "tdf['val_1'] = tdf.val_1.fillna(-99)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 204
        },
        "id": "9nIijmynVWsr",
        "outputId": "0c45a7a7-d43e-4ef4-ea7f-a2e3e484f768"
      },
      "outputs": [],
      "source": [
        "tdf"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ax6568FAbI_1"
      },
      "source": [
        "## Recap:\n",
        "\n",
        " **Useful methods for dealing with missing data**:\n",
        " - `pd.isna(VALUE)` and  `pd.isnull(VALUE)`: check if a value is `NaN`, returns `boolean`\n",
        " - `df.COLUMN_NAME.isna()` and `df.COLUMN_NAME.isnull()`: check each value in column to see if it is `NaN`, returns `boolean`\n",
        " - `df.dropna()`: will drop **every** row that has a missing value\n",
        "     - optional arguments:\n",
        "         - `axis`: drop the column\n",
        "         - `how`: `any` or `all` drop if `any` value is missing, or `all` values in that row/col are missing\n",
        " - `df.fillna(VALUE_TO_FILL_WITH)`: will fill every missing value (`NaN`) with the value of your choice\n",
        " \n",
        "**NOTE**: na and null do the **exact** same thing in Pandas, even the docs are the same. The duplicate methods are carried over from `R` which treated `na` and `null` as different values."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "6tWur5WUbI_1"
      },
      "source": [
        "## Clean `property_condition`"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "tV1Xo5ODbI_2",
        "outputId": "cd931248-2c16-47d4-98ce-412c35764c07"
      },
      "outputs": [],
      "source": [
        "(df.isnull().sum() / len(df)).sort_values(ascending = False)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 360
        },
        "id": "iUuCkDCqbI_4",
        "outputId": "12f4de04-eabe-42f6-b4cb-68c9f21b293e"
      },
      "outputs": [],
      "source": [
        "df.head()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "eEfU4G9WbI_6"
      },
      "source": [
        "#### Categorical Features need to be handled differently than Continuous\n",
        "\n",
        "Common methods:\n",
        " - Mode: May bias the dataset towards that category\n",
        " - Intuitive: Set the category based on domain knowledge, could be a problem if you are wrong\n",
        " - An `Unknown` Category: A catch all category for missing categories, especially useful if there is a reason the data is missing"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "bse6G0C8W20W",
        "outputId": "03890521-b27d-427f-a000-20d593abedad"
      },
      "outputs": [],
      "source": [
        "df.parking_space.isnull().sum() / len(df)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "HtcYUIzsW9mi",
        "outputId": "79070adc-787f-4723-aab0-20cfe2ace9ee"
      },
      "outputs": [],
      "source": [
        "df.parking_space.mode()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "uf8K8PLIbI_7",
        "outputId": "291b8cfa-d9da-4921-dd37-9cb2c1faf04b"
      },
      "outputs": [],
      "source": [
        "df.property_condition.fillna('MISSING').value_counts()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "AIMGY-RibI_9"
      },
      "outputs": [],
      "source": [
        "df['property_condition'] = df.property_condition.fillna('Unknown')"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 542
        },
        "id": "MwDASxOmSgM7",
        "outputId": "3ed11986-ffe0-4f0a-c7c9-fb8747302cfd"
      },
      "outputs": [],
      "source": [
        "px.bar(df.property_condition.value_counts().reset_index(), x='index', y='property_condition')"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "MRoozyr8bJAB"
      },
      "source": [
        "## Clean `cold_rent`"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "TqZ6sv7EbJAC",
        "outputId": "8f3da4cd-cfd5-4add-9552-17d86310f26c"
      },
      "outputs": [],
      "source": [
        "(df.isnull().sum() / len(df)).sort_values(ascending=False)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "sGDBYvuUbJAF"
      },
      "source": [
        "**Since we have the warm rent for every sample, rather than use the median or mean, a better method might be to get the average warm/cold rent ratio and apply that to all missing values**"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Tiqz9bX6aBvR",
        "outputId": "58844aa5-0295-42bd-b178-bb32f89c75ef"
      },
      "outputs": [],
      "source": [
        "df.cold_rent"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "WImgpWYibJAG"
      },
      "outputs": [],
      "source": [
        "# reuse functions from before\n",
        "df['cold_rent'] = df.cold_rent.apply(lambda x: remove_strings(x, [\"€\", \"Euro\"])).astype(str)\n",
        "df['cold_rent'] = df.cold_rent.apply(convert_decimal)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "guLHeOdlbJAK",
        "outputId": "be468c47-1150-4d6b-9f19-30073d8c7914"
      },
      "outputs": [],
      "source": [
        "total_warm_and_cold = df[['rent', 'cold_rent']].dropna().astype(float).sum()\n",
        "total_warm_and_cold"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "csXv2ZAQbJAN",
        "outputId": "9f9b7714-fd53-433b-aebe-6197888ffd42"
      },
      "outputs": [],
      "source": [
        "warm_cold_rent_ratio = total_warm_and_cold.cold_rent / total_warm_and_cold.rent\n",
        "warm_cold_rent_ratio"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 542
        },
        "id": "jhnPY0dLS_Me",
        "outputId": "83653bbd-dc0e-4b70-8043-7a6fd2d875d3"
      },
      "outputs": [],
      "source": [
        "px.histogram(df[['cold_rent']].astype(float).dropna(), x='cold_rent', marginal='box')"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "IOjd4rKJbJAV"
      },
      "outputs": [],
      "source": [
        "df['cold_rent'] = df.cold_rent.astype(float)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "fJOTBKQBbJAW"
      },
      "outputs": [],
      "source": [
        "def get_cold_rent(row):\n",
        "    if not pd.isna(row.cold_rent): \n",
        "        return float(row.cold_rent)\n",
        "    else:\n",
        "        return row.rent * warm_cold_rent_ratio"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "fMkIdrXKbJAY"
      },
      "outputs": [],
      "source": [
        "df['cold_rent'] = df.apply(get_cold_rent, axis=1)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 542
        },
        "id": "T1aZVcz6bJAd",
        "outputId": "97001820-3d5e-49be-cb93-f38c0e29136e"
      },
      "outputs": [],
      "source": [
        "px.histogram(df, x='cold_rent', marginal='box')"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "C10Z29twTkH1"
      },
      "outputs": [],
      "source": [
        "df.to_pickle('berlin_housing_with_scraped_class_cleaned.pkl')"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "tHUbESqOUFqQ"
      },
      "outputs": [],
      "source": []
    }
  ],
  "metadata": {
    "colab": {
      "collapsed_sections": [
        "ax6568FAbI_1"
      ],
      "name": "Data Cleaning & Missing Values - Class.ipynb",
      "provenance": []
    },
    "kernelspec": {
      "display_name": "Python 3.9.12 ('base')",
      "language": "python",
      "name": "python3"
    },
    "language_info": {
      "codemirror_mode": {
        "name": "ipython",
        "version": 3
      },
      "file_extension": ".py",
      "mimetype": "text/x-python",
      "name": "python",
      "nbconvert_exporter": "python",
      "pygments_lexer": "ipython3",
      "version": "3.9.12"
    },
    "varInspector": {
      "cols": {
        "lenName": 16,
        "lenType": 16,
        "lenVar": 40
      },
      "kernels_config": {
        "python": {
          "delete_cmd_postfix": "",
          "delete_cmd_prefix": "del ",
          "library": "var_list.py",
          "varRefreshCmd": "print(var_dic_list())"
        },
        "r": {
          "delete_cmd_postfix": ") ",
          "delete_cmd_prefix": "rm(",
          "library": "var_list.r",
          "varRefreshCmd": "cat(var_dic_list()) "
        }
      },
      "types_to_exclude": [
        "module",
        "function",
        "builtin_function_or_method",
        "instance",
        "_Feature"
      ],
      "window_display": false
    },
    "vscode": {
      "interpreter": {
        "hash": "1e3f06a58ee9140a7af43a06e97858489ebd5ee54f1ad3650c8e966fe6503781"
      }
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}