Module 1- Pandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Module 1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------#"
   ]
  },
  {
   "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": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np \n",
    "import pandas as pd\n",
    "from range_key_dict import RangeKeyDict\n",
    "import csv as csv\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(\"Datasets/MID_CAP/GMRINFRA.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['EQ'], dtype=object)"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.Series.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "EQ    494\n",
       "Name: Series, dtype: int64"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.Series.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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",
       "      <th>0</th>\n",
       "      <td>GMRINFRA</td>\n",
       "      <td>EQ</td>\n",
       "      <td>15-May-2017</td>\n",
       "      <td>16.60</td>\n",
       "      <td>16.70</td>\n",
       "      <td>17.70</td>\n",
       "      <td>16.70</td>\n",
       "      <td>17.60</td>\n",
       "      <td>17.55</td>\n",
       "      <td>17.15</td>\n",
       "      <td>51996656</td>\n",
       "      <td>8.916579e+08</td>\n",
       "      <td>11922</td>\n",
       "      <td>29960644</td>\n",
       "      <td>57.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>GMRINFRA</td>\n",
       "      <td>EQ</td>\n",
       "      <td>16-May-2017</td>\n",
       "      <td>17.55</td>\n",
       "      <td>17.60</td>\n",
       "      <td>17.70</td>\n",
       "      <td>17.05</td>\n",
       "      <td>17.35</td>\n",
       "      <td>17.40</td>\n",
       "      <td>17.38</td>\n",
       "      <td>18797177</td>\n",
       "      <td>3.267238e+08</td>\n",
       "      <td>10494</td>\n",
       "      <td>5716554</td>\n",
       "      <td>30.41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>GMRINFRA</td>\n",
       "      <td>EQ</td>\n",
       "      <td>17-May-2017</td>\n",
       "      <td>17.40</td>\n",
       "      <td>17.40</td>\n",
       "      <td>17.70</td>\n",
       "      <td>17.25</td>\n",
       "      <td>17.25</td>\n",
       "      <td>17.30</td>\n",
       "      <td>17.40</td>\n",
       "      <td>14686157</td>\n",
       "      <td>2.555702e+08</td>\n",
       "      <td>8448</td>\n",
       "      <td>4250771</td>\n",
       "      <td>28.94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>GMRINFRA</td>\n",
       "      <td>EQ</td>\n",
       "      <td>18-May-2017</td>\n",
       "      <td>17.30</td>\n",
       "      <td>17.15</td>\n",
       "      <td>17.25</td>\n",
       "      <td>16.65</td>\n",
       "      <td>16.75</td>\n",
       "      <td>16.75</td>\n",
       "      <td>16.94</td>\n",
       "      <td>17050095</td>\n",
       "      <td>2.889004e+08</td>\n",
       "      <td>9429</td>\n",
       "      <td>5476423</td>\n",
       "      <td>32.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>GMRINFRA</td>\n",
       "      <td>EQ</td>\n",
       "      <td>19-May-2017</td>\n",
       "      <td>16.75</td>\n",
       "      <td>16.85</td>\n",
       "      <td>17.35</td>\n",
       "      <td>16.60</td>\n",
       "      <td>17.00</td>\n",
       "      <td>16.95</td>\n",
       "      <td>16.96</td>\n",
       "      <td>22080313</td>\n",
       "      <td>3.744406e+08</td>\n",
       "      <td>9355</td>\n",
       "      <td>2110450</td>\n",
       "      <td>9.56</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Symbol Series         Date  Prev Close  Open Price  High Price  \\\n",
       "0  GMRINFRA     EQ  15-May-2017       16.60       16.70       17.70   \n",
       "1  GMRINFRA     EQ  16-May-2017       17.55       17.60       17.70   \n",
       "2  GMRINFRA     EQ  17-May-2017       17.40       17.40       17.70   \n",
       "3  GMRINFRA     EQ  18-May-2017       17.30       17.15       17.25   \n",
       "4  GMRINFRA     EQ  19-May-2017       16.75       16.85       17.35   \n",
       "\n",
       "   Low Price  Last Price  Close Price  Average Price  Total Traded Quantity  \\\n",
       "0      16.70       17.60        17.55          17.15               51996656   \n",
       "1      17.05       17.35        17.40          17.38               18797177   \n",
       "2      17.25       17.25        17.30          17.40               14686157   \n",
       "3      16.65       16.75        16.75          16.94               17050095   \n",
       "4      16.60       17.00        16.95          16.96               22080313   \n",
       "\n",
       "       Turnover  No. of Trades  Deliverable Qty  % Dly Qt to Traded Qty  \n",
       "0  8.916579e+08          11922         29960644                   57.62  \n",
       "1  3.267238e+08          10494          5716554                   30.41  \n",
       "2  2.555702e+08           8448          4250771                   28.94  \n",
       "3  2.889004e+08           9429          5476423                   32.12  \n",
       "4  3.744406e+08           9355          2110450                    9.56  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "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",
       "      <th>489</th>\n",
       "      <td>GMRINFRA</td>\n",
       "      <td>EQ</td>\n",
       "      <td>07-May-2019</td>\n",
       "      <td>16.40</td>\n",
       "      <td>16.50</td>\n",
       "      <td>16.85</td>\n",
       "      <td>15.80</td>\n",
       "      <td>16.1</td>\n",
       "      <td>16.00</td>\n",
       "      <td>16.32</td>\n",
       "      <td>15954295</td>\n",
       "      <td>260340687.3</td>\n",
       "      <td>5815</td>\n",
       "      <td>5676609</td>\n",
       "      <td>35.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>490</th>\n",
       "      <td>GMRINFRA</td>\n",
       "      <td>EQ</td>\n",
       "      <td>08-May-2019</td>\n",
       "      <td>16.00</td>\n",
       "      <td>16.00</td>\n",
       "      <td>16.15</td>\n",
       "      <td>15.70</td>\n",
       "      <td>15.9</td>\n",
       "      <td>15.85</td>\n",
       "      <td>15.93</td>\n",
       "      <td>9537037</td>\n",
       "      <td>151898215.9</td>\n",
       "      <td>4605</td>\n",
       "      <td>2012032</td>\n",
       "      <td>21.10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>491</th>\n",
       "      <td>GMRINFRA</td>\n",
       "      <td>EQ</td>\n",
       "      <td>09-May-2019</td>\n",
       "      <td>15.85</td>\n",
       "      <td>15.85</td>\n",
       "      <td>16.25</td>\n",
       "      <td>15.65</td>\n",
       "      <td>16.0</td>\n",
       "      <td>16.05</td>\n",
       "      <td>15.95</td>\n",
       "      <td>9317526</td>\n",
       "      <td>148600851.5</td>\n",
       "      <td>4191</td>\n",
       "      <td>3356754</td>\n",
       "      <td>36.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>492</th>\n",
       "      <td>GMRINFRA</td>\n",
       "      <td>EQ</td>\n",
       "      <td>10-May-2019</td>\n",
       "      <td>16.05</td>\n",
       "      <td>16.00</td>\n",
       "      <td>16.30</td>\n",
       "      <td>15.85</td>\n",
       "      <td>16.0</td>\n",
       "      <td>16.10</td>\n",
       "      <td>16.10</td>\n",
       "      <td>7705137</td>\n",
       "      <td>124014438.6</td>\n",
       "      <td>3967</td>\n",
       "      <td>1768866</td>\n",
       "      <td>22.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>493</th>\n",
       "      <td>GMRINFRA</td>\n",
       "      <td>EQ</td>\n",
       "      <td>13-May-2019</td>\n",
       "      <td>16.10</td>\n",
       "      <td>16.10</td>\n",
       "      <td>16.10</td>\n",
       "      <td>15.15</td>\n",
       "      <td>15.3</td>\n",
       "      <td>15.30</td>\n",
       "      <td>15.60</td>\n",
       "      <td>11409435</td>\n",
       "      <td>178008565.3</td>\n",
       "      <td>5108</td>\n",
       "      <td>2176806</td>\n",
       "      <td>19.08</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Symbol Series         Date  Prev Close  Open Price  High Price  \\\n",
       "489  GMRINFRA     EQ  07-May-2019       16.40       16.50       16.85   \n",
       "490  GMRINFRA     EQ  08-May-2019       16.00       16.00       16.15   \n",
       "491  GMRINFRA     EQ  09-May-2019       15.85       15.85       16.25   \n",
       "492  GMRINFRA     EQ  10-May-2019       16.05       16.00       16.30   \n",
       "493  GMRINFRA     EQ  13-May-2019       16.10       16.10       16.10   \n",
       "\n",
       "     Low Price  Last Price  Close Price  Average Price  Total Traded Quantity  \\\n",
       "489      15.80        16.1        16.00          16.32               15954295   \n",
       "490      15.70        15.9        15.85          15.93                9537037   \n",
       "491      15.65        16.0        16.05          15.95                9317526   \n",
       "492      15.85        16.0        16.10          16.10                7705137   \n",
       "493      15.15        15.3        15.30          15.60               11409435   \n",
       "\n",
       "        Turnover  No. of Trades  Deliverable Qty  % Dly Qt to Traded Qty  \n",
       "489  260340687.3           5815          5676609                   35.58  \n",
       "490  151898215.9           4605          2012032                   21.10  \n",
       "491  148600851.5           4191          3356754                   36.03  \n",
       "492  124014438.6           3967          1768866                   22.96  \n",
       "493  178008565.3           5108          2176806                   19.08  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.tail()"
   ]
  },
  {
   "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>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",
       "      <th>count</th>\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>4.940000e+02</td>\n",
       "      <td>494.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>17.669737</td>\n",
       "      <td>17.714474</td>\n",
       "      <td>18.122874</td>\n",
       "      <td>17.281883</td>\n",
       "      <td>17.669636</td>\n",
       "      <td>17.667105</td>\n",
       "      <td>17.703421</td>\n",
       "      <td>3.445313e+07</td>\n",
       "      <td>6.358151e+08</td>\n",
       "      <td>17167.698381</td>\n",
       "      <td>8.475346e+06</td>\n",
       "      <td>24.544575</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>1.754294</td>\n",
       "      <td>1.774066</td>\n",
       "      <td>1.879006</td>\n",
       "      <td>1.695158</td>\n",
       "      <td>1.753287</td>\n",
       "      <td>1.756875</td>\n",
       "      <td>1.775291</td>\n",
       "      <td>3.150286e+07</td>\n",
       "      <td>6.448500e+08</td>\n",
       "      <td>15217.628326</td>\n",
       "      <td>8.194839e+06</td>\n",
       "      <td>9.551338</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>14.550000</td>\n",
       "      <td>14.400000</td>\n",
       "      <td>14.850000</td>\n",
       "      <td>12.950000</td>\n",
       "      <td>14.600000</td>\n",
       "      <td>14.550000</td>\n",
       "      <td>14.420000</td>\n",
       "      <td>9.224280e+05</td>\n",
       "      <td>1.532302e+07</td>\n",
       "      <td>1992.000000</td>\n",
       "      <td>5.279650e+05</td>\n",
       "      <td>7.450000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>16.350000</td>\n",
       "      <td>16.350000</td>\n",
       "      <td>16.650000</td>\n",
       "      <td>16.012500</td>\n",
       "      <td>16.300000</td>\n",
       "      <td>16.312500</td>\n",
       "      <td>16.360000</td>\n",
       "      <td>1.717144e+07</td>\n",
       "      <td>2.914811e+08</td>\n",
       "      <td>8055.000000</td>\n",
       "      <td>3.492949e+06</td>\n",
       "      <td>17.915000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>17.400000</td>\n",
       "      <td>17.425000</td>\n",
       "      <td>17.750000</td>\n",
       "      <td>17.100000</td>\n",
       "      <td>17.375000</td>\n",
       "      <td>17.400000</td>\n",
       "      <td>17.415000</td>\n",
       "      <td>2.574128e+07</td>\n",
       "      <td>4.424640e+08</td>\n",
       "      <td>12620.000000</td>\n",
       "      <td>5.696470e+06</td>\n",
       "      <td>22.920000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>18.650000</td>\n",
       "      <td>18.650000</td>\n",
       "      <td>19.150000</td>\n",
       "      <td>18.187500</td>\n",
       "      <td>18.700000</td>\n",
       "      <td>18.650000</td>\n",
       "      <td>18.715000</td>\n",
       "      <td>3.991203e+07</td>\n",
       "      <td>7.098195e+08</td>\n",
       "      <td>20736.000000</td>\n",
       "      <td>1.019290e+07</td>\n",
       "      <td>28.930000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>24.100000</td>\n",
       "      <td>24.400000</td>\n",
       "      <td>25.050000</td>\n",
       "      <td>23.250000</td>\n",
       "      <td>24.000000</td>\n",
       "      <td>24.100000</td>\n",
       "      <td>23.880000</td>\n",
       "      <td>3.180001e+08</td>\n",
       "      <td>5.692564e+09</td>\n",
       "      <td>147752.000000</td>\n",
       "      <td>5.677204e+07</td>\n",
       "      <td>67.780000</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    17.669737   17.714474   18.122874   17.281883   17.669636   \n",
       "std      1.754294    1.774066    1.879006    1.695158    1.753287   \n",
       "min     14.550000   14.400000   14.850000   12.950000   14.600000   \n",
       "25%     16.350000   16.350000   16.650000   16.012500   16.300000   \n",
       "50%     17.400000   17.425000   17.750000   17.100000   17.375000   \n",
       "75%     18.650000   18.650000   19.150000   18.187500   18.700000   \n",
       "max     24.100000   24.400000   25.050000   23.250000   24.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     17.667105      17.703421           3.445313e+07  6.358151e+08   \n",
       "std       1.756875       1.775291           3.150286e+07  6.448500e+08   \n",
       "min      14.550000      14.420000           9.224280e+05  1.532302e+07   \n",
       "25%      16.312500      16.360000           1.717144e+07  2.914811e+08   \n",
       "50%      17.400000      17.415000           2.574128e+07  4.424640e+08   \n",
       "75%      18.650000      18.715000           3.991203e+07  7.098195e+08   \n",
       "max      24.100000      23.880000           3.180001e+08  5.692564e+09   \n",
       "\n",
       "       No. of Trades  Deliverable Qty  % Dly Qt to Traded Qty  \n",
       "count     494.000000     4.940000e+02              494.000000  \n",
       "mean    17167.698381     8.475346e+06               24.544575  \n",
       "std     15217.628326     8.194839e+06                9.551338  \n",
       "min      1992.000000     5.279650e+05                7.450000  \n",
       "25%      8055.000000     3.492949e+06               17.915000  \n",
       "50%     12620.000000     5.696470e+06               22.920000  \n",
       "75%     20736.000000     1.019290e+07               28.930000  \n",
       "max    147752.000000     5.677204e+07               67.780000  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.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": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "16.713"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Mean for Last 90 days\n",
    "round(df['Close Price'].tail(90).mean(axis=0),3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "20.0"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Max for Last 90 days\n",
    "df['Close Price'].tail(90).max(axis=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "14.6"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Min for Last 90 days\n",
    "df['Close Price'].tail(90).min(axis=0)"
   ]
  },
  {
   "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": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Symbol                     object\n",
       "Series                     object\n",
       "Date                       object\n",
       "Prev Close                float64\n",
       "Open Price                float64\n",
       "High Price                float64\n",
       "Low Price                 float64\n",
       "Last Price                float64\n",
       "Close Price               float64\n",
       "Average Price             float64\n",
       "Total Traded Quantity       int64\n",
       "Turnover                  float64\n",
       "No. of Trades               int64\n",
       "Deliverable Qty             int64\n",
       "% Dly Qt to Traded Qty    float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Changing Date's Data Type\n",
    "df['Date'] = df['Date'].astype('datetime64[ns]')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Symbol                            object\n",
       "Series                            object\n",
       "Date                      datetime64[ns]\n",
       "Prev Close                       float64\n",
       "Open Price                       float64\n",
       "High Price                       float64\n",
       "Low Price                        float64\n",
       "Last Price                       float64\n",
       "Close Price                      float64\n",
       "Average Price                    float64\n",
       "Total Traded Quantity              int64\n",
       "Turnover                         float64\n",
       "No. of Trades                      int64\n",
       "Deliverable Qty                    int64\n",
       "% Dly Qt to Traded Qty           float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "-728 days +00:00:00\n"
     ]
    }
   ],
   "source": [
    "max_date = df[\"Date\"].max(axis = 0)\n",
    "min_date = df[\"Date\"].min(axis = 0)\n",
    "result_q3 = min_date - max_date\n",
    "print(result_q3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Query 1.4\n",
    "\n",
    "In a separate array , calculate the monthwise VWAP (Volume Weighted Average Price ) of the stock.\n",
    "( VWAP = sum(price*volume)/sum(volume) )\n",
    "To know more about VWAP , visit - VWAP definition\n",
    "{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": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = df.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2['VP'] = df2['Close Price']+df2['High Price']+df2['Low Price']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2['VP'] = df2['VP']/3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2['VP'] = df2['VP']*df2['Total Traded Quantity']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2['Year'] = pd.DatetimeIndex(df2['Date']).year\n",
    "df2['Month'] = pd.DatetimeIndex(df2['Date']).month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = df2.groupby(['Month','Year']).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2['VWAP'] = df2['VP']/df2['Total Traded Quantity']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = df2[['Total Traded Quantity','VWAP']]"
   ]
  },
  {
   "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>Total Traded Quantity</th>\n",
       "      <th>VWAP</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Month</th>\n",
       "      <th>Year</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">1</th>\n",
       "      <th>2018</th>\n",
       "      <td>1721458737</td>\n",
       "      <td>22.622076</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019</th>\n",
       "      <td>290262233</td>\n",
       "      <td>16.019089</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">2</th>\n",
       "      <th>2018</th>\n",
       "      <td>980268114</td>\n",
       "      <td>19.443823</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019</th>\n",
       "      <td>548122506</td>\n",
       "      <td>15.113983</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">3</th>\n",
       "      <th>2018</th>\n",
       "      <td>605275161</td>\n",
       "      <td>17.598367</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019</th>\n",
       "      <td>724280427</td>\n",
       "      <td>18.334244</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">4</th>\n",
       "      <th>2018</th>\n",
       "      <td>896502078</td>\n",
       "      <td>19.927740</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019</th>\n",
       "      <td>332164753</td>\n",
       "      <td>18.448896</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">5</th>\n",
       "      <th>2017</th>\n",
       "      <td>310421284</td>\n",
       "      <td>16.102434</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018</th>\n",
       "      <td>507598930</td>\n",
       "      <td>18.332436</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019</th>\n",
       "      <td>82097532</td>\n",
       "      <td>16.182845</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">6</th>\n",
       "      <th>2017</th>\n",
       "      <td>1766454808</td>\n",
       "      <td>18.707522</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018</th>\n",
       "      <td>786817700</td>\n",
       "      <td>16.080805</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">7</th>\n",
       "      <th>2017</th>\n",
       "      <td>900319105</td>\n",
       "      <td>18.666773</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018</th>\n",
       "      <td>817652353</td>\n",
       "      <td>17.574662</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">8</th>\n",
       "      <th>2017</th>\n",
       "      <td>693626626</td>\n",
       "      <td>17.521630</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018</th>\n",
       "      <td>682530442</td>\n",
       "      <td>18.959403</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">9</th>\n",
       "      <th>2017</th>\n",
       "      <td>585943092</td>\n",
       "      <td>17.283002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018</th>\n",
       "      <td>538992932</td>\n",
       "      <td>17.852586</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">10</th>\n",
       "      <th>2017</th>\n",
       "      <td>473130971</td>\n",
       "      <td>17.509132</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018</th>\n",
       "      <td>591143415</td>\n",
       "      <td>16.134160</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">11</th>\n",
       "      <th>2017</th>\n",
       "      <td>627251064</td>\n",
       "      <td>18.094024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018</th>\n",
       "      <td>396595080</td>\n",
       "      <td>16.443618</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">12</th>\n",
       "      <th>2017</th>\n",
       "      <td>895471810</td>\n",
       "      <td>19.469497</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018</th>\n",
       "      <td>265462716</td>\n",
       "      <td>15.750927</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            Total Traded Quantity       VWAP\n",
       "Month Year                                  \n",
       "1     2018             1721458737  22.622076\n",
       "      2019              290262233  16.019089\n",
       "2     2018              980268114  19.443823\n",
       "      2019              548122506  15.113983\n",
       "3     2018              605275161  17.598367\n",
       "      2019              724280427  18.334244\n",
       "4     2018              896502078  19.927740\n",
       "      2019              332164753  18.448896\n",
       "5     2017              310421284  16.102434\n",
       "      2018              507598930  18.332436\n",
       "      2019               82097532  16.182845\n",
       "6     2017             1766454808  18.707522\n",
       "      2018              786817700  16.080805\n",
       "7     2017              900319105  18.666773\n",
       "      2018              817652353  17.574662\n",
       "8     2017              693626626  17.521630\n",
       "      2018              682530442  18.959403\n",
       "9     2017              585943092  17.283002\n",
       "      2018              538992932  17.852586\n",
       "10    2017              473130971  17.509132\n",
       "      2018              591143415  16.134160\n",
       "11    2017              627251064  18.094024\n",
       "      2018              396595080  16.443618\n",
       "12    2017              895471810  19.469497\n",
       "      2018              265462716  15.750927"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Monthwise VWAP\n",
    "df2"
   ]
  },
  {
   "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.\n",
    "Calculate the average price AND the profit/loss percentages over the course of last -\n",
    "1 week, 2 weeks, 1 month, 3 months, 6 months and 1 year.<br>\n",
    "{Note : Profit/Loss percentage between N days is the percentage change between the closing prices of the 2 days }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "def avg_value(days):\n",
    "    req_value_1 = df[df.Date.isin(pd.date_range(end='13/5/2019', periods=days))]\\\n",
    "    ['Close Price'].mean()\n",
    "    print(\"Average for last {0} days is {1}\".format(days,req_value_1))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "def pro_los_value(days):\n",
    "    new_value = df[df.Date.isin(pd.date_range(end='13/5/2019', periods=days))]\\\n",
    "    ['Close Price'].iloc[-1]\n",
    "    old_value = df[df.Date.isin(pd.date_range(end='13/5/2019', periods=days))]\\\n",
    "    ['Close Price'].iloc[0]\n",
    "    req_value_2 = (new_value - old_value)\n",
    "    req_value_2 = req_value_2/old_value\n",
    "    req_value_2 = req_value_2*100\n",
    "    if req_value_2 > 0:\n",
    "        print(\"Profit Percentage over last {0} days is {1}%\".format\\\n",
    "              (days,round(req_value_2,3)))\n",
    "    elif req_value_2 < 0:\n",
    "        print(\"Loss Percentage over last {0} days is {1}%\".format\\\n",
    "              (days,round(abs(req_value_2),3)))\n",
    "    else:\n",
    "        print(\"No Profit - No Loss\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      " 1.Compute Average & Profit/Loss Percentage 0. Exit\n",
      "Enter Choice:  1\n",
      "Enter No of days: 7\n",
      "Average for last 7 days is 15.86\n",
      "Loss Percentage over last 7 days is 4.375%\n",
      "\n",
      " 1.Compute Average & Profit/Loss Percentage 0. Exit\n",
      "Enter Choice:  1\n",
      "Enter No of days: 14\n",
      "Average for last 14 days is 16.17777777777778\n",
      "Loss Percentage over last 14 days is 8.108%\n",
      "\n",
      " 1.Compute Average & Profit/Loss Percentage 0. Exit\n",
      "Enter Choice:  1\n",
      "Enter No of days: 30\n",
      "Average for last 30 days is 16.923529411764708\n",
      "Loss Percentage over last 30 days is 17.742%\n",
      "\n",
      " 1.Compute Average & Profit/Loss Percentage 0. Exit\n",
      "Enter Choice:  1\n",
      "Enter No of days: 90\n",
      "Average for last 90 days is 17.213793103448275\n",
      "Profit Percentage over last 90 days is 4.795%\n",
      "\n",
      " 1.Compute Average & Profit/Loss Percentage 0. Exit\n",
      "Enter Choice:  1\n",
      "Enter No of days: 180\n",
      "Average for last 180 days is 16.51291666666667\n",
      "Loss Percentage over last 180 days is 5.263%\n",
      "\n",
      " 1.Compute Average & Profit/Loss Percentage 0. Exit\n",
      "Enter Choice:  1\n",
      "Enter No of days: 365\n",
      "Average for last 365 days is 16.90548780487805\n",
      "Loss Percentage over last 365 days is 15.47%\n",
      "\n",
      " 1.Compute Average & Profit/Loss Percentage 0. Exit\n",
      "Enter Choice:  0\n"
     ]
    }
   ],
   "source": [
    "while(True):\n",
    "    try:\n",
    "        print(\"\\n 1.Compute Average & Profit/Loss Percentage 0. Exit\")\n",
    "        d = int(input(\"Enter Choice:  \"))\n",
    "        if(d == 1):\n",
    "            c = int(input(\"Enter No of days: \"))\n",
    "            avg_value(c)\n",
    "            pro_los_value(c)\n",
    "        elif(d == 0):\n",
    "            break\n",
    "        else:\n",
    "            print(\"Wrong Choice\")\n",
    "    except ValueError:\n",
    "        print(\"Numbers Only\")\n",
    "        pass"
   ]
  },
  {
   "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": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Day_Perc_Change'] = df['Close Price'].pct_change(periods = 1)\n",
    "df['Day_Perc_Change'] = df['Day_Perc_Change']*100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Calculating Value for 1st Row using Prev Closing Value\n",
    "#No need to drop the row or set it to zero\n",
    "number1 = df.loc[df.index,'Close Price'].iat[0]\n",
    "number2 = df.loc[df.index,'Prev Close'].iat[0]\n",
    "number = (number1 - number2)\n",
    "number = number/number2\n",
    "number = number * 100\n",
    "df['Day_Perc_Change'][0] = number"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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>Day_Perc_Change</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>GMRINFRA</td>\n",
       "      <td>EQ</td>\n",
       "      <td>2017-05-15</td>\n",
       "      <td>16.60</td>\n",
       "      <td>16.70</td>\n",
       "      <td>17.70</td>\n",
       "      <td>16.70</td>\n",
       "      <td>17.60</td>\n",
       "      <td>17.55</td>\n",
       "      <td>17.15</td>\n",
       "      <td>51996656</td>\n",
       "      <td>8.916579e+08</td>\n",
       "      <td>11922</td>\n",
       "      <td>29960644</td>\n",
       "      <td>57.62</td>\n",
       "      <td>5.722892</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>GMRINFRA</td>\n",
       "      <td>EQ</td>\n",
       "      <td>2017-05-16</td>\n",
       "      <td>17.55</td>\n",
       "      <td>17.60</td>\n",
       "      <td>17.70</td>\n",
       "      <td>17.05</td>\n",
       "      <td>17.35</td>\n",
       "      <td>17.40</td>\n",
       "      <td>17.38</td>\n",
       "      <td>18797177</td>\n",
       "      <td>3.267238e+08</td>\n",
       "      <td>10494</td>\n",
       "      <td>5716554</td>\n",
       "      <td>30.41</td>\n",
       "      <td>-0.854701</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>GMRINFRA</td>\n",
       "      <td>EQ</td>\n",
       "      <td>2017-05-17</td>\n",
       "      <td>17.40</td>\n",
       "      <td>17.40</td>\n",
       "      <td>17.70</td>\n",
       "      <td>17.25</td>\n",
       "      <td>17.25</td>\n",
       "      <td>17.30</td>\n",
       "      <td>17.40</td>\n",
       "      <td>14686157</td>\n",
       "      <td>2.555702e+08</td>\n",
       "      <td>8448</td>\n",
       "      <td>4250771</td>\n",
       "      <td>28.94</td>\n",
       "      <td>-0.574713</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>GMRINFRA</td>\n",
       "      <td>EQ</td>\n",
       "      <td>2017-05-18</td>\n",
       "      <td>17.30</td>\n",
       "      <td>17.15</td>\n",
       "      <td>17.25</td>\n",
       "      <td>16.65</td>\n",
       "      <td>16.75</td>\n",
       "      <td>16.75</td>\n",
       "      <td>16.94</td>\n",
       "      <td>17050095</td>\n",
       "      <td>2.889004e+08</td>\n",
       "      <td>9429</td>\n",
       "      <td>5476423</td>\n",
       "      <td>32.12</td>\n",
       "      <td>-3.179191</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>GMRINFRA</td>\n",
       "      <td>EQ</td>\n",
       "      <td>2017-05-19</td>\n",
       "      <td>16.75</td>\n",
       "      <td>16.85</td>\n",
       "      <td>17.35</td>\n",
       "      <td>16.60</td>\n",
       "      <td>17.00</td>\n",
       "      <td>16.95</td>\n",
       "      <td>16.96</td>\n",
       "      <td>22080313</td>\n",
       "      <td>3.744406e+08</td>\n",
       "      <td>9355</td>\n",
       "      <td>2110450</td>\n",
       "      <td>9.56</td>\n",
       "      <td>1.194030</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Symbol Series       Date  Prev Close  Open Price  High Price  Low Price  \\\n",
       "0  GMRINFRA     EQ 2017-05-15       16.60       16.70       17.70      16.70   \n",
       "1  GMRINFRA     EQ 2017-05-16       17.55       17.60       17.70      17.05   \n",
       "2  GMRINFRA     EQ 2017-05-17       17.40       17.40       17.70      17.25   \n",
       "3  GMRINFRA     EQ 2017-05-18       17.30       17.15       17.25      16.65   \n",
       "4  GMRINFRA     EQ 2017-05-19       16.75       16.85       17.35      16.60   \n",
       "\n",
       "   Last Price  Close Price  Average Price  Total Traded Quantity  \\\n",
       "0       17.60        17.55          17.15               51996656   \n",
       "1       17.35        17.40          17.38               18797177   \n",
       "2       17.25        17.30          17.40               14686157   \n",
       "3       16.75        16.75          16.94               17050095   \n",
       "4       17.00        16.95          16.96               22080313   \n",
       "\n",
       "       Turnover  No. of Trades  Deliverable Qty  % Dly Qt to Traded Qty  \\\n",
       "0  8.916579e+08          11922         29960644                   57.62   \n",
       "1  3.267238e+08          10494          5716554                   30.41   \n",
       "2  2.555702e+08           8448          4250771                   28.94   \n",
       "3  2.889004e+08           9429          5476423                   32.12   \n",
       "4  3.744406e+08           9355          2110450                    9.56   \n",
       "\n",
       "   Day_Perc_Change  \n",
       "0         5.722892  \n",
       "1        -0.854701  \n",
       "2        -0.574713  \n",
       "3        -3.179191  \n",
       "4         1.194030  "
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Query 1.7\n",
    "\n",
    "Add another column 'Trend' whose values are:\n",
    "\n",
    "    'Slight or No change' for 'Day_Perc_Change' in between -0.5 and 0.5\n",
    "    'Slight positive' for 'Day_Perc_Change' in between 0.5 and 1\n",
    "    'Slight negative' for 'Day_Perc_Change' in between -0.5 and -1\n",
    "    'Positive' for 'Day_Perc_Change' in between 1 and 3\n",
    "    'Negative' for 'Day_Perc_Change' in between -1 and -3\n",
    "    'Among top gainers' for 'Day_Perc_Change' in between 3 and 7\n",
    "    'Among top losers' for 'Day_Perc_Change' in between -3 and -7\n",
    "    'Bull run' for 'Day_Perc_Change' >7\n",
    "    'Bear drop' for 'Day_Perc_Change' <-7\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "rkd = RangeKeyDict({\n",
    "        (-0.5,0.5): 'Slight or No change',\n",
    "        (0.5,1): 'Slight positive',\n",
    "        (-1,-0.5): 'Slight negative',\n",
    "        (1,3):'Positive',\n",
    "        (-3,-1):'Negative',\n",
    "        (3,7):'Among top gainers',\n",
    "        (-7,-3):'Among top losers',\n",
    "        (-10000, -7):'Bull run',\n",
    "        (7, 10000 ):'Bear drop',\n",
    "    })"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"Trend\"] = np.nan\n",
    "for xi in range(0,494):\n",
    "    df['Trend'][xi] = rkd[df['Day_Perc_Change'][xi]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "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>Day_Perc_Change</th>\n",
       "      <th>Trend</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>GMRINFRA</td>\n",
       "      <td>EQ</td>\n",
       "      <td>2017-05-15</td>\n",
       "      <td>16.60</td>\n",
       "      <td>16.70</td>\n",
       "      <td>17.70</td>\n",
       "      <td>16.70</td>\n",
       "      <td>17.60</td>\n",
       "      <td>17.55</td>\n",
       "      <td>17.15</td>\n",
       "      <td>51996656</td>\n",
       "      <td>8.916579e+08</td>\n",
       "      <td>11922</td>\n",
       "      <td>29960644</td>\n",
       "      <td>57.62</td>\n",
       "      <td>5.722892</td>\n",
       "      <td>Among top gainers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>GMRINFRA</td>\n",
       "      <td>EQ</td>\n",
       "      <td>2017-05-16</td>\n",
       "      <td>17.55</td>\n",
       "      <td>17.60</td>\n",
       "      <td>17.70</td>\n",
       "      <td>17.05</td>\n",
       "      <td>17.35</td>\n",
       "      <td>17.40</td>\n",
       "      <td>17.38</td>\n",
       "      <td>18797177</td>\n",
       "      <td>3.267238e+08</td>\n",
       "      <td>10494</td>\n",
       "      <td>5716554</td>\n",
       "      <td>30.41</td>\n",
       "      <td>-0.854701</td>\n",
       "      <td>Slight negative</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>GMRINFRA</td>\n",
       "      <td>EQ</td>\n",
       "      <td>2017-05-17</td>\n",
       "      <td>17.40</td>\n",
       "      <td>17.40</td>\n",
       "      <td>17.70</td>\n",
       "      <td>17.25</td>\n",
       "      <td>17.25</td>\n",
       "      <td>17.30</td>\n",
       "      <td>17.40</td>\n",
       "      <td>14686157</td>\n",
       "      <td>2.555702e+08</td>\n",
       "      <td>8448</td>\n",
       "      <td>4250771</td>\n",
       "      <td>28.94</td>\n",
       "      <td>-0.574713</td>\n",
       "      <td>Slight negative</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>GMRINFRA</td>\n",
       "      <td>EQ</td>\n",
       "      <td>2017-05-18</td>\n",
       "      <td>17.30</td>\n",
       "      <td>17.15</td>\n",
       "      <td>17.25</td>\n",
       "      <td>16.65</td>\n",
       "      <td>16.75</td>\n",
       "      <td>16.75</td>\n",
       "      <td>16.94</td>\n",
       "      <td>17050095</td>\n",
       "      <td>2.889004e+08</td>\n",
       "      <td>9429</td>\n",
       "      <td>5476423</td>\n",
       "      <td>32.12</td>\n",
       "      <td>-3.179191</td>\n",
       "      <td>Among top losers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>GMRINFRA</td>\n",
       "      <td>EQ</td>\n",
       "      <td>2017-05-19</td>\n",
       "      <td>16.75</td>\n",
       "      <td>16.85</td>\n",
       "      <td>17.35</td>\n",
       "      <td>16.60</td>\n",
       "      <td>17.00</td>\n",
       "      <td>16.95</td>\n",
       "      <td>16.96</td>\n",
       "      <td>22080313</td>\n",
       "      <td>3.744406e+08</td>\n",
       "      <td>9355</td>\n",
       "      <td>2110450</td>\n",
       "      <td>9.56</td>\n",
       "      <td>1.194030</td>\n",
       "      <td>Positive</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Symbol Series       Date  Prev Close  Open Price  High Price  Low Price  \\\n",
       "0  GMRINFRA     EQ 2017-05-15       16.60       16.70       17.70      16.70   \n",
       "1  GMRINFRA     EQ 2017-05-16       17.55       17.60       17.70      17.05   \n",
       "2  GMRINFRA     EQ 2017-05-17       17.40       17.40       17.70      17.25   \n",
       "3  GMRINFRA     EQ 2017-05-18       17.30       17.15       17.25      16.65   \n",
       "4  GMRINFRA     EQ 2017-05-19       16.75       16.85       17.35      16.60   \n",
       "\n",
       "   Last Price  Close Price  Average Price  Total Traded Quantity  \\\n",
       "0       17.60        17.55          17.15               51996656   \n",
       "1       17.35        17.40          17.38               18797177   \n",
       "2       17.25        17.30          17.40               14686157   \n",
       "3       16.75        16.75          16.94               17050095   \n",
       "4       17.00        16.95          16.96               22080313   \n",
       "\n",
       "       Turnover  No. of Trades  Deliverable Qty  % Dly Qt to Traded Qty  \\\n",
       "0  8.916579e+08          11922         29960644                   57.62   \n",
       "1  3.267238e+08          10494          5716554                   30.41   \n",
       "2  2.555702e+08           8448          4250771                   28.94   \n",
       "3  2.889004e+08           9429          5476423                   32.12   \n",
       "4  3.744406e+08           9355          2110450                    9.56   \n",
       "\n",
       "   Day_Perc_Change              Trend  \n",
       "0         5.722892  Among top gainers  \n",
       "1        -0.854701    Slight negative  \n",
       "2        -0.574713    Slight negative  \n",
       "3        -3.179191   Among top losers  \n",
       "4         1.194030           Positive  "
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "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'.\n",
    "{Hint : use 'groupby()' on the 'Trend' column and then calculate the average and median values of the column 'Total Traded Quantity'} "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "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>Total Traded Quantity</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Trend</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Among top gainers</th>\n",
       "      <td>5.382153e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Among top losers</th>\n",
       "      <td>3.575107e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bear drop</th>\n",
       "      <td>1.484080e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bull run</th>\n",
       "      <td>7.690706e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Negative</th>\n",
       "      <td>3.054619e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Positive</th>\n",
       "      <td>3.152717e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Slight negative</th>\n",
       "      <td>2.322225e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Slight or No change</th>\n",
       "      <td>2.359785e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Slight positive</th>\n",
       "      <td>2.658083e+07</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     Total Traded Quantity\n",
       "Trend                                     \n",
       "Among top gainers             5.382153e+07\n",
       "Among top losers              3.575107e+07\n",
       "Bear drop                     1.484080e+08\n",
       "Bull run                      7.690706e+07\n",
       "Negative                      3.054619e+07\n",
       "Positive                      3.152717e+07\n",
       "Slight negative               2.322225e+07\n",
       "Slight or No change           2.359785e+07\n",
       "Slight positive               2.658083e+07"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[['Total Traded Quantity','Trend']].groupby('Trend').mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "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>Total Traded Quantity</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Trend</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Among top gainers</th>\n",
       "      <td>46252848.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Among top losers</th>\n",
       "      <td>30212097.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bear drop</th>\n",
       "      <td>129338963.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bull run</th>\n",
       "      <td>50693970.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Negative</th>\n",
       "      <td>26000410.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Positive</th>\n",
       "      <td>25282971.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Slight negative</th>\n",
       "      <td>20499497.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Slight or No change</th>\n",
       "      <td>18872459.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Slight positive</th>\n",
       "      <td>20076688.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     Total Traded Quantity\n",
       "Trend                                     \n",
       "Among top gainers               46252848.0\n",
       "Among top losers                30212097.5\n",
       "Bear drop                      129338963.0\n",
       "Bull run                        50693970.0\n",
       "Negative                        26000410.5\n",
       "Positive                        25282971.0\n",
       "Slight negative                 20499497.5\n",
       "Slight or No change             18872459.0\n",
       "Slight positive                 20076688.5"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[['Total Traded Quantity','Trend']].groupby('Trend').median()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Query 1.9\n",
    "\n",
    "SAVE the dataframe with the additional columns computed as a csv file week2.csv."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Exported\n"
     ]
    }
   ],
   "source": [
    "df.to_csv('Datasets/week2.csv', index=False)\n",
    "print('Exported')"
   ]
  }
 ],
 "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.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}