feature_engineering_v6.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt\n",
    "import warnings\n",
    "import numpy as np\n",
    "from scipy.special import boxcox1p\n",
    "\n",
    "# processing\n",
    "from sklearn import preprocessing\n",
    "from sklearn.preprocessing import StandardScaler\n",
    "from sklearn.decomposition import PCA\n",
    "from sklearn.feature_selection import VarianceThreshold\n",
    "from sklearn.preprocessing import MinMaxScaler\n",
    "from sklearn.model_selection import cross_val_score\n",
    "\n",
    "# model\n",
    "from sklearn.linear_model import LinearRegression\n",
    "from sklearn import svm\n",
    "from sklearn.metrics import mean_squared_error\n",
    "\n",
    "from scipy import stats\n",
    "from scipy.stats import norm, skew\n",
    "\n",
    "warnings.filterwarnings(\n",
    "    action=\"ignore\",\n",
    "    module=\"scipy\",\n",
    "    message=\"^.*LAPACK bug 0038.*\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1460, 81)\n",
      "(1460,)\n",
      "(1459, 81)\n"
     ]
    }
   ],
   "source": [
    "# load training data split train_x and train_y\n",
    "train_pd = pd.read_csv(\"all/train.csv\")\n",
    "\n",
    "# remove outlier from data analysis\n",
    "# drop outliers\n",
    "# query = train_pd[(train_pd['GrLivArea'] > 4000) & (train_pd['SalePrice'] < 200000)]\n",
    "# train_pd = train_pd.drop(query.index.values)\n",
    "\n",
    "train_x = train_pd.drop(['SalePrice'], axis=1)\n",
    "train_x['is_train'] = 1\n",
    "# train_y = train_pd['SalePrice']\n",
    "#log transform the target:\n",
    "train_y = np.log1p(train_pd[\"SalePrice\"])\n",
    "# print(train_y)\n",
    "# print(np.log1p(train_y[\"SalePrice\"]))\n",
    "test_pd = pd.read_csv(\"all/test.csv\")\n",
    "test_pd['is_train'] = 0\n",
    "print(train_x.shape)\n",
    "print(train_y.shape)\n",
    "print(test_pd.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(2919, 81)\n"
     ]
    }
   ],
   "source": [
    "# concat train and test\n",
    "\n",
    "all_data = pd.concat((train_x, test_pd)).reset_index(drop=True)\n",
    "print(all_data.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(2919, 80)\n"
     ]
    }
   ],
   "source": [
    "# remove ID\n",
    "all_data = all_data.drop(['Id'], axis=1)\n",
    "print(all_data.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "      column_name  percentage\n",
      "0      MSSubClass    0.000000\n",
      "1        MSZoning    0.137033\n",
      "2     LotFrontage   16.649538\n",
      "3         LotArea    0.000000\n",
      "4          Street    0.000000\n",
      "..            ...         ...\n",
      "75         MoSold    0.000000\n",
      "76         YrSold    0.000000\n",
      "77       SaleType    0.034258\n",
      "78  SaleCondition    0.000000\n",
      "79       is_train    0.000000\n",
      "\n",
      "[80 rows x 2 columns]\n",
      "    column_name  percentage\n",
      "5         Alley   93.216855\n",
      "71       PoolQC   99.657417\n",
      "72        Fence   80.438506\n",
      "73  MiscFeature   96.402878\n"
     ]
    }
   ],
   "source": [
    "# nan data\n",
    "NAN = [(col, all_data[col].isna().mean()*100) for col in all_data]\n",
    "NAN = pd.DataFrame(NAN, columns=[\"column_name\", \"percentage\"])\n",
    "print(NAN)\n",
    "\n",
    "NAN = NAN[NAN.percentage > 50]\n",
    "NAN.sort_values(\"percentage\", ascending=False)\n",
    "print(NAN)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(2919, 76)\n"
     ]
    }
   ],
   "source": [
    "# There are 4 features has more than 80 percent nan value\n",
    "# drop these columns\n",
    "all_data = all_data.drop(['Alley', 'PoolQC', 'Fence','MiscFeature'], axis=1)\n",
    "print(all_data.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "MSSubClass         0\n",
      "LotFrontage      486\n",
      "LotArea            0\n",
      "OverallQual        0\n",
      "OverallCond        0\n",
      "YearBuilt          0\n",
      "YearRemodAdd       0\n",
      "MasVnrArea        23\n",
      "BsmtFinSF1         1\n",
      "BsmtFinSF2         1\n",
      "BsmtUnfSF          1\n",
      "TotalBsmtSF        1\n",
      "1stFlrSF           0\n",
      "2ndFlrSF           0\n",
      "LowQualFinSF       0\n",
      "GrLivArea          0\n",
      "BsmtFullBath       2\n",
      "BsmtHalfBath       2\n",
      "FullBath           0\n",
      "HalfBath           0\n",
      "BedroomAbvGr       0\n",
      "KitchenAbvGr       0\n",
      "TotRmsAbvGrd       0\n",
      "Fireplaces         0\n",
      "GarageYrBlt      159\n",
      "GarageCars         1\n",
      "GarageArea         1\n",
      "WoodDeckSF         0\n",
      "OpenPorchSF        0\n",
      "EnclosedPorch      0\n",
      "3SsnPorch          0\n",
      "ScreenPorch        0\n",
      "PoolArea           0\n",
      "MiscVal            0\n",
      "MoSold             0\n",
      "YrSold             0\n",
      "is_train           0\n",
      "dtype: int64\n",
      "MSZoning            4\n",
      "Street              0\n",
      "LotShape            0\n",
      "LandContour         0\n",
      "Utilities           2\n",
      "LotConfig           0\n",
      "LandSlope           0\n",
      "Neighborhood        0\n",
      "Condition1          0\n",
      "Condition2          0\n",
      "BldgType            0\n",
      "HouseStyle          0\n",
      "RoofStyle           0\n",
      "RoofMatl            0\n",
      "Exterior1st         1\n",
      "Exterior2nd         1\n",
      "MasVnrType         24\n",
      "ExterQual           0\n",
      "ExterCond           0\n",
      "Foundation          0\n",
      "BsmtQual           81\n",
      "BsmtCond           82\n",
      "BsmtExposure       82\n",
      "BsmtFinType1       79\n",
      "BsmtFinType2       80\n",
      "Heating             0\n",
      "HeatingQC           0\n",
      "CentralAir          0\n",
      "Electrical          1\n",
      "KitchenQual         1\n",
      "Functional          2\n",
      "FireplaceQu      1420\n",
      "GarageType        157\n",
      "GarageFinish      159\n",
      "GarageQual        159\n",
      "GarageCond        159\n",
      "PavedDrive          0\n",
      "SaleType            1\n",
      "SaleCondition       0\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# split features into object_columns and numerical_columns\n",
    "object_columns_df = all_data.select_dtypes(include=['object'])\n",
    "numerical_columns_df = all_data.select_dtypes(exclude=['object'])\n",
    "print(numerical_columns_df.isna().sum())\n",
    "print(object_columns_df.isna().sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0\n"
     ]
    }
   ],
   "source": [
    "# fill lotfrontage with mean value corespond to overall quality\n",
    "lot_not_nan = numerical_columns_df[numerical_columns_df['LotFrontage'].notna()]\n",
    "lot_mean_map = dict(lot_not_nan.groupby('OverallQual').mean()['LotFrontage'])\n",
    "all_data['LotFrontage'] = all_data['LotFrontage'].fillna(all_data['OverallQual'].map(lot_mean_map))\n",
    "print(all_data['LotFrontage'].isna().sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0\n"
     ]
    }
   ],
   "source": [
    "# MasVnrArea nan means no Masonry veneer area\n",
    "# just fill with 0\n",
    "all_data['MasVnrArea'] = all_data['MasVnrArea'].fillna(0)\n",
    "print(all_data['MasVnrArea'].isna().sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0 0 0 0\n"
     ]
    }
   ],
   "source": [
    "# BsmtFinSF1, BsmtFinSF2, BsmtUnfSF, TotalBsmtSF with 1 nan only. fill with 0\n",
    "all_data['BsmtFinSF1'] = all_data['BsmtFinSF1'].fillna(0)\n",
    "all_data['BsmtFinSF2'] = all_data['BsmtFinSF2'].fillna(0)\n",
    "all_data['BsmtUnfSF'] = all_data['BsmtUnfSF'].fillna(0)\n",
    "all_data['TotalBsmtSF'] = all_data['TotalBsmtSF'].fillna(0)\n",
    "print(all_data['BsmtFinSF1'].isna().sum(), all_data['BsmtFinSF2'].isna().sum(),\n",
    "     all_data['BsmtUnfSF'].isna().sum(), all_data['TotalBsmtSF'].isna().sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0 0\n"
     ]
    }
   ],
   "source": [
    "# BsmtFullBath, BsmtHalfBath means basement bathrooms, value can be 0\n",
    "all_data['BsmtFullBath'] = all_data['BsmtFullBath'].fillna(0)\n",
    "all_data['BsmtHalfBath'] = all_data['BsmtHalfBath'].fillna(0)\n",
    "print(all_data['BsmtFullBath'].isna().sum(), all_data['BsmtHalfBath'].isna().sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0\n"
     ]
    }
   ],
   "source": [
    "# fill GarageYrBlt with average yearbuilt+3\n",
    "all_data['GarageYrBlt'] = all_data['GarageYrBlt'].fillna(all_data['YearBuilt'] + 3)\n",
    "print(all_data['GarageYrBlt'].isna().sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0 0\n"
     ]
    }
   ],
   "source": [
    "# GarageCars, GarageArea can be 0, fill with 0\n",
    "all_data['GarageCars'] = all_data['GarageCars'].fillna(0)\n",
    "all_data['GarageArea'] = all_data['GarageArea'].fillna(0)\n",
    "print(all_data['GarageCars'].isna().sum(), all_data['GarageArea'].isna().sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0\n"
     ]
    }
   ],
   "source": [
    "# MasVnrType has None value, fill with None\n",
    "all_data['MasVnrType'] = all_data['MasVnrType'].fillna('None')\n",
    "print(all_data['MasVnrType'].isna().sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "BsmtQual        0\n",
      "BsmtCond        0\n",
      "BsmtExposure    0\n",
      "BsmtFinType1    0\n",
      "BsmtFinType2    0\n",
      "GarageType      0\n",
      "GarageFinish    0\n",
      "GarageQual      0\n",
      "GarageCond      0\n",
      "FireplaceQu     0\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# BsmtQual BsmtCond BsmtExposure BsmtFinType1 BsmtFinType2 GarageType GarageFinish GarageQual GarageCond FireplaceQu have NA value, fill with NA\n",
    "fill_na_col = ['BsmtQual', 'BsmtCond', 'BsmtExposure', 'BsmtFinType1', 'BsmtFinType2', 'GarageType', 'GarageFinish', 'GarageQual', 'GarageCond', 'FireplaceQu']\n",
    "all_data[fill_na_col] = all_data[fill_na_col].fillna('NA')\n",
    "print(all_data[fill_na_col].isna().sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "MSZoning       0\n",
      "Utilities      0\n",
      "Exterior1st    0\n",
      "Exterior2nd    0\n",
      "Electrical     0\n",
      "KitchenQual    0\n",
      "Functional     0\n",
      "SaleType       0\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# fill remain nan feature with common value\n",
    "unfilled = all_data.columns[all_data.isna().any()].tolist()\n",
    "all_data[unfilled] = all_data[unfilled].fillna(all_data[unfilled].mode().iloc[0])\n",
    "print(all_data[unfilled].isna().sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(2919, 76)\n"
     ]
    }
   ],
   "source": [
    "print(all_data.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(2919, 76)\n"
     ]
    }
   ],
   "source": [
    "# transform some feature to numerical\n",
    "level_map  = {\n",
    "    'Ex':5,\n",
    "    'Gd':4,\n",
    "    'TA':3,\n",
    "    'Fa':2,\n",
    "    'Po':1,\n",
    "    'NA':0,\n",
    "    'Y':1,\n",
    "    'N':0,\n",
    "    'Reg':3,\n",
    "    'IR1':2,\n",
    "    'IR2':1,\n",
    "    'IR3':0,\n",
    "    \"Unf\" : 1,\n",
    "    \"LwQ\": 2,\n",
    "    \"Rec\" : 3,\n",
    "    \"BLQ\" : 4,\n",
    "    \"ALQ\" : 5,\n",
    "    \"GLQ\" : 6\n",
    "}\n",
    "# assign value \n",
    "all_data['LotShape'] = all_data['LotShape'].map(level_map)\n",
    "all_data['ExterQual'] = all_data['ExterQual'].map(level_map)\n",
    "all_data['ExterCond'] = all_data['ExterCond'].map(level_map)\n",
    "all_data['BsmtQual'] = all_data['BsmtQual'].map(level_map)\n",
    "all_data['BsmtCond'] = all_data['BsmtCond'].map(level_map)\n",
    "all_data['BsmtFinType1'] = all_data['BsmtFinType1'].map(level_map)\n",
    "all_data['BsmtFinType2'] = all_data['BsmtFinType2'].map(level_map)\n",
    "all_data['HeatingQC'] = all_data['HeatingQC'].map(level_map)\n",
    "all_data['CentralAir'] = all_data['CentralAir'].map(level_map)\n",
    "all_data['KitchenQual'] = all_data['KitchenQual'].map(level_map)\n",
    "all_data['FireplaceQu'] = all_data['FireplaceQu'].map(level_map)\n",
    "all_data['GarageQual'] = all_data['GarageQual'].map(level_map)\n",
    "all_data['GarageCond'] = all_data['GarageCond'].map(level_map)\n",
    "\n",
    "print(all_data.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # transfer skewed features\n",
    "# numerical_data = all_data.select_dtypes(exclude=['object'])\n",
    "# skewValue = numerical_data.skew().sort_values(ascending=False)\n",
    "# large_skew = skewValue[abs(skewValue) > 0.75]\n",
    "# lamda = 0.15\n",
    "# for feature in large_skew.index:\n",
    "#     all_data[feature] = boxcox1p(all_data[feature], lamda)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(2919, 77)\n"
     ]
    }
   ],
   "source": [
    "# build house age feature\n",
    "house_age = all_data['YrSold'] - all_data['YearBuilt']\n",
    "all_data['house_age'] = house_age\n",
    "print(all_data.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(2919, 234)\n"
     ]
    }
   ],
   "source": [
    "# one_hot other features\n",
    "all_data = pd.get_dummies(all_data)\n",
    "print(all_data.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(2919, 246)\n"
     ]
    }
   ],
   "source": [
    "all_data['YrBltAndRemod']=all_data['YearBuilt']+all_data['YearRemodAdd']\n",
    "all_data['TotalSF']=all_data['TotalBsmtSF'] + all_data['1stFlrSF'] + all_data['2ndFlrSF']\n",
    "\n",
    "all_data['Total_sqr_footage'] = (all_data['BsmtFinSF1'] + all_data['BsmtFinSF2'] +\n",
    "                                 all_data['1stFlrSF'] + all_data['2ndFlrSF'])\n",
    "\n",
    "all_data['Total_Bathrooms'] = (all_data['FullBath'] + (0.5 * all_data['HalfBath']) +\n",
    "                               all_data['BsmtFullBath'] + (0.5 * all_data['BsmtHalfBath']))\n",
    "\n",
    "all_data['Total_porch_sf'] = (all_data['OpenPorchSF'] + all_data['3SsnPorch'] +\n",
    "                              all_data['EnclosedPorch'] + all_data['ScreenPorch'] +\n",
    "                              all_data['WoodDeckSF'])\n",
    "\n",
    "# simplified features\n",
    "all_data['haspool'] = all_data['PoolArea'].apply(lambda x: 1 if x > 0 else 0)\n",
    "all_data['has2ndfloor'] = all_data['2ndFlrSF'].apply(lambda x: 1 if x > 0 else 0)\n",
    "all_data['hasgarage'] = all_data['GarageArea'].apply(lambda x: 1 if x > 0 else 0)\n",
    "all_data['hasbsmt'] = all_data['TotalBsmtSF'].apply(lambda x: 1 if x > 0 else 0)\n",
    "all_data['hasfireplace'] = all_data['Fireplaces'].apply(lambda x: 1 if x > 0 else 0)\n",
    "all_data['qual_square'] = all_data['OverallQual'].pow(2)\n",
    "all_data['qual_cube'] = all_data['OverallQual'].pow(3)\n",
    "\n",
    "print(all_data.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1460, 245)\n",
      "(1459, 245)\n"
     ]
    }
   ],
   "source": [
    "train_x = all_data[all_data['is_train'] == 1]\n",
    "test_x = all_data[all_data['is_train'] == 0]\n",
    "train_x = train_x.drop(['is_train'], axis=1)\n",
    "test_x = test_x.drop(['is_train'], axis=1)\n",
    "print(train_x.shape)\n",
    "print(test_x.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "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.6.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}