Go Digital Technology Consulting assignment.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "f2e92161",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "801a1701",
   "metadata": {},
   "outputs": [],
   "source": [
    "employee = pd.read_csv(\"C:\\\\Users\\\\hp\\\\Desktop\\\\New folder (2)\\\\Data For Python Assessment\\\\employee_data.csv\")\n",
    "insurance = pd.read_csv(\"C:\\\\Users\\\\hp\\\\Desktop\\\\New folder (2)\\\\Data For Python Assessment\\\\insurance_data.csv\")\n",
    "vendor = pd.read_csv(\"C:\\\\Users\\\\hp\\\\Desktop\\\\New folder (2)\\\\Data For Python Assessment\\\\vendor_data.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "a008e1a7",
   "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>AGENT_ID</th>\n",
       "      <th>AGENT_NAME</th>\n",
       "      <th>DATE_OF_JOINING</th>\n",
       "      <th>ADDRESS_LINE1</th>\n",
       "      <th>ADDRESS_LINE2</th>\n",
       "      <th>CITY</th>\n",
       "      <th>STATE</th>\n",
       "      <th>POSTAL_CODE</th>\n",
       "      <th>EMP_ROUTING_NUMBER</th>\n",
       "      <th>EMP_ACCT_NUMBER</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AGENT00001</td>\n",
       "      <td>Ray Johns</td>\n",
       "      <td>1993-06-05</td>\n",
       "      <td>1402 Maggies Way</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Waterbury Center</td>\n",
       "      <td>VT</td>\n",
       "      <td>5677</td>\n",
       "      <td>34584958</td>\n",
       "      <td>HKUN51252328472585</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AGENT00002</td>\n",
       "      <td>Angelo Borjon</td>\n",
       "      <td>2005-12-27</td>\n",
       "      <td>414 Tanya Pass</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Panama City</td>\n",
       "      <td>FL</td>\n",
       "      <td>32404</td>\n",
       "      <td>107363763</td>\n",
       "      <td>OPIS19290040088204</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     AGENT_ID     AGENT_NAME DATE_OF_JOINING     ADDRESS_LINE1 ADDRESS_LINE2  \\\n",
       "0  AGENT00001      Ray Johns      1993-06-05  1402 Maggies Way           NaN   \n",
       "1  AGENT00002  Angelo Borjon      2005-12-27    414 Tanya Pass           NaN   \n",
       "\n",
       "               CITY STATE  POSTAL_CODE  EMP_ROUTING_NUMBER     EMP_ACCT_NUMBER  \n",
       "0  Waterbury Center    VT         5677            34584958  HKUN51252328472585  \n",
       "1       Panama City    FL        32404           107363763  OPIS19290040088204  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "employee.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "be53b8a5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1200, 10)"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "employee.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "4d220b41",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>TXN_DATE_TIME</th>\n",
       "      <th>TRANSACTION_ID</th>\n",
       "      <th>CUSTOMER_ID</th>\n",
       "      <th>POLICY_NUMBER</th>\n",
       "      <th>POLICY_EFF_DT</th>\n",
       "      <th>LOSS_DT</th>\n",
       "      <th>REPORT_DT</th>\n",
       "      <th>INSURANCE_TYPE</th>\n",
       "      <th>PREMIUM_AMOUNT</th>\n",
       "      <th>CLAIM_AMOUNT</th>\n",
       "      <th>...</th>\n",
       "      <th>CLAIM_STATUS</th>\n",
       "      <th>INCIDENT_SEVERITY</th>\n",
       "      <th>AUTHORITY_CONTACTED</th>\n",
       "      <th>ANY_INJURY</th>\n",
       "      <th>POLICE_REPORT_AVAILABLE</th>\n",
       "      <th>INCIDENT_STATE</th>\n",
       "      <th>INCIDENT_CITY</th>\n",
       "      <th>INCIDENT_HOUR_OF_THE_DAY</th>\n",
       "      <th>AGENT_ID</th>\n",
       "      <th>VENDOR_ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-06-01 00:00:00</td>\n",
       "      <td>TXN00000001</td>\n",
       "      <td>A00003822</td>\n",
       "      <td>PLC00008468</td>\n",
       "      <td>2015-06-23</td>\n",
       "      <td>2020-05-16</td>\n",
       "      <td>2020-05-21</td>\n",
       "      <td>Health</td>\n",
       "      <td>157.13</td>\n",
       "      <td>9000</td>\n",
       "      <td>...</td>\n",
       "      <td>A</td>\n",
       "      <td>Major Loss</td>\n",
       "      <td>Police</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>GA</td>\n",
       "      <td>Savannah</td>\n",
       "      <td>4</td>\n",
       "      <td>AGENT00413</td>\n",
       "      <td>VNDR00556</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-06-01 00:00:00</td>\n",
       "      <td>TXN00000002</td>\n",
       "      <td>A00008149</td>\n",
       "      <td>PLC00009594</td>\n",
       "      <td>2018-04-21</td>\n",
       "      <td>2020-05-13</td>\n",
       "      <td>2020-05-18</td>\n",
       "      <td>Property</td>\n",
       "      <td>141.71</td>\n",
       "      <td>26000</td>\n",
       "      <td>...</td>\n",
       "      <td>A</td>\n",
       "      <td>Total Loss</td>\n",
       "      <td>Ambulance</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>AL</td>\n",
       "      <td>Montgomery</td>\n",
       "      <td>0</td>\n",
       "      <td>AGENT00769</td>\n",
       "      <td>VNDR00592</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 38 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         TXN_DATE_TIME TRANSACTION_ID CUSTOMER_ID POLICY_NUMBER POLICY_EFF_DT  \\\n",
       "0  2020-06-01 00:00:00    TXN00000001   A00003822   PLC00008468    2015-06-23   \n",
       "1  2020-06-01 00:00:00    TXN00000002   A00008149   PLC00009594    2018-04-21   \n",
       "\n",
       "      LOSS_DT   REPORT_DT INSURANCE_TYPE  PREMIUM_AMOUNT  CLAIM_AMOUNT  ...  \\\n",
       "0  2020-05-16  2020-05-21         Health          157.13          9000  ...   \n",
       "1  2020-05-13  2020-05-18       Property          141.71         26000  ...   \n",
       "\n",
       "  CLAIM_STATUS INCIDENT_SEVERITY AUTHORITY_CONTACTED ANY_INJURY  \\\n",
       "0            A        Major Loss              Police          0   \n",
       "1            A        Total Loss           Ambulance          1   \n",
       "\n",
       "  POLICE_REPORT_AVAILABLE  INCIDENT_STATE INCIDENT_CITY  \\\n",
       "0                       1              GA      Savannah   \n",
       "1                       0              AL    Montgomery   \n",
       "\n",
       "  INCIDENT_HOUR_OF_THE_DAY    AGENT_ID  VENDOR_ID  \n",
       "0                        4  AGENT00413  VNDR00556  \n",
       "1                        0  AGENT00769  VNDR00592  \n",
       "\n",
       "[2 rows x 38 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "insurance.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "99b4c548",
   "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>VENDOR_ID</th>\n",
       "      <th>VENDOR_NAME</th>\n",
       "      <th>ADDRESS_LINE1</th>\n",
       "      <th>ADDRESS_LINE2</th>\n",
       "      <th>CITY</th>\n",
       "      <th>STATE</th>\n",
       "      <th>POSTAL_CODE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>VNDR00001</td>\n",
       "      <td>King, Proctor and Jones</td>\n",
       "      <td>2027 North Shannon Drive</td>\n",
       "      <td>#5</td>\n",
       "      <td>Fayetteville</td>\n",
       "      <td>AR</td>\n",
       "      <td>72703</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>VNDR00002</td>\n",
       "      <td>Garcia Ltd</td>\n",
       "      <td>5701 East Shirley Lane</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Montgomery</td>\n",
       "      <td>AL</td>\n",
       "      <td>36117</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   VENDOR_ID              VENDOR_NAME             ADDRESS_LINE1 ADDRESS_LINE2  \\\n",
       "0  VNDR00001  King, Proctor and Jones  2027 North Shannon Drive            #5   \n",
       "1  VNDR00002               Garcia Ltd    5701 East Shirley Lane           NaN   \n",
       "\n",
       "           CITY STATE  POSTAL_CODE  \n",
       "0  Fayetteville    AR        72703  \n",
       "1    Montgomery    AL        36117  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "vendor.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "88682119",
   "metadata": {},
   "source": [
    "# Task # 1\n",
    "## Merge the 3 dataset and create 1 view of data.\n",
    "You can merge insurance_data.csv and employee_data.csv on\n",
    "AGENT_ID You can merge insurance_data.csv and vendor_data.csv on\n",
    "\n",
    "VENDOR_ID Note: Use left Outer join as not all claims require Vendor"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "d2358c6a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>TXN_DATE_TIME</th>\n",
       "      <th>TRANSACTION_ID</th>\n",
       "      <th>CUSTOMER_ID</th>\n",
       "      <th>POLICY_NUMBER</th>\n",
       "      <th>POLICY_EFF_DT</th>\n",
       "      <th>LOSS_DT</th>\n",
       "      <th>REPORT_DT</th>\n",
       "      <th>INSURANCE_TYPE</th>\n",
       "      <th>PREMIUM_AMOUNT</th>\n",
       "      <th>CLAIM_AMOUNT</th>\n",
       "      <th>...</th>\n",
       "      <th>STATE_A</th>\n",
       "      <th>POSTAL_CODE_A</th>\n",
       "      <th>EMP_ROUTING_NUMBER</th>\n",
       "      <th>EMP_ACCT_NUMBER</th>\n",
       "      <th>VENDOR_NAME</th>\n",
       "      <th>ADDRESS_LINE1</th>\n",
       "      <th>ADDRESS_LINE2</th>\n",
       "      <th>CITY</th>\n",
       "      <th>STATE</th>\n",
       "      <th>POSTAL_CODE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-06-01 00:00:00</td>\n",
       "      <td>TXN00000001</td>\n",
       "      <td>A00003822</td>\n",
       "      <td>PLC00008468</td>\n",
       "      <td>2015-06-23</td>\n",
       "      <td>2020-05-16</td>\n",
       "      <td>2020-05-21</td>\n",
       "      <td>Health</td>\n",
       "      <td>157.13</td>\n",
       "      <td>9000</td>\n",
       "      <td>...</td>\n",
       "      <td>TN</td>\n",
       "      <td>37217</td>\n",
       "      <td>35441155</td>\n",
       "      <td>LRZO53254369804934</td>\n",
       "      <td>Ramsey Inc</td>\n",
       "      <td>3114 West 20th Court</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Panama City</td>\n",
       "      <td>FL</td>\n",
       "      <td>32405.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-06-01 00:00:00</td>\n",
       "      <td>TXN00000002</td>\n",
       "      <td>A00008149</td>\n",
       "      <td>PLC00009594</td>\n",
       "      <td>2018-04-21</td>\n",
       "      <td>2020-05-13</td>\n",
       "      <td>2020-05-18</td>\n",
       "      <td>Property</td>\n",
       "      <td>141.71</td>\n",
       "      <td>26000</td>\n",
       "      <td>...</td>\n",
       "      <td>AZ</td>\n",
       "      <td>85301</td>\n",
       "      <td>44055451</td>\n",
       "      <td>XBYG14382904542874</td>\n",
       "      <td>Morales, Campbell and Washington</td>\n",
       "      <td>42 West Louise Street</td>\n",
       "      <td>B</td>\n",
       "      <td>Fayetteville</td>\n",
       "      <td>AR</td>\n",
       "      <td>72701.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 53 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         TXN_DATE_TIME TRANSACTION_ID CUSTOMER_ID POLICY_NUMBER POLICY_EFF_DT  \\\n",
       "0  2020-06-01 00:00:00    TXN00000001   A00003822   PLC00008468    2015-06-23   \n",
       "1  2020-06-01 00:00:00    TXN00000002   A00008149   PLC00009594    2018-04-21   \n",
       "\n",
       "      LOSS_DT   REPORT_DT INSURANCE_TYPE  PREMIUM_AMOUNT  CLAIM_AMOUNT  ...  \\\n",
       "0  2020-05-16  2020-05-21         Health          157.13          9000  ...   \n",
       "1  2020-05-13  2020-05-18       Property          141.71         26000  ...   \n",
       "\n",
       "  STATE_A POSTAL_CODE_A EMP_ROUTING_NUMBER     EMP_ACCT_NUMBER  \\\n",
       "0      TN         37217           35441155  LRZO53254369804934   \n",
       "1      AZ         85301           44055451  XBYG14382904542874   \n",
       "\n",
       "                        VENDOR_NAME          ADDRESS_LINE1 ADDRESS_LINE2  \\\n",
       "0                        Ramsey Inc   3114 West 20th Court           NaN   \n",
       "1  Morales, Campbell and Washington  42 West Louise Street             B   \n",
       "\n",
       "           CITY  STATE  POSTAL_CODE  \n",
       "0   Panama City     FL      32405.0  \n",
       "1  Fayetteville     AR      72701.0  \n",
       "\n",
       "[2 rows x 53 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "insurance_employee =pd.merge(insurance , employee, on= \"AGENT_ID\", how = \"left\",suffixes=('_C','_A'))\n",
    "insurance_employee_vendor = pd.merge(insurance_employee,vendor, on = \"VENDOR_ID\", how = \"left\" )\n",
    "insurance_employee_vendor.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "bca4e23f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# combine datafile convert in to csv file \n",
    "insurance_employee_vendor.to_csv(\"insurance_employee_vendor.csv\",index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "00373d07",
   "metadata": {},
   "source": [
    "# Task #2\n",
    "Business Leader wants to find Top 3 Insurance Type where we are getting\n",
    "most insurance claims?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "429d24d6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Property    1692\n",
       "Mobile      1692\n",
       "Health      1690\n",
       "Name: INSURANCE_TYPE, dtype: int64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list_insurance_type = insurance[\"INSURANCE_TYPE\"].value_counts()\n",
    "list_insurance_type[:3]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b7ce405b",
   "metadata": {},
   "source": [
    "# Task #3\n",
    "Business Leader wants to find Top 5 States where we are getting most insurance claims\n",
    "for customer belonging to HIGH(H) risk segment?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "81aaa3c1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "CA    148\n",
       "AZ    105\n",
       "FL    104\n",
       "TN    100\n",
       "AR     97\n",
       "Name: STATE, dtype: int64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list_high_risk = insurance[insurance[\"RISK_SEGMENTATION\"] == \"H\"]\n",
    "state_list = list_high_risk[\"STATE\"].value_counts()\n",
    "state_list[:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "426e4e9d",
   "metadata": {},
   "source": [
    "# Task #4\n",
    "Business wants to create a new variable “COLOCATION” which will have following values IF\n",
    "Customer State == Incident State \n",
    "== Agent Address State THEN 1 ELSE 0\n",
    "Find the mean of this new column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "75ac99a0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['TXN_DATE_TIME', 'TRANSACTION_ID', 'CUSTOMER_ID', 'POLICY_NUMBER',\n",
       "       'POLICY_EFF_DT', 'LOSS_DT', 'REPORT_DT', 'INSURANCE_TYPE',\n",
       "       'PREMIUM_AMOUNT', 'CLAIM_AMOUNT', 'CUSTOMER_NAME', 'ADDRESS_LINE1_C',\n",
       "       'ADDRESS_LINE2_C', 'CITY_C', 'STATE_C', 'POSTAL_CODE_C', 'SSN',\n",
       "       'MARITAL_STATUS', 'AGE', 'TENURE', 'EMPLOYMENT_STATUS',\n",
       "       'NO_OF_FAMILY_MEMBERS', 'RISK_SEGMENTATION', 'HOUSE_TYPE',\n",
       "       'SOCIAL_CLASS', 'ROUTING_NUMBER', 'ACCT_NUMBER',\n",
       "       'CUSTOMER_EDUCATION_LEVEL', 'CLAIM_STATUS', 'INCIDENT_SEVERITY',\n",
       "       'AUTHORITY_CONTACTED', 'ANY_INJURY', 'POLICE_REPORT_AVAILABLE',\n",
       "       'INCIDENT_STATE', 'INCIDENT_CITY', 'INCIDENT_HOUR_OF_THE_DAY',\n",
       "       'AGENT_ID', 'VENDOR_ID', 'AGENT_NAME', 'DATE_OF_JOINING',\n",
       "       'ADDRESS_LINE1_A', 'ADDRESS_LINE2_A', 'CITY_A', 'STATE_A',\n",
       "       'POSTAL_CODE_A', 'EMP_ROUTING_NUMBER', 'EMP_ACCT_NUMBER', 'VENDOR_NAME',\n",
       "       'ADDRESS_LINE1', 'ADDRESS_LINE2', 'CITY', 'STATE', 'POSTAL_CODE'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "i_e_v = insurance_employee_vendor\n",
    "i_e_v.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "60fe3b51",
   "metadata": {},
   "outputs": [],
   "source": [
    "i_e_v[\"COLOCATION\"] = ((i_e_v[\"STATE_C\"] == i_e_v[\"INCIDENT_STATE\"]) & (i_e_v[\"STATE_C\"] == i_e_v[\"STATE_A\"])).astype(\"int\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "1eb0a7f0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.0044"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "i_e_v[\"COLOCATION\"] .mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fcfa0377",
   "metadata": {},
   "source": [
    "# Task #5\n",
    "Data entry error was detected in the data and you are required to correct it. If for any\n",
    "claim transaction “AUTHORITY_CONTACTED” is NOT “Police” and POLICE_AVAILABLE == 1\n",
    "Then Update “AUTHORITY_CONTACTED” to Police."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "4371c415",
   "metadata": {},
   "outputs": [],
   "source": [
    "update_row_police = i_e_v[(i_e_v[\"AUTHORITY_CONTACTED\"] != \"Police\") & (i_e_v[\"POLICE_REPORT_AVAILABLE\"] == 1)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "7ca7da4e",
   "metadata": {},
   "outputs": [],
   "source": [
    "index_no = update_row_police.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "e21ea53d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# update the AUTHORITY_CONTACTED value to police \n",
    "i_e_v.loc[index_no,\"AUTHORITY_CONTACTED\"] = \"Police\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "c617afa8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>TXN_DATE_TIME</th>\n",
       "      <th>TRANSACTION_ID</th>\n",
       "      <th>CUSTOMER_ID</th>\n",
       "      <th>POLICY_NUMBER</th>\n",
       "      <th>POLICY_EFF_DT</th>\n",
       "      <th>LOSS_DT</th>\n",
       "      <th>REPORT_DT</th>\n",
       "      <th>INSURANCE_TYPE</th>\n",
       "      <th>PREMIUM_AMOUNT</th>\n",
       "      <th>CLAIM_AMOUNT</th>\n",
       "      <th>...</th>\n",
       "      <th>POSTAL_CODE_A</th>\n",
       "      <th>EMP_ROUTING_NUMBER</th>\n",
       "      <th>EMP_ACCT_NUMBER</th>\n",
       "      <th>VENDOR_NAME</th>\n",
       "      <th>ADDRESS_LINE1</th>\n",
       "      <th>ADDRESS_LINE2</th>\n",
       "      <th>CITY</th>\n",
       "      <th>STATE</th>\n",
       "      <th>POSTAL_CODE</th>\n",
       "      <th>COLOCATION</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>0 rows × 54 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [TXN_DATE_TIME, TRANSACTION_ID, CUSTOMER_ID, POLICY_NUMBER, POLICY_EFF_DT, LOSS_DT, REPORT_DT, INSURANCE_TYPE, PREMIUM_AMOUNT, CLAIM_AMOUNT, CUSTOMER_NAME, ADDRESS_LINE1_C, ADDRESS_LINE2_C, CITY_C, STATE_C, POSTAL_CODE_C, SSN, MARITAL_STATUS, AGE, TENURE, EMPLOYMENT_STATUS, NO_OF_FAMILY_MEMBERS, RISK_SEGMENTATION, HOUSE_TYPE, SOCIAL_CLASS, ROUTING_NUMBER, ACCT_NUMBER, CUSTOMER_EDUCATION_LEVEL, CLAIM_STATUS, INCIDENT_SEVERITY, AUTHORITY_CONTACTED, ANY_INJURY, POLICE_REPORT_AVAILABLE, INCIDENT_STATE, INCIDENT_CITY, INCIDENT_HOUR_OF_THE_DAY, AGENT_ID, VENDOR_ID, AGENT_NAME, DATE_OF_JOINING, ADDRESS_LINE1_A, ADDRESS_LINE2_A, CITY_A, STATE_A, POSTAL_CODE_A, EMP_ROUTING_NUMBER, EMP_ACCT_NUMBER, VENDOR_NAME, ADDRESS_LINE1, ADDRESS_LINE2, CITY, STATE, POSTAL_CODE, COLOCATION]\n",
       "Index: []\n",
       "\n",
       "[0 rows x 54 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "i_e_v[(i_e_v[\"AUTHORITY_CONTACTED\"] != \"Police\") & (i_e_v[\"POLICE_REPORT_AVAILABLE\"] == 1)]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eeb1f824",
   "metadata": {},
   "source": [
    "# Task #6\n",
    "Business wants to check the Claim Amount for deviation for each transaction, they would\n",
    "like you to calculate as follow\n",
    "CLAIM_DEVIATION = AVG_CLAIM_AMOUNT_FOR_LAST_30DAYS (same insurance type)\n",
    "/ CURRENT_CLAIM_AMOUNT\n",
    "If the value < 0.5 THEN CLAIM_DEVIATION = 1 ELSE 0\n",
    "If there is less than 30 days of transaction history THEN\n",
    "-1 Note: LAST_30DAYS does not include current day"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "463c87b3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# convert TXN_DATE_TIME to date time \n",
    "i_e_v[\"TXN_DATE_TIME\"] = pd.to_datetime(i_e_v[\"TXN_DATE_TIME\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "682e7602",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>TXN_DATE_TIME</th>\n",
       "      <th>TRANSACTION_ID</th>\n",
       "      <th>CUSTOMER_ID</th>\n",
       "      <th>POLICY_NUMBER</th>\n",
       "      <th>POLICY_EFF_DT</th>\n",
       "      <th>LOSS_DT</th>\n",
       "      <th>REPORT_DT</th>\n",
       "      <th>INSURANCE_TYPE</th>\n",
       "      <th>PREMIUM_AMOUNT</th>\n",
       "      <th>CLAIM_AMOUNT</th>\n",
       "      <th>...</th>\n",
       "      <th>POSTAL_CODE_A</th>\n",
       "      <th>EMP_ROUTING_NUMBER</th>\n",
       "      <th>EMP_ACCT_NUMBER</th>\n",
       "      <th>VENDOR_NAME</th>\n",
       "      <th>ADDRESS_LINE1</th>\n",
       "      <th>ADDRESS_LINE2</th>\n",
       "      <th>CITY</th>\n",
       "      <th>STATE</th>\n",
       "      <th>POSTAL_CODE</th>\n",
       "      <th>COLOCATION</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-06-01</td>\n",
       "      <td>TXN00000001</td>\n",
       "      <td>A00003822</td>\n",
       "      <td>PLC00008468</td>\n",
       "      <td>2015-06-23</td>\n",
       "      <td>2020-05-16</td>\n",
       "      <td>2020-05-21</td>\n",
       "      <td>Health</td>\n",
       "      <td>157.13</td>\n",
       "      <td>9000</td>\n",
       "      <td>...</td>\n",
       "      <td>37217</td>\n",
       "      <td>35441155</td>\n",
       "      <td>LRZO53254369804934</td>\n",
       "      <td>Ramsey Inc</td>\n",
       "      <td>3114 West 20th Court</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Panama City</td>\n",
       "      <td>FL</td>\n",
       "      <td>32405.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-06-01</td>\n",
       "      <td>TXN00000002</td>\n",
       "      <td>A00008149</td>\n",
       "      <td>PLC00009594</td>\n",
       "      <td>2018-04-21</td>\n",
       "      <td>2020-05-13</td>\n",
       "      <td>2020-05-18</td>\n",
       "      <td>Property</td>\n",
       "      <td>141.71</td>\n",
       "      <td>26000</td>\n",
       "      <td>...</td>\n",
       "      <td>85301</td>\n",
       "      <td>44055451</td>\n",
       "      <td>XBYG14382904542874</td>\n",
       "      <td>Morales, Campbell and Washington</td>\n",
       "      <td>42 West Louise Street</td>\n",
       "      <td>B</td>\n",
       "      <td>Fayetteville</td>\n",
       "      <td>AR</td>\n",
       "      <td>72701.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 54 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  TXN_DATE_TIME TRANSACTION_ID CUSTOMER_ID POLICY_NUMBER POLICY_EFF_DT  \\\n",
       "0    2020-06-01    TXN00000001   A00003822   PLC00008468    2015-06-23   \n",
       "1    2020-06-01    TXN00000002   A00008149   PLC00009594    2018-04-21   \n",
       "\n",
       "      LOSS_DT   REPORT_DT INSURANCE_TYPE  PREMIUM_AMOUNT  CLAIM_AMOUNT  ...  \\\n",
       "0  2020-05-16  2020-05-21         Health          157.13          9000  ...   \n",
       "1  2020-05-13  2020-05-18       Property          141.71         26000  ...   \n",
       "\n",
       "  POSTAL_CODE_A EMP_ROUTING_NUMBER     EMP_ACCT_NUMBER  \\\n",
       "0         37217           35441155  LRZO53254369804934   \n",
       "1         85301           44055451  XBYG14382904542874   \n",
       "\n",
       "                        VENDOR_NAME          ADDRESS_LINE1  ADDRESS_LINE2  \\\n",
       "0                        Ramsey Inc   3114 West 20th Court            NaN   \n",
       "1  Morales, Campbell and Washington  42 West Louise Street              B   \n",
       "\n",
       "           CITY STATE  POSTAL_CODE  COLOCATION  \n",
       "0   Panama City    FL      32405.0           0  \n",
       "1  Fayetteville    AR      72701.0           0  \n",
       "\n",
       "[2 rows x 54 columns]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "i_e_v.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "eca66169",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "| 2021-05-30 00:00:00 |-----| 2021-06-30 00:00:00 |\n"
     ]
    }
   ],
   "source": [
    "last_date = i_e_v[\"TXN_DATE_TIME\"].max()\n",
    "start_date = last_date - pd.Timedelta(days=31)\n",
    "print(\"|\",start_date, \"|-----|\" ,last_date,\"|\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "dcbf4a5b",
   "metadata": {},
   "outputs": [],
   "source": [
    "last_30_days = i_e_v[(i_e_v[\"TXN_DATE_TIME\"] > start_date) & (i_e_v[\"TXN_DATE_TIME\"] < last_date)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "88a87130",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>TXN_DATE_TIME</th>\n",
       "      <th>TRANSACTION_ID</th>\n",
       "      <th>CUSTOMER_ID</th>\n",
       "      <th>POLICY_NUMBER</th>\n",
       "      <th>POLICY_EFF_DT</th>\n",
       "      <th>LOSS_DT</th>\n",
       "      <th>REPORT_DT</th>\n",
       "      <th>INSURANCE_TYPE</th>\n",
       "      <th>PREMIUM_AMOUNT</th>\n",
       "      <th>CLAIM_AMOUNT</th>\n",
       "      <th>...</th>\n",
       "      <th>POSTAL_CODE_A</th>\n",
       "      <th>EMP_ROUTING_NUMBER</th>\n",
       "      <th>EMP_ACCT_NUMBER</th>\n",
       "      <th>VENDOR_NAME</th>\n",
       "      <th>ADDRESS_LINE1</th>\n",
       "      <th>ADDRESS_LINE2</th>\n",
       "      <th>CITY</th>\n",
       "      <th>STATE</th>\n",
       "      <th>POSTAL_CODE</th>\n",
       "      <th>COLOCATION</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>9189</th>\n",
       "      <td>2021-05-31</td>\n",
       "      <td>TXN00009190</td>\n",
       "      <td>A00004648</td>\n",
       "      <td>PLC00005883</td>\n",
       "      <td>2014-12-20</td>\n",
       "      <td>2021-05-28</td>\n",
       "      <td>2021-05-28</td>\n",
       "      <td>Property</td>\n",
       "      <td>112.57</td>\n",
       "      <td>16000</td>\n",
       "      <td>...</td>\n",
       "      <td>95501</td>\n",
       "      <td>114787167</td>\n",
       "      <td>NPRV40932107822625</td>\n",
       "      <td>Wilson PLC</td>\n",
       "      <td>23 North Hill Street</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Nashville</td>\n",
       "      <td>TN</td>\n",
       "      <td>37210.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9190</th>\n",
       "      <td>2021-05-31</td>\n",
       "      <td>TXN00009191</td>\n",
       "      <td>A00005798</td>\n",
       "      <td>PLC00001906</td>\n",
       "      <td>2015-02-02</td>\n",
       "      <td>2021-05-24</td>\n",
       "      <td>2021-05-26</td>\n",
       "      <td>Travel</td>\n",
       "      <td>69.41</td>\n",
       "      <td>2000</td>\n",
       "      <td>...</td>\n",
       "      <td>6042</td>\n",
       "      <td>109301503</td>\n",
       "      <td>EMFD60069652268552</td>\n",
       "      <td>Welch, Keller and Hicks</td>\n",
       "      <td>1820 22nd Street Southeast</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Washington</td>\n",
       "      <td>DC</td>\n",
       "      <td>20020.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 54 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     TXN_DATE_TIME TRANSACTION_ID CUSTOMER_ID POLICY_NUMBER POLICY_EFF_DT  \\\n",
       "9189    2021-05-31    TXN00009190   A00004648   PLC00005883    2014-12-20   \n",
       "9190    2021-05-31    TXN00009191   A00005798   PLC00001906    2015-02-02   \n",
       "\n",
       "         LOSS_DT   REPORT_DT INSURANCE_TYPE  PREMIUM_AMOUNT  CLAIM_AMOUNT  \\\n",
       "9189  2021-05-28  2021-05-28       Property          112.57         16000   \n",
       "9190  2021-05-24  2021-05-26         Travel           69.41          2000   \n",
       "\n",
       "      ... POSTAL_CODE_A EMP_ROUTING_NUMBER     EMP_ACCT_NUMBER  \\\n",
       "9189  ...         95501          114787167  NPRV40932107822625   \n",
       "9190  ...          6042          109301503  EMFD60069652268552   \n",
       "\n",
       "                  VENDOR_NAME               ADDRESS_LINE1  ADDRESS_LINE2  \\\n",
       "9189               Wilson PLC        23 North Hill Street            NaN   \n",
       "9190  Welch, Keller and Hicks  1820 22nd Street Southeast            NaN   \n",
       "\n",
       "            CITY STATE  POSTAL_CODE  COLOCATION  \n",
       "9189   Nashville    TN      37210.0           0  \n",
       "9190  Washington    DC      20020.0           0  \n",
       "\n",
       "[2 rows x 54 columns]"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "last_30_days.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "id": "207ccb38",
   "metadata": {},
   "outputs": [],
   "source": [
    "AVG_CLAIM_AMOUNT_FOR_LAST_30DAYS = dict(last_30_days.groupby(\"INSURANCE_TYPE\")[\"CLAIM_AMOUNT\"].mean())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "id": "ba8fa8e1",
   "metadata": {},
   "outputs": [],
   "source": [
    "def CLAIM_DEVIATION(row):\n",
    "    if (last_date- row[\"TXN_DATE_TIME\"]) < pd.Timedelta(days=30):\n",
    "        return -1\n",
    "    CLAIM_DEVIATION = AVG_CLAIM_AMOUNT_FOR_LAST_30DAYS[row[\"INSURANCE_TYPE\"]] / row['CLAIM_AMOUNT']\n",
    "    if CLAIM_DEVIATION < 0.5:\n",
    "        return 1\n",
    "    else:\n",
    "        return 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "id": "bae7c0d1",
   "metadata": {},
   "outputs": [],
   "source": [
    "i_e_v[\"CLAIM_DEVIATION\"] = i_e_v.apply(CLAIM_DEVIATION,axis = 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "id": "fe2b99d8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       " 0    9216\n",
       "-1     784\n",
       "Name: CLAIM_DEVIATION, dtype: int64"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "i_e_v[\"CLAIM_DEVIATION\"].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2f3da306",
   "metadata": {},
   "source": [
    "# Task #7\n",
    "Find All Agents who have worked on more than 2 types of Insurance Claims. Sort them by\n",
    "Total Claim Amount Approved under them in descending order"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "id": "604c574b",
   "metadata": {},
   "outputs": [],
   "source": [
    "agent_count = i_e_v.groupby('AGENT_ID')['INSURANCE_TYPE'].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "id": "eb0158b2",
   "metadata": {},
   "outputs": [],
   "source": [
    "agent_index = agent_count[agent_count>2].index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "id": "d741bfbf",
   "metadata": {},
   "outputs": [],
   "source": [
    "Top_agents = i_e_v.groupby(['AGENT_ID','AGENT_NAME']).sum().loc[agent_index][['CLAIM_AMOUNT']].sort_values('CLAIM_AMOUNT',ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "id": "7c5df19b",
   "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></th>\n",
       "      <th>CLAIM_AMOUNT</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT_ID</th>\n",
       "      <th>AGENT_NAME</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>AGENT00807</th>\n",
       "      <th>Don Filkins</th>\n",
       "      <td>528800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT00679</th>\n",
       "      <th>Clara Barnett</th>\n",
       "      <td>489000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT00771</th>\n",
       "      <th>Roger Burns</th>\n",
       "      <td>422100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT00125</th>\n",
       "      <th>Salvador Soriano</th>\n",
       "      <td>400400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT00789</th>\n",
       "      <th>Alison Baron</th>\n",
       "      <td>392900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT00910</th>\n",
       "      <th>Vincent Ragan</th>\n",
       "      <td>15500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT00689</th>\n",
       "      <th>Connie Hunt</th>\n",
       "      <td>13700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT00732</th>\n",
       "      <th>Sylvia Tran</th>\n",
       "      <td>11300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT00706</th>\n",
       "      <th>Laura Staggs</th>\n",
       "      <td>10800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT00604</th>\n",
       "      <th>Thelma Salinas</th>\n",
       "      <td>5500</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1164 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                             CLAIM_AMOUNT\n",
       "AGENT_ID   AGENT_NAME                    \n",
       "AGENT00807 Don Filkins             528800\n",
       "AGENT00679 Clara Barnett           489000\n",
       "AGENT00771 Roger Burns             422100\n",
       "AGENT00125 Salvador Soriano        400400\n",
       "AGENT00789 Alison Baron            392900\n",
       "...                                   ...\n",
       "AGENT00910 Vincent Ragan            15500\n",
       "AGENT00689 Connie Hunt              13700\n",
       "AGENT00732 Sylvia Tran              11300\n",
       "AGENT00706 Laura Staggs             10800\n",
       "AGENT00604 Thelma Salinas            5500\n",
       "\n",
       "[1164 rows x 1 columns]"
      ]
     },
     "execution_count": 105,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Top_agents"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f368617b",
   "metadata": {},
   "source": [
    "# Task #8\n",
    "Mobile & Travel Insurance premium are discounted by 10%\n",
    "Health and Property Insurance premium are increased by 7%\n",
    "Life and Motor Insurance premium are marginally increased by 2%\n",
    "What will be overall change in % of the Premium Amount Collected for all these Customer?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "id": "0613ae25",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "885085.95"
      ]
     },
     "execution_count": 122,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_amount_initial = i_e_v[\"PREMIUM_AMOUNT\"].sum()\n",
    "total_amount_initial"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "id": "c6b0af94",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'Health': 252455.73,\n",
       " 'Life': 125621.22,\n",
       " 'Mobile': 15191.42,\n",
       " 'Motor': 165391.98,\n",
       " 'Property': 202285.83,\n",
       " 'Travel': 124139.77}"
      ]
     },
     "execution_count": 123,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_amount_individual  = dict(i_e_v.groupby(\"INSURANCE_TYPE\")[\"PREMIUM_AMOUNT\"].sum())\n",
    "total_amount_individual"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "id": "bf1edb85",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Mobile & Travel Insurance premium are discounted by 10% \n",
    "# Health and Property Insurance premium are increased by 7%\n",
    "# Life and Motor Insurance premium are marginally increased by 2%\n",
    "\n",
    "total_amount_individual[\"Mobile\"] *= 0.9\n",
    "total_amount_individual[\"Travel\"] *= 0.9    \n",
    "\n",
    "total_amount_individual[\"Health\"] *= 1.07\n",
    "total_amount_individual[\"Property\"] *= 1.07\n",
    "\n",
    "total_amount_individual[\"Life\"] *= 1.02\n",
    "total_amount_individual[\"Motor\"] *= 1.02"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "id": "ed2801ec",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'Health': 270127.6311,\n",
       " 'Life': 128133.6444,\n",
       " 'Mobile': 13672.278,\n",
       " 'Motor': 168699.81960000002,\n",
       " 'Property': 216445.8381,\n",
       " 'Travel': 111725.793}"
      ]
     },
     "execution_count": 128,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_amount_individual    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "id": "b06b4762",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "908805.0042000001"
      ]
     },
     "execution_count": 130,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_amount_after = sum(total_amount_individual.values())\n",
    "total_amount_after"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "id": "1114f29b",
   "metadata": {},
   "outputs": [],
   "source": [
    "overall_change = ((total_amount_after - total_amount_initial)*100)/total_amount_initial"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "id": "c962e21f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2.6798588543858517"
      ]
     },
     "execution_count": 132,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "overall_change"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9f529256",
   "metadata": {},
   "source": [
    "# Task #9\n",
    "Business wants to give discount to customer who are loyal and under stress due to Covid-19. \n",
    "They have laid down an eligibility Criteria as follow\n",
    "IF CUSTOMER_TENURE > 60 AND EMPLOYMENT_STATUS = “N”\n",
    "AND NO_OF_FAMILY_MEMBERS >=4 THEN 1 ELSE 0\n",
    "Create a new column “ELIGIBLE_FOR_DISCOUNT” and find it mean."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "id": "87f3be6b",
   "metadata": {},
   "outputs": [],
   "source": [
    "doscount_list = ((i_e_v[\"TENURE\"] >60)  & (i_e_v[\"EMPLOYMENT_STATUS\"] == \"N\") & (i_e_v[\"EMPLOYMENT_STATUS\"] == \"N\") \n",
    "                 & (i_e_v[\"NO_OF_FAMILY_MEMBERS\"] >= 4))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "id": "004ee01b",
   "metadata": {},
   "outputs": [],
   "source": [
    "i_e_v[\"ELIGIBLE_FOR_DISCOUNT\"] = doscount_list.astype(\"int\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "id": "139773a6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.0299"
      ]
     },
     "execution_count": 140,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "i_e_v[\"ELIGIBLE_FOR_DISCOUNT\"].mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "443da9eb",
   "metadata": {},
   "source": [
    "# Task #10\n",
    "Business wants to check Claim Velocity which is defined as follow\n",
    "CLAIM_VELOCITY = NO_OF_CLAIMS_IN_LAST30DAYS (for the current insurance type)/ NO_OF_CLAIMS_IN_LAST3DAYS\n",
    "(for the current insurance type)\n",
    "Note: LAST30DAYS & LAST3DAYS does not include current\n",
    "day"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "id": "5d187a55",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "| 2021-06-26 00:00:00 |-----| 2021-06-30 00:00:00 |\n"
     ]
    }
   ],
   "source": [
    "last_date = i_e_v[\"TXN_DATE_TIME\"].max()\n",
    "last_3_days = last_date - pd.Timedelta(days=4)\n",
    "print(\"|\",last_3_days, \"|-----|\" ,last_date,\"|\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 143,
   "id": "19068f7f",
   "metadata": {},
   "outputs": [],
   "source": [
    "last_3_days_list = i_e_v[(i_e_v[\"TXN_DATE_TIME\"] > last_3_days) & (i_e_v[\"TXN_DATE_TIME\"] < last_date)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "id": "1cd73255",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>TXN_DATE_TIME</th>\n",
       "      <th>TRANSACTION_ID</th>\n",
       "      <th>CUSTOMER_ID</th>\n",
       "      <th>POLICY_NUMBER</th>\n",
       "      <th>POLICY_EFF_DT</th>\n",
       "      <th>LOSS_DT</th>\n",
       "      <th>REPORT_DT</th>\n",
       "      <th>INSURANCE_TYPE</th>\n",
       "      <th>PREMIUM_AMOUNT</th>\n",
       "      <th>CLAIM_AMOUNT</th>\n",
       "      <th>...</th>\n",
       "      <th>EMP_ACCT_NUMBER</th>\n",
       "      <th>VENDOR_NAME</th>\n",
       "      <th>ADDRESS_LINE1</th>\n",
       "      <th>ADDRESS_LINE2</th>\n",
       "      <th>CITY</th>\n",
       "      <th>STATE</th>\n",
       "      <th>POSTAL_CODE</th>\n",
       "      <th>COLOCATION</th>\n",
       "      <th>CLAIM_DEVIATION</th>\n",
       "      <th>ELIGIBLE_FOR_DISCOUNT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>9897</th>\n",
       "      <td>2021-06-27</td>\n",
       "      <td>TXN00009898</td>\n",
       "      <td>A00005727</td>\n",
       "      <td>PLC00007733</td>\n",
       "      <td>2019-11-16</td>\n",
       "      <td>2021-06-14</td>\n",
       "      <td>2021-06-19</td>\n",
       "      <td>Property</td>\n",
       "      <td>114.82</td>\n",
       "      <td>17000</td>\n",
       "      <td>...</td>\n",
       "      <td>HUII66210152910986</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9898</th>\n",
       "      <td>2021-06-27</td>\n",
       "      <td>TXN00009899</td>\n",
       "      <td>A00003496</td>\n",
       "      <td>PLC00001925</td>\n",
       "      <td>2018-01-30</td>\n",
       "      <td>2021-06-15</td>\n",
       "      <td>2021-06-20</td>\n",
       "      <td>Motor</td>\n",
       "      <td>76.35</td>\n",
       "      <td>7000</td>\n",
       "      <td>...</td>\n",
       "      <td>HCQB64970746728804</td>\n",
       "      <td>Walker-Hines</td>\n",
       "      <td>824 Main Street</td>\n",
       "      <td>D</td>\n",
       "      <td>Manchester</td>\n",
       "      <td>CT</td>\n",
       "      <td>6040.0</td>\n",
       "      <td>0</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 56 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     TXN_DATE_TIME TRANSACTION_ID CUSTOMER_ID POLICY_NUMBER POLICY_EFF_DT  \\\n",
       "9897    2021-06-27    TXN00009898   A00005727   PLC00007733    2019-11-16   \n",
       "9898    2021-06-27    TXN00009899   A00003496   PLC00001925    2018-01-30   \n",
       "\n",
       "         LOSS_DT   REPORT_DT INSURANCE_TYPE  PREMIUM_AMOUNT  CLAIM_AMOUNT  \\\n",
       "9897  2021-06-14  2021-06-19       Property          114.82         17000   \n",
       "9898  2021-06-15  2021-06-20          Motor           76.35          7000   \n",
       "\n",
       "      ...     EMP_ACCT_NUMBER   VENDOR_NAME    ADDRESS_LINE1 ADDRESS_LINE2  \\\n",
       "9897  ...  HUII66210152910986           NaN              NaN           NaN   \n",
       "9898  ...  HCQB64970746728804  Walker-Hines  824 Main Street             D   \n",
       "\n",
       "            CITY  STATE POSTAL_CODE COLOCATION  CLAIM_DEVIATION  \\\n",
       "9897         NaN    NaN         NaN          0               -1   \n",
       "9898  Manchester     CT      6040.0          0               -1   \n",
       "\n",
       "      ELIGIBLE_FOR_DISCOUNT  \n",
       "9897                      0  \n",
       "9898                      0  \n",
       "\n",
       "[2 rows x 56 columns]"
      ]
     },
     "execution_count": 146,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "last_3_days_list.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 147,
   "id": "4b6f48b6",
   "metadata": {},
   "outputs": [],
   "source": [
    "claims_30days = pd.DataFrame.from_dict(last_30_days.groupby('INSURANCE_TYPE').size().to_dict(),orient='index',\n",
    "                                       columns=['NO_OF_CLAIMS_IN_LAST30DAYS'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "id": "dc60500f",
   "metadata": {},
   "outputs": [],
   "source": [
    "claims_3days = pd.DataFrame.from_dict(last_3_days_list.groupby('INSURANCE_TYPE').size().to_dict(),orient='index',\n",
    "                                       columns=['NO_OF_CLAIMS_IN_LAST3DAYS'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 173,
   "id": "ced4372f",
   "metadata": {},
   "outputs": [],
   "source": [
    "total_claims = claims_30days.join(claims_3days)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 171,
   "id": "4a8398c0",
   "metadata": {},
   "outputs": [],
   "source": [
    "total_claims[\"CLAIM_VELOCITY\"] = total_claims[\"NO_OF_CLAIMS_IN_LAST30DAYS\"] / total_claims[\"NO_OF_CLAIMS_IN_LAST3DAYS\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 172,
   "id": "b483d7c6",
   "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>NO_OF_CLAIMS_IN_LAST30DAYS</th>\n",
       "      <th>NO_OF_CLAIMS_IN_LAST3DAYS</th>\n",
       "      <th>CLAIM_VELOCITY</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Health</th>\n",
       "      <td>136</td>\n",
       "      <td>14</td>\n",
       "      <td>9.714286</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Life</th>\n",
       "      <td>140</td>\n",
       "      <td>14</td>\n",
       "      <td>10.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mobile</th>\n",
       "      <td>134</td>\n",
       "      <td>10</td>\n",
       "      <td>13.400000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Motor</th>\n",
       "      <td>122</td>\n",
       "      <td>13</td>\n",
       "      <td>9.384615</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Property</th>\n",
       "      <td>129</td>\n",
       "      <td>13</td>\n",
       "      <td>9.923077</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Travel</th>\n",
       "      <td>125</td>\n",
       "      <td>14</td>\n",
       "      <td>8.928571</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          NO_OF_CLAIMS_IN_LAST30DAYS  NO_OF_CLAIMS_IN_LAST3DAYS  \\\n",
       "Health                           136                         14   \n",
       "Life                             140                         14   \n",
       "Mobile                           134                         10   \n",
       "Motor                            122                         13   \n",
       "Property                         129                         13   \n",
       "Travel                           125                         14   \n",
       "\n",
       "          CLAIM_VELOCITY  \n",
       "Health          9.714286  \n",
       "Life           10.000000  \n",
       "Mobile         13.400000  \n",
       "Motor           9.384615  \n",
       "Property        9.923077  \n",
       "Travel          8.928571  "
      ]
     },
     "execution_count": 172,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_claims"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ddb0ba78",
   "metadata": {},
   "source": [
    "# Task #11\n",
    "Find all low performing agents i.e. employees who are in the bottom 5 percentile based\n",
    "on Claims worked by them."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 223,
   "id": "4d37a819",
   "metadata": {},
   "outputs": [],
   "source": [
    "all_agent_list = i_e_v.groupby(\"AGENT_ID\")[\"CLAIM_AMOUNT\"].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 227,
   "id": "7ecb43c2",
   "metadata": {},
   "outputs": [],
   "source": [
    "bottom_5_percentile = i_e_v.groupby('AGENT_ID')['CLAIM_AMOUNT'].sum().quantile(0.05)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 228,
   "id": "c9c34776",
   "metadata": {},
   "outputs": [],
   "source": [
    "low_performing_agent_list = all_agent_list[all_agent_list[:] <= bottom_5_percentile]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 229,
   "id": "e44314da",
   "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>CLAIM_AMOUNT</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT_ID</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>AGENT00006</th>\n",
       "      <td>22400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT00014</th>\n",
       "      <td>7400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT00016</th>\n",
       "      <td>27000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT00085</th>\n",
       "      <td>25700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT00088</th>\n",
       "      <td>27700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT00091</th>\n",
       "      <td>30000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT00098</th>\n",
       "      <td>28200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT00134</th>\n",
       "      <td>22000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT00149</th>\n",
       "      <td>28900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT00156</th>\n",
       "      <td>13200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT00172</th>\n",
       "      <td>22200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT00185</th>\n",
       "      <td>16100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT00186</th>\n",
       "      <td>27700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT00188</th>\n",
       "      <td>28400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT00197</th>\n",
       "      <td>18500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT00252</th>\n",
       "      <td>20000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT00263</th>\n",
       "      <td>11500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT00275</th>\n",
       "      <td>16200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT00279</th>\n",
       "      <td>28000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT00300</th>\n",
       "      <td>12600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT00310</th>\n",
       "      <td>28800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT00316</th>\n",
       "      <td>22200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT00351</th>\n",
       "      <td>21700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT00425</th>\n",
       "      <td>17800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT00442</th>\n",
       "      <td>30300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT00536</th>\n",
       "      <td>20000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT00562</th>\n",
       "      <td>21100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT00571</th>\n",
       "      <td>24200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT00585</th>\n",
       "      <td>16600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT00590</th>\n",
       "      <td>22200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT00604</th>\n",
       "      <td>5500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT00621</th>\n",
       "      <td>5300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT00628</th>\n",
       "      <td>6100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT00672</th>\n",
       "      <td>21300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT00689</th>\n",
       "      <td>13700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT00706</th>\n",
       "      <td>10800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT00721</th>\n",
       "      <td>9000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT00732</th>\n",
       "      <td>11300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT00773</th>\n",
       "      <td>15000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT00780</th>\n",
       "      <td>11300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT00790</th>\n",
       "      <td>6000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT00804</th>\n",
       "      <td>20000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT00805</th>\n",
       "      <td>22600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT00830</th>\n",
       "      <td>23500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT00841</th>\n",
       "      <td>17800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT00856</th>\n",
       "      <td>24200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT00894</th>\n",
       "      <td>27600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT00910</th>\n",
       "      <td>15500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT00969</th>\n",
       "      <td>28300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT00985</th>\n",
       "      <td>30000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT01021</th>\n",
       "      <td>24300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT01037</th>\n",
       "      <td>5600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT01052</th>\n",
       "      <td>28000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT01077</th>\n",
       "      <td>4200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT01107</th>\n",
       "      <td>24700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT01128</th>\n",
       "      <td>28000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT01131</th>\n",
       "      <td>27500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT01151</th>\n",
       "      <td>28000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT01154</th>\n",
       "      <td>800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGENT01179</th>\n",
       "      <td>27300</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            CLAIM_AMOUNT\n",
       "AGENT_ID                \n",
       "AGENT00006         22400\n",
       "AGENT00014          7400\n",
       "AGENT00016         27000\n",
       "AGENT00085         25700\n",
       "AGENT00088         27700\n",
       "AGENT00091         30000\n",
       "AGENT00098         28200\n",
       "AGENT00134         22000\n",
       "AGENT00149         28900\n",
       "AGENT00156         13200\n",
       "AGENT00172         22200\n",
       "AGENT00185         16100\n",
       "AGENT00186         27700\n",
       "AGENT00188         28400\n",
       "AGENT00197         18500\n",
       "AGENT00252         20000\n",
       "AGENT00263         11500\n",
       "AGENT00275         16200\n",
       "AGENT00279         28000\n",
       "AGENT00300         12600\n",
       "AGENT00310         28800\n",
       "AGENT00316         22200\n",
       "AGENT00351         21700\n",
       "AGENT00425         17800\n",
       "AGENT00442         30300\n",
       "AGENT00536         20000\n",
       "AGENT00562         21100\n",
       "AGENT00571         24200\n",
       "AGENT00585         16600\n",
       "AGENT00590         22200\n",
       "AGENT00604          5500\n",
       "AGENT00621          5300\n",
       "AGENT00628          6100\n",
       "AGENT00672         21300\n",
       "AGENT00689         13700\n",
       "AGENT00706         10800\n",
       "AGENT00721          9000\n",
       "AGENT00732         11300\n",
       "AGENT00773         15000\n",
       "AGENT00780         11300\n",
       "AGENT00790          6000\n",
       "AGENT00804         20000\n",
       "AGENT00805         22600\n",
       "AGENT00830         23500\n",
       "AGENT00841         17800\n",
       "AGENT00856         24200\n",
       "AGENT00894         27600\n",
       "AGENT00910         15500\n",
       "AGENT00969         28300\n",
       "AGENT00985         30000\n",
       "AGENT01021         24300\n",
       "AGENT01037          5600\n",
       "AGENT01052         28000\n",
       "AGENT01077          4200\n",
       "AGENT01107         24700\n",
       "AGENT01128         28000\n",
       "AGENT01131         27500\n",
       "AGENT01151         28000\n",
       "AGENT01154           800\n",
       "AGENT01179         27300"
      ]
     },
     "execution_count": 229,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.DataFrame(low_performing_agent_list)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "459dd54f",
   "metadata": {},
   "source": [
    "# Task #12\n",
    "Business wants to find all Suspicious Employees (Agents).\n",
    "IF TOTAL CLAIM AMOUNT which meet below criteria is >= 15000 THEN AGENT is classified\n",
    "as Suspicious ELSE Not\n",
    "CLAIM_STATUS = Approved AND CUSTOMER_RISK_SEGMENTATION = High\n",
    "AND INCIDENT_SEVERITY = “Major Loss”\n",
    "If Suspicious, then 1 ELSE 0. Find mean of this column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 232,
   "id": "4502d6eb",
   "metadata": {},
   "outputs": [],
   "source": [
    "agent_list = i_e_v[(i_e_v['CLAIM_STATUS']=='A')&(i_e_v['RISK_SEGMENTATION']=='H')&(i_e_v['INCIDENT_SEVERITY']=='Major Loss')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 236,
   "id": "fdf94d7b",
   "metadata": {},
   "outputs": [],
   "source": [
    "group_suspicious_data = agent_list.groupby(\"AGENT_ID\").sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 256,
   "id": "8413a4ac",
   "metadata": {},
   "outputs": [],
   "source": [
    "group_suspicious_agent = list(group_suspicious_data[group_suspicious_data[\"CLAIM_AMOUNT\"] >= 15000].index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 242,
   "id": "df0a8da0",
   "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>AGENT_ID</th>\n",
       "      <th>AGENT_NAME</th>\n",
       "      <th>DATE_OF_JOINING</th>\n",
       "      <th>ADDRESS_LINE1</th>\n",
       "      <th>ADDRESS_LINE2</th>\n",
       "      <th>CITY</th>\n",
       "      <th>STATE</th>\n",
       "      <th>POSTAL_CODE</th>\n",
       "      <th>EMP_ROUTING_NUMBER</th>\n",
       "      <th>EMP_ACCT_NUMBER</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AGENT00001</td>\n",
       "      <td>Ray Johns</td>\n",
       "      <td>1993-06-05</td>\n",
       "      <td>1402 Maggies Way</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Waterbury Center</td>\n",
       "      <td>VT</td>\n",
       "      <td>5677</td>\n",
       "      <td>34584958</td>\n",
       "      <td>HKUN51252328472585</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AGENT00002</td>\n",
       "      <td>Angelo Borjon</td>\n",
       "      <td>2005-12-27</td>\n",
       "      <td>414 Tanya Pass</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Panama City</td>\n",
       "      <td>FL</td>\n",
       "      <td>32404</td>\n",
       "      <td>107363763</td>\n",
       "      <td>OPIS19290040088204</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     AGENT_ID     AGENT_NAME DATE_OF_JOINING     ADDRESS_LINE1 ADDRESS_LINE2  \\\n",
       "0  AGENT00001      Ray Johns      1993-06-05  1402 Maggies Way           NaN   \n",
       "1  AGENT00002  Angelo Borjon      2005-12-27    414 Tanya Pass           NaN   \n",
       "\n",
       "               CITY STATE  POSTAL_CODE  EMP_ROUTING_NUMBER     EMP_ACCT_NUMBER  \n",
       "0  Waterbury Center    VT         5677            34584958  HKUN51252328472585  \n",
       "1       Panama City    FL        32404           107363763  OPIS19290040088204  "
      ]
     },
     "execution_count": 242,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "employee.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 261,
   "id": "fc36b851",
   "metadata": {},
   "outputs": [],
   "source": [
    "employee[\"Suspicious\"] = 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 262,
   "id": "fbba7a0c",
   "metadata": {},
   "outputs": [],
   "source": [
    "susp_emp_list = employee[\"AGENT_ID\"].isin(group_suspicious_agent)\n",
    "employee.loc[susp_emp_list,\"Suspicious\"] = 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 263,
   "id": "36dd8722",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.12416666666666666"
      ]
     },
     "execution_count": 263,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "employee[\"Suspicious\"].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8e134242",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}