Task 30-07-2022.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(r\"C:\\Users\\91840\\Downloads\\agentperformancereport.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Q1. Find out there avarage rating on weekly basis keep this in a mind that they take two days of leave"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "date = pd.to_datetime(df['Date'])\n",
    "df['converted_date']= date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['week']= df['converted_date'].dt.weekofyear"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Agent Name\n",
       "Abhishek          0.000000\n",
       "Aditya            0.000000\n",
       "Aditya Shinde    12.275000\n",
       "Aditya_iot       15.990909\n",
       "Amersh            0.000000\n",
       "                   ...    \n",
       "Uday Mishra       0.000000\n",
       "Vasanth P         0.000000\n",
       "Vivek             3.413636\n",
       "Wasim            16.363636\n",
       "Zeeshan          15.593182\n",
       "Name: Average Rating, Length: 70, dtype: float64"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby('Agent Name')['Average Rating'].sum()/4.4"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Q2.Total working days for each agents "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Agent Name\n",
       "Abhishek         30\n",
       "Aditya           30\n",
       "Aditya Shinde    30\n",
       "Aditya_iot       30\n",
       "Amersh           30\n",
       "                 ..\n",
       "Uday Mishra      30\n",
       "Vasanth P        30\n",
       "Vivek            30\n",
       "Wasim            30\n",
       "Zeeshan          30\n",
       "Name: converted_date, Length: 70, dtype: int64"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby('Agent Name')['converted_date'].count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Q3. Total query that you hvae taken "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Agent Name\n",
       "Abhishek           0\n",
       "Aditya             0\n",
       "Aditya Shinde    277\n",
       "Aditya_iot       231\n",
       "Amersh             0\n",
       "                ... \n",
       "Uday Mishra        0\n",
       "Vasanth P          0\n",
       "Vivek             44\n",
       "Wasim            433\n",
       "Zeeshan          542\n",
       "Name: Total Chats, Length: 70, dtype: int64"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby('Agent Name')['Total Chats'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "14720"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[\"Total Chats\"].sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Q4. total Feedback that you have received "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Agent Name\n",
       "Abhishek           0\n",
       "Aditya             0\n",
       "Aditya Shinde    153\n",
       "Aditya_iot       131\n",
       "Amersh             0\n",
       "                ... \n",
       "Uday Mishra        0\n",
       "Vasanth P          0\n",
       "Vivek             20\n",
       "Wasim            284\n",
       "Zeeshan          335\n",
       "Name: Total Feedback, Length: 70, dtype: int64"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby('Agent Name')['Total Feedback'].sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Q5. a agent name who have average rating between 3.5 to 4 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Agent Name\n",
       "Abhishek         0.000000\n",
       "Aditya           0.000000\n",
       "Aditya Shinde    2.455000\n",
       "Aditya_iot       3.198182\n",
       "Amersh           0.000000\n",
       "                   ...   \n",
       "Uday Mishra      0.000000\n",
       "Vasanth P        0.000000\n",
       "Vivek            0.682727\n",
       "Wasim            3.272727\n",
       "Zeeshan          3.118636\n",
       "Name: Average Rating, Length: 70, dtype: float64"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby('Agent Name')['Average Rating'].sum()/22"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Q6 Agent name who have rating lesss then 3.5 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1            Nandani Gupta\n",
       "19        Hitesh Choudhary\n",
       "20              Sanjeevan \n",
       "21                Anirudh \n",
       "22        Shiva Srivastava\n",
       "               ...        \n",
       "2155     Sowmiya Sivakumar\n",
       "2156               Nitin M\n",
       "2157                Vivek \n",
       "2158         Ayushi Mishra\n",
       "2159    Chaitra K Hiremath\n",
       "Name: Agent Name, Length: 1474, dtype: object"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[(df['Average Rating']<3.5)]['Agent Name']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Q7 agent name who have rating more then 4.5 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2               Ameya Jain\n",
       "3            Mahesh Sarade\n",
       "5                  Mukesh \n",
       "6          Saikumarreddy N\n",
       "7            Sanjeev Kumar\n",
       "               ...        \n",
       "2100        Shubham Sharma\n",
       "2101         Jaydeep Dixit\n",
       "2102    Shivananda Sonwane\n",
       "2103        Khushboo Priya\n",
       "2104       Hrisikesh Neogi\n",
       "Name: Agent Name, Length: 307, dtype: object"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['Average Rating']>4.5]['Agent Name']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q8 how many feedaback agents have received more then 4.5 average"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "70"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "((df.groupby('Agent Name')['Total Feedback'].mean())>4.5).count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Q9 average weekly response time for each agent "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "SL No                               int64\n",
       "Date                               object\n",
       "Agent Name                         object\n",
       "Total Chats                         int64\n",
       "Average Response Time              object\n",
       "Average Resolution Time            object\n",
       "Average Rating                    float64\n",
       "Total Feedback                      int64\n",
       "converted_date             datetime64[ns]\n",
       "week                                int64\n",
       "Avg_Res_Time               datetime64[ns]\n",
       "Hrs                                 int64\n",
       "Min                                 int64\n",
       "Sec                                 int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Avg_Res_Time']=pd.to_datetime(df['Average Response Time'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Hrs'] = df['Avg_Res_Time'].dt.hour\n",
    "df['Min'] = df['Avg_Res_Time'].dt.hour\n",
    "df['Sec'] = df['Avg_Res_Time'].dt.hour"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Sec'] = df['Avg_Res_Time'].dt.hour"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Agent Name\n",
       "Abhishek         0\n",
       "Aditya           0\n",
       "Aditya Shinde    0\n",
       "Aditya_iot       0\n",
       "Amersh           0\n",
       "                ..\n",
       "Uday Mishra      0\n",
       "Vasanth P        0\n",
       "Vivek            0\n",
       "Wasim            0\n",
       "Zeeshan          0\n",
       "Length: 70, dtype: int64"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "((df.groupby('Agent Name')['Sec'].sum())+(df.groupby('Agent Name')['Min'].sum()*60)+(df.groupby('Agent Name')['Hrs'].sum()*3600))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Q11. list of all agents name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0             Prerna Singh\n",
       "1            Nandani Gupta\n",
       "2               Ameya Jain\n",
       "3            Mahesh Sarade\n",
       "4                   Swati \n",
       "               ...        \n",
       "2155     Sowmiya Sivakumar\n",
       "2156               Nitin M\n",
       "2157                Vivek \n",
       "2158         Ayushi Mishra\n",
       "2159    Chaitra K Hiremath\n",
       "Name: Agent Name, Length: 2160, dtype: object"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Agent Name']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Q12.  percentage of chat on which they have received a feedback "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Agent Name\n",
       "Abhishek               NaN\n",
       "Aditya                 NaN\n",
       "Aditya Shinde    55.234657\n",
       "Aditya_iot       56.709957\n",
       "Amersh                 NaN\n",
       "                   ...    \n",
       "Uday Mishra            NaN\n",
       "Vasanth P              NaN\n",
       "Vivek            45.454545\n",
       "Wasim            65.588915\n",
       "Zeeshan          61.808118\n",
       "Length: 70, dtype: float64"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "((df.groupby('Agent Name')['Total Feedback'].sum())/(df.groupby('Agent Name')['Total Chats'].sum()))*100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0       81.818182\n",
       "1       63.636364\n",
       "2       78.571429\n",
       "3       50.000000\n",
       "4       42.857143\n",
       "          ...    \n",
       "2155          NaN\n",
       "2156          NaN\n",
       "2157          NaN\n",
       "2158          NaN\n",
       "2159          NaN\n",
       "Length: 2160, dtype: float64"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(df['Total Feedback']/df['Total Chats'])*100"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Q13. Total contributation hour for each and every agents weekly basis "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = pd.read_csv(r\"C:\\Users\\91840\\Downloads\\Book1.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SL No</th>\n",
       "      <th>Agent</th>\n",
       "      <th>Date</th>\n",
       "      <th>Login Time</th>\n",
       "      <th>Logout Time</th>\n",
       "      <th>Duration</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Shivananda Sonwane</td>\n",
       "      <td>30-Jul-22</td>\n",
       "      <td>3:35:29 PM</td>\n",
       "      <td>5:39:39 PM</td>\n",
       "      <td>02:04:10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Khushboo Priya</td>\n",
       "      <td>30-Jul-22</td>\n",
       "      <td>3:06:59 PM</td>\n",
       "      <td>3:07:16 PM</td>\n",
       "      <td>00:00:17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Nandani Gupta</td>\n",
       "      <td>30-Jul-22</td>\n",
       "      <td>3:04:24 PM</td>\n",
       "      <td>5:31:07 PM</td>\n",
       "      <td>02:26:42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Hrisikesh Neogi</td>\n",
       "      <td>30-Jul-22</td>\n",
       "      <td>2:34:29 PM</td>\n",
       "      <td>3:19:35 PM</td>\n",
       "      <td>00:45:06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Mukesh</td>\n",
       "      <td>30-Jul-22</td>\n",
       "      <td>2:03:15 PM</td>\n",
       "      <td>3:11:52 PM</td>\n",
       "      <td>01:08:36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>995</th>\n",
       "      <td>996</td>\n",
       "      <td>Manjunatha A</td>\n",
       "      <td>20-Jul-22</td>\n",
       "      <td>9:03:51 AM</td>\n",
       "      <td>3:02:28 PM</td>\n",
       "      <td>05:58:37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>996</th>\n",
       "      <td>997</td>\n",
       "      <td>Bharath</td>\n",
       "      <td>20-Jul-22</td>\n",
       "      <td>9:00:49 AM</td>\n",
       "      <td>3:01:32 PM</td>\n",
       "      <td>06:00:43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>997</th>\n",
       "      <td>998</td>\n",
       "      <td>Khushboo Priya</td>\n",
       "      <td>20-Jul-22</td>\n",
       "      <td>8:59:20 AM</td>\n",
       "      <td>2:26:55 PM</td>\n",
       "      <td>05:27:34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>998</th>\n",
       "      <td>999</td>\n",
       "      <td>Nishtha Jain</td>\n",
       "      <td>20-Jul-22</td>\n",
       "      <td>8:43:55 AM</td>\n",
       "      <td>2:00:33 PM</td>\n",
       "      <td>05:16:38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>999</th>\n",
       "      <td>1000</td>\n",
       "      <td>Tarun</td>\n",
       "      <td>01-Jul-22</td>\n",
       "      <td>1:52:47 AM</td>\n",
       "      <td>12:01:07 PM</td>\n",
       "      <td>514:08:20</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1000 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     SL No               Agent       Date  Login Time  Logout Time   Duration\n",
       "0        1  Shivananda Sonwane  30-Jul-22  3:35:29 PM   5:39:39 PM   02:04:10\n",
       "1        2      Khushboo Priya  30-Jul-22  3:06:59 PM   3:07:16 PM   00:00:17\n",
       "2        3       Nandani Gupta  30-Jul-22  3:04:24 PM   5:31:07 PM   02:26:42\n",
       "3        4     Hrisikesh Neogi  30-Jul-22  2:34:29 PM   3:19:35 PM   00:45:06\n",
       "4        5              Mukesh  30-Jul-22  2:03:15 PM   3:11:52 PM   01:08:36\n",
       "..     ...                 ...        ...         ...          ...        ...\n",
       "995    996        Manjunatha A  20-Jul-22  9:03:51 AM   3:02:28 PM   05:58:37\n",
       "996    997             Bharath  20-Jul-22  9:00:49 AM   3:01:32 PM   06:00:43\n",
       "997    998      Khushboo Priya  20-Jul-22  8:59:20 AM   2:26:55 PM   05:27:34\n",
       "998    999        Nishtha Jain  20-Jul-22  8:43:55 AM   2:00:33 PM   05:16:38\n",
       "999   1000               Tarun  01-Jul-22  1:52:47 AM  12:01:07 PM  514:08:20\n",
       "\n",
       "[1000 rows x 6 columns]"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1['WH'] = (pd.to_datetime(df1.Date) + pd.to_timedelta(df1.Duration))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     2022-07-30 02:04:10\n",
       "1     2022-07-30 00:00:17\n",
       "2     2022-07-30 02:26:42\n",
       "3     2022-07-30 00:45:06\n",
       "4     2022-07-30 01:08:36\n",
       "              ...        \n",
       "995   2022-07-20 05:58:37\n",
       "996   2022-07-20 06:00:43\n",
       "997   2022-07-20 05:27:34\n",
       "998   2022-07-20 05:16:38\n",
       "999   2022-07-22 10:08:20\n",
       "Name: WH, Length: 1000, dtype: datetime64[ns]"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1['WH']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1['hrs'] = df1['WH'].dt.hour\n",
    "df1['Min'] = df1['WH'].dt.minute\n",
    "df1['Sec'] = df1['WH'].dt.second"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Agent\n",
       "Aditya Shinde              0.008207\n",
       "Aditya_iot                 3.575253\n",
       "Amersh                     0.696338\n",
       "Ameya Jain                 9.562626\n",
       "Ankitjha                   0.515215\n",
       "Anurag Tiwari              0.631566\n",
       "Aravind                    5.522538\n",
       "Ayushi Mishra              8.664015\n",
       "Bharath                   10.926515\n",
       "Boktiar Ahmed Bappy        9.151957\n",
       "Chaitra K Hiremath         7.801263\n",
       "Deepranjan Gupta          24.153472\n",
       "Dibyanshu                 11.953598\n",
       "Harikrishnan Shaji        12.207891\n",
       "Hrisikesh Neogi           13.083775\n",
       "Hyder Abbas                0.088194\n",
       "Ineuron Intelligence       0.329230\n",
       "Ishawant Kumar            11.767929\n",
       "Jawala Prakash            10.545960\n",
       "Jaydeep Dixit              8.145581\n",
       "Khushboo Priya             9.899684\n",
       "Madhulika G               10.468245\n",
       "Mahesh Sarade              9.754987\n",
       "Maitry                     7.032955\n",
       "Manjunatha A               9.380619\n",
       "Mithun S                  10.266730\n",
       "Mukesh                     2.023864\n",
       "Muskan Garg                3.939899\n",
       "Nandani Gupta              9.130051\n",
       "Nishtha Jain               9.966540\n",
       "Nitin M                    0.181566\n",
       "Prabir Kumar Satapathy     7.585732\n",
       "Prateek _iot               4.185922\n",
       "Prerna Singh              10.389899\n",
       "Rishav Dash                9.494444\n",
       "Saikumarreddy N            9.803977\n",
       "Sanjeev Kumar             10.156187\n",
       "Saurabh Shukla             3.787058\n",
       "Shiva Srivastava           3.407765\n",
       "Shivan K                   8.205051\n",
       "Shivananda Sonwane        11.201768\n",
       "Shubham Sharma            12.226894\n",
       "Sowmiya Sivakumar         10.171402\n",
       "Sudhanshu Kumar           10.507071\n",
       "Suraj S Bilgi              2.854356\n",
       "Swati                      5.682071\n",
       "Tarun                      2.304293\n",
       "Wasim                     10.949811\n",
       "Zeeshan                   11.156503\n",
       "dtype: float64"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "((df1.groupby('Agent')['Sec'].sum()/3600)+(df1.groupby('Agent')['Min'].sum()/60)+(df1.groupby('Agent')['hrs'].sum()))/4.4"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Q14 total percentage of active hour for a month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Agent\n",
       "Aditya Shinde              0.020518\n",
       "Aditya_iot                 8.938131\n",
       "Amersh                     1.740846\n",
       "Ameya Jain                23.906566\n",
       "Ankitjha                   1.288037\n",
       "Anurag Tiwari              1.578914\n",
       "Aravind                   13.806345\n",
       "Ayushi Mishra             21.660038\n",
       "Bharath                   27.316288\n",
       "Boktiar Ahmed Bappy       22.879893\n",
       "Chaitra K Hiremath        19.503157\n",
       "Deepranjan Gupta          60.383681\n",
       "Dibyanshu                 29.883996\n",
       "Harikrishnan Shaji        30.519729\n",
       "Hrisikesh Neogi           32.709438\n",
       "Hyder Abbas                0.220486\n",
       "Ineuron Intelligence       0.823074\n",
       "Ishawant Kumar            29.419823\n",
       "Jawala Prakash            26.364899\n",
       "Jaydeep Dixit             20.363952\n",
       "Khushboo Priya            24.749211\n",
       "Madhulika G               26.170612\n",
       "Mahesh Sarade             24.387468\n",
       "Maitry                    17.582386\n",
       "Manjunatha A              23.451547\n",
       "Mithun S                  25.666824\n",
       "Mukesh                     5.059659\n",
       "Muskan Garg                9.849747\n",
       "Nandani Gupta             22.825126\n",
       "Nishtha Jain              24.916351\n",
       "Nitin M                    0.453914\n",
       "Prabir Kumar Satapathy    18.964331\n",
       "Prateek _iot              10.464804\n",
       "Prerna Singh              25.974747\n",
       "Rishav Dash               23.736111\n",
       "Saikumarreddy N           24.509943\n",
       "Sanjeev Kumar             25.390467\n",
       "Saurabh Shukla             9.467645\n",
       "Shiva Srivastava           8.519413\n",
       "Shivan K                  20.512626\n",
       "Shivananda Sonwane        28.004419\n",
       "Shubham Sharma            30.567235\n",
       "Sowmiya Sivakumar         25.428504\n",
       "Sudhanshu Kumar           26.267677\n",
       "Suraj S Bilgi              7.135890\n",
       "Swati                     14.205177\n",
       "Tarun                      5.760732\n",
       "Wasim                     27.374527\n",
       "Zeeshan                   27.891256\n",
       "dtype: float64"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(((df1.groupby('Agent')['Sec'].sum()/3600)+(df1.groupby('Agent')['Min'].sum()/60)+(df1.groupby('Agent')['hrs'].sum()))/176)*100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}