trader4_analysis3.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Topics for the Day\n",
    "1. pd.merge()\n",
    "2. pd.apply()\n",
    "3. pd.groupby()\n",
    "4. Group-based Aggregation\n",
    "5. Group-based Filtering\n",
    "6. Group-based Apply"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>DP ID</th>\n",
       "      <th>Client ID</th>\n",
       "      <th>Broker</th>\n",
       "      <th>Transaction Date</th>\n",
       "      <th>Exchange</th>\n",
       "      <th>Scrip</th>\n",
       "      <th>Order Direction</th>\n",
       "      <th>Quantity</th>\n",
       "      <th>Share Price (₹)</th>\n",
       "      <th>Brokerage (₹)</th>\n",
       "      <th>ETC (₹)</th>\n",
       "      <th>SEBI+ (₹)</th>\n",
       "      <th>GST (₹)</th>\n",
       "      <th>STT (₹)</th>\n",
       "      <th>SD (₹)</th>\n",
       "      <th>Transaction Time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>251</th>\n",
       "      <td>12765333</td>\n",
       "      <td>26657514</td>\n",
       "      <td>Zerodha</td>\n",
       "      <td>2024-10-10 00:00:00</td>\n",
       "      <td>NSE</td>\n",
       "      <td>IOB</td>\n",
       "      <td>BUY</td>\n",
       "      <td>150</td>\n",
       "      <td>54.55</td>\n",
       "      <td>15.0</td>\n",
       "      <td>0.243020</td>\n",
       "      <td>0.016365</td>\n",
       "      <td>2.746689</td>\n",
       "      <td>8.18250</td>\n",
       "      <td>1.0</td>\n",
       "      <td>14:42:44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>252</th>\n",
       "      <td>12765333</td>\n",
       "      <td>26657514</td>\n",
       "      <td>Zerodha</td>\n",
       "      <td>2024-10-10 00:00:00</td>\n",
       "      <td>NSE</td>\n",
       "      <td>GMDCLTD</td>\n",
       "      <td>BUY</td>\n",
       "      <td>25</td>\n",
       "      <td>326.40</td>\n",
       "      <td>15.0</td>\n",
       "      <td>0.242352</td>\n",
       "      <td>0.016320</td>\n",
       "      <td>2.746561</td>\n",
       "      <td>8.16000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>15:20:53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>253</th>\n",
       "      <td>12765333</td>\n",
       "      <td>26657514</td>\n",
       "      <td>Zerodha</td>\n",
       "      <td>2024-10-10 00:00:00</td>\n",
       "      <td>NSE</td>\n",
       "      <td>FLUOROCHEM</td>\n",
       "      <td>SELL</td>\n",
       "      <td>2</td>\n",
       "      <td>4585.00</td>\n",
       "      <td>15.0</td>\n",
       "      <td>0.272349</td>\n",
       "      <td>0.018340</td>\n",
       "      <td>2.752324</td>\n",
       "      <td>9.17000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10:12:49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>254</th>\n",
       "      <td>12765333</td>\n",
       "      <td>26657514</td>\n",
       "      <td>Zerodha</td>\n",
       "      <td>2024-10-15 00:00:00</td>\n",
       "      <td>NSE</td>\n",
       "      <td>BAJAJHFL</td>\n",
       "      <td>BUY</td>\n",
       "      <td>13</td>\n",
       "      <td>138.47</td>\n",
       "      <td>15.0</td>\n",
       "      <td>0.053463</td>\n",
       "      <td>0.003600</td>\n",
       "      <td>2.710271</td>\n",
       "      <td>1.80011</td>\n",
       "      <td>0.0</td>\n",
       "      <td>11:12:49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>255</th>\n",
       "      <td>12765333</td>\n",
       "      <td>26657514</td>\n",
       "      <td>Zerodha</td>\n",
       "      <td>2024-10-22 00:00:00</td>\n",
       "      <td>NSE</td>\n",
       "      <td>J&amp;KBANK</td>\n",
       "      <td>SELL</td>\n",
       "      <td>35</td>\n",
       "      <td>93.55</td>\n",
       "      <td>15.0</td>\n",
       "      <td>0.097245</td>\n",
       "      <td>0.006548</td>\n",
       "      <td>2.718683</td>\n",
       "      <td>3.27425</td>\n",
       "      <td>0.0</td>\n",
       "      <td>12:12:49</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        DP ID  Client ID   Broker     Transaction Date Exchange       Scrip  \\\n",
       "251  12765333   26657514  Zerodha  2024-10-10 00:00:00      NSE         IOB   \n",
       "252  12765333   26657514  Zerodha  2024-10-10 00:00:00      NSE     GMDCLTD   \n",
       "253  12765333   26657514  Zerodha  2024-10-10 00:00:00      NSE  FLUOROCHEM   \n",
       "254  12765333   26657514  Zerodha  2024-10-15 00:00:00      NSE    BAJAJHFL   \n",
       "255  12765333   26657514  Zerodha  2024-10-22 00:00:00      NSE     J&KBANK   \n",
       "\n",
       "    Order Direction  Quantity  Share Price (₹)  Brokerage (₹)   ETC (₹)  \\\n",
       "251             BUY       150            54.55           15.0  0.243020   \n",
       "252             BUY        25           326.40           15.0  0.242352   \n",
       "253            SELL         2          4585.00           15.0  0.272349   \n",
       "254             BUY        13           138.47           15.0  0.053463   \n",
       "255            SELL        35            93.55           15.0  0.097245   \n",
       "\n",
       "     SEBI+ (₹)   GST (₹)  STT (₹)  SD (₹) Transaction Time  \n",
       "251   0.016365  2.746689  8.18250     1.0         14:42:44  \n",
       "252   0.016320  2.746561  8.16000     1.0         15:20:53  \n",
       "253   0.018340  2.752324  9.17000     0.0         10:12:49  \n",
       "254   0.003600  2.710271  1.80011     0.0         11:12:49  \n",
       "255   0.006548  2.718683  3.27425     0.0         12:12:49  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_trader = pd.read_excel(\"../pandas-trader/stock_trader.xlsx\", sheet_name='TRADES')\n",
    "df_trader.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>Unnamed: 0</th>\n",
       "      <th>Symbol</th>\n",
       "      <th>Company</th>\n",
       "      <th>Price</th>\n",
       "      <th>Category</th>\n",
       "      <th>52wk Low</th>\n",
       "      <th>52wk High</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>166</th>\n",
       "      <td>NaN</td>\n",
       "      <td>WELCORP</td>\n",
       "      <td>Welspun Corp Limited</td>\n",
       "      <td>772.85</td>\n",
       "      <td>Small Cap</td>\n",
       "      <td>440.15</td>\n",
       "      <td>794.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>167</th>\n",
       "      <td>NaN</td>\n",
       "      <td>WESTLIFE</td>\n",
       "      <td>Westlife Foodworld Limited</td>\n",
       "      <td>777.05</td>\n",
       "      <td>Small Cap</td>\n",
       "      <td>708.95</td>\n",
       "      <td>957.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>168</th>\n",
       "      <td>NaN</td>\n",
       "      <td>ZEEL</td>\n",
       "      <td>Zee Entertainment Enterprises Limited</td>\n",
       "      <td>123.36</td>\n",
       "      <td>Mid Cap</td>\n",
       "      <td>116.98</td>\n",
       "      <td>299.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>169</th>\n",
       "      <td>NaN</td>\n",
       "      <td>ZFCVINDIA</td>\n",
       "      <td>Zf Commercial Vehicle Control Systems India Li...</td>\n",
       "      <td>14491.15</td>\n",
       "      <td>Small Cap</td>\n",
       "      <td>12975.00</td>\n",
       "      <td>18250.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>170</th>\n",
       "      <td>NaN</td>\n",
       "      <td>ZYDUSWELL</td>\n",
       "      <td>Zydus Wellness Limited</td>\n",
       "      <td>1946.35</td>\n",
       "      <td>Small Cap</td>\n",
       "      <td>1440.15</td>\n",
       "      <td>2484.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Unnamed: 0     Symbol                                            Company  \\\n",
       "166        NaN    WELCORP                               Welspun Corp Limited   \n",
       "167        NaN   WESTLIFE                         Westlife Foodworld Limited   \n",
       "168        NaN       ZEEL              Zee Entertainment Enterprises Limited   \n",
       "169        NaN  ZFCVINDIA  Zf Commercial Vehicle Control Systems India Li...   \n",
       "170        NaN  ZYDUSWELL                             Zydus Wellness Limited   \n",
       "\n",
       "        Price   Category  52wk Low  52wk High  \n",
       "166    772.85  Small Cap    440.15     794.75  \n",
       "167    777.05  Small Cap    708.95     957.00  \n",
       "168    123.36    Mid Cap    116.98     299.70  \n",
       "169  14491.15  Small Cap  12975.00   18250.00  \n",
       "170   1946.35  Small Cap   1440.15    2484.00  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_prices = pd.read_excel(\"../pandas-trader/stock_trader.xlsx\", sheet_name='PRICES')\n",
    "df_prices.tail()"
   ]
  },
  {
   "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>Company</th>\n",
       "      <th>Scrip</th>\n",
       "      <th>Transaction Date</th>\n",
       "      <th>Order Direction</th>\n",
       "      <th>Quantity</th>\n",
       "      <th>Share Price (₹)</th>\n",
       "      <th>52wk Low</th>\n",
       "      <th>52wk High</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Interglobe Aviation Limited</td>\n",
       "      <td>INDIGO</td>\n",
       "      <td>2019-07-11</td>\n",
       "      <td>buy</td>\n",
       "      <td>10</td>\n",
       "      <td>1360.10</td>\n",
       "      <td>2486.25</td>\n",
       "      <td>5035.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bajaj Auto Limited.</td>\n",
       "      <td>BAJAJ-AUTO</td>\n",
       "      <td>2019-08-19</td>\n",
       "      <td>buy</td>\n",
       "      <td>7</td>\n",
       "      <td>2750.95</td>\n",
       "      <td>5349.05</td>\n",
       "      <td>12774.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Reliance Industries Limited</td>\n",
       "      <td>RELIANCE</td>\n",
       "      <td>2019-08-19</td>\n",
       "      <td>buy</td>\n",
       "      <td>10</td>\n",
       "      <td>1232.49</td>\n",
       "      <td>1149.03</td>\n",
       "      <td>1608.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Jio Financial Services Limited</td>\n",
       "      <td>JIOFIN</td>\n",
       "      <td>2019-08-19</td>\n",
       "      <td>buy-demerger</td>\n",
       "      <td>10</td>\n",
       "      <td>60.51</td>\n",
       "      <td>213.80</td>\n",
       "      <td>394.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Mahindra And Mahindra Limited</td>\n",
       "      <td>M&amp;M</td>\n",
       "      <td>2019-08-19</td>\n",
       "      <td>buy</td>\n",
       "      <td>1</td>\n",
       "      <td>533.85</td>\n",
       "      <td>1473.55</td>\n",
       "      <td>3222.10</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>244</th>\n",
       "      <td>Aarti Industries Ltd</td>\n",
       "      <td>AARTIIND</td>\n",
       "      <td>2024-10-10 00:00:00</td>\n",
       "      <td>BUY</td>\n",
       "      <td>12</td>\n",
       "      <td>527.90</td>\n",
       "      <td>475.00</td>\n",
       "      <td>769.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>245</th>\n",
       "      <td>Indian Overseas Bank</td>\n",
       "      <td>IOB</td>\n",
       "      <td>2024-10-10 00:00:00</td>\n",
       "      <td>BUY</td>\n",
       "      <td>150</td>\n",
       "      <td>54.55</td>\n",
       "      <td>38.95</td>\n",
       "      <td>83.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>246</th>\n",
       "      <td>Gujarat Mineral Development Corporation Limited</td>\n",
       "      <td>GMDCLTD</td>\n",
       "      <td>2024-10-10 00:00:00</td>\n",
       "      <td>BUY</td>\n",
       "      <td>25</td>\n",
       "      <td>326.40</td>\n",
       "      <td>307.10</td>\n",
       "      <td>506.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>247</th>\n",
       "      <td>Gujarat Fluorochemicals Limited</td>\n",
       "      <td>FLUOROCHEM</td>\n",
       "      <td>2024-10-10 00:00:00</td>\n",
       "      <td>SELL</td>\n",
       "      <td>2</td>\n",
       "      <td>4585.00</td>\n",
       "      <td>2476.00</td>\n",
       "      <td>4880.95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>248</th>\n",
       "      <td>Jammu And Kashmir Bank Limited</td>\n",
       "      <td>J&amp;KBANK</td>\n",
       "      <td>2024-10-22 00:00:00</td>\n",
       "      <td>SELL</td>\n",
       "      <td>35</td>\n",
       "      <td>93.55</td>\n",
       "      <td>88.11</td>\n",
       "      <td>152.50</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>249 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             Company       Scrip  \\\n",
       "0                        Interglobe Aviation Limited      INDIGO   \n",
       "1                                Bajaj Auto Limited.  BAJAJ-AUTO   \n",
       "2                        Reliance Industries Limited    RELIANCE   \n",
       "3                     Jio Financial Services Limited      JIOFIN   \n",
       "4                      Mahindra And Mahindra Limited         M&M   \n",
       "..                                               ...         ...   \n",
       "244                             Aarti Industries Ltd    AARTIIND   \n",
       "245                             Indian Overseas Bank         IOB   \n",
       "246  Gujarat Mineral Development Corporation Limited     GMDCLTD   \n",
       "247                  Gujarat Fluorochemicals Limited  FLUOROCHEM   \n",
       "248                   Jammu And Kashmir Bank Limited     J&KBANK   \n",
       "\n",
       "        Transaction Date Order Direction  Quantity  Share Price (₹)  52wk Low  \\\n",
       "0             2019-07-11             buy        10          1360.10   2486.25   \n",
       "1             2019-08-19             buy         7          2750.95   5349.05   \n",
       "2             2019-08-19             buy        10          1232.49   1149.03   \n",
       "3             2019-08-19    buy-demerger        10            60.51    213.80   \n",
       "4             2019-08-19             buy         1           533.85   1473.55   \n",
       "..                   ...             ...       ...              ...       ...   \n",
       "244  2024-10-10 00:00:00             BUY        12           527.90    475.00   \n",
       "245  2024-10-10 00:00:00             BUY       150            54.55     38.95   \n",
       "246  2024-10-10 00:00:00             BUY        25           326.40    307.10   \n",
       "247  2024-10-10 00:00:00            SELL         2          4585.00   2476.00   \n",
       "248  2024-10-22 00:00:00            SELL        35            93.55     88.11   \n",
       "\n",
       "     52wk High  \n",
       "0      5035.00  \n",
       "1     12774.00  \n",
       "2      1608.80  \n",
       "3       394.70  \n",
       "4      3222.10  \n",
       "..         ...  \n",
       "244     769.25  \n",
       "245      83.75  \n",
       "246     506.00  \n",
       "247    4880.95  \n",
       "248     152.50  \n",
       "\n",
       "[249 rows x 8 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.merge(df_trader, df_prices, left_on='Scrip', right_on='Symbol').loc[:,['Company', 'Scrip', 'Transaction Date', 'Order Direction', 'Quantity', 'Share Price (₹)', '52wk Low', '52wk High']]\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pandas.core.groupby.generic.DataFrameGroupBy object at 0x1194f36b0>"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby('Scrip')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "scala",
   "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.12.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}