test_dataprep.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "focal-emerald",
   "metadata": {},
   "source": [
    "### Disclaimer : Please refer `problem_1.ipynb` before this.\n",
    "\n",
    "This NoteBook purpose is just to clean the **testdata** which is further on used in `problem_1.ipynb`"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "associate-armor",
   "metadata": {},
   "source": [
    "## 1. Importing required packages"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "third-oasis",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "sonic-lingerie",
   "metadata": {},
   "source": [
    "## 2. Data Exploration\n",
    "\n",
    "Data can be found in `Data/` Folder. \n",
    "The ones we are going to use:\n",
    "\n",
    "\n",
    "Once we have imported our data, we are going to explore and get to know about the different Attributes and their patterns with the data.\n",
    "\n",
    "Data Exploration is one of most important tasks in Machine Learning."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "inside-indie",
   "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>Stock Index</th>\n",
       "      <th>Index</th>\n",
       "      <th>Industry</th>\n",
       "      <th>VWAP</th>\n",
       "      <th>General Index</th>\n",
       "      <th>NAV</th>\n",
       "      <th>P/E Ratio</th>\n",
       "      <th>Volumes Traded</th>\n",
       "      <th>Inventory Turnover</th>\n",
       "      <th>Covid Impact (Beta)</th>\n",
       "      <th>Tracking Error</th>\n",
       "      <th>Dollar Exchange Rate</th>\n",
       "      <th>Put-Call Ratio</th>\n",
       "      <th>P/B Ratio</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AC3235</td>\n",
       "      <td>NSE</td>\n",
       "      <td>Materials</td>\n",
       "      <td>53.30</td>\n",
       "      <td>11270.15</td>\n",
       "      <td>44.59</td>\n",
       "      <td>185.09</td>\n",
       "      <td>12067855.0</td>\n",
       "      <td>2.78</td>\n",
       "      <td>0.03</td>\n",
       "      <td>0.0330</td>\n",
       "      <td>74.9</td>\n",
       "      <td>0.80</td>\n",
       "      <td>4.66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AC3236</td>\n",
       "      <td>JSE</td>\n",
       "      <td>Energy</td>\n",
       "      <td>749.34</td>\n",
       "      <td>55722.00</td>\n",
       "      <td>74.20</td>\n",
       "      <td>34.01</td>\n",
       "      <td>6172474.0</td>\n",
       "      <td>6.78</td>\n",
       "      <td>0.11</td>\n",
       "      <td>0.0464</td>\n",
       "      <td>17.7</td>\n",
       "      <td>0.86</td>\n",
       "      <td>6.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AC3237</td>\n",
       "      <td>S&amp;P 500</td>\n",
       "      <td>Information Tech</td>\n",
       "      <td>567.75</td>\n",
       "      <td>3351.28</td>\n",
       "      <td>88.41</td>\n",
       "      <td>177.40</td>\n",
       "      <td>17472488.0</td>\n",
       "      <td>4.36</td>\n",
       "      <td>0.23</td>\n",
       "      <td>0.0401</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.93</td>\n",
       "      <td>4.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AC3238</td>\n",
       "      <td>NSE</td>\n",
       "      <td>Healthcare</td>\n",
       "      <td>646.78</td>\n",
       "      <td>11270.15</td>\n",
       "      <td>79.36</td>\n",
       "      <td>105.44</td>\n",
       "      <td>15553159.0</td>\n",
       "      <td>2.77</td>\n",
       "      <td>0.78</td>\n",
       "      <td>0.0187</td>\n",
       "      <td>74.9</td>\n",
       "      <td>1.09</td>\n",
       "      <td>1.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AC3239</td>\n",
       "      <td>NYSE</td>\n",
       "      <td>Materials</td>\n",
       "      <td>380.33</td>\n",
       "      <td>12765.84</td>\n",
       "      <td>19.90</td>\n",
       "      <td>139.40</td>\n",
       "      <td>12525784.0</td>\n",
       "      <td>5.93</td>\n",
       "      <td>0.03</td>\n",
       "      <td>0.0261</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.18</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Stock Index    Index          Industry    VWAP  General Index    NAV  \\\n",
       "0      AC3235      NSE         Materials   53.30       11270.15  44.59   \n",
       "1      AC3236      JSE            Energy  749.34       55722.00  74.20   \n",
       "2      AC3237  S&P 500  Information Tech  567.75        3351.28  88.41   \n",
       "3      AC3238      NSE        Healthcare  646.78       11270.15  79.36   \n",
       "4      AC3239     NYSE         Materials  380.33       12765.84  19.90   \n",
       "\n",
       "   P/E Ratio  Volumes Traded  Inventory Turnover  Covid Impact (Beta)  \\\n",
       "0     185.09      12067855.0                2.78                 0.03   \n",
       "1      34.01       6172474.0                6.78                 0.11   \n",
       "2     177.40      17472488.0                4.36                 0.23   \n",
       "3     105.44      15553159.0                2.77                 0.78   \n",
       "4     139.40      12525784.0                5.93                 0.03   \n",
       "\n",
       "   Tracking Error  Dollar Exchange Rate  Put-Call Ratio  P/B Ratio  \n",
       "0          0.0330                  74.9            0.80       4.66  \n",
       "1          0.0464                  17.7            0.86       6.11  \n",
       "2          0.0401                   1.0            0.93       4.99  \n",
       "3          0.0187                  74.9            1.09       1.26  \n",
       "4          0.0261                   1.0             NaN       6.18  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_data = pd.read_csv(\"data/Test_dataset - Test_Aug10.csv\")\n",
    "test_data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "directed-ranch",
   "metadata": {},
   "source": [
    "### 2.A Filling Missing Values\n",
    "By exploring the data we can observe that there are patterns between a few columns. These patterns could be used to our advantage to fill the missing values."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "tropical-record",
   "metadata": {},
   "source": [
    "**Pattern 1 : Index vs. Dollar Exchange Rate**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "existing-polls",
   "metadata": {},
   "outputs": [],
   "source": [
    "test_data.loc[test_data[\"Index\"]==\"NSE\", [\"Dollar Exchange Rate\"]] = 74.9\n",
    "test_data.loc[test_data[\"Index\"]==\"NYSE\", [\"Dollar Exchange Rate\"]] = 1.0\n",
    "test_data.loc[test_data[\"Index\"]==\"BSE\", [\"Dollar Exchange Rate\"]] = 74.9\n",
    "test_data.loc[test_data[\"Index\"]==\"JSE\", [\"Dollar Exchange Rate\"]] = 17.7\n",
    "test_data.loc[test_data[\"Index\"]==\"S&P 500\", [\"Dollar Exchange Rate\"]] = 1.0"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "binary-webmaster",
   "metadata": {},
   "source": [
    "**Pattern 2 : Industry vs. Covid Impact (Beta)**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "hearing-terrain",
   "metadata": {},
   "outputs": [],
   "source": [
    "test_data.loc[test_data[\"Industry\"]==\"Real Estate\", [\"Covid Impact (Beta)\"]] = -0.43\n",
    "test_data.loc[test_data[\"Industry\"]==\"Energy\", [\"Covid Impact (Beta)\"]] = 0.11\n",
    "test_data.loc[test_data[\"Industry\"]==\"Healthcare\", [\"Covid Impact (Beta)\"]] = 0.78\n",
    "test_data.loc[test_data[\"Industry\"]==\"Materials\", [\"Covid Impact (Beta)\"]] = 0.03\n",
    "test_data.loc[test_data[\"Industry\"]==\"Information Tech\", [\"Covid Impact (Beta)\"]] = 0.23"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "engaged-greenhouse",
   "metadata": {},
   "source": [
    "**Pattern 3 : Index vs. General Index**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "flexible-particle",
   "metadata": {},
   "outputs": [],
   "source": [
    "test_data.loc[test_data[\"Index\"]==\"NSE\", [\"General Index\"]] = 11270.15\n",
    "test_data.loc[test_data[\"Index\"]==\"NYSE\", [\"General Index\"]] = 12765.84\n",
    "test_data.loc[test_data[\"Index\"]==\"BSE\", [\"General Index\"]] = 38182.08\n",
    "test_data.loc[test_data[\"Index\"]==\"JSE\", [\"General Index\"]] = 55722.00\n",
    "test_data.loc[test_data[\"Index\"]==\"S&P 500\", [\"General Index\"]] = 1461"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "incredible-argentina",
   "metadata": {},
   "source": [
    "#### Filling the remaining columns by respective means"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "impressive-syndrome",
   "metadata": {},
   "outputs": [],
   "source": [
    "test_data[\"Volumes Traded\"].fillna(test_data[\"Volumes Traded\"].mean(),inplace = True)\n",
    "test_data[\"P/E Ratio\"].fillna(test_data[\"P/E Ratio\"].mean(),inplace = True)\n",
    "test_data[\"Inventory Turnover\"].fillna(test_data[\"Inventory Turnover\"].mean(),inplace = True)\n",
    "test_data[\"Tracking Error\"].fillna(test_data[\"Tracking Error\"].mean(),inplace = True)\n",
    "test_data[\"Put-Call Ratio\"].fillna(test_data[\"Put-Call Ratio\"].mean(),inplace = True)\n",
    "test_data[\"VWAP\"].fillna(test_data[\"VWAP\"].mean(),inplace = True)\n",
    "test_data[\"NAV\"].fillna(test_data[\"NAV\"].mean(),inplace = True)\n",
    "test_data[\"P/B Ratio\"].fillna(test_data[\"P/B Ratio\"].mean(),inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "organic-williams",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Stock Index             0\n",
       "Index                   0\n",
       "Industry                0\n",
       "VWAP                    0\n",
       "General Index           0\n",
       "NAV                     0\n",
       "P/E Ratio               0\n",
       "Volumes Traded          0\n",
       "Inventory Turnover      0\n",
       "Covid Impact (Beta)     0\n",
       "Tracking Error          0\n",
       "Dollar Exchange Rate    0\n",
       "Put-Call Ratio          0\n",
       "P/B Ratio               0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_data.isna().sum() #All the Missing Data is Filled"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "handled-significance",
   "metadata": {},
   "source": [
    "### LabelEncoding the Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "traditional-tooth",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.preprocessing import LabelEncoder\n",
    "le =LabelEncoder()\n",
    "label = le.fit_transform(test_data[\"Industry\"]) #Label encoded the Industry column\n",
    "label1 = le.fit_transform(test_data[\"Index\"])   #Label encoded the Index column\n",
    "\n",
    "test_data.drop(\"Industry\",axis=1,inplace=True)\n",
    "test_data[\"Industry\"] = label\n",
    "#Dropped the Previous column and added new encoded column for Industry.\n",
    "\n",
    "test_data.drop(\"Index\",axis=1,inplace=True)\n",
    "test_data[\"Index\"] = label1\n",
    "#Dropped the Previous column and added new encoded column for Index.\n",
    "\n",
    "\n",
    "test_data.index=test_data[\"Stock Index\"]\n",
    "#The Stock Index is set as default index as it will be used later.\n",
    "\n",
    "test_data.drop(\"Stock Index\",axis=1,inplace =True)\n",
    "#The Stock Index is removed now from the Dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "right-latex",
   "metadata": {
    "scrolled": true
   },
   "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>VWAP</th>\n",
       "      <th>General Index</th>\n",
       "      <th>NAV</th>\n",
       "      <th>P/E Ratio</th>\n",
       "      <th>Volumes Traded</th>\n",
       "      <th>Inventory Turnover</th>\n",
       "      <th>Covid Impact (Beta)</th>\n",
       "      <th>Tracking Error</th>\n",
       "      <th>Dollar Exchange Rate</th>\n",
       "      <th>Put-Call Ratio</th>\n",
       "      <th>P/B Ratio</th>\n",
       "      <th>Industry</th>\n",
       "      <th>Index</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Stock Index</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></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>AC3235</th>\n",
       "      <td>53.30</td>\n",
       "      <td>11270.15</td>\n",
       "      <td>44.59</td>\n",
       "      <td>185.09</td>\n",
       "      <td>12067855.0</td>\n",
       "      <td>2.78</td>\n",
       "      <td>0.03</td>\n",
       "      <td>0.0330</td>\n",
       "      <td>74.9</td>\n",
       "      <td>0.800000</td>\n",
       "      <td>4.66</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AC3236</th>\n",
       "      <td>749.34</td>\n",
       "      <td>55722.00</td>\n",
       "      <td>74.20</td>\n",
       "      <td>34.01</td>\n",
       "      <td>6172474.0</td>\n",
       "      <td>6.78</td>\n",
       "      <td>0.11</td>\n",
       "      <td>0.0464</td>\n",
       "      <td>17.7</td>\n",
       "      <td>0.860000</td>\n",
       "      <td>6.11</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AC3237</th>\n",
       "      <td>567.75</td>\n",
       "      <td>1461.00</td>\n",
       "      <td>88.41</td>\n",
       "      <td>177.40</td>\n",
       "      <td>17472488.0</td>\n",
       "      <td>4.36</td>\n",
       "      <td>0.23</td>\n",
       "      <td>0.0401</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.930000</td>\n",
       "      <td>4.99</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AC3238</th>\n",
       "      <td>646.78</td>\n",
       "      <td>11270.15</td>\n",
       "      <td>79.36</td>\n",
       "      <td>105.44</td>\n",
       "      <td>15553159.0</td>\n",
       "      <td>2.77</td>\n",
       "      <td>0.78</td>\n",
       "      <td>0.0187</td>\n",
       "      <td>74.9</td>\n",
       "      <td>1.090000</td>\n",
       "      <td>1.26</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AC3239</th>\n",
       "      <td>380.33</td>\n",
       "      <td>12765.84</td>\n",
       "      <td>19.90</td>\n",
       "      <td>139.40</td>\n",
       "      <td>12525784.0</td>\n",
       "      <td>5.93</td>\n",
       "      <td>0.03</td>\n",
       "      <td>0.0261</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.938828</td>\n",
       "      <td>6.18</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               VWAP  General Index    NAV  P/E Ratio  Volumes Traded  \\\n",
       "Stock Index                                                            \n",
       "AC3235        53.30       11270.15  44.59     185.09      12067855.0   \n",
       "AC3236       749.34       55722.00  74.20      34.01       6172474.0   \n",
       "AC3237       567.75        1461.00  88.41     177.40      17472488.0   \n",
       "AC3238       646.78       11270.15  79.36     105.44      15553159.0   \n",
       "AC3239       380.33       12765.84  19.90     139.40      12525784.0   \n",
       "\n",
       "             Inventory Turnover  Covid Impact (Beta)  Tracking Error  \\\n",
       "Stock Index                                                            \n",
       "AC3235                     2.78                 0.03          0.0330   \n",
       "AC3236                     6.78                 0.11          0.0464   \n",
       "AC3237                     4.36                 0.23          0.0401   \n",
       "AC3238                     2.77                 0.78          0.0187   \n",
       "AC3239                     5.93                 0.03          0.0261   \n",
       "\n",
       "             Dollar Exchange Rate  Put-Call Ratio  P/B Ratio  Industry  Index  \n",
       "Stock Index                                                                    \n",
       "AC3235                       74.9        0.800000       4.66         3      2  \n",
       "AC3236                       17.7        0.860000       6.11         0      1  \n",
       "AC3237                        1.0        0.930000       4.99         2      4  \n",
       "AC3238                       74.9        1.090000       1.26         1      2  \n",
       "AC3239                        1.0        0.938828       6.18         3      3  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "employed-estate",
   "metadata": {},
   "source": [
    "### Finally importing the cleaned test data which can be used in `problem_1.ipynb`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "prostate-childhood",
   "metadata": {},
   "outputs": [],
   "source": [
    "test_data.to_csv(\"data/final_test.csv\",index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "coastal-billy",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "whitehatjrs",
   "language": "python",
   "name": "whitehatjrs"
  },
  "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": 5
}