Allocation_Analysis.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "source": [
    "---\r\n",
    "# Loading data into a DataFrame\r\n",
    "---"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Importing Libraries:-"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "source": [
    "import pandas as pd\r\n",
    "import numpy as np\r\n",
    "import re"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "#### \n",
    "## Importing Data File:-\n",
    "### 1. Initial inspection of data.\n",
    "### 2. Understanding data.\n",
    "#### "
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "source": [
    "df = pd.read_csv(\"allocations.csv\")\r\n",
    "df.head()"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "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>Slot</th>\n",
       "      <th>Roll</th>\n",
       "      <th>Department</th>\n",
       "      <th>Program</th>\n",
       "      <th>Company</th>\n",
       "      <th>Job code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10.1</td>\n",
       "      <td>183059003</td>\n",
       "      <td>Computer Science &amp; Engineering</td>\n",
       "      <td>M.Tech.</td>\n",
       "      <td>Colortokens</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10.1</td>\n",
       "      <td>193100039</td>\n",
       "      <td>Mechanical Engineering</td>\n",
       "      <td>M.Tech.</td>\n",
       "      <td>Impact Guru Technology Ventures Pvt. Ltd.</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10.1</td>\n",
       "      <td>195280009</td>\n",
       "      <td>Applied Statistics and Informatics</td>\n",
       "      <td>M.Sc.</td>\n",
       "      <td>NeuralTechSoft</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10.1</td>\n",
       "      <td>193070021</td>\n",
       "      <td>Electrical Engineering</td>\n",
       "      <td>M.Tech.</td>\n",
       "      <td>Ignitarium Technology Solutions Pvt Ltd</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10.1</td>\n",
       "      <td>193060032</td>\n",
       "      <td>Earth Sciences</td>\n",
       "      <td>M.Tech.</td>\n",
       "      <td>Impact Guru Technology Ventures Pvt. Ltd.</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Slot       Roll                          Department  Program  \\\n",
       "0  10.1  183059003      Computer Science & Engineering  M.Tech.   \n",
       "1  10.1  193100039              Mechanical Engineering  M.Tech.   \n",
       "2  10.1  195280009  Applied Statistics and Informatics    M.Sc.   \n",
       "3  10.1  193070021              Electrical Engineering  M.Tech.   \n",
       "4  10.1  193060032                      Earth Sciences  M.Tech.   \n",
       "\n",
       "                                     Company  Job code  \n",
       "0                                Colortokens         1  \n",
       "1  Impact Guru Technology Ventures Pvt. Ltd.         1  \n",
       "2                             NeuralTechSoft         1  \n",
       "3    Ignitarium Technology Solutions Pvt Ltd         1  \n",
       "4  Impact Guru Technology Ventures Pvt. Ltd.         1  "
      ]
     },
     "metadata": {},
     "execution_count": 2
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "source": [
    "df.shape"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "(1139, 6)"
      ]
     },
     "metadata": {},
     "execution_count": 3
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "source": [
    "df[\"Slot\"].describe()"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "count     1139\n",
       "unique      23\n",
       "top          .\n",
       "freq       314\n",
       "Name: Slot, dtype: object"
      ]
     },
     "metadata": {},
     "execution_count": 4
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "source": [
    "df[\"Slot\"].value_counts()"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       ".            314\n",
       "1.1          100\n",
       "3.1          100\n",
       "2.2           94\n",
       "2.1           92\n",
       "5.1           68\n",
       "1.2           59\n",
       "10.1          45\n",
       "3.2           33\n",
       "4.2           30\n",
       "7.1           27\n",
       "6.1           25\n",
       "4.1           24\n",
       "8.1           23\n",
       "9.1           19\n",
       "14.1          19\n",
       "3 (IDC).1     18\n",
       "1 (IDC).1     12\n",
       "2 (IDC).1     11\n",
       "11.1           7\n",
       "12.1           7\n",
       "4 (IDC).1      7\n",
       "15.1           5\n",
       "Name: Slot, dtype: int64"
      ]
     },
     "metadata": {},
     "execution_count": 5
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "source": [
    "df[\"Slot\"].dtypes"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "dtype('O')"
      ]
     },
     "metadata": {},
     "execution_count": 6
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "---\r\n",
    "# Data Cleaning and Transformation of data:-\r\n",
    "--- "
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "source": [
    "def convert(x):\r\n",
    "    x = x.replace(\" (IDC)\",\"00\")\r\n",
    "    if x == \".\":\r\n",
    "        x = \"16.3\"\r\n",
    "    return x"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "source": [
    "convert(\"3 (IDC).1\")"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "'300.1'"
      ]
     },
     "metadata": {},
     "execution_count": 8
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "source": [
    "df[\"Slot\"] = df[\"Slot\"].apply(convert)"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "#### \n",
    "### Day 16 means 2nd phase placements, Slot 1 is from morning to evening, slot 2 is from evening to late night, slot 3 is full day in phase 2. For IDC, days are multiplied by 100 to differentiate from general placements; like \"Day 2' for IDC is represented as \"Day 200\" and so on.\n",
    "#### "
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "source": [
    "df[[\"Day\",\"Slot No.\"]] = df[\"Slot\"].str.split(\".\",expand = True)\r\n",
    "df.head(10)"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "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>Slot</th>\n",
       "      <th>Roll</th>\n",
       "      <th>Department</th>\n",
       "      <th>Program</th>\n",
       "      <th>Company</th>\n",
       "      <th>Job code</th>\n",
       "      <th>Day</th>\n",
       "      <th>Slot No.</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10.1</td>\n",
       "      <td>183059003</td>\n",
       "      <td>Computer Science &amp; Engineering</td>\n",
       "      <td>M.Tech.</td>\n",
       "      <td>Colortokens</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10.1</td>\n",
       "      <td>193100039</td>\n",
       "      <td>Mechanical Engineering</td>\n",
       "      <td>M.Tech.</td>\n",
       "      <td>Impact Guru Technology Ventures Pvt. Ltd.</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10.1</td>\n",
       "      <td>195280009</td>\n",
       "      <td>Applied Statistics and Informatics</td>\n",
       "      <td>M.Sc.</td>\n",
       "      <td>NeuralTechSoft</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10.1</td>\n",
       "      <td>193070021</td>\n",
       "      <td>Electrical Engineering</td>\n",
       "      <td>M.Tech.</td>\n",
       "      <td>Ignitarium Technology Solutions Pvt Ltd</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10.1</td>\n",
       "      <td>193060032</td>\n",
       "      <td>Earth Sciences</td>\n",
       "      <td>M.Tech.</td>\n",
       "      <td>Impact Guru Technology Ventures Pvt. Ltd.</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>10.1</td>\n",
       "      <td>183310001</td>\n",
       "      <td>Geoinformatics and Natural Resources Engineering</td>\n",
       "      <td>M.Tech.</td>\n",
       "      <td>Regent Climate Connect Knowledge Solutions Pri...</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>10.1</td>\n",
       "      <td>193050057</td>\n",
       "      <td>Computer Science &amp; Engineering</td>\n",
       "      <td>M.Tech.</td>\n",
       "      <td>Colortokens</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>10.1</td>\n",
       "      <td>170070003</td>\n",
       "      <td>Electrical Engineering</td>\n",
       "      <td>B.Tech.</td>\n",
       "      <td>BeeHyv</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>10.1</td>\n",
       "      <td>193170012</td>\n",
       "      <td>Energy Science and Engineering</td>\n",
       "      <td>M.Tech.</td>\n",
       "      <td>Impact Guru Technology Ventures Pvt. Ltd.</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10.1</td>\n",
       "      <td>183109005</td>\n",
       "      <td>Mechanical Engineering</td>\n",
       "      <td>M.Tech.</td>\n",
       "      <td>Applied Materials</td>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Slot       Roll                                        Department  Program  \\\n",
       "0  10.1  183059003                    Computer Science & Engineering  M.Tech.   \n",
       "1  10.1  193100039                            Mechanical Engineering  M.Tech.   \n",
       "2  10.1  195280009                Applied Statistics and Informatics    M.Sc.   \n",
       "3  10.1  193070021                            Electrical Engineering  M.Tech.   \n",
       "4  10.1  193060032                                    Earth Sciences  M.Tech.   \n",
       "5  10.1  183310001  Geoinformatics and Natural Resources Engineering  M.Tech.   \n",
       "6  10.1  193050057                    Computer Science & Engineering  M.Tech.   \n",
       "7  10.1  170070003                            Electrical Engineering  B.Tech.   \n",
       "8  10.1  193170012                    Energy Science and Engineering  M.Tech.   \n",
       "9  10.1  183109005                            Mechanical Engineering  M.Tech.   \n",
       "\n",
       "                                             Company  Job code Day Slot No.  \n",
       "0                                        Colortokens         1  10        1  \n",
       "1          Impact Guru Technology Ventures Pvt. Ltd.         1  10        1  \n",
       "2                                     NeuralTechSoft         1  10        1  \n",
       "3            Ignitarium Technology Solutions Pvt Ltd         1  10        1  \n",
       "4          Impact Guru Technology Ventures Pvt. Ltd.         1  10        1  \n",
       "5  Regent Climate Connect Knowledge Solutions Pri...         1  10        1  \n",
       "6                                        Colortokens         1  10        1  \n",
       "7                                             BeeHyv         1  10        1  \n",
       "8          Impact Guru Technology Ventures Pvt. Ltd.         1  10        1  \n",
       "9                                  Applied Materials         2  10        1  "
      ]
     },
     "metadata": {},
     "execution_count": 10
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "source": [
    "df.tail()"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "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>Slot</th>\n",
       "      <th>Roll</th>\n",
       "      <th>Department</th>\n",
       "      <th>Program</th>\n",
       "      <th>Company</th>\n",
       "      <th>Job code</th>\n",
       "      <th>Day</th>\n",
       "      <th>Slot No.</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1134</th>\n",
       "      <td>16.3</td>\n",
       "      <td>170050005</td>\n",
       "      <td>Computer Science &amp; Engineering</td>\n",
       "      <td>B.Tech.</td>\n",
       "      <td>Placement Office</td>\n",
       "      <td>4</td>\n",
       "      <td>16</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1135</th>\n",
       "      <td>16.3</td>\n",
       "      <td>170100028</td>\n",
       "      <td>Mechanical Engineering</td>\n",
       "      <td>B.Tech.</td>\n",
       "      <td>Placement Office</td>\n",
       "      <td>4</td>\n",
       "      <td>16</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1136</th>\n",
       "      <td>16.3</td>\n",
       "      <td>193100059</td>\n",
       "      <td>Mechanical Engineering</td>\n",
       "      <td>M.Tech.</td>\n",
       "      <td>BYJU'S - The Learning App</td>\n",
       "      <td>1</td>\n",
       "      <td>16</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1137</th>\n",
       "      <td>16.3</td>\n",
       "      <td>193100075</td>\n",
       "      <td>Mechanical Engineering</td>\n",
       "      <td>M.Tech.</td>\n",
       "      <td>Solar Energy Corporation Of India Limited</td>\n",
       "      <td>2</td>\n",
       "      <td>16</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1138</th>\n",
       "      <td>16.3</td>\n",
       "      <td>16D070061</td>\n",
       "      <td>Electrical Engineering</td>\n",
       "      <td>Dual Degree (B.Tech. + M.Tech.)</td>\n",
       "      <td>Accenture</td>\n",
       "      <td>1</td>\n",
       "      <td>16</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Slot       Roll                      Department  \\\n",
       "1134  16.3  170050005  Computer Science & Engineering   \n",
       "1135  16.3  170100028          Mechanical Engineering   \n",
       "1136  16.3  193100059          Mechanical Engineering   \n",
       "1137  16.3  193100075          Mechanical Engineering   \n",
       "1138  16.3  16D070061          Electrical Engineering   \n",
       "\n",
       "                              Program  \\\n",
       "1134                          B.Tech.   \n",
       "1135                          B.Tech.   \n",
       "1136                          M.Tech.   \n",
       "1137                          M.Tech.   \n",
       "1138  Dual Degree (B.Tech. + M.Tech.)   \n",
       "\n",
       "                                        Company  Job code Day Slot No.  \n",
       "1134                           Placement Office         4  16        3  \n",
       "1135                           Placement Office         4  16        3  \n",
       "1136                  BYJU'S - The Learning App         1  16        3  \n",
       "1137  Solar Energy Corporation Of India Limited         2  16        3  \n",
       "1138                                  Accenture         1  16        3  "
      ]
     },
     "metadata": {},
     "execution_count": 11
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "source": [
    "df.drop(columns = \"Slot\", inplace=True)"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "source": [
    "df[\"Day\"] = pd.to_numeric(df[\"Day\"])"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "source": [
    "df[\"Slot No.\"] = pd.to_numeric(df[\"Slot No.\"])"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "source": [
    "df.head()"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "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>Roll</th>\n",
       "      <th>Department</th>\n",
       "      <th>Program</th>\n",
       "      <th>Company</th>\n",
       "      <th>Job code</th>\n",
       "      <th>Day</th>\n",
       "      <th>Slot No.</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>183059003</td>\n",
       "      <td>Computer Science &amp; Engineering</td>\n",
       "      <td>M.Tech.</td>\n",
       "      <td>Colortokens</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>193100039</td>\n",
       "      <td>Mechanical Engineering</td>\n",
       "      <td>M.Tech.</td>\n",
       "      <td>Impact Guru Technology Ventures Pvt. Ltd.</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>195280009</td>\n",
       "      <td>Applied Statistics and Informatics</td>\n",
       "      <td>M.Sc.</td>\n",
       "      <td>NeuralTechSoft</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>193070021</td>\n",
       "      <td>Electrical Engineering</td>\n",
       "      <td>M.Tech.</td>\n",
       "      <td>Ignitarium Technology Solutions Pvt Ltd</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>193060032</td>\n",
       "      <td>Earth Sciences</td>\n",
       "      <td>M.Tech.</td>\n",
       "      <td>Impact Guru Technology Ventures Pvt. Ltd.</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Roll                          Department  Program  \\\n",
       "0  183059003      Computer Science & Engineering  M.Tech.   \n",
       "1  193100039              Mechanical Engineering  M.Tech.   \n",
       "2  195280009  Applied Statistics and Informatics    M.Sc.   \n",
       "3  193070021              Electrical Engineering  M.Tech.   \n",
       "4  193060032                      Earth Sciences  M.Tech.   \n",
       "\n",
       "                                     Company  Job code  Day  Slot No.  \n",
       "0                                Colortokens         1   10         1  \n",
       "1  Impact Guru Technology Ventures Pvt. Ltd.         1   10         1  \n",
       "2                             NeuralTechSoft         1   10         1  \n",
       "3    Ignitarium Technology Solutions Pvt Ltd         1   10         1  \n",
       "4  Impact Guru Technology Ventures Pvt. Ltd.         1   10         1  "
      ]
     },
     "metadata": {},
     "execution_count": 15
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "source": [
    "df.drop(columns = \"Job code\", inplace=True)"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "source": [
    "df.head()"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "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>Roll</th>\n",
       "      <th>Department</th>\n",
       "      <th>Program</th>\n",
       "      <th>Company</th>\n",
       "      <th>Day</th>\n",
       "      <th>Slot No.</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>183059003</td>\n",
       "      <td>Computer Science &amp; Engineering</td>\n",
       "      <td>M.Tech.</td>\n",
       "      <td>Colortokens</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>193100039</td>\n",
       "      <td>Mechanical Engineering</td>\n",
       "      <td>M.Tech.</td>\n",
       "      <td>Impact Guru Technology Ventures Pvt. Ltd.</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>195280009</td>\n",
       "      <td>Applied Statistics and Informatics</td>\n",
       "      <td>M.Sc.</td>\n",
       "      <td>NeuralTechSoft</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>193070021</td>\n",
       "      <td>Electrical Engineering</td>\n",
       "      <td>M.Tech.</td>\n",
       "      <td>Ignitarium Technology Solutions Pvt Ltd</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>193060032</td>\n",
       "      <td>Earth Sciences</td>\n",
       "      <td>M.Tech.</td>\n",
       "      <td>Impact Guru Technology Ventures Pvt. Ltd.</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Roll                          Department  Program  \\\n",
       "0  183059003      Computer Science & Engineering  M.Tech.   \n",
       "1  193100039              Mechanical Engineering  M.Tech.   \n",
       "2  195280009  Applied Statistics and Informatics    M.Sc.   \n",
       "3  193070021              Electrical Engineering  M.Tech.   \n",
       "4  193060032                      Earth Sciences  M.Tech.   \n",
       "\n",
       "                                     Company  Day  Slot No.  \n",
       "0                                Colortokens   10         1  \n",
       "1  Impact Guru Technology Ventures Pvt. Ltd.   10         1  \n",
       "2                             NeuralTechSoft   10         1  \n",
       "3    Ignitarium Technology Solutions Pvt Ltd   10         1  \n",
       "4  Impact Guru Technology Ventures Pvt. Ltd.   10         1  "
      ]
     },
     "metadata": {},
     "execution_count": 17
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### \n",
    "## Final inspection of data after the tranformation:-\n",
    "### "
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "source": [
    "df_BTech = df[df.Program == \"B.Tech.\"]\r\n",
    "df_BTech.head()"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "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>Roll</th>\n",
       "      <th>Department</th>\n",
       "      <th>Program</th>\n",
       "      <th>Company</th>\n",
       "      <th>Day</th>\n",
       "      <th>Slot No.</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>170070003</td>\n",
       "      <td>Electrical Engineering</td>\n",
       "      <td>B.Tech.</td>\n",
       "      <td>BeeHyv</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>160110047</td>\n",
       "      <td>Metallurgical Engineering and Materials Science</td>\n",
       "      <td>B.Tech.</td>\n",
       "      <td>Agrifi</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>170050085</td>\n",
       "      <td>Computer Science &amp; Engineering</td>\n",
       "      <td>B.Tech.</td>\n",
       "      <td>Agrifi</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>170020120</td>\n",
       "      <td>Chemical Engineering</td>\n",
       "      <td>B.Tech.</td>\n",
       "      <td>BeeHyv</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>170100087</td>\n",
       "      <td>Mechanical Engineering</td>\n",
       "      <td>B.Tech.</td>\n",
       "      <td>BeeHyv</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Roll                                       Department  Program  \\\n",
       "7   170070003                           Electrical Engineering  B.Tech.   \n",
       "16  160110047  Metallurgical Engineering and Materials Science  B.Tech.   \n",
       "17  170050085                   Computer Science & Engineering  B.Tech.   \n",
       "19  170020120                             Chemical Engineering  B.Tech.   \n",
       "23  170100087                           Mechanical Engineering  B.Tech.   \n",
       "\n",
       "   Company  Day  Slot No.  \n",
       "7   BeeHyv   10         1  \n",
       "16  Agrifi   10         1  \n",
       "17  Agrifi   10         1  \n",
       "19  BeeHyv   10         1  \n",
       "23  BeeHyv   10         1  "
      ]
     },
     "metadata": {},
     "execution_count": 18
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "source": [
    "df_MTech = df[df.Program == \"M.Tech.\"]\r\n",
    "df_MTech.head()"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "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>Roll</th>\n",
       "      <th>Department</th>\n",
       "      <th>Program</th>\n",
       "      <th>Company</th>\n",
       "      <th>Day</th>\n",
       "      <th>Slot No.</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>183059003</td>\n",
       "      <td>Computer Science &amp; Engineering</td>\n",
       "      <td>M.Tech.</td>\n",
       "      <td>Colortokens</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>193100039</td>\n",
       "      <td>Mechanical Engineering</td>\n",
       "      <td>M.Tech.</td>\n",
       "      <td>Impact Guru Technology Ventures Pvt. Ltd.</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>193070021</td>\n",
       "      <td>Electrical Engineering</td>\n",
       "      <td>M.Tech.</td>\n",
       "      <td>Ignitarium Technology Solutions Pvt Ltd</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>193060032</td>\n",
       "      <td>Earth Sciences</td>\n",
       "      <td>M.Tech.</td>\n",
       "      <td>Impact Guru Technology Ventures Pvt. Ltd.</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>183310001</td>\n",
       "      <td>Geoinformatics and Natural Resources Engineering</td>\n",
       "      <td>M.Tech.</td>\n",
       "      <td>Regent Climate Connect Knowledge Solutions Pri...</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Roll                                        Department  Program  \\\n",
       "0  183059003                    Computer Science & Engineering  M.Tech.   \n",
       "1  193100039                            Mechanical Engineering  M.Tech.   \n",
       "3  193070021                            Electrical Engineering  M.Tech.   \n",
       "4  193060032                                    Earth Sciences  M.Tech.   \n",
       "5  183310001  Geoinformatics and Natural Resources Engineering  M.Tech.   \n",
       "\n",
       "                                             Company  Day  Slot No.  \n",
       "0                                        Colortokens   10         1  \n",
       "1          Impact Guru Technology Ventures Pvt. Ltd.   10         1  \n",
       "3            Ignitarium Technology Solutions Pvt Ltd   10         1  \n",
       "4          Impact Guru Technology Ventures Pvt. Ltd.   10         1  \n",
       "5  Regent Climate Connect Knowledge Solutions Pri...   10         1  "
      ]
     },
     "metadata": {},
     "execution_count": 19
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "source": [
    "df_Dual = df[df.Program.str.contains(\"Dual Degree\")]\r\n",
    "df_Dual.head()"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "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>Roll</th>\n",
       "      <th>Department</th>\n",
       "      <th>Program</th>\n",
       "      <th>Company</th>\n",
       "      <th>Day</th>\n",
       "      <th>Slot No.</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>16D070026</td>\n",
       "      <td>Electrical Engineering</td>\n",
       "      <td>Dual Degree (B.Tech. + M.Tech.)</td>\n",
       "      <td>Ignitarium Technology Solutions Pvt Ltd</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>16D070027</td>\n",
       "      <td>Electrical Engineering</td>\n",
       "      <td>Dual Degree (B.Tech. + M.Tech.)</td>\n",
       "      <td>Agrifi</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>160110023</td>\n",
       "      <td>Metallurgical Engineering and Materials Science</td>\n",
       "      <td>Dual Degree (B.Tech. + M.Tech.)</td>\n",
       "      <td>BeeHyv</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>160110071</td>\n",
       "      <td>Electrical Engineering</td>\n",
       "      <td>Dual Degree (B.Tech. + M.Tech.)</td>\n",
       "      <td>Boston Consulting Group</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>16D170019</td>\n",
       "      <td>Energy Science and Engineering, Energy Science...</td>\n",
       "      <td>Dual Degree (B.Tech. + M.Tech.)</td>\n",
       "      <td>Procter and Gamble</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Roll                                         Department  \\\n",
       "14  16D070026                             Electrical Engineering   \n",
       "36  16D070027                             Electrical Engineering   \n",
       "38  160110023    Metallurgical Engineering and Materials Science   \n",
       "55  160110071                             Electrical Engineering   \n",
       "56  16D170019  Energy Science and Engineering, Energy Science...   \n",
       "\n",
       "                            Program                                  Company  \\\n",
       "14  Dual Degree (B.Tech. + M.Tech.)  Ignitarium Technology Solutions Pvt Ltd   \n",
       "36  Dual Degree (B.Tech. + M.Tech.)                                   Agrifi   \n",
       "38  Dual Degree (B.Tech. + M.Tech.)                                   BeeHyv   \n",
       "55  Dual Degree (B.Tech. + M.Tech.)                  Boston Consulting Group   \n",
       "56  Dual Degree (B.Tech. + M.Tech.)                       Procter and Gamble   \n",
       "\n",
       "    Day  Slot No.  \n",
       "14   10         1  \n",
       "36   10         1  \n",
       "38   10         1  \n",
       "55    1         1  \n",
       "56    1         1  "
      ]
     },
     "metadata": {},
     "execution_count": 20
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "source": [
    "df.info()"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1139 entries, 0 to 1138\n",
      "Data columns (total 6 columns):\n",
      " #   Column      Non-Null Count  Dtype \n",
      "---  ------      --------------  ----- \n",
      " 0   Roll        1139 non-null   object\n",
      " 1   Department  1139 non-null   object\n",
      " 2   Program     1139 non-null   object\n",
      " 3   Company     1139 non-null   object\n",
      " 4   Day         1139 non-null   int64 \n",
      " 5   Slot No.    1139 non-null   int64 \n",
      "dtypes: int64(2), object(4)\n",
      "memory usage: 53.5+ KB\n"
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "---\r\n",
    "# Data Analysis\r\n",
    "--- "
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "1. Drawing insights from the data.\r\n",
    "2. Storing insights in form of DataFrames."
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Slot wise analysis:-"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "source": [
    "slot_data = df.groupby(\"Slot No.\",as_index=0)[\"Roll\"].count()\r\n",
    "slot_data = slot_data.reset_index(drop = 1)\r\n",
    "slot_data.rename({\"Roll\":\"No. of Students\"},axis=1,inplace=True)\r\n",
    "slot_data\r\n",
    "# EXPORT AS SHEET"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "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>Slot No.</th>\n",
       "      <th>No. of Students</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>609</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>216</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>314</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Slot No.  No. of Students\n",
       "0         1              609\n",
       "1         2              216\n",
       "2         3              314"
      ]
     },
     "metadata": {},
     "execution_count": 22
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Department and Program wise analysis:-"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "source": [
    "Department_Stats_Breakdown = df.groupby([\"Department\",\"Program\"], as_index = False)[\"Roll\"].count().sort_values([\"Roll\",\"Program\",\"Department\"], ascending = [False,True,True])\r\n",
    "Department_Stats_Breakdown.reset_index(drop = 1, inplace = True)\r\n",
    "Department_Stats_Breakdown.head(15)"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "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>Department</th>\n",
       "      <th>Program</th>\n",
       "      <th>Roll</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Computer Science &amp; Engineering</td>\n",
       "      <td>B.Tech.</td>\n",
       "      <td>108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Electrical Engineering</td>\n",
       "      <td>M.Tech.</td>\n",
       "      <td>101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Computer Science &amp; Engineering</td>\n",
       "      <td>M.Tech.</td>\n",
       "      <td>88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Mechanical Engineering</td>\n",
       "      <td>B.Tech.</td>\n",
       "      <td>78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Chemical Engineering</td>\n",
       "      <td>B.Tech.</td>\n",
       "      <td>73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Electrical Engineering</td>\n",
       "      <td>B.Tech.</td>\n",
       "      <td>57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Civil Engineering</td>\n",
       "      <td>B.Tech.</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Electrical Engineering</td>\n",
       "      <td>Dual Degree (B.Tech. + M.Tech.)</td>\n",
       "      <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Industrial Design Centre</td>\n",
       "      <td>M.Des.</td>\n",
       "      <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Metallurgical Engineering and Materials Science</td>\n",
       "      <td>B.Tech.</td>\n",
       "      <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Civil Engineering</td>\n",
       "      <td>M.Tech.</td>\n",
       "      <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Mechanical Engineering</td>\n",
       "      <td>M.Tech.</td>\n",
       "      <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Aerospace Engineering</td>\n",
       "      <td>B.Tech.</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Metallurgical Engineering and Materials Science</td>\n",
       "      <td>M.Tech.</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Mechanical Engineering</td>\n",
       "      <td>Dual Degree (B.Tech. + M.Tech.)</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                         Department  \\\n",
       "0                    Computer Science & Engineering   \n",
       "1                            Electrical Engineering   \n",
       "2                    Computer Science & Engineering   \n",
       "3                            Mechanical Engineering   \n",
       "4                              Chemical Engineering   \n",
       "5                            Electrical Engineering   \n",
       "6                                 Civil Engineering   \n",
       "7                            Electrical Engineering   \n",
       "8                          Industrial Design Centre   \n",
       "9   Metallurgical Engineering and Materials Science   \n",
       "10                                Civil Engineering   \n",
       "11                           Mechanical Engineering   \n",
       "12                            Aerospace Engineering   \n",
       "13  Metallurgical Engineering and Materials Science   \n",
       "14                           Mechanical Engineering   \n",
       "\n",
       "                            Program  Roll  \n",
       "0                           B.Tech.   108  \n",
       "1                           M.Tech.   101  \n",
       "2                           M.Tech.    88  \n",
       "3                           B.Tech.    78  \n",
       "4                           B.Tech.    73  \n",
       "5                           B.Tech.    57  \n",
       "6                           B.Tech.    56  \n",
       "7   Dual Degree (B.Tech. + M.Tech.)    48  \n",
       "8                            M.Des.    48  \n",
       "9                           B.Tech.    47  \n",
       "10                          M.Tech.    36  \n",
       "11                          M.Tech.    36  \n",
       "12                          B.Tech.    28  \n",
       "13                          M.Tech.    24  \n",
       "14  Dual Degree (B.Tech. + M.Tech.)    23  "
      ]
     },
     "metadata": {},
     "execution_count": 23
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Program wise analysis:-\n",
    "### 1. Number count.\n",
    "### 2. Percentage count."
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "source": [
    "Program_Stats = df.groupby(\"Program\",as_index = 0)[\"Roll\"].count().sort_values(\"Roll\",ascending = False)\r\n",
    "Program_Stats.reset_index(drop = True, inplace= True)\r\n",
    "Program_Stats.rename({\"Roll\":\"No. of Students\"},axis=1,inplace=True)\r\n",
    "Program_Stats\r\n",
    "# EXPORT AS SHEET"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "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>Program</th>\n",
       "      <th>No. of Students</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>B.Tech.</td>\n",
       "      <td>466</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>M.Tech.</td>\n",
       "      <td>398</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Dual Degree (B.Tech. + M.Tech.)</td>\n",
       "      <td>132</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>M.Des.</td>\n",
       "      <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>M.Sc.</td>\n",
       "      <td>37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>B.S.</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>B.Des.</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Ph.D.</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Dual Degree (B.Des. + M. Des.)</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Dual Degree (M.Tech. + Ph.D.)</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>M.P.P</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>M.Phil.</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>M.S. by research (Exit Degree)</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            Program  No. of Students\n",
       "0                           B.Tech.              466\n",
       "1                           M.Tech.              398\n",
       "2   Dual Degree (B.Tech. + M.Tech.)              132\n",
       "3                            M.Des.               48\n",
       "4                             M.Sc.               37\n",
       "5                              B.S.               20\n",
       "6                            B.Des.               12\n",
       "7                             Ph.D.               10\n",
       "8    Dual Degree (B.Des. + M. Des.)                9\n",
       "9     Dual Degree (M.Tech. + Ph.D.)                4\n",
       "10                            M.P.P                1\n",
       "11                          M.Phil.                1\n",
       "12   M.S. by research (Exit Degree)                1"
      ]
     },
     "metadata": {},
     "execution_count": 24
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "source": [
    "Program_Stats_Percentage = pd.DataFrame(round(df.value_counts(\"Program\", normalize = True)*100,2))"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "source": [
    "Program_Stats_Percentage.reset_index(inplace = True)\r\n",
    "Program_Stats_Percentage.rename({0:\"% of Students\"},axis=1,inplace=True)\r\n",
    "Program_Stats_Percentage\r\n",
    "# EXPORT AS SHEET"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "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>Program</th>\n",
       "      <th>% of Students</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>B.Tech.</td>\n",
       "      <td>40.91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>M.Tech.</td>\n",
       "      <td>34.94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Dual Degree (B.Tech. + M.Tech.)</td>\n",
       "      <td>11.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>M.Des.</td>\n",
       "      <td>4.21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>M.Sc.</td>\n",
       "      <td>3.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>B.S.</td>\n",
       "      <td>1.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>B.Des.</td>\n",
       "      <td>1.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Ph.D.</td>\n",
       "      <td>0.88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Dual Degree (B.Des. + M. Des.)</td>\n",
       "      <td>0.79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Dual Degree (M.Tech. + Ph.D.)</td>\n",
       "      <td>0.35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>M.P.P</td>\n",
       "      <td>0.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>M.Phil.</td>\n",
       "      <td>0.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>M.S. by research (Exit Degree)</td>\n",
       "      <td>0.09</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            Program  % of Students\n",
       "0                           B.Tech.          40.91\n",
       "1                           M.Tech.          34.94\n",
       "2   Dual Degree (B.Tech. + M.Tech.)          11.59\n",
       "3                            M.Des.           4.21\n",
       "4                             M.Sc.           3.25\n",
       "5                              B.S.           1.76\n",
       "6                            B.Des.           1.05\n",
       "7                             Ph.D.           0.88\n",
       "8    Dual Degree (B.Des. + M. Des.)           0.79\n",
       "9     Dual Degree (M.Tech. + Ph.D.)           0.35\n",
       "10                            M.P.P           0.09\n",
       "11                          M.Phil.           0.09\n",
       "12   M.S. by research (Exit Degree)           0.09"
      ]
     },
     "metadata": {},
     "execution_count": 26
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Company, Day and Slot wise mixed analysis:-"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "source": [
    "company_stats = df.groupby([\"Company\",\"Day\",\"Slot No.\"],as_index=False)[\"Roll\"].count().sort_values([\"Roll\",\"Company\"], ascending = [0,1])\r\n",
    "company_stats.reset_index(drop = True, inplace=True)\r\n",
    "company_stats.shape"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "(285, 4)"
      ]
     },
     "metadata": {},
     "execution_count": 27
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "source": [
    "company_stats[company_stats[\"Company\"].str.contains(\"Hp\",flags=re.I, regex=True)]"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Company</th>\n",
       "      <th>Day</th>\n",
       "      <th>Slot No.</th>\n",
       "      <th>Roll</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [Company, Day, Slot No., Roll]\n",
       "Index: []"
      ]
     },
     "metadata": {},
     "execution_count": 28
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "source": [
    "company_stats.rename({\"Roll\":\"No. of Students\"},axis=1,inplace=True)\r\n",
    "company_stats.head(10)\r\n",
    "# EXPORT AS SHEET"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Company</th>\n",
       "      <th>Day</th>\n",
       "      <th>Slot No.</th>\n",
       "      <th>No. of Students</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Placement Office</td>\n",
       "      <td>16</td>\n",
       "      <td>3</td>\n",
       "      <td>147</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Oracle</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>TATA Projects</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Texas Instruments</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Axis Bank</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Google India Pvt. Ltd.</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>ICICI Lombard GIC Ltd.</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Micron Technologies Operation India LLP</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Bridgei2i Analytics Solutions</td>\n",
       "      <td>16</td>\n",
       "      <td>3</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>ICICI Bank</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   Company  Day  Slot No.  No. of Students\n",
       "0                         Placement Office   16         3              147\n",
       "1                                   Oracle    2         1               23\n",
       "2                            TATA Projects    3         1               16\n",
       "3                        Texas Instruments    1         1               15\n",
       "4                                Axis Bank    3         1               13\n",
       "5                   Google India Pvt. Ltd.    1         1               13\n",
       "6                   ICICI Lombard GIC Ltd.    5         1               12\n",
       "7  Micron Technologies Operation India LLP    3         1               12\n",
       "8            Bridgei2i Analytics Solutions   16         3               11\n",
       "9                               ICICI Bank    3         1               11"
      ]
     },
     "metadata": {},
     "execution_count": 29
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "source": [
    "company_stats.info()"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 285 entries, 0 to 284\n",
      "Data columns (total 4 columns):\n",
      " #   Column           Non-Null Count  Dtype \n",
      "---  ------           --------------  ----- \n",
      " 0   Company          285 non-null    object\n",
      " 1   Day              285 non-null    int64 \n",
      " 2   Slot No.         285 non-null    int64 \n",
      " 3   No. of Students  285 non-null    int64 \n",
      "dtypes: int64(3), object(1)\n",
      "memory usage: 9.0+ KB\n"
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "source": [
    "company_stats[\"Company\"].nunique()"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "258"
      ]
     },
     "metadata": {},
     "execution_count": 31
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "source": [
    "company_stats[\"Company\"].count()"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "285"
      ]
     },
     "metadata": {},
     "execution_count": 32
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Finding all the companies who recruited more than once:-"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "source": [
    "repeated_companies = company_stats[company_stats.duplicated(subset=\"Company\", keep=False)]\r\n",
    "repeated_companies = repeated_companies.sort_values(\"Company\")\r\n",
    "repeated_companies.reset_index(drop=True,inplace=True)\r\n",
    "repeated_companies.head()\r\n",
    "# EXPORT AS SHEET"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Company</th>\n",
       "      <th>Day</th>\n",
       "      <th>Slot No.</th>\n",
       "      <th>No. of Students</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ANZ Bank</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ANZ Bank</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ANZ Bank</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Applied Materials</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Applied Materials</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             Company  Day  Slot No.  No. of Students\n",
       "0           ANZ Bank    3         1                4\n",
       "1           ANZ Bank    1         1                2\n",
       "2           ANZ Bank    3         2                5\n",
       "3  Applied Materials   10         1                2\n",
       "4  Applied Materials    3         1                2"
      ]
     },
     "metadata": {},
     "execution_count": 33
    }
   ],
   "metadata": {
    "scrolled": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "source": [
    "repeated_companies_total = repeated_companies.groupby([\"Company\"],as_index=False)[\"No. of Students\"].sum()\r\n",
    "repeated_companies_total = repeated_companies_total.sort_values([\"No. of Students\",\"Company\"],ascending=[0,1])\r\n",
    "repeated_companies_total.reset_index(drop=True,inplace=True)\r\n",
    "repeated_companies_total\r\n",
    "# EXPORT AS SHEET"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Company</th>\n",
       "      <th>No. of Students</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Tata Consultancy Services</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Axis Bank</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Intel</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Microsoft India Pvt. Ltd.</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ANZ Bank</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Qualcomm</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Enphase Energy</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>HCL Technologies Ltd.</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>IDFC First Bank</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Jaguar Land Rover India Limited</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Bajaj Auto Ltd</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>LnT Infotech</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Samsung Research Institute, Bangalore</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>pharmaACE- Analytics Centre of Excellence</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Flipkart</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Infosys</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Navi Technologies Pvt Ltd</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Applied Materials</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>HiLabs Inc.</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>KLA Tencor</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Procter and Gamble</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Sprinklr</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Quantsapp Private limited</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      Company  No. of Students\n",
       "0                   Tata Consultancy Services               26\n",
       "1                                   Axis Bank               20\n",
       "2                                       Intel               12\n",
       "3                   Microsoft India Pvt. Ltd.               12\n",
       "4                                    ANZ Bank               11\n",
       "5                                    Qualcomm               11\n",
       "6                              Enphase Energy               10\n",
       "7                       HCL Technologies Ltd.               10\n",
       "8                             IDFC First Bank               10\n",
       "9             Jaguar Land Rover India Limited               10\n",
       "10                             Bajaj Auto Ltd                8\n",
       "11                               LnT Infotech                8\n",
       "12      Samsung Research Institute, Bangalore                8\n",
       "13  pharmaACE- Analytics Centre of Excellence                8\n",
       "14                                   Flipkart                6\n",
       "15                                    Infosys                5\n",
       "16                  Navi Technologies Pvt Ltd                5\n",
       "17                          Applied Materials                4\n",
       "18                                HiLabs Inc.                4\n",
       "19                                 KLA Tencor                4\n",
       "20                         Procter and Gamble                4\n",
       "21                                   Sprinklr                4\n",
       "22                  Quantsapp Private limited                3"
      ]
     },
     "metadata": {},
     "execution_count": 34
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Top recruiter Companies for the entire season:-"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "source": [
    "top_companies = company_stats.groupby(\"Company\",as_index=False)[\"No. of Students\"].sum()\r\n",
    "top_companies = top_companies.sort_values([\"No. of Students\",\"Company\"],ascending=[0,1])\r\n",
    "top_companies.reset_index(drop=True,inplace=True)\r\n",
    "top_companies.head(20)\r\n",
    "# EXPORT AS SHEET"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Company</th>\n",
       "      <th>No. of Students</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Placement Office</td>\n",
       "      <td>147</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Tata Consultancy Services</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Oracle</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Axis Bank</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>TATA Projects</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Texas Instruments</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Google India Pvt. Ltd.</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>ICICI Lombard GIC Ltd.</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Intel</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Micron Technologies Operation India LLP</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Microsoft India Pvt. Ltd.</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>ANZ Bank</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Bridgei2i Analytics Solutions</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>ICICI Bank</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Qualcomm</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Accenture</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Enphase Energy</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>ExxonMobil Lubricants Private Limited</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>HCL Technologies Ltd.</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>IDFC First Bank</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                    Company  No. of Students\n",
       "0                          Placement Office              147\n",
       "1                 Tata Consultancy Services               26\n",
       "2                                    Oracle               23\n",
       "3                                 Axis Bank               20\n",
       "4                             TATA Projects               16\n",
       "5                         Texas Instruments               15\n",
       "6                    Google India Pvt. Ltd.               13\n",
       "7                    ICICI Lombard GIC Ltd.               12\n",
       "8                                     Intel               12\n",
       "9   Micron Technologies Operation India LLP               12\n",
       "10                Microsoft India Pvt. Ltd.               12\n",
       "11                                 ANZ Bank               11\n",
       "12            Bridgei2i Analytics Solutions               11\n",
       "13                               ICICI Bank               11\n",
       "14                                 Qualcomm               11\n",
       "15                                Accenture               10\n",
       "16                           Enphase Energy               10\n",
       "17    ExxonMobil Lubricants Private Limited               10\n",
       "18                    HCL Technologies Ltd.               10\n",
       "19                          IDFC First Bank               10"
      ]
     },
     "metadata": {},
     "execution_count": 35
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Top branches:-"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "source": [
    "def dept_splitter(x):\r\n",
    "    splitted = x.split(\", \")\r\n",
    "    if len(splitted)==1:\r\n",
    "        return x\r\n",
    "    elif splitted[0]==splitted[1]:\r\n",
    "        return splitted[0]\r\n",
    "    else:\r\n",
    "        return x"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "source": [
    "top_branches = Department_Stats_Breakdown.copy()\r\n",
    "top_branches[\"Department\"] = top_branches[\"Department\"].apply(dept_splitter)\r\n",
    "top_branches = top_branches.rename({\"Roll\":\"No. of Students\"},axis=1)\r\n",
    "top_branches.head(20)\r\n",
    "# EXPORT AS SHEET"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "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>Department</th>\n",
       "      <th>Program</th>\n",
       "      <th>No. of Students</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Computer Science &amp; Engineering</td>\n",
       "      <td>B.Tech.</td>\n",
       "      <td>108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Electrical Engineering</td>\n",
       "      <td>M.Tech.</td>\n",
       "      <td>101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Computer Science &amp; Engineering</td>\n",
       "      <td>M.Tech.</td>\n",
       "      <td>88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Mechanical Engineering</td>\n",
       "      <td>B.Tech.</td>\n",
       "      <td>78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Chemical Engineering</td>\n",
       "      <td>B.Tech.</td>\n",
       "      <td>73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Electrical Engineering</td>\n",
       "      <td>B.Tech.</td>\n",
       "      <td>57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Civil Engineering</td>\n",
       "      <td>B.Tech.</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Electrical Engineering</td>\n",
       "      <td>Dual Degree (B.Tech. + M.Tech.)</td>\n",
       "      <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Industrial Design Centre</td>\n",
       "      <td>M.Des.</td>\n",
       "      <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Metallurgical Engineering and Materials Science</td>\n",
       "      <td>B.Tech.</td>\n",
       "      <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Civil Engineering</td>\n",
       "      <td>M.Tech.</td>\n",
       "      <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Mechanical Engineering</td>\n",
       "      <td>M.Tech.</td>\n",
       "      <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Aerospace Engineering</td>\n",
       "      <td>B.Tech.</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Metallurgical Engineering and Materials Science</td>\n",
       "      <td>M.Tech.</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Mechanical Engineering</td>\n",
       "      <td>Dual Degree (B.Tech. + M.Tech.)</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Applied Statistics and Informatics</td>\n",
       "      <td>M.Sc.</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Geoinformatics and Natural Resources Engineering</td>\n",
       "      <td>M.Tech.</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Aerospace Engineering</td>\n",
       "      <td>M.Tech.</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Engineering Physics</td>\n",
       "      <td>B.Tech.</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Energy Science and Engineering</td>\n",
       "      <td>Dual Degree (B.Tech. + M.Tech.)</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                          Department  \\\n",
       "0                     Computer Science & Engineering   \n",
       "1                             Electrical Engineering   \n",
       "2                     Computer Science & Engineering   \n",
       "3                             Mechanical Engineering   \n",
       "4                               Chemical Engineering   \n",
       "5                             Electrical Engineering   \n",
       "6                                  Civil Engineering   \n",
       "7                             Electrical Engineering   \n",
       "8                           Industrial Design Centre   \n",
       "9    Metallurgical Engineering and Materials Science   \n",
       "10                                 Civil Engineering   \n",
       "11                            Mechanical Engineering   \n",
       "12                             Aerospace Engineering   \n",
       "13   Metallurgical Engineering and Materials Science   \n",
       "14                            Mechanical Engineering   \n",
       "15                Applied Statistics and Informatics   \n",
       "16  Geoinformatics and Natural Resources Engineering   \n",
       "17                             Aerospace Engineering   \n",
       "18                               Engineering Physics   \n",
       "19                    Energy Science and Engineering   \n",
       "\n",
       "                            Program  No. of Students  \n",
       "0                           B.Tech.              108  \n",
       "1                           M.Tech.              101  \n",
       "2                           M.Tech.               88  \n",
       "3                           B.Tech.               78  \n",
       "4                           B.Tech.               73  \n",
       "5                           B.Tech.               57  \n",
       "6                           B.Tech.               56  \n",
       "7   Dual Degree (B.Tech. + M.Tech.)               48  \n",
       "8                            M.Des.               48  \n",
       "9                           B.Tech.               47  \n",
       "10                          M.Tech.               36  \n",
       "11                          M.Tech.               36  \n",
       "12                          B.Tech.               28  \n",
       "13                          M.Tech.               24  \n",
       "14  Dual Degree (B.Tech. + M.Tech.)               23  \n",
       "15                            M.Sc.               23  \n",
       "16                          M.Tech.               23  \n",
       "17                          M.Tech.               20  \n",
       "18                          B.Tech.               19  \n",
       "19  Dual Degree (B.Tech. + M.Tech.)               16  "
      ]
     },
     "metadata": {},
     "execution_count": 37
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Irrespective of Programs:-"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "source": [
    "top_branches_all = top_branches.groupby(\"Department\",as_index=False)[\"No. of Students\"].sum()\r\n",
    "top_branches_all = top_branches_all.sort_values([\"No. of Students\",\"Department\"],ascending=[0,1])\r\n",
    "top_branches_all.reset_index(drop = True, inplace = True)\r\n",
    "top_branches_all.head(10)\r\n",
    "# EXPORT AS SHEET"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "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>Department</th>\n",
       "      <th>No. of Students</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Electrical Engineering</td>\n",
       "      <td>219</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Computer Science &amp; Engineering</td>\n",
       "      <td>198</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mechanical Engineering</td>\n",
       "      <td>144</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Civil Engineering</td>\n",
       "      <td>97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Chemical Engineering</td>\n",
       "      <td>89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Metallurgical Engineering and Materials Science</td>\n",
       "      <td>86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Industrial Design Centre</td>\n",
       "      <td>69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Aerospace Engineering</td>\n",
       "      <td>54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Energy Science and Engineering</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Engineering Physics</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        Department  No. of Students\n",
       "0                           Electrical Engineering              219\n",
       "1                   Computer Science & Engineering              198\n",
       "2                           Mechanical Engineering              144\n",
       "3                                Civil Engineering               97\n",
       "4                             Chemical Engineering               89\n",
       "5  Metallurgical Engineering and Materials Science               86\n",
       "6                         Industrial Design Centre               69\n",
       "7                            Aerospace Engineering               54\n",
       "8                   Energy Science and Engineering               30\n",
       "9                              Engineering Physics               24"
      ]
     },
     "metadata": {},
     "execution_count": 38
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Program wise:-"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "source": [
    "top_branches_BTech = top_branches.loc[top_branches.Program == \"B.Tech.\",[\"Department\",\"No. of Students\"]]\r\n",
    "top_branches_BTech.reset_index(drop = True, inplace=True)\r\n",
    "top_branches_BTech\r\n",
    "# EXPORT AS SHEET"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "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>Department</th>\n",
       "      <th>No. of Students</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Computer Science &amp; Engineering</td>\n",
       "      <td>108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Mechanical Engineering</td>\n",
       "      <td>78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Chemical Engineering</td>\n",
       "      <td>73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Electrical Engineering</td>\n",
       "      <td>57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Civil Engineering</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Metallurgical Engineering and Materials Science</td>\n",
       "      <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Aerospace Engineering</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Engineering Physics</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        Department  No. of Students\n",
       "0                   Computer Science & Engineering              108\n",
       "1                           Mechanical Engineering               78\n",
       "2                             Chemical Engineering               73\n",
       "3                           Electrical Engineering               57\n",
       "4                                Civil Engineering               56\n",
       "5  Metallurgical Engineering and Materials Science               47\n",
       "6                            Aerospace Engineering               28\n",
       "7                              Engineering Physics               19"
      ]
     },
     "metadata": {},
     "execution_count": 39
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "source": [
    "top_branches_MTech = top_branches.loc[top_branches.Program == \"M.Tech.\",[\"Department\",\"No. of Students\"]]\r\n",
    "top_branches_MTech.reset_index(drop = True, inplace=True)\r\n",
    "top_branches_MTech\r\n",
    "# EXPORT AS SHEET"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "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>Department</th>\n",
       "      <th>No. of Students</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Electrical Engineering</td>\n",
       "      <td>101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Computer Science &amp; Engineering</td>\n",
       "      <td>88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Civil Engineering</td>\n",
       "      <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Mechanical Engineering</td>\n",
       "      <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Metallurgical Engineering and Materials Science</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Geoinformatics and Natural Resources Engineering</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Aerospace Engineering</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Chemical Engineering</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Industrial Engineering &amp; Operations Research</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Energy Science and Engineering</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Technology and Development</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Environmental Science &amp; Engineering</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Earth Sciences</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Systems &amp; Control Engineering</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Biosciences and Bioengineering</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                          Department  No. of Students\n",
       "0                             Electrical Engineering              101\n",
       "1                     Computer Science & Engineering               88\n",
       "2                                  Civil Engineering               36\n",
       "3                             Mechanical Engineering               36\n",
       "4    Metallurgical Engineering and Materials Science               24\n",
       "5   Geoinformatics and Natural Resources Engineering               23\n",
       "6                              Aerospace Engineering               20\n",
       "7                               Chemical Engineering               15\n",
       "8       Industrial Engineering & Operations Research               15\n",
       "9                     Energy Science and Engineering               14\n",
       "10                        Technology and Development                8\n",
       "11               Environmental Science & Engineering                6\n",
       "12                                    Earth Sciences                5\n",
       "13                     Systems & Control Engineering                5\n",
       "14                    Biosciences and Bioengineering                2"
      ]
     },
     "metadata": {},
     "execution_count": 40
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "source": [
    "top_branches_other = top_branches.loc[~top_branches.Program.isin([\"M.Tech.\",\"B.Tech.\"])]\r\n",
    "top_branches_other.reset_index(drop=True,inplace=True)\r\n",
    "top_branches_other.head()\r\n",
    "# EXPORT AS SHEET"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "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>Department</th>\n",
       "      <th>Program</th>\n",
       "      <th>No. of Students</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Electrical Engineering</td>\n",
       "      <td>Dual Degree (B.Tech. + M.Tech.)</td>\n",
       "      <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Industrial Design Centre</td>\n",
       "      <td>M.Des.</td>\n",
       "      <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mechanical Engineering</td>\n",
       "      <td>Dual Degree (B.Tech. + M.Tech.)</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Applied Statistics and Informatics</td>\n",
       "      <td>M.Sc.</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Energy Science and Engineering</td>\n",
       "      <td>Dual Degree (B.Tech. + M.Tech.)</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           Department                          Program  \\\n",
       "0              Electrical Engineering  Dual Degree (B.Tech. + M.Tech.)   \n",
       "1            Industrial Design Centre                           M.Des.   \n",
       "2              Mechanical Engineering  Dual Degree (B.Tech. + M.Tech.)   \n",
       "3  Applied Statistics and Informatics                            M.Sc.   \n",
       "4      Energy Science and Engineering  Dual Degree (B.Tech. + M.Tech.)   \n",
       "\n",
       "   No. of Students  \n",
       "0               48  \n",
       "1               48  \n",
       "2               23  \n",
       "3               23  \n",
       "4               16  "
      ]
     },
     "metadata": {},
     "execution_count": 41
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "--- \r\n",
    "# Exporting DataFrames into multiple Excel sheets of a single file:-\r\n",
    "---"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "source": [
    "# %%script echo \"Skipping execution of this cell.\"\r\n",
    "# Please uncomment above line ( only remove '# ' ) to stop executing below codes and creating the excel file again and again each time you run all cells. \r\n",
    "\r\n",
    "with pd.ExcelWriter(\"Allocation_Analysis.xlsx\") as w:\r\n",
    "    df_BTech.to_excel(w,sheet_name=\"B.Tech Details\",index=False,freeze_panes=(1,0))\r\n",
    "    df_MTech.to_excel(w,sheet_name=\"M.Tech Details\",index=False,freeze_panes=(1,0))\r\n",
    "    df_Dual.to_excel(w,sheet_name=\"Dual Degree Details\",index=False,freeze_panes=(1,0))\r\n",
    "    slot_data.to_excel(w,sheet_name=\"Slot Analysis\",index=False,freeze_panes=(1,0))\r\n",
    "    Program_Stats.to_excel(w,sheet_name=\"Program_Stats\",index=False,freeze_panes=(1,0))\r\n",
    "    Program_Stats_Percentage.to_excel(w,sheet_name=\"Program_Stats_Percentage\",index=False,freeze_panes=(1,0))\r\n",
    "    company_stats.to_excel(w,sheet_name=\"Company_Stats\",index=False,freeze_panes=(1,0))\r\n",
    "    repeated_companies.to_excel(w,sheet_name=\"Repeating Companies\",index=False,freeze_panes=(1,0))\r\n",
    "    repeated_companies_total.to_excel(w,sheet_name=\"Best repeating Companies\",index=False,freeze_panes=(1,0))\r\n",
    "    top_companies.to_excel(w,sheet_name=\"Top Recruiters\",index=False,freeze_panes=(1,0))\r\n",
    "    top_branches.to_excel(w,sheet_name=\"Best Program & Department\",index=False,freeze_panes=(1,0))\r\n",
    "    top_branches_all.to_excel(w,sheet_name=\"Overall Best Department\",index=False,freeze_panes=(1,0))\r\n",
    "    top_branches_BTech.to_excel(w,sheet_name=\"Best B.Tech Department\",index=False,freeze_panes=(1,0))\r\n",
    "    top_branches_MTech.to_excel(w,sheet_name=\"Best M.Tech Department\",index=False,freeze_panes=(1,0))\r\n",
    "    top_branches_other.to_excel(w,sheet_name=\"Best_Dept_other_Programs\",index=False,freeze_panes=(1,0))"
   ],
   "outputs": [],
   "metadata": {}
  }
 ],
 "metadata": {
  "kernelspec": {
   "name": "python3",
   "display_name": "Python 3.9.2 64-bit"
  },
  "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.2"
  },
  "interpreter": {
   "hash": "68e6f762b01821c724edd32c8c28c1d5e0a3b08ea85eec78b3942b03350556a9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}