Module 1- Pandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Module 1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Query 1.1 \n",
    "Import the csv file of the stock of your choosing using 'pd.read_csv()' function into a dataframe.\n",
    "Shares of a company can be offered in more than one category. The category of a stock is indicated in the ‘Series’ column. If the csv file has data on more than one category, the ‘Date’ column will have repeating values. To avoid repetitions in the date, remove all the rows where 'Series' column is NOT 'EQ'.\n",
    "Analyze and understand each column properly.\n",
    "You'd find the head(), tail() and describe() functions to be immensely useful for exploration. You're free to carry out any other exploration of your own."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np \n",
    "import pandas as pd\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = pd.read_csv(r\"C:\\Users\\Dell\\MINDAIND.csv\")"
   ]
  },
  {
   "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>Symbol</th>\n",
       "      <th>Series</th>\n",
       "      <th>Date</th>\n",
       "      <th>Prev Close</th>\n",
       "      <th>Open Price</th>\n",
       "      <th>High Price</th>\n",
       "      <th>Low Price</th>\n",
       "      <th>Last Price</th>\n",
       "      <th>Close Price</th>\n",
       "      <th>Average Price</th>\n",
       "      <th>Total Traded Quantity</th>\n",
       "      <th>Turnover</th>\n",
       "      <th>No. of Trades</th>\n",
       "      <th>Deliverable Qty</th>\n",
       "      <th>% Dly Qt to Traded Qty</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>MINDAIND</td>\n",
       "      <td>EQ</td>\n",
       "      <td>15-May-2017</td>\n",
       "      <td>495.65</td>\n",
       "      <td>495.95</td>\n",
       "      <td>522.00</td>\n",
       "      <td>495.90</td>\n",
       "      <td>522.00</td>\n",
       "      <td>517.90</td>\n",
       "      <td>514.90</td>\n",
       "      <td>110264</td>\n",
       "      <td>5.677483e+07</td>\n",
       "      <td>3648</td>\n",
       "      <td>68819</td>\n",
       "      <td>62.41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>MINDAIND</td>\n",
       "      <td>EQ</td>\n",
       "      <td>16-May-2017</td>\n",
       "      <td>517.90</td>\n",
       "      <td>523.70</td>\n",
       "      <td>568.00</td>\n",
       "      <td>500.10</td>\n",
       "      <td>551.00</td>\n",
       "      <td>545.60</td>\n",
       "      <td>543.53</td>\n",
       "      <td>1185514</td>\n",
       "      <td>6.443668e+08</td>\n",
       "      <td>28000</td>\n",
       "      <td>220287</td>\n",
       "      <td>18.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>MINDAIND</td>\n",
       "      <td>EQ</td>\n",
       "      <td>17-May-2017</td>\n",
       "      <td>545.60</td>\n",
       "      <td>555.00</td>\n",
       "      <td>571.00</td>\n",
       "      <td>541.15</td>\n",
       "      <td>559.00</td>\n",
       "      <td>560.10</td>\n",
       "      <td>558.38</td>\n",
       "      <td>465042</td>\n",
       "      <td>2.596686e+08</td>\n",
       "      <td>14208</td>\n",
       "      <td>181463</td>\n",
       "      <td>39.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>MINDAIND</td>\n",
       "      <td>EQ</td>\n",
       "      <td>18-May-2017</td>\n",
       "      <td>560.10</td>\n",
       "      <td>556.95</td>\n",
       "      <td>558.00</td>\n",
       "      <td>542.20</td>\n",
       "      <td>547.00</td>\n",
       "      <td>545.25</td>\n",
       "      <td>551.99</td>\n",
       "      <td>90333</td>\n",
       "      <td>4.986314e+07</td>\n",
       "      <td>3951</td>\n",
       "      <td>47989</td>\n",
       "      <td>53.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>MINDAIND</td>\n",
       "      <td>EQ</td>\n",
       "      <td>19-May-2017</td>\n",
       "      <td>545.25</td>\n",
       "      <td>549.70</td>\n",
       "      <td>588.80</td>\n",
       "      <td>547.00</td>\n",
       "      <td>560.00</td>\n",
       "      <td>560.80</td>\n",
       "      <td>568.56</td>\n",
       "      <td>371398</td>\n",
       "      <td>2.111632e+08</td>\n",
       "      <td>10466</td>\n",
       "      <td>184909</td>\n",
       "      <td>49.79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>490</td>\n",
       "      <td>MINDAIND</td>\n",
       "      <td>EQ</td>\n",
       "      <td>07-May-2019</td>\n",
       "      <td>351.60</td>\n",
       "      <td>354.90</td>\n",
       "      <td>354.90</td>\n",
       "      <td>346.00</td>\n",
       "      <td>346.35</td>\n",
       "      <td>347.05</td>\n",
       "      <td>348.75</td>\n",
       "      <td>76556</td>\n",
       "      <td>2.669909e+07</td>\n",
       "      <td>6056</td>\n",
       "      <td>53021</td>\n",
       "      <td>69.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>491</td>\n",
       "      <td>MINDAIND</td>\n",
       "      <td>EQ</td>\n",
       "      <td>08-May-2019</td>\n",
       "      <td>347.05</td>\n",
       "      <td>350.00</td>\n",
       "      <td>352.00</td>\n",
       "      <td>346.00</td>\n",
       "      <td>350.00</td>\n",
       "      <td>350.35</td>\n",
       "      <td>349.79</td>\n",
       "      <td>41823</td>\n",
       "      <td>1.462933e+07</td>\n",
       "      <td>1764</td>\n",
       "      <td>25157</td>\n",
       "      <td>60.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>492</td>\n",
       "      <td>MINDAIND</td>\n",
       "      <td>EQ</td>\n",
       "      <td>09-May-2019</td>\n",
       "      <td>350.35</td>\n",
       "      <td>352.00</td>\n",
       "      <td>352.30</td>\n",
       "      <td>340.15</td>\n",
       "      <td>342.95</td>\n",
       "      <td>342.00</td>\n",
       "      <td>345.22</td>\n",
       "      <td>67267</td>\n",
       "      <td>2.322195e+07</td>\n",
       "      <td>2327</td>\n",
       "      <td>48953</td>\n",
       "      <td>72.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>493</td>\n",
       "      <td>MINDAIND</td>\n",
       "      <td>EQ</td>\n",
       "      <td>10-May-2019</td>\n",
       "      <td>342.00</td>\n",
       "      <td>344.75</td>\n",
       "      <td>349.90</td>\n",
       "      <td>340.00</td>\n",
       "      <td>347.00</td>\n",
       "      <td>345.15</td>\n",
       "      <td>345.18</td>\n",
       "      <td>88441</td>\n",
       "      <td>3.052768e+07</td>\n",
       "      <td>3123</td>\n",
       "      <td>47078</td>\n",
       "      <td>53.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>494</td>\n",
       "      <td>MINDAIND</td>\n",
       "      <td>EQ</td>\n",
       "      <td>13-May-2019</td>\n",
       "      <td>345.15</td>\n",
       "      <td>350.00</td>\n",
       "      <td>354.95</td>\n",
       "      <td>340.50</td>\n",
       "      <td>347.50</td>\n",
       "      <td>344.95</td>\n",
       "      <td>345.70</td>\n",
       "      <td>245445</td>\n",
       "      <td>8.485105e+07</td>\n",
       "      <td>11109</td>\n",
       "      <td>155350</td>\n",
       "      <td>63.29</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>495 rows × 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Symbol Series         Date  Prev Close  Open Price  High Price  \\\n",
       "0    MINDAIND     EQ  15-May-2017      495.65      495.95      522.00   \n",
       "1    MINDAIND     EQ  16-May-2017      517.90      523.70      568.00   \n",
       "2    MINDAIND     EQ  17-May-2017      545.60      555.00      571.00   \n",
       "3    MINDAIND     EQ  18-May-2017      560.10      556.95      558.00   \n",
       "4    MINDAIND     EQ  19-May-2017      545.25      549.70      588.80   \n",
       "..        ...    ...          ...         ...         ...         ...   \n",
       "490  MINDAIND     EQ  07-May-2019      351.60      354.90      354.90   \n",
       "491  MINDAIND     EQ  08-May-2019      347.05      350.00      352.00   \n",
       "492  MINDAIND     EQ  09-May-2019      350.35      352.00      352.30   \n",
       "493  MINDAIND     EQ  10-May-2019      342.00      344.75      349.90   \n",
       "494  MINDAIND     EQ  13-May-2019      345.15      350.00      354.95   \n",
       "\n",
       "     Low Price  Last Price  Close Price  Average Price  Total Traded Quantity  \\\n",
       "0       495.90      522.00       517.90         514.90                 110264   \n",
       "1       500.10      551.00       545.60         543.53                1185514   \n",
       "2       541.15      559.00       560.10         558.38                 465042   \n",
       "3       542.20      547.00       545.25         551.99                  90333   \n",
       "4       547.00      560.00       560.80         568.56                 371398   \n",
       "..         ...         ...          ...            ...                    ...   \n",
       "490     346.00      346.35       347.05         348.75                  76556   \n",
       "491     346.00      350.00       350.35         349.79                  41823   \n",
       "492     340.15      342.95       342.00         345.22                  67267   \n",
       "493     340.00      347.00       345.15         345.18                  88441   \n",
       "494     340.50      347.50       344.95         345.70                 245445   \n",
       "\n",
       "         Turnover  No. of Trades  Deliverable Qty  % Dly Qt to Traded Qty  \n",
       "0    5.677483e+07           3648            68819                   62.41  \n",
       "1    6.443668e+08          28000           220287                   18.58  \n",
       "2    2.596686e+08          14208           181463                   39.02  \n",
       "3    4.986314e+07           3951            47989                   53.12  \n",
       "4    2.111632e+08          10466           184909                   49.79  \n",
       "..            ...            ...              ...                     ...  \n",
       "490  2.669909e+07           6056            53021                   69.26  \n",
       "491  1.462933e+07           1764            25157                   60.15  \n",
       "492  2.322195e+07           2327            48953                   72.77  \n",
       "493  3.052768e+07           3123            47078                   53.23  \n",
       "494  8.485105e+07          11109           155350                   63.29  \n",
       "\n",
       "[495 rows x 15 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "clean_data = data[data.Series == 'EQ']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>Symbol</th>\n",
       "      <th>Series</th>\n",
       "      <th>Date</th>\n",
       "      <th>Prev Close</th>\n",
       "      <th>Open Price</th>\n",
       "      <th>High Price</th>\n",
       "      <th>Low Price</th>\n",
       "      <th>Last Price</th>\n",
       "      <th>Close Price</th>\n",
       "      <th>Average Price</th>\n",
       "      <th>Total Traded Quantity</th>\n",
       "      <th>Turnover</th>\n",
       "      <th>No. of Trades</th>\n",
       "      <th>Deliverable Qty</th>\n",
       "      <th>% Dly Qt to Traded Qty</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>MINDAIND</td>\n",
       "      <td>EQ</td>\n",
       "      <td>15-May-2017</td>\n",
       "      <td>495.65</td>\n",
       "      <td>495.95</td>\n",
       "      <td>522.00</td>\n",
       "      <td>495.90</td>\n",
       "      <td>522.00</td>\n",
       "      <td>517.90</td>\n",
       "      <td>514.90</td>\n",
       "      <td>110264</td>\n",
       "      <td>5.677483e+07</td>\n",
       "      <td>3648</td>\n",
       "      <td>68819</td>\n",
       "      <td>62.41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>MINDAIND</td>\n",
       "      <td>EQ</td>\n",
       "      <td>16-May-2017</td>\n",
       "      <td>517.90</td>\n",
       "      <td>523.70</td>\n",
       "      <td>568.00</td>\n",
       "      <td>500.10</td>\n",
       "      <td>551.00</td>\n",
       "      <td>545.60</td>\n",
       "      <td>543.53</td>\n",
       "      <td>1185514</td>\n",
       "      <td>6.443668e+08</td>\n",
       "      <td>28000</td>\n",
       "      <td>220287</td>\n",
       "      <td>18.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>MINDAIND</td>\n",
       "      <td>EQ</td>\n",
       "      <td>17-May-2017</td>\n",
       "      <td>545.60</td>\n",
       "      <td>555.00</td>\n",
       "      <td>571.00</td>\n",
       "      <td>541.15</td>\n",
       "      <td>559.00</td>\n",
       "      <td>560.10</td>\n",
       "      <td>558.38</td>\n",
       "      <td>465042</td>\n",
       "      <td>2.596686e+08</td>\n",
       "      <td>14208</td>\n",
       "      <td>181463</td>\n",
       "      <td>39.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>MINDAIND</td>\n",
       "      <td>EQ</td>\n",
       "      <td>18-May-2017</td>\n",
       "      <td>560.10</td>\n",
       "      <td>556.95</td>\n",
       "      <td>558.00</td>\n",
       "      <td>542.20</td>\n",
       "      <td>547.00</td>\n",
       "      <td>545.25</td>\n",
       "      <td>551.99</td>\n",
       "      <td>90333</td>\n",
       "      <td>4.986314e+07</td>\n",
       "      <td>3951</td>\n",
       "      <td>47989</td>\n",
       "      <td>53.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>MINDAIND</td>\n",
       "      <td>EQ</td>\n",
       "      <td>19-May-2017</td>\n",
       "      <td>545.25</td>\n",
       "      <td>549.70</td>\n",
       "      <td>588.80</td>\n",
       "      <td>547.00</td>\n",
       "      <td>560.00</td>\n",
       "      <td>560.80</td>\n",
       "      <td>568.56</td>\n",
       "      <td>371398</td>\n",
       "      <td>2.111632e+08</td>\n",
       "      <td>10466</td>\n",
       "      <td>184909</td>\n",
       "      <td>49.79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>490</td>\n",
       "      <td>MINDAIND</td>\n",
       "      <td>EQ</td>\n",
       "      <td>07-May-2019</td>\n",
       "      <td>351.60</td>\n",
       "      <td>354.90</td>\n",
       "      <td>354.90</td>\n",
       "      <td>346.00</td>\n",
       "      <td>346.35</td>\n",
       "      <td>347.05</td>\n",
       "      <td>348.75</td>\n",
       "      <td>76556</td>\n",
       "      <td>2.669909e+07</td>\n",
       "      <td>6056</td>\n",
       "      <td>53021</td>\n",
       "      <td>69.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>491</td>\n",
       "      <td>MINDAIND</td>\n",
       "      <td>EQ</td>\n",
       "      <td>08-May-2019</td>\n",
       "      <td>347.05</td>\n",
       "      <td>350.00</td>\n",
       "      <td>352.00</td>\n",
       "      <td>346.00</td>\n",
       "      <td>350.00</td>\n",
       "      <td>350.35</td>\n",
       "      <td>349.79</td>\n",
       "      <td>41823</td>\n",
       "      <td>1.462933e+07</td>\n",
       "      <td>1764</td>\n",
       "      <td>25157</td>\n",
       "      <td>60.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>492</td>\n",
       "      <td>MINDAIND</td>\n",
       "      <td>EQ</td>\n",
       "      <td>09-May-2019</td>\n",
       "      <td>350.35</td>\n",
       "      <td>352.00</td>\n",
       "      <td>352.30</td>\n",
       "      <td>340.15</td>\n",
       "      <td>342.95</td>\n",
       "      <td>342.00</td>\n",
       "      <td>345.22</td>\n",
       "      <td>67267</td>\n",
       "      <td>2.322195e+07</td>\n",
       "      <td>2327</td>\n",
       "      <td>48953</td>\n",
       "      <td>72.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>493</td>\n",
       "      <td>MINDAIND</td>\n",
       "      <td>EQ</td>\n",
       "      <td>10-May-2019</td>\n",
       "      <td>342.00</td>\n",
       "      <td>344.75</td>\n",
       "      <td>349.90</td>\n",
       "      <td>340.00</td>\n",
       "      <td>347.00</td>\n",
       "      <td>345.15</td>\n",
       "      <td>345.18</td>\n",
       "      <td>88441</td>\n",
       "      <td>3.052768e+07</td>\n",
       "      <td>3123</td>\n",
       "      <td>47078</td>\n",
       "      <td>53.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>494</td>\n",
       "      <td>MINDAIND</td>\n",
       "      <td>EQ</td>\n",
       "      <td>13-May-2019</td>\n",
       "      <td>345.15</td>\n",
       "      <td>350.00</td>\n",
       "      <td>354.95</td>\n",
       "      <td>340.50</td>\n",
       "      <td>347.50</td>\n",
       "      <td>344.95</td>\n",
       "      <td>345.70</td>\n",
       "      <td>245445</td>\n",
       "      <td>8.485105e+07</td>\n",
       "      <td>11109</td>\n",
       "      <td>155350</td>\n",
       "      <td>63.29</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>494 rows × 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Symbol Series         Date  Prev Close  Open Price  High Price  \\\n",
       "0    MINDAIND     EQ  15-May-2017      495.65      495.95      522.00   \n",
       "1    MINDAIND     EQ  16-May-2017      517.90      523.70      568.00   \n",
       "2    MINDAIND     EQ  17-May-2017      545.60      555.00      571.00   \n",
       "3    MINDAIND     EQ  18-May-2017      560.10      556.95      558.00   \n",
       "4    MINDAIND     EQ  19-May-2017      545.25      549.70      588.80   \n",
       "..        ...    ...          ...         ...         ...         ...   \n",
       "490  MINDAIND     EQ  07-May-2019      351.60      354.90      354.90   \n",
       "491  MINDAIND     EQ  08-May-2019      347.05      350.00      352.00   \n",
       "492  MINDAIND     EQ  09-May-2019      350.35      352.00      352.30   \n",
       "493  MINDAIND     EQ  10-May-2019      342.00      344.75      349.90   \n",
       "494  MINDAIND     EQ  13-May-2019      345.15      350.00      354.95   \n",
       "\n",
       "     Low Price  Last Price  Close Price  Average Price  Total Traded Quantity  \\\n",
       "0       495.90      522.00       517.90         514.90                 110264   \n",
       "1       500.10      551.00       545.60         543.53                1185514   \n",
       "2       541.15      559.00       560.10         558.38                 465042   \n",
       "3       542.20      547.00       545.25         551.99                  90333   \n",
       "4       547.00      560.00       560.80         568.56                 371398   \n",
       "..         ...         ...          ...            ...                    ...   \n",
       "490     346.00      346.35       347.05         348.75                  76556   \n",
       "491     346.00      350.00       350.35         349.79                  41823   \n",
       "492     340.15      342.95       342.00         345.22                  67267   \n",
       "493     340.00      347.00       345.15         345.18                  88441   \n",
       "494     340.50      347.50       344.95         345.70                 245445   \n",
       "\n",
       "         Turnover  No. of Trades  Deliverable Qty  % Dly Qt to Traded Qty  \n",
       "0    5.677483e+07           3648            68819                   62.41  \n",
       "1    6.443668e+08          28000           220287                   18.58  \n",
       "2    2.596686e+08          14208           181463                   39.02  \n",
       "3    4.986314e+07           3951            47989                   53.12  \n",
       "4    2.111632e+08          10466           184909                   49.79  \n",
       "..            ...            ...              ...                     ...  \n",
       "490  2.669909e+07           6056            53021                   69.26  \n",
       "491  1.462933e+07           1764            25157                   60.15  \n",
       "492  2.322195e+07           2327            48953                   72.77  \n",
       "493  3.052768e+07           3123            47078                   53.23  \n",
       "494  8.485105e+07          11109           155350                   63.29  \n",
       "\n",
       "[494 rows x 15 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "clean_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>Symbol</th>\n",
       "      <th>Series</th>\n",
       "      <th>Date</th>\n",
       "      <th>Prev Close</th>\n",
       "      <th>Open Price</th>\n",
       "      <th>High Price</th>\n",
       "      <th>Low Price</th>\n",
       "      <th>Last Price</th>\n",
       "      <th>Close Price</th>\n",
       "      <th>Average Price</th>\n",
       "      <th>Total Traded Quantity</th>\n",
       "      <th>Turnover</th>\n",
       "      <th>No. of Trades</th>\n",
       "      <th>Deliverable Qty</th>\n",
       "      <th>% Dly Qt to Traded Qty</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>490</td>\n",
       "      <td>MINDAIND</td>\n",
       "      <td>EQ</td>\n",
       "      <td>07-May-2019</td>\n",
       "      <td>351.60</td>\n",
       "      <td>354.90</td>\n",
       "      <td>354.90</td>\n",
       "      <td>346.00</td>\n",
       "      <td>346.35</td>\n",
       "      <td>347.05</td>\n",
       "      <td>348.75</td>\n",
       "      <td>76556</td>\n",
       "      <td>26699093.00</td>\n",
       "      <td>6056</td>\n",
       "      <td>53021</td>\n",
       "      <td>69.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>491</td>\n",
       "      <td>MINDAIND</td>\n",
       "      <td>EQ</td>\n",
       "      <td>08-May-2019</td>\n",
       "      <td>347.05</td>\n",
       "      <td>350.00</td>\n",
       "      <td>352.00</td>\n",
       "      <td>346.00</td>\n",
       "      <td>350.00</td>\n",
       "      <td>350.35</td>\n",
       "      <td>349.79</td>\n",
       "      <td>41823</td>\n",
       "      <td>14629334.60</td>\n",
       "      <td>1764</td>\n",
       "      <td>25157</td>\n",
       "      <td>60.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>492</td>\n",
       "      <td>MINDAIND</td>\n",
       "      <td>EQ</td>\n",
       "      <td>09-May-2019</td>\n",
       "      <td>350.35</td>\n",
       "      <td>352.00</td>\n",
       "      <td>352.30</td>\n",
       "      <td>340.15</td>\n",
       "      <td>342.95</td>\n",
       "      <td>342.00</td>\n",
       "      <td>345.22</td>\n",
       "      <td>67267</td>\n",
       "      <td>23221949.60</td>\n",
       "      <td>2327</td>\n",
       "      <td>48953</td>\n",
       "      <td>72.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>493</td>\n",
       "      <td>MINDAIND</td>\n",
       "      <td>EQ</td>\n",
       "      <td>10-May-2019</td>\n",
       "      <td>342.00</td>\n",
       "      <td>344.75</td>\n",
       "      <td>349.90</td>\n",
       "      <td>340.00</td>\n",
       "      <td>347.00</td>\n",
       "      <td>345.15</td>\n",
       "      <td>345.18</td>\n",
       "      <td>88441</td>\n",
       "      <td>30527679.75</td>\n",
       "      <td>3123</td>\n",
       "      <td>47078</td>\n",
       "      <td>53.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>494</td>\n",
       "      <td>MINDAIND</td>\n",
       "      <td>EQ</td>\n",
       "      <td>13-May-2019</td>\n",
       "      <td>345.15</td>\n",
       "      <td>350.00</td>\n",
       "      <td>354.95</td>\n",
       "      <td>340.50</td>\n",
       "      <td>347.50</td>\n",
       "      <td>344.95</td>\n",
       "      <td>345.70</td>\n",
       "      <td>245445</td>\n",
       "      <td>84851053.15</td>\n",
       "      <td>11109</td>\n",
       "      <td>155350</td>\n",
       "      <td>63.29</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Symbol Series         Date  Prev Close  Open Price  High Price  \\\n",
       "490  MINDAIND     EQ  07-May-2019      351.60      354.90      354.90   \n",
       "491  MINDAIND     EQ  08-May-2019      347.05      350.00      352.00   \n",
       "492  MINDAIND     EQ  09-May-2019      350.35      352.00      352.30   \n",
       "493  MINDAIND     EQ  10-May-2019      342.00      344.75      349.90   \n",
       "494  MINDAIND     EQ  13-May-2019      345.15      350.00      354.95   \n",
       "\n",
       "     Low Price  Last Price  Close Price  Average Price  Total Traded Quantity  \\\n",
       "490     346.00      346.35       347.05         348.75                  76556   \n",
       "491     346.00      350.00       350.35         349.79                  41823   \n",
       "492     340.15      342.95       342.00         345.22                  67267   \n",
       "493     340.00      347.00       345.15         345.18                  88441   \n",
       "494     340.50      347.50       344.95         345.70                 245445   \n",
       "\n",
       "        Turnover  No. of Trades  Deliverable Qty  % Dly Qt to Traded Qty  \n",
       "490  26699093.00           6056            53021                   69.26  \n",
       "491  14629334.60           1764            25157                   60.15  \n",
       "492  23221949.60           2327            48953                   72.77  \n",
       "493  30527679.75           3123            47078                   53.23  \n",
       "494  84851053.15          11109           155350                   63.29  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "clean_data.tail()"
   ]
  },
  {
   "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>Symbol</th>\n",
       "      <th>Series</th>\n",
       "      <th>Date</th>\n",
       "      <th>Prev Close</th>\n",
       "      <th>Open Price</th>\n",
       "      <th>High Price</th>\n",
       "      <th>Low Price</th>\n",
       "      <th>Last Price</th>\n",
       "      <th>Close Price</th>\n",
       "      <th>Average Price</th>\n",
       "      <th>Total Traded Quantity</th>\n",
       "      <th>Turnover</th>\n",
       "      <th>No. of Trades</th>\n",
       "      <th>Deliverable Qty</th>\n",
       "      <th>% Dly Qt to Traded Qty</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>MINDAIND</td>\n",
       "      <td>EQ</td>\n",
       "      <td>15-May-2017</td>\n",
       "      <td>495.65</td>\n",
       "      <td>495.95</td>\n",
       "      <td>522.0</td>\n",
       "      <td>495.90</td>\n",
       "      <td>522.0</td>\n",
       "      <td>517.90</td>\n",
       "      <td>514.90</td>\n",
       "      <td>110264</td>\n",
       "      <td>5.677483e+07</td>\n",
       "      <td>3648</td>\n",
       "      <td>68819</td>\n",
       "      <td>62.41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>MINDAIND</td>\n",
       "      <td>EQ</td>\n",
       "      <td>16-May-2017</td>\n",
       "      <td>517.90</td>\n",
       "      <td>523.70</td>\n",
       "      <td>568.0</td>\n",
       "      <td>500.10</td>\n",
       "      <td>551.0</td>\n",
       "      <td>545.60</td>\n",
       "      <td>543.53</td>\n",
       "      <td>1185514</td>\n",
       "      <td>6.443668e+08</td>\n",
       "      <td>28000</td>\n",
       "      <td>220287</td>\n",
       "      <td>18.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>MINDAIND</td>\n",
       "      <td>EQ</td>\n",
       "      <td>17-May-2017</td>\n",
       "      <td>545.60</td>\n",
       "      <td>555.00</td>\n",
       "      <td>571.0</td>\n",
       "      <td>541.15</td>\n",
       "      <td>559.0</td>\n",
       "      <td>560.10</td>\n",
       "      <td>558.38</td>\n",
       "      <td>465042</td>\n",
       "      <td>2.596686e+08</td>\n",
       "      <td>14208</td>\n",
       "      <td>181463</td>\n",
       "      <td>39.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>MINDAIND</td>\n",
       "      <td>EQ</td>\n",
       "      <td>18-May-2017</td>\n",
       "      <td>560.10</td>\n",
       "      <td>556.95</td>\n",
       "      <td>558.0</td>\n",
       "      <td>542.20</td>\n",
       "      <td>547.0</td>\n",
       "      <td>545.25</td>\n",
       "      <td>551.99</td>\n",
       "      <td>90333</td>\n",
       "      <td>4.986314e+07</td>\n",
       "      <td>3951</td>\n",
       "      <td>47989</td>\n",
       "      <td>53.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>MINDAIND</td>\n",
       "      <td>EQ</td>\n",
       "      <td>19-May-2017</td>\n",
       "      <td>545.25</td>\n",
       "      <td>549.70</td>\n",
       "      <td>588.8</td>\n",
       "      <td>547.00</td>\n",
       "      <td>560.0</td>\n",
       "      <td>560.80</td>\n",
       "      <td>568.56</td>\n",
       "      <td>371398</td>\n",
       "      <td>2.111632e+08</td>\n",
       "      <td>10466</td>\n",
       "      <td>184909</td>\n",
       "      <td>49.79</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Symbol Series         Date  Prev Close  Open Price  High Price  \\\n",
       "0  MINDAIND     EQ  15-May-2017      495.65      495.95       522.0   \n",
       "1  MINDAIND     EQ  16-May-2017      517.90      523.70       568.0   \n",
       "2  MINDAIND     EQ  17-May-2017      545.60      555.00       571.0   \n",
       "3  MINDAIND     EQ  18-May-2017      560.10      556.95       558.0   \n",
       "4  MINDAIND     EQ  19-May-2017      545.25      549.70       588.8   \n",
       "\n",
       "   Low Price  Last Price  Close Price  Average Price  Total Traded Quantity  \\\n",
       "0     495.90       522.0       517.90         514.90                 110264   \n",
       "1     500.10       551.0       545.60         543.53                1185514   \n",
       "2     541.15       559.0       560.10         558.38                 465042   \n",
       "3     542.20       547.0       545.25         551.99                  90333   \n",
       "4     547.00       560.0       560.80         568.56                 371398   \n",
       "\n",
       "       Turnover  No. of Trades  Deliverable Qty  % Dly Qt to Traded Qty  \n",
       "0  5.677483e+07           3648            68819                   62.41  \n",
       "1  6.443668e+08          28000           220287                   18.58  \n",
       "2  2.596686e+08          14208           181463                   39.02  \n",
       "3  4.986314e+07           3951            47989                   53.12  \n",
       "4  2.111632e+08          10466           184909                   49.79  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "clean_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>Prev Close</th>\n",
       "      <th>Open Price</th>\n",
       "      <th>High Price</th>\n",
       "      <th>Low Price</th>\n",
       "      <th>Last Price</th>\n",
       "      <th>Close Price</th>\n",
       "      <th>Average Price</th>\n",
       "      <th>Total Traded Quantity</th>\n",
       "      <th>Turnover</th>\n",
       "      <th>No. of Trades</th>\n",
       "      <th>Deliverable Qty</th>\n",
       "      <th>% Dly Qt to Traded Qty</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>count</td>\n",
       "      <td>494.000000</td>\n",
       "      <td>494.000000</td>\n",
       "      <td>494.000000</td>\n",
       "      <td>494.000000</td>\n",
       "      <td>494.000000</td>\n",
       "      <td>494.000000</td>\n",
       "      <td>494.000000</td>\n",
       "      <td>4.940000e+02</td>\n",
       "      <td>4.940000e+02</td>\n",
       "      <td>494.000000</td>\n",
       "      <td>494.000000</td>\n",
       "      <td>494.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>mean</td>\n",
       "      <td>725.454960</td>\n",
       "      <td>726.206579</td>\n",
       "      <td>739.548279</td>\n",
       "      <td>712.045951</td>\n",
       "      <td>725.613866</td>\n",
       "      <td>725.149899</td>\n",
       "      <td>726.152551</td>\n",
       "      <td>1.554882e+05</td>\n",
       "      <td>9.978910e+07</td>\n",
       "      <td>6066.582996</td>\n",
       "      <td>87039.127530</td>\n",
       "      <td>58.218907</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>std</td>\n",
       "      <td>364.838877</td>\n",
       "      <td>365.941476</td>\n",
       "      <td>372.368522</td>\n",
       "      <td>358.403590</td>\n",
       "      <td>365.417843</td>\n",
       "      <td>365.094333</td>\n",
       "      <td>365.687820</td>\n",
       "      <td>1.897702e+05</td>\n",
       "      <td>1.246845e+08</td>\n",
       "      <td>5527.186938</td>\n",
       "      <td>94977.222687</td>\n",
       "      <td>13.517435</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>min</td>\n",
       "      <td>264.400000</td>\n",
       "      <td>265.000000</td>\n",
       "      <td>269.350000</td>\n",
       "      <td>260.700000</td>\n",
       "      <td>266.000000</td>\n",
       "      <td>264.400000</td>\n",
       "      <td>267.440000</td>\n",
       "      <td>8.188000e+03</td>\n",
       "      <td>5.505349e+06</td>\n",
       "      <td>262.000000</td>\n",
       "      <td>6396.000000</td>\n",
       "      <td>17.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>25%</td>\n",
       "      <td>347.100000</td>\n",
       "      <td>348.662500</td>\n",
       "      <td>354.862500</td>\n",
       "      <td>341.062500</td>\n",
       "      <td>346.887500</td>\n",
       "      <td>347.012500</td>\n",
       "      <td>347.782500</td>\n",
       "      <td>6.719725e+04</td>\n",
       "      <td>3.940619e+07</td>\n",
       "      <td>2993.500000</td>\n",
       "      <td>36382.000000</td>\n",
       "      <td>49.072500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>50%</td>\n",
       "      <td>681.375000</td>\n",
       "      <td>680.475000</td>\n",
       "      <td>687.175000</td>\n",
       "      <td>672.025000</td>\n",
       "      <td>682.050000</td>\n",
       "      <td>681.375000</td>\n",
       "      <td>680.165000</td>\n",
       "      <td>1.049440e+05</td>\n",
       "      <td>6.181448e+07</td>\n",
       "      <td>4548.000000</td>\n",
       "      <td>56433.000000</td>\n",
       "      <td>58.125000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>75%</td>\n",
       "      <td>1106.900000</td>\n",
       "      <td>1106.412500</td>\n",
       "      <td>1132.925000</td>\n",
       "      <td>1087.537500</td>\n",
       "      <td>1110.000000</td>\n",
       "      <td>1106.900000</td>\n",
       "      <td>1112.707500</td>\n",
       "      <td>1.739965e+05</td>\n",
       "      <td>1.140088e+08</td>\n",
       "      <td>7185.000000</td>\n",
       "      <td>97374.750000</td>\n",
       "      <td>67.635000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>max</td>\n",
       "      <td>1331.100000</td>\n",
       "      <td>1334.150000</td>\n",
       "      <td>1358.000000</td>\n",
       "      <td>1310.000000</td>\n",
       "      <td>1338.000000</td>\n",
       "      <td>1331.100000</td>\n",
       "      <td>1339.460000</td>\n",
       "      <td>2.335284e+06</td>\n",
       "      <td>1.720436e+09</td>\n",
       "      <td>60739.000000</td>\n",
       "      <td>898519.000000</td>\n",
       "      <td>92.460000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Prev Close   Open Price   High Price    Low Price   Last Price  \\\n",
       "count   494.000000   494.000000   494.000000   494.000000   494.000000   \n",
       "mean    725.454960   726.206579   739.548279   712.045951   725.613866   \n",
       "std     364.838877   365.941476   372.368522   358.403590   365.417843   \n",
       "min     264.400000   265.000000   269.350000   260.700000   266.000000   \n",
       "25%     347.100000   348.662500   354.862500   341.062500   346.887500   \n",
       "50%     681.375000   680.475000   687.175000   672.025000   682.050000   \n",
       "75%    1106.900000  1106.412500  1132.925000  1087.537500  1110.000000   \n",
       "max    1331.100000  1334.150000  1358.000000  1310.000000  1338.000000   \n",
       "\n",
       "       Close Price  Average Price  Total Traded Quantity      Turnover  \\\n",
       "count   494.000000     494.000000           4.940000e+02  4.940000e+02   \n",
       "mean    725.149899     726.152551           1.554882e+05  9.978910e+07   \n",
       "std     365.094333     365.687820           1.897702e+05  1.246845e+08   \n",
       "min     264.400000     267.440000           8.188000e+03  5.505349e+06   \n",
       "25%     347.012500     347.782500           6.719725e+04  3.940619e+07   \n",
       "50%     681.375000     680.165000           1.049440e+05  6.181448e+07   \n",
       "75%    1106.900000    1112.707500           1.739965e+05  1.140088e+08   \n",
       "max    1331.100000    1339.460000           2.335284e+06  1.720436e+09   \n",
       "\n",
       "       No. of Trades  Deliverable Qty  % Dly Qt to Traded Qty  \n",
       "count     494.000000       494.000000              494.000000  \n",
       "mean     6066.582996     87039.127530               58.218907  \n",
       "std      5527.186938     94977.222687               13.517435  \n",
       "min       262.000000      6396.000000               17.000000  \n",
       "25%      2993.500000     36382.000000               49.072500  \n",
       "50%      4548.000000     56433.000000               58.125000  \n",
       "75%      7185.000000     97374.750000               67.635000  \n",
       "max     60739.000000    898519.000000               92.460000  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "clean_data.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Query 1.2\n",
    "Calculate the maximum, minimum and mean price for the last 90 days. (price=Closing Price unless stated otherwise) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "388.05"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "clean_data.tail(90)['Close Price'].max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "264.4"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "clean_data.tail(90)['Close Price'].min()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "325.8327777777777"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "clean_data.tail(90)['Close Price'].mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Query 1.3\n",
    "Analyse the data types for each column of the dataframe. Pandas knows how to deal with dates in an intelligent manner. But to make use of Pandas functionality for dates, you need to ensure that the column is of type 'datetime64(ns)'. Change the date column from 'object' type to 'datetime64(ns)' for future convenience. See what happens if you subtract the minimum value of the date column from the maximum value."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "clean_data['Date'] = pd.to_datetime(clean_data['Date'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     2017-05-15\n",
       "1     2017-05-16\n",
       "2     2017-05-17\n",
       "3     2017-05-18\n",
       "4     2017-05-19\n",
       "         ...    \n",
       "490   2019-05-07\n",
       "491   2019-05-08\n",
       "492   2019-05-09\n",
       "493   2019-05-10\n",
       "494   2019-05-13\n",
       "Name: Date, Length: 494, dtype: datetime64[ns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.to_datetime(clean_data['Date'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Timedelta('728 days 00:00:00')"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "clean_data['Date'].max()-clean_data['Date'].min()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Query 1.4\n",
    "In a separate array , calculate the monthwise VWAP (Volume Weighted Average Price ) of the stock. ( VWAP = sum(price*volume)/sum(volume) ) To know more about VWAP , visit - VWAP definition {Hint : Create a new dataframe column ‘Month’. The values for this column can be derived from the ‘Date” column by using appropriate pandas functions. Similarly, create a column ‘Year’ and initialize it. Then use the 'groupby()' function by month and year. Finally, calculate the vwap value for each month (i.e. for each group created)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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></th>\n",
       "      <th>VWAP</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Month</th>\n",
       "      <th>Year</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td rowspan=\"2\" valign=\"top\">1</td>\n",
       "      <td>2018</td>\n",
       "      <td>855.159406</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2019</td>\n",
       "      <td>725.416677</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td rowspan=\"2\" valign=\"top\">2</td>\n",
       "      <td>2018</td>\n",
       "      <td>892.430695</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2019</td>\n",
       "      <td>702.370379</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td rowspan=\"2\" valign=\"top\">3</td>\n",
       "      <td>2018</td>\n",
       "      <td>916.414102</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2019</td>\n",
       "      <td>670.453397</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td rowspan=\"2\" valign=\"top\">4</td>\n",
       "      <td>2018</td>\n",
       "      <td>933.660396</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2019</td>\n",
       "      <td>653.830206</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td rowspan=\"3\" valign=\"top\">5</td>\n",
       "      <td>2017</td>\n",
       "      <td>517.900000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2018</td>\n",
       "      <td>941.469087</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2019</td>\n",
       "      <td>643.911460</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td rowspan=\"2\" valign=\"top\">6</td>\n",
       "      <td>2017</td>\n",
       "      <td>561.541187</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2018</td>\n",
       "      <td>973.468001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td rowspan=\"2\" valign=\"top\">7</td>\n",
       "      <td>2017</td>\n",
       "      <td>577.555554</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2018</td>\n",
       "      <td>986.912609</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td rowspan=\"2\" valign=\"top\">8</td>\n",
       "      <td>2017</td>\n",
       "      <td>644.040942</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2018</td>\n",
       "      <td>947.918880</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td rowspan=\"2\" valign=\"top\">9</td>\n",
       "      <td>2017</td>\n",
       "      <td>679.776395</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2018</td>\n",
       "      <td>874.963686</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td rowspan=\"2\" valign=\"top\">10</td>\n",
       "      <td>2017</td>\n",
       "      <td>712.607102</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2018</td>\n",
       "      <td>819.786234</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td rowspan=\"2\" valign=\"top\">11</td>\n",
       "      <td>2017</td>\n",
       "      <td>727.241113</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2018</td>\n",
       "      <td>773.327790</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td rowspan=\"2\" valign=\"top\">12</td>\n",
       "      <td>2017</td>\n",
       "      <td>804.116871</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2018</td>\n",
       "      <td>741.164953</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  VWAP\n",
       "Month Year            \n",
       "1     2018  855.159406\n",
       "      2019  725.416677\n",
       "2     2018  892.430695\n",
       "      2019  702.370379\n",
       "3     2018  916.414102\n",
       "      2019  670.453397\n",
       "4     2018  933.660396\n",
       "      2019  653.830206\n",
       "5     2017  517.900000\n",
       "      2018  941.469087\n",
       "      2019  643.911460\n",
       "6     2017  561.541187\n",
       "      2018  973.468001\n",
       "7     2017  577.555554\n",
       "      2018  986.912609\n",
       "8     2017  644.040942\n",
       "      2018  947.918880\n",
       "9     2017  679.776395\n",
       "      2018  874.963686\n",
       "10    2017  712.607102\n",
       "      2018  819.786234\n",
       "11    2017  727.241113\n",
       "      2018  773.327790\n",
       "12    2017  804.116871\n",
       "      2018  741.164953"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "clean_data['Month'] = pd.DatetimeIndex(clean_data['Date']).month\n",
    "clean_data['Year'] = pd.DatetimeIndex(clean_data['Date']).year\n",
    "clean_data['VWAP'] = (clean_data['Close Price'] * clean_data['Total Traded Quantity']).cumsum() / clean_data['Total Traded Quantity'].cumsum()\n",
    "clean_data_vwap = clean_data[['Month','Year','VWAP']]\n",
    "group = clean_data_vwap.groupby(['Month','Year'])\n",
    "group.first()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Query 1.5\n",
    "\n",
    "Write a function to calculate the average price over the last N days of the stock price data where N is a user defined parameter. Write a second function to calculate the profit/loss percentage over the last N days. Calculate the average price AND the profit/loss percentages over the course of last - 1 week, 2 weeks, 1 month, 3 months, 6 months and 1 year. {Note : Profit/Loss percentage between N days is the percentage change between the closing prices of the 2 days }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Average prices for last N days are as follows:\n",
      "Last 1 week 346.928\n",
      "Last 2 weeks 352.51699999999994\n",
      "Last 1 month 357.43136363636364\n",
      "Last 3 months 333.25181818181824\n",
      "Last 6 months 327.681893939394\n",
      "Last 1 year 514.8776284584981\n",
      "Profit/Loss % for N days are as follows:\n",
      "Loss/Profit percentage for last N days are as follows:\n",
      "Last 1 week 0.6087838817219953\n",
      "Last 2 weeks 8.291056674880425\n",
      "Last 1 month 0.11595883461372203\n",
      "Last 3 months 16.538628786780695\n",
      "Last 6 months 9.508624438324398\n",
      "Last 1 year 213.04536889404258\n"
     ]
    }
   ],
   "source": [
    "def avg_price(N):\n",
    "    return (data['Average Price'].tail(N).sum())/N\n",
    "print(\"Average prices for last N days are as follows:\")\n",
    "print(\"Last 1 week\",avg_price(5))\n",
    "print(\"Last 2 weeks\",avg_price(10))\n",
    "print(\"Last 1 month\",avg_price(22))\n",
    "print(\"Last 3 months\",avg_price(66))\n",
    "print(\"Last 6 months\",avg_price(132))\n",
    "print(\"Last 1 year\",avg_price(253))\n",
    "print(\"Profit/Loss % for N days are as follows:\")\n",
    "def prof_loss(N):\n",
    "    difference = (clean_data['Close Price'].tail(N).iloc[N-1] - clean_data['Close Price'].tail(N).iloc[0])\n",
    "    if difference < 0 :\n",
    "        loss = -(difference)\n",
    "        loss_percen = (loss/clean_data['Close Price'].tail(N).iloc[N-1])*100\n",
    "        return loss_percen\n",
    "    if difference > 0 :\n",
    "        profit = difference\n",
    "        profit_percen = (profit/clean_data['Close Price'].tail(N).iloc[N-1])*100\n",
    "        return profit_percen\n",
    "print(\"Loss/Profit percentage for last N days are as follows:\")\n",
    "print(\"Last 1 week\",prof_loss(5))\n",
    "print(\"Last 2 weeks\",prof_loss(10))\n",
    "print(\"Last 1 month\",prof_loss(22))\n",
    "print(\"Last 3 months\",prof_loss(66))\n",
    "print(\"Last 6 months\",prof_loss(132))\n",
    "print(\"Last 1 year\",prof_loss(253))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Query 1.6\n",
    "\n",
    "Add a column 'Day_Perc_Change' where the values are the daily change in percentages i.e. the percentage change between 2 consecutive day's closing prices. Instead of using the basic mathematical formula for computing the same, use 'pct_change()' function provided by Pandas for dataframes. You will note that the first entry of the column will have a ‘Nan’ value. Why does this happen? Either remove the first row, or set the entry to 0 before proceeding."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "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>Symbol</th>\n",
       "      <th>Series</th>\n",
       "      <th>Date</th>\n",
       "      <th>Prev Close</th>\n",
       "      <th>Open Price</th>\n",
       "      <th>High Price</th>\n",
       "      <th>Low Price</th>\n",
       "      <th>Last Price</th>\n",
       "      <th>Close Price</th>\n",
       "      <th>Average Price</th>\n",
       "      <th>Total Traded Quantity</th>\n",
       "      <th>Turnover</th>\n",
       "      <th>No. of Trades</th>\n",
       "      <th>Deliverable Qty</th>\n",
       "      <th>% Dly Qt to Traded Qty</th>\n",
       "      <th>Month</th>\n",
       "      <th>Year</th>\n",
       "      <th>VWAP</th>\n",
       "      <th>Day_Perc_Change</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>MINDAIND</td>\n",
       "      <td>EQ</td>\n",
       "      <td>2017-05-15</td>\n",
       "      <td>495.65</td>\n",
       "      <td>495.95</td>\n",
       "      <td>522.00</td>\n",
       "      <td>495.90</td>\n",
       "      <td>522.00</td>\n",
       "      <td>517.90</td>\n",
       "      <td>514.90</td>\n",
       "      <td>110264</td>\n",
       "      <td>5.677483e+07</td>\n",
       "      <td>3648</td>\n",
       "      <td>68819</td>\n",
       "      <td>62.41</td>\n",
       "      <td>5</td>\n",
       "      <td>2017</td>\n",
       "      <td>517.900000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>MINDAIND</td>\n",
       "      <td>EQ</td>\n",
       "      <td>2017-05-16</td>\n",
       "      <td>517.90</td>\n",
       "      <td>523.70</td>\n",
       "      <td>568.00</td>\n",
       "      <td>500.10</td>\n",
       "      <td>551.00</td>\n",
       "      <td>545.60</td>\n",
       "      <td>543.53</td>\n",
       "      <td>1185514</td>\n",
       "      <td>6.443668e+08</td>\n",
       "      <td>28000</td>\n",
       "      <td>220287</td>\n",
       "      <td>18.58</td>\n",
       "      <td>5</td>\n",
       "      <td>2017</td>\n",
       "      <td>543.242873</td>\n",
       "      <td>0.053485</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>MINDAIND</td>\n",
       "      <td>EQ</td>\n",
       "      <td>2017-05-17</td>\n",
       "      <td>545.60</td>\n",
       "      <td>555.00</td>\n",
       "      <td>571.00</td>\n",
       "      <td>541.15</td>\n",
       "      <td>559.00</td>\n",
       "      <td>560.10</td>\n",
       "      <td>558.38</td>\n",
       "      <td>465042</td>\n",
       "      <td>2.596686e+08</td>\n",
       "      <td>14208</td>\n",
       "      <td>181463</td>\n",
       "      <td>39.02</td>\n",
       "      <td>5</td>\n",
       "      <td>2017</td>\n",
       "      <td>547.694931</td>\n",
       "      <td>0.026576</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>MINDAIND</td>\n",
       "      <td>EQ</td>\n",
       "      <td>2017-05-18</td>\n",
       "      <td>560.10</td>\n",
       "      <td>556.95</td>\n",
       "      <td>558.00</td>\n",
       "      <td>542.20</td>\n",
       "      <td>547.00</td>\n",
       "      <td>545.25</td>\n",
       "      <td>551.99</td>\n",
       "      <td>90333</td>\n",
       "      <td>4.986314e+07</td>\n",
       "      <td>3951</td>\n",
       "      <td>47989</td>\n",
       "      <td>53.12</td>\n",
       "      <td>5</td>\n",
       "      <td>2017</td>\n",
       "      <td>547.575623</td>\n",
       "      <td>-0.026513</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>MINDAIND</td>\n",
       "      <td>EQ</td>\n",
       "      <td>2017-05-19</td>\n",
       "      <td>545.25</td>\n",
       "      <td>549.70</td>\n",
       "      <td>588.80</td>\n",
       "      <td>547.00</td>\n",
       "      <td>560.00</td>\n",
       "      <td>560.80</td>\n",
       "      <td>568.56</td>\n",
       "      <td>371398</td>\n",
       "      <td>2.111632e+08</td>\n",
       "      <td>10466</td>\n",
       "      <td>184909</td>\n",
       "      <td>49.79</td>\n",
       "      <td>5</td>\n",
       "      <td>2017</td>\n",
       "      <td>549.785474</td>\n",
       "      <td>0.028519</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>490</th>\n",
       "      <td>MINDAIND</td>\n",
       "      <td>EQ</td>\n",
       "      <td>2019-05-07</td>\n",
       "      <td>351.60</td>\n",
       "      <td>354.90</td>\n",
       "      <td>354.90</td>\n",
       "      <td>346.00</td>\n",
       "      <td>346.35</td>\n",
       "      <td>347.05</td>\n",
       "      <td>348.75</td>\n",
       "      <td>76556</td>\n",
       "      <td>2.669909e+07</td>\n",
       "      <td>6056</td>\n",
       "      <td>53021</td>\n",
       "      <td>69.26</td>\n",
       "      <td>5</td>\n",
       "      <td>2019</td>\n",
       "      <td>642.942796</td>\n",
       "      <td>-0.012941</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>491</th>\n",
       "      <td>MINDAIND</td>\n",
       "      <td>EQ</td>\n",
       "      <td>2019-05-08</td>\n",
       "      <td>347.05</td>\n",
       "      <td>350.00</td>\n",
       "      <td>352.00</td>\n",
       "      <td>346.00</td>\n",
       "      <td>350.00</td>\n",
       "      <td>350.35</td>\n",
       "      <td>349.79</td>\n",
       "      <td>41823</td>\n",
       "      <td>1.462933e+07</td>\n",
       "      <td>1764</td>\n",
       "      <td>25157</td>\n",
       "      <td>60.15</td>\n",
       "      <td>5</td>\n",
       "      <td>2019</td>\n",
       "      <td>642.782646</td>\n",
       "      <td>0.009509</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>492</th>\n",
       "      <td>MINDAIND</td>\n",
       "      <td>EQ</td>\n",
       "      <td>2019-05-09</td>\n",
       "      <td>350.35</td>\n",
       "      <td>352.00</td>\n",
       "      <td>352.30</td>\n",
       "      <td>340.15</td>\n",
       "      <td>342.95</td>\n",
       "      <td>342.00</td>\n",
       "      <td>345.22</td>\n",
       "      <td>67267</td>\n",
       "      <td>2.322195e+07</td>\n",
       "      <td>2327</td>\n",
       "      <td>48953</td>\n",
       "      <td>72.77</td>\n",
       "      <td>5</td>\n",
       "      <td>2019</td>\n",
       "      <td>642.518087</td>\n",
       "      <td>-0.023833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>493</th>\n",
       "      <td>MINDAIND</td>\n",
       "      <td>EQ</td>\n",
       "      <td>2019-05-10</td>\n",
       "      <td>342.00</td>\n",
       "      <td>344.75</td>\n",
       "      <td>349.90</td>\n",
       "      <td>340.00</td>\n",
       "      <td>347.00</td>\n",
       "      <td>345.15</td>\n",
       "      <td>345.18</td>\n",
       "      <td>88441</td>\n",
       "      <td>3.052768e+07</td>\n",
       "      <td>3123</td>\n",
       "      <td>47078</td>\n",
       "      <td>53.23</td>\n",
       "      <td>5</td>\n",
       "      <td>2019</td>\n",
       "      <td>642.174597</td>\n",
       "      <td>0.009211</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>494</th>\n",
       "      <td>MINDAIND</td>\n",
       "      <td>EQ</td>\n",
       "      <td>2019-05-13</td>\n",
       "      <td>345.15</td>\n",
       "      <td>350.00</td>\n",
       "      <td>354.95</td>\n",
       "      <td>340.50</td>\n",
       "      <td>347.50</td>\n",
       "      <td>344.95</td>\n",
       "      <td>345.70</td>\n",
       "      <td>245445</td>\n",
       "      <td>8.485105e+07</td>\n",
       "      <td>11109</td>\n",
       "      <td>155350</td>\n",
       "      <td>63.29</td>\n",
       "      <td>5</td>\n",
       "      <td>2019</td>\n",
       "      <td>641.224835</td>\n",
       "      <td>-0.000579</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>494 rows × 19 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Symbol Series       Date  Prev Close  Open Price  High Price  \\\n",
       "0    MINDAIND     EQ 2017-05-15      495.65      495.95      522.00   \n",
       "1    MINDAIND     EQ 2017-05-16      517.90      523.70      568.00   \n",
       "2    MINDAIND     EQ 2017-05-17      545.60      555.00      571.00   \n",
       "3    MINDAIND     EQ 2017-05-18      560.10      556.95      558.00   \n",
       "4    MINDAIND     EQ 2017-05-19      545.25      549.70      588.80   \n",
       "..        ...    ...        ...         ...         ...         ...   \n",
       "490  MINDAIND     EQ 2019-05-07      351.60      354.90      354.90   \n",
       "491  MINDAIND     EQ 2019-05-08      347.05      350.00      352.00   \n",
       "492  MINDAIND     EQ 2019-05-09      350.35      352.00      352.30   \n",
       "493  MINDAIND     EQ 2019-05-10      342.00      344.75      349.90   \n",
       "494  MINDAIND     EQ 2019-05-13      345.15      350.00      354.95   \n",
       "\n",
       "     Low Price  Last Price  Close Price  Average Price  Total Traded Quantity  \\\n",
       "0       495.90      522.00       517.90         514.90                 110264   \n",
       "1       500.10      551.00       545.60         543.53                1185514   \n",
       "2       541.15      559.00       560.10         558.38                 465042   \n",
       "3       542.20      547.00       545.25         551.99                  90333   \n",
       "4       547.00      560.00       560.80         568.56                 371398   \n",
       "..         ...         ...          ...            ...                    ...   \n",
       "490     346.00      346.35       347.05         348.75                  76556   \n",
       "491     346.00      350.00       350.35         349.79                  41823   \n",
       "492     340.15      342.95       342.00         345.22                  67267   \n",
       "493     340.00      347.00       345.15         345.18                  88441   \n",
       "494     340.50      347.50       344.95         345.70                 245445   \n",
       "\n",
       "         Turnover  No. of Trades  Deliverable Qty  % Dly Qt to Traded Qty  \\\n",
       "0    5.677483e+07           3648            68819                   62.41   \n",
       "1    6.443668e+08          28000           220287                   18.58   \n",
       "2    2.596686e+08          14208           181463                   39.02   \n",
       "3    4.986314e+07           3951            47989                   53.12   \n",
       "4    2.111632e+08          10466           184909                   49.79   \n",
       "..            ...            ...              ...                     ...   \n",
       "490  2.669909e+07           6056            53021                   69.26   \n",
       "491  1.462933e+07           1764            25157                   60.15   \n",
       "492  2.322195e+07           2327            48953                   72.77   \n",
       "493  3.052768e+07           3123            47078                   53.23   \n",
       "494  8.485105e+07          11109           155350                   63.29   \n",
       "\n",
       "     Month  Year        VWAP  Day_Perc_Change  \n",
       "0        5  2017  517.900000         0.000000  \n",
       "1        5  2017  543.242873         0.053485  \n",
       "2        5  2017  547.694931         0.026576  \n",
       "3        5  2017  547.575623        -0.026513  \n",
       "4        5  2017  549.785474         0.028519  \n",
       "..     ...   ...         ...              ...  \n",
       "490      5  2019  642.942796        -0.012941  \n",
       "491      5  2019  642.782646         0.009509  \n",
       "492      5  2019  642.518087        -0.023833  \n",
       "493      5  2019  642.174597         0.009211  \n",
       "494      5  2019  641.224835        -0.000579  \n",
       "\n",
       "[494 rows x 19 columns]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "clean_data['Day_Perc_Change'] = clean_data['Close Price'].pct_change().fillna(0)\n",
    "clean_data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Query 1.7\n",
    "\n",
    "Add another column 'Trend' whose values are: 'Slight or No change' for 'Day_Perc_Change' in between -0.5 and 0.5 'Slight positive' for 'Day_Perc_Change' in between 0.5 and 1 'Slight negative' for 'Day_Perc_Change' in between -0.5 and -1 'Positive' for 'Day_Perc_Change' in between 1 and 3 'Negative' for 'Day_Perc_Change' in between -1 and -3 'Among top gainers' for 'Day_Perc_Change' in between 3 and 7 'Among top losers' for 'Day_Perc_Change' in between -3 and -7 'Bull run' for 'Day_Perc_Change' >7 'Bear drop' for 'Day_Perc_Change' <-7."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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>Symbol</th>\n",
       "      <th>Series</th>\n",
       "      <th>Date</th>\n",
       "      <th>Prev Close</th>\n",
       "      <th>Open Price</th>\n",
       "      <th>High Price</th>\n",
       "      <th>Low Price</th>\n",
       "      <th>Last Price</th>\n",
       "      <th>Close Price</th>\n",
       "      <th>Average Price</th>\n",
       "      <th>Total Traded Quantity</th>\n",
       "      <th>Turnover</th>\n",
       "      <th>No. of Trades</th>\n",
       "      <th>Deliverable Qty</th>\n",
       "      <th>% Dly Qt to Traded Qty</th>\n",
       "      <th>Month</th>\n",
       "      <th>Year</th>\n",
       "      <th>VWAP</th>\n",
       "      <th>Day_Perc_Change</th>\n",
       "      <th>Trend</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>MINDAIND</td>\n",
       "      <td>EQ</td>\n",
       "      <td>2017-05-15</td>\n",
       "      <td>495.65</td>\n",
       "      <td>495.95</td>\n",
       "      <td>522.00</td>\n",
       "      <td>495.90</td>\n",
       "      <td>522.00</td>\n",
       "      <td>517.90</td>\n",
       "      <td>514.90</td>\n",
       "      <td>110264</td>\n",
       "      <td>5.677483e+07</td>\n",
       "      <td>3648</td>\n",
       "      <td>68819</td>\n",
       "      <td>62.41</td>\n",
       "      <td>5</td>\n",
       "      <td>2017</td>\n",
       "      <td>517.900000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>Slight or No change</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>MINDAIND</td>\n",
       "      <td>EQ</td>\n",
       "      <td>2017-05-16</td>\n",
       "      <td>517.90</td>\n",
       "      <td>523.70</td>\n",
       "      <td>568.00</td>\n",
       "      <td>500.10</td>\n",
       "      <td>551.00</td>\n",
       "      <td>545.60</td>\n",
       "      <td>543.53</td>\n",
       "      <td>1185514</td>\n",
       "      <td>6.443668e+08</td>\n",
       "      <td>28000</td>\n",
       "      <td>220287</td>\n",
       "      <td>18.58</td>\n",
       "      <td>5</td>\n",
       "      <td>2017</td>\n",
       "      <td>543.242873</td>\n",
       "      <td>0.053485</td>\n",
       "      <td>Slight or No change</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>MINDAIND</td>\n",
       "      <td>EQ</td>\n",
       "      <td>2017-05-17</td>\n",
       "      <td>545.60</td>\n",
       "      <td>555.00</td>\n",
       "      <td>571.00</td>\n",
       "      <td>541.15</td>\n",
       "      <td>559.00</td>\n",
       "      <td>560.10</td>\n",
       "      <td>558.38</td>\n",
       "      <td>465042</td>\n",
       "      <td>2.596686e+08</td>\n",
       "      <td>14208</td>\n",
       "      <td>181463</td>\n",
       "      <td>39.02</td>\n",
       "      <td>5</td>\n",
       "      <td>2017</td>\n",
       "      <td>547.694931</td>\n",
       "      <td>0.026576</td>\n",
       "      <td>Slight or No change</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>MINDAIND</td>\n",
       "      <td>EQ</td>\n",
       "      <td>2017-05-18</td>\n",
       "      <td>560.10</td>\n",
       "      <td>556.95</td>\n",
       "      <td>558.00</td>\n",
       "      <td>542.20</td>\n",
       "      <td>547.00</td>\n",
       "      <td>545.25</td>\n",
       "      <td>551.99</td>\n",
       "      <td>90333</td>\n",
       "      <td>4.986314e+07</td>\n",
       "      <td>3951</td>\n",
       "      <td>47989</td>\n",
       "      <td>53.12</td>\n",
       "      <td>5</td>\n",
       "      <td>2017</td>\n",
       "      <td>547.575623</td>\n",
       "      <td>-0.026513</td>\n",
       "      <td>Slight or No change</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>MINDAIND</td>\n",
       "      <td>EQ</td>\n",
       "      <td>2017-05-19</td>\n",
       "      <td>545.25</td>\n",
       "      <td>549.70</td>\n",
       "      <td>588.80</td>\n",
       "      <td>547.00</td>\n",
       "      <td>560.00</td>\n",
       "      <td>560.80</td>\n",
       "      <td>568.56</td>\n",
       "      <td>371398</td>\n",
       "      <td>2.111632e+08</td>\n",
       "      <td>10466</td>\n",
       "      <td>184909</td>\n",
       "      <td>49.79</td>\n",
       "      <td>5</td>\n",
       "      <td>2017</td>\n",
       "      <td>549.785474</td>\n",
       "      <td>0.028519</td>\n",
       "      <td>Slight or No change</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>490</th>\n",
       "      <td>MINDAIND</td>\n",
       "      <td>EQ</td>\n",
       "      <td>2019-05-07</td>\n",
       "      <td>351.60</td>\n",
       "      <td>354.90</td>\n",
       "      <td>354.90</td>\n",
       "      <td>346.00</td>\n",
       "      <td>346.35</td>\n",
       "      <td>347.05</td>\n",
       "      <td>348.75</td>\n",
       "      <td>76556</td>\n",
       "      <td>2.669909e+07</td>\n",
       "      <td>6056</td>\n",
       "      <td>53021</td>\n",
       "      <td>69.26</td>\n",
       "      <td>5</td>\n",
       "      <td>2019</td>\n",
       "      <td>642.942796</td>\n",
       "      <td>-0.012941</td>\n",
       "      <td>Slight or No change</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>491</th>\n",
       "      <td>MINDAIND</td>\n",
       "      <td>EQ</td>\n",
       "      <td>2019-05-08</td>\n",
       "      <td>347.05</td>\n",
       "      <td>350.00</td>\n",
       "      <td>352.00</td>\n",
       "      <td>346.00</td>\n",
       "      <td>350.00</td>\n",
       "      <td>350.35</td>\n",
       "      <td>349.79</td>\n",
       "      <td>41823</td>\n",
       "      <td>1.462933e+07</td>\n",
       "      <td>1764</td>\n",
       "      <td>25157</td>\n",
       "      <td>60.15</td>\n",
       "      <td>5</td>\n",
       "      <td>2019</td>\n",
       "      <td>642.782646</td>\n",
       "      <td>0.009509</td>\n",
       "      <td>Slight or No change</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>492</th>\n",
       "      <td>MINDAIND</td>\n",
       "      <td>EQ</td>\n",
       "      <td>2019-05-09</td>\n",
       "      <td>350.35</td>\n",
       "      <td>352.00</td>\n",
       "      <td>352.30</td>\n",
       "      <td>340.15</td>\n",
       "      <td>342.95</td>\n",
       "      <td>342.00</td>\n",
       "      <td>345.22</td>\n",
       "      <td>67267</td>\n",
       "      <td>2.322195e+07</td>\n",
       "      <td>2327</td>\n",
       "      <td>48953</td>\n",
       "      <td>72.77</td>\n",
       "      <td>5</td>\n",
       "      <td>2019</td>\n",
       "      <td>642.518087</td>\n",
       "      <td>-0.023833</td>\n",
       "      <td>Slight or No change</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>493</th>\n",
       "      <td>MINDAIND</td>\n",
       "      <td>EQ</td>\n",
       "      <td>2019-05-10</td>\n",
       "      <td>342.00</td>\n",
       "      <td>344.75</td>\n",
       "      <td>349.90</td>\n",
       "      <td>340.00</td>\n",
       "      <td>347.00</td>\n",
       "      <td>345.15</td>\n",
       "      <td>345.18</td>\n",
       "      <td>88441</td>\n",
       "      <td>3.052768e+07</td>\n",
       "      <td>3123</td>\n",
       "      <td>47078</td>\n",
       "      <td>53.23</td>\n",
       "      <td>5</td>\n",
       "      <td>2019</td>\n",
       "      <td>642.174597</td>\n",
       "      <td>0.009211</td>\n",
       "      <td>Slight or No change</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>494</th>\n",
       "      <td>MINDAIND</td>\n",
       "      <td>EQ</td>\n",
       "      <td>2019-05-13</td>\n",
       "      <td>345.15</td>\n",
       "      <td>350.00</td>\n",
       "      <td>354.95</td>\n",
       "      <td>340.50</td>\n",
       "      <td>347.50</td>\n",
       "      <td>344.95</td>\n",
       "      <td>345.70</td>\n",
       "      <td>245445</td>\n",
       "      <td>8.485105e+07</td>\n",
       "      <td>11109</td>\n",
       "      <td>155350</td>\n",
       "      <td>63.29</td>\n",
       "      <td>5</td>\n",
       "      <td>2019</td>\n",
       "      <td>641.224835</td>\n",
       "      <td>-0.000579</td>\n",
       "      <td>Slight or No change</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>494 rows × 20 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Symbol Series       Date  Prev Close  Open Price  High Price  \\\n",
       "0    MINDAIND     EQ 2017-05-15      495.65      495.95      522.00   \n",
       "1    MINDAIND     EQ 2017-05-16      517.90      523.70      568.00   \n",
       "2    MINDAIND     EQ 2017-05-17      545.60      555.00      571.00   \n",
       "3    MINDAIND     EQ 2017-05-18      560.10      556.95      558.00   \n",
       "4    MINDAIND     EQ 2017-05-19      545.25      549.70      588.80   \n",
       "..        ...    ...        ...         ...         ...         ...   \n",
       "490  MINDAIND     EQ 2019-05-07      351.60      354.90      354.90   \n",
       "491  MINDAIND     EQ 2019-05-08      347.05      350.00      352.00   \n",
       "492  MINDAIND     EQ 2019-05-09      350.35      352.00      352.30   \n",
       "493  MINDAIND     EQ 2019-05-10      342.00      344.75      349.90   \n",
       "494  MINDAIND     EQ 2019-05-13      345.15      350.00      354.95   \n",
       "\n",
       "     Low Price  Last Price  Close Price  Average Price  Total Traded Quantity  \\\n",
       "0       495.90      522.00       517.90         514.90                 110264   \n",
       "1       500.10      551.00       545.60         543.53                1185514   \n",
       "2       541.15      559.00       560.10         558.38                 465042   \n",
       "3       542.20      547.00       545.25         551.99                  90333   \n",
       "4       547.00      560.00       560.80         568.56                 371398   \n",
       "..         ...         ...          ...            ...                    ...   \n",
       "490     346.00      346.35       347.05         348.75                  76556   \n",
       "491     346.00      350.00       350.35         349.79                  41823   \n",
       "492     340.15      342.95       342.00         345.22                  67267   \n",
       "493     340.00      347.00       345.15         345.18                  88441   \n",
       "494     340.50      347.50       344.95         345.70                 245445   \n",
       "\n",
       "         Turnover  No. of Trades  Deliverable Qty  % Dly Qt to Traded Qty  \\\n",
       "0    5.677483e+07           3648            68819                   62.41   \n",
       "1    6.443668e+08          28000           220287                   18.58   \n",
       "2    2.596686e+08          14208           181463                   39.02   \n",
       "3    4.986314e+07           3951            47989                   53.12   \n",
       "4    2.111632e+08          10466           184909                   49.79   \n",
       "..            ...            ...              ...                     ...   \n",
       "490  2.669909e+07           6056            53021                   69.26   \n",
       "491  1.462933e+07           1764            25157                   60.15   \n",
       "492  2.322195e+07           2327            48953                   72.77   \n",
       "493  3.052768e+07           3123            47078                   53.23   \n",
       "494  8.485105e+07          11109           155350                   63.29   \n",
       "\n",
       "     Month  Year        VWAP  Day_Perc_Change                Trend  \n",
       "0        5  2017  517.900000         0.000000  Slight or No change  \n",
       "1        5  2017  543.242873         0.053485  Slight or No change  \n",
       "2        5  2017  547.694931         0.026576  Slight or No change  \n",
       "3        5  2017  547.575623        -0.026513  Slight or No change  \n",
       "4        5  2017  549.785474         0.028519  Slight or No change  \n",
       "..     ...   ...         ...              ...                  ...  \n",
       "490      5  2019  642.942796        -0.012941  Slight or No change  \n",
       "491      5  2019  642.782646         0.009509  Slight or No change  \n",
       "492      5  2019  642.518087        -0.023833  Slight or No change  \n",
       "493      5  2019  642.174597         0.009211  Slight or No change  \n",
       "494      5  2019  641.224835        -0.000579  Slight or No change  \n",
       "\n",
       "[494 rows x 20 columns]"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "if ((clean_data['Day_Perc_Change'] >= -0.5) & (clean_data['Day_Perc_Change'] <= 0.5)).all():\n",
    "    clean_data['Trend'] = 'Slight or No change'\n",
    "elif ((clean_data['Day_Perc_Change'] >= 0.5) & (clean_data['Day_Perc_Change'] <= 1)).all():\n",
    "    clean_data['Trend'] = 'Slight positive'\n",
    "elif ((clean_data['Day_Perc_Change'] <= -0.5) & (clean_data['Day_Perc_Change'] >= -1)).all():\n",
    "    clean_data['Trend'] = 'Slight negative'\n",
    "elif ((clean_data['Day_Perc_Change'] >= 1) & (clean_data['Day_Perc_Change'] <= 3)).all():\n",
    "    clean_data['Trend'] = 'Positive' \n",
    "elif ((clean_data['Day_Perc_Change'] <= -1) & (clean_data['Day_Perc_Change'] >= -3)).all():\n",
    "    clean_data['Trend'] = 'Negative'\n",
    "elif ((clean_data['Day_Perc_Change'] >= 3) & (clean_data['Day_Perc_Change'] <= 7)).all():\n",
    "    clean_data['Trend'] = 'Among top gainers'\n",
    "elif ((clean_data['Day_Perc_Change'] <= -3) & (clean_data['Day_Perc_Change'] >= -7)).all():\n",
    "    clean_data['Trend'] = 'Among top losers'\n",
    "elif (clean_data['Day_Perc_Change'] > 7).all():\n",
    "    clean_data['Trend'] = 'Bull run' \n",
    "elif (clean_data['Day_Perc_Change'] < -7).all():\n",
    "    clean_data['Trend'] = 'Bear drop' \n",
    "clean_data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Query 1.8\n",
    "\n",
    "Find the average and median values of the column 'Total Traded Quantity' for each of the types of 'Trend'. {Hint : use 'groupby()' on the 'Trend' column and then calculate the average and median values of the column 'Total Traded Quantity'}\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Trend\n",
       "Slight or No change    104944.0\n",
       "Name: Total Traded Quantity, dtype: float64"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "clean_data.groupby(clean_data.Trend).mean()['Total Traded Quantity']\n",
    "clean_data.groupby(clean_data.Trend).median()['Total Traded Quantity']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Query 1.9\n",
    "\n",
    "SAVE the dataframe with the additional columns computed as a csv file week2.csv. In Module 2, you are going to get familiar with matplotlib, the python module which is used to visualize data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "clean_data.to_csv('week2.csv')"
   ]
  },
  {
   "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.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}