preprocessing.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Imports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd # to interact with dataframes \n",
    "from sklearn.preprocessing import LabelEncoder, OrdinalEncoder #for preprocessing\n",
    "import joblib #to save encoders and models \n",
    "import os #to interact with hardware and create folders \n",
    "import warnings\n",
    "warnings.simplefilter(action='ignore')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Constants"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Initialize filepaths \n",
    "LOCALITY_ENCODER_PATH = 'Objects/Encoders/LabelEncoder/'\n",
    "LAYOUT_ENCODER_PATH = 'Objects/Encoders/OrdinalEncoder/layout_type/'\n",
    "PROPERTY_ENCODER_PATH = 'Objects/Encoders/OrdinalEncoder/property_type/'\n",
    "SELLER_ENCODER_PATH = 'Objects/Encoders/OrdinalEncoder/seller_type/'\n",
    "FURNITURE_ENCODER_PATH = 'Objects/Encoders/OrdinalEncoder/furniture_encoders/'\n",
    "CLEAN_SQL_PATH = 'sql_files/clean/'\n",
    "PREPROCESSED_FILE_PATH = 'preprocessed/'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Reading the Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = pd.read_csv('_All_Cities_Cleaned.csv') # read the data "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>seller_type</th>\n",
       "      <th>bedroom</th>\n",
       "      <th>layout_type</th>\n",
       "      <th>property_type</th>\n",
       "      <th>locality</th>\n",
       "      <th>price</th>\n",
       "      <th>area</th>\n",
       "      <th>furnish_type</th>\n",
       "      <th>bathroom</th>\n",
       "      <th>city</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>OWNER</td>\n",
       "      <td>2.0</td>\n",
       "      <td>BHK</td>\n",
       "      <td>Apartment</td>\n",
       "      <td>Bodakdev</td>\n",
       "      <td>20000.0</td>\n",
       "      <td>1450.0</td>\n",
       "      <td>Furnished</td>\n",
       "      <td>2.0</td>\n",
       "      <td>Ahmedabad</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>OWNER</td>\n",
       "      <td>1.0</td>\n",
       "      <td>RK</td>\n",
       "      <td>Studio Apartment</td>\n",
       "      <td>CG Road</td>\n",
       "      <td>7350.0</td>\n",
       "      <td>210.0</td>\n",
       "      <td>Semi-Furnished</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Ahmedabad</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>OWNER</td>\n",
       "      <td>3.0</td>\n",
       "      <td>BHK</td>\n",
       "      <td>Apartment</td>\n",
       "      <td>Jodhpur</td>\n",
       "      <td>22000.0</td>\n",
       "      <td>1900.0</td>\n",
       "      <td>Unfurnished</td>\n",
       "      <td>3.0</td>\n",
       "      <td>Ahmedabad</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>OWNER</td>\n",
       "      <td>2.0</td>\n",
       "      <td>BHK</td>\n",
       "      <td>Independent House</td>\n",
       "      <td>Sanand</td>\n",
       "      <td>13000.0</td>\n",
       "      <td>1285.0</td>\n",
       "      <td>Semi-Furnished</td>\n",
       "      <td>2.0</td>\n",
       "      <td>Ahmedabad</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>OWNER</td>\n",
       "      <td>2.0</td>\n",
       "      <td>BHK</td>\n",
       "      <td>Independent House</td>\n",
       "      <td>Navrangpura</td>\n",
       "      <td>18000.0</td>\n",
       "      <td>1600.0</td>\n",
       "      <td>Furnished</td>\n",
       "      <td>2.0</td>\n",
       "      <td>Ahmedabad</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  seller_type  bedroom layout_type      property_type     locality    price  \\\n",
       "0       OWNER      2.0         BHK          Apartment     Bodakdev  20000.0   \n",
       "1       OWNER      1.0          RK   Studio Apartment      CG Road   7350.0   \n",
       "2       OWNER      3.0         BHK          Apartment      Jodhpur  22000.0   \n",
       "3       OWNER      2.0         BHK  Independent House       Sanand  13000.0   \n",
       "4       OWNER      2.0         BHK  Independent House  Navrangpura  18000.0   \n",
       "\n",
       "     area    furnish_type  bathroom       city  \n",
       "0  1450.0       Furnished       2.0  Ahmedabad  \n",
       "1   210.0  Semi-Furnished       1.0  Ahmedabad  \n",
       "2  1900.0     Unfurnished       3.0  Ahmedabad  \n",
       "3  1285.0  Semi-Furnished       2.0  Ahmedabad  \n",
       "4  1600.0       Furnished       2.0  Ahmedabad  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head() # see first 5 rows of the data "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create a copy of the subsets of the dataframes for each city\n",
    "ahmedabad = data[data['city'] == 'Ahmedabad'].copy()\n",
    "bangalore = data[data['city'] == 'Bangalore'].copy()\n",
    "chennai = data[data['city'] == 'Chennai'].copy()\n",
    "delhi = data[data['city'] == 'Delhi'].copy()\n",
    "hyderabad = data[data['city'] == 'Hyderabad'].copy()\n",
    "kolkata = data[data['city'] == 'Kolkata'].copy()\n",
    "mumbai = data[data['city'] == 'Mumbai'].copy()\n",
    "pune = data[data['city'] == 'Pune'].copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_list = [ahmedabad,bangalore,chennai,delhi,hyderabad,kolkata,mumbai,pune] #store the dataframes in a list\n",
    "cities = ['AHEMDABAD','BANGALORE','CHENNAI','DELHI','HYDERABAD','KOLKATA','MUMBAI','PUNE'] # store the city names in a list\n",
    "df_dict = dict(zip(cities,df_list)) #create a dictionary of dataframes with cities as key"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# for storing encoders while preprocessing \n",
    "locality_encoder_dict = {}\n",
    "furnish_type_encoder_dict = {}\n",
    "seller_type_encoder_dict = {}\n",
    "layout_type_encoder_dict = {}\n",
    "property_type_encoder_dict = {}\n",
    "def preprocess(df_dict):\n",
    "    \"\"\"\n",
    "    Cleans and preprocesses the data for future use \n",
    "    Cleaning:\n",
    "        - Removes duplicate rows (if they exist)\n",
    "        - Removes the city column as the dataframes are already separated \n",
    "        - Removes outliers on the basis of price column using lower limit as (Q1-1.5*IQR) and \n",
    "        upper limit as (Q3+1.5*IQR)\n",
    "    Preprocessing:\n",
    "        - Uses label encoding for locality column as order does not matter in locality\n",
    "        - Uses ordianal encoding for furnish_type, layout_type, seller_type and property_type as the \n",
    "        order matters in these columns for predicting the rent prices\n",
    "    Args:\n",
    "        df_dict - the dictionary containing cities as keys and the dataframe corresponding to them as values \n",
    "    Returns:\n",
    "        None \n",
    "    \"\"\"\n",
    "    for city, df in df_dict.items():\n",
    "        # Cleaning the data \n",
    "        # Dropping the city column\n",
    "        df.drop(['city'], axis=1, inplace=True)\n",
    "        # Dropping duplicate rows (if they exist)\n",
    "        df.drop_duplicates(inplace=True)\n",
    "        # Renaming the columns \n",
    "        cols = ['SELLER TYPE','BEDROOM','LAYOUT TYPE','PROPERTY TYPE','LOCALITY','PRICE','AREA','FURNISH TYPE','BATHROOM']\n",
    "        df.columns = cols\n",
    "        # Removing outliers \n",
    "        desc = df['PRICE'].describe()\n",
    "        q1 = desc.loc['25%']\n",
    "        q3 = desc.loc['75%']\n",
    "        iqr = q3-q1 \n",
    "        lower_lim = q1-(1.5*iqr)\n",
    "        upper_lim = q3+(1.5*iqr)\n",
    "        df = df[(df['PRICE']>=lower_lim)&(df['PRICE']<=upper_lim)]\n",
    "        # Renaming the columns \n",
    "        # since the data is cleaned now, we can store it in clean_dict \n",
    "        clean_df = df.copy()\n",
    "\n",
    "        # write the data to SQL file to insert into the CLEAN database\n",
    "        clean_sql_filepath = os.path.join(CLEAN_SQL_PATH, f'{city}.sql')\n",
    "\n",
    "        with open(clean_sql_filepath, 'w+') as f:    \n",
    "            for idx, row in clean_df.iterrows():\n",
    "                query = f\"\"\"\n",
    "    INSERT INTO {city} (SELLER_TYPE, BEDROOM, LAYOUT, PROPERTY_TYPE, LOCALITY, PRICE, AREA, FURNISH_TYPE, BATHROOM)\n",
    "    VALUES \n",
    "    {tuple(row.values)};\\n\n",
    "                \"\"\"\n",
    "                f.write(query)\n",
    "        # Preprocessing the data \n",
    "        locality_encoder = LabelEncoder()\n",
    "        df['LOCALITY'] = locality_encoder.fit_transform(df['LOCALITY'])\n",
    "        locality_encoder_dict[city] = locality_encoder\n",
    "        if not os.path.exists(LOCALITY_ENCODER_PATH):\n",
    "            joblib.dump(locality_encoder)\n",
    "        \n",
    "        ordinal_encoder_cols = ['SELLER TYPE','LAYOUT TYPE','PROPERTY TYPE','FURNISH TYPE']        \n",
    "        ord_enc_dict = {\n",
    "            'SELLER TYPE':seller_type_encoder_dict,\n",
    "            'LAYOUT TYPE':layout_type_encoder_dict,\n",
    "            'PROPERTY TYPE':property_type_encoder_dict,\n",
    "            'FURNISH TYPE':furnish_type_encoder_dict\n",
    "        }\n",
    "        for col in ordinal_encoder_cols:\n",
    "            cat = [df.groupby(by=[col])['PRICE'].mean().sort_values(ascending=True).index]\n",
    "            col_encoder = OrdinalEncoder(categories=cat)\n",
    "            df[col] = col_encoder.fit_transform(df[[col]])\n",
    "            ord_enc_dict[col][city] = col_encoder\n",
    "        paths = {\n",
    "            'SELLER TYPE': os.path.join(SELLER_ENCODER_PATH, f'{city}_seller_type_encoder.pkl'),\n",
    "            'LAYOUT TYPE': os.path.join(LAYOUT_ENCODER_PATH,f'{city}_layout_type_encoder.pkl'),\n",
    "            'PROPERTY TYPE': os.path.join(PROPERTY_ENCODER_PATH,f'{city}_property_type_encoder.pkl'),\n",
    "            'FURNISH TYPE': os.path.join(FURNITURE_ENCODER_PATH, f'{city}_furnish_type_encoder.pkl')\n",
    "        }\n",
    "\n",
    "        if not os.path.exists(FURNITURE_ENCODER_PATH): #check if the desired file path exists\n",
    "            os.makedirs(FURNITURE_ENCODER_PATH) #if not then make one \n",
    "\n",
    "        if not os.path.exists(SELLER_ENCODER_PATH):\n",
    "            os.makedirs(SELLER_ENCODER_PATH)\n",
    "        \n",
    "        if not os.path.exists(LAYOUT_ENCODER_PATH):\n",
    "            os.makedirs(LAYOUT_ENCODER_PATH)\n",
    "        \n",
    "        if not os.path.exists(PROPERTY_ENCODER_PATH):\n",
    "            os.makedirs(PROPERTY_ENCODER_PATH)\n",
    "     \n",
    "        for col in ordinal_encoder_cols:\n",
    "            joblib.dump(ord_enc_dict[col][city], paths[col])\n",
    "\n",
    "        # Saving the dataframes \n",
    "        if not os.path.exists(PREPROCESSED_FILE_PATH):\n",
    "            os.makedirs(PREPROCESSED_FILE_PATH)\n",
    "        df.to_csv(os.path.join(PREPROCESSED_FILE_PATH, f'{city}.csv'),index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "preprocess(df_dict) # call the preprocess function "
   ]
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "440417352d35e351716406bf78418b8afcf69d67c180a9408a2a69feaabae790"
  },
  "kernelspec": {
   "display_name": "Python 3.9.6 64-bit ('venv': venv)",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.10"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}