trade_analysis1.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "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>0</th>\n",
       "      <td>12231800</td>\n",
       "      <td>15003721</td>\n",
       "      <td>5Paisa</td>\n",
       "      <td>2019-07-11</td>\n",
       "      <td>NSE</td>\n",
       "      <td>INDIGO</td>\n",
       "      <td>buy</td>\n",
       "      <td>10</td>\n",
       "      <td>1360.10</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.403950</td>\n",
       "      <td>0.027202</td>\n",
       "      <td>0.077607</td>\n",
       "      <td>13.60100</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2019-07-11 15:04:10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>12231800</td>\n",
       "      <td>15003721</td>\n",
       "      <td>5Paisa</td>\n",
       "      <td>2019-08-19</td>\n",
       "      <td>NSE</td>\n",
       "      <td>BAJAJ-AUTO</td>\n",
       "      <td>buy</td>\n",
       "      <td>7</td>\n",
       "      <td>2750.95</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.571923</td>\n",
       "      <td>0.038513</td>\n",
       "      <td>0.109878</td>\n",
       "      <td>19.25665</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2019-08-19 09:27:18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>12231800</td>\n",
       "      <td>15003721</td>\n",
       "      <td>5Paisa</td>\n",
       "      <td>2019-08-19</td>\n",
       "      <td>NSE</td>\n",
       "      <td>RELIANCE</td>\n",
       "      <td>buy</td>\n",
       "      <td>10</td>\n",
       "      <td>1232.49</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.366050</td>\n",
       "      <td>0.024650</td>\n",
       "      <td>0.070326</td>\n",
       "      <td>12.32490</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2019-08-19 12:40:29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>12231800</td>\n",
       "      <td>15003721</td>\n",
       "      <td>5Paisa</td>\n",
       "      <td>2019-08-19</td>\n",
       "      <td>NSE</td>\n",
       "      <td>JIOFIN</td>\n",
       "      <td>buy-demerger</td>\n",
       "      <td>10</td>\n",
       "      <td>60.51</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>12231800</td>\n",
       "      <td>15003721</td>\n",
       "      <td>5Paisa</td>\n",
       "      <td>2019-08-19</td>\n",
       "      <td>NSE</td>\n",
       "      <td>M&amp;M</td>\n",
       "      <td>buy</td>\n",
       "      <td>1</td>\n",
       "      <td>533.85</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.015855</td>\n",
       "      <td>0.001068</td>\n",
       "      <td>0.003046</td>\n",
       "      <td>0.53385</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2019-08-19 12:44:22</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",
       "    </tr>\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",
       "<p>256 rows × 16 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        DP ID  Client ID   Broker     Transaction Date Exchange       Scrip  \\\n",
       "0    12231800   15003721   5Paisa           2019-07-11      NSE      INDIGO   \n",
       "1    12231800   15003721   5Paisa           2019-08-19      NSE  BAJAJ-AUTO   \n",
       "2    12231800   15003721   5Paisa           2019-08-19      NSE    RELIANCE   \n",
       "3    12231800   15003721   5Paisa           2019-08-19      NSE      JIOFIN   \n",
       "4    12231800   15003721   5Paisa           2019-08-19      NSE         M&M   \n",
       "..        ...        ...      ...                  ...      ...         ...   \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",
       "0               buy        10          1360.10            0.0  0.403950   \n",
       "1               buy         7          2750.95            0.0  0.571923   \n",
       "2               buy        10          1232.49            0.0  0.366050   \n",
       "3      buy-demerger        10            60.51            NaN       NaN   \n",
       "4               buy         1           533.85            0.0  0.015855   \n",
       "..              ...       ...              ...            ...       ...   \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",
       "0     0.027202  0.077607  13.60100     2.0  2019-07-11 15:04:10  \n",
       "1     0.038513  0.109878  19.25665     3.0  2019-08-19 09:27:18  \n",
       "2     0.024650  0.070326  12.32490     2.0  2019-08-19 12:40:29  \n",
       "3          NaN       NaN       NaN     NaN                  NaN  \n",
       "4     0.001068  0.003046   0.53385     0.0  2019-08-19 12:44:22  \n",
       "..         ...       ...       ...     ...                  ...  \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  \n",
       "\n",
       "[256 rows x 16 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stocks_data = pd.read_excel('stock_trader.xlsx', sheet_name='TRADES')\n",
    "stocks_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['DP ID', 'Client ID', 'Broker', 'Transaction Date', 'Exchange', 'Scrip',\n",
       "       'Order Direction', 'Quantity', 'Share Price (₹)', 'Brokerage (₹)',\n",
       "       'ETC (₹)', 'SEBI+ (₹)', 'GST (₹)', 'STT (₹)', 'SD (₹)',\n",
       "       'Transaction Time'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stocks_data.keys()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['DP ID', 'Client ID', 'Broker', 'Transaction Date', 'Exchange', 'Scrip',\n",
       "       'Order Direction', 'Quantity', 'Share Price (₹)', 'Brokerage (₹)',\n",
       "       'ETC (₹)', 'SEBI+ (₹)', 'GST (₹)', 'STT (₹)', 'SD (₹)',\n",
       "       'Transaction Time'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stocks_data.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 256 entries, 0 to 255\n",
      "Data columns (total 16 columns):\n",
      " #   Column            Non-Null Count  Dtype  \n",
      "---  ------            --------------  -----  \n",
      " 0   DP ID             256 non-null    int64  \n",
      " 1   Client ID         256 non-null    int64  \n",
      " 2   Broker            256 non-null    object \n",
      " 3   Transaction Date  256 non-null    object \n",
      " 4   Exchange          256 non-null    object \n",
      " 5   Scrip             256 non-null    object \n",
      " 6   Order Direction   256 non-null    object \n",
      " 7   Quantity          256 non-null    int64  \n",
      " 8   Share Price (₹)   256 non-null    float64\n",
      " 9   Brokerage (₹)     252 non-null    float64\n",
      " 10  ETC (₹)           249 non-null    float64\n",
      " 11  SEBI+ (₹)         249 non-null    float64\n",
      " 12  GST (₹)           252 non-null    float64\n",
      " 13  STT (₹)           249 non-null    float64\n",
      " 14  SD (₹)            252 non-null    float64\n",
      " 15  Transaction Time  249 non-null    object \n",
      "dtypes: float64(7), int64(3), object(6)\n",
      "memory usage: 32.1+ KB\n"
     ]
    }
   ],
   "source": [
    "stocks_data.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "123"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stocks_data['Scrip'].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['AARTIIND',\n",
       " 'ACC',\n",
       " 'ADANIPORTS',\n",
       " 'ANANDRATHI',\n",
       " 'ANGELONE',\n",
       " 'ANURAS',\n",
       " 'APOLLOTYRE',\n",
       " 'ASIANPAINT',\n",
       " 'ASTERDM',\n",
       " 'ASTRAL',\n",
       " 'ATL',\n",
       " 'ATUL',\n",
       " 'AUBANK',\n",
       " 'BAJAJ-AUTO',\n",
       " 'BAJAJFINSV',\n",
       " 'BAJAJHFL',\n",
       " 'BATAINDIA',\n",
       " 'BAYERCROP',\n",
       " 'BDL',\n",
       " 'BEL',\n",
       " 'BERGEPAINT',\n",
       " 'BIRLACORPN',\n",
       " 'CASTROLIND',\n",
       " 'CEATLTD',\n",
       " 'CENTURYPLY',\n",
       " 'CERA',\n",
       " 'CGCL',\n",
       " 'CHAMBLFERT',\n",
       " 'CHEMCRUX',\n",
       " 'CHOLAFIN',\n",
       " 'COCHINAIRPORT',\n",
       " 'COCHINSHIP',\n",
       " 'CONCOR',\n",
       " 'CREDITACC',\n",
       " 'CRISIL',\n",
       " 'DATAPATTNS',\n",
       " 'DEEPAKNTR',\n",
       " 'DLF',\n",
       " 'EIHOTEL',\n",
       " 'EMAMILTD',\n",
       " 'EMAMIREAL',\n",
       " 'ENGINERSIN',\n",
       " 'EQUITASBNK',\n",
       " 'EXPLEOSOL',\n",
       " 'FACT',\n",
       " 'FLUOROCHEM',\n",
       " 'GMDCLTD',\n",
       " 'GOODYEAR',\n",
       " 'GRSE',\n",
       " 'GSPL',\n",
       " 'HATSUN',\n",
       " 'HBLPOWER',\n",
       " 'HCLTECH',\n",
       " 'HDFCBANK',\n",
       " 'HDFCLIFE',\n",
       " 'HEROFINCORP',\n",
       " 'HEROMOTOCO',\n",
       " 'HINDALCO',\n",
       " 'HINDZINC',\n",
       " 'ICICIGI',\n",
       " 'INDIGO',\n",
       " 'INFY',\n",
       " 'IOB',\n",
       " 'IRCTC',\n",
       " 'IRFC',\n",
       " 'ITC',\n",
       " 'J&KBANK',\n",
       " 'JBMA',\n",
       " 'JIOFIN',\n",
       " 'JKCEMENT',\n",
       " 'JKTYRE',\n",
       " 'JYOTHYLAB',\n",
       " 'KOTAKBANK',\n",
       " 'KSB',\n",
       " 'LINDEINDIA',\n",
       " 'LT',\n",
       " 'LTIM',\n",
       " 'LTTS',\n",
       " 'M&M',\n",
       " 'M&MFIN',\n",
       " 'MAHSEAMLES',\n",
       " 'MAXHEALTH',\n",
       " 'MAZDOCK',\n",
       " 'MCX',\n",
       " 'MEDANTA',\n",
       " 'MON100',\n",
       " 'NAVINFLUOR',\n",
       " 'NH',\n",
       " 'NIITLTD',\n",
       " 'PAYTM',\n",
       " 'PERSISTENT',\n",
       " 'PNBHOUSING',\n",
       " 'POONAWALLA',\n",
       " 'POWERGRID',\n",
       " 'PRSMJOHNSN',\n",
       " 'PVRINOX',\n",
       " 'RAINBOW   ',\n",
       " 'RAMCOCEM',\n",
       " 'RAYMOND',\n",
       " 'RCF',\n",
       " 'RELIANCE',\n",
       " 'RENUKA',\n",
       " 'RHIM',\n",
       " 'RKFORGE',\n",
       " 'RVNL',\n",
       " 'SBICARD',\n",
       " 'SBILIFE',\n",
       " 'SBIN',\n",
       " 'SCHAEFFLER',\n",
       " 'SONACOMS',\n",
       " 'SPARC',\n",
       " 'SSDL',\n",
       " 'SUNDRMFAST',\n",
       " 'SUNTECK',\n",
       " 'SWIGGY',\n",
       " 'SYNGENE',\n",
       " 'TATASTEEL',\n",
       " 'TCS',\n",
       " 'TITAGARH',\n",
       " 'TRIDENT',\n",
       " 'WELCORP',\n",
       " 'ZEEL',\n",
       " 'ZFCVINDIA']"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sorted(stocks_data['Scrip'].unique().tolist())"
   ]
  },
  {
   "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>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>0</th>\n",
       "      <td>12231800</td>\n",
       "      <td>15003721</td>\n",
       "      <td>5Paisa</td>\n",
       "      <td>2019-07-11</td>\n",
       "      <td>NSE</td>\n",
       "      <td>INDIGO</td>\n",
       "      <td>buy</td>\n",
       "      <td>10</td>\n",
       "      <td>1360.10</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.403950</td>\n",
       "      <td>0.027202</td>\n",
       "      <td>0.077607</td>\n",
       "      <td>13.60100</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2019-07-11 15:04:10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>12231800</td>\n",
       "      <td>15003721</td>\n",
       "      <td>5Paisa</td>\n",
       "      <td>2019-08-19</td>\n",
       "      <td>NSE</td>\n",
       "      <td>BAJAJ-AUTO</td>\n",
       "      <td>buy</td>\n",
       "      <td>7</td>\n",
       "      <td>2750.95</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.571923</td>\n",
       "      <td>0.038513</td>\n",
       "      <td>0.109878</td>\n",
       "      <td>19.25665</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2019-08-19 09:27:18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>12231800</td>\n",
       "      <td>15003721</td>\n",
       "      <td>5Paisa</td>\n",
       "      <td>2019-08-19</td>\n",
       "      <td>NSE</td>\n",
       "      <td>RELIANCE</td>\n",
       "      <td>buy</td>\n",
       "      <td>10</td>\n",
       "      <td>1232.49</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.366050</td>\n",
       "      <td>0.024650</td>\n",
       "      <td>0.070326</td>\n",
       "      <td>12.32490</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2019-08-19 12:40:29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>12231800</td>\n",
       "      <td>15003721</td>\n",
       "      <td>5Paisa</td>\n",
       "      <td>2019-08-19</td>\n",
       "      <td>NSE</td>\n",
       "      <td>JIOFIN</td>\n",
       "      <td>buy-demerger</td>\n",
       "      <td>10</td>\n",
       "      <td>60.51</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>12231800</td>\n",
       "      <td>15003721</td>\n",
       "      <td>5Paisa</td>\n",
       "      <td>2019-08-19</td>\n",
       "      <td>NSE</td>\n",
       "      <td>M&amp;M</td>\n",
       "      <td>buy</td>\n",
       "      <td>1</td>\n",
       "      <td>533.85</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.015855</td>\n",
       "      <td>0.001068</td>\n",
       "      <td>0.003046</td>\n",
       "      <td>0.53385</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2019-08-19 12:44:22</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      DP ID  Client ID  Broker Transaction Date Exchange       Scrip  \\\n",
       "0  12231800   15003721  5Paisa       2019-07-11      NSE      INDIGO   \n",
       "1  12231800   15003721  5Paisa       2019-08-19      NSE  BAJAJ-AUTO   \n",
       "2  12231800   15003721  5Paisa       2019-08-19      NSE    RELIANCE   \n",
       "3  12231800   15003721  5Paisa       2019-08-19      NSE      JIOFIN   \n",
       "4  12231800   15003721  5Paisa       2019-08-19      NSE         M&M   \n",
       "\n",
       "  Order Direction  Quantity  Share Price (₹)  Brokerage (₹)   ETC (₹)  \\\n",
       "0             buy        10          1360.10            0.0  0.403950   \n",
       "1             buy         7          2750.95            0.0  0.571923   \n",
       "2             buy        10          1232.49            0.0  0.366050   \n",
       "3    buy-demerger        10            60.51            NaN       NaN   \n",
       "4             buy         1           533.85            0.0  0.015855   \n",
       "\n",
       "   SEBI+ (₹)   GST (₹)   STT (₹)  SD (₹)     Transaction Time  \n",
       "0   0.027202  0.077607  13.60100     2.0  2019-07-11 15:04:10  \n",
       "1   0.038513  0.109878  19.25665     3.0  2019-08-19 09:27:18  \n",
       "2   0.024650  0.070326  12.32490     2.0  2019-08-19 12:40:29  \n",
       "3        NaN       NaN       NaN     NaN                  NaN  \n",
       "4   0.001068  0.003046   0.53385     0.0  2019-08-19 12:44:22  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stocks_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>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>246</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>TITAGARH</td>\n",
       "      <td>BUY</td>\n",
       "      <td>9</td>\n",
       "      <td>1080.00</td>\n",
       "      <td>15.0</td>\n",
       "      <td>0.288684</td>\n",
       "      <td>0.019440</td>\n",
       "      <td>2.755462</td>\n",
       "      <td>9.72000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>15:23:55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>247</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>RCF</td>\n",
       "      <td>BUY</td>\n",
       "      <td>50</td>\n",
       "      <td>183.75</td>\n",
       "      <td>15.0</td>\n",
       "      <td>0.272869</td>\n",
       "      <td>0.018375</td>\n",
       "      <td>2.752424</td>\n",
       "      <td>9.18750</td>\n",
       "      <td>1.0</td>\n",
       "      <td>15:20:15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>248</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>ENGINERSIN</td>\n",
       "      <td>BUY</td>\n",
       "      <td>30</td>\n",
       "      <td>200.30</td>\n",
       "      <td>15.0</td>\n",
       "      <td>0.178467</td>\n",
       "      <td>0.012018</td>\n",
       "      <td>2.734287</td>\n",
       "      <td>6.00900</td>\n",
       "      <td>1.0</td>\n",
       "      <td>15:25:09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>249</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>RVNL</td>\n",
       "      <td>BUY</td>\n",
       "      <td>20</td>\n",
       "      <td>481.40</td>\n",
       "      <td>15.0</td>\n",
       "      <td>0.285952</td>\n",
       "      <td>0.019256</td>\n",
       "      <td>2.754937</td>\n",
       "      <td>9.62800</td>\n",
       "      <td>1.0</td>\n",
       "      <td>15:23:06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>250</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>AARTIIND</td>\n",
       "      <td>BUY</td>\n",
       "      <td>12</td>\n",
       "      <td>527.90</td>\n",
       "      <td>15.0</td>\n",
       "      <td>0.188144</td>\n",
       "      <td>0.012670</td>\n",
       "      <td>2.736146</td>\n",
       "      <td>6.33480</td>\n",
       "      <td>1.0</td>\n",
       "      <td>15:29:34</td>\n",
       "    </tr>\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",
       "246  12765333   26657514  Zerodha  2024-10-10 00:00:00      NSE    TITAGARH   \n",
       "247  12765333   26657514  Zerodha  2024-10-10 00:00:00      NSE         RCF   \n",
       "248  12765333   26657514  Zerodha  2024-10-10 00:00:00      NSE  ENGINERSIN   \n",
       "249  12765333   26657514  Zerodha  2024-10-10 00:00:00      NSE        RVNL   \n",
       "250  12765333   26657514  Zerodha  2024-10-10 00:00:00      NSE    AARTIIND   \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",
       "246             BUY         9          1080.00           15.0  0.288684   \n",
       "247             BUY        50           183.75           15.0  0.272869   \n",
       "248             BUY        30           200.30           15.0  0.178467   \n",
       "249             BUY        20           481.40           15.0  0.285952   \n",
       "250             BUY        12           527.90           15.0  0.188144   \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",
       "246   0.019440  2.755462  9.72000     1.0         15:23:55  \n",
       "247   0.018375  2.752424  9.18750     1.0         15:20:15  \n",
       "248   0.012018  2.734287  6.00900     1.0         15:25:09  \n",
       "249   0.019256  2.754937  9.62800     1.0         15:23:06  \n",
       "250   0.012670  2.736146  6.33480     1.0         15:29:34  \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": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stocks_data.tail(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>Transaction Date</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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>251</th>\n",
       "      <td>2024-10-10 00:00:00</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>252</th>\n",
       "      <td>2024-10-10 00:00:00</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>253</th>\n",
       "      <td>2024-10-10 00:00:00</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>254</th>\n",
       "      <td>2024-10-15 00:00:00</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>255</th>\n",
       "      <td>2024-10-22 00:00:00</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",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Transaction Date       Scrip Order Direction  Quantity  \\\n",
       "251  2024-10-10 00:00:00         IOB             BUY       150   \n",
       "252  2024-10-10 00:00:00     GMDCLTD             BUY        25   \n",
       "253  2024-10-10 00:00:00  FLUOROCHEM            SELL         2   \n",
       "254  2024-10-15 00:00:00    BAJAJHFL             BUY        13   \n",
       "255  2024-10-22 00:00:00     J&KBANK            SELL        35   \n",
       "\n",
       "     Share Price (₹)  Brokerage (₹)   ETC (₹)  SEBI+ (₹)   GST (₹)  STT (₹)  \\\n",
       "251            54.55           15.0  0.243020   0.016365  2.746689  8.18250   \n",
       "252           326.40           15.0  0.242352   0.016320  2.746561  8.16000   \n",
       "253          4585.00           15.0  0.272349   0.018340  2.752324  9.17000   \n",
       "254           138.47           15.0  0.053463   0.003600  2.710271  1.80011   \n",
       "255            93.55           15.0  0.097245   0.006548  2.718683  3.27425   \n",
       "\n",
       "     SD (₹)  \n",
       "251     1.0  \n",
       "252     1.0  \n",
       "253     0.0  \n",
       "254     0.0  \n",
       "255     0.0  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "column_list = ['Transaction Date', 'Scrip', 'Order Direction', 'Quantity', 'Share Price (₹)', 'Brokerage (₹)', 'ETC (₹)', 'SEBI+ (₹)', 'GST (₹)', 'STT (₹)', 'SD (₹)']\n",
    "stocks_data[column_list].tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Transaction Date\n",
       "2024-05-22 00:00:00    37\n",
       "2024-05-27 00:00:00    26\n",
       "2024-06-04 00:00:00    18\n",
       "2024-07-16 00:00:00    16\n",
       "2019-08-19             11\n",
       "2024-10-10 00:00:00     8\n",
       "2024-05-29 00:00:00     7\n",
       "2024-07-30 00:00:00     6\n",
       "2024-07-26 00:00:00     6\n",
       "2024-08-05 00:00:00     5\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stocks_data['Transaction Date'].value_counts().head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "stocks_data.rename(\n",
    "    {'Transaction Date': 'txn_date', \n",
    "     'Scrip': 'scrip', \n",
    "     'Order Direction': 'order_type', \n",
    "     'Quantity': 'quantity', \n",
    "     'Share Price (₹)': 'price', \n",
    "     'Brokerage (₹)': 'charges_brokerage', \n",
    "     'ETC (₹)': 'charges_etc', \n",
    "     'SEBI+ (₹)': 'charges_sebi', \n",
    "     'GST (₹)': 'charges_gst', \n",
    "     'STT (₹)': 'charges_stt', \n",
    "     'SD (₹)': 'charges_sd'}, \n",
    "        axis=1, inplace=True)\n",
    "\n",
    "stocks_data.drop(columns=['DP ID', 'Client ID', 'Broker', 'Exchange', 'Transaction Time'], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>txn_date</th>\n",
       "      <th>scrip</th>\n",
       "      <th>order_type</th>\n",
       "      <th>quantity</th>\n",
       "      <th>price</th>\n",
       "      <th>charges_brokerage</th>\n",
       "      <th>charges_etc</th>\n",
       "      <th>charges_sebi</th>\n",
       "      <th>charges_gst</th>\n",
       "      <th>charges_stt</th>\n",
       "      <th>charges_sd</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>251</th>\n",
       "      <td>2024-10-10 00:00:00</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>252</th>\n",
       "      <td>2024-10-10 00:00:00</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>253</th>\n",
       "      <td>2024-10-10 00:00:00</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>254</th>\n",
       "      <td>2024-10-15 00:00:00</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>255</th>\n",
       "      <td>2024-10-22 00:00:00</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",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                txn_date       scrip order_type  quantity    price  \\\n",
       "251  2024-10-10 00:00:00         IOB        BUY       150    54.55   \n",
       "252  2024-10-10 00:00:00     GMDCLTD        BUY        25   326.40   \n",
       "253  2024-10-10 00:00:00  FLUOROCHEM       SELL         2  4585.00   \n",
       "254  2024-10-15 00:00:00    BAJAJHFL        BUY        13   138.47   \n",
       "255  2024-10-22 00:00:00     J&KBANK       SELL        35    93.55   \n",
       "\n",
       "     charges_brokerage  charges_etc  charges_sebi  charges_gst  charges_stt  \\\n",
       "251               15.0     0.243020      0.016365     2.746689      8.18250   \n",
       "252               15.0     0.242352      0.016320     2.746561      8.16000   \n",
       "253               15.0     0.272349      0.018340     2.752324      9.17000   \n",
       "254               15.0     0.053463      0.003600     2.710271      1.80011   \n",
       "255               15.0     0.097245      0.006548     2.718683      3.27425   \n",
       "\n",
       "     charges_sd  \n",
       "251         1.0  \n",
       "252         1.0  \n",
       "253         0.0  \n",
       "254         0.0  \n",
       "255         0.0  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stocks_data.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>charges_brokerage</th>\n",
       "      <th>charges_etc</th>\n",
       "      <th>charges_sebi</th>\n",
       "      <th>charges_gst</th>\n",
       "      <th>charges_stt</th>\n",
       "      <th>charges_sd</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>251</th>\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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>252</th>\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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>253</th>\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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>254</th>\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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>255</th>\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",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     charges_brokerage  charges_etc  charges_sebi  charges_gst  charges_stt  \\\n",
       "251               15.0     0.243020      0.016365     2.746689      8.18250   \n",
       "252               15.0     0.242352      0.016320     2.746561      8.16000   \n",
       "253               15.0     0.272349      0.018340     2.752324      9.17000   \n",
       "254               15.0     0.053463      0.003600     2.710271      1.80011   \n",
       "255               15.0     0.097245      0.006548     2.718683      3.27425   \n",
       "\n",
       "     charges_sd  \n",
       "251         1.0  \n",
       "252         1.0  \n",
       "253         0.0  \n",
       "254         0.0  \n",
       "255         0.0  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stocks_data.loc[:, 'charges_brokerage':'charges_sd'].tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>charges_brokerage</th>\n",
       "      <th>charges_etc</th>\n",
       "      <th>charges_sebi</th>\n",
       "      <th>charges_gst</th>\n",
       "      <th>charges_stt</th>\n",
       "      <th>charges_sd</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>251</th>\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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>252</th>\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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>253</th>\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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>254</th>\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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>255</th>\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",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     charges_brokerage  charges_etc  charges_sebi  charges_gst  charges_stt  \\\n",
       "251               15.0     0.243020      0.016365     2.746689      8.18250   \n",
       "252               15.0     0.242352      0.016320     2.746561      8.16000   \n",
       "253               15.0     0.272349      0.018340     2.752324      9.17000   \n",
       "254               15.0     0.053463      0.003600     2.710271      1.80011   \n",
       "255               15.0     0.097245      0.006548     2.718683      3.27425   \n",
       "\n",
       "     charges_sd  \n",
       "251         1.0  \n",
       "252         1.0  \n",
       "253         0.0  \n",
       "254         0.0  \n",
       "255         0.0  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import re\n",
    "charges = list(filter(lambda f: re.match(r\"charges_\\w+\", f), stocks_data.keys()))\n",
    "stocks_data[charges].tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0      16.109759\n",
       "1      22.976964\n",
       "2      14.785925\n",
       "3       0.000000\n",
       "4       0.553819\n",
       "         ...    \n",
       "251    27.188575\n",
       "252    27.165233\n",
       "253   -27.213013\n",
       "254    19.567445\n",
       "255   -21.096727\n",
       "Name: nett_charges, Length: 256, dtype: float64"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "buy_or_sell_factor = stocks_data['order_type'].str.lower().map({'buy': 1, 'sell': -1}).fillna(0)\n",
    "stocks_data['nett_charges'] = stocks_data[charges].sum(axis=1) * buy_or_sell_factor\n",
    "stocks_data['nett_charges']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>txn_date</th>\n",
       "      <th>order_type</th>\n",
       "      <th>scrip</th>\n",
       "      <th>quantity</th>\n",
       "      <th>price</th>\n",
       "      <th>nett_charges</th>\n",
       "      <th>nett_trade_value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-07-11</td>\n",
       "      <td>buy</td>\n",
       "      <td>INDIGO</td>\n",
       "      <td>10</td>\n",
       "      <td>1360.10</td>\n",
       "      <td>16.109759</td>\n",
       "      <td>13617.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019-08-19</td>\n",
       "      <td>buy</td>\n",
       "      <td>BAJAJ-AUTO</td>\n",
       "      <td>7</td>\n",
       "      <td>2750.95</td>\n",
       "      <td>22.976964</td>\n",
       "      <td>19279.63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-08-19</td>\n",
       "      <td>buy</td>\n",
       "      <td>RELIANCE</td>\n",
       "      <td>10</td>\n",
       "      <td>1232.49</td>\n",
       "      <td>14.785925</td>\n",
       "      <td>12339.69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-08-19</td>\n",
       "      <td>buy-demerger</td>\n",
       "      <td>JIOFIN</td>\n",
       "      <td>10</td>\n",
       "      <td>60.51</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>605.10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-08-19</td>\n",
       "      <td>buy</td>\n",
       "      <td>M&amp;M</td>\n",
       "      <td>1</td>\n",
       "      <td>533.85</td>\n",
       "      <td>0.553819</td>\n",
       "      <td>534.40</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>251</th>\n",
       "      <td>2024-10-10 00:00:00</td>\n",
       "      <td>BUY</td>\n",
       "      <td>IOB</td>\n",
       "      <td>150</td>\n",
       "      <td>54.55</td>\n",
       "      <td>27.188575</td>\n",
       "      <td>8209.69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>252</th>\n",
       "      <td>2024-10-10 00:00:00</td>\n",
       "      <td>BUY</td>\n",
       "      <td>GMDCLTD</td>\n",
       "      <td>25</td>\n",
       "      <td>326.40</td>\n",
       "      <td>27.165233</td>\n",
       "      <td>8187.17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>253</th>\n",
       "      <td>2024-10-10 00:00:00</td>\n",
       "      <td>SELL</td>\n",
       "      <td>FLUOROCHEM</td>\n",
       "      <td>2</td>\n",
       "      <td>4585.00</td>\n",
       "      <td>-27.213013</td>\n",
       "      <td>9142.79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>254</th>\n",
       "      <td>2024-10-15 00:00:00</td>\n",
       "      <td>BUY</td>\n",
       "      <td>BAJAJHFL</td>\n",
       "      <td>13</td>\n",
       "      <td>138.47</td>\n",
       "      <td>19.567445</td>\n",
       "      <td>1819.68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>255</th>\n",
       "      <td>2024-10-22 00:00:00</td>\n",
       "      <td>SELL</td>\n",
       "      <td>J&amp;KBANK</td>\n",
       "      <td>35</td>\n",
       "      <td>93.55</td>\n",
       "      <td>-21.096727</td>\n",
       "      <td>3253.15</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>256 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                txn_date    order_type       scrip  quantity    price  \\\n",
       "0             2019-07-11           buy      INDIGO        10  1360.10   \n",
       "1             2019-08-19           buy  BAJAJ-AUTO         7  2750.95   \n",
       "2             2019-08-19           buy    RELIANCE        10  1232.49   \n",
       "3             2019-08-19  buy-demerger      JIOFIN        10    60.51   \n",
       "4             2019-08-19           buy         M&M         1   533.85   \n",
       "..                   ...           ...         ...       ...      ...   \n",
       "251  2024-10-10 00:00:00           BUY         IOB       150    54.55   \n",
       "252  2024-10-10 00:00:00           BUY     GMDCLTD        25   326.40   \n",
       "253  2024-10-10 00:00:00          SELL  FLUOROCHEM         2  4585.00   \n",
       "254  2024-10-15 00:00:00           BUY    BAJAJHFL        13   138.47   \n",
       "255  2024-10-22 00:00:00          SELL     J&KBANK        35    93.55   \n",
       "\n",
       "     nett_charges  nett_trade_value  \n",
       "0       16.109759          13617.11  \n",
       "1       22.976964          19279.63  \n",
       "2       14.785925          12339.69  \n",
       "3        0.000000            605.10  \n",
       "4        0.553819            534.40  \n",
       "..            ...               ...  \n",
       "251     27.188575           8209.69  \n",
       "252     27.165233           8187.17  \n",
       "253    -27.213013           9142.79  \n",
       "254     19.567445           1819.68  \n",
       "255    -21.096727           3253.15  \n",
       "\n",
       "[256 rows x 7 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stocks_data['nett_trade_value'] = round(stocks_data['quantity'] * stocks_data['price'] + stocks_data['nett_charges'],2)\n",
    "brief_table = ['txn_date', 'order_type', 'scrip', 'quantity', 'price', 'nett_charges', 'nett_trade_value']\n",
    "stocks_data[brief_table]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "977201.9199999999"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "buy_transactions = stocks_data[list(map(lambda val: 'buy' in val.lower(), stocks_data['order_type']))][brief_table]\n",
    "buy_transactions['nett_trade_value'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Nett Trade (₹):    684291.37\n",
       "dtype: float64"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sell_transactions = stocks_data[list(map(lambda val: 'sell' in val.lower(), stocks_data['order_type']))][brief_table]\n",
    "\n",
    "pd.Series(\n",
    "    abs(sell_transactions['nett_trade_value'].sum() - buy_transactions['nett_trade_value'].sum()), \n",
    "    index=['Nett Trade (₹):'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'shape: [stocks_data: (256, 13), buy_transactions: (199, 7), sell_transactions: (57, 7)]'"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\"shape: [stocks_data: {}, buy_transactions: {}, sell_transactions: {}]\".format(stocks_data.shape, buy_transactions.shape, sell_transactions.shape)"
   ]
  },
  {
   "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
}