Day_011_HW - outliers.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 處理 outliers\n",
    "* 新增欄位註記\n",
    "* outliers 或 NA 填補\n",
    "    1. 平均數 (mean)\n",
    "    2. 中位數 (median, or Q50)\n",
    "    3. 最大/最小值 (max/min, Q100, Q0)\n",
    "    4. 分位數 (quantile)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# [作業目標]\n",
    "- 仿造範例的資料操作, 試著進行指定的離群值處理"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# [作業重點]\n",
    "- 計算 AMT_ANNUITY 的分位點 (q0 - q100) (Hint : np.percentile, In[3])\n",
    "- 將 AMT_ANNUITY 的 NaN 用中位數取代 (Hint : q50, In[4])\n",
    "- 將 AMT_ANNUITY 數值轉換到 -1 ~ 1 之間 (Hint : 參考範例, In[5])\n",
    "- 將 AMT_GOOD_PRICE 的 NaN 用眾數取代 (In[6])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import 需要的套件\n",
    "import os\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "%matplotlib inline\n",
    "\n",
    "# 設定 data_path\n",
    "#dir_data = 'data/'\n",
    "#f_app = os.path.join(dir_data, 'application_train.csv')\n",
    "#print('Path of read in data: %s' % (f_app))"
   ]
  },
  {
   "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>SK_ID_CURR</th>\n",
       "      <th>TARGET</th>\n",
       "      <th>NAME_CONTRACT_TYPE</th>\n",
       "      <th>CODE_GENDER</th>\n",
       "      <th>FLAG_OWN_CAR</th>\n",
       "      <th>FLAG_OWN_REALTY</th>\n",
       "      <th>CNT_CHILDREN</th>\n",
       "      <th>AMT_INCOME_TOTAL</th>\n",
       "      <th>AMT_CREDIT</th>\n",
       "      <th>AMT_ANNUITY</th>\n",
       "      <th>...</th>\n",
       "      <th>FLAG_DOCUMENT_18</th>\n",
       "      <th>FLAG_DOCUMENT_19</th>\n",
       "      <th>FLAG_DOCUMENT_20</th>\n",
       "      <th>FLAG_DOCUMENT_21</th>\n",
       "      <th>AMT_REQ_CREDIT_BUREAU_HOUR</th>\n",
       "      <th>AMT_REQ_CREDIT_BUREAU_DAY</th>\n",
       "      <th>AMT_REQ_CREDIT_BUREAU_WEEK</th>\n",
       "      <th>AMT_REQ_CREDIT_BUREAU_MON</th>\n",
       "      <th>AMT_REQ_CREDIT_BUREAU_QRT</th>\n",
       "      <th>AMT_REQ_CREDIT_BUREAU_YEAR</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100002</td>\n",
       "      <td>1</td>\n",
       "      <td>Cash loans</td>\n",
       "      <td>M</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>0</td>\n",
       "      <td>202500.0</td>\n",
       "      <td>406597.5</td>\n",
       "      <td>24700.5</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100003</td>\n",
       "      <td>0</td>\n",
       "      <td>Cash loans</td>\n",
       "      <td>F</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>0</td>\n",
       "      <td>270000.0</td>\n",
       "      <td>1293502.5</td>\n",
       "      <td>35698.5</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>100004</td>\n",
       "      <td>0</td>\n",
       "      <td>Revolving loans</td>\n",
       "      <td>M</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>0</td>\n",
       "      <td>67500.0</td>\n",
       "      <td>135000.0</td>\n",
       "      <td>6750.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>100006</td>\n",
       "      <td>0</td>\n",
       "      <td>Cash loans</td>\n",
       "      <td>F</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>0</td>\n",
       "      <td>135000.0</td>\n",
       "      <td>312682.5</td>\n",
       "      <td>29686.5</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>100007</td>\n",
       "      <td>0</td>\n",
       "      <td>Cash loans</td>\n",
       "      <td>M</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>0</td>\n",
       "      <td>121500.0</td>\n",
       "      <td>513000.0</td>\n",
       "      <td>21865.5</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 122 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   SK_ID_CURR  TARGET NAME_CONTRACT_TYPE CODE_GENDER FLAG_OWN_CAR  \\\n",
       "0      100002       1         Cash loans           M            N   \n",
       "1      100003       0         Cash loans           F            N   \n",
       "2      100004       0    Revolving loans           M            Y   \n",
       "3      100006       0         Cash loans           F            N   \n",
       "4      100007       0         Cash loans           M            N   \n",
       "\n",
       "  FLAG_OWN_REALTY  CNT_CHILDREN  AMT_INCOME_TOTAL  AMT_CREDIT  AMT_ANNUITY  \\\n",
       "0               Y             0          202500.0    406597.5      24700.5   \n",
       "1               N             0          270000.0   1293502.5      35698.5   \n",
       "2               Y             0           67500.0    135000.0       6750.0   \n",
       "3               Y             0          135000.0    312682.5      29686.5   \n",
       "4               Y             0          121500.0    513000.0      21865.5   \n",
       "\n",
       "   ...  FLAG_DOCUMENT_18 FLAG_DOCUMENT_19 FLAG_DOCUMENT_20 FLAG_DOCUMENT_21  \\\n",
       "0  ...                 0                0                0                0   \n",
       "1  ...                 0                0                0                0   \n",
       "2  ...                 0                0                0                0   \n",
       "3  ...                 0                0                0                0   \n",
       "4  ...                 0                0                0                0   \n",
       "\n",
       "  AMT_REQ_CREDIT_BUREAU_HOUR AMT_REQ_CREDIT_BUREAU_DAY  \\\n",
       "0                        0.0                       0.0   \n",
       "1                        0.0                       0.0   \n",
       "2                        0.0                       0.0   \n",
       "3                        NaN                       NaN   \n",
       "4                        0.0                       0.0   \n",
       "\n",
       "   AMT_REQ_CREDIT_BUREAU_WEEK  AMT_REQ_CREDIT_BUREAU_MON  \\\n",
       "0                         0.0                        0.0   \n",
       "1                         0.0                        0.0   \n",
       "2                         0.0                        0.0   \n",
       "3                         NaN                        NaN   \n",
       "4                         0.0                        0.0   \n",
       "\n",
       "   AMT_REQ_CREDIT_BUREAU_QRT  AMT_REQ_CREDIT_BUREAU_YEAR  \n",
       "0                        0.0                         1.0  \n",
       "1                        0.0                         0.0  \n",
       "2                        0.0                         0.0  \n",
       "3                        NaN                         NaN  \n",
       "4                        0.0                         0.0  \n",
       "\n",
       "[5 rows x 122 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "app_train = pd.read_csv(r\"C:\\Users\\user\\100 days\\data\\application_train.csv\")\n",
    "app_train.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. 列出 AMT_ANNUITY 的 q0 - q100\n",
    "## 2.1 將 AMT_ANNUITY 中的 NAs 暫時以中位數填補\n",
    "## 2.2 將 AMT_ANNUITY 的數值標準化至 -1 ~ 1 間\n",
    "## 3. 將 AMT_GOOD_PRICE 的 NAs 以眾數填補\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# 1: 計算 AMT_ANNUITY 的 q0 - q100\n",
    "# 看不懂\n",
    "# q_all = \n",
    "\n",
    "# pd.DataFrame({'q': list(range(101)),\n",
    "#              'value': q_all})\n",
    "\n",
    "q_all = [np.percentile(app_train[~app_train['AMT_ANNUITY'].isnull()]['AMT_ANNUITY'], q = i) for i in range(100)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Before replace NAs, numbers of row that AMT_ANNUITY is NAs: 0\n",
      "After replace NAs, numbers of row that AMT_ANNUITY is NAs: 0\n"
     ]
    }
   ],
   "source": [
    "# 2.1 將 NAs 以 q50 填補\n",
    "# print(\"Before replace NAs, numbers of row that AMT_ANNUITY is NAs: %i\" % sum(app_train['AMT_ANNUITY'].isnull()))\n",
    "# 看不懂\n",
    "#q_50 = \n",
    "#app_train.loc[app_train['AMT_ANNUITY'].isnull(),'AMT_ANNUITY'] = q_50\n",
    "\n",
    "#print(\"After replace NAs, numbers of row that AMT_ANNUITY is NAs: %i\" % sum(app_train['AMT_ANNUITY'].isnull()))\n",
    "\n",
    "\n",
    "print(\"Before replace NAs, numbers of row that AMT_ANNUITY is NAs: %i\" % sum(app_train['AMT_ANNUITY'].isnull()))\n",
    "\n",
    "q_50 = np.percentile(app_train[~app_train['AMT_ANNUITY'].isnull()]['AMT_ANNUITY'], 50)\n",
    "app_train.loc[app_train['AMT_ANNUITY'].isnull(),'AMT_ANNUITY'] = q_50\n",
    "\n",
    "print(\"After replace NAs, numbers of row that AMT_ANNUITY is NAs: %i\" % sum(app_train['AMT_ANNUITY'].isnull()))\n",
    "\n",
    "# 原本有出現結果 Before replace NAs, numbers of row that AMT_ANNUITY is NAs: 12"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Hints: Normalize function (to -1 ~ 1)\n",
    "$ y = 2*(\\frac{x - min(x)}{max(x) - min(x)} - 0.5) $"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "== Original data range ==\n",
      "count    307511.000000\n",
      "mean      27108.487841\n",
      "std       14493.461065\n",
      "min        1615.500000\n",
      "25%       16524.000000\n",
      "50%       24903.000000\n",
      "75%       34596.000000\n",
      "max      258025.500000\n",
      "Name: AMT_ANNUITY, dtype: float64\n",
      "== Normalized data range ==\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "count    307511.000000\n",
       "mean         -0.801154\n",
       "std           0.113049\n",
       "min          -1.000000\n",
       "25%          -0.883714\n",
       "50%          -0.818357\n",
       "75%          -0.742752\n",
       "max           1.000000\n",
       "Name: AMT_ANNUITY_NORMALIZED, dtype: float64"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 2.2 Normalize values to -1 to 1\n",
    "print(\"== Original data range ==\")\n",
    "print(app_train['AMT_ANNUITY'].describe())\n",
    "\n",
    "def normalize_value(x):\n",
    "    x = (( (x - min(x)) / ( max(x) - min(x) ) ) - 0.5) * 2    \n",
    "    return x\n",
    "\n",
    "app_train['AMT_ANNUITY_NORMALIZED'] = normalize_value(app_train['AMT_ANNUITY'])\n",
    "\n",
    "print(\"== Normalized data range ==\")\n",
    "app_train['AMT_ANNUITY_NORMALIZED'].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Before replace NAs, numbers of row that AMT_GOODS_PRICE is NAs: 278\n",
      "450000.0    26022\n",
      "225000.0    25282\n",
      "675000.0    24962\n",
      "900000.0    15416\n",
      "270000.0    11428\n",
      "Name: AMT_GOODS_PRICE, dtype: int64\n",
      "After replace NAs, numbers of row that AMT_GOODS_PRICE is NAs: 0\n"
     ]
    }
   ],
   "source": [
    "# 3\n",
    "print(\"Before replace NAs, numbers of row that AMT_GOODS_PRICE is NAs: %i\" % sum(app_train['AMT_GOODS_PRICE'].isnull()))\n",
    "\n",
    "# 列出重複最多的數值\n",
    "print(app_train['AMT_GOODS_PRICE'].value_counts().head())\n",
    "\n",
    "#value_most = \n",
    "#print(value_most)\n",
    "\n",
    "mode_goods_price = list(app_train['AMT_GOODS_PRICE'].value_counts().index)\n",
    "app_train.loc[app_train['AMT_GOODS_PRICE'].isnull(), 'AMT_GOODS_PRICE'] = mode_goods_price[0]\n",
    "\n",
    "print(\"After replace NAs, numbers of row that AMT_GOODS_PRICE is NAs: %i\" % sum(app_train['AMT_GOODS_PRICE'].isnull()))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 2019/09/19 不懂"
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python 3",
   "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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}