Sem-Analysis.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 156,
   "id": "b8b13f21",
   "metadata": {},
   "outputs": [],
   "source": [
    "import tabula\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import seaborn as sns\n",
    "import pprint"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 157,
   "id": "8c818691",
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "#tabula.convert_into(\"Sem-I2014Batch.pdf\", \"Sem-I2014Batch.pdf.csv\", output_format=\"csv\", pages='all')\n",
    "#tabula.convert_into(\"aidssem.pdf\", \"aidssem.csv\", output_format=\"csv\", pages='all')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 158,
   "id": "0e9434ed",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(\"aidssem.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 159,
   "id": "db86e674",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Data Cleaning\n",
    "\n",
    "i = df[((df.Htno == 'Htno') &( df.Subcode == 'Subcode') & (df.Subname == 'Subname') & (df.Grade == 'Grade') & (df.Credits == 'Credits'))].index\n",
    "df.drop(i,inplace=True)\n",
    "\n",
    "df.reset_index(inplace=True)\n",
    "del df['index']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 160,
   "id": "08c34880",
   "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>Htno</th>\n",
       "      <th>Subcode</th>\n",
       "      <th>Subname</th>\n",
       "      <th>Grade</th>\n",
       "      <th>Credits</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>20L31A5401</td>\n",
       "      <td>1000201205</td>\n",
       "      <td>Statistics for Data Science-I</td>\n",
       "      <td>O</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20L31A5401</td>\n",
       "      <td>1005201201</td>\n",
       "      <td>Computer Organization</td>\n",
       "      <td>C</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>20L31A5401</td>\n",
       "      <td>1005201202</td>\n",
       "      <td>Web Design</td>\n",
       "      <td>B</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>20L31A5401</td>\n",
       "      <td>1005201203</td>\n",
       "      <td>Data Structures</td>\n",
       "      <td>B</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>20L31A5401</td>\n",
       "      <td>1005201211</td>\n",
       "      <td>Web Design Lab</td>\n",
       "      <td>O</td>\n",
       "      <td>1.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>20L31A5401</td>\n",
       "      <td>1005201212</td>\n",
       "      <td>Data Structures Lab</td>\n",
       "      <td>C</td>\n",
       "      <td>1.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>20L31A5401</td>\n",
       "      <td>1000201213</td>\n",
       "      <td>Physics of Materials Lab</td>\n",
       "      <td>A</td>\n",
       "      <td>1.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>20L31A5401</td>\n",
       "      <td>1000201214</td>\n",
       "      <td>R Programming for Data Science Lab</td>\n",
       "      <td>A</td>\n",
       "      <td>1.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>20L31A5401</td>\n",
       "      <td>1000201121</td>\n",
       "      <td>Constitution of India</td>\n",
       "      <td>CP</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>20L31A5402</td>\n",
       "      <td>1000201205</td>\n",
       "      <td>Statistics for Data Science-I</td>\n",
       "      <td>A</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>20L31A5402</td>\n",
       "      <td>1005201201</td>\n",
       "      <td>Computer Organization</td>\n",
       "      <td>O</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>20L31A5402</td>\n",
       "      <td>1005201202</td>\n",
       "      <td>Web Design</td>\n",
       "      <td>C</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>20L31A5402</td>\n",
       "      <td>1005201203</td>\n",
       "      <td>Data Structures</td>\n",
       "      <td>B</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>20L31A5402</td>\n",
       "      <td>1005201211</td>\n",
       "      <td>Web Design Lab</td>\n",
       "      <td>A</td>\n",
       "      <td>1.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>20L31A5402</td>\n",
       "      <td>1005201212</td>\n",
       "      <td>Data Structures Lab</td>\n",
       "      <td>B</td>\n",
       "      <td>1.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>20L31A5402</td>\n",
       "      <td>1000201213</td>\n",
       "      <td>Physics of Materials Lab</td>\n",
       "      <td>O</td>\n",
       "      <td>1.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>20L31A5402</td>\n",
       "      <td>1000201214</td>\n",
       "      <td>R Programming for Data Science Lab</td>\n",
       "      <td>O</td>\n",
       "      <td>1.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>20L31A5402</td>\n",
       "      <td>1000201121</td>\n",
       "      <td>Constitution of India</td>\n",
       "      <td>CP</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>20L31A5403</td>\n",
       "      <td>1000201205</td>\n",
       "      <td>Statistics for Data Science-I</td>\n",
       "      <td>C</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>20L31A5403</td>\n",
       "      <td>1005201201</td>\n",
       "      <td>Computer Organization</td>\n",
       "      <td>C</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>20L31A5403</td>\n",
       "      <td>1005201202</td>\n",
       "      <td>Web Design</td>\n",
       "      <td>C</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>20L31A5403</td>\n",
       "      <td>1005201203</td>\n",
       "      <td>Data Structures</td>\n",
       "      <td>C</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>20L31A5403</td>\n",
       "      <td>1005201211</td>\n",
       "      <td>Web Design Lab</td>\n",
       "      <td>A</td>\n",
       "      <td>1.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>20L31A5403</td>\n",
       "      <td>1005201212</td>\n",
       "      <td>Data Structures Lab</td>\n",
       "      <td>B</td>\n",
       "      <td>1.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>20L31A5403</td>\n",
       "      <td>1000201213</td>\n",
       "      <td>Physics of Materials Lab</td>\n",
       "      <td>A</td>\n",
       "      <td>1.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>20L31A5403</td>\n",
       "      <td>1000201214</td>\n",
       "      <td>R Programming for Data Science Lab</td>\n",
       "      <td>O</td>\n",
       "      <td>1.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>20L31A5403</td>\n",
       "      <td>1000201121</td>\n",
       "      <td>Constitution of India</td>\n",
       "      <td>CP</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>20L31A5404</td>\n",
       "      <td>1000201205</td>\n",
       "      <td>Statistics for Data Science-I</td>\n",
       "      <td>O</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>20L31A5404</td>\n",
       "      <td>1005201201</td>\n",
       "      <td>Computer Organization</td>\n",
       "      <td>A</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>20L31A5404</td>\n",
       "      <td>1005201202</td>\n",
       "      <td>Web Design</td>\n",
       "      <td>B</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Htno     Subcode                             Subname Grade Credits\n",
       "0   20L31A5401  1000201205       Statistics for Data Science-I     O       3\n",
       "1   20L31A5401  1005201201               Computer Organization     C       3\n",
       "2   20L31A5401  1005201202                          Web Design     B       3\n",
       "3   20L31A5401  1005201203                     Data Structures     B       3\n",
       "4   20L31A5401  1005201211                      Web Design Lab     O     1.5\n",
       "5   20L31A5401  1005201212                 Data Structures Lab     C     1.5\n",
       "6   20L31A5401  1000201213            Physics of Materials Lab     A     1.5\n",
       "7   20L31A5401  1000201214  R Programming for Data Science Lab     A     1.5\n",
       "8   20L31A5401  1000201121               Constitution of India    CP       -\n",
       "9   20L31A5402  1000201205       Statistics for Data Science-I     A       3\n",
       "10  20L31A5402  1005201201               Computer Organization     O       3\n",
       "11  20L31A5402  1005201202                          Web Design     C       3\n",
       "12  20L31A5402  1005201203                     Data Structures     B       3\n",
       "13  20L31A5402  1005201211                      Web Design Lab     A     1.5\n",
       "14  20L31A5402  1005201212                 Data Structures Lab     B     1.5\n",
       "15  20L31A5402  1000201213            Physics of Materials Lab     O     1.5\n",
       "16  20L31A5402  1000201214  R Programming for Data Science Lab     O     1.5\n",
       "17  20L31A5402  1000201121               Constitution of India    CP       -\n",
       "18  20L31A5403  1000201205       Statistics for Data Science-I     C       3\n",
       "19  20L31A5403  1005201201               Computer Organization     C       3\n",
       "20  20L31A5403  1005201202                          Web Design     C       3\n",
       "21  20L31A5403  1005201203                     Data Structures     C       3\n",
       "22  20L31A5403  1005201211                      Web Design Lab     A     1.5\n",
       "23  20L31A5403  1005201212                 Data Structures Lab     B     1.5\n",
       "24  20L31A5403  1000201213            Physics of Materials Lab     A     1.5\n",
       "25  20L31A5403  1000201214  R Programming for Data Science Lab     O     1.5\n",
       "26  20L31A5403  1000201121               Constitution of India    CP       -\n",
       "27  20L31A5404  1000201205       Statistics for Data Science-I     O       3\n",
       "28  20L31A5404  1005201201               Computer Organization     A       3\n",
       "29  20L31A5404  1005201202                          Web Design     B       3"
      ]
     },
     "execution_count": 160,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(30)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 161,
   "id": "6890d809",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['20L31A5401' '20L31A5402' '20L31A5403' '20L31A5404' '20L31A5405'\n",
      " '20L31A5406' '20L31A5407' '20L31A5408' '20L31A5409' '20L31A5411'\n",
      " '20L31A5412' '20L31A5413' '20L31A5414' '20L31A5415' '20L31A5416'\n",
      " '20L31A5417' '20L31A5418' '20L31A5419' '20L31A5420' '20L31A5421'\n",
      " '20L31A5422' '20L31A5423' '20L31A5424' '20L31A5425' '20L31A5426'\n",
      " '20L31A5427' '20L31A5428' '20L31A5429' '20L31A5430' '20L31A5431'\n",
      " '20L31A5432' '20L31A5433' '20L31A5434' '20L31A5435' '20L31A5436'\n",
      " '20L31A5437' '20L31A5438' '20L31A5439' '20L31A5440' '20L31A5441'\n",
      " '20L31A5442' '20L31A5443' '20L31A5444' '20L31A5445' '20L31A5446'\n",
      " '20L31A5447' '20L31A5448' '20L31A5449' '20L31A5450' '20L31A5451'\n",
      " '20L31A5452' '20L31A5453' '20L31A5454' '20L31A5455' '20L31A5456'\n",
      " '20L31A5457' '20L31A5458' '20L31A5459' '20L31A5460' '20L31A5461'\n",
      " '20L31A5462' '20L31A5463' '20L31A5464']\n",
      "\n",
      "\n",
      "\n",
      "{'1000201121': 'Constitution of India',\n",
      " '1000201205': 'Statistics for Data Science-I',\n",
      " '1000201213': 'Physics of Materials Lab',\n",
      " '1000201214': 'R Programming for Data Science Lab',\n",
      " '1005201201': 'Computer Organization',\n",
      " '1005201202': 'Web Design',\n",
      " '1005201203': 'Data Structures',\n",
      " '1005201211': 'Web Design Lab',\n",
      " '1005201212': 'Data Structures Lab'}\n",
      "\n",
      "\n",
      "\n",
      "{'A': 148, 'B': 92, 'C': 75, 'CP': 63, 'D': 43, 'F': 16, 'O': 130}\n"
     ]
    }
   ],
   "source": [
    "subjects = df['Subname'].unique()\n",
    "htno = df['Htno'].unique()\n",
    "sub_code = df['Subcode'].unique()\n",
    "\n",
    "\n",
    "print(htno)\n",
    "print('\\n\\n')\n",
    "sub_dict = dict(zip(sub_code,subjects))\n",
    "pprint.pprint(sub_dict)\n",
    "\n",
    "grade_count = df['Grade'].value_counts().to_dict()\n",
    "print('\\n\\n')\n",
    "pprint.pprint(grade_count)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 162,
   "id": "c390b6c0",
   "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>Subname</th>\n",
       "      <th>Computer Organization</th>\n",
       "      <th>Constitution of India</th>\n",
       "      <th>Data Structures</th>\n",
       "      <th>Data Structures Lab</th>\n",
       "      <th>Physics of Materials Lab</th>\n",
       "      <th>R Programming for Data Science Lab</th>\n",
       "      <th>Statistics for Data Science-I</th>\n",
       "      <th>Web Design</th>\n",
       "      <th>Web Design Lab</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Htno</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>20L31A5401</th>\n",
       "      <td>C</td>\n",
       "      <td>CP</td>\n",
       "      <td>B</td>\n",
       "      <td>C</td>\n",
       "      <td>A</td>\n",
       "      <td>A</td>\n",
       "      <td>O</td>\n",
       "      <td>B</td>\n",
       "      <td>O</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20L31A5402</th>\n",
       "      <td>O</td>\n",
       "      <td>CP</td>\n",
       "      <td>B</td>\n",
       "      <td>B</td>\n",
       "      <td>O</td>\n",
       "      <td>O</td>\n",
       "      <td>A</td>\n",
       "      <td>C</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20L31A5403</th>\n",
       "      <td>C</td>\n",
       "      <td>CP</td>\n",
       "      <td>C</td>\n",
       "      <td>B</td>\n",
       "      <td>A</td>\n",
       "      <td>O</td>\n",
       "      <td>C</td>\n",
       "      <td>C</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20L31A5404</th>\n",
       "      <td>A</td>\n",
       "      <td>CP</td>\n",
       "      <td>A</td>\n",
       "      <td>A</td>\n",
       "      <td>O</td>\n",
       "      <td>O</td>\n",
       "      <td>O</td>\n",
       "      <td>B</td>\n",
       "      <td>O</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20L31A5405</th>\n",
       "      <td>C</td>\n",
       "      <td>CP</td>\n",
       "      <td>D</td>\n",
       "      <td>C</td>\n",
       "      <td>A</td>\n",
       "      <td>A</td>\n",
       "      <td>B</td>\n",
       "      <td>C</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20L31A5406</th>\n",
       "      <td>D</td>\n",
       "      <td>CP</td>\n",
       "      <td>D</td>\n",
       "      <td>C</td>\n",
       "      <td>O</td>\n",
       "      <td>O</td>\n",
       "      <td>C</td>\n",
       "      <td>D</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20L31A5407</th>\n",
       "      <td>D</td>\n",
       "      <td>CP</td>\n",
       "      <td>C</td>\n",
       "      <td>C</td>\n",
       "      <td>O</td>\n",
       "      <td>A</td>\n",
       "      <td>D</td>\n",
       "      <td>D</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20L31A5408</th>\n",
       "      <td>D</td>\n",
       "      <td>CP</td>\n",
       "      <td>D</td>\n",
       "      <td>C</td>\n",
       "      <td>O</td>\n",
       "      <td>O</td>\n",
       "      <td>D</td>\n",
       "      <td>D</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20L31A5409</th>\n",
       "      <td>B</td>\n",
       "      <td>CP</td>\n",
       "      <td>B</td>\n",
       "      <td>A</td>\n",
       "      <td>O</td>\n",
       "      <td>O</td>\n",
       "      <td>O</td>\n",
       "      <td>D</td>\n",
       "      <td>O</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20L31A5411</th>\n",
       "      <td>B</td>\n",
       "      <td>CP</td>\n",
       "      <td>B</td>\n",
       "      <td>C</td>\n",
       "      <td>O</td>\n",
       "      <td>O</td>\n",
       "      <td>A</td>\n",
       "      <td>B</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20L31A5412</th>\n",
       "      <td>D</td>\n",
       "      <td>CP</td>\n",
       "      <td>D</td>\n",
       "      <td>C</td>\n",
       "      <td>O</td>\n",
       "      <td>A</td>\n",
       "      <td>D</td>\n",
       "      <td>F</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20L31A5413</th>\n",
       "      <td>B</td>\n",
       "      <td>CP</td>\n",
       "      <td>A</td>\n",
       "      <td>A</td>\n",
       "      <td>O</td>\n",
       "      <td>O</td>\n",
       "      <td>A</td>\n",
       "      <td>B</td>\n",
       "      <td>O</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20L31A5414</th>\n",
       "      <td>O</td>\n",
       "      <td>CP</td>\n",
       "      <td>A</td>\n",
       "      <td>O</td>\n",
       "      <td>O</td>\n",
       "      <td>A</td>\n",
       "      <td>O</td>\n",
       "      <td>B</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20L31A5415</th>\n",
       "      <td>O</td>\n",
       "      <td>CP</td>\n",
       "      <td>C</td>\n",
       "      <td>A</td>\n",
       "      <td>O</td>\n",
       "      <td>A</td>\n",
       "      <td>C</td>\n",
       "      <td>C</td>\n",
       "      <td>O</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20L31A5416</th>\n",
       "      <td>A</td>\n",
       "      <td>CP</td>\n",
       "      <td>B</td>\n",
       "      <td>B</td>\n",
       "      <td>A</td>\n",
       "      <td>A</td>\n",
       "      <td>B</td>\n",
       "      <td>C</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20L31A5417</th>\n",
       "      <td>D</td>\n",
       "      <td>CP</td>\n",
       "      <td>D</td>\n",
       "      <td>C</td>\n",
       "      <td>A</td>\n",
       "      <td>A</td>\n",
       "      <td>F</td>\n",
       "      <td>D</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20L31A5418</th>\n",
       "      <td>O</td>\n",
       "      <td>CP</td>\n",
       "      <td>A</td>\n",
       "      <td>A</td>\n",
       "      <td>O</td>\n",
       "      <td>A</td>\n",
       "      <td>O</td>\n",
       "      <td>B</td>\n",
       "      <td>O</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20L31A5419</th>\n",
       "      <td>B</td>\n",
       "      <td>CP</td>\n",
       "      <td>B</td>\n",
       "      <td>B</td>\n",
       "      <td>O</td>\n",
       "      <td>A</td>\n",
       "      <td>A</td>\n",
       "      <td>C</td>\n",
       "      <td>O</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20L31A5420</th>\n",
       "      <td>F</td>\n",
       "      <td>CP</td>\n",
       "      <td>C</td>\n",
       "      <td>B</td>\n",
       "      <td>A</td>\n",
       "      <td>O</td>\n",
       "      <td>A</td>\n",
       "      <td>C</td>\n",
       "      <td>O</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20L31A5421</th>\n",
       "      <td>F</td>\n",
       "      <td>CP</td>\n",
       "      <td>F</td>\n",
       "      <td>B</td>\n",
       "      <td>A</td>\n",
       "      <td>A</td>\n",
       "      <td>F</td>\n",
       "      <td>D</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20L31A5422</th>\n",
       "      <td>F</td>\n",
       "      <td>CP</td>\n",
       "      <td>D</td>\n",
       "      <td>B</td>\n",
       "      <td>A</td>\n",
       "      <td>A</td>\n",
       "      <td>F</td>\n",
       "      <td>D</td>\n",
       "      <td>O</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20L31A5423</th>\n",
       "      <td>A</td>\n",
       "      <td>CP</td>\n",
       "      <td>B</td>\n",
       "      <td>B</td>\n",
       "      <td>A</td>\n",
       "      <td>A</td>\n",
       "      <td>A</td>\n",
       "      <td>C</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20L31A5424</th>\n",
       "      <td>D</td>\n",
       "      <td>CP</td>\n",
       "      <td>C</td>\n",
       "      <td>C</td>\n",
       "      <td>O</td>\n",
       "      <td>O</td>\n",
       "      <td>A</td>\n",
       "      <td>C</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20L31A5425</th>\n",
       "      <td>B</td>\n",
       "      <td>CP</td>\n",
       "      <td>C</td>\n",
       "      <td>B</td>\n",
       "      <td>O</td>\n",
       "      <td>A</td>\n",
       "      <td>O</td>\n",
       "      <td>C</td>\n",
       "      <td>O</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20L31A5426</th>\n",
       "      <td>A</td>\n",
       "      <td>CP</td>\n",
       "      <td>A</td>\n",
       "      <td>O</td>\n",
       "      <td>O</td>\n",
       "      <td>O</td>\n",
       "      <td>O</td>\n",
       "      <td>B</td>\n",
       "      <td>O</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20L31A5427</th>\n",
       "      <td>A</td>\n",
       "      <td>CP</td>\n",
       "      <td>B</td>\n",
       "      <td>O</td>\n",
       "      <td>O</td>\n",
       "      <td>A</td>\n",
       "      <td>C</td>\n",
       "      <td>B</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20L31A5428</th>\n",
       "      <td>A</td>\n",
       "      <td>CP</td>\n",
       "      <td>B</td>\n",
       "      <td>B</td>\n",
       "      <td>O</td>\n",
       "      <td>A</td>\n",
       "      <td>O</td>\n",
       "      <td>C</td>\n",
       "      <td>O</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20L31A5429</th>\n",
       "      <td>D</td>\n",
       "      <td>CP</td>\n",
       "      <td>C</td>\n",
       "      <td>B</td>\n",
       "      <td>O</td>\n",
       "      <td>A</td>\n",
       "      <td>B</td>\n",
       "      <td>C</td>\n",
       "      <td>O</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20L31A5430</th>\n",
       "      <td>C</td>\n",
       "      <td>CP</td>\n",
       "      <td>C</td>\n",
       "      <td>A</td>\n",
       "      <td>O</td>\n",
       "      <td>A</td>\n",
       "      <td>C</td>\n",
       "      <td>C</td>\n",
       "      <td>O</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20L31A5431</th>\n",
       "      <td>A</td>\n",
       "      <td>CP</td>\n",
       "      <td>B</td>\n",
       "      <td>A</td>\n",
       "      <td>O</td>\n",
       "      <td>A</td>\n",
       "      <td>O</td>\n",
       "      <td>C</td>\n",
       "      <td>O</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Subname    Computer Organization Constitution of India Data Structures  \\\n",
       "Htno                                                                     \n",
       "20L31A5401                     C                    CP               B   \n",
       "20L31A5402                     O                    CP               B   \n",
       "20L31A5403                     C                    CP               C   \n",
       "20L31A5404                     A                    CP               A   \n",
       "20L31A5405                     C                    CP               D   \n",
       "20L31A5406                     D                    CP               D   \n",
       "20L31A5407                     D                    CP               C   \n",
       "20L31A5408                     D                    CP               D   \n",
       "20L31A5409                     B                    CP               B   \n",
       "20L31A5411                     B                    CP               B   \n",
       "20L31A5412                     D                    CP               D   \n",
       "20L31A5413                     B                    CP               A   \n",
       "20L31A5414                     O                    CP               A   \n",
       "20L31A5415                     O                    CP               C   \n",
       "20L31A5416                     A                    CP               B   \n",
       "20L31A5417                     D                    CP               D   \n",
       "20L31A5418                     O                    CP               A   \n",
       "20L31A5419                     B                    CP               B   \n",
       "20L31A5420                     F                    CP               C   \n",
       "20L31A5421                     F                    CP               F   \n",
       "20L31A5422                     F                    CP               D   \n",
       "20L31A5423                     A                    CP               B   \n",
       "20L31A5424                     D                    CP               C   \n",
       "20L31A5425                     B                    CP               C   \n",
       "20L31A5426                     A                    CP               A   \n",
       "20L31A5427                     A                    CP               B   \n",
       "20L31A5428                     A                    CP               B   \n",
       "20L31A5429                     D                    CP               C   \n",
       "20L31A5430                     C                    CP               C   \n",
       "20L31A5431                     A                    CP               B   \n",
       "\n",
       "Subname    Data Structures Lab Physics of Materials Lab  \\\n",
       "Htno                                                      \n",
       "20L31A5401                   C                        A   \n",
       "20L31A5402                   B                        O   \n",
       "20L31A5403                   B                        A   \n",
       "20L31A5404                   A                        O   \n",
       "20L31A5405                   C                        A   \n",
       "20L31A5406                   C                        O   \n",
       "20L31A5407                   C                        O   \n",
       "20L31A5408                   C                        O   \n",
       "20L31A5409                   A                        O   \n",
       "20L31A5411                   C                        O   \n",
       "20L31A5412                   C                        O   \n",
       "20L31A5413                   A                        O   \n",
       "20L31A5414                   O                        O   \n",
       "20L31A5415                   A                        O   \n",
       "20L31A5416                   B                        A   \n",
       "20L31A5417                   C                        A   \n",
       "20L31A5418                   A                        O   \n",
       "20L31A5419                   B                        O   \n",
       "20L31A5420                   B                        A   \n",
       "20L31A5421                   B                        A   \n",
       "20L31A5422                   B                        A   \n",
       "20L31A5423                   B                        A   \n",
       "20L31A5424                   C                        O   \n",
       "20L31A5425                   B                        O   \n",
       "20L31A5426                   O                        O   \n",
       "20L31A5427                   O                        O   \n",
       "20L31A5428                   B                        O   \n",
       "20L31A5429                   B                        O   \n",
       "20L31A5430                   A                        O   \n",
       "20L31A5431                   A                        O   \n",
       "\n",
       "Subname    R Programming for Data Science Lab Statistics for Data Science-I  \\\n",
       "Htno                                                                          \n",
       "20L31A5401                                  A                             O   \n",
       "20L31A5402                                  O                             A   \n",
       "20L31A5403                                  O                             C   \n",
       "20L31A5404                                  O                             O   \n",
       "20L31A5405                                  A                             B   \n",
       "20L31A5406                                  O                             C   \n",
       "20L31A5407                                  A                             D   \n",
       "20L31A5408                                  O                             D   \n",
       "20L31A5409                                  O                             O   \n",
       "20L31A5411                                  O                             A   \n",
       "20L31A5412                                  A                             D   \n",
       "20L31A5413                                  O                             A   \n",
       "20L31A5414                                  A                             O   \n",
       "20L31A5415                                  A                             C   \n",
       "20L31A5416                                  A                             B   \n",
       "20L31A5417                                  A                             F   \n",
       "20L31A5418                                  A                             O   \n",
       "20L31A5419                                  A                             A   \n",
       "20L31A5420                                  O                             A   \n",
       "20L31A5421                                  A                             F   \n",
       "20L31A5422                                  A                             F   \n",
       "20L31A5423                                  A                             A   \n",
       "20L31A5424                                  O                             A   \n",
       "20L31A5425                                  A                             O   \n",
       "20L31A5426                                  O                             O   \n",
       "20L31A5427                                  A                             C   \n",
       "20L31A5428                                  A                             O   \n",
       "20L31A5429                                  A                             B   \n",
       "20L31A5430                                  A                             C   \n",
       "20L31A5431                                  A                             O   \n",
       "\n",
       "Subname    Web Design Web Design Lab  \n",
       "Htno                                  \n",
       "20L31A5401          B              O  \n",
       "20L31A5402          C              A  \n",
       "20L31A5403          C              A  \n",
       "20L31A5404          B              O  \n",
       "20L31A5405          C              A  \n",
       "20L31A5406          D              A  \n",
       "20L31A5407          D              A  \n",
       "20L31A5408          D              A  \n",
       "20L31A5409          D              O  \n",
       "20L31A5411          B              A  \n",
       "20L31A5412          F              A  \n",
       "20L31A5413          B              O  \n",
       "20L31A5414          B              A  \n",
       "20L31A5415          C              O  \n",
       "20L31A5416          C              A  \n",
       "20L31A5417          D              A  \n",
       "20L31A5418          B              O  \n",
       "20L31A5419          C              O  \n",
       "20L31A5420          C              O  \n",
       "20L31A5421          D              A  \n",
       "20L31A5422          D              O  \n",
       "20L31A5423          C              A  \n",
       "20L31A5424          C              A  \n",
       "20L31A5425          C              O  \n",
       "20L31A5426          B              O  \n",
       "20L31A5427          B              A  \n",
       "20L31A5428          C              O  \n",
       "20L31A5429          C              O  \n",
       "20L31A5430          C              O  \n",
       "20L31A5431          C              O  "
      ]
     },
     "execution_count": 162,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "table = df.pivot(index='Htno', columns='Subname', values='Grade').fillna(0)\n",
    "table.head(30)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 163,
   "id": "8841cf4a",
   "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>Htno</th>\n",
       "      <th>Subcode</th>\n",
       "      <th>Subname</th>\n",
       "      <th>Grade</th>\n",
       "      <th>Credits</th>\n",
       "      <th>GradePt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>20L31A5401</td>\n",
       "      <td>1000201205</td>\n",
       "      <td>Statistics for Data Science-I</td>\n",
       "      <td>O</td>\n",
       "      <td>3.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20L31A5401</td>\n",
       "      <td>1005201201</td>\n",
       "      <td>Computer Organization</td>\n",
       "      <td>C</td>\n",
       "      <td>3.0</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>20L31A5401</td>\n",
       "      <td>1005201202</td>\n",
       "      <td>Web Design</td>\n",
       "      <td>B</td>\n",
       "      <td>3.0</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>20L31A5401</td>\n",
       "      <td>1005201203</td>\n",
       "      <td>Data Structures</td>\n",
       "      <td>B</td>\n",
       "      <td>3.0</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>20L31A5401</td>\n",
       "      <td>1005201211</td>\n",
       "      <td>Web Design Lab</td>\n",
       "      <td>O</td>\n",
       "      <td>1.5</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>20L31A5401</td>\n",
       "      <td>1005201212</td>\n",
       "      <td>Data Structures Lab</td>\n",
       "      <td>C</td>\n",
       "      <td>1.5</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>20L31A5401</td>\n",
       "      <td>1000201213</td>\n",
       "      <td>Physics of Materials Lab</td>\n",
       "      <td>A</td>\n",
       "      <td>1.5</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>20L31A5401</td>\n",
       "      <td>1000201214</td>\n",
       "      <td>R Programming for Data Science Lab</td>\n",
       "      <td>A</td>\n",
       "      <td>1.5</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>20L31A5401</td>\n",
       "      <td>1000201121</td>\n",
       "      <td>Constitution of India</td>\n",
       "      <td>CP</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>20L31A5402</td>\n",
       "      <td>1000201205</td>\n",
       "      <td>Statistics for Data Science-I</td>\n",
       "      <td>A</td>\n",
       "      <td>3.0</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Htno     Subcode                             Subname Grade  Credits  \\\n",
       "0  20L31A5401  1000201205       Statistics for Data Science-I     O      3.0   \n",
       "1  20L31A5401  1005201201               Computer Organization     C      3.0   \n",
       "2  20L31A5401  1005201202                          Web Design     B      3.0   \n",
       "3  20L31A5401  1005201203                     Data Structures     B      3.0   \n",
       "4  20L31A5401  1005201211                      Web Design Lab     O      1.5   \n",
       "5  20L31A5401  1005201212                 Data Structures Lab     C      1.5   \n",
       "6  20L31A5401  1000201213            Physics of Materials Lab     A      1.5   \n",
       "7  20L31A5401  1000201214  R Programming for Data Science Lab     A      1.5   \n",
       "8  20L31A5401  1000201121               Constitution of India    CP      0.0   \n",
       "9  20L31A5402  1000201205       Statistics for Data Science-I     A      3.0   \n",
       "\n",
       "   GradePt  \n",
       "0     10.0  \n",
       "1      7.0  \n",
       "2      8.0  \n",
       "3      8.0  \n",
       "4     10.0  \n",
       "5      7.0  \n",
       "6      9.0  \n",
       "7      9.0  \n",
       "8      0.0  \n",
       "9      9.0  "
      ]
     },
     "execution_count": 163,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grade_dict = {'O':10,'A':9,'B':8,'C':7,'D':6,'F':-1}\n",
    "\n",
    "\n",
    "df['GradePt'] = (df['Grade'].map(grade_dict).fillna(0))\n",
    "df['GradePt'] = df['GradePt'].astype(float)\n",
    "df = df.replace(to_replace =\"-\",\n",
    "                 value =\"0\")\n",
    "df['Credits'] = df['Credits'].astype(float)\n",
    "\n",
    "df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 164,
   "id": "97768bd3",
   "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>Htno</th>\n",
       "      <th>Subcode</th>\n",
       "      <th>Subname</th>\n",
       "      <th>Grade</th>\n",
       "      <th>Credits</th>\n",
       "      <th>GradePt</th>\n",
       "      <th>CreditsMul</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>20L31A5401</td>\n",
       "      <td>1000201205</td>\n",
       "      <td>Statistics for Data Science-I</td>\n",
       "      <td>O</td>\n",
       "      <td>3.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>30.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20L31A5401</td>\n",
       "      <td>1005201201</td>\n",
       "      <td>Computer Organization</td>\n",
       "      <td>C</td>\n",
       "      <td>3.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>21.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>20L31A5401</td>\n",
       "      <td>1005201202</td>\n",
       "      <td>Web Design</td>\n",
       "      <td>B</td>\n",
       "      <td>3.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>24.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>20L31A5401</td>\n",
       "      <td>1005201203</td>\n",
       "      <td>Data Structures</td>\n",
       "      <td>B</td>\n",
       "      <td>3.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>24.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>20L31A5401</td>\n",
       "      <td>1005201211</td>\n",
       "      <td>Web Design Lab</td>\n",
       "      <td>O</td>\n",
       "      <td>1.5</td>\n",
       "      <td>10.0</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>20L31A5401</td>\n",
       "      <td>1005201212</td>\n",
       "      <td>Data Structures Lab</td>\n",
       "      <td>C</td>\n",
       "      <td>1.5</td>\n",
       "      <td>7.0</td>\n",
       "      <td>10.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>20L31A5401</td>\n",
       "      <td>1000201213</td>\n",
       "      <td>Physics of Materials Lab</td>\n",
       "      <td>A</td>\n",
       "      <td>1.5</td>\n",
       "      <td>9.0</td>\n",
       "      <td>13.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>20L31A5401</td>\n",
       "      <td>1000201214</td>\n",
       "      <td>R Programming for Data Science Lab</td>\n",
       "      <td>A</td>\n",
       "      <td>1.5</td>\n",
       "      <td>9.0</td>\n",
       "      <td>13.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>20L31A5401</td>\n",
       "      <td>1000201121</td>\n",
       "      <td>Constitution of India</td>\n",
       "      <td>CP</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>20L31A5402</td>\n",
       "      <td>1000201205</td>\n",
       "      <td>Statistics for Data Science-I</td>\n",
       "      <td>A</td>\n",
       "      <td>3.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>27.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Htno     Subcode                             Subname Grade  Credits  \\\n",
       "0  20L31A5401  1000201205       Statistics for Data Science-I     O      3.0   \n",
       "1  20L31A5401  1005201201               Computer Organization     C      3.0   \n",
       "2  20L31A5401  1005201202                          Web Design     B      3.0   \n",
       "3  20L31A5401  1005201203                     Data Structures     B      3.0   \n",
       "4  20L31A5401  1005201211                      Web Design Lab     O      1.5   \n",
       "5  20L31A5401  1005201212                 Data Structures Lab     C      1.5   \n",
       "6  20L31A5401  1000201213            Physics of Materials Lab     A      1.5   \n",
       "7  20L31A5401  1000201214  R Programming for Data Science Lab     A      1.5   \n",
       "8  20L31A5401  1000201121               Constitution of India    CP      0.0   \n",
       "9  20L31A5402  1000201205       Statistics for Data Science-I     A      3.0   \n",
       "\n",
       "   GradePt  CreditsMul  \n",
       "0     10.0        30.0  \n",
       "1      7.0        21.0  \n",
       "2      8.0        24.0  \n",
       "3      8.0        24.0  \n",
       "4     10.0        15.0  \n",
       "5      7.0        10.5  \n",
       "6      9.0        13.5  \n",
       "7      9.0        13.5  \n",
       "8      0.0         0.0  \n",
       "9      9.0        27.0  "
      ]
     },
     "execution_count": 164,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['CreditsMul'] = df.Credits * df.GradePt\n",
    "df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 165,
   "id": "30ab5863",
   "metadata": {},
   "outputs": [],
   "source": [
    "stud_sgpa_dict= {}\n",
    "stud_percentage_dict= {}\n",
    "\n",
    "ranks_df = pd.DataFrame()\n",
    "ranks_df['Rollno'] = df['Htno'].unique()\n",
    "\n",
    "for i in htno:\n",
    "    stud_df = df[df['Htno']==i]\n",
    "    stud_df.head(10)\n",
    "    sem_total_crdits = stud_df['Credits'].sum()\n",
    "    stud_total_crdits = stud_df['CreditsMul'].sum()\n",
    "    stud_sgpa = round(stud_total_crdits/sem_total_crdits,4)\n",
    "    stud_sem_percentage = (stud_sgpa*10)-7.5\n",
    "    stud_sgpa_dict.update({i:stud_sgpa})\n",
    "    stud_percentage_dict.update({i:stud_sem_percentage})\n",
    "\n",
    "\n",
    "stud_sgpa = pd.DataFrame([stud_sgpa_dict])\n",
    "stud_sgpa_df = stud_sgpa.T\n",
    "stud_percentage = pd.DataFrame([stud_percentage_dict]) \n",
    "stud_percentage_df = stud_percentage.T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "id": "af2aa1a1",
   "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>Rollno</th>\n",
       "      <th>Sgpa</th>\n",
       "      <th>Percentage</th>\n",
       "      <th>Rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20L31A5452</td>\n",
       "      <td>9.3333</td>\n",
       "      <td>85.833</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>20L31A5426</td>\n",
       "      <td>9.3333</td>\n",
       "      <td>85.833</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>20L31A5418</td>\n",
       "      <td>9.3333</td>\n",
       "      <td>85.833</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>20L31A5414</td>\n",
       "      <td>9.3333</td>\n",
       "      <td>85.833</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>20L31A5404</td>\n",
       "      <td>9.2500</td>\n",
       "      <td>85.000</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>20L31A5454</td>\n",
       "      <td>9.2500</td>\n",
       "      <td>85.000</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>20L31A5455</td>\n",
       "      <td>9.1667</td>\n",
       "      <td>84.167</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>20L31A5450</td>\n",
       "      <td>9.1667</td>\n",
       "      <td>84.167</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>20L31A5457</td>\n",
       "      <td>9.0000</td>\n",
       "      <td>82.500</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>20L31A5413</td>\n",
       "      <td>8.9167</td>\n",
       "      <td>81.667</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Rollno    Sgpa  Percentage  Rank\n",
       "1   20L31A5452  9.3333      85.833     1\n",
       "2   20L31A5426  9.3333      85.833     2\n",
       "3   20L31A5418  9.3333      85.833     3\n",
       "4   20L31A5414  9.3333      85.833     4\n",
       "5   20L31A5404  9.2500      85.000     5\n",
       "6   20L31A5454  9.2500      85.000     6\n",
       "7   20L31A5455  9.1667      84.167     7\n",
       "8   20L31A5450  9.1667      84.167     8\n",
       "9   20L31A5457  9.0000      82.500     9\n",
       "10  20L31A5413  8.9167      81.667    10"
      ]
     },
     "execution_count": 166,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ranks_df = pd.concat([stud_sgpa_df,stud_percentage_df],axis=1,sort=True)\n",
    "ranks_df.columns = ['Sgpa','Percentage']\n",
    "ranks_df = ranks_df.sort_values(by=['Sgpa'], ascending=False)\n",
    "ranks_df.reset_index(inplace=True)\n",
    "ranks_df.rename(columns = {'index':'Rollno'}, inplace = True)\n",
    "ranks_df.index+=1\n",
    "ranks_df['Rank'] = ranks_df.index\n",
    "ranks_df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 167,
   "id": "67c89323",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'20L31A5452'"
      ]
     },
     "execution_count": 167,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "class_topper_roll = ranks_df.iloc[0]['Rollno']\n",
    "class_topper_roll "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 168,
   "id": "f0af82c5",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "8.2392\n",
      "74.8915\n"
     ]
    }
   ],
   "source": [
    "avg_sgpa = round(ranks_df['Sgpa'].mean(),4)\n",
    "avg_percentage = round(ranks_df['Percentage'].mean(),4)\n",
    "\n",
    "print(avg_sgpa)\n",
    "print(avg_percentage)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 169,
   "id": "8c3977a8",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'Statistics for Data Science-I': 'O',\n",
       " 'Computer Organization': 'A',\n",
       " 'Web Design': 'C',\n",
       " 'Data Structures': 'B',\n",
       " 'Web Design Lab': 'O',\n",
       " 'Data Structures Lab': 'B',\n",
       " 'Physics of Materials Lab': 'O',\n",
       " 'R Programming for Data Science Lab': 'A',\n",
       " 'Constitution of India': 'CP'}"
      ]
     },
     "execution_count": 169,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "avg_subject_grade_dict = {}\n",
    "for subject in subjects:\n",
    "    single_subject = df.loc[df['Subname']==subject]\n",
    "    single_subject = single_subject['Grade'].value_counts().to_dict()\n",
    "    avg_grade = max(single_subject, key= lambda x: single_subject[x])\n",
    "    avg_subject_grade_dict.update({subject:avg_grade})\n",
    "\n",
    "avg_subject_grade_dict  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 170,
   "id": "d38be695",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Enter roll number: 20L31A5413\n",
      "8.9167\n",
      "81.667\n"
     ]
    },
    {
     "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>Htno</th>\n",
       "      <th>Subcode</th>\n",
       "      <th>Subname</th>\n",
       "      <th>Grade</th>\n",
       "      <th>Credits</th>\n",
       "      <th>GradePt</th>\n",
       "      <th>CreditsMul</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>20L31A5413</td>\n",
       "      <td>1000201205</td>\n",
       "      <td>Statistics for Data Science-I</td>\n",
       "      <td>A</td>\n",
       "      <td>3.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>27.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100</th>\n",
       "      <td>20L31A5413</td>\n",
       "      <td>1005201201</td>\n",
       "      <td>Computer Organization</td>\n",
       "      <td>B</td>\n",
       "      <td>3.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>24.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101</th>\n",
       "      <td>20L31A5413</td>\n",
       "      <td>1005201202</td>\n",
       "      <td>Web Design</td>\n",
       "      <td>B</td>\n",
       "      <td>3.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>24.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102</th>\n",
       "      <td>20L31A5413</td>\n",
       "      <td>1005201203</td>\n",
       "      <td>Data Structures</td>\n",
       "      <td>A</td>\n",
       "      <td>3.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>27.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103</th>\n",
       "      <td>20L31A5413</td>\n",
       "      <td>1005201211</td>\n",
       "      <td>Web Design Lab</td>\n",
       "      <td>O</td>\n",
       "      <td>1.5</td>\n",
       "      <td>10.0</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>104</th>\n",
       "      <td>20L31A5413</td>\n",
       "      <td>1005201212</td>\n",
       "      <td>Data Structures Lab</td>\n",
       "      <td>A</td>\n",
       "      <td>1.5</td>\n",
       "      <td>9.0</td>\n",
       "      <td>13.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105</th>\n",
       "      <td>20L31A5413</td>\n",
       "      <td>1000201213</td>\n",
       "      <td>Physics of Materials Lab</td>\n",
       "      <td>O</td>\n",
       "      <td>1.5</td>\n",
       "      <td>10.0</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106</th>\n",
       "      <td>20L31A5413</td>\n",
       "      <td>1000201214</td>\n",
       "      <td>R Programming for Data Science Lab</td>\n",
       "      <td>O</td>\n",
       "      <td>1.5</td>\n",
       "      <td>10.0</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>107</th>\n",
       "      <td>20L31A5413</td>\n",
       "      <td>1000201121</td>\n",
       "      <td>Constitution of India</td>\n",
       "      <td>CP</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Htno     Subcode                             Subname Grade  \\\n",
       "99   20L31A5413  1000201205       Statistics for Data Science-I     A   \n",
       "100  20L31A5413  1005201201               Computer Organization     B   \n",
       "101  20L31A5413  1005201202                          Web Design     B   \n",
       "102  20L31A5413  1005201203                     Data Structures     A   \n",
       "103  20L31A5413  1005201211                      Web Design Lab     O   \n",
       "104  20L31A5413  1005201212                 Data Structures Lab     A   \n",
       "105  20L31A5413  1000201213            Physics of Materials Lab     O   \n",
       "106  20L31A5413  1000201214  R Programming for Data Science Lab     O   \n",
       "107  20L31A5413  1000201121               Constitution of India    CP   \n",
       "\n",
       "     Credits  GradePt  CreditsMul  \n",
       "99       3.0      9.0        27.0  \n",
       "100      3.0      8.0        24.0  \n",
       "101      3.0      8.0        24.0  \n",
       "102      3.0      9.0        27.0  \n",
       "103      1.5     10.0        15.0  \n",
       "104      1.5      9.0        13.5  \n",
       "105      1.5     10.0        15.0  \n",
       "106      1.5     10.0        15.0  \n",
       "107      0.0      0.0         0.0  "
      ]
     },
     "execution_count": 170,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "roll = str(input(\"Enter roll number: \"))\n",
    "stud_df = df[df['Htno']==roll]\n",
    "\n",
    "temp_stud_df = df[df['Htno']==class_topper_roll]\n",
    "\n",
    "\n",
    "\n",
    "sem_total_credits = temp_stud_df['Credits'].sum()\n",
    "\n",
    "stud_total_credits = stud_df['CreditsMul'].sum()\n",
    "\n",
    "stud_sgpa = round(stud_total_credits/sem_total_credits,4)\n",
    "stud_sem_percentage = (stud_sgpa*10)-7.5\n",
    "\n",
    "print(stud_sgpa)\n",
    "print(stud_sem_percentage)\n",
    "stud_df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 181,
   "id": "891c64db",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "10\n"
     ]
    }
   ],
   "source": [
    "temp_rank_df = ranks_df.loc[ranks_df['Rollno']==roll]\n",
    "stdu_rank = temp_rank_df['Rank'].tolist()\n",
    "print(stdu_rank[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 172,
   "id": "cb8758ca",
   "metadata": {},
   "outputs": [],
   "source": [
    "stud_df = stud_df.drop(['Htno', 'Subcode','Credits','GradePt','CreditsMul'], axis = 1)\n",
    "stud_df_dict = stud_df.to_dict()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 173,
   "id": "2219f588",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'Subname': {99: 'Statistics for Data Science-I',\n",
       "  100: 'Computer Organization',\n",
       "  101: 'Web Design',\n",
       "  102: 'Data Structures',\n",
       "  103: 'Web Design Lab',\n",
       "  104: 'Data Structures Lab',\n",
       "  105: 'Physics of Materials Lab',\n",
       "  106: 'R Programming for Data Science Lab',\n",
       "  107: 'Constitution of India'},\n",
       " 'Grade': {99: 'A',\n",
       "  100: 'B',\n",
       "  101: 'B',\n",
       "  102: 'A',\n",
       "  103: 'O',\n",
       "  104: 'A',\n",
       "  105: 'O',\n",
       "  106: 'O',\n",
       "  107: 'CP'}}"
      ]
     },
     "execution_count": 173,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stud_df_dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 174,
   "id": "55e3cf4a",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Statistics for Data Science-I --> A\n",
      "Computer Organization --> B\n",
      "Web Design --> B\n",
      "Data Structures --> A\n",
      "Web Design Lab --> O\n",
      "Data Structures Lab --> A\n",
      "Physics of Materials Lab --> O\n",
      "R Programming for Data Science Lab --> O\n",
      "Constitution of India --> CP\n",
      "\n",
      "Your Total Credits = 160.5\n",
      "Semester Total Credits = 18.0\n",
      "\n",
      "Your SGPA is 8.9167\n",
      "Average SGPA in class is 8.2392\n",
      "\n",
      "Your Semester percentage is 81.667%\n",
      "Average Semester percentage in class is 74.8915%\n",
      "\n",
      "You secured 10 Rank in the class\n"
     ]
    }
   ],
   "source": [
    "message = ''\n",
    "for i in stud_df_dict['Subname'].keys():\n",
    "    message += str(stud_df_dict['Subname'].get(i)+' --> '+stud_df_dict['Grade'].get(i))+'\\n'\n",
    "message+= '\\n'+'Your Total Credits = '+str(stud_total_credits )+'\\n'+'Semester Total Credits = '+str(sem_total_credits)\n",
    "message+= '\\n\\n'+'Your SGPA is '+str(stud_sgpa)+'\\n'+'Average SGPA in class is '+str(avg_sgpa)\n",
    "message+= '\\n\\n'+'Your Semester percentage is '+str(stud_sem_percentage)+'%'+'\\n'+'Average Semester percentage in class is '+str(avg_percentage)+'%'\n",
    "message+= '\\n\\n'+f'You secured {stdu_rank} Rank in the class'\n",
    "print(message)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4c30dae7",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d2a196e6",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}